db/schema.sql

-- ==========================================================================
-- DocuMint AI — Supabase Schema
--
-- Run this in the Supabase SQL Editor to create all required tables.
-- Safe to re-run: every CREATE uses IF NOT EXISTS.
-- ==========================================================================

-- Enable uuid-ossp for uuid_generate_v4() if not already enabled.
CREATE EXTENSION IF NOT EXISTS "uuid-ossp";

-- --------------------------------------------------------------------------
-- 1. documents — metadata for every uploaded file
-- --------------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS documents (
    id          UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    org_id      TEXT,
    user_id     TEXT,
    filename    TEXT NOT NULL,
    file_type   TEXT NOT NULL CHECK (file_type IN ('image', 'pdf')),
    file_size   INTEGER NOT NULL DEFAULT 0,
    page_count  INTEGER NOT NULL DEFAULT 1,
    created_at  TIMESTAMPTZ NOT NULL DEFAULT now()
);

CREATE INDEX IF NOT EXISTS idx_documents_org   ON documents (org_id)  WHERE org_id IS NOT NULL;
CREATE INDEX IF NOT EXISTS idx_documents_user  ON documents (user_id) WHERE user_id IS NOT NULL;

-- --------------------------------------------------------------------------
-- 2. pipeline_runs — one row per extraction + validation pipeline execution
-- --------------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS pipeline_runs (
    id                UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    document_id       UUID REFERENCES documents(id) ON DELETE SET NULL,
    org_id            TEXT,
    user_id           TEXT,
    use_case          TEXT NOT NULL DEFAULT 'invoice',
    status            TEXT NOT NULL DEFAULT 'running'
                          CHECK (status IN ('running', 'completed', 'failed')),
    overall_result    TEXT CHECK (overall_result IN ('passed', 'failed', 'needs_review')),
    extraction_data   JSONB,
    processing_time_ms INTEGER,
    error_message     TEXT,
    started_at        TIMESTAMPTZ NOT NULL DEFAULT now(),
    completed_at      TIMESTAMPTZ
);

CREATE INDEX IF NOT EXISTS idx_runs_org        ON pipeline_runs (org_id)  WHERE org_id IS NOT NULL;
CREATE INDEX IF NOT EXISTS idx_runs_user       ON pipeline_runs (user_id) WHERE user_id IS NOT NULL;
CREATE INDEX IF NOT EXISTS idx_runs_status     ON pipeline_runs (status);
CREATE INDEX IF NOT EXISTS idx_runs_result     ON pipeline_runs (overall_result) WHERE overall_result IS NOT NULL;
CREATE INDEX IF NOT EXISTS idx_runs_started    ON pipeline_runs (started_at DESC);
CREATE INDEX IF NOT EXISTS idx_runs_doc        ON pipeline_runs (document_id) WHERE document_id IS NOT NULL;

-- --------------------------------------------------------------------------
-- 3. stage_results — per-stage breakdown within a pipeline run
-- --------------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS stage_results (
    id           UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    run_id       UUID NOT NULL REFERENCES pipeline_runs(id) ON DELETE CASCADE,
    stage_type   TEXT NOT NULL,          -- 'upload', 'extraction', 'validation'
    stage_name   TEXT NOT NULL,          -- human-friendly label
    status       TEXT NOT NULL CHECK (status IN ('passed', 'failed', 'needs_review', 'skipped')),
    sort_order   INTEGER NOT NULL DEFAULT 0,
    output       JSONB,
    error_message TEXT,
    duration_ms  INTEGER,
    started_at   TIMESTAMPTZ NOT NULL DEFAULT now(),
    completed_at TIMESTAMPTZ DEFAULT now()
);

CREATE INDEX IF NOT EXISTS idx_stages_run ON stage_results (run_id);

-- --------------------------------------------------------------------------
-- 4. prompt_versions — versioned extraction / validation prompts
-- --------------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS prompt_versions (
    id          UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    use_case    TEXT NOT NULL,            -- 'invoice', 'receipt', '_default', …
    stage       TEXT NOT NULL,            -- 'extraction', 'validation'
    version     INTEGER NOT NULL DEFAULT 1,
    prompt_text TEXT NOT NULL,
    is_active   BOOLEAN NOT NULL DEFAULT false,
    created_by  TEXT,                     -- user or 'system'
    created_at  TIMESTAMPTZ NOT NULL DEFAULT now()
);

-- Only one active prompt per (use_case, stage) at a time.
CREATE UNIQUE INDEX IF NOT EXISTS idx_prompt_active
    ON prompt_versions (use_case, stage)
    WHERE is_active = true;

CREATE INDEX IF NOT EXISTS idx_prompt_lookup
    ON prompt_versions (use_case, stage, version DESC);

-- --------------------------------------------------------------------------
-- 5. api_keys — optional table for storing API keys in Supabase
--    (alternative to the API_KEYS env var)
-- --------------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS api_keys (
    id          UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    key_hash    TEXT NOT NULL UNIQUE,     -- SHA-256 hex digest of the raw key
    name        TEXT,                     -- human label ("Frontend prod", "CI")
    org_id      TEXT,
    user_id     TEXT,
    is_active   BOOLEAN NOT NULL DEFAULT true,
    created_at  TIMESTAMPTZ NOT NULL DEFAULT now(),
    last_used   TIMESTAMPTZ
);

CREATE INDEX IF NOT EXISTS idx_apikeys_hash ON api_keys (key_hash) WHERE is_active = true;

-- --------------------------------------------------------------------------
-- Row-Level Security (RLS) — enable on all tables
--
-- By default the service_role key (used by the backend) bypasses RLS.
-- These policies protect against anon/client-side access.
-- --------------------------------------------------------------------------

ALTER TABLE documents      ENABLE ROW LEVEL SECURITY;
ALTER TABLE pipeline_runs  ENABLE ROW LEVEL SECURITY;
ALTER TABLE stage_results  ENABLE ROW LEVEL SECURITY;
ALTER TABLE prompt_versions ENABLE ROW LEVEL SECURITY;
ALTER TABLE api_keys       ENABLE ROW LEVEL SECURITY;

-- Allow service_role full access (default Supabase behaviour).
-- Add more granular policies when enabling multi-tenant client access.

-- --------------------------------------------------------------------------
-- Done!  Set these env vars in your HF Space / deploy environment:
--
--   SUPABASE_URL  = https://<project-ref>.supabase.co
--   SUPABASE_KEY  = <service_role key — NOT the anon key>
--
-- The service_role key bypasses RLS and is needed for backend operations.
-- Never expose it to the frontend.
-- --------------------------------------------------------------------------