14. Audit trail

A compliance officer, a risk officer or a regulator opens this record with one question: for a given order that left this installation, what decided to send it, against what state, and when. An account created under a storage regime that includes the database answers that question from one file, and every row of the retained_trades table in that file is one execution, stated completely and never afterwards altered: the rule and the robot that produced it, the market event it responded to, the moment it executed, what executed, and the state of both the position and the whole portfolio as they stood immediately before it was applied. This chapter reads that record the way a reviewer reads it — what it establishes, what it does not, and what has to be kept beside it. The appendix carries the schema itself, column by column with SQL types, together with the file's name and where it is written; none of that is repeated here.

What the record answers

The columns fall into groups, and each group exists to close one question. The appendix names every column and its type; the table below says what each group is for.

Three properties of that record decide how much weight it carries, and each has to be read exactly.

The two snapshots are observations, not reconstructions. The engine reads the contract exposure and the portfolio exposure, then applies the fill to them, and only then hands the trade and both snapshots to the blotter. The insert takes exactly three things — the trade and the two snapshots — and writes them in one transaction, so the state a row reports is the state that existed before that row's fill, captured at that moment rather than recomputed afterwards from the sequence of trades. A reviewer who recomputes the position from the trades and gets a different answer has found a real discrepancy, not a rounding of the journal.

A row is a fill, not an order. The quantity, price and fee written into a row are the quantity, price and fee of one fill report. An order filled in slices produces one row per slice, each with its own snapshot pair, and the order is reassembled by grouping on client_order_id.

A reversal is journaled as two rows. When a transaction crosses through flat — closing a position and opening the opposite side in one execution — the blotter splits it into the part that closes the existing round trip and the part that starts the new one, writes both, and marks both with book_mode_code set to the split code. The two rows share their order identifiers and their timestamps. The second of them carries a neutral contract-exposure snapshot rather than an observed one, because by construction it begins from flat; its portfolio snapshot is the same observed one as the first row's. booked_pl is booked on the closing part only: profit and loss is realized when exposure moves toward zero, and a row that increases exposure carries only the negated fee.

What the record guarantees

Rows are written once and never changed. The insert carries ON CONFLICT ... DO NOTHING against a compound uniqueness constraint over ten columns, so a conflict resolves by discarding the incoming row, never by updating the stored one. There is no UPDATE, no DELETE and no DROP against this table anywhere in the engine or in the exported surface: the only way a stored row changes is if something outside the library edits the file. id is INTEGER PRIMARY KEY AUTOINCREMENT, which means SQLite never reuses a key, so a gap in the sequence is evidence that a row was removed by something other than the engine.

Write order is recoverable; time order is not asserted. id increases with the order in which rows were journaled, and it is the only column that carries that guarantee. Neither timestamp is checked for monotonicity anywhere in the engine. ts_execution is assigned by the execution point — the simulator in a backtest, the venue in production — and is written as received; ts_mkt_event is the timestamp of the input element that fired the pattern, and is likewise written as received. Order the journal by id when the question is what the engine did in sequence, and by a timestamp only when the question is about wall-clock time.

The two timestamps are independent and are meant to be compared. ts_mkt_event is a property of the data the decision was taken on; ts_execution is a property of the venue's report. Their difference is the latency of the whole path from market event to fill, and neither is derived from the other.

The write is transactional and blocking. The insert opens an explicit transaction, writes the robot's row into strategies and the trade's row into retained_trades, and commits only if the statement completed; any failure rolls the whole transaction back. A lock contended by another writer is retried five times at 100 ms intervals before the write is reported failed. When the blotter runs on a dedicated storage thread, the caller submits the work and waits for it, so there is no queue of pending writes that a crash could discard.

Durability is bounded, and the bound has to be stated. The database is opened in WAL journal mode with PRAGMA synchronous = OFF, and foreign-key enforcement on. synchronous = OFF means SQLite issues no fsync at commit. The consequence splits in two, and the halves are not interchangeable. If the process dies — a signal, an uncaught exception, an abort — committed rows survive, because the data has already been handed to the operating system and its flushing does not depend on the process. If the operating system dies or power is lost, there is no guarantee: committed transactions may be lost and the write-ahead log may be left damaged. On an orderly shutdown the database is checkpointed into the main file before the handle closes; an abrupt one leaves that to the ordinary log replay at the next open. An installation that must survive power loss has to raise synchronous, and that setting is an internal default today rather than something the public surface exposes.

What ex_post is not

The scoring database is a separate file with its own tables — ex_post_strategies, ex_post_serie and ex_post_scoring — and it is not part of the audit trail. It holds derived analytics: per-robot summaries bucketed by duration, and the scores computed from them. Every write into it is an upsert, INSERT ... ON CONFLICT (...) DO UPDATE SET ..., against that table's declared key — the robot and the duration bucket for a summary row, the robot, the scoring moment and the parameter for a score. A row whose key already exists is overwritten in place, and what it held before is gone. Re-running a scoring pass over the same robot and the same bucket replaces the earlier numbers without leaving a trace that they were ever different.

This is the correct behaviour for a derived store, which is what it is: everything in it is recomputable from the trades. It is stated here so that the distinction is on the record before anyone infers otherwise. retained_trades is the evidence; ex_post is a view over the evidence, and only retained_trades is insert-only.

What you must keep alongside it

The chain a reviewer wants to walk runs from data through firing and rule to risk check and order. The journal covers that chain from the rule onward. The first links are not in it, because the engine persists no market data at all: there is no table, anywhere in it, holding quotes, bars, book messages, input values or pattern values. ts_mkt_event is a pointer to the instant, not a copy of what was read at that instant.

Closing the chain is therefore the operator's responsibility, and it takes three things.

• The input stream, timestamped on the same scale. Both timestamp columns are nanoseconds since the Unix epoch. A retained recording of the ticks fed to the adapters, carrying those same timestamps, joins to the journal on equality between ts_mkt_event and the timestamp of the input element. Without it, what the strategy saw when it fired cannot be reproduced.
• The recipe the robot ran under. Inputs, patterns and rules with their parameters, as saved by the save-and-load machinery the environment chapter describes.
• The code behind every registered processor key. A recipe stores the key of a processor, not its body. Two runs under the same key and different code are indistinguishable in the journal and in the recipe alike.

One partial exception is worth knowing. When a rule leaves a field undefined and takes it from the signal, what the signal brought for that field lands in the order and therefore in the journal: a signal-carried size shows in the quantity column, a signal-carried price in price_type and the limit price, a signal-carried direction in the side columns. What is recoverable is the figure the pattern published, not the input value behind it, and only for the fields the rule left undefined; everything else in the row is the rule's own declaration. In a rule that declared all three fields, nothing of the firing beyond its moment is persisted in any form.

What the record does not carry

An honest reading of the journal has to name its gaps, because each of them is a question an auditor will ask and the record will not answer.

A refused order leaves no row. The engine refuses an order before it is sent in two places: when reserving the order's exposure fails, and when a risk policy does not hold at submit time. Both paths set a diagnostic message, write a log line, roll back every reservation the submission took, and return. Neither touches the database. The refusal is real and it is recorded, but it is recorded in the rotating text log — appended, rotated daily and capped by size — and not in any table. Reconstructing the moment and the reason of a refusal after the fact is possible only from that log file, and only while it is still within the rotation window. There is no table of refusals to query.

Nothing in a row identifies a version. rule_label and robot_id are labels. There is no hash of the recipe, no library version, no build identifier and no configuration fingerprint in the table. Two runs sharing a rule label and a robot label but differing in logic produce rows that cannot be told apart; conversely, renaming a rule whose logic never changed presents as two different strategies. The engine does keep a robot descriptor store — a tse_robot table with a label, a schema version and the descriptor — but it does not close this gap: it is a separate file with no key into the journal, it is keyed on the same mutable label, and it is written with INSERT OR REPLACE, so it holds the current descriptor and no history of previous ones. Establishing that the code and configuration behind two rows were the same requires evidence the operator keeps outside the library.

Uniqueness is enforced by discarding, and it is silent. The compound constraint spans ten columns and deliberately excludes both order identifiers and the price, which is what makes journaling idempotent against a replayed or redelivered execution report. The same mechanism means two genuinely distinct fills that agree on all ten columns yield one row rather than two, and the discard produces no diagnostic. Completeness of the journal is therefore exact up to that key, not absolute. For a reviewer the practical consequence is narrow — the key includes both timestamps at nanosecond resolution, so the collision requires two fills of equal size on the same contract, rule, robot and booking mode at the identical nanosecond — but it is a bound, and it is stated rather than left to be discovered.

What leaves the machine

The engine reaches the network on its own behalf for one purpose, the licence check, and the environment chapter enumerates field by field what that exchange carries. It is not repeated here. What belongs here is the negative half of that statement, because it is the half an auditor is asking about.

Nothing of the record described in this chapter travels in that exchange, and nothing of it can: no market data, no contract, no input value, no pattern, no rule, no strategy, no order, no trade and no statistic has a field to travel in. The claim is structural rather than a matter of policy — the message has no member of those types. Everything else the engine sends goes through a connector the operator configured or a processor the operator wrote, to an address the operator chose. The blotter file and the scoring file stay on the machine that wrote them.

A worked example

The rows below are illustrative. They are not a capture from a real run; the values are chosen to be readable, and they are shown to demonstrate how a row is interrogated, not as reference data.

A robot labelled ma_crossover holds a rule labelled entry_long. At 2025-09-19T10:00:00Z its pattern fires on a moving-average crossover, the rule emits a buy, the order passes the risk gate and is sent, and the venue fills it 41 milliseconds later. The journal gains one row.

Read in order, these establish the following. The trade is a buy — txn_side_code is the long code — of 100 units of the contract that contracts resolves to a symbol, at 171.42, charged 0.35 in fees. It opened rather than added: prev_contract_exposure_quantity is zero and prev_contract_exposure_side is the neutral code, so no position in this contract existed the instant before, and accordingly booked_pl is exactly the negated fee, since nothing was closed. It was not an arbitrary purchase: robot_id resolves through strategies to ma_crossover, and rule_label names entry_long directly, so the decision is attributed to a named rule of a named robot. It responded to an event at ts_mkt_event, and the operator's own recording of the feed, joined on that value, supplies the prices the crossover was computed from. It executed 41 milliseconds later, which is the whole latency from market event to fill. The portfolio stood at 248310.00 in market value immediately before, which is the state the risk policies were evaluated against.

What the row does not establish is equally definite. It does not show that entry_long contained a crossover rather than something else — no version or recipe hash is present, so that comes from the operator's retained recipe and processor code. It does not show what the moving averages read at that instant — no input value is persisted, so that comes from the operator's retained feed. And had the risk gate refused this order instead of passing it, there would be no row here at all, only a line in the log.

An exit follows an hour later under the rule exit_long, at 172.10, with txn_side_code set to the short code, prev_contract_exposure_quantity reading 100 and prev_contract_exposure_side reading the long code — the position this row closes, as observed the instant before it was closed. Its booked_pl is booked rather than merely negated fees, because exposure moved toward zero. The two rows share contract_id, differ in rule_label, and are ordered against each other by id regardless of what the venue stamped on them.

Version 5.0.0.1