supabase-brand-studio-checkout-attempts

-- Durable, owner-bound Stripe Checkout identity. Additive; apply before enabling checkout.
begin;
create table if not exists public.brand_studio_checkout_attempts (
  id uuid primary key default gen_random_uuid(),
  user_id uuid not null references auth.users(id) on delete cascade,
  pack_id text not null check (pack_id in ('single', 'twenty')),
  stripe_mode text not null check (stripe_mode in ('test', 'live')),
  checkout_origin text not null check (checkout_origin ~ '^https://[^/?#@]+$'),
  state text not null default 'creating' check (state in ('creating', 'open', 'complete', 'expired')),
  stripe_session_id text unique,
  created_at timestamptz not null default now()
);
create index if not exists brand_studio_checkout_attempts_owner_created_idx
  on public.brand_studio_checkout_attempts (user_id, created_at desc, id desc);
alter table public.brand_studio_checkout_attempts enable row level security;
revoke all on public.brand_studio_checkout_attempts from public, anon, authenticated;
grant select, insert, update, delete on public.brand_studio_checkout_attempts to service_role;

-- Serialize first requests, including two tabs whose first HTTP response was lost.
-- A terminal attempt only rolls over when the customer explicitly acknowledges it.
create or replace function public.brand_studio_reserve_checkout_attempt(
  p_user_id uuid, p_pack_id text, p_stripe_mode text, p_checkout_origin text,
  p_after_attempt_id uuid default null
) returns setof public.brand_studio_checkout_attempts
language plpgsql security definer set search_path = public, pg_temp as $$
declare current_attempt public.brand_studio_checkout_attempts;
begin
  perform pg_advisory_xact_lock(hashtextextended('brand-studio-checkout:' || p_user_id::text || ':' || p_pack_id, 0));
  select * into current_attempt from public.brand_studio_checkout_attempts
    where user_id = p_user_id and pack_id = p_pack_id
    order by created_at desc, id desc limit 1;
  if found and not (p_after_attempt_id is not null and current_attempt.state in ('complete', 'expired') and current_attempt.id = p_after_attempt_id) then
    return next current_attempt;
    return;
  end if;
  insert into public.brand_studio_checkout_attempts (user_id, pack_id, stripe_mode, checkout_origin)
    values (p_user_id, p_pack_id, p_stripe_mode, p_checkout_origin) returning * into current_attempt;
  return next current_attempt;
end;
$$;
revoke all on function public.brand_studio_reserve_checkout_attempt(uuid, text, text, text, uuid) from public, anon, authenticated;
grant execute on function public.brand_studio_reserve_checkout_attempt(uuid, text, text, text, uuid) to service_role;
commit;