NLQueries GitHub

Blog  / 

Designing an Internal Slack Data Assistant with NLQueries: The Controls to Test First

Published . Written for nlqueries-core 0.4.

slack authorization python

Picture the rollout. Someone on the finance team types /data what was paid revenue for October? into a busy channel, and a bot answers with a number. People love it. A week later a support engineer types the same command in the same channel, asks about a customer's orders, and gets an answer too. Then a security reviewer asks the question nobody planned for: who decided that being in that Slack channel meant you could read the orders table?

That's the real design problem. Wiring a slash command to a text-to-SQL pipeline is easy. The hard part is that a Slack user ID isn't a database permission, the model call is slower than Slack's acknowledgement window, access can change while a query is running, and an answer posted in a channel is visible to people who never asked. This post walks through a custom adapter around NLQueries Core 0.4.1 that has to handle all four, and the tests it must pass before anyone trusts it. It's a design walkthrough, not a finished build: the adapter code is an untested sketch, and the end-to-end acceptance tests haven't been demonstrated.

Write the policy before you write the listener

Here's the running example. An approved finance analyst types /data paid revenue for tenant A in October 2026 and should get back two numbers: revenue and order count. A support user who isn't on the finance list types the same thing and should get a polite refusal, with no query generated and no rows read.

Write that down as policy first, because it pins down three decisions that are easy to get wrong later. The first is scope: one kind of aggregate question, for one approved group. The second is the default delivery: a private (ephemeral) response that only the asker sees, never a channel post. The third is the ownership split. NLQueries supplies the question pipeline: generating SQL, validating it and running it read-only. It isn't a Slack integration. The Core 0.4.1 sources cited here don't include a Slack plugin, so the listener, the identity mapping, the job queue and the renderer are all your application code (sync_runner.py, README).

Be clear about what NLQueries' own safeguards cover. The project's hardening guide treats its generated-statement policy and your database permissions as separate layers, each with engine-specific limits (database hardening, sql_policy.py). A read-only SQL policy stops a DELETE. It doesn't decide whether a particular Slack user should see a particular number, or whether the channel they typed it in is a sensible place to show it. That decision is yours.

To have something concrete to check answers against, here's a tiny synthetic fixture: three customers across two tenants, six orders, and one order with two line items so that a careless join shows up.

-- Synthetic fixture (SQLite). Not real data.
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 for "tenant A, paid revenue and paid-order count, 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';

Against this fixture, on SQLite 3.45.3, the reference query returns (30000, 3). The NULL-amount order counts toward the order total but not toward revenue. That checks the expected answer and nothing else: it says nothing about NLQueries, Slack or authorization. The tenant_id = 'A' predicate is expected-answer arithmetic, not a security boundary. Real tenant separation has to be enforced below the model, by a database role that can't read tenant B's rows or by a row-enforcement wrapper you implement and test, not by hoping the model writes the right WHERE clause.

A verified Slack request is an input, not a permission

Slack signs every HTTP request it sends your app. Verification means computing an HMAC over the raw request body and timestamp with your signing secret, comparing it to the signature header, and rejecting requests outside the accepted timestamp window (Verifying requests from Slack). Bolt for Python's App takes the signing secret and runs a built-in request-verification middleware for HTTP mode, enabled by default (Bolt App reference), so don't hand-roll it unless you have to.

Here's the subtle part. A valid signature proves the request came from Slack. It doesn't prove the user is allowed to see revenue. The team_id and user_id in a verified payload are trustworthy inputs to your authorization, not the authorization itself. This adapter must authorize the Slack identity before calling Core. Use a connector backed by an independently restricted database role, or an explicitly implemented and tested row-enforcement wrapper. Don't infer Enterprise agent-record row filters from Core's Python interface: Core's run_query takes a question and an agent ID, with no caller principal or row-filter parameter (sync_runner.py), and the product's edition matrix lists table-scoped agents and row-level filters as Enterprise features (NLQueries editions).

So the adapter keeps its own server-side map from Slack identities to the NLQueries agent each person may query, and each agent's connector uses a database role scoped to what that group may read.

# entitlements.py: custom application code, not part of NLQueries or Slack. Untested sketch.
from dataclasses import dataclass

@dataclass(frozen=True)
class Grant:
    agent_id: str          # an NLQueries agent whose connector uses a restricted database role
    show_sql: bool         # whether this group may see generated SQL

# Keyed by (team_id, user_id). In production, load this from your identity system,
# not from a dict, and include enterprise_id if you run Enterprise Grid.
_GRANTS: dict[tuple[str, str], Grant] = {
    ("T_EXAMPLE", "U_FINANCE_ANALYST"): Grant(agent_id="finance_tenant_a", show_sql=True),
}

def resolve_grant(team_id: str, user_id: str) -> Grant | None:
    return _GRANTS.get((team_id, user_id))

The user never picks the agent. If a command's text could select agent_id, a support user could just ask for the finance agent by name. Keeping the choice server-side closes that door, and the restricted role behind the connector means a wrongly generated query for tenant B fails at the database rather than depending on the model's WHERE clause.

Acknowledge within three seconds, then hand off to a queue

Slack expects your app to acknowledge a slash command within three seconds (Acknowledging requests). A model call plus a database query will often take longer than that, so the listener should do only fast, local work: check the grant, enqueue a job, and ack() with a short "working on it" message. Bolt's app.command listener gives you ack and respond for exactly this pattern (Listening & responding to commands).

# app.py: custom Slack adapter using Bolt for Python. Untested sketch: still needs signed
# HTTP ingress tests, a durable queue and a deduplication/expiry contract. Pin slack_bolt.
import os
from slack_bolt import App
from entitlements import resolve_grant
from jobs import enqueue          # your durable queue; its contract is described below

app = App(token=os.environ["SLACK_BOT_TOKEN"],
          signing_secret=os.environ["SLACK_SIGNING_SECRET"])   # Bolt verifies signatures

@app.command("/data")
def handle_data(ack, command):
    grant = resolve_grant(command["team_id"], command["user_id"])
    if grant is None:
        ack(text="You don't have access to /data. Ask in #data-access if you need it.")
        return                                    # denied before any query is generated
    question = command["text"].strip()
    enqueue(team_id=command["team_id"], user_id=command["user_id"],
            question=question, response_url=command["response_url"])
    ack(text="Working on it. The reply will appear here, visible only to you.")

The queue is where you take on the most responsibility, so it's left as a contract here. The tempting shortcut is to hash team, user and question into a deduplication key. That isn't a contract: the same analyst deliberately asking the same question an hour later gets the same key, and nothing bounds how long that key means "already done". The queue needs a bounded window and an expiry, keyed on verified request identity, so a retry of one request does the expensive work once while a later, intentional repeat gets fresh data. Job and delivery state have to be persisted, too; an in-process background thread is lost on a restart. Durability and idempotency are claims you can make only after testing a real queue, and neither Bolt nor NLQueries provides jobs.enqueue.

Treat response_url as a sensitive capability, because anyone holding it can post into that conversation. Store it with the job, never log it, and never accept one that arrives from anywhere except the verified command payload.

Call Core narrowly, and check access again before you deliver

The worker is where NLQueries comes in. Core 0.4.1 exposes an async run_query and a synchronous run_query_sync that wraps it with asyncio.run, so the sync version can't be called from inside a running event loop (sync_runner.py). Execution permission is an explicit, immutable ExecutionPolicy value. If you don't supply one, execution is denied, so a request that fails closed gives you SQL and no rows (execution.py). For an assistant that's supposed to answer, the worker mints ExecutionPolicy.execute_read_only() deliberately. The docstring is careful to say this permits execution as a read without asserting the statement is safe. The read-only transaction and the database's own grants still apply.

# worker.py: custom worker. Signatures checked against Core 0.4.1 source.
# Untested sketch: not executed; needs the delivery-time access tests described below.
from nlqueries.execution import ExecutionPolicy
from nlqueries.orchestrator.sync_runner import run_query_sync
from entitlements import resolve_grant
from render import deliver_private, deliver_error   # your renderer; not shown, see next section

def process(job):
    grant = resolve_grant(job.team_id, job.user_id)
    if grant is None:                       # entitlement revoked since the command arrived
        deliver_error(job, "Your access to /data has changed, so this request was cancelled.")
        return
    result = run_query_sync(
        job.question,
        grant.agent_id,
        dialect="postgres",
        execution=ExecutionPolicy.execute_read_only(),
        use_cache=False,          # baseline: no shared semantic cache
        history=None,             # baseline: no follow-up context across users
        timeout_seconds=20,       # statement timeout where the connector honors it
    )
    # Recheck right before delivery: suppress the answer if access was revoked OR changed.
    # For a real identity system, compare a complete entitlement/version token,
    # not merely agent_id and show_sql.
    current_grant = resolve_grant(job.team_id, job.user_id)
    if current_grant != grant:
        deliver_error(job, "Your access changed, so the answer was not sent. Submit a new request.")
        return
    deliver_private(job, result, show_sql=grant.show_sql)

The second grant check is the one that's easy to get subtly wrong. Testing only whether the grant still exists catches a full revocation, but not a change. If an analyst moves from tenant A to tenant B, or loses permission to see SQL, while the query is running, an "is it still there?" check happily delivers the old tenant's result with the old SQL visibility. Comparing the whole grant closes that gap: Grant is a frozen dataclass, so != compares both fields, and any difference suppresses the answer and asks the user to submit again. In a real deployment the grant will carry more than two fields (column permissions, a policy version), so compare a complete entitlement or version token from your identity system.

Two of the arguments are baseline choices, not defaults.

The first is use_cache=False. NLQueries' semantic cache is partitioned by agent, and the architecture doc states plainly that every entry in an agent's collection is readable by everyone who may query that agent. If two callers of the same agent are entitled to different results, the distinguishing value has to go into cache_context on both reads and writes (architecture). Slack workspace and user IDs aren't automatically part of that context. With one agent per entitlement group, sharing the cache within a group may be exactly what you want. Turning it off at first means you take on that question only once you've decided how cache context maps to Slack identity. The same reasoning applies to history: one person's conversation turns shouldn't quietly shape another person's answer.

The second is timeout_seconds. According to the source docstring, it's forwarded to the SQL sub-agent as a statement timeout (for example, SET LOCAL statement_timeout on Postgres) so a runaway query is aborted server-side, and which dialects honor it depends on the connector. It doesn't currently bound the model call itself, so the job still needs its own expiry.

Keep the data flow in mind as well. NLQueries' configuration reference lists what leaves the machine on each model call: the question, the schema with descriptions, and up to five sample values per column on SQL questions. Result rows go to the model only on the hybrid path, and NLQ_SEND_RESULT_ROWS_TO_LLM=false reduces that to column names and a row count (configuration). Don't read that flag as a promise that no stored values leave: the same reference says up to 1,000 characters of a database error go back to the model when it corrects a failed statement, and literal grounding can send nearby stored values to its repair step. Slack is a second destination, too. A posted answer is a copy outside your database that its retention rules don't cover, so ask whoever administers your Slack workspace how long those messages are kept before deciding which results the bot may post.

Don't let the bot announce a number it can't prove

The renderer is where an assistant earns trust, or loses it. AgentQueryResult gives you answer, sql, sql_is_valid, execution_mode, sql_result and from_cache. Those middle fields exist so that "no rows" is legible: the source notes that without sql_is_valid, a generation-only run, a rejected statement and an empty result all look the same (sync_runner.py). The sql_result itself is a QueryResult carrying columns, rows, row_count, error and truncated (connectors/base.py).

The trap is reaching for answer as the thing to post. On a SQL-only question it isn't a summary of the rows that were actually returned. In a Core 0.4.1 run against the fixture with execution enabled, answer came back as an empty string while sql_result held a single row, [30000, 3]. A renderer that posts answer would send a blank "success" there, and on other paths it can send prose written before any SQL ran, or a narrative alongside a failed or truncated query.

Result rendering is intentionally omitted. A renderer must derive displayed numbers from a successful, complete QueryResult and validate the result shape and units; result.answer is not proof of the executed values.

For the running example, that means an explicitly limited, deterministic aggregate formatter. It should refuse anything where sql_is_valid is false, sql_result is missing, error is set or truncated is true. It should require exactly the columns ["revenue_cents", "paid_order_count"], exactly one row, an integer-or-NULL revenue and an integer count, and render those values with their units, so 30000 shows up as cents and a NULL revenue never turns into zero. Every other result shape gets a request to narrow the question, which matches the scope the policy set at the start. Deliver through the stored response_url with response_type="ephemeral", which slack_sdk's WebhookClient.send accepts (WebhookClient reference). If the analyst wants to share an answer with the channel, that's then a deliberate human choice rather than the bot's default.

The fixture's order-items table is there for a reason. If the generated SQL joins order_items without care, order 101 is counted twice and revenue inflates while everything still "works". A valid, policy-approved query can still be the wrong answer, and a shape check won't catch it. Showing the SQL to the people allowed to read it is one of the cheapest ways to spot that.

Break the adapter on purpose before anyone installs it

Most of this can be tested without sending a real Slack message. Use synthetic command payloads and a test signing secret, and intercept WebhookClient.send so local tests never reach Slack. The adapter isn't ready until all four of these pass:

  • Ingress and timing. A tampered body or a stale timestamp is rejected before anything is enqueued or run_query is called, and an unknown team or user is refused. With a deliberately slow fake queue and model behind it, the acknowledgement still comes back inside three seconds.
  • Retries versus repeats. A retry of the same request does the expensive work once, a later intentional repeat of the same question gets fresh data, and expired jobs never deliver.
  • Changed access. Revoke a grant, switch the user to another agent or tenant, and change their column or SQL visibility, each between execution and delivery. Every stale-scope result is suppressed, while an unchanged grant still delivers. Separately, a query for tenant B through the tenant A agent fails at the database or control layer, whatever WHERE clause the model wrote.
  • Honest rendering. The real SQL-only result for the reference question renders as 30000 cents and 3 orders, NULL revenue isn't shown as zero, and a driver error, a truncated result, unexpected columns or pre-SQL narrative can't be presented as a successful aggregate.

Then, and only with explicit approval, repeat the important cases in a private development workspace with a development app. Keep the exact versions, configuration and redacted outputs, because "the team tested it" isn't evidence six months later. None of these acceptance tests has been run end to end for this post. They're the gate, not a result.

Keep it internal, and write the failures first

One licensing note before you ship. NLQueries Core is released under the Business Source License 1.1, whose Additional Use Grant permits production use provided it doesn't include offering the Licensed Work to third parties on a hosted or managed basis (LICENSE). That restriction applies whatever the front end is; putting Slack in front doesn't change it. An internal assistant for your own staff is the scenario here. If the plan ever turns into a Slack app you offer to other companies, read the grant, and get proper legal advice, before writing more code.

Your next step: load the six-row fixture into a scratch database behind a role that can't read tenant B, point one finance_tenant_a agent at it, and write the rejection tests for bad signatures, unentitled users and a grant that changes mid-query before you write the happy path. If those fail closed, the rest of the assistant is mostly plumbing.

References

All posts · RSS feed · Documentation