Repository: Operational Knowledge Base (Wiki.js)
Runbook ID: SOP-OPS-014
Last Review: 2026-10-15 — AIOps SRE Team
This Standard Operating Procedure (SOP) defines the operational investigation workflow
for Post-Trade data anomalies within the TradeFlow ecosystem. It addresses:
tradeflow_reporting PostgreSQL replica.Trade executions originate in Order Management Systems (OMS) and publish to Kafka topic trade-events.
trade-events, validates schemas, and writes to primary OLTP storage (tradeflow_oltp).tradeflow_reporting (public.trades).Check PostgreSQL replication lag between primary OLTP and reporting replica:
SELECT
client_addr,
application_name,
state,
sync_state,
ROUND(EXTRACT(EPOCH FROM (now() - pg_last_xact_replay_timestamp()))::numeric, 2) AS lag_seconds
FROM pg_stat_replication;
If lag_seconds > 900 (15 minutes), flag as active replication stall. Check network saturation and IOPS bottlenecks on the replica host.
Compare trade statuses across primary and replica:
SELECT trade_ref, status, updated_at
FROM public.trades
WHERE trade_date >= CURRENT_DATE - INTERVAL '1 day'
AND status NOT IN ('BOOKED', 'SETTLED', 'CANCELLED');
Investigate any records with unrecognized statuses or where BOOKED transitions failed to register.
Identify orphan trades referencing unmapped counterparties or missing instruments:
-- Unmapped counterparties
SELECT t.trade_id, t.trade_ref, t.counterparty_id
FROM public.trades t
LEFT JOIN public.counterparties c ON t.counterparty_id = c.counterparty_id
WHERE c.counterparty_id IS NULL;
-- Unmapped instruments
SELECT t.trade_id, t.trade_ref, t.instrument_id
FROM public.trades t
LEFT JOIN public.instruments i ON t.instrument_id = i.instrument_id
WHERE i.instrument_id IS NULL;
If orphan trades are detected, trigger an emergency master data refresh from the Enterprise Master Data service.
Run sanity checks on notional amounts and rate boundaries:
SELECT trade_id, trade_ref, currency_pair, notional, rate
FROM public.trades
WHERE notional <= 0
OR rate <= 0
OR currency_pair NOT LIKE '%/%';
Any negative or zero notional indicates an upstream serialization bug or malformed FIX message.
pg_blocking_pids() and terminate idle-in-transaction sessions.trade-events, verify consumer group health:kafka-consumer-groups.sh --bootstrap-server localhost:9092 --describe --group tradeflow-ingestion-group
tradeflow_reporting under user svc_reporting_ro. Never execute diagnostic queries directly against OLTP primary.