maintainer: drop SQLite — Postgres-only OpenCRE (Makefile, CI, local cache) #982

Maintainer-only — do not assign to GSoC / first-time contributors

This work is not suitable for non-maintainers. It touches local developer UX, CI, deploy glue, and every path that still assumes a file-backed SQLite cache. Schedule it after the GSoC Librarian / Module C work is merged, or fold it into that merge if maintainers are already in the middle of the Postgres/pgvector cutover — do not open it as a parallel community-facing “good first issue.”

Why

OpenCRE now has a vec-only embedding store (embeddings.embedding_vec). Real similarity uses Postgres pgvector (<=>). SQLite remains a second dialect in Makefiles, default DEV_DATABASE_URL fallbacks, standards_cache.sqlite, CI, import/export, and Module C’s in_memory backend.

That dual path is increasingly expensive and error-prone (legacy CSV→vec rewrites, “fail on pgvector-on-SQLite” guards, sync scripts, different Alembic behavior). The long-term shape should be: Postgres (+ pgvector) everywhere we run code that matters.

Goal

Drop SQLite as a supported application database for OpenCRE. Migrate Makefiles, scripts, CI, docs, and defaults so local/dev/test/prod all assume Postgres (pgvector image locally).

In scope (checklist)

• Makefile / AGENTS.md / scripts: no standards_cache.sqlite as default cache; make docker-postgres (or equivalent) is the documented local DB
• Config: remove SQLite fallbacks from DEV_DATABASE_URL / PROD_DATABASE_URL / CMDConfig paths (or make them hard-fail with a clear message)
• Tests / CI: run against Postgres (service container or ephemeral pgvector), not in-memory/file SQLite
• Import / export / sync scripts that still mention SQLite: Postgres-first or retired
• Module C / Librarian: retire or re-scope CRE_LIBRARIAN_RETRIEVER_BACKEND=in_memory once CI is on Postgres/pgvector
• Document destruction of rewrite_sqlite_embeddings_to_vec.py-style escape hatches once caches are gone
• Confirm Heroku / staging / scripts/db/* remain the only production DB story

Out of scope

• Re-embedding the corpus on Heroku (still forbidden — OOM risk)
• HNSW index work (can stay a separate follow-up on Essential plan capacity)
• Community drive-by PRs

Acceptance

• Fresh clone + documented Makefile path brings up Postgres and runs tests without creating/using SQLite
• No remaining “supported” SQLite cache workflow in maintainer docs
• CI green on Postgres
• Clear SystemExit / docs if someone still points CRE_CACHE_FILE at a .sqlite path

Related

• pgvector / embedding_vec cutover (P1: pgvector migration + chat/Librarian similarity cutover #977 / feat: pgvector embedding_vec migration + chat similarity cutover #979 and follow-ons)
• GSoC Librarian (Module C) merge