Skip to content

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 ts TEXT yes 1
table: index_level_snapshots index_id TEXT yes 2
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_id TEXT no
table: order_events reason 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: 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

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 (
    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,
    PRIMARY KEY (ts, index_id)
)

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,
    trade_id        TEXT,
    reason          TEXT,
    client_tag      TEXT,
    combo_parent_id TEXT,
    oco_group_id    TEXT,
    priority_reset  INTEGER
)

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,
    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_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: 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
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 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,
  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)

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_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)

market_sim_news.db

Owner: pm-market-sim
Purpose: Generated and instructor-published news events.
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

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
        )

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)

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 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,
    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
)

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
)

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)

CREATE INDEX idx_links_to      ON links(to_episode)