How a shift week gets set up
The drafter can only build from what’s in the sheet. So the first job is making sure each week’s sheet reflects who’s actually free and what actually needs covering. There are three ways things get in: somebody types into the Drive sheet, somebody emails a plain-English time-off note, or the standing weekly pattern gets copied in for you. The first one is obvious. The other two exist because in real life nobody updates a sheet for the day off they asked about in passing, and nobody wants to retype the same Monday-opener shift fifty-two times a year.
Key takeaways
- Three intake lanes feed one sheet: the Drive sheet, a time-off note lane, and a recurring template lane.
- Plain-English time-off notes are read by Bedrock Haiku 4.5 into a clean date range.
- Every read-in note goes to the manager’s Slack for one-tap approval before it lands on the week.
- The recurring template lane copies the standing weekly pattern so only exceptions need editing.
- The sheet stays the canonical store. The other lanes are conveniences that write into it.
Three lanes into one sheet
Lane 1: the Drive sheet itself
The simplest lane. Open the sheet in Drive, edit the week, save. The staff tab is short: name, role, weekly hours target, skills cleared, Slack ID or email, and a max-hours cap. The week tab holds the shifts that need covering (day, start, end, role, skills needed) and each person’s availability. A small Lambda — drive-sync — runs every fifteen minutes, exports the sheet as plain CSV via the Drive API, and writes it to s3://ss-roster-source/roster.csv if the sheet has changed since the last sync. The drafter reads from S3, not Drive directly. That keeps Drive API calls predictable and gives you S3 versioning for free, so a bad bulk-edit can be rolled back in one click.
This lane covers the cases where the manager already knows the week — the new hire’s first shift, the festival weekend that needs extra cover, the person who’s out all week. Most of the week goes in this way.
Lane 2: time-off notes (the lane most teams actually use)
Set up a dedicated inbound address — something like timeoff@your-company.com — via Amazon SES. Anyone on the team emails it in plain English and the scheduler takes it from there. SES writes the raw message to s3://ss-raw-mime/. The S3 PUT triggers a parser Lambda. The Lambda pulls out the note text and calls Bedrock Haiku 4.5 to read it.
The note “off next Friday for a wedding, back Monday” becomes a structured result: who sent it (from the email address), a start date, an end date, and a short reason. The model prompt is plain: “Read this time-off note. Return JSON only with start date, end date, and reason. Use today’s date to resolve words like ‘next Friday.’ Do not invent a date that isn’t implied by the note.” The result goes to a small Slack message that pings the manager: the person, the dates read, the reason, and three buttons — approve, edit, decline. On approve, a Lambda marks those days as unavailable for that person via the Sheets API. On edit, the manager gets a fillable modal pre-filled with the read-in dates. On decline, the request is logged and the staffer gets a short note back.
The reason every read-in note goes to the manager first is simple: a time-off request the model misread is worse than one that never made it into the sheet. A wrong date quietly tells the drafter someone’s free when they’re not, and the short-staffed shift shows up the morning of, not the week before.
Lane 3: recurring template
Most weeks look a lot like the last one. The same Monday opener, the same Friday-night double, the same two cleaners on Wednesday. Forcing the manager to retype that grid every week is busywork that invites mistakes.
Lane 3 keeps a standing weekly pattern in its own tab — the shifts that repeat. A small template-sync Lambda runs once a week, copies the pattern into next week’s tab, and leaves the manager to edit only the exceptions: the holiday, the extra cover, the person who’s away. Anything the manager hasn’t touched is assumed unchanged. If the template would clash with an approved time-off note, the conflict is surfaced in the same Slack flow as Lane 2 so it’s caught before the draft runs.
The template lane is the most opt-in of the three. A team with a wildly different week every week can ignore it; a team with a steady rhythm avoids rebuilding the same grid fifty-two times a year.
Why the sheet stays the source of truth
Three lanes in, but only one place where the drafter actually looks. That’s a deliberate constraint. If two lanes both wrote directly to the drafter’s state, every “why was this person scheduled?” question would mean checking three places. Funneling everything through the Drive sheet means there is exactly one view of the week, and the manager can read or edit any of it without learning a new tool. The convenience lanes are first-class for getting things in, but they always pass through the sheet on the way.
Next post: how the drafter actually reads the week, balances hours, and picks one of four outcomes per shift.
All posts