-- Job results table to store sync job outcomes
CREATE TABLE job_results (
    job_id UUID PRIMARY KEY,
    user_did TEXT NOT NULL,
    slice_uri TEXT NOT NULL,
    status TEXT NOT NULL CHECK (status IN ('completed', 'failed')),
    success BOOLEAN NOT NULL,
    total_records BIGINT NOT NULL DEFAULT 0,
    collections_synced JSONB NOT NULL DEFAULT '[]'::jsonb,
    repos_processed BIGINT NOT NULL DEFAULT 0,
    message TEXT NOT NULL,
    error_message TEXT,
    created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    completed_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

-- Index for looking up jobs by user
CREATE INDEX idx_job_results_user_did ON job_results(user_did);
-- Index for looking up recent jobs
CREATE INDEX idx_job_results_completed_at ON job_results(completed_at DESC);