0005 · Postgres is truth, Typesense is derived

Status: Accepted · 2026-09-22

Context

CoDNA needs full-text search with typo tolerance and faceting, and it needs transactional writes with foreign keys and recursive graph traversal. No single engine is good at both.

Decision

PostgreSQL is the source of truth for every byte. Typesense holds a derived index, is never written to directly, and is never read as truth. It can be dropped and rebuilt at any time.

Document bodies are markdown in a text column; revisions are full snapshots.

Consequences

• codna reindex is always a valid repair. There is no state in Typesense that Postgres cannot regenerate, so an index problem is never a data problem.
• Search is eventually consistent, typically within milliseconds. Reads by id or address go to Postgres and are immediately consistent — which is what an agent needs after writing a document.
• Recursive CTEs handle link traversal; GIN handles tags; FOR UPDATE SKIP LOCKED handles the scheduler. All in the database that already holds the data.
• Two systems to operate instead of one.

Alternatives rejected

Typesense as the only store. No transactions, no foreign keys, no recursive traversal. The link graph alone rules it out.

Postgres full-text search only. tsvector is genuinely good and would remove a dependency. It lacks typo tolerance, which matters more than expected when agents generate queries from half-remembered terms, and its faceting is hand-rolled.

Git-backed markdown files as truth. Real diffs, real blame, and git clone as export — a genuinely attractive option. Rejected because permissions live outside the file store: anyone with the repository has every document in it, which is incompatible with per-document access control being the core feature. Revisited in future as a one-way mirror, never as truth.