---
id: varejo/assistente-ia-diretoria-lojas
industry: Retail and e-commerce
usage: widely used
effort: medium
grain: One semantic table per purpose: store x day, fiscal line item, product x branch, order line item
---

# AI assistant for leadership and stores

Leadership and store managers ask in chat or on WhatsApp how much they sold, how much stock there is and what the margin is, and each person only sees their own store.

## Executive summary

**For:** Leadership and store managers at retail chains who want answers without opening the BI tool.

**What it solves**

- The AI answers net sales, available inventory and margin with the same rule as the dashboard.
- Each manager sees only their own store; sensitive data stays with leadership.
- The AI runs the query before answering and says when the data does not exist or is late.

**Why it matters:** AI is confidently wrong when the table does not explain what each number is: it has already confused gross sales with net and zeroed out a day of sales through a time zone error. An assistant without per-store access exposes data from other locations and about cashiers. With a semantic layer and the right permissions, a WhatsApp question returns the same number as the close.

**Business questions it answers**

- How much did my store sell yesterday, net?
- What are revenue and margin by store this month?
- Is product X available at the DC? How many days of coverage?
- What needs leadership's attention today?
- Which SKUs explain each channel's variance against target?
- Which A-curve products are close to a stockout?

**What you need**

- ERP, POS or hub connected to Nekt, with the sales, inventory and margin tables already modeled
- The list of stores and who can see each one
- The official definitions of net sales, margin and available inventory

**What you get**

- Semantic tables per purpose, with grain and measures described for the AI
- One view per store with its own permission, and full views for leadership only
- Context document with the rules the AI must follow
- Freshness monitor that warns when a load did not run

## Detailed

### The problem

Leadership and managers want to ask in natural language and get the official number. Without semantic descriptions, the AI picks the wrong column (gross instead of net), applies a time zone to a field that is already a business date, and adds up branches that share the same inventory. Without per-table permissions, one store's manager queries another store, and the assistant exposes cancellations that identify the cashier.

### Business rules

1. **One semantic table per purpose.** Create consumption tables per question: daily sales by store, fiscal line item, inventory by product and branch, margin by item. Each one has its grain declared in the description in one sentence ("One row per store, per day"). Do not expose a generic table that mixes grains, and do not use a financial field from an operational table as revenue. (Avoid: One unified table with everything, and the AI picking the columns.)
2. **Annotate every column for the AI.** Start the column description with the tag: @pk for the grain key, @fk for a foreign key, @measure additive for something summable across any dimension, @measure non_additive for average ticket and percentages, @grain=daily or monthly. Then, in text: origin, calculation rule and pitfall. Document the table, not just the transformation. (Avoid: A "sales" column with no description, and average ticket summed by month.)
3. **Net sales is the default, and the AI says which one it used.** "How much did we sell" is answered with net sales (gross minus discounts minus cancellations). The answer always names the metric, the period and the store. Gross only when asked. (Avoid: Answering with gross because the column is called total_vendas.)
4. **No time zone on a business date.** ERP date fields stored as midnight UTC are already the business day. The AI computes today and yesterday in America/Sao_Paulo and compares directly, without converting the field. The rule is written in the column description and in the context document. (Avoid: The AI applying a time zone conversion to every date column.)
5. **Per-store access with per-view permission.** In Nekt, permissions apply to an entire layer or to a table or view, with read and edit roles. Folders in the catalog are only visual organization and protect nothing. Create one view per store (daily sales filtered to that store) and grant each manager only theirs. Use a token scoped by table for each audience's assistant. (Avoid: Splitting the store views into folders and assuming that restricts access.)
6. **Sensitive data only in leadership's scope.** Cancellations and cancellation attempts identify the cashier and are used for evaluation and investigation. They do not go into the store views, not even filtered to the store itself. They stay in leadership, audit and HR views. (Avoid: Giving the manager the cancellations table filtered to their store.)
7. **No SQL, no number.** The assistant never answers a number without running the query. It also does not decline for lack of data before reading the structure of the domain's semantic table. Metrics flagged as "to be validated" are not inferred. When the field does not exist, the answer is "data not found in this source". (Avoid: Answering from memory, or saying the data is not there without querying.)
8. **Available inventory by stock group.** Available = total quantity minus reserved, never negative. Branches that mirror the same physical inventory are aggregated by stock group, never summed. Products with unlimited stock stay out. A nonexistent product and an out-of-stock product get different answers. Product codes are standardized (for example, left-padded with zeros to 18 positions in SAP) before joining with sales. (Avoid: Adding the balance of two mirror branches and doubling inventory.)
9. **Freshness stated in every answer.** The assistant's views run on an event right after the source table, never with a manual trigger. A monitor with its own schedule detects missing runs (for example, more than 120 minutes without a load). The answer states the date and time of the last update. (Avoid: Views with a manual trigger that stop updating without anyone noticing.)
10. **Intercompany is not revenue.** Companies in the same group can show up as separate accounts or stores. Transfers between them are neither revenue nor operating expense and are eliminated in the consolidated view. Margin comes from the official margin table, never recomputed by the AI with a different rule. (Avoid: Adding sales from one group company to another into consolidated revenue.)

### Which table answers each question

Routing for the assistant's context document.

- **How much the store sold, average ticket** → Daily sales by store (`one row per store, per day`)
- **Product sold, taxes, CFOP** → Fiscal line item (`one row per receipt or invoice line item`)
- **Is it in stock? Where?** → Inventory by product and branch (`one row per product, per branch`)
- **Stockouts and turnover over time** → Inventory snapshot (`one row per product, per branch, per measurement`)
- **Margin by SKU and channel** → Contribution margin (`one row per order line item`)
- **Target, gap and projection** → Targets vs. actual (`one row per store or product, per month`)
- **Cancellations by cashier** → Leadership and audit only (`outside the store views`)

### Definitions

| Metric | Definition |
|---|---|
| Net sales | gross sales minus discounts minus cancellations, by store and business day; additive measure |
| Average ticket | net sales / valid receipts in the requested cut; non-additive measure |
| Available inventory | greater of (total quantity minus reserved) and zero, by product and stock group, at the latest measurement |
| Days of coverage | (available inventory + in transit) / average daily sales over the last 30 days, by product and branch |
| Net requirement | open requisition minus (available inventory + open purchase order), by product and ABC curve |
| Margin | sum of gross profit / sum of revenue in the period, from the official margin table |
| Freshness | time since the last successful run of the queried table, in minutes |

### Pitfalls

- **Gross confused with net.** The AI answered gross sales as net and was off by six figures on a single store's result for a single day. How to avoid: Net sales as the default, with @measure and the rule written in the description.
- **Day zeroed out by time zone.** The AI converted a date field that was already the business day to BRT and answered zero sales on a high-sales day. How to avoid: Column description stating there is no time zone conversion.
- **View stalled for over a week.** The per-store views were created with a manual trigger and went more than a week without updating, while the source was up to date. How to avoid: Event trigger after the source table and a missing-run monitor.
- **Folder treated as security.** The store views were in separate folders and the team assumed each manager only saw their own. A folder is visual organization. How to avoid: Permission granted view by view, tested with each store's user.
- **Inventory doubled or missing.** Two mirror branches added together doubled the balance. In another database, extracting the product code from the URL field instead of the reference code lost about 80% of inventory. How to avoid: Aggregate by stock group and extract the code from the reference field.

### Acceptance test

- [ ] For a set of 10 reference questions (one store's net sales yesterday, a product's inventory at a DC, a channel's margin for the month), the assistant returns exactly the dashboard number for all of them.
- [ ] With a store's user, querying another store or the cancellations table returns permission denied.
- [ ] The inventory of a group of mirror branches equals the balance of one branch in the group, not the sum.
- [ ] Every numeric answer includes the date of the last update, and the monitor alerts when a load passes the limit without running.

### Context document

```text
Sales, inventory and margin assistant
- Do not answer a number without running SQL. Do not decline for lack of data before reading the DDL of the domain's semantic table.
- Routing: sales and ticket by store in daily sales by store; product and tax in the fiscal line item; inventory in inventory by product and branch; stockouts in the inventory snapshot; margin in the contribution margin table; targets in the targets table.
- "How much did we sell" = net sales (gross minus discounts minus cancellations). Always state the metric, the period and the store.
- ERP sale dates are the business day stored as midnight UTC. Do not convert time zones. Today and yesterday in America/Sao_Paulo.
- Average ticket and percentages are not additive: recompute from the sums.
- Available inventory = total minus reserved, minimum zero. Aggregate mirror branches by stock group. Say whether the product does not exist or is just out of stock.
- Metrics flagged as "to be validated" are not inferred. Nonexistent field: "data not found in this source".
- Transfers between group companies are not revenue.
- State the date of the last update in every numeric answer.
Client exceptions:
- <client>: branches <A> and <B> mirror the same inventory (group <group>).
- <client>: stores without direct sales <list>.
- <client>: group companies <list>; sales between them are intercompany.
```

### Variations

- **Product search by nickname:** Dictionary of catalog abbreviations (qj for queijo, cheese) and an inverted index to find the product the way the salesperson writes it.
- **Assistant evaluation:** Store WhatsApp questions and answers in a table to review hits and misses.
- **After-sales and workshop:** Same layer for work orders, parts and labor, separating revenue from profit and service visit volume.