max / makenotwork

2.0 KB · 41 lines History Blame Raw

1	-- SyncKit per-key storage attribution.
2	--
3	-- Migration 117 introduced enforcement_mode = 'per_key' but tracked storage
4	-- only at the app level. As a result, a full key in per_key mode would
5	-- degrade the whole app instead of just that key. This migration adds the
6	-- per-key counter table and the `key` column on sync_blobs that lets the
7	-- weekly drift job reconcile per-key totals from the authoritative blob set.
8	--
9	-- JWT minting now requires a `key` claim (see `synckit_auth.rs`), so every
10	-- new blob upload knows its key. Existing sync_blobs rows are first-party
11	-- internal app data (GO/BB/AF) — we don't need to preserve them and a
12	-- backfill default would not be meaningful, so we drop and recreate.
13
14	-- ── Per-key live counters ──
15	-- One row per (app_id, key). bytes_stored is the running total maintained
16	-- by the storage layer; the weekly drift job reconciles from sync_blobs.
17	CREATE TABLE sync_key_usage_current (
18	app_id UUID NOT NULL REFERENCES sync_apps(id) ON DELETE CASCADE,
19	key TEXT NOT NULL,
20	bytes_stored BIGINT NOT NULL DEFAULT 0,
21	bytes_egress_period BIGINT NOT NULL DEFAULT 0,
22	-- Highest 75/90/100 warning band already emailed for this key under the
23	-- current cap. Mirrors `sync_app_usage_current.last_warning_pct` so the
24	-- per-key warning loop can stamp progress without re-firing every tick.
25	last_warning_pct SMALLINT NOT NULL DEFAULT 0,
26	period_started_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
27	updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
28	PRIMARY KEY (app_id, key)
29	);
30
31	CREATE INDEX idx_sync_key_usage_current_app ON sync_key_usage_current(app_id);
32
33	-- ── sync_blobs: attribute each blob to a key ──
34	-- Drop existing rows (internal-app data only; not preserved across this
35	-- schema change). The `key` column is NOT NULL with no default — every
36	-- future write must supply it.
37	DELETE FROM sync_blobs;
38	ALTER TABLE sync_blobs ADD COLUMN key TEXT NOT NULL;
39
40	CREATE INDEX idx_sync_blobs_app_key ON sync_blobs(app_id, key);
41