Labor Data Model Specification

Munu Data Store (MDS) - Labor Data Dataset

Version: 1.1 Last Updated: 2025-12-10 Dataset Path: /labor-data

Overview

The Labor Data dataset provides a canonical data model for storing workforce management data from multiple source systems (Planday, Personalkollen, Quinyx, Timegrip, and others) in the Munu Data Store (MDS). This enables correlation of personnel costs with sales data and supports forecasting and budgeting purposes.

Primary Use Cases

1. Historical Cost Analysis: Correlate actual labor costs with sales data by employee, department, role, and time period
2. Forecasting & Budgeting: Use planned shift and cost data for future planning and budget preparation
3. Cross-System Analytics: Normalize data from multiple workforce management systems into a unified model

Design Principles

1. Source System Agnostic: Model accommodates data from any workforce management system
2. Master Data References: Links to existing MDS master data (employees, departments, business units) via IDs
3. Separation of Concerns: Master/structural data stored separately from time-series operational data
4. Daily Granularity: Time-based data organized by year/month/day for efficient querying
5. Cost Transparency: Primary total cost with optional breakdown fields for detailed analysis
6. Dual Timeline Support: Both scheduled (planned) and actual (realized) shifts in unified structure
7. snake_case Convention: All JSON property names use snake_case for consistency

Storage Structure

/labor-data/
├── metadata.json                          # Dataset metadata and schema version
├── source_systems.json                    # Registry of connected source systems
├── cost_categories.json                   # Cost type definitions and mappings
├── shift_types.json                       # Shift type classifications
├── {year}/                                # e.g., 2024/, 2025/
│   └── {month}/                           # e.g., 01/, 02/, ... 12/
│       ├── shifts-{YYYY-MM-DD}.json       # Daily shift records
│       └── _metadata.json                 # Optional monthly processing metadata

File Naming Conventions

• Date Format: ISO 8601 (YYYY-MM-DD)
• Year Folders: 4-digit year (e.g., 2024)
• Month Folders: 2-digit month, zero-padded (e.g., 01, 12)
• Shift Files: shifts-{YYYY-MM-DD}.json

Data Entities

1. Source Systems Registry

File: /labor-data/source_systems.json

Defines the workforce management systems integrated into the dataset.

{
  "version": "1.0",
  "last_updated": "2025-12-03T10:30:00Z",
  "systems": [
    {
      "system_id": "planday",
      "system_name": "Planday",
      "system_type": "workforce-management",
      "is_active": true,
      "implemented_date": "2025-01-15"
    },
    {
      "system_id": "personalkollen",
      "system_name": "Personalkollen",
      "system_type": "workforce-management",
      "is_active": false,
      "implemented_date": null
    }
  ]
}

Fields:

• system_id (string, required): Unique identifier for the source system (lowercase, hyphenated)
• system_name (string, required): Display name of the system
• system_type (string, required): Classification of system type
• is_active (boolean, required): Whether system is currently syncing data
• implemented_date (ISO 8601 date, nullable): Date when integration went live

2. Cost Categories

File: /labor-data/cost_categories.json

Defines standard cost breakdown categories used across systems.

{
  "version": "1.0",
  "last_updated": "2025-12-03T10:30:00Z",
  "categories": [
    {
      "category_id": "base-salary",
      "category_name": "Base Salary",
      "description": "Base hourly or shift wage without supplements",
      "is_standard": true
    },
    {
      "category_id": "taxes",
      "category_name": "Employer Taxes",
      "description": "Mandatory employer tax contributions",
      "is_standard": true
    },
    {
      "category_id": "benefits",
      "category_name": "Benefits & Insurance",
      "description": "Employer-paid benefits and insurance costs",
      "is_standard": true
    },
    {
      "category_id": "supplements",
      "category_name": "Supplements",
      "description": "Additional pay (overtime, late hours, bonuses)",
      "is_standard": true
    },
    {
      "category_id": "total",
      "category_name": "Total Cost",
      "description": "All-inclusive labor cost",
      "is_standard": true
    }
  ]
}

Fields:

• category_id (string, required): Unique identifier for cost category
• category_name (string, required): Display name
• description (string, optional): Explanation of what’s included
• is_standard (boolean, required): Whether this is a standard category across all systems

3. Shift Types

File: /labor-data/shift_types.json

Defines classifications for different shift types.

{
  "version": "1.0",
  "last_updated": "2025-12-03T10:30:00Z",
  "types": [
    {
      "type_id": "regular",
      "type_name": "Regular Shift",
      "description": "Standard scheduled work shift",
      "is_counted_in_payroll": true
    },
    {
      "type_id": "overtime",
      "type_name": "Overtime",
      "description": "Hours worked beyond standard schedule",
      "is_counted_in_payroll": true
    },
    {
      "type_id": "absence",
      "type_name": "Absence",
      "description": "Paid or unpaid absence",
      "is_counted_in_payroll": false
    },
    {
      "type_id": "leave",
      "type_name": "Leave",
      "description": "Vacation, sick leave, or other leave types",
      "is_counted_in_payroll": true
    }
  ]
}

Fields:

• type_id (string, required): Unique identifier for shift type
• type_name (string, required): Display name
• description (string, optional): Explanation of shift type
• is_counted_in_payroll (boolean, required): Whether included in payroll calculations

4. Daily Shift Records

File: /labor-data/{year}/{month}/shifts-{YYYY-MM-DD}.json

Contains all shift records (both scheduled and actual) for a single day.

{
  "version": "1.0",
  "date": "2025-01-15",
  "record_count": 143,
  "last_updated": "2025-01-16T02:30:00Z",
  "shifts": [
    {
      "shift_id": "550e8400-e29b-41d4-a716-446655440000",
      "source_system": "planday",
      "source_shift_id": "12345",
      "shift_status": "completed",

      "employee_id": "emp-001",
      "revenue_unit_id": 101,
      "role_id": "role-205",
      "position_id": "pos-42",

      "shift_date": "2025-01-15",
      "shift_type": "regular",

      "scheduled_start_time": "2025-01-15T08:00:00Z",
      "scheduled_end_time": "2025-01-15T16:00:00Z",
      "scheduled_duration_minutes": 480,

      "actual_start_time": "2025-01-15T08:05:00Z",
      "actual_end_time": "2025-01-15T16:10:00Z",
      "actual_duration_minutes": 485,

      "cost": {
        "total_cost": 1250.00,
        "currency": "NOK",
        "breakdown": {
          "base_salary": 960.00,
          "taxes": 200.00,
          "benefits": 90.00,
          "supplements": 0.00
        }
      },

      "metadata": {
        "created_at": "2025-01-10T12:00:00Z",
        "updated_at": "2025-01-16T02:15:00Z",
        "synced_at": "2025-01-16T02:30:00Z"
      }
    }
  ]
}

Shift Record Fields

Core Identifiers:

• shift_id (string/UUID, required): Canonical unique identifier for this shift in MDS
• source_system (string, required): Reference to system in source_systems.json (e.g., “planday”)
• source_shift_id (string, required): Original shift ID from source system
• shift_status (enum, required): Status of the shift
  • scheduled: Planned but not yet occurred
  • in_progress: Currently active
  • completed: Finished and finalized
  • cancelled: Cancelled shift
  • no_show: Employee did not show up

Master Data References:

• employee_id (string, required): Reference to MDS employee master data
• revenue_unit_id (integer/string, required): Reference to MDS revenue unit (see masterdata/revenue-units.md)
• role_id (string, optional): Reference to role/job function in MDS
• position_id (string, optional): Reference to specific position in MDS

Shift Classification:

• shift_date (ISO 8601 date, required): Date of the shift (YYYY-MM-DD)
• shift_type (string, required): Reference to shift type from shift_types.json

Scheduled Time Information:

• scheduled_start_time (ISO 8601 datetime, required): Planned start time with timezone
• scheduled_end_time (ISO 8601 datetime, required): Planned end time with timezone
• scheduled_duration_minutes (integer, required): Planned duration in minutes

Actual Time Information:

• actual_start_time (ISO 8601 datetime, nullable): Actual clock-in time with timezone
• actual_end_time (ISO 8601 datetime, nullable): Actual clock-out time with timezone
• actual_duration_minutes (integer, nullable): Actual worked duration in minutes (breaks deducted)

Cost Information:

• cost (object, required):
  • total_cost (decimal, required): Primary all-inclusive labor cost
  • currency (ISO 4217 code, required): Three-letter currency code (e.g., “NOK”, “SEK”, “EUR”)
  • breakdown (object, optional): Detailed cost components
    • base_salary (decimal, optional): Base wage component
    • taxes (decimal, optional): Employer tax component
    • benefits (decimal, optional): Benefits and insurance component
    • supplements (decimal, optional): Additional pay (overtime, bonuses, etc.)

Metadata:

• metadata (object, required):
  • created_at (ISO 8601 datetime, required): When shift was first created in source system
  • updated_at (ISO 8601 datetime, required): When shift was last modified in source system
  • synced_at (ISO 8601 datetime, required): When shift was last synced to MDS

Data Quality Rules

Required Fields

All shifts MUST have:

• Core identifiers (shift_id, source_system, source_shift_id)
• Master data references (employee_id, revenue_unit_id)
• Shift date and type
• Scheduled time information (start, end, duration) - nullable for shifts without times
• Total cost and currency (cost may be null for scheduled shifts)
• Metadata timestamps

Nullable Fields

These fields may be null for scheduled (not yet occurred) shifts:

• actual_start_time, actual_end_time, actual_duration_minutes
• cost.breakdown (optional detailed breakdown)

Data Validation Rules

1. Time Consistency: scheduled_end_time must be after scheduled_start_time
2. Actual vs Scheduled: If actual_start_time is present, actual_end_time must also be present
3. Duration Calculation: Duration should match time difference (allowing for system rounding)
4. Status Logic:
   • scheduled shifts: actual_start_time must be null
   • completed shifts: actual_start_time and actual_end_time must be present
   • cancelled shifts: cost may be zero or cancellation fee
5. Currency Consistency: All costs within a single shift must use the same currency
6. Date Alignment: Shift must be stored in folder matching shift_date

Entity Diagram

---
title: Labor Data Model
---
erDiagram
    SOURCE_SYSTEM {
        string system_id PK
        string system_name
        string system_type
        boolean is_active
        date implemented_date
    }

    SHIFT_TYPE {
        string type_id PK
        string type_name
        string description
        boolean is_counted_in_payroll
    }

    SHIFT {
        uuid shift_id PK
        string source_system FK
        string source_shift_id
        enum shift_status
        string employee_id FK
        int revenue_unit_id FK
        string role_id FK
        string position_id FK
        date shift_date
        string shift_type FK
        datetime scheduled_start_time
        datetime scheduled_end_time
        int scheduled_duration_minutes
        datetime actual_start_time
        datetime actual_end_time
        int actual_duration_minutes
        decimal total_cost
        string currency
        decimal base_salary
        decimal taxes
        decimal benefits
        decimal supplements
        datetime created_at
        datetime updated_at
        datetime synced_at
    }

    EMPLOYEE {
        string employee_id PK
        string name
    }

    REVENUE_UNIT {
        int id PK
        string name
        int customerId FK
    }

    ROLE {
        string role_id PK
        string name
    }

    POSITION {
        string position_id PK
        string name
    }

    SHIFT }o--|| SOURCE_SYSTEM : "comes from"
    SHIFT }o--|| SHIFT_TYPE : "classified as"
    SHIFT }o--|| EMPLOYEE : "worked by"
    SHIFT }o--|| REVENUE_UNIT : "assigned to"
    SHIFT }o--o| ROLE : "performed as"
    SHIFT }o--o| POSITION : "fills"

Related Documentation

• Master Data Reference
• Revenue Units

Version History