My question is somewhat specific to my app's issue, but the answer should be instructive in terms of use cases for association logic and the record timestamp.

I have an NBA pick 'em game where I want to award badges for picking x number of games in a row correctly -- 10, 20, 30.

Here are the models, attributes, and associations in-play:

User
id

Pick
id
result # (values can be 'W', 'L', 'T', or nil. nil means hasn't resolved yet.)
resolved # (values can be true, false, or nil.)
game_time
created_at

*Note: There are cases where a pick's result field and resolved field will always be nil. Perhaps the game was cancelled.

Badge
id

Award
id
user_id
badge_id
created_at

• User has many awards.
• User has many picks.
• Pick belongs to user.
• Badge has many awards.
• Award belongs to user.
• Award belongs to badge.

One of the important rules here to capture in the code is that while a user can be awarded multiple streak badges (e.g., a user can win multiple 10-streak badges), the user CAN'T be awarded another badge for consecutive winning picks that were previously granted an award badge. One way to think of this is that all the dates of the winning picks must come after the date that the streak badge was awarded. For example, let's pretend that a user made 13 winning picks from May 5 to May 8, with the 10th winning pick occurring on May 7, and the last 3 on May 8. The user would be awarded a 10-streak badge on May 7. Now if the user makes another winning pick on May 9, the code must recognize that the user only has a streak of 4 winning picks, not 14, because the user already received an award for the first 10.

Now let's assume that the user makes 6 more winning picks. In this case, the code must recognize that all winning picks since May 5 are eligible for a 20-streak badge award, and make the award.

Another important rule is that when looking at a winning streak, we don't care about the game time, but rather when the pick was made (created_at). For example, let's say that Team A plays Team B on Sat. And Team C plays Team D on Sun. If the user picks Team C to beat Team D on Thurs, and Team A to beat Team C on Fri, and Team A wins on Sat, but Team C loses on Sun, then the user has a losing streak of 1.

So when must the streak-check kick-in? As soon as a pick is a win. If it's a loss or tie, no point in checking.

One more note: if the pick is not resolved (false) and the result is nil, that means the game was postponed and must be factored out.

With all that said, what is the most efficient, effective and lean way to determine whether a user has a 10-, 20- or 30-win streak?