> There are auditing solutions to record all that, but the ones I know are (rightly) not really designed for building application logic on top of.

Yes, a big problem with table-level audits is that you lose all kinds of information about the other entities in the system. Sure, now you have an audit log of when a row was changed, but you don't really know anything about the state of all the other pieces of the database at that time, so you can't really usefully reconstruct what the entity looked like at the time it was modified.

In theory you could parse through the whole audit log to reconstruct the state of the DB but in practice it gets very complicated.

A better solution is to perform row-level snapshots with a compressed storage format, such as a column store. I maintain a database which takes monthly snapshots of data and supports an application that allows period vs. period comparisons of aggregates or even individual rows. In my case, I use snapshots, but more space efficient (at the cost of computation) would be to only store changed records, then dynamically determine which data to show based on the desired periods and sorted the row changes.

I think what mason had in mind, which I agree is a major pain point, is when a central table "owns" records in other tables, e.g. a `book` might have several rows in `pages`. I want to say "give me edition 3" and get not just the book at that point but all its pages too. Tracking changes to the book is not so hard, but reconstructing it with all its child records is a pain.

Yes exactly. Piecing together the state of all the foreign tables across the system at a specific point in time is difficult/painful.

This is one place where document stores really shine as you generally keep everything in a single place. When you update a document you don't have to worry about the values of all the foreign keys, you just save the current version which contains all your values.

> The idea of a relational schema having some kind of temporal dimension letting you get at changes is something that's been on my mind a lot lately.

You may want to check out Datomic. It uses an immutable, time-based model that covers deleted_at and many more scenarios (e.g., it's effortless to ask, "what was the state of this object last month?" without the need for looking at old backups.)

I'd be rocking Datomic everywhere if it were free software.

It's really too bad - if they offered paid support and otherwise-sane licensing, I'd be all over it.

I have dealt with this scenario before. The type of data is called "slowly changing dimension" or SCD. SCDs might never change, or change daily. It all depends on the business logic. To implement this tracking, I have seen row-level historical snapshots for auditing and dimension-level tables to track the less-important dimension changes. For health data, row-level snapshots with a key generated from the data might be most appropriate. For data that has less legal implications, a table that has "id", "dimension value", and "timestamp" may be enough. From that, using from advanced SQL, you can even produce dimension time intervals. An example where that would be useful is tracking the average time a support ticket spends in "Waiting on Customer" or a call spends waiting in queue to be answered.

You should take a look at event sourcing. Every action is stored as a separate event and periodically snapshotted. To see how something was modified, you can replay all the events forward from day 0 or from any snapshot.

Core team member Magnus gave an excellent presentation at PGConf this year about how to achieve that with PG triggers and schemas: http://www.pgconf.us/2015/event/60/