How the InventDB SOAR AI answers with SQL you can check

A number from a language model is only useful if you can see where it came from. "Occupancy is 91%" might be right, or it might be a confident guess, and the sentence looks the same either way.

The AI in InventDB SOAR, the database with the business engine, does not answer questions about your records from memory. It writes SQL, the server runs that SQL as you, and the query travels with the answer so you can read it, run it again or change it. The model is the one your administrator chose: Claude on Amazon Bedrock, Sonnet 5 by default with Opus 4.8 available, or an open-weight model served by InventDB Sparkle, our own inference engine. Every call goes through our AI gateway.

This article walks through one turn of the agent: how it chooses its tools, what it knows about your schema, how its queries are scoped, how the one-shot plain-English SQL path reuses earlier answers, how prompts are cached, and how usage is capped. The agent calls a model from inside the server, so it is part of InventDB SOAR only. InventDB Serverless, the managed database, has no AI inside.

One turn, step by step

1. The console streams your message to the agent endpoint. The server reads the AI settings from the instance's encrypted settings record on every request, so a change in Settings applies without a restart.
2. The server records who you are and carries that identity through the whole stream, including the worker threads that execute SQL.
3. An intent router picks the tools this turn needs.
4. The server builds the prompt: instructions and the SQL grammar, a schema index of the types you can read, the SQL convention this kind of request needs, and sample values from your data.
5. The loop runs. Claude replies with tool calls, the server executes them, and the results go back for the next round, up to 20 rounds. That cap is a safety limit; most turns need a few.
6. Every step is streamed to the console as an event. A query step carries the SQL text, and its result step carries the SQL again with the rows.

Choosing tools before the first call

The agent has roughly sixty tools: SQL, records, reports, charts, workflows, email, SMS, calendar, attachments, web search and schema. Offering all of them on every turn costs tokens and lowers the chance that the model picks the right one. Mixing conventions also causes errors: workflow plans bind values as ${...} while report SQL uses :name placeholders, and a model shown both rules tends to mix them.

So before the loop starts, a router classifies your latest message into up to four of eleven capability areas. A keyword check settles most turns, about 80 percent, at no cost. Messages under 12 characters and greetings get the full tool set. For the rest, the server makes one short classification call with no reasoning, temperature 0 and a 1.5-second timeout, and asks for the labels as one line of JSON with a confidence flag.

The agent is then offered the tools for the matched areas plus seven that are always present: plan, run SQL, read the schema, open a record, show a form, list workflow runs and open a run. Only the SQL convention those areas need is added to the prompt. The router can only remove tools from the full set. A timeout, an unparseable reply, low confidence, or a selection that would cover 60 percent or more of the catalog all fall back to the full set. A unit test checks that every tool name in the router's map exists and that every real tool is reachable from at least one area.

What the model knows about your data

InventDB has no fixed DDL, so the server builds a schema index from the data itself, for the person asking. It contains only the types that person can read, with each type's columns and its join key, followed by the foreign-key edges between types and the rule that a foreign key is compared with the column it references. The edges come from the relationship graph recorded at import, with inferred edges only for types the graph does not cover, and they are sorted so the text is byte-for-byte stable. Multi-hop join templates stay out of the index; the agent fetches them with a schema tool when it needs one.

The index is cached in the server and keyed by a fingerprint of namespaces, types and column names, with a one-hour expiry as a backstop. A new type or column changes the fingerprint and is visible to the AI on the next call; an ordinary insert changes nothing.

Column names alone do not tell the model that an owner id looks like O-1006 rather than a UUID, or that a status is stored as Active rather than active. So the server appends the first and last record of each visible type, found by two index seeks, with every value truncated at 64 characters. That block sits at the end of the prompt, so a change to it leaves the cached part before it intact. It is on by default, and an administrator can switch it off in Engine settings.

Running the SQL as you

When Claude calls execute_sql, the server runs the query on a worker thread with your identity installed, so the engine adds your row rules to every table the query reads. The agent cannot see more than you could see by typing the same query yourself. For a question like "which three properties had the most open work orders last month", the server returns the answer with a card like this:

SELECT p.name, COUNT(*) AS open_orders
FROM pms.work_orders w
JOIN pms.properties p ON w.property_id = p.property_id
WHERE w.status = 'Open' AND w.opened_on >= '2026-09-01' AND w.opened_on < '2026-10-01'
GROUP BY p.name ORDER BY open_orders DESC LIMIT 3

The join uses the business key from the index, and the status casing comes from the sample values. The result card keeps the SQL, so you can open it as a grid, filter it or export it. A few guards keep the loop honest: an identical tool call with identical arguments runs once per turn, tool-call markup is stripped from the final text, a result whose row count equals the query's LIMIT is flagged as possibly truncated, and when a long conversation is summarised, query results keep their leading rows so a summary cannot invent a ranking.

Requests to change records take a different path, covered in the article on safe AI changes. Writes made through the agent pass the same row checks as any other write.

Plain-English SQL and question reuse

SQL Studio, the record filters and the widget editor have an "Ask AI" box that returns a query without running it. This path makes one model call. Its prompt is layered from most to least stable: the SQL grammar, your schema index with sample values, the instructions, and finally the question with today's date. The model has one tool and calls it with the SQL, which the server returns to your editor. When the request refines an existing grid query, the model must keep its column list and joins and change only the filter, sort or limit. If the rewritten query comes back identical to the original, the server returns a 422 with a hint, because a filter that changes nothing would show every row under a "filtered" label.

Answers on this path are reused. The first tier hashes the exact question. The second normalises it: lowercase, common phrasings folded ("how many" becomes "count"), and US state names, statuses, numbers and dates replaced by tags, so "properties in Florida" and "properties in Texas" share one template whose literals become parameters. A template is reused only when the literal counts match and the rebuilt SQL parses, and a hit is promoted to the exact tier. The cache stores SQL text and never rows, and it is emptied whenever the schema text it was built against changes. A reused query still runs under your own rules when you run it.

Prompt caching

Most of an agent prompt is identical from one call to the next. Anthropic's prompt cache bills a cached prefix at about a tenth of the input price, and allows four cache breakpoints per request. The server spends them like this: the leading system blocks and the end of the tool catalog are cached for one hour, and the latest tool result in the loop is cached for five minutes, because it changes every round. The breakpoint count is capped at four.

We measured the effect of the one-hour and tool-result breakpoints in June 2026 with Claude Opus 4.8 on a property-management test dataset, using a fixed four-scenario benchmark and a 15-minute mixed workload:

With the change in place, the prefix of about 76,000 tokens was written once, on the first cold call, and every later call read it from the cache. Over the 15-minute workload, 104 model calls had a cache-hit rate of 97.6%.

Quotas and metering

Before every model call the server checks daily limits, and it records usage after the call. The counters live in memory, are written to the database every 10 seconds, and the limits are reloaded every 60 seconds. Tokens counted against a limit are fresh input plus output; cache reads are billed but not counted, so a long conversation, which is mostly cache reads, does not use up a person's daily limit. The defaults, which an administrator can change with PUT /api/ai-metrics/quotas, are:

Each call is recorded with its token counts and a cost, and the Engine page breaks the cost down by model. The money limit is separate: the monthly AI allowance that comes with your plan, enforced by the gateway.

Limits

• The agent is part of InventDB SOAR only.
• A model can write wrong SQL. Showing the query is the check, so read it before relying on an important number.
• The sample values are real data from your records and are sent to the model with the prompt. Turn them off if that does not suit you.
• The router can misjudge a turn. Uncertain cases get every tool, and SQL and schema tools are always present.
• One turn runs at most 20 rounds.
• Question reuse applies only to the plain-English SQL path. Its normalisation assumes English and recognises US state names.

Using it

In InventDB SOAR, ask on the Analyze page and open the SQL on any answer, or use "Ask AI" in SQL Studio to draft a query you run yourself. The same draft is available over HTTP:

POST /ai/generate-sql
Authorization: Bearer <token>
Content-Type: application/json

{
  "question": "tenants more than 30 days overdue, largest balance first",
  "context": { "namespace": "pms", "type": "leases" }
}

The response contains the SQL, and running it goes through the same rules as any query you type. Your instance's live reference at /api/docs lists the AI endpoints with the rest of the API.