Data warehouse & integration schema starter for POS, inventory & labor

Purpose

This starter schema and integration playbook is designed to get a small to medium food service or hospitality operation from disconnected vendor files into a coherent operational data warehouse (ODW). The goal is a dependable single source of truth for reporting, daily KPIs, reconciliations, and early-stage AI experiments while remaining small, practical, and easy to extend.

Design principles

• Keep the core schema small and stable: canonical sales, items, inventory movements, recipes and labor tables.
• Capture source provenance and timestamps for traceability.
• Use simple surrogate keys and natural keys where useful for join hygiene.
• Design for incremental loads and idempotent ETL so runs can be reprocessed safely.
• Validate with reconciliations (sales totals, inventory balance, labor hours) to build trust in dashboards.

Core tables and recommended fields

sales_transactions

One row per sold (or voided/comped) item event derived from POS.

• sales_transaction_id (surrogate PK)
• source_system (e.g., POS_vendor)
• source_transaction_id (vendor id)
• transaction_datetime_utc (UTC timestamp)
• location_id
• register_id
• ticket_id
• item_code (mapped to items.item_code)
• quantity
• price (unit price before tax/discount)
• tax_amount
• discount_amount
• modifier_group (if present)
• sale_type (sale, void, comp, return)
• payment_type
• created_at and loaded_at

items (master item dimension)

• item_id (surrogate PK)
• item_code (natural key from POS/vendor)
• name
• category
• portion_size and uom
• standard_cost (latest known cost)
• sku_or_vendor_code
• active_from, active_to (SCD-compatible fields)
• created_at, updated_at

recipes (bill of materials / recipe definitions)

• recipe_id, menu_item_code
• One-to-many recipe_lines: ingredient_item_code, quantity_per_recipe, uom
• yield, prep_loss_pct, and effective_date

inventory_movements

Track receipts, adjustments, transfers, and usage estimated by sales-to-recipe mapping.

• movement_id, location_id, movement_type (receipt, usage, spoilage, adjustment)
• item_code, quantity, uom
• reference_id (vendor invoice id, PO id, or sales_transaction_id for usage)
• movement_datetime_utc, cost_at_receipt

labor_shifts

• shift_id, employee_id, location_id
• clock_in_utc, clock_out_utc, hours_worked
• pay_rate, role, source_system

Relationships and keys

• sales_transactions.item_code -> items.item_code (left join for analysis)
• recipes.menu_item_code -> items.item_code (to compute ingredient usage)
• inventory_movements.item_code -> items.item_code
• sales_transactions.transaction_datetime_utc -> labor_shifts for per-shift KPIs (use overlapping time-window match)

Sample joins and common queries

Daily food cost by location (simplified):

SELECT
  s.location_id,
  DATE_TRUNC('day', s.transaction_datetime_utc) AS day,
  SUM(s.quantity * i.standard_cost) AS estimated_food_cost,
  SUM(s.quantity * s.price) AS sales
FROM sales_transactions s
LEFT JOIN items i ON s.item_code = i.item_code
WHERE s.sale_type = 'sale'
GROUP BY 1,2;
  

ETL & incremental load pattern (lightweight)

1. Extract raw files / vendor APIs into a staging schema preserving raw JSON/CSV and source metadata (filename, batch id, fetch_ts).
2. Basic normalization: normalize timestamps to UTC, unify unit-of-measure, standardize numeric formats.
3. Transform to canonical rows (one row per sold item event). Apply mapping table for item_code translation.
4. Idempotent load into target tables using merge/upsert keyed on source ids (source_system + source_transaction_id + line_number) and loaded_at timestamps.
5. Maintain a last_loaded_offset per source to support incremental pulls. For polling APIs prefer updated_since cursors; for file drops use file name + modified timestamp.

Idempotent merge example (pseudo-SQL)

MERGE INTO ods.sales_transactions tgt
USING (SELECT * FROM staging.sales_transactions_batch WHERE batch_id = :batch) src
ON (tgt.source_system = src.source_system AND tgt.source_transaction_id = src.source_transaction_id AND tgt.line_no = src.line_no)
WHEN MATCHED AND src.updated_at > tgt.updated_at THEN
  UPDATE SET ...
WHEN NOT MATCHED THEN
  INSERT (...)
;
  

Basic validation checks (build trust quickly)

• Daily sales total in POS vs aggregated sales_transactions (sum(sales) ≈ POS summary with tolerance for discounts/taxes).
• Inventory receipts and usage balance over a week (opening + receipts - usage - spoilage ≈ closing).
• Labor hours reconciliation: total paid hours in payroll extract vs summed hours in labor_shifts.
• Item mapping coverage: percent of sales rows with matched items.item_code; flag unmatched codes daily.
• Stale source detection: alert when a source hasn't provided data for X hours/days.

Common vendor quirks & mapping guidance

• POS aggregators may report ticket-level totals without item breakdowns — classify these as summary_sales and prioritize sources that provide line-level items for inventory coupling.
• Modifiers: normalize frequent modifier patterns (e.g., extra cheese) into modifier groups and map to ingredient usage only when significant.
• Comps & voids: keep explicit sale_type flags and treat comps separately for margin and waste analysis.
• Timezones: always store UTC and keep source timezone metadata to debug cross-day shifts.
• Multiple item codes for the same menu item across systems — maintain an item_code_mapping table with priority and effective dates.

Recipes, estimated usage and stock reconciliation

To approximate ingredient usage from sales, join sales_transactions -> recipes -> recipe_lines and multiply by quantity sold and yield. Keep this as an estimated_usage table and reconcile against actual inventory_movements.receipts and counted stock to detect waste and shrinkage.

Dimensional and change management notes

• Use surrogate keys for slowly changing data (items, recipes). Preserve effective_from/effective_to to support historical cost and menu-change analysis.
• When recipe changes occur, create a new recipe version with an effective_date. Don’t overwrite historical recipe lines used in past sales analysis.
• Capture source created_at and vendor updated_at for every source row to diagnose late-arriving updates.

Performance, partitioning and indexing

• Partition large tables by date (transaction_datetime_utc) and location_id where appropriate.
• Index on (source_system, source_transaction_id, line_no) and on (item_code) for joins and lookups.

Small checklist for first 30 days

1. Ingest one week of POS line-level data into staging and load sales_transactions.
2. Load items master and build item_code mapping table; measure unmatched rate.
3. Run daily sales reconciliation and fix mapping errors until < 1% unmatched.
4. Add inventory receipts for one location and run usage vs receipts reconciliation.
5. Bring in labor_shifts and validate hours vs payroll for a pay period.

KPIs and early dashboards

• Sales by day/location/menu category
• Estimated food cost % (estimated_food_cost / sales)
• Labor cost % by shift
• Inventory turn and days of cover
• Unmatched item % and data freshness

How to tailor this starter

This schema is intentionally a practical starter. Teams should copy it into their own domain, add local master data (location calendars, supplier lists, POS-specific fields), and gradually replace estimated usage with counted usage where physical inventories and adjustments are recorded.

Next steps & recommended mappings to capture

Create a mapping worksheet that collects for each source:

• Source system name and vendor
• Export format and frequency
• Key fields available (transaction id, line id, item code, modifier details, timestamps)
• Known quirks (aggregated tickets, timezone differences, comp handling)
• Sample rows for validation

If you copy this starter into your Adaptive Domain, consider building an interactive mapping form (see capability notes) that helps teams capture the items above for every source system — this significantly reduces mapping errors and speeds onboarding of new locations or vendors.