Dynamic Hybrid Reporting Engine

The Problem

Reporting Is Always the Hard Part

Every project eventually needs reports — but writing separate queries per source, per format, per tenant, is unsustainable.

😩 The Old Way One report = one custom class

⏳ MySQLRevenueReport.php+80 lines

⏳ MongoAnalyticsReport.php+110 lines

⏳ ExcelExportController.php+60 lines

⏳ CsvExportController.php+45 lines

...×12 reports

// Repeated for every report $query = DB::table('orders') ->join('customers', ...) ->where('tenant_id', $tenantId) ->groupBy('status') ->selectRaw('SUM(amount) as total...');

⚠ 1 new report = 3–5 new files every time

A new report requires a new PHP class from scratch every time
Separate, incompatible query logic for MySQL and MongoDB
Export logic (Excel, CSV, JSON) duplicated across controllers
No centralized validation — injection risks go unnoticed
Multi-tenant filtering manually bolted onto every query
Dashboard widgets have no standard data-binding contract
Caching copy-pasted inconsistently across different reports

The Solution

8-Step Execution Pipeline

Every report — regardless of source — passes through the same hardened pipeline before a single database call is made.

Entry

DSL Input

A JSON object (or PHP array) describes the report: source, table, fields, filters, aggregations, grouping, sorting, pagination.

Core

DslParser

DslParser converts raw input into a typed, immutable QueryDefinition value object with a stable MD5 hash.

Validation

QueryValidator

Enforces limits: max rows, max joins, max conditions, max aggregations. Rejects unknown operators before any DB call.

Security

QuerySanitizer + ACL

QuerySanitizer regex-whitelists every identifier. FieldAccessControl strips globally-denied and role-denied columns.

Cache

Redis Cache Probe

QueryCacheManager checks Redis by definition hash. Cache keys are tenant-scoped — one tenant's cache never leaks to another.

Routing

DataSourceResolver

Resolves the correct adapter from the registry: MySQLDataSource or MongoDataSource. Custom adapters can be registered at runtime.

Execution

Adapter Executes

MySQL uses Laravel Query Builder with correct LIMIT/OFFSET. MongoDB builds a $facet pipeline — data + totalCount in one round-trip.

Result

ExecutionResult + Event

Returns ExecutionResult with data, total, and ExecutionMetadata (time, memory, cache hit). Fires ReportExecuted event.

Unified DSL

One Schema — Two Engines

The same JSON structure works for both MySQL and MongoDB. Only the "source" field changes.

MySQL DSL

{ "source": "mysql", "table": "orders", "fields": [ { "column": "id", "alias": "order_id" }, { "column": "customer_name" } ], "aggregations": [ { "function": "sum", "column": "amount", "alias": "revenue" }, { "function": "count", "column": "id", "alias": "orders" } ], "filters": { "operator": "AND", "conditions": [ { "field": "status", "operator": "=", "value": "completed" }, { "operator": "OR", "conditions": [ { "field": "amount", "operator": ">", "value": 100 }, { "field": "is_premium", "operator": "=", "value": true } ] } ] }, "joins": [{ "type": "left", "table": "customers", "first": "orders.customer_id", "operator": "=", "second": "customers.id" }], "group_by": ["status"], "order_by": [{ "column": "revenue", "direction": "desc" }], "pagination": { "page": 1, "per_page": 25 } }

MongoDB DSL

{ "source": "mongodb", "table": "analytics", "fields": [ { "column": "userId" }, { "column": "event" } ], "aggregations": [ { "function": "sum", "column": "revenue", "alias": "total_revenue" }, { "function": "count_distinct", "column": "userId", "alias": "unique_users" } ], "filters": { "operator": "AND", "conditions": [ { "field": "status", "operator": "=", "value": "active" }, { "field": "ts", "operator": ">=", "value": "2024-01-01" } ] }, "group_by": ["userId"], "order_by": [{ "column": "total_revenue", "direction": "desc" }], "pagination": { "page": 1, "per_page": 50 } }

🔄 MongoDB adapter builds a $match → $group → $project → $sort → $facet pipeline automatically — data + totalCount in a single round-trip.

Features

Everything Built In — Nothing to Wire

🔀

Unified JSON DSL

One schema for MySQL and MongoDB — fields, filters (nested AND/OR), aggregations, joins, group-by, order-by, pagination.

🔌

Pluggable Adapters

Register custom data sources at runtime via DataSourceResolver::register(). Add PostgreSQL, Elasticsearch, or any HTTP API.

⚡

Redis Query Cache

Results cached by definition hash. Cache keys are tenant-scoped and tag-invalidated when a report is updated.

🔐

Injection-proof Security

Identifier regex whitelist on every column/table name. Values are always PDO-bound — never interpolated into SQL.

🛡️

Field-Level ACL

Global deny list + per-role deny list with wildcard patterns. Denied fields are silently stripped; denied filter fields throw immediately.

📤

3 Export Formats

CSV (UTF-8 BOM streaming), XLSX (PhpSpreadsheet), and JSON — all streamed directly to the client, no temp file needed.

🏢

Multi-tenant Ready

Tenant ID injected into every query automatically. MySQL gets a WHERE clause; MongoDB gets a $match stage — zero boilerplate.

🧩

Native Blade Components

Embed KPIs, charts, tables, filters, and export buttons in any Blade template with a single tag. Bootstrap & Tailwind. RTL ready.

⚡

Livewire Integration

Optional livewire:report-widget adds real-time filtering, sorting, and pagination — zero JavaScript required.

📊

Dashboard & Widgets

Domain entities for Dashboards and Widgets (table, bar chart, line chart, KPI card, heatmap). Each widget binds to a saved report.

📋

Execution Audit Log

dhr_report_executions records every run: ms, rows, memory, cache hit, status — full observability out of the box.

🎨

6 KPI Visual Themes

🍃

MongoDB $facet Pipeline

Auto-built $match → $group → $project → $sort → $facet — data + totalCount in one round-trip. BSONArray/ObjectId normalised automatically.

Developer Experience

From Zero to Report in Minutes

The Facade, the HTTP API, and the export endpoint — all work out of the box.

// Execute a report via the Facade — anywhere in your app
use Mostafax\ReportingEngine\Support\Facades\ReportingEngine;

$result = ReportingEngine::run([
    'source'       => 'mysql',
    'table'        => 'orders',
    'fields'       => [
        ['column' => 'id',    'alias' => 'order_id'],
        ['column' => 'customer_name'],
    ],
    'aggregations' => [
        ['function' => 'sum',   'column' => 'amount', 'alias' => 'revenue'],
        ['function' => 'count', 'column' => 'id',     'alias' => 'total'  ],
    ],
    'filters'      => [
        'operator'   => 'AND',
        'conditions' => [
            ['field' => 'status', 'operator' => '=', 'value' => 'completed'],
        ],
    ],
    'group_by'     => ['status'],
    'order_by'     => [['column' => 'revenue', 'direction' => 'desc']],
    'pagination'   => ['page' => 1, 'per_page' => 25],
]);

// $result is an ExecutionResult value object
$result->data;                      // array of rows
$result->total;                     // total count (without LIMIT)
$result->metadata->executionTimeMs; // e.g. 12.4
$result->metadata->cacheHit;        // true if served from Redis

<!-- KPI cards — variant: flat | gradient | dark | glass | minimal | bold -->
<x-reporting-engine::kpi-widget
    report="revenue-by-status"
    :cols="3"
    color="#0077A8"
    variant="glass"
/>

<!-- Bar / line / pie chart — SSR table fallback (no-JS) -->
<x-reporting-engine::chart-widget
    report="revenue-by-status"
    chart-type="bar"
    label-column="status"
    value-column="total_revenue"
    :height="280"
/>

<!-- MongoDB pie chart -->
<x-reporting-engine::chart-widget
    report="demo-mongo-revenue-by-channel"
    chart-type="pie"
    label-column="channel"
    value-column="total_revenue"
/>

<!-- Table with filter form + export buttons -->
<x-reporting-engine::report-filter report="orders" :inline="true" />
<x-reporting-engine::report-widget
    report="orders"
    :show-export="true"
    :show-filters="false"
    :per-page="15"
/>

<!-- Full saved dashboard -->
<x-dashboard slug="ceo-dashboard" :cols="2" />

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

// POST /api/reporting/run — ad-hoc execution
POST /api/reporting/run
Content-Type: application/json

{
  "definition": {
    "source": "mongodb",
    "table":  "analytics",
    "aggregations": [
      { "function": "count_distinct", "column": "userId", "alias": "unique_users" }
    ],
    "pagination": { "page": 1, "per_page": 10 }
  }
}

// POST /api/reporting — save a report
POST /api/reporting
{ "name": "Revenue by Status", "definition": { ... } }

// POST /api/reporting/{id}/run — execute a saved report
POST /api/reporting/{id}/run
{ "page": 2, "per_page": 50 }

// GET /api/reporting/{id}/export — stream a file download
GET  /api/reporting/{id}/export?format=xlsx
GET  /api/reporting/{id}/export?format=csv
GET  /api/reporting/{id}/export?format=json

// Implement a custom adapter — e.g. PostgreSQL
class PostgreSQLDataSource implements DataSourceInterface
{
    public function supports(string $sourceType): bool
    {
        return $sourceType === 'pgsql';
    }

    public function query(QueryDefinition $def): ExecutionResult
    {
        // Use the same QueryDefinition — build your own query
        $rows = DB::connection('pgsql')
                    ->table($def->table)
                    ->... ;

        return new ExecutionResult($rows, $total, $metadata);
    }
    // also implement aggregate() and count()
}

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

// Stream an export directly from a controller
use Mostafax\ReportingEngine\Application\Services\ExportService;

class ReportExportController
{
    public function __construct(
        private readonly ExportService $export,
    ) {}

    public function download(Request $req, string $reportId): StreamedResponse
    {
        // Streams directly — no temp file, no memory spike
        return $this->export->exportById(
            reportId:  $reportId,
            format:    $req->query('format', 'csv'), // csv | xlsx | json
            userRoles: $req->user()->roles(),
        );
    }
}

// The ExporterFactory is extensible — register custom formats:
$factory->register('parquet', new ParquetExporter());

Security Model

Defense in Depth — at Every Layer

Six independent security controls fire before the first database call is made.

Layer	Class	What It Protects Against
Identifier Injection	QuerySanitizer	Regex whitelist on every column/table name — `; DROP TABLE` is rejected before touching the builder
Value Injection	Laravel Query Builder	All values passed as PDO bindings — never string-interpolated into SQL or Mongo queries
Field Exposure	FieldAccessControl	Globally-denied fields (password, api_key, …) stripped silently; denied filter fields throw 422
Query Limits	QueryValidator	Max rows, joins, conditions, aggregations per query — prevents resource exhaustion
Rate Limiting	QueryLimitMiddleware	Per-user request budget via Laravel RateLimiter; remaining budget exposed in response headers
Tenant Isolation	ReportEngine + Builders	Tenant ID auto-appended to every query — one tenant can never read another's data

Installation

Up and Running in 3 Commands

Install, publish, migrate — then the REST API is live and the Facade is ready.

bash — laravel artisan

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

✓ Package installed successfully

# Required: Redis client (choose one)

$ composer require predis/predis

✓ predis/predis — pure-PHP Redis client installed

$ php artisan vendor:publish --tag=reporting-engine-config

✓ Config published → config/reporting-engine.php

$ php artisan vendor:publish --tag=reporting-engine-migrations

✓ Migrations published → database/migrations/

$ php artisan migrate

✓ dhr_reports created

✓ dhr_report_executions created

✓ dhr_dashboards created

✓ dhr_widgets created

# Optional: XLSX export + Livewire real-time widgets

$ composer require phpoffice/phpspreadsheet livewire/livewire

✓ Excel export + Livewire enabled

# Demo seeder — MySQL (80 orders) + MongoDB (120 events) + 5 saved reports

$ php artisan db:seed --class=ReportingEngineDemoSeeder

✓ demo_orders seeded (80 rows — MySQL)

✓ demo_events seeded (120 docs — MongoDB)

✓ dhr_reports: 5 demo reports saved

✓ Visit: /reporting-demo

# KPI widget — 6 visual variants, live-switchable:

<x-reporting-engine::kpi-widget report="id" variant="flat" />

<x-reporting-engine::kpi-widget report="id" variant="gradient" />

<x-reporting-engine::kpi-widget report="id" variant="dark" />

<x-reporting-engine::kpi-widget report="id" variant="glass" />

<x-reporting-engine::kpi-widget report="id" variant="minimal" />

<x-reporting-engine::kpi-widget report="id" variant="bold" />

# Other Blade components:

<x-reporting-engine::chart-widget report="id" chart-type="bar" />

<x-reporting-engine::report-widget report="id" :show-export="true" />

<x-dashboard slug="ceo-dashboard" />

<livewire:report-widget report="id" />

# REST API is now live at:

POST /api/reporting/run ← ad-hoc execution

POST /api/reporting ← save a report

POST /api/reporting/{id}/run ← execute saved report

GET /api/reporting/{id}/export ← stream export

Dynamic Hybrid
Reporting Engine