Source change feed

Discrawl records a durable change feed so that a reader can copy only changed rows instead of rereading whole tables. The archive serves two contracts side by side. Contract 1 is unchanged; contract 2 adds the content tables and the metric series, with the same entry semantics:

A contract-2 reader reads every table of both contracts.

The feed is local archive state. It is not exported to Git snapshots and does not change the columns of any tracked table or any existing reader contract.

#Tables

change_log holds one entry per changed native row:

change_feed_tables has one row per tracked table: generation (counter), generation_id (random 128-bit hex), generation_seq (feed head when the generation started), reason, schema_sha256 (hash of the table's sqlite_master.sql), contract_version (the contract that introduced the table: 1 or 2) and updated_at.

change_feed_contracts lists the contracts the archive serves: contract_version and tables_json (the JSON array of that contract's tables, in the order above) and updated_at. Discrawl 0.20.0 and later write rows 1 and 2.

change_feed_meta is a singleton: contract_version (the baseline contract, which stays 1 so contract-1 readers keep working), compacted_through (highest compacted seq), compacted_entry (the digest of that compacted entry, or null), compacted_at and retention_seconds (604800).

An entry's digest is json_array(table_name, op, row_key, recorded_at_ms).

#How entries are written

AFTER INSERT, AFTER UPDATE and AFTER DELETE triggers on each tracked table append entries in the writer's own transaction. Every writer is covered, including snapshot imports, guild deletion, repairs, older Discrawl binaries opening the same file, and manual SQL.

• An insert writes i with the new key; a delete writes d with the old key.
• An update writes u only when at least one column changed (`old.c IS NOT

new.c for each column; values of different storage classes never compare equal). If the primary key changed, d for the old key precedes u` for the new key.

• INSERT OR IGNORE that inserts nothing writes nothing.
• INSERT OR REPLACE (snapshot imports) removes the conflicting row without a

delete trigger. This is still complete because each tracked table's only uniqueness constraint is its primary key, so the replacement's i entry names the same key. A test enforces this invariant.

Because SQLite admits one writer at a time, a reader transaction sees a sequence prefix: every later commit allocates larger seq values. Entries do not copy row contents. A reader rereads the native row by key.

Since 0.20.0 writers change content rows only when their content changes (see Data storage). An unchanged repair, member refresh or Gateway replay writes no content row, so it writes no content entry. Evidence/check entries still record the check. Storing a message reconciles its relationship rows in place: an attachment row is updated in one statement only when one of its content columns changes, and a mention keeps its event_id for the same (message_id, target_type, target_id). Only mentions and attachments that appear or disappear are inserted or deleted. mention_events is therefore still not insert-only (an in-place update writes u). Older binaries opening the same file delete and reinsert these rows; the feed records that faithfully too.

The two history tables are only written by insert or ignore in current code (archiveMessageEmbeddings, retainAttachmentText) and have no update, delete or retention path. They are tracked anyway, because their composite keys have no stable monotonic order a reader could use.

#Generations

A table starts a new generation, with a new random generation_id, when the log cannot express what happened:

Writable opens check the feed after every migration. When the installed triggers already match, the check issues no DDL, so routine opens do not change PRAGMA schema_version. Merge imports do not start a generation; their row writes are recorded like any other writer.

#Retention and compaction

discrawl tail compacts hourly. Compaction removes only a seq prefix of entries whose recorded_at_ms is older than 7 days, stops at the first entry inside the window (so a wall-clock step never removes newer entries), and records the new compacted_through in the same transaction. Entries above compacted_through are never deleted. A reader whose cursor is below compacted_through must reconcile once; that is the only way compaction affects a reader. AUTOINCREMENT keeps sequences unique even after the whole log has been compacted. Other commands do not compact; growth then pauses at whatever the archive accumulates until the next tail run.

discrawl status --json reports change_feed with the contract version, head, compaction floor and each table's generation.

#Contract 2

Contract 2 keeps every contract-1 rule: the entry format, generations, compaction, digest and reader algorithm are identical, and contract-1 tables keep their generations when an archive gains contract 2. A reader selects a contract as follows:

• Contract 1 reader (unchanged): require `change_feed_meta.contract_version

= 1` and read only the four contract-1 tables.

• Contract 2 reader: require change_feed_meta.contract_version = 1 and a

change_feed_contracts row with contract_version = 2 whose tables_json lists the table. Otherwise treat the table as untracked (full read), exactly as for an absent feed.

What contract 2 adds for readers:

• messages, members, channels and guilds entries mean a content change

(or a tombstone, scope or relationship-owner update). A reader can replace updated_at >= cursor scans with changed keys. messages.updated_at still advances when a message's attachments or mentions change, so the existing owner rebinding remains valid; the feed now also covers it directly.

• entity_metrics is an append-only value-change series. Each entry is an

insert of a new value; rows are never updated by Discrawl.

• entity_checks (latest check per entity) is deliberately not tracked:

it changes on every observation. Read it directly by checked_at if check clocks are needed.

An older (contract-1) binary that opens an archive with contract 2 leaves the contract-2 triggers in place, so its writes are still recorded (including its unconditional rewrites). If it recreates a lost log, the compaction floor moves to the head, so contract-2 cursors below it reconcile.

#Reader algorithm

A reader stores, per table, a cursor (generation_id, seq, digest), where digest is the digest of the entry at seq, or compacted_entry (empty when null) when no entry exists there.

capture(T, cursor):
  BEGIN read transaction                         # one consistent snapshot
  if change_feed_tables has no row for T: full snapshot (feed absent)
  G     = select generation_id from change_feed_tables where table_name = T
  floor, compacted = select compacted_through, coalesce(compacted_entry, '') from change_feed_meta
  head  = max(sqlite_sequence.seq for 'change_log', max(change_log.seq), floor)
  valid = cursor != null
      and cursor.generation_id == G
      and floor <= cursor.seq <= head
      and (digest of entry at cursor.seq == cursor.digest
           or (no entry at cursor.seq and cursor.seq == floor
               and cursor.digest == compacted))
  if not valid:
      rows = full read of T (apply the reader's own scope filter)
  else:
      for each row_key with entries for T where cursor.seq < seq <= head,
          keeping the op of its highest seq:
        row = select * from T where primary key = row_key
        if row: upsert(row)                       # last op must not be 'd'
        else:   delete(row_key)                   # last op must be 'd'
        a contradiction means the contract was broken: reconcile fully
  cursor = (G, head, digest of entry at head, or compacted if head == floor and absent)
  COMMIT

The digest check detects a store restored in place from an earlier copy and then written again: the generation is unchanged, but the restored file reassigns sequences after its own head to different entries. Because compaction keeps the digest of the last entry it removes, the check still works when the reassigned entry at the reader's cursor has been compacted. A file replaced by a new inode should also be detected by the reader's file identity check. internal/store/change_feed_test.go contains a reference implementation that reproduces each table exactly across random writes, repairs, deletions, compaction, generation changes and an in-place restore.

#What the feed does not cover

• Scope. Entries are not filtered. Collection scope lives in channels;

a reader that filters by allowed channels must recompute its allowed set on every capture and backfill or remove whole channels whose admission changed.

• Owning messages (contract 1). mention_events and message_attachments

join to messages. Message writes that change relationship rows produce entries, but owner-only changes (for example a deletion tombstone or a text repair) do not. A contract-1 reader that embeds owner state must rebind it from its own message change source, such as the existing messages.updated_at cursor. Contract 2 tracks messages itself. An owner stream was rejected before 0.20.0 because its triggers ran on every unconditional message write; with conditional writes, unchanged rereads no longer execute a message statement at all.

• Check clocks. entity_checks is not tracked (see Contract 2).

#Writer cost

Each changed row in a tracked table writes one change_log row (plus the sqlite_sequence update AUTOINCREMENT requires). The opt-in measurement DISCRAWL_CHANGE_FEED_OVERHEAD=1 go test -run TestChangeFeedWriterOverhead ./internal/store uses an archive-shaped fixture: 20,000 messages with 1.5 KB payloads, 13% with a mention, 1.5% with an attachment, and 10% with an embedding, written in batches of 50 as an ingest, an unchanged resync and an edit pass. The best of three runs on a Mac Studio (M-series, local SSD):

The run wrote 17,700 entries using 888,832 bytes (1.50% of the 59 MB fixture, about 50 bytes per entry). Most of the time cost is not the log rows: every statement that writes a tracked table also compiles its trigger programs, and the message writer runs relationship delete statements even for messages without attachments or mentions. Baselines varied by about 15% between runs, so treat the percentages as estimates. At the Studio archive's observed live rate of a few changed rows per minute, the absolute cost is negligible; bulk repairs cost proportionally more.