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
hostname	text	Device hostname
os	text	Operating system
arch	text	CPU architecture
agentVersion	text	Installed agent version
status	enum	online, offline, warning, critical, quarantined
organizationId	UUID	FK → organizations
siteId	UUID	FK → sites
agentTokenHash	text	SHA-256 hash of the agent bearer token
lastHeartbeat	timestamp	Last telemetry received
mtlsCertSerialNumber	text	Cloudflare mTLS cert serial (optional)
mtlsCertExpiresAt	timestamp	mTLS cert expiry (optional)
quarantinedAt	timestamp	When device was quarantined (optional)

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;