Component: storage and infrastructure

Who is this for

An operator who maintains the server, and a developer who adds a column, a file type or a new process and needs to know what the surrounding infrastructure will do with it.

TL;DR

• One SQLite file (DB_PATH, default bot/data/app.db) in WAL mode is shared by both processes. Schema changes are applied automatically at start-up, with a backup and a trial run on a copy first.
• Photos are files, not rows. They live under DATA_DIR/sessions/ and DATA_DIR/debug_sheets/ and are deleted by an hourly sweep once older than PHOTO_TTL_HOURS (1–72, default 72). The selfie itself is deleted as soon as the sticker set is created.
• Caddy is the only public listener. It is shared with a second, unrelated project on the same droplet, so this project manages only its own site file and never rewrites the common Caddyfile once it contains the import line.
• systemd runs sticker-api and sticker-bot as an unprivileged user with memory and CPU limits and a read-only code directory.
• Backups: a daily timer copies the database (not the photos), keeps the last 14 copies.
• Firewall: inbound 22, 80 and 443 only. fail2ban guards SSH. A 2 GB swap file is a safety net against out-of-memory kills.

Diagram

flowchart TB
    subgraph host["Droplet - Ubuntu, 2 vCPU and 4 GB, shared with a second, unrelated project"]
        ufw["ufw - inbound 22, 80, 443"]
        f2b["fail2ban - sshd jail"]
        caddy["Caddy - sites/stickers.caddy"]
        subgraph units["systemd units, user sticker"]
            apiu["sticker-api.service"]
            botu["sticker-bot.service"]
        end
        timer["sticker-backup.timer and service, root"]
        logrot["logrotate - daily, 14 days"]
        subgraph data["DATA_DIR - bot/data"]
            db[("app.db in WAL mode")]
            sess[("sessions - per session token")]
            dbg[("debug_sheets")]
            lockf["main.lock and main.pid"]
        end
        logs[("logs - app.log and api.log")]
        bk[("/var/backups/sticker-pack - last 14")]
        swap["swap file 2 GB"]
    end

    ufw --> caddy
    caddy -- "reverse proxy to loopback port 8000" --> apiu
    apiu --> db
    apiu --> sess
    botu --> db
    botu --> sess
    botu --> dbg
    botu --> lockf
    apiu --> logs
    botu --> logs
    logrot --> logs
    timer -- "sqlite3 .backup" --> db
    timer --> bk

SQLite

Aspect	How it works	Source
Location	DB_PATH. Relative paths are resolved from the bot/ directory, not the current directory, so both processes see the same file wherever they are started from	bot/config.py
Journal mode	PRAGMA journal_mode = WAL, set in Database.init(). WAL lets readers work while one writer writes — needed because two processes share the file	bot/db/db.py
Connections	A new aiosqlite connection per call, PRAGMA busy_timeout = 5000 on each, autocommit (isolation_level=None)	Database._connect
Transactions	Single-statement status changes are conditional UPDATE … WHERE status = …. Multi-step operations use an explicit BEGIN IMMEDIATE: photo + job, link sent + pack recorded, lead submit, lead status, guest reset, job retry	take_photo_and_enqueue, mark_link_sent, lead_submit, lead_set_status, reset_user, retry_failed_job
Foreign keys	Two are declared in the schema (lead_status_history.lead_id, lead_repeats.lead_id). The code never runs PRAGMA foreign_keys = ON, so SQLite does not enforce them; child rows are deleted explicitly in lead_delete	bot/db/models.py
Timestamps	ISO 8601 text in UTC (now_iso()); comparisons use julianday()	db.py
Schema	A single SCHEMA string of CREATE TABLE IF NOT EXISTS / CREATE INDEX IF NOT EXISTS statements, executed on every start	bot/db/models.py

Tables and columns are described on the data model page.

Migrations

There is no migration framework and no version table. Two mechanisms cover every change made so far:

• New tables and indexes appear through CREATE … IF NOT EXISTS in SCHEMA.
• New columns on existing tables are listed in ADDED_COLUMNS and applied by migrate_schema(path).

Database.init() calls migrate_schema first, then sets WAL and runs SCHEMA. Both processes call init() at start; whichever starts first migrates, the other finds nothing to do.

flowchart TB
    start["Database.init"]
    miss{"Any column from ADDED_COLUMNS missing"}
    bak["Backup with the SQLite backup API to app.db.bak-timestamp"]
    trial["Copy the backup, apply ALTER TABLE on the copy"]
    verify["Verify - columns exist, row counts unchanged, integrity_check ok"]
    live["BEGIN IMMEDIATE on the live file, re-check, apply, verify, COMMIT"]
    wal["Set WAL, run SCHEMA"]
    stop["Raise - process does not start"]

    start --> miss
    miss -- "no" --> wal
    miss -- "yes" --> bak --> trial --> verify
    verify -- "ok" --> live --> wal
    verify -- "failed" --> stop

To add a column: add it to the CREATE TABLE in SCHEMA (for fresh databases) and append ("table", "column", "DECLARATION") to ADDED_COLUMNS (for existing ones). Only ALTER TABLE … ADD COLUMN is supported; renames, type changes and drops would need new code.

Database backups made by the application

When	File	Removed by
Before a column migration	app.db.bak-<YYYYMMDD-HHMMSS> next to the database	Nothing in the repository — remove by hand
Before a guest reset in the admin panel	app.db.bak-<YYYYMMDD-HHMMSS>-reset	Nothing in the repository — remove by hand

These files contain the same personal data as the live database (guest names, lead contacts) and are not covered by the TTL sweep, which only looks at sessions/ and debug_sheets/. Treat them as part of the data lifecycle — see Backups and data lifecycle.

File storage layout

Everything is under DATA_DIR (default bot/data, created with mode 750 for the service user).

Path	Content	Personal data	Lifetime
app.db (+ SQLite’s WAL side files)	The database	Yes	Permanent
main.lock, main.pid	Instance lock of the bot process	No	Permanent
layout_guide_<cols>x<rows>_<aspect>.png	Blank grid images sent with sheet requests; generated once per format	No	Permanent
sessions/<session_token>/selfie_<hex>.jpg	The normalised photo (JPEG, EXIF removed, longest side ≤ 2048 px)	Yes	Deleted right after the sticker set is created; otherwise by TTL. Kept after a failure so an admin can retry
sessions/<session_token>/face_crop.jpg	Crop of the selfie around the face	Yes	TTL
sessions/<session_token>/ other files	Generated sheets, the reference copy of sheet 1, anchor (raw and corrected), regenerated figures, quality-check grids	Yes — drawings of the guest	TTL
sessions/<session_token>/stickers/sticker_<n>.png	The 12 final stickers	Yes — drawings of the guest	TTL
debug_sheets/job<N>_sheet<K>_try<M>.png and .json	A copy of every figure sheet attempt with its cut diagnosis	Yes	TTL
debug_sheets/job<N>_anchor.png	The anchor shown in the admin panel	Yes	TTL
debug_sheets/job<N>_consistency.json, job<N>_likeness.json	Per-job summary of colour measurements and quality checks	Indirect (colour values, check reasons)	TTL

TTL sweep

bot/services/storage.py cleanup_expired, called by the cleanup task in the bot process every 3600 s:

• Walks sessions/ and debug_sheets/ and deletes every file whose modification time is older than the cutoff, then removes empty directories bottom-up.
• Age is taken per file, not per folder. Otherwise a new file written into a session folder during a retry would make the whole folder look fresh and the selfie would outlive the TTL.
• The cutoff is PHOTO_TTL_HOURS minus the one-hour sweep interval (never below 0.5 h), so a file is gone no later than the configured TTL rather than TTL plus up to an hour.
• PHOTO_TTL_HOURS outside 1–72 is a start-up error. The upper bound is treated as a legal requirement in the code.
• The loop never stops on an error; a failed deletion is logged and retried on the next pass.

Two more deletions happen outside the sweep: the selfie right after a successful sticker set creation (_process_one_job in worker.py), and a rejected upload immediately (upload_photo in api.py).

The textual appearance description is not a file: it sits in jobs.appearance_json and is set to NULL when the job reaches done or failed; the same hourly task clears any that were missed.

Caddy

Site file: deploy/caddy/stickers.caddy, installed as /etc/caddy/sites/stickers.caddy.

Setting	Value	Why
Site address	stickers.eventtalks.uz	Caddy obtains the Let’s Encrypt certificate automatically. The camera API requires HTTPS
Upstream	The loopback interface, port 8000	sticker-api is not reachable directly
Compression	zstd, gzip	—
Request body cap	20 MB	The API itself rejects photos above 15 MB with a clear 413; the proxy cap sits above it so the request reaches the API instead of being cut off
Headers	HSTS (one year, subdomains), X-Content-Type-Options: nosniff, X-Frame-Options: DENY, Referrer-Policy: no-referrer, Permissions-Policy allowing the camera for the own origin only, Cross-Origin-Opener-Policy: same-origin, Server header removed	The camera is needed only by the kiosk on its own origin
Access log	/var/log/caddy/stickers.log, rolled at 20 MiB, 14 files kept	—

Sharing Caddy with the neighbour

The main /etc/caddy/Caddyfile contains a single line that imports /etc/caddy/sites/*.caddy; each project owns one file in sites/. deploy/caddy.sh is written so that a deploy of this project cannot break the other one:

State of the main Caddyfile	Action
Already has the import line	Not touched at all
Missing, empty, the stock package file, or this project’s old single-site file	Backed up, replaced with the import line (one-time migration)
Anything else without the import line	Error, nothing changed — a human must add the line

Then the site file is compared with the repository copy. If nothing changed, Caddy is neither validated nor reloaded (so a neighbour’s half-finished edit is not applied on their behalf). If something changed: caddy validate; on failure both files are rolled back and Caddy is not reloaded; on success systemctl reload caddy.

systemd units

Unit files: sticker-api.service, sticker-bot.service.

Setting	sticker-api	sticker-bot
Command	python -m uvicorn api:app bound to the loopback interface, port 8000, with --proxy-headers	python main.py
User / group	sticker (system user without a login shell)	same
Working directory	/opt/ai-sticker-pack/bot	same
Environment	EnvironmentFile=/opt/ai-sticker-pack/bot/.env	same
Restart	always, after 3 s	same
Stop	TimeoutStopSec=15	KillSignal=SIGINT, TimeoutStopSec=30
MemoryHigh	1.2G	1.45G
MemoryMax	1.6G	1.6G
CPUWeight	50	50
Hardening	NoNewPrivileges, ProtectSystem=full, ProtectHome, PrivateTmp, code tree read-only, writable only bot/data and logs, UMask=0027	same

Why the limits (comments in the unit files): the droplet is shared. Above MemoryHigh the kernel throttles the process and reclaims its memory (cache first, then swap); MemoryMax is the hard RAM ceiling. The bot’s MemoryHigh is higher than the API’s because the measured peak of one pack with two worker slots and two parallel cuts was up to 1.42 GB (the figure is quoted in the unit file from a load run), and a lower threshold would throttle every pack during cutting. CPUWeight=50 against the default 100 gives the neighbour twice the CPU share under contention.

EnvironmentFile means the same bot/.env is both loaded by systemd into the process environment and read by config.py. deploy/update.sh converts Windows line endings in that file to LF before restarting, because systemd would otherwise keep a carriage return inside a token.

Backup timer

Aspect	Value	Source
Schedule	Daily at 03:30 UTC, random delay up to 5 min, Persistent=true (a missed run happens after boot)	sticker-backup.timer
Why 03:30	To finish before the droplet provider’s weekly backup window on Saturdays (04:00–08:00 UTC, per the timer comment)	same
Runs as	root, Nice=10, idle I/O class	sticker-backup.service
Steps	sqlite3 <db> ".backup …" (consistent with open connections and WAL) → PRAGMA integrity_check → gzip -9 → mode 600 → delete all but the newest 14	deploy/backup.sh
Destination	/var/backups/sticker-pack/app-<timestamp>.db.gz (directory mode 700)	same
Result log	/var/log/sticker-backup.log and journald	same
Not backed up	Photos, session folders, debug_sheets	same

The backup is a copy of the whole database, so it includes the leads tables with contact data. Erasing a lead removes its contacts from the live database at once and from backups only as the 14 daily copies rotate out — the bot says exactly this in its reply to /stickers_forget.

Logs and rotation

Log	Written by	Rotation
logs/app.log	sticker-bot (also to stdout → journald)	In-process: 5 MB × 3 backups. Plus logrotate
logs/api.log	sticker-api — a separate file so two processes never rotate the same one	In-process: 5 MB × 3 backups. Plus logrotate
logrotate rule	deploy/logrotate/sticker-pack	Daily, 14 rotations, maxage 14, compressed, copytruncate (processes keep the file open, no restart needed)
/var/log/caddy/stickers.log	Caddy	20 MiB × 14
/var/log/sticker-backup.log	Backup script	—

Firewall, SSH protection, swap

Configured by deploy/setup.sh (idempotent first-time server setup) and deploy/swap.sh.

Item	Configuration
ufw	Default deny incoming, allow outgoing; allow TCP 22, 80, 443
fail2ban	sshd jail: 5 failures within 10 minutes → ban for 1 hour
Swap	2 GB swap file, vm.swappiness=10 — a last resort against out-of-memory kills, not working memory
Service user	sticker, system account, no login shell, home /opt/ai-sticker-pack
Packages	Python venv and pip, sqlite3, Caddy from its official repository, ufw, fail2ban, rsync

setup.sh does not touch bot/.env and does not start the application services.

Deployment scripts

Script	Runs on	Does
deploy/push.ps1	Developer’s Windows machine	git archive HEAD (committed files only) → checks the archive for forbidden paths (.env, data, logs, virtual environments) and for BOM / CR in Linux files → scp → unpack over the application directory → write VERSION (short commit hash, shown by /health) → pip install
deploy/update.sh	Server, as root	pip install → show active jobs and ask for confirmation → install changed unit files → restart sticker-api, then sticker-bot → caddy.sh → poll /health for up to 15 s → print status and recent log lines
deploy/setup.sh	Server, as root, once	Everything in the table above plus units, Caddy, logrotate and the backup timer

Where to go next

• Data model — what is inside app.db.
• Flow: deploy and rollback — the deploy scripts as a sequence.
• Backups and data lifecycle — restoring a backup, cleaning old copies.
• Runbook: disk and memory — when a limit on this page is hit.
• Runbook: services stopped on shared droplet — living next to the neighbour.

---

Last verified: 2026-11-13 against ai-sticker-pack@b715418 and ai-stickers-landing@e350ff5.