Natural language to SQL: how can staff query business data safely?

Yes, if a narrow reporting service controls access, execution and the meaning of its answers. Start with approved data and questions whose results staff can check. For recurring questions with stable definitions, a governed report is the stronger starting point.

What does a read-only natural language to SQL example look like?

Start with a synthetic reporting view and an analyst-approved interpretation. Natural language to SQL means translating a question into a database query; the surrounding service must still authorise and validate it.

Suppose a user asks, “How many open jobs were created in September 2026, by region?” Agree that September uses the reporting system's recorded timezone and that “open” is the current status in this snapshot. The approved view contains only records the caller may read.

SELECT region, COUNT(*) AS open_jobs
FROM reporting.authorised_jobs
WHERE created_at >= $1
  AND created_at < $2
  AND status = $3
GROUP BY region
ORDER BY region;

The trusted service supplies typed period boundaries and the value open as bound parameters. It derives the caller's scope from verified identity. This illustrative query assumes the reporting view and entitlements already exist; it is not an installation script or a tested permission boundary.

If the user means jobs that were open at the end of September, a current-status view cannot establish that history. Ask for clarification or use an approved historical measure. A successful query against the wrong definition would still give the wrong answer.

Use the query reference-case template to record expected totals, boundary dates, ambiguity and denied-user cases before testing. Compare with saved reports and analyst-written SQL on the same snapshot.

Which business questions genuinely require flexible queries rather than saved reports?

Flexible queries are worth considering when the combinations of filters and comparisons change often. Start by collecting real questions from the intended staff group, including questions an analyst currently has to clarify.

A hypothetical operations team might compare overdue work across regions, service categories and periods. A fixed weekly total needs much less flexibility. Separate these cases before deciding whether generated SQL belongs in the workflow.

The strongest countercase is a maintained dashboard with agreed measures. Staff can reuse a reviewed calculation without asking a model to reconstruct it. This is an editorial recommendation about operating effort, not a measured claim that dashboards always cost less.

Compare the proposed assistant with both saved reports and an analyst-assisted route. Record how much clarification and checking each question needs. If most questions reduce to a small set of filters, expose those filters through existing reports. Natural language may still help users find the right report without generating database queries.

What views, fields and row policies should be visible to the query service?

Expose only approved reporting subjects and fields, with a trusted mapping from staff identity to permitted records. A view is a stored query that can present a simpler reporting interface.

PostgreSQL 18 distinguishes view-owner permissions from the invoking user's permissions. A security-invoker view uses the caller's underlying privileges and row policies. It also requires access to the underlying relations, so the query service must separately restrict which relations generated SQL may address.

Row-level security can filter records by role. With row security enabled, the absence of an applicable policy means access is denied. Superusers and roles with BYPASSRLS bypass it; table owners normally do too.

Use a non-owner reporting role without those bypass privileges. Have the database owner review grants, view ownership and policies together. Do not let a model choose the authenticated user or company scope.

Keep sensitive subjects out of the initial pilot where small-group totals could reveal information indirectly. Test alternating users on reused connections, including revocation of access. A correct policy on paper does not establish that connection pooling preserves it.

How do we prevent generated SQL from bypassing ERP permissions or reaching production write access?

Keep generated SQL away from the production ERP database and enforce read permissions at the database boundary. Read-only transactions add protection against changes; they do not decide which information a person may see.

Odoo's 19.0 security documentation warns that direct cursor access bypasses ORM features, including access rights. The ORM is the application layer that applies record and field behaviour. A database copy does not automatically carry those application rules with it.

For this pilot, use a separate reporting dataset with explicitly approved entitlements. Exclude ERP-derived data until its owner can demonstrate equivalent or narrower visibility. Include company restrictions, stale exports and permission changes in that assessment.

If current ERP rules must decide every answer, use an application-mediated reporting route instead. That is a different design from generated SQL against a reporting database. Avoid silently switching between them when a query fails.

The reporting credential should have no production write authority. Keep credentials outside prompts, restrict service network access and define retention for question logs, query results and backups. Local model execution alone establishes none of those controls.

What query parsing, time limits and resource controls should be applied outside the model?

Validate a parsed query against a small supported grammar, then execute it with independent permissions and limits. Checking whether text begins with SELECT is insufficient for the proposed boundary.

Ollama structured outputs can constrain an answer to a JSON schema. That helps package a proposed query and assumptions. It does not establish that the SQL is authorised or meaningful. OWASP's output-handling guidance specifically warns about passing generated SQL downstream without validation.

The proposed validator would resolve relations, columns, functions and operators against an allowlist. It would reject multiple statements, modifying subqueries, locking clauses and unsupported syntax. Bind filter values separately; parameter binding does not authorise generated identifiers or joins.

PostgreSQL documents separate statement and lock timeouts. Configure them for the reporting connection, while preventing generated queries from changing settings. Add service limits for concurrency, response size and retries.

Review temporary-file use and memory separately. PostgreSQL's resource documentation explains that work_mem applies to operations and can multiply across queries and workers. A small result can still require substantial processing. These controls require workload testing before selecting limits.

How do we verify that a syntactically valid query uses the correct joins, dates and business definitions?

Compare results against analyst-reviewed queries and definitions on a controlled dataset. Successful execution establishes neither the intended measure nor the correct join.

Consider the hypothetical question, “What were sales last quarter?” The reporting owner must settle the date basis, quarter boundaries, currency, GST treatment and treatment of credits. If those choices are unresolved, the interface should request clarification before executing.

Create reference cases with duplicate-producing joins, missing records, refunds, boundary dates and empty results. Include paraphrases and unsupported questions. Record the expected result and permitted scope before running the assistant.

A bounded pilot could cover one reporting domain and one staff group using non-production records. Inputs would be questions, approved schema descriptions and a versioned measure dictionary. Outputs would be capped result tables with execution metadata. The reporting owner signs off meaning; the database owner signs off permissions and resource limits.

Acceptance should require every permission-negative case to be denied, reference calculations to match, and ambiguous questions to trigger clarification. Record latency and concurrency thresholds before testing. A failure blocks wider access until corrected and retested.

What should users see to check the result and challenge an incorrect interpretation?

Show the interpreted measure, filters and source freshness beside the result, with a route to the reporting owner. Derive these details from validated execution metadata wherever possible.

Include date boundaries, timezone, currency, company scope and whether the result was truncated. Provide a query identifier and accessible calculation detail. Compare every number in the narrative with the returned values. Suppress an explanation that changes those values, and distinguish an empty result from an execution failure.

The pilot's operating cost is integration effort plus data maintenance, compute, security review and ongoing answer review. Measure these inputs in the pilot. Hardware prices, staff rates, savings and user capacity remain unknown; released time does not automatically become a cash saving.

The service owner needs a disable switch and a credential-revocation procedure. Rollback should return staff to approved reports and the analyst queue. Recheck the reference suite after model, parser, policy or schema changes. Retain enough evidence to investigate a challenged answer without creating an unrestricted archive of business data.

Proceed only when changing questions justify this maintenance and the access and correctness gates pass. Otherwise, keep the governed reporting route.