Improve operation auditing in `fdm-core`: a reconstructable change log (GDPR-aware)

[SvenVw]

Summary

The fdm-authz.audit table records permission checks — it proves an action was attempted and authorized, but not that it actually executed, and it stores only resource IDs, never human-readable names. As a result the trail cannot reliably reconstruct who did what: it can't confirm which named farm was deleted (the name is hard-deleted with the row), nor whether an authorized mutation (e.g. a cultivation update with end-before-start) actually changed the database or silently rolled back. This proposes a companion operation log: a normalized table, linked 1:1 to each permission-check row, that records the executed operation and its outcome — so the audit trail becomes reconstructable, while staying GDPR-compliant for the deleted-name case.

Motivation

Two concrete gaps make the current audit trail insufficient for answering after-the-fact questions (e.g. account support, data-subject requests, incident review):

• Deletions are not reconstructable by name. removeFarm (fdm-core/src/farm.ts) hard-deletes the farms row, so b_name_farm is gone. The audit table and the soft-deleted role rows (role.deleted in fdm-core/src/db/schema-authz.ts) retain only farm IDs, so a question phrased in farm names cannot be answered.
• Outcomes are not recorded. The audit row only proves an action was authorized, not that it committed (see Root cause). An authorized-but-rolled-back mutation is indistinguishable from one that never ran.

Root cause

The audit table is an authorization-decision log, not an operation log. checkPermission (fdm-core/src/authorization.ts:178) inserts one row before the operation runs, describing the decision:

audit(audit_id, audit_timestamp, audit_origin, audit_channel, credential_id,
      principal_id, target_resource, target_resource_id,
      granting_resource, granting_resource_id, action, allowed, duration)

Two consequences:

1. No outcome. The row is written before (and outside) the mutation's transaction. If the mutation is authorized but then fails — a validation error (cultivation end before start), a constraint violation, or any thrown error — the transaction rolls back and nothing changes in the database, yet the audit row still says allowed = true. You cannot distinguish "did it" from "was allowed to, but it never took effect."
2. No human-readable snapshot. Only IDs are stored. For hard deletes (removeFarm, fdm-core/src/farm.ts:987) the entity's name (b_name_farm) is destroyed with the row, so a support question phrased in names cannot be answered from IDs alone.

Together these mean the trail records intent, not what actually happened.

Goals

• Reconstruct who did what, and whether it took effect — for every mutation (create / update / delete) across fdm-core, not just deletes.
• Distinguish an authorized-but-failed/rolled-back operation from one that actually committed (explicit outcome).
• Answer "which named farm/field did principal X delete, and when?" for account support and Data Subject Access Requests (DSAR), even after a hard delete.
• Stay GDPR-compliant for retained names: bounded retention, minimal data, redactable on request.
• Keep the change backward-compatible (the audit table and its existing rows are untouched).

Non-goals

• Converting entities (farms, fields, …) to soft-delete. Deleted data stays hard-deleted; only a minimal deletion record (name snapshot) is retained, time-boxed.
• Auditing read/list operations (only state-changing mutations are in scope).
• Building the scheduler/cron that runs the retention purge (we expose the function and document where to call it).

GDPR reasoning

The operation log itself (who/what/when/outcome) is ordinary accountability data. The GDPR-sensitive part is narrow: the name snapshot retained for deletes. For that:

• A hard delete correctly honours the right to erasure for the primary data.
• Retaining a minimal, time-limited record of the deletion event itself (who deleted what-ID, when, plus a short name label) is a recognised, lawful accountability / legitimate-interest purpose (GDPR Art. 5(1)(f), 5(2), 17(3)(b/e)).
• This is not a soft delete of the entity — only a deletion event with a minimal label is retained; the farm's actual data stays gone.
• Retention must be bounded: the identifying detail (name snapshot) is purged after a fixed period, after which only non-identifying IDs remain.
• Because a farm name may itself be personal data, the snapshot must be redactable on demand so an erasure request can purge it for a principal before the timer elapses.

Design decisions

Decision	Choice
Scope	All state-changing mutations (add / update / set / remove / grant / revoke / enable / disable) across fdm-core
What to record	The executed operation + explicit outcome (succeeded / failed), so the trail is reconstructable
Deleted-name retention	Store plaintext name snapshot, bounded 365-day retention (documented legitimate-interest/accountability basis), redactable on request
Where to store	Normalized fdm-authz.audit_outcome extension table, 1:1 linked to the audit permission-check row via an audit_id FK (no duplicated resource/principal columns)
Migration	Include a Drizzle migration

Proposed design

1. New table — fdm-authz.audit_outcome (fdm-core/src/db/schema-authz.ts)

Every mutation already calls checkPermission, which writes one audit row describing the authorization (principal, resource, resource_id, action, origin/function name, channel, credential, allowed, timestamp). Rather than duplicate those columns, model the operation record as a normalized 1:1 extension of that audit row, keyed by an audit_id foreign key:

audit_outcome(
  audit_id          text primary key references "fdm-authz".audit(audit_id) on delete cascade,
  outcome           text not null,        -- 'succeeded' | 'failed'
  resource_name     text,                 -- human-readable snapshot (redactable), esp. for deletes (e.g. b_name_farm)
  error             text,                 -- populated when outcome = 'failed'
  retention_expires timestamptz           -- now() + RETENTION_DAYS; set only when a name snapshot is retained
)

principal_id, resource, resource_id, action, audit_origin (the function, e.g. updateCultivation), audit_channel, credential_id and the timestamp are not duplicated — they are read from the joined audit row. Parent/context is likewise not duplicated: the audit row already stores granting_resource / granting_resource_id (the hierarchy point where the role matched — usually the owning farm), captured at check time so it survives a hard delete. audit_outcome therefore holds only what the permission check doesn't: the executed outcome, an optional redactable name snapshot (mainly for deletes, where the data is about to vanish), and (on failure) the error.

Reconstruction semantics (join audit_outcome to audit on audit_id):

Observed	Meaning
audit.allowed = false, no child row	Attempt denied
allowed = true, outcome = 'succeeded'	Committed — atomic proof the DB changed (see §2)
allowed = true, outcome = 'failed' (+ error)	Authorized but rolled back, no DB change (e.g. cultivation end-before-start)
allowed = true, no child row	Process died before an outcome could be written (rare; nothing committed)

Index: retention_expires (for the purge sweep) and optionally outcome. Per-principal / per-resource reporting is served by the existing audit columns via the join.

Why a 1:1 extension over duplicated columns or jsonb: no denormalized copies to keep consistent, no schemaless blob, and the audit table stays a pure permission-check log. on delete cascade keeps the two in lockstep. Parent/context is intentionally not stored here — it is read from the audit row's granting_resource / granting_resource_id (the owning farm, for the common case). We accept one small gap: for a deep-chain delete (e.g. a cultivation) the immediate parent (its field) is not preserved, only the farm; if that linkage is ever needed, add a typed column deliberately.

Requirement this imposes: checkPermission must expose the audit_id it inserts so the caller can attach the audit_outcome child (see §2). Today every one of the ~90 checkPermission call sites ignores the return value, so surfacing the id is backward-compatible.

auditOutcomeTypeSelect / auditOutcomeTypeInsert are inferred — no manual type edits needed.

2. Surface audit_id from checkPermission + operation-log helpers — fdm-core/src/authorization.ts

2a. Return the audit_id. checkPermission already generates audit_id: createId() for its insert. Change it to return that id (or null in non-strict/no-insert paths) instead of the current boolean. All ~90 existing callers await it purely for the throw-on-deny side-effect and ignore the value, so this is backward-compatible. It keeps checkPermission the single place that writes the parent audit row.

2b. Success path — recordOperationSuccess(tx, audit_id, { resource_name?, retain? }):

• Inserts one audit_outcome row with outcome = 'succeeded', the optional name snapshot, and — when retain is set (i.e. a deletion whose name should be kept) — retention_expires = now() + RETENTION_DAYS.
• Called with the mutation's transaction handle so it commits atomically with the change. Because it lives inside the same tx as the actual insert/update/delete, the row's very existence is proof the operation committed; if the tx rolls back, this row rolls back with it.
• The parent audit row was already committed by checkPermission before the tx opened, so the FK resolves.

2c. Failure path — recordOperationFailure(fdm, audit_id, error):

• A failed mutation's tx rolls back, so the failure record cannot be written inside it. Instead it is written in the function's catch block on a separate connection/transaction (best-effort), inserting outcome = 'failed' + the error message.
• This is what makes the cultivation end-before-start case visible: authorized, attempted, but no DB change — now explicitly logged as failed rather than looking identical to "never ran".

2d. Export RETENTION_DAYS = 365 and both helpers from fdm-core/src/index.ts.

3. Wire into every mutation function

For each state-changing function: capture the audit_id from its existing checkPermission call; inside the mutation tx (on success) call recordOperationSuccess(tx, audit_id, {...}); in the catch call recordOperationFailure(fdm, audit_id, error). To keep this DRY and consistent, prefer a small wrapper (e.g. withOperationAudit(fdm, audit_id, tx => …)) that runs the body and records success/failure automatically, rather than hand-editing every catch.

Deletes (also snapshot the name with retain: true, since the data vanishes):
removeFarm (b_name_farm), removeField (b_name), removeCultivation, removeFertilizer, removeFertilizerApplication, removeSoilAnalysis, removeSoilImage, removeSoilImageAnnotation, removeHarvest, removeMeasure, removeDerogation, removeOrganicCertification, removeGrazingIntention.

Creates / updates / state changes (record outcome; a name snapshot is optional since the data still exists):
addFarm/updateFarm, addField/updateField, addCultivation/updateCultivation/addCultivationToCatalogue, addFertilizer/updateFertilizerFromCatalogue/addFertilizerToCatalogue/addFertilizerApplication/updateFertilizerApplication, addSoilAnalysis/updateSoilAnalysis, addSoilImage/addSoilImageAnnotation/updateSoilImageAnnotation, addHarvest/updateHarvest, addMeasure/updateMeasure, addDerogation, addOrganicCertification, setGrazingIntention, enable*/disable* catalogue toggles, and the sharing/role mutations (grantRole/updateRole/revokePrincipal, grantRoleToFarm/updateRoleOfPrincipalAtFarm/revokePrincipalFromFarm/updateRoleOfInvitationForFarm).

Where there is no meaningful name, leave resource_name null and rely on the joined audit IDs (including granting_resource / granting_resource_id for the owning-farm context).

Note: some functions already run checkPermission inside their tx (e.g. derogation.ts, several in farm.ts/organic.ts). For those the parent audit row commits with the tx; that's fine for the success path. Keep the failure-path insert on a separate connection so it survives the rollback.

4. Reporting helper (support / DSAR)

Add read-only getOperationLog(fdm, { principal_id?, resource?, resource_id?, outcome?, from?, to? }) that joins audit_outcome to audit on audit_id and returns typed rows: principal_id, audit_origin (the function), resource, resource_id, resource_name, granting_resource, granting_resource_id, outcome, error, audit_timestamp, audit_channel, credential_id. This answers "who did what to this resource, and did it take effect" — including "which named farms did this user delete and when". Returns null resource_name where already purged. Export from index.ts.

5. Retention / purge mechanism

• purgeExpiredAuditDetail(fdm) — UPDATE fdm-authz.audit_outcome SET resource_name = NULL WHERE retention_expires < now() AND resource_name IS NOT NULL. Redacts the identifying name snapshot while keeping the operation record (its audit_id link, outcome, and the joined audit row's IDs/timestamp) for accountability.
• redactAuditDetailForPrincipal(fdm, principal_id) — same redaction, scoped to the principal by matching audit_outcome.audit_id against the audit rows for that principal_id (subquery/join, since principal_id lives on audit). For on-demand erasure requests.
• Document that fdm-app should call purgeExpiredAuditDetail on a schedule (it already runs migrations on startup via app/lib/fdm-migrate.server.js; a periodic/cron call can invoke this). Scheduler wiring is out of scope beyond exposing the function.

6. Migration

Run pnpm db:generate (drizzle-kit) in fdm-core after the schema edit to produce the next NNNN_*.sql + snapshot, or hand-write a migration consistent with existing ones (CREATE TABLE audit_outcome with the audit_id FK + CREATE INDEX on retention_expires). Verify against src/db/migrations/meta/_journal.json.

7. Tests (fdm-core, Vitest, requires Postgres/PostGIS)

Extend authorization.test.ts and relevant *.test.ts:

• Success: after removeFarm, an audit_outcome row exists with outcome = 'succeeded', resource_name = the farm's name, and retention_expires set.
• Failure: a mutation that is authorized but rolls back on validation (e.g. updateCultivation with end before start) writes an audit_outcome row with outcome = 'failed' and a populated error, and the target is unchanged in the DB.
• Atomicity: if a delete's tx rolls back for another reason, no succeeded row remains (only the permission-check audit row).
• getOperationLog reconstructs "who did what": returns the deleted farm by name, and distinguishes succeeded vs. failed operations for a principal.
• purgeExpiredAuditDetail nulls resource_name for expired rows but keeps the row + outcome + IDs.
• redactAuditDetailForPrincipal nulls resource_name for the target principal only.
• The audit row's granting_resource / granting_resource_id correctly reflects the owning farm for a child-entity operation (no separate parent column needed).

8. Docs & release

• Update fdm-docs (core-concepts / contributing): the audit_outcome table and its audit join, the reconstruction semantics (denied / succeeded / failed), the deleted-name retention + 365-day purge, and how support answers "who did what / which named farms were deleted".
• Add a changeset (pnpm changeset, minor bump for @nmi-agro/fdm-core).

Acceptance criteria

• New fdm-authz.audit_outcome table with an audit_id FK (1:1 to audit), an outcome column, and a retention_expires index, via a Drizzle migration; the audit table is otherwise unchanged.
• checkPermission returns its audit_id (backward-compatible; existing callers ignore it).
• Every state-changing mutation records an audit_outcome row: succeeded atomically inside its tx, failed (with error) from its catch.
• getOperationLog reconstructs who did what (incl. deletes by name) and distinguishes succeeded vs. failed.
• purgeExpiredAuditDetail and redactAuditDetailForPrincipal redact the retained name while preserving the operation record.
• Tests cover success, failure/rollback, atomicity, report, purge, and on-demand redaction.
• Docs updated and a changeset added (minor bump @nmi-agro/fdm-core).
• Backward compatible: the audit table is untouched; audit_outcome is a new additive table.

Validation

• pnpm check-types in fdm-core.
• Targeted tests (needs docker compose up -d): cd fdm-core then pnpm exec dotenvx run -- vitest run src/authorization.test.ts src/farm.test.ts src/field.test.ts.
• pnpm lint.

Notes / caveats

• The success record is written inside the mutation's transaction, so its existence is atomic proof the change committed. The failure record is best-effort (written on a separate connection in catch): if the process is hard-killed mid-rollback, nothing is written — but in that case nothing committed either, so the trail is not misleading.
• audit (permission checks) and audit_outcome (executed outcomes) stay cleanly separated; join on audit_id to reconstruct the full picture. No columns are duplicated and there is no schemaless jsonb.
• This does not convert entities to soft-delete; deleted data is still hard-deleted. Only a minimal, time-boxed name snapshot is retained for deletes — the GDPR-defensible middle ground vs. soft-deleting whole entities.
• No retroactive recovery: entities deleted before this change have already lost their names (hard delete); those cannot be recovered. Existing soft-deleted role rows still map farm IDs to owners, but this change only makes deletions reconstructable by name going forward.
• Consider duration / row-count on the success record later if useful; out of scope here.