# ADR 0002: PostgreSQL as the only database

- Status: accepted
- Date: 2026-07-04

## Context

Dorfteich stores relational data (users, ponds, pages, labels, grants,
quotas), binary CRDT document state, and needs full-text search. Self-hosting
must stay simple ("one `docker compose up`"), so every additional stateful
service raises the barrier. The capacity target is small-to-medium instances
(order of 100 ponds / 10,000 pages), decided in the project kickoff.

## Decision

- **PostgreSQL (current stable major) is the single database** for:
  - all relational entities (see `data-model.md`),
  - Yjs document state and incremental updates as `bytea` (ADR 0003),
  - page version snapshots (ADR 0013),
  - full-text search via `tsvector` (ADR 0010),
  - job/outbox tables for e-mail sending and notifications (no separate
    message broker).
- Schema migrations are managed with Prisma Migrate (ADR 0006) and run
  automatically on API startup (`prisma migrate deploy`), so self-hosters
  update by pulling new images (see `operations.md`).
- Binary user uploads (images, attachments) do **not** go into PostgreSQL —
  they live on a filesystem volume (ADR 0011). Only metadata is stored in the
  database.

## Consequences

- Exactly one stateful service to run, back up, and restore
  (plus the uploads volume) — backup strategy stays a plain `pg_dump`
  (ADR 0015).
- No Redis: rate limiting, session storage, and pub/sub needs are served by
  PostgreSQL (sessions table, `LISTEN/NOTIFY` where needed). If horizontal
  scaling of the collab server ever becomes necessary, introducing Redis
  pub/sub is a contained change inside `apps/collab`.
- Full-text search quality is bounded by PostgreSQL FTS; ADR 0010 keeps the
  door open for an external engine.

## Alternatives considered

- **SQLite**: attractive for tiny self-hosts, but concurrent-write behavior
  under the collab server's persistence load and the FTS requirements make it
  risky; supporting two databases doubles the test matrix. Rejected.
- **PostgreSQL + Redis + object storage from day one**: standard SaaS stack,
  but oversized for the target scale and hostile to casual self-hosting.