Tables
Status: Current | Last reviewed: 2026-10-07
The tables you will actually touch when working in this codebase, with their status. For the full list, grep CREATE TABLE across deploy/init-scripts/.
Status values:
current — actively read and written.support — actively used but you should not touch directly (Asterisk PJSIP, sync tables).view — Postgres view, not a base table.legacy — kept for backfill or reads only; do not write.dropped — gone; if a doc still references it, the doc is stale.
Identity and tenancy
| Table |
Status |
Purpose |
tenants |
current |
Multi-tenancy root. slug is the URL- and FS-safe identifier. |
users |
current |
Auth + role assignments. is_platform_admin bypasses RLS on read paths. |
tenant_memberships |
current |
User-to-tenant roles plus direct tenant permissions synced from Authentik. |
api_tokens |
current |
Hashed tsf_* bearer tokens. token_type distinguishes tenant tokens from personal tokens; api_token_audit_log records use. |
oauth_apps, oauth_grants, oauth_tokens |
current |
OAuth2 grant tables. cleanup_expired_oauth_tokens() runs periodically. |
Agents
| Table |
Status |
Purpose |
director_projects |
current |
Authored voice-agent recording scripts, scene timelines, voices, component data and optimistic save revisions. |
director_assets |
current |
Project-owned generated voice/sound takes and private object references. |
director_exports |
current |
Frozen recording snapshots, progress, cancellation and private MP4 references. See Director. |
agents |
current |
Identity and team-wide flags: id, name, description, surface_config_id, tenant_id, debug_enabled. Read data model before touching. |
agent_versions |
current |
Runtime config JSONB. PK (agent_id, version_number) — agent_id is the bare FK to agents.id (renamed from base_agent_id in the 2026-06-20 squash). is_current_published is unique per agent. |
agent_version_snapshots |
current |
Audit trail of config changes. Append-only. |
assistant_sessions |
current |
Scoped editor/designer conversations, CLI resume IDs and saved UI history. Designer rows carry typed visual output, source draft revision, approved build snapshot/artifact receipt, checked handoff timestamp and saved pending/answered question form (design_questions). |
assistant_design_turns |
current |
Durable designer request IDs, design/build intent, running-turn leases, terminal status and bounded public activity. Tenant/session foreign keys and RLS prevent cross-tenant attachment. |
assistant_design_sources |
current |
Private image/text reference metadata: creator, tenant/session, object key, hash, media type, size and last-shared timestamp. Contents use the private assets store. See Conversational design. |
agent_integrations |
current |
Link agents to integrations (Genesys, Salesforce, etc.). |
agent_skills |
current |
Tenant-scoped reusable Assistant Agent skills for Antigravity, Codex, and Claude Code. Runtime node config stores selected skill_ids and inline inline_skills; reusable skill rows can also store skill-local inline_tools referenced as inline:<id>. ai-orchestrator expands both into tool schemas and runner skill packages. Added in migration 189; inline_tools added in migration 201. |
teams_agent_configs |
current |
Per-agent Microsoft Teams custom app metadata and bot integration linkage. Added in migration 187. |
agent_configs |
dropped |
Was a view joining agents + agent_versions. Removed in migration 104. |
agents.config (column) |
dropped |
Was a JSONB on agents. Removed in migration 104. Now lives on agent_versions. |
Templates
| Table |
Status |
Purpose |
agents (is_template=TRUE) |
current |
Full agent blueprints the New Agent Wizard clones. Same row shape as a normal agent + template_category / template_metadata. See Agents. |
prompt_templates |
current |
Reusable prompt text. snippet_type = snippet (in-editor picker) or full_template. Tenant-scoped. |
pipeline_templates |
current |
Reusable pipeline building blocks. kind = stage (a ServiceStage, may have subagents) or subagent (a SubagentDef, no nested subagents). config JSONB holds the node. Seeded into pipelines via the editor's "+" picker. Added in migration 179. |
Workflows
| Table |
Status |
Purpose |
workflow_configs |
current |
Identity: id (numeric), name, description, tenant_id; is_template=TRUE marks reusable workflow blueprints. |
workflow_versions |
current |
Config JSONB; same draft/published model as agents. PK (workflow_id, version_number) — workflow_id renamed from base_workflow_id in the 2026-06-20 squash. |
workflow_test_inputs |
current |
Tenant-shared saved tester payloads for a workflow. The context JSONB is the same top-level object sent to the workflow test endpoint. Added in migration 200. |
workflow_runs |
current |
Run history. execution_id FK → workflow_executions(id) (renamed from case_id in migration 177). |
workflow_node_executions |
current |
Per-node execution history backing the run-detail UI; written by async-agent's node_execution_tracer. execution_id correlation column (renamed from case_id in migration 177), plus parent_execution_id/iteration_context for foreach bodies. |
llm_batch_jobs |
current |
One row per (execution_id, node_id, attempt) batch LLM submission. execution_id (renamed from case_id in migration 177). |
workflow_datasets |
current |
Compact manifests for large workflow outputs such as Salesforce Bulk query results. Workflow state carries dataset_id; rows live in object-storage pages. Added in migration 181. |
workflow_dataset_pages |
current |
Per-page object-storage index for gzipped NDJSON dataset artifacts. Added in migration 181. |
salesforce_bulk_query_jobs |
current |
One row per Salesforce Bulk API query node while a workflow is suspended waiting for results. Added in migration 181. |
workflow_fanout_runs |
current |
Durable For Each parent rows for dataset-backed iteration, including body graph, concurrency, retry, and summary counters. Added in migration 181. |
workflow_fanout_items |
current |
Durable For Each item rows, with lease, retry/backoff, output, and error state. Added in migration 181. |
workflow_run_checkpoints |
current |
LangGraph checkpoint state for resumable runs. |
trigger_rules |
current |
Event-to-workflow bindings. Filters: lifecycle phase (pre/post), event type, conversation source. |
workflow_configs.legacy_slug (column) |
dropped |
Residue from migration 130 (text → numeric ID cutover). DROPPED in the 2026-06-20 squash — workflows are addressed by numeric id; nothing read the slug. |
trigger_rules_legacy |
dropped |
Empty, unreferenced legacy table. DROPPED in the 2026-06-20 squash. |
Conversations and messages
| Table |
Status |
Purpose |
conversations |
current |
Per-session record. FK to agents, tenants, surface_configs. Archive columns (archive_state, archive_uri, archived_at, archive_hot_purged_at) track whether hot transcript detail is still present or has moved to object storage. |
conversation_messages |
current |
Per-message detail. Carries role (user/agent/system), text, attachments, embedding vector. |
conversation_recordings |
current |
Audio/video file metadata. storage_path points at the hot object-storage key; archived recordings are listed in the archive manifest after hot detail rows are purged. |
conversation_attachments |
current |
File attachment metadata. Attachments are copied into archive bundles and hot rows are purged with transcript detail only after archive verification succeeds. |
conversation_events |
current |
Pipeline-emitted events (tool calls, A2UI renders, lifecycle). |
conversation_search_documents |
current |
One denormalized, tenant-scoped row per conversation for full-text/trigram search and cursor pagination across global and per-agent history pages. Trigger-maintained from conversations, messages, events, and recordings. |
conversation_archive_sinks |
current |
Tenant archive destinations. integration_id points at credential storage; config stores non-secret S3-compatible settings such as bucket, prefix, endpoint, region, and source bucket. |
conversation_archive_policies |
current |
Disabled-by-default archive policies. Select terminal conversations by tenant, age, test flag, and future JSON filters. |
conversation_archive_jobs |
current |
Worker job rollups for a policy run: requested/running/completed/failed counts. |
conversation_archive_items |
current |
Per-conversation archive state (pending, archiving, archived, failed, purged), retry attempts, manifest, and archive URI. |
teams_conversation_links |
current |
Maps Teams personal-chat conversation IDs to TrueServe conversation IDs for Teams agent resume. Added in migration 187. |
workflow_executions |
current |
A workflow run's long-form execution record (renamed from cases in migration 177; case_* satellite tables → execution_*). The vestigial case-management columns (case_number, customer_name, priority, …) were dropped in the same migration. |
| Table |
Status |
Purpose |
integration_types |
current |
Legacy connector catalog. Seeded in init scripts and retained for type_id compatibility with saved rows/configs. The add-integration picker now uses the ai-manager source/capability catalog instead of this flat list. Per-tenant availability of addable sources is still governed through the tenant integration-type policy by matching source ids or legacy ids. |
integrations |
current |
Tenant-scoped credential rows. source_id names the high-level source, enabled_capabilities controls where the row appears, legacy type_id preserves old saved references, and encrypted_config is AES-256-GCM bytea. |
tools |
current |
Agent-callable actions. May reference an integration for credentials. |
tool_secrets |
current |
Tool-level secrets (auth tokens) separate from integration credentials. |
mcp_tool_transports |
current |
MCP transport configuration per tool. Added in migration 160. |
Customer Authentication
Added in forward-only script 027_customer_auth.sql. All four tables carry tenant RLS (ENABLE + FORCE). Full flow: customer-authentication.md.
| Table |
Status |
Purpose |
customer_identities |
current |
Per-tenant customer CIAM rows: external_ref (CRM key), normalized phone_e164 / lowercased email (unique per tenant — deterministic ANI lookup), phone_verified_at/email_verified_at, status. |
customer_webauthn_credentials |
current |
Passkeys. credential_id BYTEA is globally unique by necessity (a discoverable assertion carries only the credential id; the service re-checks the resolved tenant against the challenge tenant). public_key (COSE), sign_count (clone detection), transports[], aaguid, revoked_at. |
customer_auth_challenges |
current |
Evidence rows and the source of truth for the achieved assurance. method ∈ otp_sms\|otp_email\|passkey\|kba\|oidc; code_hash (peppered HMAC — plaintext codes never stored), webauthn_challenge (single-use), target_masked (never the full target), share_code, expires_at, attempt_count/max_attempts, status ∈ pending\|verified\|failed\|expired\|locked\|superseded. An evidence CHECK requires the method-appropriate column. |
customer_auth_events |
current |
Append-only audit trail (INSERT-only grants + a trigger rejecting UPDATE/DELETE): event type, method, assurance_level, result, actor, IP, UA, masked detail. |
027 also extends session_variables_source_check to allow 'share_code' (a pre-existing insert the old CHECK forbade) and adds cleanup_expired_auth_challenges(). Tenant provider settings live in the tenant_settings key customer_auth (no table).
Surfaces and A2UI
| Table |
Status |
Purpose |
surface_configs |
current |
Customer-facing chat UI and embed branding: colors, logos, avatars, loading text, feature flags, and CSS overrides. |
a2ui_catalogs |
current |
Published component bundles. Source archived alongside the build. scope is shared|tenant, but as of migration 183 all built-ins were rescoped to the TrueServe tenant — scope='shared' is now legal-but-unused. Unique on (scope, COALESCE(tenant_id, sentinel), slug), so tenants can each hold a same-slug catalog. New tenants auto-clone the baselines; init-scripts/152 locks a tenant's slug once it owns catalogs. |
a2ui_catalog_versions |
current |
Per-publish version row. |
a2ui_tools |
current |
Components-as-tools registry. |
a2ui_tool_plugins |
current |
Plugin metadata for components. |
a2ui_catalog_presence |
current |
Real-time presence per catalog editor. Added in migration 091. |
Knowledge and search
| Table |
Status |
Purpose |
knowledge_documents |
current |
Source documents indexed by knowledge-engine. |
knowledge_chunks |
current |
Chunked text + pgvector embeddings + tsvector full-text. |
knowledge_assertions |
current |
Extracted graph claims pending Neo4j sync. |
knowledge_events |
current |
Ingestion event log. |
knowledge_sync_checkpoints |
current |
Resume points for incremental sync. |
knowledge_compiled_artifacts |
current |
Knowledge compiler output. |
Telephony state (Asterisk PJSIP)
| Table |
Status |
Purpose |
ps_aors |
support |
Asterisk PJSIP "address of record". Asterisk owns the schema; do not rename. |
ps_endpoints |
support |
PJSIP endpoint definitions. |
ps_auths |
support |
PJSIP credentials. |
ps_contacts |
support |
Live registered contacts. |
Note: the ps_* tables follow upstream Asterisk naming. They are coupled to the ARA driver. Treat them as Asterisk's, not ours.
Connector-specific
| Table |
Status |
Purpose |
ccaas_pipeline_integrations |
current |
Default-deny allow-list: which CCaaS integrations may route inbound calls to which agents. Checked by ari-controller on every CCaaS call; controlled from the agent publish dialog. Renamed from genesys_pipeline_integrations in 194; the transitional genesys_pipeline_integrations compat view was dropped in the 2026-06-20 squash. |
genesys_agent_mappings |
support |
Maps TrueServe agents to Genesys org artifacts. |
genesys_enrollments |
support |
Per-trunk enrollment state. |
genesys_transfer_rules |
support |
Routing rules for Genesys hand-offs. |
sd_agents |
support |
Salesforce-side agent denormalization. |
webex_oauth_tokens |
support |
Webex token lifecycle (managed by webex-app-manager). |
Dropped in 195: sip_header_mappings, sip_routing_rules, sip_provider_types, telephony_config_snapshots — the never-shipped dynamic header-mapping/routing system. Header extraction is hardcoded in the Asterisk dialplan; sip_provider_endpoints (read live by Kamailio) stays.
Batch and async
| Table |
Status |
Purpose |
batch_runs |
current |
Workflow batch execution history. FKs rewritten in migration 130 (text → int). |
batch_flush_state |
current |
Partial-flush bookkeeping. |
pii_redaction_runs |
current |
PII redaction node history. Added in 2026-05-20. |
Cost estimation and usage
Added in 044_add_cost_estimation.sql.
See Cost estimation and usage attribution
for lifecycle, selector precedence, formulas, and operations.
| Table |
Status |
Purpose |
cost_price_books |
current |
Global public-retail or tenant-contract price-book identity and currency. |
cost_price_book_versions |
current |
Draft/published/retired effective-dated versions with import checksum, verification, and review timestamps. |
cost_rates |
current |
Normalized provider/product/model/region/tier/meter rates, increments, and minimums for one version. |
cost_assumption_sets |
current |
Immutable published low/typical/high forecasting defaults and provenance. |
cost_subject_profiles |
current |
Optimistically locked, sparse per-agent/workflow assumption overrides. |
cost_estimate_snapshots |
current |
Replayable publish/saved/observed estimates pinned to config, assumption, and price versions. |
cost_usage_events |
current |
Append-only normalized provider measurements, idempotency keys, attribution, and active price-version stamps. |
cost_reconciliation_runs |
current |
Invoice-import comparison summaries and variance diagnostics. |
Auth sync
| Table |
Status |
Purpose |
authentik_user_mappings |
support |
Authentik → TrueServe user link. |
authentik_group_mappings |
support |
Authentik group → tenant role mapping. |
User preferences
| Table |
Status |
Purpose |
user_favorites |
current |
Per-user favorites for agents/workflows. entity_type ∈ {agent, workflow}, entity_id is the base id. Backs the card star, dashboard Quick Access. Migration 178. |
user_recent_items |
current |
Per-user recently-opened agents/workflows (upsert on open). Backs list "Recents" + the editor Link Sub-Agent picker. Migration 178. |
Both are tenant-scoped + RLS-isolated via app.current_tenant_id, keyed by users.id.
See also
Studio design integration state
Migration 073 adds studio_notification_states (tenant/user/key primary key,
JSONB action state, updated timestamp) and agent_test_suites (tenant/agent
primary key, source version/hash, behavior fingerprints, case-generation entries,
optimistic revision and timestamps). Both use forced tenant RLS. Suite entries
refer to the existing agent_test_cases; durable runs continue to pin cases in
agent_sim_batches.spec and do not read changing suite metadata when scoring.
The composite agents_tenant_identity_idx supports a tenant-safe suite foreign
key. See Studio rollout.