Database Schema

Schema Location

All Drizzle ORM schema definitions are in apps/api/src/db/schema/.

Core Tables

Devices

The central table for managed endpoints:

Column	Type	Description
id	UUID	Primary key
orgId	UUID	FK → organizations
siteId	UUID	FK → sites
agentId	varchar(64)	Unique agent identifier
agentTokenHash	varchar(64)	SHA-256 hash of the main agent bearer token (nullable for pre-migration devices)
watchdogTokenHash	varchar(64)	SHA-256 hash of the companion watchdog token
helperTokenHash	varchar(64)	SHA-256 hash of the user-helper token
agentTokenSuspendedAt	timestamp	Set when the agent token is auto-suspended (e.g. cross-tenant access attempt). Re-enrollment is blocked until cleared.
agentTokenSuspendedReason	varchar(255)	Reason recorded alongside agentTokenSuspendedAt.
hostname	varchar(255)	Device hostname
displayName	varchar(255)	Optional display name (editable inline from the device detail page)
osType	enum	windows, macos, linux
osVersion	varchar(100)	OS version string
osBuild	varchar(100)	OS build identifier (optional)
architecture	varchar(20)	CPU architecture (e.g. amd64, arm64)
agentVersion	varchar(20)	Installed agent version
status	enum	online, offline, maintenance, updating, pending, decommissioned, quarantined
lastSeenAt	timestamp	Last telemetry received
lastSeenIp	inet	Source IP recorded on the most recent heartbeat; changes raise an agent.source.ip.changed audit event.
mainAgentSilentSince	timestamp	Set by the server when the main agent stops heartbeating but the watchdog is still online. Cleared when the agent resumes.
watchdogStatus	enum	connected, failover, offline — last reported watchdog state.
watchdogLastSeenAt	timestamp	Last watchdog check-in.
watchdogVersion	varchar(20)	Installed watchdog binary version.
enrolledAt	timestamp	When the device was enrolled
enrolledBy	UUID	FK → users (who initiated enrollment)
tags	text[]	Array of string tags
mtlsCertSerialNumber	varchar(128)	Cloudflare mTLS cert serial (optional)
mtlsCertExpiresAt	timestamp	mTLS cert expiry (optional)
mtlsCertIssuedAt	timestamp	mTLS cert issue date (optional)
mtlsCertCfId	varchar(128)	Cloudflare cert ID (optional)
quarantinedAt	timestamp	When device was quarantined (optional)
quarantinedReason	varchar(255)	Reason for quarantine (optional)
createdAt	timestamp	Row creation time
updatedAt	timestamp	Last update time

Related Tables

Table	Description
deviceHardware	CPU model, cores, RAM, disk, GPU, serial number, manufacturer
deviceNetworkInterfaces	Network interfaces with MAC, IP, type, primary flag, public IP
deviceDisks	Mount points, filesystem type, capacity, health
deviceTelemetry	Time-series CPU, RAM, disk, network, process count
deviceSoftware	Installed applications with version, publisher, install location
deviceRegistryEntries	Windows registry snapshots
deviceConfigEntries	Configuration file key-value pairs
deviceNetworkConnections	Active TCP/UDP connections with process info
deviceGroups	Static and dynamic device groups with filter rules
deviceGroupMemberships	Many-to-many device ↔ group mapping
deviceCommands	Command queue (pending, completed, failed)
elevationRequests	Privileged-action mode (PAM) elevation requests awaiting approval
elevationAudit	Immutable audit trail of elevation approve/deny/expire decisions
refreshTokenFamilies	Refresh-token lineage for reuse-detection (RFC 9700 §4.13.2)

Organizations

partners (MSP)
  └── organizations (customer)
        └── sites (location)
              └── device_groups
                    └── devices

Users & RBAC

Table	Purpose
users	User accounts with email, password hash, MFA status
roles	Role definitions (Partner Admin, Technician, Viewer, etc.)
permissions	Resource + action pairs (e.g., devices:read)
rolePermissions	Maps roles to permissions
partnerUsers	Maps users to partners with roles

Default Roles

Seeded by pnpm db:seed:

Role	Scope	Permissions
Partner Admin	partner	*:* (full access)
Partner Technician	partner	Read + execute on devices, scripts, alerts
Partner Viewer	partner	Read-only
Org Admin	organization	Full access within org
Org User	organization	Read + limited write

Schema Management

# Push schema changes (development)
pnpm db:push

# Run migrations (production)
pnpm db:migrate

# Seed default roles, permissions, templates
pnpm db:seed

# Open Drizzle Studio (visual schema browser)
pnpm db:studio

Inspecting the Database

# Connect to the database
docker compose -f docker/docker-compose.prod.yml exec postgres \
  psql -U breeze -d breeze

# List tables
\dt

# Describe a table
\d devices

# Count devices
SELECT count(*) FROM devices;