# Jaffle Shop, worked: what `orders.status` means

> dbt Labs' sample project, with its five-value status column annotated so an agent looks it up instead of guessing. Every field is on the page.

- Page: https://coremodels.io/recipe/jaffle-shop-status
- JSON: https://coremodels.io/recipe/jaffle-shop-status.json
- Connector: [dbt](https://coremodels.io/connector/dbt) (Import)
- [Start with this recipe →](https://go.coremodels.io/app/new/dbt/jaffle-shop-status) No warehouse credential. No dbt Cloud token. Read-only.

A worked instance of the agent-grounding recipe on jaffle-shop-classic, the dbt Labs sample project most analytics engineers run first. Its docs tell a human what the five `orders.status` values mean. They do not tell an agent that `paid` and `cancelled` do not exist, that `shipped` is not revenue, or that `stg_payments` has no status column at all. This recipe fills exactly that layer (meaningNote, commonMistake, doNotUseFor, permitted values and an owner), then shows the SQL an agent writes before and after it can read them. Import the same manifest into your own workspace and fill the same fields.

## What you do

1. Run `dbt parse` on jaffle-shop-classic and import target/manifest.json; each accepted_values list, orders.status included, becomes a governed vocabulary.
2. Fill meaningNote, commonMistake and doNotUseFor on the columns an agent gets wrong. The annotated set is below.
3. Point your assistant at the read-only endpoint and ask: what was completed revenue last month?

## The worked example: Jaffle Shop · orders.status

Built on [jaffle-shop-classic](https://github.com/dbt-labs/jaffle-shop-classic), dbt Labs' sample project (Apache-2.0). Four models:

- `customers` (mart): One row per customer, with derived facts from that customer's orders and payments.
- `orders` (mart): One row per order, with payment amounts by method. Carries status.
- `stg_orders` (staging): Cleaned orders from the raw_orders seed. Carries the same status column and the same accepted_values test.
- `stg_payments` (staging): Cleaned payments from the raw_payments seed: payment_method and amount. No status column.

## The question

**What was completed revenue last month?**

A text-to-SQL assistant that cannot look the column up guesses a status list, and the guess is usually some mix of shipped, completed and paid.

### Before: the agent guesses

```sql
select sum(amount) as revenue
from orders
where order_date >= date_trunc('month', current_date - interval '1 month')
  and order_date < date_trunc('month', current_date)
  and status in ('shipped', 'completed', 'paid');
```

- paid is not a permitted value. It matches no rows, so the query runs cleanly and nobody learns the guess was wrong.
- shipped is not revenue: the customer has not received the goods.
- The currency is unstated. The column is AUD.

### After: the agent looks it up

```sql
select sum(amount) as completed_revenue_aud
from orders
where order_date >= date_trunc('month', current_date - interval '1 month')
  and order_date < date_trunc('month', current_date)
  and status = 'completed';
```

orders.status has five permitted values and no paid. Its doNotUseFor says revenue means filtering status = 'completed'. orders.amount says the unit is AUD.

## `orders.status`, fully annotated

Permitted values (accepted_values on orders.status and stg_orders.status; descriptions from the orders_status docs block in models/docs.md):

| Value | Meaning |
|---|---|
| `placed` | The order has been placed but has not yet left the warehouse. |
| `shipped` | The order has been shipped to the customer and is currently in transit. |
| `completed` | The order has been received by the customer. |
| `return_pending` | The customer has indicated that they would like to return the order, but it has not yet been received at the warehouse. |
| `returned` | The order has been returned by the customer and received at the warehouse. |

- **definition:** Fulfillment state of a single order. Five values, mutually exclusive. The values and their descriptions come from the orders_status docs block in jaffle-shop-classic.
- **permittedValues:** `placed`, `shipped`, `completed`, `return_pending`, `returned`
- **meaningNote:** This is the fulfillment state of one order, not a customer lifecycle flag and not a payment result. Only completed means the customer received the goods. placed and shipped are open. return_pending and returned come after the fact: they are not kinds of open and not kinds of successful. There is no cancelled, pending, paid, active or failed value in this column.
- **commonMistake:** Inventing values the column does not have (pending, cancelled, active, paid, success). Treating shipped plus completed as successful orders. Reading status as a property of the customer rather than the order. Looking for a status on payments: stg_payments has none in this project.
- **doNotUseFor:** Revenue on its own (filter amount where status = 'completed'). Active-customer counts. Payment success or failure. Inventory on hand. Any query that IN-lists values you did not look up here.
- **agentGuidance:** Before writing SQL against orders.status, read permittedValues. If the question says revenue, completed or sold, filter status = 'completed'. If it says open or in flight, use placed or shipped. If it says returns, use return_pending or returned. Never invent a sixth value.
- **owner:** Jordan Hale (Finance lead · example person)
- **steward:** Maya Chen (Analytics engineer · example person)
- **source:** `models/schema.yml` · accepted_values · docs block orders_status

## The other columns an agent gets wrong

### `stg_orders.status`

- **definition:** The staging copy of the same field. Same vocabulary as orders.status.
- **permittedValues:** `placed`, `shipped`, `completed`, `return_pending`, `returned`
- **meaningNote:** Identical permitted values to orders.status. Prefer the mart column in analyst questions unless the question is about the staging model itself.
- **commonMistake:** Declaring a different accepted_values list here than on orders.status. Both tests can pass. That is how two definitions of one column survive in a dbt project.
- **doNotUseFor:** A second, private meaning of status. If staging and mart disagree, that is drift, not a new definition.
- **owner:** Maya Chen (Analytics engineer · example person)
- **source:** `models/staging/schema.yml` · accepted_values

### `orders.amount`

- **definition:** Total amount of the order in Australian dollars: the sum of the credit_card, coupon, bank_transfer and gift_card payment amounts.
- **unit:** AUD
- **meaningNote:** Present on every order whatever its status. It is not revenue. Completed revenue is sum(amount) where status = 'completed'. The raw_payments seed stores cents; by the time it reaches this column it is dollars.
- **commonMistake:** sum(amount) across all statuses, labelled revenue. Assuming USD. Adding raw_payments.amount (cents) to it. Treating coupon_amount as a discount rather than a payment method.
- **doNotUseFor:** Completed revenue without a status filter. USD reporting. Margin: there is no cost column in this project.
- **owner:** Jordan Hale (Finance lead · example person)
- **source:** `models/schema.yml`

### `customers.customer_lifetime_value`

- **definition:** Lifetime sum of payments for this customer's orders, all statuses. customers.sql emits this column as customer_lifetime_value; models/schema.yml documents the same figure under the name total_order_amount, which the model does not produce.
- **unit:** AUD
- **meaningNote:** Lifetime gross payments, not completed-only value and not current-period revenue, whatever the name suggests.
- **commonMistake:** Querying total_order_amount: it is documented in schema.yml but is not a column of the built table. Calling this figure what the customer paid and kept: it includes placed, shipped, return_pending and returned orders.
- **doNotUseFor:** Period revenue. Completed-only customer value. Churn or active-customer definitions.
- **owner:** Jordan Hale (Finance lead · example person)
- **source:** `models/customers.sql`

### `customers.number_of_orders`

- **definition:** Count of the orders this customer has placed, all statuses.
- **meaningNote:** Includes return_pending and returned orders. A customer with one placed order and one returned order has number_of_orders = 2.
- **commonMistake:** Using it as a count of completed purchases, or as a proxy for active.
- **doNotUseFor:** Completed-order counts. Active-customer flags.
- **owner:** Maya Chen (Analytics engineer · example person)
- **source:** `models/schema.yml`

### `customers.first_name`

- **definition:** Customer's first name. Marked PII in the upstream schema.yml.
- **pii:** true
- **meaningNote:** Personal data. The seed values are fictional, which is the only reason this example shows the column at all.
- **commonMistake:** Echoing names into agent logs, eval traces or answers as if they were a dimension.
- **doNotUseFor:** Joins, aggregations, or anything an agent might print. Use customer_id.
- **owner:** Maya Chen (Analytics engineer · example person)
- **source:** `models/schema.yml`

### `customers.last_name`

- **definition:** Customer's last name. Marked PII in the upstream schema.yml.
- **pii:** true
- **meaningNote:** Personal data. The seed values are fictional.
- **commonMistake:** Same as first_name.
- **doNotUseFor:** Joins, aggregations, or anything an agent might print. Use customer_id.
- **owner:** Maya Chen (Analytics engineer · example person)
- **source:** `models/schema.yml`

### `stg_payments.payment_method`

- **definition:** How this payment was tendered. One row per payment; an order can have several.
- **permittedValues:** `credit_card`, `coupon`, `bank_transfer`, `gift_card`
- **meaningNote:** coupon is a payment method in this project, not a discount. There is no payment status column in jaffle-shop-classic.
- **commonMistake:** Inventing cash or paypal. Treating coupon as a markdown against amount. Looking for a payments status column: it does not exist here.
- **doNotUseFor:** Order fulfillment state (that is orders.status). Discount analysis. A second status vocabulary.
- **owner:** Maya Chen (Analytics engineer · example person)
- **source:** `models/staging/schema.yml` · accepted_values

### `stg_payments.amount`

- **definition:** Payment amount in dollars. The raw_payments seed stores cents; stg_payments.sql divides by 100.
- **unit:** AUD
- **meaningNote:** Already converted: this column and orders.amount are both dollars. Only the raw_payments seed is in cents.
- **commonMistake:** Dividing by 100 a second time. Reading the raw_payments seed directly and summing cents as dollars.
- **doNotUseFor:** Revenue on its own: join to orders and filter status = 'completed'. Mixing with raw_payments.amount without converting.
- **owner:** Maya Chen (Analytics engineer · example person)
- **source:** `models/staging/stg_payments.sql`

## Point your agent at it

Import the manifest into your own workspace, fill the fields, then connect your assistant to the read-only MCP endpoint. There is no public demo project to connect to; your agent reads your copy.

MCP client config (mcp.json):

```json
{
  "mcpServers": {
    "coremodels": {
      "url": "https://go.coremodels.io/mcp",
      "headers": {
        "Authorization": "Bearer <your CoreModels API key>"
      }
    }
  }
}
```

Claude Code:

```bash
claude mcp add --transport http coremodels https://go.coremodels.io/mcp
```

Setup for Cursor and other clients: https://library.schematica.io/mcp-server

## For agents

Before you assert what a Jaffle Shop column means, allows, or is used for, read its entry in columns. If it has permittedValues, do not invent another value. If it has doNotUseFor, obey it. If you cannot find the column, say so; do not infer it from the name.

## Attribution

Jaffle Shop is dbt Labs' sample project jaffle-shop-classic, licensed Apache-2.0. The models, the columns, the accepted_values lists and the five status descriptions are theirs (models/schema.yml, models/docs.md). The annotations on this page (meaningNote, commonMistake, doNotUseFor, owners) are CoreModels example content, not part of the upstream project.
Sources: https://github.com/dbt-labs/jaffle-shop-classic/blob/main/models/schema.yml · https://github.com/dbt-labs/jaffle-shop-classic/blob/main/models/docs.md · https://github.com/dbt-labs/jaffle-shop-classic/blob/main/LICENSE

Maya Chen and Jordan Hale are example people, invented for this recipe. They are not customers or staff.

## Guides for this recipe

### Strategy

- [The Fourth Option for a Column Named status](https://coremodels.io/connector/dbt/h5-agents)

### Engineering

- [Grounding an Agent in Your dbt Project: What It Reads, and What Changes](https://coremodels.io/connector/dbt/t6-agent-grounding)
- [Four Tools and a Ledger: dbt Contracts from an Agent's Seat](https://coremodels.io/connector/dbt/t3-mcp)

All dbt recipes: https://coremodels.io/connector/dbt#recipes