PHPackages mostafax/ai-reporting-engine - PHPackages - PHPackages [Skip to content](#main-content)[PHPackages](/)[Directory](/)[Categories](/categories)[Trending](/trending)[Leaderboard](/leaderboard)[Changelog](/changelog)[Analyze](/analyze)[Collections](/collections)[Log in](/login)[Sign up](/register) 1. [Directory](/) 2. / 3. [Utility & Helpers](/categories/utility) 4. / 5. mostafax/ai-reporting-engine ActiveLibrary[Utility & Helpers](/categories/utility) mostafax/ai-reporting-engine ============================ AI Reporting Engine β€” turn natural-language prompts into report queries via a unified Regex + OpenAI pipeline, with prompt normalization, fingerprint caching, and a pluggable DSL builder. 00 Since Jul 5Compare [ Source](https://github.com/mostafax2/AI-Reporting-Engine)[ Packagist](https://packagist.org/packages/mostafax/ai-reporting-engine)[ RSS](/packages/mostafax-ai-reporting-engine/feed)WikiDiscussions Synced today READMEChangelogDependenciesVersions (1)Used By (0) πŸ€– AI Reporting Engine ===================== [](#-ai-reporting-engine) **Turn natural-language prompts into report queries β€” securely, cheaply, and deterministically.** Natural language β†’ normalized prompt β†’ cache β†’ regex β†’ OpenAI β†’ **unified intent** β†’ validated **DSL** β†’ execution. The AI never touches your database; it only proposes an *intent*. πŸ“– **[Documentation](https://mostafax2.github.io/AI-Reporting-Engine/)** Β· [Ψ§Ω„ΨΉΨ±Ψ¨ΩŠΨ©](https://mostafax2.github.io/AI-Reporting-Engine/ar.html) [![PHP](https://camo.githubusercontent.com/b6678a70d950ab60b00bae752ec36f600713eba52b81d9bb44076b127809ae37/68747470733a2f2f696d672e736869656c64732e696f2f62616467652f5048502d382e312532422d373737626234)](https://php.net)[![Laravel](https://camo.githubusercontent.com/636fbc4f1f4d86eaf69f851ee83ecc36ff70ce8d1212cf0b467c2698a047fd49/68747470733a2f2f696d672e736869656c64732e696f2f62616467652f4c61726176656c2d31302d2d31332d666632643230)](https://laravel.com)[![License](https://camo.githubusercontent.com/5caa455d8debc46fb23abbadb45a733a937f3910a73fc875c2f7820468e1bb54/68747470733a2f2f696d672e736869656c64732e696f2f62616467652f4c6963656e73652d4d49542d677265656e)](LICENSE) --- Table of Contents ----------------- [](#table-of-contents) - [Overview](#-overview) - [The Hybrid Intent-to-DSL Pattern](#-the-hybrid-intent-to-dsl-pattern) - [Feature Summary](#-feature-summary) - [Requirements](#-requirements) - [Quick Install](#-quick-install) - [Detailed Setup](#-detailed-setup) - [The Pipeline](#-the-pipeline) - [Usage](#-usage) - [The Unified Intent Object](#-the-unified-intent-object) - [Parsers](#-parsers) - [Prompt Normalization](#-prompt-normalization) - [Cache Storage](#-cache-storage) - [DSL Builder](#-dsl-builder) - [Configuration](#-configuration) - [Extending (SOLID)](#-extending-solid) - [Data Stores (MongoDB)](#-data-stores-mongodb) - [Logging](#-logging) - [Error Handling](#-error-handling) - [Testing](#-testing) - [Architecture Reference](#-architecture-reference) - [License](#-license) --- 🌟 Overview ---------- [](#-overview) **AI Reporting Engine** converts a natural-language question (Arabic or English) into an executable **reporting DSL** through a layered, cache-first pipeline. Unlike naive "prompt β†’ SQL" approaches, the AI is **only ever allowed to produce a structured intent** β€” never a query, never SQL, never DSL. A dedicated, centralized `DslBuilder` is the single component that turns that validated intent into an executable DSL. The engine produces a **DSL only**. Execution is delegated to [`mostafax/dynamic-hybrid-reporting-engine`](https://packagist.org/packages/mostafax/dynamic-hybrid-reporting-engine), fed by data replicated through [`mostafax/dual-layer-reporting-engine`](https://packagist.org/packages/mostafax/dual-layer-reporting-engine). Both are hard requirements and are pulled in automatically. > **Why not just prompt β†’ SQL?** Because it burns tokens on every request, reprocesses identical questions, lets the model hallucinate invalid or unsafe queries, and couples business rules to the AI. This package fixes all five. --- 🧠 The Hybrid Intent-to-DSL Pattern ---------------------------------- [](#-the-hybrid-intent-to-dsl-pattern) This package is a concrete implementation of the **Hybrid Intent-to-DSL Reporting Pattern** by Mostafa Elbayyar. **Core principles** 1. **AI never generates executable queries** β€” it produces a validated *intent* only. 2. **Intent before execution** β€” parsing and execution are fully separated. 3. **Cache first** β€” identical questions never re-hit the AI. 4. **Rules before AI** β€” a deterministic regex engine runs before any token is spent. 5. **Validation before execution** β€” no invalid intent or DSL ever reaches the database. **Data flow** ``` Prompt β†’ Normalized Prompt β†’ Intent β†’ Validated Intent β†’ DSL β†’ Validated DSL β†’ Execution ``` --- 🎯 Feature Summary ----------------- [](#-feature-summary) FeatureDescription🧹 **Prompt Normalization**Trim, lowercase, Arabic letter unification, tashkeel & punctuation removal β€” then a stable `sha256` fingerprintπŸ”‘ **Fingerprint Cache**Equivalent prompts share one fingerprint; a repeated question skips parsing entirely and costs **zero tokens**βš™οΈ **Regex Parser**Deterministic, token-free. Matches editable MongoDB mappings & column concepts β†’ a unified intent🧠 **OpenAI Parser**Runs **only** when regex misses. Returns strict JSON β€” never DSL, never SQL. Tracks latency + token usage🎯 **Unified Intent Object**Both parsers emit the identical `IntermediateQuery`; the rest of the system is source-agnosticπŸ—οΈ **Central DSL Builder**The **single** place that builds an executable DSL. No parser ever builds DSL directlyπŸ”€ **Method Switch**`auto` (regex β†’ OpenAI), `regex` (no tokens), or `openai` β€” runtime-configurableπŸƒ **MongoDB Stores**Editable regex mappings, column concepts, and the prompt/DSL cache β€” all runtime-editable🧩 **Pluggable Resolvers**Resolve entity names ("class 1-A") to ids for filtering, via a swappable `EntityResolverInterface`πŸ”Œ **SOLID / DI**Every collaborator sits behind an interface β€” swap any piece via a container binding🌐 **Arabic-aware**First-class Arabic normalization so "ΨΉΨ―Ψ― Ψ§Ω„Ψ·Ω„Ψ§Ψ¨" and "ΨΉΨ―Ψ― الطُلّاب" hit the same cache entryπŸ“Š **Execution Logging**Parser used, cache hit, execution ms, OpenAI latency, token usage, and the generated DSL--- πŸ“‹ Requirements -------------- [](#-requirements) ### Required [](#required) DependencyVersionPHP`8.1+`Laravel`10 / 11 / 12 / 13``mostafax/dynamic-hybrid-reporting-engine``dev-main` (executes the DSL)`mostafax/dual-layer-reporting-engine``dev-main` (replicates data for reporting)### Suggested [](#suggested) PackagePurpose`mongodb/laravel-mongodb`MongoDB-backed mapping & prompt-cache stores (`^4.0 | ^5.0`)--- ⚑ Quick Install --------------- [](#-quick-install) ``` # 1. Install the package (both reporting engines are pulled in automatically) composer require mostafax/ai-reporting-engine # 2. Publish the config php artisan vendor:publish --tag=ai-reporting-config ``` **Done.** Inject `ReportQueryPipeline`, call `process($prompt)`, and hand the returned DSL to the reporting engine. --- πŸ”§ Detailed Setup ---------------- [](#-detailed-setup) ### Step 1 β€” Install [](#step-1--install) ``` composer require mostafax/ai-reporting-engine ``` The service provider is auto-discovered (`AiReportingEngineServiceProvider`) and binds every interface to a sensible default. ### Step 2 β€” Publish config [](#step-2--publish-config) ``` php artisan vendor:publish --tag=ai-reporting-config # β†’ config/ai-reporting.php ``` ### Step 3 β€” Environment variables [](#step-3--environment-variables) ``` AI_REPORTING_ENABLED=true AI_REPORTING_METHOD=auto # auto | regex | openai OPENAI_API_KEY=sk-proj-... OPENAI_MODEL=gpt-4o-mini OPENAI_TIMEOUT=30 AI_REPORTING_MONGO_CONNECTION=mongodb ``` ### Step 4 β€” Seed the regex mappings (host-defined) [](#step-4--seed-the-regex-mappings-host-defined) The regex parser reads section mappings and column concepts from MongoDB (`ai_report_mappings`, `ai_column_concepts`). Seed them once from your domain schema, then edit them at runtime without a deploy. ### Step 5 β€” (Optional) Bind host implementations [](#step-5--optional-bind-host-implementations) To enable entity lookups (e.g. class-name β†’ `class_id`) or a DB-backed AI config, bind your own implementations β€” see [Extending](#-extending-solid). --- πŸ”€ The Pipeline -------------- [](#-the-pipeline) Every prompt passes through the same 7-step pipeline before a single DB call: \#StepComponentResponsibility01Normalize`PromptNormalizer`Canonicalize text (Arabic, case, punctuation) β†’ `fingerprint`02Cache Lookup`MongoPromptCache`Hit β†’ return stored DSL, **skip** regex + OpenAI03Regex`RegexParser`Token-free match β†’ `IntermediateQuery`04OpenAI`OpenAiParser`On regex miss β†’ strict JSON β†’ `IntermediateQuery`05Build DSL`DslBuilder`Validate + map + build the executable DSL06Cache Store`MongoPromptCache`Persist intent + DSL (never rows)07Execute`ReportEngine`Run the DSL β€” the **unchanged** reporting engine`ReportQueryPipeline` orchestrates steps 1–6 and returns the DSL; step 7 is your call. --- πŸš€ Usage ------- [](#-usage) ``` use Mostafax\AiReportingEngine\ReportQueryPipeline; use Mostafax\ReportingEngine\Core\Engine\ReportEngine; public function search(Request $request, ReportQueryPipeline $pipeline, ReportEngine $engine) { $result = $pipeline->process($request->string('question')); // [ // 'source' => 'regex' | 'openai' | 'cache', // 'origin' => the engine that first produced it, // 'collection' => 'students', // 'query' => [...unified intent...], // 'dsl' => [...executable DSL...], // 'cached' => bool, // ] $rows = $engine->run($result['dsl']); // execute via the reporting engine return response()->json([ 'data' => $rows->data, 'source' => $result['source'], ]); } ``` Unrecognized prompts throw `ReportParsingException` β€” catch it and return a helpful message. --- 🎯 The Unified Intent Object --------------------------- [](#-the-unified-intent-object) Both parsers return the identical `IntermediateQuery`. The DSL builder and the rest of the app never know whether the source was regex or AI β€” only the parser sets `_source`. ``` { "module": "students", "operation": "list", "metrics": [], "dimensions": [], "filters": [ { "field": "class_id", "operator": "=", "value": 1 } ], "groupBy": [], "sort": [], "limit": 20, "dateRange": null, "projection": ["first_name_ar", "status"], "_source": "regex" } ``` --- βš™οΈ Parsers ---------- [](#️-parsers) ### RegexParser (deterministic, token-free) [](#regexparser-deterministic-token-free) - Matches section patterns and column concepts stored in MongoDB. - Prefers the **primary entity** (students/teachers/parents) over a container mentioned only as a filter β€” so *"students in class 1-A"* resolves to `students` filtered by `class_id`, not the classes report. - Extracts status filters (accepted, unpaid…), relative dates (today, this month…), free-text titles ("book *X*"), and explicit column requests ("names only"). - Resolves entity names to ids via a pluggable `EntityResolverInterface`. ### OpenAiParser (fallback only) [](#openaiparser-fallback-only) - Runs **only** when regex cannot match β€” keeping token cost near zero. - Returns strict JSON matching the intent shape; never DSL, never SQL. - Exposes `lastLatencyMs()` and `lastTokens()` for the execution log. - The system/schema prompt is host-provided via config, so the package stays domain-agnostic. ### Parse method [](#parse-method) MethodBehavior`auto`Regex first, OpenAI on miss β€” **recommended**`regex`Local regex only β€” no tokens, no network`openai`OpenAI only--- 🧹 Prompt Normalization ---------------------- [](#-prompt-normalization) `PromptNormalizer` canonicalizes prompts so equivalent phrasings share one cache entry: StepEffectTrim + collapse spaces`" ΨΉΨ―Ψ― Ψ§Ω„Ψ·Ω„Ψ§Ψ¨ "` β†’ `"ΨΉΨ―Ψ― Ψ§Ω„Ψ·Ω„Ψ§Ψ¨"`Arabic letter unify`Ψ£ Ψ₯ Ψ’ β†’ Ψ§`, `Ω‰ β†’ ي`, `Ψ© β†’ Ω‡`, `Ψ€ β†’ و`, `Ψ¦ β†’ ي`Strip tashkeel & tatweel`"Ψ§Ω„Ψ·ΩΩ‘Ω„ΩŽΩ‘Ψ§Ψ¨"` β†’ `"Ψ§Ω„Ψ·Ω„Ψ§Ψ¨"`LowercaseLatin case-foldingStrip punctuationkeeps letters, digits, spacesFingerprint`sha256(normalized)` β€” the cache key--- πŸ’Ύ Cache Storage --------------- [](#-cache-storage) Each cache entry keeps the parsed **intent only** β€” never result rows (data changes over time; rows are always fetched fresh). FieldDescription`question`Original prompt`normalized_prompt`Canonicalized prompt`question_hash``sha256` fingerprint (the key)`collection`Resolved module`source``regex` | `openai` | `cache``query`The unified intent`dsl`The generated DSL`created_at`First seen`last_used_at`Last hit`usage_count`Hit counterDefault TTL is 24 hours (configurable). --- πŸ—οΈ DSL Builder -------------- [](#️-dsl-builder) The single, centralized place that turns a validated `IntermediateQuery` into a ReportingEngine DSL. **No parser builds DSL directly.** - Rejects an invalid intent with `ReportParsingException`. - Keeps only flat (non-relation) columns for MongoDB documents. - Wraps filters in a `FilterGroup` (`{operator, conditions}`) so the DSL is **directly executable** by the reporting engine. - Wraps `like` values in `%…%`. ``` [ 'source' => 'mongodb', 'connection' => 'mongodb', 'table' => 'invoices', 'pagination' => ['page' => 1, 'per_page' => 20], 'fields' => ['invoice_number', 'status'], 'filters' => ['operator' => 'AND', 'conditions' => [ ['column' => 'status', 'operator' => '!=', 'value' => 'paid'], ]], ] ``` --- πŸ”§ Configuration --------------- [](#-configuration) `config/ai-reporting.php`: ``` return [ 'enabled' => env('AI_REPORTING_ENABLED', true), 'parse_method' => env('AI_REPORTING_METHOD', 'auto'), // auto | regex | openai 'openai' => [ 'api_key' => env('OPENAI_API_KEY', ''), 'model' => env('OPENAI_MODEL', 'gpt-4o-mini'), 'timeout' => (int) env('OPENAI_TIMEOUT', 30), 'schema_prompt' => '…your collections + columns…', 'endpoint' => 'https://api.openai.com/v1/chat/completions', ], 'storage' => [ 'connection' => env('AI_REPORTING_MONGO_CONNECTION', 'mongodb'), 'cache_collection' => 'ai_queries', 'mappings_collection' => 'ai_report_mappings', 'concepts_collection' => 'ai_column_concepts', 'cache_ttl_hours' => 24, ], 'dsl' => [ 'source' => 'mongodb', 'connection' => 'mongodb', ], ]; ``` --- πŸ”Œ Extending (SOLID) ------------------- [](#-extending-solid) Every collaborator is an interface β€” bind your own in a service provider: InterfaceDefaultBind to override`PromptNormalizerInterface``PromptNormalizer`custom normalization`AiConfigInterface``ConfigAiConfig` (config)DB-backed settings`PromptCacheInterface``MongoPromptCache`Redis / SQL cache`MappingRepositoryInterface``MongoMappingRepository`your mapping store`EntityResolverInterface`*(none)*resolve `class` name β†’ id, etc.`PromptParserInterface``RegexParser`, `OpenAiParser`add a custom parser``` // Enable class-name β†’ class_id lookups $this->app->bind(EntityResolverInterface::class, MyClassResolver::class); // Read AI settings from your own store $this->app->singleton(AiConfigInterface::class, SystemSettingAiConfig::class); ``` --- πŸƒ Data Stores (MongoDB) ----------------------- [](#-data-stores-mongodb) Three collections back the engine (all runtime-editable): CollectionPurpose`ai_report_mappings`Section patterns + default projection + text-search column per module`ai_column_concepts`Keyword β†’ columns-per-module (e.g. "name" β†’ `first_name_ar`, `family_name_ar`)`ai_queries`Prompt/DSL cache (intent only)**Section mapping** ``` { "collection": "students", "patterns": "Ψ·Ψ§Ω„Ψ¨|Ψ·Ω„Ψ§Ψ¨|student|students", "projection": ["student_code", "first_name_ar", "status"], "text_column": "first_name_ar", "priority": 150, "is_active": true } ``` **Column concept** ``` { "label": "name", "patterns": "Ψ§Ψ³Ω…|Ψ£Ψ³Ω…Ψ§Ψ‘|name", "columns_per_collection": { "students": ["first_name_ar", "family_name_ar"], "teachers": ["name_ar"] }, "is_active": true } ``` --- πŸ“Š Logging --------- [](#-logging) Every generated query is logged with: ``` parser Β· cache_hit Β· execution_ms Β· openai_ms Β· openai_tokens Β· dsl ``` Use it to monitor token spend, cache-hit ratio, and slow prompts. --- πŸ›‘οΈ Error Handling ----------------- [](#️-error-handling) - **No match** (regex miss + AI unavailable/disabled) β†’ `ReportParsingException`. - **Invalid intent / DSL** β†’ `ReportParsingException` from `DslBuilder`. - **OpenAI failure / invalid JSON** β†’ logged, returns `null`, pipeline falls back per method. - **Cache/store failure** β†’ logged, never breaks generation. An invalid report is **never** executed. --- πŸ§ͺ Testing --------- [](#-testing) ``` composer install vendor/bin/phpunit ``` Unit tests cover the DSL builder (filter groups, `like` wrapping, validation) and can be extended with your own parser and normalization cases. --- πŸ“ Architecture Reference ------------------------ [](#-architecture-reference) ``` User Prompt β”‚ β–Ό Prompt Normalization (PromptNormalizer) β”‚ β–Ό Intent Cache Lookup (MongoPromptCache) β”Œβ”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”΄β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β” Cache Hit Cache Miss β”‚ β”‚ β–Ό β–Ό Return Stored DSL Regex Rule Engine (RegexParser) β”‚ β”Œβ”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”΄β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β” Match Found No Match β”‚ β”‚ β–Ό β–Ό Intent Object OpenAI Intent Parser (OpenAiParser) β””β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”¬β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”˜ β–Ό DSL Builder (DslBuilder β€” validates) β–Ό Cache Storage (MongoPromptCache) β–Ό Report Execution (ReportEngine β€” unchanged) ``` --- πŸ“„ License --------- [](#-license) MIT Β© Mostafa Elbayyar ### Health Score 9 β€” LowBetter than 0% of packages Maintenance20 Infrequent updates β€” may be unmaintained Popularity0 Limited adoption so far Community2 Small or concentrated contributor base Maturity11 Early-stage or recently created project How is this calculated?**Maintenance (25%)** β€” Last commit recency, latest release date, and issue-to-star ratio. Uses a 2-year decay window. **Popularity (30%)** β€” Total and monthly downloads, GitHub stars, and forks. Logarithmic scaling prevents top-heavy scores. **Community (15%)** β€” Contributors, dependents, forks, watchers, and maintainers. Measures real ecosystem engagement. **Maturity (30%)** β€” Project age, version count, PHP version support, and release stability. ### Community Maintainers ![](https://avatars.githubusercontent.com/u/146624906?v=4)[Mostafa Elbayyar](/maintainers/mostafax2)[@mostafax2](https://github.com/mostafax2) ### Embed Badge ![Health badge](/badges/mostafax-ai-reporting-engine/health.svg) ``` [![Health](https://phpackages.com/badges/mostafax-ai-reporting-engine/health.svg)](https://phpackages.com/packages/mostafax-ai-reporting-engine) ``` ### Alternatives [badges/poser Poser, add badges on your readme.. 14678.5k13](/packages/badges-poser) PHPackages Β© 2026 [Directory](/)[Categories](/categories)[Trending](/trending)[Changelog](/changelog)[Analyze](/analyze)