Schema Validation: 21 critical schema drift issues found (ad-hoc tables, missing DDL)

[vybe]

Automated Schema Validation Report

Date: 2026-04-23
Result: 21 critical schema drift issues require attention

---

P0 — Ad-Hoc Tables (Architectural Invariant #3 Violation)

9 tables created with inline CREATE TABLE outside db/schema.py and db/migrations.py. These bypass the migration tracking system and cannot be managed, inspected, or rolled back via standard tooling.

File	Table
services/process_engine/repositories/sqlite_definitions.py:70	process_definitions
services/process_engine/repositories/sqlite_executions.py:75	process_executions
services/process_engine/repositories/sqlite_executions.py:99	step_executions
services/process_engine/repositories/sqlite_events.py:86	execution_events
services/process_engine/repositories/audit.py:62	audit_entries
services/process_engine/services/templates.py:117	process_templates
services/process_engine/services/alerts.py:163	cost_thresholds
services/process_engine/services/alerts.py:178	cost_alerts
routers/processes.py:781	process_schedules

Fix: Move all CREATE TABLE statements into db/schema.py (TABLES dict) and add corresponding CREATE TABLE IF NOT EXISTS migrations for existing installs.

---

P1 — Tables in migrations.py but Missing from schema.py

9 tables are created by migrations but absent from schema.py, making the authoritative DDL incomplete. Additionally, subscription_credentials is referenced as a foreign key in schema.py's agent_ownership table but has no DDL definition there — a dangling FK reference.

Table	Migration	Note
subscription_credentials	_migrate_subscription_credentials_table	FK referenced in agent_ownership DDL
agent_notifications	_migrate_agent_notifications_table
subscription_rate_limit_events	_migrate_subscription_rate_limit_tracking
slack_workspaces	_migrate_slack_channel_agents	Documented in architecture.md
slack_channel_agents	_migrate_slack_channel_agents	Documented in architecture.md
slack_active_threads	_migrate_slack_channel_agents	Documented in architecture.md
telegram_bindings	_migrate_telegram_bindings
telegram_chat_links	_migrate_telegram_bindings
telegram_group_configs	_migrate_telegram_group_configs

Fix: Add all 9 tables to db/schema.py TABLES dict with their full DDL.

---

P1 — Columns Added by Migrations but Missing from schema.py CREATE TABLE

12 columns exist in the database (applied via ALTER TABLE in migrations) but are absent from the CREATE TABLE definitions in schema.py. On fresh installs, these columns are added by the second migration pass, but schema.py does not serve as a complete, readable DDL.

agent_ownership (3 columns):

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

schedule_executions (9 columns):

• 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 (also documented in architecture.md!)
• fan_out_id — migration execution_fan_out_id

Fix: Add all missing columns to the respective CREATE TABLE definitions in schema.py.

---

Recommended Actions (Priority Order)

1. Immediately: Add all 9 tables missing from schema.py to the TABLES dict with their full DDL (including subscription_credentials to fix the dangling FK)
2. Immediately: Add 12 missing columns to agent_ownership and schedule_executions CREATE TABLE definitions in schema.py
3. Short-term: Create proper migrations for the 9 process engine tables (move their CREATE TABLE calls out of repository/service init code into db/migrations.py)
4. Short-term: Update docs/memory/architecture.md Database Schema section to reflect current column lists for mcp_api_keys, agent_ownership, agent_schedules, schedule_executions, and access_requests

---

Generated by scheduled /validate-schema run on 2026-04-23

[vybe]

Superseded by newer auto-validation snapshot. Closing as part of grooming session 2026-04-25.