desertthunder.dev
a love letter to tangled (android, iOS, and a search API)
1---
2title: ADR - Choose PostgreSQL And Coolify For Search Storage
3updated: 2026-03-26
4status: accepted
5---
6
7## Decision
8
9Twisted will use PostgreSQL as the primary database backend for search,
10indexing, queue state, and activity cache. The production deploy target is one
11Compose stack for `postgres`, `migrate`, `api`, `indexer`, `tap`, and
12`llama-embeddings`. Coolify can host that stack, but the Compose file remains
13the source of truth.
14
15## Why
16
17- PostgreSQL is the better fit for long-running multi-service deployment.
18- Coolify still gives the project a straightforward Git-to-deploy path with
19 built-in Traefik when we want it.
20- The current service shape already wants two long-lived processes writing to
21 one shared database.
22- A local PostgreSQL workflow keeps development closer to production than the
23 old Turso split.
24
25## Consequences
26
27### Positive
28
29- one mainstream database for local and remote environments
30- simpler production backups and restore story
31- easier operational model for `api` and `indexer`
32- explicit migration ownership through a one-shot `migrate` command
33- no dependency on Turso-specific SQLite extension behavior
34
35### Negative
36
37- the search layer must move off SQLite FTS5
38- ranking and snippet behavior will change
39- SQLite remains only as a temporary rollback path during migration
40
41## Search Shape
42
43Keyword search will use PostgreSQL full-text search:
44
45- weighted `tsvector`
46- `websearch_to_tsquery('simple', ...)`
47- `ts_rank_cd`
48- `ts_headline`
49
50The HTTP response shape stays stable, but exact scores and snippets are not
51expected to match the previous FTS5 implementation.
52
53## Migration Plan
54
551. add PostgreSQL connection/config support and local defaults
562. add a primary PostgreSQL migration set
573. move search and store implementations to PostgreSQL
584. deploy the Compose stack from `docker-compose.prod.yaml`
595. rebuild data through `backfill`, `enrich`, and `reindex`
606. cut traffic over only after smoke checks pass
61
62## Explicit Non-Goal
63
64The default migration does not include a Turso-to-PostgreSQL data import. The
65serving dataset should be rebuilt from authoritative upstream sources.
66
67## Related Records
68
69- `docs/adr/pg.md` remains the background research for this decision
70- `docs/adr/turso.md` is retained as superseded historical context