Blog /
Replacing Static Filters with Natural Language: Designing a Streamlit + NLQueries Prototype
Published . Written for nlqueries-core 0.4.
streamlit python dashboards
Picture an internal sales dashboard that has slowly grown a row of dropdowns: tenant, region, month, order status. Every new question from finance means another filter, and eventually someone suggests the obvious fix. Replace the filter bar with a text box. Let people type "paid revenue for tenant A in October" and get the number back.
It's a good idea, and Streamlit makes the first version feel like an afternoon's work. The trouble is that those dropdowns were quietly doing security and correctness work. They limited which queries could exist, they made every request deliberate, and they never pretended a half-finished result was a full one. Remove them and your app must do that work explicitly. This post walks through the design of that app on top of NLQueries Core: submitting deliberately, deciding who may see what on every rerun, showing the SQL, and being honest about results.
Treat what follows as a design prototype, not a finished build. The complete setup and the end-to-end acceptance tests haven't been demonstrated, and the component checks described here don't establish a working deployment or any measured product advantage. The sources are the Streamlit docs and NLQueries Core 0.4.1 source and docs at a pinned commit.
What the dropdowns were really doing
A filter bar is a tiny query language with a closed vocabulary. The user can't ask for a table they shouldn't see, because no dropdown offers it. They can't fire off five requests while typing, because nothing happens until they pick a value. And because the dashboard's SQL was written once and reviewed, a zero on screen almost certainly meant zero.
A free-text box removes all three guarantees at once. So before writing any code, decide what the app owns. In this design it owns four things: a single, deliberate submission per question; a mapping from a verified identity to the NLQueries agent that person may use right now; a rule that nothing reaches the screen without a current authorization decision; and a renderer that never presents a failed, empty or truncated query as a complete answer. NLQueries supplies SQL generation, validation and structured result metadata. The Streamlit interface, the identity bridge and the state handling are all your application code. NLQueries doesn't ship this UI.
One licensing note before you build. NLQueries Core is released under the Business Source License 1.1, a source-available license whose Additional Use Grant restricts offering the functionality to third parties as a hosted service. An internal app for your own staff is the scenario this post assumes. If the same app might later face customers, settle that question first.
A pinned backend and a six-row fixture
Pin your Streamlit, pandas and NLQueries versions first. The behavior described here is for NLQueries Core 0.4.1 at commit 2fbf276, and Streamlit's caching and authentication docs change often enough that your installed version matters (Streamlit caching overview). The renderer behavior reported later in this post was observed on Streamlit 1.60.0 with pandas 3.1.1 and PyArrow 24.0.0.
You'll also want a dataset small enough to know the right answer by heart. This synthetic fixture has two tenants, a cancelled order, an order outside the month, an order with a NULL amount, and an order with two line items, which exists to catch join mistakes:
-- Synthetic fixture (SQLite). Amounts are integer cents.
CREATE TABLE customers(id INTEGER PRIMARY KEY, tenant_id TEXT NOT NULL, region TEXT);
CREATE TABLE orders(id INTEGER PRIMARY KEY, customer_id INTEGER REFERENCES customers(id),
order_date TEXT, amount_cents INTEGER, status TEXT);
CREATE TABLE order_items(id INTEGER PRIMARY KEY, order_id INTEGER REFERENCES orders(id), sku TEXT);
INSERT INTO customers VALUES (1,'A','East'),(2,'A','West'),(3,'B','North');
INSERT INTO orders VALUES
(101,1,'2026-10-01',10000,'PAID'), (102,1,'2026-10-02',5000,'CANCELLED'),
(103,2,'2026-10-31',20000,'PAID'), (104,3,'2026-10-02',70000,'PAID'),
(105,1,'2026-11-01',40000,'PAID'), (106,2,'2026-10-15',NULL,'PAID');
INSERT INTO order_items VALUES (1,101,'a'),(2,101,'b'),(3,103,'c');
-- Reference answer: tenant A, paid orders, October 2026
SELECT SUM(o.amount_cents) AS revenue_cents, COUNT(*) AS paid_order_count
FROM orders AS o JOIN customers AS c ON c.id = o.customer_id
WHERE c.tenant_id = 'A' AND o.status = 'PAID'
AND o.order_date >= '2026-10-01' AND o.order_date < '2026-11-01';
The running question is: for tenant A, what were paid-order revenue and paid-order count in October 2026? The right answer is 30,000 cents across 3 orders. The NULL-amount order counts as an order but adds nothing to revenue. Against this fixture on SQLite 3.45.3, the reference query returns (30000, 3). That checks the fixture and the expected answer, not NLQueries, and the tenant_id = 'A' predicate is arithmetic for the expected result, not an access control.
Point a configured NLQueries agent (call it sales_internal) at this data through a database role that can only read these tables. NLQueries' own database hardening guide is clear that its SQL policy and your database permissions are separate safeguards with engine-specific limits. You want both.
One question, one submit
Streamlit reruns your whole script whenever a widget changes. That's lovely for sliders and terrible for a text box wired to a model and a database: every keystroke-and-blur could become a request. The fix is st.form, which batches widget values until the user presses the form's submit button.
The other decision is what "submit" is allowed to do. NLQueries makes this explicit with an ExecutionPolicy. The default, ExecutionPolicy.generate_only(), produces SQL without executing it; ExecutionPolicy.execute_read_only() runs the statement as a read, with the read-only transaction and the database's own grants still in force (nlqueries.execution). There's one subtlety here: generate-only mode can still touch the database for literal grounding, which looks up stored values that a literal is compared to. The source documents NLQ_LITERAL_GROUNDING=false for a mode that opens no connector at all. So "preview" doesn't automatically mean "zero database reads".
Here's the part of the app that's safe to show on its own: the form and the mapping from mode to policy. It's an untested sketch against Core 0.4.1, deliberately incomplete; the per-rerun identity and entitlement gate from the next two sections has to wrap it.
# Form and policy choice only (untested sketch; Streamlit + NLQueries Core 0.4.1).
# Not a complete app: the per-rerun identity and entitlement gate and the
# result handling described in the following sections are deliberately left out.
import streamlit as st
from nlqueries.execution import ExecutionPolicy
with st.form("ask"):
question = st.text_area("Ask about orders", max_chars=500)
mode = st.radio("Mode", ["Show SQL only (no execution)", "Run as read-only query"])
submitted = st.form_submit_button("Ask")
policy = (ExecutionPolicy.execute_read_only()
if mode.startswith("Run") else ExecutionPolicy.generate_only())
When submitted is true and the question isn't blank, and only after the entitlement check has passed on that same rerun, the request is run_query_sync(question, agent_id, execution=policy, use_cache=False), imported directly from nlqueries.orchestrator.sync_runner. run_query_sync is a synchronous wrapper around asyncio.run(run_query(...)), and the source notes it can't be called from inside a running event loop (sync_runner.py). Streamlit's normal script path is a plain function call, which is why the sync entry point looks like a fit, though you should confirm that on your Streamlit version. use_cache=False means NLQueries neither reads nor writes its semantic cache, so every submission is generated fresh. That's slower, but it removes a whole class of leak while you're still proving the identity model.
The mode labels are deliberate too. It's tempting to build "Preview, then Approve" as two buttons: generate-only first, then call again with execution allowed. But that second call generates SQL again, and nothing guarantees it matches what the user looked at. Two honest modes, "show me the SQL" and "run it read-only", beat one misleading workflow. A real approval step would need something custom, like executing the exact stored statement, and that's a different design.
Logged in isn't the same as allowed
Streamlit's built-in authentication (st.login, st.user, st.logout) uses OpenID Connect to tell you who someone is (Streamlit authentication docs). The docs are explicit that OIDC covers authentication, not authorization, so it doesn't decide what anyone may query. The identity cookie runs on its own clock, too: per the same page, it expires after 30 days if the user never logs out, independent of any expiry in the identity token. Entitlements change on a different schedule.
Two consequences follow. First, key everything on a stable, verified identifier from your identity provider, such as the OIDC issuer and subject pair, rather than assuming the email claim is verified or immutable. Second, the entitlement lookup is a server-side call your app makes on every rerun, mapping that identifier to an approved agent from current rights, never to whatever database the user names in the question. That resolver is custom code; neither Streamlit nor NLQueries provides it.
Caching is the other quiet leak. Streamlit's st.cache_data and st.cache_resource share cached values across users and sessions by default (caching overview). Decorate a "run this question" function with @st.cache_data and the second user who asks the same question gets the first user's answer, entitlements notwithstanding. Current docs describe a scope="session" option, but session scope isn't authorization either, and you'd want a pinned version before relying on it. For a first version, don't cache query results at all. If you later turn on either Streamlit caching or the NLQueries semantic cache, key it by the caller's entitlement, not by the question text, and test it with two users.
A saved result isn't permission to show it
This is where the obvious Streamlit pattern goes wrong. The natural first version keeps the latest answer in st.session_state, re-renders it whenever it's there, clears it when the logged-in email changes, and checks entitlements only on submission. It looks careful, but on a rerun without a submission it never asks the resolver anything. Revoke someone's access and their next click still redisplays the stored answer. The same hole opens when one person switches tenant or agent: the email hasn't changed, so the old result survives. When that control flow is exercised with a simulated session and a resolver that has revoked access, the resolver is called zero times on rerun and the stored result is displayed.
So the control flow that pattern implies is not ready for use: retained results require a fresh entitlement check on every rerun and before delivery. A session-local result is not authorization to redisplay it.
The design that replaces it runs at the top of every rerun, before anything renders:
- Resolve the stable, verified identity and the current entitlement for this rerun. If either is absent, clear any stored result and stop.
- Store each result together with its owner and an entitlement or version token captured when the request was authorized.
- Before rendering a stored result, compare its owner and token with the current scope. If they differ, or the current scope is absent, clear the result and stop.
The same comparison applies to a request that's still in flight: when a newly completed answer comes back, recheck the scope before delivering it, because access can be revoked while the model and the database are working. This design hasn't been implemented or tested, so treat it as the specification to build against and the identity checks at the end of this post as the bar it has to clear.
One limit deserves honesty: none of this stops someone from copying data that's already on their screen. UI restrictions shape what's displayed; they're not data-loss prevention.
Show the SQL, and tell the truth about the result
This is where a friendly table can quietly mislead. NLQueries returns an AgentQueryResult with the generated sql, an sql_is_valid flag, the execution_mode the request was permitted, and an sql_result that is None unless something was executed. The source comments explain why sql_is_valid exists: without it, a caller could only guess from a missing result, which looks the same for a generation-only run and a rejected statement.
The executed result is a QueryResult with columns, rows, row_count, error, truncated and truncation_reason (connector base types). The comment on truncated puts it bluntly: silently returning the first N rows of a larger answer is a wrong answer, not a partial one. Your renderer should treat each of these states as distinct, and it should run only after the scope check has passed for the result in hand:
# render.py (untested sketch; fields checked against Core 0.4.1 source)
# Call only after the current-rerun entitlement check has passed for this result.
import pandas as pd
import streamlit as st
def display_labels(columns):
"""Unique labels for display; the original ordered list stays in qr.columns."""
used, labels = set(), []
for name in columns:
label, n = str(name), 1
while label in used:
n += 1
label = f"{name} ({n})"
used.add(label)
labels.append(label)
return labels
def show_sql_result(result):
if result.sql:
st.code(result.sql, language="sql")
if not result.sql_is_valid:
st.warning("The generated SQL did not pass validation, so it wasn't run.")
return
qr = result.sql_result
if qr is None:
if result.execution_mode == "generate_only":
st.info("Preview only. No query was executed.")
else:
st.error("No query result was returned. Share the request time with support.")
return
if qr.error:
st.error("The database query failed. Share the request time with support.")
return # never show raw driver errors
if qr.truncated:
st.warning(f"Result is incomplete ({qr.truncation_reason}). Narrow the question.")
if not qr.rows:
st.info("The query ran and matched no rows.")
return
display_rows = [[None if value is None else str(value) for value in row] for row in qr.rows]
st.dataframe(pd.DataFrame(display_rows, columns=display_labels(qr.columns), dtype="string"))
st.caption("Check the SQL and column definitions for units; this interface does not infer them.")
Notice what this refuses to do. It never turns an error into an empty table, never hides truncation, and never lets "nothing was executed" look like "nothing matched".
Two details deserve a closer look. The first is column labels. On Streamlit 1.60.0 with PyArrow 24.0.0, duplicate names can fail at the DataFrame serialization boundary. The helper makes display labels unique while keeping the original column order. A column containing NULL and the integer 9007199254740993 also rounded the integer down when pandas inferred a float. The display table renders non-NULL values as text and keeps NULL missing. In the component check it preserved the large integer digits and decimal text. Keep the original result for typed calculations and check the actual client display before release.
The second is units. It's tempting to stamp "Amounts are in integer cents" under every table, because that's how the fixture stores money. But the SQL comes from free text. One question returns raw cents, the next amount_cents / 100, a third a count. A fixed label would be wrong by a factor of 100 on a perfectly correct answer. Unless you build and test a typed metric contract that knows what each result column is, the caption should point people to the SQL and the column definitions rather than guess.
Showing the SQL is also how users catch the mistakes validation can't, since validation says the statement parsed and passed policy, not that it answered the question. Here's the classic one on this fixture. If the model joins order_items to filter by product, order 101 appears twice and order 106, which has no line items, drops out:
-- Plausible but wrong: the join fans out order 101 and drops order 106
SELECT SUM(o.amount_cents), COUNT(DISTINCT o.id)
FROM orders o JOIN customers c ON c.id = o.customer_id
JOIN order_items i ON i.order_id = o.id
WHERE c.tenant_id = 'A' AND o.status = 'PAID'
AND o.order_date >= '2026-10-01' AND o.order_date < '2026-11-01';
Against the fixture on SQLite 3.45.3, this returns (40000, 2). Both numbers look reasonable, and both are wrong. A user who can see the SQL has a fighting chance of spotting the extra join. A user who only sees "40,000" has none.
Break it before your users do
Because this design hasn't been run end to end, the useful next step is a set of checks with clear pass and fail conditions, run against a pinned build of the complete app with a real identity provider configured:
- Form and execution path. Edit the question and switch modes several times without submitting, then submit once. Pass: exactly one NLQueries call per accepted submission, generate-only submissions never produce an
sql_result, and the running question, asked in read-only mode against the fixture, matches the reference answer. - Renderer states. Feed the renderer synthetic results for each state: invalid SQL, no execution, a database error, empty rows, NULL amounts, duplicate column names, large integer cents, decimal values and a truncated result. Add queries that return cents, cents divided by 100, counts and non-monetary columns. Fail on any false zero, silent truncation, lost column, raw error text, changed value or a unit claim the SQL doesn't support.
- Identity and reruns. Open two sessions as two different identities and ask the same question. Then revoke one user's access, switch the other user's tenant, and for each trigger a rerun, a new tab and a logout, including while a request is still running. Fail if any answer crosses users, if a retained or in-flight result appears after its scope changed, or if the app keys identity on the email claim.
Keep the exact versions, configuration and redacted outputs for each run; a pass nobody can reproduce isn't evidence a month later.
Where to take it next
If you're replacing a filter bar this quarter, start small: one internal agent, a read-only database role, both caches off, and the two honest modes. Load the six-row fixture, then write the per-rerun entitlement gate before you wire up the text box, since everything else depends on it. Run the identity and rerun check first, and keep anyone outside your team away from the app until all three checks pass. After that you can add what people will ask for next, such as charts on top of the result or a cache keyed safely by entitlement.
References
- Streamlit. st.form. Live reference. Accessed 2026-11-05.
- Streamlit. Caching overview. Live docs. Accessed 2026-11-05.
- Streamlit. User authentication and information. Live docs. Accessed 2026-11-05.
- NLQueries Core 0.4.1. nlqueries.orchestrator.sync_runner. Commit 2fbf276. Accessed 2026-11-04.
- NLQueries Core 0.4.1. Connector base types. Commit 2fbf276. Accessed 2026-11-05.
- NLQueries Core 0.4.1. nlqueries.execution. Commit 2fbf276. Accessed 2026-11-04.
- NLQueries Core 0.4.1. Hardening the database NLQueries reads. Commit 2fbf276. Accessed 2026-11-04.
- NLQueries Core 0.4.1. Business Source License 1.1. Commit 2fbf276. Accessed 2026-11-04.