Phase 3-A: Add Claude Usage Snapshot DB Model + Migration #46

New Issue

Open

opened 2026-05-24 16:55:18 -05:00 by null · 0 comments

null commented

2026-05-24 16:55:18 -05:00

Owner

Source plan: /home/kaspa/.claude/plans/with-our-backend-created-precious-owl.md

Feature context: Feature 3: Daily Cost Snapshot (Historical Trending)

Scope

Phase 3-A — Backend, Batch 1: DB Model + Migration

Summary

Add a ClaudeUsageSnapshot database model and Alembic migration to persist daily cost and token totals.

Problem

Cost data is recalculated fresh from JSONL on every API request. There is no historical record — impossible to chart "how much did I spend this week vs last week."

Affected area

Database: new table claude_usage_snapshots
Backend service: models, migration

Affected files

backend/app/models/claude_usage.py — new file, ClaudeUsageSnapshot SQLModel
backend/alembic/versions/XXXX_add_claude_usage_snapshots.py — new migration

Expected behavior

Table claude_usage_snapshots with columns:

id (uuid pk)
snapshot_date (date, unique per org — one row per day)
org_id (fk to organizations)
total_cost_usd (float)
total_tokens (int)
session_count (int)
per_model (JSON — {"claude-sonnet-4-6": {"cost": 1.23, "tokens": 45000}, ...})
created_at (datetime)

Steps to reproduce (acceptance criteria)

Run alembic upgrade head — no errors
Table exists in DB with correct columns
Model imports cleanly in Python

Source plan: `/home/kaspa/.claude/plans/with-our-backend-created-precious-owl.md` Feature context: **Feature 3: Daily Cost Snapshot (Historical Trending)** ## Scope ### Phase 3-A — Backend, Batch 1: DB Model + Migration #### Summary Add a `ClaudeUsageSnapshot` database model and Alembic migration to persist daily cost and token totals. #### Problem Cost data is recalculated fresh from JSONL on every API request. There is no historical record — impossible to chart "how much did I spend this week vs last week." #### Affected area - Database: new table `claude_usage_snapshots` - Backend service: models, migration #### Affected files - `backend/app/models/claude_usage.py` — new file, `ClaudeUsageSnapshot` SQLModel - `backend/alembic/versions/XXXX_add_claude_usage_snapshots.py` — new migration #### Expected behavior Table `claude_usage_snapshots` with columns: - `id` (uuid pk) - `snapshot_date` (date, unique per org — one row per day) - `org_id` (fk to organizations) - `total_cost_usd` (float) - `total_tokens` (int) - `session_count` (int) - `per_model` (JSON — `{"claude-sonnet-4-6": {"cost": 1.23, "tokens": 45000}, ...}`) - `created_at` (datetime) #### Steps to reproduce (acceptance criteria) 1. Run `alembic upgrade head` — no errors 2. Table exists in DB with correct columns 3. Model imports cleanly in Python ---