"How much did we receive this year, grouped by fund?" sounds like a simple analytics question. It becomes ambiguous as soon as a model sees a database containing received payments, future promises, archived records, and several date columns.
This tutorial designs a semantic query engine for an AI agent. The goal is to let a model translate business language into a small validated specification while keeping calculations and access rules in application code.
The running domain is fundraising. A donation is a gift already received; a donor gives it; a pledge promises a future gift; a recurring schedule arranges repeated gifts. A fund is a category used to classify donations. Promised money and received money must remain separate. In another product, the equivalent distinction might be booked orders versus collected payments.
Give the Agent a Vocabulary, Not an SQL Editor
The model submits a structured specification rather than arbitrary SQL or Python. This gives the executor a finite set of concepts to validate.
The reference engine has four entities:
| Entity | Meaning | Date basis | Default period |
|---|---|---|---|
| Donations | Recorded gifts received, using gross amounts | Donation date | This year to date |
| Contacts | People and organizations in the contact list | Date added | All time |
| Pledges | Amounts promised over a period | Pledge start date | All time |
| Recurring donation schedules | Schedules that create future donations | Schedule start date | All time |
Pledges and recurring commitments are not received gifts. If that distinction exists only in the prompt, a future tool or developer can accidentally erase it. It belongs in the entity definitions and metric calculations.
Each entity declares four kinds of terms:
- Filters narrow the matching records.
- Dimensions group those records.
- Metrics calculate declared figures.
- Detail fields describe individual records when the user asks for a list.
The definitions also own the required permission and module, date basis, default period, tenant predicate, fetch function, and listing order. A description tells the model what a term means; its implementation makes that meaning executable.
For "How much was received this year by category?", an illustrative tool input using the reviewed contract is:
{
"entity": "donations",
"period": { "relative": "this_year" },
"dimensions": ["category"],
"metrics": ["totalAmount"]
}There is no SQL string to inspect. The executor validates the entity, every filter value, dimension, metric, sort field, and permission. It rejects a filter used as a grouping and grouping without a metric. Useful rejection messages tell the model which terms are supported so it can correct an invalid call.
This reduces the language surface the backend must trust. It also creates deliberate limits: an unsupported business concept stays unsupported until someone defines and tests it. An honest "I cannot calculate that yet" is better than a plausible answer built from the wrong records.
Resolve Names Before Using IDs
"Exclude the Building Fund" introduces another problem. A name is not an identifier, and an identifier is not proof of access.
Typed, authorized search resolves names to contacts, categories, payment methods, or tags. Query validation checks both the ID kind and its provenance. A contact ID cannot quietly become a category filter. Names that could not be resolved, exclusions that were not applied, and unused names need explicit feedback; otherwise the answer can look complete while answering a different question.
Broad analytics currently requires organization-wide grants, represented by a null resource ID. General resolution of record-scoped analytics permissions is absent. Supporting that later would require a real scope resolver in the executor, not just showing more tools to the model.
Resolve Dates in Code
The model can request this_year; the backend resolves it into dates.
Explicit date inputs include both boundary days. Internally, windows include the start and exclude the end. The helper that reports the last day converts that exclusive end back into the final included date for the answer.
The reviewed product uses the browser's time zone, with UTC as fallback, Monday-start weeks, and a calendar-year default. The resolver can support a fiscal start month, but this product supplies January. Those are different claims: an extensible resolver does not mean a fiscal-year setting is already wired into the product.
Currency formatting similarly comes from organization currency and code-derived money metadata. The model should not guess a currency from a symbol or decide which numeric columns represent money.
Trace the Executor's Responsibilities
A schema can validate that dimensions is an array of strings. It cannot establish that category is a supported donation dimension or that the user has access to donor names. Validation therefore has two layers: the shared input contract, then entity-specific semantic and authorization checks.
For the example question, the executor selects donations, resolves the period, injects tenant and archive predicates, fetches bounded records, groups by category, and computes totalAmount. Label lookups turn authorized category IDs into readable names. The answer carries the resolved dates and metric definition so the user can see what was calculated.
The tenant predicate must come from server context. A user-supplied filter must never overwrite it. Detail fields can require additional permissions even when the entity itself is available.
Choose an Execution Strategy with an Explicit Ceiling
A semantic layer does not automatically mean database-side aggregation.
The general executor fetches bounded Prisma records, then groups and calculates in application memory. It requests at most 50,001 records so it can detect a match exceeding the 50,000-record ceiling. Above that ceiling, it asks for a narrower period or more filters rather than returning a partial total.
The record fetch runs in a read-only transaction with a ten-second statement timeout. Here is the relevant source pattern, shortened for readability:
return db.$transaction(async (tx) => {
await tx.$executeRaw`SET TRANSACTION READ ONLY`;
await tx.$executeRaw`SET LOCAL statement_timeout = 10000`;
return read(tx);
});Both statements are fixed text. User input is validated and passed through the declared query implementation.
In-memory calculation keeps the initial executor straightforward and makes custom metrics easy to express. It costs memory and application CPU, and it cannot answer arbitrarily large questions. A ten-second database statement timeout also does not bound every millisecond spent grouping results afterward.
Specialized tools handle questions such as lapsed donors and first-time donors with dedicated algorithms, including database grouping. That is a useful escape hatch for a question with semantics that do not fit a simple grouping. These tools currently return bounded donor lists, not reusable export handles.
The next scaling step is to move suitable aggregations into the database while preserving the semantic contract. Replacing the execution strategy should not change what "received" means.
Store Results Once; Present Them Several Ways
A result table may be useful to the user without being useful as thousands of tokens in the prompt.
The general query path stores calculated results server-side and returns an opaque handle, columns, row count, and a preview. The store caps results at 10,000 rows, exposes a 50-row preview, and expires results after 24 hours. The panel can page through stored rows.
Keep four counts separate: database records matched, rows calculated, rows retained, and rows shown to the model. They are not interchangeable. The store slices to its cap, so an export cannot be assumed to contain every computed row. If a query explicitly requests the top ten, the stored result is also those ten; a later export should not silently expand it.
Charts and CSV downloads consume the authorized stored data. Declared PDF templates also render from that data, with documents expiring after seven days. The model chooses a supported presentation; it does not retype the table and recompute totals.
A handle is only a reference. Result and document reads enforce the same user, tenant, and expiry checks on the server. Result reads are not additionally bound to the originating session in this snapshot, even though conversations can be used to discover recent handles.
This makes follow-ups like "chart that" practical. It also means the answer should disclose preview and retention limits, especially when a user expects a complete export.
Return Evidence with the Number
A useful result includes its metric definition, date boundaries and time zone, applied filters, grouping, archive policy, row count, and calculation timestamp. This evidence lets a user detect a wrong default without needing to inspect a database query.
The semantic description hash versions generated descriptions, not every compute-function change. A query-specification hash identifies the requested calculation, not a database execution plan. If you need complete reproducibility, proposed improvements include tracking the implementation version and the relevant data snapshot as well.
Test Meaning Before Adding Vocabulary
Use small synthetic fixtures where received gifts, pledges, and recurring schedules have deliberately different totals. Assert the expected received amount. Add records from another tenant, archived records, ambiguous names, and dates on both boundaries.
Then verify that a top-N query stores the same top N, that oversized reads are rejected, and that an export cannot expose another user's result. These cases protect meaning and ownership; a screenshot of a convincing chart does not.
Start with one entity and a handful of useful metrics. Add new terms when real questions reveal a gap. For a specialized question such as lapsed donors, define its algorithm explicitly rather than stretching a generic grouping into something it cannot represent.
Part 3 covers credits and usage limits, because even a bounded query engine can generate unpredictable model work across a conversation.
About This Series
These tutorials use a reviewed implementation snapshot (ae7c6e6fca71) as a concrete reference, with synthetic examples and generalized identifiers. They do not describe a verified live deployment. Code marked illustrative explains a pattern rather than providing a complete application. Proposed improvements are labeled explicitly.