Dev test-user reset

The standard way to wipe one test user from qr-setu-dev and re-onboard the same mobile number from a clean state. Two or three numbers get re-used across onboarding, auth, consumer and merchant scenarios, and without this each run leaves state that changes the next run's behaviour.

DEVELOPMENT ONLY, AND THE FILE'S LOCATION IS THE CONTROL

It lives at supabase/scripts/dev-reset-test-user.sql, not in supabase/migrations/. deploy-prod.yml promotes migrations; nothing promotes scripts/. A destructive helper that no pipeline can carry to production is safer than one guarded by a flag somebody can flip.

Accepted consequence: rebuild Dev and this must be re-run by hand. It is not in the schema.

The short answer: run /deboard-dev-user

In Claude Code, /deboard-dev-user <number> runs this whole procedure in order: it checks the project and the installed version, previews, rehearses, reports, stops for your confirmation, deletes, and then verifies from outside the reset. The skill is .claude/skills/deboard-dev-user/.

It exists because both failures so far were process rather than SQL: an installer run mistaken for a delete (QRS-1071), and a row count nobody read (QRS-1072). The command's value is that it always runs the same order and always reads the numbers that have actually bitten.

The SQL below is what it runs, and stays the reference for doing it by hand.

Install once

Run the file against Dev in the SQL editor, or:

env -u SUPABASE_ACCESS_TOKEN npm run sb -- dev db execute -f supabase/scripts/dev-reset-test-user.sql

It creates a dev schema with four functions, all revoked from public, anon and authenticated.

Use it

Always preview first. The preview is read-only and is the dry run.

select * from dev.describe_test_user('+919999999999');

Every row is USER, DELETE, KEEP or BLOCKER. Read the BLOCKER rows before going further: they name a RESTRICT foreign key that will make the reset refuse rather than half-finish.

Then, with the identifier typed twice:

select * from dev.reset_test_user('+919999999999', '+919999999999');

It returns a step-by-step row count and ends with a self-check that raises (rolling the whole thing back) if anything survived. The identifier is repeated instead of a boolean on purpose: true is easy to leave in a saved snippet, a phone number is not.

Rehearse it first, on the real user

The safest step, and the one to use before every first-time delete of a shape you have not reset before. This runs every statement for real, so every foreign key is genuinely checked, and then aborts so nothing is kept. A blocking constraint surfaces here instead of half way through a delete.

do $$
declare r record; total bigint := 0; steps int := 0;
begin
  for r in select * from dev.reset_test_user('+919999999999', '+919999999999') loop
    total := total + r.rows_affected;
    steps := steps + 1;
  end loop;
  raise exception 'REHEARSAL PASSED: % steps ran, % rows would go. Rolled back.', steps, total;
end $$;

A REHEARSAL PASSED error is the success case. Any other error is the real blocker, and it names the constraint. Re-run dev.describe_test_user afterwards to confirm the user is still there.

⚠ Prefer this over begin; ...; rollback;: a dashboard SQL editor may commit each statement separately, and then the "rehearsal" is a real delete. The raise above cannot be defeated that way.

The identifier may be an E.164 phone, an email, or a user id. Two matches raise rather than guess.

⚠ Dev phone rows are stored WITHOUT the + (919130976459), which is QRS-936 still live on existing rows. Pass either form: the resolver matches both.

⚠ Running the .sql file DELETES NOTHING

The file is create schema and create or replace function from top to bottom. Running it successfully means the tools are installed, not that a user was removed. The number is an argument you pass when you CALL the function, never a value to edit inside the file. Editing the example number in the hint string changes an error message and nothing else.

Confirm the install rather than assume it:

select proname from pg_proc where pronamespace = 'dev'::regnamespace order by 1;
-- describe_test_user - reset_test_user - resolve_test_user - test_user_workspaces

⚠ "I deleted the account in the app and the rows are still there"

That is correct behaviour, not a failure, and it is a different mechanism entirely.

The app's Delete my account calls manage-account delete_account, which does three things: soft_delete_account, then a 100 year ban on the auth user, then a global sign-out. Measured on a real Dev account 2026-09-05: users.status = 'deleted', deleted_at set, display_name and avatar_media_id nulled, biodata profiles retired and shares withdrawn.

Everything else stays, deliberately. soft_delete_account's own comment says why the address is left alone: "Releasing it would strand the name (a released row still holds the primary key) and the account still exists, so a person who returns signs into the same account and still has their address." So the auth row, the workspace, the Setu Card and the slug all remain.

after an in-app delete	state
auth.users	present, and banned until 2126 so the number cannot sign in again
public.users	present, status = 'deleted', name and avatar nulled
workspace, Setu Card, catalogue	untouched
the address in public.slugs	still active, so it cannot be re-claimed

⚠ For repeat testing the ban is the blocker. A soft-deleted number is not a clean slate, it is a locked one: the app will refuse the sign-in. dev.reset_test_user deletes the auth row, which removes the ban with it. That is the only thing that returns the number to a genuinely fresh state.

⚠ The one finding that makes this necessary rather than convenient

Deleting the auth user does not free the address, and the failure is silent.

public.slugs.user_id is ON DELETE SET NULL, so the row is not removed. The trigger slugs_release_on_owner_loss then flips it to state = 'released' — and the row survives. resolve_slug_status answers 'reserved' for any row matching the slug (its own comment calls that the oracle-closing branch, which is correct for production), while claim_slug proceeds only on 'available'.

So after a naive delete the same person can never re-claim their own address. Measured on Dev 2026-09-05, by deleting a probe user inside an aborted transaction:

slugs rows left: 1, state: released, resolve_slug_status: reserved   (claim_slug needs 'available')

The reset deletes the slug row. That single line is what makes a test number re-usable.

⚠ Read the address row's count, every time

The reset prints a row per step. The one to check is slugs. If it says 0 while the preview listed an address, something is wrong: the address has survived and the number is not reusable.

That is not hypothetical. It shipped (QRS-1072). The workspace was deleted before the slug, and slugs.workspace_id is ON DELETE SET NULL, so the delete's own predicate stopped matching the row it was aiming at. The consumer path worked, because a consumer's slug is user-owned and the user row goes later, which is exactly why it went unnoticed.

The self-check missed it too, and that is the sharper lesson: it asked slugs where user_id = v_user, which is NULL by that point whether or not the row was deleted. A verification that reuses the delete's own broken predicate confirms nothing. It now counts the addresses captured before the run and names the number in its message:

verified: no user, no auth row, and 0 of 1 address row(s) remain

If an address ever does survive, it is visible and repairable:

select slug, state, owner_kind from public.slugs where user_id is null and workspace_id is null;
delete from public.slugs where slug = 'the-address' and user_id is null and workspace_id is null;

⚠ workspaces.contact_phone is not a link to an account

A test number often appears in several workspace rows because a merchant typed it into the business contact field. That column is free text with no foreign key, so those rows belong to whichever account created them. Deleting one user does not and must not touch them. To see who actually owns a workspace, read workspace_members, not contact_phone.

What it deletes, keeps, and refuses

Deletes	the auth user (cascading public.users, identities, sessions, refresh tokens, one-time tokens, MFA and WebAuthn rows), the slug rows, workspaces where this user is the sole member and everything under them, owned media, biodata subjects and their profiles/shares/requests, hosted meetings, reminders, user-scoped feature grants, idempotency keys, resource holds
Keeps	audit_log (its actor_user_id is SET NULL, so the schema already decides an audit trail outlives its actor) · other merchants' orders where this user was the buyer, which go anonymous rather than vanish, because deleting them would rewrite somebody else's sales history · communication_messages, the WhatsApp/OTP delivery and billing ledger, unless you pass p_purge_communications => true
Refuses	a workspace that owns a WhatsApp Business Account, or one with child workspaces. Both are ON DELETE RESTRICT; the function raises instead of deleting half of it
Never touches	any workspace with another member. Resetting one test user must not delete a tenant somebody else is testing against

Two things it cannot do

public.rate_limits is not joinable to a user. It is keyed by (scope, subject_hash) where the hash is a digest computed inside the Edge Function. If OTP sends start being refused mid-testing, clear by scope:

delete from public.rate_limits where scope like 'otp%';

It does not auto-discover new tables. The delete order was derived by reading pg_constraint on 2026-09-05. A migration that adds a table referencing public.users or public.workspaces is invisible to it, so re-measure after schema changes:

select src.relnamespace::regnamespace || '.' || src.relname as src_table, tgt.relname, c.confdeltype
  from pg_constraint c
  join pg_class src on src.oid = c.conrelid
  join pg_class tgt on tgt.oid = c.confrelid
 where c.contype = 'f' and tgt.relname in ('users', 'workspaces');

confdeltype: c cascade · n set null · a no action · r restrict. a and r are the ones that need a line in the function.

Proven, not assumed

End to end on Dev, 2026-09-05, with a throwaway user (+919000000001, address dev-reset-probe-01), then removed so Dev finished exactly as it started (17 auth users, 17 profile rows, 10 slugs, 8 workspaces, zero residue):

0. The merchant path, rehearsed on a real Dev merchant (workspace "Sunny", address chai, one Setu Card, one catalogue item, four idempotency keys): all 20 report rows ran, 12 rows would have gone, and the abort kept every one of them. Dev finished at 17 users / 10 slugs / 8 workspaces, unchanged. This matters because the throwaway user below owned no workspace, so without it the merchant half of the order would be reasoned rather than exercised.
1. Created the user and claimed the address; handle_new_user wrote the profile row as individual.
2. Naive delete, in an aborted transaction — left the slug behind as released/reserved, above.
3. dev.describe_test_user listed the address with its warning and every count.
4. dev.reset_test_user ran all eleven steps and its own final check passed.
5. Re-onboarded the same number and re-claimed the same address (active/user), with primary_context now business where it had been individual — proving no stale persona survived.
6. Wrong confirmation string raised 22023 rather than deleting.

⚠ Two bugs in the function itself were found by running it, not by reading it: Postgres has no min(uuid) aggregate (42883), and public.workspaces has neither a slug nor a name column, only display_name (42703). Both were assumptions; both failed on first execution. That is the argument for probing any helper before trusting it with a delete.

Related: QRS-1071 · supabase/docs/ERASURE_RUNBOOK.md (the production-side erasure procedure, which is a different thing and stays manual).