ADR 040 System Events

ADR-040: System Events (History) — Parameters, De-duplication, Backfill

Status

Accepted (2026-10-08), planned — EPIC-21 (UX restructure, #2190). Implemented: §4 W1, W3 and W4 shipped in #2209 (story 1.3), W2 for milestones in #2199, §2–§6 and §8 in #2223 (story 2.4), and the History page with GET /api/history (contract 17, reusing the §6 module), the §2 budgetSourceId snapshot and the §6 batch collapse in #2224 (story 2.5). The object pages' History tabs follow with the object-page stories. Plan stories 0.8 (part 1: no repeat milestone-delay events), 1.3 (A→B→A with Undo), 2.4 (this ADR's core) and 2.5 (the History page) implement it. Contract 17 of API Contract › EPIC-21 is its read API.

Story 2.4 design finalised for #2223 (2026-10-19): v1 events write no English text (§2), the backfill is a guarded SQL migration that also converts the first release's pre-v1 metadata (§5), the display rules run server-side in one module (§6), and DIARY_AUTO_EVENTS is removed with a one-line startup warning when still set (§8).

Story 0.8 part (W2 for milestone delays) implemented in #2199: completed and not-later milestones write nothing; the same projected date as the latest event writes nothing (read from the legacy body until #2223 writes metadata).

Owner decisions: Q6 (automatic events leave the diary for History), Q20 (backfill, collapse exact repeats when shown, delete nothing), Q3 (no schema change).

Supersedes in part ADR-020 §3 ("Fire-and-Forget Automatic Events"): the fire-and-forget principle stays; what is written, and where it is shown, changes.

Context

Automatic events are rows of diary_entries with is_automatic = 1, written by server/src/services/diaryAutoEventService.ts through createAutomaticDiaryEntry (server/src/services/diaryService.ts). Today:

#	Observation (verified in code, 2026-10-08)
a	metadata is always NULL and created_by is always NULL. The only content is an English title and body built from templates ("Status changed from In Progress to Completed"), so the German UI shows English history.
b	autoReschedule (schedulingEngine.ts, step 7) calls onMilestoneDelayed on every run while the milestone's projected end is after its target — including for completed milestones, whose node is dated at completed_at. Every reschedule therefore appends another identical delay row, and keeps doing so after the milestone is reached.
c	Status events are written even when nothing changed (paid → paid).
d	Deposit events are written with source_entity_type = 'invoice_deposit' and the deposit id. That value is not in DIARY_SOURCE_ENTITY_TYPES (shared/src/types/diary.ts), so the event cannot link to anything (defect D-16).
e	Events are mixed into the site diary; in the owner's data the large majority of diary rows are system events, and a large share of those are exact repeats.
f	onBudgetCategoryOverspend (budget_breach) has no caller; onAutoRescheduleCompleted (auto_reschedule) is a deliberate no-op. Both types stay in the CHECK constraint for legacy rows.

Decision

1. Storage: unchanged table, new meaning of metadata for automatic rows

System events stay in diary_entries (is_automatic = 1). No column is added or changed — Q3 holds. The existing nullable metadata TEXT column carries the event's parameters as JSON (ADR-020 already reserved it for "type-specific structured fields"). created_by (existing column) records the acting user when there is one; scheduler-originated events keep NULL.

The site diary list stops showing them (the client passes automatic=false; implemented in #2224 — the server's diary list still returns them when automatic is omitted, for the Home "Recent diary" card); they appear on the object's History tab, on the History page (/history) and, when actionable, as one attention row.

2. Event parameters (metadata, version 1)

// shared/src/types/systemEvent.ts (new)
type SystemEventMetadataV1 =
  | { v: 1; event: 'status_changed'; from: string | null; to: string; subjectLabel: string | null;
      amount?: number; depositEntryType?: 'deposit' | 'refund'; depositId?: string;
      batchId?: string; budgetSourceId?: string; revertOf?: string }
      // from: null only on backfilled rows whose old text had none; subjectLabel: null for an
      // invoice without a number
  | { v: 1; event: 'milestone_delayed'; targetDate: string; projectedDate: string;
      delayDays: number; subjectLabel: string }
  | { v: 1; event: 'invoice_created'; subjectLabel: string | null; vendorId: string | null;
      vendorName: string | null; amount?: number; invoiceStatus?: string }
  | { v: 1; event: 'legacy'; depositId?: string };   // backfill could not parse the stored text

• Snapshot fields (#2223, all optional): amount (invoice and deposit status events, and invoice_created; major units as stored), depositEntryType (deposit | refund, deposit events) and invoiceStatus (invoice_created; quotation marks an offer). Backfilled rows carry none of them, and the wording then omits them. A legacy row of a deposit keeps depositId.
• Bank-report snapshots: batchId (#2223, W5) and budgetSourceId (#2224, the funding source the batch was submitted for; no FK, it survives the source) are written by markInvoicesClaimed on every event of the batch, invoice and deposit events alike. Batches written before #2224 carry no budgetSourceId.
• Canonical key order (writer and backfill must agree, because R2 compares the JSON text): status_changed → v, event, from, to, subjectLabel, amount, depositEntryType, depositId, batchId, budgetSourceId, revertOf; milestone_delayed → v, event, targetDate, projectedDate, delayDays, subjectLabel; invoice_created → v, event, subjectLabel, vendorId, vendorName, amount, invoiceStatus. A key whose value is undefined is omitted, never written as null (except from and subjectLabel, which are always present).
• from / to are stored values (in_progress, claimed), never labels; the client renders labels through the status key sets in I18N_UNION_KEYS (CLAUDE.md › Union-derived keys).
• subjectLabel is a snapshot of the object's name at write time (task title, invoice number, grant name). The read API returns the live name when the subject still exists and falls back to the snapshot when it was deleted (ADR-020: events survive their subject).
• entry_type keeps its current values (work_item_status, invoice_status, subsidy_status, milestone_delay, invoice_created); event is the render template, entry_type the filter.
• v1 events write no English text: title is NULL and body is '' (the column is NOT NULL). The client renders every v1 event from metadata in the UI language with locale dates; only legacy rows render their stored title/body. Nothing on the server reads an automatic row's text (search and the company diary counts exclude is_automatic = 1; the diary PDF export was removed), so keeping an English copy would only create a second rendering that can drift.
• created_by is the acting user for every user-initiated event: the status routes, the bank report, quotation conversion, deposit create and invoice create. Scheduler-originated events (milestone_delay) keep NULL. Taking part in W3 is a separate flag (§4).
• Client rendering (client/src/lib/systemEventText.ts): one function maps metadata to the sentence, using the status key sets of I18N_UNION_KEYS for from/to, formatDate for dates and the live subject name (sourceEntityTitle) with subjectLabel as the fallback. Every place that shows an automatic row uses it.

3. Deposit events link to their invoice (D-16)

Deposit (progress payment) status events are written with source_entity_type = 'invoice', source_entity_id = <invoice id> and metadata.depositId = <deposit id>. The source-entity union stays as documented. The row links to the payment itself: routeUrl('invoice', { id }, { payment: depositId }) (the invoice route carries tab and payment). The invoice page scrolls that payment row into view, focuses it and highlights it, then removes payment with a replace navigation. When the payment no longer exists, the page says politely "That progress payment no longer exists." and stays on the invoice. A deposit event whose invoice is gone (legacy rows only, §5) shows its snapshot text and no link.

4. De-duplication at write time

The single writer stays in server/src/services/diaryAutoEventService.ts (its on* functions lose the enabled parameter; a rename would churn every caller for no behaviour). Every function takes an event context { userId, undoable }: userId goes to created_by, and only undoable callers (the PATCH status routes) record a retractable ledger entry or retract one. The bank report, quotation conversion, deposit create and invoice create pass a userId but are not undoable. The writer applies, in order:

Rule	Condition	Effect
W1	from === to	write nothing
W2	milestone delay: the milestone is completed, or projected ≤ target, or the latest milestone_delay event of this milestone already has the same projectedDate	write nothing
W3	A→B→A inside the Undo window: the new event is B → A and the latest event of the same subject (entry_type, source_entity_type, source_entity_id) is A → B, by the same user, written less than UNDO_WINDOW_MS ago	retract that latest event (delete the row) and write nothing
W4	Undo by snapshot (contract 11): the snapshot lists the event ids its operation wrote	restoring the snapshot retracts exactly those rows
W5	bank-report submission (POST /api/source-reports/mark-claimed)	every event of the call carries the same batchId (UUID), used by the revert (contract 14)

• UNDO_WINDOW_MS is one constant in shared/ (30 000 ms, owner decision); the client's Undo toast must close before it.
• W3/W4 are the only deletions of event rows, and they can only reach a row younger than the Undo window that the same user's undone action created. Legacy rows are never deleted (Q20).
• Fire-and-forget stays (ADR-020 §3): the de-dup reads, the retraction and the insert run inside the writer's own try; a failure is logged and never fails the primary write.
• W3 metadata check (#2223): before retracting, the writer also requires that the row's stored metadata.from/to equal the ledger entry's; otherwise it drops the ledger entry and writes normally. For deposits the ledger key stays invoice_status|invoice_deposit|<depositId> (in memory only), while the row is written with the invoice as subject and metadata.depositId.
• W5 is written from #2223: markInvoicesClaimed creates one batchId (UUID) per call and every event of that call carries it, so batches submitted after #2223 can be reverted (contract 14, story 4.11, which also returns batchId in the response).
• Dead writers removed (#2223): onBudgetCategoryOverspend (no caller) and onAutoRescheduleCompleted (a no-op) are deleted; budget_breach and auto_reschedule stay valid types for legacy rows.
• W3 retracts, it does not defer (confirmed for #2223): the B → A move deletes the earlier A → B row found in the in-process ledger (Deviation Log, 2026-10-10) and writes nothing. Delaying every write by 30 s was rejected: it needs a durable buffer (a schema change) or loses events on restart. From #2223 the ledger also matches on the stored from/to in metadata, so a ledger entry and its row can no longer disagree.
• System events are always written (owner decision, EPIC-21 approval). The DIARY_AUTO_EVENTS environment variable is removed in #2223 (story 2.4; §8); after that, a stale value left in a deployment has no effect. Every bank-report batch therefore carries events and can be reverted through History. A batch submitted before #2223 while the variable was false has no events and stays unrevertable (404 on contract 14).

5. Backfill of legacy rows (data-only SQL migration 0047)

One SQL migration, server/src/db/migrations/0047_system_event_backfill.sql. The runner (server/src/db/migrate.ts) applies .sql files only, records each in _migrations and runs each in a transaction, so the file runs once per database. The statements themselves are also written to be safe to run again: every UPDATE is guarded, so a second run changes nothing.

• Rows in scope: is_automatic = 1 AND (metadata IS NULL OR json_extract(metadata, '$.v') IS NULL). The second arm matters: the first auto-event release (2026-03-14 to 2026-03-16) stored a pre-v1 object { changeSummary, previousValue?, newValue? } in metadata (removed in #895). Those rows are converted too; previousValue/newValue are stored values and are used directly.

• Templates to cover (every version of diaryAutoEventService.ts in git history; ‹A›/‹B› are labels, except in the first release, which wrote raw stored values):

  entry_type	Body templates (oldest first)	Parsed into
  work_item_status	[Work Item] "‹title›" status changed from ‹A› to ‹B›; "‹title›" status changed from ‹A› to ‹B›	status_changed, subjectLabel = title without quotes
  invoice_status	[Invoice] ‹number› status changed from ‹A› to ‹B›; ‹number› status changed from ‹A› to ‹B› (N/A when no number)	status_changed, subjectLabel = number (N/A → null)
  invoice_status (deposits, source_entity_type = 'invoice_deposit')	Deposit for invoice ‹number› changed from ‹A› to ‹B›	status_changed + depositId, re-linked per §3
  subsidy_status	Subsidy: ‹name› application status changed to ‹B› (no from); Subsidy: ‹name› application status changed from ‹A› to ‹B›; ‹name› application status changed from ‹A› to ‹B›	status_changed (from from previousValue when present, else null)
  milestone_delay	Milestone: ‹name› is delayed beyond target date (no dates); [Milestone: ]‹name› is delayed by ‹n› days (Target date ‹YYYY-MM-DD›, new projected date ‹YYYY-MM-DD›)	milestone_delayed with both dates and delayDays; the dateless form → legacy
  invoice_created	[Invoice] ‹number› created for ‹vendor›; ‹number› created for ‹vendor›	invoice_created; vendorId from the live invoice when it still exists, else null
  budget_breach, auto_reschedule	Budget: Category … / Category … has exceeded planned amount; [Schedule] Auto-reschedule completed …	legacy (no current writer, §Context f)

• Matching without regular expressions: a TEMP table (se_status, created and dropped by the migration; a CTE is scoped to one statement and several statements need it) lists every status value ever stored by the writers (work items not_started, in_progress, completed; invoices pending, paid, claimed, quotation; deposits pending, paid, claimed; grants eligible, applied, approved, received, rejected), each with both spellings: its English label (the writer's STATUS_LABELS, or its title-cased fallback such as Eligible) and the raw value. A row matches a pair (A, B) when its body ends with ' changed from ' || A || ' to ' || B (substr(body, -length(suffix)) = suffix). The subject is the text before that suffix, with the template's fixed prefix ([Work Item] , [Invoice] , Subsidy: , Deposit for invoice ) and the status / application status words removed. Dates are read from fixed offsets after Target date and new projected date , and must pass date(x) = x to count. The JSON is built with json_object.

• Deposits (§3): rows with source_entity_type = 'invoice_deposit' become source_entity_type = 'invoice', source_entity_id = invoice_deposits.invoice_id when the deposit still exists; when it does not, both become NULL, and metadata.depositId keeps the old id either way.

• Anything else (an unknown label, a changed template, an empty body) gets {"v":1,"event":"legacy"} and keeps rendering its stored English title/body.

• Non-destructive: no DELETE; title, body, entry_date and created_at are never touched. created_by stays NULL on legacy rows (the actor is not recoverable).

• Tests (0047 migration test): synthetic rows for every template row above, both label spellings, the pre-v1 metadata shape, a deposit with and without its deposit, N/A, an unparseable body; a second run of the statements changes nothing. Checking the result against a real backup is done locally and never committed (Q18).

6. Display rules (read time, on the server)

Rule	Behaviour
R1	milestone_delay events created after the milestone's completed_at are hidden (they describe nothing real).
R2	Exact repeats collapse: consecutive events of the same subject with identical parameters are returned as one row: the newest, with repeat: { count, firstAt, lastAt }. The subject key is entry_type, source_entity_type, source_entity_id, metadata.depositId (two payments of one invoice never merge) and the R1 flag (a hidden row never merges with a visible one); automatic rows without a subject group by entry_type. Parameters are compared as json_remove(metadata, '$.subjectLabel') text, hence the canonical key order (§2). legacy rows collapse on identical title and body.
R3	Nothing is deleted to achieve R1 or R2. showHidden=true (any signed-in user) turns R1 off and returns those rows with hidden: true; R2 still applies. includeHidden=true on the History API (admins only, diagnostics) returns the raw rows, R1 and R2 off.

R1 compares julianday(created_at) > julianday(completed_at); a date-only completed_at (entered by a user) counts as the start of that day. The list response carries hiddenCount, the number of R1 rows after collapse that match the filters, so the client never counts. List order gains an id DESC tie-break (entry_date DESC, created_at DESC, id DESC) so paging is total.

Where (decided for #2223): server-side, in one module (server/src/services/systemEventDisplay.ts) that builds the R1 predicate and the R2 collapse as SQL (LAG/LEAD window functions per subject, ordered by created_at, id), so pagination.totalItems counts what is shown. Story 2.4 applies it in GET /api/diary-entries to automatic rows (today's surfaces for system events). Story 2.5's GET /api/history (contract 17) reuses the same module. Doing it on the client would break paging and counts, and would let two surfaces disagree.

Batch collapse (History only, #2224). One bank-report submission is one row. With the module's collapseBatches option (set by GET /api/history), after R1 and R2 the rows sharing metadata.batchId that pass the filters collapse into one representative: the batch's first event (created_at, id), which keeps its own place in the list order. The response computes a summary for that row from all events of the batch (batch: invoice count, submitted total, the first invoice numbers, the funding source from budgetSourceId), so a filter never shrinks the figures. Collapse is off when an object filter or a batchId filter is set (an object's history shows its own event; a batch filter lists the members) and in the admin raw mode (includeHidden, the module's raw option: R1, R2 and collapse off). GET /api/diary-entries never collapses batches.

Payment and Submission (History type filter, #2224). Two filter values are derived from invoice_status events rather than stored as types: Submission = an event with a metadata.batchId; Payment = an event with metadata.to = 'paid' and no batchId (an invoice or a progress payment / refund marked Paid). This matches the kind the client renderer already derives (paid → payment, claimed → submission), so filter chip, badge and wording agree. invoice_status still matches every invoice-status row. Bank-report rows written before #2223 carry no batchId and stay plain invoice-status rows: nothing recorded which rows formed one submission.

7. Schema documentation impact

No migration changes the schema. Schema › diary_entries documents the metadata v1 shapes for automatic rows and the data-only backfill 0047.

8. Configuration: DIARY_AUTO_EVENTS removed (#2223)

• The variable is removed from server/src/plugins/config.ts, from AppConfig (diaryAutoEvents) and from every service and route that passed it on (the enabled parameter of the writer goes away).
• A stale value is accepted and logged once. If the variable is set (any value, including false) the config loader logs one warn line at startup, DIARY_AUTO_EVENTS is no longer used and has no effect; system events are always recorded. Remove it from your environment., and startup continues. Silent ignoring was rejected: an operator who set false on purpose should learn that it no longer does anything. Startup never fails on it.
• Settings › Integrations › History (contract 9): diaryEvents.enabled is always true and diaryEvents.env.optional is empty, in the same PR. The card shows "On" with no variable to set. The field stays in the response for compatibility.
• Documentation: the variable leaves the CLAUDE.md environment table, docs/src/getting-started/ configuration.md and the planned-removal notes on the wiki (Architecture, API Contract).

Consequences

What becomes easier

• History reads in the user's language with locale dates, and every row links to its object.
• "≤ 1 event per real state change, none after a milestone is reached" becomes a testable invariant.
• The site diary shows only what people wrote.
• A bank-report submission is one recognisable batch that can be reverted.

What becomes harder

• Two renderings exist for a while: v1 metadata and the legacy text fallback. The fallback is permanent for legacy rows and must stay tested.
• W3 makes the event writer read before it writes; the reads use the existing idx_diary_entries_source_entity index.
• Retracting rows (W3/W4) is a deliberate, narrow exception to "events are immutable"; it is the only reading of "A→B→A writes nothing" that does not need a pending-event buffer or a schema change.

Alternatives considered

• New system_events table: cleaner separation, but a schema change (Q3 forbids it) and a data copy of every legacy row.
• Delete exact repeats: irreversible; rejected by the owner (Q20).
• Defer event writes until the Undo window closes: needs a durable pending buffer (schema) or loses events on restart.
• Backfill in a TypeScript data migration or a startup task: the runner is SQL-only; a second migration kind, or a task that runs on every start, is not worth the mechanism for one closed set of templates. The label-pair CTE (§5) makes the parsing exact without regular expressions.
• Keep writing English title/body for v1 events: rejected (§2); it would be a second rendering that drifts, and nothing reads it.
• Collapse repeats on the client: rejected (§6); paging counts would be wrong.

Deviation Log

Date	Section	Deviation	Resolution
2026-10-09	§4 Write rules	The ADR kept DIARY_AUTO_EVENTS=false meaning "nothing is written" and called the Undo window "initially" 30 s.	Owner decisions at EPIC-21 approval: system events are always written and the variable is removed in #2223 (a stale value has no effect); the Undo window is 30 s. Code still reads the variable until #2223 lands.
2026-10-10	§4 Write rules (W1, W3, W4)	#2209 (story 1.3) implements W1, W3 and W4 before story 2.4 replaces the writer. W3 as written reads "the latest event of the same subject … by the same user", but automatic rows have created_by = NULL and metadata = NULL until story 2.4, so neither the user nor from/to can be read from the row, and no schema change is allowed (Q3).	W3 uses an in-process ledger, not a read of the latest row: the writer records each status event it writes (subject → { eventId, from, to, userId, at }, subject = entry_type, source_entity_type, source_entity_id) and retracts only an event found there, by the same user, younger than UNDO_WINDOW_MS, and only with is_automatic = 1. A restart clears the ledger; the next reverse move then writes its event normally. Callers without a user (bank report, quotation conversion, invoice create) never retract. W1 (from === to writes nothing) is now applied by the writer itself. Deposits: every deposit status change, including → pending, now passes through the writer so W3 can retract a pending → paid event on paid → pending; a row is still written only for → paid and → claimed. W3 equally retracts paid → claimed on claimed → paid by the same user. W4: the Undo snapshot (API contract 11) collects the ids of every event written during the undoable call and the rows W3 retracted during it; Undo deletes the former (is_automatic = 1) and re-inserts the latter. When DIARY_AUTO_EVENTS=false (until #2223) nothing is written and nothing is recorded.
2026-10-19	§2 Parameters, §3 Deposit link, §4 W3, §5 Backfill, §6 Display rules, new §8	Design pass for #2223 (story 2.4). (1) §2 kept writing the English title/body for new events, which AC1 replaces with client rendering. (2) §5 guarded the backfill on metadata IS NULL, but the first auto-event release (2026-03-14 to 2026-03-16, before #895) stored a pre-v1 { changeSummary, previousValue, newValue } object, so those rows would never be converted; the template list was not written down. (3) §6 placed the display rules in the History API only (story 2.5), but #2223's AC5 needs them on today's surfaces. (4) The removal of DIARY_AUTO_EVENTS had no startup behaviour and no Integrations change defined.	Decision change: (1) v1 events write title = NULL, body = ''; one client renderer. (2) Guard metadata IS NULL OR json_extract(metadata,'$.v') IS NULL; full template table and the label-pair CTE method; SQL migration 0047. (3) One server module, applied to GET /api/diary-entries now and to contract 17 later. (4) §8: a set variable logs one startup warning and has no effect; contract 9's diaryEvents.enabled is always true. W3 confirmed as retract-not-defer.
2026-10-19	§2, §3, §4, §5 Matching, §6	#2223 implementation spec (dev-team-lead) and orchestrator decisions. (1) AC3 links to the payment record itself, not the Payments section. (2) The UX wording needs amounts and the offer/invoice distinction, which v1 did not carry; R2 compares JSON text, so key order was unstated. (3) created_by and W3 participation were one notion, but the bank report, conversion, deposit create and invoice create have a user and must not retract; the writer was to be renamed. (4) A CTE cannot serve several migration statements. (5) The display rules had no user-facing "show hidden" switch, no hidden count, no deposit split in the collapse key and no total list order.	(1) ?payment=<depositId> (invoice route carries tab, payment); #payments dropped. (2) Optional snapshot fields amount, depositEntryType, invoiceStatus; canonical key order; legacy keeps depositId. (3) Event context { userId, undoable }; writer keeps its file name; W3 also checks the row's metadata; W5 batchId written from #2223; dead writers deleted. (4) TEMP table se_status. (5) showHidden, hiddenCount, hidden; R2 key adds depositId and the R1 flag; julianday comparison; id DESC tie-break.
2026-10-19	Status, §1, §2, §6	#2224 (story 2.5) implements the History page. (1) Contract 17 planned its own item shape (kind, subject, params, actor, legacyText, revertible). (2) The History page was to link a submission to the funding source's page, but no event stored the source id and the route fundingSource is still planned. (3) #2223 noted that the automatic entry page's breadcrumb would become History in story 2.5. (4) §6 did not say how a bank-report batch becomes one row, nor how Payment and Submission are told apart.	(1) Items are the diary list's DiaryEntrySummary plus a History-only batch: one shape, one renderer (renderSystemEvent); every planned field already had a home, and revertible moves to contract 14 (story 4.11). (2) New optional snapshot budgetSourceId (§2, no schema change, Q3 holds); submission rows link to the funding source's bank report (routeUrl('bankReport', undefined, { sourceId })), "No longer exists" for a deleted source, no link for batches written before #2224. (3) Superseded: the entry page's breadcrumb stays Site diary — History rows link to their object, not to the entry page, and a History crumb would need a route-map parent per entry kind. (4) §6 batch-collapse and Payment/Submission paragraphs added; §1 automatic=false marked implemented.