Fintech Development

Financial Audit Log Schema Prompt

Design an audit log schema that captures who changed what financial record, when, and from where, standing up to regulatory scrutiny.

Anselm Fowel / Public collection27 Sept 2026

Your prompt

The prompt

You are a fintech engineer who has designed audit logging that survived a real regulatory examination, and you know examiners ask for the one record you didn't think to capture.

My system in [DATABASE] handles [RECORD TYPES: ACCOUNT BALANCES / LOAN TERMS / KYC STATUS / TRANSACTION RECORDS] and changes are made by [ACTOR TYPES: INTERNAL EMPLOYEES VIA ADMIN TOOL / AUTOMATED JOBS / CUSTOMER-FACING API]. We're subject to [REGULATION IF KNOWN, e.g., SOX / STATE LENDING REGULATIONS / PCI].

First, design the audit log schema itself: what's captured for every change (actor identity, actor type, timestamp, the record and field changed, the before and after value, the originating request ID, and the business justification or reason code if applicable), and explain why storing only the after-value is insufficient for a financial audit trail.

Next, address how the audit log itself is protected from tampering: whether it's append-only at the database level, whether it lives in a separate datastore from the operational data it audits so a compromise of one doesn't compromise the other, and whether a hash chain or similar mechanism is warranted given [REGULATION IF KNOWN].

Then design how automated system changes are distinguished from human-initiated ones, including how you capture "on behalf of" relationships, such as a support agent acting for a customer, or a batch job applying an automated rate change to thousands of accounts, without logging that as one ambiguous bulk entry that can't be traced to individual affected records. Also design the query interface this log needs to actually support an audit: answering "show every change to account X" and "show every change made by employee Y" efficiently, given the log will grow far faster than the operational tables it audits.

Finally, output the schema as DDL for [DATABASE] with comments, followed by 3 example audit records showing a customer-initiated change, an employee-initiated change on behalf of a customer, and a batch job change, and a checklist of 6 things a regulator would look for that this design specifically addresses.

2,108 characters

Your prompt

Recent articles