Reporting AI

Natural language to SQL reporting module enabling conversational data queries, automated report generation, and cross-module analytics.

Features

• Natural Language Queries - Ask questions in plain English, get SQL-powered answers
• SQL Generation - AI automatically generates optimized SQL from natural language
• Automated Reports - Schedule and generate reports automatically
• Cross-Module Analytics - Query data across multiple modules seamlessly
• Query History - Save and reuse frequently asked queries

Requirements

Requirement	Details
Dependencies	AICore
PHP Version	8.2+
AI Provider	At least one AI provider configured in AI Core
Database	MySQL 8.0+ or PostgreSQL 13+

Installation

1. Ensure AI Core module is installed and enabled
2. Enable the Reporting AI module in Settings > Modules
3. Configure database schema access and query limits

Configuration

Navigate to Settings > AI Core > Reporting AI to configure:

• Allowed Tables - Configure which tables can be queried
• Query Limits - Set maximum rows and execution time
• Caching - Enable query result caching
• Saved Queries - Manage organization-wide saved queries

Usage

Natural Language Queries

Ask questions in plain language:

• "Show me all employees hired in the last 6 months"
• "What's the total revenue by product category this quarter?"
• "List customers who haven't ordered in 90 days"
• "Compare attendance rates by department"

Programmatic Access

use Modules\ReportingAI\Services\ReportingAIService;

$reporting = app(ReportingAIService::class);

// Natural language query
$result = $reporting->query([
    'question' => 'Show me top 10 customers by revenue this year',
    'format' => 'table',
]);

// View generated SQL
$sql = $reporting->generateSQL([
    'question' => 'Monthly sales trend for the past year',
    'explain' => true,
]);

// Schedule automated report
$reporting->scheduleReport([
    'query' => 'Weekly sales summary by region',
    'schedule' => 'every_monday_9am',
    'recipients' => ['[email protected]'],
    'format' => 'pdf',
]);

Cross-Module Queries

$result = $reporting->query([
    'question' => 'Show employees with pending leave requests who have overdue tasks',
    'modules' => ['Leave', 'TaskSystem'],
]);

API Endpoints

Method	Endpoint	Description
POST	/api/v1/reporting-ai/query	Execute natural language query
POST	/api/v1/reporting-ai/sql	Generate SQL from question
GET	/api/v1/reporting-ai/saved	List saved queries
POST	/api/v1/reporting-ai/schedule	Schedule automated report

Output Formats

Format	Description
Table	Interactive data table
Chart	Auto-generated visualization
PDF	Formatted PDF report
Excel	Excel spreadsheet export
JSON	Raw JSON data

Safety Features

• Read-Only Queries - Only SELECT statements are generated
• Permission Enforcement - Queries respect user data permissions
• Query Validation - AI-generated SQL is validated before execution
• Execution Limits - Timeouts and row limits prevent runaway queries

Notes

• AI learns from query patterns to improve over time
• Complex queries may require multiple iterations
• All queries are logged for audit purposes
• Supports joining data across module boundaries