For any table I want to be able to create a shadow table which tracks just when each row was last modified or deleted.

So for a table like this, called packages

There would be a table like this, called _chronicle_packages:

That's a Unix timestamp in milliseconds, with an index on it for fast retrieval of stuff that has happened since a given point.

If a record is deleted, the table gets this:

The shadow table (called a "chronicle" table, because I was worried that "changes" or "history" could be confused with a table that records the full details of the change, like sqlite-history does).

These chronicle tables can be used for a bunch of different things:

• Synchronization and replication: other databases can "subscribe" to tables, keeping track of when they last refreshed their copy and requesting just rows that changed since the last time - and deleting rows that have been marked as deleted.
• Indexing: if you need to update an Elasticsearch index or a vector database embeddings index or similar you can run against just the records that changed since your last run - see also The denormalized query engine design pattern
• Enrichments, in datasette-enrichments. I want to be able to persist something that says "every address column should be geocoded" - then have an enrichment that runs every X seconds and looks for newly inserted or updated rows and enriches just those.
• Showing regular users what has changed since their last visit - "52 rows have been updated and 16 deleted since yesterday" kind of thing.