Repository: Operational Knowledge Base (Wiki.js)
Associated Runbook: RB-EXTRACT-SQL-001 (ad hoc extraction via generated query)
Last Review: 2026-07-06 — AIOps Practice
This dictionary documents the reporting replica of the TradeFlow application
(Post-Trade portfolio). It is the sole authorized source for data extraction query
generation by Shield: every generated query must resolve entirely against the tables,
columns, and joins documented here. Any request referencing data absent from this dictionary
is routed to the Cockpit (unsupported data).
| Parameter |
Value |
Rule |
| Database |
tradeflow_reporting (PostgreSQL) |
Never production OLTP |
| Schema |
public (within the dedicated database tradeflow_reporting) |
Only authorized schema |
| Execution User |
svc_reporting_ro |
Read-only — zero write privileges |
| Data Freshness |
T+0 replication, latency <= 15 min |
Must be stated in delivered report |
| Column |
Type |
Description |
PII |
trade_id |
INTEGER (PK) |
Technical identifier — internal usage, do not expose in reports |
— |
trade_ref |
TEXT (unique) |
Business reference of the trade (FX-2026-000NN) — deliver this field |
— |
trade_date |
DATE |
Trade execution date — default reference for any period filter |
— |
value_date |
DATE |
Value date (settlement date) |
— |
instrument_id |
INTEGER (FK) |
→ instruments.instrument_id |
— |
counterparty_id |
INTEGER (FK) |
→ counterparties.counterparty_id |
— |
trader_id |
INTEGER (FK) |
→ traders.trader_id |
— |
side |
TEXT |
BUY | SELL |
— |
notional |
NUMERIC(18,2) |
Notional amount |
— |
currency_pair |
TEXT |
Currency pair (EUR/USD, …) |
— |
rate |
NUMERIC(12,6) |
Negotiated exchange rate |
— |
status |
TEXT |
BOOKED | SETTLED | CANCELLED |
— |
| Column |
Type |
Description |
PII |
counterparty_id |
INTEGER (PK) |
Technical identifier |
— |
name |
TEXT |
Legal corporate name — standard filter key for requests |
— |
lei_code |
TEXT |
Legal Entity Identifier (public LEI) |
— |
country |
TEXT |
Country code (ISO-2) |
— |
sector |
TEXT |
Business sector |
— |
| Column |
Type |
Description |
PII |
instrument_id |
INTEGER (PK) |
Technical identifier |
— |
instrument_type |
TEXT |
FX_SPOT | FX_FORWARD | FX_SWAP |
— |
description |
TEXT |
Instrument label |
— |
| Column |
Type |
Description |
PII |
trader_id |
INTEGER (PK) |
Technical identifier |
— |
full_name |
TEXT |
Operator full name |
PII |
email |
TEXT |
Operator email address |
PII |
desk |
TEXT |
Assigned trading desk (FX Desk Paris, …) — non-PII, deliverable |
— |
trades.instrument_id = instruments.instrument_id
trades.counterparty_id = counterparties.counterparty_id
trades.trader_id = traders.trader_id
Any join outside this relational graph is strictly prohibited. Joining to traders is
authorized for the desk column only, unless explicit PII entitlement is verified.
| Common wording in tickets |
Schema resolution |
| "trade reference", "deal ID" |
trades.trade_ref (never trade_id) |
| "trade date", "execution date", "period" |
trades.trade_date |
| "value date", "settlement date" |
trades.value_date |
| "side", "buy/sell", "direction" |
trades.side |
| "notional", "amount" |
trades.notional |
| "currency pair", "currency", "pair" |
trades.currency_pair |
| "rate", "price", "exchange rate" |
trades.rate |
| "status", "cancelled trades" |
trades.status (CANCELLED = cancelled) |
| "FX Spot", "FX Forward", "FX Swap" |
instruments.instrument_type IN (FX_SPOT, FX_FORWARD, FX_SWAP) |
| "counterparty", "client" |
counterparties.name |
| "desk" |
traders.desk |
| "trader", "operator" (named individual) |
traders.full_name — PII: explicit clearance required |
- SELECT-only: The generated query must be a pure read operation. Any DDL/DML token
(INSERT, UPDATE, DELETE, DROP, ALTER, TRUNCATE, GRANT, …)
immediately invalidates generation — hard block, zero bypass.
- Replica execution only: Exclusive execution on
tradeflow_reporting,
schema public, user account svc_reporting_ro.
- No
SELECT *: Output columns must be explicitly enumerated and match
the requested fields in the ticket.
- PII by entitlement only:
traders.full_name and traders.email are only
selectable if the requester holds appropriate PII clearance (see matrix §7).
Otherwise: column excluded and annotated in ticket.
- Mandatory bounded period: All time filters must specify closed intervals.
Any request without explicit dates is returned for clarification.
- Mandatory dry-run: Execute
EXPLAIN + COUNT(*) prior to extraction;
the exact SQL text and estimated row count are presented in Cockpit.
What is approved is what executes — any modification to SQL post-approval
invalidates approval.
- Volumetrics: Extractions exceeding 100,000 rows at dry-run require Cockpit
human arbitration before execution.
| Level |
Requester Role |
Authorized Scope |
PII Columns |
| RISK-EXTRACT |
Risk Control |
Trade data, all counterparties, any period |
No |
| MO-EXTRACT |
Middle Office |
Trade data, limited to requester's desk |
No |
| COMPLIANCE-EXTRACT |
Compliance |
Full scope, referenced audit mission mandatory |
Yes |
| — (out of matrix) |
Any other requester |
None |
— |
Requests involving user personal data (DSAR / GDPR) fall outside this runbook:
refer to KB-EXT-001 / RB-EXTRACT-USER-001.