Audit log database schema
An append-only audit log with actor, action, target, and change diff. Designed for tamper-evident compliance logging and high write throughput via monthly partitions.
Database schema previewAudit log database schema
Recreate this in Raltey
Copy this prompt
Paste this into Raltey's AI agent to generate the same diagram, then tweak it on the canvas.
Prompt
Design a PostgreSQL database schema for: Audit log database schema. An append-only audit log with actor, action, target, and change diff. Designed for tamper-evident compliance logging and high write throughput via monthly partitions. Include these tables: audit_event. Use proper primary keys, foreign keys, and indexes. Export SQL DDL for PostgreSQL.
Context
- - Append-only - no UPDATE or DELETE.
- - Partitioned by month on occurred_at.
- - Optional cryptographic hash chain for tamper evidence.
Patterns
- - Partition by month: CREATE TABLE audit_event_2026_05 PARTITION OF audit_event FOR VALUES FROM ('2026-05-01') TO ('2026-06-01').
- - Hash chain: hash = sha256(prev_hash || row_data) so any retroactive change breaks the chain.
- - Index per partition on (workspace_id, occurred_at DESC).
Tables
The schema, table by table
audit_event
Parent partitioned table - children are audit_event_YYYY_MM.
DBML - audit_event
Table audit_event {
id uuid not null
occurred_at timestamptz not null
actor_type text not null // user, system, api
actor_id uuid
workspace_id uuid
action text not null // diagram.created, project.deleted
target_type text
target_id uuid
metadata jsonb
ip_address inet
user_agent text
prev_hash text
hash text
pk (occurred_at, id)
}Notes from the field
- No FK constraints on actor_id / target_id - they may point at rows that have since been soft-deleted.
- Drop old partitions to enforce retention - DROP TABLE is O(1) vs DELETE on a billion-row table.
- For tamper evidence in regulated industries, periodically anchor the latest hash to an external timestamping service.
Frequently asked
Why not use INSERT INTO audit_event without partitioning?
For audit logs, you usually retain by time and query by time + tenant. Monthly partitions make retention drops O(1) and let the planner skip old partitions for recent queries.
Do I need a hash chain for audit logs?
Only in regulated industries (finance, healthcare). For most B2B SaaS, partitioning + append-only access controls are enough.
More templates
FlowchartSaaS onboarding email flowFlowchartCustomer support runbook flowFlowchartPayment webhook retry and error flowFlowchartGitHub PR review and merge flowFlowchartLogin with magic link flowArchitectureThree-tier SaaS architecture on AWSArchitectureMicroservices architecture on KubernetesArchitectureReal-time data pipeline on GCP
Open this template in Raltey
Edit it on the canvas, ask the AI agent to extend it, then share or export.
Start for free