File size: 5,527 Bytes
877a8a5
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
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);