---
id: w8/m1/t001
title: Usage schema migration (0003_usage) with an idempotent-upsert key
worker: worker8
status: done
estimate: 30m
depends_on: []
---

## Objective

Add the `usage_hourly` table to the control-plane store via a new `0003_usage` migration: one row per (service, kind, hourly window) holding a quantity, keyed by workspace for month-to-date rollups, with a unique constraint that makes the metering loop's upserts idempotent.

## Context

- Migrations live in `lego/backend/internal/store/migrations/` (`0001_core`, `0002_workspaces` exist); the store runs them when `BEX_CP_DB_URI` is set (docs/ADR003-control-plane.md).
- Suggested columns: `workspace_id`, `service_id`, `kind` (`instance_seconds` | `egress_bytes` | `build_seconds`), `tier` (only meaningful for `instance_seconds`; empty string otherwise so it can sit in the unique key), `window_start timestamptz`, `quantity bigint`.
- Unique key: `(service_id, kind, tier, window_start)` — re-running a window's rollup must be a no-op via `ON CONFLICT … DO UPDATE`.
- Ids are typed opaque `<prefix>-<xid>` strings (docs/ADR020-identifiers.md) — `workspace_id` is a `tea-…`, `service_id` a `srv-…`; text columns, no new id kinds needed.

## Steps

1. Write `0003_usage.up.sql` / `0003_usage.down.sql` following the style of `0002_workspaces`.
2. Confirm the migration embed/glob in `internal/store` picks up `0003` without code changes (adjust if the list is explicit).
3. Run the store's migration test path (`store_pg_test.go` pattern) against a scratch Postgres.

## Files

- `lego/backend/internal/store/migrations/0003_usage.up.sql`
- `lego/backend/internal/store/migrations/0003_usage.down.sql`
- `lego/backend/internal/store/` (only if the migration list is explicit)

## Acceptance criteria

- [ ] `up` then `down` then `up` applies cleanly against a scratch Postgres.
- [ ] Inserting the same (service, kind, tier, window) twice via the upsert form leaves one row.

## Out of scope

- The rollup queries and loop that populate the table (t002/t003).
