Viska IBKR / Flex Trading-Data Stack

§0TL;DR & current state

The Phase-1 single-day pipeline is live end-to-end in production — a real round-trip wrote June-19 holdings to the database. A Phase-2 multi-day upgrade is coded, merged, and tested, but its deployment to the running Railway service is unconfirmed, and the nightly schedule sits behind one operator activation gate.

§1The end goal

The IBKR Flex stack is the data foundation of the "Viska Trading Intelligence" product (board #8, epic viska-pm#7). Its job: keep an authoritative, daily-updating book of the fund's holdings, NAV, cash, FX, trades and dividends in Postgres, so every downstream surface reads one source of truth instead of stale manual snapshots.

Frame: CLAUDE.md:69 — board #8 "data product — IBKR/Flex ingest, trading schema, morning brief, corpus WS-A".

§2Architecture at a glance

Three moving parts, one ruling decision. The ruling decision (055-A, operator-ratified): n8n is pure transport; apply-svc is the SSOT parser. n8n pulls the raw Flex XML and uploads it untouched; the Railway service re-parses it and is the only writer to trading.*. This removed an earlier ambiguity about where parsing lives.

The Railway service surface

Environment surface (read in create_app())

Cited: apply_service.py:322–331 (env reads) · :46 — FLEX_UPLOADER_JWT DROPPED in task 060.

§3The data flow, end to end

┌──────────────────────────────────────────────────────────────────────────┐
│  IBKR Flex Web Service  →  n8n (transport)  →  apply-svc  →  trading.*     │
└──────────────────────────────────────────────────────────────────────────┘

TRIGGER   cron 0 30 22 * * 1-5 (22:30, Mon–Fri)   ┐
          on-demand webhook  POST /viska/flex-pull   ┘ (header-auth)
                    │
IBKR PULL  POST .../FlexWebService/SendRequest  q=1384817 v=3
          → ReferenceCode → poll GetStatement (20s × up to 6) → raw FlexQueryResponse XML
                    │  guards: SendRequest "Success"? · Statement ready? (else Slack alert)
                    ▼
NORMALIZE Code node → normalizeFlexDataset()  (scripts/lib/flex-normalize.js)
          multi-day aware: report_dates[], position_count = Σ days, date_from/to
          guard "Statement Usable?"  position_count > 0 AND report_date present
                    │   (false → respond count=0, skip apply — empty-XML guard)
                    ▼
SIGN      POST /sign-upload (Bearer) ─────────────────────────────┐
          apply-svc mints short-lived signed URL (service-role, server-side)│
                    │◄──────────────────────── signedUrl ───────────────────┘
UPLOAD    PUT {signedUrl}  raw XML  →  Supabase Storage  flex-inbox/incoming/*.xml
                    ▼
APPLY     POST /apply (Bearer)
          ├─ fetch raw XML from flex-inbox
          ├─ build_sections_by_day()  → one tuple per distinct report_date   ◄── Fix A
          ├─ emit_days()  → ONE BEGIN…COMMIT, per-day _stmt_<i> temp tables
          │                       ON CONFLICT throughout (idempotent)
          ├─ apply_sql() over VISKA_DB_URL
          ├─ move object → processed/  (best-effort, non-blocking)
          └─ on error → stderr alert + INSERT ops.health_events + 500 (retry-safe)
                    ▼
WRITE     trading.statements · positions_snapshots · nav_snapshots · fx_balances
          trading.trades · cash_flows · dividends · fees_interest · instruments
                    ▼
RESPOND   { ok, account, reportDate, report_dates[], statements, positions, fx, nav_rows }

READ      v_positions_current · v_nav_latest · v_pnl_monthly  → Mímir brief
          public.allocation_snapshots · positions_current · nav_latest → viska.gg

Cited: ViskaN8N flex-pull-ADeTjBZiHOMIXzvr.json:5–546 (nodes) · apply_service.py:104,116,125–169,227–299 · ibkr_import.py:142–407 · scripts/lib/flex-normalize.js.

§4The trading.* schema

Every table carries statement_id lineage back to the statement row that produced it. The importer writes all of them in one transaction per /apply call.

Cited: migrations/20260610-01-trading_schema_core.sql:17–79 · 20260610-02-trading_book.sql:11–194 · ibkr_import.py:181–352.

§5Idempotency — why re-firing is safe

Each event table then carries its own UNIQUE constraint with ON CONFLICT DO NOTHING, so a re-fire of /apply with the same object produces zero net-new rows. This is what makes the 500-on-error / retry-from-inbox design correct, and what made the multi-day fix a pure importer change with no migration.

§6Development history

From operator directive to live pipeline in ~6 days. Phases below; status badges reflect state as of this report.

PHASE 0 · Foundation — CSV book (May → early June)

WS-1 stood up the trading.* schema + read-only views; WS-2 did a one-time IBKR Activity CSV first-import. That CSV book was the baseline — but static, not auto-updating. The whole Flex project exists to replace it with a live feed. STATE.md (ViskaN8N-010): "trading.* were populated by the one-time WS-2 CSV first-import — NOT auto-updating".

PHASE 1 · Flex XML parser + first live landing (Jun 17 → 19)

PHASE 2 · Architecture decisions (Jun 16 → 22)

• 06-16 — Operator directive: "live IBKR query to shift the SSOT on holdings." Task 054 filed; Flex token env-vars wired.
• 06-17 — Decision 055-A ratified: n8n = transport, apply-svc = SSOT parser. Governs everything downstream.
• 06-19 — CIO ratification packet prepared: 9 items A–P (ISK→NAV interim, TWR method, MTD rename, market-quotes vendor, fx_rates, allocation, TSLA theme, position count 31→21, public-data posture). awaiting signature

PHASE 3 · Apply-service + auth (Jun 21)

PHASE 4 · Deploy, wire, go live (Jun 21 → 22)

§7Decision records

Captured in Viska-Wiki: [[flex-apply-service-auth-bearer]] · [[flex-upload-signed-urls]] · [[viska-supabase-es256-jwt-signing]].

§8Delivery status

§9Current unknowns & blockers

Ordered by what stands between "coded" and "the nightly feed lands all 22 days in prod." Confirmed facts and honest unknowns are marked distinctly — the deploy trigger is genuinely unconfirmed and should not be assumed.