Feature/Project Proposal
Overhaul the data retrieval layer for analytics and progress tracking by utilizing advanced MongoDB Aggregation Pipelines.
Problem Statement
Progress tracking in sessionController.js and userSheetProgressController.js currently relies on inefficient client-side filtering or multiple round-trip MongoDB queries (N+1 query problem). As collections grow, these linear scans and multiple .find() operations will cause severe memory bloat and API latency.
Proposed Solution
- Replace multiple Mongoose
.find() calls with advanced MongoDB Aggregation Pipelines ($match, $lookup, $group, $project) to compute statistics directly in the database engine.
- Implement compound indexes (e.g.,
{ userId: 1, sheetId: 1, status: 1 }) via a new setupIndexes.js migration script to optimize frequent query paths.
- Ensure all bulk read operations use
.lean() for faster execution and lower memory footprint.
Alternatives Considered
We could cache the stats in Redis, but that requires complex invalidation logic. Real-time DB aggregation over properly indexed fields is more reliable for dynamic user progress data.
Benefits
Reduces API latency for dashboard metrics by >80%, significantly drops server memory consumption, and ensures the database scales gracefully as user data grows into the millions of rows.
Priority
High
Additional Context
N/A
Checklist
Feature/Project Proposal
Overhaul the data retrieval layer for analytics and progress tracking by utilizing advanced MongoDB Aggregation Pipelines.
Problem Statement
Progress tracking in
sessionController.jsanduserSheetProgressController.jscurrently relies on inefficient client-side filtering or multiple round-trip MongoDB queries (N+1 query problem). As collections grow, these linear scans and multiple.find()operations will cause severe memory bloat and API latency.Proposed Solution
.find()calls with advanced MongoDB Aggregation Pipelines ($match,$lookup,$group,$project) to compute statistics directly in the database engine.{ userId: 1, sheetId: 1, status: 1 }) via a newsetupIndexes.jsmigration script to optimize frequent query paths..lean()for faster execution and lower memory footprint.Alternatives Considered
We could cache the stats in Redis, but that requires complex invalidation logic. Real-time DB aggregation over properly indexed fields is more reliable for dynamic user progress data.
Benefits
Reduces API latency for dashboard metrics by >80%, significantly drops server memory consumption, and ensures the database scales gracefully as user data grows into the millions of rows.
Priority
High
Additional Context
N/A
Checklist