I don't know of a "polymorphic counter cache" kind of thing.

A JSONB column on the Warehouse model also feels less-than-ideal.

A join model between the Warehouse and InventoryItems models is an approach. The counter cache could be on the join model. That'd still involve including/joining the join model when the app wants to display inventory counts along with a Warehouse but would also avoid manually fiddling with counters as InventoryItems move from Warehouse to Warehouse.

@davetron5000

“should SKU belong to a warehouse?”

I don’t believe so, no. SKU here would be for categorization and reporting, as you noted.

@jgarber stitch fix had this exact model and we counted items per SKU when needed. The primary DB did not have reporting data cached on it. So a count of items in a SKU per warehouse was not modeled in Rails explicitly.

Transactional logic in our case never needed to know the counts. Only reporting and that was not needed realtime.

Much of our complex transactional queries were in SQL-way easier to understand and maintain than Active Record.

@davetron5000 The app I'm working on operates at a fraction of a fraction of a percent of the scale of StitchFix. 😅

Strictly internal app for managing serialized inventory between various locations out there in the real world and making sure we restock depending on configured conditions, contractual obligations, etc.

So, in this case at least, the reporting aspect (knowing how much inventory is where and when) is part of the core function.

There's also a bunch of knock-on data-related tasks that the team's pulling out from a replica database and using some off-the-shelf dashboard kind of thing. I tend not to have much to do with that, though.

@jgarber Yeah, the last place I worked was at a tiny scale, but I still put non-normalized data into separate tables. Maybe more infra than you want, but I had migrations for reporting tables + models in the rails app, and then regular tasks to query the normalized data and populate the reporting stuff. Even in that case, the reporting was needed at most daily, so again realtime was not a factor.

I just balk at the rails "auto update some count" thing because how could you ever trust it?