PPLX-35 - trckr

← PPLX-35

feature,backend

Created: 2025-12-21 • Updated: 2025-12-22

Relationships Loading...

Add backend endpoint to retrieve aggregated billing/usage statistics.

## Implementation
- Create /api/usage/summary endpoint in new backend/routers/usage.py
- Return: total_cost, total_tokens, query_count, breakdown by model
- Support optional query params: start_date, end_date, model, thread_id
- Add SQL aggregation queries in crud.py

## Response Schema
```json
{
  "total_cost_usd": 0.0007,
  "total_tokens": 7011,
  "query_count": 13,
  "breakdown_by_model": {
    "sonar": {"cost": 0.0005, "tokens": 5000, "count": 10},
    "sonar-pro": {"cost": 0.0002, "tokens": 2011, "count": 3}
  }
}
```

## Files to Create/Modify
- backend/routers/usage.py (new)
- backend/crud.py (add aggregation functions)
- backend/main.py (register router)
- backend/schemas.py (add response schemas)

## Acceptance Criteria
- GET /api/usage/summary returns aggregate stats
- Date range filtering works correctly (start_date, end_date params)
- Model filtering works correctly (model param)
- Thread filtering works correctly (thread_id param)
- Returns proper error codes for invalid inputs (400, 404)
- All data matches APIUsageLog table

Pull Requests (comma-separated URLs)

Attachments

Comments (4)

agent · 2025-12-22

[QA] ✅ VERIFIED - All acceptance criteria met. 12/12 endpoint tests pass, 391/391 total tests pass. Live API testing confirms correct behavior for all scenarios (empty data, authentication, date validation, thread validation, model filtering). Database migrations applied successfully. Implementation is production-ready. Full report: /tmp/qa-PPLX-35.md

agent · 2025-12-22

Fixed database schema mismatch by running alembic migrations. **Root Cause**: The database was created from SQLAlchemy models directly but hadn't been initialized with alembic version tracking. When incremental migrations (like a534488c3a48 for cost breakdown columns) were added, they couldn't be applied. **Fix Applied**: 1. Stamped the database at the initial migration: `alembic stamp cc9d2f3bc955` 2. Applied all pending migrations: `alembic upgrade head` 3. This added the missing columns: `input_tokens_cost`, `output_tokens_cost`, `request_cost`, `api_total_cost` **Verification**: - All 391 tests pass - Endpoint tested via curl with cookie auth - returns proper response - Date range filtering validated (400 for invalid range) - Model filtering works - Thread filtering works (404 for nonexistent thread) **Note for deployment**: Run `alembic upgrade head` on any database that was created before migrations were added.

agent · 2025-12-22

[QA] FAILED - Database schema mismatch Code: ✅ Excellent (12/12 tests pass, 391 total pass) Production: ❌ FAILED - Missing DB columns Error: sqlite3.OperationalError: no such column: api_usage_log.input_tokens_cost Missing columns from migration a534488c3a48: - input_tokens_cost - output_tokens_cost - request_cost - api_total_cost FIX: Run alembic upgrade head before deployment Full report: /tmp/qa-PPLX-35.md

agent · 2025-12-22

Implementation complete. Created GET /api/usage/summary endpoint with date/model/thread filtering. Added get_usage_summary CRUD function and UsageSummaryResponse schema. 12 new tests, all 391 tests pass. Merged to main.