`db/migrations/010_installer_connect.sql`

37 lines
-- 010 — Stripe Connect Express account fields on installers.
-- Required so each installer can receive booking-deposit payouts net of NPH's
-- platform fee. Booking flow falls back to platform-balance hold when an
-- installer has no Connect account yet (unclaimed studios, or claimed but
-- onboarding incomplete) — those balances queue for manual transfer once
-- the installer completes Connect onboarding.

ALTER TABLE installers
  ADD COLUMN IF NOT EXISTS stripe_account_id text,
  ADD COLUMN IF NOT EXISTS stripe_account_charges_enabled boolean DEFAULT false,
  ADD COLUMN IF NOT EXISTS stripe_account_payouts_enabled boolean DEFAULT false,
  ADD COLUMN IF NOT EXISTS stripe_account_onboarded_at timestamptz;

CREATE INDEX IF NOT EXISTS idx_installers_stripe_account
  ON installers (stripe_account_id)
  WHERE stripe_account_id IS NOT NULL;

-- Audit table for payment_intent webhook events. Mirrors subscription_events
-- pattern: stripe_event_id is UNIQUE so the webhook is idempotent under retry.
CREATE TABLE IF NOT EXISTS payment_events (
  id              bigserial PRIMARY KEY,
  stripe_event_id text UNIQUE NOT NULL,
  event_type      text NOT NULL,
  payment_intent_id text,
  booking_uuid    uuid,
  installer_id    integer REFERENCES installers(id) ON DELETE SET NULL,
  amount_cents    integer,
  application_fee_cents integer,
  payload         jsonb,
  created_at      timestamptz NOT NULL DEFAULT now()
);

CREATE INDEX IF NOT EXISTS idx_payment_events_pi
  ON payment_events (payment_intent_id);
CREATE INDEX IF NOT EXISTS idx_payment_events_booking
  ON payment_events (booking_uuid);