Skip to content
Breeze RMM

Database Schema

Schema Location

Section titled “Schema Location”

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

Core Tables

Section titled “Core Tables”

Devices

Section titled “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 |

Section titled “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

Section titled “Organizations”
partners (MSP)
  └── organizations (customer)
        └── sites (location)
              └── device_groups
                    └── devices

Users & RBAC

Section titled “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

Section titled “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

Section titled “Schema Management”
Terminal window
# 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

Section titled “Inspecting the Database”
Terminal window
# 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;