Data dictionary

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)

Id mapping cheat sheet

-- 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.