← Back to Dashboard

Database Directives

MAIN DIRECTIVE: No Tech Debt in Database

Created: 2026-01-06
Priority: Critical

The database must be future-proof and reliable. All queries must work correctly without workarounds. The database will be used for years.

Key Principles

1. Proper Data Categorization

2. Source of Truth

Earnings Market Data: - earnings_markets table is the source of truth for earnings market data - Contains properly extracted: ticker, earnings_date, earnings_time, market_cap_category (Large / Mid / Small) - Synced by the hourly Gamma earnings_market_fetcher; earnings_time (BMO/AMC) is resolved in Python from NASDAQ when explicit, else earnings_timing.py DEFAULT_TIMINGS + timing_data/overrides.json only — not yfinance/web fallback (those remain runtime-only in SignalGenerator; Finding #220; amended by #349: a ≥2-vote calendar consensus from the multi-source verifier may also correct date+timing, and verifier-confirmed rows — date_confidence='confirmed' — are locked against ALL Polymarket-derived sync overwrites; changes audited in earnings_date_audit). Market cap from KNOWN_MCAPS, then Yahoo/Finnhub via yfinance_limiter, persisted by backfill_upcoming_null_mcaps() after each live fetch (Finding #219). Finding #480 retires run_full_sync / sync_from_markets_table and the manual wrapper before DB access because that legacy writer could change a date without the mandatory audit row.

Live Position State (Phase 4B+4C - Feb 7, 2026): - IBKR is the source of truth for live position state (is a position currently open?) - IBKRPositionCache (5s TTL) provides thread-safe cached access - trading_positions table is the source of truth for historical data (P&L, audit trail, crash recovery) - All DB writes remain as complete audit trail - they are never removed - When IBKR is disconnected, ALL trading operations are blocked

Outcome resolution writers (Finding #227 — Apr 21-22; coverage updates Apr 26): - earnings_markets.actual_beat / eps_actual / revenue_actual / stock_price_* / stock_return_* — single writer is src/data_tracker/collectors/outcome_resolver.py (run by polymarket-resolver.timer daily 06:00 ET). The trading orchestrator may pre-fill actual_beat on instant-exit detection (faster path); the resolver back-fills the rest and is idempotent (COALESCE(... IS NULL)). - actual_beat source cascade (Finding #227 update Apr 22; #244/#246 update Apr 26): 1. EPS surprise (eps_actual >= eps_estimate) — Yahoo → Finnhub → Polygon /vX/reference/financials 2. Post-event Polymarket yes_price from price_snapshots >12h after earnings_date (≥0.95/≤0.05) 3. Pre-event Polymarket prob cascade (prob_30min_before → prob_1h → ... → prob_24h) Coverage: 73% (Apr 22) → 84.1% (Apr 26 #244 — --force bypasses the 6-hour updated_at guard the hourly sync was tripping) → 98.1% (Apr 26 #246 — post-event price_snapshot backfill via Polymarket data-api) → ~95-99.8% current (Finding #321, May 2026 — 948/961 past-settled resolved) → decayed back to 76.0% by Aug 16, 2026 (the #244 lockout was never structurally fixed; the hourly sync kept re-locking unresolved rows — populations.md §1.2) → structurally fixed Aug 21, 2026 (Finding #371, A-2): the guard now keys on the resolver-OWNED resolver_attempted_at (migration 033), which no other writer touches; backlog self-heals on normal timer fires. Remaining unresolved are bounded by post-event PM-trade availability + ambiguous mid-band; this is no longer a fixed "14 irreducible" set (the earlier "14 irreducible" figure was superseded by #321). - earnings_markets.actual_earnings_time (NEW Apr 26 — Migration 026, Finding #247) — populated from SEC EDGAR submissions/{cik}.json acceptedDate. Coverage 90.9% (667/734 past-settled). Refines simulator's ±90-min release-window heuristics. Backfill: scripts/backfill_press_release_times_from_sec.py. Weekly self-heal in polymarket-data-backfill.timer (5th ExecStart pass). - skipped_signals.actual_outcome / would_have_won / hypothetical_pnl[_pct] / hypothetical_stop_hit — single writer is the resolver (Pass 2). Inserts come from signal_generator.log_skipped_signal. - polymarket_opportunity_log.hypothetical_* — single writer is the resolver (Pass 3); operator can also run scripts/compute_counterfactual_pnl.py for ad-hoc batches. Now also wired into polymarket-data-backfill.timer weekly so new rows get counterfactual P&L automatically (Finding #238, Apr 26). - polymarket_opportunity_log.snapshot (JSONB) — pm_v2 schema since Apr 26 (Finding #243): captures pm_best_bid, pm_best_ask, pm_spread, pm_bid_depth, pm_ask_depth, snapshot_schema='pm_v2'. Note: schema label fires on every new row, but value-add fields are NULL on signal_emitted rows pending Phase 3 fix (Finding #273, documented Apr 27 evening). - trade_audit_log.prediction_correct / actual_earnings_result — derived by resolver Pass 4 by joining to earnings_markets.actual_beat once that's known. Also backfilled one-shot Apr 27 evening via SQL (Finding #264 — VZ + 22 historical trades). - trade_audit_log.regime_at_entry / regime_details / vix_at_entry / sp500_change_at_entry — written by position_manager._persist_position_open on entry using the shared MarketRegimeProvider (mandatory non-null on new rows starting Apr 22, 2026; historical rows backfilled by scripts/backfill_regime_for_audit_log.py). Bug fix Apr 26 — Finding #234 — restored the orchestrator INSERT path that was silently dropping these fields for 5/116 trades. - trade_audit_log.conviction_at_exit — added on close path Apr 26 (Finding #237). Forward-only — historical NULL. - trade_audit_log.hold_duration_hours — Apr 27 morning (#256) added writer; Apr 27 evening (#263) discovered the writer's pg8000 syntax error and re-fixed with CAST(... AS timestamptz). Backfilled VZ #120 + 22 trades via SQL. - trade_audit_log.volume_24h_at_entry — Polymarket Gamma's volume24hr field has been intermittently NULL since ~Feb 16, 2026 (Finding #272 RCA Apr 27). signal_generator.get_market_volume() now falls back to most-recent positive price_snapshots.volume_24h within last 6h. Backfilled 114/117 (97%) historical rows. - polymarket_opportunity_log / skipped_signals intended_position_value / intended_shares / intended_stop_price / intended_flex_applied / regime_at_entry / vix_at_entry / sp500_change_at_entry — written by signal_generator at insert time (forward strategy-replay capture, no later reconstruction needed).

BUG_EXIT convention on trade_audit_log.notes (Finding #276, Apr 27, 2026):

When a trade was force-exited by a since-fixed bug, the exit_reason column is left intact as the historical record of WHAT the trader did, but the notes column is annotated with a structured prefix so future analyses can exclude these polluting rows from exit-reason aggregations:

The dashboard trade-history serializer (earnings_dashboard/app.py:get_trade_history) detects this prefix, prefixes exit_reason_display with 🐛 BUG-EXIT ·, and surfaces a bug_exit_flagged: bool field on the API response so JS / analyses can filter without parsing the prefix string.

3. Sync Process

4. No API Workarounds

Database Schema

earnings_markets (Source of Truth)

CREATE TABLE earnings_markets (
    id SERIAL PRIMARY KEY,
    condition_id VARCHAR(66) NOT NULL UNIQUE,
    ticker VARCHAR(10) NOT NULL,
    company_name VARCHAR(255),
    market_cap_category VARCHAR(20),  -- 'Large', 'Mid', 'Small'
    earnings_date DATE NOT NULL,
    earnings_time VARCHAR(10),         -- 'BMO', 'AMC', 'TBD'
    fiscal_quarter VARCHAR(10),
    fiscal_year INTEGER,
    -- ... probability snapshots for analysis ...
    created_at TIMESTAMP,
    updated_at TIMESTAMP
);

CREATE INDEX idx_earnings_date ON earnings_markets(earnings_date);
CREATE INDEX idx_earnings_ticker ON earnings_markets(ticker);

Indexes for Performance

Sync Architecture

┌─────────────────┐     ┌──────────────────┐     ┌──────────────────┐
│ markets table   │────>│ EarningsMarketSync│────>│ earnings_markets │
│ (raw Polymarket)│     │ (parse & enrich) │     │ (clean, indexed) │
└─────────────────┘     └──────────────────┘     └──────────────────┘
                               │
                               v
                    ┌──────────────────────────────┐
                    │ Timing: NASDAQ → BMO/AMC      │
                    │ else DEFAULT_TIMINGS/override│
                    │ (no web → DB; Finding #220)   │
                    └──────────────────────────────┘
                               │
                               v
                    ┌──────────────────────────────┐
                    │ Yahoo + Finnhub (limiter)     │
                    │ mcap / company fallbacks     │
                    └──────────────────────────────┘
                              │
                              v
                    ┌──────────────────┐
                    │ backfill NULL    │
                    │ market_cap_*     │
                    │ (Finding #219)   │
                    └──────────────────┘
                              │
                              v
                    ┌──────────────────┐
                    │ backfill NULL/   │
                    │ TBD earnings_time│
                    │ (Finding #220)   │
                    └──────────────────┘

Research: polymarket_opportunity_log (Migration 021, Finding #221)

Append-only log of every Polymarket-linked earnings candidate evaluated on the BMO/AMC signal path (SignalGenerator), including rejects (timing, depth, conviction, generate_signal, priority filter, calendar misses). Rows include live-rule entry_datetime_scheduled, exit_datetime_scheduled, and stop_loss_pct when BMO/AMC and earnings datetime are known—so counterfactual P&L jobs can run over all stages without re-deriving the calendar.

Troubleshooting

Missing Markets

If markets are missing from dashboard: 1. Check earnings_markets table: SELECT COUNT(*) FROM earnings_markets WHERE earnings_date >= CURRENT_DATE 2. Start the live oneshot: systemctl start polymarket-earnings-sync.service 3. Check logs: tail -f /var/log/polymarket/earnings_sync.log

Stale Data

Why Jan 21+ Has No Markets

Polymarket creates earnings markets 1-2 weeks before the earnings date. This is expected behavior - markets will appear as dates approach.

Commands

# Run supported sync immediately
systemctl start polymarket-earnings-sync.service

# Check sync logs
tail -f /var/log/polymarket/earnings_sync.log

# Verify earnings sync timer (hourly systemd; NOT crontab)
systemctl list-timers polymarket-earnings-sync.timer --no-pager

# Check database stats
python3 scripts/check_earnings_db.py

Historical Note

Before this fix (pre-2026-01-06): - Relied on Polymarket's tag=Earnings API filter - This filter was broken and returned sports/crypto markets - Dashboard code had complex workarounds parsing question text - Data was inconsistent and unreliable

After this fix: - Dedicated earnings_markets table with proper schema - Automated sync process keeps data fresh - Dashboard uses clean database queries - No workarounds, no tech debt