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.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,
creditedBalance,
orderId,
`refund:${orderId}`,
]);
await pg.query("COMMIT", []);
return { refunded: returnedAmount, alreadyRefunded: false };
} catch (err) {
await pg.query("ROLLBACK", []);
throw err;
}
}
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,
creditedBalance,
orderId,
`refund:${orderId}`,
),
);
return { refunded: returnedAmount, alreadyRefunded: false };
}
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;
