Module: Aidp::Database::Schema

Defined in:: lib/aidp/database/schema.rb

Overview

Schema definitions for AIDP SQLite database Each constant represents a migration version with its SQL statements

Constant Summary collapse

V1_INITIAL = Version 1: Initial schema with all core tables

"-- Schema version tracking for migrations\nCREATE TABLE IF NOT EXISTS schema_migrations (\n    version INTEGER PRIMARY KEY,\n    applied_at TEXT NOT NULL DEFAULT (datetime('now'))\n);\n\n-- Checkpoints (replaces checkpoint.yml)\nCREATE TABLE IF NOT EXISTS checkpoints (\n    id INTEGER PRIMARY KEY AUTOINCREMENT,\n    project_dir TEXT NOT NULL,\n    step_name TEXT,\n    step_index INTEGER,\n    total_steps INTEGER,\n    status TEXT,\n    started_at TEXT,\n    completed_at TEXT,\n    run_loop_started_at TEXT,\n    metadata TEXT,\n    created_at TEXT NOT NULL DEFAULT (datetime('now')),\n    updated_at TEXT NOT NULL DEFAULT (datetime('now'))\n);\nCREATE INDEX IF NOT EXISTS idx_checkpoints_project ON checkpoints(project_dir);\n\n-- Checkpoint history (replaces checkpoint_history.jsonl)\nCREATE TABLE IF NOT EXISTS checkpoint_history (\n    id INTEGER PRIMARY KEY AUTOINCREMENT,\n    project_dir TEXT NOT NULL,\n    step_name TEXT,\n    step_index INTEGER,\n    status TEXT,\n    timestamp TEXT NOT NULL,\n    metadata TEXT\n);\nCREATE INDEX IF NOT EXISTS idx_checkpoint_history_project ON checkpoint_history(project_dir);\nCREATE INDEX IF NOT EXISTS idx_checkpoint_history_timestamp ON checkpoint_history(timestamp);\n\n-- Tasks (replaces tasklist.jsonl)\nCREATE TABLE IF NOT EXISTS tasks (\n    id TEXT PRIMARY KEY,\n    project_dir TEXT NOT NULL,\n    description TEXT NOT NULL,\n    priority TEXT DEFAULT 'medium',\n    status TEXT DEFAULT 'pending',\n    tags TEXT,\n    source TEXT,\n    created_at TEXT NOT NULL DEFAULT (datetime('now')),\n    updated_at TEXT NOT NULL DEFAULT (datetime('now')),\n    started_at TEXT,\n    completed_at TEXT\n);\nCREATE INDEX IF NOT EXISTS idx_tasks_project ON tasks(project_dir);\nCREATE INDEX IF NOT EXISTS idx_tasks_status ON tasks(status);\n\n-- Progress tracking (replaces progress/*.yml)\nCREATE TABLE IF NOT EXISTS progress (\n    id INTEGER PRIMARY KEY AUTOINCREMENT,\n    project_dir TEXT NOT NULL,\n    mode TEXT NOT NULL,\n    current_step TEXT,\n    current_step_index INTEGER,\n    total_steps INTEGER,\n    steps_completed TEXT,\n    started_at TEXT,\n    updated_at TEXT NOT NULL DEFAULT (datetime('now')),\n    metadata TEXT\n);\nCREATE UNIQUE INDEX IF NOT EXISTS idx_progress_project_mode ON progress(project_dir, mode);\n\n-- Harness state (replaces harness/*_state.json)\nCREATE TABLE IF NOT EXISTS harness_state (\n    id INTEGER PRIMARY KEY AUTOINCREMENT,\n    project_dir TEXT NOT NULL,\n    mode TEXT NOT NULL,\n    state TEXT NOT NULL,\n    version INTEGER DEFAULT 1,\n    created_at TEXT NOT NULL DEFAULT (datetime('now')),\n    updated_at TEXT NOT NULL DEFAULT (datetime('now'))\n);\nCREATE UNIQUE INDEX IF NOT EXISTS idx_harness_state_project_mode ON harness_state(project_dir, mode);\n\n-- Worktrees (replaces worktrees.json and pr_worktrees.json)\nCREATE TABLE IF NOT EXISTS worktrees (\n    id INTEGER PRIMARY KEY AUTOINCREMENT,\n    project_dir TEXT NOT NULL,\n    worktree_type TEXT NOT NULL,\n    path TEXT NOT NULL,\n    branch TEXT NOT NULL,\n    slug TEXT,\n    pr_number INTEGER,\n    status TEXT DEFAULT 'active',\n    metadata TEXT,\n    created_at TEXT NOT NULL DEFAULT (datetime('now')),\n    updated_at TEXT NOT NULL DEFAULT (datetime('now'))\n);\nCREATE INDEX IF NOT EXISTS idx_worktrees_project ON worktrees(project_dir);\nCREATE INDEX IF NOT EXISTS idx_worktrees_type ON worktrees(worktree_type);\n\n-- Workstream state (replaces workstreams/*/state.json)\nCREATE TABLE IF NOT EXISTS workstreams (\n    id INTEGER PRIMARY KEY AUTOINCREMENT,\n    project_dir TEXT NOT NULL,\n    slug TEXT NOT NULL,\n    status TEXT DEFAULT 'pending',\n    iteration INTEGER DEFAULT 0,\n    branch TEXT,\n    worktree_path TEXT,\n    task TEXT,\n    metadata TEXT,\n    created_at TEXT NOT NULL DEFAULT (datetime('now')),\n    updated_at TEXT NOT NULL DEFAULT (datetime('now'))\n);\nCREATE UNIQUE INDEX IF NOT EXISTS idx_workstreams_project_slug ON workstreams(project_dir, slug);\n\n-- Workstream history (replaces workstreams/*/history.jsonl)\nCREATE TABLE IF NOT EXISTS workstream_events (\n    id INTEGER PRIMARY KEY AUTOINCREMENT,\n    project_dir TEXT NOT NULL,\n    workstream_slug TEXT NOT NULL,\n    event_type TEXT NOT NULL,\n    event_data TEXT,\n    timestamp TEXT NOT NULL DEFAULT (datetime('now'))\n);\nCREATE INDEX IF NOT EXISTS idx_workstream_events_slug ON workstream_events(project_dir, workstream_slug);\n\n-- Watch mode state (replaces watch/*.yml)\nCREATE TABLE IF NOT EXISTS watch_state (\n    id INTEGER PRIMARY KEY AUTOINCREMENT,\n    project_dir TEXT NOT NULL,\n    repository TEXT NOT NULL,\n    plans TEXT,\n    builds TEXT,\n    reviews TEXT,\n    ci_fixes TEXT,\n    change_requests TEXT,\n    detection_comments TEXT,\n    feedback_comments TEXT,\n    processed_reactions TEXT,\n    auto_prs TEXT,\n    projects TEXT,\n    hierarchies TEXT,\n    worktree_cleanup TEXT,\n    last_poll_at TEXT,\n    metadata TEXT,\n    created_at TEXT NOT NULL DEFAULT (datetime('now')),\n    updated_at TEXT NOT NULL DEFAULT (datetime('now'))\n);\nCREATE UNIQUE INDEX IF NOT EXISTS idx_watch_state_repo ON watch_state(project_dir, repository);\n\n-- Evaluations (replaces evaluations/*.json)\nCREATE TABLE IF NOT EXISTS evaluations (\n    id TEXT PRIMARY KEY,\n    project_dir TEXT NOT NULL,\n    rating TEXT NOT NULL,\n    comment TEXT,\n    target_type TEXT,\n    target_id TEXT,\n    context TEXT,\n    created_at TEXT NOT NULL DEFAULT (datetime('now'))\n);\nCREATE INDEX IF NOT EXISTS idx_evaluations_project ON evaluations(project_dir);\nCREATE INDEX IF NOT EXISTS idx_evaluations_rating ON evaluations(rating);\n\n-- Provider metrics (replaces provider_metrics.yml)\nCREATE TABLE IF NOT EXISTS provider_metrics (\n    id INTEGER PRIMARY KEY AUTOINCREMENT,\n    project_dir TEXT NOT NULL,\n    provider_name TEXT NOT NULL,\n    model_name TEXT,\n    metrics TEXT NOT NULL,\n    recorded_at TEXT NOT NULL DEFAULT (datetime('now'))\n);\nCREATE INDEX IF NOT EXISTS idx_provider_metrics_provider ON provider_metrics(project_dir, provider_name);\n\n-- Provider rate limits (replaces provider_rate_limits.yml)\nCREATE TABLE IF NOT EXISTS provider_rate_limits (\n    id INTEGER PRIMARY KEY AUTOINCREMENT,\n    project_dir TEXT NOT NULL,\n    provider_name TEXT NOT NULL,\n    model_name TEXT,\n    rate_limit_info TEXT NOT NULL,\n    updated_at TEXT NOT NULL DEFAULT (datetime('now'))\n);\nCREATE UNIQUE INDEX IF NOT EXISTS idx_rate_limits_provider ON provider_rate_limits(project_dir, provider_name, model_name);\n\n-- Security secrets registry (replaces security/secrets_registry.json)\nCREATE TABLE IF NOT EXISTS secrets_registry (\n    id INTEGER PRIMARY KEY AUTOINCREMENT,\n    project_dir TEXT NOT NULL,\n    secret_id TEXT NOT NULL,\n    secret_name TEXT NOT NULL,\n    env_var TEXT,\n    description TEXT,\n    scopes TEXT,\n    registered_at TEXT NOT NULL DEFAULT (datetime('now')),\n    metadata TEXT\n);\nCREATE UNIQUE INDEX IF NOT EXISTS idx_secrets_project_name ON secrets_registry(project_dir, secret_name);\n\n-- Prompt archive (replaces prompt_archive/*.md)\nCREATE TABLE IF NOT EXISTS prompt_archive (\n    id INTEGER PRIMARY KEY AUTOINCREMENT,\n    project_dir TEXT NOT NULL,\n    step_name TEXT,\n    content TEXT NOT NULL,\n    archived_at TEXT NOT NULL DEFAULT (datetime('now'))\n);\nCREATE INDEX IF NOT EXISTS idx_prompt_archive_project ON prompt_archive(project_dir);\n\n-- Provider info cache (replaces providers/*_info.yml)\nCREATE TABLE IF NOT EXISTS provider_info_cache (\n    id INTEGER PRIMARY KEY AUTOINCREMENT,\n    project_dir TEXT NOT NULL,\n    provider_name TEXT NOT NULL,\n    info TEXT NOT NULL,\n    cached_at TEXT NOT NULL DEFAULT (datetime('now')),\n    expires_at TEXT\n);\nCREATE UNIQUE INDEX IF NOT EXISTS idx_provider_info_name ON provider_info_cache(project_dir, provider_name);\n\n-- Model cache (replaces model_cache/models.json)\nCREATE TABLE IF NOT EXISTS model_cache (\n    id INTEGER PRIMARY KEY AUTOINCREMENT,\n    project_dir TEXT NOT NULL,\n    provider_name TEXT NOT NULL,\n    models TEXT NOT NULL,\n    cached_at TEXT NOT NULL DEFAULT (datetime('now')),\n    expires_at TEXT\n);\nCREATE UNIQUE INDEX IF NOT EXISTS idx_model_cache_provider ON model_cache(project_dir, provider_name);\n\n-- Deprecated models cache (replaces deprecated_models.json)\nCREATE TABLE IF NOT EXISTS deprecated_models (\n    id INTEGER PRIMARY KEY AUTOINCREMENT,\n    project_dir TEXT NOT NULL,\n    provider_name TEXT NOT NULL,\n    model_name TEXT NOT NULL,\n    replacement TEXT,\n    reason TEXT,\n    detected_at TEXT NOT NULL DEFAULT (datetime('now'))\n);\nCREATE INDEX IF NOT EXISTS idx_deprecated_models_provider ON deprecated_models(project_dir, provider_name);\nCREATE UNIQUE INDEX IF NOT EXISTS idx_deprecated_models_unique ON deprecated_models(project_dir, provider_name, model_name);\n\n-- Background jobs (replaces jobs/*)\nCREATE TABLE IF NOT EXISTS background_jobs (\n    id TEXT PRIMARY KEY,\n    project_dir TEXT NOT NULL,\n    job_type TEXT NOT NULL,\n    status TEXT DEFAULT 'pending',\n    pid INTEGER,\n    options TEXT,\n    result TEXT,\n    error TEXT,\n    started_at TEXT,\n    completed_at TEXT,\n    created_at TEXT NOT NULL DEFAULT (datetime('now'))\n);\nCREATE INDEX IF NOT EXISTS idx_jobs_project ON background_jobs(project_dir);\nCREATE INDEX IF NOT EXISTS idx_jobs_status ON background_jobs(status);\n\n-- Auto-update checkpoints (replaces checkpoints/*.json)\nCREATE TABLE IF NOT EXISTS auto_update_checkpoints (\n    id TEXT PRIMARY KEY,\n    project_dir TEXT NOT NULL,\n    checkpoint_type TEXT,\n    data TEXT NOT NULL,\n    checksum TEXT,\n    created_at TEXT NOT NULL DEFAULT (datetime('now'))\n);\nCREATE INDEX IF NOT EXISTS idx_auto_checkpoints_project ON auto_update_checkpoints(project_dir);\n"

V2_PROMPT_FEEDBACK = Version 2: Add prompt feedback table for AGD pattern

"-- Prompt feedback (tracks prompt template effectiveness for AGD evolution)\nCREATE TABLE IF NOT EXISTS prompt_feedback (\n    id INTEGER PRIMARY KEY AUTOINCREMENT,\n    project_dir TEXT NOT NULL,\n    template_id TEXT NOT NULL,\n    outcome TEXT NOT NULL,\n    iterations INTEGER,\n    user_reaction TEXT,\n    suggestions TEXT,\n    context TEXT,\n    aidp_version TEXT,\n    created_at TEXT NOT NULL DEFAULT (datetime('now'))\n);\nCREATE INDEX IF NOT EXISTS idx_prompt_feedback_project ON prompt_feedback(project_dir);\nCREATE INDEX IF NOT EXISTS idx_prompt_feedback_template ON prompt_feedback(template_id);\nCREATE INDEX IF NOT EXISTS idx_prompt_feedback_outcome ON prompt_feedback(outcome);\n"

V3_TEMPLATE_VERSIONS = Version 3: Add template_versions table for versioned template management

"-- Template versions (tracks template versions with feedback-based selection)\nCREATE TABLE IF NOT EXISTS template_versions (\n    id INTEGER PRIMARY KEY AUTOINCREMENT,\n    project_dir TEXT NOT NULL,\n    template_id TEXT NOT NULL,\n    version_number INTEGER NOT NULL,\n    content TEXT NOT NULL,\n    parent_version_id INTEGER,\n    is_active INTEGER DEFAULT 0 CHECK (is_active IN (0, 1)),\n    positive_votes INTEGER DEFAULT 0,\n    negative_votes INTEGER DEFAULT 0,\n    created_at TEXT NOT NULL DEFAULT (datetime('now')),\n    metadata TEXT  -- JSON-encoded hash with evolution context (source, suggestions, changes)\n);\nCREATE INDEX IF NOT EXISTS idx_template_versions_project ON template_versions(project_dir);\nCREATE INDEX IF NOT EXISTS idx_template_versions_template ON template_versions(project_dir, template_id);\nCREATE INDEX IF NOT EXISTS idx_template_versions_active ON template_versions(project_dir, template_id, is_active);\nCREATE UNIQUE INDEX IF NOT EXISTS idx_template_versions_unique ON template_versions(project_dir, template_id, version_number);\n"

V4_STRATEGY_EXECUTION = Version 4: Add strategy execution experience store

"-- Strategy definitions for orchestration-as-data\nCREATE TABLE IF NOT EXISTS strategies (\n    id TEXT PRIMARY KEY,\n    project_dir TEXT NOT NULL,\n    name TEXT NOT NULL,\n    spec TEXT NOT NULL,\n    created_at TEXT NOT NULL DEFAULT (datetime('now'))\n);\nCREATE INDEX IF NOT EXISTS idx_strategies_project_name ON strategies(project_dir, name);\n\n-- Replayable task inputs for strategy execution\nCREATE TABLE IF NOT EXISTS experience_tasks (\n    id TEXT PRIMARY KEY,\n    project_dir TEXT NOT NULL,\n    title TEXT,\n    description TEXT NOT NULL,\n    input_payload TEXT,\n    context TEXT,\n    source_run_id TEXT,\n    created_at TEXT NOT NULL DEFAULT (datetime('now'))\n);\nCREATE INDEX IF NOT EXISTS idx_experience_tasks_project ON experience_tasks(project_dir);\nCREATE INDEX IF NOT EXISTS idx_experience_tasks_source_run ON experience_tasks(source_run_id);\n\n-- Individual strategy execution runs, including speculative branches\nCREATE TABLE IF NOT EXISTS experience_runs (\n    id TEXT PRIMARY KEY,\n    project_dir TEXT NOT NULL,\n    task_id TEXT NOT NULL,\n    strategy_id TEXT NOT NULL,\n    workflow_id TEXT,\n    parent_run_id TEXT,\n    branch_key TEXT,\n    status TEXT NOT NULL,\n    depth INTEGER DEFAULT 0,\n    input_payload TEXT,\n    output_payload TEXT,\n    metadata TEXT,\n    started_at TEXT NOT NULL DEFAULT (datetime('now')),\n    completed_at TEXT\n);\nCREATE INDEX IF NOT EXISTS idx_experience_runs_project ON experience_runs(project_dir);\nCREATE INDEX IF NOT EXISTS idx_experience_runs_task ON experience_runs(task_id);\nCREATE INDEX IF NOT EXISTS idx_experience_runs_parent ON experience_runs(parent_run_id);\nCREATE INDEX IF NOT EXISTS idx_experience_runs_strategy ON experience_runs(strategy_id);\n\n-- Evaluation signals captured for each run\nCREATE TABLE IF NOT EXISTS experience_evaluations (\n    id INTEGER PRIMARY KEY AUTOINCREMENT,\n    project_dir TEXT NOT NULL,\n    run_id TEXT NOT NULL,\n    evaluator_name TEXT NOT NULL,\n    score REAL,\n    passed INTEGER DEFAULT 0 CHECK (passed IN (0, 1)),\n    summary TEXT,\n    metadata TEXT,\n    created_at TEXT NOT NULL DEFAULT (datetime('now'))\n);\nCREATE INDEX IF NOT EXISTS idx_experience_evaluations_run ON experience_evaluations(run_id);\nCREATE INDEX IF NOT EXISTS idx_experience_evaluations_name ON experience_evaluations(project_dir, evaluator_name);\n\n-- Artifact metadata produced by runs\nCREATE TABLE IF NOT EXISTS experience_artifacts (\n    id INTEGER PRIMARY KEY AUTOINCREMENT,\n    project_dir TEXT NOT NULL,\n    run_id TEXT NOT NULL,\n    role TEXT NOT NULL,\n    path TEXT NOT NULL,\n    metadata TEXT,\n    created_at TEXT NOT NULL DEFAULT (datetime('now'))\n);\nCREATE INDEX IF NOT EXISTS idx_experience_artifacts_run ON experience_artifacts(run_id);\n\n-- Embeddings are stored as JSON vectors until a dedicated vector store exists\nCREATE TABLE IF NOT EXISTS experience_embeddings (\n    id INTEGER PRIMARY KEY AUTOINCREMENT,\n    project_dir TEXT NOT NULL,\n    task_id TEXT,\n    run_id TEXT,\n    embedding_type TEXT NOT NULL,\n    vector TEXT NOT NULL,\n    metadata TEXT,\n    created_at TEXT NOT NULL DEFAULT (datetime('now'))\n);\nCREATE INDEX IF NOT EXISTS idx_experience_embeddings_task ON experience_embeddings(task_id);\nCREATE INDEX IF NOT EXISTS idx_experience_embeddings_run ON experience_embeddings(run_id);\n"

MIGRATIONS = All migrations in order V4's strategies index is intentionally non-unique so that immutable versions of a named strategy can coexist for replay and auditability; StrategyRepository keys rows by a SHA256(project_dir:name:spec) digest.

{
  1 => V1_INITIAL,
  2 => V2_PROMPT_FEEDBACK,
  3 => V3_TEMPLATE_VERSIONS,
  4 => V4_STRATEGY_EXECUTION
}.freeze

Class Method Summary collapse

.latest_version ⇒ Object
Get latest version.
.migration_sql(version) ⇒ Object
Get SQL for a specific version.
.versions ⇒ Object
Get all migration versions.

Class Method Details

.latest_version ⇒ `Object`

Get latest version



436
437
438

# File 'lib/aidp/database/schema.rb', line 436

def self.latest_version
  versions.last || 0
end

.migration_sql(version) ⇒ `Object`

Get SQL for a specific version



426
427
428

# File 'lib/aidp/database/schema.rb', line 426

def self.migration_sql(version)
  MIGRATIONS[version]
end

.versions ⇒ `Object`

Get all migration versions



431
432
433

# File 'lib/aidp/database/schema.rb', line 431

def self.versions
  MIGRATIONS.keys.sort
end