Double-entry accounting ledger patterns for group billing. This skill should be used when implementing the billing/ledger system, recording financial transactions, calculating balances, or handling charges, payments, adjustments, and voids. Triggers on tasks involving billing, ledger, accounting, charges, payments, balances, or financial transactions.
Patterns for implementing a double-entry accounting ledger for tennis group billing.
Reference these guidelines when:
Each group has system accounts + one account per member:
| Account | Type | Purpose |
|---|---|---|
| REVENUE | System | Tracks fees charged |
| CASH | System | Tracks payments received |
| Member account | Member | Tracks member's balance |
Every transaction creates balanced debit + credit entries:
| Operation | Debit | Credit | Effect |
|---|---|---|---|
| Charge fee | Member | REVENUE | Member owes money |
| Record payment | CASH | Member | Member paid, reduces balance |
| Adjustment (reduce) | REVENUE | Member | Reduce what member owes |
| Adjustment (add) | Member | REVENUE | Add to what member owes |
| Void | Reverse original entries | Nullifies a transaction |
Member balance = SUM(debits) - SUM(credits) on their account.
CREATE TABLE ledger_accounts (
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
group_id UUID NOT NULL REFERENCES groups(id),
user_id UUID REFERENCES users(id), -- NULL for system accounts
type VARCHAR(20) NOT NULL, -- 'MEMBER', 'REVENUE', 'CASH'
name VARCHAR(100) NOT NULL,
created_at TIMESTAMPTZ DEFAULT NOW(),
UNIQUE (group_id, user_id), -- One account per member per group
UNIQUE (group_id, type) WHERE type IN ('REVENUE', 'CASH') -- One system account per type
);
CREATE TABLE ledger_transactions (
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
group_id UUID NOT NULL REFERENCES groups(id),
type VARCHAR(20) NOT NULL, -- 'CHARGE', 'PAYMENT', 'ADJUSTMENT', 'VOID'
description TEXT NOT NULL,
reference_type VARCHAR(20), -- 'EVENT', 'TOURNAMENT', 'MEMBERSHIP', NULL
reference_id UUID, -- event_id, tournament_id, etc.
amount NUMERIC(10,2) NOT NULL,
voided_at TIMESTAMPTZ,
voided_by UUID REFERENCES users(id),
void_of_id UUID REFERENCES ledger_transactions(id), -- Points to original if this is a void
created_by UUID NOT NULL REFERENCES users(id),
created_at TIMESTAMPTZ DEFAULT NOW()
);
CREATE TABLE ledger_entries (
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
transaction_id UUID NOT NULL REFERENCES ledger_transactions(id),
account_id UUID NOT NULL REFERENCES ledger_accounts(id),
type VARCHAR(6) NOT NULL CHECK (type IN ('DEBIT', 'CREDIT')),
amount NUMERIC(10,2) NOT NULL CHECK (amount > 0),
created_at TIMESTAMPTZ DEFAULT NOW()
);
async function chargeMember(
groupId: string,
memberId: string,
amount: number,
description: string,
referenceType?: string,
referenceId?: string,
createdBy: string,
) {
const client = await pool.connect();
try {
await client.query('BEGIN');
// Get accounts
const {
rows: [memberAccount],
} = await client.query(`SELECT id FROM ledger_accounts WHERE group_id = $1 AND user_id = $2`, [
groupId,
memberId,
]);
const {
rows: [revenueAccount],
} = await client.query(
`SELECT id FROM ledger_accounts WHERE group_id = $1 AND type = 'REVENUE'`,
[groupId],
);
// Create transaction
const {
rows: [txn],
} = await client.query(
`INSERT INTO ledger_transactions
(group_id, type, description, reference_type, reference_id, amount, created_by)
VALUES ($1, 'CHARGE', $2, $3, $4, $5, $6) RETURNING *`,
[groupId, description, referenceType, referenceId, amount, createdBy],
);
// Create balanced entries: DEBIT member (they owe), CREDIT revenue
await client.query(
`INSERT INTO ledger_entries (transaction_id, account_id, type, amount)
VALUES ($1, $2, 'DEBIT', $3), ($1, $4, 'CREDIT', $3)`,
[txn.id, memberAccount.id, amount, revenueAccount.id],
);
await client.query('COMMIT');
return txn;
} catch (err) {
await client.query('ROLLBACK');
throw err;
} finally {
client.release();
}
}
async function recordPayment(
groupId: string,
memberId: string,
amount: number,
description: string,
createdBy: string,
) {
const client = await pool.connect();
try {
await client.query('BEGIN');
const {
rows: [memberAccount],
} = await client.query(`SELECT id FROM ledger_accounts WHERE group_id = $1 AND user_id = $2`, [
groupId,
memberId,
]);
const {
rows: [cashAccount],
} = await client.query(`SELECT id FROM ledger_accounts WHERE group_id = $1 AND type = 'CASH'`, [
groupId,
]);
const {
rows: [txn],
} = await client.query(
`INSERT INTO ledger_transactions
(group_id, type, description, amount, created_by)
VALUES ($1, 'PAYMENT', $2, $3, $4) RETURNING *`,
[groupId, description, amount, createdBy],
);
// DEBIT cash (money received), CREDIT member (balance reduced)
await client.query(
`INSERT INTO ledger_entries (transaction_id, account_id, type, amount)
VALUES ($1, $2, 'DEBIT', $3), ($1, $4, 'CREDIT', $3)`,
[txn.id, cashAccount.id, amount, memberAccount.id],
);
await client.query('COMMIT');
return txn;
} catch (err) {
await client.query('ROLLBACK');
throw err;
} finally {
client.release();
}
}
async function voidTransaction(transactionId: string, voidedBy: string) {
const client = await pool.connect();
try {
await client.query('BEGIN');
// Mark original as voided
const {
rows: [original],
} = await client.query(
`UPDATE ledger_transactions SET voided_at = NOW(), voided_by = $2
WHERE id = $1 AND voided_at IS NULL RETURNING *`,
[transactionId, voidedBy],
);
if (!original) throw new ConflictError('Transaction already voided or not found');
// Get original entries
const { rows: entries } = await client.query(
`SELECT * FROM ledger_entries WHERE transaction_id = $1`,
[transactionId],
);
// Create reversing transaction
const {
rows: [voidTxn],
} = await client.query(
`INSERT INTO ledger_transactions
(group_id, type, description, amount, void_of_id, created_by)
VALUES ($1, 'VOID', $2, $3, $4, $5) RETURNING *`,
[original.group_id, `Void: ${original.description}`, original.amount, original.id, voidedBy],
);
// Create reversed entries (swap DEBIT/CREDIT)
for (const entry of entries) {
await client.query(
`INSERT INTO ledger_entries (transaction_id, account_id, type, amount)
VALUES ($1, $2, $3, $4)`,
[voidTxn.id, entry.account_id, entry.type === 'DEBIT' ? 'CREDIT' : 'DEBIT', entry.amount],
);
}
await client.query('COMMIT');
return voidTxn;
} catch (err) {
await client.query('ROLLBACK');
throw err;
} finally {
client.release();
}
}
SELECT
COALESCE(SUM(CASE WHEN le.type = 'DEBIT' THEN le.amount ELSE 0 END), 0) -
COALESCE(SUM(CASE WHEN le.type = 'CREDIT' THEN le.amount ELSE 0 END), 0) AS balance
FROM ledger_entries le
JOIN ledger_transactions lt ON le.transaction_id = lt.id
WHERE le.account_id = $1
AND lt.voided_at IS NULL;
Every transaction's entries must sum to zero (total debits = total credits):
-- Audit query: find unbalanced transactions
SELECT
lt.id,
SUM(CASE WHEN le.type = 'DEBIT' THEN le.amount ELSE -le.amount END) AS imbalance
FROM ledger_transactions lt
JOIN ledger_entries le ON le.transaction_id = lt.id
GROUP BY lt.id
HAVING SUM(CASE WHEN le.type = 'DEBIT' THEN le.amount ELSE -le.amount END) != 0;
This query should always return zero rows. Run it as a health check.
async function chargeEventFee(eventId: string, createdBy: string) {
const { rows: participants } = await pool.query(
`SELECT ep.user_id, e.cost_per_player, e.group_id, e.title
FROM event_participants ep
JOIN events e ON ep.event_id = e.id
WHERE ep.event_id = $1 AND ep.status = 'CONFIRMED' AND e.cost_per_player > 0`,
[eventId],
);
for (const p of participants) {
await chargeMember(
p.group_id,
p.user_id,
p.cost_per_player,
`Event fee: ${p.title}`,
'EVENT',
eventId,
createdBy,
);
}
}