Database schema

Generated from the migrations in crates/sp-store/migrations by the schema_doc test of sp-store; do not edit. Schema strataplane.

The model has two halves: the spec (what should be: immutable, content-addressed revisions edited through change sets, docs/spec-format.md) and state (what is: hosts, agents, artifacts, rollouts, secrets, audit), written only by the server. Targets PostgreSQL >= 15, no extensions. Embedded and external Postgres use the same schema.

Conventions:

• ids: uuid (v7, generated by the application) unless noted.
• tenancy (docs/architecture.md §12): every table with a tenant_id column is tenant data. References between tenant tables are composite foreign keys over (tenant_id, …), so a row can only point into its own tenant; uuid ids stay globally unique, and each such table has UNIQUE (tenant_id, id) as the target. Tables without tenant_id are installation-wide.
• row-level security: every table with a tenant_id column (except platform_audit_event) has the policy tenant_isolation, enabled and forced, so it is visible only in its tenant's scope: each transaction sets strataplane.tenant (SET LOCAL). Platform scope (strataplane.scope = 'platform') is set only by platform code paths: credential lookup by hash before the tenant is known, the tenants a user can choose at login, controller shard scheduling, tenant administration and deletion, migrations. FORCE applies the policy to the owning role too, so no second database role is needed. RLS backs up the tenant filter of every query; it does not defend against a compromised server.
• spec revisions: bigint, monotonic per tenant (r1, r2, ...).
• hashes: bytea (SHA-256, 32 bytes).
• timestamps: timestamptz.
• vocabularies: an enum type for lifecycle states, ordered values and values shared by several columns; a text CHECK otherwise.
• large binaries (journey evidence, wasm modules, validator binaries) live in object storage under keys derived from the row (docs/operations.md §9); the row holds size and hash.

Tables: tenant, tenant_counter, installation, controller_shard, app_user, tenant_member, billing_membership, team, team_member, service_account, role_binding, api_token, web_session, host, host_label, network_zone, spec_blob, spec_revision, spec_object, change_set, change_set_file, change_set_impact, change_set_review, enrollment_token, trust_anchor, agent_instance, class_default, class_served, agent_credential, agent_status_event, agent_log_entry, agent_component, agent_action, artifact, artifact_validation, release, rollout, rollout_stage, rollout_assignment, pin, secret_key, tenant_key, sealed_value, secret_version, secret_delivery, catalog_bundle, plugin_schema, llm_provider, chat, chat_row, audit_event, platform_audit_event, audit_sink, operation, idempotency_key, blob_delete, journey_receipt, journey_run, journey_state, journey_evidence, explore_session, explore_request.

1. Types

Type tenant_state

Enum: active, suspended, deleting.

active: normal operation. suspended: no logins and no API tokens; agents keep receiving their current desired artifacts and heartbeats are accepted, but controllers do nothing for the tenant. deleting: every request is rejected and the deletion job removes rows, objects and data keys; the tenant row goes last.

Type role_name

Enum: viewer, author, approver, operator, secret-admin, admin.

Type host_kind

Enum: server, client, pod.

Type label_source

Enum: agent, zone, enrollment, placement, manual.

Label sources in ascending precedence (a higher source wins per label key): agent facts, network zone, enrollment token, placement, manual. Placement labels are the picks of the Placement objects (docs/spec-format.md §3.9), each key belonging to one Placement; they are the picks' only record.

Type agent_kind

Enum: telegraf, elastic-agent, strataplane-agent, vector.

Type change_set_state

Enum: draft, in_review, merged, closed.

draft -> in_review -> merged; any state before merged can be closed. Validation runs as an operation and records the digest it validated: it reads the overlay and the digest together under the change set's row lock, which every edit takes, and stores its result only while the change set still has that digest. Submit requires a successful validation of the current digest. An edit or a rebase changes the digest and returns the change set to draft.

Approved is not a state. A change set in review is approved when its requirements are met by approvals at the current digest and no reviewer's latest review at that digest is a reject; a reviewer's latest review at a digest replaces their earlier ones.

Merge requires the digest the caller saw, approval, and a base that is the latest revision. A moved base needs a rebase: a three-way merge per file that lists the paths changed on both sides as conflicts, followed by validation and review of the new digest. Merge recomputes impact and requirements against the current inventory and fails, leaving the change set in review, if the approvals no longer satisfy them.

Type agent_health

Enum: pending, healthy, degraded, failed, not_reporting.

Health and sync are separate: an agent can be healthy and behind, or in sync and failing. pending = enrolled, never reported; not_reporting = nothing received for 3 expected report intervals (Telegraf and Vector heartbeat.interval, Elastic Agent pollTimeout, strataplane-agent checkin interval); on hosts of kind client it is shown but not alerted on. updating = desired not yet applied, and not yet out_of_sync (never served, or served within the module's reload grace).

Type agent_sync

Enum: in_sync, updating, out_of_sync, render_error.

Type assurance

Enum: low, high.

high = bound client cert on the request

Type rollout_state

Enum: running, paused, halted, completed, aborted.

A rollout starts when it is created (from_revision = the release pointer). The stages are rollout_stage rows, copied from the RolloutPolicy at creation together with its guardrails. running: stages assign agents and bake. paused: an operator stopped it. halted: a guardrail breached after a stage's bake time. Actions: pause; resume (from paused or halted, continuing with the next stage, so resuming a halt accepts the breach); advance (end the current stage's bake time now and evaluate its guardrails); abort. completed moves the release pointer to to_revision. aborted ends all assignments: the rollout's agents fall back to the release pointer, which is still from_revision.

Assignment: a stage, when it starts, takes every agent of the rollout's kind that its selector matches, that no earlier stage took, that no pin holds, and whose artifact at to_revision differs from the one at from_revision. Assignments stay when labels change later. The last stage matches every host (docs/spec-format.md §3.7), so every impacted agent is staged; agents enrolled after it started follow the release pointer. An agent pinned after its assignment follows the pin and leaves its stage's guardrails; a forgotten agent leaves the stage.

Type rollout_stage_state

Enum: pending, running, passed, breached, skipped.

pending: not started. running: its agents apply to_revision during the bake time. passed / breached: its guardrails held / failed after the bake time. skipped: it took no agents.

Type operation_state

Enum: queued, running, succeeded, failed.

Type journey_result

Enum: passed, failed, error.

2. Tables

tenant

Column	Type	Null	Default	Description
id	uuid
slug	text			short name for people and the CLI
display_name	text
state	tenant_state		'active'::tenant_state
shard	integer			controller shard, fixed at creation (docs/operations.md §3)
limits	jsonb		'{}'::jsonb	quotas, docs/architecture.md §12
external_id	text	yes		service mode: the billing service's workspace id
entitlement	jsonb	yes		service mode: the newest entitlement billing sent (docs/architecture.md §12)
entitlement_version	bigint	yes		its version; older pushes are ignored
created_at	timestamp with time zone		now()
updated_at	timestamp with time zone		now()
state_since	timestamp with time zone		now()

Constraints:

• PRIMARY KEY (id)
• UNIQUE (external_id)
• UNIQUE (slug)
• CHECK ((slug ~ '^[a-z0-9]([a-z0-9-]{0,30}[a-z0-9])?$'::text))

tenant_counter

Per-tenant counters: 'revision', 'change_set', 'rollout', 'audit'. Taking a number locks the row until commit, so numbers of one tenant commit in order and never fork; tenants never wait for each other.

Column	Type	Null	Default	Description
tenant_id	uuid
name	text
value	bigint		0

Constraints:

• PRIMARY KEY (tenant_id, name)
• FOREIGN KEY (tenant_id) REFERENCES tenant(id)
• CHECK ((name = ANY (ARRAY['revision'::text, 'change_set'::text, 'rollout'::text, 'audit'::text])))

Row-level security (forced): tenant_isolation.

installation

The installation's own settings, one row, written by strataplane init or the first serve. shards: the controller shard count, fixed here (docs/operations.md §3). backup_at: when a replica last claimed a scheduled backup, so that one replica runs each (docs/operations.md §2).

Column	Type	Null	Default	Description
id	boolean		true
shards	integer
backup_at	timestamp with time zone	yes
created_at	timestamp with time zone		now()

Constraints:

• PRIMARY KEY (id)
• CHECK (((shards >= 1) AND (shards <= 1024)))
• CHECK (id)

controller_shard

One row per controller shard that ever ran (docs/operations.md §3). The replica holding the shard's lock sets alive_at about once a minute, on the lock connection. Taking the lock sets alive_at and, when alive_at is missing or older than 10 minutes (no replica ran the shard meanwhile), resumed_at = now(); a lock passed on within that time (rolling restart, rebalancing) keeps resumed_at. Agent silence counts from no earlier than the resumed_at of the tenant's shard (agent_instance), so an outage of every replica does not make every agent look silent.

Column	Type	Null	Default	Description
shard	integer
alive_at	timestamp with time zone
resumed_at	timestamp with time zone

Constraints:

• PRIMARY KEY (shard)
• CHECK ((shard >= 0))

app_user

Users are installation-wide: one identity can be a member of several tenants. Platform admins manage tenants and the catalog; they hold no role inside a tenant unless a role binding of that tenant gives them one. A service account is a user of kind 'service' (issuer 'service', subject its id) and a member of exactly one tenant, so every actor column (author, created_by, reviewer, ...) references app_user. Service accounts cannot log in.

Column	Type	Null	Default	Description
id	uuid
kind	text		'human'::text
issuer	text			'local', 'service' or OIDC issuer URL
subject	text			OIDC sub, local username or service account id
email	text	yes
display_name	text
password_hash	text	yes		argon2id, local users only
platform_admin	boolean		false
created_at	timestamp with time zone		now()
last_login_at	timestamp with time zone	yes
disabled_at	timestamp with time zone	yes

Constraints:

• PRIMARY KEY (id)
• UNIQUE (issuer, subject)
• CHECK (((kind = 'human'::text) OR ((password_hash IS NULL) AND (NOT platform_admin))))
• CHECK (((kind = 'service'::text) = (issuer = 'service'::text)))
• CHECK ((kind = ANY (ARRAY['human'::text, 'service'::text])))

tenant_member

oidc: created at the first OIDC login (self-hosted installations); local: added by a tenant admin; service: the service account's own membership; billing: pushed by the billing service (service mode, billing_membership) (docs/architecture.md §12). A tenant admin disables the membership, never the user: disabled_at blocks logins and tokens of the user in this tenant only. Every request re-reads the membership, so disabling it also ends the access of open sessions.

Column	Type	Null	Default	Description
tenant_id	uuid
user_id	uuid
source	text
created_at	timestamp with time zone		now()
disabled_at	timestamp with time zone	yes

Constraints:

• PRIMARY KEY (tenant_id, user_id)
• FOREIGN KEY (tenant_id) REFERENCES tenant(id)
• FOREIGN KEY (user_id) REFERENCES app_user(id) ON DELETE CASCADE
• CHECK ((source = ANY (ARRAY['local'::text, 'oidc'::text, 'service'::text, 'billing'::text])))

Row-level security (forced): tenant_isolation.

billing_membership

Service mode: the state of one membership the billing service pushed (docs/architecture.md §12). id is billing's membership id; version the newest applied, so older pushes change nothing. While status is active the user is a tenant_member (source billing) holding binding_id, the role binding the billing role maps to (admin: admin, member: viewer; NULL when an identical binding already existed). revoked removes the tenant_member row, and with it the user's bindings, tokens and team memberships in this tenant, and ends the user's sessions bound to it.

Column	Type	Null	Default	Description
id	text
tenant_id	uuid
user_id	uuid
role	text
status	text
version	bigint
binding_id	uuid	yes
updated_at	timestamp with time zone		now()

Constraints:

• PRIMARY KEY (id)
• UNIQUE (tenant_id, id)
• FOREIGN KEY (tenant_id) REFERENCES tenant(id)
• FOREIGN KEY (user_id) REFERENCES app_user(id)
• CHECK ((role = ANY (ARRAY['admin'::text, 'member'::text])))
• CHECK ((status = ANY (ARRAY['active'::text, 'revoked'::text])))

Row-level security (forced): tenant_isolation.

team

Column	Type	Null	Default	Description
id	uuid
tenant_id	uuid
slug	text
display_name	text
source	text
external_id	text	yes		OIDC group claim value
created_at	timestamp with time zone		now()

Constraints:

• PRIMARY KEY (id)
• UNIQUE (tenant_id, id)
• UNIQUE (tenant_id, slug)
• FOREIGN KEY (tenant_id) REFERENCES tenant(id)
• CHECK ((slug ~ '^[a-z0-9][a-z0-9-]{0,62}$'::text))
• CHECK ((source = ANY (ARRAY['local'::text, 'oidc'::text])))

Row-level security (forced): tenant_isolation.

team_member

Members of oidc teams are synced at login from the groups claim (self-hosted installations); local teams are managed by hand. Service accounts are not team members; they are bound directly.

Column	Type	Null	Default	Description
tenant_id	uuid
team_id	uuid
user_id	uuid
synced_at	timestamp with time zone		now()

Constraints:

• PRIMARY KEY (team_id, user_id)
• FOREIGN KEY (tenant_id, team_id) REFERENCES team(tenant_id, id) ON DELETE CASCADE
• FOREIGN KEY (tenant_id, user_id) REFERENCES tenant_member(tenant_id, user_id) ON DELETE CASCADE
• FOREIGN KEY (user_id) REFERENCES app_user(id) ON DELETE CASCADE

Row-level security (forced): tenant_isolation.

service_account

The tenant-side details of a service account; id is its app_user id. Deleting the service account deletes its user, membership, tokens and role bindings. Once it has acted (authored a change set, reviewed, created a rollout, pin or operation), rows still reference its user and the deletion is refused (409 service_account.in_use): revoke its tokens and remove its role bindings instead.

Column	Type	Null	Default	Description
id	uuid
tenant_id	uuid
name	text
description	text	yes
owner_team_id	uuid	yes
created_by	uuid	yes
created_at	timestamp with time zone		now()

Constraints:

• PRIMARY KEY (id)
• UNIQUE (tenant_id, name)
• FOREIGN KEY (created_by) REFERENCES app_user(id)
• FOREIGN KEY (id) REFERENCES app_user(id) ON DELETE CASCADE
• FOREIGN KEY (tenant_id) REFERENCES tenant(id)
• FOREIGN KEY (tenant_id, id) REFERENCES tenant_member(tenant_id, user_id) ON DELETE CASCADE
• FOREIGN KEY (tenant_id, owner_team_id) REFERENCES team(tenant_id, id) ON DELETE SET NULL (owner_team_id)

Row-level security (forced): tenant_isolation.

role_binding

Subject: a user (human or service account) or a team. Scope: the whole tenant, the objects owned by a team, or the hosts a selector matches (docs/authorization.md §3.3). admin is bound to the whole tenant only; tenant admins are its subjects. A team that scopes a binding cannot be deleted.

Column	Type	Null	Default	Description
id	uuid
tenant_id	uuid
user_id	uuid	yes
team_id	uuid	yes
role	role_name
scope_kind	text
scope_team_id	uuid	yes		scope_kind owner
scope_selector	jsonb	yes		scope_kind selector
created_by	uuid	yes
created_at	timestamp with time zone		now()

Constraints:

• PRIMARY KEY (id)
• UNIQUE NULLS NOT DISTINCT (tenant_id, user_id, team_id, role, scope_kind, scope_team_id, scope_selector)
• FOREIGN KEY (created_by) REFERENCES app_user(id)
• FOREIGN KEY (tenant_id) REFERENCES tenant(id)
• FOREIGN KEY (tenant_id, scope_team_id) REFERENCES team(tenant_id, id)
• FOREIGN KEY (tenant_id, team_id) REFERENCES team(tenant_id, id) ON DELETE CASCADE
• FOREIGN KEY (tenant_id, user_id) REFERENCES tenant_member(tenant_id, user_id) ON DELETE CASCADE
• CHECK ((((scope_kind = 'owner'::text) = (scope_team_id IS NOT NULL)) AND ((scope_kind = 'selector'::text) = (scope_selector IS NOT NULL))))
• CHECK (((role <> 'admin'::role_name) OR (scope_kind = 'tenant'::text)))
• CHECK ((num_nonnulls(user_id, team_id) = 1))
• CHECK ((scope_kind = ANY (ARRAY['tenant'::text, 'owner'::text, 'selector'::text])))

Row-level security (forced): tenant_isolation.

api_token

Tokens of a user or service account. Token = 'spk_' + 32 random bytes (base32). tenant_id NULL: platform token of a platform admin, valid for the platform API only. token_hash is unique across tenants: it is how a request finds its tenant's token.

Column	Type	Null	Default	Description
id	uuid
tenant_id	uuid	yes
name	text
token_prefix	text			first 8 chars, for display
token_hash	bytea			sha256(token)
user_id	uuid
roles	role_name[]	yes		optional allowlist: the token acts with at most these of the owner's roles
expires_at	timestamp with time zone	yes
last_used_at	timestamp with time zone	yes
created_at	timestamp with time zone		now()
revoked_at	timestamp with time zone	yes

Constraints:

• PRIMARY KEY (id)
• UNIQUE (token_hash)
• FOREIGN KEY (tenant_id) REFERENCES tenant(id)
• FOREIGN KEY (tenant_id, user_id) REFERENCES tenant_member(tenant_id, user_id) ON DELETE CASCADE
• FOREIGN KEY (user_id) REFERENCES app_user(id) ON DELETE CASCADE

Row-level security (forced): tenant_isolation.

web_session

Web sessions (cookie sp_session). A session's data holds what it is bound to: the tenant chosen at login, the platform, or nothing yet (between login and the choice of a tenant). Choosing again replaces the session.

Column	Type	Null	Default	Description
id	text
data	bytea
expiry_date	timestamp with time zone

Constraints:

• PRIMARY KEY (id)

Indexes:

• web_session_expiry USING btree (expiry_date)

host

Forgetting a host or agent instance deletes its rows; audit_event, secret_delivery and change_set_impact keep the ids without a foreign key.

Column	Type	Null	Default	Description
id	uuid
tenant_id	uuid
name	text			FQDN or stable client name
kind	host_kind		'server'::host_kind
owner_team_id	uuid	yes
labels	jsonb		'{}'::jsonb	effective labels: host_label plus the built-in labels
first_seen_at	timestamp with time zone		now()
updated_at	timestamp with time zone		now()

Constraints:

• PRIMARY KEY (id)
• UNIQUE (tenant_id, id)
• UNIQUE (tenant_id, name)
• FOREIGN KEY (tenant_id) REFERENCES tenant(id)
• FOREIGN KEY (tenant_id, owner_team_id) REFERENCES team(tenant_id, id) ON DELETE SET NULL (owner_team_id)

Indexes:

• host_labels_gin USING gin (labels jsonb_path_ops): The GIN index serves containment only (matchLabels); other selector predicates filter the tenant's rows (host_owner and the unique indexes start with tenant_id). It cannot lead with tenant_id without btree_gin.
• host_owner USING btree (tenant_id, owner_team_id)

Row-level security (forced): tenant_isolation.

host_label

Built-in labels (docs/spec-format.md §2) come from the host row; no source sets them, except os and arch, which only agent facts set.

Column	Type	Null	Default	Description
tenant_id	uuid
host_id	uuid
source	label_source
key	text
value	text
updated_at	timestamp with time zone		now()

Constraints:

• PRIMARY KEY (host_id, source, key)
• FOREIGN KEY (tenant_id, host_id) REFERENCES host(tenant_id, id) ON DELETE CASCADE
• CHECK (((key ~ '^[a-z0-9]([a-z0-9_.\-/]{0,126}[a-z0-9])?$'::text) AND (key <> ALL (ARRAY['host.name'::text, 'host.kind'::text, 'owner'::text])) AND ((source = 'agent'::label_source) OR (key <> ALL (ARRAY['os'::text, 'arch'::text])))))
• CHECK (((length(value) <= 256) AND (value !~ '[[:cntrl:]]'::text)))

Row-level security (forced): tenant_isolation.

network_zone

Network zones: CIDR -> labels (source 'zone'), derived from agent source IPs. A source address matches the zone with the longest prefix among the zones of the agent's tenant (zones of different tenants may overlap). A host's zone labels come from the last source address of its oldest agent instance.

Column	Type	Null	Default	Description
id	uuid
tenant_id	uuid
cidr	cidr
labels	jsonb			e.g. {"zone": "dmz-1"}

Constraints:

• PRIMARY KEY (id)
• UNIQUE (tenant_id, cidr)
• FOREIGN KEY (tenant_id) REFERENCES tenant(id)

Indexes:

• network_zone_cidr USING gist (cidr inet_ops)

Row-level security (forced): tenant_isolation.

spec_blob

Canonical file content, content-addressed within the tenant.

Column	Type	Null	Default	Description
tenant_id	uuid
hash	bytea			sha256(canonical bytes)
content	bytea			canonical TOML
created_at	timestamp with time zone		now()

Constraints:

• PRIMARY KEY (tenant_id, hash)
• FOREIGN KEY (tenant_id) REFERENCES tenant(id)

Row-level security (forced): tenant_isolation.

spec_revision

Each tenant has its own spec: revisions, blobs and change sets. History is native: log per file from spec_object validity ranges, diff between any two revision trees, revert and restore as ordinary change sets. Revisions and blobs are deleted only with their tenant.

Immutable revisions. The tree of revision N is the spec_object rows valid at N. History is linear and gapless: the parent of rN is rN-1 (r1 is the empty tree). A revert can recreate an earlier tree, so tree hashes repeat. The change set behind a revision is the one whose merged_revision_id it is.

Column	Type	Null	Default	Description
tenant_id	uuid
id	bigint			r-number, from tenant_counter 'revision'
tree_hash	bytea			sha256 over the sorted (path, blob hash) pairs of the tree
message	text
created_by	uuid	yes
created_at	timestamp with time zone		now()

Constraints:

• PRIMARY KEY (tenant_id, id)
• FOREIGN KEY (created_by) REFERENCES app_user(id)
• FOREIGN KEY (tenant_id) REFERENCES tenant(id)

Row-level security (forced): tenant_isolation.

spec_object

Object index with validity ranges, so "objects at revision N" is a range query without copying unchanged rows per revision. One object per file. A merge closes and opens ranges in the transaction that creates the revision, so the ranges of one object or path never overlap.

Column	Type	Null	Default	Description
tenant_id	uuid
kind	text
name	text
path	text
blob_hash	bytea
owner_team	text			team slug of the tenant
agent_kind	agent_kind	yes		Layer, Bundle, Route (Journey: strataplane-agent)
agent_versions	text	yes		semver requirement, NULL = any
precedence	integer	yes		layers only
selector	jsonb	yes		layers and journeys
valid_from	bigint
valid_to	bigint	yes		exclusive; NULL = still current

Constraints:

• PRIMARY KEY (tenant_id, kind, name, valid_from)
• FOREIGN KEY (tenant_id, blob_hash) REFERENCES spec_blob(tenant_id, hash)
• FOREIGN KEY (tenant_id, valid_from) REFERENCES spec_revision(tenant_id, id)
• FOREIGN KEY (tenant_id, valid_to) REFERENCES spec_revision(tenant_id, id)
• CHECK ((((kind = 'Secret'::text) AND (name ~ '^[A-Za-z0-9_]+$'::text)) OR ((kind <> 'Secret'::text) AND (name ~ '^[a-z0-9]([a-z0-9.-]{0,251}[a-z0-9])?$'::text))))
• CHECK (((kind = 'Layer'::text) OR (agent_versions IS NULL)))
• CHECK (((kind = ANY (ARRAY['Layer'::text, 'Bundle'::text, 'Route'::text, 'Journey'::text])) = (agent_kind IS NOT NULL)))
• CHECK ((kind = ANY (ARRAY['Layer'::text, 'Bundle'::text, 'Route'::text, 'Secret'::text, 'Journey'::text, 'RolloutPolicy'::text, 'ApprovalPolicy'::text, 'Settings'::text, 'Placement'::text])))
• CHECK ((valid_to > valid_from))

Indexes:

• spec_object_current UNIQUE USING btree (tenant_id, kind, name) WHERE (valid_to IS NULL)
• spec_object_current_path UNIQUE USING btree (tenant_id, path) WHERE (valid_to IS NULL)
• spec_object_path USING btree (tenant_id, path, valid_from DESC): per-file history

Row-level security (forced): tenant_isolation.

change_set

Column	Type	Null	Default	Description
id	uuid
tenant_id	uuid
number	bigint			human handle CS-123, from tenant_counter 'change_set'
title	text
description	text		''::text
author_id	uuid			user or service account
assistant_chat_id	uuid	yes		created by the assistant in this chat
base_revision_id	bigint
state	change_set_state		'draft'::change_set_state
digest	bytea			sha256 over (base, overlay); validation and approvals bind to it
validated_digest	bytea	yes		digest of the last successful validation
validation	jsonb	yes		diagnostics per file / artifact
impact	jsonb	yes		summary: counts by kind, owner, stage, version
requirements	jsonb	yes		computed approval requirements and their state
reverts_revision_id	bigint	yes		a revert: the revision being undone
merged_revision_id	bigint	yes
created_at	timestamp with time zone		now()
updated_at	timestamp with time zone		now()
submitted_at	timestamp with time zone	yes
merged_at	timestamp with time zone	yes
closed_at	timestamp with time zone	yes

Constraints:

• PRIMARY KEY (id)
• UNIQUE (tenant_id, id)
• UNIQUE (tenant_id, merged_revision_id)
• UNIQUE (tenant_id, number)
• FOREIGN KEY (author_id) REFERENCES app_user(id)
• FOREIGN KEY (tenant_id) REFERENCES tenant(id)
• FOREIGN KEY (tenant_id, base_revision_id) REFERENCES spec_revision(tenant_id, id)
• FOREIGN KEY (tenant_id, merged_revision_id) REFERENCES spec_revision(tenant_id, id)
• FOREIGN KEY (tenant_id, reverts_revision_id) REFERENCES spec_revision(tenant_id, id)
• CHECK ((((state = 'merged'::change_set_state) = (merged_revision_id IS NOT NULL)) AND ((state = 'merged'::change_set_state) = (merged_at IS NOT NULL)) AND ((state = 'closed'::change_set_state) = (closed_at IS NOT NULL))))
• CHECK (((state <> ALL (ARRAY['in_review'::change_set_state, 'merged'::change_set_state])) OR (NOT (validated_digest IS DISTINCT FROM digest))))

Row-level security (forced): tenant_isolation.

change_set_file

The overlay: one row per changed path. blob_hash NULL = deletion.

Column	Type	Null	Default	Description
tenant_id	uuid
change_set_id	uuid
path	text
blob_hash	bytea	yes
base_hash	bytea	yes		blob at base revision (NULL = new file)
updated_at	timestamp with time zone		now()

Constraints:

• PRIMARY KEY (change_set_id, path)
• FOREIGN KEY (tenant_id, blob_hash) REFERENCES spec_blob(tenant_id, hash)
• FOREIGN KEY (tenant_id, change_set_id) REFERENCES change_set(tenant_id, id) ON DELETE CASCADE

Row-level security (forced): tenant_isolation.

change_set_impact

The impact set (docs/spec-format.md §6); before = after for agents affected only through a Secret or Placement. Large but bounded by fleet size; rows are deleted when the change set is merged or closed (the summary stays in change_set.impact).

Column	Type	Null	Default	Description
tenant_id	uuid
change_set_id	uuid
agent_instance_id	uuid			no FK: forgetting an agent keeps the row
before_artifact	bytea	yes
after_artifact	bytea	yes		NULL = render failed, see validation

Constraints:

• PRIMARY KEY (change_set_id, agent_instance_id)
• FOREIGN KEY (tenant_id, after_artifact) REFERENCES artifact(tenant_id, hash)
• FOREIGN KEY (tenant_id, before_artifact) REFERENCES artifact(tenant_id, hash)
• FOREIGN KEY (tenant_id, change_set_id) REFERENCES change_set(tenant_id, id) ON DELETE CASCADE

Row-level security (forced): tenant_isolation.

change_set_review

Reviewers are human users; how reviews count is described at change_set_state.

Column	Type	Null	Default	Description
id	uuid
tenant_id	uuid
change_set_id	uuid
reviewer_id	uuid
decision	text
digest	bytea			counts only while it equals change_set.digest
acting_teams	text[]		'{}'::text[]	teams this approval satisfies
comment	text	yes
created_at	timestamp with time zone		now()

Constraints:

• PRIMARY KEY (id)
• FOREIGN KEY (reviewer_id) REFERENCES app_user(id)
• FOREIGN KEY (tenant_id, change_set_id) REFERENCES change_set(tenant_id, id) ON DELETE CASCADE
• CHECK ((decision = ANY (ARRAY['approve'::text, 'reject'::text, 'comment'::text])))

Row-level security (forced): tenant_isolation.

enrollment_token

token_hash is unique across tenants: the enrollment token names the tenant an agent enrolls into.

Column	Type	Null	Default	Description
id	uuid
tenant_id	uuid
name	text
agent_kind	agent_kind
token_prefix	text
token_hash	bytea
labels	jsonb		'{}'::jsonb	applied to hosts enrolled with it (source 'enrollment')
host_kind	host_kind		'server'::host_kind
mode	text		'enroll'::text	enroll: exchanged for a per-agent credential. class (Telegraf only): the shared credential of every instance of the class (agents/telegraf.md, class mode). pair (strataplane-agent only): a pairing code of created_by, single use, enrolling a temporary exploration runner only that user may use (journeys.md §8.2); the instance's facts record paired_by.
requires_approval	boolean		false
max_uses	integer	yes
uses	integer		0
expires_at	timestamp with time zone	yes
created_by	uuid	yes
created_at	timestamp with time zone		now()
revoked_at	timestamp with time zone	yes

Constraints:

• PRIMARY KEY (id)
• UNIQUE (tenant_id, id)
• UNIQUE (token_hash)
• FOREIGN KEY (created_by) REFERENCES app_user(id)
• FOREIGN KEY (tenant_id) REFERENCES tenant(id)
• CHECK (((mode <> 'class'::text) OR (agent_kind = 'telegraf'::agent_kind)))
• CHECK (((mode <> 'pair'::text) OR ((agent_kind = 'strataplane-agent'::agent_kind) AND (created_by IS NOT NULL))))
• CHECK ((mode = ANY (ARRAY['enroll'::text, 'class'::text, 'pair'::text])))

Row-level security (forced): tenant_isolation.

trust_anchor

CAs the tenant accepts for agent mTLS. Deleting an anchor first unbinds the certificates of the agents identified by it (identity_* NULL) in the same transaction, so they drop to assurance low.

Column	Type	Null	Default	Description
id	uuid
tenant_id	uuid
name	text
ca_pem	text
crl_url	text	yes		subject to the egress rules (docs/architecture.md §11)
host_mapping	text		'san-dns'::text
created_at	timestamp with time zone		now()

Constraints:

• PRIMARY KEY (id)
• UNIQUE (tenant_id, id)
• UNIQUE (tenant_id, name)
• FOREIGN KEY (tenant_id) REFERENCES tenant(id)
• CHECK ((host_mapping = ANY (ARRAY['san-dns'::text, 'cn'::text, 'san-uri-spiffe'::text])))

Row-level security (forced): tenant_isolation.

agent_instance

The feedback loop: desired (rendered by the reconciler), served (fetched by Telegraf or Vector / POLICY_CHANGE delivered), applied (proven by the Telegraf or Vector heartbeat header, or Elastic ack plus checkin policy_revision_idx). A reported hash that is not an artifact of the tenant is ignored. An artifact proves content, not a revision: applied_revision_id is the revision whose render produced the applied artifact: the desired revision when the agent applied its desired artifact (or a later desired revision rendering the same bytes), kept while a newer desired artifact is not applied yet, and NULL when the agent applied an artifact that was not its desired one, whose revision the server does not know.

The bound client certificate. Bound means assurance high: every request that can carry a client cert must then present it.

With retention.silentAgents set (docs/spec-format.md §3.8), the retention job forgets an instance whose silence exceeds it, as a manual forget does (credentials revoked, rows deleted, audit event agent.forget by the system), at most 1000 per pass. Silence counts from the latest of last_seen_at (enrolled_at while it never reported) and the resumed_at of the tenant's controller_shard; the API shows that start plus silentAgents as the instance's forget_at. A host left without instances, manual labels and owner team is forgotten with it (host.forget).

Column	Type	Null	Default	Description
id	uuid
tenant_id	uuid
host_id	uuid
kind	agent_kind
external_id	text			Telegraf instance_id (class mode: hostname) / Fleet agent id / helper or Vector instance id
version	text	yes		normalized semver, e.g. 1.40.1
version_raw	text	yes		as reported
enrollment_token_id	uuid	yes
enrolled_at	timestamp with time zone		now()
approved_at	timestamp with time zone	yes		NULL while first contact awaits approval
identity_subject	text	yes		cert subject/SAN
identity_fingerprint	bytea	yes
identity_anchor_id	uuid	yes
identity_not_after	timestamp with time zone	yes
replace_token_hash	bytea	yes		Elastic Agent: SHA-256 of the replace_token sent at enrollment; a later enrollment with the same id re-binds the instance only with the same token (agents/elastic-agent.md §3).
assurance	assurance	yes	generated: CASE WHEN (identity_fingerprint IS NULL) THEN 'low'::assurance ELSE 'high'::assurance END
health	agent_health		'pending'::agent_health
health_message	text	yes
sync	agent_sync		'updating'::agent_sync
sync_reason	text	yes		out_of_sync reasons: reload_failed (served, not applied in time), watcher_stalled (Telegraf or Vector stopped polling), ambiguous_address (Telegraf class mode), restart_required (Vector rejected the reload because a global option changed). agents/telegraf.md §4, §6 and class mode; agents/vector.md §6.
flags	text[]		'{}'::text[]	unsupported_version: outside the supported range, receives no config; catalog_mismatch: no catalog bundle for the version, the nearest older one is used.
quarantined_at	timestamp with time zone	yes		no config updates, no secrets
quarantine_reason	text	yes
desired_revision_id	bigint	yes
desired_artifact	bytea	yes
desired_since	timestamp with time zone	yes		drives Telegraf Last-Modified; stable across replicas
served_artifact	bytea	yes		last artifact actually delivered (GET / action)
served_at	timestamp with time zone	yes
applied_revision_id	bigint	yes
applied_artifact	bytea	yes
applied_at	timestamp with time zone	yes
last_seen_at	timestamp with time zone	yes		not indexed, so heartbeat updates stay HOT
last_source_ip	inet	yes		PROXY protocol or X-Forwarded-For from agent_proxy_from
facts	jsonb		'{}'::jsonb	reported metadata (ECS host, hostname, statistics, helper Chromium version)
created_at	timestamp with time zone		now()

Constraints:

• PRIMARY KEY (id)
• UNIQUE (tenant_id, id)
• UNIQUE (tenant_id, kind, external_id)
• FOREIGN KEY (tenant_id) REFERENCES tenant(id)
• FOREIGN KEY (tenant_id, applied_artifact) REFERENCES artifact(tenant_id, hash)
• FOREIGN KEY (tenant_id, applied_revision_id) REFERENCES spec_revision(tenant_id, id)
• FOREIGN KEY (tenant_id, desired_artifact) REFERENCES artifact(tenant_id, hash)
• FOREIGN KEY (tenant_id, desired_revision_id) REFERENCES spec_revision(tenant_id, id)
• FOREIGN KEY (tenant_id, enrollment_token_id) REFERENCES enrollment_token(tenant_id, id)
• FOREIGN KEY (tenant_id, host_id) REFERENCES host(tenant_id, id) ON DELETE CASCADE
• FOREIGN KEY (tenant_id, identity_anchor_id) REFERENCES trust_anchor(tenant_id, id)
• FOREIGN KEY (tenant_id, served_artifact) REFERENCES artifact(tenant_id, hash)
• CHECK (((quarantined_at IS NULL) = (quarantine_reason IS NULL)))
• CHECK (((sync_reason IS NULL) OR (sync = 'out_of_sync'::agent_sync)))
• CHECK ((flags <@ ARRAY['unsupported_version'::text, 'catalog_mismatch'::text]))
• CHECK ((num_nulls(identity_subject, identity_fingerprint, identity_anchor_id, identity_not_after) = ANY (ARRAY[0, 4])))
• CHECK ((sync_reason = ANY (ARRAY['reload_failed'::text, 'watcher_stalled'::text, 'ambiguous_address'::text, 'restart_required'::text])))

Indexes:

• agent_instance_class USING btree (enrollment_token_id, last_source_ip) WHERE (enrollment_token_id IS NOT NULL): Telegraf class mode: the members of a class at a source address.
• agent_instance_health USING btree (tenant_id, kind, health)
• agent_instance_host USING btree (tenant_id, host_id)

Row-level security (forced): tenant_isolation.

class_default

Telegraf class mode (agents/telegraf.md, class mode): the class config, rendered from the token's labels and the Telegraf version in User-Agent at the release pointer. Config requests get it unless their source address resolves to a member whose own desired artifact differs from it.

Column	Type	Null	Default	Description
tenant_id	uuid
enrollment_token_id	uuid
agent_version	text
desired_revision_id	bigint	yes
desired_artifact	bytea	yes
desired_since	timestamp with time zone	yes

Constraints:

• PRIMARY KEY (enrollment_token_id, agent_version)
• FOREIGN KEY (tenant_id, desired_artifact) REFERENCES artifact(tenant_id, hash)
• FOREIGN KEY (tenant_id, desired_revision_id) REFERENCES spec_revision(tenant_id, id)
• FOREIGN KEY (tenant_id, enrollment_token_id) REFERENCES enrollment_token(tenant_id, id) ON DELETE CASCADE

Row-level security (forced): tenant_isolation.

class_served

Telegraf class mode: the artifact last served to a source address, for the watcher rule (agents/telegraf.md §6). Written only when it changes; rows without a GET for 30 days are pruned, and a missing row counts as different, which costs one extra reload.

Column	Type	Null	Default	Description
tenant_id	uuid
enrollment_token_id	uuid
address	inet
artifact	bytea
served_at	timestamp with time zone		now()

Constraints:

• PRIMARY KEY (enrollment_token_id, address)
• FOREIGN KEY (tenant_id, artifact) REFERENCES artifact(tenant_id, hash)
• FOREIGN KEY (tenant_id, enrollment_token_id) REFERENCES enrollment_token(tenant_id, id) ON DELETE CASCADE

Row-level security (forced): tenant_isolation.

agent_credential

Per-agent credentials, by the instance's kind: Telegraf bearer tokens ('spt_'), Elastic access API keys ('spe_'), strataplane-agent bearer tokens ('sph_'), Vector bearer tokens ('spv_'). token_hash is unique across tenants. At most one is unrevoked per instance, so its last use is the instance's last_seen_at.

Column	Type	Null	Default	Description
id	uuid
tenant_id	uuid
agent_instance_id	uuid
token_prefix	text
token_hash	bytea
created_at	timestamp with time zone		now()
revoked_at	timestamp with time zone	yes

Constraints:

• PRIMARY KEY (id)
• UNIQUE (token_hash)
• FOREIGN KEY (tenant_id, agent_instance_id) REFERENCES agent_instance(tenant_id, id) ON DELETE CASCADE

Indexes:

• agent_credential_active UNIQUE USING btree (agent_instance_id) WHERE (revoked_at IS NULL)

Row-level security (forced): tenant_isolation.

agent_status_event

transitions only, not every heartbeat

Column	Type	Null	Default	Description
id	bigint		nextval('agent_status_event_id_seq'::regclass)
tenant_id	uuid
agent_instance_id	uuid
ts	timestamp with time zone		now()
field	text
from_value	text	yes
to_value	text
detail	jsonb	yes

Constraints:

• PRIMARY KEY (id)
• FOREIGN KEY (tenant_id, agent_instance_id) REFERENCES agent_instance(tenant_id, id) ON DELETE CASCADE
• CHECK ((field = ANY (ARRAY['health'::text, 'sync'::text, 'version'::text, 'applied'::text])))

Indexes:

• agent_status_event_agent USING btree (agent_instance_id, ts DESC)

Row-level security (forced): tenant_isolation.

agent_log_entry

from Telegraf heartbeat 'logs' and the internal_logs events of Vector heartbeats (source: Telegraf plugin::alias or Vector component id); the last 200 per agent, at most retention.agentLogs

Column	Type	Null	Default	Description
id	bigint		nextval('agent_log_entry_id_seq'::regclass)
tenant_id	uuid
agent_instance_id	uuid
ts	timestamp with time zone
level	text
source	text			e.g. inputs.http::portal -> instance key
instance_key	text	yes
message	text
attributes	jsonb	yes

Constraints:

• PRIMARY KEY (id)
• FOREIGN KEY (tenant_id, agent_instance_id) REFERENCES agent_instance(tenant_id, id) ON DELETE CASCADE

Indexes:

• agent_log_entry_agent USING btree (agent_instance_id, ts DESC)

Row-level security (forced): tenant_isolation.

agent_component

Elastic Agent component/unit state from checkins (current state only).

Column	Type	Null	Default	Description
tenant_id	uuid
agent_instance_id	uuid
component_id	text
unit_id	text		''::text	'' = component-level row
unit_type	text	yes		input | output
state	text			STARTING, HEALTHY, DEGRADED, FAILED, STOPPING, ...
message	text	yes
payload	jsonb	yes
updated_at	timestamp with time zone		now()

Constraints:

• PRIMARY KEY (agent_instance_id, component_id, unit_id)
• FOREIGN KEY (tenant_id, agent_instance_id) REFERENCES agent_instance(tenant_id, id) ON DELETE CASCADE

Row-level security (forced): tenant_isolation.

agent_action

Elastic Agent action queue. The instance's policy revision_idx is the policy_seq of its newest POLICY_CHANGE, which is never pruned.

Column	Type	Null	Default	Description
id	uuid
tenant_id	uuid
agent_instance_id	uuid
type	text
data	jsonb		'{}'::jsonb	never contains secret values; policy is re-materialized on delivery
artifact	bytea	yes		for POLICY_CHANGE
policy_seq	bigint	yes		revision_idx carried by this policy
created_by	uuid	yes
created_at	timestamp with time zone		now()
start_time	timestamp with time zone	yes
expires_at	timestamp with time zone	yes
delivered_at	timestamp with time zone	yes
delivery_count	integer		0
acked_at	timestamp with time zone	yes
ack_error	text	yes		POLICY_CHANGE: the checkin's failure message, never an ack
superseded_by	uuid	yes

Constraints:

• PRIMARY KEY (id)
• UNIQUE (tenant_id, id)
• FOREIGN KEY (created_by) REFERENCES app_user(id)
• FOREIGN KEY (tenant_id, agent_instance_id) REFERENCES agent_instance(tenant_id, id) ON DELETE CASCADE
• FOREIGN KEY (tenant_id, artifact) REFERENCES artifact(tenant_id, hash)
• FOREIGN KEY (tenant_id, superseded_by) REFERENCES agent_action(tenant_id, id)
• CHECK ((type = ANY (ARRAY['POLICY_CHANGE'::text, 'UNENROLL'::text, 'SETTINGS'::text])))

Indexes:

• agent_action_pending USING btree (agent_instance_id, created_at) WHERE ((acked_at IS NULL) AND (superseded_by IS NULL))

Row-level security (forced): tenant_isolation.

artifact

Rendered bytes, content-addressed. Renders that produce the same bytes from different inputs share one artifact, so an artifact carries no provenance: explain recomputes it from the render inputs (docs/architecture.md §4). Artifacts are per tenant even when two tenants render identical bytes. The format follows from the agent kind (TOML for Telegraf and Vector, JSON otherwise). Every artifact column points at a stored artifact, so garbage collection cannot delete one that is still in use.

Column	Type	Null	Default	Description
tenant_id	uuid
hash	bytea			sha256(content)
agent_kind	agent_kind
content	bytea			zstd-compressed; secret references only, never values
secret_refs	text[]		'{}'::text[]	'name@vN': the secret versions rendered against
size	integer			uncompressed bytes
created_at	timestamp with time zone		now()
last_used_at	timestamp with time zone		now()	GC: unreferenced and unused for retention.artifactsUnreferenced

Constraints:

• PRIMARY KEY (tenant_id, hash)
• FOREIGN KEY (tenant_id) REFERENCES tenant(id)

Row-level security (forced): tenant_isolation.

artifact_validation

L2/L3 results per distinct (artifact, agent version)

Column	Type	Null	Default	Description
tenant_id	uuid
artifact_hash	bytea
agent_version	text
level	text
ok	boolean
diagnostics	jsonb		'[]'::jsonb
validator	text			e.g. 'telegraf 1.40.1 config check'
created_at	timestamp with time zone		now()

Constraints:

• PRIMARY KEY (tenant_id, artifact_hash, agent_version, level)
• FOREIGN KEY (tenant_id, artifact_hash) REFERENCES artifact(tenant_id, hash) ON DELETE CASCADE
• CHECK ((level = ANY (ARRAY['schema'::text, 'sandbox'::text])))

Row-level security (forced): tenant_isolation.

release

Desired revision of an agent instance, first match wins: an active pin (agent, then selector, newest first); an assignment in an active rollout (its to_revision); the release pointer of its agent kind. The revision carries everything a render depends on besides inventory, secret versions and server.agent_url, including the render level (docs/architecture.md §4), so pins and rollouts fix renderer behaviour too. one row per tenant and agent kind

Column	Type	Null	Default	Description
tenant_id	uuid
agent_kind	agent_kind
revision_id	bigint
rollout_id	uuid	yes
set_at	timestamp with time zone		now()
set_by	uuid	yes

Constraints:

• PRIMARY KEY (tenant_id, agent_kind)
• FOREIGN KEY (set_by) REFERENCES app_user(id)
• FOREIGN KEY (tenant_id) REFERENCES tenant(id)
• FOREIGN KEY (tenant_id, revision_id) REFERENCES spec_revision(tenant_id, id)
• FOREIGN KEY (tenant_id, rollout_id) REFERENCES rollout(tenant_id, id)

Row-level security (forced): tenant_isolation.

rollout

The change sets a rollout delivers are those merged into (from_revision, to_revision].

Column	Type	Null	Default	Description
id	uuid
tenant_id	uuid
number	bigint			RO-17, from tenant_counter 'rollout'
agent_kind	agent_kind
from_revision_id	bigint
to_revision_id	bigint
policy_name	text	yes		RolloutPolicy used; NULL = single stage of all impacted agents
guardrails	jsonb		'{}'::jsonb	the policy's guardrails at creation
state	rollout_state		'running'::rollout_state
created_by	uuid	yes
created_at	timestamp with time zone		now()
finished_at	timestamp with time zone	yes
halt_reason	text	yes		why the rollout halted (the breached guardrail); kept when the halted rollout is aborted, cleared when it is resumed

Constraints:

• PRIMARY KEY (id)
• UNIQUE (tenant_id, id)
• UNIQUE (tenant_id, number)
• FOREIGN KEY (created_by) REFERENCES app_user(id)
• FOREIGN KEY (tenant_id) REFERENCES tenant(id)
• FOREIGN KEY (tenant_id, from_revision_id) REFERENCES spec_revision(tenant_id, id)
• FOREIGN KEY (tenant_id, to_revision_id) REFERENCES spec_revision(tenant_id, id)
• CHECK (((state = ANY (ARRAY['completed'::rollout_state, 'aborted'::rollout_state])) = (finished_at IS NOT NULL)))

Indexes:

• rollout_one_active UNIQUE USING btree (tenant_id, agent_kind) WHERE (state = ANY (ARRAY['running'::rollout_state, 'paused'::rollout_state, 'halted'::rollout_state]))

Row-level security (forced): tenant_isolation.

rollout_stage

Column	Type	Null	Default	Description
tenant_id	uuid
rollout_id	uuid
idx	integer
name	text
selector	jsonb
bake_time	interval		'00:00:00'::interval
state	rollout_stage_state		'pending'::rollout_stage_state
agents_total	integer	yes		set when the stage starts
guardrails	jsonb	yes		last evaluated values
started_at	timestamp with time zone	yes
finished_at	timestamp with time zone	yes

Constraints:

• PRIMARY KEY (rollout_id, idx)
• UNIQUE (tenant_id, rollout_id, idx)
• FOREIGN KEY (tenant_id, rollout_id) REFERENCES rollout(tenant_id, id) ON DELETE CASCADE

Row-level security (forced): tenant_isolation.

rollout_assignment

Column	Type	Null	Default	Description
tenant_id	uuid
rollout_id	uuid
agent_instance_id	uuid
stage_idx	integer
assigned_at	timestamp with time zone		now()
baseline_health	agent_health	yes		health before the change, for regression guardrails

Constraints:

• PRIMARY KEY (rollout_id, agent_instance_id)
• FOREIGN KEY (tenant_id, agent_instance_id) REFERENCES agent_instance(tenant_id, id) ON DELETE CASCADE
• FOREIGN KEY (tenant_id, rollout_id, stage_idx) REFERENCES rollout_stage(tenant_id, rollout_id, idx) ON DELETE CASCADE

Row-level security (forced): tenant_isolation.

pin

A pin keeps its agents on its revision. Pins fix the revision, not the artifact: a new secret version still re-renders at the pinned revision and reaches the agent. Active from starts_at until expires_at. A pin or rollout to a revision that references a Secret absent at the latest revision is rejected, since that Secret is delivered to no one.

Column	Type	Null	Default	Description
id	uuid
tenant_id	uuid
selector	jsonb	yes		either selector (with agent_kinds) or agent_instance_id
agent_kinds	agent_kind[]	yes
agent_instance_id	uuid	yes
revision_id	bigint
reason	text
created_by	uuid	yes
created_at	timestamp with time zone		now()
starts_at	timestamp with time zone		now()
expires_at	timestamp with time zone	yes

Constraints:

• PRIMARY KEY (id)
• FOREIGN KEY (created_by) REFERENCES app_user(id)
• FOREIGN KEY (tenant_id) REFERENCES tenant(id)
• FOREIGN KEY (tenant_id, agent_instance_id) REFERENCES agent_instance(tenant_id, id) ON DELETE CASCADE
• FOREIGN KEY (tenant_id, revision_id) REFERENCES spec_revision(tenant_id, id)
• CHECK (((selector IS NULL) <> (agent_instance_id IS NULL)))
• CHECK (((selector IS NULL) = (agent_kinds IS NULL)))
• CHECK ((expires_at > starts_at))

Row-level security (forced): tenant_isolation.

secret_key

Keys of the deployment's keyring (key-encryption keys): fingerprints only, never key material.

Column	Type	Null	Default	Description
id	integer			key id as in the keyring file
fingerprint	bytea			sha256 over a fixed label encrypted with the key
created_at	timestamp with time zone		now()
exported_at	timestamp with time zone	yes		set by secrets export-key; readiness warns while NULL
retired_at	timestamp with time zone	yes		set: decrypts only, until rotation finishes

Constraints:

• PRIMARY KEY (id)
• UNIQUE (fingerprint)

tenant_key

Per-tenant data keys, wrapped under a deployment key. They encrypt the tenant's sealed values and objects. Deleting a tenant's rows here makes its remaining ciphertext unreadable.

Column	Type	Null	Default	Description
tenant_id	uuid
id	integer
kek_id	integer
nonce	bytea
wrapped_key	bytea			AES-256-GCM(data key) under the deployment key, AAD = tenant id and key id
created_at	timestamp with time zone		now()
retired_at	timestamp with time zone	yes		set: decrypts only, until rotation finishes

Constraints:

• PRIMARY KEY (tenant_id, id)
• FOREIGN KEY (kek_id) REFERENCES secret_key(id)
• FOREIGN KEY (tenant_id) REFERENCES tenant(id)

Row-level security (forced): tenant_isolation.

sealed_value

Every value encrypted under a tenant's data key: secret versions, LLM provider API keys, audit sink credentials. AES-256-GCM with AAD = tenant id and owner; decryption checks owner against the row that references the value, so a value cannot be moved to another owner. Written and deleted together with that row; rotating a data key re-encrypts this table.

Column	Type	Null	Default	Description
id	uuid
tenant_id	uuid
owner	text			'secret//', 'llm_provider/', 'audit_sink/'
key_id	integer
nonce	bytea			96-bit, random per value
ciphertext	bytea			never returned by the API
created_at	timestamp with time zone		now()

Constraints:

• PRIMARY KEY (id)
• UNIQUE (tenant_id, id)
• UNIQUE (tenant_id, owner)
• FOREIGN KEY (tenant_id) REFERENCES tenant(id)
• FOREIGN KEY (tenant_id, key_id) REFERENCES tenant_key(tenant_id, id)

Row-level security (forced): tenant_isolation.

secret_version

Values of the spec Secret objects. The Secret object at the latest revision governs its values: its owner may write them (only while it exists there), and its grant and minAssurance decide delivery (docs/architecture.md §11). The current version of a name is the newest one not disabled. Versions belong to the name, not to a revision, so a Secret re-added under the same name gets its versions back.

Column	Type	Null	Default	Description
tenant_id	uuid
secret_name	text
version	integer
value_id	uuid
comment	text	yes
created_by	uuid	yes
created_at	timestamp with time zone		now()
disabled_at	timestamp with time zone	yes

Constraints:

• PRIMARY KEY (tenant_id, secret_name, version)
• FOREIGN KEY (created_by) REFERENCES app_user(id)
• FOREIGN KEY (tenant_id) REFERENCES tenant(id)
• FOREIGN KEY (tenant_id, value_id) REFERENCES sealed_value(tenant_id, id)
• CHECK ((secret_name ~ '^[A-Za-z0-9_]+$'::text))

Row-level security (forced): tenant_isolation.

secret_delivery

audit of every delivery

Column	Type	Null	Default	Description
id	bigint		nextval('secret_delivery_id_seq'::regclass)
tenant_id	uuid
secret_name	text
version	integer
agent_instance_id	uuid
channel	text			telegraf-secretstore: one row per key fetched; elastic-policy: per POLICY_CHANGE delivered; vector-config: when a config carrying the version is first served to the instance (served_artifact changes), not on every poll
assurance	assurance
identity_subject	text	yes
ts	timestamp with time zone		now()

Constraints:

• PRIMARY KEY (id)
• FOREIGN KEY (tenant_id) REFERENCES tenant(id)
• CHECK ((channel = ANY (ARRAY['telegraf-secretstore'::text, 'elastic-policy'::text, 'vector-config'::text])))

Indexes:

• secret_delivery_secret USING btree (tenant_id, secret_name, ts DESC)

Row-level security (forced): tenant_isolation.

catalog_bundle

Installation-wide, imported by platform admins, read by every tenant. Import rejects bundles whose signature no trusted key verifies. Bundle files (wasm modules, validator binaries) are objects under catalog//.

Column	Type	Null	Default	Description
id	uuid
agent_kind	agent_kind
version	text			agent version this bundle describes
bundle_hash	bytea
contents	jsonb			manifest: schemas, wasm modules (with their parsers and ABI version), validator binaries, with sizes and hashes
imported_by	uuid	yes
imported_at	timestamp with time zone		now()

Constraints:

• PRIMARY KEY (id)
• UNIQUE (agent_kind, version)
• UNIQUE (bundle_hash)
• FOREIGN KEY (imported_by) REFERENCES app_user(id)

plugin_schema

Column	Type	Null	Default	Description
bundle_id	uuid
section	text			inputs, outputs, processors, aggregators, parsers, serializers, secretstores
plugin	text
schema	jsonb			JSON Schema 2020-12
sample	text	yes		sample.conf / reference config
docs_md	text	yes

Constraints:

• PRIMARY KEY (bundle_id, section, plugin)
• FOREIGN KEY (bundle_id) REFERENCES catalog_bundle(id) ON DELETE CASCADE

llm_provider

Column	Type	Null	Default	Description
id	uuid
tenant_id	uuid
name	text
kind	text
base_url	text			subject to the egress rules (docs/architecture.md §11)
api_key_id	uuid	yes		API key: a server credential, not a spec Secret, so no agent config can reference it. Write-only, never sent to browsers.
models	jsonb			[{id, displayName, contextWindow, maxOutputTokens, protocol, thinking}]
enabled	boolean		true	default model and prompt addendum: Settings 'assistant'

Constraints:

• PRIMARY KEY (id)
• UNIQUE (tenant_id, name)
• FOREIGN KEY (tenant_id) REFERENCES tenant(id)
• FOREIGN KEY (tenant_id, api_key_id) REFERENCES sealed_value(tenant_id, id)
• CHECK ((kind = ANY (ARRAY['openai-compatible'::text, 'anthropic'::text])))

Row-level security (forced): tenant_isolation.

chat

One Pi Durable commit log per chat (docs/assistant.md §2). Opening a chat starts a new writer_epoch; a commit must carry the current epoch and seq = last_seq + 1, and a retry with last_seq and last_digest is accepted again without effect. Deleting a chat deletes its rows and keeps the row as a tombstone (deleted_at), so a view that still holds the chat cannot store it again.

Column	Type	Null	Default	Description
id	uuid			chosen by the client
tenant_id	uuid
owner_id	uuid
title	text		''::text
writer_epoch	bigint		0	one writer at a time (tab takeover)
last_seq	bigint		0
last_digest	text	yes		opaque to the server, at most 512 bytes
stored_bytes	bigint		0	sum of the rows' payload bytes; counts toward chat_bytes
created_at	timestamp with time zone		now()
updated_at	timestamp with time zone		now()
deleted_at	timestamp with time zone	yes

Constraints:

• PRIMARY KEY (id)
• UNIQUE (tenant_id, id)
• FOREIGN KEY (owner_id) REFERENCES app_user(id)
• FOREIGN KEY (tenant_id) REFERENCES tenant(id)
• CHECK (((deleted_at IS NULL) OR (stored_bytes = 0)))

Indexes:

• chat_recent USING btree (tenant_id, owner_id, updated_at DESC) WHERE (deleted_at IS NULL)

Row-level security (forced): tenant_isolation.

chat_row

A commit's rows in order (pos). A row with replace deletes the chat's earlier rows with the same key before it is stored: task and submission records, and document bases. Document deltas carry their document's key without replace, so the next base removes them.

Column	Type	Null	Default	Description
tenant_id	uuid
chat_id	uuid
seq	bigint
pos	integer
key	text	yes		e.g. task:, submission:, document:
replace	boolean		false
payload	text			one Pi Durable storage write, JSON as sent

Constraints:

• PRIMARY KEY (chat_id, seq, pos)
• FOREIGN KEY (tenant_id, chat_id) REFERENCES chat(tenant_id, id) ON DELETE CASCADE
• CHECK (((NOT replace) OR (key IS NOT NULL)))
• CHECK ((length(key) <= 512))

Indexes:

• chat_row_key USING btree (chat_id, key, seq) WHERE (key IS NOT NULL)

Row-level security (forced): tenant_isolation.

audit_event

One hash chain per tenant. seq comes from tenant_counter 'audit', whose row lock is held until commit, so a tenant's events commit in seq order: the chain never forks and an export cursor on seq never skips an event. The event is the last write of its transaction, so the lock is held only for the commit.

Column	Type	Null	Default	Description
tenant_id	uuid
seq	bigint
ts	timestamp with time zone		now()
actor_kind	text			user: human or service account; assistant: a request of the browser assistant naming its chat (X-Assistant-Chat, docs/assistant.md §1), actor_id the chat id
actor_id	text	yes
on_behalf_of	uuid	yes		user behind an assistant action, whose permissions it used
action	text			e.g. change_set.approve, secret.write, rollout.halt
object_kind	text
object_id	text
request_id	text	yes
source_ip	inet	yes
details	jsonb		'{}'::jsonb	user events: details.authz = {package, bindings}, the policy package version and the role bindings that determined the request's permission decisions (docs/authorization.md §7)
prev_hash	bytea	yes
hash	bytea			sha256(prev_hash || canonical(event)); tamper evidence

Constraints:

• PRIMARY KEY (tenant_id, seq)
• FOREIGN KEY (tenant_id) REFERENCES tenant(id)
• CHECK ((actor_kind = ANY (ARRAY['user'::text, 'agent'::text, 'system'::text, 'assistant'::text])))

Indexes:

• audit_event_object USING btree (tenant_id, object_kind, object_id, ts DESC)

Row-level security (forced): tenant_isolation.

platform_audit_event

Platform actions (tenants, catalog imports, platform admins): one chain for the installation. Appends hold a transaction advisory lock from taking the id until commit, for the same ordering guarantee.

Column	Type	Null	Default	Description
id	bigint		nextval('platform_audit_event_id_seq'::regclass)
ts	timestamp with time zone		now()
actor_kind	text
actor_id	text	yes
action	text			e.g. tenant.create, tenant.suspend, catalog.import
object_kind	text
object_id	text
tenant_id	uuid	yes		tenant concerned, if any; no FK, kept after deletion
request_id	text	yes
source_ip	inet	yes
details	jsonb		'{}'::jsonb
prev_hash	bytea	yes
hash	bytea

Constraints:

• PRIMARY KEY (id)
• CHECK ((actor_kind = ANY (ARRAY['user'::text, 'system'::text, 'idp'::text])))

audit_sink

Export targets. config names the target and never holds credentials; a webhook's Authorization header value is the credential, write-only. A new sink starts after the tenant's latest event unless created with an explicit start; updating config or credential keeps the cursor.

Column	Type	Null	Default	Description
id	uuid
tenant_id	uuid
kind	text
config	jsonb			webhook url; syslog address and tls
credential_id	uuid	yes
cursor_seq	bigint			last exported audit_event.seq of the tenant
last_attempt_at	timestamp with time zone	yes
last_error	text	yes		of the last attempt; NULL after a success
created_at	timestamp with time zone		now()

Constraints:

• PRIMARY KEY (id)
• FOREIGN KEY (tenant_id) REFERENCES tenant(id)
• FOREIGN KEY (tenant_id, credential_id) REFERENCES sealed_value(tenant_id, id)
• CHECK (((kind = 'webhook'::text) OR (credential_id IS NULL)))
• CHECK ((kind = ANY (ARRAY['syslog'::text, 'webhook'::text])))

Row-level security (forced): tenant_isolation.

operation

Long requests (validation, catalog import, tenant deletion) answer 202 with an operation. The work runs as an absurd task with the operation's id (schema "absurd", docs/stack.md), whose steps checkpoint in the same transaction as their writes here. This row is what the API shows; absurd's tables carry no tenant and are never read by request handlers. An operation stays queued, without a task, while its tenant has as many operations of its kind running as its limit allows (docs/architecture.md §12). tenant_id NULL: platform operation, visible to platform credentials only. One operation of a kind runs per object: requesting it again while it is queued or running returns that operation. The operation keeps its creator and the creator's token restriction and rechecks them before each privileged step (docs/authorization.md §6). Finished operations are deleted after 24 h.

Column	Type	Null	Default	Description
id	uuid
tenant_id	uuid	yes
kind	text			e.g. change_set.validate, catalog.import, tenant.delete
object_id	text			what it works on (change set id, bundle hash, tenant id)
state	operation_state		'queued'::operation_state
progress	real	yes
result	jsonb	yes
error	jsonb	yes		problem details
created_by	uuid	yes
created_at	timestamp with time zone		now()
finished_at	timestamp with time zone	yes

Constraints:

• PRIMARY KEY (id)
• FOREIGN KEY (created_by) REFERENCES app_user(id)
• FOREIGN KEY (tenant_id) REFERENCES tenant(id)
• CHECK (((progress >= (0)::double precision) AND (progress <= (1)::double precision)))
• CHECK (((state = 'failed'::operation_state) = (error IS NOT NULL)))
• CHECK (((state = ANY (ARRAY['succeeded'::operation_state, 'failed'::operation_state])) = (finished_at IS NOT NULL)))

Indexes:

• operation_active UNIQUE USING btree (kind, object_id) WHERE (state = ANY (ARRAY['queued'::operation_state, 'running'::operation_state]))
• operation_finished USING btree (finished_at) WHERE (finished_at IS NOT NULL)
• operation_tenant USING btree (tenant_id, kind, state)

Row-level security (forced): tenant_isolation.

idempotency_key

Idempotency-Key of creating POSTs (docs/openapi.yaml), kept 24 h per principal and tenant (or platform). A request is authenticated first (credential validity, CSRF, an enabled user with an active membership and a role in the tenant, or a platform admin); then its key is claimed by inserting the row in a transaction that stays open while the request runs and stores the successful response (status, body, Content-Type, ETag, Location) before it commits. A concurrent repeat waits on the unique index until that transaction ends; a failed or streamed response is not stored, so its repeat runs again. A repeat returns the stored response; a repeat with a different request is rejected. Requests that return a credential take no key, so no stored response ever holds one.

Column	Type	Null	Default	Description
principal_id	uuid			user or service account
key	text
tenant_id	uuid	yes		NULL for platform requests
request_hash	bytea			sha256 over method, path and body
response_status	integer
response_body	bytea
response_headers	jsonb		'{}'::jsonb	content-type, etag, location
created_at	timestamp with time zone		now()

Constraints:

• UNIQUE NULLS NOT DISTINCT (principal_id, tenant_id, key)
• FOREIGN KEY (tenant_id) REFERENCES tenant(id)
• CHECK ((length(key) <= 255))

Indexes:

• idempotency_key_created USING btree (created_at)

Row-level security (forced): tenant_isolation.

blob_delete

Objects whose rows are gone, deleted by the blob sweeper. Object keys are derived from row ids, so a retried upload overwrites its own object.

Column	Type	Null	Default	Description
key	text			object key, or a prefix ending in '/'
enqueued_at	timestamp with time zone		now()

Constraints:

• PRIMARY KEY (key)

journey_receipt

Every accepted result, by its run id (UUIDv7 chosen by the runner), kept 24 h: the deduplication record. A result whose run id is already here is a duplicate whatever its content; the runner drops results older than 24 h, and the server rejects them (expired).

Column	Type	Null	Default	Description
tenant_id	uuid
run_id	uuid
agent_instance_id	uuid
received_at	timestamp with time zone		now()

Constraints:

• PRIMARY KEY (tenant_id, run_id)
• FOREIGN KEY (tenant_id, agent_instance_id) REFERENCES agent_instance(tenant_id, id) ON DELETE CASCADE

Indexes:

• journey_receipt_received USING btree (received_at)

Row-level security (forced): tenant_isolation.

journey_run

Every run (journeys.md §6): failed and error runs up to failedRunsKept per journey, passed ones up to passedRunsKept; evidence only for failures. Scheduled runs also update journey_state, manual runs (journeys.md §3.1) never do. id is the run id (journey_receipt.run_id).

Column	Type	Null	Default	Description
id	uuid
tenant_id	uuid
journey_name	text
agent_instance_id	uuid
revision_id	bigint
script_hash	bytea			sha256 of the script that ran
result	journey_result
message	text	yes		failing assertion or error
steps	jsonb		'[]'::jsonb	[{name, offset_ms, duration_ms, result, error, line}]
output	text	yes		print/console output, at most 64 KiB
started_at	timestamp with time zone
duration_ms	integer
received_at	timestamp with time zone		now()
triggered_by	uuid	yes		manual runs: who ran it; NULL for scheduled runs

Constraints:

• PRIMARY KEY (id)
• UNIQUE (tenant_id, id)
• FOREIGN KEY (tenant_id, agent_instance_id) REFERENCES agent_instance(tenant_id, id) ON DELETE CASCADE
• FOREIGN KEY (tenant_id, revision_id) REFERENCES spec_revision(tenant_id, id)
• FOREIGN KEY (triggered_by) REFERENCES app_user(id)

Indexes:

• journey_run_journey USING btree (tenant_id, journey_name, started_at DESC)
• journey_run_result USING btree (tenant_id, journey_name, ((result = 'passed'::journey_result)), started_at DESC)

Row-level security (forced): tenant_isolation.

journey_state

Latest result and counters per journey and runner; the source of the strataplane_journey_* series on the tenant's metrics endpoint (identical on every replica). Each receipt increments the counters once; the latest-run fields change only for a run that started after the stored one, so a delayed upload is counted without moving the state back.

Column	Type	Null	Default	Description
tenant_id	uuid
journey_name	text
agent_instance_id	uuid
revision_id	bigint
script_hash	bytea
result	journey_result
message	text	yes
started_at	timestamp with time zone
duration_ms	integer
steps	jsonb		'[]'::jsonb	[{name, duration_ms, result}] of the latest run, for step metrics
failing_since	timestamp with time zone	yes		first non-passed run of the current streak
last_failed_run_id	uuid	yes
runs_passed	bigint		0
runs_failed	bigint		0
runs_error	bigint		0
runs_skipped	bigint		0	missed or overlapping slots, as reported by the runner
updated_at	timestamp with time zone		now()

Constraints:

• PRIMARY KEY (tenant_id, journey_name, agent_instance_id)
• FOREIGN KEY (tenant_id, agent_instance_id) REFERENCES agent_instance(tenant_id, id) ON DELETE CASCADE
• FOREIGN KEY (tenant_id, last_failed_run_id) REFERENCES journey_run(tenant_id, id) ON DELETE SET NULL (last_failed_run_id)
• FOREIGN KEY (tenant_id, revision_id) REFERENCES spec_revision(tenant_id, id)

Row-level security (forced): tenant_isolation.

journey_evidence

Screenshot and accessibility snapshot of the failing step of a run, stored as the object tenants//evidence//. The 2 MiB cap per run is enforced on upload. Pruned with the run.

Column	Type	Null	Default	Description
tenant_id	uuid
run_id	uuid
idx	integer
step	integer			index into journey_run.steps
kind	text
media_type	text
size	integer
hash	bytea

Constraints:

• PRIMARY KEY (run_id, idx)
• FOREIGN KEY (tenant_id, run_id) REFERENCES journey_run(tenant_id, id) ON DELETE CASCADE
• CHECK ((kind = ANY (ARRAY['screenshot'::text, 'snapshot'::text])))
• CHECK ((media_type = ANY (ARRAY['image/png'::text, 'image/jpeg'::text, 'application/json'::text])))

Row-level security (forced): tenant_isolation.

explore_session

Requests of a user to a runner: a page on an exploration runner that the user, or the assistant acting as that user, drives ('session'), one trial run of a Journey file ('try', both docs/journeys.md §8.2), or one manual run of a deployed journey ('run', journeys.md §3.1). A session closes on request, after 10 minutes without a request, after 30 minutes, when the runner no longer holds it, or when its runner is deleted; a trial or manual run closes with its answer. Rows are deleted an hour after closing.

Column	Type	Null	Default	Description
id	uuid
tenant_id	uuid
agent_instance_id	uuid			the exploration runner
kind	text
created_by	uuid
start_url	text
allow_hosts	text[]		'{}'::text[]
created_at	timestamp with time zone		now()
last_used_at	timestamp with time zone		now()
closed_at	timestamp with time zone	yes
close_reason	text	yes

Constraints:

• PRIMARY KEY (id)
• UNIQUE (tenant_id, id)
• FOREIGN KEY (created_by) REFERENCES app_user(id)
• FOREIGN KEY (tenant_id) REFERENCES tenant(id)
• FOREIGN KEY (tenant_id, agent_instance_id) REFERENCES agent_instance(tenant_id, id) ON DELETE CASCADE
• CHECK (((closed_at IS NULL) = (close_reason IS NULL)))
• CHECK ((close_reason = ANY (ARRAY['stopped'::text, 'idle'::text, 'expired'::text, 'runner_lost'::text, 'failed'::text, 'done'::text])))
• CHECK ((kind = ANY (ARRAY['session'::text, 'try'::text, 'run'::text])))

Indexes:

• explore_session_closed USING btree (closed_at) WHERE (closed_at IS NOT NULL)
• explore_session_open USING btree (tenant_id, agent_instance_id) WHERE (closed_at IS NULL)

Row-level security (forced): tenant_isolation.

explore_request

One request to the runner and its answer; a session has at most one unanswered request. The runner claims pending requests with its long-poll and answers each once; NOTIFY agent_actions with the runner id wakes the poll, with the request id the waiting API call. answer: outcome, output, page URL, title and snapshot, screenshots base64; at most 8 MiB.

Column	Type	Null	Default	Description
id	uuid
tenant_id	uuid
session_id	uuid
agent_instance_id	uuid
op	text
body	jsonb		'{}'::jsonb
created_at	timestamp with time zone		now()
claimed_at	timestamp with time zone	yes
answered_at	timestamp with time zone	yes
answer	jsonb	yes

Constraints:

• PRIMARY KEY (id)
• FOREIGN KEY (tenant_id, session_id) REFERENCES explore_session(tenant_id, id) ON DELETE CASCADE
• CHECK (((answered_at IS NULL) = (answer IS NULL)))
• CHECK ((op = ANY (ARRAY['open'::text, 'do'::text, 'observe'::text, 'close'::text, 'try'::text, 'run'::text])))

Indexes:

• explore_request_pending USING btree (tenant_id, agent_instance_id, created_at) WHERE (claimed_at IS NULL)
• explore_request_session USING btree (tenant_id, session_id)

Row-level security (forced): tenant_isolation.