Tracking Every Change for Compliance and Debugging

Core audit table schema (PostgreSQL)

CREATE TABLE audit_log (
    id              BIGSERIAL PRIMARY KEY,
    recorded_at     TIMESTAMPTZ NOT NULL DEFAULT NOW(),

    -- Who made the change
    actor_type      VARCHAR(50) NOT NULL,  -- 'user', 'system', 'api_client'
    actor_id        BIGINT,                -- NULL for anonymous/system
    actor_name      VARCHAR(255),          -- Denormalised for query convenience

    -- What was changed
    entity_type     VARCHAR(100) NOT NULL, -- 'orders', 'customers', 'invoices'
    entity_id       BIGINT NOT NULL,
    action          VARCHAR(50) NOT NULL,  -- 'create', 'update', 'delete', 'approve'

    -- The change itself
    old_values      JSONB,                 -- NULL for creates
    new_values      JSONB,                 -- NULL for deletes
    changed_fields  TEXT[],                -- Array of field names that changed

    -- Context
    request_id      UUID,                  -- Correlation ID
    ip_address      INET,
    user_agent      TEXT,
    source          VARCHAR(50),           -- 'web', 'api', 'import', 'migration'
    reason          TEXT,                  -- User-provided justification

    -- Multi-tenant support
    tenant_id       BIGINT,

    -- Indexing support
    recorded_date   DATE GENERATED ALWAYS AS (recorded_at::date) STORED
);

-- Essential indexes
CREATE INDEX idx_audit_entity ON audit_log (entity_type, entity_id, recorded_at DESC);
CREATE INDEX idx_audit_actor ON audit_log (actor_type, actor_id, recorded_at DESC);
CREATE INDEX idx_audit_date ON audit_log (recorded_date, entity_type);
CREATE INDEX idx_audit_tenant ON audit_log (tenant_id, recorded_at DESC);
CREATE INDEX idx_audit_request ON audit_log (request_id) WHERE request_id IS NOT NULL;

Partitioning by date (PostgreSQL 12+)

CREATE TABLE audit_log (
    -- ... same columns as above ...
) PARTITION BY RANGE (recorded_date);

-- Create partitions for each month
CREATE TABLE audit_log_2026_01 PARTITION OF audit_log
    FOR VALUES FROM ('2026-01-01') TO ('2026-02-01');
CREATE TABLE audit_log_2026_02 PARTITION OF audit_log
    FOR VALUES FROM ('2026-02-01') TO ('2026-03-01');

-- Automate partition creation with pg_partman or similar

Audit trait for Eloquent models

trait Auditable
{
    public static function bootAuditable(): void
    {
        static::created(fn ($model) => $model->recordAudit('create'));
        static::updated(fn ($model) => $model->recordAudit('update'));
        static::deleted(fn ($model) => $model->recordAudit('delete'));
    }

    protected function recordAudit(string $action): void
    {
        $oldValues = $action === 'create' ? null : $this->getOriginal();
        $newValues = $action === 'delete' ? null : $this->getAttributes();

        // Filter out unchanged values and non-auditable fields
        if ($action === 'update') {
            $changedFields = array_keys($this->getDirty());
            $oldValues = Arr::only($oldValues, $changedFields);
            $newValues = Arr::only($newValues, $changedFields);
        }

        // Skip if nothing actually changed
        if ($action === 'update' && empty($changedFields)) {
            return;
        }

        AuditLog::create([
            'entity_type'    => $this->getTable(),
            'entity_id'      => $this->getKey(),
            'action'         => $action,
            'old_values'     => $this->sanitiseForAudit($oldValues),
            'new_values'     => $this->sanitiseForAudit($newValues),
            'changed_fields' => $changedFields ?? null,
            'actor_type'     => $this->resolveActorType(),
            'actor_id'       => auth()->id(),
            'actor_name'     => auth()->user()?->name ?? 'System',
            'request_id'     => request()->header('X-Request-ID'),
            'ip_address'     => request()->ip(),
            'user_agent'     => request()->userAgent(),
            'source'         => $this->resolveSource(),
            'tenant_id'      => $this->tenant_id ?? null,
        ]);
    }

    protected function sanitiseForAudit(?array $values): ?array
    {
        if ($values === null) return null;

        // Remove sensitive fields
        $redacted = array_fill_keys($this->auditRedacted ?? [], '[REDACTED]');
        return array_merge(
            Arr::except($values, $this->auditExcluded ?? []),
            $redacted
        );
    }

    protected function resolveActorType(): string
    {
        if (app()->runningInConsole()) return 'system';
        if (auth()->guard('api')->check()) return 'api_client';
        return 'user';
    }

    protected function resolveSource(): string
    {
        if (app()->runningInConsole()) return 'console';
        if (request()->is('api/*')) return 'api';
        return 'web';
    }
}

class Order extends Model
{
    use Auditable;

    // Fields to exclude from audit (timestamps, cached values)
    protected array $auditExcluded = ['updated_at', 'search_vector'];

    // Fields to redact (show that they changed, but not the value)
    protected array $auditRedacted = ['payment_token'];
}

Generic audit trigger (PostgreSQL)

CREATE OR REPLACE FUNCTION audit_trigger_func()
RETURNS TRIGGER AS $$
DECLARE
    actor_id BIGINT;
    old_data JSONB;
    new_data JSONB;
BEGIN
    -- Try to get actor from session variable (set by application)
    actor_id := NULLIF(current_setting('app.current_user_id', true), '')::BIGINT;

    IF TG_OP = 'DELETE' THEN
        old_data := to_jsonb(OLD);
        new_data := NULL;
    ELSIF TG_OP = 'INSERT' THEN
        old_data := NULL;
        new_data := to_jsonb(NEW);
    ELSE
        old_data := to_jsonb(OLD);
        new_data := to_jsonb(NEW);
    END IF;

    INSERT INTO audit_log (
        entity_type, entity_id, action,
        old_values, new_values,
        actor_type, actor_id, actor_name,
        source
    ) VALUES (
        TG_TABLE_NAME,
        COALESCE((new_data->>'id')::BIGINT, (old_data->>'id')::BIGINT),
        LOWER(TG_OP),
        old_data, new_data,
        CASE WHEN actor_id IS NULL THEN 'system' ELSE 'user' END,
        actor_id,
        COALESCE(
            (SELECT name FROM users WHERE id = actor_id),
            'System'
        ),
        'database'
    );

    RETURN COALESCE(NEW, OLD);
END;
$$ LANGUAGE plpgsql;

-- Apply to tables
CREATE TRIGGER audit_orders
    AFTER INSERT OR UPDATE OR DELETE ON orders
    FOR EACH ROW EXECUTE FUNCTION audit_trigger_func();

// In middleware or service provider
DB::statement("SET LOCAL app.current_user_id = ?", [auth()->id()]);

SELECT
    recorded_at,
    action,
    actor_name,
    old_values,
    new_values,
    changed_fields,
    source,
    ip_address
FROM audit_log
WHERE entity_type = 'orders'
  AND entity_id = 12345
ORDER BY recorded_at DESC
LIMIT 100;

-- Uses idx_audit_entity, efficient even with billions of rows

SELECT
    recorded_at,
    entity_type,
    entity_id,
    action,
    changed_fields
FROM audit_log
WHERE actor_type = 'user'
  AND actor_id = 789
  AND recorded_at >= NOW() - INTERVAL '7 days'
ORDER BY recorded_at DESC;

-- Uses idx_audit_actor, efficient for user-centric queries

SELECT
    recorded_at,
    entity_id,
    actor_name,
    old_values->>'status' as old_status,
    new_values->>'status' as new_status
FROM audit_log
WHERE entity_type = 'orders'
  AND 'status' = ANY(changed_fields)
  AND recorded_date BETWEEN '2026-01-01' AND '2026-01-31'
ORDER BY recorded_at DESC;

-- Uses partition pruning on recorded_date, array operator on changed_fields

-- Get the state of order 12345 as of a specific timestamp
WITH ordered_changes AS (
    SELECT
        new_values,
        recorded_at,
        ROW_NUMBER() OVER (ORDER BY recorded_at DESC) as rn
    FROM audit_log
    WHERE entity_type = 'orders'
      AND entity_id = 12345
      AND recorded_at <= '2026-01-15 14:30:00+00'
      AND action != 'delete'
)
SELECT new_values
FROM ordered_changes
WHERE rn = 1;

-- Returns the complete record state as of that timestamp

-- Most frequently modified records
SELECT
    entity_type,
    entity_id,
    COUNT(*) as change_count
FROM audit_log
WHERE recorded_date >= CURRENT_DATE - INTERVAL '30 days'
GROUP BY entity_type, entity_id
HAVING COUNT(*) > 10
ORDER BY change_count DESC
LIMIT 100;

-- Change volume by hour (useful for capacity planning)
SELECT
    EXTRACT(HOUR FROM recorded_at) as hour,
    COUNT(*) as changes
FROM audit_log
WHERE recorded_date = CURRENT_DATE - 1
GROUP BY EXTRACT(HOUR FROM recorded_at)
ORDER BY hour;

-- Move partitions older than 90 days to archive tablespace
ALTER TABLE audit_log_2025_09 SET TABLESPACE archive_storage;

-- Export to Parquet and upload to S3 for cold storage
COPY (SELECT * FROM audit_log_2025_09)
TO PROGRAM 'aws s3 cp - s3://audit-archive/2025/09/audit.parquet'
WITH (FORMAT parquet);

-- Scheduled job to enforce retention policy
CREATE OR REPLACE PROCEDURE enforce_audit_retention()
LANGUAGE plpgsql AS $$
DECLARE
    partition_name TEXT;
    partition_date DATE;
BEGIN
    -- Find partitions older than retention period
    FOR partition_name, partition_date IN
        SELECT tablename,
               substring(tablename from 'audit_log_(\d{4}_\d{2})')::date
        FROM pg_tables
        WHERE tablename LIKE 'audit_log_%'
          AND schemaname = 'public'
    LOOP
        -- Archive partitions between 90 days and 2 years
        IF partition_date < CURRENT_DATE - INTERVAL '90 days'
           AND partition_date >= CURRENT_DATE - INTERVAL '2 years' THEN
            EXECUTE format('ALTER TABLE %I SET TABLESPACE archive_storage',
                          partition_name);
        END IF;

        -- Export and drop partitions older than 2 years
        IF partition_date < CURRENT_DATE - INTERVAL '2 years' THEN
            -- Export to S3 first (via external process)
            -- Then detach and drop
            EXECUTE format('ALTER TABLE audit_log DETACH PARTITION %I',
                          partition_name);
            EXECUTE format('DROP TABLE %I', partition_name);
        END IF;
    END LOOP;
END;
$$;

-- Trigger to prevent updates and deletes
CREATE OR REPLACE FUNCTION audit_immutable_trigger()
RETURNS TRIGGER AS $$
BEGIN
    RAISE EXCEPTION 'Audit records cannot be modified or deleted';
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER audit_immutable
    BEFORE UPDATE OR DELETE ON audit_log
    FOR EACH ROW EXECUTE FUNCTION audit_immutable_trigger();

-- Remove DELETE and UPDATE privileges
REVOKE UPDATE, DELETE ON audit_log FROM application_user;
GRANT INSERT, SELECT ON audit_log TO application_user;

ALTER TABLE audit_log ADD COLUMN record_hash BYTEA;
ALTER TABLE audit_log ADD COLUMN previous_hash BYTEA;

-- Compute hash on insert
CREATE OR REPLACE FUNCTION compute_audit_hash()
RETURNS TRIGGER AS $$
DECLARE
    prev_hash BYTEA;
BEGIN
    -- Get hash of previous record
    SELECT record_hash INTO prev_hash
    FROM audit_log
    ORDER BY id DESC LIMIT 1;

    NEW.previous_hash := prev_hash;
    NEW.record_hash := sha256(
        COALESCE(prev_hash, ''::bytea) ||
        NEW.recorded_at::text::bytea ||
        NEW.entity_type::bytea ||
        NEW.entity_id::text::bytea ||
        COALESCE(NEW.old_values::text, '')::bytea ||
        COALESCE(NEW.new_values::text, '')::bytea
    );

    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER audit_hash_chain
    BEFORE INSERT ON audit_log
    FOR EACH ROW EXECUTE FUNCTION compute_audit_hash();

-- Verify hash chain integrity
WITH chain_check AS (
    SELECT
        id,
        record_hash,
        previous_hash,
        LAG(record_hash) OVER (ORDER BY id) as expected_previous
    FROM audit_log
)
SELECT id
FROM chain_check
WHERE previous_hash IS DISTINCT FROM expected_previous
  AND id > 1;  -- First record has no previous

-- Empty result = chain intact

// Queue audit records for background processing
class AuditLogJob implements ShouldQueue
{
    public function __construct(
        private array $auditData
    ) {}

    public function handle(): void
    {
        AuditLog::create($this->auditData);
    }
}

// In the Auditable trait
protected function recordAudit(string $action): void
{
    dispatch(new AuditLogJob([
        'entity_type' => $this->getTable(),
        // ... other fields
    ]));
}

// Collect audit records during bulk operation
$auditBatch = [];
foreach ($records as $record) {
    $record->save();
    $auditBatch[] = [
        'entity_type' => $record->getTable(),
        'entity_id' => $record->getKey(),
        // ... other fields
    ];
}

// Single batch insert
AuditLog::insert($auditBatch);

// config/database.php
'connections' => [
    'mysql' => [...],  // Main application database
    'audit' => [       // Dedicated audit database
        'driver' => 'pgsql',
        'host' => env('AUDIT_DB_HOST'),
        // ...
    ],
],

// AuditLog model
class AuditLog extends Model
{
    protected $connection = 'audit';
}

-- Reindex during low-traffic periods
REINDEX TABLE CONCURRENTLY audit_log;

-- Or use pg_repack for zero-downtime index rebuilds
SELECT pg_repack.repack_table('audit_log');