Customer funds management

[onnimonni]

Hey!

Thanks for greating this resource. How would you model accounting for companies like Doordash which have hundreds of thousands of restaurants or PSPs like Stripe which have a lot of merchants?

At that scale they probably might need a custom system but OpenAI seems to be able to continue with PostgreSQL so I guess many other companies could as well: https://openai.com/index/scaling-postgresql/

The model I've seen has ledgers and subledgers. Eg there's a "merchant payables" where restaurant 1 has subledger and restaurant 2 has subledger. But I guess this comes from my history and nothing prevents from having all ledgers on the top level.

You seem to have quite strong track record in this domain and I'm wondering if you would still suggest something like these even on those cases:

select id from pgledger_create_account('user1.USD', 'USD');
select id from pgledger_create_account('user1.EUR', 'EUR');

[pgr0ss]

I would probably model these as a hierarchy of accounts using the name, such as:

• restaurants.{r1_id}.payables
• restaurants.{r1_id}.receivables
• restaurants.{r2_id}.payables
• restaurants.{r2_id}.receivables
• etc

And then you can do rollups like:

select sum(balance) from pgledger_accounts where name::ltree <@ 'restaurants.{r1_id}';

Or even:

select sum(balance) from pgledger_accounts where name like 'restaurants.{r1_id}.%';