desertthunder.dev / twisted
a love letter to tangled (android, iOS, and a search API)
fork atom
twisted / docs / adr / storage.md
at main 2.3 kB view raw view code
desertthunder.dev build: add migration service
f77beb5a

title: ADR - Choose PostgreSQL And Coolify For Search Storage updated: 2026-03-26 status: accepted#

Decision#

Twisted will use PostgreSQL as the primary database backend for search, indexing, queue state, and activity cache. The production deploy target is one Compose stack for postgres, migrate, api, indexer, tap, and llama-embeddings. Coolify can host that stack, but the Compose file remains the source of truth.

Why#

  • PostgreSQL is the better fit for long-running multi-service deployment.
  • Coolify still gives the project a straightforward Git-to-deploy path with built-in Traefik when we want it.
  • The current service shape already wants two long-lived processes writing to one shared database.
  • A local PostgreSQL workflow keeps development closer to production than the old Turso split.

Consequences#

Positive#

  • one mainstream database for local and remote environments
  • simpler production backups and restore story
  • easier operational model for api and indexer
  • explicit migration ownership through a one-shot migrate command
  • no dependency on Turso-specific SQLite extension behavior

Negative#

  • the search layer must move off SQLite FTS5
  • ranking and snippet behavior will change
  • SQLite remains only as a temporary rollback path during migration

Search Shape#

Keyword search will use PostgreSQL full-text search:

  • weighted tsvector
  • websearch_to_tsquery('simple', ...)
  • ts_rank_cd
  • ts_headline

The HTTP response shape stays stable, but exact scores and snippets are not expected to match the previous FTS5 implementation.

Migration Plan#

  1. add PostgreSQL connection/config support and local defaults
  2. add a primary PostgreSQL migration set
  3. move search and store implementations to PostgreSQL
  4. deploy the Compose stack from docker-compose.prod.yaml
  5. rebuild data through backfill, enrich, and reindex
  6. cut traffic over only after smoke checks pass

Explicit Non-Goal#

The default migration does not include a Turso-to-PostgreSQL data import. The serving dataset should be rebuilt from authoritative upstream sources.

  • docs/adr/pg.md remains the background research for this decision
  • docs/adr/turso.md is retained as superseded historical context