Data model

Source of truth: entrosity-axis.backend/db/migrations/*.sql and the queries in entrosity-axis.backend/db/queries/*.sql.

PostgreSQL 18. Conventions:

• Primary keys are uuid (UUIDv7 generated in Go so they sort by time).
• All timestamps are timestamptz; created_at/updated_at maintained by the application.
• Enumerations are text with CHECK constraints (easier to extend than Postgres enums).
• Every tenant-owned table has tenant_id uuid NOT NULL REFERENCES tenants(id) and a leading tenant_id in every index used for listing.
• Soft delete (deleted_at) only on devices and packages (history must survive); everything else hard-deletes with ON DELETE CASCADE where safe.
• Extensions: citext (emails), pg_trgm (search), pgcrypto (optional), pg_stat_statements when preloaded; river's own migration set for the job queue.
• Row-level security (0007) is enabled and forced on every tenant-owned table. The application role rmm_app sees only rows of the session's tenant (app.tenant_id), or all rows with app.bypass. Rows with tenant_id IS NULL are shared and readable by everyone. See Multi-tenancy.
• References between tenant tables are composite (tenant_id, x_id) → parent(tenant_id, id), so a row can never point into another tenant.

Identity & tenancy

tenants

column	type	notes
id	uuid PK
name	text	display name
slug	citext UNIQUE	used in URLs/emails
status	text	active / suspended
settings	jsonb	tenant-level settings owned by Axis (default time zone, update channel, retention overrides)
created_at, updated_at	timestamptz

sites

Physical/logical location inside a tenant; an AD domain typically maps to one site. timezone (IANA) is used for maintenance windows.

users

A copy of Entrosity Hub's users (same IDs), kept by the sync for attribution (audit log, jobs, script runs) and roles. Users are global rows; which tenants a user belongs to, and their role in each, is in tenant_memberships. Axis stores no credentials, sessions or sign-in counters.

column	notes
email	citext, unique among users that are not deleted
display_name	as on the Hub
status	active / disabled / deleted (deleted users stay so past actions keep their author)
is_global_admin	the Hub's platform admins: every permission in every tenant
created_at, updated_at

Row-level security: every session may read users; only the global scope (the Hub sync) may write them.

tenant_memberships

One row per user and tenant: role (tenant_admin / technician / viewer), and the user's alert e-mail preferences for that tenant (notification_prefs, alert_digest_at, and since 0014 the delivery markers offline_rollup_at, resolved_mailed_at, quiet_held_since; the Hub sync keeps them). Row-level security scopes it to the tenant.

platform_sync_state, platform_sessions_revoked, platform_step_ups_used

The ETag of the last access snapshot applied from Entrosity Hub, platform sessions that ended (their tokens are refused before they expire; kept for an hour), and the IDs (jti) of step-up tokens already used, so each confirms one action on any replica (purged once expired).

Agents, connectors, devices

enrollment_tokens

kind is agent or connector. max_uses NULL = unlimited. Tokens are shown once at creation; token_hash only afterwards. Agents enrolled with a token record enrollment_token_id for auditing.

devices

The central table. One row per managed or discovered computer.

group	columns
identity	hostname, fqdn, domain, machine_sid, machine_guid (HKLM\SOFTWARE\Microsoft\Cryptography\MachineGuid), agent_id
OS/hardware	os_name, os_version, os_build, os_arch, manufacturer, model, serial_number, cpu_model, cpu_cores, ram_bytes, last_boot_at, last_user
network summary	ip_addresses jsonb, mac_addresses jsonb (detail in device_network_adapters)
state	status (online/offline/never_connected/decommissioned), source (agent/ad/both), agent_version, last_seen_at, last_inventory_at
AD	ad_object_guid, ad_dn, ad_ou, ad_enabled, ad_last_logon_at
user data	tags text[], custom_fields jsonb, site_id

Indexes: UNIQUE(tenant_id, machine_guid) WHERE machine_guid IS NOT NULL, (tenant_id, lower(hostname)), (tenant_id, ad_object_guid), (tenant_id, status), GIN on tags, trigram GIN on hostname for search.

Since 0008:

• search_text is a generated column (hostname, FQDN, display name, last user, serial, IP addresses) with a trigram index that serves the device search.
• inventory_hashes (jsonb) holds a fingerprint per inventory section. Ingestion skips sections that have not changed.
• Sort indexes cover the device list columns and offline_since.

agents

One row per agent installation, device_id unique. Holds auth_key_hash, version, connection_state, last_heartbeat_at, config_version (bumped when server-side agent config changes so the agent re-fetches).

Inventory tables

device_inventory_snapshots keeps the raw report (payload jsonb, 30-day retention). Normalised tables (device_software, device_disks, device_network_adapters, device_services, device_updates, device_local_users) are replaced per device per report in one transaction (delete + insert, or upsert with seen_at and delete stale). device_software has UNIQUE(device_id, name, version) and a trigram index on name for the "devices that have software X" filter.

device_metrics

device_metrics(device_id uuid, ts timestamptz, cpu_pct real, mem_pct real, disk_pct jsonb, cpu_temp_c real)  -- cpu_temp_c NULL without a sensor (0016)
PARTITION BY RANGE (ts)   -- monthly partitions, created 2 months ahead by the daily metrics.partitions job
PRIMARY KEY (device_id, ts)

jobs

Generic unit of work for an agent (device_id) or connector (connector_id). payload and result are jsonb typed by type (schemas in entrosity-shared-go/proto). Indexes on (device_id, status), (connector_id, status), (deployment_target_id), (expires_at) WHERE status IN ('created','sent','acked','running').

remote_sessions

One row per remote desktop session: device_id, agent_id (the device's agent when the session was created; revoking it ends the session), job_id (the remote_desktop job), mode (control/view), require_consent, show_banner (the on-device session bar; migration 0011), status (pending → active → ended, or failed when it never started), created_by, ticket_hash (SHA-256 of the one-time viewer ticket, cleared when used or when the session ends) and ticket_expires_at, agent_node (URL of the replica holding the agent's stream), end_code (stream close code) and end_reason, created_at, started_at, ended_at. A unique partial index allows one open (pending or active) session per device. Finished sessions are purged with the job history (RMM_RETENTION_JOB_DAYS or the tenant override).

AD sync

connectors

One row per site connector installation: hostname, domain, version, capabilities, auth_key_hash, presence (status, last_seen_at), revoked_at. UNIQUE(tenant_id, lower(hostname), lower(domain)) WHERE revoked_at IS NULL — re-installing on the same machine rotates the key. Deleting a connector revokes it and deletes its sync configurations.

ad_sync_configs

One per domain sync; belongs to a connector. bind_password_enc, push_credential_enc and push_token_enc (the raw agent enrollment token handed to pushed installs, push_token_id/push_token_expires_at) are AES-GCM ciphertext (base64, key-id prefix) with additional data binding them to the config id. tls_mode is ldaps/starttls/none; computer_filter is ANDed with (objectCategory=computer); ou_include/ou_exclude hold DNs. Scheduling state: last_sync_at, last_sync_status, consecutive_failures, next_sync_at (interval × 2^failures, max 24 h). auto_push_agent pushes to newly discovered enabled computers after each sync.

ad_sync_runs

One per run (trigger manual/schedule, status pending/running/succeeded/failed, job_id), at most one active per config (partial unique index). Counters seen, created_count, updated_count, gone_count, matched_count; received_seqs and expected_total make chunk ingestion idempotent and order-independent. ad_sync_seen(run_id, object_guid) records the GUIDs a run saw (multi-replica safe "mark gone"); it is cleared when the run ends.

ad_computers

Mirror of AD computer objects. object_guid unique per tenant; device_id links to the reconciled device. gone_since is set when an object disappears from AD and cleared if it reappears; AD-only devices whose object is gone for >30 days get custom_fields.ad_missing = "true" (never auto-deleted). Push state: push_status (queued/connecting/copying/installing/success/failed), push_error_code, push_error, push_at, push_job_id.

Packages & deployments

packages

tenant_id NULL means a global package created by a global admin and visible to all tenants (read-only for them). kind drives which fields are required:

• msi/exe: object_key, sha256, size_bytes, install_args, uninstall_args
• powershell: object_key of the .ps1 (or inline script_content)
• winget: winget_id, optional winget_version, winget_source (winget/msstore)

status is draft until an uploaded file has been finalized (object stat'd, SHA-256 and size checked against what the browser declared, MSI metadata such as msi_product_code read best-effort), then ready; winget packages and inline PowerShell are ready at creation. Objects live at packages/<tenant or global>/<package id>/<sanitized file name>. Execution columns: install_args, uninstall_args, success_exit_codes (default {0,3010,1641}), requires_reboot, timeout_seconds (60–86400), run_as (system/logged_on_user), arch, min_os_build. Packages are soft-deleted (deleted_at, the object is kept because finished deployments still reference the package); deleting one used by an unfinished deployment is refused with package_in_use.

detection jsonb examples:

{"type":"msi_product_code","product_code":"{GUID}"}
{"type":"registry","key":"HKLM\\SOFTWARE\\Vendor\\App","value":"Version","op":">=","expected":"2.0"}
{"type":"file","path":"C:\\Program Files\\App\\app.exe","min_version":"2.0.0"}
{"type":"winget","id":"Vendor.App"}

deployments

action is install or uninstall. target_kind: all (whole tenant), devices (explicit list stored in deployment_targets), filter (device filter in target_filter jsonb, including software_present/software_absent {name, version_op?, version?} conditions; resolved at start and again by "add new devices"). Devices without an agent or decommissioned are excluded and counted in excluded_count.

Scheduling: schedule_kind now / at (scheduled_at) / window (daily window_start–window_end as HH:MM, end < start wraps midnight, evaluated in each device's site time zone or the tenant's settings.default_timezone). max_concurrency (1–1000), retry_count (0–10), retry_backoff_seconds, reboot_policy, expires_at. status: scheduled → running ⇄ paused → completed | failed | cancelled (failed when targets failed or timed out and none succeeded or was skipped, completed otherwise). Counter columns count_pending, count_queued, count_running (downloading + installing), count_success, count_failed, count_skipped, count_cancelled, count_timeout are recomputed from the targets after every change for cheap list rendering.

deployment_targets

One row per (deployment, device), UNIQUE(deployment_id, device_id); two deployments of the same package to one device are allowed. Columns: attempt, next_attempt_at (a failed target waiting for its retry is pending with a future next_attempt_at), job_id (current job; jobs.deployment_target_id points back), exit_code, error_code, last_error, output_tail, started_at, finished_at. Status transitions:

pending → queued → downloading → installing → success
                                            → failed (retry ≤ retry_count → pending)
pending → skipped (detection says already installed)
pending/queued → cancelled | timeout
failed | timeout → pending (manual retry)

winget_index

Snapshot of the winget community source (id, name, publisher, versions[]), refreshed about daily by the winget.refresh job from source.msix (RMM_WINGET_SOURCE_URL) and searched with trigram indexes by the package wizard.

SQL functions

• deployment_in_window(ts, tz, window_start, window_end) – whether ts in time zone tz lies in the daily window; an empty window always matches, an unknown zone never does.
• version_cmp(a, b) – dotted version comparison (numeric per part where both parts are numbers, else case-insensitive text; leading v ignored), returning -1/0/1; used by the software target conditions.

Scripts, alerts, releases, audit

scripts, script_versions

tenant_id NULL is the global library (managed by global admins, runnable by every tenant, read-only there). language powershell (5.1) / pwsh / cmd; params_schema is a JSON-schema subset (properties of type string/integer/number/boolean with title, default, enum, minimum/maximum, maxLength; required; x-order) rendered as a form in the portal and validated server-side before a run. Changing content or params_schema bumps current_version and appends to script_versions (script_id, version, content, params_schema, created_by, created_at); deleting soft-deletes (deleted_at) and keeps runs.

script_runs

One per device per run request: script_id + script_version + script_name (kept if the script is deleted), job_id (unique), params (validated, typed), run_as, status queued → running → succeeded|failed|timeout|cancelled, exit_code, error_code/error. output holds the streamed text (live chunks are appended in order at their byte offset); when the agent uploads the full output (PUT /jobs/{id}/output, ≤ 10 MiB) it goes to object storage under output_object_key and output keeps the tail; output_bytes, output_truncated.

alert_rules, alert_rule_overrides, alerts

• alert_rules.tenant_id NULL are global rules applying to every tenant; a tenant can switch a global rule off for itself with alert_rule_overrides (rule_id, tenant_id, enabled). Types and condition keys: device_offline {minutes}, disk_free_pct {below, drive?}, agent_outdated {min_version?} (default: newest stable agent release), cpu_pct/mem_pct {above, minutes} (average over the window), cpu_temp {above_c, minutes} (°C, average of the samples that have a temperature; 0016 seeds the global rule CPU temperature high, 90 °C for 10 min), deployment_failed {threshold_pct} (default 20: failed and timed-out share of the targets of a deployment created in the last week), adsync_failed {}, connector_offline {minutes}. Migration 0006 seeds five global defaults (offline 60 min, disk below 10 %, agent outdated, connector offline 15 min critical, AD sync failing).
• alerts are per rule and subject (device, connector, deployment, adsync_config), with a partial unique index so a subject has at most one active (open or acknowledged) alert per rule. The engine opens, refreshes the message of, and resolves them by itself; users may acknowledge (it still auto-resolves) or resolve.
• tenant_memberships.notification_prefs {alert_email: off|immediate|digest, min_severity, digest_interval: 15m|hourly|daily, digest_hour, timezone, offline_batch_minutes: 5|15|30|60, muted_types[], notify_resolved, quiet_hours{enabled, start, end, critical_bypass}} (defaults immediate, warning, 15m, 8, tenant zone, 15, none, false, off/22:00–07:00/bypass) and alert_digest_at (last digest sent).

agent_releases

(component agent|connector, version) unique; channel stable/beta; status draft → published; object_key (releases/<component>/<version>/rmm-<component>.msi), size_bytes, sha256 (verified against the uploaded object at publish), signature (Ed25519 over the release manifest, made at publish), rollout_pct, notes. agents and connectors gained update_version / update_requested_at (the last offer, for the one-hour retry cool-down).

device_logons

One row per Windows sign-in reported by the agent: device_id, username, session_id, session_type (console/remote), client, logon_at, logoff_at (NULL while signed in), logoff_estimated. Unique (device_id, session_id, logon_at); RLS like the other tenant tables. Rows are deleted 7 days after logoff_at (fixed; retention category device_logons), and with the device.

audit_log

Append-only (no UPDATE/DELETE grants for the app role); before/after hold redacted snapshots (secrets stripped). Release automation writes entries without a user (actor_user_id NULL).

Migrations

#	file	contents
0001	init	extensions, tenants, sites, users, refresh_tokens, password_resets, invitations, audit_log
0002	agents_devices	enrollment_tokens, devices, agents, inventory tables, jobs
0003	metrics	device_metrics partitioned + helper function to create partitions
0004	adsync	connectors, ad_sync_configs, ad_sync_runs, ad_computers
0005	packages_deployments	packages, deployments, deployment_targets
0006	scripts_alerts	scripts, script_versions, script_runs, alert_rules (+ global defaults), alert_rule_overrides, alerts, agent_releases; users.notification_prefs; agents/connectors update offer columns
0007	rls	role rmm_app, rmm_tenant_ok(), policies on every table, composite tenant foreign keys, append-only audit log with purge_audit_log(), SECURITY DEFINER partition helpers
0008	perf_indexes	devices.search_text (trigram) and inventory_hashes; sort, status and job-reference indexes; rmm_devices_with_software(); pg_stat_statements when available
0009	login_attempts	fixed-window sign-in counters shared by all replicas (hashed keys, purged after a day)
0010	remote_sessions	remote desktop sessions (RLS, composite tenant keys, one open session per device)
0011	remote_sessions_banner	remote_sessions.show_banner
0012	platform_mirror	tenant_memberships (a role per user and tenant, alert preferences per tenant; backfilled from users.tenant_id/role), users.is_global_admin, platform_sync_state, platform_sessions_revoked, platform_step_ups_used; users readable as global rows; e-mail unique among non-deleted users; local-sign-in trigger
0013	drop_local_auth	drops local sign-in: tables refresh_tokens, password_resets, invitations, login_attempts; users columns tenant_id, role, password_hash, totp_*, must_change_password, failed_logins, locked_until, last_login_at, notification_prefs, alert_digest_at; the 0012 trigger that kept users.tenant_id/role and tenant_memberships in step. users.status becomes active/disabled/deleted (invited users become deleted); users policies: read for all, writes in the global scope only. The down migration recreates the tables and columns empty: credentials cannot be restored, so rolling back past 0013 means restoring a backup taken before it.
0014	notification_controls	tenant_memberships markers for grouped offline e-mails, resolution e-mails and quiet hours (offline_rollup_at, resolved_mailed_at, quiet_held_since); index on resolved alerts
0015	device_logons	device sign-ins (RLS, deleted 7 days after sign-out)
0016	cpu_temperature	device_metrics.cpu_temp_c; alert rule type cpu_temp and the global rule CPU temperature high
0017	screen_wall	Role teacher in tenant_memberships; remote_sessions.profile ('' or wall, view only), room_firewall_id, room

River's own migrations are applied by migrate up before these. New migrations are created with make migration name=… (Make targets); remember to add them to this table.

Entrosity Hub database

The Hub keeps its data in its own database, platform, on the same PostgreSQL server (owner rmm, the service connects as platform_app, no row-level security: every query is scoped by the Hub's own access rules). Migrations are in entrosity-hub.backend/db/migrations (platform-server migrate); river's job tables are created first, as in Axis.

Table	Holds
users	One Entrosity account per person: e-mail (unique among non-deleted users), argon2id password hash, display name, status (active, disabled, deleted), is_platform_admin, two-factor secret (encrypted with PLATFORM_MASTER_KEY), recovery code hashes, lockout counters. IDs are kept when imported from Axis (import-rmm, which needs an Axis database from before migration 0013).
organizations	Customers: name, unique slug (^[a-z0-9-]{3,40}$), status (active, suspended), settings (require_totp). Axis tenants have the same IDs.
products	The products the Hub signs users in to: id (rmm), name, base path (/axis), the roles it knows, enabled. Seeded with Axis; the launcher and the redirect after sign-in follow the base path.
organization_products	Which products an organization has.
memberships	A user in an organization, with the organization role admin or member.
product_roles	A member's role in one product of the organization (for Axis tenant_admin, technician, viewer, teacher); needs the membership and the organization's product, and a trigger checks the role against the product's list.
refresh_tokens	Session cookies (hashed), grouped in families that rotate; a revoked family with no successor is a session that ended (the snapshot lists these for products).
password_resets, email_changes, invitations	One-time links (hashed tokens) with expiry; an e-mail change holds the new address until its link is opened; an invitation grants an organization role, product roles and/or platform admin.
login_attempts	Sign-in and rate-limit counters shared by the replicas.
audit_log	Every change, append-only for platform_app (a trigger refuses updates and deletes).

Hub migrations:

#	file	contents
0001	init	the tables above; role platform_app; products seeded with Axis (rmm, base path /manage)
0002	axis_path	Axis's base path becomes /axis
0003	edge_product	registers Entrosity Edge (edge, disabled)
0004	edge_enable	enables Edge
0005	product_beta	products.beta: only platform admins see a product in beta
0006	sphere_product	registers Entrosity Sphere (sphere, disabled, beta)
0007	sphere_enable	enables Sphere
0008	matrix_product	registers Entrosity Matrix (matrix, disabled, beta)
0009	matrix_enable	enables Matrix
0010	email_changes	pending e-mail changes (new address, hashed token, expiry)
0011	matrix_teacher	Matrix's role operator becomes teacher (members and open invitations)
0012	axis_teacher	Axis gets the role teacher (screen wall only)