Spaces:
Runtime error
Runtime error
File size: 6,689 Bytes
12bad22 | 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 | -- ==========================================================================
-- 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.
-- --------------------------------------------------------------------------
|