Data Model

Data Model

One PostgreSQL database. Collected state lives in JSONB, one row per device per module, so a client can change what it collects without a migration. Only a small set of tables have a fixed relational shape.

Ownership

The schema has two owners, and the split matters when you read the migrations.

Alembic (this repo) creates and owns usage_history, usage_history_archive, idempotency_keys, app_settings, api_keys, and api_key_audit, plus ingest_failures.

The deployment's own schema creates devices and the collection-module tables. The API's ingestion path inserts into the module tables but does not create them, and Alembic's index statements on them are wrapped in a PL/pgSQL block that swallows only undefined_table — so a fresh database migrates cleanly before those tables exist. If you provision by hand, create devices and the module tables yourself; the Terraform and Docker Compose repos do it for you.

Identity

serial_number is the device identity throughout. devices.id holds the same value, and every module table's device_id column holds the serial number, not a surrogate key. The hardware UUID is carried separately in devices.device_id and is never used as a join key.

devices

Read by the API, created by the deployment schema. The columns the code touches:

Column	Notes
id	Serial number.
device_id	Hardware/OS UUID reported by the agent.
serial_number	Serial number; the join key for module tables.
name	Best-known display name.
status	Set to online on first registration.
platform, os, os_name, os_version	Updated from metadata and the system module.
client_version	Agent version from the last check-in.
model, manufacturer	Read by the device detail query.
last_seen	The check-in watermark everything else keys on.
created_at, updated_at
archived, archived_at	Soft delete. Archived devices keep all data and are hidden from default views.

Collection-module tables

system, hardware, network, installs, security, applications, inventory, management, peripherals, identity — the ten the ingest path writes. profiles, displays, and printers also exist and are read by /api/v1/profiles and the diagnostics endpoint, but ingest does not write them: displays and printers arrive inside the peripherals and hardware modules, and profile data arrives inside management and security.

Every module table has the same shape:

Column	Type	Notes
id	serial
device_id	varchar(255)	Serial number. Unique per table.
data	jsonb	The module payload, stored verbatim.
collected_at	timestamptz	The client's collection timestamp.
created_at, updated_at	timestamptz	Server-side.

One row per device per module, replaced on each check-in. Migration 0001 creates a (device_id) index on each of them, and a (device_id, updated_at DESC) index on all but peripherals and identity.

installs additionally carries four precomputed counters — cimian_errors, cimian_warnings, munki_errors, munki_warnings — added in migration 0003. The dashboard aggregates these columns rather than evaluating the JSONB per request. Ingest recomputes them whenever an installs payload changes, and the clear-errors admin endpoint recomputes them in the same statement it rewrites the JSONB, so the two cannot disagree.

Migration 0008 dropped the twelve GIN indexes over the module data columns. Nothing in the API issued a containment query against them, and because every check-in rewrites its module rows, the GIN entries were where the database's growth lived.

events

Created by the deployment schema, written by ingest.

Column	Notes
id
device_id	Serial number.
event_type	success, warning, error, info, or system.
module_id	Set only for module-scoped events. os_update upserts on (device_id, module_id) so a device keeps one, not a history.
message
details	jsonb. The event's own details only; module payloads live in the module tables.
timestamp, created_at

Indexed on (timestamp DESC), (device_id), and (event_type, timestamp DESC). The composite index turns the dashboard's per-type recent-events fetch into five bounded index scans. Rows older than 30 days are purged opportunistically in batches of 50 000.

usage_history

Owned by Alembic. Daily application usage per device.

Column	Type	Notes
id	bigserial
device_id	text	Serial number. No foreign key.
date	date
app_name	text	Raw name as reported. Canonicalization happens at query time.
publisher	text
launches	integer	Accumulated.
total_seconds	double precision	Summed process lifetime. Accumulated with no ceiling — multi-process applications legitimately exceed 24h of summed process time in a day.
active_seconds	double precision	Human attention. Accumulated, held at 86 400 per day.
foreground_seconds	double precision	Same ceiling.
users	jsonb	Distinct usernames, merged on conflict.
updated_at	timestamptz

Unique on (device_id, date, app_name). Indexed on (device_id, date DESC), (app_name, date DESC), and (date).

This is the only table a device cannot re-report. Every module row is a current-state snapshot that self-heals on the next check-in; usage rows are accumulated deltas and cannot be recomputed from anything the server holds. That is why the export, integrity, and archive endpoints exist.

Because there is no foreign key, DELETE /api/v1/device/{serial} removes a device's usage rows explicitly.

usage_history_archive

Added in migration 0004. Receives a verbatim copy of every row the baseline reset removes, plus archive_id, archived_at, and reason. It keeps the original id so a copy is traceable, but has its own key because the same row can be archived more than once if a reset is re-run over an overlapping range.

It is deliberately not a partition or a foreign-key child: archived rows must survive whatever happens to the live table and must never be picked up by a reporting query. The migration's downgrade drops only the indexes — removing the table is a manual act.

ingest_failures

Added in migration 0002. Every rejected or repaired check-in.

Column	Notes
id, occurred_at
failure_type	auth, validation, throttle, or server_error.
reason	The reason code, e.g. sentinel_serial.
detail	Human-readable, truncated to 2000 characters.
status_code	The HTTP status the caller received. Rows below 400 are accepted check-ins kept for visibility.
endpoint, client_ip, user_agent
serial_number, device_uuid, device_name, platform, client_version	Device identity from the body, or from the mirrored request headers when the body could not be parsed.

Deliberately standalone with no foreign key to devices — the whole point is recording machines that are not yet registered. Indexed on (occurred_at DESC), (serial_number), and (reason).

api_keys and api_key_audit

api_keys column	Notes
id	The public 12-hex-character key id.
client_id	Human label.
key_hash	SHA-256 of the secret. The secret itself is never stored.
scopes	jsonb array of read / ingest / admin.
active	Revocation is a soft delete.
created_at, created_by, last_used	last_used is stamped best-effort on each successful authentication.

api_key_audit appends (key_id, action, actor, detail jsonb, created_at) on create and revoke. Audit writes are best-effort and never block the operation.

app_settings

Single-document org configuration, unique on (scope, principal). In practice one row: scope org, empty principal. Holds value (jsonb), schema_version, updated_at, updated_by. The document is validated loosely — unknown keys are preserved so it can evolve — with the security rules' severities spot-checked because the UI colours on them.

idempotency_keys

(key, device_id, first_seen). An ingest POST carrying Idempotency-Key inserts here first; losing the conflict means the submission is a replay and is acknowledged without reprocessing. The key row commits with the submission, so a failed submission rolls its key back and the retry processes normally. Rows older than 7 days are purged.

Migration history

Revision	What it did
0001	Baseline. Creates the app-owned tables and the index set that startup DDL used to create ad hoc. Idempotent; no downgrade.
0002	ingest_failures.
0003	The four installs counter columns, backfilled with the rules the dashboard used at read time, plus the (event_type, timestamp DESC) index.
0004	usage_history_archive.
0005	Recomputes the Munki counters. 0003 counted a Munki warning only when an item's status contained "warning", which those statuses never do — the signal is on lastWarning / lastError / currentStatus, or on the run's warningItems / errorItems.
0006	A standalone logs table for the management-tool log survey.
0007	Drops it again — the survey is a section of the management module (management.logs), so the table was never written.
0008	Drops the twelve unused GIN indexes over the module data columns.

Migrations run automatically from the application lifespan on startup. Failure is logged but not fatal; the readiness probe decides whether the database is usable.