README

Dynamic Hybrid Reporting Engine

Enterprise reporting infrastructure for Laravel — MySQL + MongoDB, no SQL required

One JSON DSL. MySQL + MongoDB. Visual no-code builder. Six KPI themes.
Enterprise versioning, sharing, and row-level security — all built in.

Author: Mostafa Elbayyar — mostafa.m.elbiar2@gmail.com
GitHub: github.com/mostafax2/dynamic-hybrid-reporting-engine
Docs: mostafax2.github.io/dynamic-hybrid-reporting-engine

Table of Contents

• Overview
• Requirements
• Quick Install
• Detailed Setup
• Visual Report Builder
• DSL Reference
• PHP Facade
• Blade Components
• KPI Themes
• REST API
• Versioning
• Sharing & Permissions
• Row-Level Security
• Templates
• Security Model
• Custom Adapters
• Configuration
• Database Tables
• Architecture
• FAQ

🌟 Overview

Dynamic Hybrid Reporting Engine is a Laravel package that delivers a complete reporting infrastructure in a single composer require. It provides a visual no-code report builder, a unified JSON DSL that works identically for MySQL and MongoDB, six live-switchable KPI card themes, and enterprise-grade features including versioning, RBAC sharing, row-level security, and Redis caching.

Feature Summary

📋 Requirements

Required

Optional

⚡ Quick Install

# 1. Install the package and Redis client
composer require mostafax/dynamic-hybrid-reporting-engine predis/predis

# 2. Publish config and run migrations
php artisan vendor:publish --tag=reporting-engine-config
php artisan vendor:publish --tag=reporting-engine-migrations
php artisan migrate

# 3. Seed demo data (optional)
php artisan db:seed --class=ReportingEngineDemoSeeder

Done! Open /reporting-demo to see live components, /report-builder for the visual builder, and /api/reporting for the REST API.

🔧 Detailed Setup

Step 1 — Install

composer require mostafax/dynamic-hybrid-reporting-engine predis/predis

For local development with a path repository:

{
    "repositories": [
        { "type": "path", "url": "packages/mostafax/reporting-engine" }
    ],
    "require": {
        "mostafax/dynamic-hybrid-reporting-engine": "@dev"
    }
}

Step 2 — Publish Assets

# Config only
php artisan vendor:publish --tag=reporting-engine-config

# Migrations only
php artisan vendor:publish --tag=reporting-engine-migrations

# Vue builder assets
php artisan vendor:publish --tag=reporting-engine-builder-js

Step 3 — Run Migrations

php artisan migrate

Step 4 — Environment Variables

# Redis (required)
REDIS_HOST=127.0.0.1
REDIS_PORT=6379

# Route prefix (default: api/reporting)
REPORTING_ROUTE_PREFIX=api/reporting

# Auth middleware (leave empty to disable for dev)
REPORTING_AUTH_MIDDLEWARE=auth:sanctum

# Multi-tenancy (optional)
REPORTING_TENANT_ENABLED=false
REPORTING_TENANT_COLUMN=tenant_id

Step 5 — Build the Vue Builder (optional)

npm install --legacy-peer-deps
php artisan vendor:publish --tag=reporting-engine-builder-js
npm run build

Visit /report-builder — the full Vue 3 visual builder is now available.

Step 6 — Seed Demo Data (optional)

php artisan db:seed --class=ReportingEngineDemoSeeder

Creates:

• demo_orders — 80 MySQL rows
• demo_events — 120 MongoDB documents
• 5 demo reports saved in dhr_reports

🏗️ Visual Report Builder

A full-page Vue 3 SPA at /report-builder:

/report-builder          → create a new report
/report-builder/{id}     → edit an existing report

Disable auth for development:

REPORTING_AUTH_MIDDLEWARE=

Builder Panels

Embed a Saved Report

{{-- Embed anywhere after saving in the builder --}}
<x-reporting-engine::report-widget
    report="monthly-revenue"
    :show-export="true"
    :per-page="25"
/>

{{-- Or via Facade --}}
$result = ReportingEngine::run('monthly-revenue');

{{-- Or via REST API --}}
POST /api/reporting/monthly-revenue/run

📐 DSL Reference

Full Schema

Filter operators: = != > >= < <= like not_like in not_in between null not_null

Aggregation functions: sum count avg min max count_distinct

MySQL DSL Example

{
  "source": "mysql",
  "table":  "orders",
  "fields": [
    { "column": "id",            "alias": "order_id" },
    { "column": "customer_name" },
    { "column": "amount" }
  ],
  "aggregations": [
    { "function": "sum",   "column": "amount", "alias": "revenue" },
    { "function": "count", "column": "id",     "alias": "orders"  }
  ],
  "filters": {
    "operator": "AND",
    "conditions": [
      { "field": "status", "operator": "=",  "value": "completed" },
      { "field": "amount", "operator": ">=", "value": 100 }
    ]
  },
  "group_by":   ["status"],
  "order_by":   [{ "column": "revenue", "direction": "desc" }],
  "pagination": { "page": 1, "per_page": 25 }
}

MongoDB DSL Example

{
  "source":     "mongodb",
  "connection": "mongodb",
  "table":      "analytics",
  "aggregations": [
    { "function": "sum",            "column": "revenue", "alias": "total_revenue"  },
    { "function": "count_distinct", "column": "userId",  "alias": "unique_users" },
    { "function": "avg",            "column": "revenue", "alias": "avg_revenue"    }
  ],
  "group_by":   ["channel"],
  "order_by":   [{ "column": "total_revenue", "direction": "desc" }],
  "pagination": { "page": 1, "per_page": 20 }
}

Engine auto-builds: $match → $addFields → $group → $project → $sort → $facet
Data + totalCount in a single round-trip. BSONArray/ObjectId/UTCDateTime normalised automatically.

Computed Fields (Formula Engine)

{
  "computed": [
    {
      "alias":      "margin",
      "expression": "(amount - cost) / NULLIF(amount, 0) * 100",
      "label":      "Margin %",
      "format":     "percent"
    },
    {
      "alias":      "tax",
      "expression": "amount * 0.15",
      "label":      "Tax Amount",
      "format":     "currency"
    },
    {
      "alias":      "tier",
      "expression": "IF(amount > 1000, 'Premium', 'Standard')",
      "label":      "Customer Tier"
    }
  ]
}

Allowed functions: IF COALESCE NULLIF ROUND ABS CEILING FLOOR CONCAT DATEDIFF DATE_FORMAT UPPER LOWER TRIM LENGTH NOW YEAR MONTH DAY

Security: All expressions pass through FormulaLexer (character allowlist) then MySQLFormulaTranspiler (backtick-quoted identifiers, function whitelist) before any SQL is built.

Column Metadata

{
  "column_meta": [
    { "key": "order_id", "label": "Order #",   "visible": true,  "order": 0, "width": "80px",  "align": "left"  },
    { "key": "amount",   "label": "Revenue",   "visible": true,  "order": 1, "format": "currency", "align": "right" },
    { "key": "cost",     "label": "Cost",      "visible": false, "order": 2 },
    { "key": "margin",   "label": "Margin %",  "visible": true,  "order": 3, "format": "percent",  "align": "right" }
  ]
}

format values: currency · percent · date · datetime · number · text
align values: left · center · right

Joins with Aliases

{
  "source": "mysql",
  "table":  "orders",
  "joins": [
    {
      "type":     "left",
      "table":    "customers",
      "alias":    "c",
      "first":    "orders.customer_id",
      "operator": "=",
      "second":   "c.id"
    }
  ],
  "fields": [
    { "column": "orders.id",     "alias": "order_id" },
    { "column": "c.name",        "alias": "customer" },
    { "column": "orders.amount", "alias": "amount"   }
  ]
}

🔧 PHP Facade

use Mostafax\ReportingEngine\Support\Facades\ReportingEngine;

// Run an ad-hoc DSL query
$result = ReportingEngine::run([
    'source'       => 'mysql',
    'table'        => 'orders',
    'fields'       => [
        ['column' => 'id',     'alias' => 'order_id'],
        ['column' => 'status'],
        ['column' => 'amount'],
    ],
    'computed' => [
        ['alias' => 'tax', 'expression' => 'amount * 0.15', 'label' => 'Tax Amount', 'format' => 'currency'],
    ],
    'aggregations' => [
        ['function' => 'sum',   'column' => 'amount', 'alias' => 'revenue'],
        ['function' => 'count', 'column' => 'id',     'alias' => 'orders'],
    ],
    'filters' => [
        'operator'   => 'AND',
        'conditions' => [
            ['field' => 'status', 'operator' => '=',  'value' => 'completed'],
            ['field' => 'amount', 'operator' => '>=', 'value' => 100],
        ],
    ],
    'order_by'   => [['column' => 'revenue', 'direction' => 'desc']],
    'pagination' => ['page' => 1, 'per_page' => 25],
]);

$result->data;                        // array of rows (includes computed 'tax' column)
$result->total;                       // total row count without LIMIT
$result->metadata->executionTimeMs;   // e.g. 8.4
$result->metadata->cacheHit;          // true if served from Redis

// Execute a saved report by ID
use Mostafax\ReportingEngine\Application\Services\ExecutionService;

$result = app(ExecutionService::class)
    ->runById('monthly-revenue', ['pagination' => ['page' => 2]]);

🧩 Blade Components

{{-- KPI cards — 6 visual themes --}}
<x-reporting-engine::kpi-widget
    report="revenue-by-status"
    :cols="3"
    color="#0077A8"
    variant="glass"
/>

{{-- Chart with Chart.js (SSR table fallback for no-JS) --}}
<x-reporting-engine::chart-widget
    report="revenue-by-status"
    chart-type="bar"
    label-column="status"
    value-column="total_revenue"
/>

{{-- Filterable table with inline filters and export --}}
<x-reporting-engine::report-filter report="orders" :inline="true" />
<x-reporting-engine::report-widget
    report="orders"
    :show-export="true"
    :per-page="15"
/>

{{-- Export buttons only --}}
<x-reporting-engine::report-export
    report="orders"
    :formats="['csv', 'xlsx', 'json']"
/>

{{-- Livewire: real-time filter + sort + paginate --}}
<livewire:report-widget
    report="orders"
    :per-page="20"
    :show-filters="true"
    :show-export="true"
/>

🎨 KPI Themes

Pass variant="…" to switch the card style. The color prop sets --kpi-color inherited by all variants. Visit /reporting-demo for a live switcher and color picker.

<x-reporting-engine::kpi-widget report="id" variant="flat"     /> {{-- default, white card with color top border --}}
<x-reporting-engine::kpi-widget report="id" variant="gradient" /> {{-- tinted background --}}
<x-reporting-engine::kpi-widget report="id" variant="dark"     /> {{-- dark slate background --}}
<x-reporting-engine::kpi-widget report="id" variant="glass"    /> {{-- frosted glass on color gradient --}}
<x-reporting-engine::kpi-widget report="id" variant="minimal"  /> {{-- borderless, color bottom line --}}
<x-reporting-engine::kpi-widget report="id" variant="bold"     /> {{-- solid color fill --}}

🌐 REST API

All endpoints are prefixed with config('reporting-engine.routes.prefix') (default: api/reporting).

Report CRUD

POST /api/reporting/run — Body:

{
  "definition": {
    "source": "mysql",
    "table": "orders",
    "fields": [{"column": "id"}, {"column": "amount"}],
    "filters": {
      "operator": "AND",
      "conditions": [{"field": "status", "operator": "=", "value": "completed"}]
    }
  }
}

POST /api/reporting — Body:

{
  "name": "Revenue by Status",
  "description": "Monthly revenue grouped by order status",
  "definition": { "source": "mysql", "table": "orders", "..." : "..." },
  "change_note": "Initial version"
}

Versioning

Sharing

Templates

Schema Introspection

RLS Policies

🕑 Versioning

Every PUT /{id} automatically snapshots the previous definition before overwriting. The current state is always preserved — nothing is lost on rollback.

// List version history
GET /api/reporting/{id}/versions
// → [{ version_number: 3, saved_by: "user-id", change_note: "Added tax column", created_at: "..." }]

// View a specific version
GET /api/reporting/{id}/versions/2

// Roll back to v2 (current state auto-saved as v4 first)
POST /api/reporting/{id}/versions/2/rollback

Pass a change_note when saving:

PUT /api/reporting/{id}
{ "name": "Revenue Report", "definition": {...}, "change_note": "Added margin column" }

👥 Sharing & Permissions

Share any report with individual users, roles, or teams. Three permission levels: view, edit, admin. Optional expiry date per grant.

// Grant view access to a role
POST /api/reporting/{id}/shares
{
  "shareable_type": "role",
  "shareable_id":   "analyst",
  "permission":     "view",
  "expires_at":     "2026-12-31"
}

// Grant edit access to a specific user
POST /api/reporting/{id}/shares
{ "shareable_type": "user", "shareable_id": "usr_123", "permission": "edit" }

// List all active shares
GET /api/reporting/{id}/shares

// Revoke a share
DELETE /api/reporting/{id}/shares/{shareId}

🔒 Row-Level Security

Policies stored in dhr_rls_policies are automatically AND-merged into every query for matching roles — no code changes required in individual reports.

// Analysts can only see completed orders
POST /api/reporting/rls-policies
{
  "role":       "analyst",
  "table_name": "orders",
  "where_clause": {
    "operator": "AND",
    "conditions": [
      { "field": "status", "operator": "=", "value": "completed" }
    ]
  }
}

• report_id: null — global policy (applies to all reports using that table)
• report_id: "uuid" — scoped to a specific report only

📋 Templates

// Save a report as a reusable template
POST /api/reporting/templates/from-report/{reportId}
{ "name": "Monthly Revenue Template", "category": "sales" }

// List templates (system + tenant)
GET /api/reporting/templates?category=sales
// → [{ id, name, category, is_system, definition }]

// Create a report from a template
POST /api/reporting/templates/{templateId}/instantiate
{ "name": "June 2026 Revenue" }
// → Returns a new report DTO with a fresh UUID

🛡️ Security Model

Every layer fires independently — compromising one does not bypass the others.

🔌 Custom Adapters

use Mostafax\ReportingEngine\Contracts\DataSourceInterface;
use Mostafax\ReportingEngine\Domain\DTOs\QueryDefinition;
use Mostafax\ReportingEngine\Domain\DTOs\ExecutionResult;

class PostgreSQLDataSource implements DataSourceInterface
{
    public function supports(string $sourceType): bool
    {
        return $sourceType === 'pgsql';
    }

    public function query(QueryDefinition $def): ExecutionResult
    {
        $rows = DB::connection('pgsql')
            ->table($def->table)
            ->get()
            ->toArray();

        return new ExecutionResult($rows, $total, $metadata);
    }

    public function aggregate(QueryDefinition $def): ExecutionResult { /* ... */ }
    public function count(QueryDefinition $def): int                 { /* ... */ }
}

// Register in AppServiceProvider::boot()
$this->app->make(DataSourceResolver::class)
    ->register('pgsql', new PostgreSQLDataSource());

⚙️ Configuration

// config/reporting-engine.php

return [

    // ========== Query Limits ==========
    'limits' => [
        'max_rows'              => 10_000,
        'max_per_page'          => 500,
        'max_execution_seconds' => 30,
        'max_joins'             => 5,
        'max_conditions'        => 20,
        'max_aggregations'      => 10,
    ],

    // ========== Redis Cache ==========
    'cache' => [
        'enabled' => env('REPORTING_CACHE_ENABLED', true),
        'driver'  => 'redis',       // requires predis/predis
        'ttl'     => 300,           // seconds
    ],

    // ========== Multi-Tenancy ==========
    'multi_tenancy' => [
        'enabled'       => env('REPORTING_TENANT_ENABLED', false),
        'tenant_column' => env('REPORTING_TENANT_COLUMN', 'tenant_id'),
    ],

    // ========== Field ACL ==========
    'field_acl' => [
        'always_deny' => ['password', 'api_key', 'secret'],
        'role_deny'   => [
            'analyst' => ['ssn', 'credit_card'],
        ],
    ],

    // ========== Blade ==========
    'blade' => [
        'theme'       => 'auto',          // 'bootstrap' | 'tailwind' | 'auto'
        'rtl_locales' => ['ar', 'fa', 'he', 'ur'],
    ],

    // ========== Routes ==========
    'routes' => [
        'enabled'    => true,
        'prefix'     => env('REPORTING_ROUTE_PREFIX', 'api/reporting'),
        'middleware' => array_filter(['api', env('REPORTING_AUTH_MIDDLEWARE', 'auth:sanctum')]),
    ],

];

🗄️ Database Tables

🏛️ Architecture

The 8-step hardened pipeline fires on every report — ad-hoc or saved.

ReportEngine::run($dsl)
  01  DslParser            → raw array → typed, immutable QueryDefinition (MD5-hashed)
  02  QueryValidator       → enforce limits + operator/aggregation whitelist
  03  QuerySanitizer       → identifier regex + FormulaLexer character allowlist
  04  FieldACL + RLS       → strip denied fields; AND-merge RLS WHERE policies
  05  Redis Cache          → GET by definition hash (tenant-scoped key)
  06  DataSourceResolver   → MySQLDataSource | MongoDataSource | custom
  07  Execute              → data + total + metadata in one round-trip
  08  Cache PUT + Event    → store result; dispatch ReportExecuted event

// Export — streamed, no temp files
use Mostafax\ReportingEngine\Application\Services\ExportService;

return app(ExportService::class)->exportById(
    reportId:  $id,
    format:    $request->query('format', 'csv'),  // csv | xlsx | json
    userRoles: $request->user()->roles(),
);

// Clone a report
POST /api/reporting/{id}/clone
{ "name": "Q2 Revenue Report" }
// → New UUID, "Copy of…" name, same definition, shares not copied

❓ FAQ

Q: How do I change the API route prefix?

// config/reporting-engine.php
'routes' => ['prefix' => 'api/v2/reports']

Q: How do I disable authentication in development?

REPORTING_AUTH_MIDDLEWARE=

Q: Can I use a non-default Redis connection?

// config/reporting-engine.php
'cache' => [
    'driver'     => 'redis',
    'connection' => 'reporting',  // any connection from config/database.php
],

Q: How do I add a field to the global deny list?

// config/reporting-engine.php
'field_acl' => [
    'always_deny' => ['password', 'api_key', 'secret', 'stripe_key'],
],

Q: Does it work with PostgreSQL?
Not out-of-the-box, but you can write a custom adapter in ~30 lines. See Custom Adapters above.

Q: How do I use it with multi-tenancy?

REPORTING_TENANT_ENABLED=true
REPORTING_TENANT_COLUMN=tenant_id

Bind current_tenant_id in a middleware:

app()->bind('current_tenant_id', fn () => auth()->user()->tenant_id);

📄 License

MIT © 2026 Mostafa Elbayyar

Built with ❤️ for the Laravel community

⭐ Star on GitHub · 🐛 Report Bug · 💡 Request Feature