Slack data Q&A bot for your SQL Server database

Build me a Slack data Q&A bot that turns plain-English questions into safe, read-only SQL Server queries and posts the answer back in Slack.

Trigger: an incoming Slack webhook (Slack Events API) for the app_mention event in a designated analytics channel, plus the message.channels event when the bot is replied to inside an existing thread it started. Verify Slack's signing secret, respond 200 immediately, and process the event asynchronously. The bot identity should be the Slack Bot (slackbot) integration so it can receive app_mention events and post as a bot.

Agent behavior on each mention:

1. Extract the user's question from the Slack event (strip the bot mention and any leading whitespace). Capture the channel, thread_ts (use the parent ts if this is a new mention, or the existing thread_ts if it is a reply), and the asking user id so the final reply can address them.

2. Ground in the schema before generating any SQL. Call SQL Server's List Tables operation to see what user tables exist, then call List Columns for the small subset of tables that look relevant to the question. Do not assume table or column names. If the question references entities you cannot map to a real table after this lookup, post a clarifying question in the same Slack thread (using Slack Bot's Send a Message with thread_ts set) and stop.

3. Generate a single T-SQL SELECT statement. Hard guardrails: SELECT only. Refuse anything containing INSERT, UPDATE, DELETE, MERGE, TRUNCATE, DROP, ALTER, CREATE, EXEC, EXECUTE, or sp_ prefixed procedures. Always include a row cap: TOP (100) for ungrouped queries, or ORDER BY ... OFFSET 0 ROWS FETCH NEXT 100 ROWS ONLY when pagination semantics are needed. Use square brackets around identifiers ([Orders], [Customer Name]). Never interpolate user values directly into the SQL string. Use @p1, @p2, @p3 placeholders and pass values through the params array per the SQL Server common_docs.

4. Run the query through SQL Server's Custom Query operation with the parameterized SQL and params array. If it errors (invalid object name, invalid column, syntax), feed the error back into the agent loop, regenerate the SQL using the schema you already fetched, and try once more. After two failures, post the error in-thread in plain English and stop.

5. Summarize the result and reply in the same Slack thread with Slack Bot's Send a Message (channel = original channel, thread_ts = the thread you are in). The reply must contain, in order: a one-line restatement of the question the bot understood; a short plain-English narrative answer (one to three sentences) grounded in the actual returned numbers; a compact Slack-formatted table of up to 10 rows from the result set (use mrkdwn-friendly fixed-width columns inside a triple-backtick code block, and note the total row count if more than 10 rows were returned, capped at the 100-row hard limit); and a final section labeled 'SQL' containing the exact T-SQL that ran inside a triple-backtick block so analysts can audit it.

6. Ambiguity handling. If the question is missing a time window, an entity scope, or a metric definition the schema does not pin down (for example, 'top customers' without defining 'top by what'), ask one focused clarifying question in-thread before running anything. Do not guess.

7. Safety and limits to enforce in code, not just in prompts: reject any generated SQL that does not start with SELECT or WITH (after trimming whitespace) and does not contain at least one of TOP / FETCH NEXT; cap returned rows shown in Slack at 10 even if the database returned more; cap the SQL Server response at 100 rows; do not echo the database connection details in Slack ever.

Integrations to use: SQL Server (sql-server) with operations List Tables, List Columns, and Custom Query; Slack Bot (slackbot) with operation Send a Message for replies. The bot must be invited to the analytics channel the user designates during setup.

Configurable inputs at setup time: the SQL Server credential, the Slack Bot credential, the list of channel IDs where the bot is allowed to answer, an optional allowlist of schemas or tables the agent is permitted to query (default: all user tables in dbo), and the row cap to display in the reply table (default 10, max 100).