Raxx · internal docs

internal · gated

"What Could've Been" (WCB) — Architecture Design

Issue: #1659 (Card A) Epic: #1657 Date: 2026-05-12 UTC Status: Design locked Data-science brief: docs/data-science/strategy-briefs/what-couldve-been-2026-05-11.md (PR #1658) ADR: docs/architecture/adr/0079-wcb-snapshot-storage.md

1. Context

After a user closes a position, Raxx retrospectively surfaces what the position would have been worth if held to close-of-market (EOD) or to the end of the calendar month (EOM). This is purely backward-looking: it describes what did happen to the underlying after the user exited, derived entirely from the user's own closed position data plus public market prices. It never predicts, recommends, or benchmarks against indices.

The data-scientist brief (PR #1658) resolved all methodology questions. This document translates that brief into shippable design: finalized DDL, service location, job interface, API contract, cache spec, error states, and migration sequencing.

2. Invariants

The following constraints are non-negotiable for this feature:

3. Data Model

3.1 Finalized DDL — closed_position_wcb_snapshots

The brief's §4.2 DDL is adopted with the following annotated changes:

CREATE TABLE closed_position_wcb_snapshots (
    id                       UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    position_id              UUID NOT NULL REFERENCES closed_positions(id) ON DELETE CASCADE,
    user_id                  UUID NOT NULL,
    symbol                   VARCHAR(16) NOT NULL,
    tracking_horizon         VARCHAR(24) NOT NULL
                               CHECK (tracking_horizon IN ('close_of_market_only', 'monthly')),
    close_timestamp_utc      TIMESTAMPTZ NOT NULL,
    close_price              NUMERIC(12,6) NOT NULL,
    cost_basis               NUMERIC(12,6) NOT NULL,
    position_size_signed     NUMERIC(12,4) NOT NULL,
    close_commission         NUMERIC(10,4) NOT NULL DEFAULT 0,
    multiplier               INTEGER NOT NULL DEFAULT 1,
    asset_type               VARCHAR(16) NOT NULL DEFAULT 'equity'
                               CHECK (asset_type IN ('equity', 'call', 'put', 'multi_leg')),
    -- Horizon fields — populated by scheduled jobs only; null until horizon_reached = TRUE
    horizon_price            NUMERIC(12,6),
    horizon_timestamp_utc    TIMESTAMPTZ,
    would_have_pnl           NUMERIC(14,6),
    actual_pnl               NUMERIC(14,6),
    delta_pnl                NUMERIC(14,6),
    dividends_in_window      NUMERIC(12,6) NOT NULL DEFAULT 0,
    split_ratio_applied      NUMERIC(10,6) NOT NULL DEFAULT 1.0,
    data_source              VARCHAR(32),  -- 'alpaca' | 'alpha_vantage'
    -- Computation metadata
    last_computed_at         TIMESTAMPTZ,
    horizon_reached          BOOLEAN NOT NULL DEFAULT FALSE,
    -- Spinoff detection flag (V1: informational only; see §9 open questions)
    spinoff_detected         BOOLEAN NOT NULL DEFAULT FALSE,
    created_at               TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    updated_at               TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

CREATE INDEX idx_wcb_user_position
    ON closed_position_wcb_snapshots (user_id, position_id);

CREATE INDEX idx_wcb_pending_eod
    ON closed_position_wcb_snapshots (tracking_horizon, close_timestamp_utc)
    WHERE NOT horizon_reached AND tracking_horizon = 'close_of_market_only';

CREATE INDEX idx_wcb_pending_eom
    ON closed_position_wcb_snapshots (tracking_horizon, close_timestamp_utc)
    WHERE NOT horizon_reached AND tracking_horizon = 'monthly';

Changes from brief §4.2 and rationale:

Change Rationale
Two partial indexes split by tracking_horizon (replacing the single idx_wcb_horizon_reached) The EOD and EOM jobs query on different tracking_horizon values; two tight partial indexes outperform a combined index on large datasets.
CHECK constraint added to asset_type Prevents invalid values at the DB layer without a separate lookup table (V1 has four asset types; if V2 adds more, the migration adds them to the constraint).
spinoff_detected BOOLEAN DEFAULT FALSE added Brief Appendix Q5 defers spinoff handling to V2 but requires a UI note when a spinoff is detected. This flag allows the render path to surface the note without additional table joins. The scheduled job sets this flag when the Alpaca corporate actions endpoint returns a spinoff event within the tracking window.
data_source VARCHAR(32) — values 'alpaca' or 'alpha_vantage' Explicit provenance for audit purposes; supports future SLA/quality monitoring.

3.2 Position FK assumption

closed_positions is expected to be the table name for closed position records in Raptor's Postgres schema. Feature-developer must confirm this table name exists after the Postgres migration baseline is applied. If the table is named differently, update the FK reference before filing the Alembic migration.

4. Service Location and Job Interface

4.1 Service location: Raptor, not a separate microservice

WCB computation lives inside backend_v2/api/services/ as a new wcb_service.py. It is not a separate microservice because: - It is a read-only analytical layer over existing Raptor tables. - It calls AlpacaMarketDataService (already in Raptor) for bar data. - Launch timeline does not support spinning up a new Heroku app. - The compute load is light: scheduled jobs process pending rows in bulk once or twice per day; the render path hits the Redis trajectory cache on the hot path.

4.2 Celery task files

Two Celery periodic tasks are added to Raptor:

backend_v2/
  tasks/
    wcb_eod_job.py    — daily at 22:00 UTC
    wcb_eom_job.py    — first trading day of month at 02:00 UTC

Both tasks import from wcb_service.py for computation logic. The task files contain only the Celery decorator, scheduling metadata, and error handling. All domain logic lives in the service module.

4.3 Interface to AlpacaMarketDataService

The WCB service calls the existing get_bars() method with these parameters:

bars = alpaca_market_data_service.get_bars(
    symbol=row.symbol,
    start=date(row.close_timestamp_utc),   # or next trading day if after-hours
    end=horizon_date,
    timeframe="1Day",
    adjustment="split",                     # see §5.1 below
)

For dividend events, a separate call to the Alpaca corporate actions endpoint:

dividends = alpaca_market_data_service.get_corporate_actions(
    symbol=row.symbol,
    ca_types=["dividend"],
    since=date(row.close_timestamp_utc),
    until=horizon_date,
)

get_corporate_actions() is a new method on AlpacaMarketDataService. Its interface:

def get_corporate_actions(
    self,
    symbol: str,
    ca_types: list[str],
    since: date,
    until: date,
) -> list[CorporateActionEvent]:
    ...

Where CorporateActionEvent is a typed dataclass (or TypedDict) with at minimum: ca_type, ex_date, cash_amount, symbol, new_rate (for splits).

4.4 Trading calendar

The service requires a trading-calendar helper to: 1. Find the next regular-session close date after an after-hours close. 2. Find the last trading day of a calendar month (EOM horizon).

This helper calls AlpacaMarketDataService.get_calendar(start, end) (wrapping Alpaca's /v2/calendar endpoint). The calendar data is cached aggressively — trading calendars are published months in advance and change only for unscheduled market closures.

5. Data Source Decision

5.1 adjustment=split confirmed as V1 primary

The brief recommends adjustment=split over adjustment=all. This design confirms that recommendation for the following reasons:

Open question for feature-developer (Appendix Q2 from brief): Verify that adjustment=split is available on paper-trading Alpaca credentials. If it is not available (i.e., paper credentials only support adjustment=raw), fall back to adjustment=raw plus explicit split events from the corporate actions endpoint. Document the finding as a comment on #1661 before completing the implementation.

5.2 Fallback: Alpha Vantage TIME_SERIES_DAILY_ADJUSTED

If the Alpaca bars endpoint returns a non-200 response, the service falls back to Alpha Vantage. Alpha Vantage's TIME_SERIES_DAILY_ADJUSTED provides split coefficient and dividend amount in a single response, simplifying the fallback path. The fallback sets data_source = 'alpha_vantage' on the snapshot row.

The Alpha Vantage free tier (25 calls/day) is adequate for the scheduled job fallback path because: (a) the fallback is invoked only when Alpaca is unavailable, and (b) the scheduled jobs run at off-peak hours. The render-path trajectory computation does not use Alpha Vantage as a fallback — if both Alpaca and the cached trajectory are unavailable, the endpoint returns a 503 with { error: "market_data_unavailable" }.

5.3 Dividend data lag (Appendix Q1 from brief)

The Alpaca corporate actions endpoint provides dividend announcements with ex-date and per-share cash amount. Data availability lag is not formally published by Alpaca.

Design decision: The EOD job schedule (22:00 UTC) is approximately 6 hours after regular market close (16:00 ET = 21:00 UTC in EDT). Dividend ex-date data for ex-date = today should be queryable by 22:00 UTC. However, to be conservative, the EOD job applies a 1-business-day lag rule: a dividend with ex_date = today is not finalized in dividends_in_window until the following trading day's job run. This means:

For close_of_market_only positions that have already reached horizon_reached = TRUE, no re-evaluation is needed. The 1-day lag only affects positions closed on an ex-dividend day.

This is a conservative design. Feature-developer should verify Alpaca's actual data lag during Card B implementation and remove the buffer if empirically unnecessary. Document findings on

1661.

6. Cache Strategy

6.1 Trajectory cache

Cache key: wcb_trajectory:{position_id}:{current_date_utc}

TTL rules: - horizon_reached = FALSE (in-progress EOM tracking): TTL = 3600 seconds (1 hour). The trajectory changes each trading day; a 1-hour cache is adequate for a feature that shows historical data. - horizon_reached = TRUE (finalized): TTL = indefinite (or until the position record is deleted via DSR/downgrade pruning). Cache the finalized trajectory forever — it is immutable.

6.2 Cache infrastructure

Current state: Raptor does not have Redis provisioned. The existing AlpacaMarketDataService uses an in-process LRU cache (functools.lru_cache) for bar data.

Design decision for V1: The trajectory result is cached in Redis if REDIS_URL is set in the environment; otherwise the cache is skipped and the trajectory is recomputed on every render. This is a graceful degradation path, not a hard dependency.

When Redis is absent: - The bar data LRU cache in AlpacaMarketDataService still reduces Alpaca API calls for repeated bar requests. - Trajectory computation (applying the P&L formula to ≤31 bars) is fast enough that an uncached render adds ~50–150 ms to the response — acceptable for a non-critical analytical endpoint.

When Redis is present: - The trajectory response is cached under the key above. - The Redis client is the same instance used by Celery (if Celery uses Redis as a broker). Feature-developer should use the existing Redis connection from the Raptor app config, not create a new connection pool.

Recommendation: Provision Redis before Card B ships to staging. File a follow-up card if not already tracked. The WCB feature is not launch-blocking without Redis, but the render performance is materially better with it.

7. API Contract

7.1 Endpoint

GET /api/positions/<position_id>/what-could-have-been

Query params: - horizon (optional): eod | eom. Defaults to the tracking_horizon stored on the snapshot row. If the user's tier does not permit the requested horizon, returns 403.

7.2 Auth

Existing JWT middleware. No changes to auth infrastructure.

Tier gate: evaluated at request time via the user's current subscription tier (read from the Queue service or the Raptor session). Returns 403 with { "error": "tier_insufficient" } for free-tier users. No content returned — feature does not exist for free tier.

7.3 Success response — 200 OK

{
  "snapshot": {
    "position_id": "uuid",
    "symbol": "AAPL",
    "asset_type": "equity",
    "tracking_horizon": "close_of_market_only",
    "close_timestamp_utc": "2026-04-15T19:45:00Z",
    "close_price": "182.340000",
    "cost_basis": "170.120000",
    "position_size_signed": "50.0000",
    "close_commission": "0.0000",
    "multiplier": 1,
    "actual_pnl": "611.000000",
    "horizon_reached": true,
    "horizon_timestamp_utc": "2026-04-15T20:00:00Z",
    "horizon_price": "185.620000",
    "would_have_pnl": "775.000000",
    "delta_pnl": "164.000000",
    "dividends_in_window": "0.000000",
    "split_ratio_applied": "1.000000",
    "spinoff_detected": false,
    "data_source": "alpaca"
  },
  "trajectory": [
    ["2026-04-15", 611.00],
    ["2026-04-16", 730.50]
  ],
  "metadata": {
    "horizon_reached": true,
    "tracking_days": 1,
    "near_eom_note": null,
    "options_caveat": null,
    "spinoff_note": null,
    "data_as_of_utc": "2026-04-15T20:00:00Z"
  }
}

All numeric fields in snapshot are returned as strings (NUMERIC precision preserved, no floating-point rounding in transit). The trajectory array uses float for the P&L values (display precision; rounding at the application layer before returning).

Field definitions:

Field Type Notes
snapshot.* — Mirror of closed_position_wcb_snapshots row; all non-null fields always present
trajectory [(date_str, pnl_float)] Trading-day series from close-date to horizon. For EOD: single element. For EOM in-progress: partial series to current date. For EOM finalized: full series to month-end.
metadata.horizon_reached bool True when the scheduled job has finalized the horizon values.
metadata.tracking_days int Number of trading days in the window (1 for EOD; N for EOM).
metadata.near_eom_note str|null Set when position closed with ≤3 trading days left in the month. Example: "Tracked 2 trading day(s) into the month."
metadata.options_caveat str|null Set for asset_type in ['call', 'put', 'multi_leg']. Example: "The underlying (AAPL) would have moved from $X to $Y over this window. Your option contract's value also depended on implied volatility and time decay, which are not reflected here."
metadata.spinoff_note str|null Set when spinoff_detected = TRUE. Example: "A corporate spinoff occurred during this window. Tracking reflects the original security only."
metadata.data_as_of_utc ISO-8601 str UTC timestamp of the most recent bar data used in this response.

7.4 Error responses

Status Body Trigger
403 { "error": "tier_insufficient" } User tier is Free
403 { "error": "tier_insufficient" } User requests horizon=eom but tier is Pro
404 { "error": "position_not_found" } No position with given ID in user's account
404 { "error": "wcb_not_available" } Position exists but no WCB snapshot row (e.g., position closed before WCB was enabled)
503 { "error": "market_data_unavailable" } Alpaca unavailable AND Redis trajectory cache empty AND Alpha Vantage unavailable
422 { "error": "invalid_horizon", "detail": "horizon must be eod or eom" } Invalid horizon query param

Ownership check: The endpoint must verify that position_id belongs to the requesting user. If the position exists but belongs to a different user, return 404 (not 403) to avoid position-ID enumeration.

7.5 Settings endpoint

PATCH /api/account/settings/wcb

Request body:

{ "tracking_horizon": "close_of_market_only" | "monthly" }

Responses: - 200 OK with { "tracking_horizon": "<new_value>" } on success. - 403 with { "error": "tier_insufficient" } if the user's tier does not permit the requested horizon (Free: all settings changes rejected; Pro: monthly rejected). - 422 with { "error": "invalid_value" } for unknown tracking_horizon values.

Every successful PATCH emits an audit event:

{
  "event_type": "wcb_setting_changed",
  "user_id": "uuid",
  "old_value": "close_of_market_only",
  "new_value": "monthly",
  "changed_at_utc": "2026-05-12T14:32:00Z"
}

8. Sequence Diagrams

8.1 Row creation at position close

sequenceDiagram
    participant Client
    participant Raptor
    participant DB as Postgres

    Client->>Raptor: POST /api/trading/close-position
    Raptor->>DB: INSERT INTO closed_positions (...)
    Raptor->>Raptor: check user tier
    alt tier = Pro or Pro+
        Raptor->>DB: INSERT INTO closed_position_wcb_snapshots (snapshot fields only)
        Note over DB: horizon_reached = FALSE; horizon_price = NULL
    end
    Raptor-->>Client: 200 OK (position closed)

8.2 EOD scheduled job

sequenceDiagram
    participant Celery as Celery Beat
    participant WCBJob as wcb_eod_job
    participant WCBSvc as wcb_service
    participant AlpacaSvc as AlpacaMarketDataService
    participant DB as Postgres

    Celery->>WCBJob: trigger at 22:00 UTC
    WCBJob->>DB: SELECT pending EOD rows (idx_wcb_pending_eod)
    loop for each pending row
        WCBJob->>WCBSvc: compute_eod_horizon(row)
        WCBSvc->>AlpacaSvc: get_bars(symbol, close_date, adjustment='split')
        AlpacaSvc-->>WCBSvc: bar data
        WCBSvc->>AlpacaSvc: get_corporate_actions(symbol, ca_types=['dividend'], since, until)
        AlpacaSvc-->>WCBSvc: dividend events
        WCBSvc->>WCBSvc: apply P&L formula (§1.2/1.3 of brief)
        WCBSvc-->>WCBJob: computed fields
        WCBJob->>DB: UPDATE ... SET horizon_price=..., would_have_pnl=..., horizon_reached=TRUE
    end

8.3 On-render trajectory (in-progress EOM)

sequenceDiagram
    participant Client
    participant Raptor
    participant Redis
    participant WCBSvc as wcb_service
    participant AlpacaSvc as AlpacaMarketDataService
    participant DB as Postgres

    Client->>Raptor: GET /api/positions/<id>/what-could-have-been
    Raptor->>Raptor: JWT auth + tier check
    Raptor->>DB: SELECT snapshot row
    DB-->>Raptor: row (horizon_reached=FALSE)
    Raptor->>Redis: GET wcb_trajectory:{id}:{today}
    alt cache hit
        Redis-->>Raptor: cached trajectory
    else cache miss
        Raptor->>WCBSvc: compute_trajectory(row, up_to=today)
        WCBSvc->>AlpacaSvc: get_bars(symbol, close_date, today, adjustment='split')
        AlpacaSvc-->>WCBSvc: bar series
        WCBSvc->>WCBSvc: apply P&L formula per bar
        WCBSvc-->>Raptor: [(date, pnl), ...]
        Raptor->>Redis: SET wcb_trajectory:{id}:{today} TTL=3600
    end
    Raptor-->>Client: 200 { snapshot, trajectory, metadata }

9. Migrations

9.1 Ordering constraint — HARD GATE

The Alembic migration for closed_position_wcb_snapshots MUST NOT be filed or merged until the Raptor SQLite-to-Postgres Phase 3 cutover is confirmed complete.

Rationale: the WCB migration references closed_positions(id) as a foreign key. This table does not exist in the current SQLite schema. It is created as part of the Raptor Postgres baseline migration (0001_raptor_baseline.py). Running the WCB migration before the baseline would fail with a FK constraint error.

Sequencing: 1. Raptor Postgres migration — Phase 1 (Alembic + engine module) lands. 2. Raptor Postgres migration — Phase 2 (callsite ports) lands. 3. Raptor Postgres migration — Phase 3 cutover confirmed (staging, then prod). 4. Card B (#1661) can begin. WCB Alembic migration targets Postgres only.

PM must not dispatch Card B (#1661) until the Raptor Postgres Phase 3 cutover is confirmed on staging.

9.2 WCB migration file

Migration filename: 0002_wcb_snapshots.py (next after the raptor baseline 0001).

Rollback: DROP TABLE closed_position_wcb_snapshots — safe pre-launch because no customer data depends on this table before it ships.

9.3 No migration changes in this card

This design card (Card A, #1659) produces zero DDL changes. The migration is filed and merged as part of Card B (#1661).

10. Rollout Plan

dark    → WCB snapshot row creation wired at position-close, gated by tier check.
          No endpoint exposed. Data accumulates silently for Pro/Pro+ users.
flag    → WCB endpoint + jobs enabled behind FLAG_WCB_ENABLED (default OFF).
          Staging soak: verify EOD job runs correctly; verify trajectory renders.
beta    → FLAG_WCB_ENABLED=1 on prod for a subset of Pro/Pro+ users (operator sets).
ga      → Full rollout. Flag removed; endpoint is standard.

The FLAG_WCB_ENABLED feature flag follows the existing feature_flags.yaml pattern. Adding it requires a console_flag_promotions migration in the same PR per feedback_new_flag_needs_b1_migration_same_pr.md.

11. Security Considerations

11.1 PII

closed_position_wcb_snapshots stores financial position data including cost basis, close price, and P&L values. This is user financial PII.

Retention: - Pro/Pro+ users: retained while the user's account is active. - Downgraded to free: retained 90 days, then subject to the data-retention pruning job. The 90-day window matches Raptor's free-tier history retention (confirm exact window with billing/Queue design if different). - DSR erasure: ON DELETE CASCADE from closed_positions propagates erasure. The DSR flow that deletes closed_positions rows automatically removes WCB snapshots.

Logged for audit (per customer_audit_events): - WCB setting changes (wcb_setting_changed). - Scheduled job writes (logged at job level: job ID, number of rows updated, timestamp). Individual row updates are not audited at the customer_audit_events level — they are bulk operational writes, not user-driven state changes.

Audit retention: governed by the existing customer_audit_events retention policy.

11.2 No stored credentials

Alpaca API keys (ALPACA_API_KEY, ALPACA_SECRET_KEY) remain in Heroku config vars only. The WCB service receives them via the existing AlpacaMarketDataService initialization path. No new credential storage.

11.3 Breach notification

No new breach surface beyond what Raptor already exposes. Financial PII is already in closed_positions. If a breach occurs, the breach notification automation (per GDPR 72-hour rule) covers all financial PII in Raptor including WCB snapshot rows.

11.4 Kill switch

FLAG_WCB_ENABLED=0 disables the endpoint. The scheduled jobs can be disabled independently via Celery beat configuration without a redeploy. Both are rotatable without code deployment.

11.5 Ownership enforcement

The endpoint verifies position.user_id == requesting_user.id before returning any WCB data. Returns 404 on mismatch (not 403) to prevent position-ID enumeration.

12. Open Questions

The following questions require operator decisions or feature-developer verification before Card B (#1661) can be closed. They are restated here from the data-science brief appendix with design context added.

  1. Alpaca adjustment=split on paper credentials (Appendix Q2). This brief recommends adjustment=split but it is unverified on paper-trading credentials. Feature-developer must confirm during Card B. If unavailable, fall back to adjustment=raw + explicit split events. Document findings on #1661.

  2. Dividend data lag (Appendix Q1). Design uses a 1-business-day buffer for ex-date-T dividends. Feature-developer should verify Alpaca's actual data availability latency during Card B and remove the buffer if it is unnecessary. Unnecessary conservatism delays finalization for positions closed on ex-dividend days.

  3. Near-EOM single-point result (Appendix Q3 — operator decision). When a position is closed on the last trading day of the month, EOM and EOD produce identical results. Design §4.2 of the brief recommends displaying the single-point result transparently. Operator (Kristerpher) should confirm this is the preferred behavior before Card C (Antlers) ships the UI.

  4. 90-day free-tier downgrade retention window (Appendix Q4). This document assumes 90 days matches the free-tier data history retention. Confirm against the billing/Queue retention policy.

  5. closed_positions table name. Feature-developer must confirm the exact table name in the Raptor Postgres baseline migration before writing 0002_wcb_snapshots.py. The FK reference must match.

  6. Redis provisioning. Cache degradation is designed in (§6.2), but performance is materially better with Redis. Operator should confirm Redis provisioning timeline relative to WCB launch.