Skip to content

Schema guide — what the columns mean and who uses them

Status: Current | Last reviewed: 2026-09-01

A developer's data dictionary for the TrueServe Postgres database (trusrv_agents). For each core table it explains what the columns are for and which services read or write them, so you can reason about the blast radius of a change without spelunking the SQL. This is the narrative companion to the terse tables.md catalog, the rename-map.md, and the SQL functions reference.

Conventions (enforced by review + migration-conventions.md): snake_case columns; *_id foreign keys (agent_id, tenant_id); *_at timestamps; is_*/has_* booleans; config/metadata for JSONB blobs; tenant-scoped tables carry tenant_id + RLS. No legacy_*/old_* columns — if you see one, it's a cleanup target (the 2026-06-20 squash removed the last ones).

The services that touch the DB

Service Lang Access Role
ai-manager TS (Next.js) pg, centralized in lib/db*.ts + lib/schema-*.ts The authoring/admin plane — writes agents, workflows, tools, integrations, catalogs, tenants, branding.
ai-orchestrator Python psycopg2 + asyncpg, scattered Real-time agent runtime. Reads agent config via resolve_agent_version(); writes conversation transcripts.
conversation-api Python asyncpg, centralized in db_queries.py Conversation/transcript read + search API; serves surfaces; A2A card host.
async-agent Python asyncpg Workflow runtime. Reads workflow config via resolve_workflow_version(); writes execution state.
agent-host Python asyncpg Tool/MCP execution host; reads tool + workflow config.
ari-controller-node TS pg, centralized in database.ts Telephony bridge. Reads agent_versions, ccaas_pipeline_integrations, integrations.
media-proxy / genesys/webex/five9 connectors (Go) Go database/sql Read agent config / integration config for their channel.
Asterisk (func_odbc.conf) — ODBC, live Reads agent_versions (extension → agent) on every inbound call.
Kamailio (kamailio.cfg) — sqlops, live Reads sip_provider_endpoints + sip_digest_auth only.

The two live, non-app readers (Asterisk, Kamailio) are why a handful of columns/tables must never be renamed without a lockstep config change — see rename-map.md §5.

Agents: identity vs versioned config

The single most important split in the schema. agents = identity; all runtime config lives on agent_versions.config JSONB. Nothing reads agent config off agents (the legacy agents.config column and the agent_configs view were dropped in migration 104).

agents — one row per agent (identity + team-wide flags)

Column Meaning Used by
id (text PK) The agent id. Text, because it appears in URLs, SIP extensions, and embed stamps (e.g. 3023). New ids are short numeric strings allocated from public.agent_id_seq; they are globally unique across tenants, not tenant-local counters. everyone
name, description Display metadata. ai-manager (write), surfaces (read)
pipeline_type, subtype Classification: pipeline_type ∈ speech_to_speech/text_to_text/speech_to_text; subtype is one of the four supported archetypes — voice_agents, chat_agents, copilots, translation (the orphaned voice_changing/transcription/stt_translation/transcription_enhancement/custom subtypes were removed 2026-06-20; see apps/ai-manager/lib/pipeline-types.ts). Denormalized here for fast list-filtering; also on agent_versions and inside config. Indexed. ai-manager agent list (filter), Asterisk chart configmap (SELECT pipeline_type FROM agents)
surface_config_id (uuid) FK → surface_configs; the embed/branding surface this agent renders in. conversation-api, surface-app
tenant_id (uuid) Multi-tenancy owner; RLS key. everyone
created_by (uuid) FK → users. ai-manager
is_template, template_category, template_metadata If is_template, this row is an agent blueprint the New Agent wizard clones (not a live agent). ai-manager
tags (text[]) Free-form labels for the agent list. ai-manager
a2a_enabled (bool), a2a_metadata (jsonb) Agent2Agent expose state. Deliberately columns, not config — the collab Yjs allow-list would silently drop a config key. conversation-api (serves the A2A card), ai-manager
debug_enabled (bool) Per-agent debug capture toggle. ai-orchestrator, ai-manager
generation_job_id (uuid) If set, a "Generate with AI" run owns this placeholder; the editor shows the generation panel instead of the canvas. ai-manager

agent_versions — versioned runtime config

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).

Column Meaning Used by
agent_id (text) FK → agents.id. everyone
version_number (int) Draft is version 0; published versions are 1+. resolve/publish functions
status 'draft' or 'published'. resolve functions, ai-manager
is_current_published (bool) Exactly one published version per agent is the live one. Unique-indexed. resolve_agent_version, traffic split
config (jsonb) The entire pipeline: stages, settings, output lanes, multimodality, tools refs. TS shape in pipeline-types.ts; field-by-field in config-jsonb.md. ai-manager (write via collab), ai-orchestrator (read)
pipeline_type, subtype Denormalized copy (see agents); returned by the resolve functions. ai-orchestrator, ari-controller
traffic_weight (int) A/B split weight across published versions. resolve_agent_version_weighted
catalogs (jsonb) Pinned A2UI catalog URIs, set at publish time independently of config. conversation-api catalog_manifest, ai-manager
created_from_version, published_at, published_by, publish_notes Publish audit trail. ai-manager, publish function
tenant_id RLS key. everyone

How it's read: never SELECT … FROM agent_versions ad-hoc — go through resolve_agent_version(versioned_id, allow_draft). It parses 3023, 3023:v2, 3023:draft and returns the right (agent_id, version_number, config, …) tuple. See sql-functions.md.

Workflows: same identity/version split, numeric ids

workflow_configs (identity) + workflow_versions (config). The cutover from text slugs to numeric ids happened in migration 130 — workflows are addressed by the numeric workflow_configs.id; the old legacy_slug was dropped in the 2026-06-20 squash.

Table.column Meaning Used by
workflow_configs.id (integer PK) The workflow id. Integer, internal-only. async-agent, agent-host, ai-manager
workflow_configs.{name,description,is_active,is_template,tags} Identity + classification. ai-manager
workflow_configs.config (jsonb) Phases/nodes/edges (the workflow graph). TS shape in workflow-types.ts; field-by-field in config-jsonb.md. ai-manager (write), async-agent (read)
workflow_configs.{expose_mcp,mcp_tool_name} If exposed, the workflow is callable as an MCP tool. async-agent, agent-host
workflow_versions.workflow_id (integer) FK → workflow_configs.id (renamed from base_workflow_id; it is integer, matching workflow_configs.id). async-agent, ai-manager
workflow_versions.{version_number,status,is_current_published,config,traffic_weight} Same draft/published model as agents. resolve_workflow_version

Read via resolve_workflow_version() / _weighted, mirroring the agent functions.

Conversations & transcripts

Table What Written by Read by
conversations One row per session: id, session_id, agent_id (may carry a :version suffix), caller_id, call_source, status, started_at/ended_at/duration_ms, config_version, is_test, tenant_id, multimodal_config_id, archive columns. ai-orchestrator (conversation_store) conversation-api, ai-manager history
conversation_messages Per-message transcript detail (31 cols) — role, content, turn_id, plus the normalization lifecycle columns (variant, revision, message_key, audible_start_ms, provider, …). ai-orchestrator conversation-api (transcript + SSE)
conversation_events Pipeline events (tool calls, A2UI renders, lifecycle). ai-orchestrator conversation-api, surfaces
conversation_search_documents One denormalized, tenant-scoped row per conversation for full-text/faceted search. Note: carries BOTH agent_id (the versioned id used) and base_agent_id (the bare id, for grouping) — the only place base_agent_id legitimately survives. trigger-maintained conversation-api search
conversation_recordings / _attachments / _archive_* Media + the disabled-by-default archive-to-object-storage subsystem. ai-orchestrator, archive worker conversation-api

agent_id on conversations is the bare-or-versioned id the session ran; auth checks must split(":",1)[0] before comparing to agents.id.

Tools & integrations (the encryption boundary)

Table What Notes
tool_types / integration_types Reference catalogs of available tool/integration kinds (mcp, openapi, python_sandboxed, a2a, knowledge, …; openai, genesys, salesforce, …). integration_types is compatibility metadata for legacy type_id rows; new add-integration UI is driven by the source/capability catalog in ai-manager. Seeded by 001_reference_data.sql. Adding a provider/source edits the source catalog, runtime resolver helpers, and any capability-specific routes.
tool_configs A configured tool instance: type_id, name, config (jsonb), integration_id (FK), input_schema/parameters, is_active, tenant_id. ai-manager (write); ai-orchestrator/async-agent/agent-host (resolve + execute)
integrations A configured provider profile: source_id (Amazon, Google, Salesforce, BeyondPresence, …), enabled_capabilities (LLM, Telephony, OCR, Events, PII Redaction, Avatar, …), legacy type_id, config (jsonb, non-secret settings like region/org id), scope (platform/tenant), is_active, is_default. Query new use cases by source/capability and keep type_id only for saved-reference compatibility. ai-manager (write); all runtimes (read via helper resolution + config->>'key')
integration_secrets / tool_secrets Encrypted credentials (encrypted_value/iv/auth_tag, AES-GCM under MASTER_ENCRYPTION_KEY). Never put secrets in *.config. written by ai-manager on save; decrypted by runtimes
tool_health Periodic MCP/tool reachability probe results; drives the Tools page "Unreachable"/"Drift" badge. ai-manager tool-health worker

Source fields can be marked discoverable in the ai-manager source catalog. The /api/integrations/discover/* routes merge unsaved draft config/secrets with saved integration secrets when editing, then return dropdown options for capability-specific fields without storing anything. Amazon discovery covers Connect instance/contact-flow options for telephony and SQS queue ARN/URL pairs for events. Google discovery covers Pub/Sub subscriptions/topics for events, BigQuery/GCS resources for storage, and Document AI processors for ocr. Azure discovery covers OpenAI deployments for llm, Document Intelligence models for ocr, and Speech voices for text_to_speech / avatar. Unsupported capabilities still save manual config values.

See secret-storage.md.

Telephony & CCaaS routing

Table What Live reader
ccaas_pipeline_integrations Default-deny allow-list: (pipeline_id, integration_id, enabled) — which CCaaS integrations may route inbound to which agent. Keyed on the bare agent id; controlled only from the publish dialog. (Renamed from genesys_pipeline_integrations in mig 194; the compat view was dropped in the squash.) ari-controller-node validates every inbound CCaaS call against it
sip_provider_endpoints Per-provider SIP/media settings (host, transport, media_profile, CIDRs). Kamailio reads live — do not rename
sip_digest_auth, sip_credentials SIP auth. Kamailio / Asterisk
ps_endpoints / ps_auths / ps_aors Asterisk PJSIP realtime config. Upstream Asterisk ARA column names — do not rename. Asterisk
sip_logs Kamailio writes SIP message logs here (tenant-scoped). Kamailio (write)

Tenancy, auth & RLS

  • tenants (UUID id; the TrueServe platform tenant is 00000000-…-001, and seed tenants use vanity ids like carrier 0000c477-…/fedex f0000000-…-fede). tenants_id_uuid_format is a shape-only CHECK (8-4-4-4-12 hex), NOT the RFC-4122 v4/v7 version/variant nibbles — it's NOT VALID and re-fires on every UPDATE, so a stricter regex made vanity-id rows un-updatable (edit/deactivate 500s). tenant_memberships, groups/group_members, users (is_platform_admin bypasses RLS on reads).
  • api_tokens (tsf_ bearer tokens, scoped) + api_token_audit_log.
  • management_oauth_* (hashed, tenant/resource-bound management OAuth grants with rotating refresh-token families). The older oauth_clients/oauth_* tables are a separate plaintext legacy surface and must not back management MCP/API tokens. embed_* holds single-use embed JWTs + signing keys, allowed_origins (CORS / frame-ancestors).
  • RLS: tenant-scoped tables enable row-level security keyed on current_setting('app.current_tenant_id'). ai-manager wraps every query in withTenantContext(); backend services connect as tsf_app which has BYPASSRLS (granted by 002_tsf_app_bypassrls.sql) and set tenant context explicitly where needed. In prod, 099_normalize_user.sql strips superuser so RLS is enforced.

Catalogs, generation, and templates (admin-plane)

  • a2ui_catalogs / _catalog_versions / _components / _tools / _tool_plugins — the A2UI component catalog system (tenant-scoped; built-ins live on the TrueServe tenant).
  • generation_jobs — "Generate with AI" runs; agents.generation_job_id / workflow_configs.generation_job_id point back at the owning run.
  • assistant_sessions — per-target CLI sessions. access_mode='edit' backs the editor Assistant; access_mode='read' isolates advisory Management API questions and cannot expose draft-save tools.
  • pipeline_templates (stage/subagent), prompt_templates, agent-template rows (agents.is_template) — the four template stores.
  • surface_configs / tenant_themes / sidebar_custom_tabs — branding + UI surfaces.
  • trigger_rules — event→workflow bindings (workflow_config_id integer FK, event_type, source_filter, criteria, lifecycle trigger_type).

Migrations & how this stays true

The schema is one squashed baseline (000_full_schema.sql) + 001/002/003/004/099, forward-only (overview.md). A fresh boot must re-pg_dump to the committed baseline byte-for-byte — schema-drift.yml enforces it, plus a renamed-column grep guard. Populated environments migrate in place with migrate-city-db.sh (runbook).

The JSONB inside config is its own contract: generated into JSON Schema by generate-config-schemas.ts and drift-guarded by config-schema-drift.yml. 005_normalize_config_shapes.sql normalised the config JSONB to one canonical shape per node/stage (router outputs, ocr_extract fields, translation stage-level language/voice, dead-key cleanup) — see config-jsonb.md and the realign backlog.

See also