Schema Validation: 11 tables + 14 columns missing from schema.py (invariant #3 violated)

[vybe]

Automated Schema Validation Report

Date: 2026-05-07
Result: 26 P1 schema drift issues require attention

---

Critical Findings (P1)

1. 11 Tables Exist Only in migrations.py — Not in schema.py

schema.py is the documented authoritative DDL home (Architectural Invariant #3: "All table DDL lives in schema.py"), but 11 production tables are created exclusively via migrations. schema.py does not represent the full database schema.

Table	Migration	Feature
agent_notifications	agent_notifications	NOTIF-001
subscription_credentials	subscription_credentials	SUB-001 — also FK-referenced from agent_ownership in schema.py!
subscription_rate_limit_events	subscription_rate_limit_tracking	SUB-003
slack_workspaces	slack_channel_agents	SLACK-002
slack_channel_agents	slack_channel_agents	SLACK-002
slack_active_threads	slack_channel_agents	SLACK-002
telegram_bindings	telegram_bindings	TELEGRAM-001
telegram_chat_links	telegram_bindings	TELEGRAM-001
telegram_group_configs	telegram_group_configs	TGRAM-GROUP
whatsapp_bindings	whatsapp_bindings	WHATSAPP-001
whatsapp_chat_links	whatsapp_bindings	WHATSAPP-001

The subscription_credentials case is the most severe: schema.py's agent_ownership DDL declares FOREIGN KEY (subscription_id) REFERENCES subscription_credentials(id), but subscription_credentials itself is only created via migration. If PRAGMA foreign_keys=ON is ever enabled, fresh installs would fail.

2. 14 Columns in migrations.py Not in schema.py DDL

These columns exist in every running database but are absent from the CREATE TABLE in schema.py, making it an unreliable schema reference.

agent_ownership (3 missing):

• full_capabilities — migration agent_ownership_full_capabilities
• max_backlog_depth — migration backlog_support
• voice_system_prompt — migration agent_ownership_voice_prompt

schedule_executions (9 missing):

• source_user_id, source_user_email, source_agent_name, source_mcp_key_id, source_mcp_key_name — migration execution_origin_tracking
• claude_session_id — migration execution_session_tracking
• queued_at, backlog_metadata — migration backlog_support
• fan_out_id — migration execution_fan_out_id

agent_schedules (2 missing):

• webhook_token, webhook_enabled — migration agent_schedules_webhook

---

Why It Works Today (but shouldn't be relied on)

init_database() uses a two-pass init: migrations run before and after init_schema(). The first pass creates migration-only tables; the second pass adds missing columns to schema.py tables. This means fresh installs function correctly — but schema.py is no longer the single source of truth, violating Invariant #3 and making the codebase harder to reason about.

---

Recommended Actions

1. Add all 11 missing tables to schema.py — copy their final DDL (i.e., original CREATE TABLE + any ALTER TABLE columns from later migrations combined) into the TABLES dict
2. Update schema.py DDL for agent_ownership, schedule_executions, agent_schedules — add the 14 missing columns to their CREATE TABLE definitions
3. No migration needed — existing databases already have these tables/columns; schema.py just needs to reflect reality for fresh installs
4. Verify by doing a clean install and comparing PRAGMA table_info output against schema.py

---

Generated by scheduled /validate-schema run