[dkubb]

Has anyone ever implemented a schema where you define a view to return the latest instance of each resource, and it maps INSERT, UPDATE and DELETE to an append only table underneath?

I’ve done some of this with an event sourcing system, but not the query rewriting. All my reads use the “latest view”, but the app uses functions to write to the underlying scheme rather than doing “simple” DML.

I wondered if keeping the storage as append only under the hood would keep the app layer simple.

[benpacker]

The end result is almost the same as an audit table except there's much more work to do at read time, and some queries simply cannot scale as they need to with this approach.

In general for most apps you'd rather have 2 writes (write to table and write to audit table) than 1 write to the event log and hard to scale reads.

[rrr_oh_man]

Why would it keep the app layer simple? As in: Insert only? (Create = Insert, Update = new Insert, Delete = new empty Insert with deleted flag)