APSCA Audit & Event Logging Architecture

Executive decision

Keep the audit authority in PostgreSQL

Recommended pattern: one generic, versioned audit event spine; an explicitly indexed and erasable subject compartment; and a synchronous write in the same transaction as the domain mutation.

One authority

PostgreSQL stores the committed audit fact. Current state stays in domain tables; audit history does not replace it.

One event contract

Universal fields are relational. Action-specific details are allowlisted, typed, and versioned—not arbitrary JSON dumps.

One erasure path

Identifying values live in subject-owned rows that can be cleared without deleting the non-identifying event header.

Choose structured redaction on erasure as the default PII strategy.
Emit legally significant events explicitly inside the domain transaction.
Use database triggers as a later defensive guardrail, not as the sole semantic mechanism.
Use Trigger.dev, CDC, and any future data lake only after the authoritative event commits.
Keep workflow receipts, automation telemetry, delivery runs, comments, and current state in their own modules.

Current-state finding

The existing “logs” are partial by design

They do not capture a full record automatically. Each table stores only what its caller explicitly supplies. An unwired mutation path leaves no corresponding history.

Record	What it is	What it actually preserves	Important gap
`UserActivityLog`	Selected user administration history	Creation, role changes, caller-supplied states, actor	Not every profile/user change is wired; deletion cascades
`FirmMembershipAuditLog`	Intended membership audit	Add/edit/failed-match contracts	No production writer was found
`MessageActivityLog`	Recipient interaction history	Open, read/unread, links, actions, attachments	Browser click receipts can fail independently
`MessageRecipient`	Current inbox state	Read status and first/last-read times	This—not history—answers “is it unread now?”
`TaskLogEntry`	Comments plus readable task history	Free text, creator, timestamps	No structured old/new values; a bulk coverage gap exists
`PlatformEvent`	Automation input	Only explicitly emitted platform facts	Not a complete domain audit trail
`WorkflowRun`	Workflow command receipt	Idempotency, outcome, before/after state	Only workflows create these records
`TriggerRun` / `DeliveryRun`	Operational observability	Automation and provider execution results	Explains execution, not domain history

Do not merge these solely because their names contain “log” or “event.” Audit history, domain content, current state, automation facts, workflow receipts, and delivery telemetry answer different questions.

Recommended boundary

One transaction first; asynchronous work after commit

Domain command Actor, reason, approved before/after facts, command ID

One PostgreSQL transaction Domain mutation + AuditEvent + AuditEventParty + optional outbox

After commit Trigger.dev, search, exports, CDC, analytics or data lake

Dissociation illustrates why the semantic write belongs with the command. The command knows that the relationship ended, who caused it, the reason and effective time, and exactly which profile and membership facts are approved for the historical snapshot. A row observer sees only physical changes.

await audit.record(tx, {
  uniqueKey: commandId,
  action: "employment.dissociated",
  schemaVersion: 1,
  captureKind: "SNAPSHOT",
  actor,
  parties,
  structuralData: { reasonCode, effectiveAt }
});

Alternatives evaluated

Capture-layer decision matrix

Avenue	Atomic?	Business meaning?	Coverage	Decision
Explicit transactional write	Yes	Yes	Canonical command paths	Primary
PostgreSQL trigger	Yes	Physical rows only	All writers to selected tables	Defensive
Prisma query extension	Context-sensitive	Top-level Prisma operation	Only the extended client	Adapter only
Trigger.dev	No distributed atomic commit	Only the sent payload	Accepted jobs	No-go as authority
CDC / logical replication	Committed WAL, later projection	Physical changes	Published database tables	Downstream
Data lake / SIEM	No	Exported projection	Only exported datasets	Optional later

Why database triggers are not the primary mechanism

PostgreSQL triggers are strongly atomic and can catch changes that bypass application repositories. They do not inherently know the APSCA human actor, business reason, command boundary, or correct multi-table snapshot. Actor context must be supplied through transaction-local settings, and erasure paths must be designed so a trigger never copies OLD PII back into history.

Recommended later use: detect or reject uncorrelated direct changes on selected high-risk tables, or emit a clearly labeled low-level *.row_changed forensic event.

Why Prisma Client extensions are an adapter only

An extension produces a separate extended client; the original client and independently created clients remain available. Migrations, scripts, direct SQL, and other services bypass it. Query interception sees the top-level operation and intended arguments, not a governed aggregate snapshot.

A client extension may still offer a clean forAuditContext(...) or audited-command API. It must delegate to the same explicit transaction-bound audit module.

Why a data lake is downstream only

Operational history and erasure need indexed, transactional data. A lake adds asynchronous delivery, duplicate/order handling, schema evolution, backup windows, and another full subject-discovery surface. If introduced later, it should receive minimized post-commit projections and erasure tombstones with acknowledgements.

Schema recommendation

Generic event header + erasable party rows

AuditEvent

Immutable event ID, unique command key, action, schema version, capture kind, source, occurrence/recording times, actor kind, correlation/causation IDs, retention policy key, and strictly non-identifying structural data.

AuditEventParty

Actor, subject, recipient, or resource role; entity lookup; canonical data-subject lookup; all direct and quasi-identifying values; erasure timestamp and job receipt. Human actors are parties too, so they can be erased independently.

AuditOutbox

Transactionally committed delivery intent for asynchronous projections, exports, notifications, or erasure tombstones. Consumers deduplicate by the immutable event ID.

ErasureJob

Verified request, policy version, status, category counts, holds, timestamps, and completion evidence without copying the erased identity into the final receipt.

ErasureSinkReceipt

Destination, tombstone ID, attempts, acknowledgement, and terminal result for every external or analytical copy.

Immutable

Event structure

Action, version, occurrence time, non-identifying reason, retention policy, and event/correlation identity. Corrections append a new event.

Deliberately destructible

PII compartment

Live subject IDs, relationship identifiers, display data, profile snapshots, membership numbers, and identifying deltas. Only a privileged erasure routine may clear them.

Capture policy

Snapshot, delta, or timestamp?

Timestamp

An action occurred

Use when the durable fact is the occurrence itself. Current state remains in the domain table.

Delta

Approved fields changed

Use when a reviewer must understand the exact old/new values or state transition.

Snapshot

A boundary state matters

Use only for a named, versioned projection at a legally or operationally significant boundary—never every database field.

Use case	Current truth	Audit contract	PII rule
Dissociation	Employment lifecycle	`employment.dissociated.v1` · snapshot	Profile, membership and relationship IDs are erasable
Message read/unread	MessageRecipient state	Timestamp/state transition	Recipient and actor parties; never copy the body
Open/link/action/attachment	Interaction receipt only	Timestamp	Minimize destination metadata
Task status	Task status/completion time	Delta	Status can be structural; actor is erasable
Task assignment	Current assignee	Delta	Old/new assignees are subject-owned
Task comment	Domain content	Optional “comment added” timestamp	Never copy comment text into audit
Auditor profile	Profile aggregate	Changed paths + approved old/new values	Values live in the auditor compartment
User account	User account	Role/status delta; activation timestamp	All person-related values are erasable
Firm profile	Firm aggregate	Delta	Human contact data belongs to a subject
Firm team member	Membership	Delta; approved exit snapshot only if needed	Member values and links are erasable

Foundational GDPR choice

Choose structured redaction, with one correction

Not sufficient alone

A · Reference by ID

Minimizes duplicate PII but loses the required historical dissociation snapshot. IDs also remain personal while linkable.

Recommended

B · Structured redaction

Preserve action, time and non-identifying structure; clear the indexed subject payload and live identity links on erasure.

Separate investment

C · Crypto-shredding

Strong for selected copies, but requires per-subject key boundaries, rotation, backups, escrow, caches and multi-subject event handling.

A stable pseudonymous token is still personal data while identification or singling-out remains reasonably possible. The safe default after erasure is to lose cross-event subject correlation. A surviving stable token requires privacy approval and irreversible destruction of its identity mapping.

End-to-end operation

Erasure is a resumable workflow, not one DELETE

Verify and inventory

Create an idempotent job, verify the request, and resolve all internal IDs, files and registered destinations before deleting lookup roots.

Evaluate policy and holds

Resolve purpose, lawful basis, normal TTL and erasure rule for each category. Apply only a documented legal obligation or claims hold.

Erase the primary database atomically

Lock the subject, clear subject-owned audit data and domain records, prevent triggers from re-copying old PII, preserve the PII-free event header, and commit outbox tombstones.

Propagate downstream

Delete or redact identity, files, communications, search, analytics and any future lake/SIEM copies. Track one acknowledgement per sink and retry failures.

Handle backups and restore

Put old backup data beyond use until expiry. Every restore must replay completed erasures before the environment becomes available.

Complete with non-identifying evidence

Close only after required acknowledgements or approved holds. Keep job ID, policy version, counts and outcomes—never recreate the erased identity in the receipt.

Survives

An event occurred at a time
Action and non-identifying reason category
Schema and retention-policy versions
Non-identifying erasure completion evidence

Removed without a lawful hold

Name, email, phone, address and image
User, profile and membership identity links
Identifying free text and quasi-identifiers
Recoverable subject keys or identity mappings

Disposable prototype evidence

The generic model is queryable and the trigger is atomic

Generic audit dataset

100,000

Temporary events with indexed entity/action history lookups.

Dissociation lookup

0.147 ms

Local indexed lookup; evidence of queryability, not a capacity forecast.

Current read state

0.031 ms

Indexed domain-table lookup, deliberately separate from audit history.

Prototype assertion	Observed result	Architecture implication
Generic entity history	B-tree index used; 0.042–0.147 ms locally	Known audit queries do not require a table per entity
Entity-specific history	0.010–0.015 ms locally	Slightly cheaper lookup, much larger schema/erasure surface
Trigger rollback	Inside transaction: event = 1; after rollback: event = 0	Database triggers share mutation atomicity
Subject erasure	PII and subject ID cleared; action/reason preserved	Option B preserves structural integrity

Policy model

No single “keep logs forever” rule

GDPR requires purpose limitation, minimization and storage limitation. It does not establish one general minimum retention period for APSCA product activity. Retention must be approved by category and any other applicable legal or regulatory requirement.

Class	Examples	Default access	Required policy decision
Legally significant domain history	Dissociation, consent, activation	Narrow legal/support/admin roles	Lawful basis, snapshot, TTL and holds
Security administration	Authentication and permission changes	Security and authorized administrators	Security retention and export policy
Product history	Tasks, profiles, firms and team changes	Authorized product users and support	User visibility and TTL
Interaction receipts	Message open/read/unread/click	Recipient where useful; limited support	Which interactions warrant persistence
Automation observability	Workflow, trigger and delivery runs	Engineering/support/admin	Operational TTL and payload minimization
Analytics	Aggregated usage	Analytics/engineering	Prefer anonymous export; define lake lifecycle

Immediate downstream handoff

The Employment story can now proceed

Model a lifecycle/end mutation with effective time, initiator and reason; do not represent dissociation only as a raw delete.
Write employment.dissociated.v1 in the same PostgreSQL transaction.
Define a named, allowlisted snapshot projection; never serialize the entire auditor aggregate.
Put profile, membership and relationship identifiers in the erasable subject compartment.
Store a retention policy key, but do not hard-code an unapproved duration into the schema.
Test atomicity, idempotency, versioning, subject lookup, redaction and protection against PII re-capture.

Correctness has no dependency on workflow state, Trigger.dev, Prisma interception, generic database triggers, CDC or a data lake.

Before implementation

Decisions that remain with business and privacy owners

Privacy / GDPR owner

Lawful basis, TTL and hold rules by category
Whether any post-erasure token is truly non-identifying
Backup beyond-use and restore-replay policy
Country-specific employment/certification retention

Product / operations

Exact dissociation snapshot fields
User-visible versus support-only histories
Telemetry versus durable interaction receipts
Appeal/correction workflows and identity retention

Architecture / delivery

Approve event-plus-party and outbox boundary
Decide on a later trigger-hardening story
Keep a data lake out of the current dependency chain
Fund key management separately if crypto-shredding returns

Decision record

Final go / no-go

Decision	Result
Generic governed event spine	Go
History table per entity	No-go by default
Reference-only PII strategy	No-go alone
Structured subject-indexed redaction	Go
Crypto-shredding as default	No-go
Explicit same-transaction semantic write	Go
PostgreSQL triggers	Defensive only
Prisma extension as universal capture	No-go
Prisma extension as command ergonomics	Optional
Trigger.dev as authoritative logger	No-go
Trigger.dev after transactional outbox	Go
Data lake / CDC as audit authority	No-go
Minimized erasure-aware downstream lake	Optional later
Workflow history as platform audit	No-go

Research basis

Selected primary sources

Vendor examples demonstrate technical and product patterns; they do not establish APSCA's legal compliance. No source reviewed establishes a GDPR-wide minimum retention period for APSCA product activity logs. Country-specific legal review remains required.