Clean Up Lemon Squeezy Test Purchases

Remove one sandbox purchase from Shipflash analytics and customer projections without deleting the Supabase Auth account or touching Stripe history.

On this page

1
Finish provider testing first
Issue the test refund before local deletion when refund webhook behavior is part of the smoke test. A later provider refund can recreate the local projection.
2
Preview the exact target
Confirm the order is a Lemon Squeezy sandbox row and record its order, customer, profile, refund, and outbox identifiers.
3
Delete dependent projections in one transaction
Remove outbox, customer-safe activity, credit, entitlement, refund, charge, and order rows before considering the provider customer.
4
Refresh and verify
Reload revenue analytics and billing history, confirm the order is absent, then run a new checkout with the existing Supabase Auth account.

Cleanup ownership

Data	Cleanup rule	Keep when
billing_orders	Delete the exact Lemon Squeezy order only after asserting livemode is false.	Always keep live orders and unrelated provider orders.
notification_outbox and billing_activity_events	Delete entries for the order and refund IDs so communications and billing history are reset.	Keep provider webhook audit rows unless replaying the exact event intentionally.
billing_refunds, billing_charges, credits, entitlements	Delete exact order-owned projections before deleting the order.	Keep unrelated subscriptions, invoices, usage, and ledger entries.
billing_customers	Delete only when no remaining Lemon Squeezy record references that provider customer.	Keep by default; never delete by Gmail address alone.
profiles and auth.users	Do not delete for a billing smoke-test reset.	Keep the same account so its UUID and non-test history remain stable.

select
  provider_order_id,
  provider_customer_id,
  profile_id,
  status,
  livemode,
  purchased_at,
  refunded_at
from public.billing_orders
where provider = 'lemonsqueezy'
  and provider_order_id = 'REPLACE_WITH_TEST_ORDER_ID'
  and livemode = false;

begin;

create temporary table cleanup_ls_order on commit drop as
select provider_order_id, provider_customer_id, profile_id,
       metadata ->> 'billing_plan_key' as plan_key
from public.billing_orders
where provider = 'lemonsqueezy'
  and provider_order_id = 'REPLACE_WITH_TEST_ORDER_ID'
  and livemode = false
for update;

do $$
begin
  if (select count(*) from cleanup_ls_order) <> 1 then
    raise exception 'Expected exactly one Lemon Squeezy test order';
  end if;
end $$;

create temporary table cleanup_ls_refund on commit drop as
select provider_refund_id
from public.billing_refunds
where provider = 'lemonsqueezy'
  and provider_order_id in (select provider_order_id from cleanup_ls_order);

delete from public.notification_outbox
where resource_id in (select provider_order_id from cleanup_ls_order);

delete from public.billing_activity_events
where provider = 'lemonsqueezy'
  and (
    (resource_type = 'order' and resource_id in (select provider_order_id from cleanup_ls_order))
    or (resource_type = 'refund' and resource_id in (select provider_refund_id from cleanup_ls_refund))
  );

delete from public.billing_credit_transactions
where provider = 'lemonsqueezy'
  and provider_order_id in (select provider_order_id from cleanup_ls_order);

delete from public.billing_credit_grants
where provider = 'lemonsqueezy'
  and provider_order_id in (select provider_order_id from cleanup_ls_order);

delete from public.billing_entitlements
where provider = 'lemonsqueezy'
  and source_type = 'one_time'
  and source_id in (select provider_order_id from cleanup_ls_order);

delete from public.billing_refunds
where provider = 'lemonsqueezy'
  and provider_order_id in (select provider_order_id from cleanup_ls_order);

delete from public.billing_charges
where provider = 'lemonsqueezy'
  and provider_order_id in (select provider_order_id from cleanup_ls_order);

delete from public.billing_orders billing_order
using cleanup_ls_order target
where billing_order.provider = 'lemonsqueezy'
  and billing_order.provider_order_id = target.provider_order_id
  and billing_order.livemode = false;

commit;

delete from public.billing_customers customer
where customer.provider = 'lemonsqueezy'
  and customer.provider_customer_id = 'REPLACE_WITH_TEST_CUSTOMER_ID'
  and not exists (select 1 from public.billing_subscriptions row where row.provider = customer.provider and row.provider_customer_id = customer.provider_customer_id)
  and not exists (select 1 from public.billing_subscription_items row where row.provider = customer.provider and row.provider_customer_id = customer.provider_customer_id)
  and not exists (select 1 from public.billing_orders row where row.provider = customer.provider and row.provider_customer_id = customer.provider_customer_id)
  and not exists (select 1 from public.billing_invoices row where row.provider = customer.provider and row.provider_customer_id = customer.provider_customer_id)
  and not exists (select 1 from public.billing_charges row where row.provider = customer.provider and row.provider_customer_id = customer.provider_customer_id)
  and not exists (select 1 from public.billing_refunds row where row.provider = customer.provider and row.provider_customer_id = customer.provider_customer_id)
  and not exists (select 1 from public.billing_disputes row where row.provider = customer.provider and row.provider_customer_id = customer.provider_customer_id)
  and not exists (select 1 from public.billing_usage_events row where row.provider = customer.provider and row.provider_customer_id = customer.provider_customer_id)
  and not exists (select 1 from public.billing_credit_grants row where row.provider = customer.provider and row.provider_customer_id = customer.provider_customer_id)
  and not exists (select 1 from public.billing_credit_transactions row where row.provider = customer.provider and row.provider_customer_id = customer.provider_customer_id)
  and not exists (select 1 from public.billing_entitlements row where row.provider = customer.provider and row.provider_customer_id = customer.provider_customer_id)
  and not exists (select 1 from public.billing_activity_events row where row.provider = customer.provider and row.provider_customer_id = customer.provider_customer_id)
returning customer.provider_customer_id;

Finish provider testing first

Preview the exact target

Delete dependent projections in one transaction

Refresh and verify

Cleanup ownership