JosiahSiegel/stripe-credit-audit-trail

Quick Reference

| Field | Purpose | |--|--| | userId | Who the transaction belongs to | | delta (signed int) | +granted / -revoked | | balanceAfter (int snapshot) | Reconciliation baseline | | reason (enum) | Categorical — one of a fixed set | | referenceType + referenceId | Pointer to the originating entity | | idempotencyKey (UNIQUE partial index) | Dedup + G1 checkpoint | | metadata (jsonb) | Free-form context (previousStatus, resolvedVia, etc.) |

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:

| Idempotency key format | When | |--|--| | debit:{orderId} | order creation debit | | refund:{orderId} | order refund | | debit:batch:{batchId} | batch debit | | refund_unused:batch:{batchId} | batch partial refund | | stripe_checkout:{sessionId} | credit pack grant | | stripe_invoice:{invoiceId} | subscription renewal | | stripe_refund:{eventId} | Stripe refund event | | stripe_signup_bonus:{userId} | signup bonus | | dispute_hold:{disputeId} | dispute-initiated past_due hold | | dispute_restore:{disputeId} | dispute closure status restore |

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.