Pub scheduling system

The Pub Scheduler handles the weekly staffing cycle: opening shifts, collecting tender availability, generating fair assignments, and emailing the schedule. It runs inside the Cloudflare Worker (worker.js) with data in D1—no external automation platform is involved.

Three n8n workflows handled this work in the past. They moved into the Worker in May and June 2026 and are no longer used. This page describes the current system.

Admin UI: https://pub.ihnyc-rc.org/admin/pub-schedule (Cloudflare Access required)

Weekly state flow

The scheduler follows a 4-step weekly cycle, visible as a progress bar in the admin UI:

flowchart LR
  SHIFTS["1. Create shifts"] --> FORM["2. Collect availability"]
  FORM --> ASSIGN["3. Generate and review assignments"]
  ASSIGN --> SEND["4. Approve and send"]

The weekly anchor is “week of” — the Sunday that starts the week, computed in America/New_York time.

Data model

All scheduler data lives in CONTENT_DB (Cloudflare D1).

Admin interface

This Week (/admin/pub-schedule)

The main operator view. A 4-step wizard showing the current week’s progress:

Weekly scheduling wizard

• Shifts panel — shows the week’s shifts with status badges. “Apply template” seeds from saved presets.
• Form & Availability panel — live feed of who has submitted, who is outstanding, and who said “not available this week”. Includes a “Remind outstanding” button to re-email non-responders.
• Assignments panel — after generating, shows the proposed schedule. Each assignment can be held (excluded from the email) or released. An “Edit assignments” button opens a detailed view.
• Send panel — preview the email, send to all active tenders, or send a full update if the schedule changes after the initial send. There is no separate “correction” recipient subset.
• Fairness brief — preview or send the operator-only fairness report to supervisors who opted in.

An Expert toggle shows all four panels at once.

Pub Setup (/admin/pub-setup)

Card grid for less-frequent operations:

Scheduling configuration and access defaults

• Tenders — add/edit/archive tenders, upload photos, set payment links
• Shifts — manage shift templates, edit individual shifts
• Email Templates — edit the HTML/text templates for schedule, availability, and update emails
• Supervisors — manage supervisor contacts shown in email footers
• Email Log — audit trail of every email the scheduler sent
• Stats — fill rate, opportunity-adjusted fairness, history, and per-tender assignment counts
• Raw Database — Tabulator grid for direct CRUD on all entities

Assignment algorithm

worker/lib/pub-scheduler/assign.js

1. Takes the latest finished submission per tender (deduped by token, latest timestamp wins; finished beats unfinished)
2. Tenders who said “not available” contribute zero slot keys — excluded from assignment
3. Groups candidates by shift slot key
4. Fisher-Yates shuffles candidates per slot
5. Selects up to staff_needed per shift
6. Fairness: opportunity-adjusted fill rate (assignments / weeks available) breaks ties so under-scheduled tenders get priority

Emails

Three email types, all with customizable HTML/text templates:

Templates are editable at /admin/pub-setup > Email Templates. Each type has four styles (Branded, Classic, Clean, Minimal). The branded style uses the AVI + Affective Technologies joint logo with a navy/teal-green palette.

Emails are sent via the worker’s configured email provider. Every send is logged in pub_email_log with recipient, subject, timestamp, and send result.

The fairness brief is separate from the tender schedule. It summarizes assignment opportunity and outcomes for opted-in supervisors; it is not sent to tenders.

Shift templates

Reusable presets stored in pub_shift_templates. Each template specifies:

• Day of week (0=Sun through 6=Sat)
• Start and end time (HH:MM, supports overnight spans)
• Staff needed count
• Display label

“Apply to week” stamps a template into pub_shifts for a specific week, computing the correct date with America/New_York DST handling.

Managed via the Shifts page under Pub Setup, or the Templates tab in the Raw Database view.

Availability polling

The worker’s scheduled() cron polls Formbricks for new responses on a cadence (configurable, typically during the Fri-Sun availability window). Each response is normalized and upserted into pub_submissions keyed by response_id.

If a tender submits multiple times, all rows are stored. The checklist and assignment algorithm deduplicate at read time: latest finished submission per tender wins.

Specials

Drink and food specials managed at /admin/pub-specials. Each special has a title, price label, description, optional image, and an active toggle. Active specials appear in the right bar TV’s rotation on their linked shift days.

Weekly auto-remind

A cron job (scheduled()) sends a single reminder email per week to the operator listing outstanding tenders who haven’t submitted their availability form. The reminder fires once per pub_schedule_runs row (tracked by reminded_at).

Migration history

Migrations are in migrations/content/ and applied manually via wrangler d1 execute CONTENT_DB --remote --file=<path>. Key migrations:

Related

• ihnyc-avi-pub-landing — the parent worker that hosts the scheduler
• admin — admin UI reference
• ops — deployment and secrets