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, api_key_audit, ingest_failures, device_ingest_state, and device_inventory_state.

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 Docker Compose stack loads schema/init.sql from selfhosted-docker-reportmate on first boot, and terraform-azurerm-reportmate ships its schema files under schemas/ to apply with psql.

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 module, 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. No module table declares a foreign key to devices, so nothing cascades: deleting a device clears its module rows explicitly, and a module row whose device row has gone is an orphan that GET /api/v1/admin/orphans reports. 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. Every stored install item also carries reportmateStatus — error, warning, pending, installed, or removed — stamped at ingest by the one classifier in install_status.py, and the counters are computed from the same classification. Ingest recomputes both whenever an installs payload changes; the clear-errors admin endpoint recomputes the counters in the same statement it rewrites the JSONB; and POST /api/v1/admin/installs/reclassify re-applies the current classifier to every stored row.

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	The module the event belongs to, e.g. installs or os_update. A managed-software run's events are stored under its module and superseded by the next run's; a new os_update event replaces the previous one.
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), (event_type, timestamp DESC), and (device_id, module_id) where module_id is set. The (event_type, timestamp DESC) index turns the dashboard's per-type recent-events fetch into five bounded index scans; the (device_id, module_id) index serves the per-device delete that supersedes a run's events. A run reports up to three events (success, warning, error) under one module_id, so that index is not unique. 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.

Like the module tables it has no foreign key, so 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).

device_ingest_state

Added in migration 0010. One row per device: device_id (serial number) and last_accepted_at, the server time of the last check-in the API accepted. devices.last_seen is the client's collection timestamp, and a retry carries the same timestamp as the upload that dropped, so it cannot show that a later attempt landed. The retried outcome on GET /api/v1/events/failures reads this table instead.

device_inventory_state

Added in migration 0011. What an asset inventory system says each device is doing, written in bulk through PUT /api/v1/inventory-state.

Column	Notes
serial_number	Primary key, upper-cased so it joins however a client reported the serial.
state	checked_out, storage, or decommissioning (check constraint).
storage_location, lease_number, asset_tag	Optional.
updated_at

Indexed on (state). Read paths treat it as an overlay: if it cannot be read, they return no states rather than failing.

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 is committed with the device upsert, the first commit of the request, so only a submission that fails before that point rolls its key back. Rows older than 7 days are purged.

Migration history

Revision	What it did
0001	Baseline. Creates the app-owned tables and their index set. 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.
0009	Replaces the partial unique index on events(device_id, module_id) with a plain one, so a run can store more than one event per module and the next run can supersede them.
0010	device_ingest_state.
0011	device_inventory_state.

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