max / makenotwork

1.7 KB · 34 lines History Blame Raw

1	-- End-user SyncKit subscriptions, reintroduced for first-party apps.
2	--
3	-- Migration 098 created `app_sync_subscriptions` and migration 117 dropped it
4	-- in favor of a developer-pays-MNW billing model. Per-app end-user billing
5	-- was supposed to live "outside SyncKit" for first-party apps (GO/BB/AF),
6	-- but we ended up reusing the SyncKit auth/server plumbing for it. This
7	-- migration restores the table with the original shape plus a new
8	-- `pending_storage_limit_bytes` column for cap changes that take effect at
9	-- the next billing cycle (no mid-cycle proration surprises).
10	--
11	-- The pricing model is now formula-driven (see `payments::synckit_app_pricing`)
12	-- — no tier table, the user picks any cap and the server quotes a price.
13	CREATE TABLE app_sync_subscriptions (
14	id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
15	user_id UUID NOT NULL REFERENCES users(id) ON DELETE CASCADE,
16	app_id UUID NOT NULL REFERENCES sync_apps(id) ON DELETE CASCADE,
17	stripe_subscription_id TEXT NOT NULL UNIQUE,
18	stripe_customer_id TEXT NOT NULL,
19	-- Billing interval ("monthly" or "annual"). Reuses the legacy `tier`
20	-- column name so existing SDK code that reads `sub.tier` keeps working.
21	tier TEXT NOT NULL DEFAULT 'monthly',
22	status TEXT NOT NULL DEFAULT 'active',
23	storage_limit_bytes BIGINT,
24	-- Queued cap change applied at the next billing cycle.
25	pending_storage_limit_bytes BIGINT,
26	current_period_start TIMESTAMPTZ,
27	current_period_end TIMESTAMPTZ,
28	canceled_at TIMESTAMPTZ,
29	created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
30	);
31
32	CREATE UNIQUE INDEX idx_app_sync_subs_user_app ON app_sync_subscriptions(user_id, app_id);
33	CREATE INDEX idx_app_sync_subs_stripe ON app_sync_subscriptions(stripe_subscription_id);
34