0

I have a model that looks like this:

class WidgetStack < ApplicationRecord
  belongs_to :widget
  belongs_to :person

  validates :quantity, numericality: { greater_than_or_equal_to: 0, only_integer: true }
end

All the table consists of is those two foreign keys and that quantity bigint. quantity can only increase, and potentially changes thousands of times a day.

I want to make a record of the state of the widget_stacks table every hour, on the hour, so that I can report on the changes to each WidgetStack's quantity over the last hour, day, week, month, etc.

My inclination is to make a WidgetStacksHistory model and table, and then use the whenever gem to hourly create a WidgetStackHistory record for every WidgetStack.

I also found the paper_trail gem, which does model versioning, but I don't think it's for me because a WidgetStack's quantity changes so often and I need the record taken every hour on the hour, for every WidgetStack at the same time. There will be a lot of WidgetStacks, and I don't need/want to record every single change. Furthermore, since I need the exact quantity every hour on the hour, if I used paper_trail, I'd have to record every single change to make sure that I had the latest data on the hour.

What's the "Rails way" of taking periodic snapshots of a table like this? Is my WidgetStacksHistory + whenever solution the way to go?

UPDATE with more info:

To clarify regarding the data: When I capture the data, it needs to fully include every WidgetStack, so three bigints (two foreign keys plus quantity), and the created_at timestamp for each WidgetStack. There will potentially be thousands of WidgetStacks. I need to store the data with at least hourly granularity for the last day, daily granularity for the last month, and monthly granularity indefinitely.

David Gay
  • 1,094
  • 2
  • 16
  • 32
  • Would you please explain what you expect the data to look like when you capture it, how long you need to store it, and how large the dataset is for each capture (byte size + record count)? – anothermh Jul 21 '20 at 00:03
  • @anothermh Sure, updated my question with more info. – David Gay Jul 21 '20 at 00:22
  • 1
    I'd go with your inclination. You're right, `papertrail` is not meant for this sort of historical reporting of data state. – Bill Doughty Jul 21 '20 at 01:26
  • Are you using Postgres? If so, would https://github.com/palkan/logidze work? – anothermh Jul 21 '20 at 16:30
  • @anothermh That looks like a great tool and I'd try it in place of paper_trail, so thanks for bringing it to my attention. But it looks like it serves the same purpose as paper_trail, so I think for this particular issue, my whenever-based solution is probably the way to go. – David Gay Jul 21 '20 at 22:08
  • 1
    I think your own solution, a WidgetStackHistory model and table is exactly the right approach. Rails is not prescriptive for everything. Your concept is entirely consistent with "the Rails way". – Les Nightingill Jul 22 '20 at 03:24

0 Answers0