Designing a SQLite Schema for Trade Persistence | Toss Dev #3-1

Phases 0 through 2 got the bot’s foundation, stability, and strategy layer in place. The next item on the roadmap was data persistence. This time I didn’t start with code. The scope was clearly bigger than anything before it, so I locked the design down in a document first instead of coding and backtracking halfway through.

Scoping this session down to one thing

Data persistence breaks into five steps: schema design, db.py implementation, auto_trader.py integration, toss_portfolio.py integration, and tests. This session covered exactly one of those — the schema. The rest gets pushed to next time. Trying to force all five in one pass has bitten me before.

The trades / alerts / price_snapshots schema

Three tables:

• trades — trade history. Beyond symbol, quantity, and price, it carries is_dry_run (was this a simulated fill) and strategy_name (which strategy fired the signal).
• alerts — alert history: message, level, and an optional related symbol.
• price_snapshots — price snapshots. Price is stored as REAL to handle fractional pricing on overseas stocks, with a separate currency column (KRW/USD). This mirrors a lesson from an earlier currency bug where overseas holdings dropped out of the totals — same KRW/USD separation, now baked into the data layer.

Since time-series lookups will be frequent, I already planned a composite index: CREATE INDEX idx_price_symbol_ts ON price_snapshots(symbol, timestamp).

Simulated (dry-run) trades get logged in the trades table too, marked by an is_dry_run column instead of being dropped or kept separate. That preserves tuning data from simulation runs while keeping real and simulated trades unambiguous.

The one decision that took the longest — log simulated trades or not?

Whether DRY_RUN buys should show up in trade history at all was the harder call. Skip them, and there’s no record of how often a strategy actually fired. Log them without marking them, and real trades get muddied. The answer was to log them and flag them via is_dry_run, which keeps it consistent with the project’s triple-safety-net principle.

Locking the db.py interface at the same time

Beyond the schema, I also fixed the function signatures for db.py:

def get_connection():
    ...

def init_db():
    ...

def insert_trade(symbol, quantity, price, is_dry_run, strategy_name):
    ...

def insert_alert(message, level, symbol=None):
    ...

def insert_price_snapshot(symbol, price, currency, timestamp):
    ...

A DB write failure can’t be allowed to stop the trading loop, so this follows the same best-effort pattern already used for Telegram alerts — a failed insert just gets logged and the loop moves on.

Testing strategy: in-memory SQLite, not real files

Rather than spinning up a real SQLite file per test run (and risking contamination across repeated runs), the plan is to use sqlite3.connect(':memory:') for verification. That’ll carry over directly into the pytest suite next session.

What’s next

This post covers design only. Next session picks up from this document to actually implement db.py, wire it into auto_trader.py and toss_portfolio.py, and write tests against the in-memory DB. That work will be its own post — Phase 3-2.