Designed our entire billing schema from one paragraph of requirements

Schema for the multi-tenant subscription system. Normalized to 3NF, soft deletes, audit timestamps. ``` organizations ──1:N── subscriptions ──N:1── plans │ │ └──1:N── members └──1:N── invoices ``` ```sql CREATE TABLE plans ( id UUID PRIMARY KEY DEFAULT gen_random_uuid(), code TEXT NOT NULL UNIQUE, -- 'free' | 'pro' | 'scale' price_cents INTEGER NOT NULL CHECK (price_cents >= 0), interval TEXT NOT NULL CHECK (interval IN ('month','year')), created_at TIMESTAMPTZ NOT NULL DEFAULT now() ); CREATE TABLE subscriptions ( id UUID PRIMARY KEY DEFAULT gen_random_uuid(), org_id UUID NOT NULL REFERENCES organizations(id) ON DELETE CASCADE, plan_id UUID NOT NULL REFERENCES plans(id), status TEXT NOT NULL CHECK (status IN ('trialing','active','past_due','canceled')), current_period_end TIMESTAMPTZ NOT NULL, deleted_at TIMESTAMPTZ, -- soft delete created_at TIMESTAMPTZ NOT NULL DEFAULT now(), updated_at TIMESTAMPTZ NOT NULL DEFAULT now() ); -- One active subscription per org (partial unique index) CREATE UNIQUE INDEX idx_subs_one_active_per_org ON subscriptions(org_id) WHERE status = 'active' AND deleted_at IS NULL; -- Billing cron scans by status + period end CREATE INDEX idx_subs_renewal ON subscriptions(status, current_period_end); ``` The partial unique index enforces "one active sub per org" at the DB level — no race condition possible in application code. Need the up/down migration for your migration tool next?