---
id: w8/m4/t002
title: usage_monthly table migration + monthly-aggregation SQL
worker: worker8
status: done
estimate: 35m
depends_on: [w8/m4/t001]
---

## Objective

Add a `usage_monthly` table (one row per `workspace_id, service_id, kind, tier, month`) and the SQL to aggregate `usage_hourly` rows into it, mirroring `0004_usage.up.sql`'s style and constraints.

## Context

- Follow `0004_usage.up.sql`'s pattern: same `kind` CHECK constraint, same `tier` default-empty-string convention, same `(workspace_id, ...)` FK to `tenants`.
- Migration file naming: next free `NNNN_usage_monthly.{up,down}.sql` in `lego/backend/internal/store/migrations/`.

## Steps

1. Write the `up` migration: `CREATE TABLE usage_monthly (workspace_id, service_id, kind, tier, month date, quantity bigint, PRIMARY KEY (service_id, kind, tier, month))` + index on `(workspace_id, month)`.
2. Write the matching `down` migration.
3. Write the aggregation SQL (`SUM(quantity) GROUP BY service_id, kind, tier, date_trunc('month', window_start)`) as a reusable query in `store/usage.go`.

## Files

- `lego/backend/internal/store/migrations/NNNN_usage_monthly.up.sql` (new)
- `lego/backend/internal/store/migrations/NNNN_usage_monthly.down.sql` (new)
- `lego/backend/internal/store/usage.go`

## Acceptance criteria

- [ ] Migration applies cleanly against a fresh + an already-migrated `usage_hourly`-having database.
- [ ] Aggregation SQL correctly sums quantities per `(service_id, kind, tier, month)`.

## Out of scope

- The compaction routine that calls this SQL and purges hourly rows (t003).
