schema.sql

-- Task Queue Database Schema

CREATE TABLE IF NOT EXISTS tasks (
    task_id TEXT PRIMARY KEY,
    payload TEXT NOT NULL,
    priority INTEGER DEFAULT 0,
    status TEXT NOT NULL CHECK(status IN ('pending', 'in_progress', 'completed', 'failed')),
    retry_count INTEGER DEFAULT 0,
    max_retries INTEGER DEFAULT 3,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    started_at TIMESTAMP,
    completed_at TIMESTAMP
);

CREATE TABLE IF NOT EXISTS workers (
    worker_id TEXT PRIMARY KEY,
    last_heartbeat TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    status TEXT NOT NULL CHECK(status IN ('active', 'dead'))
);

CREATE TABLE IF NOT EXISTS dead_letter_queue (
    task_id TEXT PRIMARY KEY,
    payload TEXT NOT NULL,
    failure_reason TEXT,
    failed_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE INDEX IF NOT EXISTS idx_tasks_status ON tasks(status);
CREATE INDEX IF NOT EXISTS idx_tasks_priority ON tasks(priority DESC, created_at ASC);
CREATE INDEX IF NOT EXISTS idx_workers_status ON workers(status);