Skip to content

STORY-F-016: Event store table + immutability trigger + hash chain

Epic: Core Data Priority: Must Have Story Points: 3 Status: Not Started Assigned To: Unassigned Created: 2026-04-17 Sprint: 4

User Story

As an auditor (and any future IRAP assessor), I want an append-only events.domain_events table with a DB-level trigger blocking UPDATE and DELETE, plus a hash chain making tampering detectable, so that the architectural invariant #14 (event store immutability) is enforced at the storage engine and tamper-evidence (SE-21, IR-10) is genuine — not just documented.

Description

Background

ADR-005-F makes events-first cross-domain communication. Architecture §Data Architecture specifies the event store as a single append-only table with monthly partitioning, trigger-enforced immutability, and a hash chain per-org for tamper evidence. Phase 0 decision (architecture Part 5) locked in the hash chain from day one — backfilling hashes across millions of rows later would be painful.

This story delivers it. From this sprint onwards, every write in every domain emits an event alongside its domain row within the same Repo transaction, and every retroactive modification breaks the chain detectably.

Scope

In scope:

  • Migration: events schema, events.domain_events parent partitioned table, monthly partitions for current + next 12 months, indexes per data.md spec (org_id + domain + event_type + inserted_at, aggregate_id, correlation_id)
  • Immutability trigger events.prevent_modification() (PL/pgSQL):
  • BEFORE UPDATE OR DELETE ON events.domain_events FOR EACH ROW
  • Raises P0001 exception with message "Events are immutable. UPDATE and DELETE are not allowed."
  • Hash chain trigger events.compute_hash() (PL/pgSQL):
  • BEFORE INSERT ON events.domain_events FOR EACH ROW
  • Reads last hash for NEW.org_id; sets NEW.prev_hash = last_hash, NEW.hash = sha256(prev_hash || id || org_id || domain || event_type || aggregate_id || payload || inserted_at)
  • Chain is per-org (not global) — means an org's events form their own chain; simpler retention
  • Monthly partition auto-creation Oban cron: on 25th of each month, create partition for month+1
  • FinnestCore.EventStore GenServer:
  • append/1 — insert event within caller's transaction; validates required fields (domain, event_type, org_id, payload)
  • verify_chain/1 — walks chain for given org_id; asserts integrity; returns {:ok, tip_hash} or {:error, {:break_at, event_id}}
  • FinnestCore.Event struct — standardised event shape; used by all emitters: %Event{id, domain, event_type, aggregate_id, org_id, payload, metadata: %{correlation_id, causation_id, actor_id, agent_id, classification, schema_version}}
  • Ecto schema FinnestCore.DomainEvent — read-only Ecto schema for querying events (never used for insert — always raw SQL or EventStore.append/1)
  • Integration with existing stories: wire EventStore into Tenant plug (F-008), auth events (F-006 was stubbed), user creation (F-007)
  • Hash chain verification script — runs monthly via Oban cron; reports integrity to ops via log + Sentry if break detected
  • Architecture test event_store_immutability_test.exs: insert event → attempt UPDATE → raises; attempt DELETE → raises
  • Architecture test event_hash_chain_test.exs: insert 3 events; verify_chain returns ok; manually modify an event's payload (bypass trigger for test) → verify_chain returns error at that event

Out of scope:

  • Event subscribers / PubSub broadcast (Scout+Verify sprints — first subscribers land with real domain events)
  • Event replay tool (defer to ops tooling sprint)
  • Long-term partition archival to S3 Parquet (data.md DA-OI-03 — Phase 4)
  • Event schema versioning / migration tooling (defer)

Technical Notes

  • Partitioning by RANGE (inserted_at) per month is standard Postgres pattern; enables fast retention (drop old partition) and query locality
  • Hash chain per-org means sorting + verification is O(N events per org), not O(N total) — scales
  • Trigger implementation: BEFORE not AFTER so raise propagates to the transaction cleanly
  • Hash computation uses digest() from pgcrypto extension — enable CREATE EXTENSION IF NOT EXISTS pgcrypto in migration
  • Event store queries in application code always use Repo.all(from e in DomainEvent, where: e.org_id == ^org_id, ...) — relies on prepare_query (F-008) allowlist; DomainEvent must be on reference-data allowlist OR queries must explicitly scope by org_id
  • EventStore.append/1 must be called within a Repo.transaction/1 — asserted via a transaction-context check; outside a txn raises
  • For bootstrap: first event in an org's chain has prev_hash = NULL; trigger handles this via COALESCE
  • Verification script: runs in a read-only transaction; does NOT acquire locks; can run concurrently with inserts
  • Module namespace: modules live under FinnestCore.* (flat — not Finnest.Core.*). Boundary library cannot classify the dotted form under the existing FinnestCore boundary block; F-003/F-006/F-007/F-012..F-015 all ship with the flat namespace. Same rationale as Sprint 3 D4 resolution.

Dependencies

  • Blocked by: STORY-F-003 (Repo + supervisor), STORY-F-008 (tenant enforcement)
  • Impacts: F-006 auth events, F-007 user/org/office CRUD events — these get wired to emit via EventStore after F-016

Acceptance Criteria

  • Migration creates events schema + events.domain_events + monthly partitions + indexes
  • pgcrypto extension enabled
  • Immutability trigger active: UPDATE events.domain_events SET ... raises clear error
  • Immutability trigger active: DELETE FROM events.domain_events WHERE ... raises clear error
  • Hash chain trigger active: INSERT populates prev_hash + hash columns automatically
  • Multiple inserts for same org chain correctly (event N's prev_hash = event N-1's hash)
  • Different orgs have independent chains (insert events for org A and org B interleaved; verify each chain independently intact)
  • FinnestCore.EventStore.append/1 called outside a transaction raises
  • FinnestCore.EventStore.verify_chain/1 for a clean org returns {:ok, tip_hash}
  • FinnestCore.EventStore.verify_chain/1 after a deliberate corruption (test-only bypass of trigger) returns {:error, {:break_at, event_id}}
  • Auth events (from F-006) wired to EventStore; user registration emits user_registered event visible in events.domain_events
  • Org + User CRUD events (from F-007) wired: organisation_created, user_created events emitted
  • Monthly partition auto-creation Oban cron job registered (runs on 25th of month)
  • Hash chain verification Oban cron registered (runs monthly; logs result)
  • Architecture tests pass (immutability + hash chain)
  • mix format, credo --strict, dialyzer, mix boundary all green

Testing Requirements

  • Unit: EventStore.append happy path + validation error paths
  • Integration: insert → query → manually trigger UPDATE → raises
  • Chain integrity: insert 100 events across 3 orgs; verify_chain for each org returns ok
  • Corruption test: deliberate hash byte flip → verify_chain detects and reports which event
  • Performance: 1000 event inserts in a single transaction in <100ms (triggers are fast)

References