Schema Validation: 14 critical drift issues found (schema.py incomplete, arch.md mismatches)

[vybe]

Automated Schema Validation Report

Date: 2026-04-22
Result: 14 critical schema drift issues require attention

---

Critical Findings (P1)

1. schema.py Missing 9 Entire Tables (Created Only in Migrations)

db/schema.py claims to be the authoritative DDL but is missing 9 tables that only exist via migration CREATE TABLE IF NOT EXISTS statements. These tables have no entry in TABLES dict:

Table	Migration
agent_notifications	agent_notifications
subscription_credentials	subscription_credentials
subscription_rate_limit_events	subscription_rate_limit_tracking
slack_workspaces	slack_channel_agents
slack_channel_agents	slack_channel_agents
slack_active_threads	slack_channel_agents
telegram_bindings	telegram_bindings
telegram_chat_links	telegram_bindings
telegram_group_configs	telegram_group_configs

Risk: schema.py is not a reliable source of truth. Anyone auditing the schema from schema.py alone will miss ~9 tables.

---

2. schedule_executions Missing 9 Columns in schema.py DDL

Columns added by migrations are absent from schema.py's CREATE TABLE for schedule_executions:

• 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)
• fan_out_id (migration execution_fan_out_id)
• queued_at, backlog_metadata (migration backlog_support)

---

3. agent_ownership Missing 3 Columns in schema.py DDL

• full_capabilities (migration agent_ownership_full_capabilities)
• voice_system_prompt (migration agent_ownership_voice_prompt)
• max_backlog_depth (migration backlog_support)

---

4. architecture.md Documents Wrong Column Names for mcp_api_keys

architecture.md	Actual schema.py
last_used TIMESTAMP	last_used_at TEXT
use_count INTEGER	usage_count INTEGER
(missing)	description TEXT
(missing)	key_prefix TEXT NOT NULL
(missing)	is_active INTEGER DEFAULT 1
(missing)	agent_name TEXT
(missing)	scope TEXT DEFAULT 'user'

---

5. architecture.md Documents Wrong Column Name for access_requests

architecture.md says created_at TEXT NOT NULL but actual schema (schema.py line 672 + migration) uses requested_at TEXT NOT NULL. Any code querying access_requests.created_at would fail.

---

6. Process Engine: process_schedules Created Ad-Hoc in Router (Invariant #3 Violation)

routers/processes.py contains an inline CREATE TABLE IF NOT EXISTS process_schedules inside a route handler function — hard violation of Architectural Invariant #3 ("Schema in db/schema.py, Migrations in db/migrations.py").

---

7. Process Engine: Service/Router Tables Outside Schema System

services/process_engine/services/templates.py creates process_templates.
services/process_engine/services/alerts.py creates cost_thresholds and cost_alerts.
All three are created in service code, not in schema.py or migrations.py.

---

8. Process Engine Tables Documented in architecture.md but Not in schema.py

architecture.md documents process_definitions, process_executions, process_step_executions, process_approvals — these exist in the Process Engine's own SQLite DB (via sqlite_definitions.py, sqlite_executions.py), not in the main Trinity DB or schema.py. The architecture doc should clarify they are in a separate DB.

---

Recommended Actions (Prioritized)

1. Immediate: Add process_schedules CREATE TABLE to schema.py / migrations.py and remove inline creation from routers/processes.py (Invariant Feature/vector log retention #3 hard violation)
2. High: Add the 9 migration-only tables to schema.py's TABLES dict to restore schema.py as a complete reference
3. High: Add the 12 missing columns to schedule_executions and agent_ownership in schema.py DDL
4. High: Fix architecture.md mcp_api_keys column names (last_used_at, usage_count)
5. High: Fix architecture.md access_requests column name (requested_at not created_at)
6. Medium: Add note to architecture.md clarifying that Process Engine tables (process_definitions, etc.) live in a separate SQLite database managed by the Process Engine's own repository layer
7. Low: Document the 22 undocumented tables in architecture.md (or add a note that schema.py is the source of truth for all tables)

---

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

[vybe]

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