================================================================================
TASK 1: SCHEMA CONSUMERS — Complete Table Consumer Map
Date: 2026-05-30
================================================================================

## TABLE: `usageHistory` — All Consumers
================================================================================

### WRITERS (INSERT INTO usageHistory)
================================================================================

1. `src/lib/db/repos/usageRepo.js:258` — saveRequestUsage()
   INSERT INTO usageHistory(timestamp, provider, model, connectionId, apiKey,
   endpoint, promptTokens, completionTokens, cost, status, tokens, meta)

   Called by:
   ├── open-sse/utils/usageTracking.js:345 — logUsage()
   │   └── open-sse/utils/stream.js:289 — (SSE proxy streaming handler)
   │   └── open-sse/utils/stream.js:359 — (SSE proxy streaming handler)
   └── open-sse/handlers/chatCore/requestDetail.js:93 — saveUsageStats()

2. `src/lib/db/migrate.js:177` — importLegacyUsage()
   INSERT INTO usageHistory(...) — one-time legacy JSON migration

### READERS (SELECT FROM usageHistory)
================================================================================

1. `src/lib/db/repos/usageRepo.js:104` — ensureRingInitialized()
   SELECT ... FROM usageHistory ORDER BY id DESC LIMIT 50
   → Ring buffer for recent requests display

2. `src/lib/db/repos/usageRepo.js:300` — getUsageHistory()
   SELECT ... FROM usageHistory [WHERE ...] ORDER BY id ASC
   → Exported function, used for filtered history queries

3. `src/lib/db/repos/usageRepo.js:345` — getUsageStats()
   SELECT ... FROM usageHistory ORDER BY id DESC LIMIT 100
   → Recent requests for stats display

4. `src/lib/db/repos/usageRepo.js:404` — getUsageStats()
   SELECT ... FROM usageHistory WHERE timestamp >= ? AND timestamp <= ?
   → Last 10 minutes bucket for real-time display

5. `src/lib/db/repos/usageRepo.js:508` — getUsageStats()
   SELECT ... FROM usageHistory WHERE timestamp >= ?
   → Overlay precise lastUsed timestamps

6. `src/lib/db/repos/usageRepo.js:542` — getUsageStats()
   SELECT ... FROM usageHistory WHERE timestamp >= ?
   → 24h/today period live history

7. `src/lib/db/repos/usageRepo.js:635` — getChartData()
   SELECT ... FROM usageHistory WHERE timestamp >= ?
   → Today chart buckets

8. `src/lib/db/repos/usageRepo.js:658` — getChartData()
   SELECT ... FROM usageHistory WHERE timestamp >= ?
   → 24h chart buckets

9. `src/lib/db/repos/usageRepo.js:705` — getRecentLogs()
   SELECT ... FROM usageHistory ORDER BY id DESC LIMIT ?
   → Recent log entries

10. `src/lib/db/repos/rateLimitRepo.js:59` — getMonthlyTokens()
    SELECT SUM(promptTokens + completionTokens) FROM usageHistory
    INNER JOIN apiKeys ON usageHistory.apiKey = apiKeys.key
    WHERE apiKeys.userId = ?
    → Monthly token count for rate limiting

11. `src/lib/db/repos/rateLimitRepo.js:73` — getMonthlyCost()
    SELECT SUM(cost) FROM usageHistory
    INNER JOIN apiKeys ON usageHistory.apiKey = apiKeys.key
    WHERE apiKeys.userId = ?
    → Monthly cost for credit/quota limiting

12. `src/lib/auth/rateLimitMiddleware.js:78` — checkRTPM()
    SELECT COUNT(*) FROM usageHistory
    INNER JOIN apiKeys ON usageHistory.apiKey = apiKeys.key
    WHERE apiKeys.userId = ? AND timestamp >= ?
    → Requests per minute rate limit check

13. `src/lib/auth/rateLimitMiddleware.js:97` — checkRTPD()
    SELECT COUNT(*) FROM usageHistory
    INNER JOIN apiKeys ON usageHistory.apiKey = apiKeys.key
    WHERE apiKeys.userId = ? AND timestamp >= ?
    → Requests per day rate limit check

### API ENDPOINTS CONSUMING usageHistory (via usageRepo.js)
================================================================================

- GET /api/usage/stats    → getUsageStats()     → src/app/api/usage/stats/route.js:17
- GET /api/usage/stream   → getUsageStats()      → src/app/api/usage/stream/route.js:22
- GET /api/usage/chart    → getChartData()        → src/app/api/usage/chart/route.js:15
- GET /api/usage/history  → getUsageStats()       → src/app/api/usage/history/route.js:6
- GET /api/usage/logs     → getRecentLogs()       → src/app/api/usage/logs/route.js:6
- GET /api/usage/request-logs → getRecentLogs()   → src/app/api/usage/request-logs/route.js:6

### UI COMPONENTS CONSUMING usageHistory data
================================================================================

- src/shared/components/UsageStats.js — Overview tab (stats + charts)
  Fetches: /api/usage/stats, /api/usage/stream
- src/app/(dashboard)/dashboard/usage/components/UsageTable.js
- src/app/(dashboard)/dashboard/usage/components/UsageChart.js

================================================================================
## TABLE: `usage_records` — All Consumers
================================================================================

### WRITERS (INSERT INTO usage_records)
================================================================================

1. `src/lib/db/repos/usageRecordsRepo.js:29` — logUsage()
   INSERT INTO usage_records (id, userId, apiKeyId, model, inputTokens,
   outputTokens, totalTokens, cost, creditUsed, status, errorMessage,
   endpoint, timestamp)

   Called by:
   └── src/lib/auth/rateLimitMiddleware.js:58-68 — logUsageAsync()
       └── src/lib/auth/rateLimitMiddleware.js:215 — enforceRateLimits() (success)
       └── src/lib/auth/rateLimitMiddleware.js:230 — enforceRateLimits() (rate limited)
           └── src/dashboardGuard.js:239-240 — LLM API gate middleware

### READERS (SELECT FROM usage_records)
================================================================================

1. `src/lib/db/repos/usageRecordsRepo.js:80` — getUsageByUser()
   SELECT * FROM usage_records WHERE userId = ? [AND ...]
   → Paginated user usage records

2. `src/lib/db/repos/usageRecordsRepo.js:84` — getUsageByUser()
   SELECT COUNT(*) FROM usage_records WHERE userId = ?
   → Total count for pagination

3. `src/lib/db/repos/usageRecordsRepo.js:121` — getUsageSummary()
   SELECT SUM(inputTokens), SUM(outputTokens), ... FROM usage_records
   WHERE userId = ?
   → Aggregated usage summary

4. `src/lib/db/repos/usageRecordsRepo.js:158` — getUsageByModel()
   SELECT model, COUNT(*), SUM(totalTokens), SUM(cost)
   FROM usage_records WHERE userId = ? GROUP BY model
   → Per-model usage breakdown

5. `src/lib/db/repos/usageRecordsRepo.js:196` — getUsageDaily()
   SELECT DATE(timestamp), COUNT(*), SUM(totalTokens), SUM(cost)
   FROM usage_records WHERE userId = ? GROUP BY DATE(timestamp)
   → Daily usage trend

6. `src/lib/db/repos/usageRecordsRepo.js:235` — getAllUsersUsage()
   SELECT userId, username, COUNT(*), SUM(totalTokens), SUM(cost)
   FROM usage_records LEFT JOIN users ...
   → Admin: all users aggregate

7. `src/lib/db/repos/usageRecordsRepo.js:259` — cleanupOldRecords()
   DELETE FROM usage_records WHERE timestamp < ?
   → Cleanup old records (90 days)

### API ENDPOINTS CONSUMING usage_records (via usageRecordsRepo.js)
================================================================================

- GET /api/usage          → getUsageByUser() + getUsageSummary() + getAllUsersUsage()
                          → src/app/api/usage/route.js:47,67,75,89,97

- GET /api/usage/export   → getUsageByUser() + getUsageSummary()
                          → src/app/api/usage/export/route.js:42,176

### UI COMPONENTS CONSUMING usage_records data
================================================================================

- src/app/(dashboard)/dashboard/usage/UsageClient.jsx — Table tab
  Fetches: /api/usage (line 578)
  Uses: records, summary, totalPages, total
- src/app/(dashboard)/dashboard/usage/UsageClient.jsx — Export
  Fetches: /api/usage/export (line 542)

================================================================================
## TABLE: `usageDaily` — All Consumers
================================================================================

### WRITERS
1. `src/lib/db/repos/usageRepo.js:274` — saveRequestUsage()
   INSERT/UPDATE usageDaily(dateKey, data)
   → Same transaction as usageHistory insert

2. `src/lib/db/migrate.js:191` — importLegacyUsage()
   INSERT OR REPLACE INTO usageDaily(dateKey, data)

### READERS
1. `src/lib/db/repos/usageRepo.js:311-316` — loadDaysInRange()
   SELECT dateKey, data FROM usageDaily [WHERE dateKey >= ?]
   → Used by getUsageStats() and getChartData() for 7d/30d/60d periods

================================================================================
## TABLE: `requestDetails` — All Consumers (separate system, not disconnected)
================================================================================

### WRITERS
1. `src/lib/db/repos/requestDetailsRepo.js` — saveRequestDetail()
   Called from open-sse/handlers/chatCore/requestDetail.js

### READERS
1. `src/lib/db/repos/requestDetailsRepo.js` — getRequestDetails(), getRequestDetailById()
2. `src/app/api/usage/request-details/route.js` — GET /api/usage/request-details
3. `src/app/api/usage/providers/route.js` — GET /api/usage/providers

================================================================================
## SUMMARY: THE DISCONNECT
================================================================================

WRITES to `usageHistory`:
  ✅ SSE proxy requests (via saveRequestUsage in usageTracking.js)

WRITES to `usage_records`:
  ✅ Rate limit middleware (via logUsage in rateLimitMiddleware.js)

READS from `usageHistory`:
  ✅ Overview tab, Charts, Logs, Stream, Rate limiting checks

READS from `usage_records`:
  ❌ Table tab, Export, Admin user queries

THE GAP:
  SSE proxy requests → writes to usageHistory → Table tab reads usage_records → EMPTY
  Rate limit middleware → writes to usage_records → Overview tab reads usageHistory → MISSING

  Neither table captures ALL usage data. The two write paths are disjoint.

================================================================================
