director-app/schema.sql

-- Director Schema v1 — Phase 1 Foundation
-- Based on Director_MasterPlan_v1.2.md

-- Plans
CREATE TABLE plans (
    id              SERIAL PRIMARY KEY,
    title           TEXT NOT NULL,
    description     TEXT NOT NULL,
    team            TEXT NOT NULL CHECK (team IN ('tech', 'comms')),
    priority        TEXT NOT NULL DEFAULT '3' CHECK (priority IN ('critical', '1', '2', '3', '4', '5', 'backlog')),
    status          TEXT NOT NULL DEFAULT 'draft' CHECK (status IN ('draft', 'clarification', 'approved', 'in_progress', 'done')),
    created_by      TEXT NOT NULL DEFAULT 'director',
    created_at      TIMESTAMPTZ NOT NULL DEFAULT now(),
    updated_at      TIMESTAMPTZ NOT NULL DEFAULT now()
);

-- Tasks within plans
CREATE TABLE tasks (
    id              SERIAL PRIMARY KEY,
    plan_id         INTEGER NOT NULL REFERENCES plans(id) ON DELETE CASCADE,
    title           TEXT NOT NULL,
    description     TEXT,
    task_type       TEXT NOT NULL DEFAULT 'core' CHECK (task_type IN ('core', 'extended')),
    status          TEXT NOT NULL DEFAULT 'queued' CHECK (status IN ('queued', 'in_progress', 'blocked', 'complete', 'returned')),
    assigned_agent  TEXT,
    progress_note   TEXT,
    blockers        TEXT,
    sort_order      INTEGER NOT NULL DEFAULT 0,
    created_at      TIMESTAMPTZ NOT NULL DEFAULT now(),
    updated_at      TIMESTAMPTZ NOT NULL DEFAULT now()
);

-- Task artifacts (files, links, outputs produced by agents)
CREATE TABLE task_artifacts (
    id              SERIAL PRIMARY KEY,
    task_id         INTEGER NOT NULL REFERENCES tasks(id) ON DELETE CASCADE,
    artifact_type   TEXT NOT NULL DEFAULT 'file' CHECK (artifact_type IN ('file', 'url', 'text')),
    path            TEXT NOT NULL,
    description     TEXT,
    created_at      TIMESTAMPTZ NOT NULL DEFAULT now()
);

-- Agents registry
CREATE TABLE agents (
    id              TEXT PRIMARY KEY,
    name            TEXT NOT NULL,
    team            TEXT NOT NULL CHECK (team IN ('tech', 'comms')),
    role            TEXT NOT NULL,
    model_provider  TEXT NOT NULL,
    model_id        TEXT NOT NULL,
    gpu_default     TEXT,
    status          TEXT NOT NULL DEFAULT 'idle' CHECK (status IN ('idle', 'working', 'blocked', 'error', 'offline')),
    current_task_id INTEGER REFERENCES tasks(id) ON DELETE SET NULL,
    last_seen_at    TIMESTAMPTZ,
    created_at      TIMESTAMPTZ NOT NULL DEFAULT now()
);

-- Messages (director <-> team lead <-> agent communication)
CREATE TABLE messages (
    id              SERIAL PRIMARY KEY,
    plan_id         INTEGER REFERENCES plans(id) ON DELETE SET NULL,
    task_id         INTEGER REFERENCES tasks(id) ON DELETE SET NULL,
    from_role       TEXT NOT NULL CHECK (from_role IN ('director', 'lead', 'agent', 'system')),
    from_id         TEXT NOT NULL,
    message         TEXT NOT NULL,
    message_type    TEXT NOT NULL DEFAULT 'comment' CHECK (message_type IN ('comment', 'question', 'answer', 'escalation', 'status', 'completion')),
    created_at      TIMESTAMPTZ NOT NULL DEFAULT now()
);

-- Director queue (verified completions awaiting approval)
CREATE TABLE director_queue (
    id              SERIAL PRIMARY KEY,
    plan_id         INTEGER REFERENCES plans(id) ON DELETE CASCADE,
    task_id         INTEGER REFERENCES tasks(id) ON DELETE CASCADE,
    status          TEXT NOT NULL DEFAULT 'pending_verification' CHECK (status IN ('pending_verification', 'ready_for_approval', 'returned', 'approved', 'redirected')),
    verification_notes TEXT,
    director_notes  TEXT,
    submitted_at    TIMESTAMPTZ NOT NULL DEFAULT now(),
    reviewed_at     TIMESTAMPTZ
);

-- Pending questions queue
CREATE TABLE pending_questions (
    id              SERIAL PRIMARY KEY,
    plan_id         INTEGER REFERENCES plans(id) ON DELETE CASCADE,
    task_id         INTEGER REFERENCES tasks(id) ON DELETE CASCADE,
    from_role       TEXT NOT NULL CHECK (from_role IN ('lead', 'agent')),
    from_id         TEXT NOT NULL,
    question        TEXT NOT NULL,
    context         TEXT,
    priority        TEXT NOT NULL DEFAULT '3',
    status          TEXT NOT NULL DEFAULT 'awaiting_director' CHECK (status IN ('awaiting_director', 'answered', 'resolved')),
    answer          TEXT,
    created_at      TIMESTAMPTZ NOT NULL DEFAULT now(),
    answered_at     TIMESTAMPTZ
);

-- Indexes for common queries
CREATE INDEX idx_plans_status ON plans(status);
CREATE INDEX idx_plans_priority ON plans(priority);
CREATE INDEX idx_plans_team ON plans(team);
CREATE INDEX idx_tasks_plan_id ON tasks(plan_id);
CREATE INDEX idx_tasks_status ON tasks(status);
CREATE INDEX idx_tasks_assigned_agent ON tasks(assigned_agent);
CREATE INDEX idx_messages_plan_id ON messages(plan_id);
CREATE INDEX idx_director_queue_status ON director_queue(status);
CREATE INDEX idx_pending_questions_status ON pending_questions(status);

-- Updated_at trigger function
CREATE OR REPLACE FUNCTION update_updated_at()
RETURNS TRIGGER AS $$
BEGIN
    NEW.updated_at = now();
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER plans_updated_at BEFORE UPDATE ON plans FOR EACH ROW EXECUTE FUNCTION update_updated_at();
CREATE TRIGGER tasks_updated_at BEFORE UPDATE ON tasks FOR EACH ROW EXECUTE FUNCTION update_updated_at();