Stripe Credit Audit Trail

Quick Reference

Pick one domain prefix that matches your billable entity (order, job, item, etc.) and use it consistently. The table below uses order as the neutral example:

When to Use This Skill

Use whenever any path mutates a balance / credit / entitlement column; whenever a reconciliation or audit cron is being designed; whenever a refund helper is being extended.

Related skills:

• For dispute-hold lifecycle and shouldRestoreStatus: stripe-billing-master:stripe-refund-dispute-lifecycle
• For the G1 checkpoint that precedes every balance mutation: stripe-billing-master:stripe-webhook-idempotency
• For the resolvedVia flag that gates money-safe email rendering: stripe-billing-master:stripe-list-pagination-previous-attributes

Canonical refund helper pattern

In the example below, orders.amount_used is a per-order ledger of credits spent on that order. Zeroing it on refund returns the spent amount (via RETURNING), which is then re-credited to users.credit_balance in the same transaction. Rename orders / amount_used to match your billable-entity schema.

One helper. Two call patterns (pg-raw for Cloudflare Workers / Node workers, Drizzle for Next.js route handlers). Byte-identical SQL kept as exported constants. In the example below, orders stands in for whatever your billable-entity table is named; rename consistently across the helper, the key format, and all call sites.

// example path: packages/shared/src/refund-credits.ts
export const REFUND_ORDER_SQL = {
  zeroOrder: `UPDATE orders SET amount_used = 0 WHERE id = $1 AND amount_used > 0 RETURNING amount_used`,
  creditUser: `UPDATE users SET credit_balance = credit_balance + $1 WHERE id = $2 RETURNING credit_balance`,
  insertTransaction: `INSERT INTO credit_transactions (user_id, delta, balance_after, reason, reference_type, reference_id, idempotency_key)
    VALUES ($1, $2, $3, 'order_refunded', 'order', $4, $5)
    ON CONFLICT (idempotency_key) WHERE idempotency_key IS NOT NULL DO NOTHING`,
};

type RefundArgs = { userId: string; orderId: string };
type RefundResult = { refunded: number; alreadyRefunded: boolean };

// pg-raw variant (Workers, Node workers, any pg.Client / pg.Pool compatible driver).
export async function refundOrderCreditsPg(
  pg: { query: (sql: string, params: unknown[]) => Promise<{ rows: any[] }> },
  { userId, orderId }: RefundArgs,
): Promise<RefundResult> {
  await pg.query("BEGIN", []);
  try {
    const zeroed = await pg.query(REFUND_ORDER_SQL.zeroOrder, [orderId]);
    const returnedAmount: number = zeroed.rows[0]?.amount_used ?? 0;
    if (returnedAmount <= 0) {
      await pg.query("COMMIT", []);
      return { refunded: 0, alreadyRefunded: true };
    }

    const credited = await pg.query(REFUND_ORDER_SQL.creditUser, [returnedAmount, userId]);
    const creditedBalance: number = credited.rows[0].credit_balance;

    await pg.query(REFUND_ORDER_SQL.insertTransaction, [
      userId,
      returnedAmount,        // delta (+returnedAmount)
      creditedBalance,       // balance_after snapshot
      orderId,               // reference_id
      `refund:${orderId}`,   // idempotency_key
    ]);

    await pg.query("COMMIT", []);
    return { refunded: returnedAmount, alreadyRefunded: false };
  } catch (err) {
    await pg.query("ROLLBACK", []);
    throw err;
  }
}

// Drizzle variant (Next.js route handlers / any Drizzle tx). Caller passes tx + sql tag.
export async function refundOrderCreditsDrizzle(
  tx: { execute: (q: unknown) => Promise<{ rows: any[] }> },
  sql: { raw: (s: string, ...p: unknown[]) => unknown },
  { userId, orderId }: RefundArgs,
): Promise<RefundResult> {
  const zeroed = await tx.execute(sql.raw(REFUND_ORDER_SQL.zeroOrder, orderId));
  const returnedAmount: number = zeroed.rows[0]?.amount_used ?? 0;
  if (returnedAmount <= 0) {
    return { refunded: 0, alreadyRefunded: true };
  }

  const credited = await tx.execute(sql.raw(REFUND_ORDER_SQL.creditUser, returnedAmount, userId));
  const creditedBalance: number = credited.rows[0].credit_balance;

  await tx.execute(
    sql.raw(
      REFUND_ORDER_SQL.insertTransaction,
      userId,
      returnedAmount,        // delta (+returnedAmount)
      creditedBalance,       // balance_after snapshot
      orderId,               // reference_id
      `refund:${orderId}`,   // idempotency_key
    ),
  );

  return { refunded: returnedAmount, alreadyRefunded: false };
}

Call sites: every service, queue consumer, and worker entry point that refunds on behalf of a user MUST delegate to this helper. Never add a new refund call site with inline SQL — that's how audit rows go missing on one branch and the reconciliation cron stops being authoritative.

Reconciliation cron

-- Snapshot comparison, no full sum
WITH latest AS (
  SELECT DISTINCT ON (user_id) user_id, balance_after, created_at
  FROM credit_transactions
  ORDER BY user_id, created_at DESC
)
SELECT u.id, u.credit_balance AS actual, l.balance_after AS expected, u.credit_balance - l.balance_after AS drift
FROM users u
JOIN latest l ON l.user_id = u.id
WHERE u.credit_balance <> l.balance_after;

Alert-only policy — never auto-correct. Users with zero credit_transactions rows since the table landed have no baseline; skip them.

creditsDeducted boolean pattern

Split credit-deduction and queue-submission into separate try/catch blocks. Return shape always carries creditsDeducted: boolean so callers can distinguish:

• DB error BEFORE deduction -> creditsDeducted=false, caller SKIPS refund
• DB error AFTER deduction -> creditsDeducted=true, caller refunds via canonical helper
• Queue submission failure -> creditsDeducted=true, caller refunds

Never gate refunds on the order row's amount_used column — it was set BEFORE the UPDATE succeeded, so reading it post-error can mint free credits for deductions that never actually committed. Rely on the explicit creditsDeducted flag returned from the deduction function instead.