Plan: Backend usage routes accept data but don't persist

[sbaker]

Overview

The backend usage routes (/api/usage/sync, /api/usage/summary, /api/usage/stats) already accept data and have validation logic, but the three TODO comments at lines 60, 90, and 121 show they don't persist anything. The frontend's usageTracker.ts is already sending batched execution records. This plan creates a Usage Mongoose model following the ExecutionHistory pattern, wires up persistence in the sync route, and implements the summary/stats query routes with proper auth middleware.

Steps

1. Create Usage model - New file backend/src/models/Usage.js

   • Schema fields from frontend's ExecutionRecord: userId (required, indexed), executionId (unique identifier from frontend), timestamp, provider, model, promptTokens, completionTokens, totalTokens, duration, success, error, executionMode, context
   • Indexes: userId + timestamp (compound, for date-range queries), provider (for breakdown queries), executionId (unique, for dedup on re-sync)
   • 90-day TTL index on createdAt (matches ExecutionHistory GDPR pattern)
   • Enable { timestamps: true }
   • Follow ExecutionHistory.js model structure

2. Add auth middleware to usage routes - Modify backend/src/routes/usage.js

   • Import auth middleware from ../middleware/auth.js
   • Apply auth to all three routes (POST sync, GET summary, GET stats)
   • Use req.user._id as userId for all database operations

3. Implement POST /api/usage/sync persistence (line 60) - Modify backend/src/routes/usage.js

   • Replace the TODO with Usage.insertMany() using the sanitized records
   • Add userId: req.user._id to each record
   • Use executionId for upsert/dedup (skip records that already exist)
   • Keep existing validation and sanitization logic intact
   • Return count of actually inserted records (not duplicates)

4. Implement GET /api/usage/summary (line 90) - Modify backend/src/routes/usage.js

   • Query Usage collection filtered by userId and optional startDate/endDate
   • Aggregate: total executions, total tokens (prompt + completion), breakdown by provider, breakdown by day
   • Use MongoDB aggregation pipeline with $match, $group, $sort
   • Return the UsageSummary shape the frontend expects

5. Implement GET /api/usage/stats (line 121) - Modify backend/src/routes/usage.js

   • Query Usage collection for today, this week, this month windows
   • For each window: count executions, sum tokens, count errors
   • Use $facet aggregation for single query across all three windows
   • Return the stats shape the frontend expects

Key Files

File	Role
backend/src/models/Usage.js	New - Mongoose model for usage records
backend/src/routes/usage.js	Modify - Wire up persistence in 3 route handlers
backend/src/models/ExecutionHistory.js	Reference - Model pattern (schema, indexes, TTL)
backend/src/middleware/auth.js	Reference - Auth middleware to import
frontend/src/modules/services/usageTracker.ts	Reference - Frontend data shape (ExecutionRecord)

Reference Patterns

• Model structure: ExecutionHistory.js - userId index, token fields, timestamps, 90-day TTL
• Auth middleware: Used in other route files - import { auth } from '../middleware/auth.js' then router.post('/path', auth, handler)
• Aggregation: MongoDB $group/$facet for multi-window stats queries
• Dedup pattern: Use executionId with ordered: false on insertMany and catch duplicate key errors gracefully

Estimate

1 new file, 1 modified file. Medium complexity (aggregation pipelines need careful construction).