Reader change contract

Let a read-only consumer decide cheaply and exactly whether selected archive tables changed since a recorded cursor, and which rows changed.

#Purpose

SQLite's PRAGMA data_version only reports that some commit happened. A continuously written archive therefore looks changed on almost every poll, and a consumer that rebuilds derived state from a few tables rebuilds it on every cycle. The change contract records, inside the archive and in the writer's own transaction, a monotonic sequence for every insert, update and delete of tracked tables, and the primary key of each changed row for row-mode tables.

The contract is opt-in per archive. It is never published in portable stores or cloud snapshots: portable prune, portable export and the cloud snapshot strip it before they rewrite rows.

#Commands

gitcrawl change-contract status [--json]
gitcrawl change-contract enable [--retention N] [--json]
gitcrawl change-contract bump --reason TEXT [--json]
gitcrawl change-contract disable [--json]

• enable creates the contract (new epoch, generation 1) or re-arms tables whose

triggers are missing or altered, advancing the generation. It is idempotent. --retention sets the retained log span, default 1,000,000 sequence values, minimum 1,024. Changing it on an enabled contract prunes the existing log to the new bound in the same transaction. Any surviving partial contract (for example after an interrupted manual removal) is replaced by a new epoch, keeping its retention.

• bump advances the generation after an owner-run rewrite the triggers could

not observe, forcing every reader to rebuild once.

• disable removes every contract trigger and table. Readers then see no

contract and rebuild as before.

• status is read-only and reports the cursor, per-table state and families.

enable, bump and disable open an existing archive without running schema migrations or convergence work, and refuse an archive whose schema version differs from the binary's. They take only SQLite's ordinary write lock, so they can run beside a live collector.

Every writable open by this or a later Gitcrawl binary re-arms an enabled contract whose triggers a migration removed, and advances the generation. Open does not fail if the repair fails; readers detect unarmed tables independently.

#How changes are recorded

enable installs three AFTER triggers, change_contract_<table>_insert, _update and _delete, on each tracked table. Triggers are part of the database, so every writer records changes, including older pinned Gitcrawl binaries and other SQLite clients that know nothing about the contract. They fire for ordinary statements, INSERT ... ON CONFLICT DO UPDATE (for example upsertThreadRevisionEvidenceBlob updating an existing blobs row on a hash conflict), foreign-key cascades and bulk deletes, and roll back with the writer's transaction. Statement results are unchanged: changes() and last_insert_rowid() exclude trigger writes. Gitcrawl never uses INSERT OR REPLACE, whose implicit deletes do not fire delete triggers unless recursive_triggers is on.

Each journaled mutation:

1. advances change_contract_sequence.value by one (two for a key change);
2. in rows mode, appends change_contract_log(seq, table_id, op, row_key);
3. sets change_contract_tables.last_seq for the table to the new value.

op is 1 insert, 2 update in place, 3 delete. An update that changes a row's key journals a delete of the old key at seq - 1 and an insert of the new key at seq. row_key is the declared primary key: its value for a single column, a JSON array in key order for a composite key (for example [thread_id,"review_thread_id"]), and the rowid for a table without one. change_contract_tables.key_columns names the columns. Keys are declared primary keys, so VACUUM does not invalidate them.

table mode tables advance the sequence and last_seq without row entries. They answer "changed since C" but not "which rows".

#Objects

Table identifiers are permanent. New tables receive new identifiers.

#Families

¹ table mode. All other tracked tables are rows mode.

Families are a convenience derived from the Clawmmunity reader. A consumer must still verify that every table it reads, including tables named by its own removal policy, is tracked and armed, and treat any untracked table as "always changed".

#Bounded growth

The log keeps at most retention sequence values, plus one prune interval of 1,024. When an appended entry reaches next_prune, the prune trigger deletes entries at or below seq - retention in the same transaction and raises pruned_through. Entries with seq <= pruned_through may be gone; a cursor below pruned_through must rebuild fully. All other contract tables have a fixed size. A log entry occupies about 16 bytes for an integer key and about 48 bytes for a composite review-thread key, so the default retention costs about 16-48 MB.

#Generations and rebuilds

The cursor is (epoch, generation, schema_version, seq). A full rebuild is required when any of these differs from the last read, or when the contract is absent, unarmed or of an unknown version:

• epoch changes when the contract is created again after disable.
• generation changes when a table is re-armed or after bump.
• schema_version changes on any DDL. A table rebuild drops its triggers and

copies rows without journaling; the schema change makes the cursor invalid even if identical triggers are recreated.

• An unarmed table means writes since its triggers disappeared are not

journaled, so readers rebuild on every cycle until a writable open or enable re-arms it.

• seq lower than the cursor means the file was restored. Restores and file

replacements also change the file identity (device, inode, birth time), which consumers should bind as well; the contract cannot detect a restore of an older same-epoch copy that later advanced past the cursor.

Git object repacks of portable stores, including byte-aware optional compaction, never touch an archive's SQLite rows and need no generation change. Portable prune and cloud snapshots strip the contract from their copies. VACUUM preserves rows and declared keys.

#Reader algorithm

Run in one read transaction, so the cursor and the rows read are one snapshot:

-- 1. Contract identity and cursor.
PRAGMA schema_version;                                  -- sv
SELECT s.contract_version, s.epoch, s.generation, q.value, q.pruned_through
FROM change_contract_state s, change_contract_sequence q; -- v, e, g, seq, pruned

-- 2. Every present table of family F must be tracked and armed (expect 0).
SELECT count(*) FROM change_contract_families f
JOIN sqlite_schema m ON m.type = 'table' AND m.name = f.table_name
LEFT JOIN change_contract_tables t ON t.table_name = f.table_name
WHERE f.family = :F
  AND (t.table_id IS NULL OR (SELECT count(*) FROM sqlite_schema s
       WHERE s.type = 'trigger' AND s.tbl_name = t.table_name
         AND s.sql IN (t.insert_trigger, t.update_trigger, t.delete_trigger)) <> 3);
SELECT count(*) FROM sqlite_schema
WHERE type = 'trigger' AND name = 'change_contract_log_prune';  -- expect 1

-- 3. Changed since cursor C?
SELECT max(t.last_seq) FROM change_contract_families f
JOIN change_contract_tables t ON t.table_name = f.table_name
WHERE f.family = :F;                                           -- last

-- 4. Incremental rows (rows-mode tables only, requires C.seq >= pruned).
SELECT l.seq, t.table_name, t.key_columns, l.op, l.row_key
FROM change_contract_log l
JOIN change_contract_tables t ON t.table_id = l.table_id
JOIN change_contract_families f ON f.table_name = t.table_name AND f.family = :F
WHERE l.seq > :C_seq AND l.seq <= :seq ORDER BY l.seq;

if contract absent, v != 1, step 2 fails, C missing,
   (e, g, sv) != (C.epoch, C.generation, C.schema_version), or seq < C.seq:
    full rebuild; store cursor (e, g, sv, seq)
elif last <= C.seq:
    family F unchanged; advance the cursor to (e, g, sv, seq)
elif C.seq < pruned or a table-mode table of F has last_seq > C.seq:
    full rebuild; store new cursor
else:
    for each distinct (table, row_key) in step 4:
        re-read the row by key; absent means deleted
    store new cursor

The reference reader in internal/store/change_contract_test.go implements exactly this algorithm.

#Overhead

Measured on Apple M-series with the bundled pure-Go SQLite, inside one writer transaction so commit flushes are excluded (go test ./internal/store -bench ChangeContractWriterOverhead):

Each committing transaction that touches tracked tables also dirties the sequence page, the table-state page and the log tail page (at most three extra WAL pages), plus a prune every 1,024 log entries.