Intake (f1 + F2)

The workflow JSON

Copy or download the full n8n JSON below. Paste it into a new n8n workflow, add your credentials, activate. Full import guide →

{
  "name": "01 - Intake (F1 + F2)",
  "nodes": [
    {
      "parameters": {
        "rule": {
          "interval": [
            {
              "field": "minutes",
              "minutesInterval": 1
            }
          ]
        }
      },
      "id": "Schedule Trigger",
      "name": "Schedule Trigger",
      "type": "n8n-nodes-base.scheduleTrigger",
      "typeVersion": 1.2,
      "position": [
        -400,
        300
      ]
    },
    {
      "parameters": {
        "resource": "record",
        "operation": "search",
        "base": {
          "__rl": true,
          "mode": "id",
          "value": "={{ $env.AIRTABLE_BASE_ID }}"
        },
        "table": {
          "__rl": true,
          "mode": "name",
          "value": "Roles"
        },
        "filterByFormula": "{Active} = TRUE()",
        "options": {}
      },
      "id": "Fetch Active Roles",
      "name": "Fetch Active Roles",
      "type": "n8n-nodes-base.airtable",
      "typeVersion": 2.1,
      "position": [
        -180,
        300
      ],
      "executeOnce": true,
      "notes": "Attach Airtable Personal Access Token credential after import."
    },
    {
      "parameters": {
        "resource": "record",
        "operation": "search",
        "base": {
          "__rl": true,
          "mode": "id",
          "value": "={{ $env.AIRTABLE_BASE_ID }}"
        },
        "table": {
          "__rl": true,
          "mode": "name",
          "value": "Task_Templates"
        },
        "filterByFormula": "{Active} = TRUE()",
        "options": {}
      },
      "id": "Fetch Active Templates",
      "name": "Fetch Active Templates",
      "type": "n8n-nodes-base.airtable",
      "typeVersion": 2.1,
      "position": [
        40,
        300
      ],
      "executeOnce": true,
      "notes": "Attach Airtable Personal Access Token credential after import."
    },
    {
      "parameters": {
        "resource": "record",
        "operation": "search",
        "base": {
          "__rl": true,
          "mode": "id",
          "value": "={{ $env.AIRTABLE_BASE_ID }}"
        },
        "table": {
          "__rl": true,
          "mode": "name",
          "value": "Assignees"
        },
        "filterByFormula": "{Active} = TRUE()",
        "options": {}
      },
      "id": "Fetch Active Assignees",
      "name": "Fetch Active Assignees",
      "type": "n8n-nodes-base.airtable",
      "typeVersion": 2.1,
      "position": [
        260,
        300
      ],
      "executeOnce": true,
      "notes": "Attach Airtable Personal Access Token credential after import."
    },
    {
      "parameters": {
        "resource": "record",
        "operation": "search",
        "base": {
          "__rl": true,
          "mode": "id",
          "value": "={{ $env.AIRTABLE_BASE_ID }}"
        },
        "table": {
          "__rl": true,
          "mode": "name",
          "value": "Employees"
        },
        "filterByFormula": "{Status} = 'New'",
        "options": {}
      },
      "id": "Fetch New Employees",
      "name": "Fetch New Employees",
      "type": "n8n-nodes-base.airtable",
      "typeVersion": 2.1,
      "position": [
        480,
        300
      ],
      "executeOnce": true,
      "notes": "Attach Airtable Personal Access Token credential after import."
    },
    {
      "parameters": {
        "language": "javaScript",
        "jsCode": "// ============================================================================\n// GENERATED by `npm run build:nodes` \u2014 DO NOT EDIT.\n// Wrapper: workflows/code-nodes/validate.wrapper.js\n// Core inlined verbatim from: scripts/dates.js, scripts/expandTemplates.js, scripts/validate.js\n// Paste this whole file into the n8n Code node.\n// ============================================================================\n\n// n8n Code node \u2014 \"Validate intake\"  (THIN WRAPPER \u2014 do not put logic here)\n//\n// The core (validate + its deps) is inlined by `npm run build:nodes`, which reads\n// scripts/ verbatim and replaces the __CORE__ marker below. Edit logic in scripts/,\n// never here. Paste the BUILT file (validate.built.js) into the n8n Code node.\n//\n// Upstream node names (rename the strings below if your workflow differs):\n//   input items              -> New employee records\n//   \"Fetch Active Roles\"     -> Roles rows\n//   \"Fetch Active Templates\" -> Task_Templates rows\n// Env: OFFICE_TIMEZONE (falls back to UTC).\n//\n// Output: one item per employee = original fields + { _valid, _errors }.\n\n// ---- inlined from scripts/dates.js ----\n// dates.js \u2014 due-date arithmetic for onboarding tasks.\n//\n// Pure and deterministic: operates only on the ISO string it is given, in UTC.\n// No `Date.now()`, no local timezone, no external date library. This is on purpose \u2014\n// timezone only matters when comparing against \"today\" (notification / escalation\n// logic), never in the offset arithmetic itself, so this module stays trivially\n// testable outside n8n.\n//\n// dueDateFromOffset(startDate, offset):\n//   Day_Offset is CALENDAR days (HR edits it and thinks calendar \u2014 \"contract 3 days\n//   before start\"), added to the start date. If the result lands on a weekend it is\n//   nudged to a working day, in the direction that keeps the deadline safe:\n//     offset > 0  (after start)      -> shift FORWARD to Monday\n//     offset < 0  (prep before start) -> shift BACKWARD to Friday, so a prep task\n//                                        never slips onto or past the day it precedes\n//     offset === 0 (the start day itself) -> returned as-is, never adjusted; per\n//                                        SPEC F1 the start date does not move, only\n//                                        task deadlines do\n//   All I/O is ISO `YYYY-MM-DD`.\n\nconst ISO_RE = /^\\d{4}-\\d{2}-\\d{2}$/;\nconst MS_PER_DAY = 86_400_000;\n\n/** Parse a strict ISO date to a UTC timestamp, rejecting malformed or impossible dates. */\nfunction parseISO(isoDate) {\n  if (typeof isoDate !== 'string' || !ISO_RE.test(isoDate)) {\n    throw new Error(`Invalid ISO date: ${JSON.stringify(isoDate)} (expected \"YYYY-MM-DD\")`);\n  }\n  const [y, m, d] = isoDate.split('-').map(Number);\n  const ts = Date.UTC(y, m - 1, d);\n  const back = new Date(ts);\n  // Reject values JS would silently roll over, e.g. 2026-02-31 -> 2026-03-03.\n  if (back.getUTCFullYear() !== y || back.getUTCMonth() !== m - 1 || back.getUTCDate() !== d) {\n    throw new Error(`Invalid calendar date: ${isoDate}`);\n  }\n  return ts;\n}\n\n/** Format a UTC timestamp back to ISO `YYYY-MM-DD`. */\nfunction toISO(ts) {\n  const dt = new Date(ts);\n  const y = dt.getUTCFullYear();\n  const m = String(dt.getUTCMonth() + 1).padStart(2, '0');\n  const d = String(dt.getUTCDate()).padStart(2, '0');\n  return `${y}-${m}-${d}`;\n}\n\nfunction isWeekendTs(ts) {\n  const day = new Date(ts).getUTCDay(); // 0 = Sun \u2026 6 = Sat\n  return day === 0 || day === 6;\n}\n\n/** True if the ISO date falls on Saturday or Sunday. */\nfunction isWeekend(isoDate) {\n  return isWeekendTs(parseISO(isoDate));\n}\n\n/**\n * Compute a task due date from the start date and a calendar-day offset, keeping the\n * result on a working day (see the sign rules in the file header).\n * @param {string} startDate - start date, ISO `YYYY-MM-DD`\n * @param {number} offset    - integer calendar-day offset; negative = before the start date\n * @returns {string} the resulting due date, ISO `YYYY-MM-DD`\n */\nfunction dueDateFromOffset(startDate, offset) {\n  if (!Number.isInteger(offset)) {\n    throw new Error(`offset must be an integer, got: ${JSON.stringify(offset)}`);\n  }\n  let ts = parseISO(startDate) + offset * MS_PER_DAY;\n  if (offset === 0) {\n    return toISO(ts); // the start day itself \u2014 never weekend-adjusted\n  }\n  const step = offset > 0 ? MS_PER_DAY : -MS_PER_DAY; // forward for after-start, backward for prep\n  while (isWeekendTs(ts)) {\n    ts += step;\n  }\n  return toISO(ts);\n}\n\n// ---- inlined from scripts/expandTemplates.js ----\n// expandTemplates.js \u2014 turn an employee + role templates into dated, assigned tasks.\n//\n// Pure and side-effect free: no network, no notifications. It returns\n// `{ tasks, warnings }`; F2 (the n8n workflow) is responsible for de-duping against\n// existing Task_Keys, writing the records, and messaging HR about the warnings.\n//\n// Shapes (plain values, as they arrive from Airtable rows in n8n):\n//   employee  { id, full_name, work_email, role, start_date, manager_telegram_id }\n//     - id: Airtable record id (the stable half of Task_Key)\n//     - role: role name string, matched against template.role\n//     - start_date: ISO YYYY-MM-DD\n//   template  { id, title, description, role, applies_to_all, assignee_role, day_offset, active }\n//   assignee  { name, assignee_role, telegram_id, active }\n//\n// Each returned task uses Airtable field names so the n8n Code node can write it\n// almost verbatim (link fields Employee/Template are record ids; wrap as [id] on write).\n\n\nconst MANAGER_ROLE = 'Manager';\n\n/**\n * A template is expanded for a role if it is active and either universal or role-matched.\n * Exported so validate.js applies the exact same selection when checking the Manager rule.\n */\nfunction appliesToRole(template, role) {\n  if (!template.active) return false;\n  return template.applies_to_all === true || template.role === role;\n}\n\n/**\n * Resolve the Telegram id for a task's assignee role.\n * Manager resolves to the employee's own manager; every other role to the first active\n * assignee with that role. Returns { telegram_id, reason } \u2014 reason is set only on a miss.\n */\nfunction resolveAssignee(assigneeRole, employee, assignees) {\n  if (assigneeRole === MANAGER_ROLE) {\n    const managerId = employee.manager_telegram_id;\n    if (managerId) return { telegram_id: managerId };\n    return { telegram_id: '', reason: 'Employee has no Manager_Telegram_ID' };\n  }\n  const match = assignees.find((a) => a.active && a.assignee_role === assigneeRole);\n  if (match && match.telegram_id) return { telegram_id: match.telegram_id };\n  return { telegram_id: '', reason: `No active assignee configured for role \"${assigneeRole}\"` };\n}\n\n/**\n * Expand a role's task templates into concrete onboarding tasks for one employee.\n * @returns {{ tasks: object[], warnings: {task_key:string, assignee_role:string, reason:string}[] }}\n */\nfunction expandTemplates(employee, templates, assignees) {\n  const tasks = [];\n  const warnings = [];\n\n  for (const template of templates) {\n    if (!appliesToRole(template, employee.role)) continue;\n\n    const taskKey = `${employee.id}::${template.id}`;\n    const { telegram_id, reason } = resolveAssignee(template.assignee_role, employee, assignees);\n    const unresolved = reason !== undefined;\n\n    if (unresolved) {\n      warnings.push({ task_key: taskKey, assignee_role: template.assignee_role, reason });\n    }\n\n    tasks.push({\n      Task_Key: taskKey,\n      Employee: employee.id,        // link (wrap as [id] when writing to Airtable)\n      Template: template.id,        // link\n      Title: template.title,        // snapshot at creation\n      Description: template.description,\n      Assignee_Role: template.assignee_role,\n      Blocking: template.blocking === true, // snapshot, not read live from the template\n      Category: template.category ?? null,  // snapshot too (used for dashboard grouping)\n      Assignee_Telegram_ID: telegram_id,\n      Due_Date: dueDateFromOffset(employee.start_date, template.day_offset),\n      Status: 'Pending',\n      Unresolved_Assignee: unresolved,\n    });\n  }\n\n  return { tasks, warnings };\n}\n\n// ---- inlined from scripts/validate.js ----\n// validate.js \u2014 new-hire intake validation (SPEC F1, step 1).\n//\n// Pure: collects ALL errors instead of throwing on the first, so HR sees every\n// problem at once. Returns { valid, errors }, errors = [{ field, message }].\n//\n// `today` is injected (ISO YYYY-MM-DD) so the module stays testable and timezone-free.\n// n8n must pass today's date in OFFICE_TIMEZONE; the default is UTC today as a fallback.\n\n\nconst REQUIRED_FIELDS = [\n  ['full_name', 'Full name'],\n  ['work_email', 'Work email'],\n  ['role', 'Role'],\n  ['department', 'Department'],\n  ['start_date', 'Start date'],\n];\n\nfunction isBlank(value) {\n  return value === undefined || value === null || String(value).trim() === '';\n}\n\n// Deliberately not an RFC regex \u2014 just enough to keep a broken address out of the\n// dedupe key (F1) and IT's mailbox task: exactly one \"@\", both sides non-empty,\n// and a dot inside the domain that is neither first nor last char.\nfunction isValidEmail(value) {\n  const parts = String(value).split('@');\n  if (parts.length !== 2) return false;\n  const [local, domain] = parts;\n  if (local.trim() === '' || domain.trim() === '') return false;\n  const dot = domain.indexOf('.');\n  return dot > 0 && dot < domain.length - 1;\n}\n\nfunction utcTodayISO() {\n  const now = new Date();\n  const pad = (n) => String(n).padStart(2, '0');\n  return `${now.getUTCFullYear()}-${pad(now.getUTCMonth() + 1)}-${pad(now.getUTCDate())}`;\n}\n\n/**\n * Validate a new-hire intake record.\n * @param {object} employee - { full_name, work_email, role, department, start_date, manager_telegram_id }\n * @param {object[]} roles - [{ role_name, active }]\n * @param {object[]} templates - [{ role, applies_to_all, assignee_role, active, ... }]\n * @param {string} [today] - ISO YYYY-MM-DD reference for \"future\"; defaults to UTC today\n * @returns {{ valid: boolean, errors: {field: string, message: string}[] }}\n */\nfunction validate(employee, roles, templates, today = utcTodayISO()) {\n  const errors = [];\n  const emp = employee ?? {};\n\n  // 1. Required fields present.\n  for (const [field, label] of REQUIRED_FIELDS) {\n    if (isBlank(emp[field])) {\n      errors.push({ field, message: `${label} is required` });\n    }\n  }\n\n  // 2. Email format (only when present \u2014 emptiness is already covered above).\n  if (!isBlank(emp.work_email) && !isValidEmail(emp.work_email)) {\n    errors.push({ field: 'work_email', message: `Work email is not a valid email address: ${emp.work_email}` });\n  }\n\n  // 3. Start date: valid ISO and strictly in the future (today is not the future).\n  if (!isBlank(emp.start_date)) {\n    let startTs;\n    try {\n      startTs = parseISO(emp.start_date);\n    } catch {\n      errors.push({ field: 'start_date', message: `Start date is not a valid date: ${emp.start_date}` });\n    }\n    if (startTs !== undefined && startTs <= parseISO(today)) {\n      errors.push({ field: 'start_date', message: 'Start date must be in the future' });\n    }\n  }\n\n  // 4. Role exists and is active.\n  const role = roles.find((r) => r.role_name === emp.role);\n  if (!isBlank(emp.role)) {\n    if (!role) {\n      errors.push({ field: 'role', message: `Role does not exist: ${emp.role}` });\n    } else if (!role.active) {\n      errors.push({ field: 'role', message: `Role is inactive: ${emp.role}` });\n    }\n  }\n\n  // 5. Manager rule: manager_telegram_id is required only if the role has at least one\n  //    active template assigned to the Manager (same selection as F2 expansion).\n  const needsManager = templates.some(\n    (t) => appliesToRole(t, emp.role) && t.assignee_role === 'Manager',\n  );\n  if (needsManager && isBlank(emp.manager_telegram_id)) {\n    errors.push({\n      field: 'manager_telegram_id',\n      message: 'Manager Telegram ID is required because this role has manager-assigned tasks',\n    });\n  }\n\n  return { valid: errors.length === 0, errors };\n}\n\n// --- n8n glue ---------------------------------------------------------------\n// Airtable lookups/links come back as arrays; take the first scalar.\nconst flat = (v) => (Array.isArray(v) ? v[0] : v);\n\nconst tz = $env.OFFICE_TIMEZONE || 'UTC';\n// en-CA gives YYYY-MM-DD; formatting in the office tz yields \"today\" as a plain date.\nconst today = new Date().toLocaleDateString('en-CA', { timeZone: tz });\n\nconst roles = $('Fetch Active Roles').all().map((i) => ({\n  role_name: i.json.Role_Name,\n  active: i.json.Active === true,\n}));\n\nconst templates = $('Fetch Active Templates').all().map((i) => ({\n  role: flat(i.json.Role_Name) ?? null,\n  applies_to_all: i.json.Applies_To_All === true,\n  assignee_role: i.json.Assignee_Role,\n  active: i.json.Active === true,\n}));\n\nreturn items.map((item) => {\n  const f = item.json;\n  const employee = {\n    id: f.id,\n    full_name: f.Full_Name,\n    work_email: f.Work_Email,\n    role: flat(f.Role_Name),\n    department: f.Department,\n    start_date: f.Start_Date,\n    manager_telegram_id: f.Manager_Telegram_ID,\n  };\n  const { valid, errors } = validate(employee, roles, templates, today);\n  return { json: { ...f, _valid: valid, _errors: errors } };\n});\n"
      },
      "id": "Validate",
      "name": "Validate",
      "type": "n8n-nodes-base.code",
      "typeVersion": 2,
      "position": [
        700,
        300
      ]
    },
    {
      "parameters": {
        "conditions": {
          "options": {
            "caseSensitive": true,
            "typeValidation": "strict"
          },
          "combinator": "and",
          "conditions": [
            {
              "leftValue": "={{ $json._valid }}",
              "rightValue": true,
              "operator": {
                "type": "boolean",
                "operation": "true",
                "singleValue": true
              }
            }
          ]
        }
      },
      "id": "IF Valid",
      "name": "IF Valid",
      "type": "n8n-nodes-base.if",
      "typeVersion": 2,
      "position": [
        920,
        300
      ]
    },
    {
      "parameters": {
        "language": "javaScript",
        "jsCode": "return items.map((i) => ({ json: {\n  id: i.json.id,\n  Status: 'Error',\n  Validation_Notes: (i.json._errors || []).map((e) => e.field + ': ' + e.message).join('; '),\n} }));"
      },
      "id": "Build Error Update",
      "name": "Build Error Update",
      "type": "n8n-nodes-base.code",
      "typeVersion": 2,
      "position": [
        1140,
        460
      ]
    },
    {
      "parameters": {
        "method": "PATCH",
        "url": "=https://api.airtable.com/v0/{{ $env.AIRTABLE_BASE_ID }}/Employees/{{ $json.id }}",
        "authentication": "predefinedCredentialType",
        "nodeCredentialType": "airtableTokenApi",
        "sendBody": true,
        "specifyBody": "json",
        "jsonBody": "={{ JSON.stringify({ fields: { Status: 'Error', Validation_Notes: $json.Validation_Notes } }) }}",
        "options": {}
      },
      "id": "Mark Error",
      "name": "Mark Error",
      "type": "n8n-nodes-base.httpRequest",
      "typeVersion": 4.2,
      "position": [
        1360,
        460
      ],
      "notes": "Airtable update via HTTP PATCH; attach the Airtable PAT credential (Predefined Credential Type -> Airtable API)."
    },
    {
      "parameters": {
        "resource": "message",
        "operation": "sendMessage",
        "chatId": "={{ $env.TELEGRAM_HR_CHAT_ID }}",
        "text": "=Onboarding intake failed for {{ $json.fields.Full_Name }}:\n{{ $json.fields.Validation_Notes }}",
        "additionalFields": {}
      },
      "id": "Notify HR (invalid)",
      "name": "Notify HR (invalid)",
      "type": "n8n-nodes-base.telegram",
      "typeVersion": 1.2,
      "position": [
        1580,
        460
      ],
      "notes": "Attach Telegram Bot API credential after import."
    },
    {
      "parameters": {
        "language": "javaScript",
        "jsCode": "// ============================================================================\n// GENERATED by `npm run build:nodes` \u2014 DO NOT EDIT.\n// Wrapper: workflows/code-nodes/expandTemplates.wrapper.js\n// Core inlined verbatim from: scripts/dates.js, scripts/expandTemplates.js\n// Paste this whole file into the n8n Code node.\n// ============================================================================\n\n// n8n Code node \u2014 \"Expand templates\"  (THIN WRAPPER \u2014 do not put logic here)\n//\n// The core (expandTemplates + its deps) is inlined by `npm run build:nodes` from\n// scripts/ verbatim, replacing the __CORE__ marker. Edit logic in scripts/, never\n// here. Paste the BUILT file (expandTemplates.built.js) into the n8n Code node.\n//\n// Upstream node names (rename the strings below if your workflow differs):\n//   input items              -> valid employee records (IF \"true\" branch)\n//   \"Fetch Active Templates\" -> Task_Templates rows\n//   \"Fetch Active Assignees\" -> Assignees rows\n//\n// Output: one item PER TASK (json = Onboarding_Tasks fields). Unresolved assignees\n// carry Unresolved_Assignee = true; the HR summary downstream is derived from those.\n\n// ---- inlined from scripts/dates.js ----\n// dates.js \u2014 due-date arithmetic for onboarding tasks.\n//\n// Pure and deterministic: operates only on the ISO string it is given, in UTC.\n// No `Date.now()`, no local timezone, no external date library. This is on purpose \u2014\n// timezone only matters when comparing against \"today\" (notification / escalation\n// logic), never in the offset arithmetic itself, so this module stays trivially\n// testable outside n8n.\n//\n// dueDateFromOffset(startDate, offset):\n//   Day_Offset is CALENDAR days (HR edits it and thinks calendar \u2014 \"contract 3 days\n//   before start\"), added to the start date. If the result lands on a weekend it is\n//   nudged to a working day, in the direction that keeps the deadline safe:\n//     offset > 0  (after start)      -> shift FORWARD to Monday\n//     offset < 0  (prep before start) -> shift BACKWARD to Friday, so a prep task\n//                                        never slips onto or past the day it precedes\n//     offset === 0 (the start day itself) -> returned as-is, never adjusted; per\n//                                        SPEC F1 the start date does not move, only\n//                                        task deadlines do\n//   All I/O is ISO `YYYY-MM-DD`.\n\nconst ISO_RE = /^\\d{4}-\\d{2}-\\d{2}$/;\nconst MS_PER_DAY = 86_400_000;\n\n/** Parse a strict ISO date to a UTC timestamp, rejecting malformed or impossible dates. */\nfunction parseISO(isoDate) {\n  if (typeof isoDate !== 'string' || !ISO_RE.test(isoDate)) {\n    throw new Error(`Invalid ISO date: ${JSON.stringify(isoDate)} (expected \"YYYY-MM-DD\")`);\n  }\n  const [y, m, d] = isoDate.split('-').map(Number);\n  const ts = Date.UTC(y, m - 1, d);\n  const back = new Date(ts);\n  // Reject values JS would silently roll over, e.g. 2026-02-31 -> 2026-03-03.\n  if (back.getUTCFullYear() !== y || back.getUTCMonth() !== m - 1 || back.getUTCDate() !== d) {\n    throw new Error(`Invalid calendar date: ${isoDate}`);\n  }\n  return ts;\n}\n\n/** Format a UTC timestamp back to ISO `YYYY-MM-DD`. */\nfunction toISO(ts) {\n  const dt = new Date(ts);\n  const y = dt.getUTCFullYear();\n  const m = String(dt.getUTCMonth() + 1).padStart(2, '0');\n  const d = String(dt.getUTCDate()).padStart(2, '0');\n  return `${y}-${m}-${d}`;\n}\n\nfunction isWeekendTs(ts) {\n  const day = new Date(ts).getUTCDay(); // 0 = Sun \u2026 6 = Sat\n  return day === 0 || day === 6;\n}\n\n/** True if the ISO date falls on Saturday or Sunday. */\nfunction isWeekend(isoDate) {\n  return isWeekendTs(parseISO(isoDate));\n}\n\n/**\n * Compute a task due date from the start date and a calendar-day offset, keeping the\n * result on a working day (see the sign rules in the file header).\n * @param {string} startDate - start date, ISO `YYYY-MM-DD`\n * @param {number} offset    - integer calendar-day offset; negative = before the start date\n * @returns {string} the resulting due date, ISO `YYYY-MM-DD`\n */\nfunction dueDateFromOffset(startDate, offset) {\n  if (!Number.isInteger(offset)) {\n    throw new Error(`offset must be an integer, got: ${JSON.stringify(offset)}`);\n  }\n  let ts = parseISO(startDate) + offset * MS_PER_DAY;\n  if (offset === 0) {\n    return toISO(ts); // the start day itself \u2014 never weekend-adjusted\n  }\n  const step = offset > 0 ? MS_PER_DAY : -MS_PER_DAY; // forward for after-start, backward for prep\n  while (isWeekendTs(ts)) {\n    ts += step;\n  }\n  return toISO(ts);\n}\n\n// ---- inlined from scripts/expandTemplates.js ----\n// expandTemplates.js \u2014 turn an employee + role templates into dated, assigned tasks.\n//\n// Pure and side-effect free: no network, no notifications. It returns\n// `{ tasks, warnings }`; F2 (the n8n workflow) is responsible for de-duping against\n// existing Task_Keys, writing the records, and messaging HR about the warnings.\n//\n// Shapes (plain values, as they arrive from Airtable rows in n8n):\n//   employee  { id, full_name, work_email, role, start_date, manager_telegram_id }\n//     - id: Airtable record id (the stable half of Task_Key)\n//     - role: role name string, matched against template.role\n//     - start_date: ISO YYYY-MM-DD\n//   template  { id, title, description, role, applies_to_all, assignee_role, day_offset, active }\n//   assignee  { name, assignee_role, telegram_id, active }\n//\n// Each returned task uses Airtable field names so the n8n Code node can write it\n// almost verbatim (link fields Employee/Template are record ids; wrap as [id] on write).\n\n\nconst MANAGER_ROLE = 'Manager';\n\n/**\n * A template is expanded for a role if it is active and either universal or role-matched.\n * Exported so validate.js applies the exact same selection when checking the Manager rule.\n */\nfunction appliesToRole(template, role) {\n  if (!template.active) return false;\n  return template.applies_to_all === true || template.role === role;\n}\n\n/**\n * Resolve the Telegram id for a task's assignee role.\n * Manager resolves to the employee's own manager; every other role to the first active\n * assignee with that role. Returns { telegram_id, reason } \u2014 reason is set only on a miss.\n */\nfunction resolveAssignee(assigneeRole, employee, assignees) {\n  if (assigneeRole === MANAGER_ROLE) {\n    const managerId = employee.manager_telegram_id;\n    if (managerId) return { telegram_id: managerId };\n    return { telegram_id: '', reason: 'Employee has no Manager_Telegram_ID' };\n  }\n  const match = assignees.find((a) => a.active && a.assignee_role === assigneeRole);\n  if (match && match.telegram_id) return { telegram_id: match.telegram_id };\n  return { telegram_id: '', reason: `No active assignee configured for role \"${assigneeRole}\"` };\n}\n\n/**\n * Expand a role's task templates into concrete onboarding tasks for one employee.\n * @returns {{ tasks: object[], warnings: {task_key:string, assignee_role:string, reason:string}[] }}\n */\nfunction expandTemplates(employee, templates, assignees) {\n  const tasks = [];\n  const warnings = [];\n\n  for (const template of templates) {\n    if (!appliesToRole(template, employee.role)) continue;\n\n    const taskKey = `${employee.id}::${template.id}`;\n    const { telegram_id, reason } = resolveAssignee(template.assignee_role, employee, assignees);\n    const unresolved = reason !== undefined;\n\n    if (unresolved) {\n      warnings.push({ task_key: taskKey, assignee_role: template.assignee_role, reason });\n    }\n\n    tasks.push({\n      Task_Key: taskKey,\n      Employee: employee.id,        // link (wrap as [id] when writing to Airtable)\n      Template: template.id,        // link\n      Title: template.title,        // snapshot at creation\n      Description: template.description,\n      Assignee_Role: template.assignee_role,\n      Blocking: template.blocking === true, // snapshot, not read live from the template\n      Category: template.category ?? null,  // snapshot too (used for dashboard grouping)\n      Assignee_Telegram_ID: telegram_id,\n      Due_Date: dueDateFromOffset(employee.start_date, template.day_offset),\n      Status: 'Pending',\n      Unresolved_Assignee: unresolved,\n    });\n  }\n\n  return { tasks, warnings };\n}\n\n// --- n8n glue ---------------------------------------------------------------\nconst flat = (v) => (Array.isArray(v) ? v[0] : v);\n\nconst templates = $('Fetch Active Templates').all().map((i) => ({\n  id: i.json.id,\n  title: i.json.Title,\n  description: i.json.Description,\n  role: flat(i.json.Role_Name) ?? null,\n  applies_to_all: i.json.Applies_To_All === true,\n  assignee_role: i.json.Assignee_Role,\n  day_offset: Number(i.json.Day_Offset),\n  blocking: i.json.Blocking === true,\n  category: i.json.Category ?? null,\n  active: i.json.Active === true,\n}));\n\nconst assignees = $('Fetch Active Assignees').all().map((i) => ({\n  assignee_role: i.json.Assignee_Role,\n  telegram_id: i.json.Telegram_ID != null ? String(i.json.Telegram_ID) : '',\n  active: i.json.Active === true,\n}));\n\nconst out = [];\nfor (const item of items) {\n  const f = item.json;\n  const employee = {\n    id: f.id,\n    role: flat(f.Role_Name),\n    start_date: f.Start_Date,\n    manager_telegram_id: f.Manager_Telegram_ID,\n  };\n  const { tasks } = expandTemplates(employee, templates, assignees);\n  // Airtable link fields need arrays of record ids, not bare strings.\n  for (const task of tasks) out.push({ json: { ...task, Employee: [task.Employee], Template: [task.Template] } });\n}\nreturn out;\n"
      },
      "id": "Expand Templates",
      "name": "Expand Templates",
      "type": "n8n-nodes-base.code",
      "typeVersion": 2,
      "position": [
        1140,
        160
      ]
    },
    {
      "parameters": {
        "resource": "record",
        "operation": "search",
        "base": {
          "__rl": true,
          "mode": "id",
          "value": "={{ $env.AIRTABLE_BASE_ID }}"
        },
        "table": {
          "__rl": true,
          "mode": "name",
          "value": "Onboarding_Tasks"
        },
        "filterByFormula": "={{ (() => { const ids = [...new Set($('Expand Templates').all().map(t => Array.isArray(t.json.Employee) ? t.json.Employee[0] : t.json.Employee))]; return ids.length ? 'OR(' + ids.map(id => \"FIND('\" + id + \"::', {Task_Key}) > 0\").join(', ') + ')' : 'FALSE()'; })() }}",
        "options": {}
      },
      "id": "Fetch Existing Tasks",
      "name": "Fetch Existing Tasks",
      "type": "n8n-nodes-base.airtable",
      "typeVersion": 2.1,
      "position": [
        1360,
        160
      ],
      "executeOnce": true,
      "notes": "Attach Airtable Personal Access Token credential after import.",
      "alwaysOutputData": true
    },
    {
      "parameters": {
        "language": "javaScript",
        "jsCode": "// Dedupe by Task_Key \u2014 create-only. Input items = existing Onboarding_Tasks.\nconst existing = new Set(items.map((i) => i.json.Task_Key));\nreturn $('Expand Templates').all().filter((t) => !existing.has(t.json.Task_Key));"
      },
      "id": "Dedupe by Task_Key",
      "name": "Dedupe by Task_Key",
      "type": "n8n-nodes-base.code",
      "typeVersion": 2,
      "position": [
        1580,
        160
      ]
    },
    {
      "parameters": {
        "resource": "record",
        "operation": "create",
        "base": {
          "__rl": true,
          "mode": "id",
          "value": "={{ $env.AIRTABLE_BASE_ID }}"
        },
        "table": {
          "__rl": true,
          "mode": "name",
          "value": "Onboarding_Tasks"
        },
        "columns": {
          "mappingMode": "autoMapInputData"
        },
        "options": {
          "bulkSize": 10
        }
      },
      "id": "Create Tasks",
      "name": "Create Tasks",
      "type": "n8n-nodes-base.airtable",
      "typeVersion": 2.1,
      "position": [
        1800,
        160
      ],
      "notes": "Link fields Employee/Template take record ids; wrap as [id] if your Airtable node expects arrays."
    },
    {
      "parameters": {
        "language": "javaScript",
        "jsCode": "// One item per valid employee id, for the status update.\nconst seen = new Set();\nconst out = [];\nfor (const it of $('Validate').all()) {\n  if (it.json._valid && !seen.has(it.json.id)) { seen.add(it.json.id); out.push({ json: { id: it.json.id, Status: 'Onboarding' } }); }\n}\nreturn out;"
      },
      "id": "Collect Valid Employees",
      "name": "Collect Valid Employees",
      "type": "n8n-nodes-base.code",
      "typeVersion": 2,
      "position": [
        2020,
        60
      ]
    },
    {
      "parameters": {
        "method": "PATCH",
        "url": "=https://api.airtable.com/v0/{{ $env.AIRTABLE_BASE_ID }}/Employees/{{ $json.id }}",
        "authentication": "predefinedCredentialType",
        "nodeCredentialType": "airtableTokenApi",
        "sendBody": true,
        "specifyBody": "json",
        "jsonBody": "={{ JSON.stringify({ fields: { Status: 'Onboarding' } }) }}",
        "options": {}
      },
      "id": "Mark Onboarding",
      "name": "Mark Onboarding",
      "type": "n8n-nodes-base.httpRequest",
      "typeVersion": 4.2,
      "position": [
        2240,
        60
      ],
      "notes": "Airtable update via HTTP PATCH; attach the Airtable PAT credential (Predefined Credential Type -> Airtable API)."
    },
    {
      "parameters": {
        "language": "javaScript",
        "jsCode": "// One HR summary from the tasks we just created that have no assignee.\nconst created = $('Dedupe by Task_Key').all();\nconst unresolved = created.filter((t) => t.json.Unresolved_Assignee === true);\nif (unresolved.length === 0) return [{ json: { hasWarnings: false, message: '' } }];\nconst byEmp = {};\nfor (const t of unresolved) { (byEmp[t.json.Employee] ??= []).push(t.json.Title); }\nconst lines = Object.entries(byEmp).map(([emp, titles]) => `- ${emp}: ${titles.length} task(s) \u2014 ${titles.join(', ')}`);\nconst message = `Onboarding created, but ${unresolved.length} task(s) have no assignee. Check the assignee config:\\n` + lines.join('\\n');\nreturn [{ json: { hasWarnings: true, message } }];"
      },
      "id": "HR Warnings Summary",
      "name": "HR Warnings Summary",
      "type": "n8n-nodes-base.code",
      "typeVersion": 2,
      "position": [
        2020,
        260
      ]
    },
    {
      "parameters": {
        "conditions": {
          "options": {
            "caseSensitive": true,
            "typeValidation": "strict"
          },
          "combinator": "and",
          "conditions": [
            {
              "leftValue": "={{ $json.hasWarnings }}",
              "rightValue": true,
              "operator": {
                "type": "boolean",
                "operation": "true",
                "singleValue": true
              }
            }
          ]
        }
      },
      "id": "IF Has Warnings",
      "name": "IF Has Warnings",
      "type": "n8n-nodes-base.if",
      "typeVersion": 2,
      "position": [
        2240,
        260
      ]
    },
    {
      "parameters": {
        "resource": "message",
        "operation": "sendMessage",
        "chatId": "={{ $env.TELEGRAM_HR_CHAT_ID }}",
        "text": "={{ $json.message }}",
        "additionalFields": {}
      },
      "id": "Notify HR (warnings)",
      "name": "Notify HR (warnings)",
      "type": "n8n-nodes-base.telegram",
      "typeVersion": 1.2,
      "position": [
        2460,
        260
      ],
      "notes": "Attach Telegram Bot API credential after import."
    }
  ],
  "connections": {
    "Schedule Trigger": {
      "main": [
        [
          {
            "node": "Fetch Active Roles",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "Fetch Active Roles": {
      "main": [
        [
          {
            "node": "Fetch Active Templates",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "Fetch Active Templates": {
      "main": [
        [
          {
            "node": "Fetch Active Assignees",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "Fetch Active Assignees": {
      "main": [
        [
          {
            "node": "Fetch New Employees",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "Fetch New Employees": {
      "main": [
        [
          {
            "node": "Validate",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "Validate": {
      "main": [
        [
          {
            "node": "IF Valid",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "IF Valid": {
      "main": [
        [
          {
            "node": "Expand Templates",
            "type": "main",
            "index": 0
          }
        ],
        [
          {
            "node": "Build Error Update",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "Build Error Update": {
      "main": [
        [
          {
            "node": "Mark Error",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "Mark Error": {
      "main": [
        [
          {
            "node": "Notify HR (invalid)",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "Expand Templates": {
      "main": [
        [
          {
            "node": "Fetch Existing Tasks",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "Fetch Existing Tasks": {
      "main": [
        [
          {
            "node": "Dedupe by Task_Key",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "Dedupe by Task_Key": {
      "main": [
        [
          {
            "node": "Create Tasks",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "Create Tasks": {
      "main": [
        [
          {
            "node": "Collect Valid Employees",
            "type": "main",
            "index": 0
          },
          {
            "node": "HR Warnings Summary",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "Collect Valid Employees": {
      "main": [
        [
          {
            "node": "Mark Onboarding",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "HR Warnings Summary": {
      "main": [
        [
          {
            "node": "IF Has Warnings",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "IF Has Warnings": {
      "main": [
        [
          {
            "node": "Notify HR (warnings)",
            "type": "main",
            "index": 0
          }
        ],
        []
      ]
    }
  },
  "active": false,
  "settings": {
    "executionOrder": "v1"
  },
  "meta": {
    "note": "Set Settings -> Error Workflow to an error handler that messages HR (SPEC: no silent failures). Credentials are stripped; attach Airtable + Telegram after import."
  }
}

About this workflow

01 - Intake (F1 + F2). Uses airtable, httpRequest, telegram. Scheduled trigger; 19 nodes.

Source: https://github.com/ibragim-0202/employee-onboarding-automation/blob/main/workflows/01-intake.json — original creator credit. Request a take-down →

More Slack & Telegram workflows → · Browse all categories →

Related workflows

Workflows that share integrations, category, or trigger type with this one. All free to copy and import.