ibkr canary
DocumentationUnder the hood

Storage

When ibkr restarts, it must pick up the same account context with the same safety history: which state was current, which evidence supported a decision, which preview tokens were consumed, and how far broker order IDs advanced.

That job belongs to the daemon's storage layer, a local SQLite file named daemon.db reached through a pure-Go driver inside the ibkr binary. SQLite supplies the storage engine. The surrounding code defines what may be stored, who may write it, and how it becomes safe to use.

One daemon writes durable runtime state. Product surfaces read it through typed daemon APIs. Human-authored policy and original broker evidence keep their own sources of record.

What the daemon owns

What the daemon storage layer owns and what remains outside it

PNG fallback · SVG source generator · Tabler Icons license

Inputs with their own owners enter from the left, and CLI, app, and MCP surfaces receive structured results on the right. The boundary keeps the meaning of a row with the code that created it, and it gives broker-critical writes one ordered path instead of several competing writers.

daemon.db carries five kinds of continuity:

Several records stay beside the database because they have different owners or recovery lifecycles:

Record Owning location Role
Human intent and approved limits config.toml and policies/*.toml People author these declarations. SQLite retains applied identities and resulting events.
Original broker statements $XDG_STATE_HOME/ibkr/statements/flex-*.xml The retained XML is the broker-supplied evidence behind SQLite's inventory and derived daily views.
Preview signer and Flex credentials Private config and state files Secrets and signing material rotate independently from ordinary database rows.
Device grants, push subscriptions, and relay credentials App-owned state directory The app has separate authentication duties and never opens daemon.db.
Recovery material daemon.db.head, verified backups, and sealed legacy artifacts These detect rollback or support offline recovery. Normal product reads use the published database.

A lock beside daemon.db prevents alternate socket paths from writing the same file concurrently. A second socket is not isolation on its own; Architecture lists what a separate stack needs.

Why SQLite fits this desk

The current deployment has one application writer on one machine, and SQLite fits that shape. It is a local file with no database server, port, credentials, or administrator. One transaction can commit state, evidence, token tombstones, order floors, and lifecycle events as a unit, and every writer goes through the daemon's typed transaction APIs. Tables and indexes support bounded state, event, order, observation, and statement queries.

Several writers or hosts would require a different topology, as would automatic failover or many concurrent analytical readers. Giving those readers the live file instead would bypass the ownership and recovery rules that make the current design safe.

The data model

The schema follows the questions the product must answer:

Product question Storage structure Meaning of one row
What is true now? state_documents, statement_files, statement_equity_days The latest accepted revision or selected statement-derived record for one scope and kind.
What did the software observe or decide? event_log One immutable local lifecycle event with its original JSON payload and digest.
What was measured? observations One retained source measurement, including observation time and decision eligibility.
What must never be reused or move backwards? broker_scopes, consumed_preview_tokens, order_id_floors, order_events Route identity, a spent capability, a conservative ID floor, or an order-lifecycle fact.
What did the broker originally report? Retained Flex XML plus four statement tables SQLite records exact file versions and current or historical daily-equity projections.

Relational columns carry safety-critical identities and irreversible facts, and keys, foreign keys, constraints, indexes, and triggers make those rules visible to SQLite. Varied current snapshots stay versioned JSON documents, and immutable events keep their original payloads. Frequently queried event fields may also appear in relational projection tables. Those projections are not the read path yet: the main Regime, rules, Canary, and capital-history readers still load matching event_log JSON and filter it in Go.

Physical entity relationships in daemon.db schema version 1

PNG fallback · SVG source generator · Canonical DDL

The diagram shows schema version 1. Solid lines are declared SQLite foreign keys. Dashed lines describe relationships enforced by Go code. A shared name such as scope_key creates a namespace only when no foreign key joins it.

Parent Child Cardinality
event_log Each event projection: regime_decisions, rule_transitions, canary_transitions, capital_events, risk_policy_events, proposal_outcomes, and order_events One event to zero or one row in each individual child table.
regime_decisions regime_indicators One decision to zero or more indicators.
broker_scopes consumed_preview_tokens One broker route to zero or more consumed tokens.
broker_scopes order_events One broker route to zero or more order events.
statement_files statement_equity_days One current statement-file identity to zero or more current daily-equity rows.
statement_file_versions statement_equity_day_versions One immutable file version to zero or more immutable daily-equity versions.

Six tables stand alone: store_meta, schema_migrations, legacy_imports, state_documents, observations, and order_id_floors. Other useful relationships also remain application conventions:

The renderer compares its complete table and foreign-key inventory with internal/daemon/corestore/schema.go, so schema drift fails the diagram gate. Readers of JSON documents, events, and observations still need the expected kind and payload version, plus scope, time basis, provenance, eligibility, and data quality.

Writing and reading data

Commit a state change

Publishing a market observation follows one ordered path:

  1. Validate scope, payload, source metadata, and the expected current revision.
  2. Update the current state_documents row and append the immutable observations row in one SQLite transaction.
  3. Advance the committed-write counter and commit under WAL with foreign keys and full synchronization enabled.
  4. Update and fsync daemon.db.head, the external record of the newest accepted counter, before publishing the new in-memory and RPC view.

A failure in step 4 happens after the SQLite commit. The store reports the failure, marks itself unhealthy, and withholds the new in-memory/RPC view. State and observation remain transactionally aligned, although the current schema stores no direct observation ID on the state row.

Protect a broker transmission

Before sending an order, one transaction binds the exact broker route, records the consumed preview token, advances the conservative order-ID floor, and appends the pre-transmit event. A failed transaction stops the send. Policy, dashboards, and alternate files cannot replace this durable safety evidence.

Read current and historical data

Question Supported path Present limitation
Current product or dashboard state Typed daemon RPC and a daemon-owned reader A new dashboard needs a defined contract before it gets a query.
Regime, rules, Canary, or capital history Existing CLI commands over typed daemon RPC Most paths still scan canonical event JSON instead of indexed projections.
Orders ibkr orders open, ibkr orders history, and ibkr order status Local lifecycle records explain intent and evidence; the broker Activity Statement remains execution truth.
Statement-derived equity Typed reconciliation and equity readers The current reader has a fixed result ceiling rather than a general paginated API.
Retained observations Narrow daemon-owned readers for their product purpose General research access awaits a corrected time-and-ID pagination cursor.
Offline forensic SQL Stop the daemon and open daemon.db read-only, or inspect a verified consistent backup SQL shapes are implementation details and may change with the binary.

A running WAL database may contain committed changes outside the main file. Copying daemon.db alone can therefore produce an inconsistent artifact. Keep sqlite3, Grafana, notebooks, ORMs, and other services away from the live file.

For a new dashboard or analysis, define scope, row meaning, time basis, freshness, eligibility, ordering, pagination, and redaction first. Then add a bounded daemon query or typed export backed by a suitable index or projection.

Durability and recovery

Normal operation

The store uses WAL, foreign keys, synchronous=FULL, supported full-fsync controls, a bounded busy timeout, and one serialized connection. Serial access keeps writer ordering simple. It also means an unbounded read can delay a broker-critical write, so production reads need limits and indexes.

Startup gate

Storage becomes ready before state adapters attach, the daemon serves RPC, schedulers run, or either broker connection starts. Startup checks private file types and modes, SQLite's application ID and schema version, the checksummed migration ledger, the exact table/index/trigger inventory, quick_check, foreign keys, the external minimum write counter, and digests for selected payload columns.

This catches schema drift, structural damage, rollback, and selected payload changes. Integrity coverage remains selective: event headers, observation metadata and eligibility, typed projections, broker bindings, and current statement winners do not yet share one canonical whole-record digest.

The daemon writes and fsyncs the daemon.db.head watermark before publishing the initial database and after every committed mutation. An existing database with no watermark fails closed and waits for explicit verified recovery.

Schema upgrades

A fresh installation is created and validated directly at the binary's target schema. For an existing database, an equal schema version still passes full validation, a newer one refuses downgrade, and an older valid one enters the automatic upgrade coordinator, which never mutates the published database in place.

Under the persistence lock the coordinator takes a verified standalone backup at the exact current authority head, applies immutable ordered migrations to an unpublished same-directory candidate, and runs the full schema, integrity, foreign-key, content-hash, and authority checks before advancing the authority head once and publishing atomically with the authority epoch and evidence intact. A small fsynced recovery manifest carries the durable upgrade phase across a restart; it is transient coordination, not another business-state authority.

The pre-upgrade backup stays recovery-only. Ambiguous recovery state stops startup, and a failed or ambiguous upgrade never triggers automatic repair or restore.

SQL schema versions govern tables, indexes, constraints, and triggers, while mutable JSON documents carry independent, kind-specific payload versions and typed migrations. Append-only events are not rewritten to fit a new shape; a new event or payload version retains a compatible reader or feeds a new projection.

The full crash-boundary protocol lives in the SQLite implementation contract. Schema version 1 remains the only production migration, so the general coordinator has yet to carry a released schema transition.

Backup and restore

The code can create and verify a standalone backup, and upgrades use that primitive. The operator surface still lacks scheduled-current backups, backup status, restore commands, retention, off-host copies, recovery objectives, and a rehearsed restore runbook. Recovery is an offline procedure because the database head, preview signer generation, broker-open orders, and conservative order-ID floors must agree before writes resume.

Current limits and evolution

Area Current state Next decision
Event projections Projection rows are written while most non-order histories read event JSON. Route bounded reads through projections or remove redundant tables when compatibility allows.
Read concurrency One connection serves reads and broker-critical writes; several histories scan broadly. Bound and index reads before adding a read-only handle or pool.
Observation pagination Results order by observation time plus ID while the cursor carries only ID. Fix the cursor before exposing general research queries.
Original Flex durability XML publication closes and renames without fsyncing the file and statements directory before the SQLite projection may commit. Repair the publication boundary so a power loss cannot separate evidence from its projection.
Integrity coverage Structural checks and selected payload hashes leave some semantic fields and current statement winners uncovered. Extend canonical validation where audit requirements demand it.
Backup and lifecycle Scheduled backup, restore, retention, archive, size budgets, and restore drills are absent. Define the operating lifecycle before retained evidence grows without bound.
Desk identity Business state assumes one isolated daemon/account context. Add opaque entity, portfolio, book, currency, actor, and command identities before family-office consolidation.
Migration layout Append-only triggers are appended dynamically to migration 1. Put future triggers in the migration that creates their table so old checksums stay immutable.

Dedicated order-safety and statement-version tables earn their cost because they protect irreversible facts and restatements. Write-only projections, unbounded duplicate snapshots, and application-only relationships need an equally clear read or safety purpose.

The storage topology should evolve when several hosts must write, many readers need concurrent analysis, several entities or books need consolidation, retained data makes startup validation unsafe, or the desk adopts centralized retention and recovery objectives. The natural next layer is a central read-only analytical and control plane fed by typed, redacted, versioned exports or a change stream from each authoritative daemon.

Storage terms and references

Term Plain-language meaning
Projection Selected fields copied from a richer payload into searchable columns.
Revision check Update only when the caller's expected revision is still current.
WAL SQLite's write-ahead log, which may contain committed changes not yet folded into the main database file.
Write counter An ever-increasing number for the newest committed storage change. daemon.db.head records the minimum accepted value.
Verified backup A standalone copy tied to one known write counter and reopened for validation.
Decision eligible Allowed to support a live decision. Historical observations may be retained for research without this permission.
Broker route The exact endpoint, client ID, account, and mode combination belonging to an order lifecycle.