Instruction file imported from tomanizer/market-risk-analytics-drilldown-browser (.cursor/rules/tdd-reference.mdc). Copyright stays with the author.

TDD Reference — Key Architectural Decisions

This file summarises the binding technical decisions made in the full Technical Design Document (tdd.md). These decisions are final. Do not deviate from them or re-evaluate them. If a decision seems suboptimal for a specific case, flag it in a comment and raise it for human review — do not silently substitute a different approach.

1. DuckDB Concurrency Model

Decision: Multiple independent in-memory connections, semaphore-gated pool.

• Each acquired connection is duckdb.connect(":memory:") — never a file-based connection
• S3 credentials and extensions (httpfs, postgres_scanner) configured on every new connection
• Connections are closed after use, never returned to a pool
• Max concurrent connections: DUCKDB_MAX_CONNECTIONS (default 8)
• Excess requests queue behind the semaphore — never rejected, never error before timeout
• Hard timeout: DUCKDB_QUERY_TIMEOUT_SECONDS (default 30s) — raise QueryTimeoutError on breach
• FastAPI is async; DuckDB is sync — bridge via run_in_executor with a dedicated ThreadPoolExecutor
• Memory per connection: DUCKDB_MEMORY_LIMIT_GB / DUCKDB_MAX_CONNECTIONS

# Always acquire connections like this — never instantiate DuckDB directly in routes or services
async with pool.acquire() as conn:
    result = await run_duckdb_query(conn, sql, params)

2. Three-Way Comparison Query Pattern

Decision: Single parquet_scan with conditional aggregation — never three separate queries.

One S3 read covers all three methodology partitions. Aggregation is conditioned on run_id using FILTER (WHERE run_id = ?) within each aggregate function.

-- CORRECT — single scan, conditional aggregation
SELECT
    desk,
    percentile_cont(0.01) WITHIN GROUP (ORDER BY pnl_value)
        FILTER (WHERE run_id = :tg)  AS var99_tg,
    percentile_cont(0.01) WITHIN GROUP (ORDER BY pnl_value)
        FILTER (WHERE run_id = :ng)  AS var99_ng,
    percentile_cont(0.01) WITHIN GROUP (ORDER BY pnl_value)
        FILTER (WHERE run_id = :fr)  AS var99_fr,
    var99_ng - var99_tg              AS delta_var99_ng_tg,
    var99_fr - var99_tg              AS delta_var99_fr_tg
FROM parquet_scan(:glob, hive_partitioning=true)
WHERE cob_date = :cob_date
  AND run_id IN (:tg, :ng, :fr)
GROUP BY desk

-- WRONG — never do this
df_tg = conn.execute("SELECT ... WHERE run_id = :tg ...", ...).df()
df_ng = conn.execute("SELECT ... WHERE run_id = :ng ...", ...).df()
df_fr = conn.execute("SELECT ... WHERE run_id = :fr ...", ...).df()
merged = df_tg.merge(df_ng, ...).merge(df_fr, ...)

3. Never Load Full Data Into Memory

Decision: All filtering, aggregation, and statistical computation happens in DuckDB SQL.

Python only handles the result set (small, aggregated). Raw Parquet rows are never loaded into pandas DataFrames for further processing.

# CORRECT
result = conn.execute("""
    SELECT book_id, percentile_cont(0.01) WITHIN GROUP (ORDER BY pnl_value) AS var99
    FROM parquet_scan(?, hive_partitioning=true)
    WHERE cob_date = ? AND run_id = ? AND desk = ?
    GROUP BY book_id
""", [path, cob_date, run_id, desk]).df()

# WRONG — never do this
df = conn.execute("SELECT * FROM parquet_scan(?)", [path]).df()
result = df[df['desk'] == desk].groupby('book_id')['pnl_value'].quantile(0.01)

The only exception: vector detail view returns raw scenario rows, but only when scoped to a single book_id × risk_factor × run_id combination (max ~3,000 rows total).

4. All SQL Lives in query_builder.py

Decision: QueryBuilder is the single source of truth for all SQL. No SQL anywhere else.

• Route handlers call service functions
• Service functions call QueryBuilder methods
• QueryBuilder methods return (sql: str, params: dict)
• SQL is never constructed in routes, services, or anywhere outside query_builder.py
• All queries are parameterised — never f-string or format() SQL values
• group_by column names are validated against a strict whitelist before being interpolated into SQL (the only safe interpolation)

# CORRECT — route handler
@router.post("/comparison/summary")
async def summary(ctx: FilterContext, user = Depends(get_current_user)):
    return await comparison_service.get_summary(ctx, user)

# CORRECT — service
async def get_summary(ctx: FilterContext, user: User) -> ComparisonResponse:
    paths = catalog.get_glob_path(ctx.run_ids, ctx.cob_date)
    sql, params = query_builder.build_summary_query(ctx, paths)
    async with pool.acquire() as conn:
        df = await run_duckdb_query(conn, sql, params)
    return ComparisonResponse.from_df(df, ctx)

# WRONG — SQL in a route handler
@router.post("/comparison/summary")
async def summary(ctx: FilterContext):
    sql = f"SELECT {ctx.group_by}, ... WHERE desk = '{ctx.desk}'"  # NEVER

5. Parquet Partition Structure (Immutable)

Decision: Hive-partitioned by cob_date / methodology / run_id.

s3://market-risk-parquet/runs/
  cob_date=2024-01-15/
    methodology=TRADITIONAL_GRID/
      run_id=run_abc123/
        data_0001.parquet
    methodology=NEW_GRID/
      run_id=run_def456/
        data_0001.parquet
    methodology=FULL_REVAL/
      run_id=run_ghi789/
        data_0001.parquet

• Every query MUST include WHERE cob_date = ? and run_id IN (?, ?, ?) to enable partition pruning
• Always use hive_partitioning=true on every parquet_scan() call
• Verify partition pruning is active during development via EXPLAIN (look for Hive Partitioning Filter)
• Never hard-code S3 paths in queries — always resolve via ParquetCatalog.get_glob_path()
• Parquet files are read-only — never written to by this application

6. Flag Exclusion Mechanism

Decision: Fetch flags from PostgreSQL into a DuckDB VALUES clause at query time.

Flags live in PostgreSQL. The postgres_scanner DuckDB extension is available but the chosen approach is to fetch active flags for the current run_id + cob_date into Python first (cached 60s), then inject as a NOT EXISTS / VALUES clause into the DuckDB query.

-- Injected by query_builder._apply_flag_exclusion() when exclude_flagged=True
AND NOT EXISTS (
    SELECT 1 FROM (VALUES
        ('run_abc', 'BOOK_001', 'IR_SWAP_5Y', NULL),
        ('run_abc', 'BOOK_002', NULL, NULL)
    ) AS flags(run_id, book_id, risk_factor, scenario_index)
    WHERE flags.run_id = base.run_id
      AND flags.book_id = base.book_id
      AND (flags.risk_factor IS NULL OR flags.risk_factor = base.risk_factor)
      AND (flags.scenario_index IS NULL OR flags.scenario_index = base.scenario_index)
)

• Flag cache: in-process Python dict, TTL 60s per (run_id, cob_date) key
• Cache invalidated immediately on POST /api/flags or DELETE /api/flags/{id}
• exclude_flagged=True must cause ALL derived statistics (VaR, ES, IQR, MAD) to recalculate on the filtered dataset — this is non-negotiable
• This logic lives only in query_builder._apply_flag_exclusion() — never duplicated

7. Difference Vector Computation

Decision: Always computed in DuckDB SQL — never in Python.

The difference vector (FR − TG per scenario_index) and its statistics (IQR, MAD, Q1/Q3) are computed via SQL window functions and conditional aggregation. The raw difference vector is only materialised at vector-detail level (single book × risk factor scope).

-- Statistics of the difference vector — fully in SQL
WITH diffs AS (
    SELECT
        scenario_index,
        MAX(pnl_value) FILTER (WHERE run_id = :fr) -
        MAX(pnl_value) FILTER (WHERE run_id = :tg)  AS delta_fr_tg
    FROM parquet_scan(:path, hive_partitioning=true)
    WHERE cob_date = :cob_date AND run_id IN (:fr, :tg)
    {filter_clauses}
    GROUP BY scenario_index
)
SELECT
    percentile_cont(0.25) WITHIN GROUP (ORDER BY delta_fr_tg) AS q1,
    percentile_cont(0.50) WITHIN GROUP (ORDER BY delta_fr_tg) AS median,
    percentile_cont(0.75) WITHIN GROUP (ORDER BY delta_fr_tg) AS q3,
    percentile_cont(0.75) WITHIN GROUP (ORDER BY delta_fr_tg) -
    percentile_cont(0.25) WITHIN GROUP (ORDER BY delta_fr_tg) AS iqr,
    MEDIAN(ABS(delta_fr_tg - MEDIAN(delta_fr_tg) OVER ())) AS mad
FROM diffs

8. URL as Source of Truth for Filter State

Decision: FilterContext lives in URL query parameters. Zustand holds UI state only.

• useFilterContext hook reads and writes the URL via React Router useSearchParams
• Every filter change is a URL navigation — views are bookmarkable and shareable by construction
• If URL and Zustand diverge, URL wins — Zustand must derive from URL, not vice versa
• Breadcrumb is stored in sessionStorage (too large for URL) — resets to single step on page reload
• Run IDs are stored as repeated params: ?run_id=abc&run_id=def&run_id=ghi

// Every API query key includes the full FilterContext — cache hits are automatic
useQuery({
  queryKey: ['comparison', 'summary', ctx],  // ctx from URL
  queryFn: () => api.post('/comparison/summary', ctx),
})

9. Design System Constraints

Decision: Dark theme, IBM Plex fonts, compact density, BofA corporate colour palette.

Key values agents must use consistently — never substitute:

Background app:    #0F1117    Surface:      #1E2330
Background panel:  #171B24    Surface alt:  #242938
Primary (crimson): #C8102E    Navy:         #012169
Text primary:      #E8EAF0    Text dim:     #9BA3B5
Border:            #2A3045

Methodology colours (use these everywhere, never ad hoc):
  Traditional Grid: #3B82F6 (blue)
  New Grid:         #F97316 (orange)
  Full Reval:       #22C55E (green)
  Diff vector:      #A855F7 (purple)

Font: IBM Plex Sans (UI), IBM Plex Mono (all numbers and financial figures)
Base font-size: 12px. Table cells: 11px mono. Row height: 28px.
No mobile breakpoints. Minimum viewport: 1440px wide.
No decorative whitespace. No rounded cards with drop shadows.
No animated numbers. Navigation transitions: 150ms ease only.

10. Signed Difference Columns — Direction Always Preserved

Decision: Difference columns are always signed. Absolute value is a secondary column.

• delta_ng_tg = New Grid − Traditional Grid (positive = NG higher = TG understates)
• delta_fr_tg = Full Reval − Traditional Grid (positive = FR higher = TG understates risk)
• delta_fr_ng = Full Reval − New Grid
• Percentage deviation denominator is always Full Reval: (delta / ABS(fr_value)) * 100
• Cell colouring: red = positive deviation (TG understates risk vs FR), green = negative
• Never display unsigned absolute deviation as the primary column — direction is analytically critical

11. PostgreSQL Owns All Mutable State

Decision: Parquet files are read-only source data. PostgreSQL owns everything the application writes.

Tables in PostgreSQL:

• runs — run registry discovered from S3 by ParquetCatalog
• outlier_flags — user-created flags, with is_active soft-delete
• annotations — free-text notes with FilterContext snapshot (Phase 2)
• audit_log — every query execution, flag action, export

Never write to Parquet files. Never store flags or annotations in S3. Never store market risk data in PostgreSQL.

12. Required Statistical Measures (All Mandatory)

Every statistics endpoint must return all of these. Never return a partial set:

All measures must recalculate excluding flagged items when exclude_flagged=True.

13. API Conventions

• All comparison endpoints: POST with FilterContext as JSON body
• All responses: typed Pydantic models — never raw dicts or DataFrames
• Max rows returned: 10,000 (summary/drill views), 1,000 scenarios (vector detail)
• Error shape: {"error": str, "code": str, "detail": any, "requestId": str}
• Every request: extract user identity from token, write audit log entry (async, non-blocking)
• No SQL injection possible: group_by column names validated against whitelist; all values parameterised

14. Build Order (Phase 1)

Always build in this sequence. Each step must have passing tests before the next begins:

1.  Parquet fixture generator + DuckDB CLI validation
2.  DuckDB connection pool (duckdb_pool.py)
3.  Query builder with unit tests (query_builder.py)
4.  PostgreSQL schema + Alembic migrations
5.  ParquetCatalog (S3 discovery + glob resolution)
6.  Runs API endpoints
7.  Summary comparison API (POST /api/comparison/summary)
8.  Statistics API (POST /api/comparison/statistics)
9.  Drill-down API
10. Drill-through API
11. Vector detail API
12. Auth middleware (OIDC or dev bypass)
13. Frontend: Tailwind config + CSS variables + base layout
14. Frontend: Run selector
15. Frontend: useFilterContext (URL sync)
16. Frontend: ComparisonTable (AG Grid + deviation colours)
17. Frontend: Breadcrumb + useDrillNavigation
18. Frontend: StatsSummaryPanel
19. Frontend: VectorChart (3 overlaid series + diff panel)
20. Export endpoints + frontend buttons