Scalable Audit Trails: Leveraging PostgreSQL JSONB in Laravel

The Problem with Polymorphic Audit Logs

In B2B SaaS engineering at Smart Tech Devs, maintaining a robust audit trail—tracking exactly who changed what, and when—is often a hard compliance requirement. The standard Laravel approach involves using polymorphic relations to a central audits table, creating a new row for every single model update.

When your platform scales to millions of rows in core tables like invoices or users, this auditing strategy collapses. Your central audits table becomes massive, indexing it chokes your database, and prunning old data becomes an expensive locking operation. Furthermore, standard logging often captures the entire model state rather than just the changeset, wasting precious disk space. To build durable architecture, we must utilize the power of **PostgreSQL JSONB**.

Enter JSONB compact Changesets

PostgreSQL's JSONB data type allows us to store unstructured, indexed JSON data efficiently. Instead of creating massive polymorphic log tables, we append a compact "changeset" directly to a dedicated audit column on the main model, or into a sidecar table using JSONB.

Step 1: The Database Migration

Instead of a separate table, we add a jsonb column to the model we want to audit. We also ensure a GIN index is created on this column for fast lookups.

use Illuminate\Database\Migrations\Migration;
use Illuminate\Database\Schema\Blueprint;
use Illuminate\Support\Facades\Schema;

class AddAuditLogToInvoicesTable extends Migration
{
    public function up(): void
    {
        Schema::table('invoices', function (Blueprint $table) {
            // Store a compact array of changesets: [{user_id, changed_at, old: {}, new: {}}, ...]
            $table->jsonb('audit_log')->nullable();
        });

        // Add a GIN index for efficient querying inside the JSONB data
        DB::statement('CREATE INDEX invoices_audit_log_gin ON invoices USING GIN (audit_log);');
    }
}

Step 2: Efficient Auditing via Model Observers

We do not capture the whole model. We use an Eloquent Observer to hook into the updating event, calculate the strict changeset using Laravel's getDirty() and getOriginal() methods, and prepend the minimal JSON delta to our audit column.

namespace App\Observers;

use App\Models\Invoice;
use Illuminate\Support\Facades\Auth;

class InvoiceObserver
{
    /**
     * Handle the Invoice "updating" event.
     */
    public function updating(Invoice $invoice): void
    {
        // 1. Get ONLY the changed attributes and their original values
        $dirty = $invoice->getDirty();
        $original = array_intersect_key($invoice->getOriginal(), $dirty);

        if (empty($dirty)) {
            return; // No changes made
        }

        // 2. Create a compact changeset
        $changeset = [
            'user_id' => Auth::id() ?? 'system',
            'changed_at' => now()->toIso8601String(),
            'old' => $original,
            'new' => $dirty,
        ];

        // 3. Prepend the changeset to the existing JSONB array (using database-level concatenation)
        // We do this to avoid loading the entire audit history into memory just to append.
        $invoice->audit_log = DB::raw("jsonb_insert(COALESCE(audit_log, '[]'), '{0}', '" . json_encode($changeset) . "')");
    }
}

The Engineering ROI

Transitioning to JSONB changesets on the model itself (or a sidecar table) fundamentally upgrades your auditing infrastructure:

• Drastically Reduced Database Bloat: You are only storing the data that *changed*, rather than duplicating the entire model state multiple times.
• Flat Query Performance: Fetching a model's audit history is now a single, indexed lookup on that model's row, rather than a complex join on a multi-million-row polymorphic table.
• Simplified Pruning: If you need to purge data older than 2 years, you don't need expensive locked deletes on a central table. You can run simple, partitioned database updates or simply ignore older entries inside the JSONB structure.

Conclusion

Polymorphic audit logs are a liability at scale. By leveraging the flexibility and performance of PostgreSQL's JSONB inside your Laravel Observers, you build an immutable, highly efficient audit trail that safeguards data integrity without compromising system performance.