Design a Scalable Append-Only Audit Logging System

Get a structured, immutable schema design for tracking sensitive data changes, including field-level diffs and compliance-ready partitioning strategies.

Prompt

Prompt
You are a senior database architect. Design an append-only audit logging system for sensitive data changes. Ask these 4 questions first and wait for my reply: 1. What is the primary database engine? 2. What is the approximate daily volume of changes? 3. Are there specific compliance standards like GDPR or SOC2 to address? 4. What is the required retention period for these logs? After I answer, provide the design following these rules: Use a normalized schema that captures the actor, operation, and state transition. Use a JSONB or similar binary format for field-level diffs to minimize storage. Define clear partitioning strategies for performance and long-term storage. Output format: 1. Schema Definition: SQL table structure with appropriate indexes. 2. Storage Strategy: Explanation of how to handle high-frequency writes. 3. Compliance Rules: Specific retention and archival policies. 4. Implementation Checklist: Steps for application-level integration. This design is for technical guidance and does not constitute legal or compliance advice; consult with a security professional for your specific regulatory needs.

Example output

Example for: A PostgreSQL-based fintech application processing 50,000 daily changes under SOC2 compliance with a 7-year retention policy.

1. Schema Definition

CREATE TABLE audit_logs (audit_id BIGSERIAL PRIMARY KEY, entity_type TEXT NOT NULL, entity_id UUID NOT NULL, actor_id UUID NOT NULL, operation TEXT NOT NULL, changed_fields JSONB NOT NULL, created_at TIMESTAMPTZ DEFAULT NOW()); CREATE INDEX idx_audit_entity ON audit_logs (entity_type, entity_id); CREATE INDEX idx_audit_time ON audit_logs (created_at);

2. Storage Strategy

To handle high write volume, use table partitioning by range on the created_at column. Create monthly partitions to simplify data lifecycle management. Implement a background worker to offload logs older than 90 days to cold storage like AWS S3 using a Parquet format, keeping only the most recent logs in the hot primary database.

3. Compliance Rules
  • Immutability: Revoke all UPDATE and DELETE permissions on the audit_logs table for application users. Use a dedicated audit service role with INSERT-only access.
  • Retention: Automated partition dropping or archiving after 7 years to meet regulatory requirements.
  • Access Control: Restrict read access to audit logs to security administrators only, requiring multi-factor authentication for query execution.
4. Implementation Checklist
  • Wrap all sensitive data modifications in a database transaction that includes an entry to the audit_logs table.
  • Use a database trigger or a service-layer interceptor to capture the before-and-after state of changed fields.
  • Sanitize PII before logging if the audit system does not require the raw value for reconstruction.
  • Monitor partition growth to ensure query performance remains within the 200ms threshold.

This design assumes standard relational database capabilities and does not cover encryption-at-rest configurations, which should be handled by your infrastructure layer.

Written by AI from this prompt. Your results will vary.

Tips and common edits

Why this works

  • Forces a structured schema definition to ensure data integrity and query performance.
  • Addresses compliance requirements by explicitly including retention and access control rules.
  • Uses a clear four-part output format that separates architecture from implementation steps.

Use this prompt when you need to architect an immutable logging system for sensitive database operations. Paste this into ChatGPT, Claude, or Gemini and answer the four questions to receive a tailored schema and strategy.

Common edits

  • Adjust the retention period based on specific local data protection laws.
  • Modify the diff storage format if the database engine does not support JSONB.

Make it repeatable

Keep using this prompt without pasting it in every time.

Keep exploring

More in Coding