README

Let anyone on your team ask your database questions in plain language — safely.

Querai is a Laravel package that turns natural language into read-only SQL, runs it against your database, and returns answers a human can actually use. No SQL client, no BI tool, no ad-hoc queries in production.

What you can use it for

Admin & support — "How many orders did customer X place last month?" without pinging a developer.
Internal analytics — quick counts, lists, and breakdowns from real data, not exports.
Multi-tenant SaaS — one isolated client per customer DB, with its own AI config and domain hints.
Embedded in your app — use the facade in controllers, jobs, or the built-in chat UI behind your existing auth.

Why Querai

Safe by default — only SELECT, blocked DDL/DML, row limits, optional table exclusions.
Your schema, your rules — domain hints in config teach the AI how your tables relate.
Production-ready — retries on bad SQL, paginated answers for large result sets, conversation context.
Provider-agnostic — OpenAI, Azure OpenAI, Gemini, or Anthropic.

Ask in Swedish, English, or any language — answers follow the question.

MIT licensed.

How it works

You: "How many orders did we get last month?"
  → AI generates SELECT … (schema-aware)
  → SqlGuard validates (SELECT only, no DDL/DML)
  → Query runs on your DB (with row limit)
  → AI formats the answer for humans

If the SQL fails, Querai sends the error back to the AI and retries (configurable).

Without domain hints, the AI only sees table/column names and will often guess wrong JOINs (e.g. linking orders to the wrong user_id). Hints in config/querai.php tell it how your app actually works.

Requirements

PHP 8.2+
Laravel 11 or 12
A database connection Laravel already uses
An AI API key (OpenAI, Azure OpenAI, Gemini, or Anthropic)

Installation

composer require jake142/querai

Publish config:

php artisan vendor:publish --tag=querai-config

Configure domain hints in config/querai.php — this is the most important step for accurate SQL:

'hints' => [
    'enabled' => true,
    'text' => <<<'HINTS'
- A customer is a row in users; find by users.email
- An order belongs to a customer: orders.user_id = users.id
- "Revenue" means SUM(orders.total) where orders.status = 'completed'
- Do NOT use the audit_log table unless the question is about audit events
HINTS,
],

Write short, factual rules: entity lookups, how tables relate, business terms, and tables to avoid. Paste a working SQL query from your app if a join is non-obvious.

Add to .env:

QUERAI_DB_CONNECTION=mysql

QUERAI_AI_PROVIDER=openai
OPENAI_API_KEY=sk-...

# Optional
QUERAI_MAX_ROWS=200
QUERAI_MAX_ATTEMPTS=3
QUERAI_CONVERSATION_ENABLED=true
QUERAI_UI_ENABLED=true

Extract and cache your schema once (recommended after migrations):

php artisan querai:schema
# Re-extract after schema changes:
php artisan querai:schema --fresh

Usage

Facade / client

use Querai\Facades\Querai;

$result = Querai::ask('How many active users do we have?');

echo $result->answer;      // Human-readable reply
echo $result->sql;         // SQL that was executed
echo $result->rowCount;    // Number of rows returned
echo $result->attempts;    // Query attempts (retries on error)

Conversation context

Pass the same conversation_id across calls to keep follow-up context (cached, TTL configurable):

$id = 'user-session-abc';

Querai::ask('How many orders last month?', $id);
Querai::ask('Break that down by country', $id);

Large results — continue / load more

When a query returns more rows than response.threshold (default 15), the full result set is cached and the answer is split into pages.

$result = Querai::ask('List all products with stock below 10');

if ($result->hasMore) {
    $next = Querai::continue($result->responseId);
    echo $next->answer; // next batch, human-readable
}

Response fields:

Field	Description
`response_id`	Use with `continue()` while `has_more` is true
`has_more`	More pages available
`page` / `total_pages`	Current page of the formatted answer

QUERAI_RESPONSE_CACHE_ENABLED=true
QUERAI_RESPONSE_THRESHOLD=15
QUERAI_RESPONSE_PAGE_SIZE=15
QUERAI_RESPONSE_TTL=60

UI: a Load more button appears automatically when has_more is true.

Controller / API

use Querai\Facades\Querai;

$result = Querai::ask($request->input('question'));

return response()->json($result->toArray());

Domain hints

Hints are sent to the AI on every question, together with a compact schema. They prevent wrong guesses when column names are ambiguous (e.g. users vs customers, or several foreign keys to the same table).

What to include

Topic	Example hint
Lookups	find a customer by `users.email`
Relationships	`orders.user_id` → `users.id`
Business terms	"sale" = `orders` row with `status = 'completed'`
Anti-patterns	do not use `legacy_orders` — data lives in `orders`

What to avoid

Long prose or full API docs — keep under hints.max_chars (default 8000)
Secrets or PII in hints

Per-tenant (different DB + hints per customer):

$client = Querai::configure()
    ->namespace('tenant:'.$tenant->id)
    ->connection('tenant_'.$tenant->id)
    ->hints("customer: users.email\norders: orders.user_id = users.id")
    ->make();

After changing hints: php artisan config:clear. No need to re-run querai:schema.

Isolated clients (multi-tenant / per-customer DB)

The default facade uses config/querai.php (one DB + one AI setup). For apps where each customer has their own database, build a dedicated client per tenant:

use Querai\Facades\Querai;

$client = Querai::configure()
    ->namespace('tenant:'.$tenant->id)   // isolates conversation/response/schema cache
    ->database([                          // register tenant DB for this request
        'driver' => 'mysql',
        'host' => $tenant->db_host,
        'database' => $tenant->db_name,
        'username' => $tenant->db_user,
        'password' => $tenant->db_password,
    ])
    ->ai([
        'provider' => 'openai',
        'openai' => [
            'api_key' => $tenant->openai_api_key ?? config('querai.ai.openai.api_key'),
            'model' => 'gpt-4o-mini',
        ],
    ])
    ->make();

$result = $client->ask('How many orders this week?');

Use an existing Laravel connection (if you already register tenant connections in your app):

$client = Querai::configure()
    ->namespace('tenant:'.$tenant->id)
    ->connection('tenant_'.$tenant->id)
    ->ai([/* ... */])
    ->make();

Store the client on a service, request attribute, or resolve per request — each instance only talks to its DB and its AI config. Cache keys are namespaced so tenants never share conversation or paginated results.

Optional per-tenant overrides:

->security(['excluded_tables' => ['internal_audit']])
->conversation(['ttl_minutes' => 30])

AI providers

Set QUERAI_AI_PROVIDER to one of: openai, azure, gemini, anthropic.

Provider	Env vars
OpenAI	`OPENAI_API_KEY`, optional `QUERAI_OPENAI_MODEL`, `OPENAI_BASE_URL`
Azure	`AZURE_OPENAI_API_KEY`, `AZURE_OPENAI_ENDPOINT`, `AZURE_OPENAI_DEPLOYMENT`
Gemini	`GEMINI_API_KEY`, optional `QUERAI_GEMINI_MODEL`
Anthropic	`ANTHROPIC_API_KEY`, optional `QUERAI_ANTHROPIC_MODEL`

Security (important)

Querai is designed for read-only analytics, not arbitrary SQL execution.

Built-in protections

SqlGuard – Only SELECT is allowed. Blocks INSERT, UPDATE, DELETE, DROP, TRUNCATE, ALTER, multiple statements, etc.
Row limit – Appends LIMIT if missing (QUERAI_MAX_ROWS, default 200).
Excluded tables – Tables never appear in schema or queries:

// config/querai.php
'security' => [
    'excluded_tables' => ['users', 'password_reset_tokens', 'sessions'],
    'excluded_column_patterns' => [
        '/password/i',
        '/secret/i',
        '/token/i',
    ],
],

Recommended hardening

Use a read-only database user for the Querai connection (MySQL: GRANT SELECT ON db.* TO 'querai'@'%').
Point QUERAI_DB_CONNECTION at a replica if you have one.
Never expose the UI without authentication (see below).
Add rate limiting on /querai/ask in your app if the UI or API is used heavily.
Monitor AI API costs; each question uses at least two AI calls (SQL + formatting).

Admin UI

A minimal chat UI is included at /querai (prefix configurable).

It is not public by default. Routes use Laravel middleware from config:

'ui' => [
    'enabled' => true,
    'prefix' => 'querai',
    'middleware' => ['web', 'auth'],  // your admin guard
    'gate' => 'viewQuerai',           // optional ability
],

Protect with your existing auth

Option A – middleware only (default web + auth):

'middleware' => ['web', 'auth:admin'],

Option B – Gate / policy (fine-grained):

In App\Providers\AuthServiceProvider:

Gate::define('viewQuerai', fn ($user) => $user->is_admin);

Set in config:

'gate' => 'viewQuerai',

The package registers a placeholder gate if missing; override it in your app.

Disable UI entirely:

QUERAI_UI_ENABLED=false

Publish views to customize:

php artisan vendor:publish --tag=querai-views

Configuration reference

Key	Description
`connection`	Laravel DB connection name
`ai.provider`	`openai`, `azure`, `gemini`, `anthropic`
`security.excluded_tables`	Tables hidden from AI
`security.excluded_column_patterns`	Regex patterns for sensitive columns
`security.max_rows`	Max rows per query
`security.blocked_keywords`	Extra blocked SQL keywords
`retries.max_attempts`	SQL generation retries on error
`conversation.enabled`	Multi-turn context cache
`conversation.ttl_minutes`	Conversation cache TTL
`response.threshold`	Row count before paginating answers
`response.page_size`	Rows per page sent to AI
`response.ttl_minutes`	Cached result session TTL
`schema.cache`	Cache extracted schema
`schema.compact`	Compact schema format (fewer tokens)
`schema.path`	JSON schema file path
`hints.enabled`	Send hints to AI (default true)
`hints.text`	Domain knowledge for SQL generation — configure this
`hints.max_chars`	Max characters sent to AI
`ui.middleware`	Route middleware stack
`ui.gate`	Optional Gate ability

Artisan commands

Command	Description
`php artisan querai:schema`	Extract DB schema to cache
`php artisan querai:schema --fresh`	Force re-extraction

Error handling & retries

When a generated query fails (syntax, unknown column, etc.), Querai:

Captures the database error message
Asks the AI to fix the SQL
Retries up to retries.max_attempts (default 3)

Disable retries:

'retries' => ['enabled' => false],

Conversation cache

When enabled, user/assistant messages are stored in Laravel cache so follow-up questions understand prior context.

QUERAI_CONVERSATION_ENABLED=true
QUERAI_CONVERSATION_TTL=60
QUERAI_CONVERSATION_CACHE_STORE=redis

Things to consider in production

Topic	Suggestion
DB access	Read-only DB user + optional replica
Secrets	Exclude `users`, tokens, payment tables
Auth	`auth` middleware + Gate for UI
Rate limits	`throttle` middleware on ask endpoint
Schema drift	Run `querai:schema --fresh` after migrations
AI costs	Cache schema; limit UI to admins
Compliance	Log questions/SQL if your policy requires audit trails (add in your app)

License

MIT. See LICENSE.

jake142 / querai

Maintainers

Package info

Statistics

Security