---
title: Data dictionary
description: The semantic layer - every table, view, metric definition, and the conventions that keep numbers honest.
---

# Data dictionary

The full metric contract lives in `joe-data-plane/docs/metrics.md` and is the source of truth. This page is the analyst-friendly version.

## Conventions (read these first)

1. **All money columns are integer cents.** `net_sales_cents = 151075` means $1,510.75. Divide by 100 at the last moment, never mid-aggregation.
2. **`business_date` is the store-local calendar day.** Timestamps named `*_local` are already in the store's timezone. Never apply `AT TIME ZONE` to them; take `::date` directly.
3. **Order inclusion:** a row counts as a sale when `order_status = 'completed'`. Exactly that string, nothing else.
4. **Two id spaces exist.** Supabase `shop_id` (used by shops, subscriptions, live path) and AWS/Aurora `store_id` (used by the transaction ledger and reporting tables). Map between them with `source_identities` (`source = 'joe_api'`, `active = true`). Joining across id spaces without this mapping silently returns nothing.
5. **Net vs gross, the eternal confusion:**
   - **Net sales** = post-discount subtotal, pre-tip, pre-tax. Matches Looker "Net Sales".
   - **Total collected / gross collected** = everything charged: subtotal + tax + tips (+ donations). Matches Looker "Total Collected". The Stripe-comparable number.
   - **Looker "Gross Sales"** = PRE-discount item value. This is a different number from our gross collected. Never map the two to each other.

## Core tables (reporting path, complete through yesterday)

### `transaction_ledger` - the workhorse
One row per transaction (orders, refunds, gift-card loads), 365 days deep, all stores. This backs the All Transactions dashboard and the accounting exports.

Key columns: `business_date`, `store_id` + `store_name`, `company_id` + `company_name`, `transaction_type` (`Order`, `Gift Card`, ...), `transaction_source` (mobile, kiosk, till...), `payment_method`, `order_status`, and the full money split: `gross_sales_cents`, `net_sales_cents`, `tax_cents`, `tip_cents`, `donation_cents`, `upload_amount_cents` (gift-card loads), `credit_amount_cents`, `joebucks_amount_cents`, `prepaid_amount_cents`, `rate_fee_cents` + `flat_fee_cents` + `stripe_fee_cents` (processing costs), `transfer_amount_cents` (what moves to the merchant), `merchant_refund_cents`, `cash_payment_cents`, `total_collected_cents`, plus `payout_id` and `arrival_date` linking each transaction to its Stripe payout.

### `shop_daily_sales`
Per shop per business date: `net_revenue_cents`, `gross_revenue_cents`, `tips_cents`, `order_count`. Backs the weekly/daily emails. Average ticket = summed net / summed orders.

### `item_sales_daily`
Per shop per item per day: `quantity`, `gross_revenue_cents`, `order_count`. (No menu-category column yet - category-grain data is the top remaining ingestion gap.)

### `sales_adjustment_daily`
Per shop per day discount/comp/reward adjustments, backfilled from reporting. Money already in cents.

### `payout_daily`
Per Stripe connected account per arrival date: `payout_count`, `gross_cents`, `fee_cents`, `net_payout_cents`. Payouts arrive on banking days only.

### `recent_orders` (live path)
Today's orders, updated every ~20 seconds, keyed by Supabase `shop_id`. Carries order payloads including line items and customer fields. Use for anything intraday; do not use for closed-day reporting (the ledger is authoritative once the day closes).

## `bi.*` views (query these in Metabase)

| View | Grain | Use it for |
| --- | --- | --- |
| `bi.sales_daily` | shop x day | Sales, orders, tips, average ticket |
| `bi.network_sales_daily` | network x day | Whole-network trends |
| `bi.payment_mix_daily` | shop x day x method | Cash vs card vs prepaid mix |
| `bi.item_sales_daily` | shop x item x day | Item velocity, top items |
| `bi.item_margin_daily` | shop x item x day | Margin (note: cost data still missing for most items) |
| `bi.payouts_daily` | account x arrival date | Payout reconciliation |
| `bi.processor_fees_daily` | shop x day | Processing-fee visibility (~$378k/30d network-wide) |
| `bi.customer_shop_rollups` | customer x shop | Repeat behavior, customer counts |
| `bi.transactions` | transaction | Ledger passthrough with friendly column names |

## Id mapping cheat sheet

```sql
-- Supabase shop -> AWS store ids
select source_id
from source_identities
where shop_id = :shop_id and source = 'joe_api' and active;

-- so a ledger query scoped to one shop is:
select *
from transaction_ledger
where store_id in (
  select source_id from source_identities
  where shop_id = :shop_id and source = 'joe_api' and active
);
```

Stripe account -> shop mapping is `stripe_account_directory` (authoritative source: Aurora `company.paymentAccountId`, backfilled 2026-07-12).

## Known open items

- **Tips scope:** Looker counted card tips only; our `tipsCents` may include cash tips. Under adjudication.
- **Donations in gross:** verifying whether our gross-collected includes donations exactly as Looker did.
- **Ledger vs facts reconciliation:** low single-digit percent daily deltas between `transaction_ledger` and legacy fact tables are tracked on a dedicated reconciliation card; adjudication in progress (JOE-474).
- **Item margin:** all rows currently `missing_cost` until cost ingestion lands.