Transfers

Move money #

Use the ids create_account returns. The examples below use bank = pla_01M730CQFEF2CR8VRK2DCTVZ7P, alice = pla_01M730CQFHFJQ9TRR4FW8SYK2Y, bob = pla_01M730CQFMENFR9M7TNTR98C4S and shop = pla_01M730CQFPFJASDMZCRS59HPJ6.

SELECT * FROM pledge.create_transfer('pla_01M730CQFEF2CR8VRK2DCTVZ7P', 'pla_01M730CQFHFJQ9TRR4FW8SYK2Y', 100.00);

This moves 100.00 from bank to alice. The bank goes down by 100, Alice goes up by 100.

The rules:

The amount must be greater than zero.
Both accounts must exist, be open, and use the same currency.
The balance flags of both accounts must still hold afterwards.

If any rule fails, you get an error and nothing is written.

Add your own data #

Use metadata for anything you want to keep with the transfer, like an order number. Use event_at for when it really happened, if that’s not now.

SELECT amount, event_at, metadata
FROM pledge.create_transfer('pla_01M730CQFEF2CR8VRK2DCTVZ7P', 'pla_01M730CQFHFJQ9TRR4FW8SYK2Y', 12.50,
                            event_at => '2025-06-01',
                            metadata => '{"order": 7}');

 amount |        event_at        |   metadata
--------+------------------------+--------------
  12.50 | 2025-06-01 00:00:00+00 | {"order": 7}

Without event_at, it’s the time of the transfer. created_at is always the time pledge booked it.

Many transfers at once #

Use create_transfers with a list of pledge.request(...). It’s all or nothing: if one transfer fails, none are booked.

SELECT * FROM pledge.create_transfers(ARRAY[
    pledge.request('pla_01M730CQFHFJQ9TRR4FW8SYK2Y', 'pla_01M730CQFMENFR9M7TNTR98C4S', 30),
    pledge.request('pla_01M730CQFHFJQ9TRR4FW8SYK2Y', 'pla_01M730CQFPFJASDMZCRS59HPJ6', 2.50, metadata => '{"fee": true}')
]);

You get one row back per request, in the same order.

pledge.request takes the same optional fields as a single transfer:

pledge.request(from_account_id, to_account_id, amount, id, event_at, metadata)

Batches are also the fastest way to book many transfers. One call with 100 transfers is much cheaper than 100 calls.

Order inside a batch

Transfers run in the order you list them. So a later transfer can spend money that an earlier one in the same batch brought in:

-- Alice starts at 0 and may not go negative.
SELECT * FROM pledge.create_transfers(ARRAY[
    pledge.request('pla_01M730CQFEF2CR8VRK2DCTVZ7P', 'pla_01M730CQFHFJQ9TRR4FW8SYK2Y', 5),    -- alice: 5
    pledge.request('pla_01M730CQFHFJQ9TRR4FW8SYK2Y', 'pla_01M730CQFMENFR9M7TNTR98C4S', 5)      -- alice: 0, fine
]);

Swap the two lines and the batch fails.

Defaults for the whole batch #

Pass event_at and metadata after the list. They fill in for every request that doesn’t set its own:

SELECT metadata->>'order' AS "order", event_at::date
FROM pledge.create_transfers(ARRAY[
        pledge.request('pla_01M730CQFSF7ZSF5J115593B6R', 'pla_01M730CQFWFK3BEDJ1HS72AX9E', 1, metadata => '{"order": 1}', event_at => '2024-01-01'),
        pledge.request('pla_01M730CQFSF7ZSF5J115593B6R', 'pla_01M730CQFWFK3BEDJ1HS72AX9E', 2, metadata => '{"order": 2}'),
        pledge.request('pla_01M730CQFSF7ZSF5J115593B6R', 'pla_01M730CQFWFK3BEDJ1HS72AX9E', 3)],
     event_at => '2025-06-01',
     metadata => '{"order": "default"}');

  order  |  event_at
---------+------------
 1       | 2024-01-01
 2       | 2025-06-01
 default | 2025-06-01

Building a batch from your app

Don’t glue ARRAY[...] strings together in code. Send plain arrays as parameters and build the requests in SQL. The query text stays the same for any batch size:

SELECT * FROM pledge.create_transfers(
    ARRAY(
        SELECT pledge.request(r.f::pledge.account_id, r.t::pledge.account_id, r.amount)
        FROM unnest($1::text[], $2::text[], $3::numeric[]) WITH ORDINALITY AS r (f, t, amount, n)
        ORDER BY r.n));

Here $1 and $2 are lists of account ids (pla_…), and $3 the amounts.

Make it fast #

Batch when you can. One create_transfers call with many requests is much faster than many single calls.
Use prepared statements. Send the call as a prepared statement, so PostgreSQL plans it once instead of on every call. psycopg does this by itself after a query has run a few times; with other drivers, prepare it yourself (PREPARE, or your driver’s prepare option).

Can I undo a transfer? #

No. Booked transfers can’t be changed or deleted. To undo one, book a new transfer the other way:

SELECT * FROM pledge.create_transfer('pla_01M730CQFHFJQ9TRR4FW8SYK2Y', 'pla_01M730CQFEF2CR8VRK2DCTVZ7P', 12.50,
                                     metadata => '{"refund_of": "order 7"}');