Moheeb Zara
b477c38425
Independent audit (verified vs Stripe Basil 2025-03-31 changelog): - P0: period end moved off the top-level Subscription -> read sub.items.data[0].current_period_end (grace/lapse now actually fires; was always null -> past_due members never went 'late'). - P0: invoice subscription metadata moved under invoice.parent. subscription_details -> read parent (metadata member-resolution was always failing, mislinking dues payments). - P1: handle only invoice.paid (not also invoice.payment_succeeded) + pre-insert dedupe on (space_id,platform,external_id) -> no double payment-ledger rows. - P2: idempotencyKey on customers.create -> no duplicate Stripe customers under concurrent checkout. - 040: documented the 'do not wrap in manual BEGIN/COMMIT' caveat. (P0 webhook-reprocess-on-failure was fixed in ca34e53.) Audit verdict: isolation/secrets/RLS/status-bounds were already sound.
66 lines
3.6 KiB
SQL
66 lines
3.6 KiB
SQL
-- =============================================================================
|
|
-- 040: Stripe recurring dues (Phase 1) — enum + member billing + webhook dedupe
|
|
-- =============================================================================
|
|
-- Per-space OWN Stripe keys (NOT Connect). Secret key + webhook signing secret
|
|
-- live in the AES-256-GCM secrets vault; non-secret config (publishable key,
|
|
-- mode, tier->price map, secret refs) in integrations.config. This migration
|
|
-- is structure only; it never writes the literal 'stripe' enum value, so
|
|
-- ALTER TYPE ADD VALUE in the same script is safe.
|
|
--
|
|
-- * payment_platform gains 'stripe' (ALTER TYPE ADD VALUE is the only safe
|
|
-- path; mirrored into the CREATE TYPE list in scripts/schema.sql).
|
|
-- * member_billing: one row per member with a Stripe customer/subscription.
|
|
-- Webhook (service client) is the only writer; SELECT = admin/board/
|
|
-- treasurer (mirrors payments visibility). Member self-view of their own
|
|
-- billing is a validated service-client action, not an RLS path.
|
|
-- * stripe_webhook_events: idempotency ledger keyed by Stripe's stable
|
|
-- event id. Service-client only; no client policy.
|
|
--
|
|
-- Idempotent: ADD VALUE IF NOT EXISTS, CREATE TABLE/INDEX IF NOT EXISTS,
|
|
-- DROP POLICY IF EXISTS then CREATE.
|
|
--
|
|
-- Apply this file as-is (Supabase SQL editor / psql). Do NOT wrap it in a
|
|
-- manual BEGIN/COMMIT: `ALTER TYPE ... ADD VALUE` historically could not run
|
|
-- inside a transaction block (fine on Supabase PG15 unwrapped). The new
|
|
-- value is not used by any statement here, so a single implicit transaction
|
|
-- is safe and re-runs are no-ops.
|
|
-- =============================================================================
|
|
|
|
ALTER TYPE public.payment_platform ADD VALUE IF NOT EXISTS 'stripe';
|
|
|
|
CREATE TABLE IF NOT EXISTS public.member_billing (
|
|
id uuid PRIMARY KEY DEFAULT uuid_generate_v4(),
|
|
space_id uuid NOT NULL REFERENCES public.spaces(id) ON DELETE CASCADE,
|
|
member_id uuid NOT NULL REFERENCES public.space_members(id) ON DELETE CASCADE,
|
|
stripe_customer_id text,
|
|
stripe_subscription_id text,
|
|
subscription_status text,
|
|
current_period_end timestamptz,
|
|
created_at timestamptz NOT NULL DEFAULT now(),
|
|
updated_at timestamptz NOT NULL DEFAULT now()
|
|
);
|
|
CREATE UNIQUE INDEX IF NOT EXISTS idx_member_billing_member ON public.member_billing (space_id, member_id);
|
|
CREATE INDEX IF NOT EXISTS idx_member_billing_customer ON public.member_billing (stripe_customer_id);
|
|
CREATE INDEX IF NOT EXISTS idx_member_billing_space ON public.member_billing (space_id);
|
|
|
|
ALTER TABLE public.member_billing ENABLE ROW LEVEL SECURITY;
|
|
DROP POLICY IF EXISTS member_billing_select ON public.member_billing;
|
|
CREATE POLICY member_billing_select ON public.member_billing FOR SELECT
|
|
USING (public.user_has_role_in_space(auth.uid(), space_id, ARRAY['admin','board','treasurer']));
|
|
-- No INSERT/UPDATE/DELETE policy: the validated service-client webhook/actions
|
|
-- are the only writers.
|
|
|
|
CREATE TABLE IF NOT EXISTS public.stripe_webhook_events (
|
|
event_id text PRIMARY KEY,
|
|
space_id uuid REFERENCES public.spaces(id) ON DELETE CASCADE,
|
|
type text,
|
|
received_at timestamptz NOT NULL DEFAULT now()
|
|
);
|
|
ALTER TABLE public.stripe_webhook_events ENABLE ROW LEVEL SECURITY;
|
|
-- No policy at all: service-client (webhook) only; opaque idempotency ledger.
|
|
|
|
DROP TRIGGER IF EXISTS trg_member_billing_touch ON public.member_billing;
|
|
CREATE TRIGGER trg_member_billing_touch
|
|
BEFORE UPDATE ON public.member_billing
|
|
FOR EACH ROW EXECUTE FUNCTION public.touch_updated_at();
|