[ancieque]

I have implemented this for our tool NF Compose that allows us to build REST APIs without writing a single line of code [0]. I didn't go the route of triggers because we generate database tables automatically and we used to have a crazy versioning scheme that was inspired by data vault and anchor modelling where we stored every change on every attribute as a new record. This allowed for simple point in time queries.

Sounded cool, but in practice it was really slow. The techniques that are usually employed by Data Vault to fix this issue seemed too complex. Over time we moved to an implementation that handles the historization dynamically at runtime by generating historizing SQL queries ourselves [1]. We now use transaction time to determine winners and use an autoincrementing column to determine who wins on ties. A lot of brainpower went into ensuring this design is concurrency safe. On a sidenote: Generating SQL in python sounds dangerous, but we spent a lot of time on making it secure. We even have a linter that checks that everything is escaped properly whenever we are in dev mode [2].

[0] https://github.com/neuroforgede/nfcompose/

[1] https://github.com/neuroforgede/nfcompose/blob/main/skipper/...

[2] https://github.com/neuroforgede/nfcompose/blob/main/skipper/...