SQLite Schema Reference

This reference is generated by applying each database owner's schema initializer to a temporary SQLite database. Column tables and SQL below come from SQLite's table_info and sqlite_schema; DDL is not copied into this document by hand. Run make dbdocs-check to detect drift.

SQLite affinity types are shown as declared. The full DDL contains constraints, keys, defaults, indexes, and views where defined.

stats.db

Owner: pm-stats
Purpose: Market, order, and index statistics; raw event retention is configured by pm-stats.
Schema source: src/edumatcher/stats/main.py

Fields

Object	Field	Declared type	Not null	Default	Primary key
table: daily_stats	date	TEXT	yes		1
table: daily_stats	symbol	TEXT	yes		2
table: daily_stats	open_price	INTEGER	no
table: daily_stats	high_price	INTEGER	no
table: daily_stats	low_price	INTEGER	no
table: daily_stats	close_price	INTEGER	no
table: daily_stats	open_bid	INTEGER	no
table: daily_stats	open_ask	INTEGER	no
table: daily_stats	close_bid	INTEGER	no
table: daily_stats	close_ask	INTEGER	no
table: daily_stats	volume	INTEGER	yes	0
table: daily_stats	trade_count	INTEGER	yes	0
table: daily_stats	turnover	INTEGER	yes	0
table: daily_stats	vwap	REAL	no
table: daily_stats	largest_trade_qty	INTEGER	no
table: daily_stats	largest_trade_price	INTEGER	no
table: daily_stats	tick_decimals	INTEGER	yes	2
table: feed_gaps	seq	INTEGER	no		1
table: feed_gaps	ts	TEXT	yes
table: feed_gaps	stream	TEXT	yes
table: feed_gaps	expected_id	INTEGER	yes
table: feed_gaps	received_id	INTEGER	yes
table: feed_gaps	missing_count	INTEGER	yes
table: index_daily_stats	date	TEXT	yes		1
table: index_daily_stats	index_id	TEXT	yes		2
table: index_daily_stats	open_level	REAL	no
table: index_daily_stats	high_level	REAL	no
table: index_daily_stats	low_level	REAL	no
table: index_daily_stats	close_level	REAL	no
table: index_daily_stats	close_session_state	TEXT	no
table: index_daily_stats	open_aggregate_cap	REAL	no
table: index_daily_stats	close_aggregate_cap	REAL	no
table: index_daily_stats	update_count	INTEGER	yes	0
table: index_level_snapshots	seq	INTEGER	no		1
table: index_level_snapshots	ts	TEXT	yes
table: index_level_snapshots	index_id	TEXT	yes
table: index_level_snapshots	level	REAL	yes
table: index_level_snapshots	aggregate_cap	REAL	no
table: index_level_snapshots	divisor	REAL	no
table: index_level_snapshots	session_state	TEXT	no
table: index_level_snapshots	day_open	REAL	no
table: index_level_snapshots	day_high	REAL	no
table: index_level_snapshots	day_low	REAL	no
table: instruments	symbol	TEXT	yes		1
table: instruments	tick_decimals	INTEGER	yes
table: instruments	tick_size	REAL	yes
table: instruments	currency	TEXT	no
table: instruments	source	TEXT	yes
table: instruments	updated_ts	TEXT	yes
table: order_events	seq	INTEGER	no		1
table: order_events	ts	TEXT	yes
table: order_events	event_type	TEXT	yes
table: order_events	order_id	TEXT	yes
table: order_events	gateway_id	TEXT	yes
table: order_events	symbol	TEXT	yes
table: order_events	side	TEXT	no
table: order_events	order_type	TEXT	no
table: order_events	tif	TEXT	no
table: order_events	price	REAL	no
table: order_events	quantity	INTEGER	no
table: order_events	remaining_qty	INTEGER	no
table: order_events	status	TEXT	no
table: order_events	fill_price	REAL	no
table: order_events	fill_qty	INTEGER	no
table: order_events	trade_ids	TEXT	no
table: order_events	reason	TEXT	no
table: order_events	reject_code	TEXT	no
table: order_events	client_tag	TEXT	no
table: order_events	combo_parent_id	TEXT	no
table: order_events	oco_group_id	TEXT	no
table: order_events	priority_reset	INTEGER	no
table: order_events	msg_id	TEXT	no
table: price_snapshots	ts	TEXT	yes		1
table: price_snapshots	symbol	TEXT	yes		2
table: price_snapshots	mid_price	REAL	no
table: price_snapshots	best_bid	INTEGER	no
table: price_snapshots	best_ask	INTEGER	no
table: price_snapshots	pct_change	REAL	no
table: price_snapshots	tick_decimals	INTEGER	yes	2
table: stats_meta	key	TEXT	yes		1
table: stats_meta	value	TEXT	no
table: trade_log	ts	TEXT	yes
table: trade_log	trade_id	TEXT	yes		1
table: trade_log	symbol	TEXT	yes
table: trade_log	price	INTEGER	yes
table: trade_log	quantity	INTEGER	yes
table: trade_log	tick_decimals	INTEGER	yes	2
table: trade_log	buy_gateway_id	TEXT	no
table: trade_log	sell_gateway_id	TEXT	no
table: trade_log	aggressor_side	TEXT	no
table: trade_log	buy_order_id	TEXT	no
table: trade_log	sell_order_id	TEXT	no
table: trade_log	match_type	TEXT	no
table: trade_log	msg_id	TEXT	no

DDL

table daily_stats

CREATE TABLE daily_stats (
    date                TEXT NOT NULL,
    symbol              TEXT NOT NULL,
    open_price          INTEGER,
    high_price          INTEGER,
    low_price           INTEGER,
    close_price         INTEGER,
    open_bid            INTEGER,
    open_ask            INTEGER,
    close_bid           INTEGER,
    close_ask           INTEGER,
    volume              INTEGER NOT NULL DEFAULT 0,
    trade_count         INTEGER NOT NULL DEFAULT 0,
    -- Traded notional in ticks x quantity: sum(price_ticks * quantity). Exact,
    -- and the VWAP numerator — so vwap is exactly reproducible as
    -- turnover / volume without any float round-trip.
    turnover            INTEGER NOT NULL DEFAULT 0,
    -- Derived, therefore REAL: turnover / volume is a ratio and lands between
    -- ticks far more often than on one. The exact inputs are stored alongside,
    -- so a consumer needing full precision computes it rather than reading it.
    vwap                REAL,
    largest_trade_qty   INTEGER,
    largest_trade_price INTEGER,
    tick_decimals       INTEGER NOT NULL DEFAULT 2,
    PRIMARY KEY (date, symbol)
)

table feed_gaps

CREATE TABLE feed_gaps (
    seq             INTEGER PRIMARY KEY AUTOINCREMENT,
    ts              TEXT    NOT NULL,
    stream          TEXT    NOT NULL,
    -- The id we expected next, the one that actually arrived, and how many
    -- are unaccounted for between them.
    expected_id     INTEGER NOT NULL,
    received_id     INTEGER NOT NULL,
    missing_count   INTEGER NOT NULL
)

table index_daily_stats

CREATE TABLE index_daily_stats (
    date                TEXT NOT NULL,
    index_id            TEXT NOT NULL,
    open_level          REAL,
    high_level          REAL,
    low_level           REAL,
    close_level         REAL,
    -- session_state as of the most recent update, i.e. the one that set
    -- close_level. Only means "close_level is final" when this is CLOSED;
    -- for the current trading day it will typically show CONTINUOUS or
    -- another intraday state until pm-index's forced EOD publish arrives.
    close_session_state TEXT,
    open_aggregate_cap  REAL,
    close_aggregate_cap REAL,
    update_count        INTEGER NOT NULL DEFAULT 0,
    PRIMARY KEY (date, index_id)
)

table index_level_snapshots

CREATE TABLE index_level_snapshots (
    -- Arrival order. ts has millisecond resolution, so it cannot identify an
    -- update: two in one millisecond are two rows (K9 / S4).
    seq             INTEGER PRIMARY KEY AUTOINCREMENT,
    ts              TEXT NOT NULL,
    index_id        TEXT NOT NULL,
    level           REAL NOT NULL,
    aggregate_cap   REAL,
    divisor         REAL,
    session_state   TEXT,
    day_open        REAL,
    day_high        REAL,
    day_low         REAL
)

table instruments

CREATE TABLE instruments (
    symbol        TEXT    NOT NULL PRIMARY KEY,
    -- Decimal places a price trades in; 1 tick = 10^-tick_decimals.
    tick_decimals INTEGER NOT NULL,
    -- Stored as well as derivable, so a BI tool can join without arithmetic.
    tick_size     REAL    NOT NULL,
    -- RESERVED FOR FUTURE USE. EduMatcher has no currency model: prices carry
    -- no currency, aggregate_cap sums across whatever the constituents are
    -- priced in, and nothing converts. Always NULL today. The column exists so
    -- that adding a currency model later does not require a schema migration
    -- of the reference table, and so a consumer can see the field is absent
    -- rather than assume a currency.
    currency      TEXT,
    -- 'config'  — from the engine's symbol_meta, authoritative
    -- 'observed'— inferred from a message's tick_decimals, best effort
    source        TEXT    NOT NULL,
    updated_ts    TEXT    NOT NULL
)

table order_events

CREATE TABLE order_events (
    seq             INTEGER PRIMARY KEY AUTOINCREMENT,
    ts              TEXT NOT NULL,
    event_type      TEXT NOT NULL,
    order_id        TEXT NOT NULL,
    gateway_id      TEXT NOT NULL,
    symbol          TEXT NOT NULL,
    side            TEXT,
    order_type      TEXT,
    tif             TEXT,
    price           REAL,
    quantity        INTEGER,
    remaining_qty   INTEGER,
    status          TEXT,
    fill_price      REAL,
    fill_qty        INTEGER,
    -- JSON array of the durable engine trade ids behind a FILL. One fill
    -- event aggregates everything the order executed in one dispatch, so it
    -- can name several trades; NULL for every other event type.
    trade_ids       TEXT,
    reason          TEXT,
    -- The ack's reject_code on a *_REJECT event; NULL otherwise.
    reject_code     TEXT,
    client_tag      TEXT,
    combo_parent_id TEXT,
    oco_group_id    TEXT,
    priority_reset  INTEGER,
    -- The audit message id this row records (its line's msg=).
    msg_id          TEXT
)

table price_snapshots

CREATE TABLE price_snapshots (
    ts            TEXT NOT NULL,
    symbol        TEXT NOT NULL,
    -- Derived, therefore REAL and expressed in ticks: the midpoint of two
    -- adjacent ticks is a half tick, which no integer can hold. best_bid and
    -- best_ask beside it are exact.
    mid_price     REAL,
    best_bid      INTEGER,
    best_ask      INTEGER,
    -- A percentage, not money — never converted.
    pct_change    REAL,
    tick_decimals INTEGER NOT NULL DEFAULT 2,
    PRIMARY KEY (ts, symbol)
)

table stats_meta

CREATE TABLE stats_meta (
    key   TEXT NOT NULL PRIMARY KEY,
    value TEXT
)

table trade_log

CREATE TABLE trade_log (
    -- The durable engine trade id is the sole execution identity.
    ts              TEXT NOT NULL,
    trade_id        TEXT NOT NULL,
    symbol          TEXT NOT NULL,
    price           INTEGER NOT NULL,
    quantity        INTEGER NOT NULL,
    tick_decimals   INTEGER NOT NULL DEFAULT 2,
    buy_gateway_id  TEXT,
    sell_gateway_id TEXT,
    -- Mirrored from the engine payload verbatim: BUY or SELL for a
    -- continuous match, AUCTION for an uncross print where both sides were
    -- resting and there is no true aggressor. Without this column the table
    -- cannot support trade classification or order-flow imbalance, and
    -- auction prints are indistinguishable from continuous ones.
    aggressor_side  TEXT,
    buy_order_id    TEXT,
    sell_order_id   TEXT,
    -- How the trade came about (trade.executed.match_type).
    match_type      TEXT,
    -- The audit message id of the print (its line's msg=).
    msg_id          TEXT,
    PRIMARY KEY (trade_id)
)

index idx_ds_index_id_date

CREATE INDEX idx_ds_index_id_date ON index_daily_stats(index_id, date)

index idx_fg_ts

CREATE INDEX idx_fg_ts ON feed_gaps(ts)

index idx_ids_index_ts

CREATE INDEX idx_ids_index_ts ON index_level_snapshots(index_id, ts)

index idx_oe_gateway_ts

CREATE INDEX idx_oe_gateway_ts ON order_events(gateway_id, ts)

index idx_oe_order_id

CREATE INDEX idx_oe_order_id ON order_events(order_id)

index idx_oe_symbol_ts

CREATE INDEX idx_oe_symbol_ts ON order_events(symbol, ts)

index idx_oe_type_ts

CREATE INDEX idx_oe_type_ts ON order_events(event_type, ts)

index idx_ps_symbol_ts

CREATE INDEX idx_ps_symbol_ts ON price_snapshots(symbol, ts)

index idx_tl_symbol_qty

CREATE INDEX idx_tl_symbol_qty ON trade_log(symbol, quantity DESC, ts)

index idx_tl_symbol_ts

CREATE INDEX idx_tl_symbol_ts ON trade_log(symbol, ts)

clearing.db

Owner: pm-clearing
Purpose: Executions, gateway positions, daily summaries, and clearing metadata.
Schema source: src/edumatcher/clearing/store.py

Fields

Object	Field	Declared type	Not null	Default	Primary key
table: clearing_meta	key	TEXT	no		1
table: clearing_meta	value	TEXT	yes
table: gateway_daily_summary	trade_date	TEXT	yes		1
table: gateway_daily_summary	gateway_id	TEXT	yes		2
table: gateway_daily_summary	symbol	TEXT	yes		3
table: gateway_daily_summary	traded_qty	INTEGER	yes
table: gateway_daily_summary	traded_notional	INTEGER	yes
table: gateway_daily_summary	buy_qty	INTEGER	yes
table: gateway_daily_summary	sell_qty	INTEGER	yes
table: gateway_daily_summary	buy_notional	INTEGER	yes
table: gateway_daily_summary	sell_notional	INTEGER	yes
table: gateway_daily_summary	net_amount	INTEGER	yes
table: gateway_daily_summary	realized_pnl	REAL	yes
table: gateway_daily_summary	end_net_qty	INTEGER	yes
table: gateway_daily_summary	end_avg_cost	REAL	yes
table: gateway_daily_summary	end_unrealized_pnl	REAL	yes
table: gateway_daily_summary	tick_decimals	INTEGER	yes	2
table: gateway_daily_summary	last_trade_ts_ns	INTEGER	no
table: gateway_daily_summary	updated_ts_ns	INTEGER	yes
table: gateway_sessions	gateway_id	TEXT	yes		1
table: gateway_sessions	connected_at_ns	INTEGER	yes		2
table: gateway_sessions	disconnected_at_ns	INTEGER	no
table: gateway_sessions	disconnect_reason	TEXT	no
table: gateway_symbol_positions	gateway_id	TEXT	yes		1
table: gateway_symbol_positions	symbol	TEXT	yes		2
table: gateway_symbol_positions	net_qty	INTEGER	yes
table: gateway_symbol_positions	avg_cost	REAL	yes
table: gateway_symbol_positions	realized_pnl	REAL	yes
table: gateway_symbol_positions	unrealized_pnl	REAL	yes
table: gateway_symbol_positions	mark_price	INTEGER	no
table: gateway_symbol_positions	tick_decimals	INTEGER	yes	2
table: gateway_symbol_positions	buy_qty	INTEGER	yes
table: gateway_symbol_positions	sell_qty	INTEGER	yes
table: gateway_symbol_positions	buy_notional	INTEGER	yes
table: gateway_symbol_positions	sell_notional	INTEGER	yes
table: gateway_symbol_positions	last_trade_ts_ns	INTEGER	no
table: gateway_symbol_positions	updated_ts_ns	INTEGER	yes
table: position_close_snapshot	trade_date	TEXT	yes		1
table: position_close_snapshot	gateway_id	TEXT	yes		2
table: position_close_snapshot	symbol	TEXT	yes		3
table: position_close_snapshot	net_qty	INTEGER	yes
table: position_close_snapshot	realized_pnl	REAL	yes
table: position_close_snapshot	unrealized_pnl	REAL	yes
table: position_close_snapshot	mark_price	INTEGER	no
table: position_close_snapshot	tick_decimals	INTEGER	yes	2
table: session_events	id	INTEGER	no		1
table: session_events	event_type	TEXT	yes
table: session_events	ts_ns	INTEGER	yes
table: session_events	trade_date	TEXT	yes
table: session_events	payload_json	TEXT	no
table: trade_events	id	TEXT	yes		1
table: trade_events	ts_ns	INTEGER	yes
table: trade_events	trade_date	TEXT	yes
table: trade_events	symbol	TEXT	yes
table: trade_events	quantity	INTEGER	yes
table: trade_events	price	INTEGER	yes
table: trade_events	tick_decimals	INTEGER	yes	2
table: trade_events	buy_order_id	TEXT	no
table: trade_events	sell_order_id	TEXT	no
table: trade_events	buy_gateway_id	TEXT	yes
table: trade_events	sell_gateway_id	TEXT	yes
table: trade_events	aggressor_side	TEXT	no
table: trade_events	ingest_ts_ns	INTEGER	yes
table: trade_events	match_type	TEXT	no
table: trade_events	msg_id	TEXT	no
view: daily_exchange_totals	trade_date	TEXT	no
view: daily_exchange_totals	traded_qty_total	BLOB	no
view: daily_exchange_totals	traded_notional_total	BLOB	no
view: daily_exchange_totals	net_amount_total	BLOB	no
view: daily_exchange_totals	realized_pnl_total	BLOB	no
view: gateway_pnl_totals	gateway_id	TEXT	no
view: gateway_pnl_totals	realized_pnl_total	BLOB	no
view: gateway_pnl_totals	unrealized_pnl_total	BLOB	no
view: gateway_pnl_totals	total_pnl	BLOB	no
view: gateway_pnl_totals	net_qty_total	BLOB	no

DDL

table clearing_meta

CREATE TABLE clearing_meta (
  key   TEXT PRIMARY KEY,
  value TEXT NOT NULL
)

table gateway_daily_summary

CREATE TABLE gateway_daily_summary (
  trade_date         TEXT    NOT NULL,
  gateway_id         TEXT    NOT NULL,
  symbol             TEXT    NOT NULL,
  traded_qty         INTEGER NOT NULL,
  traded_notional    INTEGER NOT NULL,
  buy_qty            INTEGER NOT NULL,
  sell_qty           INTEGER NOT NULL,
  buy_notional       INTEGER NOT NULL,
  sell_notional      INTEGER NOT NULL,
  net_amount         INTEGER NOT NULL,
  realized_pnl       REAL    NOT NULL,
  end_net_qty        INTEGER NOT NULL,
  end_avg_cost       REAL    NOT NULL,
  end_unrealized_pnl REAL    NOT NULL,
    tick_decimals      INTEGER NOT NULL DEFAULT 2,
  last_trade_ts_ns   INTEGER,
  updated_ts_ns      INTEGER NOT NULL,
  PRIMARY KEY (trade_date, gateway_id, symbol)
)

table gateway_sessions

CREATE TABLE gateway_sessions (
  gateway_id        TEXT    NOT NULL,
  connected_at_ns   INTEGER NOT NULL,
  disconnected_at_ns INTEGER,
  disconnect_reason TEXT,
  PRIMARY KEY (gateway_id, connected_at_ns)
)

table gateway_symbol_positions

CREATE TABLE gateway_symbol_positions (
  gateway_id       TEXT    NOT NULL,
  symbol           TEXT    NOT NULL,
  net_qty          INTEGER NOT NULL,
  avg_cost         REAL    NOT NULL,
  realized_pnl     REAL    NOT NULL,
  unrealized_pnl   REAL    NOT NULL,
  mark_price       INTEGER,
    tick_decimals    INTEGER NOT NULL DEFAULT 2,
  buy_qty          INTEGER NOT NULL,
  sell_qty         INTEGER NOT NULL,
  buy_notional     INTEGER NOT NULL,
  sell_notional    INTEGER NOT NULL,
  last_trade_ts_ns INTEGER,
  updated_ts_ns    INTEGER NOT NULL,
  PRIMARY KEY (gateway_id, symbol)
)

table position_close_snapshot

CREATE TABLE position_close_snapshot (
  trade_date     TEXT    NOT NULL,
  gateway_id     TEXT    NOT NULL,
  symbol         TEXT    NOT NULL,
  net_qty        INTEGER NOT NULL,
  realized_pnl   REAL    NOT NULL,
  unrealized_pnl REAL    NOT NULL,
  mark_price     INTEGER,
  tick_decimals  INTEGER NOT NULL DEFAULT 2,
  PRIMARY KEY (trade_date, gateway_id, symbol)
)

table session_events

CREATE TABLE session_events (
  id           INTEGER PRIMARY KEY AUTOINCREMENT,
  event_type   TEXT    NOT NULL,
  ts_ns        INTEGER NOT NULL,
  trade_date   TEXT    NOT NULL,
  payload_json TEXT
)

table trade_events

CREATE TABLE trade_events (
  -- The engine's durable trade id is the sole execution identity.
  id               TEXT    NOT NULL,
  ts_ns            INTEGER NOT NULL,
  trade_date       TEXT    NOT NULL,
  symbol           TEXT    NOT NULL,
  quantity         INTEGER NOT NULL,
  price            INTEGER NOT NULL,
    tick_decimals    INTEGER NOT NULL DEFAULT 2,
  buy_order_id     TEXT,
  sell_order_id    TEXT,
  buy_gateway_id   TEXT    NOT NULL,
  sell_gateway_id  TEXT    NOT NULL,
  -- Mirrored from the engine payload verbatim.  See module docstring note:
  -- auction trades can carry a biased aggressor marker upstream.
  aggressor_side   TEXT,
  ingest_ts_ns     INTEGER NOT NULL,
  -- How the trade came about (trade.executed.match_type), and the audit's
  -- message id of the print: joins a row to its audit line.
  match_type       TEXT,
  msg_id           TEXT,
  PRIMARY KEY (id)
)

view daily_exchange_totals

CREATE VIEW daily_exchange_totals AS
SELECT
  trade_date,
  SUM(traded_qty)               AS traded_qty_total,
  SUM(traded_notional / scale)  AS traded_notional_total,
  SUM(net_amount / scale)       AS net_amount_total,
  SUM(realized_pnl / scale)     AS realized_pnl_total
FROM (
  SELECT
    trade_date, traded_qty, traded_notional, net_amount, realized_pnl,
    CASE tick_decimals
      WHEN 0 THEN 1.0        WHEN 1 THEN 10.0
      WHEN 2 THEN 100.0      WHEN 3 THEN 1000.0
      WHEN 4 THEN 10000.0    WHEN 5 THEN 100000.0
      WHEN 6 THEN 1000000.0  WHEN 7 THEN 10000000.0
      WHEN 8 THEN 100000000.0 ELSE 100.0
    END AS scale
  FROM gateway_daily_summary
)
GROUP BY trade_date

view gateway_pnl_totals

CREATE VIEW gateway_pnl_totals AS
SELECT
  gateway_id,
  SUM(realized_pnl / scale)                    AS realized_pnl_total,
  SUM(unrealized_pnl / scale)                  AS unrealized_pnl_total,
  SUM((realized_pnl + unrealized_pnl) / scale) AS total_pnl,
  SUM(net_qty)                                 AS net_qty_total
FROM (
  SELECT
    gateway_id, net_qty, realized_pnl, unrealized_pnl,
    CASE tick_decimals
      WHEN 0 THEN 1.0        WHEN 1 THEN 10.0
      WHEN 2 THEN 100.0      WHEN 3 THEN 1000.0
      WHEN 4 THEN 10000.0    WHEN 5 THEN 100000.0
      WHEN 6 THEN 1000000.0  WHEN 7 THEN 10000000.0
      WHEN 8 THEN 100000000.0 ELSE 100.0
    END AS scale
  FROM gateway_symbol_positions
)
GROUP BY gateway_id

index ix_gateway_sessions_gateway

CREATE INDEX ix_gateway_sessions_gateway
  ON gateway_sessions(gateway_id)

index ix_gds_gateway_date

CREATE INDEX ix_gds_gateway_date
  ON gateway_daily_summary(gateway_id, trade_date)

index ix_gds_symbol_date

CREATE INDEX ix_gds_symbol_date
  ON gateway_daily_summary(symbol, trade_date)

index ix_gsp_gateway

CREATE INDEX ix_gsp_gateway
  ON gateway_symbol_positions(gateway_id)

index ix_gsp_symbol

CREATE INDEX ix_gsp_symbol
  ON gateway_symbol_positions(symbol)

index ix_session_events_date

CREATE INDEX ix_session_events_date
  ON session_events(trade_date)

index ix_session_events_type

CREATE INDEX ix_session_events_type
  ON session_events(event_type)

index ix_trade_events_buy_gw_date

CREATE INDEX ix_trade_events_buy_gw_date
  ON trade_events(buy_gateway_id, trade_date)

index ix_trade_events_date

CREATE INDEX ix_trade_events_date
  ON trade_events(trade_date)

index ix_trade_events_sell_gw_date

CREATE INDEX ix_trade_events_sell_gw_date
  ON trade_events(sell_gateway_id, trade_date)

index ix_trade_events_symbol_date

CREATE INDEX ix_trade_events_symbol_date
  ON trade_events(symbol, trade_date)

eod.db

Owner: pm-eod
Purpose: End-of-day settlement ledger: each trader's amount per trading date, in integer minor units (1234 = 12.34) of the display currency; positive = the exchange owes the trader, negative = the trader owes the exchange; every date sums to zero.
Schema source: src/edumatcher/eod/ledger.py

Fields

Object	Field	Declared type	Not null	Default	Primary key
table: eod_runs	trade_date	TEXT	no		1
table: eod_runs	status	TEXT	yes
table: eod_runs	started_at	TEXT	yes
table: eod_runs	finished_at	TEXT	yes
table: eod_runs	steps_json	TEXT	yes
table: eod_settlement	trade_date	TEXT	yes		1
table: eod_settlement	account	TEXT	yes		2
table: eod_settlement	amount_minor	INTEGER	yes
table: eod_settlement	detail_json	TEXT	yes

DDL

table eod_runs

CREATE TABLE eod_runs (
  trade_date  TEXT PRIMARY KEY,
  status      TEXT NOT NULL,   -- ok | failed | report-failed
  started_at  TEXT NOT NULL,   -- ISO 8601, UTC
  finished_at TEXT NOT NULL,   -- ISO 8601, UTC
  steps_json  TEXT NOT NULL    -- [{"step", "status", "message"}, ...]
)

table eod_settlement

CREATE TABLE eod_settlement (
  trade_date   TEXT    NOT NULL,  -- YYYY-MM-DD, in the session timezone
  -- A gateway id, or 'EXCHANGE' for the rounding residual.
  account      TEXT    NOT NULL,
  -- The day's change in the account's mark-to-market equity, in integer minor
  -- units of the display currency (2 decimals: 1234 = 12.34).  Positive: the
  -- exchange owes the trader (credit).  Negative: the trader owes the
  -- exchange (debit).  A running balance is the sum of these plus the
  -- roster's opening balance; it is never stored.
  amount_minor INTEGER NOT NULL,
  -- {symbol: day P&L as a decimal string in display currency, same sign as
  -- amount_minor}; the unrounded parts the statement lists per symbol.
  detail_json  TEXT    NOT NULL,
  PRIMARY KEY (trade_date, account)
)

log.db

Owner: pm-log-srv
Purpose: Process log events, connection sessions, and server counters.
Schema source: src/edumatcher/log_srv/schema.py

Fields

Object	Field	Declared type	Not null	Default	Primary key
table: log_events	seq	INTEGER	no		1
table: log_events	client_ts	TEXT	yes
table: log_events	server_ts	TEXT	yes
table: log_events	process	TEXT	yes
table: log_events	instance	TEXT	no
table: log_events	pid	INTEGER	yes
table: log_events	host	TEXT	yes
table: log_events	session	TEXT	yes
table: log_events	level	TEXT	yes
table: log_events	logger	TEXT	yes
table: log_events	module	TEXT	no
table: log_events	line	INTEGER	no
table: log_events	has_exception	INTEGER	yes	0
table: log_events	truncated	INTEGER	yes	0
table: log_events	message	TEXT	yes
table: processes	session	TEXT	no		1
table: processes	process	TEXT	yes
table: processes	instance	TEXT	no
table: processes	pid	INTEGER	yes
table: processes	host	TEXT	yes
table: processes	connected_at	TEXT	yes
table: processes	last_seen_at	TEXT	yes
table: processes	last_heartbeat_at	TEXT	no
table: processes	disconnected_at	TEXT	no
table: processes	disconnect_reason	TEXT	no
table: processes	log_count	INTEGER	yes	0
table: server_stats	id	INTEGER	no		1
table: server_stats	started_at	TEXT	yes
table: server_stats	total_log_events	INTEGER	yes	0
table: server_stats	total_connections	INTEGER	yes	0
table: server_stats	total_truncated	INTEGER	yes	0
table: server_stats	total_errors_sent	INTEGER	yes	0

DDL

table log_events

CREATE TABLE log_events (
    seq             INTEGER PRIMARY KEY AUTOINCREMENT,
    client_ts       TEXT NOT NULL,
    server_ts       TEXT NOT NULL,
    process         TEXT NOT NULL,
    instance        TEXT,
    pid             INTEGER NOT NULL,
    host            TEXT NOT NULL,
    session         TEXT NOT NULL,
    level           TEXT NOT NULL,
    logger          TEXT NOT NULL,
    module          TEXT,
    line            INTEGER,
    has_exception   INTEGER NOT NULL DEFAULT 0,
    truncated       INTEGER NOT NULL DEFAULT 0,
    message         TEXT NOT NULL
)

table processes

CREATE TABLE processes (
    session         TEXT PRIMARY KEY,
    process         TEXT NOT NULL,
    instance        TEXT,
    pid             INTEGER NOT NULL,
    host            TEXT NOT NULL,
    connected_at    TEXT NOT NULL,
    last_seen_at    TEXT NOT NULL,
    last_heartbeat_at TEXT,
    disconnected_at TEXT,
    disconnect_reason TEXT,
    log_count       INTEGER NOT NULL DEFAULT 0
)

table server_stats

CREATE TABLE server_stats (
    id                  INTEGER PRIMARY KEY CHECK (id = 1),
    started_at          TEXT NOT NULL,
    total_log_events    INTEGER NOT NULL DEFAULT 0,
    total_connections   INTEGER NOT NULL DEFAULT 0,
    total_truncated     INTEGER NOT NULL DEFAULT 0,
    total_errors_sent   INTEGER NOT NULL DEFAULT 0
)

index idx_le_client_ts

CREATE INDEX idx_le_client_ts  ON log_events(client_ts)

index idx_le_level_ts

CREATE INDEX idx_le_level_ts   ON log_events(level, client_ts)

index idx_le_logger_ts

CREATE INDEX idx_le_logger_ts  ON log_events(logger, client_ts)

index idx_le_process_ts

CREATE INDEX idx_le_process_ts ON log_events(process, client_ts)

index idx_le_session

CREATE INDEX idx_le_session    ON log_events(session)

index idx_proc_process

CREATE INDEX idx_proc_process ON processes(process, connected_at)

metrics.db

Owner: pm-monitor
Purpose: Operator-dashboard history: raw and 1-minute samples, daily totals, counter state and events.
Schema source: src/edumatcher/monitor/store.py

Fields

Object	Field	Declared type	Not null	Default	Primary key
table: counter_last	source	TEXT	yes		1
table: counter_last	name	TEXT	yes		2
table: counter_last	label	TEXT	yes	''	3
table: counter_last	boot_id	TEXT	yes
table: counter_last	value	REAL	yes
table: counter_last	ts	TEXT	yes
table: daily_totals	date	TEXT	yes		1
table: daily_totals	source	TEXT	yes		4
table: daily_totals	name	TEXT	yes		2
table: daily_totals	label	TEXT	yes	''	3
table: daily_totals	total	REAL	yes
table: events	seq	INTEGER	no		1
table: events	ts	TEXT	yes
table: events	kind	TEXT	yes
table: events	severity	TEXT	yes
table: events	subject	TEXT	yes
table: events	detail	TEXT	yes	''
table: metrics_meta	key	TEXT	no		1
table: metrics_meta	value	TEXT	yes
table: samples_1m	series	INTEGER	yes		1
table: samples_1m	ts	TEXT	yes		2
table: samples_1m	v_min	REAL	no
table: samples_1m	v_avg	REAL	no
table: samples_1m	v_max	REAL	no
table: samples_1m	v_last	REAL	no
table: samples_1m	n	INTEGER	yes
table: samples_1m	delta	REAL	no
table: samples_raw	series	INTEGER	yes		1
table: samples_raw	ts	TEXT	yes		2
table: samples_raw	value	REAL	yes
table: series	id	INTEGER	no		1
table: series	source	TEXT	yes
table: series	name	TEXT	yes
table: series	label	TEXT	yes	''
view: v_samples_1m	ts	TEXT	no
view: v_samples_1m	source	TEXT	no
view: v_samples_1m	name	TEXT	no
view: v_samples_1m	label	TEXT	no
view: v_samples_1m	v_min	REAL	no
view: v_samples_1m	v_avg	REAL	no
view: v_samples_1m	v_max	REAL	no
view: v_samples_1m	v_last	REAL	no
view: v_samples_1m	n	INTEGER	no
view: v_samples_1m	delta	REAL	no
view: v_samples_raw	ts	TEXT	no
view: v_samples_raw	source	TEXT	no
view: v_samples_raw	name	TEXT	no
view: v_samples_raw	label	TEXT	no
view: v_samples_raw	value	REAL	no

DDL

table counter_last

CREATE TABLE counter_last (
    source  TEXT NOT NULL,
    name    TEXT NOT NULL,
    label   TEXT NOT NULL DEFAULT '',
    boot_id TEXT NOT NULL,
    value   REAL NOT NULL,
    ts      TEXT NOT NULL,
    PRIMARY KEY (source, name, label)
)

table daily_totals

CREATE TABLE daily_totals (
    date    TEXT NOT NULL,          -- trading date in the session timezone
    source  TEXT NOT NULL,
    name    TEXT NOT NULL,
    label   TEXT NOT NULL DEFAULT '',
    total   REAL NOT NULL,
    PRIMARY KEY (date, name, label, source)
)

table events

CREATE TABLE events (
    seq      INTEGER PRIMARY KEY AUTOINCREMENT,  -- numbered by pm-monitor; AUTOINCREMENT keeps the high-water mark
    ts       TEXT NOT NULL,
    kind     TEXT NOT NULL,
    severity TEXT NOT NULL,
    subject  TEXT NOT NULL,         -- gateway id, process key, symbol, rule
    detail   TEXT NOT NULL DEFAULT ''
)

table metrics_meta

CREATE TABLE metrics_meta (
    key     TEXT PRIMARY KEY,
    value   TEXT NOT NULL
)

table samples_1m

CREATE TABLE samples_1m (
    series  INTEGER NOT NULL,       -- series.id
    ts      TEXT NOT NULL,          -- bucket start
    v_min   REAL, v_avg REAL, v_max REAL, v_last REAL,
    n       INTEGER NOT NULL,       -- samples in the bucket
    delta   REAL,                   -- counters: reset-corrected increment
    PRIMARY KEY (series, ts)
) WITHOUT ROWID

table samples_raw

CREATE TABLE samples_raw (
    series  INTEGER NOT NULL,       -- series.id
    ts      TEXT NOT NULL,
    value   REAL NOT NULL,
    PRIMARY KEY (series, ts)
) WITHOUT ROWID

table series

CREATE TABLE series (
    id      INTEGER PRIMARY KEY,
    source  TEXT NOT NULL,          -- "process/instance@host" or "host"
    name    TEXT NOT NULL,
    label   TEXT NOT NULL DEFAULT '',
    UNIQUE (name, label, source)
)

view v_samples_1m

CREATE VIEW v_samples_1m AS
    SELECT m.ts, s.source, s.name, s.label,
           m.v_min, m.v_avg, m.v_max, m.v_last, m.n, m.delta
    FROM samples_1m m JOIN series s ON s.id = m.series

view v_samples_raw

CREATE VIEW v_samples_raw AS
    SELECT r.ts, s.source, s.name, s.label, r.value
    FROM samples_raw r JOIN series s ON s.id = r.series

index idx_daily_name

CREATE INDEX idx_daily_name ON daily_totals(name, label, date)

index idx_ev_ts

CREATE INDEX idx_ev_ts ON events(ts)

market_sim_news.db

Owner: pm-market-sim
Purpose: Generated and instructor-published news events, and the value model's jumps.
Schema source: src/edumatcher/market_sim/news_history.py

Fields

Object	Field	Declared type	Not null	Default	Primary key
table: news_events	event_seq	INTEGER	no		1
table: news_events	id	TEXT	yes
table: news_events	ts_ns	INTEGER	yes
table: news_events	scope	TEXT	yes
table: news_events	targets	TEXT	yes
table: news_events	kind	TEXT	yes
table: news_events	status	TEXT	yes
table: news_events	headline	TEXT	yes
table: news_events	sentiment	REAL	yes
table: news_events	credibility	REAL	no
table: news_events	related_id	TEXT	yes
table: value_jumps	ts_ns	INTEGER	yes
table: value_jumps	symbol	TEXT	yes
table: value_jumps	log_jump	REAL	yes
table: value_jumps	value	REAL	yes

DDL

table news_events

CREATE TABLE news_events (
            event_seq INTEGER PRIMARY KEY AUTOINCREMENT,
            id TEXT NOT NULL,
            ts_ns INTEGER NOT NULL,
            scope TEXT NOT NULL,
            targets TEXT NOT NULL,
            kind TEXT NOT NULL,
            status TEXT NOT NULL,
            headline TEXT NOT NULL,
            sentiment REAL NOT NULL,
            credibility REAL,
            related_id TEXT NOT NULL
        )

table value_jumps

CREATE TABLE value_jumps (
            ts_ns INTEGER NOT NULL,
            symbol TEXT NOT NULL,
            log_jump REAL NOT NULL,
            value REAL NOT NULL
        )

index news_events_status

CREATE INDEX news_events_status ON news_events(status)

index news_events_ts

CREATE INDEX news_events_ts ON news_events(ts_ns DESC)

index value_jumps_ts

CREATE INDEX value_jumps_ts ON value_jumps(ts_ns)

companies.db

Owner: pm-compdb-cli
Purpose: Made-up company profiles of the simulated market's tickers.
Schema source: src/edumatcher/compdb/store.py

Fields

Object	Field	Declared type	Not null	Default	Primary key
table: companies	symbol	TEXT	no		1
table: companies	name	TEXT	yes
table: companies	sector	TEXT	yes
table: companies	industry	TEXT	yes
table: companies	headquarters	TEXT	no
table: companies	purpose	TEXT	no
table: companies	key_market	TEXT	no
table: companies	market_position	TEXT	no
table: companies	origin	TEXT	no
table: companies	founders	TEXT	no
table: companies	founder_story	TEXT	no
table: companies	founded_year	INTEGER	no
table: companies	ipo_year	INTEGER	no
table: companies	ipo_valuation_musd	REAL	no
table: companies	ceo_name	TEXT	no
table: companies	ceo_since	INTEGER	no
table: companies	ceo_industry_years	INTEGER	no
table: companies	employees	INTEGER	no
table: companies	fiscal_year	INTEGER	no
table: companies	revenue_musd	REAL	no
table: companies	net_income_musd	REAL	no
table: companies	revenue_growth_pct	REAL	no
table: companies	operating_margin_pct	REAL	no
table: companies	free_cash_flow_musd	REAL	no
table: companies	market_cap_musd	REAL	no
table: companies	dividend_yield_pct	REAL	no
table: companies	debt_to_equity	REAL	no
table: companies	credit_rating	TEXT	no
table: companies	competitors	TEXT	no
table: companies	key_risk	TEXT	no
view: company_view	symbol	TEXT	no
view: company_view	name	TEXT	no
view: company_view	sector	TEXT	no
view: company_view	industry	TEXT	no
view: company_view	headquarters	TEXT	no
view: company_view	purpose	TEXT	no
view: company_view	key_market	TEXT	no
view: company_view	market_position	TEXT	no
view: company_view	origin	TEXT	no
view: company_view	founders	TEXT	no
view: company_view	founder_story	TEXT	no
view: company_view	founded_year	INTEGER	no
view: company_view	ipo_year	INTEGER	no
view: company_view	ipo_valuation_musd	REAL	no
view: company_view	ceo_name	TEXT	no
view: company_view	ceo_since	INTEGER	no
view: company_view	ceo_industry_years	INTEGER	no
view: company_view	employees	INTEGER	no
view: company_view	fiscal_year	INTEGER	no
view: company_view	revenue_musd	REAL	no
view: company_view	net_income_musd	REAL	no
view: company_view	revenue_growth_pct	REAL	no
view: company_view	operating_margin_pct	REAL	no
view: company_view	free_cash_flow_musd	REAL	no
view: company_view	market_cap_musd	REAL	no
view: company_view	dividend_yield_pct	REAL	no
view: company_view	debt_to_equity	REAL	no
view: company_view	credit_rating	TEXT	no
view: company_view	competitors	TEXT	no
view: company_view	key_risk	TEXT	no
view: company_view	years_in_business	BLOB	no
view: company_view	ceo_tenure_years	BLOB	no
view: company_view	pe_ratio	BLOB	no
view: company_view	ps_ratio	BLOB	no
view: company_view	net_margin_pct	BLOB	no

DDL

table companies

CREATE TABLE companies (
    symbol TEXT PRIMARY KEY,
    name TEXT NOT NULL,
    sector TEXT NOT NULL,
    industry TEXT NOT NULL,
    headquarters TEXT,
    purpose TEXT,
    key_market TEXT,
    market_position TEXT,
    origin TEXT,
    founders TEXT,
    founder_story TEXT,
    founded_year INTEGER,
    ipo_year INTEGER,
    ipo_valuation_musd REAL,
    ceo_name TEXT,
    ceo_since INTEGER,
    ceo_industry_years INTEGER,
    employees INTEGER,
    fiscal_year INTEGER,
    revenue_musd REAL,
    net_income_musd REAL,
    revenue_growth_pct REAL,
    operating_margin_pct REAL,
    free_cash_flow_musd REAL,
    market_cap_musd REAL,
    dividend_yield_pct REAL,
    debt_to_equity REAL,
    credit_rating TEXT,
    competitors TEXT,
    key_risk TEXT
)

view company_view

CREATE VIEW company_view AS
SELECT *,
    CAST(strftime('%Y', 'now') AS INTEGER) - founded_year AS years_in_business,
    CAST(strftime('%Y', 'now') AS INTEGER) - ceo_since AS ceo_tenure_years,
    CASE WHEN net_income_musd > 0 THEN ROUND(market_cap_musd / net_income_musd, 1) END AS pe_ratio,
    CASE WHEN revenue_musd > 0 THEN ROUND(market_cap_musd / revenue_musd, 2) END AS ps_ratio,
    CASE WHEN revenue_musd > 0 THEN ROUND(100.0 * net_income_musd / revenue_musd, 1) END AS net_margin_pct
FROM companies

index companies_sector

CREATE INDEX companies_sector ON companies(sector)

log-ui-acks.db

Owner: Log GUI bridge
Purpose: Operator issue acknowledgements and acknowledgement history.
Schema source: web-apps/log-gui/apps/bridge/src/schema.sql

Fields

Object	Field	Declared type	Not null	Default	Primary key
table: ack_history	id	INTEGER	no		1
table: ack_history	fingerprint	TEXT	yes
table: ack_history	action	TEXT	yes
table: ack_history	at	TEXT	yes
table: ack_history	by	TEXT	yes
table: ack_history	note	TEXT	no
table: issue_acks	fingerprint	TEXT	no		1
table: issue_acks	acked_at	TEXT	yes
table: issue_acks	acked_by	TEXT	yes
table: issue_acks	note	TEXT	no
table: issue_acks	acked_through_seq	INTEGER	yes
table: issue_acks	level	TEXT	yes
table: issue_acks	process	TEXT	yes
table: issue_acks	logger	TEXT	yes
table: issue_acks	sample_message	TEXT	yes

DDL

table ack_history

CREATE TABLE ack_history (
    id           INTEGER PRIMARY KEY AUTOINCREMENT,
    fingerprint  TEXT NOT NULL,
    action       TEXT NOT NULL,
    at           TEXT NOT NULL,
    by            TEXT NOT NULL,
    note         TEXT
)

table issue_acks

CREATE TABLE issue_acks (
    fingerprint        TEXT PRIMARY KEY,
    acked_at           TEXT NOT NULL,
    acked_by           TEXT NOT NULL,
    note               TEXT,
    acked_through_seq  INTEGER NOT NULL,
    level              TEXT NOT NULL,
    process            TEXT NOT NULL,
    logger             TEXT NOT NULL,
    sample_message     TEXT NOT NULL
)

index idx_acks_at

CREATE INDEX idx_acks_at ON issue_acks(acked_at)

audit_index.db

Owner: pm-audit-cli
Purpose: Rebuildable query index over the rotating audit log.
Schema source: src/edumatcher/audit/indexer.py

Fields

Object	Field	Declared type	Not null	Default	Primary key
table: audit_events	id	INTEGER	no		1
table: audit_events	timestamp	TEXT	yes
table: audit_events	topic	TEXT	yes
table: audit_events	payload	TEXT	yes
table: audit_events	gateway_id	TEXT	no
table: audit_events	symbol	TEXT	no
table: audit_events	order_id	TEXT	no
table: audit_events	trade_id	TEXT	no
table: audit_events	event_type	TEXT	no
table: audit_events	seq	INTEGER	no
table: audit_events	msg_id	TEXT	no
table: audit_events	causation_id	TEXT	no
table: audit_events	correlation_id	TEXT	no
table: index_meta	key	TEXT	no		1
table: index_meta	value	TEXT	yes

DDL

table audit_events

CREATE TABLE audit_events (
    id          INTEGER PRIMARY KEY AUTOINCREMENT,
    timestamp   TEXT    NOT NULL,
    topic       TEXT    NOT NULL,
    payload     TEXT    NOT NULL,
    gateway_id  TEXT,
    symbol      TEXT,
    order_id    TEXT,
    trade_id    TEXT,
    event_type  TEXT,
    seq            INTEGER,
    msg_id         TEXT,
    causation_id   TEXT,
    correlation_id TEXT
)

table index_meta

CREATE TABLE index_meta (
    key   TEXT PRIMARY KEY,
    value TEXT NOT NULL
)

index idx_causation

CREATE INDEX idx_causation ON audit_events(causation_id)

index idx_chain

CREATE INDEX idx_chain     ON audit_events(correlation_id)

index idx_event_type

CREATE INDEX idx_event_type ON audit_events(event_type)

index idx_gateway

CREATE INDEX idx_gateway    ON audit_events(gateway_id)

index idx_gw_ts

CREATE INDEX idx_gw_ts      ON audit_events(gateway_id, timestamp)

index idx_msg_id

CREATE INDEX idx_msg_id    ON audit_events(msg_id)

index idx_order

CREATE INDEX idx_order      ON audit_events(order_id)

index idx_sym_ts

CREATE INDEX idx_sym_ts     ON audit_events(symbol, timestamp)

index idx_symbol

CREATE INDEX idx_symbol     ON audit_events(symbol)

index idx_timestamp

CREATE INDEX idx_timestamp  ON audit_events(timestamp)

index idx_topic

CREATE INDEX idx_topic      ON audit_events(topic)

index idx_topic_ts

CREATE INDEX idx_topic_ts   ON audit_events(topic, timestamp)

index idx_trade

CREATE INDEX idx_trade      ON audit_events(trade_id)

audit_replay.db

Owner: pm-audit-replay
Purpose: Rebuildable episode, event, causal-link, anomaly, and actor index.
Schema source: src/edumatcher/audit/replay/index.py

Fields

Object	Field	Declared type	Not null	Default	Primary key
table: actors	gateway_id	TEXT	no		1
table: actors	description	TEXT	no
table: actors	first_seen	TEXT	yes
table: actors	last_seen	TEXT	yes
table: actors	orders	INTEGER	yes	0
table: actors	trades	INTEGER	yes	0
table: anomalies	anomaly_id	INTEGER	no		1
table: anomalies	code	TEXT	yes
table: anomalies	severity	TEXT	yes
table: anomalies	episode_id	INTEGER	no
table: anomalies	sort_key	TEXT	yes
table: anomalies	receipt_ts	TEXT	yes
table: anomalies	detail	TEXT	yes
table: anomalies	file	TEXT	no
table: anomalies	line_no	INTEGER	yes	0
table: episode_events	episode_id	INTEGER	yes		1
table: episode_events	seq_in_ep	INTEGER	yes		2
table: episode_events	ref	TEXT	yes
table: episode_events	sort_key	TEXT	yes
table: episode_events	receipt_ts	TEXT	yes
table: episode_events	topic	TEXT	yes
table: episode_events	kind	TEXT	yes
table: episode_events	file	TEXT	yes
table: episode_events	line_no	INTEGER	yes
table: episode_events	role	TEXT	yes
table: episode_events	late	INTEGER	yes	0
table: episode_events	msg_id	TEXT	no
table: episode_events	causation_id	TEXT	no
table: episode_events	correlation_id	TEXT	no
table: episode_events	topic_seq	INTEGER	no
table: episode_events	eseq	INTEGER	no
table: episode_events	publisher_run	INTEGER	yes	0
table: episode_events	payload	TEXT	yes
table: episodes	episode_id	INTEGER	no		1
table: episodes	kind	TEXT	yes
table: episodes	anchor_key	TEXT	yes
table: episodes	correlation_id	TEXT	no
table: episodes	root_msg_id	TEXT	no
table: episodes	run_seq	INTEGER	no
table: episodes	symbol	TEXT	no
table: episodes	actor	TEXT	no
table: episodes	opened_sort_key	TEXT	yes
table: episodes	closed_sort_key	TEXT	no
table: episodes	opened_ts	TEXT	yes
table: episodes	closed_ts	TEXT	no
table: episodes	outcome	TEXT	no
table: episodes	summary	TEXT	yes
table: episodes	facts_json	TEXT	yes
table: links	from_episode	INTEGER	yes		1
table: links	to_episode	INTEGER	yes		2
table: links	relation	TEXT	yes		3
table: links	confidence	TEXT	yes
table: links	evidence	TEXT	yes
table: replay_meta	key	TEXT	no		1
table: replay_meta	value	TEXT	yes
table: stated_links	from_ref	TEXT	yes		1
table: stated_links	to_ref	TEXT	yes		2
table: stated_links	relation	TEXT	yes		3
table: stated_links	confidence	TEXT	yes
table: stated_links	evidence	TEXT	yes

DDL

table actors

CREATE TABLE actors (
    gateway_id   TEXT PRIMARY KEY,
    description  TEXT,
    first_seen   TEXT NOT NULL,
    last_seen    TEXT NOT NULL,
    orders       INTEGER NOT NULL DEFAULT 0,
    trades       INTEGER NOT NULL DEFAULT 0
)

table anomalies

CREATE TABLE anomalies (
    anomaly_id  INTEGER PRIMARY KEY,
    code        TEXT NOT NULL,
    severity    TEXT NOT NULL,
    episode_id  INTEGER REFERENCES episodes(episode_id),
    sort_key    TEXT NOT NULL,
    receipt_ts  TEXT NOT NULL,
    detail      TEXT NOT NULL,
    file        TEXT,
    line_no     INTEGER NOT NULL DEFAULT 0
)

table episode_events

CREATE TABLE episode_events (
    episode_id  INTEGER NOT NULL REFERENCES episodes(episode_id),
    seq_in_ep   INTEGER NOT NULL,
    ref         TEXT    NOT NULL,
    sort_key    TEXT    NOT NULL,
    receipt_ts  TEXT    NOT NULL,
    topic       TEXT    NOT NULL,
    kind        TEXT    NOT NULL,
    file        TEXT    NOT NULL,
    line_no     INTEGER NOT NULL,
    role        TEXT    NOT NULL,
    late        INTEGER NOT NULL DEFAULT 0,
    msg_id         TEXT,
    causation_id   TEXT,
    correlation_id TEXT,
    topic_seq      INTEGER,
    eseq           INTEGER,
    publisher_run  INTEGER NOT NULL DEFAULT 0,
    payload     TEXT    NOT NULL,
    PRIMARY KEY (episode_id, seq_in_ep)
)

table episodes

CREATE TABLE episodes (
    episode_id      INTEGER PRIMARY KEY,
    kind            TEXT NOT NULL,
    anchor_key      TEXT NOT NULL,
    correlation_id  TEXT,
    root_msg_id     TEXT,
    run_seq         INTEGER,
    symbol          TEXT,
    actor           TEXT,
    opened_sort_key TEXT NOT NULL,
    closed_sort_key TEXT,
    opened_ts       TEXT NOT NULL,
    closed_ts       TEXT,
    outcome         TEXT,
    summary         TEXT NOT NULL,
    facts_json      TEXT NOT NULL
)

table links

CREATE TABLE links (
    from_episode  INTEGER NOT NULL REFERENCES episodes(episode_id),
    to_episode    INTEGER NOT NULL REFERENCES episodes(episode_id),
    relation      TEXT NOT NULL,
    confidence    TEXT NOT NULL,
    evidence      TEXT NOT NULL,
    PRIMARY KEY (from_episode, to_episode, relation)
)

table replay_meta

CREATE TABLE replay_meta (
    key         TEXT PRIMARY KEY,
    value       TEXT NOT NULL
)

table stated_links

CREATE TABLE stated_links (
    from_ref    TEXT NOT NULL,
    to_ref      TEXT NOT NULL,
    relation    TEXT NOT NULL,
    confidence  TEXT NOT NULL,
    evidence    TEXT NOT NULL,
    PRIMARY KEY (from_ref, to_ref, relation)
)

index idx_anom_code

CREATE INDEX idx_anom_code     ON anomalies(code, sort_key)

index idx_ee_cause

CREATE INDEX idx_ee_cause      ON episode_events(causation_id)

index idx_ee_chain

CREATE INDEX idx_ee_chain      ON episode_events(correlation_id)

index idx_ee_msg

CREATE INDEX idx_ee_msg        ON episode_events(msg_id)

index idx_ee_ref

CREATE INDEX idx_ee_ref        ON episode_events(ref)

index idx_ee_sort

CREATE INDEX idx_ee_sort       ON episode_events(sort_key)

index idx_ep_actor_ts

CREATE INDEX idx_ep_actor_ts   ON episodes(actor, opened_ts)

index idx_ep_chain

CREATE INDEX idx_ep_chain      ON episodes(correlation_id)

index idx_ep_kind_key

CREATE INDEX idx_ep_kind_key   ON episodes(kind, anchor_key)

index idx_ep_sort

CREATE INDEX idx_ep_sort       ON episodes(opened_sort_key)

index idx_ep_symbol_ts

CREATE INDEX idx_ep_symbol_ts  ON episodes(symbol, opened_ts)

index idx_links_to

CREATE INDEX idx_links_to      ON links(to_episode)