Schema Validation: 25 drift issues (11 critical tables + 14 critical columns missing from schema.py)

[vybe]

Automated Schema Validation Report

Date: 2026-05-11
Triggered by: Scheduled /validate-schema run
Result: 25 schema drift issues — 11 critical (missing tables), 14 critical (missing columns), ~25 informational (doc drift)

---

Critical Finding #1 — 11 Tables Missing from schema.py

These tables exist only in migrations.py and are absent from schema.py's TABLES dict. This violates Architectural Invariant #3 ("All table DDL lives in schema.py"). Fresh installs rely on the dual-call migration pattern to create them — schema.py is no longer the authoritative DDL source.

Table	Migration	Feature
agent_notifications	agent_notifications	NOTIF-001
subscription_credentials	subscription_credentials	SUB-001 — also FK-referenced from agent_ownership in schema.py DDL!
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

Sub-issue: agent_ownership in schema.py has FOREIGN KEY (subscription_id) REFERENCES subscription_credentials(id) but subscription_credentials is not in schema.py's TABLES dict — dangling FK reference in canonical DDL.

---

Critical Finding #2 — 14 Columns Missing from schema.py DDL

Added by migrations but absent from schema.py CREATE TABLE DDL. New installs get them on the second migration pass, but they're invisible when reading schema.py as documentation.

agent_ownership — 3 missing:

• full_capabilities INTEGER DEFAULT 0 (migration: agent_ownership_full_capabilities)
• max_backlog_depth INTEGER DEFAULT 50 (migration: backlog_support)
• voice_system_prompt TEXT (migration: agent_ownership_voice_prompt)

agent_schedules — 2 missing (both documented in architecture.md!):

• webhook_token TEXT (migration: agent_schedules_webhook, WEBHOOK-001)
• webhook_enabled INTEGER DEFAULT 0 (migration: agent_schedules_webhook, WEBHOOK-001)

schedule_executions — 9 missing:

• source_user_id INTEGER (migration: execution_origin_tracking)
• source_user_email TEXT (migration: execution_origin_tracking)
• source_agent_name TEXT (migration: execution_origin_tracking)
• source_mcp_key_id TEXT (migration: execution_origin_tracking)
• source_mcp_key_name TEXT (migration: execution_origin_tracking)
• claude_session_id TEXT (migration: execution_session_tracking)
• queued_at TEXT (migration: backlog_support) — documented in architecture.md
• backlog_metadata TEXT (migration: backlog_support) — documented in architecture.md
• fan_out_id TEXT (migration: execution_fan_out_id)

---

Informational — architecture.md Column Drift

These don't cause runtime errors but mislead future developers:

• agent_ownership: docs show agent_name TEXT PRIMARY KEY, schema uses id INTEGER PRIMARY KEY AUTOINCREMENT + agent_name TEXT UNIQUE NOT NULL; docs show owner_id TEXT, schema uses owner_id INTEGER; ~12 columns not documented
• mcp_api_keys: last_used → last_used_at, use_count → usage_count, user_id TEXT → user_id INTEGER
• agent_sharing: shared_by_id TEXT → shared_by_id INTEGER; allow_proactive undocumented
• agent_schedules: 7 columns undocumented in docs
• schedule_executions: docs show ~14 of 28 actual columns
• 27 tables have no DDL documentation in architecture.md at all

---

Ad-Hoc Schema

✅ None. All CREATE TABLE / ALTER TABLE usage is confined to db/schema.py and db/migrations.py.

---

Recommended Actions

1. Add 11 missing tables to schema.py — copy their DDL from migrations into the TABLES dict, preserving all columns including those added by later migrations on the same table.
2. Add 14 missing columns to schema.py DDL — update the 3 affected CREATE TABLE statements to include all columns currently added via migration.
3. Fix dangling FK — either add subscription_credentials to schema.py (covered by Fix: Add missing Docker labels to system agent container #1) or make the FK conditional.
4. Update architecture.md — fix column name/type drift for agent_ownership, mcp_api_keys, agent_sharing; add webhook_token/webhook_enabled to agent_schedules; add queued_at/backlog_metadata to schedule_executions.

---

Generated by scheduled /validate-schema run on 2026-05-11

[AndriiPasternak31]

Status update

Investigation found every "Critical" finding in the report was already resolved by #712 (fix(schema): backfill schema.py to match migrations.py reality, merged 2026-05-08 to dev). Verified by tests/unit/test_schema_parity.py — 4/4 PASS in origin/dev.

Doc-drift cleanup for the 3 explicitly-named tables in the Informational section is up for review: #788.

Follow-ups

• validate-schema produces false-positive 'critical' findings against already-fixed schema abilities#2 (P1, 2-week SLA, target 2026-05-25) — /validate-schema skill bug: produces false-positive critical findings against already-fixed schema, then auto-files them weekly. This is the real fix that closes this whole class of issue. The skill source lives in abilityai/abilities, so it can't be patched here.
• Decide DDL duplication strategy: keep architecture.md DDL or link to schema.py #786 (P3) — Decide DDL duplication strategy: keep duplicating in architecture.md, replace with links to schema.py, or auto-generate.
• Doc completeness: 27 tables in schema.py lack DDL blocks in architecture.md #787 (P3) — Doc completeness: 27 other tables in schema.py have no DDL block in architecture.md. Blocked on Decide DDL duplication strategy: keep architecture.md DDL or link to schema.py #786.

Courtesy ping to vybe

@vybe — your scheduled /validate-schema run on a remote Trinity agent is filing this issue (and will re-file it weekly until Abilityai/abilities#2 is fixed). Could you pause that schedule until the validator fix lands? Non-blocking — just heading off the weekly noise. Thanks!

Closure

This issue will close when #788 merges. Final resolution comment will link #712 + #788 + Abilityai/abilities#2.

[vybe]

Shipped in v0.6.0 (released via #998).