Skip to content

Runbook: migrate a populated DB to the squashed schema

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

One-time, data-preserving migration of a pre-squash database (vanguard and the city EC2s) up to the cleaned-up squashed schema. Use this — not db-init-local.sh — on any environment with real data you must keep. db-init-local.sh DROPs and reseeds; it is only for throwaway/fresh boots.

What it does

scripts/schema-audit/migrate-city-db.sh runs three ordered, idempotent phases:

  1. Forward-fill — replays the archived additive 1XX/2XX migrations (deploy/init-scripts/archive/) with ON_ERROR_STOP=0. Already-applied ones no-op; the few legacy ones that error on an already-migrated floor (102/103/104/130) are harmlessly skipped. This brings a stale DB (e.g. one hand-migrated to a partial floor) up to the full table/column set. These migrations still reference the OLD base_*_id names, so they MUST run before phase 2.
  2. Rename + cleanup — applies archive/migrate-rename-base-ids.sql:
  3. agent_versions.base_agent_id → agent_id
  4. workflow_versions.base_workflow_id → integer workflow_id
  5. normalizes the renamed PK/FK/index object names
  6. recreates the resolve_/publish_/create_/update_*_version functions (and — since the recreated publish_agent_version PERFORMs public._eval_gate_check — recreates that gate helper + the eval_touch_updated_at trigger helper, so a pre-squash DB that never had them doesn't 400 every publish with function public._eval_gate_check(uuid, text, text) does not exist)
  7. drops the genesys_pipeline_integrations compat view
  8. drops workflow_configs.legacy_slug and the empty trigger_rules_legacy table
  9. Config JSONB normalization + forward-only additive migrations — reapplies every current forward-only script in deploy/init-scripts/ (003 through 024, excluding the 000-002 baseline and 099): pipeline node built-ins, warm transfer, config shape cleanup, agents.role_hint drop, assistant-agent prompt canonicalization, the retired capability cleanup, the tenant-id CHECK loosening, the agent-id sequence, integration source/capabilities, Genesys custom routing properties, management OAuth, the A2UI shared-catalog husk cleanup, personal tokens/permissions, and the management A2A task tables.

When you add a forward-only script, add it to this loop. A file that exists in deploy/init-scripts/ but is missing from the for bn in ... list is applied on a fresh boot (which globs the directory) yet silently skipped on every populated DB — the failure surfaces later as a missing table or column in one environment only.

All phases are idempotent — safe to re-run, and a no-op on an already-migrated DB. conversation_search_documents.base_agent_id is intentionally NOT renamed (it coexists with a versioned agent_id).

Procedure

  1. Back up first (mandatory — verify it restores): bash docker exec tsf-local-postgres pg_dump -U tsf_app -d trusrv_agents \ --no-owner --no-privileges | gzip > backup-$(date -u +%Y%m%dT%H%M%SZ).sql.gz On a city EC2, run the equivalent against that host's Postgres. Test-restore into a scratch DB and compare row counts before proceeding.

  2. Stop DB writers so nothing writes mid-migration (Kamailio writes sip_logs and must be stopped too, or you'll hit a transient FK race): bash overmind stop ai-manager ai-orchestrator conversation-api async-agent agent-host \ conversation-archive-worker collab-gateway agent-review-worker media-proxy ari-controller \ crm-api knowledge-engine multimodal-service catalog-builder docker stop tsf-local-kamailio

  3. Run the migration: bash set -a; source .env.local; set +a PGHOST=localhost PGUSER=tsf_app PGDATABASE=trusrv_agents \ bash scripts/schema-audit/migrate-city-db.sh # or via the dev container: PSQL="docker exec -i tsf-local-postgres psql -U tsf_app -d trusrv_agents" \ bash scripts/schema-audit/migrate-city-db.sh It prints a verification block at the end (agent_id present, workflow_id integer, compat view dropped, agents preserved).

  4. Restart: bash docker start tsf-local-kamailio overmind restart <the services stopped above>

  5. Verify no schema errors in the service logs and that the app shows your data (agents, integrations, tools, workflows). A column "base_agent_id" does not exist or operator does not exist: text = integer after this means the migration didn't complete — re-run it (idempotent). A function public._eval_gate_check(uuid, text, text) does not exist on publish means the DB is on an older run of this script (before the gate helper was added to phase 2) — re-run the current script to install it.

City rollout

Cities are migrated the same way, driven over SSM (not ssh) — see the city rollout process notes. The script + the two SQL files it calls are the entire payload; ship them and run step 3 per city after a verified per-city backup.

Known post-migration drift: duplicate *_workflow_draft overloads

An instance that passed through an intermediate schema state (integer base id, but the SQL functions still carrying the pre-squash p_workflow_type arg) kept a stale overload of update_workflow_draft / create_workflow_draft beside the canonical one, because the rename migration's DROP list only knew about the text-first-arg and the final canonical signatures. Symptom: the workflow editor's "Saving…" spinner never clears — the collab autosave PATCH calls update_workflow_draft($1, $2::jsonb), and because the stale update_workflow_draft(integer, jsonb, character varying DEFAULT NULL) matches those two args exactly as well as the canonical 2-arg function, Postgres raises function update_workflow_draft(integer, jsonb) is not unique and every flush 500s. (The 3-arg create_workflow_draft call is unaffected — its exact text third arg beats the stale varchar overload, so draft creation still resolves cleanly; only the 2-arg update is ambiguous.)

migrate-rename-base-ids.sql now drops both intermediate variants, so a re-run of the migration fixes an affected box. To confirm/repair by hand:

-- Should return ONE row each (canonical). Two rows = drift.
SELECT proname, pg_get_function_identity_arguments(oid)
  FROM pg_proc WHERE proname IN ('update_workflow_draft','create_workflow_draft');

DROP FUNCTION IF EXISTS public.update_workflow_draft(integer, jsonb, character varying);
DROP FUNCTION IF EXISTS public.create_workflow_draft(integer, jsonb, character varying, text);

Why this exists

The schema was squashed 2026-06-20 (schema overview). A DESTRUCTIVE db-init-local.sh reset on a populated box wipes real data; this in-place path renames/cleans without loss. See rename-map.md for the full old→new mapping and the workflow_id is integer / legacy_slug dropped notes.