Pub Scheduler (n8n automation)

Workflows: Part1-Pub-Generate-Weekly-Form, Part2-Pub-Generate-Shifts, Part3-Pub-Approve-and-Send-Mass-Email
System: n8n
Sources: n8n workflow exports in IHNYC-Remote/assets

Overview

• Sends a weekly Formbricks availability survey to active pub tenders.
• Normalizes the latest submissions, matches responses to Notion shifts, and writes assignments.
• Requires all shifts in a week to be Approved before emailing the final schedule.
• Sends the assignment email and archives the week in Notion.

Data sources

Notion databases

• Shifts DB (id NOTION_DB_ID_SHIFTS)
  • Fields used: Shift Status (status), Date/Time (date), Week Of (rich_text), Human Label (friendly) (rich_text), Human Label (rich_text), staff_needed (number or formula).
• Assignments DB (id NOTION_DB_ID_ASSIGNMENTS)
  • Fields used: Week of (friendly) (rich_text), Date/Time (date), Name (title), relations to Shifts DB and Pub Tenders DB.
• Pub Tenders DB (id NOTION_DB_ID_PUB_TENDERS)
  • Fields used: Active (checkbox), Name, Email, access_token.
• Form Submissions DB (id NOTION_DB_ID_FORM_SUBMISSIONS)
  • Fields written: Selected Shifts, Week Of, Submission ID, Created At, Are you available, plus metadata (browser/os/device/country/form URL).

Form submissions

• Formbricks survey id: FORMBRICKS_SURVEY_ID (management API v1 for survey update, v2 for responses).
• Question ids used in code:
  • Shifts multi-select: FORMBRICKS_Q_SHIFTS_ID
  • Availability: FORMBRICKS_Q_AVAILABILITY_ID

Email

• EmailSend nodes are used for outgoing email; node names reference Resend.
• Actual provider and credentials are configured in n8n (unknown from export).

Scheduling / triggers

• Part 1 schedule: cron 0 0 10 * * 0 (Sundays at 10:00, timezone from n8n settings).
• Part 2 schedule: cron 0 0 12 * * TUE (Tuesdays at 12:00, timezone from n8n settings).
• Part 3 schedule: cron 0 0 16-17 * * 2 (Tuesdays at 16:00 and 17:00, timezone from n8n settings).

Flow breakdown by part

Part 1: Generate weekly form

• Trigger runs on the Part 1 cron schedule.
• A2 - Get Shifts from Notion pulls Shifts DB where Shift Status equals Created and sorts by Date/Time ascending.
• A3 - Build Options for Formsort builds shift labels from property_date_time in America/New_York and generates shift ids like YYYYMMDD-HHMM.
• A0 - Get Tenders from Notion pulls Pub Tenders DB (all); A5 - Merge filters out inactive tenders (property_active === false) and dedupes by access token.
• HTTP Request - Get Survey Parameters GETs the Formbricks survey; A6 - Build Formbricks Payload injects weekOf text and dynamic shift choices, sets status to inProgress.
• HTTP Request PUTs the updated survey payload to Formbricks.
• Edit Fields builds a survey link with token and name query params.
• Create Email Payload builds HTML and text availability emails (one per tender) and Send email through Resend delivers them.
• Loop Over Items and Wait exist for pacing (no parameters in export).
• Send email through Resend - Let Admin Know Complete notifies admin when availability emails are sent.
• Disabled nodes: A1b - Compute Current Week and HTTP Request - Open Survey (the disabled PUT sets survey status to completed despite the node name).

Part 2: Generate shifts and assignments

• Trigger runs on the Part 2 cron schedule.
• HTTP Request - Close Survey sets the Formbricks survey status to completed.
• A1 - Get Responses pulls responses since the current week start (Sunday) with limit 250.
• B1 - Normalize Responses (Latest Submission) keeps only finished submissions and selects the latest per token. If availability is not_available, selected shifts are cleared.
• B2 - Split Out and B3 - Rename Shifts to Shift normalize each response into a single shift field.
• C1 - Find Corresponding Shifts Database matches Shifts DB rows by Human Label (friendly) equals the selected shift and Week Of equals the current week start.
• C2 - Attach shiftId to each item attaches shift metadata, including staffNeeded.
• D1 - Pick Assigned Tenders groups by shift, runs Fisher-Yates shuffle, and selects up to staffNeeded candidates per shift.
• Create Page (Assignments DB) creates one assignment per selected tender with title =[{{ $json.weekOf }}] Assignment - {{ $json.tenderName }} and relations to Shifts DB and Pub Tenders DB.
• B_Submissions and Make Selected Shifts into a String prepare submission metadata; Update Form Submissions DB writes results into the Form Submissions DB.
• Email Payload generation and Send email notify an admin that assignments are ready for approval.

Part 3: Approve and send mass email

• Trigger runs on the Part 3 cron schedule.
• Get the Shifts that are NOT archived pulls Shifts DB where Shift Status does not equal Archived.
• Check all shifts in week are APPROVED groups by property_week_of and only continues if every shift in a week has property_shift_status set to Approved. Otherwise it returns reason: "No fully-approved weeks yet".
• Check if all shifts in the week are ready gates downstream steps on that reason check.
• Get assignments for week pulls Assignments DB where Week of (friendly) equals the ready week.
• Generate Mass Email Payload builds the schedule email (plain text + HTML), sorts shifts by weekday and start time, and extracts tender names using the [YYYY-MM-DD] Assignment - Name pattern.
• Get Active Pub Tenders pulls Pub Tenders DB where Active is true.
• Combine Email + BCC builds a unique email list from active tenders and attaches it to the payload.
• Send assignments to all pub tenders sends the schedule email.
• Update shift statuses to archived updates each shift’s Shift Status to Archived.
• Disabled node: Send assignments to admin (test).

Key business rules

• Do not email the final schedule until every shift in the week is Approved in Shifts DB.
• Assignment selection respects staffNeeded per shift and randomizes candidates via Fisher-Yates shuffle.
• Assignments are created with title [YYYY-MM-DD] Assignment - Name (value is weekOf and tenderName from Part 2).
• After emailing the schedule, shifts are marked Archived.
• Only finished Formbricks submissions are considered, and the latest submission per tender token is used.
• Inactive tenders are excluded from availability emails and BCC assembly.

Failure modes and troubleshooting

• “No fully-approved weeks yet”: Part 3 stops when any shift in a week is not Approved. Confirm Shifts DB status for the week and rerun Part 3.
• “No active shifts”: Part 3 returns No active shifts when the Shifts DB query is empty.
• Email send test node disabled: Send assignments to admin (test) is disabled in Part 3; enable it for safe dry runs.
• Notion rate limits or partial failures: GetAll and update calls can fail mid-run. Check execution logs, then rerun for only the affected week or shift pages.
• Formbricks response limits: the responses request uses limit=250 and a week-start startDate. If submissions exceed the limit, add pagination or increase limits.
• Safe reruns:
  • Part 2 creates new Assignment pages every run. Remove or archive duplicate assignments before rerunning, or add a pre-check filter in Notion.
  • Part 3 prevents resend by archiving shifts. To resend, temporarily un-archive the week’s shifts and disable the email send node if you only want to regenerate payloads.

Security and privacy notes

• Availability survey links include token and name query params; treat access_token values as secrets.
• Email recipients are sourced from Pub Tenders DB email properties; admin recipients are configured in EmailSend nodes (redact in docs).
• The BCC list is built from active tenders in Combine Email + BCC. The email node currently injects this list into ccEmail and uses a fixed bccEmail (redacted). Confirm this is intended before sharing.
• Formbricks API keys are set in HTTP headers; store them in n8n credentials and avoid copying them into documentation.

Change log / maintenance

• If Notion schema changes, update filters and property keys:
  • Shifts DB: Shift Status, Date/Time, Week Of, Human Label (friendly), staff_needed.
  • Assignments DB: Week of (friendly), Name, Shifts DB relation, Pub Tenders DB relation.
  • Pub Tenders DB: Active, Email, access_token, Name.
  • Form Submissions DB: field names listed in Update Form Submissions DB.
• If Formbricks survey changes, update:
  • Survey id FORMBRICKS_SURVEY_ID.
  • Question ids FORMBRICKS_Q_SHIFTS_ID and FORMBRICKS_Q_AVAILABILITY_ID.
• If assignment naming changes, update:
  • Part 2 page title format.
  • Part 3 extractTenderName regex.
• If cron or timezone changes, update schedule triggers and any “week of” calculations.
• If email templates or recipients change, update Create Email Payload, Generate Mass Email Payload, Combine Email + BCC, and the EmailSend node settings.

Diagrams

System overview (Mermaid)

flowchart LR
  subgraph Part1["Part 1: Generate weekly form"]
    P1Trig["Schedule Trigger"]
    P1Shifts["Notion: Shifts DB (Created)"]
    P1Tenders["Notion: Pub Tenders DB (Active)"]
    P1Form["Formbricks: Update survey"]
    P1Email["Email: Availability link"]
  end

  subgraph Part2["Part 2: Generate shifts"]
    P2Trig["Schedule Trigger"]
    P2Form["Formbricks: Close + responses"]
    P2Shifts["Notion: Shifts DB (lookup)"]
    P2Assign["Notion: Assignments DB (create)"]
    P2Subs["Notion: Form Submissions DB (update)"]
    P2Admin["Email: Approval request"]
  end

  subgraph Part3["Part 3: Approve + send"]
    P3Trig["Schedule Trigger"]
    P3Shifts["Notion: Shifts DB (not archived)"]
    P3Assign["Notion: Assignments DB (week)"]
    P3Tenders["Notion: Pub Tenders DB (Active)"]
    P3Email["Email: Mass schedule"]
    P3Archive["Notion: Shifts DB (archive)"]
  end

  P1Trig --> P1Shifts
  P1Trig --> P1Tenders
  P1Shifts --> P1Form
  P1Tenders --> P1Form
  P1Form --> P1Email
  P1Email --> P2Form

  P2Trig --> P2Form
  P2Form --> P2Shifts
  P2Form --> P2Assign
  P2Form --> P2Subs
  P2Assign --> P2Admin

  P3Trig --> P3Shifts
  P3Shifts --> P3Assign
  P3Assign --> P3Email
  P3Tenders --> P3Email
  P3Email --> P3Archive

Part 3 sequence (Mermaid)

sequenceDiagram
  participant Cron as Schedule Trigger
  participant N8N as n8n Workflow
  participant Shifts as Notion Shifts DB
  participant Assignments as Notion Assignments DB
  participant Tenders as Notion Pub Tenders DB
  participant Email as Email Provider

  Cron->>N8N: Fire on cron (0 0 16-17 * * 2)
  N8N->>Shifts: Get shifts where Shift Status != Archived
  Shifts-->>N8N: Shifts for all active weeks
  N8N->>N8N: Check all shifts in week are APPROVED
  alt Week fully approved
    N8N->>Assignments: Get assignments where Week of (friendly) == weekOf
    Assignments-->>N8N: Assignment pages
    N8N->>N8N: Generate mass email payload
    N8N->>Tenders: Get Active Pub Tenders (Active == true)
    Tenders-->>N8N: Tender emails
    N8N->>N8N: Combine payload + BCC list
    N8N->>Email: Send assignments email(s)
    N8N->>Shifts: Update Shift Status -> Archived
  else No fully-approved weeks yet
    N8N-->>Cron: Exit with reason
  end