| name | stripe-credit-audit-trail |
| description | credit_transactions audit trail invariants and daily reconciliation patterns. PROACTIVELY activate for: (1) Designing a credit / balance / entitlement ledger, (2) credit_transactions row shape (delta, balanceAfter, reason, referenceType, referenceId, idempotencyKey), (3) Canonical idempotency key formats (refund:/debit:/stripe_checkout:/stripe_invoice:/stripe_refund:/stripe_signup_bonus:/dispute_hold:/dispute_restore:), (4) Daily reconciliation cron design (snapshot comparison, not full-sum), (5) Alert-only policy (never auto-correct drift), (6) Canonical refund helper pattern (single source of truth across pg-raw and Drizzle call sites), (7) Past_due block + credit balance shared preflight, (8) creditsDeducted boolean return pattern for pre- vs post-deduction error differentiation, (9) Money-safe email description based on resolvedVia flag, (10) Skip-users-without-baseline-snapshot reconciliation logic. Provides: table schema, canonical helper skeleton, reconciliation SQL, email gating pattern. |
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.
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 };
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., [orderId]);
: = zeroed.[]?. ?? ;
(returnedAmount <= ) {
pg.(, []);
{ : , : };
}
credited = pg.(., [returnedAmount, userId]);
: = credited.[].;
pg.(., [
userId,
returnedAmount,
creditedBalance,
orderId,
,
]);
pg.(, []);
{ : returnedAmount, : };
} (err) {
pg.(, []);
err;
}
}
(): <> {
zeroed = tx.(sql.(., orderId));
: = zeroed.[]?. ?? ;
(returnedAmount <= ) {
{ : , : };
}
credited = tx.(sql.(., returnedAmount, userId));
: = credited.[].;
tx.(
sql.(
.,
userId,
returnedAmount,
creditedBalance,
orderId,
,
),
);
{ : returnedAmount, : };
}
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
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.
