File size: 3,436 Bytes
789f479 | 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 | -- =============================================================
-- CognifyAI — Supabase Schema Migration
-- Run this entire script in:
-- Supabase Dashboard → SQL Editor → New Query → Run
-- =============================================================
-- 1. Enable the pgvector extension (if not already enabled)
CREATE EXTENSION IF NOT EXISTS vector;
-- 2. Documents table (stores text chunks + their vector embeddings)
CREATE TABLE IF NOT EXISTS documents (
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
user_id UUID, -- Supabase auth.uid() — data owner
text TEXT NOT NULL,
topic VARCHAR(255),
source VARCHAR(512),
embedding VECTOR(768), -- BAAI/bge-base-en-v1.5 = 768 dims
created_at TIMESTAMPTZ DEFAULT NOW()
);
-- 2b. Add user_id to existing documents table (safe if column already exists)
ALTER TABLE documents ADD COLUMN IF NOT EXISTS user_id UUID;
-- 3. Index for fast cosine similarity search on embeddings
CREATE INDEX IF NOT EXISTS documents_embedding_idx
ON documents
USING ivfflat (embedding vector_cosine_ops)
WITH (lists = 100);
-- 3b. Index for fast user-scoped queries
CREATE INDEX IF NOT EXISTS documents_user_id_idx
ON documents (user_id);
-- 4. Users table
CREATE TABLE IF NOT EXISTS users (
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
name VARCHAR(255),
learner_type VARCHAR(50) DEFAULT 'Textual', -- Visual | Textual | Auditory
created_at TIMESTAMPTZ DEFAULT NOW()
);
-- 5. Quiz sessions (one per quiz attempt)
CREATE TABLE IF NOT EXISTS quiz_sessions (
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
user_id UUID REFERENCES users(id) ON DELETE SET NULL,
topic VARCHAR(255) NOT NULL,
learner_type VARCHAR(50),
created_at TIMESTAMPTZ DEFAULT NOW()
);
-- 6. Individual question attempts within a session
CREATE TABLE IF NOT EXISTS quiz_attempts (
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
session_id UUID NOT NULL REFERENCES quiz_sessions(id) ON DELETE CASCADE,
question TEXT NOT NULL,
selected_answer TEXT,
correct_answer TEXT,
is_correct BOOLEAN DEFAULT FALSE,
misconception_tag VARCHAR(255),
created_at TIMESTAMPTZ DEFAULT NOW()
);
-- 7. Semantic search function (user-scoped)
-- Returns top-k most similar chunks for a specific user using cosine distance.
CREATE OR REPLACE FUNCTION match_documents(
query_embedding VECTOR(768),
match_count INT DEFAULT 5,
filter_topic VARCHAR DEFAULT NULL,
p_user_id UUID DEFAULT NULL -- only return this user's documents
)
RETURNS TABLE (
id UUID,
text TEXT,
topic VARCHAR,
source VARCHAR,
score FLOAT
)
LANGUAGE plpgsql
AS $$
BEGIN
RETURN QUERY
SELECT
d.id,
d.text,
d.topic,
d.source,
1 - (d.embedding <=> query_embedding) AS score
FROM documents d
WHERE
(p_user_id IS NULL OR d.user_id = p_user_id)
AND (filter_topic IS NULL OR d.topic = filter_topic)
ORDER BY d.embedding <=> query_embedding
LIMIT match_count;
END;
$$;
-- 8. (Optional) Clean up old documents with no user_id
-- Uncomment and run this if you want to remove shared/legacy data:
-- DELETE FROM documents WHERE user_id IS NULL;
|