0015_usage_events.sql26 lines · main

1	-- usage_events — hourly rollups, one row per (project, hour, metric).
2	-- The aggregation cron writes here every hour; the Polar metering push
3	-- worker reads pending rows and POSTs them to Polar's meter API.
4	-- Survives function_logs retention (free tier prunes at 7 days) so
5	-- historical usage queries beyond the log window still resolve.
6
7	CREATE TABLE IF NOT EXISTS "usage_events" (
8	"id" text PRIMARY KEY NOT NULL,
9	"project_id" text NOT NULL,
10	"metric" text NOT NULL,
11	"period_start" timestamp with time zone NOT NULL,
12	"value" text NOT NULL,
13	"polar_push_status" text NOT NULL DEFAULT 'pending',
14	"polar_pushed_at" timestamp with time zone,
15	"created_at" timestamp with time zone DEFAULT now() NOT NULL
16	);
17
18	-- One row per (project, hour, metric). Idempotent re-runs of the cron
19	-- overwrite the same row instead of stacking duplicates.
20	CREATE UNIQUE INDEX IF NOT EXISTS "usage_events_project_period_metric_idx"
21	ON "usage_events" USING btree ("project_id", "period_start", "metric");
22
23	-- Partial index for the Polar push worker — only scans pending rows.
24	CREATE INDEX IF NOT EXISTS "usage_events_pending_idx"
25	ON "usage_events" USING btree ("polar_push_status", "period_start")
26	WHERE "polar_push_status" = 'pending';