SQLite got me far. It’s embedded, zero-config, and perfect for a single-user prototype. But the moment I started thinking about multi-user support, its limits became obvious. No row-level security, no concurrent writers, no role separation. The database that got me to TL4 couldn’t take me further.
The migration to PostgreSQL was one of the biggest structural changes in the project. I started by defining store Protocol interfaces — abstract contracts that both SQLite and PostgreSQL backends would implement. That let me build the new backend without touching the old one.
Phase by phase: add PostgreSQL to the container, create the schema with tsvector and pgvector indexes, build every store backend (session, routine, webhook, memory, search, provenance, approval, metrics, episodic), make every store method async, migrate every caller to await, write a data migration script, add validation tooling.
The async rewrite was the most tedious part. Every store method signature changed, every caller changed, every test changed. Mechanical but extensive — the kind of work where one missed await means a coroutine silently returns without executing.
Once PostgreSQL was proven, I did something satisfying: I deleted every line of SQLite code. Not deprecated, not feature-flagged — deleted. The branching logic, the SQLite-specific stores, the config flags, the test files. All gone. One database, one code path, no ambiguity.
The migration also gave me pgvector for semantic search and tsvector for full-text search — capabilities that would become essential for the episodic learning layer later.