A recorded change and an identified initiator are different pieces of evidence. PostgreSQL’s documented audit-trigger example captures the operation, timestamp, current_user and row data. But current_user is the effective database permission identity, not necessarily the human behind an application connection. Before relying on that history in a review, the technical owner needs to establish the missing link.
Treat a defensible per-field audit trail as a bounded evidence system: reconstructable, attributable, complete enough for the agreed scope, protected and retrievable. Its acceptance criteria should cover the change record, identity path, transaction outcome and retrieval process together.
A database timestamp is not a change history
When updated_at and updated_by describe only the latest modification, they leave the preceding values, intervening changes and earlier initiators unaccounted for. Evaluate a latest-row snapshot as current-state evidence. For a material field, require a retrievable sequence across the review period rather than relying on the final value and last modifier.
Current values and latest modification metadata.
A sequence of field changes, initiators and committed outcomes within declared coverage.
Set the retrieval requirement before choosing the capture mechanism.
Match the retained evidence to the question a reviewer needs answered.Define defensible as evidence, not certification
The Open Worldwide Application Security Project (OWASP) describes a Payment Card Industry Data Security Standard (PCI DSS) audit-log example as a chronological, independently verifiable trail for reconstructing and examining attributable transactions. That supports the reconstruction goal. It is not a universal definition of auditor acceptance.
Write down the evidence boundary: tenants, entities, fields, operations and periods covered. Include human, service, integration and database-administrator changes. Make explicit coverage decisions for direct database edits, migrations, deletion and bulk processing. Do not let the application’s normal update path define the scope by default.
List excluded populations, change paths, historical periods and deliberately withheld values. Assign a technical owner for capture and retrieval, with accountable business, security or legal owners for scope, access and retention decisions. The resulting evidence should be assessed within that boundary, without claims of certification, legal assurance, guaranteed auditor acceptance or cryptographic non-repudiation.
Record field changes without collecting everything
Use the following conceptual event schema as a design starting point, not a prescribed table layout. A JavaScript Object Notation (JSON) field diff is a valid per-field representation if each changed field remains individually interpretable. One audit row per field is not a requirement.
Stable event identifier; tenant, entity and record key.
Field path, schema version, action and typed old/new values or explicit redacted/reference forms.
Trusted actor identifier and type; service and delegation context.
Server event time; recorded or ingestion time if asynchronous; transaction/correlation identifier.
Outcome and committed status; reason or approval reference where required.
Logical components, not a required storage layout or execution order.
Build the event from a precisely identified change through to its verified outcome.Hypothetical one-field change: a request’s status changes from the string “pending” to the string “approved”. Attach a stable event identifier and tenant/entity/record key, identify status as the field path, and preserve the applicable schema version. Attribute the change to the authenticated human actor, with the executing service and any delegation recorded separately. Include server event time, transaction/correlation identifier and any required approval reference. Mark the outcome committed only when the change commits; if collection is asynchronous, retain its recorded or ingestion time separately.
Define value semantics before implementation. Keep null distinct from an absent field and an empty string. Represent deletion as an action, not an unexplained missing value. Preserve nested field paths against their schema version and retain mappings for migration-driven renames. Link bulk operations to their affected records and field changes. Where derived values matter to review, identify the derivation and relevant version rather than presenting them as direct human input.
Set a field-level recording policy: retain a typed value, a masked representation or a reference to separately protected evidence. Do not record passwords, tokens or indiscriminate sensitive before-and-after values. Make redaction explicit so reviewers can distinguish withheld content from capture failure. Assign responsibility for keeping referenced evidence retrievable.
In PostgreSQL, a trigger runs in the same transaction as its triggering statement. An error in either rolls back the database effects of both. Business changes and in-database audit writes can therefore commit or roll back together. That guarantee does not extend to external side effects, and a rolled-back audit write is not a retained record of a failed attempt.
Keep committed field history and failed-attempt evidence distinct. Use separate security-event logging for failed attempts, and test that an attempted value never appears as a committed change.
Capture attribution where it is actually known
Map human attribution through authenticated server-side context. Never trust a client-supplied user identifier as evidence of who acted. Preserve the initiating actor separately from the executing service, including delegation where relevant. For autonomous service and integration changes, record the system identity rather than inventing a human initiator.
Keep database identity as a separate fact. PostgreSQL’s current_user is the effective permission identity. It can differ from session_user through SET ROLE and during execution of a SECURITY DEFINER function. These identities do not establish which end user initiated a change through a pooled application connection.
- Application events: use authenticated application context to capture actor, purpose, approval and delegation. Require another capture path for changes that bypass the application.
- Database triggers: PostgreSQL automatically executes a function for specified operations; its audit example covers inserts, updates and deletes. Verify configured coverage and trusted identity propagation rather than assuming the trigger supplies both.
- Change data capture (CDC): assess the chosen implementation’s emitted changes, transaction boundaries, identity fields and recovery behavior. Require evidence for each capability; do not assume application intent or complete delivery.
- Temporal history: SQL Server system-versioned temporal tables support point-in-time analysis and automatically retain the prior row version on update or delete. Those documented capabilities do not establish actor attribution, purpose or protection against privileged alteration.
Choose the combination against the evidence boundary. Require a demonstrable link from trusted actor context to committed database changes and retrieved history. For direct administrative edits, define an attributable administrative path. If a path remains uncovered, record that limitation and obtain the scope owner’s decision before treating the design as acceptable.
Protect, export and retrieve the evidence
Use a risk-based protection model. Limit application-role history permissions, separate administrative permissions, restrict read and export access, and log administrative changes to capture rules and evidence stores. Do not treat append-only access for the application role as tamper-proof protection against database owners or superusers. Where privileged alteration is a material risk, require independently protected export and retention under separate control.
For asynchronous export, consider an outbox written in the business transaction and delivered by a separate worker. Require retries, stable event identifiers, destination deduplication, lag and gap alerts, and reconciliation between committed source events and retained destination evidence. Keep event time separate from ingestion time so delayed collection is visible. Acceptance should depend on demonstrated outage recovery and reconciliation, not an exactly-once delivery claim.
If hashes or hash chains are proposed, require separately protected checkpoints and an explicit verification procedure. Reject any design claim that a chain under the same privileged control prevents rewriting both history and chain. A matching hash should not be accepted as proof that the original event was true, or as a guarantee of non-repudiation.
Assign owners for retention and legal-hold requirements; do not adopt a universal period. Include exports, referenced evidence and schema mappings in those decisions. Require a readable, access-controlled export for a specified period and schema version, with access and export activity logged. Give retrieval an operational owner before the first review request arrives.
Test the review packet, not just the happy path
Make a review packet the acceptance deliverable. For a specified period and schema version, require it to answer what changed, who or what initiated it, and what committed. Include scope and exclusions, reconciliation results, evidence of protection, relevant administrative changes and a readable export. Missing or redacted values need an explanation, not an assumption of completeness.
No rolled-back value appears as committed; failed-attempt evidence is retained separately.
Authenticated initiators remain distinguishable behind the shared database login.
Demonstrate administrative attribution or escalate the uncovered path for a scope decision.
Identify affected records, field changes and operation linkage.
Repeated deliveries resolve to one logical event through stable identifiers and deduplication.
Demonstrate alerts, resumed delivery and source-to-destination reconciliation.
Historical field paths and values remain interpretable against their schema version.
Test app-role rejection and privileged-change detection against independently protected evidence.
Retain test results and obtain explicit decisions on unresolved gaps.
Use these recommended tests to decide whether to accept the design or require remediation.Make the release decision against that packet. Close uncovered paths or narrow the agreed scope with the accountable owner’s approval, then update the exclusions. A defensible position is a specific account of what the system can demonstrate, how that was tested and where its evidence ends.
