max / makenotwork

2.6 KB · 61 lines History Blame Raw

1	-- SyncKit billing v2.1: GB-only pricing, two modes.
2	--
3	-- Replaces the storage + egress_multiple formula with pure GB pricing.
4	-- Egress and ingress are absorbed in the storage rate's 2x margin. We still
5	-- TRACK egress in sync_app_usage_current for developer-facing stats, but no
6	-- longer enforce a cap on it.
7	--
8	-- Two modes (the `enforcement_mode` column drives both pricing and limits):
9	--
10	-- bulk — developer sets `storage_gb_cap`. Price = storage_gb_cap × rate.
11	-- When storage fills, the whole app's uploads degrade.
12	--
13	-- per_key — developer sets `key_cap` AND `gb_per_key`.
14	-- Price = key_cap × gb_per_key × rate.
15	-- Each key gets its own gb_per_key allotment. A full key
16	-- degrades only that key; other keys keep working.
17	--
18	-- Mode rename: 'app_wide' → 'bulk' (terminology change for clarity).
19
20	-- Drop the existing shape constraints so we can rework them.
21	ALTER TABLE sync_apps DROP CONSTRAINT sync_apps_billing_shape;
22	ALTER TABLE sync_apps DROP CONSTRAINT sync_apps_key_cap_shape;
23
24	-- Egress is no longer a price input. We still track usage in
25	-- sync_app_usage_current.bytes_egress_period for stats.
26	ALTER TABLE sync_apps DROP COLUMN egress_multiple;
27
28	-- New per-key allotment knob. Required in per_key mode, NULL in bulk mode.
29	ALTER TABLE sync_apps ADD COLUMN gb_per_key INT
30	CHECK (gb_per_key IS NULL OR gb_per_key > 0);
31
32	-- Rename enforcement_mode value 'app_wide' → 'bulk'. Drop the old CHECK first
33	-- so we can rewrite values, then add the new CHECK.
34	ALTER TABLE sync_apps DROP CONSTRAINT sync_apps_enforcement_mode_check;
35	UPDATE sync_apps SET enforcement_mode = 'bulk' WHERE enforcement_mode = 'app_wide';
36	ALTER TABLE sync_apps ALTER COLUMN enforcement_mode SET DEFAULT 'bulk';
37	ALTER TABLE sync_apps ADD CONSTRAINT sync_apps_enforcement_mode_check
38	CHECK (enforcement_mode IN ('per_key', 'bulk'));
39
40	-- New shape constraint: in bulk mode, storage_gb_cap is required and gb_per_key
41	-- + key_cap are forbidden; in per_key mode, key_cap + gb_per_key are required
42	-- and storage_gb_cap is forbidden. Drafts and canceled apps are exempt
43	-- (no knobs set yet), as are internal apps (no billing).
44	ALTER TABLE sync_apps ADD CONSTRAINT sync_apps_billing_shape CHECK (
45	is_internal
46	OR billing_status = 'draft'
47	OR billing_status = 'canceled'
48	OR (
49	enforcement_mode = 'bulk'
50	AND storage_gb_cap IS NOT NULL
51	AND key_cap IS NULL
52	AND gb_per_key IS NULL
53	)
54	OR (
55	enforcement_mode = 'per_key'
56	AND key_cap IS NOT NULL
57	AND gb_per_key IS NOT NULL
58	AND storage_gb_cap IS NULL
59	)
60	);
61