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. A line function with no default shift group gets no AvailableTime rows on days no override covers, so its workers can’t scan on those days.

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.

Rule Types

Type	Description	StartDate / EndDate	DayOfWeek
OneTime	A specific date or date range	Full date values; EndDate can’t be before StartDate	—
Annual	Repeats every year on the same month/day	Only month+day used; the year is ignored	—
Monthly	Repeats every month on the same day(s)	Only the day used; year and month are ignored	—
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.

An Annual or Monthly range can wrap: an Annual rule from Dec 25 to Jan 5 covers the end of one year and the start of the next, and a Monthly rule from the 28th to the 3rd covers the end of every month and the start of the next. Since only month and day are compared, a range entered backwards by mistake (e.g. Mar 10 to Mar 5) wraps too, and covers almost the whole year. The day off form, and the rule’s end date in the list, show “Wraps into the next year/month” for these rules, so a mistake can be spotted before saving.

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.

Time Zones

Value	Time zone
ShiftGroupShift timings and breaks	Factory local time
ShiftDate, day off rules, override dates	The factory’s calendar
AvailableTime / AvailableTimePart times	UTC
Every event timestamp (scans, end-line checks, audits, downtime, …)	UTC

Factory local time is UTC plus the general.utc_offset_minutes feature flag, a fixed offset in minutes (e.g. 300 for UTC+05:00, 330 for UTC+05:30).

Because AvailableTime is UTC, every lookup compares it directly with UTC event timestamps or GETUTCDATE(), with no conversion. The shift pages show its times in factory local time.

What Gets Generated

For each line function and each day from today to the end of the current year, in factory local time:

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, converted to UTC. 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

Available time is kept from today to the end of the current year, both in factory local time. It is regenerated automatically, in the same transaction as the change that requires it:

Change	Line functions regenerated
A shift is added to, updated in, or removed from a shift group	Every line function that uses the group, by default or override
A line function’s default shift group changes	That line function
An override is linked to, relinked, or unlinked from a line function	The line functions on both sides of the change
An override is deleted	The line functions it was linked to
A day off rule is added, updated, or deleted	All
The part size feature flag changes	All
The UTC offset feature flag changes	All

A background job generates the year on server start and at the start of each year in factory local time, when the year has no available time yet.

In the same transaction, it deletes the overrides whose date range ended before today (with their line functions, and the worker shifts for groups the line functions can no longer resolve to), the same as Delete past shifts. Overrides without a date range are kept. It also deletes the available time, and its parts, from before the previous year, e.g. 2025 and older once 2027 starts.

Regeneration deletes the affected line functions’ rows (and their parts) from today to the end of the year, then generates them again.

Regeneration never touches past entries. Today’s entries are only replaced for shifts that haven’t started yet (InTime after the current time). A shift that has already started is only added for today when the line function has nothing left for today, e.g. at the start of a new year or right after a group is first assigned. After a mid-day group change, the new group’s shifts that have already started aren’t added, so today’s working time isn’t counted twice.

The Regenerate button in the calendar tab regenerates every line function from today to the end of the year. It is only needed after the shift tables are edited outside the portal (e.g. directly in the database).

AvailableTime Schema

Column	Description
ShiftDate	The date of the shift, in the factory’s calendar
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, in UTC
EarlyOutTime / OutTime / LateOutTime	Resolved clock-out window, in UTC
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, UTC
EndAt	End of this contiguous work window, UTC

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.

WorkerShift needs 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. A worker without a row for the active group can’t scan.

Most of these rows are filled in automatically by matching ShiftID (the canonical shift, e.g. Morning). Existing rows are never changed.

Change	Rows added
A line function’s default shift group changes	The new default group follows the shift the worker had in the previous default group
An override is linked to or relinked on a line function	Each override group follows the shift the worker has in the default group
A shift is added to a shift group	Same as above, for every line function that uses the group
A worker is moved to another line function (workers tab)	The new default group follows the worker’s shift in the old default group, before saving

Rows for a group the worker’s line function can no longer resolve to are deleted: when its default shift group changes, when an override is unlinked, relinked or deleted (including Delete past shifts), or when the worker is moved to another line function.

When a group doesn’t have the worker’s shift (e.g. a Ramadan group with no Evening shift), no row is added. The workers tab shows these as missing, and saving the assignment tab shows a warning with the number of workers on the changed line functions that still need a shift.

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 time of the scan (UTC)

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 contains the scan time Look up Planning.AvailableTime by (LineFunctionID, ShiftGroupID, ShiftID) where the scan time falls between EarlyInTime and LateOutTime. Only one of the assigned shifts will match with this lookup.

The current shift of a line is found the same way by [Planning].[ufnGetCurrentShift](@UtcDateTime, @LineID, @LineFunction).

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.