ETF NAV Discount Strategy — Data Schema

Strategy ID: etf-nav-discount Version: 0.1 (research)

1. StrategyDefinition

The user-authored strategy record, as stored in the database after DSL parsing.

{
  "strategy_id": "string (uuid4)",
  "user_id": "string (uuid4, foreign key to users table)",
  "name": "string (user-provided label, e.g. 'Buy SPY at discount')",
  "nl_input": "string (original natural-language string the user typed)",
  "dsl": {
    "ticker": "string",
    "trigger": {
      "metric": "string (price_vs_nav | price_vs_sma_N | rsi_below | custom)",
      "operator": "string (lt | gt | lte | gte)",
      "threshold_pct": "number (signed; negative = discount)"
    },
    "action": {
      "side": "string (buy | sell)",
      "qty": "integer (shares)",
      "order_type": "string (market | limit)",
      "limit_price_rule": "string (nav | bid | ask | mid | null)"
    },
    "schedule": {
      "day_of_week": "string (Monday | Tuesday | Wednesday | Thursday | Friday | any)",
      "time_utc": "string (HH:MM, must be 14:30–21:00)"
    }
  },
  "dsl_parse_confidence": "number (0.0–1.0, from Claude parse call)",
  "dsl_version": "string (semver, e.g. '1.0')",
  "status": "string (draft | confirmed | active | paused | archived)",
  "paper_only": "boolean",
  "max_notional_usd": "number (hard cap per execution)",
  "created_at": "string (ISO 8601 datetime UTC)",
  "confirmed_at": "string (ISO 8601 datetime UTC, nullable — set when user confirms parse)",
  "last_modified_at": "string (ISO 8601 datetime UTC)"
}

Status transitions: - draft — parsed but not yet confirmed by user - confirmed — user confirmed the parsed DSL; awaiting first scheduled window - active — at least one execution has occurred; scheduled - paused — user or system suspended execution - archived — strategy deleted; retained for audit

2. ConditionEvaluation

Created on every scheduled evaluation window, whether or not the condition was met.

{
  "eval_id": "string (uuid4)",
  "strategy_id": "string (fk → StrategyDefinition)",
  "eval_datetime_utc": "string (ISO 8601 datetime UTC)",
  "ticker": "string",
  "market_price": "number (USD, ask or last trade at eval time)",
  "nav_per_share": "number (USD, most recent available NAV)",
  "nav_date": "string (ISO 8601 date)",
  "nav_lag_hours": "number (hours between nav_date close and eval_datetime_utc)",
  "discount_pct": "number (computed: (market_price - nav_per_share) / nav_per_share * 100)",
  "threshold_pct": "number (configured threshold at time of eval)",
  "condition_met": "boolean",
  "data_quality_flags": ["array of strings: stale_nav | market_closed | quote_stale | nav_unavailable"],
  "execution_id": "string (fk → ExecutionRecord, nullable — set if order fired)"
}

3. ExecutionRecord

Created when the condition is met and an order is submitted. One record per order, even if the order is rejected or fails.

{
  "execution_id": "string (uuid4)",
  "strategy_id": "string (fk → StrategyDefinition)",
  "eval_id": "string (fk → ConditionEvaluation)",
  "order_submitted_at": "string (ISO 8601 datetime UTC)",
  "ticker": "string",
  "side": "string (buy | sell)",
  "qty_requested": "integer",
  "qty_filled": "integer (nullable until fill confirmed)",
  "order_type": "string (market | limit)",
  "limit_price": "number (nullable for market orders)",
  "fill_price": "number (nullable until filled)",
  "fill_datetime_utc": "string (nullable)",
  "notional_requested": "number (qty_requested × market_price at submission)",
  "notional_filled": "number (nullable)",
  "alpaca_order_id": "string (nullable — Alpaca's order UUID)",
  "order_status": "string (pending | filled | partial | cancelled | rejected | error)",
  "rejection_reason": "string (nullable)",
  "mode": "string (paper | live)",
  "confirm_required": "boolean",
  "confirmed_at": "string (nullable — set if human confirm step was required and given)"
}

4. HumanConfirmRequest

For the first execution of each strategy (and optionally for all executions if the user opts in), a confirm request is created before the order fires.

{
  "confirm_id": "string (uuid4)",
  "strategy_id": "string (fk → StrategyDefinition)",
  "eval_id": "string (fk → ConditionEvaluation)",
  "created_at": "string (ISO 8601 datetime UTC)",
  "expires_at": "string (ISO 8601 datetime UTC, default: 4 hours after creation)",
  "summary_text": "string (plain-English description of what will happen, e.g. 'Every Friday at 17:00 UTC: if SPY trades more than 0.5% below NAV, buy 10 shares at market.')",
  "condition_snapshot": {
    "market_price": "number",
    "nav_per_share": "number",
    "discount_pct": "number"
  },
  "user_action": "string (null | confirmed | rejected | expired)",
  "user_action_at": "string (nullable)"
}

5. NAVRecord

Cache of NAV data fetched from fund family sites. Point-in-time preserved for backtesting reproducibility.

{
  "nav_record_id": "string (uuid4)",
  "ticker": "string",
  "nav_date": "string (ISO 8601 date, the business day this NAV applies to)",
  "nav_per_share": "number (USD)",
  "source": "string (spdr_site | ishares_site | vanguard_site | etf_dot_com | manual)",
  "fetched_at": "string (ISO 8601 datetime UTC)",
  "is_estimated": "boolean (true for iNAV; false for official end-of-day NAV)"
}

Retention: NAV records are append-only; never overwritten. If a corrected NAV is published, a new record is inserted with the correction flag.

6. ParseAttempt

Audit log of every call to the Claude DSL parse endpoint. Supports debugging, cost tracking, and hallucination analysis.

{
  "parse_id": "string (uuid4)",
  "user_id": "string (uuid4)",
  "nl_input": "string (verbatim user input)",
  "prompt_tokens": "integer",
  "completion_tokens": "integer",
  "model": "string (e.g. 'claude-haiku-3-5')",
  "latency_ms": "integer",
  "raw_response": "object (full structured output from Claude)",
  "parsed_dsl": "object (nullable — null if parse failed)",
  "confidence": "number (0.0–1.0)",
  "error_code": "string (nullable: parse_failed | unsupported_metric | ambiguous_action | ...)",
  "error_message": "string (nullable)",
  "attempted_at": "string (ISO 8601 datetime UTC)",
  "strategy_id_created": "string (nullable — set if this parse resulted in a saved strategy)"
}

7. Backtest Run Artifact Manifest

Every backtest run produces the following files under docs/data-science/backtests/etf-nav-discount/<YYYY-MM-DD-run-id>/: