Spaces:
Running
Running
Download vectorstore/schema.sql from Sahilalaknur/smartdocs-ui: direct link, hf CLI and curl.
- Browser
- Download file 5.53 kB
-
https://huggingface.co/spaces/Sahilalaknur/smartdocs-ui/resolve/main/vectorstore/schema.sql
- Command line
-
hf download hf://spaces/Sahilalaknur/smartdocs-ui/vectorstore/schema.sql
-
curl -L -o schema.sql https://huggingface.co/spaces/Sahilalaknur/smartdocs-ui/resolve/main/vectorstore/schema.sql
5.53 kB
| -- 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); |