Data model

Postgres 15+ via Cloud SQL in prod. Alembic for migrations. Every table has created_at, updated_at, and a UUIDv7 primary key (time-ordered — helps indexing).

Tables grouped by purpose

Tenants & identity

Table	Purpose
tenants	One row per customer org
users	Humans with SSO identity
agents	Registered agent identities (one tp_ key maps to one agent)
api_keys	Hash-stored tp_ keys; one row per issued key
sessions	Browser sessions for SSO-authenticated humans
oauth_identities	Links users to their IdP subject claims
capability_tokens	Issued capability tokens (metadata; signature not stored)

The key rule: everything in the data path is scoped by tenant_id at the top of the request. Cross-tenant reads are a bug.

Pipeline config

Table	Purpose
policy_profiles	Named config blobs (alpha / beta / default / per-customer)
step_configs	Per-tenant overrides for individual steps
provider_configs	Per-tenant LLM provider bindings (model → provider mapping)
vault_secrets	Encrypted provider keys (the “vault backend: postgres” storage)

Audit

Table	Purpose
audit_events	The append-only, hash-chained, signed event stream
audit_checkpoints	Signed checkpoint every N events
audit_archives	Pointer rows for GCS cold-storage dumps

audit_events is the single most important table. See Audit trail internals.

Observability

Table	Purpose
export_configs	Per-tenant SIEM / webhook destinations
export_deliveries	Delivery attempts (for DLQ + retry)

Trust & scoring

Table	Purpose
agent_health_snapshots	Daily rollup per agent — score, grade, dimensions
trust_events	Fine-grained signals feeding the score

Denormalisation you should know about

Some fields are intentionally denormalised for query speed. Don’t “fix” them:

• audit_events.agent_label — copied from agents.label so an agent rename doesn’t rewrite history
• audit_events.model — copied because provider_configs can be updated live
• agent_health_snapshots.score — computed, not a view, so Grafana queries stay fast

Migrations

alembic/versions/ — one file per migration, numbered + slug.

Rules:

1. Forward-only. We don’t use downgrade() in prod. Writing one is fine for local testing; never rely on it.
2. Zero-downtime. Two-phase pattern for any non-additive change. See Database & migrations.
3. Never drop a column in the same migration that stops writing to it. Add → stop-write → remove, three releases.
4. Index creation uses CREATE INDEX CONCURRENTLY. Alembic supports it via op.execute (not op.create_index, which locks).

Indices worth knowing

• audit_events (tenant_id, ts DESC) — powers the dashboard
• audit_events (agent_id, ts DESC) — powers health scoring
• audit_events (event_kind, ts DESC) WHERE severity >= 'warn' — partial index for alerts
• api_keys (key_hash) — lookup on authentication hot path
• capability_tokens (holder_public_key) — capability verification

Not in Postgres

These intentionally don’t live in the main DB:

• Secrets. Vault table rows hold ciphertext; keys live in Google Secret Manager.
• Archived audit. > 90 days goes to GCS (gs://tappass-audit-archive/).
• Metrics. Live in Prometheus / Grafana Cloud, not DB.
• Session tokens for customer agents. Agents are stateless; no server-side session.

Useful read-only queries

Get an agent’s last 50 blocks:

SELECT audit_id, ts, detections, policy_result
FROM audit_events
WHERE tenant_id = :tenant
  AND agent_id = :agent
  AND policy_result->>'verdict' = 'deny'
ORDER BY ts DESC
LIMIT 50;

Daily cost per tenant last 7 days:

SELECT date_trunc('day', ts) AS day,
       SUM((cost->>'usd')::numeric) AS usd
FROM audit_events
WHERE tenant_id = :tenant
  AND ts > now() - interval '7 days'
GROUP BY 1
ORDER BY 1;

PII detections by category last 30 days:

SELECT jsonb_array_elements(detections)->>'category' AS category,
       COUNT(*)
FROM audit_events
WHERE tenant_id = :tenant
  AND ts > now() - interval '30 days'
  AND detections IS NOT NULL
GROUP BY 1
ORDER BY 2 DESC;