README

A comprehensive Laravel package that provides standardized API responses, dynamic API controllers, query helpers, audit logging, optional permission integration, flexible authentication via Laravel guards, and OpenAPI specification generation for ERP SaaS applications.

🚀 Quick Start

# 1. Install the package
composer require mylekha/record-api

# 2. Generate/publish configs (record/audit/record-api)
php artisan record-api:setup

# 3. Publish package migrations + run migrations
php artisan vendor:publish --tag=record-api-migrations
php artisan migrate

# 4. Define rate limiters in your AppServiceProvider (see below)

# 5. Configure at least 1 table in config/record.php (see below) or scaffold a table config via the record command

# 6. Test a public read endpoint
curl -X GET http://your-app.test/api/v1/users

✨ Features

🔄 Dynamic API Controller: Full CRUD operations for any database table with advanced filtering
🚀 Upsert Support: Atomic update-or-create operations with configurable matching logic
📊 Standardized API Responses: Consistent JSON response format across your application
🔍 QueryHelpers Trait: Powerful trait for advanced query filtering and manipulation
📝 Audit Logging: Comprehensive audit trail for all data changes with queue-based processing
🔐 Permission System: Optional Spatie Laravel Permission integration for role-based access control
🔑 Authentication Driver Agnostic: Works with any Laravel auth guard (JWT, Sanctum, Passport, etc.)
📚 OpenAPI Spec Generation: CLI command to generate API documentation
🎯 Request ID Middleware: Automatic request tracking for debugging and monitoring
⚡ Performance Optimized: Query caching and lazy loading
🧹 Audit Log Cleanup: CLI command for cleaning old audit logs based on retention policy
🏢 Multi-Tenant Ready: Built-in support for tenant isolation
🔧 Configuration Publishing: Easy setup with sensible defaults

📚 Documentation

API Documentation: Detailed guide on endpoints, request/response formats, and bulk operations.
Performance & Scalability: Benchmark results and optimization strategies.
Audit Interface: How to implement custom audit logging.
Legacy Cursor Pagination: Background on the removed cursor-based paginator.
Use Cases: Why use this for SaaS ERP or E-commerce.

📋 Requirements

PHP: 8.2 or higher
Laravel: 12.x
Database: MySQL 8.0+, PostgreSQL 13+, or SQLite 3.8+
Extensions: BCMath, Ctype, JSON, Mbstring, OpenSSL, PDO, Tokenizer, XML

📦 Dependencies and Optional Integrations

Core dependency installed with the package:

Carbon (^2.0 or ^3.0) - Date manipulation library

Optional integrations you can install in your application:

Spatie Laravel Permission (^6.21) - Role and permission management
PHP Open Source Saver JWT Auth (^2.8.2) - JSON Web Token authentication (install/configure in your app)

📥 Installation & Setup

Step 1: Install the Package

Option A: Via Composer (Recommended for Production)

composer require mylekha/record-api

Option B: Local Development (VCS Repository)

Add this to your project's composer.json:

{
    "repositories": [
        {
            "type": "vcs",
            "url": "https://github.com/mylekha/laravel-record-api.git"
        }
    ],
    "require": {
        "mylekha/record-api": "*"
    }
}

Then run:

composer update mylekha/record-api -W

Step 2: Publish/Generate Configuration Files

php artisan record-api:setup

This command publishes package configs (tag: record-api-config) and creates missing app config files:

config/record.php - Database table configurations and relationships
config/audit.php - Audit logging settings
config/record-api.php - Package settings (auth guard, OpenAPI output)

To overwrite existing generated configs, run:

php artisan record-api:setup --force

Step 3: Environment Configuration

Add these environment variables to your .env file:

# Audit logging
AUDIT_LOG_ENABLED=true
AUDIT_LOG_RETENTION_DAYS=365

# Which Laravel auth guard the package uses
RECORD_API_AUTH_GUARD=api

Step 4: Publish Migrations and Run Migrations

php artisan vendor:publish --tag=record-api-migrations
php artisan migrate

Step 5: Configure Rate Limiters

The package routes use throttle:api-reads, throttle:api-writes, and throttle:api-functions. Define them in your app (example in app/Providers/AppServiceProvider.php):

use Illuminate\Cache\RateLimiting\Limit;
use Illuminate\Http\Request;
use Illuminate\Support\Facades\RateLimiter;

public function boot(): void
{
    RateLimiter::for('api-reads', function (Request $request): Limit {
        $key = $request->user()?->getAuthIdentifier() ?? $request->ip();
        return Limit::perMinute(200)->by((string) $key);
    });

    RateLimiter::for('api-writes', function (Request $request): Limit {
        $key = $request->user()?->getAuthIdentifier() ?? $request->ip();
        return Limit::perMinute(100)->by((string) $key);
    });

    RateLimiter::for('api-functions', function (Request $request): Limit {
        $key = $request->user()?->getAuthIdentifier() ?? $request->ip();
        return Limit::perMinute(100)->by((string) $key);
    });
}

Step 6: Validate Installation

Verify your installation is working correctly:

# Validate the complete setup
php artisan record-api:validate

# Run with verbose output for detailed information
php artisan record-api:validate --verbose

# Auto-fix common issues
php artisan record-api:validate --fix

Step 7: Configure Database Tables

Edit config/record.php to configure your database tables for the dynamic API. See the examples directory for a complete configuration example:

use Mylekha\RecordApi\Types\RecordTableType;
use Mylekha\RecordApi\Types\RecordHasManyType;
use Mylekha\RecordApi\Types\RecordBelongsToType;
use Mylekha\RecordApi\Types\RecordTableTriggerType;
return [
    'api_prefix' => 'api/v1',

    'tables' => [
        'users' => new RecordTableType(
            pmsName: 'user',
            table: 'users',
            isAuthRead: true,
            isAuthWrite: true,
            relationships: [
                'posts' => new RecordHasManyType(
                    table: 'posts',
                    foreignKey: 'user_id',
                    localKey: 'id',
                ),
            ],
            softDeletes: false,
            hasTenantId: false,
            createValidator: function (\Illuminate\Http\Request $request, ?int $id = null): \Illuminate\Contracts\Validation\Validator {
                return \Illuminate\Support\Facades\Validator::make($request->all(), [
                    'name' => 'required|string|max:255',
                    'email' => 'required|email',
                ]);
            },
            updateValidator: function (\Illuminate\Http\Request $request, ?int $id = null): \Illuminate\Contracts\Validation\Validator {
                return \Illuminate\Support\Facades\Validator::make($request->all(), [
                    'name' => 'sometimes|required|string|max:255',
                ]);
            },
            deleteValidator: function (\Illuminate\Http\Request $request, ?int $id = null): \Illuminate\Contracts\Validation\Validator {
                return \Illuminate\Support\Facades\Validator::make(['id' => $id], [
                    'id' => 'required|integer',
                ]);
            },
            beforeCreate: new RecordTableTriggerType(
                class: \App\Record\Triggers\UserTriggers::class,
                functionName: 'beforeCreate',
            ),
            afterCreate: new RecordTableTriggerType(
                class: \App\Record\Triggers\UserTriggers::class,
                functionName: 'afterCreate',
            ),
            beforeUpdate: new RecordTableTriggerType(
                class: \App\Record\Triggers\UserTriggers::class,
                functionName: 'beforeUpdate',
            ),
            afterUpdate: new RecordTableTriggerType(
                class: \App\Record\Triggers\UserTriggers::class,
                functionName: 'afterUpdate',
            ),
            beforeDelete: new RecordTableTriggerType(
                class: \App\Record\Triggers\UserTriggers::class,
                functionName: 'beforeDelete',
            ),
            afterDelete: new RecordTableTriggerType(
                class: \App\Record\Triggers\UserTriggers::class,
                functionName: 'afterDelete',
            ),
            beforeRead: new RecordTableTriggerType(
                class: \App\Record\Triggers\UserTriggers::class,
                functionName: 'beforeRead',
            ),
            afterRead: new RecordTableTriggerType(
                class: \App\Record\Triggers\UserTriggers::class,
                functionName: 'afterRead',
            ),
        ),
    ],
];

The package routes are loaded automatically by Mylekha\RecordApi\RecordApiServiceProvider using this prefix. Record endpoints authorize per-table using isAuthRead / isAuthWrite and permissions; public remains as legacy compatibility and is derived from auth flags.

You can override permission evaluation by setting record.authorization in config/record.php:

'authorization' => \App\Security\RecordAuthorization::class,

Handler contract:

class-string: container-resolved and must expose handle($user, $permission, $table, $action): bool
callable/closure: invoked as fn($user, string $permission, string $table, string $action): bool
null: fallback to default Gate::forUser($user)->allows($permission)

Config-Driven Middleware Map (Client Use Case)

You can apply different middleware stacks per route action and per table without editing package routes.

// config/record.php
'middleware_map' => [
    'default' => [
        '*' => [],
        'read' => [],
        'write' => ['auth:sanctum'],
        'function' => ['auth:sanctum'],
    ],
    'tables' => [
        // Public query routes
        'customers' => [
            'read' => [],
        ],
        // Auth + subscription routes
        'bills' => [
            'write' => ['auth:sanctum', 'subscribed'],
            'table_function' => ['auth:sanctum', 'subscribed'],
        ],
    ],
],

Action names available in the middleware map:

list, show
create, update, delete, restore, force_delete, upsert
bulk, bulk_create, bulk_update, bulk_delete, bulk_upsert
table_function, global_function
grouped keys: read, write, function, and wildcard *

Request Context for Hooks and Custom Audit

Request context is built-in and always available for hooks and custom audit callbacks. Tenant resolution keeps backward compatibility:

first from request attribute resolved_tenant_id (or record_context.tenant_id)
then fallback to tenant header (X-Tenant-ID by default)

Client middleware can set tenant before CRUD/controller logic:

public function handle($request, \Closure $next)
{
    $request->attributes->set('resolved_tenant_id', $request->user()?->tenant_id);

    return $next($request);
}

Or set directly into request context:

$request->attributes->set('record_context', [
    'tenant_id' => $request->user()?->tenant_id,
]);

Use context inside trigger:

public static function beforeCreate(\Illuminate\Http\Request $request, string $table, array $context): array
{
    $requestContext = $context['request_context'] ?? $request->attributes->get('record_context', []);
    $tenantId = $requestContext['tenant_id'] ?? null;
    $userId = $requestContext['user']['id'] ?? null;

    $payload = $request->all();
    $payload['tenant_id'] = $tenantId;
    $payload['created_by'] = $userId;
    $request->replace($payload);

    return [$request, $table, $context];
}

Alternatively, you can keep config/record.php focused on global options and define per-table configurations under config/records/tables using the Artisan helper:

Table-Level Custom Audit Logger

You can override the default audit logging behavior for a specific table by providing a customAuditLog callback on the RecordTableType configuration. When set, this callback is invoked instead of the built-in AuditLogService::insertAuditLog calls for that table.

Example table config (config/records/tables/invoices.php):

use Mylekha\RecordApi\Types\RecordTableType;

return new RecordTableType(
    table: 'invoices',
    pmsName: 'invoice',
    isAuthRead: false,
    isAuthWrite: false,
    // String callback formats are supported...
    // customAuditLog: \App\Http\Controllers\InvoiceAuditLogger::class . '@handle',

    // ...and so is native PHP callable array syntax
    customAuditLog: [\App\Http\Controllers\InvoiceAuditLogger::class, 'handle'],
);

Example custom audit handler:

namespace App\Http\Controllers;

use Mylekha\RecordApi\Enums\AuditLogEventEnum;
use Mylekha\RecordApi\Services\AuditLogService;

class InvoiceAuditLogger
{
    public function handle(
        AuditLogEventEnum $event,
        string $entityClass,
        array $auditData,
        mixed $tenantId,
        array $context
    ): void {
        // Optionally transform or enrich $auditData here

        // Delegate to the core audit logic with your customized payload
        AuditLogService::handleAuditDataEntry(
            event: $event,
            entityName: AuditLogService::getTableNameFromEntityType($entityClass),
            entityType: AuditLogService::getTableNameFromEntityType($entityClass),
            queryData: $auditData,
            tenantId: $tenantId,
        );
    }
}

The $context parameter contains useful runtime information you can use for more advanced scenarios:

request – The current Illuminate\Http\Request instance.
table – The logical table name used in the API (e.g. invoices).
operation – One of create, update, delete, or upsert.
record_context – The internal record context used by RecordService (includes id, payload, response, etc. depending on the operation).

php artisan record-api:record customers

This generates config/records/tables/customers.php returning a RecordTableType for the customers table. After creating the file and the underlying database table, you can sync its columns definition from the DB schema:

php artisan record-api:sync-record-columns --force

Computed Attributes (Lazy Response Fields)

You can attach computed fields to any table's read responses using the attributes property on RecordTableType. Resolvers are lazy — they only execute when the field key appears explicitly in ?select=.

use Mylekha\RecordApi\Types\RecordTableType;

'brands' => new RecordTableType(
    table: 'brands',
    attributes: [
        // [Class, method] — class resolved via Laravel container
        'full_label' => [\App\Attributes\BrandAttribute::class, 'getFullLabel'],
        // 'Class@method' string
        'logo_url'   => \App\Attributes\BrandAttribute::class . '@getLogoUrl',
        // inline Closure
        'is_premium' => fn($row, $table) => ($row->tier ?? null) === 'premium',
    ],
),

Resolver receives ($row, $table) where $row is the raw DB row (stdClass) and $table is the table name string.

Request	Behaviour
`GET /api/v1/brands`	No resolvers called
`GET /api/v1/brands?select=id,name`	No resolvers called
`GET /api/v1/brands?select=id,full_label`	Only `full_label` resolver fires
`GET /api/v1/brands?select=*,logo_url`	`logo_url` fires; `*` fetches all DB columns
`GET /api/v1/brands/1?select=id,logo_url`	Works identically on single-record endpoint

Attribute keys are automatically excluded from the SQL SELECT to prevent "Unknown column" database errors.

Column Casts

Use the top-level casting property on RecordTableType — a [column => cast] map, mirroring Laravel's $casts on Eloquent models. Only columns listed in casting are transformed; all others are untouched. null values are always preserved.

Flat keys target main-table columns. Dot-notation keys target columns inside eagerly-loaded relationships ('relation.column'), and work for both single-object relations (belongsTo/hasOne) and collection relations (hasMany/hasManyThrough).

use App\Record\Casts\GlobalCasting;

// config/record.php
'casting' => [
    'is_active' => 'bool',
    'amount' => 'decimal:2',
],

'products' => new RecordTableType(
    table: 'products',
    columns: [
        'is_active' => ['type' => 'boolean'],
        'quantity' => ['type' => 'bigint'],
        'price' => ['type' => 'decimal(12,2)'],
    ],
    casting: [
        // flat main-table casts
        'price'      => 'float',
        'quantity'   => 'int',
        'is_active'  => 'bool',
        'metadata'   => 'array',          // JSON string → array
        'score'      => 'decimal:4',
        'created_at' => 'datetime',       // ISO 8601
        // static method cast
        'is_cloud'   => [GlobalCasting::class, 'bool'],
        // inline Closure
        'status'     => fn($v) => strtoupper($v),

        // dot-notation: cast columns inside a hasMany relation
        'variants.price'    => 'float',
        'variants.qty'      => 'int',

        // dot-notation: cast a column inside a belongsTo relation
        'brand.is_active'   => 'bool',
        'brand.founded_at'  => 'date',
    ],
),

Built-in cast strings mirror Laravel model cast names: int, integer, float, double, real, decimal, decimal:N, string, bool, boolean, array, json, object, date, datetime, timestamp.

Custom callable forms (all receive ($value, $column, $row)): Closure, [Class, 'method'] (static or instance), 'Class@method', 'ClassName' (calls ->get($value, $column, $row)).

Step 8: Test Your Installation

Test the dynamic API endpoints:

# Test basic API functionality
curl -X GET http://your-app.test/api/v1/users

# Test with authentication (if configured)
curl -X GET http://your-app.test/api/v1/users \
  -H "Authorization: Bearer your-access-token"

# Test creating a record
curl -X POST http://your-app.test/api/v1/users \
  -H "Content-Type: application/json" \
  -d '{"name":"Test User","email":"test@example.com"}'

🗄️ Database-Specific Instructions

MySQL Configuration

DB_CONNECTION=mysql
DB_HOST=127.0.0.1
DB_PORT=3306
DB_DATABASE=your_database
DB_USERNAME=your_username
DB_PASSWORD=your_password

Recommended MySQL Settings:

-- For better performance with large datasets
SET GLOBAL innodb_buffer_pool_size = 1G;
SET GLOBAL query_cache_size = 256M;
SET GLOBAL max_connections = 200;

PostgreSQL Configuration

DB_CONNECTION=pgsql
DB_HOST=127.0.0.1
DB_PORT=5432
DB_DATABASE=your_database
DB_USERNAME=your_username
DB_PASSWORD=your_password

SQLite Configuration

DB_CONNECTION=sqlite
DB_DATABASE=/absolute/path/to/database.sqlite

Note: SQLite is suitable for development but not recommended for production use with this package.

🔧 Configuration Guide

Core Configuration (`config/record.php`)

return [
    // Multi-tenant mode (optional)
    'enable_tenant_id' => false,
    'tenant_column' => 'tenant_id',
    'tenant_header' => 'X-Tenant-ID',
    'table_config_path' => 'records/tables',

    // API route prefix
    'api_prefix' => 'api/v1',

    // permission 
    'permission_separator' => ':', // separator for permission ex: view:invoice
    'restrict_to_own_records' => false, // limit queries to records created by the authenticated user
    'own_records_permission_prefix' => 'viewOwn', // example: viewOwn_invoice

    // Global limits
    'per_page_max' => 10000,
    'limit_max' => 10000,
    'bulk_max' => 1000,

    // Relationship nesting limit
    'max_depth' => 10,

    // Query caching (used by QueryCacheService)
    'cache' => [
        'enabled' => env('RECORD_API_CACHE_API', false),
        'ttl' => 3600,
        'prefix' => 'sp_laravel_api',
        'per_table' => [],
    ],
    
    // Global RPC function configurations
    'global_functions' => [],

    // Table configurations
    'tables' => [
        // Your table configurations here
    ],
];

Large Schemas (Many Tables)

For applications with many tables and global RPC functions, you can split configurations into multiple files and merge them in config/record.php. For example:

use RecursiveDirectoryIterator;
use RecursiveIteratorIterator;
use Mylekha\RecordApi\Types\RecordTableType;

$tables = [
    // Core tables defined inline
];
$globalFunctions = [];

$tablesDirectory = __DIR__ . '/records/tables';
$globalFunctionsDirectory = __DIR__ . '/records/globalFunctions';

if (is_dir($tablesDirectory)) {
    $directoryIterator = new RecursiveIteratorIterator(
        new RecursiveDirectoryIterator($tablesDirectory)
    );

    foreach ($directoryIterator as $file) {
        if (!$file->isFile() || $file->getExtension() !== 'php') {
            continue;
        }

        $path = $file->getPathname();
        $config = require $path;

        if ($config instanceof RecordTableType) {
            $name = pathinfo($path, PATHINFO_FILENAME);
            $tables[$name] = $config;
        } elseif (is_array($config)) {
            $tables = array_merge($tables, $config);
        }
    }
}

if (is_dir($globalFunctionsDirectory)) {
    $globalFunctionsDirectoryIterator = new RecursiveIteratorIterator(
        new RecursiveDirectoryIterator($globalFunctionsDirectory)
    );

    foreach ($globalFunctionsDirectoryIterator as $file) {
        if (!$file->isFile() || $file->getExtension() !== 'php') {
            continue;
        }

        $path = $file->getPathname();
        $config = require $path;
        if (!is_array($config)) {
            continue;
        }

        $group = pathinfo((string) $path, PATHINFO_FILENAME);
        foreach ($config as $functionName => $functionConfig) {
            if (!is_string($functionName) || $functionName === '') {
                continue;
            }

            $normalizedFunctionName = ltrim($functionName, '/');
            $prefixedFunctionName = str_contains($normalizedFunctionName, '/')
                ? $normalizedFunctionName
                : $group . '/' . $normalizedFunctionName;

            $globalFunctions[$prefixedFunctionName] = $functionConfig;
        }
    }
}

return [
    'api_prefix' => 'api/v1',
    'enable_tenant_id' => false,
    'tenant_column' => 'tenant_id',
    'tenant_header' => 'X-Tenant-ID',
    'max_depth' => 10,
    'cache' => [
        'enabled' => env('RECORD_API_CACHE_API', false),
        'ttl' => 3600,
        'prefix' => 'sp_laravel_api',
        'per_table' => [],
    ],
    'global_functions' => $globalFunctions,
    'tables' => $tables,
];

Each file under config/records/tables can return a single RecordTableType or an array of [table_name => RecordTableType]. Each file under config/records/globalFunctions must return an array. File name becomes group prefix for keys without / (example: auth.php + login => auth/login).

Authentication Setup

SP Laravel API does not ship its own authentication driver. Instead, it uses the Laravel auth guard you configure in config/record-api.php, which can point to any driver (JWT, Sanctum, Passport, etc.):

return [
    'auth' => [
        'guard' => env('RECORD_API_AUTH_GUARD', 'api'),
    ],
];

Configure your desired guard in config/auth.php and set RECORD_API_AUTH_GUARD accordingly.

Option A: JWT Authentication

# Install JWT package
composer require php-open-source-saver/jwt-auth
php artisan vendor:publish --provider="PHPOpenSourceSaver\JWTAuth\Providers\LaravelServiceProvider"
php artisan jwt:secret

Configure your User model:

use PHPOpenSourceSaver\JWTAuth\Contracts\JWTSubject;

class User extends Authenticatable implements JWTSubject
{
    public function getJWTIdentifier()
    {
        return $this->getKey();
    }

    public function getJWTCustomClaims()
    {
        return [];
    }
}

Option B: Laravel Sanctum

composer require laravel/sanctum
php artisan vendor:publish --provider="Laravel\Sanctum\SanctumServiceProvider"
php artisan migrate

Middleware Configuration

SP Laravel API routes already include request.id. If you want request IDs on your own endpoints too, apply it to your routes:

Route::middleware(['api', 'auth:api', 'request.id'])->group(function () {
    // Your API routes
});

Step 7: Queue Configuration (Optional but Recommended)

For optimal performance with audit logging, configure queues:

# Install Redis (recommended)
composer require predis/predis

# Or use database queues
php artisan queue:table
php artisan migrate

Update your .env:

QUEUE_CONNECTION=redis
# or
QUEUE_CONNECTION=database

Start the queue worker:

php artisan queue:work

Step 8: Permissions Setup (Optional)

If you install Spatie Laravel Permission, you can set it up like this:

# Publish the permission migration
php artisan vendor:publish --provider="Spatie\Permission\PermissionServiceProvider"

# Run the migration
php artisan migrate

Create basic permissions for your tables:

use Spatie\Permission\Models\Permission;
use Spatie\Permission\Models\Role;

// Create permissions for your tables
Permission::create(['name' => 'view_users']);
Permission::create(['name' => 'create_users']);
Permission::create(['name' => 'update_users']);
Permission::create(['name' => 'delete_users']);

// Create roles and assign permissions
$adminRole = Role::create(['name' => 'admin']);
$adminRole->givePermissionTo(['view_users', 'create_users', 'update_users', 'delete_users']);

$userRole = Role::create(['name' => 'user']);
$userRole->givePermissionTo(['view_users']);

Add the trait to your User model:

use Spatie\Permission\Traits\HasRoles;

class User extends Authenticatable
{
    use HasRoles;
    
    // Your model code
}

Per-Table Custom Permission Names

Instead of relying on the auto-generated pmsName:action pattern, you can declare exact permission names per action directly on RecordTableType using the permissions property:

'items' => new RecordTableType(
    table: 'items',
    pmsName: 'item',  // fallback for any action not in the permissions map
    permissions: [
        'read'    => 'view_list_item',
        'create'  => 'insert_new_item',
        'update'  => 'update_existing_item',
        'delete'  => 'remove_item',
        'restore' => 'restore_item',
    ],
),

Partial overrides are supported — actions not listed fall back to the standard pmsName-based generation. Values can also be arrays for OR-logic: 'read' => ['view_item', 'admin_access'].

No breaking change: permissions: null (the default) preserves identical behavior to previous versions.

🧪 Testing Instructions

Running Package Tests

# Run all tests
vendor/bin/phpunit

# Run specific test suites
vendor/bin/phpunit tests/Feature/DynamicApiTest.php
vendor/bin/phpunit tests/Unit/SchemaRegistryTest.php

# Run tests with coverage
vendor/bin/phpunit --coverage-html coverage

Manual Testing

Create test data and verify API functionality:

# Create test user
php artisan tinker
>>> $user = \App\Models\User::create(['name' => 'Test User', 'email' => 'test@example.com', 'password' => bcrypt('password')]);

# Test API endpoints
curl -X GET http://your-app.test/api/v1/users
curl -X GET http://your-app.test/api/v1/users/1
curl -X POST http://your-app.test/api/v1/users -H "Content-Type: application/json" -d '{"name":"New User","email":"new@example.com"}'

🔍 Troubleshooting

Common Issues and Solutions

1. "Class 'Mylekha\RecordApi\RecordApiServiceProvider' not found"

Solution:

# Clear composer autoload cache
composer dump-autoload

# Ensure package is properly installed
composer require mylekha/record-api

# Clear Laravel caches
php artisan config:clear
php artisan cache:clear

2. "Configuration file not found"

Solution:

# Publish configuration files
php artisan vendor:publish --provider="Mylekha\RecordApi\RecordApiServiceProvider"

# Or publish specific configs
php artisan vendor:publish --tag=record-api-config

3. "Database connection issues"

Solution:

# Test database connection
php artisan record-api:validate --verbose

# Check database configuration
php artisan config:show database.connections.mysql

# Verify migrations
php artisan migrate:status

4. "Permission denied errors" (using Spatie Permission)

Solution (if you are using Spatie Laravel Permission):

# Ensure permissions are created
php artisan permission:create-permission view_users
php artisan permission:create-permission create_users

# Assign permissions to user
php artisan tinker
>>> $user = \App\Models\User::find(1);
>>> $user->givePermissionTo('view_users');

5. "Auth token issues" (JWT/Sanctum/Passport/etc)

Solution:

# Clear config cache
php artisan config:clear

Verify your guard configuration:

config/record-api.php → auth.guard
config/auth.php → the configured guard/driver setup

6. "API routes not working"

Solution:

# Check route registration
php artisan route:list | grep api

# Verify middleware configuration
php artisan route:list --middleware=api

# Clear route cache
php artisan route:clear

7. "Command summary"

Available artisan commands: Solution:

  # Clean old audit logs based on retention configuration
  php artisan record-api:clean-audit-logs
  # Setup SP Laravel API package: publish configs and create record/audit configurations using config/record.php + config/records/tables/*.php + config/records/globalFunctions/*.php
  php artisan record-api:setup
  # Create a RecordTableType config file under config/records/tables
  php artisan record-api:record customers
  # Populate RecordTableType columns in config/records/tables PHP files based on DB schema
  php artisan record-api:sync-record-columns
  # Validate SP Laravel API package setup and configuration
  php artisan record-api:validate

Debug Mode

Enable debug mode for detailed error information:

APP_DEBUG=true
LOG_LEVEL=debug

Validation Command

Use the validation command to diagnose issues:

# Run comprehensive validation
php artisan record-api:validate --verbose --fix

⚙️ Advanced Configuration

Performance Optimization

1. Database Optimization

// config/record.php
return [
    // Limit relationship nesting depth for better performance
    'max_depth' => 2,

    // Enable/disable query caching
    'cache' => [
        'enabled' => true,
        'ttl' => 3600,
    ],
];

2. Redis Configuration

REDIS_HOST=127.0.0.1
REDIS_PASSWORD=null
REDIS_PORT=6379
CACHE_DRIVER=redis
SESSION_DRIVER=redis
QUEUE_CONNECTION=redis

3. Queue Optimization

# Use multiple queue workers
php artisan queue:work --queue=high,default --tries=3 --timeout=60

# Use Supervisor for production
sudo apt-get install supervisor

Security Configuration

1. API Rate Limiting

The package routes use throttle:api-reads, throttle:api-writes, and throttle:api-functions. Define those limiters in your application (example shown in the installation steps).

2. CORS Configuration

Laravel ships CORS configuration out of the box. Configure it in your app via config/cors.php.

3. API Versioning

// config/record.php
return [
    'api_prefix' => 'api/v1',
];

Multi-tenancy Setup

// config/record.php
return [
    'enable_tenant_id' => true,
    'tenant_column' => 'tenant_id',
    'tenant_header' => 'X-Tenant-ID',
];

When enable_tenant_id=true, requests for tables configured with hasTenantId=true must include the tenant header (default: X-Tenant-ID).

Custom Middleware

// Register custom middleware
protected $middlewareGroups = [
    'api' => [
        \App\Http\Middleware\TenantMiddleware::class,
        \Mylekha\RecordApi\Http\Middleware\RequestId::class,
        \App\Http\Middleware\ApiVersioning::class,
    ],
];

🚀 Development Setup

Setting up for Package Development

# Clone the repository
git clone https://github.com/your-username/record-api.git
cd record-api

# Install dependencies
composer install

# Set up testing environment
cp .env.example .env.testing
php artisan key:generate --env=testing

# Run tests
vendor/bin/phpunit

Code Quality Tools

# Install development tools
composer require --dev rector/rector
composer require --dev phpunit/phpunit

# Fix code style
vendor/bin/rector process src

Contributing Guidelines

Fork the repository
Create a feature branch
Write tests for new functionality
Ensure all tests pass
Submit a pull request

📚 Migration Guide

From Version 1.x to 2.x

# Update composer.json
"mylekha/record-api": "*"

# Update dependencies
composer update

# Republish configurations
php artisan vendor:publish --provider="Mylekha\RecordApi\RecordApiServiceProvider" --force

# Run new migrations
php artisan migrate

# Update configuration format
# See examples/config/record.php for new format

Breaking Changes in 2.x

Configuration format changed to use Type classes
New permission system integration
Updated middleware registration
Enhanced caching mechanisms

🎯 Performance Optimization

Database Optimization

-- Add indexes for better performance
CREATE INDEX idx_audit_logs_entity ON audit_logs(entity_type, entity_id);
CREATE INDEX idx_audit_logs_created_at ON audit_logs(created_at);
-- Optional (only if you enable multi-tenant mode and store tenant IDs in audit_logs)
-- CREATE INDEX idx_audit_logs_tenant_id ON audit_logs(tenant_id);
CREATE INDEX idx_users_email ON users(email);

Caching Strategy

// config/record.php
return [
    'cache' => [
        'enabled' => env('CACHE_API', false),
        'ttl' => 3600,
        'prefix' => 'sp_laravel_api',
        'per_table' => [],
    ],
];

Queue Configuration

# Use Redis for better performance
QUEUE_CONNECTION=redis

# Configure queue workers
php artisan queue:work --queue=high,default --sleep=3 --tries=3 --max-time=3600

Step 10: Configure Your Models (Optional)

To use the QueryHelpers trait in your models:

use Mylekha\RecordApi\Traits\QueryHelpers;

class User extends Authenticatable
{
    use QueryHelpers;
    
    // Your model code
}

To implement audit logging in your models:

use Mylekha\RecordApi\Traits\HasAuditQuery;
use Mylekha\RecordApi\Interfaces\AuditQueryInterface;

class User extends Authenticatable implements AuditQueryInterface
{
    use HasAuditQuery;
    
    public function getAuditEntityName(): string
    {
        return 'users';
    }
    
    public function getAuditEntityClass(): string
    {
        return static::class;
    }
}

Usage Examples

Once installed, you can immediately start using the dynamic API endpoints:

# List users with pagination
GET /api/users

# Get specific user with relationships
GET /api/users/1?with=posts,roles

# Create new user
POST /api/users
{
    "name": "John Doe",
    "email": "john@example.com"
}

# Update user
PUT /api/users/1
{
    "name": "Jane Doe"
}

# Delete user (soft delete)
DELETE /api/users/1

# Search users
GET /api/users?s=john&status=eq.active

# Advanced filtering
GET /api/users?age=gt.18&created_at=between.2024-01-01,2024-12-31

Services

RecordApiResponseService

Provides standardized JSON responses:

use Mylekha\RecordApi\Services\RecordApiResponseService;
use Mylekha\RecordApi\Enums\RecordApiJsonResponseEnum;

$response = app('api.response');
return $response->success($data, 'Operation successful');
return $response->error('Error message', RecordApiJsonResponseEnum::ERROR->value);

AuditLogService

Comprehensive audit logging for data changes:

use Mylekha\RecordApi\Services\AuditLogService;
use Mylekha\RecordApi\Enums\AuditLogEventEnum;

AuditLogService::handleAuditDataEntry(
    event: AuditLogEventEnum::CREATED,
    entityName: 'users',
    entityType: 'users',
    queryData: ['id' => 1, 'name' => 'John Doe'],
    subject: 'John Doe',
    recap: null,
    tenantId: null,
);

AuditLogJob

Queue-based audit logging for improved performance:

use Mylekha\RecordApi\Jobs\AuditLogJob;
use Mylekha\RecordApi\Enums\AuditLogEventEnum;

// Dispatch audit log job to queue
AuditLogJob::dispatch(
    event: AuditLogEventEnum::CREATED,
    entityName: 'users',
    entityType: 'users',
    queryData: ['id' => 1, 'name' => 'John Doe'],
    tenantId: null,
);

QueryCacheService

Intelligent query caching:

use Mylekha\RecordApi\Services\QueryCacheService;

$result = QueryCacheService::remember(
    key: 'users:first',
    callback: fn () => \Illuminate\Support\Facades\DB::table('users')->first(),
    ttl: 3600,
);

Middleware

The package registers the request.id middleware:

Route::middleware('request.id')->group(function () {
    // Your routes here
});

Usage

Middleware:
- Add request.id to routes or groups: Route::middleware(['request.id'])->group(function () { ... });.
Responses:
- Resolve via container: $service = app('api.response');
- return $service->success(['items' => []]);
- All responses include meta.request_id when middleware is active.

CLI Commands

OpenAPI Specification

OpenAPI is generated dynamically from record configuration at request time:

GET /{api_prefix}/docs/openapi
GET /{api_prefix}/docs/openapi.json
GET /{api_prefix}/docs/llms.mdx
GET /{api_prefix}/docs/llms.txt

/{api_prefix}/docs/openapi.json is the recommended machine endpoint for public docs mode, returned with application/vnd.oai.openapi+json. /{api_prefix}/docs/llms.mdx (or llms.txt) provides an AI-oriented markdown contract that points to the OpenAPI schema and key endpoint patterns.

Use the bundled Scalar page to browse the API documentation:

GET /api-docs
GET /api-docs/openapi.json

Docs access mode is configurable via config/record.php:

'api_docs' => [
    'is_private' => env('RECORD_API_DOCS_PRIVATE', false),
    'access_token_key' => 'access_token',
    'login_api' => '/v1/auth/login',
    'email' => env('RECORD_API_DOCS_EMAIL'),
],

When is_private=false (or api_docs config is missing), /api-docs is public.
When is_private=true, /api-docs shows a login form and logs in through internal proxy route /api-docs/auth/login.
In private mode, schema is served from /api-docs/openapi.json and requires docs session token.
In private mode, API docs routes under /{api_prefix}/docs/* return 404 to prevent anonymous schema leakage.
login_api can be a relative path (/v1/auth/login) or absolute URL (https://api.example.com/v1/auth/login) based on client project routing.
access_token_key controls how token is extracted from login response payload.
email (optional) enforces a fixed docs login account for additional protection.

The generated OpenAPI 3.0 schema includes:

Enhanced Schema Generation:

Full Schema: Complete model schema with all properties
Read Schema: Optimized for GET responses (includes computed fields, relationships)
Write Schema: Optimized for POST/PUT requests (excludes read-only fields)

Automatic Documentation:

Dynamic CRUD endpoints for all configured tables
RPC function endpoints (global and table-specific)
Comprehensive parameter documentation (pagination, filtering, sorting)
Detailed response schemas with examples
Security schemes (Bearer token authentication)

Smart Table Detection:

Automatically discovers database tables
Generates appropriate tags and descriptions
Includes relationship documentation
Supports custom table configurations

Compatibility:

Compatible with Swagger UI, Postman, and other OpenAPI tools

Example Generated Features:

RESTful endpoints: GET /{prefix}/{table}, POST /{prefix}/{table}, etc.
RPC endpoints: GET|POST|PUT|PATCH|DELETE /{prefix}/rpc/{functionName}, GET|POST|PUT|PATCH|DELETE /{prefix}/{table}/rpc/{functionName}
Advanced filtering and pagination parameters
Comprehensive error response documentation

Setup Package

php artisan record-api:setup

Publishes default configurations for:

config/record.php - Dynamic table and global function loader configuration
config/records/tables - Per-table RecordTableType files
config/records/globalFunctions - Global RPC function group files
config/audit.php - Audit logging settings
config/record-api.php - Package settings (auth guard, OpenAPI output)

Record Table & Cache Management

# Create a new per-table RecordTableType config (config/records/tables/{name}.php)
php artisan record-api:record customers

# Clear record cache
php artisan record-api:cache-clear

# Sync RecordTableType columns in config/records/tables from DB schema
php artisan record-api:sync-record-columns

# Clean old audit logs based on retention policy
php artisan record-api:clean-audit-logs

# Clean audit logs with options
php artisan record-api:clean-audit-logs --dry-run
php artisan record-api:clean-audit-logs --force --days=30
php artisan record-api:clean-audit-logs --batch-size=500

API Endpoints

The package automatically registers RESTful API routes for dynamic database operations. The route prefix is configurable via config('record.api_prefix') (default: api/v1).

Route Configuration

// config/record.php
'api_prefix' => 'api/v1',

Examples:

'api' → /api/{table}
'api/v1' → /api/v1/{table}
'api/v2' → /api/v2/{table}
'records' → /records/{table}

Standard CRUD Operations

GET /{prefix}/{table} - List records with filtering and pagination
GET /{prefix}/{table}/{id} - Get specific record
POST /{prefix}/{table} - Create new record
PUT/PATCH /{prefix}/{table}/{id} - Update record
POST /{prefix}/{table}/upsert - Upsert (create or update) record
DELETE /{prefix}/{table}/{id} - Soft delete record

Advanced Operations

POST /{prefix}/{table}/{id}/restore - Restore soft-deleted record
DELETE /{prefix}/{table}/{id}/force - Permanently delete record
POST /{prefix}/{table}/bulk - Bulk operations
POST /{prefix}/{table}/bulk/create - Bulk create
POST /{prefix}/{table}/bulk/update - Bulk update
POST /{prefix}/{table}/bulk/delete - Bulk delete
POST /{prefix}/{table}/bulk/upsert - Bulk upsert

RPC Functions

GET|POST|PUT|PATCH|DELETE /{prefix}/rpc/{functionName} - Execute global functions
GET|POST|PUT|PATCH|DELETE /{prefix}/{table}/rpc/{functionName} - Execute table-specific functions

Configuration

Publishing Configuration Files

# Publish main package configuration
php artisan vendor:publish --tag=record-api-config

# Publish all configurations (record, audit, cursor_pagination, record-api)
php artisan record-api:setup

Main Configuration (`config/record-api.php`)

return [
    'response' => [
        'include_request_id' => env('RECORD_API_INCLUDE_REQUEST_ID', true),
    ],
    'openapi' => [
        'output' => env('RECORD_API_OPENAPI_OUTPUT', 'storage/openapi-schema.json'),
        'info' => [
            'title' => env('APP_NAME', 'Laravel API'),
            'version' => '2.0.0',
            'description' => 'Comprehensive API documentation with dynamic CRUD operations...',
        ],
        'servers' => [
            [
                'url' => env('APP_URL', 'http://localhost'),
                'description' => 'Development server',
            ],
        ],
    ],
];

Record Configuration (`config/record.php`)

Controls database table operations and OpenAPI generation:

return [
    'enable_tenant_id' => false,
    'api_prefix' => 'api/v1',
    'global_functions' => [
        // Define custom RPC functions here
        // 'functionName' => YourFunctionClass::class,
    ],
    // Table-specific configurations...
];

Example Controller

use Illuminate\Http\Request;

class ExampleController
{
    public function index(Request $request)
    {
        $responses = app('api.response');
        return $responses->success(['message' => 'OK']);
    }
}

QueryHelpers Trait

The QueryHelpers trait provides powerful query filtering and manipulation capabilities for Eloquent models.

Usage

use Mylekha\RecordApi\Traits\QueryHelpers;

class YourModel extends Model
{
    use QueryHelpers;
}

Available Methods

applyRequestFilters($request, $isArray = false, $orderBy = 'id') - Apply request-based filters

Supported Query Parameters

s - Search all fields (e.g., ?s=cambodia)
select - Specify columns including relationships (e.g., ?select=id,name,customer:id,name)
with - Load related models (e.g., ?with=user,posts.comments)
sortby - Column to sort by (e.g., ?sortby=name)
order - Sort direction: asc/desc (e.g., ?order=asc)
per_page - Enable pagination (e.g., ?per_page=20)
limit - Limit results when isArray=true (e.g., ?limit=1000)
lazy - Enable lazy loading (e.g., ?lazy=true)

Filter Operators

Supported syntax includes:

Standard filters: eq, neq, gt, gte, lt, lte, in, not_in, between, not_between, like, ilike, contains, starts_with, ends_with, regex, match, imatch, date operators, and null/empty operators.
Grouped logic: and=(...), or=(...).
Advanced expression style: not.<operator> and operator(any|all).{...}.
List styles: both id=in.(5,6,9) and legacy id=in.5,6,9.
Driver guard: unsupported operators return 422 validation error with explicit message.

For complete operator matrix, grouped logic examples, and driver compatibility details, see:

docs/api-documentation.md → Filter Operators and Grouped Logic.

Example Usage

// In your controller
public function index(Request $request)
{
    $results = YourModel::query()
        ->applyRequestFilters($request, true);
    
    return response()->json($results);
}

You can also use named arguments when calling the scope (PHP 8+):

$results = YourModel::query()->applyRequestFilters(
    request: $request,
    isArray: true,
    orderBy: 'created_at',
);

Audit Log Cleanup

The package includes a powerful CLI command for cleaning old audit logs based on your retention policy.

Configuration

Set the retention period in your audit configuration:

// config/audit.php
'retention_days' => env('AUDIT_LOG_RETENTION_DAYS', 365),

Command Options

--dry-run - Show what would be deleted without actually deleting
--force - Force deletion without confirmation prompt
--days=N - Override retention days from config
--batch-size=N - Number of records to delete per batch (default: 1000)

Usage Examples

# Basic cleanup (uses config retention_days)
php artisan record-api:clean-audit-logs

# Dry run to see what would be deleted
php artisan record-api:clean-audit-logs --dry-run

# Force cleanup without confirmation
php artisan record-api:clean-audit-logs --force

# Override retention period to 30 days
php artisan record-api:clean-audit-logs --days=30

# Use smaller batch size for large datasets
php artisan record-api:clean-audit-logs --batch-size=500

# Combine options
php artisan record-api:clean-audit-logs --dry-run --days=90

Features

Safe by default: Requires confirmation unless --force is used
Batch processing: Deletes records in configurable batches to prevent database locks
Progress tracking: Shows real-time progress with progress bar
Statistics: Reports total deleted and remaining records
Dry run mode: Preview what would be deleted without making changes
Configurable: Respects audit configuration or allows override

Documentation

Package Documentation

API Documentation: docs/api-documentation.md - Comprehensive API endpoints and usage guide
Audit Interface: docs/audit-interface.md - Custom audit queries and logging
Performance: docs/performance.md - Benchmarks and optimization notes
Use Cases: docs/use-cases.md - Why use this for SaaS/ERP style APIs

Core Classes Reference

Request ID Middleware: Mylekha\RecordApi\Http\Middleware\RequestId
API Response Service: Mylekha\RecordApi\Services\RecordApiResponseService
Audit Log Service: Mylekha\RecordApi\Services\AuditLogService
Query Cache Service: Mylekha\RecordApi\Services\QueryCacheService
Record API Controller: Mylekha\RecordApi\Http\Controllers\CoreRecordController
Query Helpers Trait: Mylekha\RecordApi\Traits\QueryHelpers
Audit Query Interface: Mylekha\RecordApi\Interfaces\AuditQueryInterface

Notes

Keep request.id middleware active to ensure meta.request_id consistency.
Extend the OpenAPI generator as needed for your endpoints.

📄 License

Released under the MIT License — see LICENSE.

mylekha / record-api

Package info

Security

Statistics

README

🚀 Quick Start

✨ Features

📚 Documentation

📋 Requirements

📦 Dependencies and Optional Integrations

📥 Installation & Setup

Step 1: Install the Package

Option A: Via Composer (Recommended for Production)

Option B: Local Development (VCS Repository)

Step 2: Publish/Generate Configuration Files

Step 3: Environment Configuration

Step 4: Publish Migrations and Run Migrations

Step 5: Configure Rate Limiters

Step 6: Validate Installation

Step 7: Configure Database Tables

Config-Driven Middleware Map (Client Use Case)

Request Context for Hooks and Custom Audit

Table-Level Custom Audit Logger

Computed Attributes (Lazy Response Fields)

Column Casts

Step 8: Test Your Installation

🗄️ Database-Specific Instructions

MySQL Configuration

PostgreSQL Configuration

SQLite Configuration

🔧 Configuration Guide

Core Configuration (config/record.php)

Large Schemas (Many Tables)

Authentication Setup

Option A: JWT Authentication

Option B: Laravel Sanctum

Middleware Configuration

Step 7: Queue Configuration (Optional but Recommended)

Step 8: Permissions Setup (Optional)

Per-Table Custom Permission Names

🧪 Testing Instructions

Running Package Tests

Manual Testing

🔍 Troubleshooting

Common Issues and Solutions

1. "Class 'Mylekha\RecordApi\RecordApiServiceProvider' not found"

2. "Configuration file not found"

3. "Database connection issues"

4. "Permission denied errors" (using Spatie Permission)

5. "Auth token issues" (JWT/Sanctum/Passport/etc)

6. "API routes not working"

7. "Command summary"

Debug Mode

Validation Command

⚙️ Advanced Configuration

Performance Optimization

1. Database Optimization

2. Redis Configuration

3. Queue Optimization

Security Configuration

1. API Rate Limiting

2. CORS Configuration

3. API Versioning

Multi-tenancy Setup

Custom Middleware

🚀 Development Setup

Setting up for Package Development

Code Quality Tools

Contributing Guidelines

📚 Migration Guide

From Version 1.x to 2.x

Breaking Changes in 2.x

🎯 Performance Optimization

Database Optimization

Caching Strategy

Queue Configuration

Step 10: Configure Your Models (Optional)

Usage Examples

Services

RecordApiResponseService

Core Configuration (`config/record.php`)

Main Configuration (`config/record-api.php`)

Record Configuration (`config/record.php`)