How a small bot can use SQLite for durable event IDs, decisions and recovery without introducing a separate database server.
In plain English
This guide focuses on making data and code predictable: define types, validate inputs, handle errors explicitly and test the cases that should fail.
Why SQLite works well for small bots
SQLite is a file-backed transactional database included with Python. It is useful when one host owns the bot state and write volume is modest.
A minimal schema
CREATE TABLE events (
event_id TEXT PRIMARY KEY,
received_at TEXT NOT NULL,
symbol TEXT NOT NULL,
action TEXT NOT NULL,
decision TEXT NOT NULL,
reason TEXT
);
CREATE TABLE executions (
id INTEGER PRIMARY KEY AUTOINCREMENT,
event_id TEXT NOT NULL,
broker_order_id TEXT,
status TEXT NOT NULL,
created_at TEXT NOT NULL
);Transactions matter
Use a transaction when “mark event handled” and “write execution result” must stay consistent. Enable WAL mode if it fits your access pattern and back up the database file when the bot is stopped or via a SQLite-aware approach.
Do not confuse the CLI with the library
A server can have Python's built-in sqlite3 module even if the separate sqlite3 command-line program is not installed. If a shell command is missing, verify the application capability before assuming the database itself is unusable.
Before you rely on this in production
- Input types are validated.
- Errors are handled instead of swallowed.
- Tests include invalid and boundary cases.
- Secrets are not embedded in code or test fixtures.