path: root/makima/migrations/20260206000000_create_directive_system.sql

-- ============================================================================
-- Migration: create_directive_system.sql
-- Replaces: chains, chain_contracts, chain_contract_definitions,
--           chain_directives, contract_evaluations, chain_events,
--           chain_repositories
-- ============================================================================

-- Drop old chain system tables (cascade to remove FKs)
DROP TABLE IF EXISTS contract_evaluations CASCADE;
DROP TABLE IF EXISTS chain_events CASCADE;
DROP TABLE IF EXISTS chain_contract_definitions CASCADE;
DROP TABLE IF EXISTS chain_contracts CASCADE;
DROP TABLE IF EXISTS chain_directives CASCADE;
DROP TABLE IF EXISTS chain_repositories CASCADE;
DROP TABLE IF EXISTS chains CASCADE;

-- Remove old chain-related columns from contracts
ALTER TABLE contracts DROP COLUMN IF EXISTS chain_id;
ALTER TABLE contracts DROP COLUMN IF EXISTS spawned_chain_id;
ALTER TABLE contracts DROP COLUMN IF EXISTS is_chain_directive;

-- ============================================================================
-- 1. DIRECTIVES -- the top-level entity
-- ============================================================================
CREATE TABLE directives (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    owner_id UUID NOT NULL REFERENCES owners(id) ON DELETE CASCADE,

    -- Goal specification
    title VARCHAR(500) NOT NULL,
    goal TEXT NOT NULL,

    -- Structured specification (JSONB arrays)
    requirements JSONB NOT NULL DEFAULT '[]',
    acceptance_criteria JSONB NOT NULL DEFAULT '[]',
    constraints JSONB NOT NULL DEFAULT '[]',
    external_dependencies JSONB NOT NULL DEFAULT '[]',

    -- State
    status VARCHAR(32) NOT NULL DEFAULT 'draft',

    -- Autonomy configuration
    autonomy_level VARCHAR(32) NOT NULL DEFAULT 'guardrails',
    confidence_threshold_green FLOAT NOT NULL DEFAULT 0.85,
    confidence_threshold_yellow FLOAT NOT NULL DEFAULT 0.60,

    -- Circuit breaker limits
    max_total_cost_usd FLOAT,
    max_wall_time_minutes INTEGER,
    max_rework_cycles INTEGER DEFAULT 3,
    max_chain_regenerations INTEGER DEFAULT 2,

    -- Repository configuration (inherited by all steps)
    repository_url VARCHAR(512),
    local_path VARCHAR(512),
    base_branch VARCHAR(255),

    -- Orchestrator contract reference
    orchestrator_contract_id UUID REFERENCES contracts(id) ON DELETE SET NULL,

    -- Tracking
    current_chain_id UUID,  -- FK added after directive_chains table
    chain_generation_count INTEGER NOT NULL DEFAULT 0,
    total_cost_usd FLOAT NOT NULL DEFAULT 0.0,
    started_at TIMESTAMPTZ,
    completed_at TIMESTAMPTZ,

    version INTEGER NOT NULL DEFAULT 1,
    created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

CREATE INDEX idx_directives_owner_id ON directives(owner_id);
CREATE INDEX idx_directives_status ON directives(status);

-- Add directive reference to contracts
ALTER TABLE contracts ADD COLUMN directive_id UUID REFERENCES directives(id) ON DELETE SET NULL;
ALTER TABLE contracts ADD COLUMN is_directive_orchestrator BOOLEAN NOT NULL DEFAULT false;
ALTER TABLE contracts ADD COLUMN spawned_directive_id UUID REFERENCES directives(id) ON DELETE SET NULL;

-- ============================================================================
-- 2. DIRECTIVE CHAINS -- generated execution plans
-- ============================================================================
CREATE TABLE directive_chains (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    directive_id UUID NOT NULL REFERENCES directives(id) ON DELETE CASCADE,
    generation INTEGER NOT NULL DEFAULT 1,

    -- Plan metadata
    name VARCHAR(255) NOT NULL,
    description TEXT,
    rationale TEXT,
    planning_model VARCHAR(100),

    -- State
    status VARCHAR(32) NOT NULL DEFAULT 'pending',

    -- Execution tracking
    total_steps INTEGER NOT NULL DEFAULT 0,
    completed_steps INTEGER NOT NULL DEFAULT 0,
    failed_steps INTEGER NOT NULL DEFAULT 0,
    current_confidence FLOAT,

    -- Timestamps
    started_at TIMESTAMPTZ,
    completed_at TIMESTAMPTZ,
    version INTEGER NOT NULL DEFAULT 1,
    created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

CREATE INDEX idx_directive_chains_directive ON directive_chains(directive_id);
CREATE INDEX idx_directive_chains_status ON directive_chains(status);

-- Add FK from directives to chains
ALTER TABLE directives
    ADD CONSTRAINT fk_directives_current_chain
    FOREIGN KEY (current_chain_id) REFERENCES directive_chains(id)
    ON DELETE SET NULL;

-- ============================================================================
-- 3. CHAIN STEPS -- nodes in the DAG
-- ============================================================================
CREATE TABLE chain_steps (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    chain_id UUID NOT NULL REFERENCES directive_chains(id) ON DELETE CASCADE,

    -- Step definition
    name VARCHAR(255) NOT NULL,
    description TEXT,
    step_type VARCHAR(32) NOT NULL DEFAULT 'execute',

    -- Contract template
    contract_type VARCHAR(32) NOT NULL DEFAULT 'simple',
    initial_phase VARCHAR(32) DEFAULT 'plan',
    task_plan TEXT,
    phases TEXT[] DEFAULT '{}',

    -- DAG edges
    depends_on UUID[] DEFAULT '{}',
    parallel_group VARCHAR(100),

    -- Requirement traceability
    requirement_ids TEXT[] DEFAULT '{}',
    acceptance_criteria_ids TEXT[] DEFAULT '{}',

    -- Verification configuration
    verifier_config JSONB DEFAULT '{}',

    -- State
    status VARCHAR(32) NOT NULL DEFAULT 'pending',

    -- Instantiated references
    contract_id UUID REFERENCES contracts(id) ON DELETE SET NULL,
    supervisor_task_id UUID,

    -- Evaluation tracking
    confidence_score FLOAT,
    confidence_level VARCHAR(10),
    evaluation_count INTEGER NOT NULL DEFAULT 0,
    rework_count INTEGER NOT NULL DEFAULT 0,
    last_evaluation_id UUID,

    -- Editor layout
    editor_x FLOAT DEFAULT 0,
    editor_y FLOAT DEFAULT 0,
    order_index INTEGER NOT NULL DEFAULT 0,

    -- Timestamps
    started_at TIMESTAMPTZ,
    completed_at TIMESTAMPTZ,
    created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

CREATE INDEX idx_chain_steps_chain ON chain_steps(chain_id);
CREATE INDEX idx_chain_steps_status ON chain_steps(status);
CREATE INDEX idx_chain_steps_contract ON chain_steps(contract_id) WHERE contract_id IS NOT NULL;

-- ============================================================================
-- 4. EVALUATIONS -- programmatic + LLM composite
-- ============================================================================
CREATE TABLE directive_evaluations (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    directive_id UUID NOT NULL REFERENCES directives(id) ON DELETE CASCADE,
    chain_id UUID REFERENCES directive_chains(id) ON DELETE SET NULL,
    step_id UUID REFERENCES chain_steps(id) ON DELETE SET NULL,
    contract_id UUID REFERENCES contracts(id) ON DELETE SET NULL,

    -- Evaluation metadata
    evaluation_type VARCHAR(32) NOT NULL,
    evaluation_number INTEGER NOT NULL DEFAULT 1,
    evaluator VARCHAR(100),

    -- Results
    passed BOOLEAN NOT NULL,
    overall_score FLOAT,
    confidence_level VARCHAR(10),

    -- Programmatic results
    programmatic_results JSONB DEFAULT '[]',

    -- LLM evaluation results
    llm_results JSONB DEFAULT '{}',

    -- Composite results
    criteria_results JSONB NOT NULL DEFAULT '[]',
    summary_feedback TEXT NOT NULL DEFAULT '',
    rework_instructions TEXT,

    -- Snapshots
    directive_snapshot JSONB,
    deliverables_snapshot JSONB,

    -- Timing
    started_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    completed_at TIMESTAMPTZ,
    created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

CREATE INDEX idx_evaluations_directive ON directive_evaluations(directive_id);
CREATE INDEX idx_evaluations_step ON directive_evaluations(step_id);
CREATE INDEX idx_evaluations_chain ON directive_evaluations(chain_id);

-- Add FK from chain_steps to evaluations
ALTER TABLE chain_steps
    ADD CONSTRAINT fk_steps_last_evaluation
    FOREIGN KEY (last_evaluation_id) REFERENCES directive_evaluations(id)
    ON DELETE SET NULL;

-- ============================================================================
-- 5. EVENTS -- comprehensive audit stream
-- ============================================================================
CREATE TABLE directive_events (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    directive_id UUID NOT NULL REFERENCES directives(id) ON DELETE CASCADE,
    chain_id UUID REFERENCES directive_chains(id) ON DELETE SET NULL,
    step_id UUID REFERENCES chain_steps(id) ON DELETE SET NULL,

    -- Event classification
    event_type VARCHAR(64) NOT NULL,
    severity VARCHAR(16) NOT NULL DEFAULT 'info',

    -- Payload
    event_data JSONB,

    -- Actor
    actor_type VARCHAR(32) NOT NULL DEFAULT 'system',
    actor_id UUID,

    created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

CREATE INDEX idx_events_directive ON directive_events(directive_id);
CREATE INDEX idx_events_chain ON directive_events(chain_id);
CREATE INDEX idx_events_step ON directive_events(step_id);
CREATE INDEX idx_events_type ON directive_events(event_type);
CREATE INDEX idx_events_created ON directive_events(created_at);

-- ============================================================================
-- 6. VERIFIERS -- pluggable verification config
-- ============================================================================
CREATE TABLE directive_verifiers (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    directive_id UUID NOT NULL REFERENCES directives(id) ON DELETE CASCADE,

    -- Definition
    name VARCHAR(100) NOT NULL,
    verifier_type VARCHAR(32) NOT NULL,

    -- Configuration
    command VARCHAR(1000),
    working_directory VARCHAR(500),
    timeout_seconds INTEGER DEFAULT 300,
    environment JSONB DEFAULT '{}',

    -- Detection
    auto_detect BOOLEAN NOT NULL DEFAULT true,
    detect_files TEXT[] DEFAULT '{}',

    -- Scoring
    weight FLOAT NOT NULL DEFAULT 1.0,
    required BOOLEAN NOT NULL DEFAULT false,

    -- State
    enabled BOOLEAN NOT NULL DEFAULT true,
    last_run_at TIMESTAMPTZ,
    last_result JSONB,

    created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

CREATE INDEX idx_verifiers_directive ON directive_verifiers(directive_id);

-- ============================================================================
-- 7. APPROVALS -- human-in-the-loop gates
-- ============================================================================
CREATE TABLE directive_approvals (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    directive_id UUID NOT NULL REFERENCES directives(id) ON DELETE CASCADE,
    step_id UUID REFERENCES chain_steps(id) ON DELETE SET NULL,

    -- Request
    approval_type VARCHAR(64) NOT NULL,
    description TEXT NOT NULL,
    context JSONB,
    urgency VARCHAR(16) NOT NULL DEFAULT 'normal',

    -- Response
    status VARCHAR(32) NOT NULL DEFAULT 'pending',
    response TEXT,
    responded_by UUID,
    responded_at TIMESTAMPTZ,

    expires_at TIMESTAMPTZ,
    created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

CREATE INDEX idx_approvals_directive ON directive_approvals(directive_id);
CREATE INDEX idx_approvals_status ON directive_approvals(status);