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

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 (#977 / #979 and follow-ons)
• GSoC Librarian (Module C) merge

Start by reading the Makefile, AGENTS.md, CMDConfig paths, and scripts/db/*, then inventory the SQLite references in CI, import/export, sync, and Module C/Librarian. Use the documented Postgres or pgvector local path to check the current test workflow. Done means fresh setup and CI use Postgres without SQLite, with maintainer docs and remaining cache behavior matching the acceptance checklist.