Experiment with a mechanism for grouping changes in the same reaction and adding optional notes to those groups

Here's my design - tell me what you think

The _history_json_X tables gain a new integer column called group - a foreign key against a new _history_json (no suffix) table

_history_json has three columns: id int primary key, note text, current integer (indexed)

current should be null on all rows. Only during a transaction with write activity is there a row with current set to 1

Any time a trigger fires during a transaction it checks to see if there is a row with current = 1 - if there is not then it inserts a new row - which he's an auto primary key - with current = 1 on it.

All rows inserted into the history tracking tables can then use that current  = 1 row in their group column.

During the transaction anything can write to the note column in that current row. Additionally at the start of the transaction before any triggers fire someone can create that current row and populate it with a note. In this way the library user can add a note that gets recorded during that transaction.

Here's the bit I haven't figured out yet: at the end of the transaction it's crucial to set current back to null again, but can I rig up a trigger that fires only at the end of the transaction after the iteration have fired? If not this whole design may be flawed.

(It fixed the design flaw: "Your design was fundamentally sound. The one change I made: triggers don’t auto-create groups. Instead they just look up (SELECT id FROM [_history_json] WHERE current = 1) — which returns NULL when no group is active. This sidesteps the “how to clean up at transaction end” problem entirely".)

Adds a mechanism for grouping related audit log entries together and
attaching an optional note to describe the batch. Uses a shared
_history_json groups table with a current sentinel column that
triggers look up via subquery.

• New _history_json table: id, note, current (indexed)
• Audit tables gain [group] column (FK to groups table)
• All three triggers (INSERT/UPDATE/DELETE) include group subquery
• change_group(conn, note=None) context manager for Python API
• get_history() and get_row_history() return group/group_note
• populate() respects active change group
• 16 new tests covering all grouping scenarios
• Updated README with schema docs, usage examples, API reference
• Showboat demo document

https://claude.ai/code/session_0173nuhzUEdDinKYkVybuy3h