-- PartnerLogic / MLT Aikins private instance
-- Enable pgvector and create the retrieval table.

CREATE EXTENSION IF NOT EXISTS vector;

CREATE TABLE IF NOT EXISTS document_chunks (
    id            BIGSERIAL PRIMARY KEY,
    text_chunk    TEXT NOT NULL,
    embedding     VECTOR(768) NOT NULL,
    source_file   TEXT,
    chunk_index   INTEGER,
    created_at    TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

-- Cosine distance operator on <=> requires an ivfflat or hnsw index.
-- HNSW is preferred for a PoC-scale corpus and exact-enough recall.
CREATE INDEX IF NOT EXISTS document_chunks_embedding_hnsw
    ON document_chunks
    USING hnsw (embedding vector_cosine_ops);

CREATE INDEX IF NOT EXISTS document_chunks_source_idx
    ON document_chunks (source_file);
