Skill-Datei

Double Entry Ledger

Name: Double Entry Ledger
Author: tombensim

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.

tombensim0 Sterne27.01.2026

Beruf
Kategorien: Geschäftsanwendungen

Skill-Inhalt

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

Verwandte Skills

Double Entry Ledger | Skills Pool

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

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;

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

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

Double Entry Ledger

When to Apply

Core Concepts

Account Types

Transaction Rules

Double Entry Ledger

When to Apply

Core Concepts

Account Types

Transaction Rules

Balance Calculation

SQL Patterns

Schema

Charge a Member

Record Payment

Void a Transaction

Get Member Balance

Validation: Ledger Must Balance

Bulk Operations

Charge All Members for an Event

Jupyter Notebook

Startup Metrics Framework

Billing Automation

Internal Comms Community

Sales Automator

Internal Comms Anthropic