Question:
I’m building a robust attendance system using Express and MongoDB with Mongoose. The system has to handle a complex multi-shift setup with dynamic shift times that vary daily. Here’s a summary of the requirements and some of the challenges I’m facing:
Requirements
Dynamic Shifts:
Each department has several shifts, e.g., shift 1: 00:00-08:00, shift 2: 08:00-16:00, shift 3: 16:00-00:00.
Attendance scans can occur close to shift boundaries, which might push them to the next day or affect the prior day.
Attendance Events:
Only certain status types, like ‘presence’ and ‘holiday_presence’, have events like check_in, check_out, break_start, and break_end.
Key events (check_in and check_out) need special handling. A check_in can occur up to 2 hours before the shift, and a check_out can happen up to 14 hours after check_in.
Time Zones:
Attendance times need to be processed in Asia/Makassar timezone using moment-timezone.
Edge Cases:
Users may scan late in one day or early the next, creating overlaps.
Handling users who forget to check_out and then attempt to check_in the next day.
Validating breaks or other events, ensuring they fall within a user’s shift time.
Current Approach
Here’s the Attendance and Department schemas:
const attendanceStatus = ['presence', 'holiday_presence', 'off', 'leave', 'skd', 'service', 'leave_special', 'transport', 'holiday', 'absent'];
const AttendancesSchema = new Schema(
{
user: {
type: mongoose.Schema.Types.ObjectId,
ref: 'Users',
required: true,
index: true,
},
date: {
type: Date,
default: Date.now,
index: true,
},
status: {
type: String,
enum: attendanceStatus,
default: 'presence',
},
attendance_details: {
shift: {
type: Number,
},
work_time: {
time_in: {
type: Date,
required: true,
},
time_out: {
type: Date,
required: true,
},
},
biometric_details: {
unit: {
type: Schema.Types.ObjectId,
ref: 'Biometrics',
},
pin: {
type: Number,
},
},
},
overtime_details: {
is_overtime: {
type: Boolean,
default: false,
},
overtime_duration: {
type: Number,
default: 0,
min: 0,
},
},
events: [
{
_id: false,
type: {
type: String,
enum: ['check_in', 'check_out', 'break_start', 'break_end', 'off_start', 'off_end', 'service_start', 'service_end'],
},
time: {
type: Date,
},
note: {
type: String,
},
},
],
note: {
type: String,
},
},
{
timestamps: true,
collection: 'attendances',
}
);
const Departments = new Schema(
{
name: {
type: String,
required: true,
trim: true,
},
work_duration: {
type: Number,
required: true,
default: 8,
min: 8,
max: 9,
},
half_day_duration: {
type: Number,
min: 1,
max: 9,
default: 6,
},
work_times: [
{
_id: false,
shift: {
type: Number,
},
time_in: {
type: Date,
required: true,
},
},
],
},
{
timestamps: true,
collection: 'departments',
}
);
Challenges
I’m currently facing several issues that I need guidance on:
Determining Shift for Each Scan: When a scan is received, I need to:
Identify if it should be attributed to yesterday, today, or tomorrow based on shift start and end times.
Handle edge cases, such as late-night shifts that technically fall into the next calendar day.
Event Validation and Updates:
If a check_in is recorded without a check_out, and the next scan is a check_in, should I treat this as a check_out for the previous day?
How to handle events that don’t directly relate to check_in/check_out but are other events like break_start or break_end.
Timezone-Specific Calculations:
I’m using moment-timezone to manage timestamps, but handling multiple overlapping shifts across days is getting complex. Any recommended patterns for dealing with timezone-sensitive calculations for multi-day shifts?
Algorithm Approach:
What are the best practices to ensure that each scan data point is accurately assigned to the correct date, shift, and event, especially considering overlapping shifts, late-night entries, and multi-day shifts?
Current Code
const example_webhookData = {
"cloud_id": "XXXXXXXXXXXXX",
"data": {
"pin": "24",
"scan": "2024-09-22 07:29:07",
}
}
const deviceTimezone = 'Asia/Makassar';
const attendanceTimeUTC = momentTz.tz(webhookData.data.scan, deviceTimezone).utc();
const attendanceTimeUTC = momentTz.tz(webhookData.data.scan, deviceTimezone).utc();
const attendanceTimeWITA = momentTz.tz(webhookData.data.scan, deviceTimezone);
const isCustomTime = users.attendance_details.work_time.is_custom_time;
const departmentWorkTimes = dataDepartment.work_times;
if (!isCustomTime && departmentWorkTimes.length > 1) {
const shiftIndex = findValidShiftIndex(attendanceTimeUTC, attendanceTimeWITA, departmentWorkTimes);
let { shiftTimeIn, shiftTimeInMin, shiftTimeInMax } = calculateShiftTimeIn(shiftIndex, attendanceTimeUTC, attendanceTimeWITA, departmentWorkTimes, deviceTimezone);
let shiftTimeOut = moment.utc(calculateTimeOut(shiftTimeIn.toDate(), dataDepartment.work_duration)).seconds(0).milliseconds(0);
// Adjust shift time out if it's a holiday or a weekend scenario
if (
(!users?.attendance_details?.holiday?.is_dynamic &&
users?.attendance_details?.holiday?.week_day !== 0 &&
momentTz.utc(attendanceTimeUTC).tz(deviceTimezone).add(1, 'days').day() === users?.attendance_details?.holiday?.week_day) ||
(momentTz.utc(attendanceTimeUTC).tz(deviceTimezone).day() === 6 && users?.attendance_details?.holiday?.week_day === 0)
) {
shiftTimeOut = shiftTimeIn
.clone()
.add(dataDepartment?.half_day_duration || 6, 'hours')
.utc();
}
let attendanceDate = determineAttendanceDate(attendanceTimeWITA, shiftTimeIn, shiftTimeOut);
const todayShiftStart = shiftTimeIn.clone().tz(deviceTimezone).startOf('day');
const todayShiftEnd = shiftTimeIn.clone().tz(deviceTimezone).endOf('day');
const prevDayStart = todayShiftStart.clone().tz(deviceTimezone).endOf('day').subtract(1, 'days');
const prevDayEnd = todayShiftEnd.clone().tz(deviceTimezone).endOf('day').subtract(1, 'days');
const existingYesterdayAttendance = await Attendances.findOne({
user: users._id,
date: { $gte: prevDayStart.toDate(), $lte: prevDayEnd.toDate() },
});
const existingTodayAttendance = await Attendances.findOne({
user: users._id,
date: { $gte: todayShiftStart.toDate(), $lte: todayShiftStart.toDate() },
});
if (existingYesterdayAttendance && !existingYesterdayAttendance.events.find((event) => event.type === 'check_out')) {
const isDuplicateEvent = existingYesterdayAttendance.events.some((event) => moment(event.time).isSame(attendanceTimeUTC));
if (isDuplicateEvent) {
console.log('Skipping duplicate event');
return res.status(200).end();
}
if (moment(attendanceTimeUTC).isSameOrAfter(yesterdayTimeWorkOut) && moment(attendanceTimeUTC).isBefore(yesterdayTimeWorkOutMax)) {
existingYesterdayAttendance.events.push({
type: 'check_out',
time: attendanceTimeUTC.toDate(),
});
await existingYesterdayAttendance.save();
resAttendanceEmit = existingYesterdayAttendance;
} else if (moment(attendanceTimeUTC).isBefore(yesterdayTimeWorkOut)) {
let attendanceType = 'check_in';
const lastEvent = existingYesterdayAttendance.events[existingYesterdayAttendance.events.length - 1];
if (lastEvent.type === 'check_in' && existingYesterdayAttendance.events.length === 1) {
attendanceType = 'break_start';
} else if (lastEvent.type.endsWith('_start') || lastEvent.type.endsWith('_end')) {
const correspondingEvent = getAfterEventType(lastEvent.type);
if (correspondingEvent) {
attendanceType = correspondingEvent;
}
}
existingYesterdayAttendance.events.push({
type: attendanceType,
time: attendanceTimeUTC.toDate(),
});
await existingYesterdayAttendance.save();
resAttendanceEmit = existingYesterdayAttendance;
} else {
if (existingTodayAttendance) {
const isDuplicateEvent = existingTodayAttendance.events.some((event) => moment(event.time).isSame(attendanceTimeUTC));
if (isDuplicateEvent) {
console.log('Skipping duplicate event');
return res.status(200).end();
}
if (moment(attendanceTimeUTC).isSameOrAfter(todayTimeWorkOut) && moment(attendanceTimeUTC).isSameOrBefore(todayTimeWorkOutMax)) {
if (!existingTodayAttendance.events.find((event) => event.type === 'check_out')) {
existingTodayAttendance.events.push({
type: 'check_out',
time: attendanceTimeUTC.toDate(),
});
await existingTodayAttendance.save();
resAttendanceEmit = existingTodayAttendance;
} else {
const errorMessage = '2.1 attempted double check_out ';
}
} else if (moment(attendanceTimeUTC).isBefore(todayTimeWorkOut)) {
let attendanceType = 'check_in';
const lastEvent = existingTodayAttendance.events[existingTodayAttendance.events.length - 1];
if (lastEvent.type === 'check_in' && existingTodayAttendance.events.length === 1) {
attendanceType = 'break_start';
} else if (lastEvent.type.endsWith('_start') || lastEvent.type.endsWith('_end')) {
const correspondingEvent = getAfterEventType(lastEvent.type);
if (correspondingEvent) {
attendanceType = correspondingEvent;
}
}
existingTodayAttendance.events.push({
type: attendanceType,
time: attendanceTimeUTC.toDate(),
});
await existingTodayAttendance.save();
resAttendanceEmit = existingTodayAttendance;
} else {
if (
moment(attendanceTimeUTC).isSameOrBefore(shiftTimeInMax) &&
moment(attendanceTimeUTC).isSameOrAfter(shiftTimeInMin) &&
!shiftTimeOut.isSameOrBefore(attendanceTimeUTC.clone().endOf('day'))
) {
const newAttendance = new Attendances({
user: users._id,
date: attendanceDate,
attendance_details: {
shift: shiftIndex + 1,
work_time: {
time_in: shiftTimeIn.toDate(),
time_out: shiftTimeOut.toDate(),
},
biometric_details: {
unit: existingBiometric?._id,
pin: webhookData?.data?.pin,
},
},
events: [
{
type: 'check_in',
time: attendanceTimeUTC.toDate(),
},
],
});
await newAttendance.save();
resAttendanceEmit = newAttendance;
} else {
const errorMessage = '5. attempted attendance outside valid range for today';
}
}
} else {
const newAttendance = new Attendances({
user: users._id,
date: attendanceDate.toDate(),
attendance_details: {
shift: shiftIndex + 1,
work_time: {
time_in: shiftTimeIn.toDate(),
time_out: shiftTimeOut.toDate(),
},
biometric_details: {
unit: existingBiometric?._id,
pin: webhookData?.data?.pin,
},
},
events: [
{
type: 'check_in',
time: attendanceTimeUTC.toDate(),
},
],
});
await newAttendance.save();
resAttendanceEmit = newAttendance;
}
}
} else if (existingTodayAttendance && !existingYesterdayAttendance) {
const isDuplicateEvent = existingTodayAttendance.events.some((event) => moment(event.time).isSame(attendanceTimeUTC));
if (isDuplicateEvent) {
console.log('Skipping duplicate event');
return res.status(200).end();
}
if (!existingTodayAttendance.events.find((event) => event.type === 'check_out') && !moment(attendanceTimeUTC).isAfter(todayTimeWorkOutMax)) {
if (moment(attendanceTimeUTC).isSameOrAfter(todayTimeWorkOut) && moment(attendanceTimeUTC).isSameOrBefore(todayTimeWorkOutMax)) {
existingTodayAttendance.events.push({
type: 'check_out',
time: attendanceTimeUTC.toDate(),
});
await existingTodayAttendance.save();
resAttendanceEmit = existingTodayAttendance;
} else if (moment(attendanceTimeUTC).isBefore(todayTimeWorkOut)) {
let attendanceType = 'check_in';
const lastEvent = existingTodayAttendance.events[existingTodayAttendance.events.length - 1];
if (lastEvent.type === 'check_in' && existingTodayAttendance.events.length === 1) {
attendanceType = 'break_start';
} else if (lastEvent.type.endsWith('_start') || lastEvent.type.endsWith('_end')) {
const correspondingEvent = getAfterEventType(lastEvent.type);
if (correspondingEvent) {
attendanceType = correspondingEvent;
}
}
existingTodayAttendance.events.push({
type: attendanceType,
time: attendanceTimeUTC.toDate(),
});
await existingTodayAttendance.save();
resAttendanceEmit = existingTodayAttendance;
} else {
const errorMessage = '4. attempted attendance outside valid range for today';
}
} else {
const errorMessage = '5. attempted attendance outside valid range for today';
}
} else {
if (existingTodayAttendance) {
const isDuplicateEvent = existingTodayAttendance.events.some((event) => moment(event.time).isSame(attendanceTimeUTC));
if (isDuplicateEvent) {
console.log('Skipping duplicate event');
return res.status(200).end();
}
if (!existingTodayAttendance.events.find((event) => event.type === 'check_out')) {
if (moment(attendanceTimeUTC).isSameOrAfter(todayTimeWorkOut) && moment(attendanceTimeUTC).isSameOrBefore(todayTimeWorkOutMax)) {
existingTodayAttendance.events.push({
type: 'check_out',
time: attendanceTimeUTC.toDate(),
});
await existingTodayAttendance.save();
resAttendanceEmit = existingTodayAttendance;
} else if (moment(attendanceTimeUTC).isBefore(todayTimeWorkOut)) {
let attendanceType = 'check_in';
const lastEvent = existingTodayAttendance.events[existingTodayAttendance.events.length - 1];
if (lastEvent.type === 'check_in' && existingTodayAttendance.events.length === 1) {
attendanceType = 'break_start';
} else if (lastEvent.type.endsWith('_start') || lastEvent.type.endsWith('_end')) {
const correspondingEvent = getAfterEventType(lastEvent.type);
if (correspondingEvent) {
attendanceType = correspondingEvent;
}
}
existingTodayAttendance.events.push({
type: attendanceType,
time: attendanceTimeUTC.toDate(),
});
await existingTodayAttendance.save();
resAttendanceEmit = existingTodayAttendance;
} else {
const errorMessage = '4. attempted attendance outside valid range for today';
}
} else {
if (
moment(attendanceTimeUTC).isSameOrBefore(shiftTimeInMax) &&
moment(attendanceTimeUTC).isSameOrAfter(shiftTimeInMin) &&
!moment(shiftTimeIn).isSame(momentTz.utc(existingTodayAttendance.date).clone().tz(deviceTimezone, true), 'day')
) {
const newAttendance = new Attendances({
user: users._id,
date: attendanceDate,
attendance_details: {
shift: shiftIndex + 1,
work_time: {
time_in: shiftTimeIn.toDate(),
time_out: shiftTimeOut.toDate(),
},
biometric_details: {
unit: existingBiometric?._id,
pin: webhookData?.data?.pin,
},
},
events: [
{
type: 'check_in',
time: attendanceTimeUTC.toDate(),
},
],
});
await newAttendance.save();
resAttendanceEmit = newAttendance;
} else {
const errorMessage = '6. attempted attendance outside valid range for today';
}
}
} else {
if (moment(attendanceTimeUTC).isSameOrBefore(shiftTimeInMax) && moment(attendanceTimeUTC).isSameOrAfter(shiftTimeInMin)) {
const newAttendance = new Attendances({
user: users._id,
date: attendanceDate,
attendance_details: {
shift: shiftIndex + 1,
work_time: {
time_in: shiftTimeIn.toDate(),
time_out: shiftTimeOut.toDate(),
},
biometric_details: {
unit: existingBiometric?._id,
pin: webhookData?.data?.pin,
},
},
events: [
{
type: 'check_in',
time: attendanceTimeUTC.toDate(),
},
],
});
await newAttendance.save();
resAttendanceEmit = newAttendance;
} else {
const errorMessage = 'attempted attendance outside valid range for shift';
}
}
}
}
const getAfterEventType = (startEventType) => {
switch (startEventType) {
case 'break_start':
return 'break_end';
case 'off_start':
return 'off_end';
case 'service_start':
return 'service_end';
case 'break_end':
case 'service_end':
case 'off_end':
return 'break_start';
default:
return 'check_in';
}
};
function calculateTimeOut(timeIn, workDurationHours) {
return momentTz.tz(timeIn, 'Asia/Makassar').clone().add(workDurationHours, 'hours').toDate();
}
function findValidShiftIndex(attendanceTimeUTC, attendanceTimeWITA, departmentWorkTimes) {
let shiftIndex = null;
let closestTimeInDifference = Number.MAX_SAFE_INTEGER;
for (let i = 0; i < departmentWorkTimes.length; i++) {
const workTime = departmentWorkTimes[i];
const shiftStart = moment.utc(workTime.time_in);
const shiftToday = shiftStart.clone().set({
year: attendanceTimeWITA.year(),
month: attendanceTimeWITA.month(),
date: attendanceTimeWITA.date(),
});
const shiftStartMin = shiftToday.clone().subtract(2, 'hours');
const shiftStartMax = shiftToday.clone().add(7, 'hours');
if (attendanceTimeUTC.isBefore(shiftToday) && attendanceTimeUTC.isAfter(shiftStartMin)) {
shiftIndex = i;
break;
}
if (attendanceTimeUTC.isBetween(shiftStartMin, shiftStartMax, null, '[]')) {
const difference = Math.abs(attendanceTimeUTC.diff(shiftToday));
if (difference < closestTimeInDifference) {
closestTimeInDifference = difference;
shiftIndex = i;
}
}
}
if (shiftIndex === null) {
for (let i = 0; i < departmentWorkTimes.length; i++) {
const workTime = departmentWorkTimes[i];
const shiftStart = moment.utc(workTime.time_in).add(1, 'days');
const shiftTomorrow = shiftStart.clone().set({
year: attendanceTimeUTC.year(),
month: attendanceTimeUTC.month(),
date: attendanceTimeUTC.date(),
});
const shiftTomorrowMin = shiftTomorrow.clone().subtract(2, 'hours');
const shiftTomorrowMax = shiftTomorrow.clone().add(7, 'hours');
if (attendanceTimeUTC.isBetween(shiftTomorrowMin, shiftTomorrowMax, null, '[]')) {
const difference = Math.abs(attendanceTimeUTC.diff(shiftTomorrow));
if (difference < closestTimeInDifference) {
closestTimeInDifference = difference;
shiftIndex = i;
}
}
}
}
return shiftIndex;
}
function calculateShiftTimeIn(shiftIndex, attendanceTimeUTC, attendanceTimeWITA, departmentWorkTimes, deviceTimezone) {
const shiftTimeInUTC = moment.utc(departmentWorkTimes[shiftIndex].time_in);
let shiftTimeIn = attendanceTimeWITA.clone();
shiftTimeIn.hours(shiftTimeInUTC.tz(deviceTimezone).hours());
shiftTimeIn.minutes(shiftTimeInUTC.tz(deviceTimezone).minutes());
shiftTimeIn.seconds(0);
shiftTimeIn.milliseconds(0);
if (shiftTimeIn.isBefore(attendanceTimeWITA)) {
const hoursDifference = attendanceTimeWITA.diff(shiftTimeIn, 'hours');
if (hoursDifference >= 12) {
shiftTimeIn.add(1, 'days');
}
} else {
const hoursDifference = shiftTimeIn.diff(attendanceTimeWITA, 'hours');
if (hoursDifference >= 12) {
shiftTimeIn.subtract(1, 'days');
}
}
const shiftTimeInMin = shiftTimeIn.clone().subtract(2, 'hours');
const shiftTimeInMax = shiftTimeIn.clone().add(7, 'hours');
return { shiftTimeIn, shiftTimeInMin, shiftTimeInMax };
}
function determineAttendanceDate(attendanceTimeWITA, shiftTimeInUTC, shiftTimeOutUTC) {
let attendanceDate;
let shiftTimeIn = momentTz.utc(shiftTimeInUTC).clone().tz('Asia/Makassar');
let shiftTimeOut = momentTz.utc(shiftTimeOutUTC).clone().tz('Asia/Makassar');
if (attendanceTimeWITA.isBefore(shiftTimeIn) && shiftTimeIn.isAfter(attendanceTimeWITA.clone().add(1, 'day').startOf('day'))) {
attendanceDate = attendanceTimeWITA.clone().add(1, 'day').startOf('day');
} else if (attendanceTimeWITA.isAfter(shiftTimeIn) && shiftTimeIn.isSame(attendanceTimeWITA.clone().startOf('day'))) {
attendanceDate = attendanceTimeWITA.clone().startOf('day');
} else if (
attendanceTimeWITA.isBefore(shiftTimeIn) &&
shiftTimeIn.isBefore(attendanceTimeWITA.clone().add(1, 'day').endOf('day')) &&
!shiftTimeOut.isSameOrBefore(attendanceTimeWITA.clone().endOf('day'))
) {
attendanceDate = attendanceTimeWITA.clone().add(1, 'day').startOf('day');
} else if (attendanceTimeWITA.isAfter(shiftTimeIn) && shiftTimeIn.isBefore(attendanceTimeWITA.clone().subtract(1, 'day').endOf('day'))) {
attendanceDate = attendanceTimeWITA.clone().startOf('day');
} else if (attendanceTimeWITA.isAfter(shiftTimeIn) && shiftTimeIn.isBefore(attendanceTimeWITA.clone().endOf('day'))) {
// attendanceDate = attendanceTimeWITA.clone().add(1, 'day').startOf('day');
attendanceDate = attendanceTimeWITA.clone().startOf('day');
} else {
attendanceDate = attendanceTimeWITA.clone().startOf('day');
}
return attendanceDate;
}
I know my current code has issues with duplication and lacks clarity, but at least help me improve its robustness and functionality while still maintaining a working solution.