Skip to main content

Reporting MVP for Evalium

This MVP focuses on delivering fast, reliable reporting for admins by leveraging a read-only replica, pre-computed aggregates, and lightweight caching—ensuring sub-second dashboards even as data grows.

1. Read-Replica Setup​

  • Streaming Replication
    • Stand up a managed PostgreSQL read-replica (AWS RDS, GCP Cloud SQL, etc.) of the primary OLTP database.
    • Configure replication slots to guarantee zero data loss and sub-second lag.
  • Read-Only Connections
    • Point all reporting queries (dashboards, exports) at the replica endpoint.
    • Keep the primary write-only for assignments, submissions, and candidate flows.

2. Core Dashboards on Replica​

  • Evaluation Summary Panel
    • Total invites vs. completions, pass rate, avg. score, median time-to-complete, daily trend sparkline.
  • Candidate Drill-Down
    • Per-candidate score tables, time per question, link to question-level performance.
  • Segment Comparison View
    • Filter by department/role/tag/date → side-by-side pass-rate & completion charts.

All dashboards query the read-replica directly, isolating heavy aggregates from the primary database.

3. Materialized Views & Aggregate Tables​

  • Pre-Aggregated Views
    • report_summary (evaluation_id → total, avg_score, pass_rate)
    • daily_trends (evaluation_id + date → invites, completions)
  • Concurrent Refresh Job
    • Schedule REFRESH MATERIALIZED VIEW CONCURRENTLY every 5 minutes via app scheduler.
    • Index the materialized views on filter keys (evaluation_id, date).

4. In-Memory Caching​

  • Redis Cache Layer
    • Cache “top KPI” endpoints (overall pass rate, last-24h sparkline) with a short TTL (10–30s).
    • Serve cache hits instantly (<5ms), falling back to replica on miss.
  • Client-Side Debounce & Cache
    • Debounce faceted filter inputs (300ms) to batch rapid changes.
    • Cache identical API queries in-memory for the duration of an admin session.

5. Smart Indexing & Partitioning​

  • Partition Submissions by Date
    • Monthly partitions on the submissions table to prune scan ranges.
  • Covering Indexes
    • Composite indexes on (evaluation_id, completed_at, score) and other common filter columns.
  • Automated Index Advisor
    • Enable PostgreSQL’s auto_explain or use a simple cron to log slow queries and propose missing indexes.

6. UI-Level Optimizations​

  • Server-Driven Pagination & Cursors
    • All tables and export endpoints support cursor pagination to avoid large payloads.
  • Virtualized Scrolling
    • Use windowing (e.g. React-Window) to render only visible rows in large tables.
  • Lightweight Charting
    • Lazy-load charts and sparklines; only fetch data when the chart component enters viewport.

MVP Scope & Timeline (Weeks 1–4)​

WeekDeliverable
1Stand up read-replica; configure connection pool for reporting
2Build core dashboards querying replica; simple faceted filters
3Create materialized views + concurrent refresh jobs
4Integrate Redis cache; add UI debouncing & virtualized lists

By the end of Week 4, admins will enjoy near-instant reporting—charts and tables loading in under 200 ms—while keeping the primary application responsive and scalable.