---
id: w8/m4/t003
title: Compaction routine — aggregate + purge hourly rows older than the hot window
worker: worker8
status: done
estimate: 45m
depends_on: [w8/m4/t002]
---

## Objective

Implement the compaction routine: for each `(workspace_id, service_id, kind, tier, month)` whose month falls entirely before the hot window, insert/upsert the aggregated total into `usage_monthly`, then delete the corresponding `usage_hourly` rows — atomically per month, idempotent if re-run.

## Context

- Must never compact/purge the current (in-progress) month or any month still inside the hot window.
- Idempotency matters: a re-run over an already-compacted month must not double-count (`ON CONFLICT … DO UPDATE`, same pattern as the hourly rollup's own upsert in `service.go`).
- Do the aggregate-insert and the hourly-delete for a given month inside one transaction, so a crash mid-compaction never leaves `usage_monthly` and `usage_hourly` inconsistent.

## Steps

1. Add `CompactUsage(ctx, before time.Time) error` to `store` (or a sibling `usage` package method): finds eligible months, runs aggregate-insert + hourly-delete per month in a transaction.
2. Guard against compacting the current or hot-window months.
3. Log a summary (months compacted, rows purged) for operational visibility.

## Files

- `lego/backend/internal/store/usage.go`
- `lego/backend/internal/usage/service.go`

## Acceptance criteria

- [ ] Running compaction twice in a row over the same eligible months is a no-op the second time (no duplicate `usage_monthly` rows, no error).
- [ ] A month still inside the hot window is never touched.
- [ ] Compaction of one month doesn't touch `usage_hourly` rows belonging to a different, still-hot month.

## Out of scope

- Scheduling/cadence (t004); the query-side read path (t005).
