Audit Database Schema Design

Audit Database Schema Design.

Installation

You can install the package via composer:

composer require cleaniquecoders/db-schema-auditor --dev

You can publish and run the migrations with:

php artisan vendor:publish --tag="db-schema-auditor-migrations"
php artisan migrate

You can publish the config file with:

php artisan vendor:publish --tag="db-schema-auditor-config"

Usage

# Basic audit - displays summary in console
php artisan db:audit

# Audit with model relationship analysis
php artisan db:audit --models

# Output as JSON to default path (/database/audit/)
php artisan db:audit --format=json

# Output as JSON to specified directory
php artisan db:audit --format=json --path=/storage/audit

# Output as Markdown
php artisan db:audit --format=markdown

# Output as CSV
php artisan db:audit --format=csv

# Output as HTML report
php artisan db:audit --format=html

# Generate fix migrations automatically
php artisan db:audit --generate-fixes

# Save results to database for tracking
php artisan db:audit --save-database

# Audit specific database connection
php artisan db:audit --connection=secondary

# Full audit with all options
php artisan db:audit --models --generate-fixes --save-database --format=markdown

Supported Database Types

This package supports all major database types:

• MySQL / MariaDB - Full support for indexes, foreign keys, and constraints
• PostgreSQL - Complete support including partial indexes and advanced features
• Microsoft SQL Server - Full support for SQL Server specific features
• Oracle - Support for Oracle-specific indexes and constraints
• SQLite - Basic support for SQLite databases

Output Formats

• console (default) - Colored terminal output with recommendations
• json - Machine-readable JSON format for integration
• markdown - GitHub-flavored markdown for documentation
• csv - Spreadsheet-compatible format for analysis
• html - Styled HTML report for sharing

What Gets Audited

Database Structure:

• Missing indexes on foreign key columns
• Missing indexes on commonly queried columns
• Missing unique constraints
• Missing foreign key constraints
• Orphaned records in relationships
• Suspicious column patterns

Model Relationships (with --models flag):

• Missing inverse relationships
• Relationship naming inconsistencies
• Missing model relationships for database foreign keys
• Relationship method issues

Generated Outputs

Migration Files (with --generate-fixes):

• Index creation migrations
• Unique constraint migrations
• Foreign key suggestion migrations (commented for review)
• Orphaned record cleanup scripts

Database Storage (with --save-database):

• Complete audit history
• Issue tracking and resolution status
• Model analysis results
• Performance trending over time

Testing

composer test

Changelog

Please see CHANGELOG for more information on what has changed recently.

Contributing

Please see CONTRIBUTING for details.

Security Vulnerabilities

Please review our security policy on how to report security vulnerabilities.

Credits

• Nasrul Hazim Bin Mohamad
• All Contributors

License

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