Fault tolerance: idempotency, persistent state and reconciliation at startup.

Where an order system breaks

Lesson 4.5.2 followed the life of an order with the system running: states, partial fills, reconnections. This one is about what happens when something breaks in the middle. An order management system (OMS) receives a signal, saves what it is going to do, sends the order, receives the acknowledgment and the executions, and updates position and cash. Between every two steps there is a window in which the process can die or the network can drop:

flowchart TB
  S[Signal] --> G[Save intent]
  G --> E[Send order]
  E --> A[Broker ack]
  A --> X[Execution]
  X --> P[Save execution]
  P --> C[Reconcile statement]

The data of the fictional level 4 system (synthetic) come with all those failures planted in the live account U-0418272, from October 13 to December 31, 2025, with their known answer in vivo4_anomalias_verdad.csv. Lesson 4.5.2 already covered the ones visible in the message stream while the system is trading (duplicates, out-of-order messages, gaps and the resent order); this one adds what happens when the one that goes down is the process itself and what only shows up when comparing with the broker statement. At the end, it compares the list of findings with that truth. The header figure gives away the result: the cash the system thinks it has, as counted by the events and after reconciling, against the cash in the statement.

Idempotency: the same order, only once

Lesson 3.7.4 taught the basic case: a timeout does not tell you whether the order arrived, and the solution is to send it with your own identifier (clientOrderId) so that the broker rejects the retry as a duplicate. Lesson 4.5.2 analyzed the mistake of the system in the data: on November 13, during a disconnection, the acknowledgment of R00113 never arrived and the OMS resent it as R00114, with another key, without asking the broker first. The summary, for what follows:

import numpy as np
import pandas as pd

ev = pd.read_csv("vivo4_eventos_broker.csv")
real = ev[ev["modo"] == "real"].reset_index(drop=True)
ords = pd.read_csv("vivo4_ordenes.csv").set_index("id_orden_cliente")
sen = pd.read_csv("vivo4_senales.csv").set_index("id_senal")

par = ords.loc[["R00113", "R00114"]]
print(par[["id_senal", "ts_envio", "lado", "cantidad", "motivo",
           "orden_origen"]].to_string())
fills = real[real["id_orden_cliente"].isin(par.index)
             & real["id_ejecucion"].notna()]
print(fills[["seq", "id_orden_cliente", "cantidad_ultima",
             "precio_ultimo"]].to_string(index=False))
s = sen.loc["S00215"]
print("signal S00215:", s["posicion_actual"], "->",
      s["posicion_objetivo"], s["instrumento"])

                 id_senal                     ts_envio   lado  cantidad                 motivo orden_origen
id_orden_cliente
R00113             S00215  2025-11-13T20:50:00.246596Z  venta         7             rebalanceo          NaN
R00114             S00215  2025-11-13T20:51:14.296596Z  venta         7  reenvio_sin_respuesta       R00113
 seq id_orden_cliente  cantidad_ultima  precio_ultimo
 309           R00114                7         717.07
 298           R00113                7         716.82
signal S00215: 147 -> 140 ORUS

The signal asked to sell 7 ORUS (from 147 to 140). Both orders were executed: 14 sold. The broker did nothing wrong; it received two orders with different keys. Asking before resending (the solution in 4.5.2) requires knowing what to ask, and that is where the way the key is generated comes in. Two approaches, with a simulated broker that loses the response to the first order and an OMS that, not receiving it, dies and restarts before resending the pending signal:

import uuid

class BrokerSim:
    """Accepts orders; rejects a repeated key; can lose the
    response to the next order (the order does go through)."""

    def __init__(self):
        self.ordenes, self.perder = {}, 0

    def enviar(self, clave, instrumento, cantidad):
        if clave in self.ordenes:
            return {"estado": "duplicate", "clave": clave}
        self.ordenes[clave] = (instrumento, cantidad)
        if self.perder:
            self.perder -= 1
            raise TimeoutError("no response")
        return {"estado": "accepted", "clave": clave}

    def consultar(self, clave):
        return self.ordenes.get(clave)

def clave_aleatoria(senal, cuenta):
    return uuid.uuid4().hex                 # new on every call

def clave_de_senal(senal, cuenta):
    return f"{cuenta}-{senal}"              # comes from the intent

def ejecutar(generar, reinicios=1):
    """Sends S00215; the response is lost and the process
    restarts `reinicios` times before retrying."""
    b = BrokerSim()
    b.perder = 1
    for _ in range(reinicios + 1):          # each pass: one process
        clave = generar("S00215", "U-0418272")
        try:
            r = b.enviar(clave, "ORUS", -7)
        except TimeoutError:
            continue                        # the process “dies”
        if r["estado"] == "duplicate":
            r = {"estado": "already existed", **r}
        break
    return len(b.ordenes), r["estado"]

for nombre, g in (("random uuid", clave_aleatoria),
                  ("signal key", clave_de_senal)):
    print(f"{nombre:18} orders at the broker: %d · %s" % ejecutar(g))

random uuid        orders at the broker: 2 · accepted
signal key         orders at the broker: 1 · duplicate

With a random UUID, the new process generates another key and the broker accepts a second order: it is exactly what happened with R00113 and R00114. With a key that comes from the intent (account and signal; if a signal can produce several orders, also an order number within the signal), the retry carries the same key even if another process makes it, the broker recognizes it and the system only has to query the state of the order that already exists. The equivalent alternative is to generate the random key and save it to disk before sending: the new process reads it instead of making up another one.

Two precautions with real brokers. Not all of them reject a repeated key the same way, and not all of them accept one (Alpaca, for example, describes it as a unique identifier for the order of up to 128 characters11Note 1 · Alpaca documentation, POST /v2/orders: client_order_id, “A unique identifier for the order. Automatically generated if not sent. (<= 128 characters)”. The page does not document the response to a repeated key. Accessed October 22, 2026.; in ccxt it depends on the exchange): you have to check in the test environment what the broker responds to a repeated key before relying on it. And if the broker does not support it, the only defense is to query the day's open orders and executions before resending (lesson 3.7.4).

Persistent state: one event, one transaction

The OMS stores its state in SQLite, which ships with Python: a single file, ACID transactions and nothing to install. Four tables are enough for what matters here: the events received (key: seq, the broker's sequence number), the executions (key: id_ejecucion), the positions and the cash. The design rule is that each broker event is applied in a single transaction: either the event, the execution, the position and the cash are all saved, or nothing is.

import sqlite3

ESQUEMA = """
CREATE TABLE IF NOT EXISTS eventos (
  seq INTEGER PRIMARY KEY, id_orden TEXT, evento TEXT,
  id_ejecucion TEXT);
CREATE TABLE IF NOT EXISTS ejecuciones (
  id_ejecucion TEXT PRIMARY KEY, id_orden TEXT, instrumento TEXT,
  cantidad INTEGER, precio REAL, comision REAL, seq INTEGER);
CREATE TABLE IF NOT EXISTS posiciones (
  instrumento TEXT PRIMARY KEY, cantidad INTEGER NOT NULL);
CREATE TABLE IF NOT EXISTS efectivo (
  id INTEGER PRIMARY KEY CHECK (id = 1), saldo REAL NOT NULL);
"""
SIGNO = ords["lado"].map({"compra": 1, "venta": -1})
INSTR = ords["instrumento"]

def abrir(ruta):
    con = sqlite3.connect(ruta, isolation_level=None)  # manual BEGIN
    con.execute("PRAGMA journal_mode=WAL")
    con.execute("PRAGMA synchronous=FULL")
    con.executescript(ESQUEMA)
    con.execute("INSERT OR IGNORE INTO efectivo VALUES (1, 1e6)")
    return con

class Caida(BaseException):
    """The process dies: nobody does a ROLLBACK or closes anything."""

def aplicar(con, e, morir=False):
    con.execute("BEGIN IMMEDIATE")
    nuevo = con.execute(
        "INSERT OR IGNORE INTO eventos VALUES (?, ?, ?, ?)",
        (int(e.seq), e.id_orden_cliente, e.evento,
         e.id_ejecucion if isinstance(e.id_ejecucion, str) else None)
    ).rowcount
    if nuevo and isinstance(e.id_ejecucion, str):
        q = int(SIGNO[e.id_orden_cliente] * e.cantidad_ultima)
        ins = INSTR[e.id_orden_cliente]
        con.execute("INSERT INTO ejecuciones VALUES (?,?,?,?,?,?,?)",
                    (e.id_ejecucion, e.id_orden_cliente, ins, q,
                     e.precio_ultimo, e.comision, int(e.seq)))
        con.execute("""INSERT INTO posiciones VALUES (?, ?)
            ON CONFLICT(instrumento)
            DO UPDATE SET cantidad = cantidad + excluded.cantidad""",
            (ins, q))
        if morir:
            raise Caida(f"dies at seq {e.seq}, before the COMMIT")
        con.execute("UPDATE efectivo SET saldo = saldo - ? - ?",
                    (q * e.precio_ultimo, e.comision))
    con.execute("COMMIT")
    return nuevo

def estado(con):
    pos = dict(con.execute("SELECT instrumento, cantidad FROM "
                           "posiciones WHERE cantidad != 0"))
    saldo = con.execute("SELECT saldo FROM efectivo").fetchone()[0]
    n = con.execute("SELECT COUNT(*) FROM ejecuciones").fetchone()[0]
    return pos, round(saldo, 2), n

Three details. isolation_level=None disables the implicit transactions of the sqlite3 module so that we open and close them by hand (BEGIN IMMEDIATE takes the write lock at the start). INSERT OR IGNORE on the primary key makes a repeated event do nothing: rowcount is 0 and the position is not touched. And WAL mode with synchronous=FULL ensures that a completed COMMIT is on disk even if the power goes out afterward; SQLite guarantees that a half-done transaction is rolled back when the file is opened again22Note 2 · SQLite, “Atomic Commit In SQLite” (https://www.sqlite.org/atomiccommit.html) and “Write-Ahead Logging” (https://www.sqlite.org/wal.html): a committed transaction survives a crash of the process or the system, and an uncommitted one is rolled back when the database is opened again.. The Caida exception inherits from BaseException so that no except Exception catches it: it represents the death of the process, not an error the code handles.

First, the reference: all the events of the live account, in the order they arrived, with no crashes.

from pathlib import Path

def limpiar(ruta):
    for sufijo in ("", "-wal", "-shm"):
        Path(ruta + sufijo).unlink(missing_ok=True)

limpiar("oms.db")
con = abrir("oms.db")
aplicados = sum(aplicar(con, e) for e in real.itertuples())
ref_pos, ref_saldo, ref_n = estado(con)
con.close()
print(len(real), "messages;", aplicados, "applied;",
      ref_n, "executions")
print("cash:", ref_saldo)
print(dict(sorted(ref_pos.items())))

649 messages; 645 applied; 348 executions
cash: 981849.26
{'ORAU': 488, 'ORIG': -557, 'ORTN': 112}

Six hundred forty-nine messages, 645 applied and 348 executions: the remaining four are exact repeats of messages already received, with the same seq and the same execution, which the primary key discards. They are the executions the broker resent when reconnecting on October 21 and December 9. Counting them twice would have added shares and costs that do not exist.

The process dies in the middle of an event

Now the crash. A naive OMS updates the position and the cash in one transaction and records the event as processed in another; the transactional one does it all in one. In both cases the process dies at event number 292 in the list (the sale of 923 ORIG of R00109, on November 6), the file is reopened as a new process would, and the broker resends the messages from the last one the system had not confirmed:

def aplicar_ingenuo(con, e, morir=False):
    """Position and cash in one transaction; the event mark,
    in another. No unique keys on the executions."""
    hecho = con.execute("SELECT 1 FROM eventos WHERE seq = ?",
                        (int(e.seq),)).fetchone()
    if hecho:
        return 0
    if isinstance(e.id_ejecucion, str):
        q = int(SIGNO[e.id_orden_cliente] * e.cantidad_ultima)
        con.execute("BEGIN")
        con.execute("""INSERT INTO posiciones VALUES (?, ?)
            ON CONFLICT(instrumento)
            DO UPDATE SET cantidad = cantidad + excluded.cantidad""",
            (INSTR[e.id_orden_cliente], q))
        con.execute("UPDATE efectivo SET saldo = saldo - ? - ?",
                    (q * e.precio_ultimo, e.comision))
        con.execute("COMMIT")
        if morir:
            raise Caida(f"dies at seq {e.seq}, between the COMMITs")
    con.execute("INSERT INTO eventos VALUES (?, ?, ?, ?)",
                (int(e.seq), e.id_orden_cliente, e.evento, None))
    return 1

K = 292                               # the event where it dies
print(real.loc[K, ["seq", "id_orden_cliente", "evento",
                   "cantidad_ultima"]].to_dict(),
      INSTR[real.loc[K, "id_orden_cliente"]])

for nombre, fn in (("naive", aplicar_ingenuo),
                   ("transactional", aplicar)):
    limpiar("oms.db")
    con = abrir("oms.db")
    try:
        for i, e in enumerate(real.itertuples()):
            fn(con, e, morir=(i == K))
    except Caida as c:
        print(f"{nombre}: {c}")
    con.close()                       # the OS closes the file
    con = abrir("oms.db")             # a new process
    for e in real.iloc[K:].itertuples():   # the broker resends
        fn(con, e)
    pos, saldo, _ = estado(con)
    con.close()
    dif = {k: pos.get(k, 0) - ref_pos.get(k, 0)
           for k in set(pos) | set(ref_pos)
           if pos.get(k, 0) != ref_pos.get(k, 0)}
    print(f"  difference from the reference: positions {dif}, "
          f"cash {saldo - ref_saldo:+,.2f}")

{'seq': 292, 'id_orden_cliente': 'R00109', 'evento': 'ejecutada', 'cantidad_ultima': 923} ORIG
naive: dies at seq 292, between the COMMITs
  difference from the reference: positions {'ORIG': -923}, cash +105,197.23
transactional: dies at seq 292, before the COMMIT
  difference from the reference: positions {}, cash +0.00

The naive one had committed the execution but not the processed-event mark; on return, the resent event looks new and the execution is counted again: the system believes it sold 923 more ORIG than it did and holds 105,197.23 USD of cash that do not exist. The transactional one dies with the execution and the position written but not committed: SQLite discards them, the resent event is applied in full once, and the state matches the reference to the cent. A variant of the same problem is dying after the COMMIT but before telling the broker that the message was processed: the broker resends it, and INSERT OR IGNORE discards it. That is the general idea: the network delivers “at least once”, and idempotent processing turns that into an “exactly once” effect.

What comes from the broker: duplicates, out-of-order messages and gaps

Lesson 4.5.2 built a sequencer that sorts messages by seq, discards repeats and detects gaps while the system is trading. For the reconciliation, it is enough to have the list of what it found, in one cell: the duplicates (which the primary key has already discarded), the executions that arrive after a later one of the same order (the cumulative quantity goes backwards, which is what breaks a state machine) and the seq gaps.

dup = real[real.duplicated(["seq", "evento", "id_ejecucion"])]
unicos = real.drop(dup.index)
fills = unicos[unicos["evento"].isin(["parcial", "ejecutada"])]
previo = (fills.groupby("id_orden_cliente")["cantidad_acumulada"]
          .transform(lambda s: s.cummax().shift()))
desorden = fills[fills["cantidad_acumulada"] < previo]
huecos = sorted(set(range(1, real["seq"].max() + 1))
                - set(real["seq"]))
tarde = (unicos["seq"] < unicos["seq"].cummax().shift()).sum()

print("duplicates:", dup[["seq", "id_ejecucion"]]
      .to_records(index=False).tolist())
print("arrive after a higher seq:", int(tarde))
print("execution that goes backwards:",
      desorden[["seq", "id_orden_cliente", "id_ejecucion",
                "cantidad_acumulada"]].to_dict("records"))
print("gaps in seq:", huecos)

duplicates: [(61, 'E710000210.01'), (62, 'E710000168.01'), (471, 'E710001365.01'), (475, 'E710001386.01')]
arrive after a higher seq: 47
execution that goes backwards: [{'seq': 393, 'id_orden_cliente': 'R00162', 'id_ejecucion': 'E710001134.01', 'cantidad_acumulada': 857}]
gaps in seq: [202, 519]

Four duplicates, 47 messages that arrive after another one with a higher number (almost all of them acknowledgments overtaken by their execution, or the batch the broker resends after a reconnection) and only one that matters: execution E710001134.01 of R00162, with 857 cumulative shares, arrives when another one of the same order with 1,509 had already arrived. An OMS that processes in order of arrival would put the order at 857 after having marked it as filled; one that sorts by seq before applying the state machine would not. And two gaps, 202 and 519: two messages that never arrived and that the broker did not resend. Lesson 4.5.2 rebuilt their quantity and price from the cumulative figure of the next message; the statement confirms or corrects it.

Reconciliation with the statement: executions

The broker statement (vivo4_extracto_*) is the accounting truth of the account: what the broker settles, charges and holds in custody. The reconciliation compares four things, from the most specific to the most aggregate: executions, positions, cash and, finally, orders against signals.

ex = pd.read_csv("vivo4_extracto_operaciones.csv")
ej = (unicos[unicos["id_ejecucion"].notna()]
      [["id_ejecucion", "id_orden_cliente", "cantidad_ultima",
        "precio_ultimo", "comision", "seq"]])
m = ex.merge(ej, on="id_ejecucion", how="outer", indicator=True,
             suffixes=("_ext", "_ev"))
print(m["_merge"].value_counts().to_dict())
solo_ext = m[m["_merge"] == "left_only"]
cliente_de = (real.dropna(subset=["id_orden_broker"])
              .drop_duplicates("id_orden_broker")
              .set_index("id_orden_broker")["id_orden_cliente"])
print(solo_ext[["fecha_operacion", "id_ejecucion", "instrumento",
                "lado", "cantidad", "precio"]].to_string(index=False))
ambos = m[m["_merge"] == "both"]
otro_px = ambos[(ambos["precio"] - ambos["precio_ultimo"]).abs() > 1e-9]
otro_q = ambos[ambos["cantidad"] != ambos["cantidad_ultima"]]
otra_com = ambos[(ambos["comision_ext"] - ambos["comision_ev"]).abs()
                 > 1e-9]
print("different price:", otro_px[["fecha_operacion", "id_ejecucion",
      "cantidad", "precio", "precio_ultimo"]].to_dict("records"))
print("different quantity:", len(otro_q),
      "| different commission:", len(otra_com))

{'both': 348, 'left_only': 2, 'right_only': 0}
fecha_operacion  id_ejecucion instrumento   lado  cantidad  precio
     2025-11-03 E710000553.01        ORTN  venta       112   99.06
     2025-12-15 E710001498.01        ORIG compra       557  114.03
different price: [{'fecha_operacion': '2025-12-03', 'id_ejecucion': 'E710001162.01', 'cantidad': 170, 'precio': 115.34, 'precio_ultimo': 115.29}]
different quantity: 0 | different commission: 0

Three hundred forty-eight executions match by identifier. Two are in the statement and not in the events: they are exactly the two gaps, a partial sale of 112 ORTN on November 3 and a partial purchase of 557 ORIG on December 15, which the broker did not resend when reconnecting. None is in the events and missing from the statement. And one matches by identifier but not by price: the broker settled E710001162.01 at 115.34 instead of the 115.29 in the execution message, a later correction of 8.50 USD on 170 shares. Quantities and commissions match in all of them.

Positions: what the broker did without telling you

With the statement's executions incorporated (the missing ones and the corrected price), the system's positions should be the statement's every day. Positions are rebuilt by summing executions by date and compared with vivo4_extracto_posiciones.csv, which gives each day's closing position:

ej_f = ej.assign(
    fecha=ords.loc[ej["id_orden_cliente"], "fecha"].to_numpy(),
    instrumento=INSTR[ej["id_orden_cliente"]].to_numpy(),
    q=(SIGNO[ej["id_orden_cliente"]].to_numpy()
       * ej["cantidad_ultima"].to_numpy()))
falta = solo_ext.assign(
    fecha=solo_ext["fecha_operacion"],
    q=np.where(solo_ext["lado"] == "compra", 1, -1)
      * solo_ext["cantidad"])
todas = pd.concat([ej_f[["fecha", "instrumento", "q"]],
                   falta[["fecha", "instrumento", "q"]]])
pos_ext = (pd.read_csv("vivo4_extracto_posiciones.csv")
           .pivot(index="fecha", columns="instrumento",
                  values="cantidad").fillna(0))
pos_sis = (todas.pivot_table(index="fecha", columns="instrumento",
                             values="q", aggfunc="sum")
           .reindex(pos_ext.index).fillna(0).cumsum())
dif = (pos_ext - pos_sis.reindex(columns=pos_ext.columns,
                                 fill_value=0))
dif = dif.loc[:, (dif != 0).any()]
print(dif[(dif != 0).any(axis=1)].head(3))
d0 = dif.index[(dif != 0).any(axis=1)][0]
sis = pos_ext.loc[d0, "ORAU"] - dif.loc[d0, "ORAU"]
x = pos_ext.loc[d0, "ORAU"] / sis
px = pd.read_csv("vivo4_extracto_posiciones.csv").query(
    "instrumento == 'ORAU'").set_index("fecha")["precio_cierre"]
print(f"{d0}: ratio {x:.4f}; close {px.shift().loc[d0]} -> "
      f"{px.loc[d0]} (ratio {px.shift().loc[d0] / px.loc[d0]:.3f})")
efe = pd.read_csv("vivo4_extracto_efectivo.csv")
print(efe[efe["concepto"] == "desdoblamiento"].to_string(index=False))

instrumento   ORAU
fecha
2025-11-17   374.0
2025-11-18   374.0
2025-11-19   374.0
2025-11-17: ratio 3.0000; close 369.01 -> 124.15 (ratio 2.972)
     fecha       concepto instrumento  importe   saldo
2025-11-17 desdoblamiento        ORAU      0.0 6297.88

All positions match every day except one, ORAU, from November 17: the statement has 374 more shares, exactly twice the 187 that were there. The ratio is 3.0000, the closing price goes from 369.01 to 124.15 (a ratio of 2.97 that combines the split and the day's move) and the cash statement has a desdoblamiento (split) line with no amount. It is a 3-for-1 stock split the system did not know about. The difference persists over the following days with the same size: the later ORAU orders were computed with the wrong position.

The broker does not send a split as an execution, and a system that does not incorporate it miscomputes everything that depends on the position: the real weight of ORAU was three times what it believed, and the next order to bring it to target bought more than it should have. Data providers and brokers publish corporate actions in advance (lesson 1.4.3); the system must read them before the open and adjust position and reference prices.

Cash: interest, fees and corrections

The system's cash is the initial balance plus the amounts of the executions. Compared with the statement balance at the end of each day, with the executions corrected, the remaining differences are movements that are not trades:

imp = ex.groupby("fecha_operacion")["importe_neto"].sum()
saldo_ext = efe.groupby("fecha")["saldo"].last()
saldo_ops = 1e6 + imp.reindex(saldo_ext.index, fill_value=0).cumsum()
resto = (saldo_ext - saldo_ops).round(2)
saltos = resto.diff().fillna(resto).round(2)
print(saltos[saltos != 0].to_dict())
otros = efe[~efe["concepto"].isin(["ingreso", "operaciones",
                                    "comisiones"])]
print(otros.groupby("fecha")["importe"].sum().round(2).to_dict())

# the same with the executions from the EVENTS, uncorrected
ev_imp = (ej_f.assign(imp=-ej_f["q"] * ej_f["precio_ultimo"]
                      - ej_f["comision"])
          .groupby("fecha")["imp"].sum())
ingenuo = 1e6 + ev_imp.reindex(saldo_ext.index, fill_value=0).cumsum()
d_ing = (saldo_ext - ingenuo).round(2)
print("uncorrected difference, at the end:", d_ing.iloc[-1],
      "| corrected:", resto.iloc[-1])

{'2025-10-31': 164.77, '2025-11-28': 42.07, '2025-12-31': 276.76}
{'2025-10-31': 164.77, '2025-11-17': 0.0, '2025-11-28': 42.07, '2025-12-31': 276.76}
uncorrected difference, at the end: -51947.56 | corrected: 483.6

With the statement's executions, the cash only departs from the system on three days, the last three business days of each month: +164.77, +42.07 and +276.76 USD. They match the sum of the statement's intereses (interest) and tasa_datos_mercado (market data fee) lines (174.77 − 10, 52.07 − 10, 286.76 − 10): interest on cash and a market data fee that the broker credits and charges without sending any event. With the events' executions uncorrected, the final difference is −51,947.56 USD: the ORIG purchase the system did not see (−63,516.66 with its commission), the ORTN sale it did not see either (+11,094.00 net), the price correction on 170 ORRE (−8.50) and the interest and fees (+483.60). The header figure follows the two curves day by day: the one from the raw events jumps at each failure; the reconciled one only moves on the last day of each month, by amounts the statement explains.

Orders versus signals

What is left is not at the broker: it is the duplicate order from the beginning. Events and statement agree on it because both orders existed. It is only visible when comparing, for each signal, what was executed with what it asked for:

o = ords[ords["modo"] == "real"].copy()
lleno = ej_f.groupby("id_orden_cliente")["q"].sum()
# the two executions from the statement, to their order (by broker id)
for _, f in solo_ext.iterrows():
    cli = cliente_de[f["id_orden_broker"]]
    lleno[cli] = lleno.get(cli, 0) + (1 if f["lado"] == "compra"
                                      else -1) * f["cantidad"]
o["ejecutado"] = lleno.reindex(o.index).fillna(0)
por_senal = o.groupby("id_senal")["ejecutado"].sum()
pide = (sen["posicion_objetivo"] - sen["posicion_actual"])
desv = (por_senal - pide.reindex(por_senal.index)).astype(int)
for s, d in desv[desv != 0].items():
    os_ = o[o["id_senal"] == s]
    print(s, sen.loc[s, "instrumento"], f"{d:+d}",
          os_.index.tolist(), os_["motivo"].tolist())
rech = real[real["evento"] == "rechazada"]
print(rech[["id_orden_cliente", "motivo"]].to_string(index=False))

S00125 ORIG -1260 ['R00007'] ['rebalanceo']
S00215 ORUS -7 ['R00113', 'R00114'] ['rebalanceo', 'reenvio_sin_respuesta']
id_orden_cliente                       motivo
          R00007 poder de compra insuficiente

Two signals did not end up as requested. S00125 executed nothing because its order was rejected for insufficient buying power: a real mismatch, but already explained and reported by the broker (it is one of the alerts in lesson 4.5.4). S00215 has 7 ORUS sold too many, with two orders, the second one flagged as reenvio_sin_respuesta (resent without response): the duplicate order. Nothing at the broker was wrong; the error was in the OMS.

All thirteen, one by one

Putting all the checks together, the reconciliation produces a list of findings with their type, date and identifiers. The known answer in the data lets us check whether the list is complete and whether anything is extra:

fecha_seq = dict(zip(real["seq"], real["ts_broker"].str[:10]))
h = []
for _, r in dup.iterrows():
    h.append(("ejecucion_duplicada", fecha_seq[r["seq"]],
              r["id_orden_cliente"], r["id_ejecucion"]))
for _, r in desorden.iterrows():
    h.append(("fuera_de_orden", fecha_seq[r["seq"]],
              r["id_orden_cliente"], r["id_ejecucion"]))
for _, r in solo_ext.iterrows():
    cli = cliente_de[r["id_orden_broker"]]
    h.append(("falta_ejecucion", r["fecha_operacion"], cli,
              r["id_ejecucion"]))
for _, r in otro_px.iterrows():
    h.append(("correccion_precio", r["fecha_operacion"],
              r["id_orden_cliente"], r["id_ejecucion"]))
h.append(("desdoblamiento", d0, None, None))
split_f = efe.loc[efe["concepto"] == "desdoblamiento", "fecha"].values
for f in otros["fecha"].unique():
    if f not in split_f:
        h.append(("tasas_e_intereses", f, None, None))
for s, d in desv[desv != 0].items():
    os_ = o[o["id_senal"] == s]
    if (os_["motivo"] == "reenvio_sin_respuesta").any():
        h.append(("orden_duplicada", os_["fecha"].iloc[0],
                  os_.index[-1], None))

hall = pd.DataFrame(h, columns=["tipo", "fecha", "id_orden_cliente",
                                "id_ejecucion"])
verdad = pd.read_csv("vivo4_anomalias_verdad.csv")
claves = ["tipo", "fecha", "id_orden_cliente", "id_ejecucion"]
cruce = verdad.merge(hall, on=claves, how="outer", indicator=True)
print(cruce["_merge"].value_counts().to_dict())
print(cruce.sort_values("fecha")[["id_anomalia", "tipo", "fecha",
      "id_orden_cliente"]].to_string(index=False))

{'both': 13, 'left_only': 0, 'right_only': 0}
id_anomalia                tipo      fecha id_orden_cliente
        A02 ejecucion_duplicada 2025-10-21           R00024
        A01 ejecucion_duplicada 2025-10-21           R00030
        A03   tasas_e_intereses 2025-10-31              NaN
        A04     falta_ejecucion 2025-11-03           R00079
        A05     orden_duplicada 2025-11-13           R00114
        A06      desdoblamiento 2025-11-17              NaN
        A07   tasas_e_intereses 2025-11-28              NaN
        A08   correccion_precio 2025-12-03           R00166
        A09      fuera_de_orden 2025-12-03           R00162
        A10 ejecucion_duplicada 2025-12-09           R00195
        A11 ejecucion_duplicada 2025-12-09           R00198
        A12     falta_ejecucion 2025-12-15           R00214
        A13   tasas_e_intereses 2025-12-31              NaN

Thirteen findings and thirteen planted anomalies, and all thirteen match by type, date, order and execution: none is missing and none is extra. The figure sorts them by the check that uncovers them: five (the price correction, the split and the three month-ends with interest and fees) leave no trace in the message stream and only show up against the statement, and the duplicate order only shows up against the signals. The rejection of S00125 does not appear because the duplicate-order rule requires a resend; a rejection is reported by the broker and goes another way (the alert). That the list matches the truth depends on the data being built for it; in a real account, the reconciliation gives a list and someone has to decide what each item is. What can be automated is the classification of known signatures, which is what the cell does.

What to do with each difference

Finding the difference is half the job. The other half is deciding, for each type, whether the system fixes it on its own, fixes it and alerts, or stops:

The rule in the last row is the most important: if on startup there is a difference left that fits no known signature, the system does not trade. Trading on a false state propagates the error (what happened with ORAU for two weeks). The startup of a live system is, therefore, a small state machine:

stateDiagram-v2
  [*] --> LOADING
  LOADING --> RECONCILING
  RECONCILING --> TRADING
  RECONCILING --> CORRECTING
  CORRECTING --> RECONCILING
  RECONCILING --> BLOCKED
  BLOCKED --> RECONCILING
  TRADING --> RECONCILING

LOADING reads the state from disk; RECONCILING asks the broker for open orders, executions and positions (and, if there is a new statement, compares it); with everything explained it moves to TRADING; with differences of a known signature, to CORRECTING, which applies the correction and reconciles again; with something unexplained, to BLOCKED, which sends a critical alert (lesson 4.5.4) and waits for a person. From TRADING it reconciles again after every reconnection and every morning. It is the same principle as the kill switch in lesson 4.2.5, applied to state instead of to results. NautilusTrader performs this step when starting its live node, as lesson 4.5.6 will show.

Exercises

1. In aplicar, replace the execution key with an INSERT without OR IGNORE and process all the events of the live account again with no crashes. What happens to the duplicates? Why is it preferable for it to fail rather than count them twice?

def aplicar_estricto(con, e):
    con.execute("BEGIN IMMEDIATE")
    try:
        con.execute("INSERT INTO eventos VALUES (?, ?, ?, ?)",
                    (int(e.seq), e.id_orden_cliente, e.evento, None))
        con.execute("COMMIT")
        return "ok"
    except sqlite3.IntegrityError as err:
        con.execute("ROLLBACK")
        return f"seq {e.seq}: {err}"

limpiar("estricto.db")
con = abrir("estricto.db")
r = [aplicar_estricto(con, e) for e in real.itertuples()]
con.close()
print([x for x in r if x != "ok"])

['seq 61: UNIQUE constraint failed: eventos.seq', 'seq 62: UNIQUE constraint failed: eventos.seq', 'seq 471: UNIQUE constraint failed: eventos.seq', 'seq 475: UNIQUE constraint failed: eventos.seq']

2. Write a function that, given the table of daily positions from the statement and the system's, detects stock splits without looking at the cash statement: a difference that appears on a day with no executions in the instrument and whose ratio to the system's position is an integer or the inverse of an integer.

def desdoblamientos(pos_ext, pos_sis, ejecs):
    out = []
    for ins in pos_ext.columns:
        d = pos_ext[ins] - pos_sis.get(ins, 0)
        nuevos = d[(d != 0) & (d.shift(fill_value=0) == 0)]
        for fecha in nuevos.index:
            hubo = ((ejecs["instrumento"] == ins)
                    & (ejecs["fecha"] == fecha)).any()
            antes = pos_sis.loc[fecha, ins]
            if hubo or antes == 0:
                continue
            r = pos_ext.loc[fecha, ins] / antes
            for k in (r, 1 / r):
                if abs(k - round(k)) < 1e-9 and round(k) > 1:
                    out.append((ins, fecha, f"{r:.4g}"))
    return out

desdoblamientos(pos_ext, pos_sis, todas)

[('ORAU', '2025-11-17', '3')]

3. A colleague proposes not reconciling with the statement and relying on the events, because “the broker always sends everything”. With the data in this lesson, what would you tell them?