Roster to calendar sync

The problem

People on rotating rosters are handed their schedule as a file: a spreadsheet-style export, or a PDF pulled out of a central rostering system. Neither is something a calendar can read. To actually know when they are working, they either squint at that document on a phone or retype four weeks of shifts into their personal calendar by hand, one all-day event at a time.

Rosters then change after publication, so the hand-typed copy silently goes stale, and the next 28-day cycle means doing the whole job again. Checking whether a published roster even complies with the rules that govern it (minimum rest days, limits on consecutive shifts and weekends worked, expected total hours) means counting rows manually, which almost nobody does.

What was built

The app ingests a roster file and writes each shift straight into the user's own calendar. Access is closed: sign-in is passwordless and restricted to verified work email addresses on one approved domain, proven with a 6-digit email code or an emailed magic link, after which the server mints its own signed session cookie.

A roster reaches the app one of two ways. It can be dragged into the browser as a file, or emailed as an attachment to a dedicated inbound address handled by a serverless email worker that authenticates the message and verifies the sender before importing anything.

Parsed shifts are classified by type, checked against the roster rules that apply to them, previewed, and only then written to Google Calendar or Microsoft Outlook over OAuth scoped to calendars only: no mail, contacts or files. There is also a self-hosted iCal or webcal feed for Apple Calendar and anything else that reads one. These are not alternatives to each other: a user can run Google, Outlook and the feed at the same time, because the calendar someone checks on their phone is often not the one their work account owns. Every import is recorded with a one-tap undo that removes those events again.

Platform

Nothing here is rented from a third party that did not have to be. The dependency list is short on purpose, and the pieces that hold user data are ones we run ourselves.

Frontend

React with React Router, built by Vite, shipped as an installable PWA with a versioned service worker. Each main panel is code-split at the route level, so admin-only code never ships to anyone else.

Backend

Node.js with Express, with standard hardening middleware and request rate limiting in place.

Sign-in

Passwordless via Stytch, using either a 6-digit email code or a magic link. The server then issues its own signed session, with the ability to sign an account out of every device at once.

Calendars

Google Calendar API v3 and Microsoft Graph v1.0 over OAuth, with refresh tokens encrypted at rest, plus a self-hosted .ics/webcal feed the user can revoke and reissue at any time.

Hosting

Self-hosted on a Raspberry Pi-class ARM box, run under PM2 from a checked-in ecosystem config with restart limits, sitting behind Cloudflare.

Email infrastructure

Cloudflare Email Routing feeding a TypeScript Cloudflare Email Worker, deployed with Wrangler, which parses MIME with postal-mime and extracts PDF text with unpdf before handing the parsed result back to the main server over an authenticated internal endpoint.

Storage

Two stores with one job each. A tree of flat JSON files on disk, written atomically, is the source of truth for profiles, rosters and history. Alongside it sits SQLite in write-ahead logging mode, rebuilt from that tree on every boot, which serves the queries that were race-prone as read-modify-write cycles over JSON, and which owns outright the two tables that exist nowhere else: the audit log, and the integration credentials.

Integration

A versioned read-only HTTP API authenticated with user-issued scoped keys, plus outbound webhooks signed with HMAC-SHA256 and retried from a durable queue rather than fired and forgotten.

Backups

A nightly snapshot taken through SQLite's own online backup API rather than by copying the file, because a copy of a write-ahead-logging database passes an integrity check while silently missing its most recent rows. Each snapshot is verified by reading its row counts back before it is kept, and a separate machine pulls it rather than being pushed to, so whatever can reach the application host cannot reach the backups. A run that fails or does not happen raises an alert instead of going unnoticed, and the result is surfaced in the admin health view.

Alerting

Notable account and administrative events raise operational alerts, with per-event cooldowns so a burst never floods the channel.

The inbound email path is deliberately paranoid. The worker requires DMARC, or aligned SPF and DKIM, before it looks at anything else; then it gates on the sender's domain; then it confirms the sender has an active account. Permanent failures are rejected outright, while transient ones are re-thrown so delivery is retried, and every non-import outcome is written back into the app's shared audit log so a roster that quietly failed to arrive can be explained.

What it does now

A signed-in user drops a roster export into the browser, or emails it in, and sees a colour-coded preview of every shift with rule warnings before anything is written. Confirming pushes the whole cycle to their connected calendar. Re-importing a revised roster updates changed shifts, adds new ones and skips unchanged ones rather than creating duplicates.

Beyond import there is an Upcoming view with today's shift and what is next; an Equity view breaking the cycle into Day, Afternoon and Night counts and hours with compliance warnings, including breaches that only appear at the seam between two consecutive rosters; and a History view with one-tap undo for each import. A banner warns when the next roster is due, and escalates once the current one has ended with nothing newer imported.

Someone who uploads a whole-team roster covering everyone on the same schedule also sees which colleagues are starting at the same time, and can opt in to having their own shifts pushed automatically when a teammate uploads a newer copy. Behind that sits an in-app issue-reporting form and an admin console for user management, an audit log, broadcast messages and feedback triage.

The parsing is the fiddly part. Shift text is classified into ten types by pattern matching on the raw label; the CSV parser rebuilds quote-balanced logical lines before tokenising, so a shift cell split across two physical lines is still read correctly; and a user is matched to their own line on a whole-team roster by scoring their sign-in address against the names present, returning nothing at all when the match is ambiguous rather than guessing wrong.

Overtime is separated from rostered hours rather than added to them. Shift length varies between work units, so the app measures the normal shift for the roster in front of it instead of assuming one, counts only the tail beyond that as overtime, and counts a shift worked on a rest day as overtime in full. The rostered total therefore stays comparable between two people on the same roster period who worked different amounts of extra time, which is the number an equity view is for.

Letting another app read the roster

Another PiStack product, a shift-aware nutrition and training planner, needs to know when someone is working in order to plan around it. It reads the roster over a read-only API, on a key the roster's owner issues.

The user creates that key in their own settings, names it, ticks what it may see and chooses an expiry. It is shown once and stored only as a hash, so nobody, including whoever runs the server, can read it back afterwards. It can be rotated, which replaces the secret and keeps the permissions, or revoked outright, and settings lists each key's last use and the address it was used from, so one that should be dead is visible.

Permissions are deliberately narrow. One grants the shifts, one grants hours and the day, afternoon and night split, one grants an anonymous identifier and a timezone. None of them expose a name, an email address or an employer, and none grant colleagues or who somebody worked with. There is no write access of any kind, and no endpoint that returns anybody but the key's owner. The obvious shortcut, letting one application read the other's files directly, fails all three of those tests at once: it hands over everything, it cannot express what the reader is allowed to see, and it leaves the person whose roster it is with no way to know it is happening, let alone stop it.

Nothing polls. When a roster actually changes, and only then, subscribers are sent the changed shifts in full, so there is no follow-up request to make. Re-importing an identical roster sends nothing, which is what makes the mechanism worth its complexity. Deliveries are signed with HMAC-SHA256 over the raw body with the timestamp inside the signed material, so a replayed delivery fails verification rather than merely looking old. Delivery is at-least-once with a unique identifier per attempt, retried on a widening schedule from a queue that survives a restart, and an endpoint can retire itself for good with an explicit response. Destinations are validated before anything is sent, and redirects are followed manually to a fixed depth, so a delivery cannot be walked onto an address inside the network.

Registering that destination is not something a key can do. A credential that could change where a roster gets sent is a credential that can exfiltrate it, so the address is set by the signed-in user in the app and nowhere else.

The whole surface is documented publicly, including the parts it deliberately does not do.

Continued development

Every release carries a dated changelog entry, because every one is user-facing enough to warrant its own written note.

The changelog is dominated by corrections rather than feature announcements: a rest day silently dropped when a night shift began on the same calendar date, a paid day off miscounted as an extra night shift and inflating the equity totals, a crash for anyone using feed-subscription mode, and an accessibility pass covering auto-zoom on text fields, screen-reader labels, Escape-to-close on modals and a swipe-to-open mobile drawer. One release changed the default sign-in method to the 6-digit code because the emailed link was being blocked in transit for some people, a change that only reads as a response to users saying they could not get in.

The release process is codified rather than remembered. A validation script syntax-checks the source and refuses any deploy where the top changelog entry does not match the package version. The deploy script refuses to run off the release branch, refuses any version that is not strictly greater than the one already live, rebuilds the frontend, restarts the process manager, and then polls the live version endpoint until it confirms the new build is actually serving. Unseen changelog entries surface in the app once per account and are then marked seen server-side, so the same update never reappears on another device.

That is the part worth pointing at. Shipping the first version is the easy half; the releases after it, most of them fixing something that only showed up in real use, are what maintaining software actually looks like.