Cleanup proposals¶

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

Stale, duplicated, or inconsistent schema findings, with a recommended migration for each. None of these are urgent; bundle them into a focused PR if and when someone has time.

This page is a living audit. When you act on a proposal, move it to the bottom under "Resolved" with the migration number.

2026-06-20 squash. The schema was squashed into a single audited baseline (overview.md), which resolved findings #1, #2, #6, and #7 below and renamed base_agent_id→agent_id / base_workflow_id→workflow_id (rename-map.md). Resolved items are at the bottom. The remaining open findings (#3, #4, #5, #8, #9, #10) still apply on top of the squashed baseline.

Findings¶

1. `resolve_agent_version()` still has a fallback path¶

The function had a path that read from the dropped agents.config column. Migration 104 removed the column but the fallback branch may still exist. Verify and remove.

Proposed migration: xxx_simplify_resolve_agent_version.sql. Read 000_full_schema.sql around the function definition; if there is still a coalesce(av.config, a.config) or similar, replace with av.config.

Risk: low. The fallback is unreachable since 104.

2. `legacy_slug` — ✅ RESOLVED (2026-06-20 squash)¶

~~Residue from migration 130 (text-slug → numeric-ID cutover).~~ The column was workflow_configs.legacy_slug (not workflow_versions); it has been dropped along with the empty trigger_rules_legacy table. Workflows are addressed by numeric id. See the Resolved section below and rename-map.md.

3. `ps_*` tables (Asterisk PJSIP)¶

These follow upstream Asterisk's column naming, not TrueServe's snake_case plural-noun convention. They look out of place but are correct — the Asterisk ARA driver requires the upstream names.

Proposed action: do not rename. Document the coupling instead. (This page is the documentation; we are done.)

Risk: high if ignored. Renaming breaks Asterisk silently.

4. Mixed `text` vs `int` ID conventions¶

Some tables use text primary keys (agents.id, agent_versions.base_agent_id), others numeric (workflow_configs.id after migration 130). The mix is historical and survives across migrations.

Proposed action: do not retrofit. Document the convention per layer:

Agent identifiers: text. They appear in URLs and configs (1004:v3).
Workflow identifiers: numeric. Internal-only.
Tenant identifiers: UUID.

Adding a "Type ID conventions" section to tables.md is enough.

Risk: low.

5. Genesys-specific tables denormalize integration data¶

genesys_agent_mappings, genesys_enrollments, genesys_transfer_rules carry copies of state that exists in integrations.encrypted_config (Genesys org config). The denormalization predates the encrypted-config refactor.

Proposed action: audit whether all rows still have a live FK to integrations. If yes, no work needed; the denormalized columns are read-cached. If there are orphaned rows, delete them.

-- Audit query
SELECT count(*)
FROM genesys_agent_mappings g
LEFT JOIN integrations i ON g.integration_id = i.id
WHERE i.id IS NULL;

Proposed migration (only if orphans exist): xxx_clean_orphan_genesys_mappings.sql.

Risk: low.

6. Confirm no remaining references to `agent_configs` view¶

The view was dropped in migration 104. Code may still reference it.

grep -rni "agent_configs" apps/ shared/ libs/ proto/ deploy/

If anything turns up, fix the reference (it should read from agent_versions joined to agents, or call resolve_agent_version()).

Proposed action: bug fix, not a migration. Open an issue per stale reference.

Risk: medium. A query against a non-existent view fails loudly, so this is not silently broken.

7. SQL functions all live in 000_full_schema.sql¶

The five agent-version functions (resolve_agent_version, resolve_agent_version_weighted, publish_agent_version, create_new_agent_draft, update_agent_draft) are all defined inline in the master schema file. Reviewing changes to one function pulls a 2,000-line diff.

Proposed migration: xxx_extract_agent_version_functions.sql. Move each function to its own file:

xxx_function_resolve_agent_version.sql
xxx_function_resolve_agent_version_weighted.sql
xxx_function_publish_agent_version.sql
xxx_function_create_new_agent_draft.sql
xxx_function_update_agent_draft.sql

Each file is a CREATE OR REPLACE FUNCTION .... The originals in 000_full_schema.sql remain — they are overridden by the later files when init runs. Future edits go in the dedicated files.

Risk: low. CREATE OR REPLACE is idempotent and the function bodies do not change.

8. PJSIP `ps_endpoints` row count drift¶

Vanguard recreates ps_endpoints rows on every wake-up.sh. In long-running cluster deploys, stale rows from deleted agents accumulate. There is no cleanup job.

Proposed migration: xxx_add_ps_endpoints_cleanup.sql. Add a function and cron-style trigger:

CREATE OR REPLACE FUNCTION cleanup_orphan_ps_endpoints() RETURNS int ...
-- delete ps_* rows whose endpoint_id has no matching agent_integration

Risk: medium. Asterisk reads these tables on every registration; deleting an in-use row drops registrations. Schedule during maintenance.

9. `conversation_messages.embedding` column may be redundant with knowledge_chunks¶

If both tables carry pgvector columns indexing similar text, one is doing duplicate work. Audit usage.

Proposed action: investigation, not a migration. Decide whether per-message embeddings serve a purpose distinct from the knowledge index.

Risk: low.

10. Old `dev` enum values lingering¶

Several enum types (e.g., agent_subtype, pipeline_stage_type) accumulate values. Some are no longer produced by ai-manager. Cleanup requires either dropping the value (only safe if no row references it) or accepting the bloat.

Proposed action: per-enum audit. Track in a follow-up issue.

Risk: medium. Dropping an enum value used in a row fails the migration.

Resolved¶

2026-06-20 squash: #1 resolve_agent_version() fallback — regenerated from the live function; no agents.config fallback remains. The function now returns OUT column agent_id.
2026-06-20 squash: #2 workflow_configs.legacy_slug — DROPPED (migration-130 text-slug residue; workflows are addressed by numeric id, nothing reads the slug). The empty, unreferenced trigger_rules_legacy table was dropped at the same time. Both are removed by the squashed baseline and by the city-migration (scripts/schema-audit/migrate-city-db.sh). workflow_versions.workflow_id / *.workflow_config_id are integer (not text — a scratch-replay artifact that was corrected by regenerating the baseline from the migrated live DB).
2026-06-20 squash: #6 agent_configs view references — the view is gone from the baseline and no code references it; the .devcontainer/CLAUDE.md func_odbc gotcha was updated (func_odbc reads agent_versions directly).
2026-06-20 squash: #7 SQL functions all in 000 — still true by design, but now in the single audited baseline rather than a stale snapshot + 118-file ledger; reviewing a function is a baseline diff, and the drift CI keeps it honest.

Cleanup proposals¶

Findings¶

1. resolve_agent_version() still has a fallback path¶

2. legacy_slug — ✅ RESOLVED (2026-06-20 squash)¶

3. ps_* tables (Asterisk PJSIP)¶

4. Mixed text vs int ID conventions¶

5. Genesys-specific tables denormalize integration data¶

6. Confirm no remaining references to agent_configs view¶

7. SQL functions all live in 000_full_schema.sql¶

8. PJSIP ps_endpoints row count drift¶

9. conversation_messages.embedding column may be redundant with knowledge_chunks¶

10. Old dev enum values lingering¶

Resolved¶

See also¶

1. `resolve_agent_version()` still has a fallback path¶

2. `legacy_slug` — ✅ RESOLVED (2026-06-20 squash)¶

3. `ps_*` tables (Asterisk PJSIP)¶

4. Mixed `text` vs `int` ID conventions¶

6. Confirm no remaining references to `agent_configs` view¶

8. PJSIP `ps_endpoints` row count drift¶

9. `conversation_messages.embedding` column may be redundant with knowledge_chunks¶

10. Old `dev` enum values lingering¶