I made this: https://gitlab.com/snippets/1992288

Spare me if the title makes no sense grammatically, not a native speaker.

I'm building a microservice that keeps a check of the subscribers on my product, I wanted to keep an eye of those subscribers that may be missing the latest payment.

One way to do that is to make it work with my code and some ORM magic, but that may become unsustainable as the customer base grows, another idea that occurred to me was doing it fully inside (?) the database. The solution is to compare how many days have passed since the last payment and right now, if 'right now' is larger than 'last payment' then the subscriber is late with their next payment.

I did this as a PL/pgSQL function with an example usage accompanying that code.

Now, I just need to figure out how to use the result of those calculations in a WHERE statement...

  • 0
    At a first glance this looks very interesting. I will definitely spend some time playing around with it to see how it works.

    If I understand it correctly, you cannot change the subscription tier? Or is that supported by creating a new subscription?

    Also, you might have to use a HAVING clause when checking for overdue payments since you are using aggregates in the function call.
  • 0
    @korrat figured that out about using 'having' after a small search, thanks!

    You can extend the CASE statement with more (or less) conditions and change the amount of the interval to accommodate your subscription tiers.
Add Comment