Migrate from SQLite to PostgreSQL for multi-node write support #42

New issue

Closed

opened 2026-05-26 10:06:46 -07:00 by pyr0ball · 0 comments

pyr0ball commented

2026-05-26 10:06:46 -07:00

Owner

Context

Turnstone currently uses SQLite with WAL mode. The context KB write-lock contention issue (fixed by moving context tables to a separate file) revealed a deeper architectural limit: SQLite is single-writer per file, which causes issues when the glean scheduler holds write locks during FTS5 bulk inserts.

Database targets

PostgreSQL — primary open-source community target. Multi-node write support, full-text search via pg_trgm or tsvector, native JSON operators for matched_patterns.
MariaDB / MySQL — required for enterprise deployments (Steris uses MariaDB internally). Must be a first-class target alongside PostgreSQL, not an afterthought.
SQLite — kept as the default for local/self-hosted single-node deployments. No migration required for existing installs.

Design constraints

Use a DB abstraction layer (SQLAlchemy Core or a thin adapter pattern) so SQLite / PostgreSQL / MariaDB can be swapped via connection string
FTS5 (SQLite) → pg_trgm or tsvector (PostgreSQL) → FULLTEXT INDEX (MariaDB) — the search layer must abstract this
Schema migrations via Alembic (or equivalent) — not raw ALTER TABLE statements
Multi-tenant namespacing (#50) must be designed with PostgreSQL/MariaDB schemas or tenant_id columns in mind
Connection string via TURNSTONE_DATABASE_URL env var (SQLAlchemy DSN format)

Phases

Define DB abstraction interface (repository pattern)
Implement PostgreSQL adapter + FTS translation
Implement MariaDB adapter + FULLTEXT translation
Migrate schema init from raw executescript() to Alembic migrations
Integration tests against all three backends (SQLite, PostgreSQL, MariaDB)

References

Enterprise POC context: Steris uses MariaDB internally — MariaDB support is a hard requirement for that contract path
See also: #50 multi-tenant namespacing (depends on this)

## Context Turnstone currently uses SQLite with WAL mode. The context KB write-lock contention issue (fixed by moving context tables to a separate file) revealed a deeper architectural limit: SQLite is single-writer per file, which causes issues when the glean scheduler holds write locks during FTS5 bulk inserts. ## Database targets - **PostgreSQL** — primary open-source community target. Multi-node write support, full-text search via pg_trgm or tsvector, native JSON operators for `matched_patterns`. - **MariaDB / MySQL** — required for enterprise deployments (Steris uses MariaDB internally). Must be a first-class target alongside PostgreSQL, not an afterthought. - **SQLite** — kept as the default for local/self-hosted single-node deployments. No migration required for existing installs. ## Design constraints - Use a DB abstraction layer (SQLAlchemy Core or a thin adapter pattern) so SQLite / PostgreSQL / MariaDB can be swapped via connection string - FTS5 (SQLite) → pg_trgm or tsvector (PostgreSQL) → FULLTEXT INDEX (MariaDB) — the search layer must abstract this - Schema migrations via Alembic (or equivalent) — not raw `ALTER TABLE` statements - Multi-tenant namespacing (#50) must be designed with PostgreSQL/MariaDB schemas or `tenant_id` columns in mind - Connection string via `TURNSTONE_DATABASE_URL` env var (SQLAlchemy DSN format) ## Phases 1. Define DB abstraction interface (repository pattern) 2. Implement PostgreSQL adapter + FTS translation 3. Implement MariaDB adapter + FULLTEXT translation 4. Migrate schema init from raw `executescript()` to Alembic migrations 5. Integration tests against all three backends (SQLite, PostgreSQL, MariaDB) ## References - Enterprise POC context: Steris uses MariaDB internally — MariaDB support is a hard requirement for that contract path - See also: #50 multi-tenant namespacing (depends on this)