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

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;