Schema rename map & data dictionary¶

Status: Applied | Last reviewed: 2026-06-20

The authoritative old→new mapping for the schema cleanup/squash, with per-name blast radius and live-reader coupling. Generated from the PR-0 audit (scripts/schema-audit/, see its README). The renames are applied directly in the squashed baseline (deploy/init-scripts/000_full_schema.sql) — there are no ALTER TABLE … RENAME migrations because there is no production data and the DB is re-bootstrapped from the baseline. Applied 2026-06-20.

What was actually renamed (and what was deliberately kept)¶

Old	New	Scope
`agent_versions.base_agent_id`	`agent_id`	column + PK `(agent_id, version_number)` + FK `agent_versions_agent_id_fkey` + index `idx_agent_versions_agent_id` + all 18 agent/workflow SQL functions (args `p_agent_id`, OUT col `agent_id`)
`workflow_versions.base_workflow_id`	`workflow_id`	column + PK + FK + functions. Type is `integer` (FK → `workflow_configs.id`), NOT text — see note below
`genesys_pipeline_integrations` (compat VIEW)	dropped	zero readers — all code uses `ccaas_pipeline_integrations`
`workflow_configs.legacy_slug`	dropped	migration-130 text-slug residue; workflows are addressed by numeric `id`, nothing reads the slug. Also dropped: `trigger_rules_legacy` (empty, unreferenced)

`workflow_id` / `workflow_config_id` are `integer`, not `text`¶

The squash baseline was first regenerated from a scratch replay of the old migration chain, which left workflow_versions.workflow_id and the *.workflow_config_id FK columns as text — the migration-130 text-slug→numeric-id cutover never fully tripped on a from-scratch replay (the "workflow_versions.base_workflow_id stays text" trap noted in the chart configmap). On a real DB these columns are integer (FK → workflow_configs.id). The committed baseline was therefore regenerated from the migrated live DB (the authoritative truth), giving integer types and dropping the vestigial workflow_configs.workflow_id text column the scratch replay had resurrected. The 5 affected columns: workflow_versions.workflow_id, trigger_rules.workflow_config_id, workflow_executions.workflow_config_id, workflow_runs.workflow_config_id (and formerly trigger_rules_legacy).

KEPT — conversation_search_documents.base_agent_id is NOT renamed. This denormalized search index carries BOTH a versioned agent_id (e.g. 4117:draft) AND a bare base_agent_id (e.g. 4117) — the base_ prefix is correct there (it distinguishes the two). Renaming would collide with the existing agent_id column and lose the distinction. The PR-2 sweep and the check-renamed-columns.sh guard both explicitly allow csd.base_agent_id.

KEPT — application-facing TS field names are unchanged. The DB column rename is decoupled from the camelCase/snake field names the code maps rows into: ai-manager keeps AgentVersionRow.baseAgentId (163 refs — a stable internal contract), and WorkflowVersion.base_workflow_id stays as the TS field. The .map()/parse layer now reads row.agent_id / row.workflow_id and assigns it to those unchanged property names. Only SQL column references and row.<col> accessors changed.

Method¶

000_full_schema.sql is a stale pg_dump snapshot. The real schema was captured by replaying the full migration chain into a scratch DB and dumping it (the chain even ERRORs on a fresh DB — see the audit README). All counts below are from scripts/schema-audit/column-usage.py against that authoritative dump.

1. Tables to DROP (dead — in stale 000, gone from real schema)¶

These are already absent from the live schema; the regenerated baseline simply must not re-create them. No code change needed (no service reads them).

Object	Why dead	Source
`sip_header_mappings`	Never-shipped dynamic SIP header system	dropped in mig 195
`sip_routing_rules`	Same	dropped in mig 195
`sip_provider_types`	Type system for the dropped header system	dropped in mig 195
`telephony_config_snapshots`	Snapshot table for the dropped system	dropped in mig 195
`genesys_pipeline_integrations` (table)	Renamed to `ccaas_pipeline_integrations`	mig 194

2. Compatibility views to DROP (rolling-deploy scaffolding, no longer needed)¶

With no prod data and a clean-break squash, the rolling-deploy compat views are pure clutter. Confirm zero readers (see §5 grep-guard), then drop:

View	Purpose (obsolete)	Action
`genesys_pipeline_integrations` (view)	`security_invoker` redirect to `ccaas_pipeline_integrations` during the 194 rollout	Drop; callers already use `ccaas_pipeline_integrations`.

scope='shared' on a2ui_catalogs stays a legal-but-unused enum value (dead-but-safe per the existing invariant) — not dropped here.

3. Column renames (PRIMARY — high value, partially adopted already)¶

3.1 `agent_versions.base_agent_id` → `agent_id`¶

The bare-id FK to agents.id. ari-controller already aliases it as agent_id; ai-manager/conversation-api already emit agent_id/pipeline_id to the API. This formalizes the convention.

Blast radius: 192 refs — apps/ai-manager 53, deploy/init-scripts 108 (mostly archived seeds + the baseline/functions), apps/conversation-api 13, apps/ari-controller-node 11, apps/ai-orchestrator 3, configs/asterisk 2, apps/webex-connector-go 1, apps/media-proxy 1.
Live reader: configs/asterisk/func_odbc.conf ([AGENT_BY_EXT], [AGENT_TYPE_BY_EXT]) reads agent_versions.base_agent_id directly — must change in lockstep. A miss fails silently at call-answer (Q.850 cause=16).

3.2 `workflow_versions.base_workflow_id` → `workflow_id`¶

The workflow analog. async-agent-heavy.

Blast radius: async-agent (workflow_config_provider.py ×4, app/api/http.py, batch_flush_poller.py), ai-manager lib/db-workflows.ts, conversation-api.
No live (Kamailio/Asterisk) reader.

3.3 Functions touched by 3.1 / 3.2 (signature + every caller)¶

Renaming the column means renaming the function OUT columns and p_base_*_id args. Full list in scripts/schema-audit/functions-touching-rename-targets.txt:

Agent: resolve_agent_version, resolve_agent_version_weighted, get_agent_versions_list, publish_agent_version (×3 overloads), create_new_agent_draft, create_draft_from_version, update_agent_draft, delete_agent_draft, update_traffic_weights.
Workflow: resolve_workflow_version, resolve_workflow_version_weighted, get_workflow_versions_list, publish_workflow_version, create_workflow_draft, create_draft_from_workflow_version, update_workflow_draft, update_workflow_traffic_weights.

OUT-column renames are the highest risk: grep cannot prove the function↔caller contract. The PR-2 runtime harness calls resolve_agent_version('<seed>') and asserts the returned column is agent_id.

4. Columns that look like duplication but are NOT dropped¶

The plan flagged dropping the denormalized pipeline_type/subtype on agents and agent_versions. Audit says keep them. They are live, queried, indexed columns, not dead duplication:

ai-manager lib/db.ts filters and selects a.pipeline_type for the agent-list UI (db.ts:142,270,369,860); db-conversations.ts:421 selects it.
resolve_agent_version / resolve_agent_version_weighted / get_agent_versions_list return pipeline_type + subtype as OUT columns.
The chart Asterisk reader charts/asterisk/templates/configmap.yaml:152 does SELECT pipeline_type FROM agents WHERE id = ….

They ARE denormalized (the values also live in config), but that is an intentional read-path optimization. Document the denormalization; do not drop.

Consistency item (PR-2): the local and chart Asterisk readers diverge — configs/asterisk/func_odbc.conf reads agent_versions.config->>'agent_type' while charts/asterisk/templates/configmap.yaml reads agents.pipeline_type. Pick one (recommend the agents.pipeline_type column read, since it survives) and align both copies.

5. DO NOT RENAME (live non-app readers / upstream names)¶

Object	Reason	Reader
`ps_endpoints`, `ps_auths`, `ps_aors`, `ps_*`	Asterisk ARA driver requires upstream PJSIP column names	Asterisk realtime
`sip_provider_endpoints` (+ all columns)	Read live by Kamailio via sqlops	`configs/kamailio/kamailio.cfg`
`sip_digest_auth`	Read live by Kamailio	`configs/kamailio/kamailio.cfg`

Kamailio reads ONLY these exception tables — it needs no change for any rename in this map. Asserted by grep in PR-2; documented here.

6. Cosmetic boolean/timestamp nits (OPTIONAL — low value, deferred)¶

The convention scan flagged booleans without is_/has_ and similar (execution_events.success/critical, test_assessments.passed, trigger_rules.exclusive, knowledge_graph_edge_log.synced_to_neo4j). These are low-value, high-churn renames with real blast radius and no current bug. Not in scope for the squash PRs — tracked as a follow-up convention pass if ever wanted. (agents.a2a_enabled/debug_enabled, tool_configs.expose_to_agents, workflow_configs.expose_mcp are already fine — _enabled/expose_ are acceptable conventions.)

7. Triage list — zero-code-reader columns¶

scripts/schema-audit/dead-columns-core-candidates.txt lists 45 core columns with no non-schema readers. This is a noisy signal: many belong to the knowledge-engine / agent-insights / conversation-threads subsystems that may use SELECT * row access the scanner can't see. Do not auto-drop. Per-column triage is a separate follow-up, not part of the squash.