Pending transactions

[tamlyn]

Both Modern Treasury and Tiger Beetle have the concept of "pending transactions" which can then be either posted or voided. This is useful for ring fencing money while a transaction is in progress to prevent double spend, and also gives you a useful "available balance" figure for each account.

Is this something you would consider adding to pgledger?

[pgr0ss]

It's something I would consider, but it would bring its own challenges. For example, you generally want some kind of time out for holding funds, but how would that work in a pure PostgreSQL implementation? Would we need something like pg_cron?

In the meantime, I like modeling this by using a logical hierarchy of accounts. Accounts are all separate, but we can use the account name to group related accounts. For example, for a given user, you can have accounts like user1.available, user1.pending_outbound, etc. Then, you can move funds between them, such as transferring from available to pending_outbound to hold funds for an external transfer.

In a setup like this, you can get available balance by querying the user1.available account, and you can get total balance by summing the user1.* accounts.

I like this approach since it's very flexible, and you can set up however many accounts you want. You can even have different types of "pending" accounts. And PostgreSQL even gives you some cool stuff for querying hierarchies: https://www.postgresql.org/docs/17/ltree.html

[tamlyn]

Thanks.

Yeah I had planned to model this using separate accounts until I came across the concept of pending entries. A nice benefit of pending entries is that you can calculate not just the available balance on the source account, but also the "eventual balance" on the destination account.

For timeouts I'd prefer to manage the scheduling in the application layer. There could be a timeout_pending_transactions() function for the app to call so that the logic is still all in the database.

ltree looks cool. Any reason not to use that instead of text for the account names?

[pgr0ss]

A nice benefit of pending entries is that you can calculate not just the available balance on the source account, but also the "eventual balance" on the destination account.

If you use separate accounts, you should be able to sum the balances across them.

ltree looks cool. Any reason not to use that instead of text for the account names?

I didn't want to be too prescriptive if someone doesn't want to use this style account name. And you can still cast to ltree on the fly and use its functions. For example:

select sum(balance) from pgledger_accounts where name::ltree <@ 'user1';

[jeffmad]

Is there a notion of a transaction code/type for a transfer? If one has M contractors working on 1 or more projects each, there is a need to record why the transfer happened milestone payment, late fee, etc.

I like the namespacing idea, but am not seeing how to model the transaction type. Would it just be more namespacing?
create_transfer (bigcorp.capital, acmeservices.P100.m1, 100.00)

I think that mostly works but worry about ensuring the transaction types are only from a known set, or having a billion project ids and a manageable set of transaction types.

[pgr0ss]

For this, I plan to add transfer metadata. I was thinking of a jsonb column on pgledger_transfers, so you could add whatever metadata you like to each transfer. I'm not sure how to best constrain the set of possible keys values. You can maybe do it in the code that is calling the sql, or in a sql function which wraps the pgledger functions?