Double-entry accounting with pgledger

We needed to properly track financial flows in our software.

It was already done using various database tables materializing money transfers between our system and accounts on an external PSP (Payment Service Provider).

But we needed a way to track these flows in a central location, and enforce additional constraints, like an account cannot have a negative balance.

Accounting 101

What is the proper way to do this? Double-entry accounting, known since the 13th century. Every accountant knows it.

The properties of such a system are:

Money is stored into various accounts
Money is moved using transfers that debit one account and credit another
The sum of all debits equals the sum of all credits in every transfer

Introducing pgledger

pgledger is a double-entry accounting implementation in pure PostgreSQL.

It has been introduced less than one year ago by its author in this post: Ledger Implementation in PostgreSQL.

Then the author followed up with posts about pgledger performance and some use cases for pgledger.

As a quick example, accounts are created using SQL functions:

select id from pgledger_create_account('arbitrary_account_name_1', 'EUR');
select id from pgledger_create_account('arbitrary_account_name_2', 'EUR');

Then data can be read using a view:

select id, name, balance from pgledger_accounts_view;
               id                │           name           │ balance 
═════════════════════════════════╪══════════════════════════╪═════════
 pgla_01KF419AC1ESQRA2QV2CEN1HE1 │ arbitrary_account_name_1 │       0
 pgla_01KF419DK4EGCRRTN8JPNDN7CA │ arbitrary_account_name_2 │       0

Again, transfers are made using a function:

select * from pgledger_create_transfer('pgla_01KF419AC1ESQRA2QV2CEN1HE1', 'pgla_01KF419DK4EGCRRTN8JPNDN7CA', 32.5);

Here is the new state of accounts with their new balances:

select id, name, balance from pgledger_accounts_view;
              id                │           name           │ balance 
═════════════════════════════════╪══════════════════════════╪═════════
 pgla_01KF419AC1ESQRA2QV2CEN1HE1 │ arbitrary_account_name_1 │   -32.5
 pgla_01KF419DK4EGCRRTN8JPNDN7CA │ arbitrary_account_name_2 │    32.5

You can enforce constraints on accounts, like forbidding to have a negative balance. It’s also possible to add free key-value metadata on accounts and transfers.

The codebase contains more useful examples.