ADR-0079: WCB Snapshot-Only Storage with Compute-on-Render Trajectory

Date: 2026-05-12 UTC Status: Accepted Epic: #1657 Design doc: docs/architecture/what-couldve-been-design.md Deciders: architect agent, data-scientist brief (PR #1658)

Context

The "What Could've Been" (WCB) feature needs to display two things after a user closes a position:

A single finalized P&L figure at the tracking horizon (EOD or EOM).
A sparkline showing the trajectory of would-have P&L from close-date to horizon.

The question is how much of this data to store persistently and how much to compute on demand.

Three options were evaluated:

Option A — Store full daily trajectory rows: Create one row per position per trading day from close-date to horizon. For a Pro+ user with 20 open EOM-tracked positions in a 22-trading-day month, this produces 440 rows per month per user. At scale (1,000 Pro+ users), this is 440,000 rows per month — before accounting for historical positions already closed.

Rejected because: O(positions × trading_days) cardinality is unnecessary for a feature that renders a sparkline from ≤31 data points. The bar data is already cached in AlpacaMarketDataService's LRU and is cheap to re-fetch. Storing it again wastes storage and complicates the schema.

Option B — Pure compute-on-render (no snapshot table): Compute everything at render time from the position's close record and Alpaca bar data. No new table. No scheduled jobs.

Rejected because: the horizon finalization is non-idempotent across job runs. Specifically: - Dividend ex_date data may not be available on the exact close day (data lag; see design doc §5.3). A pure-compute path would return different P&L values depending on when the user renders the page — before vs. after Alpaca publishes the dividend record. - For EOD, the user expects a stable, finalized P&L once the trading day closes. A pure-compute path on page render can produce drift if Alpaca bar data is adjusted retroactively (e.g., split adjustment applied after the fact). - Auditability: there is no record of what value was shown to the user at a given time. A stored snapshot provides a stable, auditable record.

Option C — Snapshot-only storage with compute-on-render trajectory (recommended): Store one row per position per tracking horizon in closed_position_wcb_snapshots. The row captures the snapshot inputs at position-close time and is updated exactly once by the scheduled job when the horizon is reached. The daily trajectory is computed on render from Alpaca bar data (≤31 bars) and cached in Redis with a 1-hour TTL.

Decision

Option C is adopted.

Store one snapshot row per position per tracking horizon. Scheduled jobs are the only writers of horizon_price, would_have_pnl, actual_pnl, delta_pnl, and horizon_reached. Once horizon_reached = TRUE, the snapshot is immutable. The render path computes the trajectory dynamically from Alpaca bar data and caches the result in Redis.

Consequences

Positive:

Storage is O(positions), not O(positions × trading_days). One row per position per tracking horizon, regardless of how long the EOM window is.
The finalized horizon values are stable and auditable. A user looking at a finalized WCB card next month sees the same numbers they would have seen the day after close.
The render path is simple: fetch the snapshot row, fetch ≤31 daily bars, apply the P&L formula. The formula is stateless — it takes bar prices and position parameters as inputs.
Graceful degradation without Redis: the trajectory is recomputed on every render, adding ~50–150 ms. Acceptable for a non-critical analytical endpoint.
Downgrade handling is straightforward: rows are flagged read-only without deletion.

Negative / constraints imposed:

Scheduled jobs are required. The EOD and EOM jobs must run reliably; a missed job means positions remain in horizon_reached = FALSE until the next run. Mitigation: Celery beat retry policy; manual re-trigger runbook for missed jobs.
The render path makes live Alpaca API calls for in-progress EOM positions (horizon not yet reached). On high-traffic render without Redis, this adds latency and Alpaca rate pressure. Mitigation: Redis cache; AlpacaMarketDataService LRU for bar data.
The 1-business-day dividend lag buffer (design doc §5.3) means positions closed on an ex-dividend day have their dividends_in_window finalized one job run late. This is a conservative design decision; feature-developer should verify Alpaca's actual data lag and remove the buffer if unnecessary.

Invariants enforced by this decision:

Scheduled jobs are the ONLY writers of horizon_price, would_have_pnl, delta_pnl, and horizon_reached. The render path is read-only after the horizon is reached.
Row creation at position-close time is lightweight (snapshot fields only; no computation). The close path latency is unaffected.
Free-tier users accumulate zero rows in closed_position_wcb_snapshots. The tier check is enforced at row-creation time, not at render time.

Alternatives Considered

See Option A and Option B descriptions in the Context section above.

An additional alternative was considered during brief review:

Option D — Materialized view: Compute the trajectory as a Postgres materialized view refreshed by the scheduled job.

Rejected because: Postgres materialized views do not support per-row refresh (refreshing the view for one position refreshes the entire view). For a table with thousands of positions across thousands of users, a full-view refresh on every EOD job run is impractical. Row-level granularity requires either explicit row writes (Option A) or on-render compute (Option C).