schema.sql

-- grounded-rag schema. Run once against a Postgres instance with pgvector installed.

CREATE EXTENSION IF NOT EXISTS vector;

CREATE TABLE IF NOT EXISTS documents (
    id SERIAL PRIMARY KEY,
    source TEXT NOT NULL,
    title TEXT NOT NULL,
    created_at TIMESTAMPTZ DEFAULT NOW()
);

-- all-MiniLM-L6-v2 produces 384-dim embeddings.
CREATE TABLE IF NOT EXISTS chunks (
    id SERIAL PRIMARY KEY,
    document_id INTEGER NOT NULL REFERENCES documents(id) ON DELETE CASCADE,
    chunk_index INTEGER NOT NULL,
    content TEXT NOT NULL,
    embedding vector(384) NOT NULL,
    created_at TIMESTAMPTZ DEFAULT NOW()
);

-- ponytail: no approximate (ivfflat/hnsw) index. IVFFlat partitions rows
-- into `lists` clusters and only probes one by default — with a small
-- corpus (dozens to low hundreds of chunks) that means most clusters are
-- empty and queries can miss real matches entirely, not just rank them
-- lower. A sequential scan is both exact and fast at this scale. Add an
-- ivfflat or hnsw index once the corpus is large enough that a full scan
-- is actually the bottleneck — pgvector's own docs recommend the same.
CREATE INDEX IF NOT EXISTS idx_chunks_document_id ON chunks(document_id);