# curated.labor_shifts

## Purpose

`curated.labor_shifts` is the shared labor shift fact table in the warehouse.

It should answer the question: `what labor shift happened, who worked it, when, for how long, and what was the compensation context?`

## Why this table matters

- gives one row per labor shift
- provides the main labor measures: `minutes_worked`, `regular_minutes_worked`, `overtime_minutes_worked`, `extended_minutes_worked`
- keeps shift timing context such as `start_time`, `end_time`, and `business_date`
- keeps break aggregation context with `break_count` and `total_break_minutes`
- provides compensation context with `pay_rate` and `declared_tips`
- creates a stable contract for future cross-source labor cost and workforce analyses

## Grain

One row per labor shift.

## Column groups

- Business properties describe the shift: identity, timing, worked minutes breakdown, compensation, and break aggregation.
- Technical and ETL properties describe warehouse lineage, surrogate keys, source reconciliation, tenant scoping, and ingestion metadata.

## Business properties

| Column | Type | Nullable | Business Meaning | Notes |
|---|---|---|---|---|
| `shift_id` | `VARCHAR(36)` | No | Source-native shift identifier. | Preferred identifier for downstream joins. |
| `employee_id` | `BIGINT` | Yes | Employee who worked the shift. | Joins to `curated.employees`. |
| `job_id` | `BIGINT` | Yes | Job (workforce role) for the shift. | Joins to `curated.pos_jobs`. |
| `business_date` | `DATE` | Yes | Restaurant business date for reporting and ETL partitioning. | |
| `start_time` | `TIMESTAMPTZ` | Yes | Shift start time with timezone. | |
| `end_time` | `TIMESTAMPTZ` | Yes | Shift end time with timezone. | |
| `minutes_worked` | `INTEGER` | Yes | Total minutes worked during the shift. | |
| `regular_minutes_worked` | `INTEGER` | Yes | Regular (non-overtime) minutes worked. | |
| `overtime_minutes_worked` | `INTEGER` | Yes | Overtime minutes worked. | |
| `extended_minutes_worked` | `INTEGER` | Yes | Extended minutes worked beyond standard overtime. | |
| `pay_rate` | `DECIMAL(18, 4)` | Yes | Hourly pay rate for the shift. | |
| `declared_tips` | `DECIMAL(18, 4)` | Yes | Tips declared by the employee for the shift. | |
| `shift_number` | `SMALLINT` | Yes | Sequential shift number within the source system. | Maps from `brink_labor_shifts.number`. |
| `break_count` | `INTEGER` | Yes | Number of breaks taken during the shift. | Aggregated from `brink_labor_breaks` via LEFT JOIN + GROUP BY. |
| `total_break_minutes` | `INTEGER` | Yes | Total minutes of break time during the shift. | Aggregated from `brink_labor_breaks` via LEFT JOIN + GROUP BY. |
| `currency_code` | `VARCHAR(10)` | Yes | Currency of monetary values. | Required when curated data spans multiple currency contexts. |

## Technical and ETL properties

| Column | Type | Nullable | Technical Meaning | Notes |
|---|---|---|---|---|
| `source_system` | `VARCHAR(100)` | Yes | Canonical integration name used for lineage across source platforms. | Identifies which operational platform produced the record. |
| `source_shift_id` | `VARCHAR(199)` | Yes | Original source identifier kept for reconciliation and drill-through. | Preserves the native source key. |
| `labor_shift_key` | `BIGINT` | Yes | Warehouse-generated stable surrogate key for the labor shift entity. | Surrogate warehouse key for dimensional joins. |
| `tenant_key` | `BIGINT` | Yes | Warehouse surrogate key for tenant or brand scope. | |
| `tenant_name` | `VARCHAR(255)` | Yes | Tenant or brand identifier injected by the ETL context. | Human-readable tenant or brand lineage value. |
| `_etl_batch_id` | `VARCHAR(256)` | Yes | ETL batch lineage identifier. | |
| `_etl_source_type` | `VARCHAR(100)` | Yes | Source adapter type used by ETL. | |
| `_etl_ingested_at` | `TIMESTAMP` | No | Warehouse ingestion timestamp for the curated row. | Required ingestion audit timestamp. |