All Stories
2-3-sidebar-materialized-viewDoneEpic 2.3
Story 2.3: Sidebar Materialized View for <100ms Loads
Status: done
Tasks
- Task 1: Create Materialized View Migration (AC: #1)
- 1.1: Create Supabase migration for `sidebar_data` materialized view
- 1.2: Add unique index `idx_sidebar_data_sport_league`
- 1.3: Verify materialized view populates correctly (7 sports confirmed)
- 1.4: Document SQL in migration with comments explaining prioritization logic
- Task 2: Implement Refresh Mechanism (AC: #2)
- 2.1: Create `refresh_sidebar_data()` PostgreSQL function with alerting
- 2.2: Implement APScheduler background task for 5-minute refresh cycle
- 2.3: Add refresh duration logging to `sidebar_refresh_log` table
- 2.4: Create admin endpoint `POST /api/v1/shared/sidebar/refresh` for manual trigger
- Task 3: Build Sidebar API Endpoint (AC: #3, #5)
- 3.1: Create `GET /api/v1/shared/sidebar` endpoint in FastAPI
- 3.2: Implement Redis cache check before database query
- 3.3: Add fallback to direct `odds_events` table query if view unavailable
- 3.4: Structure response with sport groups and leagues hierarchy
- Task 4: Redis Caching Integration (AC: #4)
- 4.1: Use existing Redis connection from `app.core.redis_cache`
- 4.2: Implement cache get/set with 300s TTL for sidebar data
- 4.3: Add cache invalidation on manual refresh
- 4.4: Add cache hit/miss logging for observability
- Task 5: Testing and Validation (AC: #6)
- 5.1: Verified materialized view query returns correct data (7 sports, 341 games)
- 5.2: Tested refresh function (17ms-74ms duration, well under 30s threshold)
- 5.3: Confirmed endpoint registration and response transformation
- 5.4: Verified scheduler starts and runs refresh automatically
- Task 6: Documentation and Monitoring (AC: #2, #6)
- 6.1: Created `sidebar_refresh_log` table for monitoring
- 6.2: Added `/api/v1/shared/sidebar/stats` endpoint for performance monitoring
- 6.3: Configured alert_triggered flag for refresh duration >30s
Progress
Tasks6/6
Acceptance Criteria0
Total Tasks6