0

Requirements

I'm building a system with the following requirements.

  1. An Invoice can be paid using multiple Payments (e.g a customer pays the invoice in 2 installments)
  2. A Payment can be allocated to multiple Invoices (e.g. a customer pays once for multiple invoices issued to them)

I other words, there is a a many-to-many relation between Invoices and Payments.

Then:

  1. The user needs to filter the list of Invoices by payment_status, to get unpaid/partially paid invoices, and perform collections on the non-paying customers (i.e. call the customer, and ask them to pay the bill).
  2. The user needs to filter the list of Payments to see if there are any unallocated or overallocated payments, and allocate (or refund) them manually.

Basic DB model and app logic

I modeled this with the following 3 tables (simplified version):

payments:
 - id
 - amount

invoices:
 - id
 - amount

allocations:
 - id
 - payment_id
 - invoice_id
 - amount

The payment_status of the invoice is calculated based on the sum of Allocations related to it (assuming the Invoice amount is positive):

  • if allocations.sum(amount) == 0 then invoice.payment_status is unpaid
  • if allocations.sum(amount) < 0 OR allocations.sum(amount) > invoice.amount then invoice.payment_status is error
  • if allocations.sum(amount) > 0 AND < allocations.sum(amount) < payment.amount then invoice.payment_status is partially_paid
  • if allocations.sum(amount) == invoice.amount then invoice.payment_status is paid

There is a similar logic to determine whether the Payment is fully allocated or not.

The way I am solving it now

I am torn apart between a) caching the calculated payment_status and allocation_status directly in the DB and b) calculating these statuses "on the fly".

When I cache the statuses, I need to make sure I trigger status recalculation on all events that drive the change - i.e. I need to know all the triggers, and make sure I'm not stuck in an infinite loop of triggers. In the real world, the scenarios are more complex than I described here (e.g. some payments need to be ignored, since they are "canceled", invoices can be voided and that "frees up" the allocatable amount of payments linked to the invoice, payments can be refunded/charged back etc.).

When I calculate the statuses "on the fly", I either need to duplicate the logic from the codebase to the DB or have very long app load times if I calculate the statuses from within the app.

It is not a purely theoretical example. The logic described above is a part of an app running 'in production' for the last 4 years. I am now in the process of rewriting this app. In the current version, I have the 'caching' approach, which works in most scenarios. However, I still miss some status change "triggers", and need to periodically recalculate & cache statuses of all Payments and Invoices. The number of "triggers" is (slowly) growing over time, as we find new edge cases. The "on the fly" approach with calculating the statuses from within the app, which would be my preference, crushes the app with the volume of data we have currently.

My question:

Are there any best practices/design patterns to model such cases? What books/resources/open code bases can I refer to? It seems to me that it's not an uncommon problem, but I am really struggling to find any resources that address it.

7
  • I wonder if this is an XY problem - does the customer really need to see the unpaid invoices, or do they just need to see customer accounts that either owe money or are in credit?
    – bdsl
    Commented Dec 2, 2021 at 19:56
  • It's a marketplace. Even if the user (i.e. customer support) needs to see only the customers "in arrears", one customer can (and often does) purchase services from multiple providers. The customer can be "in arrears" with respect to one provider, and completely okay with respect to another provider.
    – Amade
    Commented Dec 2, 2021 at 20:28
  • Why is there a need for caching straight-away? It feels like premature optimization. Calculating the status can be done with a single query to the database correct? Databases can also cache query results very efficiently so no need to invent your own. I would always calculate on the fly. If it performs poorly, try to find and fix the bottleneck. If that's not possible, maybe cache them yourself. Prevent over-engineering your solution
    – Bart
    Commented Dec 3, 2021 at 19:42
  • @Bart I tested doing it without cashing on the existing app data, relying on "real-time" calculation from within the app codebase (i.e. without duplicating the status calculation logic to DB queries), and was just getting timeouts for any query like "show me all partially paid invoices". It's not premature optimization - I'm looking for ideas to optimize my current solution implemented in the existing app.
    – Amade
    Commented Dec 3, 2021 at 19:53
  • I can understand. But it depends how the queries are implemented. I'm not sure what you mean by "status calculation logic inside a query". What are you exactly fetching from the database to do the calculations? If you're fetching massive amounts of data to be able to do the calculations I can imagine it has poor performance. First thing to optimise in my opinion would be the queries themselves. Select the smallest amount of data possible. Also to maybe prevent multiple roundtrips to the database which are always detrimental to performance.
    – Bart
    Commented Dec 3, 2021 at 20:35

2 Answers 2

1

Most important: It’s up to management to decide the rules.

A major organisation where I once worked had this rule: if a payment matches one, two or three bills exactly then it is applied to those bills. Otherwise, it is applied to the oldest bill, then newer bills in order if there is money left over.

1
  • 1
    Thank you for your answer. Unfortunately, the question is not about the allocation rules. I have the logic for the payment allocation, so it's not the issue. What I'm struggling with is where & how to implement these business rules. I doubt there's a perfect solution, but I'd like to see how other people dealt with it, and maybe stumble upon some simplification of the problem I haven't thought of.
    – Amade
    Commented Dec 2, 2021 at 20:49
0

After some more research I stumbled upon the "state machines" concept, and this is what I was looking for. There are tons of resources dealing with that problem. In particular, I find the "state machine" libraries, and open source codebases for e-commerce apps very helpful. Some examples for reference:

Before I started doing software development I worked in business intelligence, where all data dimensions in my reports (for slicing, filtering, etc.) were result of some calculations I had to define, usually by writing complex SQL queries, or using tools like Power BI, Sisense, PowerPivot, etc. That mental model stuck with my for some reason, and I tried to approach a problem in the web application realm using the same tools I used in reporting.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.