ADR-001 — extract the audit log into a plugin

Status: proposed · Decides: the second plugin extracted from simple-lms · Supersedes: nothing · Depends on: nx-common v0.3.0 (plugin contract with Core(), APIVersion 3) and the settings contract already in use by nx-register

Decision

The audit log becomes a plugin — module github.com/nextrum-sy/nx-audit, plugin id audit, table prefix au_ — with four properties that make it a product rather than relocated code:

1. One method on the write path, Record(ctx, Entry) error, which joins the caller's transaction or fails — it never opens its own.
2. A schema that survives extraction: denormalised actor (no FK to users), occurred_at separate from recorded_at, correlation_id, and a prev_hash/hash chain so deletion or edit of a row is detectable.
3. Retention and export as first-class operations (Purge, Export), because a log nobody can extract or expire is dead weight with a compliance smell.
4. Non-core by default (Core() == false) with a visible degraded state, and Core() == true available as the one-line switch when the owner decides audit failures must stop the app.

Nothing is deleted from the existing audit_log until a verified copy exists in au_entries.

Context — what exists today (measured, not remembered)

Three consequences follow directly from that table, and they are the reason this ADR exists rather than a refactor ticket:

• Audit can be lost silently. _ = on the write means a failed insert is invisible; nothing counts attempts, and no page would show the gap.
• Audit cannot answer "who changed this record" at any scale, because the only index is on created_at; resource+record_id is a full scan.
• There is no lifecycle. Rows accumulate forever with no export path, which in practice means nobody outside this database can use them.

Why this plugin next

Against the other candidates this is the cheapest and the most reusable:

It is also the first write-path plugin that must join a host transaction for a reason other than money. The register proved the seam for balances; audit proves it for every mutating action in the app.

The contract

// Package audit is the plugin's public surface. The host depends on this, never
// on au_ tables or on this module's internals.
type Recorder interface {
    // Record writes one entry. It MUST join a transaction already present in ctx
    // (nx-common runtime.EngineFor) and MUST NOT open its own: an audit row that
    // outlives a rolled-back change is worse than no audit row.
    Record(ctx context.Context, e Entry) error

    // List answers the audit screen. Filters compose; zero values mean "any".
    List(ctx context.Context, q Query) (Page, error)

    // Export streams entries as JSONL, CSV or NDJSON to w, newest last, so the
    // consumer can resume from the last written id.
    Export(ctx context.Context, w io.Writer, q Query, format Format) (ExportResult, error)

    // Purge deletes entries whose occurred_at is older than before, in bounded
    // batches, and reports how many went. It never touches the newest row.
    Purge(ctx context.Context, before time.Time) (PurgeResult, error)

    // Verify walks the hash chain over [from, to] and returns the first break.
    Verify(ctx context.Context, from, to time.Time) (VerifyResult, error)

    // Healthy reports whether recording is currently working; the host surfaces
    // this instead of failing silently.
    Healthy(ctx context.Context) Health
}

type Entry struct {
    Action     string            // create | update | delete | login_failed | …
    Resource   string            // payment, cash_box, user, plugin:<id>, …
    RecordID   string            // "" when the action has no target
    Reason     string            // operator-supplied justification, if any
    Actor      Actor             // UserID, Username, Role, IP — denormalised on purpose
    OccurredAt time.Time         // when it happened; defaults to now() if zero
    Before     []byte            // optional JSON snapshot, capped at 4 KiB
    After      []byte            // optional JSON snapshot, capped at 4 KiB
    CorrelationID string         // groups every entry of one request or one import
    Meta       map[string]string // small structured extras (kind, source, method)
}

type Actor struct{ UserID, Username, Role, IP string }

Contract rules, each with a test in the conformance kit:

1. Record inside a rolled-back host transaction leaves no row.
2. Record never truncates silently: a snapshot over 4 KiB is refused with a typed error, and Meta over 16 keys likewise (the register already caps meta at 4 KiB, so the two plugins behave the same way).
3. Export is deterministic: same query twice ⇒ byte-identical output, because the sequence number is the sort key.
4. Purge never removes the row that anchors the chain unless it rewrites the anchor and says so in PurgeResult.
5. Every method honours ctx cancellation; none of them panic on nil optional fields.

Schema (au_)

Follows nx-register's conventions — plugin prefix, explicit versioned migrations, meta on plugin tables — with the fields the current table lacks.

CREATE TABLE au_entries (
    id              TEXT PRIMARY KEY,
    seq             INTEGER NOT NULL UNIQUE,          -- chain order, assigned in-tx
    occurred_at     TIMESTAMP NOT NULL,               -- when the action happened
    recorded_at     TIMESTAMP NOT NULL,               -- when the row was written
    action          TEXT NOT NULL,
    resource        TEXT NOT NULL,
    record_id       TEXT,
    reason          TEXT,
    actor_user_id   TEXT,
    actor_username  TEXT,
    actor_role      TEXT,
    actor_ip        TEXT,
    correlation_id  TEXT,
    meta_json       TEXT,
    before_json     TEXT,
    after_json      TEXT,
    prev_hash       TEXT,
    hash            TEXT NOT NULL                     -- sha256(prev_hash || canonical row)
);
CREATE INDEX au_entries_occurred  ON au_entries (occurred_at);
CREATE INDEX au_entries_record    ON au_entries (resource, record_id);
CREATE INDEX au_entries_actor     ON au_entries (actor_user_id);
CREATE INDEX au_entries_corr      ON au_entries (correlation_id);
CREATE INDEX au_entries_action    ON au_entries (action, occurred_at);

CREATE TABLE au_settings   (key TEXT PRIMARY KEY, value TEXT NOT NULL);  -- via nx-common settings
CREATE TABLE au_migrations (version TEXT PRIMARY KEY, applied_at TIMESTAMP NOT NULL);

Append-only is enforced, not promised: BEFORE UPDATE and BEFORE DELETE triggers raise au_entries_append_only, the same shape as the register's line_append_only. Purge therefore runs as a controlled exception (drop and recreate the delete trigger inside its own transaction, exactly as the register migration does).

Two deliberate choices:

• Actor stays denormalised. No FK to users. This is what makes extraction possible today — nothing to re-point, and the row remains true after a user is renamed or deleted.
• occurred_at ≠ recorded_at. Backfills and replays have an honest time of writing that differs from the event they describe; storing one timestamp conflates them and makes "rows written during the outage" undetectable.

Hash chain: what it buys and what it costs

hash = sha256(prev_hash || id || seq || occurred_at || action || resource || record_id || actor_user_id || before_json || after_json). Editing or deleting a row breaks every later hash, so Verify can point at the first inconsistency.

Cost: a global chain serialises inserts — one writer at a time per tenant. That is already true of this deployment (SQLite, one connection, one replica per tenant), so it costs nothing today. If the plugin ever meets a multi-writer database, the fallback is a per-resource chain, and this ADR records the switch as a v2 decision rather than an accident.

Failure policy

Non-core is the default for the same reason the register will be flipped later: a school that cannot record attendance because the log is unavailable is a worse outcome than a school with a gap in the log. But the gap must be visible:

• a banner on /audit — "audit recording is not working: N writes unrecorded since <time>", backed by an in-memory failure count the host reads via Healthy;
• one slog.Error per failure with action/resource, not a silent _ =;
• an optional spool (spool_path setting): failed entries are appended to a JSONL file next to the database, replayed at the next successful boot, deduped by id. Off by default, because a spool that silently grows for weeks is its own incident.

Strict mode is a code decision (Core()), not a setting: settings must not be able to make a process refuse to start, or a bad document can brick the app.

Migration of existing history

The register migration dropped tables because nothing in them mattered. Audit is the opposite: these rows cannot be regenerated, so the copy is a first-class deliverable with a proof.

1. Create au_* from the plugin's own migrations (plugin owns its schema).
2. Copy in one transaction: INSERT INTO au_entries (id, seq, occurred_at, recorded_at, …) SELECT id, ROW_NUMBER() OVER (ORDER BY created_at, id), created_at, created_at, … FROM audit_log ORDER BY created_at, id; correlation_id is NULL for historical rows — it is new information, and inventing one would be a lie.
3. Compute prev_hash/hash in the same transaction, in seq order.
4. Verify before dropping anything: equal row counts; equal set hashes of (id, action, resource, record_id, created_at) between audit_log and au_entries; Verify walks the new chain clean.
5. audit_log stays read-only for one release (a DROP is irreversible and the live tenant is a real school). Drop it in the release after the copy is verified in production, in a separate, named migration.

The 84 rows on test-lms are the regression fixture: the copy must reproduce them exactly, including the two actions (login_failed, link) written outside the recordAudit helper.

Host integration

The seam is small because the current code already has one entry point:

• application/audit.AuditService gains no new callers; recordAudit keeps its signature so all 37 call sites stay untouched, and only its body changes: stop swallowing the error, pass OccurredAt, and attach CorrelationID from a per-request id middleware (one line in cmd/server, also useful for traces).
• login_failed and link (written directly today) become ordinary Record calls with the same CorrelationID as the request that caused them.
• Before/After stay opt-in, today only in the plugin-settings save and in role/permission edits, where the diff is the whole point.
• The audit screen keeps its filters and gains: a health banner, an export button (CSV/JSONL over the current filter), and a Verify action that reports the first broken hash.

Boot becomes explicit, driven by the contract the host already implements for the register:

bootPlugins([]plugin.Plugin{registerPlugin, auditPlugin})
// Register → always hard fail
// Migrate / Init → hard for Core(); logged + feature hidden otherwise

Settings (via the nx-common contract, not bespoke)

strict is deliberately absent — see the failure policy.

Scheduling belongs to the host, not the plugin. The plugin owns the rule (Purge(before)); the host owns when (boot, plus a ticker). A plugin that starts goroutines behind the host's back is a plugin that survives shutdown badly.

Non-goals

• No read auditing in v1. Recording every GET multiplies volume by ~1000 for value nobody has asked for.
• Not a metrics or tracing store; OTel already covers latency and errors.
• Not a SIEM connector. Export is the integration point, and a downstream agent can ship the file.
• No UI redesign beyond banner, export and verify.

Alternatives considered

Consequences

Positive. Audit gets retention, export, tamper evidence and a health signal; every future app reuses one plugin instead of copying 300 lines; the write-path seam is proven beyond money; resource+record_id becomes indexed, so "who changed this payment" stops being a table scan.

Negative. A global hash chain serialises inserts (accepted: single writer today); two audit tables coexist for one release; extraction adds an interface call per mutation (~37 sites' worth of traffic, negligible against an SQLite write); compliance semantics now live behind a version boundary, so a plugin bug is a compliance bug.

Neutral. The au_ prefix means existing SQL that names audit_log must change — deliberately, since we do not keep backwards compatibility.

Rollout

Open questions

1. Chain scope — global (proposed) or per-resource? Global is verifiable as one statement; per-resource parallelises but needs N anchors.
2. Retention default — 0 (forever) is honest but grows without bound; a school in a jurisdiction with a 7-year rule probably wants 2555.
3. Spool default — off is safe; on is lossless. Which failure hurts more in this product?
4. Do exports need signatures (Export + detached SHA-256, or a signed manifest) before anyone will accept them as evidence?
5. Should login_failed live in this plugin at all, or in the access plugin — it is authentication, not a data change, and its volume profile is different.

Acceptance criteria

• [ ] Record inside a rolled-back host transaction writes nothing (test).
• [ ] A 5 KiB snapshot is refused with a typed error (test).
• [ ] Export twice yields identical bytes (test).
• [ ] Verify detects an edited row and a deleted row (test).
• [ ] Purge respects retention_days and never removes the newest row (test).
• [ ] The 84 live rows are copied byte-for-byte in content, chain intact (script output in the commit).
• [ ] /audit shows the health banner when recording fails (handler test).
• [ ] grep -rn 'recordAudit(' | wc -l is unchanged at 37 — the extraction must not touch a single call site.

Rendered from plugins/audit/audit-plugin.md · OKF bundle · regenerate with npm run render