Database Schema

PostgreSQL. Immutable migrations under internal/store/migrations/ are embedded in the server and applied on startup. db/schema.sql is the canonical snapshot of the current schema for review/manual use; it is not replayed as the migration mechanism.

schema_migrations records a contiguous version/name/SHA-256 checksum history. Current versions run from 1 baseline to 19 oidc_browser_handoff. Startup accepts only an exact prefix of the binary’s history and rejects gaps, unknown versions, renames, or changed SQL. The migration transaction uses a PostgreSQL advisory lock. Migrations are forward-only: once a newer version is applied, an older binary can reject it, so rollback requires restoring the pre-upgrade database as well as the application. rmm-server -migrate-only applies them with the owner’s credentials and exits, so the serving role need not own the schema (see 01-architecture).

Tables

agents

Managed endpoints.

Column	Type	Notes
id	uuid PK	agent identity
hostname, os, arch, version	text	reported at enrollment
description	text	operator-editable label, shown in the device list (e.g. the user’s name)
note	text	operator-editable private observation, shown only in device detail
status	text	online / offline / revoked
token_hash	text	SHA-256 of the agent’s auth token
pending_token_hash, token_rotation_id	text	one in-flight replacement credential; promotion occurs on reconnect
token_rotation_canceling	boolean	durable cancellation fence; prevents replacement-token promotion until restoration is confirmed
revoked_at	timestamptz	nullable; blocks control, jobs, and relay tickets when set
site_id	uuid FK → sites	nullable; the agent’s place in the org hierarchy
group_id	uuid FK → device_groups	nullable
tags	text[]	free-form labels
mesh_id	text	MeshCentral node id (nullable)
last_seen	timestamptz	heartbeat
created_at	timestamptz

device_groups

Departments: a group bound to a site (id, name, site_id FK → sites cascade, created_at). Company > Site > Department — a group never spans companies/sites; optional. An agent’s group_id must point at a department of the agent’s own site.

enroll_tokens

One-time / limited-use enrollment tokens (token, uses_left, expires_at).

site_id and the optional group_id decide where an enrolling agent lands. A trigger (enroll_token_group_matches_site) rejects an inconsistent pair, so a token can never name a department belonging to a different site than the one it enrols into. The installer’s --site flag does not decide this — it is accepted and discarded; the destination comes from the token row.

users

Console accounts. password_hash (bcrypt), role (role key such as admin/tech/readonly or a custom role), totp_secret (encrypted, nullable), totp_pending_secret (encrypted candidate, nullable), and totp_enabled for optional TOTP 2FA. Setup/rotation writes the pending candidate; confirmation atomically promotes it, so an active factor is not weakened mid-rotation.

email is the user’s address: where their alerts are delivered, and where a password reset link is sent. Because a reset resolves an account through it, it is an authentication factor rather than a contact detail — unique when set, and only users.email.manage may change it.

disabled_at (nullable) withdraws access without deleting the account, so the record of what it did survives. Nil means active. Every path that grants access filters on it in SQL rather than trusting a caller to check: password login, session lookup, API tokens bound to that user, alert delivery, and the password-reset lookup — that last one otherwise being a way straight back in. A timestamp rather than a boolean because “when was access withdrawn” is what an incident review asks, and re-disabling keeps the original value.

email_verified_at (nullable) records when the owner confirmed the address; the operator’s shared alert service delivers only to confirmed addresses, and a changed address needs a new confirmation. totp_last_step is the last accepted TOTP time step (unix time / 30), so a code is accepted once. oidc_issuer and oidc_subject link the account to a single sign-on identity (empty when not linked); while SSO is on, a linked account has no password path.

roles / role_permissions

Role definitions and their permission keys. roles.role_key is the stable key stored in users.role and api_tokens.role; system=true marks the built-in readonly, tech, and admin roles. role_permissions is keyed by (role_key, permission) and stores granular module permissions such as devices.view, devices.remote, jobs.create, jobs.manage, roles.manage. The built-in readonly preset includes devices.remote; its name does not mean pure observation.

user_recovery_codes

One-time 2FA recovery codes (id, user_id, code_hash SHA-256, used_at nullable). Issued when 2FA is enabled, single-use; cleared on disable/admin-reset.

user_scopes

Per-user access scope. Each row grants a user access to a Company (client), a Site, a Department (group), or a specific device; a user with no rows is unrestricted. Enforced on read + write paths and on the AI/MCP surface.

Column	Type	Notes
id	uuid PK
user_id	uuid FK → users	cascade delete
scope_type	text	client / site / group / agent
scope_id	uuid	id of the client/site/group/agent
		unique on (user_id, scope_type, scope_id)

scope_id is polymorphic, so no foreign key can cover it. Triggers on clients, sites, device_groups and agents stand in for one (migration 006): a scope row dies with its target. Triggers rather than cleanup in the delete handlers, because deleting a company cascades to its sites and departments and a cascade never reaches application code.

Without them a deleted device left a row behind, and a dangling row is not inert: the scope editor counted it but could not draw it, and since the console sends back the scopes it loaded, every save for that account failed validation on an entry the operator could neither see nor remove.

An account left with no rows at all by that pruning is disabled, not promoted: an empty set means unrestricted, so deleting the last thing a restricted technician could see would otherwise hand them the whole fleet. Deliberately granting full access (replacing the set with an empty one) is unaffected — the rule lives only in the pruning path.

api_tokens

Hashed API tokens for the UI/integrations (token_hash, name, role key, nullable user_id). The role key resolves to the same permission set as a user role. When bound to a user, the token inherits that user’s access scope. The bootstrap RMM_ADMIN_TOKEN is also accepted without a row and uses the built-in admin permission set.

jobs

Units of work targeted at an agent.

Column	Type	Notes
id	uuid PK
agent_id	uuid FK → agents	cascade delete
type	text	run_script (extensible)
description	text	human summary
shell	text	bash/sh/powershell/cmd/python
script	text	source to run
status	text	awaiting_approval→pending→sent→running→completed/failed, or rejected
requires_approval	bool	true for AI proposals
created_by	text	actor (admin, api, ai:<actor>)
approved_by	text	nullable
task_id	uuid	set when the job was produced by a scheduled task (nullable)
batch_id	uuid	bulk deployment: jobs created together share it; one batch per company (nullable)
exit_code	int	nullable until finished
stdout, stderr	text	result output
created_at, updated_at	timestamptz

inventory

Latest snapshot per agent (agent_id PK, data jsonb, collected_at).

remote_sessions

Brokered remote-access sessions (id, agent_id, provider, status, token, connect_url, created_by, expires_at). Native/files/terminal sessions additionally store independent viewer_ticket_hash and agent_ticket_hash values plus single-use timestamps; raw tickets are returned only at session creation and are never persisted.

audit_log

Record of sensitive actions (actor, action, target, detail jsonb, created_at). Actions include job.propose, job.approve, job.reject, job.result, remote.start; every action the server records has a console label.

Append-only in three layers (migration 012_audit_append_only):

1. Triggers refuse UPDATE and DELETE, except retention’s removal of the machine-written entries (job.automated, job.result).
2. Privileges: run the server as a role that does not own the table (openrmm_app, with migrations applied separately by the owner) and a compromised server cannot disable the triggers or erase the trail. The server says at every start which of these is in force.
3. Hash chain: each human entry stores prev_hash and entry_hash, so a rewrite by someone with full database access is detectable (SELECT * FROM verify_audit_chain() reports the first break). Machine-written entries are not chained, because retention deletes them.

The API pages it by an opaque cursor (/api/audit, next_cursor) because an offset drifts while entries arrive at the top. See 07-guardrails-security for setting up the restricted role.

chat_sessions / chat_messages

Conversations with the AI. chat_sessions.created_by owns the session (private to its creator). chat_messages stores role, content, tool_calls (jsonb), tool_call_id, name — mirroring the OpenAI-compatible schema so history can be replayed to the model verbatim.

clients / sites

The org hierarchy. clients (id, name, created_at) is a customer/organization; sites (id, client_id FK → clients, name, created_at) is a location under a client. An agent’s site_id places it in this tree; deleting a client cascades to its sites, and a deleted site nulls the agents’/enroll-tokens’ site_id.

scripts

Reusable script library: id, name, description, category, shell (bash/sh/powershell/cmd/python), body, default_args, created_at, updated_at. Run on a device via /run-script, which creates a job.

script_categories

The catalogue of category names (id, name, created_at), added by migration 007. Unique on lower(name), because “Networking” and “networking” are the same category to everyone except a byte comparison.

scripts.category keeps the name, not a foreign key: this table exists so a category can be named before any script is filed under it, not to own the relationship. A script written straight through the API can therefore carry a name nobody registered, so listing unions the registry with the categories in use and such a category is never invisible. Saving a script adopts its category into the registry, so the two do not drift.

policies / policy_assignments

A policy (id, name, description) bundles checks and tasks. policy_assignments attaches a policy to a scope: scope_type (client/site/agent) + scope_id. An agent inherits every policy whose scope covers it (its own id, its site, or its client).

checks

A monitoring check, owned either by an agent or a policy — exactly one of agent_id / policy_id is set.

Column	Type	Notes
id	uuid PK
agent_id	uuid FK → agents	nullable (direct check)
policy_id	uuid FK → policies	nullable (inherited check)
type	text	diskspace/memory/cpuload/ping/winservice/script
name	text
config	jsonb	type-specific (path / host / service / inline script)
warn_threshold, err_threshold	double	nullable
interval_sec	int	run cadence (default 300)
enabled	bool	default true
proposed_by	text	AI actor that authored it ('' = a person). Permanent provenance: it survives review
approved_by	text	who released it ('' = nobody yet)
failure_count	int	consecutive failures before the alert is raised (1–100, default 2)
recovery_count	int	consecutive successes before it clears (1–100, default 2)
hysteresis	double	band a recovering value must clear the threshold by (0–100, default 2)
cooldown_sec	int	quiet period before the same condition may raise again (default 300)
timeout_sec	int	how long a dispatched execution may stay unanswered (5–86400, default 180)
created_at	timestamptz

check_executions

One dispatch of a check to an agent (id, check_id, agent_id, status, dispatched_at, deadline_at, completed_at). Status is pending/completed/timed_out/canceled.

A partial unique index on (check_id, agent_id) where status = ‘pending’ allows only one outstanding execution per pair, so a slow or disconnected agent is never sent the same check again while the first is still owed. Passing deadline_at marks the row timed_out instead of leaving it pending forever — without this a dropped agent left a check that had been sent and would never be answered. check_results.execution_id links a result back to the dispatch that asked for it.

check_states

The per-(check, agent) running state a flap decision needs: status, consecutive_failures, consecutive_successes, last_value, last_output, last_result_at, last_dispatch_at, reconciled_at. The streaks are what let an alert be raised from a run of readings rather than from the latest one.

check_results

Latest status + history per (check, agent): id, check_id FK → checks, agent_id FK → agents, status (passing/warning/failing), value (double, nullable), output, ran_at. Indexed by (check_id, agent_id, ran_at desc).

tasks

Scheduled automation, owned by an agent or a policy (like checks).

Column	Type	Notes
id	uuid PK
agent_id / policy_id	uuid	exactly one set
name	text
script_id	uuid FK → scripts	nullable; inline shell+script is the fallback
shell, script	text	inline source when no script_id
schedule_kind	text	interval / daily / weekly
interval_sec	int	for interval
run_at_minute	int	for daily: minutes since midnight (UTC)
days_of_week	smallint	for weekly: bitmask, bit N = time.Weekday N (0 = Sunday)
run_at_minutes	int[]	for weekly: times of day, minutes since midnight (UTC)
enabled	bool	default true
proposed_by	text	AI actor that authored it ('' = a person). Permanent provenance: it survives review
approved_by	text	who released it ('' = nobody yet)
created_at	timestamptz

Each dispatched run links back via jobs.task_id.

tasks and checks both carry CHECK (proposed_by = '' OR approved_by <> '' OR NOT enabled). An AI proposal nobody has reviewed cannot be enabled, and the scheduler only ever selects WHERE enabled — so it cannot run. The rule is in the database because the AI tools and the REST handlers are separate paths into these rows; see 07-guardrails-security. policies carries the same two columns for provenance, but no constraint: a policy has no schedule of its own, and what makes it dangerous is the tasks and checks attached to it.

alerts

Beyond the original fields, an alert carries its whole lifecycle (migration 008):

• Identity and dedup — condition_key collapses repeats of the same condition into one alert, counted by occurrence_count with first_seen_at / last_seen_at, so a value oscillating on its threshold is one alert rather than one per reading.
• Evidence — observed_value, threshold_value, output.
• Scope, denormalized — agent_hostname, site_id, client_id, group_id, agent_snapshot_id. The list can be filtered and paginated by scope without a join per row, and an alert still names its device after the device is deleted.
• Attribution — acked_at/acked_by, resolved_by, resolution_kind, resolution_note, and reopened_from when a resolved condition returns.
• Concurrency — version, so two operators acting at once cannot silently overwrite each other (the API takes expected_version).
• Silencing — silenced_until, silenced_by, silence_reason.
• Delivery — last_notified_at, notification_pending, notification_kind, notification_seq.

Raised by a failing/warning check or an offline agent.

Column	Type	Notes
id	uuid PK
agent_id	uuid FK → agents	nullable
source_type	text	check / task / agent
source_id	uuid	the check/task id (nullable)
severity	text	info / warning / error
message	text
status	text	new / acked / resolved
created_at, resolved_at	timestamptz

A partial unique index (uq_alert_open on source_type, source_id, agent_id where status <> 'resolved') guarantees at most one open alert per source.

alert_events

The timeline of an alert (alert_id, event_type, actor, detail, timestamp) — raised, acknowledged, suppressed, resolved, reopened. The alert row holds current state; this holds how it got there.

alert_suppressions

A silence or maintenance window (kind = silence/maintenance, ends_at, reason, author). Lets a known-noisy condition or a planned window stop mailing without deleting the alert or disabling the check.

sessions

Browser login sessions (id, user_id FK → users, token_hash unique, expires_at, created_at). A bearer login token from POST /api/auth/login maps to a row here; logout / password-change revoke them.

app_settings

Editable runtime configuration as key/value (key PK, value, is_secret, updated_at). Secret values such as the AI API key and MeshCentral token key are encrypted (enc:v1:) before storage and never returned by the config API. TOTP secrets use the same encryption helper but live on users, not in app_settings. Production requires a stable, dedicated RMM_SETTINGS_ENCRYPTION_KEY (normally through RMM_SETTINGS_ENCRYPTION_KEY_FILE). It is not stored in PostgreSQL and must be recovered separately with the database backup.

Patch tables

patches is the fleet catalog keyed by (os, identifier). agent_patches tracks each device’s missing/approved/installing/installed/failed/ excluded state. patch_approvals stores the latest global/client/site/agent approve-or-decline decision. patch_policies stores auto-approval, exclusions, server-local maintenance windows and reboot behavior; patch_policy_assignments attaches definitions to client/site/agent scopes.

OAuth tables

oauth_clients stores Dynamic Client Registration metadata, plus status (approved or pending, per the registration policy), approved_by/approved_at and last_used_at. oauth_codes stores single-use, expiring, PKCE-bound code hashes. oauth_tokens stores access and refresh hashes, user/client/scope binding, and independent access/refresh expiry; plaintext OAuth credentials are returned only when issued.

Account mail

password_resets holds single-use reset links (token_hash unique, expires_at, used_at). email_verifications holds address confirmation links (token_hash PK, the email they confirm, expires_at, used_at); a link for an address the user no longer has confirms nothing.

notifications / notification_subscriptions

notifications is the durable alert-mail outbox: one row per message (event_type, recipient, subject, body, optional alert_id/agent_id/ user_id), status pending → processing → sent or failed, with attempts, last_error, next_attempt_at, a dedupe_key, and a delivery lease (lease_token, lease_until) so a worker that dies mid-send does not lose the message. notification_subscriptions is each user’s opt-in per event type (check_failed, check_recovered, agent_offline, job_failed). See 20-email-notifications.

device_operations

Moves and deletions of devices proposed by the AI or an MCP client. kind move (with destination site_id, optional group_id) or delete; agent_ids (1–500); status awaiting_approval → approved → completed/failed, or rejected; proposed_by and, once decided, decided_by (enforced by a check); result jsonb with per-device outcomes. Deleting the destination site or department deletes a pending move to it.

Single sign-on tables

oidc_login_states holds a sign-in in progress (state_hash, nonce, PKCE code_verifier, redirect_to, and oauth_request when it started from the MCP consent page), expiring after a few minutes. oidc_console_handoffs holds the single-use code the callback hands the console (ticket_hash, user_id, and browser_hash — the browser that started the sign-in, the only one that can exchange it). oauth_sso_grants holds the five-minute, single-use grant an SSO sign-in gives the MCP consent page, bound to client_id, redirect_uri and the PKCE code_challenge.

schema_migrations

Forward migration ledger (version bigint PK, immutable name, SHA-256 checksum, applied_at). Do not edit rows or applied migration files to force a rollback.

Relationships

clients 1───* sites 1───* agents
device_groups 1───* agents
policies 1───* policy_assignments   (scope: client | site | agent)
policies 1───* checks / tasks        (or owned directly by an agent)
agents 1───* jobs 0..1───* tasks     (jobs.task_id, for scheduled runs)
agents 1───1 inventory
agents 1───* check_results / alerts / remote_sessions
checks 1───* check_results
roles 1───* role_permissions
users 1───* sessions / user_scopes / user_recovery_codes
chat_sessions 1───* chat_messages