Databases
StackOverflow2013
Postsdbo
POdifficult architectural problem involving recurring payments and future events
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POdifficult architectural problem involving recurring payments and future events
    primarykey
    data
    text
    <p>I'm looking for guidance on how to architect an elegant solution to what has become a bit of a thorny problem. Although I am using Ruby (and Rails) I think my problem is largely an architectural one, though my choice of language obviously has an impact in terms of suggestions involving libraries, etc., so the language remains relevant.</p> <p>Anyway, in a nutshell: my application contains objects representing memberships, belonging to people who are members of fitness facilities. Memberships contain a series of recurring payments. Some memberships automatically renew at the end of their term, while others do not.</p> <p>So for example, you may have a membership that is for an initial period of one year, and then renews month-to-month after that. In the application, creating a membership of this kind causes 12 recurring payments to be created. When the last month expires, so does the membership. A daily cron task is responsible for causing memberships to expire based on completed payments. If the membership is set to automatically renew, the same cron task will renew the membership.</p> <p>You may also have memberships that have no initial term and simply run month-to-month or week-to-week. These work in a similar manner, minus the initial payment scheduling.</p> <p>So far so good. What makes things complicated are the additional requirements:</p> <ul> <li><p>administrators can "freeze" memberships (put them on hold), for specific durations, after which they automatically reactivate (e.g. to represent people who go away on vacation for a set period of time). I can choose to freeze a membership right now and have it reactivate later, or I can choose to schedule a freeze by setting the freeze date at some point in the future, as well as the reactivation date (note: there is <em>always</em> a reactivation date, which makes things a bit easier).</p></li> <li><p>administrators can cancel memberships, either right now, or by setting a cancellation to occur in the future. (Future cancellations are not yet built.)</p></li> <li><p>administrators can refund memberships, which is like a cancellation except any past payments are refunded.</p></li> </ul> <p>What makes these difficult to deal with is the effect on recurring payments. When you freeze a membership, the recurring payments must "stretch out" around the freeze period, so that the period of time that represents the freeze is not paid for. This is both conceptually and programmatically difficult to handle. Payments, for example, may extend for different periods (i.e. each payment for someone who pays every other week pays for two weeks of a membership), and the date of cancellation may be anywhere within the period the payment covers.</p> <p>For the freezes, I have taken the approach where the membership object contains some dates, namely "freeze_on" and "thaw_on" to handle the freeze period. However, the client now wants future cancellations as well, and I have noticed some bugs with the freezing functionality, which leads me to believe I need to reconsider my approach.</p> <p>I am considering changing things so that future events can be scheduled but have no effect on the recurring payments portion of the application. The idea would be to queue up particular events. For example, a freeze in the future would be accomplished by queuing up a freeze event on a particular date, and a thaw event on a subsequent date (these two events would be connected into a single "scheduled freeze" from the user's perspective). A future cancellation would be handled similarly.</p> <p>This approach has some benefits, for example, if you wanted to cancel a future cancellation (that's the kind of annoying, tricky stuff I'm talking about), you could simply remove the scheduled cancellation from the events queue.</p> <p>However, I have the nagging feeling that I may simply be jumping from the frying pan into the fire. I'm wondering if anyone could provide me with some guidance on this issue. Are there design patterns or existing architectural principles for this sort of problem that I can examine?</p> <p>An additional thing to note is that recurring payments for memberships with scheduled terms (i.e. not month-to-month automatically renewing) must exist as database records that can be edited (moved in time, price adjusted), so using temporal expressions (as Martin Fowler suggests) is not appropriate for this problem, so far as I know. I realize that my proposed solution of an events queue would not display to the user the changes that would happen to any existing recurring payments, but I think I can live with that.</p> <p>not a scanlife bar code, it's a qr code</p> <p>toronto, give us your creative people</p> <p><strong>Edit:</strong> To respond to the two great suggestions below (the comment boxes don't allow nearly this level of detail):</p> <p><strong>Kris Robison:</strong></p> <ol> <li><p>Yes, the freeze period can be an arbitrary length, although in practice I imagine it would be rare for it to be less than two weeks. But any solution should work regardless of the length of the period.</p></li> <li><p>Yes, the renewal date changes - it is pushed forward by the length of the freeze. So if the freeze is two weeks long, it pushes the payment forward by two weeks. To make things especially tricky, in some businesses, the payments can only be withdrawn on specific dates - for example, some clubs only process payments on the 1st and 15th of each month. So when dates are pushed around, for these clubs, they have to "snap" to a particular date.</p></li> </ol> <p>Can you explain in more detail why these rules affect event queuing but not management of subscription payments?</p> <p>I'm interested in your amortization table concept. That's basically exactly what I have built already - a year-long membership with monthly payments creates 12, with weekly it created 52 - and each of these have an amount, tax, etc., associated with them, along with a state machine that governs "pending", "paid", "failed", and "refunded" states.</p> <p>The part I am struggling with is how this table responds to events. Right now, if you set a freeze, it affects the table immediately by changing the dates of the payments. Set a freeze in the middle of the table, and it pushes payments forward. That sounds effective, but it's actually quite complex and hard to manage. How would your amortization table idea improve this situation?</p> <p><strong>Arsen7:</strong></p> <p>This sounds like the event queue I proposed originally. It seems obvious to me that you've worked with stuff like this before (I was impressed by your error check on the processing date, this is a great idea and one I intend to implement ASAP) so I'm hoping that you can explain your suggestion in a little more detail.</p> <p>Specifically, I'm wondering how your concept would deal with the recurring payment situation I've described in my original question, and in the comment that I just left on Kris Robison's answer. If I have set up a schedule of recurring payments for a given purchase, and a freeze event is scheduled for right in the middle of the payments, would the schedule of payments remain unchanged until the date of the freeze became the current date, at which time the freeze would be instituted and the payments would move forward?</p> <p>This strikes me as perhaps a great way to simplify my application, but I am wondering how users would perceive it. How would I indicate to them that the schedule of payments they were looking at when a freeze has been scheduled is no longer an accurate schedule, but will change once the freeze takes place?</p>
    singulars
    1. PO
      singulars
      1. PTAnswer
    1. This table or related slice is empty.
    1. PTQuestion
    1. USadriandz
    1. USadriandz
    plurals
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. PO
      singulars
      1. PTAnswer
    2. PO
      singulars
      1. PTAnswer
    1. VO
      singulars
      1. POdifficult architectural problem involving recurring payments and future events
      1. This table or related slice is empty.
      1. VTUpMod
    2. VO
      singulars
      1. POdifficult architectural problem involving recurring payments and future events
      1. This table or related slice is empty.
      1. VTUpMod
    3. VO
      singulars
      1. POdifficult architectural problem involving recurring payments and future events
      1. This table or related slice is empty.
      1. VTUpMod
    1. COOk, I've just edited the answer to give an answer to edited question. ;-) Forgive me that long delay - I had a few relatively busy days.
      singulars
      1. POdifficult architectural problem involving recurring payments and future events
      1. USArsen7
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload