Aller au contenu

Story AUDIT-001a: Audit schema, Postgres roles, and schema-migration self-event

Module: audit-log Slice: AUDIT-001 (Architecture §11, split part a of a/b/c) Side: [BACKEND] Version target: V0.0.0 Priority: 2 Depends on: AUDIT-000 (dev env), TENANT-001 (the tenant table is referenced by audit_tenant_salt / audit_export foreign keys) Can develop concurrently with: None within AUDIT-001 (001b and 001c build on this schema) Merge order: Before AUDIT-001b and AUDIT-001c Estimated complexity: M PRD User Stories: AUDIT-US-05 (DB-level append-only), AUDIT-US-08 (self-auditing — schema migration part) Wireframe: N/A — backend only

Objective

Create the complete V1 audit_log schema on the platform DB on day one (frozen — no ALTER TABLE audit_log allowed between v0.0.0 and v0.1.0), plus the two supporting tables (audit_tenant_salt, audit_export) and the three PostgreSQL roles that make the audit table append-only at the database level. Also wire the Flyway Java callback that writes one AUDIT_SCHEMA_MIGRATED audit row for every migration, under a deploy-only role the running application can never use. This story is the structural backbone every producer and reader depends on.

Backend Scope

Entities

Three tables, all on the platform DB. Column types are copied verbatim from PRD §B.5 / Architecture §2 — do not change them.

audit_log (PRD §B.5): - event_id UUID PK (UUIDv7, time-ordered) - event_type TEXT NOT NULL (one of the §B.1 catalog; gate is enforced in the application layer, not as a DB CHECK) - event_schema_version SMALLINT NOT NULL (starts at 1 per event_type) - business_committed_at TIMESTAMPTZ NOT NULL (producer commit time — source of truth for ordering + retention) - audit_persisted_at TIMESTAMPTZ NOT NULL DEFAULT now() - tenant_id UUID (NOT NULL except for AUDIT_SCHEMA_MIGRATED) - acting_on_tenant_id UUID (platform admin acting on a tenant) - agency_id UUID (optional) - country_code CHAR(2) (snapshotted from tenant at write time) - producer_module TEXT NOT NULL (TENANT|AUTH|ING|OP|CP|PAY|NOTIF|AUDIT|deploy) - producer_outbox_id TEXT (FR-06 idempotence key) - actor_user_id TEXT (Keycloak sub, or NULL for system actors) - correlation_id UUID (optional) - customer_id UUID (optional) - contract_id UUID (optional) - signed_link_id UUID (optional) - result TEXT NOT NULL CHECK (result IN ('SUCCESS','FAILURE','INFO')) - client_ip INET (optional) - user_agent TEXT (optional, truncated to 500 chars by the writer) - payload JSONB NOT NULL DEFAULT '{}'::jsonb (PII pseudonymised by producers — see AUDIT-001c) - Two table CHECKs: event_type <> 'AUDIT_SCHEMA_MIGRATED' OR tenant_id IS NULL and event_type = 'AUDIT_SCHEMA_MIGRATED' OR tenant_id IS NOT NULL

audit_tenant_salt (Architecture §2.4): tenant_id UUID PK REFERENCES tenant(id) ON DELETE RESTRICT, salt_ciphertext BYTEA NOT NULL, wrapped_dek BYTEA NOT NULL, kek_version SMALLINT NOT NULL DEFAULT 1, created_at TIMESTAMPTZ NOT NULL DEFAULT now(), rotated_at TIMESTAMPTZ. (Rows are populated in AUDIT-001c; the table is created here.)

audit_export (Architecture §2.5): export_id UUID PK, requested_by TEXT NOT NULL, tenant_id UUID NOT NULL REFERENCES tenant(id) ON DELETE RESTRICT, date_range_start TIMESTAMPTZ NOT NULL, date_range_end TIMESTAMPTZ NOT NULL, scope_event_types TEXT, scope_correlation_id UUID, status TEXT NOT NULL DEFAULT 'PENDING' CHECK (status IN ('PENDING','RUNNING','COMPLETED','FAILED')), event_count BIGINT, jsonl_object_key TEXT, manifest_object_key TEXT, jsonl_sha256 TEXT, error_message TEXT, requested_at TIMESTAMPTZ NOT NULL DEFAULT now(), started_at TIMESTAMPTZ, completed_at TIMESTAMPTZ. (Worker fills this in AUDIT-003; the table is created here so the full schema is frozen day one.)

Migrations

All under backend/src/main/resources/db/migration/platform/ (platform DB only, never tenant DB): - V001__audit_log_init.sql — CREATE TABLE audit_log (full §B.5 columns + the two CHECKs above). - V002__audit_tenant_salt.sql — CREATE TABLE audit_tenant_salt. - V003__audit_export.sql — CREATE TABLE audit_export + audit_export_tenant_requested_idx (tenant_id, requested_at DESC). - V004__audit_roles.sql — create roles and grants (see Service Layer). - V005__audit_indexes.sql — all audit_log indexes: - audit_log_tenant_ts_idx (tenant_id, business_committed_at DESC) - audit_log_actor_ts_idx (actor_user_id, business_committed_at DESC) WHERE actor_user_id IS NOT NULL - audit_log_correlation_idx (correlation_id) WHERE correlation_id IS NOT NULL - audit_log_customer_idx (customer_id) WHERE customer_id IS NOT NULL - audit_log_contract_idx (contract_id) WHERE contract_id IS NOT NULL - audit_log_signed_link_idx (signed_link_id) WHERE signed_link_id IS NOT NULL - audit_log_producer_dedup_uk (producer_module, producer_outbox_id) UNIQUE WHERE producer_outbox_id IS NOT NULL (FR-06) - audit_log_acting_tenant_idx (acting_on_tenant_id, business_committed_at DESC) WHERE acting_on_tenant_id IS NOT NULL

Service Layer

  • Three PostgreSQL roles (Architecture §7.2, AUDIT-ADR-07), created in V004__audit_roles.sql:
  • papillon_app — GRANT INSERT, SELECT ON audit_log; SELECT ON audit_tenant_salt; SELECT, INSERT, UPDATE ON audit_export. Explicitly NO UPDATE, NO DELETE on audit_log. Used by the Spring Boot runtime.
  • papillon_audit_admin — GRANT ALL incl. DDL on all three tables. Used by Flyway at deploy time only; the runtime JVM never receives these credentials.
  • papillon_audit_reader — SELECT only. Created here for forward-compat but not consumed in V0 (the query API arrives in V1). May be deferred to the V1 query story if the team prefers; documented either way.
  • AuditSchemaMigratedCallback (...audit.internal) — a Flyway Java callback on AFTER_EACH_MIGRATE. Runs inside the migration transaction under papillon_audit_admin. INSERTs exactly one audit_log row with event_type = 'AUDIT_SCHEMA_MIGRATED', producer_module = 'deploy', tenant_id = NULL, result = 'SUCCESS', and a payload of {migration_id, description, checksum}. If the callback throws, the whole migration rolls back (no partial state). See Architecture §9.2 for the reference implementation.

API Endpoints

None.

Validation Rules

  • result constrained by DB CHECK to SUCCESS|FAILURE|INFO.
  • AUDIT_SCHEMA_MIGRATED is the only event_type permitted to carry tenant_id = NULL; all other rows must have a non-null tenant_id (enforced by the two table CHECKs).
  • papillon_app has no DDL, no UPDATE, no DELETE on audit_log — verified by role grants, not by trigger.

Multi-Tenant Considerations

  • Every audit_log row carries tenant_id (NULL only for AUDIT_SCHEMA_MIGRATED). country_code is snapshotted per row so an exported event is self-contained (no join needed).
  • All three tables live on the platform DB for every tenant, including BYO-DB tenants (AUDIT-ADR-01). No AUDIT migration ever runs on a tenant's own DB. Both V1 DB profiles (SHARED, BYO) therefore behave identically for audit storage — verify the Flyway config targets only db/migration/platform/.
  • audit_tenant_salt.tenant_id and audit_export.tenant_id are FKs to tenant(id) with ON DELETE RESTRICT, blocking tenant hard-delete (belt-and-suspenders over the CIMA/ARTCI retention rule).

Audit & Logging

  • Emits AUDIT_SCHEMA_MIGRATED (PRD §B.1, AC-08.3) once per applied migration, written by the deploy pipeline role only. The running application physically cannot write this event_type (closed at both the application boundary, FR-01, and the Postgres role level).

Frontend Scope

N/A — backend only.

Acceptance Criteria

AC-05.1: App role is INSERT-only on audit_log
Given the application database role papillon_app
When it attempts UPDATE or DELETE on audit_log
Then PostgreSQL rejects the statement with "permission denied"

AC-08.3: Schema migrations are logged out-of-band
Given a CR-approved Flyway migration that runs against audit_log
When the migration completes
Then exactly one AUDIT_SCHEMA_MIGRATED row appears, written by papillon_audit_admin, carrying the migration id and checksum
And the running application (papillon_app) has no code path able to write that event_type

AC-05.3: DDL guard (process)
Given a migration that changes the audit_log schema
When it is proposed
Then it must be reviewed in CR with the "audit-schema" label and signed off by the founder; schema migration is the only sanctioned path to alter the table

Compliance Rules

  • CIMA Reg. 01-24 §16.5 — audit_log is the immutable proof base. Append-only enforced via role grants (AUDIT-US-05, NFR-07). Status VÉRIFIÉ for the minimal-field requirement (id, timestamp, user, nature, result — CIMA art. 37).
  • AC-05.4 honest scope — V0/V1 append-only defends against application bugs and compromised app credentials, not against a malicious DBA holding papillon_audit_admin. Cryptographic tamper-evidence (hash chain / WORM) is V2. This must be reflected in any customer-facing §16.5 claim so it is not over-stated.
  • CI fiscal + CIMA 10-year retention (AC-07.1, AC-07.4) — structurally honoured here: no DELETE grant on papillon_app, and no purge tool exists in V0. [LEGAL-REVIEW]: the retention duration for application/server logs is PRÉLIMINAIRE in memo F5; the 10-year floor for insurance + e-money events is VÉRIFIÉ.

Standards & Conventions

  • docs/standards/database-guidelines.md — Flyway naming, platform-only migrations, append-only role pattern.
  • docs/standards/multi-datasource-patterns.md — platform vs tenant DB; AUDIT targets platform/ exclusively.
  • docs/standards/critical-rules.md — rule 1 (tenant_id on every row), rule 3 (audit catalog).
  • docs/standards/compliance-discipline.md — [LEGAL-REVIEW] handling for retention durations.
  • docs/standards/java-spring-guidelines.md — Spring Modulith internal package conventions.

Testing Requirements

Unit Tests

  • AuditSchemaMigratedCallback builds the correct INSERT payload from MigrationInfo.

Integration Tests

  • AC-05.1: connect as papillon_app, attempt UPDATE audit_log SET result='FAILURE' and DELETE FROM audit_log — both raise SQLException containing "permission denied".
  • AC-08.3: run a Flyway migration touching audit_log; assert exactly one AUDIT_SCHEMA_MIGRATED row exists, producer_module='deploy', tenant_id IS NULL, payload carries migration_id + checksum.
  • Callback rollback: force the callback to throw; assert the migration is fully rolled back (no schema change, no audit row).
  • Tenant isolation (schema-level): insert rows for two tenants directly; assert each is selectable only when filtered by its tenant_id.

What QA Will Validate

  • All five migrations apply cleanly on a fresh platform DB.
  • papillon_app cannot UPDATE/DELETE audit_log.
  • One AUDIT_SCHEMA_MIGRATED row per migration.
  • The full §B.5 column set exists day one (no follow-up ALTER TABLE audit_log is needed by AUDIT-001b/001c/002/003).

Out of Scope

  • Outbox listener, AuditEventBuilder, catalog enum, producer stubs (AUDIT-001b).
  • PiiHasher and salt provisioning logic (AUDIT-001c) — the audit_tenant_salt table is created here but populated there.
  • Export worker (AUDIT-003) — the audit_export table is created here but filled there.
  • papillon_audit_reader consumption (V1 query API).
  • Active 10-year purge tool (V2).
  • Cryptographic tamper-evidence / WORM (V2).

Definition of Done

  • [ ] All five platform-DB migrations (V001..V005) apply cleanly
  • [ ] audit_log carries the full PRD §B.5 column set (schema frozen for V0)
  • [ ] papillon_app rejects UPDATE/DELETE on audit_log (AC-05.1 verified)
  • [ ] AUDIT_SCHEMA_MIGRATED written once per migration by papillon_audit_admin (AC-08.3 verified)
  • [ ] Callback failure rolls back the entire migration (no partial state)
  • [ ] Tenant isolation verified (tenant_id present; both V1 DB profiles route audit to platform DB)
  • [ ] Audit log entry present for the required event (AUDIT_SCHEMA_MIGRATED)
  • [ ] [LEGAL-REVIEW] note on retention duration retained in the code/migration comments
  • [ ] No silent failures: a failed migration or callback aborts the deploy