# Laravel DB Auditor

Laravel DB Auditor is a comprehensive database auditing package for Laravel applications that supports MySQL, SQLite, and PostgreSQL databases. It provides tools to analyze your database structure, verify naming conventions and standards compliance, manage database constraints (primary keys, foreign keys, indexes, and unique keys), and track migration history. The package helps development teams maintain clean, consistent database schemas by identifying tables and columns that don't follow best practices.

The core functionality includes five main features: database summary providing overview statistics, standard checking to validate naming conventions and structure, constraint management to view and add database constraints, migration tracking to monitor database changes over time, and performance parameter analysis for MySQL databases. All features are accessible via both CLI commands and a web-based interface, making it flexible for different workflows and preferences.

## Installation

Install the package as a dev dependency using Composer.

```bash
composer require --dev vcian/laravel-db-auditor
```

## CLI Commands

### db:audit - Main Audit Menu

The main entry point command that provides an interactive menu to select between all available auditing features.

```bash
# Run the main audit command - presents an interactive menu
php artisan db:audit

# The command will display options based on your database driver:
# - STANDARD: Check database naming standards
# - CONSTRAINT: View and manage table constraints
# - SUMMARY: Get database overview
# - TRACK: Track migration history
# - CHECK PERFORMANCE PARAMETER (MySQL only): Analyze performance settings
```

### db:summary - Database Overview

Displays a summary of your database including name, size, table count, version, and character set information.

```bash
# Get database summary
php artisan db:summary

# Example output for MySQL:
# +---------------+----------+-------------+------------+---------------+
# | Database Name | Size (MB)| Table Count | DB Version | Character Set |
# +---------------+----------+-------------+------------+---------------+
# | my_database   | 45.23    | 28          | 8.0.32     | utf8mb4       |
# +---------------+----------+-------------+------------+---------------+

# Example output for SQLite:
# +------------------+---------------+-------------+--------------+--------------------+
# | Database Version | Database Name | Table Count | Busy Timeout | Default Cache Size |
# +------------------+---------------+-------------+--------------+--------------------+
# | 1                | database.db   | 15          | 5000         | 2000 KB            |
# +------------------+---------------+-------------+--------------+--------------------+
```

### db:standard - Check Database Standards

Analyzes all tables and columns for naming convention compliance, validates plural table names, checks name lengths, and identifies potential issues.

```bash
# Check standards for all tables
php artisan db:standard

# Check standards for a specific table
php artisan db:standard --table=users

# The command checks for:
# - Snake_case naming convention
# - Plural table names (e.g., "users" not "user")
# - Maximum name length (64 characters)
# - Alphabetic character sets (no special characters)
# - VARCHAR fields with size <= 255 (suggests using CHAR for PostgreSQL)

# Example output shows tables with status indicators:
# +-----------------+--------+--------+
# | Table Name      | Status | Size   |
# +-----------------+--------+--------+
# | users           | ✓      | 1.2 MB |
# | order_items     | ✓      | 0.5 MB |
# | UserPreference  | ✗      | 0.1 MB | <- Not following snake_case
# +-----------------+--------+--------+
```

### db:constraint - Constraint Management

View existing constraints and interactively add primary keys, foreign keys, indexes, and unique constraints to tables.

```bash
# Open constraint management interface
php artisan db:constraint

# The command displays:
# 1. List of all tables (excluding configured skip tables)
# 2. Select a table to view its constraints
# 3. Shows existing PRIMARY, FOREIGN, UNIQUE, and INDEX constraints
# 4. Option to add new constraints interactively

# Supported constraints:
# - PRIMARY: Add primary key to integer fields
# - FOREIGN: Add foreign key linking to another table
# - UNIQUE: Add unique constraint (validates no duplicates exist)
# - INDEX: Add index for query optimization

# Note: SQLite doesn't support adding PRIMARY/FOREIGN keys to existing tables
# Note: PostgreSQL constraint addition is not yet supported (view only)
```

### db:track - Migration Tracking

Track database migration files with details about creation date, table affected, fields defined, migration status, and author information.

```bash
# View all migration tracking information
php artisan db:track

# Filter by specific table name
php artisan db:track --table=users

# Filter by action type (Create or Update)
php artisan db:track --action=create
php artisan db:track --action=c       # Short form

# Filter by migration status (Migrated or Pending)
php artisan db:track --status=pending
php artisan db:track --status=p       # Short form
php artisan db:track --status=migrated
php artisan db:track --status=m       # Short form

# Example output:
# +------------+--------------------------------+--------+------------------+--------+----------+------------+
# | Date       | File Name                      | Table  | Fields           | Action | Status   | Created By |
# +------------+--------------------------------+--------+------------------+--------+----------+------------+
# | 2024-01-15 | 2024_01_15_000000_create_users | users  | id, name, email  | Create | Migrated | john_doe   |
# | 2024-01-20 | 2024_01_20_000000_add_role     | users  | role_id          | Update | Pending  | jane_smith |
# +------------+--------------------------------+--------+------------------+--------+----------+------------+
```

### db:check-performance-parameter - MySQL Performance Analysis

Analyzes MySQL database performance parameters and provides optimization suggestions (MySQL only).

```bash
# Generate performance report
php artisan db:check-performance-parameter

# Example output:
# +------------------------+---------------+--------------------------------------------------------+
# | Parameter              | Current Value | Suggestion                                             |
# +------------------------+---------------+--------------------------------------------------------+
# | innodb_buffer_pool_size| 128 MB        | Consider increasing to at least 1GB for better perf   |
# | query_cache_size       | 0             | Query cache disabled. Consider enabling for read-heavy|
# | max_connections        | 151           | Current value seems appropriate                        |
# | innodb_log_file_size   | 48 MB         | Consider increasing for better performance             |
# +------------------------+---------------+--------------------------------------------------------+

# Parameters analyzed:
# - innodb_buffer_pool_size: Should be 1GB+ for production
# - query_cache_size: Enable for read-heavy workloads
# - max_connections: 100-1000 range recommended
# - innodb_log_file_size: 128MB+ recommended
```

## Configuration

### Publishing Configuration

Publish the configuration file to customize skip tables and other settings.

```bash
# Publish configuration file
php artisan vendor:publish --tag=config

# Select "db-auditor" from the list

# This creates config/db-auditor.php with:
return [
    'skip_tables' => [
        'cache',
        'sqlite_sequence',
        'migrations',
        'migrations_history',
        'sessions',
        'password_resets',
        'failed_jobs',
        'jobs',
        'queue_job',
        'queue_failed_jobs',
    ]
];
```

### Adding Tables to Skip List

Configure tables to exclude from auditing by modifying the published configuration.

```php
// config/db-auditor.php
return [
    'skip_tables' => [
        // Default Laravel tables
        'cache',
        'migrations',
        'sessions',
        'password_resets',
        'failed_jobs',
        'jobs',

        // Add your custom tables to skip
        'telescope_entries',
        'telescope_monitoring',
        'audit_logs',
        'temp_data',
    ]
];
```

## Web Interface

### Accessing the Web UI

The package provides a web-based interface for visual database auditing.

```bash
# Publish public assets for web UI
php artisan vendor:publish --tag=public

# Select "db-auditor" from the list

# Access the web interface at:
# http://your-app.test/laravel-db-auditor

# The web UI provides:
# - Visual table listing with status indicators
# - Interactive standard checking with detailed reports
# - Constraint viewing and management
# - Click-to-add constraint functionality for MySQL
```

## REST API Endpoints

### Get Audit Data

Retrieve audit data for all tables via the API.

```bash
# Get all tables audit status
curl -X GET "http://your-app.test/api/getAudit"

# Response:
{
  "data": [
    {
      "name": "users",
      "status": "<img src='check.svg'/>",
      "size": "1.23 MB"
    },
    {
      "name": "posts",
      "status": "<img src='close.svg'/>",
      "size": "0.45 MB"
    }
  ]
}
```

### Get Table Standard Details

Retrieve detailed standard compliance information for a specific table.

```bash
# Get table-specific standard report
curl -X GET "http://your-app.test/api/getTableData/users"

# Response:
{
  "data": {
    "table": "users",
    "table_comment": [],
    "fields": {
      "id": {
        "datatype": {"data_type": "bigint", "size": null}
      },
      "UserName": {
        "0": "Name should follow snake_case convention",
        "datatype": {"data_type": "varchar", "size": "255"}
      }
    }
  }
}
```

### Get Table Constraints

Retrieve constraint information for a specific table.

```bash
# Get constraint details for a table
curl -X GET "http://your-app.test/api/gettableconstraint/users"

# Response:
{
  "data": [
    {
      "column": "id",
      "primaryKey": "<img src='green-key.svg'/>",
      "indexing": "-",
      "uniqueKey": "-",
      "foreignKey": "-"
    },
    {
      "column": "email",
      "primaryKey": "-",
      "indexing": "-",
      "uniqueKey": "<img src='gray-key.svg'/>",
      "foreignKey": "-"
    }
  ]
}
```

### Add Constraint via API

Add a constraint to a table column programmatically.

```bash
# Add a constraint (PRIMARY, INDEX, UNIQUE)
curl -X POST "http://your-app.test/api/change-constraint" \
  -H "Content-Type: application/json" \
  -d '{
    "table_name": "posts",
    "colum_name": "slug",
    "constraint": "UNIQUE"
  }'

# Response: true (on success)

# Constraint values: PRIMARY, INDEX, UNIQUE, FOREIGN
```

### Add Foreign Key Constraint

Add a foreign key constraint linking two tables.

```bash
# Get available tables for foreign key reference
curl -X GET "http://your-app.test/api/foreign-key-table"

# Response: ["users", "posts", "categories", "tags"]

# Get fields from a reference table
curl -X GET "http://your-app.test/api/foreign-key-field/users"

# Response: [{"COLUMN_NAME": "id"}, {"COLUMN_NAME": "email"}]

# Add foreign key constraint
curl -X POST "http://your-app.test/api/add-foreign-constraint" \
  -H "Content-Type: application/json" \
  -d '{
    "table_name": "posts",
    "select_field": "user_id",
    "reference_table": "users",
    "reference_field": "id"
  }'

# Response: true (on success)
# Error responses:
# - "Foreign table and selected table should not be same"
# - "Foreign key not applied due to datatype mismatch"
```

## Programmatic Usage

### Using Traits in Custom Code

The package traits can be used directly in your application code for custom auditing logic.

```php
<?php

namespace App\Services;

use Vcian\LaravelDBAuditor\Traits\Rules;
use Vcian\LaravelDBAuditor\Traits\DBFunctions;
use Vcian\LaravelDBAuditor\Traits\Audit;

class DatabaseAuditService
{
    use Rules, DBFunctions, Audit;

    public function auditAllTables(): array
    {
        // Get all tables with their standard compliance status
        $results = $this->allTablesRules();

        // Returns array of:
        // [
        //     ['name' => 'users', 'status' => true, 'size' => '1.23'],
        //     ['name' => 'posts', 'status' => false, 'size' => '0.45'],
        // ]

        return $results;
    }

    public function getTableDetails(string $tableName): array
    {
        // Check if table exists
        if (!$this->checkTableExist($tableName)) {
            return ['error' => 'Table not found'];
        }

        // Get comprehensive table audit
        $tableRules = $this->tableRules($tableName);

        // Get table fields
        $fields = $this->getFields($tableName);

        // Get table size
        $size = $this->getTableSize($tableName);

        // Get field details with data types
        $fieldDetails = $this->getFieldsDetails($tableName);

        return [
            'rules' => $tableRules,
            'fields' => $fields,
            'size' => $size,
            'details' => $fieldDetails,
        ];
    }

    public function addTableConstraint(
        string $table,
        string $field,
        string $constraint,
        ?string $refTable = null,
        ?string $refField = null
    ): bool {
        // Add constraint programmatically
        // Constraint types: PRIMARY, FOREIGN, UNIQUE, INDEX
        return $this->addConstraint($table, $field, $constraint, $refTable, $refField);
    }

    public function getMySQLPerformanceReport(): array
    {
        // Generate MySQL performance analysis
        return $this->generatePerformanceReport();

        // Returns:
        // [
        //     'innodb_buffer_pool_size' => [
        //         'current' => '134217728',
        //         'suggestion' => 'Consider increasing to at least 1GB'
        //     ],
        //     'max_connections' => [
        //         'current' => '151',
        //         'suggestion' => 'Current value seems appropriate'
        //     ],
        // ]
    }
}
```

### Helper Functions

The package provides global helper functions for database information.

```php
<?php

// Get the current database connection driver
$driver = connection_driver();
// Returns: 'mysql', 'sqlite', or 'pgsql'

// Get the current database name
$dbName = database_name();
// Returns: 'my_application_db'

// Get SQLite cache size (SQLite only)
$cacheSize = get_sqlite_database_cache_size();
// Returns: '2000 KB' or '500 Pages'

// Usage example in application code
if (connection_driver() === 'mysql') {
    // MySQL-specific logic
    $performanceReport = app(DatabaseAuditService::class)->getMySQLPerformanceReport();
} elseif (connection_driver() === 'sqlite') {
    // SQLite-specific logic
    $cacheInfo = get_sqlite_database_cache_size();
}
```

## Summary

Laravel DB Auditor is ideal for development teams who want to maintain consistent database standards across their Laravel applications. The primary use cases include automated CI/CD pipeline checks for database standard compliance, interactive development-time auditing of new tables and migrations, constraint management without writing raw SQL migrations, and performance optimization for MySQL production databases. The package integrates seamlessly with Laravel's artisan command system and can be used both interactively during development and programmatically in custom audit workflows.

For integration, the package auto-registers its service provider via Composer, making all commands immediately available after installation. The web interface provides a visual dashboard for non-technical team members to review database structure, while the API endpoints enable integration with custom admin panels or automated testing frameworks. Teams can extend the package's functionality by using its traits directly in custom services, enabling tailored audit reports and automated constraint management based on specific business rules.