Track database changes in Laravel using database triggers.

This package allows you to track changes in your database using database triggers. Currently only MySQL is supported.

Note This package is designed to track database changes that occur outside of Laravel. However, for changes originating within a Laravel application, other solutions like owen-it/laravel-auditing may better suit your requirements.

Installation

You can install the package via composer:

composer require esign/laravel-database-auditing

The package will automatically register a service provider.

This package comes with a migration to store your database changes. You can publish the migration file:

php artisan vendor:publish --provider="Esign\DatabaseAuditing\DatabaseAuditingServiceProvider" --tag="migrations"

Running this command will publish the following migration:

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

return new class extends Migration
{
    public function up(): void
    {
        Schema::create('audits', function (Blueprint $table) {
            $table->id();
            $table->string('event');
            $table->morphs('auditable');
            $table->json('old_data')->nullable();
            $table->json('new_data')->nullable();
            $table->timestamp('created_at')->useCurrent();
            $table->timestamp('updated_at')->useCurrent()->useCurrentOnUpdate();
        });
    }

    public function down(): void
    {
        Schema::dropIfExists('audits');
    }
};

Next up, you can optionally publish the configuration file:

php artisan vendor:publish --provider="Esign\DatabaseAuditing\DatabaseAuditingServiceProvider" --tag="config"

The config file will be published as config/database-auditing.php with the following contents:

return [
    /**
     * Specifies the model used by the package to retrieve audits.
     */
    'model' => Esign\DatabaseAuditing\Models\Audit::class,
];

Usage

Creating database triggers

To track database changes, use the php artisan make:audit-trigger command. This will generate a migration file with the necessary trigger configuration:

use Esign\DatabaseTrigger\DatabaseTrigger;
use Esign\DatabaseTrigger\Enums\TriggerEvent;
use Esign\DatabaseTrigger\Enums\TriggerTiming;
use Esign\DatabaseTrigger\Facades\Schema;
use Illuminate\Database\Migrations\Migration;

return new class extends Migration
{
    public function up(): void
    {
        Schema::createTrigger('audit_after_posts_insert', function (DatabaseTrigger $trigger) {
            $trigger->on('posts');
            $trigger->timing(TriggerTiming::AFTER);
            $trigger->event(TriggerEvent::INSERT);
            $trigger->statement("
                insert into audits (
                    event,
                    auditable_type,
                    auditable_id,
                    old_data,
                    new_data
                ) values (
                    'insert',
                    'post',
                    NEW.id,
                    NULL,
                    JSON_OBJECT('title', NEW.title, 'slug', NEW.slug)
                );
            ");
        });
    }

    public function down(): void
    {
        Schema::dropTriggerIfExists('audit_after_posts_insert');
    }
};

By default, a trigger name will be automatically assigned based on the provided input. However, you can specify a different name by passing it as the first argument:

php artisan make:audit-trigger my_trigger

Retrieving tracked changes

After running the trigger migration, any modifications made to the associated table will be automatically monitored and stored in the audits table.

Here is a representation of how audits are stored in the audits table based on different trigger events:

id	event	auditable_type	auditable_id	old_data	new_data
1	insert	post	1	NULL	{"title": "My Post"}
2	update	post	1	{"title": "My Post"}	{"title": "My Updated Post"}
3	delete	post	1	{"title": "My Post"}	NULL

To retrieve the recorded changes in your Laravel project, you can utilize the Esign\DatabaseAuditing\Models\Audit model provided by the package. Here's an example:

use Esign\DatabaseAuditing\Models\Audit;

$audits = Audit::query()->get();

To determine if any data changes occurred in an audit, you can utilize the hasDataChanges() method available on the Audit model. Here's how you can use it:

use Esign\DatabaseAuditing\Models\Audit;

$latestAudit = Audit::latest()->first();
$latestAudit->hasDataChanges();
$latestAudit->hasDataChanges('slug');

The hasDataChanges() method returns a boolean value indicating whether any changes were made. If you pass a specific attribute name as an argument, it will check for changes in that particular attribute only.

To retrieve audits based on specific trigger events, you can use the event() scope provided by the Audit model. Here's an example:

use Esign\DatabaseAuditing\Models\Audit;
use Esign\DatabaseTrigger\Enums\TriggerEvent;

Audit::event(TriggerEvent::UPDATE)->first();

Tracking changes in Eloquent models

To track changes related to your Eloquent model, apply the Esign\DatabaseAuditing\Concerns\HasAudits trait to the respective model. For instance:

use Esign\DatabaseAuditing\Concerns\HasAudits;
use Illuminate\Database\Eloquent\Model;

class Post extends Model
{
    use HasAudits;
}

This will enable an audits relationship on the model, allowing you to access the tracked changes. For example:

$post = Post::first();
$latestAudit = $post->audits()->latest()->first();

Feel free to explore the Esign\DatabaseAuditing\Models\Audit model for more functionality related to tracking and retrieving changes.

Testing

composer test

License

The MIT License (MIT). Please see License File for more information.