[Resources] Back-fill resource_usage from existing data

[Resources] Back-fill resource_usage from existing data

Goal

Populate the resource_usage table with historical data from existing debate_rounds.tokens_used and agent_performance.tokens_used columns. Calculate cost estimates using AWS Bedrock pricing for Claude models (Sonnet, Haiku). Enable the /resources (Costs) page to display real usage data instead of placeholder values.

Acceptance Criteria

Approach

Create spec file

Examine database schema:

- Check resource_usage table structure
- Check debate_rounds table for tokens_used column
- Check agent_performance table for tokens_used column

Check current /resources page implementation in api.py

Write backfill script (migrations/backfill_resource_usage.py):

- Extract token usage from debate_rounds
- Extract token usage from agent_performance
- Calculate costs using Bedrock pricing
- Insert into resource_usage table
- Handle duplicates (idempotent)

Test backfill script on current database

Update /resources page if needed to query resource_usage table

Verify /resources page shows accurate real data

Commit and push

Mark task complete

Bedrock Pricing (as of 2026)

• Claude Sonnet: $3 per 1M input tokens, $15 per 1M output tokens
• Claude Haiku: $0.25 per 1M input tokens, $1.25 per 1M output tokens
• Assume 80/20 split (input/output) if not tracked separately

Work Log

2026-04-02 09:50 UTC — Slot 10

• Started task: Back-fill resource_usage from existing data
• Created spec file
• Examined database schema

Database Analysis:

• resource_usage table exists with schema: resource_type, amount, artifact_type, artifact_id, description, created_at
• debate_rounds table: 72 rows with tokens_used > 0 (143,714 total tokens)
• agent_performance table: 52+ rows with tokens_used > 0 (381,370+ total tokens)
• /resources page already displays real data by querying debate_rounds/agent_performance directly

Issue Found:

• Existing scripts/backfill_resource_usage.py expected different schema (entity_type, entity_id, metadata, cost_usd columns)
• Schema mismatch between script and actual table

Solution:

• Updated backfill script to match actual resource_usage schema
• Used artifact_type/artifact_id instead of entity_type/entity_id
• Embedded cost estimates and metadata in description field as JSON
• Cost calculations: Sonnet $5.1/MTok (weighted 70/30 I/O), Haiku $0.55/MTok

Implementation:

• Modified backfill_debate_rounds() to use correct schema
• Modified backfill_agent_performance() to use correct schema
• Updated verify_results() to query new schema
• Simplified create_summary_view() to create resource_usage_by_day view

Testing:

• First run: Inserted 72 debate_rounds entries + 52 agent_performance entries = 124 total
• Total tokens tracked: 525,084
• Verification: Source totals match backfilled totals (100% accurate)
• Second run (idempotency test): Skipped all 72+52 existing entries, only added 1 new entry from concurrent agent activity
• Result: Script is fully idempotent ✓

Database State:

artifact_type | count | total_tokens
--------------+-------+-------------
analysis      | 72    | 143,714
agent_run     | 53    | 383,675
--------------+-------+-------------
TOTAL         | 125   | 527,389

Created:

• View: resource_usage_by_day (daily aggregates by artifact_type and resource_type)

Result: Task complete

• All 125 historical resource usage records backfilled
• 527,389 tokens tracked with cost estimates
• Script is idempotent and safe to re-run
• /resources page continues to display real data (no changes needed)
• resource_usage table now has unified audit log of all LLM token consumption

File: q06-r1-88766CBD_resources_backfill_resource_usage_spec.md

Modified: 2026-04-24 07:15

Size: 4.5 KB