P1: pgvector migration + chat/Librarian similarity cutover

[northdpole]

Summary

Migrate existing embeddings to pgvector, keep the CSV/embeddings_content columns, and cut over chatbot + similarity matching (and Librarian C.1) to use embedding_vec on Postgres.

This is a follow-on to the Heroku slug work (#975): drop app-side numpy/scipy/sklearn for prod chat matching by doing cosine in Postgres.

Related: Librarian C.1 already expects embedding_vec + <=> (PR #937 by @PRAteek-singHWY); the W8 Alembic migration was never written. Prod DB is Heroku Postgres essential-1 (pgvector extension supported; $0 for the extension itself).

Local plan: .cursor/plans/pgvector-migration.md

Goals

1. Add embeddings.embedding_vec (pgvector), backfill from existing CSV embeddings where dim matches.
2. Keep embeddings (CSV string) + embeddings_content (raw text) on the same row (link by PK).
3. Dual-write on add_embedding (CSV + vector + content).
4. Cut over chatbot and existing similarity paths to pgvector on Postgres.
5. Enable Librarian CRE_LIBRARIAN_RETRIEVER_BACKEND=pgvector once migrated.

Locked decisions

Decision	Choice
Dim policy	Single project-wide dim; migration hard-fails if multiple dims detected (alert clearly)
Migration backfill	Idempotent CSV → embedding_vec only (WHERE embedding_vec IS NULL + dim match)
Re-run	Safe/expected after failure or partial fill — re-run migration/backfill, not LLM re-embed
Re-embed on Heroku/prod	Forbidden — likely OOM-killed; re-embed only on a high-memory machine, then sync/backfill
Multi-dim	Exit fail migration; do not silently pick a majority dim

Contributor warning (must land in code)

Near migration / embedding write / regenerate CLI:

Do not re-embed the full corpus on Heroku/production dynos — likely OOM-killed.
Re-embedding (fix missing / wrong-dim via LLM) must run only where there is enough RAM (local or high-resource worker), then sync/backfill vectors.
Alembic on prod only: enable extension, add column, copy CSV→vector for matching dims.

Implementation outline

1. Alembic (Postgres-only)

   • CREATE EXTENSION IF NOT EXISTS vector
   • Detect dims; if ≠ 1 distinct → raise and abort
   • ADD COLUMN embedding_vec vector(N) with that N
   • Idempotent backfill from CSV embeddings
   • Optional HNSW index (may defer on Essential-1)
   • SQLite/CI: skip vector (CSV + in-memory fallback)

2. db.Embeddings + add_embedding — dual-write CSV + embedding_vec

3. Chat + existing match paths — prefer pgvector on Postgres; avoid pulling sklearn/numpy on Heroku for chat

4. Librarian — already queries embedding_vec; flip backend env after migrate

5. Verification

   • make alembic-guardrail before prod upgrade
   • Backup: scripts/db/backup-opencreorg.sh before prod migrate
   • Tests: dual-write, multi-dim fail, idempotent backfill
   • No re-embed in release/Procfile

Success criteria

• Single-dim assert fails migration loudly on mixed dims
• Matching rows backfilled; CSV + embeddings_content preserved
• Chat uses pgvector on Postgres
• Contributor OOM/Heroku re-embed warning present in code
• make lint / make test / alembic guardrail green
• Prod migrate only after backup

Out of scope

• Dropping the CSV embeddings column
• Automatic LLM re-embed on deploy/Heroku
• Librarian W8 graph-write path (separate)

Context

• Blocks lighter prod slug / chat without ~180MB numpy/scipy/sklearn stack
• Aligns with Module C C.1 PgVectorRetriever
• Parent/adjacent: P1: Unblock Heroku prod deploy — split requirements + aggressive slug strip #975