Creating one account by hand touches three systems, and only one of them has transactions

Reviewers and collaborators sometimes need an account with everything unlocked: all 52 assessment providers, the scores and feedback reports, and the interview pack. Clicking through checkout four times with a test card is not it, and an admin "make this person a customer" button is a feature I do not want to own, maintain or secure for something that runs a handful of times a year.

So it is a script. 144 lines, dry run by default:

pnpm tsx --env-file=.env.local scripts/create-unlocked-account.ts \
  --email [e-mail] --password "..." --name "Their Name"

It prints what it would do and stops. --apply makes it real. It refuses an email that already has an account, matched on lower(email) so a capitalised duplicate is still a duplicate.

That is the boring half. Here is the half worth writing down.

Three systems, one of them transactional

Making this account means writing in three places:

1. the auth provider, which owns the login
2. Stripe, which owns the customer every user row has to reference
3. our own Postgres, which owns the user row, the entitlements and the credit ledger

Only the third one has transactions. Steps one and two are HTTP calls to other people's systems, and their effects do not roll back because something later went wrong.

The Postgres part is easy, and the entitlements go in together or not at all:

const now = new Date();
await sql.begin(async (tx) => {
  await tx`insert into users_table (...) values (..., 'premium', ${ALL_PROVIDERS as string[]}, ...)`;

  // Two ledger rows, matching what a real signup plus an Interview Access
  // grant would leave, so the credit history reads the same as anyone's.
  for (const amount of [SIGNUP_FREE_INTERVIEWS, INTERVIEW_ACCESS_INTERVIEWS]) {
    await tx`insert into interview_credit_transactions (id, user_id, type, amount, created_at)
             values (${crypto.randomUUID()}, ${userId}, 'grant', ${amount}, ${now})`;
  }
  await tx`insert into interview_credits (id, user_id, balance, updated_at)
           values (${crypto.randomUUID()}, ${userId}, ${interviews}, ${now})`;
});

The hard case is a login that exists with nothing behind it. That person can sign in, and what they get is a half-built profile: every page that expects a user row to exist is now wrong, and the only way out is for somebody to notice and clean up by hand. So the script compensates:

try {
  // Stripe customer, then the user row and its entitlements.
} catch (error) {
  // Never leave a login with no account behind it: it could sign in to a
  // half-built profile. Remove it so the script can simply be re-run.
  await supabase.auth.admin.deleteUser(userId);
  throw error;
}

Delete the login, rethrow, and the script is runnable again from a clean state. That is the whole pattern: where you cannot have a transaction, pick the one artefact whose existence makes the half-finished state dangerous, and make sure that artefact is the one you undo.

The ledger rows are not decoration

The account gets 21 interview credits, and it gets them as two grant rows of 1 and 20 rather than one row of 21. One is the free interview every signup receives; twenty is what Interview Access adds.

The reason is that there is no test mode in this schema. No is_test_account column, no branch anywhere that treats these users differently. That is on purpose: a reviewer looking at a differently-shaped account is reviewing a product that no customer has. If the credit history page sums a ledger, this account's ledger has to be the kind of ledger that page will meet in production, including the fact that real balances arrive in more than one piece.

The script stamps the current terms and privacy versions at creation time for the same reason, in the other direction: it is the one difference a reviewer should not have to deal with, because being asked to re-consent on first login is noise from the script rather than something about the product.

The email is created already verified, so no confirmation mail goes to someone who did not sign up.

The cross-environment mistake that fixes itself

One comment in the header is there to stop a future me from panicking:

The Stripe customer is created with whatever key the env file holds. A test-mode customer on a production account is harmless: every checkout route goes through ensureStripeCustomerId, which replaces an id the live account does not recognise.

Running this script with a test-mode key against the production database writes a customer id that production Stripe has never heard of. Without that function it is a broken account and a support conversation. With it, the first checkout notices the id is not recognised, mints a real customer and writes it back.

I did not build that for this script. It exists because customer ids go stale for other reasons. But it is the difference between a cross-environment slip that self-heals and one that needs a runbook, and that is worth knowing about your own system before you write the script that depends on it.

See it

The "all 52 providers" figure in the script is ALL_PROVIDERS.length, and it is the same list the public site is built from. Open cogniprep.app/games and count it yourself:

new Set([...document.querySelectorAll('a[href^="/games/"]')]
  .map((a) => a.getAttribute('href'))).size;

That returns 52 today, one link per provider hub, and it goes up when the next provider lands rather than when anyone edits the script. What the three entitlements cost a real customer is on cogniprep.app/pricing, which is the arithmetic this script exists to skip.