Our entire production schema is one table and 33 lines, and it has not changed in 71 days

server/src/db/schema.ts is 33 lines. It declares one table. The last commit to touch it was 71 days ago, and there have been 23 commits to the repository since.

That is not a boast about restraint. It is a consequence of a pricing decision, and the interesting part is which columns the decision deleted.

/**
 * One row per customer. Both purchases live here.
 *
 * The base licence (£20) unlocks monitoring and email alerts. Auto-reply is a
 * second one-time £20 purchase that upgrades the same licence — there is no
 * recurring billing anywhere, so there is no billing state to track over time:
 * either the licence has been upgraded or it has not.
 */
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),

  /** Has this licence been upgraded to include auto-reply? Permanent once true. */
  autoReply: boolean("auto_reply").notNull().default(false),
  /**
   * Checkout session that paid for the upgrade. Unique so a webhook retry
   * cannot be mistaken for a second purchase. Null until upgraded.
   */
  autoReplySessionId: text("auto_reply_session_id").unique(),
  /** Kept for support and accounting; nothing gates on it. */
  autoReplyPurchasedAt: timestamp("auto_reply_purchased_at", { withTimezone: true }),

  createdAt: timestamp("created_at", { withTimezone: true }).notNull().defaultNow(),
  updatedAt: timestamp("updated_at", { withTimezone: true }).notNull().defaultNow(),
});

Ten columns. Four unique constraints. No foreign keys, because there is nothing to point at. The product that sits on top of it is a desktop app you buy once.

The columns a subscription would have needed

Auto-reply started as a monthly add-on. The comment at the top of lib/license.ts is the before and after:

/**
 * Auto-reply used to be a subscription, which needed statuses, period ends and
 * grace windows to answer "is this unlocked?". It is now a one-time purchase,
 * so the answer is a single boolean column on the licence and there is no rule
 * left to centralise beyond reading it.
 */

Count what that version required. A status enum, and a decision about which of its values count as entitled. A current_period_end, which means every read of "is this unlocked" is a comparison against now() and therefore a question whose answer changes while nobody is looking. A grace window, because a failed card should not lock someone out for 40 minutes while Stripe retries. And a webhook handler for customer.subscription.updated, .deleted, invoice.payment_failed and invoice.paid, each of which has to decide what it means for that status column.

The one-time version needs a boolean. autoReplyUnlocked() in the desktop app is three lines and does not take an argument:

export function autoReplyUnlocked(): boolean {
  return readCache()?.autoReply === true;
}

There is no "is it still valid" in there because there is no expiry to compare against. That is the part worth generalising: a subscription does not just add a table, it makes entitlement a function of the current time, and every caller of a time-dependent predicate has to be audited for what happens at the boundary.

Same thing happened on the webhook side. From the Stripe handler:

/**
 * Both products are one-time payments, so `checkout.session.completed` is the
 * only event we care about. There are no subscription or invoice lifecycles to
 * follow: a licence is issued once, and the auto-reply upgrade is granted once.
 */
if (event.type !== "checkout.session.completed") {
  return NextResponse.json({ received: true });
}

One event type. Everything else is acknowledged and dropped.

Three of the four unique constraints are idempotency, not identity

email being unique is identity: one licence per person, enforced at checkout too, with a 409 for a second purchase attempt.

The other three are there to make retries safe.

/**
 * Checkout session that paid for the upgrade. Unique so a webhook retry
 * cannot be mistaken for a second purchase. Null until upgraded.
 */
autoReplySessionId: text("auto_reply_session_id").unique(),

Stripe will deliver the same checkout.session.completed more than once, and must be allowed to: the handler returns a 500 if the activation email fails to send, specifically so that Stripe retries. Which means the write path has to be safe to run twice, and a unique column on the session id is the cheapest possible way to say so at the storage layer rather than in application logic that somebody will later refactor.

token unique is the same idea pointed at a different risk. The token is what the desktop app sends to /api/validate, so a collision is not a data-integrity problem, it is one customer's app authenticating as another customer's licence. The generator draws from a 32 character alphabet across 12 characters, which I wrote up in Our activation code has no O, no 0, no I and no 1, and 32 is what makes the maths safe. The unique constraint is the belt to that braces: if the maths is ever wrong, the insert fails rather than issuing a duplicate.

One column that is deliberately not load-bearing

/** Kept for support and accounting; nothing gates on it. */
autoReplyPurchasedAt: timestamp("auto_reply_purchased_at", { withTimezone: true }),

Writing down that nothing reads a column is worth the line. The default assumption about a timestamp next to a boolean is that something somewhere compares it to now(), and the next person to touch this file would reasonably go looking for that code. The comment says there is none, and that a future feature wanting one is adding a rule rather than finding one.

The lookup refuses to say which half was wrong

/**
 * Look up a licence by token and verify the email matches.
 * Returns null for any mismatch so callers cannot leak which part was wrong.
 */
export async function resolveLicense(email: string, token: string): Promise<License | null> {
  if (!email || !token) return null;

  const [license] = await db.select().from(licenses).where(eq(licenses.token, token)).limit(1);

  if (!license || license.email !== email || !license.active) return null;
  return license;
}

Three different failures, one return value. Token does not exist, token exists but belongs to another email, licence has been deactivated: all null. The query is keyed on the token rather than the email on purpose, so the comparison that can distinguish cases happens in our process and never reaches a response body. notifio.app/pricing sells one thing and there is no account area to probe, but an endpoint that answers "that token is real, wrong email though" is a licence-key oracle, and there is no version of that which helps a legitimate user.

Prepared statements had to be turned off to talk to it

The connection file is shorter than the schema and contains the one thing in this post I would not have guessed:

const client =
  global._pgClient ??
  postgres(process.env.DATABASE_URL!, {
    // Supabase/PgBouncer "Transaction" pooler mode (port 6543) does NOT support
    // prepared statements. postgres-js creates them automatically by default,
    // which causes "prepared statement already exists" errors. Disable them.
    prepare: false,
    // Keep the per-instance pool small for serverless; the pooler multiplexes.
    max: 1,
    idle_timeout: 30,
    connect_timeout: 10,
  });

Transaction-mode pooling hands you a different backend connection per transaction, so a statement prepared on one is not there on the next, and the name collides when the pooler reuses a backend that already has it. postgres-js prepares automatically, which is the right default against a real connection and exactly wrong through a transaction pooler. The symptom is intermittent, because it only appears once the pooler has recycled a backend, so it will not reproduce locally and it will not reproduce on the first few deploys.

max: 1 is the other half of the same thought. Each serverless instance is handling one request, so a local pool of 20 is 20 connections held open to do the work of one, multiplied by however many instances are warm. The pooler is the thing doing the multiplexing, and competing with it is how a 0.5 GB Supabase project runs out of connections long before it runs out of rows.

if (process.env.NODE_ENV !== "production") {
  global._pgClient = client;
}

The global is only set in development, where hot reload re-evaluates the module and would otherwise open a new client on every save. In production the module is evaluated once per instance and a global would just be a global.

The accounting

Two migrations, ever. 0000 created the table with seven columns. 0001 added three columns and one constraint when auto-reply became a one-time purchase. That is the entire schema history of a live product that takes money.

What makes that possible is not discipline, it is that the product has no recurring state. There is no renewal, no seat count, no usage meter, no team, no plan tier. The 15 search limit in the app is a constant in the client, not a column, because it is a latency budget rather than a pricing tier. The settings live in a JSON file on the user's own machine. The listings it has found live in a 200 entry ring buffer next to it, and none of that is our problem to store.

The one time this schema genuinely did not fit was giving a licence away for free, because every row assumes a Stripe session paid for it. That is a different post: Giving away a licence when the schema assumes a payment.

If you want to see the product the table describes, it is at notifio.app and the setup walkthrough is notifio.app/help.