Spaces:
Sleeping
Sleeping
File size: 5,527 Bytes
fa1fead | 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 | -- vectorstore/schema.sql
-- WHY THIS EXISTS: Creates pgvector tables with RLS enforced.
-- Per-user document isolation at database level. LAW 6.
-- Run this ONCE in Supabase SQL editor before any ingestion.
-- Enable pgvector extension
CREATE EXTENSION IF NOT EXISTS vector;
CREATE EXTENSION IF NOT EXISTS "uuid-ossp";
-- ββ Documents table ββββββββββββββββββββββββββββββββββββββββββββββββββββββββββ
-- One row per uploaded PDF document
CREATE TABLE IF NOT EXISTS documents (
doc_id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
user_id TEXT NOT NULL,
file_name TEXT NOT NULL,
file_path TEXT NOT NULL,
doc_hash TEXT NOT NULL UNIQUE, -- SHA-256, prevents duplicate ingestion
title TEXT NOT NULL,
primary_language TEXT NOT NULL DEFAULT 'en',
doc_type TEXT NOT NULL DEFAULT 'other',
total_pages INTEGER NOT NULL DEFAULT 0,
total_chunks INTEGER NOT NULL DEFAULT 0,
ingestion_status TEXT NOT NULL DEFAULT 'pending',
error_message TEXT,
created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);
-- ββ Chunks table βββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββ
-- One row per text chunk with embedding vector
CREATE TABLE IF NOT EXISTS document_chunks (
chunk_id TEXT PRIMARY KEY,
doc_id UUID NOT NULL REFERENCES documents(doc_id) ON DELETE CASCADE,
user_id TEXT NOT NULL, -- NEVER NULL β per-user isolation
source TEXT NOT NULL,
title TEXT NOT NULL,
doc_primary_language TEXT NOT NULL,
chunk_language TEXT NOT NULL,
script_type TEXT NOT NULL,
doc_type TEXT NOT NULL DEFAULT 'other',
page_number INTEGER NOT NULL,
section_heading TEXT DEFAULT '',
chunk_index INTEGER NOT NULL,
total_chunks INTEGER NOT NULL,
parent_chunk_id TEXT DEFAULT '',
is_parent BOOLEAN NOT NULL DEFAULT FALSE,
embedding vector(1024), -- multilingual-e5-large dim
chunk_text TEXT NOT NULL,
embedding_model TEXT NOT NULL DEFAULT 'intfloat/multilingual-e5-large',
embedding_model_version TEXT NOT NULL DEFAULT '1.0.0',
injection_risk BOOLEAN NOT NULL DEFAULT FALSE,
pii_detected BOOLEAN NOT NULL DEFAULT FALSE,
doc_hash TEXT NOT NULL,
created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);
-- ββ Indexes ββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββ
-- Vector similarity search index (IVFFlat for production)
CREATE INDEX IF NOT EXISTS idx_chunks_embedding
ON document_chunks
USING ivfflat (embedding vector_cosine_ops)
WITH (lists = 100);
-- Per-user query performance
CREATE INDEX IF NOT EXISTS idx_chunks_user_id
ON document_chunks (user_id);
-- Per-document query performance
CREATE INDEX IF NOT EXISTS idx_chunks_doc_id
ON document_chunks (doc_id);
-- Document deduplication
CREATE INDEX IF NOT EXISTS idx_documents_hash
ON documents (doc_hash);
-- Per-user document listing
CREATE INDEX IF NOT EXISTS idx_documents_user_id
ON documents (user_id);
-- ββ Row Level Security βββββββββββββββββββββββββββββββββββββββββββββββββββββββ
-- Users can ONLY see their own documents. Enforced at DB level.
ALTER TABLE documents ENABLE ROW LEVEL SECURITY;
ALTER TABLE document_chunks ENABLE ROW LEVEL SECURITY;
-- Documents RLS policies
CREATE POLICY "Users can only see their own documents"
ON documents FOR SELECT
USING (user_id = current_setting('app.current_user_id', TRUE));
CREATE POLICY "Users can only insert their own documents"
ON documents FOR INSERT
WITH CHECK (user_id = current_setting('app.current_user_id', TRUE));
CREATE POLICY "Users can only delete their own documents"
ON documents FOR DELETE
USING (user_id = current_setting('app.current_user_id', TRUE));
-- Chunks RLS policies
CREATE POLICY "Users can only see their own chunks"
ON document_chunks FOR SELECT
USING (user_id = current_setting('app.current_user_id', TRUE));
CREATE POLICY "Users can only insert their own chunks"
ON document_chunks FOR INSERT
WITH CHECK (user_id = current_setting('app.current_user_id', TRUE));
CREATE POLICY "Users can only delete their own chunks"
ON document_chunks FOR DELETE
USING (user_id = current_setting('app.current_user_id', TRUE));
-- ββ Service role bypass (for server-side operations) βββββββββββββββββββββββββ
-- The service role key bypasses RLS β only use server-side, never in client
CREATE POLICY "Service role bypass documents"
ON documents FOR ALL
USING (TRUE)
WITH CHECK (TRUE);
CREATE POLICY "Service role bypass chunks"
ON document_chunks FOR ALL
USING (TRUE)
WITH CHECK (TRUE); |