Spaces:
Sleeping
Sleeping
File size: 2,438 Bytes
0e39d80 | 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 | -- DocVerify AI — Supabase Migration
-- Run this in the Supabase SQL Editor: https://app.supabase.com/project/uymdjpfognbqcsotjztr/sql
-- Create documents table
CREATE TABLE IF NOT EXISTS documents (
id VARCHAR(36) PRIMARY KEY DEFAULT gen_random_uuid()::text,
doc_type VARCHAR(32) NOT NULL,
status VARCHAR(64) NOT NULL DEFAULT 'Pending',
confidence_score FLOAT,
flags TEXT NOT NULL DEFAULT '[]',
extracted_fields TEXT NOT NULL DEFAULT '{}',
text_source VARCHAR(16),
original_filename VARCHAR(255),
masked_image_path VARCHAR(512),
verification_status VARCHAR(64),
-- OCR data
full_text TEXT,
score_breakdown TEXT,
ocr_confidence FLOAT,
-- Review fields
reviewer_notes TEXT,
reviewed_by VARCHAR(128),
reviewed_at TIMESTAMPTZ,
-- Image storage as base64 for cloud persistence
image_base64 TEXT,
masked_image_base64 TEXT,
created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);
-- Gemini AI audit fields (additive — safe on existing tables)
ALTER TABLE documents ADD COLUMN IF NOT EXISTS gemini_model VARCHAR(64);
ALTER TABLE documents ADD COLUMN IF NOT EXISTS gemini_raw_json TEXT;
ALTER TABLE documents ADD COLUMN IF NOT EXISTS forgery_score FLOAT;
ALTER TABLE documents ADD COLUMN IF NOT EXISTS forgery_reason TEXT;
ALTER TABLE documents ADD COLUMN IF NOT EXISTS ai_confidence FLOAT;
ALTER TABLE documents ADD COLUMN IF NOT EXISTS ai_powered BOOLEAN DEFAULT FALSE;
ALTER TABLE documents ADD COLUMN IF NOT EXISTS gemini_key_index SMALLINT;
-- Index for fast queries
CREATE INDEX IF NOT EXISTS idx_documents_doc_type ON documents(doc_type);
CREATE INDEX IF NOT EXISTS idx_documents_status ON documents(status);
CREATE INDEX IF NOT EXISTS idx_documents_created_at ON documents(created_at DESC);
-- Auto-update updated_at
CREATE OR REPLACE FUNCTION update_updated_at()
RETURNS TRIGGER AS $$
BEGIN
NEW.updated_at = NOW();
RETURN NEW;
END;
$$ LANGUAGE plpgsql;
DROP TRIGGER IF EXISTS trg_documents_updated_at ON documents;
CREATE TRIGGER trg_documents_updated_at
BEFORE UPDATE ON documents
FOR EACH ROW
EXECUTE FUNCTION update_updated_at();
-- Enable Row Level Security (optional, adjust for your auth setup)
-- ALTER TABLE documents ENABLE ROW LEVEL SECURITY;
|