Spike: Investigate Change Data Capture / Event Sourcing for Historical tables

[katelovescode]

📋 Summary

We need to store historical data as snapshots of "what was active at a certain time" for creating and updating attendances (and therefore, calculating revenue) as rates and information change.

One option is to save [Table]Details records that never update, and instead when a change occurs, creates a new record, so we can look at the state of data at any given time. Two ways to do this: regular old PaperTrail, and bespoke historical data saving logic.

Another option (or two?) is to use an event streaming tool (or a db log tailing tool?) to record state changes, and then give us a snapshot of data at a specific time.

Some things to investigate (h/t to John Maguire and Chris Mason in Rails-Link Slack): https://kafka.apache.org/ https://microservices.io/patterns/data/event-sourcing.html https://debezium.io/ https://debezium.io/blog/2020/02/10/event-sourcing-vs-cdc/ https://github.com/palkan/logidze/

Tables this will apply to: Businesses Children Possibly ALL state rate-calculation tables (i.e. Geography, QRIS, Age, Time, Absence, Limit)

🇺🇸 States this change applies to

• [X] ALL
• [ ] Nebraska
• [ ] Illinois

💻 Implementation Details

[katelovescode]

This will impact the historical data comments here:

• https://docs.google.com/spreadsheets/d/1p0U7NGqQYJNesEDH38uZ1N6x_IUn18xuLuj0eRLcrOc/edit#gid=0&range=I7
• https://docs.google.com/spreadsheets/d/1p0U7NGqQYJNesEDH38uZ1N6x_IUn18xuLuj0eRLcrOc/edit#gid=0&range=I12