Schema overview

Status: Current | Last reviewed: 2026-10-20

How the database schema is structured, applied, and changed.

076_agent_testing_workspace.sql adds the Athens test entity/evidence model and separate agent_test_preparations storage while retaining Vanguard's legacy tables and consumers. 077_agent_testing_comparisons.sql adds immutable comparison points and paired runs with tenant/agent foreign keys and RLS. Both are registered for populated city databases and included in the canonical baseline. See agent testing workspace.

Customer components use existing conversations.metadata keys: customer_components on the customer and customer_component_target on the representative. The link stores allowed catalogues, current screen, revision and idempotency receipts. Immutable screens and replies are conversation_events rows (ui_render and chat_event); no SQL migration or data reset is required.

074_director.sql adds tenant-scoped scripted recording projects, generated audio assets and export snapshots. Project saves compare revisions; child assets and exports enforce tenant/project identity through composite foreign keys. See Director.

069_analytics_preparation.sql adds encrypted-file manifests, leased compile items, model annotations and pgvector chunks to Analytics. Dataset provenance separates real and synthetic sources. Existing pilot releases remain readable. Apply the forward migration without resetting data; the same schema is folded into the baseline. See Analytics.

066_capability_test_events.sql adds saved-case testing, attempt capacity_units, durable agent_sim_events and real supervisor agent_sim_participants. Both child tables enforce tenant matching to their attempt and tenant RLS. Callback identities are unique per attempt; participants are unique per attempt/role. Existing databases apply the forward migration; the canonical baseline contains the same definitions for new installations.

The baseline also includes migration 063's demo project tables, tenant policies and expanded designer session constraints. Fresh installations and forward-migrated databases therefore accept the same demo and Copilot designs.

The squash (2026-06-20)

The schema was squashed: the historical 000 snapshot + ~118 incremental 1XX migrations were collapsed into a single audited baseline. The old 000_full_schema.sql was a stale pg_dump that still created objects later migrations dropped/renamed, and replaying the full chain on a fresh DB even errored (idempotency guards in 103/104/121/130 trip because 000 already reflected their end-state) — db-init-local.sh swallowed those with || true. The squash made the baseline byte-equivalent to what the chain actually produced, then applied the agent_id/workflow_id renames on top. See rename-map.md and the audit at scripts/schema-audit/.

Layout

deploy/init-scripts/:

File	Role
000_full_schema.sql	The single SQUASHED core baseline — all tables, views, functions, indexes, constraints, triggers, types. CI-drift-guarded.
001_reference_data.sql	Platform reference data (full integration_types/tool_types set + default tenant + dev origins). Idempotent.
002_tsf_app_bypassrls.sql	Grants tsf_app BYPASSRLS for backend services.
099_normalize_user.sql	Strips SUPERUSER (prod RLS fidelity); runs last; skipped in dev.
0NN_*.sql	Forward-only migrations on top of the baseline (see below).
seed-demo/*.sql	Opt-in demo schema + data (Salesforce demo, carrier, fedex, gallery, briefing). Applied only when TSF_SEED_DEMO is set (default ON in dev).
archive/*.sql	Frozen pre-squash history. Never applied. See archive/README.md.

• 0 application-side migration runners. No Alembic, Prisma, Knex, or Flyway.
• 1 authoritative baseline; everything new is a forward-only migration.

The forward-only scripts

Scripts 003–061 carry config-JSONB normalizations, feature tables, and retirements such as the destructive Headless cleanup. The authoritative, ordered list is the for bn in ... loop in migrate-city-db.sh Phase C, where each is annotated with why it exists. The tail carries per-feature contracts other docs reference:

Script	Adds
027_customer_auth.sql	customer_identities + WebAuthn credentials, challenges, and auth events — see Customer Authentication.
028_a2ui_catalog_type.sql	a2ui_catalogs.catalog_type — the multimodal \| agent_assist discriminator that decides whether catalog-builder blocks publish on a non-strict props_schema.
029_copilot_cards_catalog.sql	The platform copilot-cards catalog row with catalog_type='agent_assist'. It is a schema script rather than a UI action because the type must be on the row before the first publish — POST /publish?bySlug=true only resolves an existing row, and every other creation path defaults to multimodal, i.e. gate off. It also repairs an existing row with the wrong type.
030_payments.sql	payment_transactions / payment_events plus the guards that keep cardholder data out of both — see Payments.
031_conversation_message_variants.sql	The conversation_messages variant column reasoning/translation transcripts key on.
032_krisp_translation_capability.sql	The translation capability on existing Krisp VIVA integration rows.
033_copilot_config_migration.sql	Converts hand-wired Agent Assist configs to the prescriptive config.copilot branch — see Copilots. Data-only: it touches agent_versions.config and the tpl-copilot-* templates, so the baseline is unchanged and it is a clean no-op on a fresh boot.
034_agent_assist_profiles.sql	Tenant-scoped draft/published profiles for record-backed and hybrid CRM Agent Assist rails — see Record-backed Agent Assist profiles.
035_agent_assist_catalog_profiles.sql	Changes the new-profile default to catalog inheritance while preserving existing card-level JSON for runtime compatibility.
036_email_copilot_catalog.sql	Registers the TrueServe-owned email-copilot Agent Assist catalog before its committed Translation and Draft Response components are published.
037_copilot_translation_engine.sql	Promotes inline transcript translation out of copilot.types[] into its own copilot.translation section, and stamps copilot.engine on Google-Agent-Assist copilots.
040_mock_payments_sepa.sql	The mock_payments integration type plus SEPA: sepa_debit on the method_type CHECK and bic on the masked-details allowlist.
041_payments_sweeper_spares_accepted.sql	Stops cleanup_stale_payment_transactions() cancelling a payment the provider already accepted.
042_payment_transactions_delete_guard.sql	A DELETE trigger on the charge record — 030's grant-based protection was a no-op.
043_agent_templates.sql	The 12 builtin agent templates (agents + their version-0 drafts). Reference data, re-homed here because the squash left it behind in archive/101_agent_templates_and_tags.sql: every database created after 2026-06-20 had no templates, so "Build with Template" was an empty grid and "Generate with AI" silently fell back to the bare-default path. Regenerate with scripts/schema-audit/dump-agent-templates.sql.
044_add_cost_estimation.sql	Versioned public/tenant price books and rates, published assumption sets with subject overlays, immutable estimate snapshots, durable usage events, and reconciliation runs — see Cost estimation and usage attribution.
045_capability_definitions.sql	Tenant and shared capability registry.
046_oauth2_api_integration_type.sql	Generic OAuth 2.0 client-credentials integration type.
047_a2ui_component_emits.sql	Declared A2UI component output events.
048_compliance_control_plane.sql	Immutable profile revisions, tenant assignments, typed exceptions, policy decisions, artifact and runtime pins, hash-chained audit events, delivery state, and RLS.
049_privacy_rights.sql	Privacy requests, request events, legal holds, erasure tombstones, opt-outs, and subject HMAC lookup.
050_ai_governance.sql	AI inventory, risk assessments, evaluation runs, release approvals, and immutable artifact evidence pins.
051_runtime_governance_audit_bridge.sql	Copies AI evidence to runtime rows and bridges operational audit records into the immutable chain.
052_compliance_invariants.sql	Database checks for assignment, exception, actor, tenant, subject, evaluation, and independent approval invariants.
053_privacy_rights_invariants.sql	Full privacy-rights types, immutable deadlines, tenant relationship checks, and the allowed request state machine.
057_conversational_designer.sql	Designer session state, revisions, request keys and tenant-isolated turn admission; see Conversational design.
058_designer_sources_activity.sql	Durable public turn activity and private image/text reference metadata.
059_designer_build_handoff.sql	Explicit build-turn intent, approved design snapshot, native artifact receipt and checked editor handoff.
060_designer_questions.sql	Saved designer question forms and submitted answers, kept separate from executable configuration.
061_openai_authoring.sql	Pinned authoring runner/integration, provider deletion timestamps and tenant-scoped durable function-call receipts; see OpenAI authoring.
062_authoring_execution.sql	Optional authoring model/effort/transport snapshot on generation jobs for repeatable benchmarks and consistent resume.
063_studio_demo_projects.sql	Tenant-scoped demo projects, native component membership, journey observations and Copilot/Multi-agent design types; see Multi-agent demos.

The integration-dependent pharma demo workflows are opt-in seeds: seed-demo/138_pharma_email_operations.sql and seed-demo/139_approved_email_translation.sql. They intentionally fail when their live OpenAI, AWS, and Salesforce integrations are absent, so they must never sit in the core migration glob or Phase C.

A forward script whose work is data, not DDL, still needs the Phase C registration — see migration-conventions.md. It is also invisible to the drift guard, which dumps --schema-only: nothing in CI notices that a seed went missing. 043 is the worked example — see Reference data is not covered by the drift guard.

How it is applied

db-init-local.sh globs [0-9][0-9][0-9]_*.sql (so 000→002→future→099), then optionally the seed-demo/*.sql set. The same glob drives scripts/db-init.sh and the chart's postgres-init ConfigMap (migrations/0*.sql, non-recursive — it never reaches seed-demo/ or archive/). See reset-local-db runbook.

In clusters, scripts apply via the dbMigration Helm hook job (db-migration-job.yaml) against managed Postgres. With the baseline now audit-clean, the file-replay bootstrap path is viable again — see deployment/DATABASE_BOOTSTRAP.md §3.

Populated city databases use migrate-city-db.sh, not the fresh-boot glob. The helper detects whether agent_versions/workflow_versions already use the squashed *_id layout. Squashed databases skip archived replay and the rename phase entirely; replaying the archive there can resurrect objects the squash removed. A pre-squash floor runs the archive first, but only the four documented legacy-floor failures are tolerated and printed—every other SQL error stops the migration. Active forward migrations then run with ON_ERROR_STOP=1 in both layouts.

Drift guard

A fresh boot of the init-scripts must re-pg_dump to byte-equal the committed 000_full_schema.sql. schema-drift.yml enforces this on every PR touching deploy/init-scripts/, plus an idempotency check that applies forward migrations twice and a legacy-column grep guard. The comparison uses sanitize-schema-dump.sh to normalize pg_dump banner/header noise. CI boots pgvector/pgvector:pg15 rather than vanilla Postgres because the baseline creates the vector extension used by knowledge retrieval.

Regenerating the baseline after a deliberate schema change

# 1. Author + apply a forward migration (NNN = next number after the highest active file)
bash .devcontainer/db-init-local.sh                 # boots 000+001+002+your NNN
# 2. Re-squash into the baseline (exclude demo tables; they stay in seed-demo/)
docker exec tsf-local-postgres pg_dump -U tsf_app -d trusrv_agents \
  --schema-only --no-owner --no-privileges --no-comments \
  | scripts/schema-audit/sanitize-schema-dump.sh \
  > deploy/init-scripts/000_full_schema.sql

Re-prepend the human header banner (the lines before -- PostgreSQL database dump).

Naming convention

<NNN>_<short_description>.sql

NNN is a three-digit sequence number, one greater than the highest active file. Short description is snake_case, no dates. See migration-conventions.md.

Why no app-side migrations

Two reasons:

1. Multi-language polyglot — Python, TS, and Go services all read the same schema. Picking one language's migration tool creates an asymmetry.
2. Deploy-time control — operators want to see, review, and apply schema changes on their own clock, not as a side-effect of an app pod starting.

The downside is no per-service ownership of schema. The mitigation is tenant_id and feature-flag isolation: each service writes only to the tables it owns.

Where the JSONB lives

Three columns carry the bulk of the runtime config:

Column	Type	Owned by
agent_versions.config	JSONB	ai-manager writes; ai-orchestrator reads
workflow_versions.config	JSONB	ai-manager workflows tab writes; async-agent reads
integrations.config	JSONB	ai-manager writes (non-secret config); all services read

Integration secrets are not in config — they live in the separate integration_secrets table (encrypted_value/iv/auth_tag, using managed KMS data-key envelopes in regulated deployments); tool secrets likewise in tool_secrets. See secret-storage.md.

Management OAuth credentials live in management_oauth_clients, management_oauth_authorization_codes, management_oauth_token_families, and management_oauth_tokens (migration 021_management_oauth.sql). Codes and tokens are stored only as SHA-256 hashes; token families carry the tenant, resource URI, scopes, expiry, and revocation state. Do not reuse the older plaintext oauth_* tables for management API or MCP grants.

The same migration adds assistant_sessions.access_mode. Advisory management questions use read, while the editor Assistant uses edit; the active-session uniqueness key includes this mode so the two can coexist without sharing tools.

Migrations 057_conversational_designer.sql and 058_designer_sources_activity.sql add a separate designer surface, revisioned visual state, durable turn admission/public activity and private reference metadata. References and turns have composite tenant/session foreign keys and forced RLS; routes additionally enforce creator and kind permissions. Reference bytes live in the assets object store, not agent configuration. See Conversational design.

The TypeScript shape for agent_versions.config lives in pipeline-types.ts. The shape for workflow_versions.config lives in workflow-types.ts and the shared registry in shared/workflow-node-types.

Auditing your changes

Before opening a PR with a new migration:

# Lint syntax
psql "$DATABASE_URL" -f deploy/init-scripts/<your-file>.sql --dry-run 2>&1 | tee /tmp/lint.log

# Verify it is idempotent (run twice; second run should be a no-op)
psql "$DATABASE_URL" -f deploy/init-scripts/<your-file>.sql
psql "$DATABASE_URL" -f deploy/init-scripts/<your-file>.sql

# Confirm RLS still works for tenant-scoped tables
psql "$DATABASE_URL" -c "SET LOCAL app.current_tenant = '00000000-0000-0000-0000-000000000000'; SELECT count(*) FROM <new_table>;"

See migration-conventions.md for the full checklist.

See also

• Tables
• SQL functions
• Migration conventions
• Cleanup proposals

Agent evaluation storage

044_agent_sim_batches.sql adds Performance batches. 064_agent_evaluation.sql adds immutable tool_mock_revisions, durable agent_sim_jobs, fenced agent_sim_attempts and operator-managed agent_sim_capacity. Batch targets hold one pinned configuration/rubric snapshot; jobs store only persona/repetition data. Attempt evidence and fixture state are isolated per conversation. See Agent evaluation.

065_agent_test_cases.sql adds tenant-scoped agent_test_cases: reusable caller definitions, expected outcomes, an optional owned golden, turn limits and optimistic revisions. Selected definitions and golden examples are copied into agent_sim_batches.spec.test_cases; jobs record test_case_id and test_case_revision. Editing or removing a source definition never rewrites a run's snapshot. Latest-result reads include the revision so an edited test cannot inherit an old passing label.