migrations/s359_task_persistence.sql

-- S359 — Agent task persistence across backend restarts
-- Run once in the Supabase SQL editor (Dashboard → SQL Editor → New query).
-- All statements are idempotent (IF NOT EXISTS / OR IGNORE).

-- ── agent_tasks ───────────────────────────────────────────────────────────────
-- Stores metadata for every agent task. Survives HuggingFace Space sleep/restart.
CREATE TABLE IF NOT EXISTS agent_tasks (
    task_id     TEXT        PRIMARY KEY,
    goal        TEXT        NOT NULL,
    status      TEXT        NOT NULL DEFAULT 'QUEUED',
    max_steps   INTEGER     NOT NULL DEFAULT 8,
    context     TEXT        NOT NULL DEFAULT '[]',   -- JSON array, truncated to 8 KB
    created_at  BIGINT      NOT NULL DEFAULT 0,
    updated_at  BIGINT      NOT NULL DEFAULT 0
);

-- ── agent_task_events ─────────────────────────────────────────────────────────
-- Stores the SSE event buffer per task (max 500 events, see persistence.py).
-- On reconnect after restart the backend replays these to the client.
CREATE TABLE IF NOT EXISTS agent_task_events (
    id           BIGSERIAL   PRIMARY KEY,
    task_id      TEXT        NOT NULL REFERENCES agent_tasks(task_id) ON DELETE CASCADE,
    event_index  INTEGER     NOT NULL,
    event_data   TEXT        NOT NULL,
    created_at   BIGINT      NOT NULL DEFAULT 0,
    UNIQUE (task_id, event_index)
);

CREATE INDEX IF NOT EXISTS idx_agent_task_events_lookup
    ON agent_task_events (task_id, event_index ASC);

-- ── Row-Level Security (optional but recommended) ─────────────────────────────
-- If your project uses RLS, enable it and add policies as needed.
-- ALTER TABLE agent_tasks        ENABLE ROW LEVEL SECURITY;
-- ALTER TABLE agent_task_events  ENABLE ROW LEVEL SECURITY;
-- Example (service-role only):
-- CREATE POLICY "service only" ON agent_tasks       FOR ALL USING (auth.role() = 'service_role');
-- CREATE POLICY "service only" ON agent_task_events FOR ALL USING (auth.role() = 'service_role');

-- ── Cleanup function (optional) ───────────────────────────────────────────────
-- Deletes tasks + events older than 7 days. Schedule via pg_cron or call manually.
CREATE OR REPLACE FUNCTION purge_old_agent_tasks() RETURNS void
LANGUAGE plpgsql AS $$
DECLARE
    cutoff BIGINT := (EXTRACT(EPOCH FROM NOW()) * 1000 - 7 * 86400000)::BIGINT;
BEGIN
    DELETE FROM agent_tasks WHERE updated_at < cutoff;
END;
$$;