# ADR-004 — Database engine is chosen in `.env`: SQLite for small, PostgreSQL 17+ for large

**Status:** accepted, implemented (initial schema migration) · **Date:** 2026-10-13 · **Deciders:** RG

*Ported from YoutubeManager, where this was decision CHG-20260924-RG-04; the contract is identical.*

## Context

SQLite is right for a single-user, self-hosted instance: one file, no service, trivial backup.
It is wrong once several people or several worker processes write concurrently, once the data
outgrows one disk file, or once the instance must be operated like a service (backups, replicas,
connection pooling). The same operator runs both kinds of deployments and does not want two
codebases or a rewrite when an instance grows.

## Decision

1. **One variable decides the engine**: `DB_ENGINE=sqlite` (default) or `DB_ENGINE=postgres`.
   Nothing else in code or compose changes between the two; only `.env` does.
2. **PostgreSQL runs as a container in the same `docker-compose.yml`**, service name `db`, image
   `postgres:17` pinned to an exact tag, on the compose default network, reachable from the app
   as host `db`. It is enabled through the compose profile `postgres`
   (`COMPOSE_PROFILES=postgres` in `.env`). With `DB_ENGINE=sqlite` no database container runs.
3. **Minimum version PostgreSQL 17.** Older majors are not supported; the image tag is bumped in
   an `infra` change with the migration test output in `## Verification`.
4. **Connection settings live in `.env`** (`POSTGRES_HOST`, `POSTGRES_PORT`, `POSTGRES_DB`,
   `POSTGRES_USER`, `POSTGRES_PASSWORD`, optional `DATABASE_URL` override). `config.py` builds
   `SQLALCHEMY_DATABASE_URI` from them; no module builds a connection string anywhere else.
5. **Data persists in a named volume** (`pgdata`), never in a bind mount of the source tree.
6. **The app waits for the database**, it does not assume it: `entrypoint.sh` runs
   `python -m app.db_wait` (a Python connect loop) up to `DB_CONNECT_TIMEOUT_SECONDS` before
   running migrations and starting gunicorn.
7. **Schema changes are Alembic migrations (Flask-Migrate)**, applied by `entrypoint.sh` with
   `flask db upgrade` on every start. `db.create_all()` is never used. Every migration must apply
   cleanly on both engines (batch mode on SQLite).
8. **Engine-neutral code.** Only SQLAlchemy constructs that exist on both engines: no raw SQL,
   no engine-specific types (`JSONB`, `ARRAY`), no SQLite pragmas, no reliance on SQLite's loose
   typing. `tasks` is stored as JSON text, counters as integers.
9. **Tests run on SQLite by default and on PostgreSQL in CI** (service container). A change that
   touches models or queries states in `## Verification` that both passed.

## `.env` contract

```ini
DB_ENGINE=sqlite
COMPOSE_PROFILES=            # set to "postgres" together with DB_ENGINE=postgres

# sqlite only
DATABASE_PATH=/app/data/tiktok_manager.db

# postgres only (all required when DB_ENGINE=postgres; no defaults for the password)
POSTGRES_HOST=db
POSTGRES_PORT=5432
POSTGRES_DB=tiktok_manager
POSTGRES_USER=tiktok_manager
POSTGRES_PASSWORD=
POSTGRES_IMAGE=postgres:17.6-alpine   # pinned; >= 17 required
DB_CONNECT_TIMEOUT_SECONDS=60

# optional: full SQLAlchemy URL that overrides everything above
DATABASE_URL=
```

## Consequences

- One image, one compose file, two deployment sizes; switching is an `.env` edit plus
  `flask db upgrade` on the new engine (data migration between engines is a documented
  operational procedure, not automatic).
- Alembic is mandatory from the first schema (`0001_initial.py`: `accounts` + `scheduled_posts`), so schema
  changes never destroy data.
- `psycopg[binary]` (v3) and `Flask-Migrate` are pinned in `requirements.txt`, with `pip-audit`.
- Backups: SQLite = copy the file; PostgreSQL = `pg_dump` from the `db` container on a schedule.
  Both contain Fernet-encrypted tokens only; treat the backup location as sensitive anyway.