ADR-002: SQLite as the only database
Who is this for
Section titled “Who is this for”Developers and the owner — read this before adding a second worker, a second server or a new kind of state.
- All state lives in one SQLite file: sessions, jobs, cost accounting, audit log, settings and leads.
- The job queue and the leads outbox are tables, not a message broker.
- Status: accepted for a single station. It is the first thing to replace when several workers or servers are needed — see Scaling scenarios.
Context
Section titled “Context”The pilot is one station and about 70 guests. Two processes on one machine share state: the API (kiosk, admin panel, leads) and the bot (polling, worker, delivery, leads loop). A separate database server would add an installation, a backup routine and a failure mode for no measurable gain at that size.
Decision
Section titled “Decision”bot/db/db.pyopens the file withjournal_mode = WALand a five-secondbusy_timeout, so readers do not block the writer.- Operations that must be atomic across the two processes run inside
BEGIN IMMEDIATE: turning a photo into a job together with the pack-limit check, storing a lead together with the repeat check, and schema migration. - Schema changes are additive and automatic: on start the service backs the file up, rehearses the migration on a copy, checks integrity, then applies it.
- A daily timer makes a compressed, integrity-checked copy and keeps the last 14
(
deploy/backup.sh).
Alternatives considered
Section titled “Alternatives considered”| Alternative | Why not (now) |
|---|---|
| PostgreSQL | The right answer for several workers or servers; unnecessary operations burden for one station. |
| Redis or a message broker for the queue | Jobs must survive restarts and be visible to the admin panel; a table gives both for free. |
Consequences
Section titled “Consequences”- One writer at a time. Fine for one station; a limit for many.
- Both processes must run on the same machine and see the same file.
- Rate limiters and login lockouts live in process memory, not in the database; a restart resets them.
- Backups of the file contain lead contact data until the copy rotates out.
Evidence
Section titled “Evidence”CLAUDE.md, “Stack”;bot/db/models.py.
Where to go next
Section titled “Where to go next”- Data model.
- Capacity — where SQLite becomes the bottleneck.
- Decisions index.
Last verified: 2026-11-13 against ai-sticker-pack@b715418 and ai-stickers-landing@e350ff5.