- A property operator should not need to understand a reporting database to ask a simple question.
- What did we spend on plumbing last quarter?
- The data exists inside the commerce platform.
- A conventional dashboard can expose dozens of filters and charts, but the user still needs to know which report contains the answer.
The client problem
A property operator should not need to understand a reporting database to ask a simple question.
What did we spend on plumbing last quarter? Which properties bought the most? How many units were concerned? Which supplier received the largest share? And how does that compare with the previous period?
The data exists inside the commerce platform. The difficulty is reaching it.
A conventional dashboard can expose dozens of filters and charts, but the user still needs to know which report contains the answer. A free-form text-to-SQL assistant appears more flexible, but it gives a probabilistic model too much authority over query structure, access scope and calculation.
For a multi-property procurement platform built on Medusa, we designed a safer middle path.
The model translates the user’s language into a strict, allowlisted analytics plan. The backend normalises and hardens that plan, intersects it with the properties the signed-in person is allowed to see and runs deterministic SQL over reconciled purchase history. The final answer is constructed from those computed results.
The AI understands the question. The platform owns the facts.
The client needed answers, not another reporting module
Procurement questions rarely arrive in dashboard language.
A regional manager does not naturally ask for a total_spend aggregation grouped by property_id with a category filter and a date range. They ask, “Which properties spent the most on HVAC this year?”
An accountant may ask for spend by GL code. An operations lead may ask how often a particular part is reordered. A property manager may want all purchases not assigned to a unit. Each question can be represented as filters, dimensions and metrics, but forcing every user to build that representation manually turns analysis into specialist work.
Creating a custom report for each question does not scale either. Every new combination of product, supplier, property, unit, period and metric becomes another screen to design and maintain.
We wanted natural language to become a new entrance to the same governed purchase data—not a separate analytics system.
Why we did not give the model a SQL console
Text-to-SQL is attractive because it appears universal. Give the model a schema, let it write a query and return the result.
That convenience hides several business risks.
The model may misunderstand the grain of the data. It can confuse number of orders, number of purchase rows and total quantity. It can join an allocation table incorrectly, ignore a returned line or calculate spend from the wrong price field. More importantly, a generated query can cross the caller’s property scope if access rules are not reconstructed perfectly every time.
We separated language interpretation from data execution.
The model never receives authority to write arbitrary SQL. It returns a JSON plan constrained by a schema we define. That plan can express only supported filters, aggregations, breakdown dimensions, sorting, limits and one of two scope choices.
The backend then decides whether and how that plan becomes a query.
Flexibility lives at the language boundary. Authority remains in application code.
Give the model a closed analytical vocabulary
The purchase-history planner knows the questions the platform can answer.
It can filter by product, SKU, vendor, category, manufacturer, GL code, property, unit, building, city, state, order status, price and date. It can request metrics such as purchase count, purchased quantity, spend, average price, average order value and distinct products, orders or properties.
It can break results down by property, month, supplier, category, GL code, unit, building, product or another supported dimension. It can ask for a top list, a bottom list, a count or a summary.
This vocabulary is broad enough for useful procurement analysis and narrow enough to validate.
If somebody asks for a capability outside the data surface, the assistant explains what it can analyse instead of inventing a result. If the question is ambiguous, it can request a concrete product, property, date or other missing detail.
The model is not asked to know the database. It is asked to map business language into a known analytical language.
Define the grain before defining the metrics
The phrase “How many products did we buy?” can mean at least three things.
- How many purchase-history rows matched?
- What total quantity was purchased?
- How many distinct SKUs were involved?
Those answers can differ dramatically.
The planner schema defines each metric explicitly. Purchase count means the number of displayed purchase-history rows after reconciliation. Purchased unit count sums represented quantities. Unique SKU count measures distinct references. Unique order count measures orders, not line items.
Similar distinctions exist for average unit price, average spend per purchase, average order value and spend per property.
The prompt teaches those meanings, and deterministic hardening recognises common phrases such as “how many,” “distinct,” “average order value” or “single most expensive purchase.”
This prevents the language interface from making business metrics vague. Natural questions are welcome; metric definitions remain precise.
Turn the conversation into a plan, not an answer
When the user asks a purchase-history question, the planner receives the supported data surface, current date, relevant conversation and—when appropriate—the filters already visible in the Purchase History table.
It returns structured intent:
- the requested scope;
- filters and date range;
- metrics to calculate;
- dimensions for grouping;
- sorting and result limit;
- any clarification or unsupported reason.
The platform then applies deterministic invariants.
It normalises “cancelled” and “canceled.” It recognises state names and abbreviations. It turns “last 30 days,” “last quarter” and year-over-year language into explicit dates. It preserves an explicit SKU rather than treating it as a generic search phrase. It recognises common asks such as spend share, reorder cadence, outliers and monthly trends.
The model proposes the plan. Application rules make the contract stable.
Conversation should reduce repetition, not carry stale assumptions
Follow-up questions are valuable.
A user can ask, “What did we spend on paint last quarter?” and then, “And by property?” The second question should preserve the paint and period context while adding a property breakdown.
But conversational memory can also mislead. If the user next asks about plumbing, the assistant should not silently keep the paint filter. If a Purchase History table is currently filtered to one vendor, a completely new question should not automatically inherit that vendor.
The planner receives previous messages, while backend rules determine when table context is relevant. Current-view filters are inherited only when the latest message refers to that view or is an obvious follow-up. A new product, category, vendor or property subject resets incompatible context.
This gives the conversation continuity without turning old filters into invisible global state.
The user can ask naturally, and the system still makes scope changes explicit in the plan.
Recalculate the caller’s property perimeter for every analysis
The most important filter never comes from the model.
Before running analytics, the backend resolves the properties accessible to the signed-in organisation member.
A Master can analyse the organisation’s portfolio. Managers and supervisors are restricted to properties they manage. Buyers and other property-scoped members use their explicit assignments. A selected active property can narrow the query further.
The user may request “all properties,” but that phrase means all properties they are entitled to see. A property identifier in a prompt or URL cannot enlarge the backend-computed set.
The organisation and property perimeter is applied at the base of the SQL data set, before business filters and aggregations.
This is the central security promise: natural language changes how a person asks, not what they are authorised to know.
Build analytics from reconciled purchase history
Commerce history is not simply the original cart frozen in time.
Orders can be edited. Quantities can be reduced. Items can be returned. Costs can be allocated to properties, units and GL accounts. A useful answer must reflect the platform’s current Purchase History representation rather than sum raw order lines blindly.
The SQL layer assembles organisation orders, property links, line items, products, vendors, categories, manufacturers, brands, allocations and units into one governed row set.
Allocation slots reconcile quantity with unit and GL attribution. Business statuses distinguish pending, completed, cancelled, returned and edited rows. Product enrichments are prepared only for products inside the organisation’s scoped purchase history.
That reconciled row grain becomes the shared foundation for both the table and conversational analytics.
The assistant does not maintain a second version of purchase truth. It queries the same business representation the product uses to explain historical purchases.
Let deterministic SQL perform the calculations
After planning and access resolution, application code builds the approved query.
Filters become parameterised conditions. Requested metrics map to predefined SQL expressions. Breakdown dimensions map to known columns. Sorting and limits are restricted to supported choices.
The platform can calculate questions such as:
- total spend by property and month;
- distinct SKUs purchased from a vendor;
- average unit price by manufacturer;
- purchases assigned to a particular GL code;
- spend per apartment unit;
- the share of spend represented by a category;
- average days between repeat orders;
- unusually high unit prices within the filtered baseline.
The model does not calculate those numbers from rows in its context window. PostgreSQL calculates them over the authoritative scoped data set.
This improves accuracy and makes the result reproducible. The same plan over the same data produces the same analytical answer.
Use catalogue vocabulary from the actual history
Users and catalogues often name the same thing differently.
Someone asks for “fridges” while product titles say “refrigerator.” They use a manufacturer name where the catalogue uses a brand. They type a plural category or a common abbreviation.
The platform can derive candidate vocabulary from the scoped purchase history: product titles, category names, manufacturers and SKUs, with their observed counts. Search terms are normalised and common singular/plural forms are considered before applying filters.
This helps reconcile language without opening the query to arbitrary invented dimensions.
An explicit SKU remains exact. A natural product or category phrase can be matched against values that actually occur in accessible history. Finished products are kept distinct from parts and accessories unless the user asks for those components.
The assistant becomes more forgiving about words while remaining strict about the data it can select.
Construct the answer from computed evidence
Once SQL returns rows and statistics, the application has everything required to answer.
It knows the applied filters, requested metrics, result count, breakdown values and scope. Deterministic answer construction can state the number, list or summary and suggest corresponding Purchase History table filters where useful.
This creates a valuable bridge between conversation and inspection.
The user can ask a broad question, receive a concise answer and then open a filtered view to inspect the underlying purchases. Aggregations remain analytics results; concrete product, vendor, property or date values can become table filters.
The conversation is not a black box that asks the user to trust a paragraph. It is an alternate interface over navigable commerce data.
That transparency is especially important when an answer influences purchasing or budget decisions.
Why Medusa was the right architectural foundation
Medusa already owns customers, products, orders and commerce lifecycle. Our platform extensions add organisations, properties, units, allocations, GL context and supplier relationships.
Purchase History AI connects those existing capabilities. It does not copy the history into a separate AI database or give a model direct ownership of business queries.
The planner is one replaceable interpretation component. The permission model, SQL definitions and commerce data remain inside the application. New supported dimensions can be added deliberately as the product evolves.
That is why choosing an extensible commerce engine matters.
We can add a sophisticated conversational experience while preserving the same organisational scope, property model and order truth used everywhere else in the platform.
AI becomes a better interface to Medusa, not an escape hatch around it.
A practical architecture checklist
Before adding natural-language analytics to commerce history, define these contracts.
- What exact row does “one purchase” represent after edits and returns?
- Which filters, metrics and breakdown dimensions are supported?
- Does the model return a strict plan rather than arbitrary SQL?
- Which deterministic rules harden dates, statuses, SKUs and common metrics?
- When should conversational or table context be inherited or reset?
- How is the caller’s organisation and property perimeter recomputed?
- Can a requested property ever expand that backend-computed perimeter?
- Do parameterised SQL expressions, not the model, calculate the numbers?
- How are product and category phrases reconciled with actual catalogue values?
- Can the user inspect the underlying filtered purchase rows?
If those boundaries are explicit, natural language can simplify access without weakening analytical trust.
The broader lesson
The client did not need a chatbot attached to orders.
They needed managers, buyers and accountants to ask useful questions without learning a reporting tool—and without giving a language model unrestricted access to procurement data.
We solved that by dividing the work correctly.
The model understands intent and produces an allowlisted plan. The backend protects organisation and property scope. Deterministic SQL computes metrics over reconciled purchase history. The product constructs an answer that can lead back to inspectable records.
So when somebody asks, “What did we buy last quarter?”, the response is conversational—but the truth behind it remains entirely operational.