-- Scratch sites backed by a git repository, and per-site usage metering.
-- Unclaimed scratch repositories live under this reserved org; nobody is a member, so only push tokens reach them.
INSERT OR IGNORE INTO accounts (id, name, kind, display_name, created_at)
VALUES ('sitesscratchaccount0', 'scratch', 'org', 'Scratch sites', 0);
ALTER TABLE sites ADD COLUMN push_token_hash TEXT;
CREATE INDEX sites_push_token ON sites(push_token_hash);
-- One row per site per UTC day. account_id is NULL for unclaimed scratch sites (the free tier).
CREATE TABLE site_usage (
site_id TEXT NOT NULL REFERENCES sites(id) ON DELETE CASCADE,
day TEXT NOT NULL,
account_id TEXT REFERENCES accounts(id) ON DELETE SET NULL,
requests INTEGER NOT NULL DEFAULT 0,
cpu_ms INTEGER NOT NULL DEFAULT 0,
bytes_out INTEGER NOT NULL DEFAULT 0,
PRIMARY KEY (site_id, day)
);
CREATE INDEX site_usage_account ON site_usage(account_id, day);