xtablo-source/supabase/migrations/20260308103000_map_founder_to_annual_plan.sql

-- Extend Stripe subscription -> profile.plan mapping so Founder plans
-- are normalized to internal annual semantics.

CREATE OR REPLACE FUNCTION public.update_profile_subscription_status() RETURNS trigger
LANGUAGE plpgsql
SECURITY DEFINER
AS $$
DECLARE
  v_user_id uuid;
  v_customer_id text;
  v_plan_hint text;
  v_has_paid_subscription boolean := FALSE;
  v_plan public.subscription_plan := 'solo';
BEGIN
  -- Resolve customer id from the changed stripe row
  IF TG_TABLE_NAME = 'subscriptions' THEN
    v_customer_id := NEW.customer;
  ELSIF TG_TABLE_NAME = 'subscription_items' THEN
    SELECT s.customer
    INTO v_customer_id
    FROM stripe.subscriptions s
    WHERE s.id = NEW.subscription;
  ELSE
    RETURN NEW;
  END IF;

  IF v_customer_id IS NULL THEN
    RETURN NEW;
  END IF;

  -- Resolve application user id from Stripe customer metadata
  SELECT (c.metadata->>'user_id')::uuid
  INTO v_user_id
  FROM stripe.customers c
  WHERE c.id = v_customer_id;

  IF v_user_id IS NULL THEN
    RETURN NEW;
  END IF;

  -- Pick the best active/trialing subscription for this user and infer plan from
  -- price/product metadata or lookup key. Fall back to Team when paid but unmapped.
  WITH candidate_subscriptions AS (
    SELECT
      s.status::text AS status,
      lower(
        coalesce(
          NULLIF(pr.metadata->>'plan', ''),
          NULLIF(pr.lookup_key, ''),
          NULLIF(pd.metadata->>'plan', ''),
          NULLIF(pd.name, '')
        )
      ) AS plan_hint,
      CASE s.status::text
        WHEN 'active' THEN 3
        WHEN 'past_due' THEN 2
        WHEN 'trialing' THEN 1
        ELSE 0
      END AS status_weight,
      CASE
        WHEN lower(
          coalesce(
            NULLIF(pr.metadata->>'plan', ''),
            NULLIF(pr.lookup_key, ''),
            NULLIF(pd.metadata->>'plan', ''),
            NULLIF(pd.name, '')
          )
        ) LIKE '%founder%' OR lower(
          coalesce(
            NULLIF(pr.metadata->>'plan', ''),
            NULLIF(pr.lookup_key, ''),
            NULLIF(pd.metadata->>'plan', ''),
            NULLIF(pd.name, '')
          )
        ) LIKE '%annual%' OR lower(
          coalesce(
            NULLIF(pr.metadata->>'plan', ''),
            NULLIF(pr.lookup_key, ''),
            NULLIF(pd.metadata->>'plan', ''),
            NULLIF(pd.name, '')
          )
        ) LIKE '%beta%' THEN 3
        WHEN lower(
          coalesce(
            NULLIF(pr.metadata->>'plan', ''),
            NULLIF(pr.lookup_key, ''),
            NULLIF(pd.metadata->>'plan', ''),
            NULLIF(pd.name, '')
          )
        ) LIKE '%team%' OR lower(
          coalesce(
            NULLIF(pr.metadata->>'plan', ''),
            NULLIF(pr.lookup_key, ''),
            NULLIF(pd.metadata->>'plan', ''),
            NULLIF(pd.name, '')
          )
        ) LIKE '%standard%' THEN 2
        WHEN lower(
          coalesce(
            NULLIF(pr.metadata->>'plan', ''),
            NULLIF(pr.lookup_key, ''),
            NULLIF(pd.metadata->>'plan', ''),
            NULLIF(pd.name, '')
          )
        ) LIKE '%solo%' THEN 1
        ELSE 0
      END AS plan_weight
    FROM stripe.subscriptions s
    JOIN stripe.customers c
      ON c.id = s.customer
    LEFT JOIN stripe.subscription_items si
      ON si.subscription = s.id
    LEFT JOIN stripe.prices pr
      ON pr.id = si.price
    LEFT JOIN stripe.products pd
      ON pd.id = pr.product
    WHERE (c.metadata->>'user_id')::uuid = v_user_id
      AND s.status::text IN ('active', 'past_due', 'trialing')
      AND (
        si.current_period_end IS NULL
        OR to_timestamp(si.current_period_end) > now()
      )
  )
  SELECT
    TRUE,
    cs.plan_hint
  INTO v_has_paid_subscription, v_plan_hint
  FROM candidate_subscriptions cs
  ORDER BY cs.plan_weight DESC, cs.status_weight DESC
  LIMIT 1;

  IF v_has_paid_subscription THEN
    IF v_plan_hint LIKE '%founder%' OR v_plan_hint LIKE '%annual%' OR v_plan_hint LIKE '%beta%' THEN
      v_plan := 'annual';
    ELSIF v_plan_hint LIKE '%team%' OR v_plan_hint LIKE '%standard%' THEN
      v_plan := 'team';
    ELSIF v_plan_hint LIKE '%solo%' OR v_plan_hint LIKE '%free%' THEN
      v_plan := 'solo';
    ELSE
      -- paid but unmapped => Team by default (backward compatibility)
      v_plan := 'team';
    END IF;
  ELSE
    v_plan := 'solo';
  END IF;

  UPDATE public.profiles
  SET plan = v_plan
  WHERE id = v_user_id;

  RETURN NEW;
END;
$$;

ALTER FUNCTION public.update_profile_subscription_status() OWNER TO postgres;

COMMENT ON FUNCTION public.update_profile_subscription_status() IS
'Maps Stripe subscription state to profile.plan and normalizes founder/annual to annual.';