better_ai_report

Better AI Report helps trusted site staff get answers from your Drupal database without writing SQL or knowing table names. A user describes what they need in everyday language-for example, "all orders containing SKU ABC-123 in the last 30 days"-and the module guides them through a short conversation until a report is ready.

Unlike tools that let an AI write and run SQL directly, Better AI Report is built around a security-first pipeline. The AI never receives your actual data rows and never produces executable SQL. It only sees schema metadata for tables and columns you have explicitly allowed (names and types). It responds with a structured JSON report specification-which tables to use, which columns to show, filters, joins, and time ranges. A deterministic query guard validates that specification against per-role allow and deny lists, and the module builds a parameterized, read-only SELECT using Drupal's database API. Nothing runs until a user reviews a summary and clicks Approve and run.

If you are new to Drupal, think of this module as a guarded reporting assistant: you configure what data is in scope, grant access only to trusted roles, and staff can then explore that data safely through conversation instead of phpMyAdmin or custom SQL.

Features

Basic functionality

• Natural-language report requests with a multi-turn workflow: clarify questions, confirm a proposed report, approve, preview, and optionally download results.
• In-browser preview of results (with configurable row limits).
• CSV export of approved reports, with spreadsheet formula-injection protection and UTF-8 BOM for Excel.
• Grouped/aggregate reports: GROUP BY, COUNT, SUM, AVG, MIN, MAX, HAVING, and GROUP_CONCAT with a configurable separator.
• Automatic identity columns on simple listing reports (for example uid and email on user data).
• Unix timestamp columns (created, changed, access, etc.) formatted using your site date settings.

Security-focused design

• Schema only to the AI - The provider receives in-scope table and column names with type hints. No row data, credentials, or out-of-scope structures are ever included.
• No AI-authored SQL - The model outputs a structured specification, never a query string. A query guard validates every spec; the query builder uses Drupal's Select API with bound placeholders.
• Deny-by-default scope - Nothing is queryable until you add allowed entity types or tables. Sensitive tables and columns are denied in the default configuration (users, sessions, config, *.pass, and more).
• Per-role scopes - Global and per-role allow/deny lists; deny always wins over allow.
• Mandatory row-scope filters - Force row-level restrictions the prompt cannot bypass (for example, limit commerce_order rows to the current user).
• Human approval - Reports run only after explicit approval. CSV export requires an approved-and-run report; refining or sending a new message clears approval.
• Optional read-only database connection - Point reports at a MySQL user with SELECT only; misconfigured connections fail closed rather than falling back to the default connection.
• Rate limits, row caps, and audit logging - Bound provider cost and a watchdog trail of conversations, generated queries, executions, rejections, and CSV exports.
• Prompt-injection resistance - An evil prompt can at most produce a spec the guard rejects; it cannot widen access beyond the user's configured scope or inject SQL.

When and why to use it

• Commerce or membership sites where managers need ad hoc reports without developer involvement.
• Teams that want AI-assisted reporting but cannot send full database contents or trust AI-generated SQL.
• Organizations that need explicit data governance: which tables, columns, and roles can see what.
• Any site already using the AI module with OpenAI, Anthropic, Gemini, or another configured provider.

Example questions

• "Orders placed last week with more than three line items"
• "Users who registered this month but never logged in"
• "Content updated in the last 7 days, grouped by content type"
• "Products with low stock, filtered to my store's orders only" (with mandatory row-scope filters)

Post-Installation

There is no new content type and no text format to configure. After installation:

1. Configure an AI provider on the AI module settings page (API key and default chat provider).
2. Go to Configuration → System → Better AI Report (/admin/config/better-ai-report/settings).
3. Add allowed entity types and/or tables to the global scope (and optionally per-role scopes). Nothing is queryable until you explicitly allow it.
4. Review default denied tables and denied columns; tighten scope for your site.
5. Optionally set mandatory row-scope filters, maximum rows, rate limits, read-only database connection key, and audit logging.
6. Grant permissions at People → Permissions:
   • Administer Better AI Report - configuration only.
   • Generate Better AI reports - use the report builder (broad read access to everything in the effective allow-list; trusted staff only).
   • Bypass Better AI report row limit - optional, for exports beyond the row cap.
7. Use the module at Reports → Better AI Report (/admin/reports/better-ai-report).

Typical workflow

1. Describe the report you want.
2. The AI asks follow-up questions or proposes a report summary.
3. Click Approve and run (or Refine to adjust).
4. Preview results in the browser.
5. Download the full result set as CSV when ready.

Important considerations

• Reports query the database using configured allow/deny lists. They do not apply Drupal entity, field, or node access grants. Scope tables narrowly and grant the generate permission only to trusted staff.
• For defense in depth, define a read-only database connection in settings.php and set the connection key in module settings.
• Enable audit logging (on by default) to troubleshoot rejected reports via the better_ai_report dblog channel.

Additional Requirements

• Drupal 10.4+ or 11.
• PHP 8.1 or later.
• AI module (drupal/ai ^1.1 or ^2.0) with at least one configured AI provider (OpenAI, Anthropic, Gemini, etc.) and your own API key.
• Composer installation is recommended: composer require drupal/better_ai_report

No additional PHP libraries beyond what the AI module pulls in. No external APIs beyond the AI provider you configure.

Recommended modules/libraries

• AI - Required. Provides provider abstraction and API key management.
• Advanced Help - Browse the module README from the Drupal admin UI.
• Markdown filter - Renders README help with Markdown when Advanced Help is enabled.

For production hardening, configure a read-only MySQL user (SELECT only) in settings.php and reference it in Better AI Report settings-this is optional but strongly recommended.

Similar projects

AI Report

AI Report is the earlier natural-language reporting module for Drupal. Both modules let administrators ask questions in plain English and use the AI module to interpret them, but they take fundamentally different approaches to safety and workflow.

AI Report Better AI Report AI output Raw SQL string Structured JSON report specification Query execution AI-generated SQL run via $database->query() Parameterized Select built by Drupal's query API Data sent to AI Full database schema (all tables/columns) plus content types Only in-scope, non-redacted schema metadata-no row data Access control Permission grants broad access; AI chooses tables Deny-by-default allow/deny lists per role; deny wins Workflow One step: ask → run immediately Clarify → confirm → approve → run CSV export Not available Yes, post-approval with CSRF protection and formula sanitization Safeguards SELECT prefix check only Query guard, row caps, rate limits, audit log, optional read-only DB Maturity Marked work-in-progress; minimal service layer Production-oriented architecture with extensive test coverage

When AI Report may be enough: quick experiments on development sites where you accept sending the full schema to an LLM and trusting AI-written SQL with minimal guardrails.

When Better AI Report is the better fit: production sites that need governed data access, no row data sent to providers, no AI-executed SQL, explicit approval before running reports, CSV export, and operational controls (limits, logging, read-only connections).

Supporting this Module

Contributions are welcome through the issue queue. Bug reports, feature requests, and merge requests help improve the module for everyone.

Community Documentation

• Issue queue: drupal.org/project/issues/better_ai_report
• README (included with the module): installation, configuration, security model, FAQ, and troubleshooting.
• Configuration path: /admin/config/better-ai-report/settings
• Report builder: /admin/reports/better-ai-report

Security FAQ

• Can the AI inject or run arbitrary SQL? No. It only produces a JSON specification validated by a deterministic guard.
• Does site data leave my server? Only schema metadata for allowed tables/columns is sent to your configured AI provider, plus the user's natural-language messages. Query results are never sent to the AI except bounded error messages during automatic spec repair.
• Does it respect Drupal node/entity access? No. Scope is configured via table/column allow and deny lists. Grant the generate permission only to trusted roles.

The module ships with unit, kernel, and functional tests, including dedicated prompt-injection cases for the query guard.

License: GPL-2.0-or-later