[Phase 5] Implement by_day token usage aggregation in MetricsTracker

[frankbria]

Overview

The MetricsTracker has a TODO for daily token usage aggregation, which would enable timeline visualizations.

Current State

File: codeframe/lib/metrics_tracker.py lines 457-458

# TODO: Implement by_day aggregation (future enhancement)
# This would group usage by date for timeline visualization

Expected Behavior

• Aggregate token usage by day for time-series analysis
• Support date range queries (start_date, end_date)
• Calculate daily costs
• Enable dashboard charts showing trends over time

Implementation Requirements

1. Add get_usage_by_day() method to MetricsTracker
2. Query database with date grouping
3. Return daily aggregates with date, tokens, cost
4. Support filtering by agent, model, project

Example Usage

tracker = MetricsTracker(db=db)

# Get daily usage for last 30 days
daily_usage = tracker.get_usage_by_day(
    project_id=1,
    start_date="2025-11-01",
    end_date="2025-11-30"
)

# Result:
# [
#     {"date": "2025-11-01", "tokens": 15000, "cost_usd": 0.45},
#     {"date": "2025-11-02", "tokens": 22000, "cost_usd": 0.66},
#     ...
# ]

SQL Query

SELECT 
    DATE(timestamp) as date,
    SUM(input_tokens + output_tokens) as total_tokens,
    SUM(cost_usd) as total_cost
FROM token_usage
WHERE project_id = ? 
  AND timestamp BETWEEN ? AND ?
GROUP BY DATE(timestamp)
ORDER BY date

Acceptance Criteria

• Daily aggregation implemented
• Date range filtering supported
• Results include date, tokens, cost
• Dashboard displays daily trends chart
• Unit tests verify aggregation logic

Priority

P3 - Advanced: Nice visualization feature, not critical for core functionality.

References

• File: codeframe/lib/metrics_tracker.py lines 457-458

[traycerai]

Plan

Observations

The ticket has been re-prioritized from P3 to Future based on UX/UI committee review. The feedback indicates that detailed time-series metrics are over-engineering for v1, and total cost tracking is the priority. The MetricsTracker class already has a get_token_usage_timeseries() method that provides time-series aggregation, making the original get_usage_by_day() method redundant. The TODO at lines 466-467 should be removed rather than implemented.

Approach

Defer the daily aggregation feature to v3+ as indicated by the committee review. Remove the TODO comment from the codebase to eliminate technical debt markers for features that are intentionally deferred. The existing get_token_usage_timeseries() method already provides the necessary time-series functionality if needed in the future. Focus remains on total cost tracking for v1.

Implementation Steps

1. Remove TODO Comment from MetricsTracker

File: codeframe/lib/metrics_tracker.py (lines 466-467)

Remove the deferred TODO comment that references the by_day aggregation feature:

• Delete lines 466-467 which contain the TODO and explanation
• No new functionality is added
• This clarifies that the feature is intentionally deferred, not forgotten

2. Document Deferral in Code Comments

File: codeframe/lib/metrics_tracker.py (in get_token_usage_stats() method docstring)

Update the docstring to clarify that the by_day field is intentionally empty in v1:

• Add note in the Returns section that by_day is reserved for future use
• Reference that get_token_usage_timeseries() provides time-series data if needed
• Indicate this feature is deferred to v3+ per UX/UI committee decision

Rationale

The UX/UI committee determined that detailed daily breakdown metrics are not necessary for v1. The current implementation provides:

• Total cost tracking ✓ (primary requirement met)
• Time-series aggregation ✓ (via get_token_usage_timeseries() if needed)
• Agent and model breakdowns ✓ (via get_project_costs())

Adding daily aggregation would introduce unnecessary complexity without corresponding UI/UX benefit in v1. The feature can be implemented in v3+ when dashboard requirements evolve.

No Additional Implementation Required

This is a deferral, not an implementation. The only code change is removing the TODO comment to keep the codebase clean and intentional about deferred features.

Import In IDE

🤖 Prompt for AI Agents

## Observations

The ticket has been re-prioritized from P3 to Future based on UX/UI committee review. The feedback indicates that detailed time-series metrics are over-engineering for v1, and total cost tracking is the priority. The `MetricsTracker` class already has a `get_token_usage_timeseries()` method that provides time-series aggregation, making the original `get_usage_by_day()` method redundant. The TODO at lines 466-467 should be removed rather than implemented.

## Approach

Defer the daily aggregation feature to v3+ as indicated by the committee review. Remove the TODO comment from the codebase to eliminate technical debt markers for features that are intentionally deferred. The existing `get_token_usage_timeseries()` method already provides the necessary time-series functionality if needed in the future. Focus remains on total cost tracking for v1.

## Implementation Steps

### 1. Remove TODO Comment from MetricsTracker

**File**: `codeframe/lib/metrics_tracker.py` (lines 466-467)

Remove the deferred TODO comment that references the by_day aggregation feature:

- Delete lines 466-467 which contain the TODO and explanation
- No new functionality is added
- This clarifies that the feature is intentionally deferred, not forgotten

### 2. Document Deferral in Code Comments

**File**: `codeframe/lib/metrics_tracker.py` (in `get_token_usage_stats()` method docstring)

Update the docstring to clarify that the `by_day` field is intentionally empty in v1:

- Add note in the Returns section that `by_day` is reserved for future use
- Reference that `get_token_usage_timeseries()` provides time-series data if needed
- Indicate this feature is deferred to v3+ per UX/UI committee decision

## Rationale

The UX/UI committee determined that detailed daily breakdown metrics are not necessary for v1. The current implementation provides:

- **Total cost tracking** ✓ (primary requirement met)
- **Time-series aggregation** ✓ (via `get_token_usage_timeseries()` if needed)
- **Agent and model breakdowns** ✓ (via `get_project_costs()`)

Adding daily aggregation would introduce unnecessary complexity without corresponding UI/UX benefit in v1. The feature can be implemented in v3+ when dashboard requirements evolve.

## No Additional Implementation Required

This is a deferral, not an implementation. The only code change is removing the TODO comment to keep the codebase clean and intentional about deferred features.

---

Execution Information

Branch: main
Commit: 2e66ed3

---

💡 Tips

Supported Commands (Inside Comments)

• Use @traycerai generate to iterate on the previous version of the implementation plan.

Supported Commands (Inside Description)

• Add @traycerai ignore anywhere in the ticket description to prevent this ticket from being processed.
• Add @traycerai branch:<branch-name> anywhere in the ticket description to specify the target branch for the implementation plan.

Community

• Join our Discord Community to get help, request features, and share feedback.
• Follow us on X/Twitter for updates and announcements.

Warning

Something went wrong while processing your request.

Reference ID: d875c273-0cc4-4fef-ad81-ebbc60f922bd

Please try again or contact support if the issue persists, referencing this ID.

[traycerai]

I have updated the Implementation Plan to reflect the recent changes in the ticket.

New Plan

Observations

The codebase uses SQLite with raw SQL queries (no ORM) and follows a repository pattern. The MetricsTracker class in file:codeframe/lib/metrics_tracker.py has a TODO at lines 457-458 for daily aggregation. The TokenRepository in file:codeframe/persistence/repositories/token_repository.py already demonstrates GROUP BY aggregation patterns. The frontend dashboard component file:web-ui/src/components/metrics/TokenUsageChart.tsx is ready to consume time-series data but the backend API endpoint doesn't exist yet.

Approach

Implement daily token usage aggregation following existing patterns in the codebase. Add a new get_usage_by_day() method to MetricsTracker that delegates to a new repository method in TokenRepository. Create a new API endpoint for time-series data consumption. Update the existing get_token_usage_stats() method to populate the by_day field. Follow the established testing patterns with pytest and async fixtures.

Implementation Steps

1. Add Daily Aggregation Query to TokenRepository

File: file:codeframe/persistence/repositories/token_repository.py

Add a new method get_usage_by_day() after the existing get_project_costs_aggregate() method:

• Accept parameters: project_id (required), agent_id (optional), model_name (optional), start_date (optional), end_date (optional)
• Use SQL query with DATE(timestamp) for grouping by day
• Aggregate: SUM(input_tokens + output_tokens) as total_tokens, SUM(estimated_cost_usd) as total_cost, COUNT(*) as call_count
• Apply WHERE filters for project_id, agent_id, model_name, and date range
• Order by date ascending for timeline visualization
• Return list of dictionaries with keys: date, total_tokens, input_tokens, output_tokens, total_cost, call_count

SQL Pattern:

SELECT 
    DATE(timestamp) as date,
    SUM(input_tokens) as input_tokens,
    SUM(output_tokens) as output_tokens,
    SUM(input_tokens + output_tokens) as total_tokens,
    SUM(estimated_cost_usd) as total_cost,
    COUNT(*) as call_count
FROM token_usage
WHERE project_id = ? [AND agent_id = ?] [AND model_name = ?] 
  [AND timestamp >= ?] [AND timestamp <= ?]
GROUP BY DATE(timestamp)
ORDER BY date ASC

2. Add get_usage_by_day() Method to MetricsTracker

File: file:codeframe/lib/metrics_tracker.py

Add new async method after get_token_usage_stats():

• Method signature: async def get_usage_by_day(project_id: int, agent_id: Optional[str] = None, model_name: Optional[str] = None, start_date: Optional[datetime] = None, end_date: Optional[datetime] = None) -> List[Dict[str, Any]]
• Delegate to self.db.get_usage_by_day() with all parameters
• Return list of daily aggregates with structure matching TokenUsageTimeSeries type from frontend
• Each item should have: date (ISO string), input_tokens, output_tokens, total_tokens, cost_usd
• Add comprehensive docstring with example usage matching the issue description

3. Update get_token_usage_stats() to Populate by_day

File: file:codeframe/lib/metrics_tracker.py (lines 457-458)

Replace the TODO comment with actual implementation:

• Call self.db.get_usage_by_day(project_id, start_date=start_date, end_date=end_date)
• Assign result to result["by_day"]
• Format dates as ISO strings for consistency
• Ensure cost values are rounded to 6 decimal places

4. Add Database Delegation Method

File: file:codeframe/persistence/database.py

Add delegation method in the token usage section (after get_token_usage()):

• Method: def get_usage_by_day(self, *args, **kwargs)
• Delegate to self.token_usage.get_usage_by_day(*args, **kwargs)
• Follow the same pattern as existing delegation methods

5. Create API Endpoint for Time Series

File: file:codeframe/ui/routers/metrics.py

Add new endpoint after get_agent_metrics():

• Route: @router.get("/api/projects/{project_id}/metrics/tokens/timeseries")
• Parameters: project_id (path), start_date (query), end_date (query), interval (query, default='day'), agent_id (optional query), model_name (optional query)
• Validate project exists and user has access
• Parse ISO date strings to datetime objects
• Call tracker.get_usage_by_day() with filters
• Return list of TokenUsageTimeSeries objects
• Handle errors with appropriate HTTP status codes (400 for invalid dates, 404 for project not found, 403 for access denied)
• Add comprehensive docstring with example request/response

Response Format:

[
  {
    "timestamp": "2025-11-01",
    "input_tokens": 15000,
    "output_tokens": 7500,
    "total_tokens": 22500,
    "cost_usd": 0.66
  },
  ...
]

6. Add Comprehensive Tests

File: file:tests/lib/test_metrics_tracker.py

Add new test cases after existing timeline tests:

Test 1: test_get_usage_by_day_basic

• Create multiple token usage records across different days
• Call tracker.get_usage_by_day(project_id=1)
• Assert daily aggregates are correct (tokens, cost, call_count)
• Verify dates are in ascending order
• Verify date format is ISO string

Test 2: test_get_usage_by_day_with_date_range

• Insert records spanning 30 days
• Query with 7-day date range
• Assert only records within range are included
• Verify boundary conditions (inclusive start/end dates)

Test 3: test_get_usage_by_day_with_agent_filter

• Create records for multiple agents on same days
• Filter by specific agent_id
• Assert only that agent's usage is aggregated

Test 4: test_get_usage_by_day_with_model_filter

• Create records with different models on same days
• Filter by specific model_name
• Assert only that model's usage is aggregated

Test 5: test_get_usage_by_day_empty_project

• Query project with no token usage
• Assert returns empty list

Test 6: test_get_usage_by_day_multiple_calls_same_day

• Create multiple records on same day
• Assert they are aggregated into single day entry
• Verify sum of tokens and costs is correct

Test 7: test_get_token_usage_stats_includes_by_day

• Call existing get_token_usage_stats() method
• Assert by_day field is populated (no longer empty)
• Verify structure matches expected format

7. Add API Router Tests

File: file:tests/ui/test_metrics_router.py (create if doesn't exist)

Add integration tests for the new endpoint:

Test 1: test_get_timeseries_success

• Create test data across multiple days
• Call GET /api/projects/1/metrics/tokens/timeseries?start_date=...&end_date=...
• Assert 200 status code
• Verify response structure matches TokenUsageTimeSeries[]

Test 2: test_get_timeseries_invalid_date_format

• Call endpoint with malformed date string
• Assert 400 Bad Request

Test 3: test_get_timeseries_project_not_found

• Call endpoint with non-existent project_id
• Assert 404 Not Found

Test 4: test_get_timeseries_with_filters

• Call endpoint with agent_id and model_name filters
• Verify filtered results

Architecture Diagram

sequenceDiagram
    participant UI as Dashboard UI
    participant API as Metrics Router
    participant MT as MetricsTracker
    participant DB as Database
    participant TR as TokenRepository
    participant SQLite as SQLite DB

    UI->>API: GET /api/projects/1/metrics/tokens/timeseries
    API->>API: Validate project & auth
    API->>API: Parse date parameters
    API->>MT: get_usage_by_day(project_id, start_date, end_date)
    MT->>DB: get_usage_by_day(...)
    DB->>TR: get_usage_by_day(...)
    TR->>SQLite: SELECT DATE(timestamp), SUM(...) GROUP BY DATE(timestamp)
    SQLite-->>TR: Daily aggregates
    TR-->>DB: List[Dict]
    DB-->>MT: List[Dict]
    MT-->>API: List[Dict] (formatted)
    API-->>UI: JSON response (TokenUsageTimeSeries[])
    UI->>UI: Render chart

Loading

Data Flow Summary

Component	Input	Output
TokenRepository	project_id, filters, date range	Raw daily aggregates from SQL
MetricsTracker	project_id, optional filters	Formatted daily usage data
API Router	HTTP request with query params	JSON response (TokenUsageTimeSeries[])
Dashboard UI	project_id, date range	Rendered line chart

Key Implementation Details

• Date Handling: Use SQLite's DATE() function for grouping, convert to ISO strings for API responses
• Filtering: Support optional filters (agent_id, model_name) for drill-down analysis
• Cost Calculation: Aggregate estimated_cost_usd from existing records (no recalculation needed)
• Token Aggregation: Sum both input_tokens and output_tokens separately, plus total
• Ordering: Always order by date ascending for timeline visualization
• Error Handling: Validate dates, project existence, and user authorization
• Testing: Use in-memory SQLite database with manually inserted backdated records for date range testing

Import In IDE

🤖 Prompt for AI Agents

## Observations

The codebase uses SQLite with raw SQL queries (no ORM) and follows a repository pattern. The `MetricsTracker` class in `file:codeframe/lib/metrics_tracker.py` has a TODO at lines 457-458 for daily aggregation. The `TokenRepository` in `file:codeframe/persistence/repositories/token_repository.py` already demonstrates GROUP BY aggregation patterns. The frontend dashboard component `file:web-ui/src/components/metrics/TokenUsageChart.tsx` is ready to consume time-series data but the backend API endpoint doesn't exist yet.

## Approach

Implement daily token usage aggregation following existing patterns in the codebase. Add a new `get_usage_by_day()` method to `MetricsTracker` that delegates to a new repository method in `TokenRepository`. Create a new API endpoint for time-series data consumption. Update the existing `get_token_usage_stats()` method to populate the `by_day` field. Follow the established testing patterns with pytest and async fixtures.

## Implementation Steps

### 1. Add Daily Aggregation Query to TokenRepository

**File**: `file:codeframe/persistence/repositories/token_repository.py`

Add a new method `get_usage_by_day()` after the existing `get_project_costs_aggregate()` method:

- Accept parameters: `project_id` (required), `agent_id` (optional), `model_name` (optional), `start_date` (optional), `end_date` (optional)
- Use SQL query with `DATE(timestamp)` for grouping by day
- Aggregate: `SUM(input_tokens + output_tokens)` as total_tokens, `SUM(estimated_cost_usd)` as total_cost, `COUNT(*)` as call_count
- Apply WHERE filters for project_id, agent_id, model_name, and date range
- Order by date ascending for timeline visualization
- Return list of dictionaries with keys: `date`, `total_tokens`, `input_tokens`, `output_tokens`, `total_cost`, `call_count`

**SQL Pattern**:
```sql
SELECT 
    DATE(timestamp) as date,
    SUM(input_tokens) as input_tokens,
    SUM(output_tokens) as output_tokens,
    SUM(input_tokens + output_tokens) as total_tokens,
    SUM(estimated_cost_usd) as total_cost,
    COUNT(*) as call_count
FROM token_usage
WHERE project_id = ? [AND agent_id = ?] [AND model_name = ?] 
  [AND timestamp >= ?] [AND timestamp <= ?]
GROUP BY DATE(timestamp)
ORDER BY date ASC
```

### 2. Add get_usage_by_day() Method to MetricsTracker

**File**: `file:codeframe/lib/metrics_tracker.py`

Add new async method after `get_token_usage_stats()`:

- Method signature: `async def get_usage_by_day(project_id: int, agent_id: Optional[str] = None, model_name: Optional[str] = None, start_date: Optional[datetime] = None, end_date: Optional[datetime] = None) -> List[Dict[str, Any]]`
- Delegate to `self.db.get_usage_by_day()` with all parameters
- Return list of daily aggregates with structure matching `TokenUsageTimeSeries` type from frontend
- Each item should have: `date` (ISO string), `input_tokens`, `output_tokens`, `total_tokens`, `cost_usd`
- Add comprehensive docstring with example usage matching the issue description

### 3. Update get_token_usage_stats() to Populate by_day

**File**: `file:codeframe/lib/metrics_tracker.py` (lines 457-458)

Replace the TODO comment with actual implementation:

- Call `self.db.get_usage_by_day(project_id, start_date=start_date, end_date=end_date)`
- Assign result to `result["by_day"]`
- Format dates as ISO strings for consistency
- Ensure cost values are rounded to 6 decimal places

### 4. Add Database Delegation Method

**File**: `file:codeframe/persistence/database.py`

Add delegation method in the token usage section (after `get_token_usage()`):

- Method: `def get_usage_by_day(self, *args, **kwargs)`
- Delegate to `self.token_usage.get_usage_by_day(*args, **kwargs)`
- Follow the same pattern as existing delegation methods

### 5. Create API Endpoint for Time Series

**File**: `file:codeframe/ui/routers/metrics.py`

Add new endpoint after `get_agent_metrics()`:

- Route: `@router.get("/api/projects/{project_id}/metrics/tokens/timeseries")`
- Parameters: `project_id` (path), `start_date` (query), `end_date` (query), `interval` (query, default='day'), `agent_id` (optional query), `model_name` (optional query)
- Validate project exists and user has access
- Parse ISO date strings to datetime objects
- Call `tracker.get_usage_by_day()` with filters
- Return list of `TokenUsageTimeSeries` objects
- Handle errors with appropriate HTTP status codes (400 for invalid dates, 404 for project not found, 403 for access denied)
- Add comprehensive docstring with example request/response

**Response Format**:
```json
[
  {
    "timestamp": "2025-11-01",
    "input_tokens": 15000,
    "output_tokens": 7500,
    "total_tokens": 22500,
    "cost_usd": 0.66
  },
  ...
]
```

### 6. Add Comprehensive Tests

**File**: `file:tests/lib/test_metrics_tracker.py`

Add new test cases after existing timeline tests:

**Test 1**: `test_get_usage_by_day_basic`
- Create multiple token usage records across different days
- Call `tracker.get_usage_by_day(project_id=1)`
- Assert daily aggregates are correct (tokens, cost, call_count)
- Verify dates are in ascending order
- Verify date format is ISO string

**Test 2**: `test_get_usage_by_day_with_date_range`
- Insert records spanning 30 days
- Query with 7-day date range
- Assert only records within range are included
- Verify boundary conditions (inclusive start/end dates)

**Test 3**: `test_get_usage_by_day_with_agent_filter`
- Create records for multiple agents on same days
- Filter by specific agent_id
- Assert only that agent's usage is aggregated

**Test 4**: `test_get_usage_by_day_with_model_filter`
- Create records with different models on same days
- Filter by specific model_name
- Assert only that model's usage is aggregated

**Test 5**: `test_get_usage_by_day_empty_project`
- Query project with no token usage
- Assert returns empty list

**Test 6**: `test_get_usage_by_day_multiple_calls_same_day`
- Create multiple records on same day
- Assert they are aggregated into single day entry
- Verify sum of tokens and costs is correct

**Test 7**: `test_get_token_usage_stats_includes_by_day`
- Call existing `get_token_usage_stats()` method
- Assert `by_day` field is populated (no longer empty)
- Verify structure matches expected format

### 7. Add API Router Tests

**File**: `file:tests/ui/test_metrics_router.py` (create if doesn't exist)

Add integration tests for the new endpoint:

**Test 1**: `test_get_timeseries_success`
- Create test data across multiple days
- Call GET `/api/projects/1/metrics/tokens/timeseries?start_date=...&end_date=...`
- Assert 200 status code
- Verify response structure matches `TokenUsageTimeSeries[]`

**Test 2**: `test_get_timeseries_invalid_date_format`
- Call endpoint with malformed date string
- Assert 400 Bad Request

**Test 3**: `test_get_timeseries_project_not_found`
- Call endpoint with non-existent project_id
- Assert 404 Not Found

**Test 4**: `test_get_timeseries_with_filters`
- Call endpoint with agent_id and model_name filters
- Verify filtered results

## Architecture Diagram

```mermaid
sequenceDiagram
    participant UI as Dashboard UI
    participant API as Metrics Router
    participant MT as MetricsTracker
    participant DB as Database
    participant TR as TokenRepository
    participant SQLite as SQLite DB

    UI->>API: GET /api/projects/1/metrics/tokens/timeseries
    API->>API: Validate project & auth
    API->>API: Parse date parameters
    API->>MT: get_usage_by_day(project_id, start_date, end_date)
    MT->>DB: get_usage_by_day(...)
    DB->>TR: get_usage_by_day(...)
    TR->>SQLite: SELECT DATE(timestamp), SUM(...) GROUP BY DATE(timestamp)
    SQLite-->>TR: Daily aggregates
    TR-->>DB: List[Dict]
    DB-->>MT: List[Dict]
    MT-->>API: List[Dict] (formatted)
    API-->>UI: JSON response (TokenUsageTimeSeries[])
    UI->>UI: Render chart
```

## Data Flow Summary

| Component | Input | Output |
|-----------|-------|--------|
| TokenRepository | project_id, filters, date range | Raw daily aggregates from SQL |
| MetricsTracker | project_id, optional filters | Formatted daily usage data |
| API Router | HTTP request with query params | JSON response (TokenUsageTimeSeries[]) |
| Dashboard UI | project_id, date range | Rendered line chart |

## Key Implementation Details

- **Date Handling**: Use SQLite's `DATE()` function for grouping, convert to ISO strings for API responses
- **Filtering**: Support optional filters (agent_id, model_name) for drill-down analysis
- **Cost Calculation**: Aggregate `estimated_cost_usd` from existing records (no recalculation needed)
- **Token Aggregation**: Sum both input_tokens and output_tokens separately, plus total
- **Ordering**: Always order by date ascending for timeline visualization
- **Error Handling**: Validate dates, project existence, and user authorization
- **Testing**: Use in-memory SQLite database with manually inserted backdated records for date range testing

---

Execution Information

Branch: main
Commit: 1169cb6

[frankbria]

Re-labeled from P3 to Future based on UX/UI committee review. Detailed time-series metrics are over-engineering for v1. Total cost tracking is sufficient - defer breakdown to v3+.

[traycerai]

I have updated the Implementation Plan to reflect the recent changes in the ticket.

Previous Plan

Observations

The codebase uses SQLite with raw SQL queries (no ORM) and follows a repository pattern. The MetricsTracker class in file:codeframe/lib/metrics_tracker.py has a TODO at lines 457-458 for daily aggregation. The TokenRepository in file:codeframe/persistence/repositories/token_repository.py already demonstrates GROUP BY aggregation patterns. The frontend dashboard component file:web-ui/src/components/metrics/TokenUsageChart.tsx is ready to consume time-series data but the backend API endpoint doesn't exist yet.

Approach

Implement daily token usage aggregation following existing patterns in the codebase. Add a new get_usage_by_day() method to MetricsTracker that delegates to a new repository method in TokenRepository. Create a new API endpoint for time-series data consumption. Update the existing get_token_usage_stats() method to populate the by_day field. Follow the established testing patterns with pytest and async fixtures.

Implementation Steps

1. Add Daily Aggregation Query to TokenRepository

File: file:codeframe/persistence/repositories/token_repository.py

Add a new method get_usage_by_day() after the existing get_project_costs_aggregate() method:

• Accept parameters: project_id (required), agent_id (optional), model_name (optional), start_date (optional), end_date (optional)
• Use SQL query with DATE(timestamp) for grouping by day
• Aggregate: SUM(input_tokens + output_tokens) as total_tokens, SUM(estimated_cost_usd) as total_cost, COUNT(*) as call_count
• Apply WHERE filters for project_id, agent_id, model_name, and date range
• Order by date ascending for timeline visualization
• Return list of dictionaries with keys: date, total_tokens, input_tokens, output_tokens, total_cost, call_count

SQL Pattern:

SELECT 
    DATE(timestamp) as date,
    SUM(input_tokens) as input_tokens,
    SUM(output_tokens) as output_tokens,
    SUM(input_tokens + output_tokens) as total_tokens,
    SUM(estimated_cost_usd) as total_cost,
    COUNT(*) as call_count
FROM token_usage
WHERE project_id = ? [AND agent_id = ?] [AND model_name = ?] 
  [AND timestamp >= ?] [AND timestamp <= ?]
GROUP BY DATE(timestamp)
ORDER BY date ASC

2. Add get_usage_by_day() Method to MetricsTracker

File: file:codeframe/lib/metrics_tracker.py

Add new async method after get_token_usage_stats():

• Method signature: async def get_usage_by_day(project_id: int, agent_id: Optional[str] = None, model_name: Optional[str] = None, start_date: Optional[datetime] = None, end_date: Optional[datetime] = None) -> List[Dict[str, Any]]
• Delegate to self.db.get_usage_by_day() with all parameters
• Return list of daily aggregates with structure matching TokenUsageTimeSeries type from frontend
• Each item should have: date (ISO string), input_tokens, output_tokens, total_tokens, cost_usd
• Add comprehensive docstring with example usage matching the issue description

3. Update get_token_usage_stats() to Populate by_day

File: file:codeframe/lib/metrics_tracker.py (lines 457-458)

Replace the TODO comment with actual implementation:

• Call self.db.get_usage_by_day(project_id, start_date=start_date, end_date=end_date)
• Assign result to result["by_day"]
• Format dates as ISO strings for consistency
• Ensure cost values are rounded to 6 decimal places

4. Add Database Delegation Method

File: file:codeframe/persistence/database.py

Add delegation method in the token usage section (after get_token_usage()):

• Method: def get_usage_by_day(self, *args, **kwargs)
• Delegate to self.token_usage.get_usage_by_day(*args, **kwargs)
• Follow the same pattern as existing delegation methods

5. Create API Endpoint for Time Series

File: file:codeframe/ui/routers/metrics.py

Add new endpoint after get_agent_metrics():

• Route: @router.get("/api/projects/{project_id}/metrics/tokens/timeseries")
• Parameters: project_id (path), start_date (query), end_date (query), interval (query, default='day'), agent_id (optional query), model_name (optional query)
• Validate project exists and user has access
• Parse ISO date strings to datetime objects
• Call tracker.get_usage_by_day() with filters
• Return list of TokenUsageTimeSeries objects
• Handle errors with appropriate HTTP status codes (400 for invalid dates, 404 for project not found, 403 for access denied)
• Add comprehensive docstring with example request/response

Response Format:

[
  {
    "timestamp": "2025-11-01",
    "input_tokens": 15000,
    "output_tokens": 7500,
    "total_tokens": 22500,
    "cost_usd": 0.66
  },
  ...
]

6. Add Comprehensive Tests

File: file:tests/lib/test_metrics_tracker.py

Add new test cases after existing timeline tests:

Test 1: test_get_usage_by_day_basic

• Create multiple token usage records across different days
• Call tracker.get_usage_by_day(project_id=1)
• Assert daily aggregates are correct (tokens, cost, call_count)
• Verify dates are in ascending order
• Verify date format is ISO string

Test 2: test_get_usage_by_day_with_date_range

• Insert records spanning 30 days
• Query with 7-day date range
• Assert only records within range are included
• Verify boundary conditions (inclusive start/end dates)

Test 3: test_get_usage_by_day_with_agent_filter

• Create records for multiple agents on same days
• Filter by specific agent_id
• Assert only that agent's usage is aggregated

Test 4: test_get_usage_by_day_with_model_filter

• Create records with different models on same days
• Filter by specific model_name
• Assert only that model's usage is aggregated

Test 5: test_get_usage_by_day_empty_project

• Query project with no token usage
• Assert returns empty list

Test 6: test_get_usage_by_day_multiple_calls_same_day

• Create multiple records on same day
• Assert they are aggregated into single day entry
• Verify sum of tokens and costs is correct

Test 7: test_get_token_usage_stats_includes_by_day

• Call existing get_token_usage_stats() method
• Assert by_day field is populated (no longer empty)
• Verify structure matches expected format

7. Add API Router Tests

File: file:tests/ui/test_metrics_router.py (create if doesn't exist)

Add integration tests for the new endpoint:

Test 1: test_get_timeseries_success

• Create test data across multiple days
• Call GET /api/projects/1/metrics/tokens/timeseries?start_date=...&end_date=...
• Assert 200 status code
• Verify response structure matches TokenUsageTimeSeries[]

Test 2: test_get_timeseries_invalid_date_format

• Call endpoint with malformed date string
• Assert 400 Bad Request

Test 3: test_get_timeseries_project_not_found

• Call endpoint with non-existent project_id
• Assert 404 Not Found

Test 4: test_get_timeseries_with_filters

• Call endpoint with agent_id and model_name filters
• Verify filtered results

Architecture Diagram

sequenceDiagram
    participant UI as Dashboard UI
    participant API as Metrics Router
    participant MT as MetricsTracker
    participant DB as Database
    participant TR as TokenRepository
    participant SQLite as SQLite DB

    UI->>API: GET /api/projects/1/metrics/tokens/timeseries
    API->>API: Validate project & auth
    API->>API: Parse date parameters
    API->>MT: get_usage_by_day(project_id, start_date, end_date)
    MT->>DB: get_usage_by_day(...)
    DB->>TR: get_usage_by_day(...)
    TR->>SQLite: SELECT DATE(timestamp), SUM(...) GROUP BY DATE(timestamp)
    SQLite-->>TR: Daily aggregates
    TR-->>DB: List[Dict]
    DB-->>MT: List[Dict]
    MT-->>API: List[Dict] (formatted)
    API-->>UI: JSON response (TokenUsageTimeSeries[])
    UI->>UI: Render chart

Loading

Data Flow Summary

Component	Input	Output
TokenRepository	project_id, filters, date range	Raw daily aggregates from SQL
MetricsTracker	project_id, optional filters	Formatted daily usage data
API Router	HTTP request with query params	JSON response (TokenUsageTimeSeries[])
Dashboard UI	project_id, date range	Rendered line chart

Key Implementation Details

• Date Handling: Use SQLite's DATE() function for grouping, convert to ISO strings for API responses
• Filtering: Support optional filters (agent_id, model_name) for drill-down analysis
• Cost Calculation: Aggregate estimated_cost_usd from existing records (no recalculation needed)
• Token Aggregation: Sum both input_tokens and output_tokens separately, plus total
• Ordering: Always order by date ascending for timeline visualization
• Error Handling: Validate dates, project existence, and user authorization
• Testing: Use in-memory SQLite database with manually inserted backdated records for date range testing

Import In IDE

🤖 Prompt for AI Agents

## Observations

The codebase uses SQLite with raw SQL queries (no ORM) and follows a repository pattern. The `MetricsTracker` class in `file:codeframe/lib/metrics_tracker.py` has a TODO at lines 457-458 for daily aggregation. The `TokenRepository` in `file:codeframe/persistence/repositories/token_repository.py` already demonstrates GROUP BY aggregation patterns. The frontend dashboard component `file:web-ui/src/components/metrics/TokenUsageChart.tsx` is ready to consume time-series data but the backend API endpoint doesn't exist yet.

## Approach

Implement daily token usage aggregation following existing patterns in the codebase. Add a new `get_usage_by_day()` method to `MetricsTracker` that delegates to a new repository method in `TokenRepository`. Create a new API endpoint for time-series data consumption. Update the existing `get_token_usage_stats()` method to populate the `by_day` field. Follow the established testing patterns with pytest and async fixtures.

## Implementation Steps

### 1. Add Daily Aggregation Query to TokenRepository

**File**: `file:codeframe/persistence/repositories/token_repository.py`

Add a new method `get_usage_by_day()` after the existing `get_project_costs_aggregate()` method:

- Accept parameters: `project_id` (required), `agent_id` (optional), `model_name` (optional), `start_date` (optional), `end_date` (optional)
- Use SQL query with `DATE(timestamp)` for grouping by day
- Aggregate: `SUM(input_tokens + output_tokens)` as total_tokens, `SUM(estimated_cost_usd)` as total_cost, `COUNT(*)` as call_count
- Apply WHERE filters for project_id, agent_id, model_name, and date range
- Order by date ascending for timeline visualization
- Return list of dictionaries with keys: `date`, `total_tokens`, `input_tokens`, `output_tokens`, `total_cost`, `call_count`

**SQL Pattern**:
```sql
SELECT 
    DATE(timestamp) as date,
    SUM(input_tokens) as input_tokens,
    SUM(output_tokens) as output_tokens,
    SUM(input_tokens + output_tokens) as total_tokens,
    SUM(estimated_cost_usd) as total_cost,
    COUNT(*) as call_count
FROM token_usage
WHERE project_id = ? [AND agent_id = ?] [AND model_name = ?] 
  [AND timestamp >= ?] [AND timestamp <= ?]
GROUP BY DATE(timestamp)
ORDER BY date ASC
```

### 2. Add get_usage_by_day() Method to MetricsTracker

**File**: `file:codeframe/lib/metrics_tracker.py`

Add new async method after `get_token_usage_stats()`:

- Method signature: `async def get_usage_by_day(project_id: int, agent_id: Optional[str] = None, model_name: Optional[str] = None, start_date: Optional[datetime] = None, end_date: Optional[datetime] = None) -> List[Dict[str, Any]]`
- Delegate to `self.db.get_usage_by_day()` with all parameters
- Return list of daily aggregates with structure matching `TokenUsageTimeSeries` type from frontend
- Each item should have: `date` (ISO string), `input_tokens`, `output_tokens`, `total_tokens`, `cost_usd`
- Add comprehensive docstring with example usage matching the issue description

### 3. Update get_token_usage_stats() to Populate by_day

**File**: `file:codeframe/lib/metrics_tracker.py` (lines 457-458)

Replace the TODO comment with actual implementation:

- Call `self.db.get_usage_by_day(project_id, start_date=start_date, end_date=end_date)`
- Assign result to `result["by_day"]`
- Format dates as ISO strings for consistency
- Ensure cost values are rounded to 6 decimal places

### 4. Add Database Delegation Method

**File**: `file:codeframe/persistence/database.py`

Add delegation method in the token usage section (after `get_token_usage()`):

- Method: `def get_usage_by_day(self, *args, **kwargs)`
- Delegate to `self.token_usage.get_usage_by_day(*args, **kwargs)`
- Follow the same pattern as existing delegation methods

### 5. Create API Endpoint for Time Series

**File**: `file:codeframe/ui/routers/metrics.py`

Add new endpoint after `get_agent_metrics()`:

- Route: `@router.get("/api/projects/{project_id}/metrics/tokens/timeseries")`
- Parameters: `project_id` (path), `start_date` (query), `end_date` (query), `interval` (query, default='day'), `agent_id` (optional query), `model_name` (optional query)
- Validate project exists and user has access
- Parse ISO date strings to datetime objects
- Call `tracker.get_usage_by_day()` with filters
- Return list of `TokenUsageTimeSeries` objects
- Handle errors with appropriate HTTP status codes (400 for invalid dates, 404 for project not found, 403 for access denied)
- Add comprehensive docstring with example request/response

**Response Format**:
```json
[
  {
    "timestamp": "2025-11-01",
    "input_tokens": 15000,
    "output_tokens": 7500,
    "total_tokens": 22500,
    "cost_usd": 0.66
  },
  ...
]
```

### 6. Add Comprehensive Tests

**File**: `file:tests/lib/test_metrics_tracker.py`

Add new test cases after existing timeline tests:

**Test 1**: `test_get_usage_by_day_basic`
- Create multiple token usage records across different days
- Call `tracker.get_usage_by_day(project_id=1)`
- Assert daily aggregates are correct (tokens, cost, call_count)
- Verify dates are in ascending order
- Verify date format is ISO string

**Test 2**: `test_get_usage_by_day_with_date_range`
- Insert records spanning 30 days
- Query with 7-day date range
- Assert only records within range are included
- Verify boundary conditions (inclusive start/end dates)

**Test 3**: `test_get_usage_by_day_with_agent_filter`
- Create records for multiple agents on same days
- Filter by specific agent_id
- Assert only that agent's usage is aggregated

**Test 4**: `test_get_usage_by_day_with_model_filter`
- Create records with different models on same days
- Filter by specific model_name
- Assert only that model's usage is aggregated

**Test 5**: `test_get_usage_by_day_empty_project`
- Query project with no token usage
- Assert returns empty list

**Test 6**: `test_get_usage_by_day_multiple_calls_same_day`
- Create multiple records on same day
- Assert they are aggregated into single day entry
- Verify sum of tokens and costs is correct

**Test 7**: `test_get_token_usage_stats_includes_by_day`
- Call existing `get_token_usage_stats()` method
- Assert `by_day` field is populated (no longer empty)
- Verify structure matches expected format

### 7. Add API Router Tests

**File**: `file:tests/ui/test_metrics_router.py` (create if doesn't exist)

Add integration tests for the new endpoint:

**Test 1**: `test_get_timeseries_success`
- Create test data across multiple days
- Call GET `/api/projects/1/metrics/tokens/timeseries?start_date=...&end_date=...`
- Assert 200 status code
- Verify response structure matches `TokenUsageTimeSeries[]`

**Test 2**: `test_get_timeseries_invalid_date_format`
- Call endpoint with malformed date string
- Assert 400 Bad Request

**Test 3**: `test_get_timeseries_project_not_found`
- Call endpoint with non-existent project_id
- Assert 404 Not Found

**Test 4**: `test_get_timeseries_with_filters`
- Call endpoint with agent_id and model_name filters
- Verify filtered results

## Architecture Diagram

```mermaid
sequenceDiagram
    participant UI as Dashboard UI
    participant API as Metrics Router
    participant MT as MetricsTracker
    participant DB as Database
    participant TR as TokenRepository
    participant SQLite as SQLite DB

    UI->>API: GET /api/projects/1/metrics/tokens/timeseries
    API->>API: Validate project & auth
    API->>API: Parse date parameters
    API->>MT: get_usage_by_day(project_id, start_date, end_date)
    MT->>DB: get_usage_by_day(...)
    DB->>TR: get_usage_by_day(...)
    TR->>SQLite: SELECT DATE(timestamp), SUM(...) GROUP BY DATE(timestamp)
    SQLite-->>TR: Daily aggregates
    TR-->>DB: List[Dict]
    DB-->>MT: List[Dict]
    MT-->>API: List[Dict] (formatted)
    API-->>UI: JSON response (TokenUsageTimeSeries[])
    UI->>UI: Render chart
```

## Data Flow Summary

| Component | Input | Output |
|-----------|-------|--------|
| TokenRepository | project_id, filters, date range | Raw daily aggregates from SQL |
| MetricsTracker | project_id, optional filters | Formatted daily usage data |
| API Router | HTTP request with query params | JSON response (TokenUsageTimeSeries[]) |
| Dashboard UI | project_id, date range | Rendered line chart |

## Key Implementation Details

- **Date Handling**: Use SQLite's `DATE()` function for grouping, convert to ISO strings for API responses
- **Filtering**: Support optional filters (agent_id, model_name) for drill-down analysis
- **Cost Calculation**: Aggregate `estimated_cost_usd` from existing records (no recalculation needed)
- **Token Aggregation**: Sum both input_tokens and output_tokens separately, plus total
- **Ordering**: Always order by date ascending for timeline visualization
- **Error Handling**: Validate dates, project existence, and user authorization
- **Testing**: Use in-memory SQLite database with manually inserted backdated records for date range testing

---

Execution Information

Branch: main
Commit: 1169cb6

New Plan

Observations

The ticket has been re-prioritized from P3 to Future based on UX/UI committee review. The feedback indicates that detailed time-series metrics are over-engineering for v1, and total cost tracking is the priority. The MetricsTracker class already has a get_token_usage_timeseries() method that provides time-series aggregation, making the original get_usage_by_day() method redundant. The TODO at lines 466-467 should be removed rather than implemented.

Approach

Defer the daily aggregation feature to v3+ as indicated by the committee review. Remove the TODO comment from the codebase to eliminate technical debt markers for features that are intentionally deferred. The existing get_token_usage_timeseries() method already provides the necessary time-series functionality if needed in the future. Focus remains on total cost tracking for v1.

Implementation Steps

1. Remove TODO Comment from MetricsTracker

File: codeframe/lib/metrics_tracker.py (lines 466-467)

Remove the deferred TODO comment that references the by_day aggregation feature:

• Delete lines 466-467 which contain the TODO and explanation
• No new functionality is added
• This clarifies that the feature is intentionally deferred, not forgotten

2. Document Deferral in Code Comments

File: codeframe/lib/metrics_tracker.py (in get_token_usage_stats() method docstring)

Update the docstring to clarify that the by_day field is intentionally empty in v1:

• Add note in the Returns section that by_day is reserved for future use
• Reference that get_token_usage_timeseries() provides time-series data if needed
• Indicate this feature is deferred to v3+ per UX/UI committee decision

Rationale

The UX/UI committee determined that detailed daily breakdown metrics are not necessary for v1. The current implementation provides:

• Total cost tracking ✓ (primary requirement met)
• Time-series aggregation ✓ (via get_token_usage_timeseries() if needed)
• Agent and model breakdowns ✓ (via get_project_costs())

Adding daily aggregation would introduce unnecessary complexity without corresponding UI/UX benefit in v1. The feature can be implemented in v3+ when dashboard requirements evolve.

No Additional Implementation Required

This is a deferral, not an implementation. The only code change is removing the TODO comment to keep the codebase clean and intentional about deferred features.

Import In IDE

🤖 Prompt for AI Agents

## Observations

The ticket has been re-prioritized from P3 to Future based on UX/UI committee review. The feedback indicates that detailed time-series metrics are over-engineering for v1, and total cost tracking is the priority. The `MetricsTracker` class already has a `get_token_usage_timeseries()` method that provides time-series aggregation, making the original `get_usage_by_day()` method redundant. The TODO at lines 466-467 should be removed rather than implemented.

## Approach

Defer the daily aggregation feature to v3+ as indicated by the committee review. Remove the TODO comment from the codebase to eliminate technical debt markers for features that are intentionally deferred. The existing `get_token_usage_timeseries()` method already provides the necessary time-series functionality if needed in the future. Focus remains on total cost tracking for v1.

## Implementation Steps

### 1. Remove TODO Comment from MetricsTracker

**File**: `codeframe/lib/metrics_tracker.py` (lines 466-467)

Remove the deferred TODO comment that references the by_day aggregation feature:

- Delete lines 466-467 which contain the TODO and explanation
- No new functionality is added
- This clarifies that the feature is intentionally deferred, not forgotten

### 2. Document Deferral in Code Comments

**File**: `codeframe/lib/metrics_tracker.py` (in `get_token_usage_stats()` method docstring)

Update the docstring to clarify that the `by_day` field is intentionally empty in v1:

- Add note in the Returns section that `by_day` is reserved for future use
- Reference that `get_token_usage_timeseries()` provides time-series data if needed
- Indicate this feature is deferred to v3+ per UX/UI committee decision

## Rationale

The UX/UI committee determined that detailed daily breakdown metrics are not necessary for v1. The current implementation provides:

- **Total cost tracking** ✓ (primary requirement met)
- **Time-series aggregation** ✓ (via `get_token_usage_timeseries()` if needed)
- **Agent and model breakdowns** ✓ (via `get_project_costs()`)

Adding daily aggregation would introduce unnecessary complexity without corresponding UI/UX benefit in v1. The feature can be implemented in v3+ when dashboard requirements evolve.

## No Additional Implementation Required

This is a deferral, not an implementation. The only code change is removing the TODO comment to keep the codebase clean and intentional about deferred features.

---

Execution Information

Branch: main
Commit: 2e66ed3