Giving away a licence when the schema assumes a payment

Notifio is a paid desktop app. You buy it, Stripe sends a webhook, the webhook writes a licence row, the app activates against that row. Clean, and it worked from day one.

Then I wanted to give one away.

A beta tester, somebody who wrote a genuinely useful bug report, a friend with a housing deadline. No payment, no Stripe session, still a real working licence. It took about an hour, and most of that hour was schema archaeology, because the table had opinions about how licences come into existence.

The schema assumed a purchase

export const licenses = pgTable("licenses", {
  id: text("id").primaryKey(),
  email: text("email").notNull().unique(),
  token: text("token").notNull().unique(),
  stripeSessionId: text("stripe_session_id").notNull().unique(),
  active: boolean("active").notNull().default(true),
  autoReply: boolean("auto_reply").notNull().default(false),
  autoReplySessionId: text("auto_reply_session_id").unique(),
  autoReplyPurchasedAt: timestamp("auto_reply_purchased_at", { withTimezone: true }),
  ...
});

stripe_session_id is NOT NULL UNIQUE. That is not an accident, it is the idempotency key: Stripe retries webhooks, and the unique constraint is what stops one purchase producing two licences.

Which is exactly the constraint a free grant cannot satisfy. Nobody paid, so there is no session id.

There were three ways out and only one of them is any good.

Make the column nullable. Tempting, one migration. It also dismantles the protection on the path that actually needs it. A nullable idempotency key is not an idempotency key.

A second table for comped licences. Now every read path needs to check two places, and the day somebody with a free licence buys the auto reply upgrade, there is a reconciliation problem that did not need to exist.

Write a synthetic id that cannot collide with a real one.

// NOT NULL and UNIQUE, but nothing was ever paid. A synthetic id keeps the
// constraint satisfied and makes comped licences obvious in the DB.
stripeSessionId: `free-grant_${createId()}`,

Stripe session ids start with cs_. Mine start with free-grant_. The constraint is satisfied, uniqueness still holds, and the origin of every licence in the table is legible from a SELECT without joining anything. That last property is worth more than it looks: a year from now, "how many of our active licences were comped" is a LIKE 'free-grant%' rather than a research project.

If a schema constraint blocks a legitimate new path, prefer a value that satisfies it honestly over a migration that weakens it for everybody.

Idempotent because I will definitely run it twice

The script is pnpm grant <email>, run by a human at a terminal, usually while doing something else. It will be run twice. It will be run again three months later when the same person asks for the auto reply upgrade.

So it does not create, it reconciles:

const [existing] = await db.select().from(licenses)
  .where(eq(licenses.email, opts.email)).limit(1);

Keyed on email, because that is what the recipient knows about themselves and what they will type into the app.

When a row exists, the script works out the difference and applies only that:

const patch = { updatedAt: new Date() };
if (!existing.active) {
  patch.active = true;
  changes.push("reactivated the licence");
}
if (opts.autoReply && !existing.autoReply) {
  patch.autoReply = true;
  patch.autoReplyPurchasedAt = new Date();
  ...
  changes.push("unlocked auto-reply");
}

if (changes.length === 0) {
  console.log("• Nothing to change, already active" + (existing.autoReply ? " with auto-reply" : ""));
}

changes is an array of sentences rather than a boolean, so the output reports what happened in English: ✓ Updated: reactivated the licence, unlocked auto-reply. A grant script whose success output is OK is a script you end up verifying by hand in psql every single time.

The line I am most glad I wrote

// Only stamp a session id if the column is free. It's UNIQUE, and a real
// Stripe id already there means they paid for the upgrade, don't clobber
// the record of that.
if (!existing.autoReplySessionId) {
  patch.autoReplySessionId = `free-grant_${createId()}`;
}

Picture the sequence. Somebody bought the auto reply upgrade months ago. Today they report a bug, and I comp them a licence without thinking hard about it. Without that guard, the script overwrites a real Stripe session id with free-grant_..., and the only record that this customer ever paid me money is gone.

The bug would be silent, permanent, and discovered during a refund request or an audit. Any admin script that writes to rows created by a payment flow should be read specifically for this: which of these fields is the only surviving evidence of something? Those get a guard, not an assignment.

The token belongs to the recipient

if (existing) {
  token = existing.token;

A re-run reuses the existing activation token rather than issuing a new one, and the rationale is entirely about the human:

That matters because the token is what the recipient has already saved.

They pasted it into the app. It is in a note, or starred in their inbox. Rotating it on a re-run means the app they have been happily running now fails to validate, for no reason they can see, because of an administrative action they never knew about. Rotation has to be something you choose, not something that falls out of re-running a script.

Dry run, and the ordering of the two side effects

--base-only   Grant the app only; leave auto-reply locked.
--no-email    Write the licence but don't send anything. Prints the token.
--dry-run     Show what would happen. No writes, no email.
--site <url>  Base URL used for the download link.

--dry-run threads through both side effects, so it prints • Would: reactivated the licence and • Would email ... without touching the database or Resend. For a script that writes to production and emails a real person, being able to rehearse it is not a luxury.

The order of the two side effects matters, and the failure path spells out why:

if (error) {
  // The licence is already granted at this point, so this is recoverable:
  // re-run with --no-email and pass the token on manually.
  console.error("✗ Email failed to send:", error);
  console.error(`  The licence IS granted. Token: ${token}`);
  process.exitCode = 1;
}

Write first, notify second. If the write succeeds and the email fails, the valuable thing exists and the cheap thing can be redone by hand, with the token printed right there in the terminal so it can be pasted into a normal message. If the order were reversed, a failed write after a sent email would mean a person holding an activation token that activates nothing.

The non zero exit code matters too. This is a script humans run, and a human reading a wall of terminal output will absolutely miss a red line in the middle of it. The exit code is what makes it impossible to close the terminal believing it worked.

Guard rails at the top

if (!process.env.DATABASE_URL) {
  die("DATABASE_URL is not set. Run via `pnpm grant` or `node --env-file=.env …`.");
}
if (opts.sendEmail && !opts.dryRun && !process.env.RESEND_API_KEY) {
  die("RESEND_API_KEY is not set. Run via `pnpm grant`, or pass --no-email.");
}

opts.email = opts.email.trim().toLowerCase();
if (!/^[^\s@]+@[^\s@]+\.[^\s@]+$/.test(opts.email)) die(`Not a valid email: ${opts.email}`);

Two small things that pay for themselves. The Resend check is conditional on actually intending to send, so --dry-run and --no-email work on a machine with no mail credentials at all. And the email is lowercased before anything looks at it, because the column is UNIQUE and [e-mail] would otherwise get a second licence rather than finding the first one. Normalise before you query, not after.

The thing it grants

The licence this script writes is the same one a purchase produces, and it activates the same app: notifio.app/download for Mac and Windows, with what is included and what the auto reply upgrade adds on notifio.app/pricing.

If you are building the paid side of a small product, the summary is this. Your payment provider defines how licences are normally created. It should not define how they can be created, and the gap between those two is usually one honest synthetic value and a script careful enough to run twice.