Nakodo 以用量事件記錄取代計數器欄位，退款時刪除記錄

Nakodo meters a handful of things per plan, and publishes the numbers on nakodo.app/pricing. Website research runs a month: 3 on Free, 30 on Pro, 150 on Business. Campaigns, search keywords per campaign, and so on down the table.

The obvious way to implement that is a counter: profiles.research_used_this_month, incremented on use, reset by a cron. We do not have one. There is no counter column anywhere in the schema, and src/lib/limits/ledger.ts is 96 lines that explain why in their header:

// Each user's metered actions, one usage_events row per action (or batch).
// The rows are the count: a limit is the sum of a kind's rows since its
// window started, and a refund shrinks or deletes a row. When the count
// decides whether an action goes ahead, read and write inside withUserLock so
// two requests can't both take the last one. The windows and the numbers are
// in ./plan.

The table

export const usageEvents = pgTable(
  "usage_events",
  {
    id: uuid().primaryKey().defaultRandom(),
    userId: uuid().notNull().references(() => authUsers.id, { onDelete: "cascade" }),
    kind: text({ enum: ["draft", "research", "search", "web_search", "instagram", "tiktok", "contact"] }).notNull(),
    quantity: integer().notNull().default(1),
    campaignId: uuid().references(() => campaigns.id, { onDelete: "set null" }),
    createdAt: createdAt(),
  },
  (t) => [
    index("usage_events_user_kind_idx").on(t.userId, t.kind, t.createdAt),
    index("usage_events_campaign_idx").on(t.campaignId, t.kind, t.createdAt),
  ],
).enableRLS();

Seven kinds, a quantity so a batch is one row, an optional campaign, and two indexes which are not decoration: they are the two questions anybody asks of this table. How much has this account used, and how much has this campaign used.

Reading a limit is one aggregate:

const total = sql<number>`coalesce(sum(${usageEvents.quantity}), 0)::int`;

export async function usedSince(scope: string | UsageScope, kind: UsageKind, since: Date, exec: Executor = db): Promise<number> {

coalesce because no rows is zero rather than null, and ::int because sum() over an integer column comes back as bigint, which the driver hands you as a string. Skip the cast and your limit comparison is "3" >= 3, which is false, which is a free extra research run for everybody.

The scope is a union rather than two functions:

export type UsageScope = { userId: string } | { campaignId: string };

and the window is a parameter, not a constant, because none of these windows is a calendar month. The research window starts at the Stripe subscription's current period start:

const sub = await getSubscription(userId, tx);
const max = sub.effective.limits.researchPerMonth;
const left = Math.max(0, max - (await usedSince(userId, "research", sub.currentPeriodStart, tx)));

Someone who subscribed on the 9th gets their allowance back on the 9th. With a counter column plus a monthly reset job, that is a cron you have to write, a timezone you have to pick, and a bug you get to find on the 31st. With rows and a window, the "reset" is the window moving and there is no job at all.

Spending, with a receipt

export async function consumeResearchIn(tx: Tx, userId: string, opts: ConsumeOptions = {}): Promise<{ granted: number; eventId: string }> {
  const quantity = opts.quantity ?? 1;
  const sub = await getSubscription(userId, tx);
  const max = sub.effective.limits.researchPerMonth;
  const left = Math.max(0, max - (await usedSince(userId, "research", sub.currentPeriodStart, tx)));
  const granted = opts.partial ? Math.min(left, quantity) : left >= quantity ? quantity : 0;
  if (granted === 0) {
    throw new LimitError(
      "researchPerMonth",
      `You've used all ${plural(max, "website research run")} on the ${sub.effective.name} plan this month.${upgradeHint(sub.effective)}`,
    );
  }
  const eventId = (await record(tx, { userId, kind: "research", quantity: granted, campaignId: opts.campaignId }))!;
  return { granted, eventId };
}

Two things to notice.

partial picks between two policies in one expression. A user-initiated action wants all or nothing, because granting two of the five runs somebody asked for is a worse outcome than telling them the number. A background batch wants as many as are left, because doing four fifths of the work is fine.

And the function returns the eventId. That is the receipt, and it is the whole reason this design is nicer than a counter.

A refund is a delete

// Gives back a whole event, or `quantity` of it.
export async function refund(eventId: string, quantity?: number): Promise<void> {
  if (quantity === undefined) {
    await db.delete(usageEvents).where(eq(usageEvents.id, eventId));
    return;
  }
  if (quantity <= 0) return;
  const [left] = await db
    .update(usageEvents)
    .set({ quantity: sql`${usageEvents.quantity} - ${quantity}` })
    .where(eq(usageEvents.id, eventId))
    .returning({ quantity: usageEvents.quantity });
  if (left && left.quantity <= 0) await db.delete(usageEvents).where(eq(usageEvents.id, eventId));
}

No row goes negative, and a fully refunded row leaves no trace. Compare the counter version: set research_used = research_used - 1, which is a statement with no idea whether the thing it is decrementing was ever incremented. Run it twice and you have minted allowance out of nothing. Here, a double refund is a delete of a row that is already gone.

The pattern shows up at every call site: spend first, get an id, hand the id to the failure path. The campaign creation flow is the clearest version of it:

const { id, eventId } = await withUserLock(userId, async (tx) => {
  await assertCanCreateCampaign(userId, tx);
  const [row] = await tx.insert(campaigns).values({ ... }).returning({ id: campaigns.id });
  const { eventId } = await consumeResearchIn(tx, userId, { campaignId: row.id });
  return { id: row.id, eventId };
});

const outcome = await runCampaignResearch(id, { userId, notes });
if (!outcome.ok) {
  await refund(eventId);
  await deleteCampaign(id);
  ...
  throw new ResearchError(outcome.error);
}

The campaign slot check, the insert and the spend are one transaction under one lock. The expensive part, which crawls a website and calls a model and takes up to 40 seconds, is outside it, holding nothing but an id it can use to undo the charge.

The refund nobody is around to make

The research call runs inside the request that started it. If that request is cut off, by a deploy, a tab closing, a platform timeout, there is nobody left to call refund(eventId). So there is a second path that reconstructs the receipt instead of being handed it:

// Refunds the latest research run charged to a campaign, if it was charged in
// the last hour. Used when research was cut off before it could refund itself.
export async function refundLatestResearch(campaignId: string): Promise<void> {
  await db.execute(sql`
    delete from ${usageEvents} where id = (
      select id from ${usageEvents}
      where campaign_id = ${campaignId} and kind = 'research' and created_at > now() - interval '1 hour'
      order by created_at desc limit 1
    )`);
}

Raw SQL for a reason: it is a delete of "the most recent one", which is a subquery with an order and a limit, and the hour bound is what stops a recovery sweep refunding a charge from last week. It is called by the sweep that notices a campaign stuck in researching:

.set({
  status: sql`case when ${campaigns.brief} is null then 'failed' when ${campaigns.launchedAt} is not null then 'paused' else 'draft' end`,
  ...
})

One UPDATE puts every stranded campaign back where it was (never researched becomes failed, previously launched comes back paused for a human to resume), and then each one gets its run back.

The one rule the design imposes

Rows as the count has exactly one sharp edge: between reading the sum and writing the row, another request can read the same sum. Two tabs, one remaining run, two runs granted. So anything where the count decides the answer runs inside the account's lock, and the lock is three lines:

// Runs `fn` in a transaction that holds an advisory lock on `key` until it
// ends, so two requests can't both act on the same thing at once.
export function withLock<T>(key: string, fn: (tx: Tx) => Promise<T>): Promise<T> {
  return db.transaction(async (tx) => {
    await tx.execute(sql`select pg_advisory_xact_lock(hashtext(${key}))`);
    return fn(tx);
  });
}

// Serialises limit checks per user, so two requests can't both spend the last one.
export const withUserLock = <T>(userId: string, fn: (tx: Tx) => Promise<T>) => withLock(userId, fn);

A transaction-scoped advisory lock needs no unlock call and no cleanup after a crashed process: it ends with the transaction, whichever way the transaction ends. hashtext is there because advisory locks take integers and our keys are UUID strings. There are eight call sites in the app, and every one of them is a limit that decides whether something happens.

Where the design bites back

Three honest notes.

hashtext returns a 32-bit integer, so two different users can hash to the same lock key. The consequence is mild (two unrelated accounts briefly serialise their limit checks) and the probability is not as small as people assume, because it is a birthday problem rather than a direct collision. Worth knowing before you spend an afternoon wondering why a write waited.

The table only grows. Nearly everything else in this database has a retention rule enforced by the daily maintenance job, and usage_events has none: nothing purges it, because deleting a row inside a live window would hand someone allowance back. Rows older than the longest window are inert, though, and could be dropped. Today they are not, which means the oldest account's ledger is as long as its whole history. The rows do go in the user's data export, and they cascade away with the account.

And the substrate is not uniform. Some metered actions have a ledger kind; others are counted off the audit log, which is also append-only:

// Use that has no ledger kind is counted from the audit rows it leaves, over
// the UTC day: the rows of `action`, or the sum of `quantity` over them.

So "where is this limit counted" has two answers, and the dividing line is whether the action needs a receipt that can be refunded. A research run can fail after being charged, so it gets a row and an id. A rewrite cannot fail halfway in a way that deserves a refund, so counting the audit trail it was going to write anyway is enough. The audit log was already the thing that answers "what happened"; for those kinds it answers "how many" for free.

Both halves share the one property that makes this design work: nothing in the system stores a number that could be wrong. It stores events, and the number is derived.