Schema Validation: 4 critical drift issues (ad-hoc tables + missing schema.py entries)

[vybe]

Automated Schema Validation Report

Date: 2026-04-29
Result: 4 critical schema drift issues require attention

Critical Findings (P1)

1. Ad-hoc CREATE TABLE process_schedules in a router (Arch Invariant #3)

File: src/backend/routers/processes.py:781
Problem: process_schedules is created lazily inside _register_process_schedules() using the main Trinity DB (TRINITY_DB_PATH). No migration, not in db/schema.py. The scheduler service reads this table — if queried before any process is published, the table may not exist.
Fix: Add to db/schema.py TABLES dict, add migration in db/migrations.py, remove ad-hoc CREATE from router.

2. Ad-hoc CREATE TABLE in process engine services (Arch Invariant #3)

Files:

• src/backend/services/process_engine/services/templates.py:117 — process_templates
• src/backend/services/process_engine/services/alerts.py:163,178 — cost_thresholds, cost_alerts
  Problem: Tables created inside service class initializers using separate process DB. Violates Invariant Feature/vector log retention #3 ("Never create tables ad-hoc in service code"). The process engine should own a schema init function.
  Fix: Extract to a process_engine/db/schema.py with an explicit init function called at startup.

3. 9 production tables only in migrations.py, absent from schema.py TABLES dict

Tables that exist on every production database but are invisible to db/schema.py:

• slack_workspaces, slack_channel_agents, slack_active_threads (SLACK-002)
• agent_notifications (NOTIF-001)
• subscription_credentials (SUB-001)
• subscription_rate_limit_events (SUB-003)
• telegram_bindings, telegram_chat_links (TELEGRAM-001)
• telegram_group_configs (TGRAM-GROUP)

Problem: Fresh installs get these via the second-pass migrations run, so no functional breakage, but schema.py is supposed to be the DDL source of truth (Invariant #3). Reading schema.py gives an incomplete picture of the actual schema.
Fix: Add all 9 table DDLs to schema.py TABLES dict (migrations can stay as-is for existing DBs).

4. 12 columns missing from schema.py DDL (present only in migrations)

schedule_executions — 9 columns missing: source_user_id, source_user_email, source_agent_name, source_mcp_key_id, source_mcp_key_name (AUDIT-001), claude_session_id (EXEC-023), fan_out_id (FANOUT-001), queued_at, backlog_metadata (BACKLOG-001)

agent_ownership — 3 columns missing: full_capabilities (migration agent_ownership_full_capabilities), max_backlog_depth (BACKLOG-001), voice_system_prompt (VOICE-005)

Fix: Add all 12 columns to the respective CREATE TABLE statements in schema.py. Migrations remain for existing-DB upgrades.

Informational Findings (P2-P3)

• mcp_api_keys in architecture.md is stale: wrong column names (last_used vs last_used_at, use_count vs usage_count), wrong types, 5 undocumented columns
• access_requests in architecture.md has wrong column name (created_at vs requested_at), wrong nullability on channel, wrong type on decided_by
• 15+ production tables have no documentation in architecture.md (public links, monitoring, dashboard, git config, etc.)

Recommended Fix Order

1. Add process_schedules to schema.py + migrations.py and remove ad-hoc CREATE from routers/processes.py
2. Add the 9 migration-only tables to schema.py TABLES dict
3. Add 12 missing columns to schema.py DDL for schedule_executions and agent_ownership
4. Fix architecture.md documentation for mcp_api_keys and access_requests
5. Decide whether process engine services should get a dedicated schema init module

---

Generated by scheduled /validate-schema run

[pavshulin]

Superseded by today's daily reports: #603 (architecture), #604 (schema), #605 (config). Closing to keep the latest snapshot canonical.