""" A0 Software Orchestrator – Schema-Migrationen Verwaltet inkrementelle Updates der patterns.db. """ from typing import TYPE_CHECKING if TYPE_CHECKING: from .db import PatternDB # --------------------------------------------------------------------------- # Migrations-Definitionen # --------------------------------------------------------------------------- # Key = Versionsnummer, Value = SQL-Statement # Migrationen werden in aufsteigender Reihenfolge ausgeführt. MIGRATIONS = { 1: """ -- Version 1: Initiales Schema (siehe schema.sql) -- Diese Migration ist nur ein Marker – das vollständige Schema -- wird in schema.sql verwaltet und bei neuen DBs ausgeführt. SELECT 1; -- No-Op, Marker """, 2: """ -- Version 2: Normalisiertes Projekt-Registry-Schema -- Teilt projects_registry in drei Tabellen: -- projects = stabile Identität (name, git_url, description, tech_stack) -- project_state = laufender Zustand (phase, status, tasks, errors, …) -- project_snapshots = Historie (state_json, trigger, commit_hash) -- 2a. Neue Tabellen anlegen CREATE TABLE IF NOT EXISTS projects ( id INTEGER PRIMARY KEY AUTOINCREMENT, name TEXT UNIQUE NOT NULL, git_url TEXT, description TEXT DEFAULT '', tech_stack TEXT DEFAULT '{}', created_at TEXT DEFAULT (datetime('now')) ); CREATE TABLE IF NOT EXISTS project_state ( project_id INTEGER PRIMARY KEY, status TEXT DEFAULT 'active', phase TEXT DEFAULT 'intake', plan_mode TEXT, total_tasks INTEGER DEFAULT 0, completed_tasks INTEGER DEFAULT 0, open_errors INTEGER DEFAULT 0, last_active_at TEXT, completed_at TEXT, updated_at TEXT DEFAULT (datetime('now')), FOREIGN KEY (project_id) REFERENCES projects(id) ); CREATE TABLE IF NOT EXISTS project_snapshots ( id INTEGER PRIMARY KEY AUTOINCREMENT, project_id INTEGER NOT NULL, snapshot_at TEXT DEFAULT (datetime('now')), state_json TEXT NOT NULL, trigger TEXT, commit_hash TEXT, FOREIGN KEY (project_id) REFERENCES projects(id) ); -- 2b. Daten aus projects_registry migrieren INSERT INTO projects (name, git_url, description, tech_stack, created_at) SELECT project_name, git_url, COALESCE(description, ''), COALESCE(tech_stack, '{}'), COALESCE(created_at, datetime('now')) FROM projects_registry WHERE project_name IS NOT NULL; INSERT INTO project_state (project_id, status, phase, plan_mode, total_tasks, completed_tasks, open_errors, last_active_at, completed_at) SELECT p.id, r.status, r.phase, r.plan_mode, COALESCE(r.total_tasks, 0), COALESCE(r.completed_tasks, 0), COALESCE(r.open_errors, 0), r.last_active_at, r.completed_at FROM projects_registry r JOIN projects p ON r.project_name = p.name; -- 2c. Alte Tabelle umbenennen (Sicherheit – kein DROP) ALTER TABLE projects_registry RENAME TO projects_registry_legacy; """, 3: """ -- Version 3: Orchestrator-State-Tabellen (orch_*) -- Migration: .a0/*.json/.md und .a0proj/handover/*.md → patterns.db -- Ersetzt file-basierten State durch DB-Tabellen, project-scoped. -- 3a. Generischer Key/Value Store (JSON-Values) -- ersetzt: project_state.json, orchestrator_mode.json, -- task_graph.json, tool_capabilities.json, -- tool_registry.json, manifests/*.json CREATE TABLE IF NOT EXISTS orch_kv ( project_id INTEGER NOT NULL, key TEXT NOT NULL, value_json TEXT NOT NULL, updated_at TEXT DEFAULT (datetime('now')), PRIMARY KEY (project_id, key), FOREIGN KEY (project_id) REFERENCES projects(id) ON DELETE CASCADE ); CREATE INDEX IF NOT EXISTS idx_orch_kv_project ON orch_kv(project_id); -- 3b. Append-only Worklog -- ersetzt: .a0/worklog.md CREATE TABLE IF NOT EXISTS orch_worklog ( id INTEGER PRIMARY KEY AUTOINCREMENT, project_id INTEGER NOT NULL, phase TEXT, work_block TEXT, agent TEXT, summary TEXT, details TEXT, created_at TEXT DEFAULT (datetime('now')), FOREIGN KEY (project_id) REFERENCES projects(id) ON DELETE CASCADE ); CREATE INDEX IF NOT EXISTS idx_orch_worklog_project ON orch_worklog(project_id, created_at DESC); -- 3c. Todos (offene + abgeschlossene) -- ersetzt: .a0/todo.md CREATE TABLE IF NOT EXISTS orch_todos ( id INTEGER PRIMARY KEY AUTOINCREMENT, project_id INTEGER NOT NULL, content TEXT NOT NULL, status TEXT DEFAULT 'open', -- open, in_progress, done, blocked, cancelled priority INTEGER DEFAULT 0, created_at TEXT DEFAULT (datetime('now')), started_at TEXT, completed_at TEXT, notes TEXT, FOREIGN KEY (project_id) REFERENCES projects(id) ON DELETE CASCADE ); CREATE INDEX IF NOT EXISTS idx_orch_todos_project ON orch_todos(project_id, status); -- 3d. Current Status (1-Zeilen-Projektstatus) -- ersetzt: .a0/current_status.md CREATE TABLE IF NOT EXISTS orch_status ( project_id INTEGER PRIMARY KEY, content TEXT NOT NULL, updated_at TEXT DEFAULT (datetime('now')), FOREIGN KEY (project_id) REFERENCES projects(id) ON DELETE CASCADE ); -- 3e. Next Steps (offene Schritte) -- ersetzt: .a0/next_steps.md CREATE TABLE IF NOT EXISTS orch_next_steps ( id INTEGER PRIMARY KEY AUTOINCREMENT, project_id INTEGER NOT NULL, content TEXT NOT NULL, status TEXT DEFAULT 'pending', -- pending, in_progress, done, blocked order_idx INTEGER DEFAULT 0, created_at TEXT DEFAULT (datetime('now')), completed_at TEXT, FOREIGN KEY (project_id) REFERENCES projects(id) ON DELETE CASCADE ); CREATE INDEX IF NOT EXISTS idx_orch_next_steps_project ON orch_next_steps(project_id, order_idx); -- 3f. Known Errors (offene + gelöste) -- ersetzt: .a0/known_errors.md CREATE TABLE IF NOT EXISTS orch_errors ( id INTEGER PRIMARY KEY AUTOINCREMENT, project_id INTEGER NOT NULL, error_id TEXT, title TEXT NOT NULL, severity TEXT DEFAULT 'medium', -- low, medium, high, critical status TEXT DEFAULT 'open', -- open, investigating, resolved, wontfix context TEXT, resolution TEXT, discovered TEXT DEFAULT (datetime('now')), resolved_at TEXT, FOREIGN KEY (project_id) REFERENCES projects(id) ON DELETE CASCADE ); CREATE INDEX IF NOT EXISTS idx_orch_errors_project ON orch_errors(project_id, status); -- 3g. Block-Compact Marker (ersetzt .a0/resume.md + .a0/conversation_summary.md) CREATE TABLE IF NOT EXISTS orch_block_compact ( project_id INTEGER PRIMARY KEY, last_block_id TEXT, last_compact_at TEXT, next_block TEXT, block_summary TEXT, decisions_json TEXT, -- JSON array key_findings_json TEXT, -- JSON array open_questions_json TEXT, -- JSON array resume_md TEXT, conversation_summary_md TEXT, context_ratio REAL, FOREIGN KEY (project_id) REFERENCES projects(id) ON DELETE CASCADE ); -- 3h. Session Snapshots (ersetzt .a0/session_snapshots/*.json) CREATE TABLE IF NOT EXISTS orch_snapshots ( id INTEGER PRIMARY KEY AUTOINCREMENT, project_id INTEGER NOT NULL, snapshot_at TEXT DEFAULT (datetime('now')), trigger TEXT, -- e.g. 'block_compact', 'phase_transition', 'manual' commit_hash TEXT, state_json TEXT NOT NULL, FOREIGN KEY (project_id) REFERENCES projects(id) ON DELETE CASCADE ); CREATE INDEX IF NOT EXISTS idx_orch_snapshots_project ON orch_snapshots(project_id, snapshot_at DESC); -- 3i. Scorecard Entries (ersetzt .a0/project_scorecard.md) CREATE TABLE IF NOT EXISTS orch_scorecard ( id INTEGER PRIMARY KEY AUTOINCREMENT, project_id INTEGER NOT NULL, category TEXT NOT NULL, score INTEGER NOT NULL, max_score INTEGER DEFAULT 100, created_at TEXT DEFAULT (datetime('now')), FOREIGN KEY (project_id) REFERENCES projects(id) ON DELETE CASCADE ); CREATE INDEX IF NOT EXISTS idx_orch_scorecard_project ON orch_scorecard(project_id, created_at DESC); -- 3j. Briefings (ersetzt .a0proj/handover/*.md) -- Persistente Handover-Briefings für Persona-Modus CREATE TABLE IF NOT EXISTS orch_briefings ( id INTEGER PRIMARY KEY AUTOINCREMENT, project_id INTEGER NOT NULL, specialist TEXT NOT NULL, topic TEXT NOT NULL, section_md TEXT NOT NULL, summary TEXT, decisions_json TEXT, artifacts_json TEXT, recent_turns_json TEXT, return_condition TEXT, handover_reason TEXT, created_at TEXT DEFAULT (datetime('now')), FOREIGN KEY (project_id) REFERENCES projects(id) ON DELETE CASCADE ); CREATE INDEX IF NOT EXISTS idx_orch_briefings_project ON orch_briefings(project_id, specialist, created_at DESC); """, 4: """ -- Version 4: Quality gate runtime tables. CREATE TABLE IF NOT EXISTS orch_quality_config ( project_id INTEGER PRIMARY KEY, tech_stack TEXT NOT NULL DEFAULT 'unknown', coverage_min_pct INTEGER DEFAULT 70, strict_types INTEGER DEFAULT 1, require_security_scan INTEGER DEFAULT 1, require_openapi_check INTEGER DEFAULT 0, custom_checks_json TEXT DEFAULT '[]', updated_at TEXT DEFAULT (datetime('now')), FOREIGN KEY (project_id) REFERENCES projects(id) ON DELETE CASCADE ); CREATE TABLE IF NOT EXISTS orch_quality_runs ( id INTEGER PRIMARY KEY AUTOINCREMENT, project_id INTEGER NOT NULL, gate_level TEXT NOT NULL, decision TEXT NOT NULL, checks_json TEXT NOT NULL, blockers_json TEXT NOT NULL, next_action TEXT, duration_ms INTEGER DEFAULT 0, triggered_by TEXT DEFAULT 'manual', created_at TEXT DEFAULT (datetime('now')), FOREIGN KEY (project_id) REFERENCES projects(id) ON DELETE CASCADE ); CREATE INDEX IF NOT EXISTS idx_orch_quality_runs_project ON orch_quality_runs(project_id, created_at DESC); """, 5: """ -- Version 5: Globale Plugin-Settings (kein Project-Scope) -- Ersetzt den project_id=0-Workaround in orch_kv (plan_mode_guard, -- siehe Bugfix-Auto-Registration §3.7). plugin_settings hat KEINE -- FK auf projects, ist also für plugin-weite Konfiguration. CREATE TABLE IF NOT EXISTS plugin_settings ( key TEXT PRIMARY KEY NOT NULL, value_json TEXT NOT NULL, updated_at TEXT DEFAULT (datetime('now')) ); """ } def _table_exists(db: "PatternDB", name: str) -> bool: row = db.conn.execute( "SELECT 1 FROM sqlite_master WHERE type IN ('table','view') AND name = ?", (name,), ).fetchone() return row is not None def _column_exists(db: "PatternDB", table: str, column: str) -> bool: try: return any(r[1] == column for r in db.conn.execute(f"PRAGMA table_info({table})")) except Exception: return False def _add_column_if_missing(db: "PatternDB", table: str, column: str, ddl: str) -> None: if _table_exists(db, table) and not _column_exists(db, table, column): db.conn.execute(f"ALTER TABLE {table} ADD COLUMN {ddl}") def ensure_runtime_schema(db: "PatternDB") -> None: """Idempotently enforce all runtime tables/columns regardless of migration history.""" db.conn.executescript(""" CREATE TABLE IF NOT EXISTS projects ( id INTEGER PRIMARY KEY AUTOINCREMENT, name TEXT UNIQUE NOT NULL, git_url TEXT, description TEXT DEFAULT '', tech_stack TEXT DEFAULT '{}', created_at TEXT DEFAULT (datetime('now')) ); CREATE TABLE IF NOT EXISTS project_state ( project_id INTEGER PRIMARY KEY, status TEXT DEFAULT 'active', phase TEXT DEFAULT 'intake', plan_mode TEXT, total_tasks INTEGER DEFAULT 0, completed_tasks INTEGER DEFAULT 0, open_errors INTEGER DEFAULT 0, last_active_at TEXT, completed_at TEXT, updated_at TEXT DEFAULT (datetime('now')), FOREIGN KEY (project_id) REFERENCES projects(id) ON DELETE CASCADE ); CREATE TABLE IF NOT EXISTS project_snapshots ( id INTEGER PRIMARY KEY AUTOINCREMENT, project_id INTEGER NOT NULL, snapshot_at TEXT DEFAULT (datetime('now')), state_json TEXT NOT NULL, trigger TEXT, commit_hash TEXT, FOREIGN KEY (project_id) REFERENCES projects(id) ON DELETE CASCADE ); CREATE TABLE IF NOT EXISTS orch_kv ( project_id INTEGER NOT NULL, key TEXT NOT NULL, value_json TEXT NOT NULL, updated_at TEXT DEFAULT (datetime('now')), PRIMARY KEY (project_id, key), FOREIGN KEY (project_id) REFERENCES projects(id) ON DELETE CASCADE ); CREATE TABLE IF NOT EXISTS orch_worklog ( id INTEGER PRIMARY KEY AUTOINCREMENT, project_id INTEGER NOT NULL, phase TEXT, work_block TEXT, agent TEXT, summary TEXT, details TEXT, created_at TEXT DEFAULT (datetime('now')), FOREIGN KEY (project_id) REFERENCES projects(id) ON DELETE CASCADE ); CREATE TABLE IF NOT EXISTS orch_todos ( id INTEGER PRIMARY KEY AUTOINCREMENT, project_id INTEGER NOT NULL, content TEXT NOT NULL, status TEXT DEFAULT 'open', priority INTEGER DEFAULT 0, created_at TEXT DEFAULT (datetime('now')), started_at TEXT, completed_at TEXT, notes TEXT, FOREIGN KEY (project_id) REFERENCES projects(id) ON DELETE CASCADE ); CREATE TABLE IF NOT EXISTS orch_status ( project_id INTEGER PRIMARY KEY, content TEXT NOT NULL, updated_at TEXT DEFAULT (datetime('now')), FOREIGN KEY (project_id) REFERENCES projects(id) ON DELETE CASCADE ); CREATE TABLE IF NOT EXISTS orch_next_steps ( id INTEGER PRIMARY KEY AUTOINCREMENT, project_id INTEGER NOT NULL, content TEXT NOT NULL, status TEXT DEFAULT 'pending', order_idx INTEGER DEFAULT 0, created_at TEXT DEFAULT (datetime('now')), completed_at TEXT, FOREIGN KEY (project_id) REFERENCES projects(id) ON DELETE CASCADE ); CREATE TABLE IF NOT EXISTS orch_errors ( id INTEGER PRIMARY KEY AUTOINCREMENT, project_id INTEGER NOT NULL, error_id TEXT, title TEXT NOT NULL, severity TEXT DEFAULT 'medium', status TEXT DEFAULT 'open', context TEXT, resolution TEXT, discovered TEXT DEFAULT (datetime('now')), resolved_at TEXT, FOREIGN KEY (project_id) REFERENCES projects(id) ON DELETE CASCADE ); CREATE TABLE IF NOT EXISTS orch_block_compact ( project_id INTEGER PRIMARY KEY, last_block_id TEXT, last_compact_at TEXT, next_block TEXT, block_summary TEXT, decisions_json TEXT, key_findings_json TEXT, open_questions_json TEXT, resume_md TEXT, conversation_summary_md TEXT, context_ratio REAL, FOREIGN KEY (project_id) REFERENCES projects(id) ON DELETE CASCADE ); CREATE TABLE IF NOT EXISTS orch_snapshots ( id INTEGER PRIMARY KEY AUTOINCREMENT, project_id INTEGER NOT NULL, snapshot_at TEXT DEFAULT (datetime('now')), trigger TEXT, commit_hash TEXT, state_json TEXT NOT NULL, FOREIGN KEY (project_id) REFERENCES projects(id) ON DELETE CASCADE ); CREATE TABLE IF NOT EXISTS orch_scorecard ( id INTEGER PRIMARY KEY AUTOINCREMENT, project_id INTEGER NOT NULL, category TEXT NOT NULL, score INTEGER NOT NULL, max_score INTEGER DEFAULT 100, created_at TEXT DEFAULT (datetime('now')), FOREIGN KEY (project_id) REFERENCES projects(id) ON DELETE CASCADE ); CREATE TABLE IF NOT EXISTS orch_briefings ( id INTEGER PRIMARY KEY AUTOINCREMENT, project_id INTEGER NOT NULL, specialist TEXT NOT NULL, topic TEXT NOT NULL, section_md TEXT NOT NULL, summary TEXT, decisions_json TEXT, artifacts_json TEXT, recent_turns_json TEXT, return_condition TEXT, handover_reason TEXT, created_at TEXT DEFAULT (datetime('now')), FOREIGN KEY (project_id) REFERENCES projects(id) ON DELETE CASCADE ); CREATE TABLE IF NOT EXISTS orch_quality_config ( project_id INTEGER PRIMARY KEY, tech_stack TEXT NOT NULL DEFAULT 'unknown', coverage_min_pct INTEGER DEFAULT 70, strict_types INTEGER DEFAULT 1, require_security_scan INTEGER DEFAULT 1, require_openapi_check INTEGER DEFAULT 0, custom_checks_json TEXT DEFAULT '[]', updated_at TEXT DEFAULT (datetime('now')), FOREIGN KEY (project_id) REFERENCES projects(id) ON DELETE CASCADE ); CREATE TABLE IF NOT EXISTS orch_quality_runs ( id INTEGER PRIMARY KEY AUTOINCREMENT, project_id INTEGER NOT NULL, gate_level TEXT NOT NULL, decision TEXT NOT NULL, checks_json TEXT NOT NULL, blockers_json TEXT NOT NULL, next_action TEXT, duration_ms INTEGER DEFAULT 0, triggered_by TEXT DEFAULT 'manual', created_at TEXT DEFAULT (datetime('now')), FOREIGN KEY (project_id) REFERENCES projects(id) ON DELETE CASCADE ); """) for ddl in [ "project_path TEXT", "patterns_extracted INTEGER DEFAULT 0", "last_extraction_at TEXT", ]: col = ddl.split()[0] _add_column_if_missing(db, "projects", col, ddl) db.conn.executescript(""" CREATE INDEX IF NOT EXISTS idx_projects_name ON projects(name); CREATE INDEX IF NOT EXISTS idx_project_state_status ON project_state(status, phase); CREATE INDEX IF NOT EXISTS idx_orch_worklog_project ON orch_worklog(project_id, created_at DESC); CREATE INDEX IF NOT EXISTS idx_orch_todos_project ON orch_todos(project_id, status); CREATE INDEX IF NOT EXISTS idx_orch_next_steps_project ON orch_next_steps(project_id, order_idx); CREATE INDEX IF NOT EXISTS idx_orch_errors_project ON orch_errors(project_id, status); CREATE INDEX IF NOT EXISTS idx_orch_snapshots_project ON orch_snapshots(project_id, snapshot_at DESC); CREATE INDEX IF NOT EXISTS idx_orch_scorecard_project ON orch_scorecard(project_id, created_at DESC); CREATE INDEX IF NOT EXISTS idx_orch_briefings_project ON orch_briefings(project_id, specialist, created_at DESC); CREATE INDEX IF NOT EXISTS idx_orch_quality_runs_project ON orch_quality_runs(project_id, created_at DESC); """) db.conn.commit() def run_migrations(db: "PatternDB"): """ Führt ausstehende Schema-Migrationen aus. Args: db: PatternDB-Instanz """ # Aktuelle Schema-Version ermitteln cursor = db.conn.execute( "SELECT MAX(version) FROM schema_version" ) current = cursor.fetchone()[0] or 0 # Fehlende Migrationen in Reihenfolge ausführen for version in sorted(MIGRATIONS.keys()): if version <= current: continue try: db.conn.executescript(MIGRATIONS[version]) db.conn.execute( "INSERT INTO schema_version (version, description) VALUES (?, ?)", (version, f"Migration {version}") ) db.conn.commit() print(f"[PatternDB] ✅ Migration {version} ausgeführt") except Exception as e: print(f"[PatternDB] ❌ Migration {version} fehlgeschlagen: {e}") raise ensure_runtime_schema(db)