Data Schema Template: POS, Inventory & Labor

Purpose

This reference spec recommends a minimal, pragmatic data schema to unify POS sales, inventory movements, and labor/time data for reliable dashboards and simple analytics. It is intentionally implementation-friendly: event-first where possible, a small set of canonical dimensions, stable join keys, and clear ETL notes to avoid common inconsistencies that break frontline decision-making.

Design principles

• Event-centric primary tables: capture immutable events (sale line, inventory movement, time punch) with timestamps and source IDs.
• Canonical master dimensions: item/menu, location, employee, supplier, and time — with stable surrogate keys where sources disagree.
• Minimal duplication: store necessary denormalized fields for fast dashboards (e.g., item price at sale), but keep canonical attributes in dimension tables.
• Explicit provenance: track source_system, source_id, and ingestion_timestamp for every row to support reconciliation and audits.
• Timezones and shifts: store all timestamps in UTC and include local event_time and timezone fields to support shift-based reporting.

Core tables and recommended fields

1) sales_header (transaction-level)

• transaction_id (string) — source unique id (POS transaction). Primary key.
• location_id (string) — canonical location key.
• employee_id (string) — server/cashier who opened or closed the ticket.
• transaction_open_time_utc (timestamp)
• transaction_close_time_utc (timestamp)
• source_system (string) — e.g., POS name
• total_amount (decimal) — gross amount (pre-tax or post-tax per convention)
• tax_amount (decimal)
• discount_amount (decimal)
• payment_methods (json/text) — optional breakdown
• ingestion_timestamp_utc (timestamp)

2) sales_line (item-level)

• sales_line_id (string) — source unique id for the line (or composite key)
• transaction_id (string) — FK to sales_header
• item_id (string) — canonical menu/item id
• item_description (string) — denormalized at time of sale
• quantity (decimal)
• unit_price (decimal) — price at sale
• line_total (decimal)
• modifiers (json/text) — e.g., extra cheese, substitutions
• voided_flag (boolean) — true if voided
• sale_timestamp_utc (timestamp)
• source_system, source_id, ingestion_timestamp_utc

3) menu_item (canonical item/menu dimension)

• item_id (string) — canonical key (use surrogate if multiple POS codes exist)
• sku_code (string) — supplier or inventory SKU, if applicable
• item_name, item_category
• standard_recipe_id (string) — links to recipe/recipe_ingredient if available
• portion_cost_standard (decimal) — precomputed from recipe table for quick reporting
• active_flag, effective_from, effective_to — for SCD handling
• last_updated_by_source, last_updated_timestamp_utc

4) recipe and recipe_ingredient (optional but recommended)

• recipe_id, item_id
• ingredient_item_id, quantity_per_portion, unit_of_measure
• ingredient_unit_cost (decimal) — snapshot or linked to purchase history

5) inventory_transaction (movements)

• inventory_txn_id (string)
• location_id, item_id
• txn_type (enum) — receive, consumption, transfer_in, transfer_out, waste, adjustment, sale_linked
• quantity (decimal)
• unit_cost (decimal) — cost at receipt or valuation method
• reference_id (string) — e.g., PO id, sales_line_id for consumption, waste_report_id
• txn_timestamp_utc
• source_system, source_id, ingestion_timestamp_utc

6) purchase_order (receipts & supplier)

• po_id, supplier_id
• item_id, received_quantity, received_unit_cost
• receipt_timestamp_utc

7) stock_snapshot (periodic stock on hand)

• snapshot_id, location_id, item_id
• quantity_on_hand, snapshot_timestamp_utc
• snapshot_method (cycle_count / theoretical / POS-derived)

8) labor_shift or timesheet (employee time)

• timesheet_id (string)
• employee_id, location_id
• clock_in_time_utc, clock_out_time_utc
• paid_break_minutes, unpaid_break_minutes
• role (string) — cook, server, manager
• pay_rate (decimal) — snapshot for payroll calculations
• ingestion_timestamp_utc

9) employee dimension

• employee_id, full_name, hire_date, termination_date, primary_role
• cost_center or pay_group

10) location dimension

• location_id, name, timezone, address, open_hours

Unique keys, surrogate keys, and SCD guidance

When source systems use different codes for the same physical item or employee, create canonical surrogate keys in the item and employee dimensions. Maintain a mapping table with source_system + source_id -> canonical_id and ingestion_timestamp to support lookups and audits.

Use slowly changing dimension (SCD) type 2 for attributes you need to historicize (e.g., recipe changes or role changes that affect historical KPIs). For other attributes, SCD type 1 (overwrite) is fine.

Timestamps and time handling

• Store all raw event timestamps in UTC and include local_event_time and timezone in the row.
• Capture both event_timestamp (when the action occurred) and ingestion_timestamp (when you received the event) to detect late-arriving data.
• For shift-based metrics, derive a consistent shift table keyed by location and local date/time ranges rather than relying on POS shift labels.

Event types (recommended enums)

• sales_line: sale, refund, void
• inventory_txn: receive, consumption, waste, adjustment, transfer_in, transfer_out
• labor: clock_in, clock_out, break_start, break_end

Sample ETL and mapping notes

1. Deduplicate by (source_system, source_id). If your source can re-emit events, use ingestion_timestamp and row_hash to detect unchanged rows.
2. Join sales lines to inventory consumption using recipe yields where possible. When not available, map common sold items to theoretical ingredient usage via recipe tables for COGS estimations.
3. For food cost: prefer consumption-based inventory_txn (waste + consumption) reconciled against receipts and snapshots rather than only using recipe estimates.
4. Handle late-arriving POs or inventory receipts by backfilling and marking affected reporting days as updated. Keep a last_updated_timestamp per reporting period.
5. Timezone example: convert POS local_time to UTC at ingestion, and also store local_date for daily aggregation consistent with local business day.
6. Payroll mapping: use timesheet pay_rate snapshots to compute labor cost per shift. Reconcile aggregated payroll with HR/payroll exports to catch missed punches or adjustments.

KPI mapping examples

• Daily Food Cost = sum(inventory_txn where txn_type IN (consumption, waste) of quantity * unit_cost) over period.
• Food Cost % = Daily Food Cost / Total Food Sales — ensure both numerator and denominator use the same local day definition and exclude discounts or returns consistently.
• Labor % = Total Labor Cost (timesheet pay_rate * hours) / Net Sales — align payroll period and sales period carefully.
• Item-level GP = item_sales_line.line_total - estimated_item_ingedient_cost (from recipe * ingredient unit_cost at time of sale).

Reconciliation and data quality checks

• Transaction counts: compare number of sales transactions per day from POS export vs. ingest counts. Flag >1–2% discrepancy.
• Sales to inventory linkage: compare theoretical consumption (from sales lines * recipe) to inventory consumption_txn totals; investigate variance thresholds (e.g., >5–10%).
• Payroll vs timesheet: reconcile total payroll spend per pay period with timesheet computed labor to detect missing time entries.
• Freshness alerts: report last ingestion timestamp per source; alert if no update within SLA window (e.g., POS should arrive within 1–2 hours for near-real-time dashboards, inventory nightly).

Storage, performance and partitioning tips

• Partition large event tables by date (event_date) and cluster by location_id or item_id for common queries.
• Keep denormalized attributes used in dashboards (e.g., item_description, pay_rate_snapshot) to avoid costly joins at query time.
• Use incremental loads based on ingestion_timestamp or event timestamp ranges to speed ETL.

Common pitfalls & how to avoid them

• Inconsistent item IDs across systems — solve with a mapping table and canonical surrogate keys.
• Mismatched day boundaries — always align to location local date when computing daily KPIs.
• Using recipe estimates as a single source of truth for COGS — combine recipe-based theoretical consumption with physical inventory transactions and receipts for a more reliable result.
• Mixing event-time and ingestion-time for aggregation — choose event-time for business metrics and use ingestion-time for monitoring and backfill logic.

Quick implementation checklist

1. Create canonical dimensions and mapping tables for items, employees, and locations.
2. Ingest POS transaction header and line events with source ids, UTC timestamps, and local_event_time.
3. Ingest inventory transactions and purchase receipts, capturing txn_type and reference_ids.
4. Ingest labor punches/timesheets with pay_rate snapshot.
5. Build reconciliation reports: transaction counts, inventory vs theoretical consumption, payroll vs timesheet.
6. Document ETL rules, SCD policies, and timezone/shift definitions.

Why this helps

Defining a compact, event-centered schema and clear ETL rules reduces the common causes of inconsistent KPIs: mismatched keys, drifting master data, timezone errors, and late-arriving events. Teams can build dashboards with confidence and trace every metric back to source events for fast troubleshooting.

Next steps & capability ideas

Convert the canonical mapping table into an interactive onboarding form that lets a location map its local POS item codes to canonical item_id and store those mappings for automated ingestion. Schedule daily reconciliation jobs and surface exceptions to managers. Consider exposing a template JSON mapping file for connector vendors.