Skip to main content
IORP II requires that trustees maintain tamper-evident records of all material governance actions. PensionsPortal.ie implements this through an append-only audit_logs table in Neon PostgreSQL. No row may ever be updated or deleted.

​
Design Principles

  • Append-only — INSERT is the only permitted operation. No UPDATE, no DELETE, ever.
  • Immutable snapshots — previous and new state are captured as JSON blobs at the moment of the event, not as foreign keys to records that may later change
  • No PII in notes — the notes field is for metadata (e.g., job IDs) only. Personal data is captured via previousState/newState snapshots, not free-text
  • Tenant-scoped — every entry is linked to at least one of brokerId, employerId, schemeId, or memberId

​
Schema

// src/db/schema/audit.ts
export const auditLogs = pgTable("audit_logs", {
  id:            text("id").primaryKey(),
  // Tenant scope
  brokerId:      text("broker_id"),
  employerId:    text("employer_id"),
  schemeId:      text("scheme_id"),
  memberId:      text("member_id"),
  // Actor
  actorId:       text("actor_id").notNull(),
  actorType:     auditActorTypeEnum("actor_type").notNull(),
  // Event
  action:        text("action").notNull(),
  entityType:    text("entity_type").notNull(),
  entityId:      text("entity_id").notNull(),
  // State snapshots (JSON blobs)
  previousState: jsonb("previous_state"),
  newState:      jsonb("new_state"),
  // Request context
  ipAddress:     text("ip_address"),
  userAgent:     text("user_agent"),
  // Metadata (no PII)
  notes:         text("notes"),
  // Immutable timestamp
  timestamp:     timestamp("timestamp", { withTimezone: true }).notNull(),
})

​
Actor Types

export const auditActorTypeEnum = pgEnum("audit_actor_type", [
  "Broker",   // Human user with broker role
  "System",   // Automated background process
  "Provider", // Third-party provider action
  "Member",   // Member-facing action (future use)
])

​
Example Events

actionentityTypeTypical trigger
MemberStatusChangedMemberFSM transition (e.g., PendingEnrolment → Active)
ContributionFileUploadedContributionBatchBroker uploads CSV
ChangeProposalApprovedChangeProposalBroker approves AI suggestion
ChangeProposalRejectedChangeProposalBroker rejects AI suggestion
SchemeCreatedSchemeNew scheme provisioned
PolicyDocumentUploadedDocumentGovernance document stored

​
Querying the Audit Log

The audit log is accessible via GET /api/audit-log with query parameters: 
GET /api/audit-log?entity_type=Member&entity_id=<id>&limit=50
Supported filters:
  • entity_type — filter by entity type (e.g., Member, Scheme)
  • entity_id — filter by specific entity
  • action — filter by action name
  • actor_id — filter by who performed the action
Results are returned in reverse chronological order (newest first).

​
Tamper-Evidence

The append-only guarantee is enforced at:
  1. Application layer — no service method calls UPDATE or DELETE on audit_logs
  2. Database layer — Row-Level Security (RLS) policy on the audit_logs table permits INSERT for authenticated roles but explicitly denies UPDATE and DELETE
Future enhancement: cryptographic chaining of audit log entries (similar to a blockchain/hash chain) to make retroactive tampering mathematically detectable. This is on the roadmap for higher assurance deployments.

​
IORP II Relevance

Article 25 of IORP II requires IORPs to have adequate internal control systems. The audit log provides:
  • A complete record of trustee actions for regulatory inspection
  • Evidence of the four-eyes principle (AI proposals require human approval)
  • Traceability from each compliance outcome back to the actor who triggered it
Audit logs are retained for a minimum of 7 years. See Log Retention and Integrity for the full retention policy.