smartdocs-ui / vectorstore /schema.sql
Sahilalaknur's picture
Bulletproof lightweight UI deployment
877a8a5
Raw History Blame Contribute Delete
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);