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`	varchar(45)	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

Other Core Tables

Table	Purpose
`alerts`	Fired alert instances: device, org, severity, status (`active`, `acknowledged`, `resolved`, `suppressed`, `dismissed`), and ack/resolve/dismiss metadata. Related: `alertRules`, `alertTemplates`.
`scripts`	Reusable script definitions: language, content, parameter definitions, timeout, run-as. Related: `scriptExecutions` (per-device runs), `scriptExecutionBatches`.
`automations`	Trigger → conditions → actions definitions, owned by an org or a partner (partner-wide). Related: `automationRuns`, `automationDeviceResults`, `automationResourceBindings`.
`tickets`	Service-desk tickets: subject, status, priority, assignment, requester. Defined in `db/schema/portal.ts`. Related: `ticketCategories`, `ticketAlertLinks`.
`apiKeys`	Organization-scoped API keys (`brz_…`) with hashed secret, key prefix, scopes, rate limit, and human/service-principal linkage.

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 (prod uses a managed Postgres instance, not a
# local container; run with .env.prod sourced: set -a; source .env.prod; set +a)
psql "$DATABASE_URL"

# List tables
\dt

# Describe a table
\d devices

# Count devices
SELECT count(*) FROM devices;