Database schema¶

Ampora uses Entity Framework Core with PostgreSQL. The migrations in src/Ampora.Persistence/Migrations are the source of truth. This page is a high-level orientation — names, purposes, relationships — to help you read the schema, not a substitute for the migration code.

Conventions¶

All timestamps are timestamptz and stored in UTC.
Primary keys are UUID v4 unless otherwise noted.
Multi-tenant tables carry tenant_id (UUID, indexed).
Soft-deletable tables carry deleted_at_utc (nullable).
Optimistic-concurrency tables carry row_version (bytea).
Encrypted-at-rest fields end in _encrypted, _nonce, _tag, _scheme — the AES-GCM envelope.

Domain tables¶

Tenants and users¶

Table	Purpose
`tenants`	One row per tenant; theme, branding, defaults
`tenant_identity_mappings`	OIDC claim → role mapping per tenant
`users`	Cached profile of OIDC subjects we have seen

Agents and identities¶

Table	Purpose
`agent_identities`	Issued client certs; bound or unbound to an agent
`agents`	Connected (or formerly connected) agents
`agent_descriptions`	Latest `AgentDescription` per agent (JSONB labels)
`agent_status_history`	Append-only event timeline
`agent_session_ownership`	Which Ampora instance owns each WebSocket session (with fencing token)
`agent_package_statuses`	Per-package state per agent (when `ReportsPackageStatuses`)

Configurations¶

Table	Purpose
`configurations`	Containers; one per logical config
`configuration_versions`	Versions per container; immutable when published
`configuration_lints`	Lint findings per version

Groups¶

Table	Purpose
`groups`	Static or Dynamic; soft-deletable
`group_memberships`	Static membership; single-group invariant enforced via partial unique index
`group_dynamic_selectors`	DSL expression and compiled AST per dynamic group

Rollouts¶

Table	Purpose
`rollouts`	One row per rollout
`rollout_batches`	Per-batch state
`rollout_targets`	Per-agent state in a batch
`rollout_gate_decisions`	Audit of gate evaluations

Audit¶

Table	Purpose
`audit_events`	Hot table
`audit_event_archive`	Same shape, fewer indexes

PKI¶

Table	Purpose
`ca_signing_keys`	Draft / Active / Trusted / Retired keys (private material AES-GCM-wrapped or HSM handle)
`crl_publications`	Latest signed CRL blob per active key
`ocsp_signers`	Per-active-key OCSP signer cert + key

Tokens¶

Table	Purpose
`bootstrap_tokens`	Single-use bootstrap tokens (hashed)
`token_pools`	Token-issuance pools
`pat_tokens`	Personal Access Tokens for API automation

Governance¶

Table	Purpose
`policies`	Built-in and custom policies
`policy_versions`	Approval-flow versions of custom policies
`lint_rules`	Tenant-scoped lint rules

GitOps¶

Table	Purpose
`git_config_sources`	Registered repos with wrapped credentials
`git_sync_runs`	Sweep history (30-day retention)

Federation¶

Table	Purpose
`federation_peers`	Inbound + outbound auth material per peer
`federation_handover_tokens`	Cross-cluster handover tokens
`federated_rollouts`	Coordination rows for cross-peer rollouts

Infrastructure¶

Table	Purpose
`leader_leases`	Named leader leases for background services
`dispatch_envelopes`	Cross-instance dispatch payloads
`live_update_events`	Optional persistence for the live-update bus (Redis backplane uses pub/sub instead)

Indexes¶

Beyond the obvious primary-key and foreign-key indexes, the performance-critical ones are:

agents — (tenant_id, last_seen_utc DESC), (tenant_id, deleted_at_utc).
agent_descriptions — JSONB GIN with jsonb_path_ops (used by dynamic group selectors).
audit_events — (tenant_id, timestamp_utc DESC), (entity_type, entity_id, timestamp_utc DESC).
rollout_targets — (rollout_id, batch_index, status) for the per-batch progress query.
agent_session_ownership — (agent_id, fencing_token DESC).

Row-Level Security¶

When MultiTenant:Mode = HardIsolation, RLS policies restrict every multi-tenant table to rows where tenant_id = current_setting('app.tenant_id')::uuid. Ampora sets SET LOCAL app.tenant_id = '<uuid>' per request.

Migrations¶

ls src/Ampora.Persistence/Migrations

Each migration has a Up method only. Forward-only — see Operator → Database & migrations.

Reading the live schema¶

psql -h db.acme.io -U ampora -d ampora -c '\d agent_descriptions'

The \d command lists the live shape. For an EF-perspective view, the __EFMigrationsHistory table records which migrations have been applied.