EscalaOper: a technical case of a shift-schedule system

EscalaOper is a desktop application that builds and maintains the schedule of a team that works in shifts: morning, afternoon, night and business hours, with days off, vacation, absences, on-call duty, call-ins, shift swaps and the ride to and from each shift. It is written in Java, stores the data in a Microsoft Access file and generates the transportation image the team receives over WhatsApp.

This technical case shows how the system represents a schedule, what happens when the real day departs from the plan and the errors that became rules. The numbers were checked in the code, in the version of 14/06/2026, the latest in the repository.

Transparency: EscalaOper was designed and developed by Jeremias Cardoso, the author of this blog. The text does not name a company, people or a city, and it leaves out folder paths and credentials.

Update note (30/10/2026): this post was rewritten as a technical case. The 20/07/2026 version described an idealized design: the day calculated by a formula from an anchor date, the deviations stored in separate records and the team consulting the schedule by app, in real time. Checked in the code, the system writes one row per person per day, part of the adjustments overwrite that row and the team receives the schedule by image and by email. This text describes what the code does.

The problem: the schedule that does not fit the business week

A shift schedule looks like a simple table until the first real week. A team that covers morning, afternoon and night works in cycles that do not respect the calendar week: five work days and two days off that fall on a Tuesday and a Wednesday, six of work and three off, four and two. On top of the cycle come vacation, medical certificates, who is called in to cover an absence and the question everyone asks the day before: who takes the ride for which shift tomorrow?

While only one person touches the control, a spreadsheet handles it, and there are ready-made templates for the routine. The problem starts when the schedule needs history, access profiles and rules the spreadsheet does not hold by itself: do not post the same day twice, know who is off so they can be called in, tell the person when their shift changes. In the technical case of SIGCOT, another system by the author, the shift schedule appears as a separate program, with its own login and database: it is this one.

The system in numbers

Numbers from the repository on 14/06/2026, the date of the last commit.

The database stores real team data and stays out of version control. Passwords are stored with PBKDF2, a hash method made for passwords, and whoever signs in for the first time is required to change the initial password.

The schedule is a recipe of periods

In EscalaOper, a schedule is not a table of days of the week. It is an ordered list of periods, and each period says four things: how many days it lasts, which shift it is, the start time and the end time. A 6x3 on the morning shift becomes two periods: six Morning days, for example from 07:00 to 15:00, followed by three Day off days. A cycle that alternates shifts is just a longer list: five mornings, two days off, five afternoons, two days off, and so on.

The entry form accepts any combination of the five shifts, requires at least one day per period and checks that the time is in hour-and-minute format. A new schedule format is data entry, not code: nobody has to program a special case for a 4x2 or a 6x4. The limit is in the unit: the model counts whole days, not hours. A cycle that can only be described in hours has to be translated into days before it goes in.

Generation joins the recipe with the people. The administrator chooses the schedule, the people and the date range. From the start date, the system walks day by day: it writes the shift of the current period, subtracts one day and, when the period ends, moves to the next; when the list ends, it goes back to the first. With no periods on file, generation is refused.

The start date works as the anchor of that person’s cycle. Two people on the same schedule, generated with different start dates, stay offset, and that is exactly what a rotating schedule needs so there is always someone on each shift.

One row per person per day

The result of generation is not a stored formula. It is a row in the table for each person on each day of the range, and the table has a rule that prevents two rows for the same person on the same day.

That choice has a large advantage and a cost that shows up only later. The advantage is that everything that comes next stays simple. The panel of more than 40 days, the coverage summary by shift, the list of who is off on a date and the transportation image are direct queries: “bring the rows between such and such a day”. Nobody has to recalculate the cycle to know who works tomorrow, and any day can be corrected without touching the recipe.

The cost is that the planned day and the real day compete for the same row. Because there is only one row per person per day, an adjustment has nowhere else to sit except on top of the plan.

The real day: call-in, absence, swap and on-call

The day-to-day adjustments sit on a screen with tabs, and each type touches the day’s row in its own way:

• Call-in: to cover an absence, the system lists who is off on the date. On confirmation, the called-in person’s row receives the new shift, the new times and the type “Call-in”. The day off that was there ceases to exist in the database.
• Absence: changes only the type and the note; the planned shift stays. That is why an absence on a day off keeps showing “F” on the panel, with a dashed border, and an absence on a work day shows the reason code: LME (medical certificate), LMA and LPA (maternity and paternity leave), LGA (marriage), TRE (training), VAS (business travel) or CHE (overtime compensation).
• Shift swap: overwrites the shift. When two people swap with each other, the two rows change in a single transaction: either both swap, or neither swaps.
• On-call: it is only a mark on the row. Shift and type stay as they were.
• Out of transportation: whoever will not use the transportation on a day gets a marker in the note field, without erasing what was already written there.

Absence and on-call preserve the plan. Call-in and the swap do not. Call-ins, swaps and vacation decisions generate email to the person: there are seven send points in the system.

Undo asks for the right shift

The price of overwriting shows up at undo time. The system has a function to undo adjustments in batch: the person and the period are chosen, and the days go back to normal. But the system does not know what normal was. The screen asks, in these words: “What is the CORRECT shift now?”. Then it writes the chosen shift on every day of the period.

That works well for undoing a week of call-ins on the same shift. For a period that mixed work days and days off, whoever undoes has to break the range into pieces, one per shift, or regenerate the schedule, and regenerating has its own side effect, described further below.

The lesson is about modeling and holds for any system with a plan and an actual: the plan needs to survive the adjustment. Storing the original shift in its own column at the moment of the call-in or the swap would be enough for undo to know, by itself, where to go back. The previous version of this text presented that separation as if it already existed. In the code, it exists for absence and on-call, and it does not exist for the call-in and the swap.

Vacation, holidays and transportation

Vacation has its own flow. The person requests up to three periods, with a visual map, and HR approves or rejects. The administrator can also post or reverse vacation in batch. That batch was already a source of error: each day was saved on its own, and a failure in the middle left the person with vacation halfway on the schedule. Today the whole period runs in a transaction, and any failure undoes everything. The reversal went through the same repair: before, the schedule could be reversed while the request stayed approved.

National holidays are calculated by the system itself, including the movable ones: Good Friday, Carnival and Corpus Christi come from the date of Easter, and November 20 has been included since 2024, when it became a national holiday. State and city holidays come from configuration, as day and month (applies every year) or as a full date (applies only that year). The panel highlights weekends and holidays.

Transportation follows a short rule, isolated in a service with tests:

1. whoever is on morning or business hours enters in the morning window and leaves in the afternoon window;
2. whoever is on afternoon enters in the afternoon window and leaves in the night window;
3. whoever is on night enters in the night window and leaves on the morning of the next day.

With that rule and each person’s city, the logistics panel shows, by shift and by day, who arrives and who leaves. The same data becomes a square image for WhatsApp, with six names per grid and “+N” when it goes past that.

The database: one file and one operation at a time

The database is an Access file, which can sit in a network folder. The driver used to open it in Java, UCanAccess, reloads the entire database on every new connection, and that is why the system keeps a single connection, open for the whole run. A backup of the file runs once a day, on the first access, or when the administrator asks, and the copies from the last 15 days are kept.

That single connection was safe while everything ran on the same execution line as the interface. It stopped being safe when the panel, the transportation and the WhatsApp image started querying the database in the background, so the screen would not freeze: two queries could use the same connection at the same time. The commit that fixed that, on 10/06/2026, calls the problem a critical bug. The fix was a lock that lets one database operation through at a time, without changing any of the code points that use the database.

The lock holds inside one open program. It does not coordinate two computers open on the same network file. What happens in that case was measured in another system by the author, Sentinela, which stores data in the same kind of file.

Incidents that became rules

1. The program that opened only in the development environment (05/06/2026). Packaged, the program could not find the login screen, because the screens were in a folder the packaging did not take, and it failed on open, because the signature files of a cryptography library made the package look tampered. The screens went into the resources folder, and the packaging started discarding those signatures, with a comment in the build file explaining why.
2. The accent that broke the filter without raising an error. Shift names and record-type names were typed by hand in several files. A “Convocaçao” without the tilde was enough for a filter to find nothing and a color not to appear, with no message at all. The values were gathered into a single constants file, with a warning: they also appear inside the SQL commands and have to be identical.
3. The green build with broken screens (11/06/2026). When the loose texts were replaced by the constants, a bulk edit did not recognize the Windows line ending, and the line that brought the constants did not enter three screens. The development environment generated classes with the error embedded; Maven, seeing classes newer than the code, did not recompile, packaged the broken program and finished with success. The screens failed only on open. The rule that stayed: after a bulk edit, compile from scratch and check that every screen loads. It is the same kind of silent failure that brings down automations: the tool says it worked, and the effect did not happen.
4. The email that never went out (10/06/2026). Direct sending through an email server did not work: the configured password was a sample, and the corporate network blocked the outbound path. Sending started using the Outlook installed on the machine, through a small temporary script that builds the message and sends it without opening a window. The cost is depending on Outlook being configured, and the first send may ask for confirmation.
5. The database copy deleted in a cleanup (11/06/2026). While cleaning files from a test, a copy of the database, from 30/04, was deleted because it was taken for a leftover of the test. The terminal delete command does not go through the Recycle Bin. The copy came back identical from another copies folder, with the same size and the same date. A database file that looks like a leftover needs its date and size checked before it disappears.

Tests: few, and in the place where the rule is pure

The system has 12 tests in 2 classes. The two services tested were born on 11/06/2026, when the rule for the panel abbreviations and the transportation rule left the screens and went into classes that depend neither on the database nor on the interface. The convention of that day: new business logic goes into a testable service, not into the screen.

All the rest (generation, adjustments, vacation, the restore point and the database) has no automatic test and is checked by hand. It is the project’s largest risk, and the findings in the next section are the kind a database test would have caught earlier.

What is still unresolved

The items below come from reading the code. The system was not run for this review.

• The restore point forgets part of the day. Before touching the schedule, the administrator can create a restore point. There is only one, and creating another replaces the previous one. The copy takes eight columns: identifier, schedule, person, date, shift, type, note and author. The start and end times, the justification and the on-call mark do not go in. Restoring gives the shifts back, but without those fields.
• Regenerating erases the adjustments of the period. If the person already has days posted in the range, the system asks whether it should overwrite. If the answer is yes, it deletes every row for the person in the period, without looking at the type: vacation, call-ins and absences go along. The approved vacation request stays in its own table, but the days disappear from the schedule.
• Vacation on a day with no schedule. In the table created by the system, every posted day has to belong to a schedule, and the batch vacation posting inserts days without saying the schedule. If that rule holds in the database in use, posting vacation in a period that has a day still without a schedule undoes the entire operation, thanks to the transaction. It was not tested.
• Batch undo depends on whoever undoes knowing the right shift of each day.
• One file, several machines. The database lock does not coordinate different computers.
• History stopped. The repository ends on 14/06/2026. If the system changed after that, the change is not in it.

Lessons that hold outside the shift schedule

1. Store the cycle as a recipe, not as a calendar. A new format becomes data entry, not code.
2. Materializing the day simplifies the read, but the plan needs a place of its own to survive the adjustments.
3. Undo is safe only if the system knows where to go back without asking anyone.
4. A value that goes into the database and into SQL needs a single source. An accent error raises no error.
5. A tool’s success is not proof. Compile from scratch and open the screens.
6. A multi-row operation is all or nothing. A half posting is worse than none.
7. A restore point has to copy everything the screen shows, or it restores something else.

Sources

• Code, documentation, commit history and project notes: the EscalaOper repository in the version of 14/06/2026 and project notes in the author’s knowledge base, read by the author; extract with the passages, the counting method and the limits of the verification, verified on 2026-10-30.