Track user's usage per month

So this is more an architecture question.

Lot's of SaaS's have this in place: a monthly cap of sorts of their service. So every [time period] a user can upload X files, connect with Y other users, etc.

Practically, I can think of three ways to go about this:

1. before Model.create() check .count on assiociated model and accept or error
2. add *_counter columns to the user's model and reset when new [time period] begins

3. create a Counter model with keeps track of the usage per month

4. is cimply a no-go.

5. would work better already

6. this would be the most sophisticated (there's a history for every user)

So how would you go about this? Anything obvious I am missing. A guide/read you can recommend?

Hey Jack,

So I'm not quite sure why you said that #1 is simply a no go, but that's the solution I would recommend. You may have to explain more about why you don't consider this an option.

Imagine I'm building a monthly cap into GoRails so you can only watch X videos per month (which I will never do, just an example guys! :P).

You'll want to track those activities that can happen. So if it's upload X files, you can check for the records for each file that's uploaded and then simply query their created_at date column within the current range. If the user's subscription started on the 1st, then you would count all files created since the 1st of the month to determine if they had hit their limit and how many remaining uploads they could do.

For example, I could create a "View" record that each time you watch a screencast, I could log this and then count the number of view records for a given month by simply saying View.where(created_at: Time.zone.now.beginning_of_month, Time.zone.now).count to get the number of views. I can do some extra changes to the query like only count views of distinct episodes you watched if I wanted you to be only able ot watch X number of videos a month, but you were free to re-watch them unlimited times.

Each one of your metrics is going to have a model created when an action is taken. Whether it's a uploaded file object, you'll need to store it in the database. Or if it's a connection with another user, you will record the new conversation or friendship model. And if for whatever reason you don't have a model for it, you can create activity logs that are generic and store the type of action and the created_at on there and query that table instead.

You can do the same for your other pieces of functionality. Another benefit of this approach is that you can get a historical log too of how much the user has used each month because you can query those time periods to find out exactly how much they used. This is useful business data for determining which plan limits you might want to offer, etc.

The counter columns being reset each month isn't really good because you have to have something clear that out and you don't have any data as to when it started counting. This is essentially just incomplete data which isn't very good since you also need to know that starting date. Plus when you're talking about subscriptions where users can start any day of the month, each user's time period may begin a different date and you're going to have to continually have scripts clear out this number. That's a piece of complexity that's completely eliminated by querying upon records that log the created_at date.

You can definitely add a Counter model into the mix to improve some of the performance of the above. The simplicity of not introducing anything new gets lost, but this would allow you to write updates to the current count for a specified time and make querying for the count faster since you won't be doing aggregations. After each change you can increase the count of usage for your various metrics, however you'll only want to do this when things have successfully completed of course. If there was an error, you wouldn't want to inadvertently count those which means that counting the records that were created_at in the current period can always be your safe backup for this. The counter model introduces some complexity but can improve query performance a bit.

At first I'd just implement the aggregation query for counts of created_at records in the current period. The Counter object can be added later for performance as it's really only a caching layer on top of that.

Thanks for the thorough answer, Chris.

I didn't explain it all too well. With 1. I meant simply *_counter columns on the User without any extra stuff going on.

I feel mostly for the extra "Counter" model, since it logs User's history + easier to look in the query beforehand.