Audit trail integrity

How audit rows are checksummed, and how to verify a month from the command line.

Audit rows are written by the auditing package into a monthly table — audits_YYYYMM — one set per tenant, in that tenant's own database. Next month's tables are pre-created on the 25th of each month at 02:00 (kiwire:provision-audit-tables --ahead=1), and a missing table is also created on demand the first time something writes to it.

The Audit Trail page that reads these tables is under System › Audit Trail — see the menu tour. Besides the create / update / delete / login events the package records, the admin pages for users, vouchers, top-up codes, profiles, bulk update, certificates, journeys, portal rules and the portal designer also record page views: a viewed event tagged view, whose new_values hold {module, action, subject_id?}. Only real page opens are logged, never the grid's AJAX data fetches.

What is stamped on every audit row

Each row carries a checksum column (varchar(64)): a SHA-256 over the row's content fields, computed at the moment the row is created — so the hash is fixed before the row is inserted, and nothing re-computes it afterwards.

Every audit row is stamped with a SHA-256 of these ten values joined with |, in this order:

1. user_type
2. user_id
3. event
4. auditable_type
5. auditable_id
6. old_values — JSON-encoded
7. new_values — JSON-encoded
8. url
9. ip_address
10. created_at

Missing values contribute an empty string ([] → "[]" for the two JSON fields). user_agent, tags, updated_at and the row id are not part of the hash: changing the tag list (which Kiwire does itself on tenant rows, to mark rows written by Synchroweb staff) does not invalidate a checksum.

Rows written before the April 2026 integrity migration added the column have checksum = NULL; a table that predates it gets the column added the first time the application writes to it.

Verifying a month

php artisan kiwire:verify-audits [--month=YYYYMM] [--tenant=<tenant_id>] [--all-months]

Option	Effect
(none)	Landlord DB plus every active tenant, current month only.
--month=202609	That month's table instead of the current one.
--tenant=acme	Only that tenant (the landlord DB is always checked first).
--all-months	Every audits_* table found on each connection, not just one month. Overrides --month.

For each connection it prints one line per table — ✓ audits_202609: 1234 verified, 0 tampered — or (no table) when the month has none. Rows with a NULL checksum are skipped silently. Every row whose recomputed hash differs from the stored one is listed individually:

  TAMPERED  [audits_202609] id=42 event=updated user_id=7 ip=203.0.113.10 created_at=2026-09-14 08:15:02

It ends with Total verified : N and either ✓ All audit records passed integrity check. or Tampered records: N plus a warning. Exit code 0 when everything verified, exit code 1 when at least one row mismatched — so it can sit behind a cron or a monitoring check that alerts on non-zero exit. It only reads; it never rewrites a checksum.

The check walks the tables in 500-row chunks; on a busy tenant with --all-months expect it to take a while.

What a mismatch means

The stored hash was computed from the row's content at insert time. A mismatch therefore means one of the hashed fields (actor, event, subject, old/new values, URL, IP or timestamp) is no longer what it was when the row was written — i.e. the row was changed after the fact, by a direct SQL edit or by a bug that updated an audit row. Because created_at is inside the hash, back-dating a row is caught too; because id is not, the check cannot detect a deleted row, only an altered one.

Treat a genuine mismatch as an incident: identify who has write access to the audit tables (the application's DB user, and anyone with the MariaDB root), compare the row against the application logs for the same second, and preserve the table before anything else runs against it.

Known issue (fixed 2026-09-20) — every row used to report TAMPERED

Until 2026-09-20 the verifier flagged every checksummed row, on every tenant, even though nothing had been altered. The hash was computed at insert time from the row as the application holds it in memory, where old_values/new_values are decoded arrays and created_at is ISO-8601 (2026-09-01T01:17:23.000000Z); the verifier hashed the raw database row, where the two JSON columns are strings and created_at is 2026-09-01 01:17:23. The same hashing routine therefore produced two different hashes for one unchanged row.

The hashing routine now canonicalises its inputs before hashing: the two JSON columns are accepted as an array, a JSON string or NULL and always hashed as the decoded array ([] when empty), and created_at is accepted as a date object, the ISO string or the Y-m-d H:i:s string and always hashed in the ISO form above. The canonical form is exactly what had been hashed at insert time all along, so rows stamped before the fix verify unchanged — no checksums were rewritten. After the fix the same tenants that reported 0 verified reported every row verified back to audits_202604. An automated test pins the round trip (hash at insert → raw row → same hash) and the legacy-row compatibility.

What it does not do

• No dashboard. There is no integrity status on the Audit Trail page or anywhere else in the admin; the page shows rows, not checksums.
• No scheduled verification. Nothing in the scheduler runs kiwire:verify-audits. The only scheduled audit job is the monthly table pre-creation above.
• No alert emails. Nobody is notified of a mismatch. The result exists only on the console and in the exit code of the run you started.
• No tamper prevention. The checksum detects a change; it does not block one. The audit tables are ordinary tables the application user can write to.
• No deletion detection, as explained above.

Older documentation described a dashboard, nightly verification and alerts. None of that exists in Kiwire 4: the check is a CLI you run — by hand, or from a cron you set up yourself and point at the exit code.