path: root/makima/migrations/20260207000000_directive_v2_wipe.sql

-- ============================================================================
-- Migration: directive_v2_wipe.sql
-- Wipes all directive system tables and recreates them clean.
-- This is a destructive migration — all directive data will be lost.
-- ============================================================================

-- Drop in reverse dependency order
DROP TABLE IF EXISTS directive_approvals CASCADE;
DROP TABLE IF EXISTS directive_verifiers CASCADE;
DROP TABLE IF EXISTS directive_events CASCADE;
DROP TABLE IF EXISTS directive_evaluations CASCADE;
DROP TABLE IF EXISTS chain_steps CASCADE;
DROP TABLE IF EXISTS directive_chains CASCADE;

-- Drop directive columns from contracts BEFORE dropping directives table
ALTER TABLE contracts DROP COLUMN IF EXISTS directive_id;
ALTER TABLE contracts DROP COLUMN IF EXISTS is_directive_orchestrator;
ALTER TABLE contracts DROP COLUMN IF EXISTS spawned_directive_id;

DROP TABLE IF EXISTS directives CASCADE;

-- ============================================================================
-- 1. DIRECTIVES
-- ============================================================================
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'
        CHECK (status IN ('draft', 'planning', 'active', 'paused', 'completed', 'archived', 'failed')),

    -- Autonomy configuration
    autonomy_level VARCHAR(32) NOT NULL DEFAULT 'guardrails'
        CHECK (autonomy_level IN ('full_auto', 'guardrails', 'manual')),
    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);
CREATE INDEX idx_directives_orchestrator_contract
    ON directives(orchestrator_contract_id)
    WHERE orchestrator_contract_id IS NOT NULL;

-- 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
-- ============================================================================
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'
        CHECK (status IN ('pending', 'running', 'completed', 'failed', 'superseded')),

    -- 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
-- ============================================================================
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'
        CHECK (status IN ('pending', 'ready', 'running', 'evaluating', 'passed', 'failed', 'rework', 'skipped', 'blocked')),

    -- 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;
CREATE INDEX idx_chain_steps_supervisor_task
    ON chain_steps(supervisor_task_id)
    WHERE supervisor_task_id IS NOT NULL;

-- ============================================================================
-- 4. EVALUATIONS
-- ============================================================================
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
-- ============================================================================
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
-- ============================================================================
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
-- ============================================================================
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'
        CHECK (status IN ('pending', 'approved', 'denied', 'expired')),
    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);