double-entry-ledger

name	double-entry-ledger
description	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.
license	MIT
metadata	{"author":"tennis-team","version":"1.0.0"}

Double-Entry Ledger

Patterns for implementing a double-entry accounting ledger for tennis group billing.

When to Apply

Reference these guidelines when:

• Implementing charge, payment, adjustment, or void operations
• Calculating member balances
• Building ledger views and summaries
• Auto-charging on event/tournament registration
• Generating financial reports

Core Concepts

Account Types

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

Transaction Rules

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

Balance Calculation

Member balance = SUM(debits) - SUM(credits) on their account.

• Positive balance = member owes money
• Negative balance = member has credit (overpaid)
• Zero = settled

SQL Patterns

Schema

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()
);

Charge a Member

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();
  }
}

Record Payment

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();
  }
}

Void a Transaction

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();
  }
}

Get Member Balance

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;

Validation: Ledger Must Balance

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.

Bulk Operations

Charge All Members for an Event

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,
    );
  }
}