RedditBot/docs/database.md

Data model and persistence

Storage model

The application uses one better-sqlite3 database, normally data/reddit-bot.db, in WAL mode with foreign keys enabled. The persistent Docker volume also holds browser profiles and downloaded media, so a database-only backup is not a complete account/content backup.

src/database/client.ts is the schema authority. Migrations are ordered functions guarded by SQLite PRAGMA user_version and run inside transactions. The repository does not use an ORM.

Ownership categories

Account registry and account content

• reddit_accounts: verified Reddit/Matrix identity, encrypted-at-rest only to the degree provided by the host/volume, cookie JSON, Matrix credentials, active/status timestamps.
• reddit_conversations: account-scoped cached Matrix conversation payloads.
• reddit_account_sync_state: account-scoped sync cursor, full/delta timestamps, and invitation cooldown.
• reddit_recipient_account_status: process-global, case-insensitive recipient username status (active, suspended, or deleted) and last confirmed check time.
• reddit_media: account-scoped private-media metadata and relative file path.
• media_library_assets: process-global operator-owned image metadata, overlapping anatomy tags, SFW/NSFW classification, consent time, enablement, dimensions, hash, and private relative path.
• media_library_sends: account/conversation/inbound-event reservation and confirmed/failed Matrix delivery audit for a shared library asset.
• account_growth_settings: per-account commenting switch, adaptive trust level, spacing/jitter, quiet hours, NSFW opt-in, and the operator-clearable pause reason.
• account_growth_subreddits: per-account community list with weight, daily cap, and any Reddit-imposed disable reason or cooldown.
• account_growth_comments: durable growth comment records from discovery through posting and verification, including the exact text posted, score history, removal and public-visibility verdicts.

The special legacy-unassigned account exists only to preserve rows created before account ownership was introduced. New runtime data must use a verified account ID.

Outreach and outcomes

• processed_posts: account/post key, source metadata, analysis, generated opener, send marker, and terminal error/rejection reason.
• sent_messages: account-owned opener/reply attribution, strategy/experiment/model metadata, Matrix room, response/image flags, and source type.
• successful_messages: mature positive examples for prompt context; terminal negative reclassification removes the linked example.
• conversation_outcomes: normalized outcome for a sent message (engaged, declined, boundary, upsell, or unknown).
• activity_log: operator-facing events with optional recipient/post context.
• reddit_users: global mapping between Reddit fullname/username and Matrix identity.
• recipient_suppressions: process-global recipient reservation/contact record used to prevent cross-account duplicates.

Durable work and idempotency

• candidate_queue: global candidate snapshot, source/priority, state, prepared payload, worker lease, retry timing, selected account, and rejection/error details. Image preparation snapshots retain the generated opener, grounded analysis, both AI scores, the physique range used, source-image URL, and experiment/model/tone metadata for delivery and operator review.
• delivery_outbox: account-scoped prepared delivery and idempotency status retained for legacy/recovery paths.
• conversation_states: account/conversation policy and derived state JSON.
• conversation_automation_claims: account/conversation/inbound-event claim with intended reply and pending, sent, or terminal superseded state plus audit timestamps.
• runtime_state: small process controls such as whether bot work is running.

Configuration and telemetry

• app_config: one secret-free validated application configuration record.
• ai_usage: requested/actual model, token/cost, latency, success/error, purpose, and account attribution.
• legacy_content_imports: records one-time imports from legacy content stores.

Critical invariants

1. Account-produced rows carry account_id; cross-account views must opt into global queries explicitly.

2. Recipient identity comparisons are case-insensitive and global for outreach suppression.

3. A sent message is persisted only after remote confirmation.

4. Candidate and automation claims are lease/claim based; a worker must own the expected lease before it changes state.

5. Conversation policy is snapshotted in state JSON when automation starts for that conversation.

6. Secrets and the database path are not stored in app_config.

7. Private media paths in SQLite are relative to the persistent data root; HTTP access remains authenticated.

8. Received reddit_media and operator media_library_assets are separate ownership domains; no automatic copy or promotion exists. Received-media identity represents the account-scoped Matrix message occurrence, so repeated use of one remote asset can produce multiple gallery rows that safely share the same private relative file path.

9. A library image content hash has at most one reservation per account conversation, even if the same file is later re-uploaded under a new asset ID; an inbound request also has at most one selected asset. Failed or uncertain attempts remain reserved to prevent duplicates.

10. Exactly one account may comment on a given thread. account_growth_comments enforces this with a unique index on post_id across all accounts, because two owned accounts commenting on the same post is the easiest pattern for Reddit to correlate.

11. A growth comment interrupted while posting is rejected, never retried. The process cannot know whether the write reached Reddit, and a duplicate comment cannot be withdrawn.

12. Recipient account status changes only after a definitive Reddit profile response. Transient lookup failures retain the previous value, and the shared check timestamp prevents duplicate lookups across account runtimes during the four-hour interval.

Repository map

src/database/repositories.ts contains the domain repositories for posts/messages/outcomes/activity/outbox/state/claims/users/recipient history. account-repositories.ts owns account records and cached account content. Candidate and AI telemetry have dedicated repositories.

Prefer narrow repository methods such as claimReady, markSent, or getByChatRoomId to exposing SQL or generic CRUD. This keeps transaction and ownership rules near the schema.

Candidate leases

Discovery inserts candidates without overwriting terminal work. Screening and dispatch use a worker ID plus lease expiry. On startup all interrupted leases can be recovered; during operation expired leases are recovered and due retries promoted. A transient account failure belongs to the selected account, so delivery can be made visible quickly for another eligible runtime without losing the prepared candidate.

Any new candidate state needs:

• an allowed predecessor and terminal/retry semantics;
• a lease ownership rule;
• recovery behavior after process death;
• counts/reasons for the operator funnel;
• tests for stale lease, wrong owner, restart, and duplicate post/author cases.

Conversation reply claims

The unique identity is account + conversation + inbound message key. The claim persists the intended reply before the remote send. A pending claim is not blindly retried: Matrix history is checked first. After rate-limit waiting, Matrix is refreshed again; a newer event, boundary, received image, seller signal, operator-owned state, or repeated media request changes the claim to superseded and records superseded_at. This closes both the process-crash window between remote acceptance and local completion and the stale-send window while a reply is queued.

Changes to this flow must prove at-most-once observable replies across retry and restart tests.

Outcome rows are re-evaluated from the complete attributed recipient transcript. Negative outcomes take precedence over reciprocal activity. successful_messages rows are created only after the conversation becomes terminal/human-owned and are deleted by sent_message_id when later evidence changes the outcome.

Configuration persistence

On an empty database, config.yaml is parsed, environment-owned values are injected, the result is validated, and the application-owned subset is saved to app_config. Later starts load SQLite first. Dashboard changes update that record.

The persisted record excludes:

• OPENROUTER_API_KEY;
• dashboard/agent tokens;
• DATABASE_PATH.

Those values are supplied by the environment on every load.

Adding a migration

1. Inspect the final schema and all repository assumptions.
2. Append one migration function; never reorder or modify an already-released migration.
3. Make the operation transactional and compatible with existing data.
4. Use addColumnIfNotExists only where repeatability is appropriate; table rebuilds need explicit foreign-key handling and foreign_key_check.
5. Update repository types and operations.
6. Add tests for a fresh in-memory database and, when relevant, an older schema upgraded in place.
7. Update this document and affected API/contracts.

Do not edit a live SQLite file by hand. Back it up, stop writers when required, apply code-owned migrations, and verify integrity_check, foreign_key_check, row counts, and application reads.

Backup and recovery

For a running WAL database, use SQLite's online backup mechanism or stop the container before copying the database plus -wal/-shm state. A full production backup should include the entire persistent volume because sessions and media live beside the database. Validate that the backup can be opened and contains expected tables before any destructive reset.