Shift

The shift module defines working hours for factory lines.

Shifts in client factories are defined at the line level, where each line can have one or more shifts (“Sewing” and “Quality” might have two different shift timings).

This presents a challenge for us, since we can not have a single set of shifts defined.

We have divided lines into Line Functions, which are subdivisions of a line with their own shift timings.

Shifts are organized into shift groups.

Each line function has a default shift group, and shift overrides can swap a line function onto a different shift group for a date range and/or day of week.

Available time is a pre-generated table that resolves all of this for fast lookup at runtime, and is further split into available time parts around breaks.

Glossary

Term	Definition
Line Function	A subdivision of a line with its own shift timing ([Essentials].[LineFunction]). Each has its own schedule, driven by its default shift group.
Shift Group	A named bundle of shifts ([Planning].[ShiftGroup]). A line function’s schedule is whichever shift group is active for it.
Shift (Base Shift)	A canonical shift name, factory-wide ([Planning].[Shift]) — e.g. Morning, Evening. Carries no timing; can belong to any number of shift groups.
Shift Group Shift	A shift’s timing definition within one specific shift group ([Planning].[ShiftGroupShift]) — attendance window, durations, breaks. No row means that shift doesn’t run in that group.
Attendance Window	The six-point clock-in/clock-out span of a ShiftGroupShift: EarlyInTime, InTime, LateInTime, EarlyOutTime, OutTime, LateOutTime.
Break	A JSON-defined gap within a ShiftGroupShift’s attendance window (e.g. lunch) during which no work is scheduled and no AvailableTimePart is generated.
Shift Override	A date-range/day-of-week window ([Planning].[ShiftOverride]) during which linked line functions use a different shift group instead of their default. (Friday, Ramadan, etc.)
Day Off Rule	A global rule ([Planning].[DayOffRule]) marking dates as non-working for the entire factory, regardless of shifts or overrides.
Available Time	A pre-generated table ([Planning].[AvailableTime]) storing resolved shift timings per line function × day; the only table read at runtime.
Available Time Part	A fixed-size chunk ([Planning].[AvailableTimePart]) of an AvailableTime entry’s work window, used for sub-shift scheduling/tracking.
Work Segment	A contiguous span of an attendance window that isn’t break time — the unit that gets chopped into available time parts.
Part Size	The configurable chunk duration (e.g. 60 minutes) used to split each work segment into available time parts. Set via feature flag.
Worker Shift	A mapping ([Planning].[WorkerShift]) pinning a worker to one specific ShiftGroupShift (a Shift’s timing within a shift group) that applies to their line function.

Background: Line Functions

Lines are divided into line functions ([Essentials].[LineFunction]).

Each line can have multiple line functions. Currently there are 2 auto-generated for each line (‘sewing’, ‘quality’)

Each line function has a default shift group via ShiftGroupID.

Sewing department lines have 2 line functions: Production and Quality. All the non-Sewing lines have 1 line function: Default

The LineFunctionLabel column holds these labels (Production, Quality, Default). The LineFunctionName value follows the pattern {FactoryCode}-{BranchCode}-{DepartmentName}-{LineCode}-{LineFunctionLabel}

Caution

LineFunction.ShiftGroupID is nullable, but every line function must resolve to a shift group — via its default or a matching override — for every day being generated. Available time generation errors if a line function is missing a default shift group.

Overrides can target one or more specific line functions to temporarily apply a different shift group for a date range or day of week.

Shift Groups

Shifts are organized into shift groups ([Planning].[ShiftGroup]), identified by ShiftGroupName.

Each distinct shift timing schedule is a shift group (e.g. Sewing, Quality, Single Piece).

Which shifts belong to a shift group, and their timing there, is defined by [Planning].[ShiftGroupShift] rows.

A shift can belong to any number of shift groups, with independent timing in each.

A line function’s schedule is determined entirely by which shift group is active for it on a given day: its default shift group (LineFunction.ShiftGroupID), or an overriding shift group if a matching ShiftOverride applies.

To give a line function different timings permanently, change its default ShiftGroupID directly — no override needed. Overrides exist only for temporary switches (date ranges, specific weekdays).

Shifts

[Planning].[Shift] holds only ShiftID and ShiftName — it’s the canonical, factory-wide list of shift names (e.g. Morning, Evening, Night). It carries no timing.

A shift’s actual timing is per shift group, in [Planning].[ShiftGroupShift]. Each row ties one ShiftID to one ShiftGroupID with its own attendance window, durations, and breaks. If a shift has no ShiftGroupShift row for a given group, it simply doesn’t run in that group — the same Shift (e.g. Morning) can run at different times in different groups, or not run at all in some.

ShiftGroupShift Schema

Column	Description
ShiftGroupShiftID	Primary key
ShiftGroupID	The shift group this timing applies to
ShiftID	The (canonical) shift this timing applies to

(ShiftGroupID, ShiftID) is unique — a shift can have at most one timing definition per group. The remaining columns (attendance window, durations, breaks) are described below.

Multiple shifts can exist within a shift group simultaneously (e.g. Morning, Evening, Night).

All shifts with a ShiftGroupShift row in a line function’s active shift group apply on a given day.

Attendance Window

Each ShiftGroupShift row defines a six-point attendance window:

Field	Description
EarlyInTime	Earliest allowed clock-in
InTime	Scheduled start time
LateInTime	Latest allowed clock-in
EarlyOutTime	Earliest allowed clock-out
OutTime	Scheduled end time
LateOutTime	Latest allowed clock-out

The maximum possible shift span runs from EarlyInTime to LateOutTime.

The normal shift span runs from InTime to OutTime.

Duration Fields

All durations are in minutes and exclude break time.

Field	Description
ShiftMinutes	Normal shift duration (OutTime − InTime)
MaxShiftMinutes	Maximum shift duration (LateOutTime − EarlyInTime)
BreakMinutes	Total break duration

Breaks

Breaks are stored as a JSON array in the Breaks column:

[
  { "start": "12:00:00", "end": "12:30:00" },
  { "start": "15:00:00", "end": "15:15:00" }
]

BreakMinutes is the sum of all break durations.

ShiftMinutes and MaxShiftMinutes are calculated after subtracting BreakMinutes.

Breaks also determine how a generated AvailableTime entry is split into AvailableTimePart windows — see Splitting into Parts.

Overrides

[Planning].[ShiftOverride] defines a date/day window during which specific line functions should temporarily use a different shift group.

Overrides don’t carry their own timings — they only select an existing shift group to substitute in. To give a line function custom one-off timings, create a new shift group with the desired shifts first, then override into it.

Overrides can have different timing for each shifts, and can even remove a shift from a line function’s schedule.

Scope

Each override narrows its scope using one or both of two optional filters. At least one filter must be set.

Filter	Fields	Description
Date Range	StartDate + EndDate	Applies only within this date range (both fields must be set together)
Day of Week	DayOfWeek	Applies only on this day (e.g., "Friday")

Applying to Line Functions

An override has no effect on its own — it must be linked to one or more line functions via [Planning].[ShiftOverrideLineFunction]. Each link specifies:

Field	Description
LineFunctionID	Which line function this override applies to
ShiftGroupID	Which shift group replaces the line function’s default during the window

There’s no implicit “all line functions” override — link every line function you want covered explicitly. A single override can link multiple line functions, each to the same or a different shift group.

Precedence

When multiple overrides link the same line function and both match a given date, the following rules determine which one applies:

Rule 1 — More filters wins. An override with both filters set takes precedence over one with a single filter.

Rule 2 — Tie-break by filter rank. When two overrides have the same number of filters, compare their highest-ranked filter. The filter rank is: DateRange > DayOfWeek.

Full precedence order (highest to lowest):

Filters set	Filter count	Score
DateRange + DayOfWeek	2	6 ((2 + 1) * 2)
DateRange only	1	2
DayOfWeek only	1	1

Note

Filter combination score is calculated by adding individual filter scores and multiplying it by the filter count.

Examples

Example 1 — Shorter Friday shift for two lines

Create a shift group (e.g. "Sewing Friday") with the adjusted shift timings.

Add an override with DayOfWeek = "Friday" (leave StartDate/EndDate null).

Link it to each line function that should use the shorter Friday schedule.

Every base shift group (Sewing, Quality, Single Piece, etc.) needs to have a separate Friday group if you want to change the timings for each.

---

Example 2 — Ramadan timings across the factory

Create a shift group (e.g. "Sewing Ramadan") with the adjusted shift timings.

Add an override with StartDate = 2026-02-17 and EndDate = 2026-03-19 (leave DayOfWeek null).

Link it to every line function that observes Ramadan hours.

Every base shift group (Sewing, Quality, Single Piece, etc.) needs to have a separate Ramadan group if you want to change the timings for each.

---

Example 3 — Ramadan Fridays get their own schedule (coexisting with Examples 1 and 2)

Create a shift group ("Sewing Ramadan Friday").

Add a third override with the same StartDate/EndDate range and DayOfWeek = "Friday", linked to the same line functions.

This 2-filter override outranks both 1-filter overrides above, so on Fridays during Ramadan it wins.

---

Example 4 — Single Piece has a different timing schedule

Create a new Shift Group ("Single Piece") with distinct shift timings. The group can even have different number of shifts.

Assign the new shift group to all line functions for all single piece lines.

---

Example 5 — Disable the evening shift for Sewing during Ramadan

Create a Ramadan shift group ("Sewing Ramadan Night") with the adjusted shift timings. Do no include the evening shift.

Add an override with StartDate/EndDate set to the Ramadan range, linked to that line function, pointing at the new shift group.

No AvailableTime rows are generated for the evening shift for that line function during Ramadan.

Day Off Rules

Day off rules are defined in [Planning].[DayOffRule]. Each rule is global — it applies to all line functions and all shifts on matching dates.

When a date matches any day off rule, all AvailableTime entries for that day are generated with IsDisabled = 1, regardless of what shifts or overrides exist.

Note

Day off rules are distinct from shift overrides. They carry no timing data — they simply mark dates as non-working days across the entire factory.

Caution

Regenerate available time after any change to day off rules to keep AvailableTime in sync.

Rule Types

Type	Description	StartDate / EndDate	DayOfWeek
OneTime	A specific date or date range	Full date values	—
Annual	Repeats every year on the same month/day	Sentinel year 1900; only month+day used	—
Monthly	Repeats every month on the same day(s)	Sentinel year+month 1900-01; only day used	—
DayOfWeek	Repeats every week on a given weekday	—	Required

StartDate and EndDate are required for OneTime, Annual, and Monthly rules. For a single-day rule, set it equal to StartDate.

DayOfWeek rules leave both StartDate and EndDate null.

For DayOfWeek, one row per weekday. A “Weekend” rule covering Saturday and Sunday requires two rows sharing the same RuleName.

Available Time Generation

[Planning].[AvailableTime] is a pre-generated table storing the resolved shift timings for every line function × day combination.

This is the table queried at runtime for shift information — lookups never touch the shift group, base shift, or override tables directly.

What Gets Generated

For each line function and each day in the selected year:

1. The active shift group is resolved: the highest-precedence matching override’s shift group, or the line function’s default ShiftGroupID if none match.
2. All shifts with a ShiftGroupShift row in that shift group are evaluated.
3. One AvailableTime row is written per shift, storing the resolved ShiftGroupID alongside that ShiftGroupShift row’s own timings. IsOverride is set to 1 if the resolved group came from a matching override rather than the line function’s default.
4. IsDisabled is set to 1 if the date matches any day off rule.
5. Each AvailableTime row is split into one or more [Planning].[AvailableTimePart] rows — see Splitting into Parts.

A single day can have multiple AvailableTime rows if multiple shifts are active in the resolved shift group for that line function (e.g., both a morning and a night shift).

Splitting into Parts

[Planning].[AvailableTimePart] breaks an AvailableTime entry’s work window into fixed-size, contiguous chunks — useful for scheduling and progress-tracking at sub-shift granularity.

For each shift, the attendance window (EarlyInTime to LateOutTime) is first divided at its breaks into work segments — the spans of time that aren’t break time. Each work segment is then chopped into equal-length chunks of the configured part size, starting from the segment’s start. If a segment doesn’t divide evenly, the final chunk is a shorter remainder rather than being merged with an adjacent segment.

Breaks themselves never get an AvailableTimePart row.

Parts are not generated for disabled Available Time rows.

Part size is configured via feature flag (shift.available_time_part_minutes) — it isn’t hardcoded, so a 60-minute default can be tuned (e.g. to 30 or 15 minutes) without a schema change.

The diagram below shows a shift running 08:00–17:30 with two breaks (10:15–11:00 and 14:00–14:15), split with a 60-minute part size. The first and third work segments (135 and 195 minutes) each end in a shorter remainder part; the middle segment (180 minutes) divides evenly into three full parts.

Triggering Generation

Generation is triggered manually in the portal via a Generate button with a year picker.

Only shift times for the current and next year can be generated.

Only entries from today onward are deleted and regenerated. Past entries are left untouched. This includes their AvailableTimePart rows, which are regenerated alongside their parent AvailableTime row.

Caution

Regenerate after any change to shift groups (including which shifts belong to them), overrides, day off rules, or the part size feature flag to keep AvailableTime and AvailableTimePart in sync.

AvailableTime Schema

Column	Description
ShiftDate	The specific date
ShiftID	Reference to the canonical shift — timings below were snapshotted from the resolved shift group’s ShiftGroupShift row
ShiftGroupID	The shift group resolved for this line function × day (default ShiftGroupID, or an override’s if one matched)
LineFunctionID	Reference to the line function
IsDisabled	1 if the entry is a day off
IsOverride	1 if ShiftGroupID was resolved from a matching ShiftOverride rather than the line function’s default
EarlyInTime / InTime / LateInTime	Resolved clock-in window
EarlyOutTime / OutTime / LateOutTime	Resolved clock-out window
ShiftMinutes	Normal shift duration (excluding breaks)
MaxShiftMinutes	Maximum shift duration (excluding breaks)
BreakMinutes	Total break duration
OvertimeMinutes	Computed: MaxShiftMinutes − ShiftMinutes

AvailableTimePart Schema

Column	Description
AvailableTimePartID	Primary key
AvailableTimeID	Reference to the parent AvailableTime row
StartAt	Start of this contiguous work window
EndAt	End of this contiguous work window

Assigning Shifts to Workers

Each worker belongs to exactly one line function (Essentials.Worker.LineFunctionID). A line function can have several shift groups apply to it over time — its default (LineFunction.ShiftGroupID) plus any it’s linked to via ShiftOverrideLineFunction — but on any given day exactly one of them is active for that line function (the same resolution AvailableTime generation uses).

A line function’s shift group tells you the set of shifts running that day (e.g. Morning + Evening for Sewing), but not which one a specific worker should scan under. [Planning].[WorkerShift] fills that gap: it pins a worker to one Shift for a given ShiftGroupID.

WorkerShift Schema

Column	Description
WorkerShiftID	Primary key
WorkerID	The worker this mapping applies to
ShiftGroupID	The shift group this mapping applies to
ShiftGroupShiftID	The specific ShiftGroupShift (shift + its timing) within that group the worker is assigned to

A worker has at most one row per shift group — (WorkerID, ShiftGroupID) is unique. This is what lets a line function run multiple concurrent shifts (Morning, Evening, Night) while still knowing which single one any given worker belongs to.

ShiftGroupShiftID’s own ShiftGroupID must always match this row’s ShiftGroupID — enforced with a composite foreign key on (ShiftGroupShiftID, ShiftGroupID) rather than left as an application-level assumption.

Caution

WorkerShift must have a row for every shift group that can become active for a worker’s line function — its default ShiftGroupID and every ShiftGroupID it’s linked to through ShiftOverrideLineFunction. Whenever an override is added to or removed from a line function, the WorkerShift rows for that work group’s workers must be updated to match (add mappings for a newly-linked shift group, remove them for one that’s no longer linked).

Resolving a Worker’s Shift at Scan Time

When a worker scans, we need to know the assigned shift for the worker on that date. The worker is only allowed to scan in that shift on the given date.

We have the following information

• The line function the worker belongs to Essentials.Worker.LineFunctionID
• The date the worker is scanning

Resolving the shift is a two-step lookup:

1. Which shift groups are assigned to the user? Look up Planning.WorkerShift to get all the shift groups and shifts assigned to the worker overall. This could be a single shift (Morning in the Sewing group) or multiple (Morning in Sewing group, Evening in Sewing Ramadan). We will get the (ShiftGroupID, ShiftID) from this lookup.
2. Find which of these assigned shifts is available on the given date Look up Planning.AvailableTime by (LineFunctionID, ShiftDate, ShiftGroupID, ShiftID). Only one of the assigned shifts will match with this lookup.

The worker’s scan is valid only against that resolved ShiftGroupShift’s attendance window — a worker mapped to “Morning” can’t scan during “Evening”, even though both belong to the same active shift group.