schema.sql

-- OpenClaw Task Tracker — PostgreSQL Schema
-- Run this against your database to set up the task tracking tables.

-- Helper function for auto-updating timestamps (skip if you already have one)
CREATE OR REPLACE FUNCTION update_updated_at()
RETURNS TRIGGER AS $$
BEGIN
    NEW.updated_at = now();
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

-- ─── Tasks ───
CREATE TABLE IF NOT EXISTS tasks (
    id              SERIAL PRIMARY KEY,
    title           VARCHAR(255) NOT NULL,
    description     TEXT,
    status          VARCHAR(20) NOT NULL DEFAULT 'pending'
                    CHECK (status IN ('pending', 'in-progress', 'done', 'blocked', 'cancelled')),
    priority        VARCHAR(10) NOT NULL DEFAULT 'medium'
                    CHECK (priority IN ('low', 'medium', 'high', 'urgent')),
    due_date        DATE,
    parent_id       INTEGER REFERENCES tasks(id) ON DELETE SET NULL,
    blocked_by      INTEGER REFERENCES tasks(id) ON DELETE SET NULL,
    tags            TEXT[],
    notes           TEXT,
    created_at      TIMESTAMPTZ DEFAULT now(),
    updated_at      TIMESTAMPTZ DEFAULT now(),
    completed_at    TIMESTAMPTZ
);

CREATE INDEX IF NOT EXISTS idx_tasks_status ON tasks(status);
CREATE INDEX IF NOT EXISTS idx_tasks_priority ON tasks(priority);
CREATE INDEX IF NOT EXISTS idx_tasks_due_date ON tasks(due_date);
CREATE INDEX IF NOT EXISTS idx_tasks_parent ON tasks(parent_id);
CREATE INDEX IF NOT EXISTS idx_tasks_tags ON tasks USING GIN(tags);

CREATE TRIGGER tasks_update_timestamp
    BEFORE UPDATE ON tasks
    FOR EACH ROW EXECUTE FUNCTION update_updated_at();

-- ─── Task References ───
-- Context sources (Slack threads, emails, docs) that help an agent understand task background.
CREATE TABLE IF NOT EXISTS task_references (
    id          SERIAL PRIMARY KEY,
    task_id     INTEGER NOT NULL REFERENCES tasks(id) ON DELETE CASCADE,
    source_type VARCHAR(50) NOT NULL,  -- 'slack_thread', 'email', 'google_doc', 'url', etc.
    source_id   TEXT,                  -- channel+ts, message ID, doc ID, etc.
    url         TEXT,                  -- direct link if available
    label       VARCHAR(255),          -- human-readable description
    created_at  TIMESTAMPTZ DEFAULT now()
);

CREATE INDEX IF NOT EXISTS idx_task_refs_task ON task_references(task_id);
CREATE INDEX IF NOT EXISTS idx_task_refs_source ON task_references(source_type);

-- ─── Task Milestones ───
CREATE TABLE IF NOT EXISTS task_milestones (
    id          SERIAL PRIMARY KEY,
    task_id     INTEGER NOT NULL REFERENCES tasks(id) ON DELETE CASCADE,
    title       VARCHAR(255) NOT NULL,
    done        BOOLEAN DEFAULT false,
    due_date    DATE,
    completed_at TIMESTAMPTZ,
    sort_order  INTEGER DEFAULT 0
);

CREATE INDEX IF NOT EXISTS idx_milestones_task ON task_milestones(task_id);

-- ─── Task Log ───
-- Append-only audit trail of changes and notes.
CREATE TABLE IF NOT EXISTS task_log (
    id          SERIAL PRIMARY KEY,
    task_id     INTEGER NOT NULL REFERENCES tasks(id) ON DELETE CASCADE,
    entry       TEXT NOT NULL,
    created_at  TIMESTAMPTZ DEFAULT now()
);

CREATE INDEX IF NOT EXISTS idx_task_log_task ON task_log(task_id);

-- ─── Sub-agent Jobs ───
-- Track ephemeral sub-agent work sessions linked to parent tasks.
CREATE TABLE IF NOT EXISTS subagent_jobs (
    id             SERIAL PRIMARY KEY,
    session_key    TEXT NOT NULL,
    archetype      VARCHAR(50),
    description    TEXT NOT NULL,
    parent_task_id INTEGER REFERENCES tasks(id) ON DELETE SET NULL,
    status         VARCHAR(20) NOT NULL DEFAULT 'running'
                   CHECK (status IN ('running', 'completed', 'failed', 'killed')),
    result_summary TEXT,
    result_path    TEXT,
    started_at     TIMESTAMPTZ DEFAULT now(),
    completed_at   TIMESTAMPTZ
);

CREATE INDEX IF NOT EXISTS idx_subagent_jobs_status ON subagent_jobs(status);
CREATE INDEX IF NOT EXISTS idx_subagent_jobs_parent ON subagent_jobs(parent_task_id);