Auditing and Logging Database

The auditing and logging database is secondary database designed to store and manage two types of records:

1. Audit Logs - Tracking data changes for key database models
2. Error Logs - Capturing application errors, exceptions, and system events

Both systems use a separate database connection to isolate logs from the main application data, ensuring that logging operations do not impact application performance and can be managed independently.

Overview

Audit Logging System

The audit logging system tracks changes to database models, providing a structured way to monitor data modifications. It uses the OwenIt Auditing package with a semi-custom implementation that provides fault tolerance and uses a separate database connection.

Captures:

• Model creation, updates, and deletion events
• User attribution for all changes
• Before and after values for modified attributes
• Request metadata (URL, IP address, user agent)

Key Features:

• Fault-tolerant logging (failures don't break the application)
• Separate database connection for security and isolation
• Integration with Laravel Nova for viewing audit history
• Configurable per-model basis

The Laravel Auditing package documentation can be found here: Laravel Auditing

Error Logging System

The error logging system captures all Laravel log messages and system errors, storing them in the audit database for centralized monitoring and troubleshooting.

Captures:

• Manual log calls (Log::error(), Log::warning(), etc.)
• Uncaught exceptions and errors
• Framework-generated errors
• Failed job attempts
• Database query errors
• Performance metrics
• Security events

Key Features:

• Supports all PSR-3 log levels (DEBUG through EMERGENCY)
• Stores contextual data with each log entry
• Compatible with both Monolog 2.x and 3.x
• Automatic pruning of old records

Database

Database Connection

The audit system uses a separate connection (audits) to isolate audit logs from the main application data. This ensures that audit logs are stored securely and can be managed independently. The connection is defined in the database configuration file config/database.php.

Usage of the audit log database connection can be configured in the .env file:

# Audit Database Connection
DB_AUDIT_ENABLED=true
DB_AUDIT_CONNECTION=audits
DB_AUDIT_HOST=127.0.0.1
DB_AUDIT_PORT=3306
DB_AUDIT_DATABASE=lancaster_academy_audit
DB_AUDIT_USERNAME=root
DB_AUDIT_PASSWORD=

Environment Variables:

• DB_AUDIT_ENABLED: Enables or disables the audit logging feature.
• DB_AUDIT_CONNECTION: Specifies the database connection name for audit logs.

When disabled, the system will not attempt to create the database connection, or log any audits. This allows for different environments (e.g., local development) to run without the need for an auditing database.

Migrations

Migrations for the auditing database are located in the database/migrations/audits directory. This keeps the audit database migration separate from the main application migrations.

Migrations themselves will need to setup slightly differently to specify the correct database connection.

Example Migration:

<?php

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

class CreateErrorLogsTable extends Migration
{
    /**
     * Run the migrations.
     *
     * @return void
     */
    public function up()
    {
        $connection = config('audit.drivers.database.connection', config('database.default'));

        Schema::connection($connection)->create('error_logs', function (Blueprint $table) {
            $table->id();
            $table->timestamps();
        });
    }

    /**
     * Reverse the migrations.
     *
     * @return void
     */
    public function down()
    {
        $connection = config('audit.drivers.database.connection', config('database.default'));

        Schema::connection($connection)->dropIfExists('error_logs');
    }
}

The connection prefix will need to be specified using the Schema::connection() method to ensure the migration is run against the correct database connection.

Important: Migrations for the audit database will need to be run manually when deploying to different environments, as they are not included in the standard Laravel migration process. See the Commands section below for details on how to run the audit migrations.

Commands

Running Migrations

# Run Audit Migrations
php artisan audits:migrate

# Rollback Audit Migrations
php artisan audits:migrate --rollback

Running audit database migrations can be done using the custom application command. This command ensures that the migrations are run against the correct audit database connection and runs only the migrations defined in the database/migrations/audits folder.

It is not recommended to run the standard Laravel migration command with the --database option for the audit database, as this may lead to confusion or errors if not managed carefully.

Command is defined in app/Console/Commands/AuditsMigrate.php.

Pruning Audit Logs

# Prune Old Audit Logs
php artisan audits:prune --days=90 --force

The audits database table can grow significantly over time. To manage the size of the audit logs, a custom command is provided to prune old audit records. This command deletes audit records older than a specified number of days.

The command accepts the following options:

• --days=: The number of days to retain audit records (default is 90 days).
• --force: Actually delete the records, without this option the command will only simulate the deletion.

Note: This command is configured to run via the application scheduler in app/Console/Kernel.php on the production environment. Scheduled to run daily at 2:30 AM. deleting records older than 90 days.

Command is defined in app/Console/Commands/AuditsPrune.php.

Pruning Error Logs

# Prune Old Error Logs
php artisan audits:prune-logs --days=90 --force

The site_logs database table can grow significantly over time. To manage the size of the audit logs, a custom command is provided to prune old audit records. This command deletes audit records older than a specified number of days.

The command accepts the following options:

• --days=: The number of days to retain audit records (default is 90 days).
• --force: Actually delete the records, without this option the command will only simulate the deletion.

Note: This command is configured to run via the application scheduler in app/Console/Kernel.php on the production environment. Scheduled to run daily at 3:00 AM. deleting records older than 90 days.

Command is defined in app/Console/Commands/AuditsPruneLogs.php.

Auditing Implementation

To enable auditing for a model, the model must implement the OwenIt\Auditing\Contracts\Auditable contract and use the OwenIt\Auditing\Auditable trait.

Example Model Configuration:

<?php

namespace App\Models;

use Illuminate\Database\Eloquent\Model;
use OwenIt\Auditing\Contracts\Auditable;
use OwenIt\Auditing\Auditable as AuditableTrait;

class YourModel extends Model implements Auditable
{
    use AuditableTrait;
    
    // Rest of your model code...
}

This all the needed configuration to enable auditing for the model.

Additional configuration options can be found at Laravel Auditing - Auditable Configuration

Audit Model

The audit records are stored in the audits table, represented by the app/models/audit.php model. This is the default configuration provided by the OwenIt Auditing package.

Represents an audit record with the following attributes:

• user_type / user_id - The user who performed the action
• event - The type of event (created, updated, deleted, restored)
• auditable_type / auditable_id - The model being audited
• old_values - Previous attribute values (JSON)
• new_values - New attribute values (JSON)
• url - Request URL
• ip_address - User's IP address
• user_agent - Browser user agent
• tags - Additional metadata (JSON)

Relationships:

• user() - BelongsTo relationship with User model (switches to main database connection)

The Audit model is used in conjunction with App/Nova/Audit.php to provide an interface for viewing audit logs in the Nova admin panel.

Audit Log Drivers

Documentation on configuring audit log drivers can be found at Laravel Auditing - Audit Drivers.

The auditing database is configured to use the app/AuditDrivers/SafeDatabaseDriver.php driver. This is a custom global driver that facilitates the fault-tolerant logging of audit events to the database.

• Purpose: Prevents any audit logging failures from impacting the main application.
• Behavior: If the audit database is unreachable or an error occurs during logging, the system will silently fail without throwing exceptions in the main application.
• Use Case: Primarily for production environments where audit logging is important but should not interfere with application functionality. Ensures that if the audit database is down, the main application continues to operate normally.

Logging Implementation

The error logging system captures all Laravel log messages and stores them in the audit database. This includes:

• Manual log calls (Log::error(), Log::warning(), etc.)
• Uncaught exceptions and errors
• Framework-generated errors
• Failed job attempts
• Database query errors

Logging

Automatic Exception Logging

All uncaught exceptions are automatically logged through Laravel's exception handler and will appear in the site_logs table.

Configuration

The error logging system uses a custom Monolog handler that writes to the audit database. This is configured in the config/logging.php file:

'channels' => [
    'stack' => [
        'driver' => 'stack',
        'channels' => ['single', 'audits_database'],
        'ignore_exceptions' => false,
    ],
   
   //... 
    
   'audits_database' => [
        'driver' => 'custom',
        'via' => App\Logging\AuditsDBCreateLogger::class,
        'level' => 'debug',
    ]
],

Configuration Details:

• The stack channel includes both the default single file logger and the custom audits_database logger.
• The audits_database channel uses a custom driver implementation

Custom Handler Implementation

The error logging system consists of two main components:

1. AuditsDBCreateLogger (app/Logging/AuditsDBCreateLogger.php)

   • Responsible for creating a Monolog logger instance with the custom handler.

2. AuditsDBHandler (app/Logging/AuditsDBHandler.php)

   • This custom Monolog handler writes log records to the audit database. It extends AbstractProcessingHandler and implements the write() method.