DOCSEARCHbyAlgolia
Submit Docs

pgledger

repository·main·Indexed 19 days ago

https://github.com/pgr0ss/pgledger

A reusable double-entry bookkeeping ledger implementation built entirely within PostgreSQL using tables, functions, and views. It ensures atomic transactional guarantees by allowing ledger operations to be part of the same transaction as application data. Features include prefixed ULID identifiers, support for multi-currency exchanges via liquidity accounts, and the ability to reconstruct historical account balances using recorded previous and current balances.

Tokens
2.2K
Snippets
6
Records
10
Agent score
17%

What's inside pgledger

  1. What is pgledger

    main
    pgledger is a double-entry bookkeeping ledger implementation built entirely within PostgreSQL. Instead of using application-level logic, the entire ledger system is implemented using PostgreSQL tables, functions, and views. This design allows you to interact with the ledger by calling SQL functions and querying SQL views, ensuring that ledger entries can be part of the same atomic transactions as the rest of your application data.

  2. Understand prefixed ULID identifiers

    main

    All table IDs in pgledger are prefixed ULIDs. This provides several benefits:

    • Type Safety: Prefixes (e.g., pgla_ for accounts, pglt_ for transfers) make it easy to distinguish ID types.
    • Ordering: ULIDs are monotonically increasing, ensuring IDs are generated in sorted order.
    • Compatibility: ULIDs can be converted to UUIDs for future storage optimizations.
    • Format: They are URL-safe and more compact than standard UUID strings.

    Example IDs:

    • Account: pgla_01JTVST7XAES5BXHWZN4KR4VEZ
    • Transfer: pglt_01JTVR1WKXEKCRG7N6YD7XCZA6

  3. Manage multi-currency exchanges

    main

    Each account in pgledger is restricted to a single currency. To handle exchanges between different currencies (e.g., USD to EUR), you must use a multi-step approach involving liquidity/system accounts to ensure the ledger remains balanced (debits and credits sum to zero for every currency).

    An exchange typically requires:

    1. Two user accounts (one for each currency).
    2. Two system/liquidity accounts (one for each currency).
    3. Two simultaneous transfers (one per currency) created via pgledger_create_transfers.
    -- Setup accounts
select id from pgledger_create_account('user1.USD', 'USD');
select id from pgledger_create_account('user1.EUR', 'EUR');
select id from pgledger_create_account('liquidity.USD', 'USD');
select id from pgledger_create_account('liquidity.EUR', 'EUR');

-- Perform exchange using two simultaneous transfers
select * from pgledger_create_transfers(
    ($user1_usd, $liquidity_usd, '10.00'), 
    ($liquidity_eur, $user1_eur, '9.26')
);

  4. Look up historical account balances

    main
    Because every entry row in pgledger records both the account_previous_balance and the account_current_balance, you can reconstruct the state of an account at any point in time. To find a historical balance, locate the most recent entry row where the timestamp is prior to your target time.

  5. Use `event_at` for historical accuracy

    main

    The pgledger_create_transfer function accepts an optional event_at timestamp.

    • If omitted, event_at defaults to now() (matching created_at).
    • Use event_at to record when a real-world event actually occurred (e.g., a bank webhook timestamp) rather than when the record was inserted into the database.

    When querying ledger history for a specific time period, you should filter by event_at instead of created_at to ensure the results align with real-world timelines.

    -- Recording a transfer with an explicit event timestamp
select * from pgledger_create_transfer($account_1_id, $account_2_id, 12.34, '2025-07-01T12:34:56Z');

-- Or using named parameter syntax
select * from pgledger_create_transfer($account_1_id, $account_2_id, 12.34, event_at => '2025-07-01T12:34:56Z');

-- Querying entries based on when the event happened
select * from pgledger_entries_view
where account_id = $account_id
and event_at >= '2025-06-01'
and event_at < '2025-07-01'
order by event_at;

  6. Basic Usage: Create accounts and transfers

    main

    The ledger is managed via SQL functions for mutations (appending to the ledger) and views for querying.

    To set up accounts, use pgledger_create_account(name, currency). To move funds, use pgledger_create_transfer(from_account_id, to_account_id, amount).

    To query the current state, use the pgledger_accounts_view to see balances and pgledger_entries_view to see the history of entries for a specific account.

    -- Set up accounts
select id from pgledger_create_account('account_1', 'USD');
select id from pgledger_create_account('account_2', 'USD');

-- Create transfers
select * from pgledger_create_transfer($account_1_id, $account_2_id, 12.34);

-- See updated balances
select name, balance, version from pgledger_accounts_view where id = $account_2_id;

-- See ledger entries
select created_at, account_version, amount, account_previous_balance, account_current_balance 
from pgledger_entries_view 
where account_id = $account_2_id 
order by id;

  7. Install pgledger

    main

    To install pgledger, you must execute a specific sequence of SQL files in your PostgreSQL database. The installation requires helper functions for ULID/UUID conversion which are vendored within the repository.

    Follow these steps in order:

    1. Run the vendored scoville/pgsql-ulid helper files:
      • vendor/scoville-pgsql-ulid/ulid-to-uuid.sql
      • vendor/scoville-pgsql-ulid/uuid-to-ulid.sql
    2. Run the main implementation file:
      • pgledger.sql

  8. Set up the pgledger development environment

    main

    The pgledger implementation is written in SQL. To develop or run tests, you need several tools for dependency management, testing, task running, SQL linting, and database hosting.

    On MacOS, you can install the primary dependencies using Homebrew:

    1. Install mise and docker-desktop via Homebrew.
    2. Use mise install to set up tool dependencies.
    3. Use docker compose up to start a PostgreSQL instance.
    4. Use just check to execute the full suite of tests and linters.
    brew install mise docker-desktop

mise install

docker compose up

just check

  9. Run pgledger with Docker Compose

    main

    You can use the provided docker-compose.yml to spin up a PostgreSQL instance configured for pgledger. The setup includes a container named pgledger_postgres that exposes port 5432 and uses default credentials for development.

    Default Credentials

    • User: pgledger
    • Password: pgledger
    • Database Port: 5432

    Configuration via Environment Variables

    You can control the PostgreSQL version using the POSTGRES_VERSION environment variable. If not provided, it defaults to version 18.

    Volume Mappings

    • ./psqlrc is mapped to /root/.psqlrc inside the container (useful for custom CLI settings).
    • The current directory . is mapped to /code inside the container.
    docker-compose up

  10. Compose ledger queries using CTEs and Joins

    main

    Since pgledger is built on standard SQL, you can use Common Table Expressions (CTEs) to join the results of mutation functions (like pgledger_create_transfer) with other views (like pgledger_accounts_view) to retrieve enriched data, such as account names instead of just IDs, in a single atomic operation.

    with transfer as (
    select * from pgledger_create_transfer('pgla_01KBE8WV6PE2BSZHVKDD5TSEBZ', 'pgla_01KBE8WV6QESATM17SHW189Q0H', 10)
)
select
    t.id,
    fa.name as from_account_name,
    ta.name as to_account_name,
    t.amount
from transfer t
join pgledger_accounts_view fa on t.from_account_id = fa.id
join pgledger_accounts_view ta on t.to_account_id = ta.id;