max / makenotwork

5.5 KB · 123 lines History Blame Raw

1	-- SyncKit v2 billing: open-platform model.
2	--
3	-- Replaces the old per-end-user subscription system (`app_sync_subscriptions`)
4	-- with a developer-pays-MNW model. The developer of a SyncKit app is the
5	-- existing `sync_apps.creator_id`. MNW charges the developer at the start of
6	-- each period using a parameterized two-knob pricing model:
7	--
8	-- monthly_price = max($5 base, storage_gb × $0.013
9	-- + storage_gb × egress_multiple × $0.0022
10	-- + key_cap × $0.02 (per_key mode only))
11	--
12	-- Knobs the developer sets:
13	-- - storage_gb_cap — total GB of blob storage available to the app
14	-- - egress_multiple — monthly egress quota = storage_gb_cap × this
15	-- - key_cap — only meaningful in per_key enforcement mode
16	--
17	-- The base fee is pro-rated: developers always pay at least $5/mo, but usage
18	-- fees count against it. Once usage exceeds $5, the floor disappears.
19	--
20	-- Enforcement modes (orthogonal to pricing):
21	-- - per_key — when key_cap reached, new key claims refused; existing keys
22	-- keep working until storage/egress caps hit.
23	-- - app_wide — any cap hit returns quota_exceeded for the whole app.
24	--
25	-- "Key" is a developer-defined opaque unit. The dev's backend calls
26	-- claim_key/release_key as their app's logic dictates (user, workspace,
27	-- server, channel, whatever).
28	--
29	-- First-party apps (GO/BB/AF) are marked `is_internal` and bypass billing
30	-- entirely. Their end-user billing (formerly app_sync_subscriptions) is
31	-- removed — those apps will grow their own subscription code outside SyncKit.
32
33	-- ── sync_apps: billing columns ──
34	ALTER TABLE sync_apps ADD COLUMN is_internal BOOLEAN NOT NULL DEFAULT FALSE;
35	ALTER TABLE sync_apps ADD COLUMN stripe_customer_id TEXT;
36	ALTER TABLE sync_apps ADD COLUMN stripe_subscription_id TEXT UNIQUE;
37	ALTER TABLE sync_apps ADD COLUMN billing_status TEXT NOT NULL DEFAULT 'draft'
38	CHECK (billing_status IN ('draft', 'active', 'suspended_unpaid', 'canceled'));
39
40	-- Pricing knobs. NULL while in draft status; required once active (unless internal).
41	ALTER TABLE sync_apps ADD COLUMN storage_gb_cap INT
42	CHECK (storage_gb_cap IS NULL OR storage_gb_cap > 0);
43	ALTER TABLE sync_apps ADD COLUMN egress_multiple NUMERIC(6, 2)
44	CHECK (egress_multiple IS NULL OR egress_multiple > 0);
45
46	-- Enforcement mode and the optional key cap that goes with per_key mode.
47	ALTER TABLE sync_apps ADD COLUMN enforcement_mode TEXT NOT NULL DEFAULT 'app_wide'
48	CHECK (enforcement_mode IN ('per_key', 'app_wide'));
49	ALTER TABLE sync_apps ADD COLUMN key_cap INT
50	CHECK (key_cap IS NULL OR key_cap > 0);
51
52	ALTER TABLE sync_apps ADD COLUMN current_period_start TIMESTAMPTZ;
53	ALTER TABLE sync_apps ADD COLUMN current_period_end TIMESTAMPTZ;
54
55	-- An active non-internal app must have both knobs set. A per_key app must
56	-- have key_cap set; an app_wide app must NOT.
57	ALTER TABLE sync_apps ADD CONSTRAINT sync_apps_billing_shape CHECK (
58	is_internal
59	OR billing_status = 'draft'
60	OR billing_status = 'canceled'
61	OR (storage_gb_cap IS NOT NULL AND egress_multiple IS NOT NULL)
62	);
63	ALTER TABLE sync_apps ADD CONSTRAINT sync_apps_key_cap_shape CHECK (
64	(enforcement_mode = 'per_key' AND key_cap IS NOT NULL)
65	OR (enforcement_mode = 'app_wide' AND key_cap IS NULL)
66	);
67
68	CREATE INDEX idx_sync_apps_stripe_customer ON sync_apps(stripe_customer_id)
69	WHERE stripe_customer_id IS NOT NULL;
70	CREATE INDEX idx_sync_apps_billing_status ON sync_apps(billing_status);
71
72	-- ── Backfill first-party apps ──
73	-- All currently-existing apps are first-party. Mark internal, set active.
74	UPDATE sync_apps SET
75	is_internal = TRUE,
76	billing_status = 'active',
77	current_period_start = NOW(),
78	current_period_end = NOW() + INTERVAL '100 years';
79
80	-- ── Per-app claimed keys ──
81	-- A key is an opaque developer-defined string. Counted against key_cap
82	-- (per_key mode only). released_at NULL ⇒ currently active.
83	CREATE TABLE sync_app_keys (
84	id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
85	app_id UUID NOT NULL REFERENCES sync_apps(id) ON DELETE CASCADE,
86	key TEXT NOT NULL,
87	claimed_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
88	released_at TIMESTAMPTZ,
89	created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
90	);
91
92	CREATE UNIQUE INDEX idx_sync_app_keys_active
93	ON sync_app_keys(app_id, key)
94	WHERE released_at IS NULL;
95	CREATE INDEX idx_sync_app_keys_app_active
96	ON sync_app_keys(app_id)
97	WHERE released_at IS NULL;
98
99	-- ── Live usage counters (one row per app, updated in place) ──
100	-- bytes_stored is a running total maintained by the storage layer;
101	-- bytes_egress_period and keys_claimed reset on period rollover.
102	-- last_warning_pct tracks which warning email has been sent (0/75/90/100)
103	-- so we don't spam the developer.
104	CREATE TABLE sync_app_usage_current (
105	app_id UUID PRIMARY KEY REFERENCES sync_apps(id) ON DELETE CASCADE,
106	bytes_stored BIGINT NOT NULL DEFAULT 0,
107	bytes_egress_period BIGINT NOT NULL DEFAULT 0,
108	keys_claimed INT NOT NULL DEFAULT 0,
109	last_warning_pct SMALLINT NOT NULL DEFAULT 0
110	CHECK (last_warning_pct IN (0, 75, 90, 100)),
111	period_started_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
112	updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
113	);
114
115	INSERT INTO sync_app_usage_current (app_id)
116	SELECT id FROM sync_apps
117	ON CONFLICT (app_id) DO NOTHING;
118
119	-- ── Drop the old end-user subscription system ──
120	-- Active Stripe subscriptions linked to this table will be canceled by the
121	-- Phase 2 application code; this migration only removes the persistence.
122	DROP TABLE app_sync_subscriptions;
123