Data model

Who is this for

A developer who writes a query or adds a column, an operator who inspects the database during an incident, and anyone answering “what personal data do we hold and when does it go away?”.

TL;DR

The whole product uses one SQLite file with nine tables: users, sessions, jobs, api_usage, audit_log, settings, leads, lead_status_history, lead_repeats.
Photos are never in the database. jobs.photo_path is a path to a file that is deleted after the pack is created or by TTL.
Only two relationships are declared as foreign keys (both point to leads.id), and SQLite does not enforce them here because the code never switches enforcement on. All other links are by convention.
The guest side has no automatic retention for database rows: users, sessions and jobs stay until an operator resets the guest. Only the face description (jobs.appearance_json) and the files are cleared automatically.
The leads side has automatic anonymisation: contact fields become NULL 30 days (spam) or 365 days (others) after the last status change, or at once on request.
All timestamps are ISO 8601 text in UTC.

ER diagram

Attributes shown are keys and the columns that carry relationships or state. The full column lists follow below.

erDiagram
    users |o--o{ sessions : "logical telegram_user_id"
    users ||--o{ jobs : "logical telegram_user_id"
    sessions ||--o| jobs : "logical session_token"
    jobs |o--o{ api_usage : "logical job_id"
    users |o--o{ audit_log : "logical telegram_user_id"
    jobs |o--o{ audit_log : "logical job_id"
    leads ||--o{ lead_status_history : "declared FK lead_id"
    leads ||--o{ lead_repeats : "declared FK lead_id"

    users {
        INTEGER telegram_user_id PK
        TEXT consented_at
        TEXT pack_name
        INTEGER extra_packs_allowed
    }
    sessions {
        TEXT session_token PK
        INTEGER telegram_user_id
        TEXT template_id
        TEXT status
        TEXT notice
        TEXT closed_reason
    }
    jobs {
        INTEGER job_id PK
        INTEGER telegram_user_id
        TEXT session_token
        TEXT status
        INTEGER attempts
        TEXT pack_name
        TEXT link_sent_at
    }
    api_usage {
        INTEGER id PK
        INTEGER job_id
        TEXT stage
        REAL est_cost_usd
    }
    audit_log {
        INTEGER id PK
        TEXT actor
        TEXT action
        INTEGER telegram_user_id
        INTEGER job_id
    }
    settings {
        TEXT key PK
        TEXT value
        TEXT updated_at
    }
    leads {
        INTEGER id PK
        TEXT request_id UK
        TEXT status
        TEXT published_at
        TEXT erased_at
    }
    lead_status_history {
        INTEGER id PK
        INTEGER lead_id FK
        TEXT status
        INTEGER by_user_id
    }
    lead_repeats {
        INTEGER id PK
        INTEGER lead_id FK
        TEXT request_id UK
        TEXT notified_at
    }

Relationships: declared versus logical

From	To	Kind	Notes
`lead_status_history.lead_id`	`leads.id`	Declared (`REFERENCES leads(id)`)	Not enforced at run time: `PRAGMA foreign_keys` is never turned on. `lead_delete` removes child rows explicitly
`lead_repeats.lead_id`	`leads.id`	Declared (`REFERENCES leads(id)`)	Same
`sessions.telegram_user_id`	`users.telegram_user_id`	Logical	Empty until the guest opens the bot. A `users` row is written at `/start`, so it normally exists by then
`jobs.telegram_user_id`	`users.telegram_user_id`	Logical	The admin table joins on it with a `LEFT JOIN`
`jobs.session_token`	`sessions.session_token`	Logical	One session yields at most one job through the kiosk flow (`photo_taken` is final)
`api_usage.job_id`	`jobs.job_id`	Logical, deliberately without a key	When a guest is reset, their jobs are deleted but the spend must stay in the accounts
`audit_log.telegram_user_id`, `audit_log.job_id`	`users`, `jobs`	Logical	The log outlives the rows it refers to; a reset entry keeps the old pack links in `details`
`settings`	—	Standalone key-value table	—
`leads.chat_id`, `leads.message_id`	A Telegram message	External	Where the lead’s card is

The guest tables (users, sessions, jobs) and the leads tables are unrelated: a lead is an organiser’s request, not a guest.

Indexes

Index	Columns	Used by
`idx_jobs_status`	`jobs(status)`	Queue claim, active-job queries
`idx_jobs_user`	`jobs(telegram_user_id)`	Pack limit
`idx_api_usage_ts`	`api_usage(ts)`	Spend since a moment, daily request counter
`idx_api_usage_job`	`api_usage(job_id)`	Cost per job
`idx_audit_ts`	`audit_log(ts)`	—
`idx_leads_status`	`leads(status)`	Open leads, reminders
`idx_leads_outbox`	`leads(published_at, next_publish_at)`	Publication outbox
`idx_leads_created`	`leads(created_at)`	Period statistics, repeat detection
`idx_lead_history_lead`	`lead_status_history(lead_id)`	Card history
`idx_lead_repeats_lead`	`lead_repeats(lead_id)`	Card repeats
`idx_lead_repeats_outbox`	`lead_repeats(notified_at, next_notify_at)`	Repeat outbox

Column reference

“PD” = personal data. “Cleared” says when the value is removed automatically; “reset” means the row is deleted only when an operator resets the guest in the admin panel (reset_user), which first copies the database file.

`users` — one row per Telegram account that opened the bot

Column	Type	Meaning	PD	Cleared
`telegram_user_id`	INTEGER, primary key	The guest’s Telegram user id	Yes	Reset only
`consented_at`	TEXT	Time of explicit consent; empty = not given	Yes (consent record)	Reset only
`pack_link`	TEXT	Link to the delivered pack	Yes (the pack name embeds the user id)	Reset only
`pack_name`	TEXT	Actual name of the delivered pack; links are always built from it	Yes	Reset only
`pack_completed_at`	TEXT	When the link was sent	No	Reset only
`extra_packs_allowed`	INTEGER, default 0	Extra packs granted by an operator on top of one	No	Reset only
`first_name`, `last_name`, `username`	TEXT	Telegram profile, saved at `/start` and at consent. Used by the admin panel and as a fallback for the pack title	Yes	Reset only
`created_at`	TEXT	Row creation	No	Reset only

`sessions` — one row per tap on Start

Column	Type	Meaning	PD	Cleared
`session_token`	TEXT, primary key	Random token carried by the QR code	No (a capability, not an identity)	Reset only
`telegram_user_id`	INTEGER	Set when the guest opens the bot	Yes	Reset only
`template_id`	TEXT	Chosen outfit id from the catalogue	No	Reset only
`status`	TEXT	`idle`, `linked`, `consented`, `template_chosen`, `photo_taken`, `expired`	No	Reset only
`notice`	TEXT	Why the pack limit closed the session: `pack_ready`, `pack_in_progress`	No	Reset only
`closed_reason`	TEXT	Why the kiosk closed the session: `idle_timeout`, `qr_timeout`, `cancel`, `staff_reset`, `camera_error`, `kiosk_reset`	No	Reset only
`created_at`, `updated_at`	TEXT	—	No	Reset only

Sessions that were never linked to a guest (telegram_user_id empty) are not removed by any code path.

`jobs` — the generation queue

Column	Type	Meaning	PD	Cleared
`job_id`	INTEGER, autoincrement key	—	No	Reset only
`telegram_user_id`	INTEGER, required	Owner	Yes	Reset only
`session_token`	TEXT, required	Source session; also the name of the session folder on disk	No	Reset only
`photo_path`	TEXT, required	Path of the selfie file. The path stays; the file does not — it is deleted after the sticker set is created, or by TTL	The file: yes. The path: no	File only
`template_id`	TEXT, required	Outfit id	No	Reset only
`provider_used`	TEXT	Provider(s) that actually produced the pack, comma-separated	No	Reset only
`status`	TEXT	`queued`, `processing`, `delivering`, `done`, `failed`	No	Reset only
`attempts`	INTEGER, default 0	Attempts used	No	Reset only
`error`	TEXT	Last error, up to 1000 characters; also carries requeue markers	May contain the user id in deadline messages	Reset only
`pack_name`	TEXT	Name of the created sticker set	Yes (embeds the user id)	Reset only
`pack_created_at`	TEXT	Sticker set created; start of the readiness check	No	Reset only
`link_sent_at`	TEXT	Link sent to the guest (exactly once)	No	Reset only
`delay_notice_sent_at`	TEXT	The 5-minute notice was sent (exactly once)	No	Reset only
`appearance_json`	TEXT	The appearance card: a JSON description of the guest’s face and hair, cached so a retry does not pay for it twice	Yes	Set to `NULL` when the job becomes `done` or `failed`; the hourly cleanup also clears finished jobs and active jobs older than 15 minutes
`render_mode`	TEXT	`figure`, `composite`, `composite_fallback`	No	Reset only
`art_style`	TEXT	Art style id for figure mode; empty = `ART_STYLE` from the environment	No	Reset only
`created_at`	TEXT	Queue time = the moment of the “Generating…” message; base of all deadlines. Rewritten by an admin retry	No	Reset only
`updated_at`	TEXT	Last change	No	Reset only

`api_usage` — one row per AI provider call

Written for every call, including rate-limited and failed ones (ok = 0, cost 0).

Column	Type	Meaning	PD	Cleared
`id`	INTEGER, autoincrement key	—	No	Never
`job_id`	INTEGER	Job the call belonged to; no key on purpose	No	Never
`ts`	TEXT	Call time	No	Never
`kind`	TEXT	`image`, `vision`, `qa`	No	Never
`provider`, `model`	TEXT	—	No	Never
`prompt_tokens`, `output_tokens`	INTEGER	Token counts reported by the provider	No	Never
`images`	INTEGER, default 0	Images produced	No	Never
`est_cost_usd`	REAL, default 0	Estimate from prices in the environment at call time	No	Never
`ok`	INTEGER	1 success, 0 failure	No	Never
`http_status`	INTEGER	—	No	Never
`error`	TEXT	Provider error text	No	Never
`stage`	TEXT	`passport`, `anchor`, `anchor_verify`, `sheet`, `heads`, `regen`, `qa_sheet`, `qa_pack`, `qa_regen`	No	Never
`resolution`	TEXT	Image size requested (`1K`, `2K`, or pixel size for OpenAI)	No	Never
`image_tokens`	INTEGER	Output tokens billed at the image rate	No	Never
`thoughts_tokens`	INTEGER	Model “thinking” tokens billed at the text rate	No	Never

`audit_log` — admin panel actions

Column	Type	Meaning	PD	Cleared
`id`	INTEGER, autoincrement key	—	No	Never
`ts`	TEXT	—	No	Never
`actor`	TEXT	Admin panel login	Staff	Never
`ip`	TEXT	Client address of the operator	Staff	Never
`action`	TEXT	`grant_extra_pack`, `reset_user`, `retry_job`, `set_balance_override`	No	Never
`telegram_user_id`	INTEGER	Affected guest	Yes	Never
`job_id`	INTEGER	Affected job	No	Never
`details`	TEXT (JSON)	Action details. For `reset_user`: counts of deleted rows, the backup file name and the links of the guest’s old packs — these are read back later so a new pack never reuses an old number	Yes (pack links embed the user id)	Never

Note for privacy work: a guest reset removes users, sessions and jobs rows but leaves the guest’s id and old pack links in audit_log, and leaves api_usage untouched.

`settings` — key-value store shared by both processes

Column	Type	Meaning
`key`	TEXT, primary key	See the table of known keys below
`value`	TEXT, required	Plain text or JSON
`updated_at`	TEXT, required	Last write

Known keys (found by searching the code for get_setting / set_setting):

Key	Value	Written by	Read by	Removed
`maintenance`	JSON `{source, reason, at}`; `source` is `auto` or `admin`	The worker when a provider reports no funds; `/pause`	Bot middleware, worker loop, kiosk endpoints, `/health`, admin summary	`/resume` deletes the row
`billing_balance_override`	JSON `{value, set_at}` — the real billing balance entered by an operator	`POST /admin/api/balance`	Balance estimate: value minus spend since `set_at`	Overwritten by the next entry
`stickers_digest_last`	`YYYY-MM-DD` (Tashkent date) of the last daily digest	Leads loop	Leads loop — prevents a duplicate digest after a restart	Overwritten daily
`rpd_warned:<YYYY-MM-DD>`	Number of image requests when the warning fired; the date is the provider’s quota day (Pacific time)	Worker, after a job	Worker — one warning per quota day	Never; one small row per day on which the warning fired

`leads` — requests from the landing form

Column	Type	Meaning	PD	Cleared
`id`	INTEGER, autoincrement key	The lead number shown in the group as `#stickers_<id>`	No	Test deletion only
`request_id`	TEXT, unique, required	UUID made by the form; a retry with the same id does not create a second lead	No	—
`created_at`	TEXT, required	Server time of storing	No	—
`submitted_at`	TEXT	Time as sent by the landing	No	—
`name`	TEXT	Contact name	Yes	`NULL` at erasure
`telegram`	TEXT	`@username`	Yes	`NULL` at erasure
`phone`	TEXT	`+digits` (E.164)	Yes	`NULL` at erasure
`event_date`	TEXT	Free text	No	Kept
`guests`	TEXT	Free text	No	Kept
`comment`	TEXT	Free text; treated as personal data because people put contacts in it	Yes	`NULL` at erasure
`locale`	TEXT	`ru`, `uz` or `en`	No	Kept
`utm`, `utm_source`, `utm_medium`, `utm_campaign`	TEXT	Raw UTM string and its parsed parts	No	Kept
`ip_hash`	TEXT	HMAC-SHA256 of the visitor’s IP with `LEADS_IP_SALT`, first 32 hex characters. The IP itself is never stored	Pseudonymous	`NULL` at erasure
`status`	TEXT, default `new`	`new`, `in_work`, `contacted`, `deal`, `refused`, `spam`	No	Kept
`status_by`	INTEGER	Telegram user id of the admin who set the status	Staff	Kept
`status_by_name`	TEXT	That admin’s display name	Staff	Kept
`status_at`	TEXT	When the status was set; base of the retention period	No	Kept
`chat_id`, `message_id`	INTEGER	Where the card is in Telegram	No	Kept
`published_at`	TEXT	Card posted; empty = still in the outbox	No	Kept
`publish_attempts`	INTEGER, default 0	Failed publication attempts	No	Kept
`next_publish_at`	TEXT	Earliest time of the next attempt	No	Kept
`publish_error`	TEXT	Last publication error, up to 300 characters	No	Kept
`reminded_at`	TEXT	The “no status for over 2 hours” ping was sent	No	Kept
`erased_at`	TEXT	Personal data erased (by request or by retention)	No	—

Erasure rules (lead_erase, leads_to_erase): on /stickers_forget <id> immediately; automatically 30 days after the last status change for spam and 365 days for every other status, counted from status_at or, if the status never changed, from created_at. The row, its status and its history stay so statistics do not change.

A request delivered through the landing’s Telegram fallback is not in this table at all.

`lead_status_history` — every status change

Column	Type	Meaning	PD	Cleared
`id`	INTEGER, autoincrement key	—	No	Test deletion only
`lead_id`	INTEGER, required, declared FK	The lead	No	—
`status`	TEXT, required	New status	No	—
`by_user_id`	INTEGER, required	Telegram user id of the admin	Staff	Kept
`by_name`	TEXT	Admin’s display name	Staff	Kept
`at`	TEXT, required	When	No	—

Pressing the button of the current status writes nothing.

`lead_repeats` — repeat contacts within 24 hours

One row each time the same Telegram username or phone submits the form again within 24 hours of an existing lead. Only fields that differ from the lead are stored.

Column	Type	Meaning	PD	Cleared
`id`	INTEGER, autoincrement key	—	No	Test deletion only
`lead_id`	INTEGER, required, declared FK	The lead this repeats	No	—
`request_id`	TEXT, unique, required	UUID of the repeated submission	No	—
`at`	TEXT, required	When	No	—
`telegram`	TEXT	New username, if different	Yes	`NULL` when the lead is erased
`phone`	TEXT	New phone, if different	Yes	`NULL` when the lead is erased
`event_date`, `guests`	TEXT	New values, if different	No	Kept
`comment`	TEXT	New comment, if different	Yes	`NULL` when the lead is erased
`locale`, `utm_source`	TEXT	Of the repeated submission	No	Kept
`ip_hash`	TEXT	Hashed visitor IP	Pseudonymous	`NULL` when the lead is erased
`notified_at`	TEXT	The card was updated and pinged (or the repeat was absorbed or given up on)	No	—
`notify_attempts`	INTEGER, default 0	Failed notification attempts	No	—
`next_notify_at`	TEXT	Earliest time of the next attempt	No	—
`notify_error`	TEXT	Last error or the reason no ping was sent	No	—

Where personal data is, in one table

Data	Location	Leaves automatically?
Selfie and drawings of the guest	Files under `DATA_DIR`	Yes — after pack creation (selfie) and by TTL (everything)
Textual face description	`jobs.appearance_json`	Yes — at the end of the job
Guest’s Telegram id, name, username, pack link	`users`, `sessions`, `jobs`, `audit_log`	No — operator reset only, and `audit_log` keeps the id
Organiser’s name, username, phone, comment	`leads`, `lead_repeats`	Yes — 30 or 365 days, or on request
Organiser’s IP	Stored only as a salted hash	Yes — with the lead
Lead admins’ and operators’ ids, names, IP	`leads.status_by*`, `lead_status_history`, `audit_log`	No
Everything in the database	Daily backups (14 kept) and the `app.db.bak-*` files made before migrations and resets	Daily backups rotate out in 14 days; `app.db.bak-*` files are not removed by any code

Where to go next

State machines — how the status columns change.
Component: storage and infrastructure — the file, WAL, migrations and backups.
Privacy — the same data from the legal and retention angle.
Flow: data erasure — each clearing rule as a sequence.
Known gaps — open issues, including retention of guest rows.

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

Data model

Who is this for

TL;DR

ER diagram

Relationships: declared versus logical

Indexes

Column reference

users — one row per Telegram account that opened the bot

sessions — one row per tap on Start

jobs — the generation queue

api_usage — one row per AI provider call

audit_log — admin panel actions

settings — key-value store shared by both processes

leads — requests from the landing form

lead_status_history — every status change

lead_repeats — repeat contacts within 24 hours