Postgres + pgvector RAG Pipeline

# RAG pipeline spec: [project name]

Build retrieval over [corpus: what it is, roughly how many documents] to
answer [who]'s questions about [what]. Build in the order below; the eval
(step 5) gates every retrieval change after the first working version.

## 1. Schema

```sql
CREATE EXTENSION IF NOT EXISTS vector;

CREATE TABLE documents (
  id          uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  source_uri  text NOT NULL UNIQUE,      -- file path or URL; identity for re-ingestion
  title       text NOT NULL,
  content_sha text NOT NULL,             -- hash of source; skip unchanged docs
  created_at  timestamptz NOT NULL DEFAULT now()
);

CREATE TABLE chunks (
  id          uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  document_id uuid NOT NULL REFERENCES documents (id) ON DELETE CASCADE,
  chunk_index int  NOT NULL,             -- position in doc; enables neighbor expansion
  content     text NOT NULL,
  embedding   vector([1536]) NOT NULL,   -- MUST match your embedding model's dims
  tsv         tsvector GENERATED ALWAYS AS (to_tsvector('english', content)) STORED,
  UNIQUE (document_id, chunk_index)
);

-- HNSW over cosine distance: good recall without tuning ivfflat lists.
CREATE INDEX chunks_embedding_idx ON chunks
  USING hnsw (embedding vector_cosine_ops);
CREATE INDEX chunks_tsv_idx ON chunks USING gin (tsv);
```

## 2. Config (one place, so tuning is editing, not archaeology)

- Embedding model: [name]. Dimensions: [1536]. NEVER mix models in one
  table: distances between different models' vectors are meaningless noise.
- Chunking: [~500 tokens, 50 overlap, split at paragraph boundaries first]
- Candidates per retriever: [20 vector, 20 keyword]. Fused list: top [5]
  to the prompt. More chunks is not more accuracy; it is more places for
  the model to find something almost-relevant to run with.

## 3. Query pipeline (in order, every query)

1. Embed the user's question with the SAME model as ingestion.
2. Vector search: top 20 by cosine distance.
3. Keyword search: top 20 by ts_rank over `tsv`, using websearch_to_tsquery.
4. Fuse with Reciprocal Rank Fusion, k=60:

```sql
WITH vec AS (
  SELECT id, row_number() OVER (ORDER BY embedding <=> $1) AS r
  FROM chunks ORDER BY embedding <=> $1 LIMIT 20
),
kw AS (
  SELECT id, row_number() OVER
    (ORDER BY ts_rank(tsv, websearch_to_tsquery('english', $2)) DESC) AS r
  FROM chunks
  WHERE tsv @@ websearch_to_tsquery('english', $2) LIMIT 20
)
SELECT c.id, c.content, c.document_id,
       COALESCE(1.0/(60+vec.r), 0) + COALESCE(1.0/(60+kw.r), 0) AS score
FROM chunks c
LEFT JOIN vec ON vec.id = c.id
LEFT JOIN kw  ON kw.id  = c.id
WHERE vec.id IS NOT NULL OR kw.id IS NOT NULL
ORDER BY score DESC LIMIT 5;
```

5. (Optional, add only after the eval says fusion isn't enough) Rerank the
   fused top 20 with a cross-encoder API, then take the top 5.

## 4. Answer contract

- Prompt: system rules + retrieved chunks (each tagged with title and
  source_uri) + the question.
- The model must cite which sources it used, by title.
- If retrieval scores are all weak or the chunks don't contain the answer,
  the model says it doesn't know. An unsupported answer is a bug, equal in
  severity to a crash. Put that sentence in the system prompt.

## 5. The eval (this gates all retrieval changes)

- Build `eval/questions.json`: [20] REAL questions users would ask, each
  with the source_uri(s) that contain the correct answer.
- Script: for each question, run retrieval only (no generation), record
  whether a correct source appears in the top 5. Report recall@5 overall.
- Baseline it. After ANY change to chunking, models, candidate counts, or
  fusion, rerun and compare in the PR. Number goes down, change goes back.

## 6. Acceptance checklist

- [ ] A part-number/exact-name query succeeds (keyword path proves itself)
- [ ] A paraphrased, no-shared-words query succeeds (vector path proves itself)
- [ ] A question the corpus cannot answer produces "I don't know," not prose
- [ ] Eval recall@5 at or above [0.8] and recorded in the repo
- [ ] Query latency under [500ms] at current corpus size