Reference Data Model: POS, Inventory & Labor Integration

Why this matters

Accurate, timely dashboards depend on a reliable analytics model that maps each source system to a single set of canonical entities and fields. When sales, inventory, labor and purchasing data are disconnected or inconsistently mapped, frontline teams see conflicting metrics, ingredient usage is wrong, and daily operational decisions suffer. This guide helps operators, data engineers, BI developers and analysts build a stable model that supports actionable daily dashboards and fast investigation of anomalies.

How to use this guide

Treat the schema below as a canonical design. You may rename fields to match internal conventions, but keep the semantic meaning, stable join keys and units. Use the mapping form structure suggested near the end to capture how local source fields map to the canonical model; store the mapping so ETL can be re-run when source systems change. Implement audit checks early to catch mapping problems before dashboards mislead operations.

Core entities, recommended fields, types and units

Only key fields are listed. Where possible include explicit data types and units so downstream joins and conversions are consistent.

1. Sales Transaction (header)

• transaction_id (string, canonical, stable)
• location_id / store_id (string)
• terminal_id (string, optional)
• transaction_time / closed_at (UTC timestamp) — source of truth for sales window
• opened_at (UTC timestamp), closed_at (UTC timestamp)
• total_amount, net_amount, tax_amount, discount_amount (decimal, currency)
• payment_method (string), tender_breakdown (json)
• order_type (enum: dine-in, takeout, delivery, online)
• employee_id (server/cashier, string)
• refund_flag / void_flag (boolean), parent_transaction_id for refunds

2. Sales Line Item (per-pos-line)

• line_id (string)
• transaction_id (FK to sales header)
• item_id (canonical menu item SKU, string)
• quantity (number), unit_price (decimal), line_total (decimal)
• modifier_codes (json), modifier_adjustment_amount (decimal)
• course, seat_number (optional)
• menu_category, department (for grouping)
• linked_recipe_id (nullable)
• is_combo / parent_item_id (for composite items)

3. Menu Item / Product Master

• item_id (canonical, stable)
• sku, name, description
• category, portion_size (numeric) and portion_unit (g, oz, each)
• linked_recipe_id, pricing_strategy_id
• active_flag, effective_from, effective_to (timestamps)

4. Recipe / BOM (Bill of Materials)

• recipe_id
• recipe_version (semver or integer)
• item_id (menu item FK)
• ingredient_id (canonical ingredient), quantity_per_unit (standard unit)
• waste_factor_pct, yield_factor (numeric)
• unit_of_measure (e.g., g, kg, ml, each) — store consistently
• effective_from / effective_to (timestamps)

5. Inventory Lot & Stock

• lot_id (string)
• ingredient_id (canonical)
• received_at (timestamp), expiry_date (optional)
• quantity_received, quantity_on_hand (snapshot) with unit and unit_cost (decimal)
• storage_location_id (freezer, cooler, dry)

6. Purchase Order & Receipt

• po_id
• supplier_id
• po_line_item_id, ingredient_id, quantity_ordered, unit_cost (currency)
• received_quantity, received_at

7. Supplier Master

• supplier_id, name, lead_time_days (int), contact_info

8. Labor Punch / Event

• punch_id
• employee_id
• location_id
• clock_in / clock_out timestamps (UTC) and local_timezone
• scheduled_shift_id (nullable)
• job_code (cook, server, manager), pay_rate_at_time (decimal)

9. Employee / HR master

• employee_id, legal_name, job_role, hire_date, termination_date

Timestamps, timezone handling and retention

Ingest times in UTC and persist the source local timezone (location_timezone). Use created_at / updated_at for master data and recipe changes. Retain raw transactional rows for a period appropriate to your accounting and compliance needs (commonly 1–7 years) and retain recipe versions indefinitely (or until you archive historical metrics) so historical consumption can be recomputed accurately.

Units, conversions and measurement sanity

Standardize ingredient units in the recipe BOM (e.g., grams/ml/each). Store a conversion table that maps supplier units (case, lb, kg) to canonical units and a multiplier. When ingesting receipts, convert received_quantity to the canonical unit immediately. Audit for unreasonable conversions (e.g., conversion factor = 0 or >1000).

Common join keys and patterns

• transaction_id joins sales header to sales lines
• item_id links sales lines to menu master and to recipe_id
• ingredient_id links recipe BOM to inventory lots and PO lines
• location_id / store_id required on every row for multi-location roll-ups
• employee_id links punches, schedules and sales (when available) for labor allocation

Integration patterns: choose per latency needs

• Event-driven / near real-time: stream POS events to a message bus, enrich (map item codes to canonical item_id) and push to analytics store. Requires deduplication and idempotency.
• CDC (Change Data Capture): capture source table changes and transform into canonical schema. Good for relational sources that support CDC.
• Daily ETL (batch): nightly extract of day’s sales, receipts and punches with validation. Simpler and acceptable when daily dashboards suffice.
• Hybrid: stream sales lines for timely KPIs and run daily reconciliation jobs for ingredient consumption and anomaly correction.

How to compute ingredient consumption and reconcile inventory

1. Map sales lines to menu item -> recipe BOM to compute theoretical ingredient usage (sales_qty × quantity_per_unit × portion conversions).
2. Aggregate theoretical usage by ingredient, location, and day.
3. Compute inventory depletion: opening_stock + receipts - closing_stock.
4. Variance = measured_depletion − theoretical_usage. Flag and surface significant variances on a daily ops dashboard.

Handling modifiers, combos, refunds and voids

Modifiers: ensure modifiers map to ingredient adjustments or portion multipliers in BOM. Combos: either expand combos into component lines at ingestion or keep parent-child line relationships with component mapping. Refunds/voids: record refund_flag and parent_transaction_id; exclude or use negative quantities when computing theoretical usage depending on business policy.

Recommended audit checks and thresholds

• Row count sanity: compare number of transactions from source vs canonical daily (allow a small ratio tolerance, e.g., 0.98–1.02).
• Monotonic timestamps: created_at <= updated_at and no future-dated events.
• Missing joins: percent of sales lines with no mapped item_id or recipe — alert at >1%.
• Ingredient variance: per-ingredient variance exceed threshold (e.g., >5% or dynamic thresholds by ingredient value) triggers investigation.
• Duplicate transaction_id detection (same id, different amounts).
• Labor sanity: punch durations >24 hours or negative durations.
• Price drift: line unit_price deviates from menu master price beyond expected discount bounds (e.g., >20% without documented promo).
• Unit conversion check: conversions that produce extreme unit quantities (e.g., 0 or >1000 multiplier) flagged as suspicious.

Schema versioning and change management

Version master data (recipes, menu items) with effective_from timestamps. Keep historical versions so you can recompute historical theoretical usage. Maintain a mapping registry with a last_updated timestamp and owner so changes to source systems are traceable and reversible.

Short glossary

Canonical ID

A stable identifier used in the analytics model that all sources map to (for example, item_id or ingredient_id).

Theoretical usage

Ingredient quantity derived from sales multiplied by recipe BOM (before shrink/waste).

Variance

Difference between theoretical usage and measured inventory depletion.

Practical implementation checklist

1. Define canonical item_id and ingredient_id and document mapping rules, units and conversion multipliers.
2. Capture the minimal set of required timestamps in ingestion pipelines; keep timezone metadata.
3. Implement an initial ETL (daily) and the audit checks above; run them on historical snapshots to tune thresholds.
4. Build a daily ops dashboard showing sales, ingredient usage, variance, labor hours, and exceptions with drill-down to offending transactions.
5. After two weeks of data, tune thresholds and investigate recurring data quality issues; iterate on mapping improvements.

Suggested mapping form (store mappings via Content Data Submission)

Capture source-to-canonical mappings in a simple form so changes are auditable and ETL can be re-run. Suggested fields:

• source_system (e.g., POS name)
• source_table / source_field
• canonical_field
• transform (e.g., multiply by 1000, parse JSON.path)
• unit_conversion (from_unit → canonical_unit multiplier)
• example_value
• owner / contact
• last_updated

Use the platform's Content Data Submission (POST /content/{contentItemId}/submit) to store mapping rows. Render the mapping form with Interactive Form Rendering so teams can edit and re-submit mappings without developer work.

Next steps & capability opportunities

Short-term: implement the canonical tables and a daily ETL with the audit checks above. Start with a nightly reconciliation dashboard and a small exceptions report emailed to ops leads each morning.

Medium-term capability enhancements (recommended):

• Interactive mapping form (Interactive Form Rendering + Content Data Submission) that stores source→canonical mappings and allows operators to update mappings when a POS or vendor changes. This reduces developer touch and makes integrations more resilient.
• Automated alerting when audit checks fail (ingredient variance, missing joins, duplicate transactions).
• Master-data versioning for recipes and a lineage view that shows which recipe version was active on a given date.
• Packaging this guide and associated mapping form as an Adaptive Ownable Domain so multi-location groups can acquire, tailor, and operate it as a living product across sites.

Image suggestion: use an integration diagram titled "POS → Enrichment → Recipe BOM → Ingredient Usage → Inventory Reconciliation" (search: "data integration diagram restaurant").