-- metering: squashed from 1600_metering.sql, 1601_metering_reservation_nonce.sql.
CREATE TABLE usage_events (
idempotency_key TEXT PRIMARY KEY,
account_id TEXT NOT NULL,
meter TEXT NOT NULL,
period TEXT NOT NULL,
quantity REAL NOT NULL,
repo_id TEXT,
attributes TEXT,
created_at INTEGER NOT NULL
);
CREATE INDEX usage_events_account ON usage_events (account_id, period, meter);
CREATE INDEX usage_events_created ON usage_events (created_at);
CREATE TRIGGER usage_events_rollup AFTER INSERT ON usage_events
BEGIN
INSERT INTO usage_periods (account_id, meter, period, quantity, updated_at)
VALUES (NEW.account_id, NEW.meter, NEW.period, NEW.quantity, NEW.created_at)
ON CONFLICT (account_id, meter, period) DO UPDATE SET quantity = quantity + excluded.quantity,
updated_at = excluded.updated_at;
INSERT INTO usage_days (account_id, meter, day, quantity)
VALUES (NEW.account_id, NEW.meter, strftime('%Y-%m-%d', NEW.created_at / 1000, 'unixepoch'), NEW.quantity)
ON CONFLICT (account_id, meter, day) DO UPDATE SET quantity = quantity + excluded.quantity;
END;
CREATE TRIGGER usage_events_repo_rollup AFTER INSERT ON usage_events WHEN NEW.repo_id IS NOT NULL
BEGIN
INSERT INTO usage_repo_periods (account_id, repo_id, meter, period, quantity)
VALUES (NEW.account_id, NEW.repo_id, NEW.meter, NEW.period, NEW.quantity)
ON CONFLICT (account_id, repo_id, meter, period) DO UPDATE SET quantity = quantity + excluded.quantity;
END;
-- Counter meters: running totals per period. reserved holds in-flight hard-cap reservations.
CREATE TABLE usage_periods (
account_id TEXT NOT NULL,
meter TEXT NOT NULL,
period TEXT NOT NULL,