basic-example.sql.out

-- This file contains the sql queries plus their output, but we set the filetype to sql for better syntax highlighting
-- vim: set filetype=sql:

-- This is a fully working example script that shows how to use pgledger
--
-- Note that it uses `\gset` to store sql responses as variables. For example,
-- `\gset foo_` creates variables for each column in the response like
-- `foo_col1`, `foo_col2`, etc. These variables can then be used like
-- `:'foo1_col`.
-- The entire script can be passed to psql. If you are running postgres via the
-- pgledger docker compose, you can run this script with:
--
--   cat basic-example.sql | \
--     docker compose exec --no-TTY postgres psql -U pgledger --echo-all --no-psqlrc
--
-- We're going to simulate a simple payment flow. First, we create our accounts:
SELECT id FROM pgledger_create_account('user1.external', 'USD') \gset user1_external_
SELECT id FROM pgledger_create_account('user1.receivables', 'USD') \gset user1_receivables_
-- Note that we may want to prevent some accounts from going negative or positive:
SELECT id FROM pgledger_create_account('user1.available', 'USD', allow_negative_balance => FALSE) \gset user1_available_
SELECT id FROM pgledger_create_account('user1.pending_outbound', 'USD') \gset user1_pending_outbound_
-- We can query accounts to see what they looks like at the beginning.
SELECT * FROM pgledger_accounts_view
WHERE id IN (:'user1_external_id',:'user1_available_id');
               id                |      name       | currency | balance | version | allow_negative_balance | allow_positive_balance | metadata |          created_at           |          updated_at           
---------------------------------+-----------------+----------+---------+---------+------------------------+------------------------+----------+-------------------------------+-------------------------------
 pgla_01KEA9YZ81FA4SH7EN1KN633NF | user1.external  | USD      |       0 |       0 | t                      | t                      |          | 2026-01-06 18:43:58.848554+00 | 2026-01-06 18:43:58.848554+00
 pgla_01KEA9YZ82F7397D24286T0WFK | user1.available | USD      |       0 |       0 | f                      | t                      |          | 2026-01-06 18:43:58.850575+00 | 2026-01-06 18:43:58.850575+00
(2 rows)

-- The first step in the flow is a $50 payment is created and we are waiting for funds to arrive:
SELECT * FROM pgledger_create_transfer(:'user1_external_id',:'user1_receivables_id', 50.00);
               id                |         from_account_id         |          to_account_id          | amount |          created_at          |           event_at           | metadata 
---------------------------------+---------------------------------+---------------------------------+--------+------------------------------+------------------------------+----------
 pglt_01KEA9YZ83FZ78XHYCZ4233TNW | pgla_01KEA9YZ81FA4SH7EN1KN633NF | pgla_01KEA9YZ82ESHTB6WZTWJTKVYN |  50.00 | 2026-01-06 18:43:58.85121+00 | 2026-01-06 18:43:58.85121+00 | 
(1 row)

-- Next, the funds arrive in our account, so we remove them from receivables and make them available:
SELECT * FROM pgledger_create_transfer(:'user1_receivables_id',:'user1_available_id', 50.00);
               id                |         from_account_id         |          to_account_id          | amount |          created_at           |           event_at            | metadata 
---------------------------------+---------------------------------+---------------------------------+--------+-------------------------------+-------------------------------+----------
 pglt_01KEA9YZ84FPPTADA0Z68QB6JP | pgla_01KEA9YZ82ESHTB6WZTWJTKVYN | pgla_01KEA9YZ82F7397D24286T0WFK |  50.00 | 2026-01-06 18:43:58.852694+00 | 2026-01-06 18:43:58.852694+00 | 
(1 row)

-- Now, we can query the accounts and see the balances. We aren't waiting on
-- any more funds, so the receivables balance is 0:
SELECT balance FROM pgledger_accounts_view
WHERE id =:'user1_receivables_id';
 balance 
---------
    0.00
(1 row)

-- And we can see the entries for the receivables account:
SELECT * FROM pgledger_entries_view
WHERE account_id =:'user1_receivables_id'
ORDER BY account_version;
               id                |           account_id            |           transfer_id           | amount | account_previous_balance | account_current_balance | account_version |          created_at           |           event_at            | metadata 
---------------------------------+---------------------------------+---------------------------------+--------+--------------------------+-------------------------+-----------------+-------------------------------+-------------------------------+----------
 pgle_01KEA9YZ84EWMTS5XS58BJWQJ9 | pgla_01KEA9YZ82ESHTB6WZTWJTKVYN | pglt_01KEA9YZ83FZ78XHYCZ4233TNW |  50.00 |                     0.00 |                   50.00 |               1 | 2026-01-06 18:43:58.85121+00  | 2026-01-06 18:43:58.85121+00  | 
 pgle_01KEA9YZ84FTXSGAH9NDBMJWCW | pgla_01KEA9YZ82ESHTB6WZTWJTKVYN | pglt_01KEA9YZ84FPPTADA0Z68QB6JP | -50.00 |                    50.00 |                    0.00 |               2 | 2026-01-06 18:43:58.852694+00 | 2026-01-06 18:43:58.852694+00 | 
(2 rows)

-- Continuing the example, let's issue a partial refund of the payment. When we
-- issue the refund, we move the money into the pending_outbound account to
-- hold it until we get confirmation that it was sent
SELECT * FROM pgledger_create_transfer(:'user1_available_id',:'user1_pending_outbound_id', 20.00);
               id                |         from_account_id         |          to_account_id          | amount |          created_at           |           event_at            | metadata 
---------------------------------+---------------------------------+---------------------------------+--------+-------------------------------+-------------------------------+----------
 pglt_01KEA9YZ85FEMA51TK0B4W809Q | pgla_01KEA9YZ82F7397D24286T0WFK | pgla_01KEA9YZ82FJHT1K29R4CSXHFD |  20.00 | 2026-01-06 18:43:58.853552+00 | 2026-01-06 18:43:58.853552+00 | 
(1 row)

-- Once we get confirmation that that refund was sent, We can move the money
-- back to the user's external account (e.g. their credit/debit card). Often,
-- this confirmation will come as a webhook or bank file or similar, so we can
-- record the event time in the confirmation separately from the time we record
-- the ledger transfer (event_at vs created_at). We can also record extra metadata
-- as JSON that helps us tie it all together:
SELECT *
FROM
    pgledger_create_transfer(
        :'user1_pending_outbound_id',
        :'user1_external_id',
        20.00,
        event_at => '2025-07-21T12:45:54.123Z',
        metadata => '{"webhook_id": "webhook_123"}'
    );
               id                |         from_account_id         |          to_account_id          | amount |          created_at           |          event_at          |           metadata            
---------------------------------+---------------------------------+---------------------------------+--------+-------------------------------+----------------------------+-------------------------------
 pglt_01KEA9YZ86EBTV5EHS1JARYNJ3 | pgla_01KEA9YZ82FJHT1K29R4CSXHFD | pgla_01KEA9YZ81FA4SH7EN1KN633NF |  20.00 | 2026-01-06 18:43:58.854049+00 | 2025-07-21 12:45:54.123+00 | {"webhook_id": "webhook_123"}
(1 row)

-- Now, we can query the current state. The external account has -$30 ($50
-- payment minus $20 refund) and our account for the user has $30. Nothing is
-- in flight, so the receivables and pending accounts are 0.
SELECT
    name,
    balance
FROM pgledger_accounts_view
WHERE id IN (:'user1_external_id',:'user1_receivables_id',:'user1_available_id',:'user1_pending_outbound_id');
          name          | balance 
------------------------+---------
 user1.external         |  -30.00
 user1.receivables      |    0.00
 user1.available        |   30.00
 user1.pending_outbound |    0.00
(4 rows)

-- Next, we can simulate an unexpected case. Let's say we initiate a payment
-- for $10 but we only receive $8 (e.g. due to unexpected fees):
SELECT * FROM pgledger_create_transfer(:'user1_external_id',:'user1_receivables_id', 10.00);
               id                |         from_account_id         |          to_account_id          | amount |          created_at           |           event_at            | metadata 
---------------------------------+---------------------------------+---------------------------------+--------+-------------------------------+-------------------------------+----------
 pglt_01KEA9YZ86FBDA9D0NZ39Z5Q15 | pgla_01KEA9YZ81FA4SH7EN1KN633NF | pgla_01KEA9YZ82ESHTB6WZTWJTKVYN |  10.00 | 2026-01-06 18:43:58.854539+00 | 2026-01-06 18:43:58.854539+00 | 
(1 row)

SELECT * FROM pgledger_create_transfer(:'user1_receivables_id',:'user1_available_id', 8.00);
               id                |         from_account_id         |          to_account_id          | amount |          created_at           |           event_at            | metadata 
---------------------------------+---------------------------------+---------------------------------+--------+-------------------------------+-------------------------------+----------
 pglt_01KEA9YZ87E37TJ70HK2151WW5 | pgla_01KEA9YZ82ESHTB6WZTWJTKVYN | pgla_01KEA9YZ82F7397D24286T0WFK |   8.00 | 2026-01-06 18:43:58.854941+00 | 2026-01-06 18:43:58.854941+00 | 
(1 row)

-- Now, we can see that our receivables balance is not $0 like we expect:
SELECT balance FROM pgledger_accounts_view
WHERE id =:'user1_receivables_id';
 balance 
---------
    2.00
(1 row)

-- And we can look at the entries to figure out what happened:
SELECT * FROM pgledger_entries_view
WHERE account_id =:'user1_receivables_id'
ORDER BY account_version;
               id                |           account_id            |           transfer_id           | amount | account_previous_balance | account_current_balance | account_version |          created_at           |           event_at            | metadata 
---------------------------------+---------------------------------+---------------------------------+--------+--------------------------+-------------------------+-----------------+-------------------------------+-------------------------------+----------
 pgle_01KEA9YZ84EWMTS5XS58BJWQJ9 | pgla_01KEA9YZ82ESHTB6WZTWJTKVYN | pglt_01KEA9YZ83FZ78XHYCZ4233TNW |  50.00 |                     0.00 |                   50.00 |               1 | 2026-01-06 18:43:58.85121+00  | 2026-01-06 18:43:58.85121+00  | 
 pgle_01KEA9YZ84FTXSGAH9NDBMJWCW | pgla_01KEA9YZ82ESHTB6WZTWJTKVYN | pglt_01KEA9YZ84FPPTADA0Z68QB6JP | -50.00 |                    50.00 |                    0.00 |               2 | 2026-01-06 18:43:58.852694+00 | 2026-01-06 18:43:58.852694+00 | 
 pgle_01KEA9YZ86FH2VS3HDZ0XKG752 | pgla_01KEA9YZ82ESHTB6WZTWJTKVYN | pglt_01KEA9YZ86FBDA9D0NZ39Z5Q15 |  10.00 |                     0.00 |                   10.00 |               3 | 2026-01-06 18:43:58.854539+00 | 2026-01-06 18:43:58.854539+00 | 
 pgle_01KEA9YZ87E8X9PZFMZ9EKAJQ1 | pgla_01KEA9YZ82ESHTB6WZTWJTKVYN | pglt_01KEA9YZ87E37TJ70HK2151WW5 |  -8.00 |                    10.00 |                    2.00 |               4 | 2026-01-06 18:43:58.854941+00 | 2026-01-06 18:43:58.854941+00 | 
(4 rows)

-- We can also see that the `allow_negative_balance => false` flag on our
-- available account prevents transfers which are more than the current
-- balance:
SELECT * FROM pgledger_create_transfer(:'user1_available_id',:'user1_pending_outbound_id', 50.00);
ERROR:  Account (id=pgla_01KEA9YZ82F7397D24286T0WFK, name=user1.available) does not allow negative balance
CONTEXT:  PL/pgSQL function pgledger_check_account_balance_constraints(pgledger_accounts) line 5 at RAISE
SQL statement "SELECT pgledger_check_account_balance_constraints(from_account)"
PL/pgSQL function pgledger_create_transfers(transfer_request[],timestamp with time zone,jsonb) line 50 at PERFORM
SQL statement "SELECT * FROM pgledger_create_transfers(
        transfer_requests => array[(from_account_id, to_account_id, amount)::TRANSFER_REQUEST],
        event_at => event_at,
        metadata => metadata
    )"
PL/pgSQL function pgledger_create_transfer(text,text,numeric,timestamp with time zone,jsonb) line 4 at RETURN QUERY