init.sql

CREATE TABLE IF NOT EXISTS users (
    id          SERIAL PRIMARY KEY,
    google_sub  TEXT UNIQUE NOT NULL,
    email       TEXT UNIQUE NOT NULL,
    name        TEXT,
    picture     TEXT,
    created_at  TIMESTAMPTZ DEFAULT NOW()
);

CREATE TABLE IF NOT EXISTS user_hidden_papers (
    user_id  INTEGER NOT NULL REFERENCES users(id) ON DELETE CASCADE,
    paper_id TEXT    NOT NULL,
    PRIMARY KEY (user_id, paper_id)
);

CREATE EXTENSION IF NOT EXISTS vector;

CREATE TABLE IF NOT EXISTS chunks (
    id                SERIAL PRIMARY KEY,
    chunk_id          TEXT UNIQUE NOT NULL,
    paper_id          TEXT NOT NULL,
    file_name         TEXT,
    source            TEXT,
    chunk_index       INTEGER NOT NULL,
    total_chunks_for_paper INTEGER NOT NULL,
    text              TEXT NOT NULL,
    embedding         VECTOR(1536),  -- text-embedding-3-small output dimension
    uploaded_by_user_id INTEGER REFERENCES users(id) ON DELETE SET NULL,
    created_at        TIMESTAMPTZ DEFAULT NOW()
);

DROP INDEX IF EXISTS idx_chunks_embedding;

CREATE INDEX IF NOT EXISTS idx_chunks_paper_id
    ON chunks (paper_id);

CREATE INDEX IF NOT EXISTS idx_chunks_chunk_id
    ON chunks (chunk_id);

-- vector index (use cosine distance)
CREATE INDEX IF NOT EXISTS idx_chunks_embedding
    ON chunks USING ivfflat (embedding vector_cosine_ops)
    WITH (lists = 100);