Headcount plan reconciliation with n8n

# Headcount plan reconciliation — n8n flow

This flow reconciles the firm's headcount plan against open reqs in the ATS and actual hires in HRIS, every Monday at 8am, and posts a per-team and aggregate variance report to Slack. It catches the failure modes that quietly cost a quarter — reqs opened against teams already at plan, hires booked against the wrong team, contractor conversions counted as new hires.

## Import

1. n8n → Workflows → Import from file → pick `headcount-plan-reconciliation-n8n.json`.
2. Set the workflow timezone (top of the canvas) to your office timezone. The default is `America/New_York`.
3. Do NOT enable yet. Set up credentials, the team mapping, and run the dry-run first.

## Credentials (four required)

### `PLACEHOLDER_PLAN_DB_CRED_ID` — Plan source

The flow's default is Postgres. The query in `Fetch Plan` reads from a `headcount_plan` table with columns `team`, `level`, `count`, `target_start_date`, `req_status`, `last_updated_at`.

If your plan lives elsewhere:

- **Google Sheets** — replace the `Fetch Plan` node with a Google Sheets node reading the plan tab. Field mapping happens in the next node.
- **Snowflake / BigQuery** — replace with the equivalent connector; same query shape works.
- **Carta / Pave / Lattice headcount module** — use their export-to-CSV path or API.

The schema is what matters; the source is interchangeable.

### `PLACEHOLDER_ASHBY_CRED_ID` — Ashby API key

- Ashby admin → Settings → API → Create key (read scope only).
- HTTP Basic Auth: username = the API key, password = empty.

### `PLACEHOLDER_HRIS_CRED_ID` — HRIS API token

The flow's default uses HTTP Header Auth. Per-HRIS specifics:

- **BambooHR** — API key as basic-auth username, password = `x`.
- **Workday** — OAuth 2.0; the n8n Workday connector handles the token refresh.
- **Rippling** — Bearer token in the `Authorization` header.
- **HiBob / Personio / Justworks / Gusto** — each has its own auth flavor; refer to the vendor docs.

Set `HRIS_BASE_URL` in the n8n env to the API base for your HRIS.

### `PLACEHOLDER_SLACK_CRED_ID` — Slack bot token

- Slack app with `chat:write` scope.
- Invite the bot into per-team `#hiring-{team}` channels AND `#recruiting-leadership`.

### `PLACEHOLDER_ANTHROPIC_CRED_ID` — Anthropic API key

- console.anthropic.com → API Keys.
- The flow uses `claude-sonnet-4-6` for narrative composition; the model only narrates, it does not compute the variance numbers.

## Team mapping file

Save `team-mapping-template.yml` from this bundle as `/data/team_mapping.yml` (or wherever `TEAM_MAPPING_PATH` env points) and fill it in. The shape:

```yaml
teams:
  Engineering:
    canonical: engineering
    aliases:
      plan: ["Engineering"]
      ashby: ["Engineering", "Eng"]
      hris: ["Engineering Department", "ENGINEERING"]
  Sales:
    canonical: sales
    aliases:
      plan: ["Sales"]
      ashby: ["Sales", "Revenue"]
      hris: ["Sales", "Sales Department"]
  Customer Success:
    canonical: customer-success
    aliases:
      plan: ["CS", "Customer Success"]
      ashby: ["Customer Success"]
      hris: ["Customer Success", "CS"]
```

The mapping is the binding setup cost. Without it, the flow surfaces every team as an anomaly. With it, ongoing maintenance is low — only when teams are created or merged.

## Dry-run procedure

1. Set the cron trigger to manual (deactivate the schedule).
2. Replay last quarter's plan, reqs, and hires (use n8n's "Execute workflow" button after pointing the queries at last-quarter date ranges).
3. Compare the variance numbers to what your finance partner had at quarter-end.
4. If they don't match, the team mapping is incomplete OR the contractor / FTE classification in the HRIS query is off.
5. Tune the team mapping. Re-run.

Only enable the schedule once the dry-run matches finance.

## First-run sanity check

After enabling, check the next two Monday runs:

1. Confirm per-team channels received only their own variance row (privacy posture).
2. Confirm aggregate channel received the cross-team table.
3. Confirm anomaly cells are surfaced (zero anomalies on the first real run usually means the mapping silently dropped unmapped entities — check `Reconcile` output for `unmapped_*` strings).
4. Confirm the LLM narrative did NOT infer causes ("the team is behind because…"). If it did, the system prompt's drift; flag for tuning.

## Known limits

- The flow assumes a single quarter's data. Roll-forward into next quarter requires a separate query change in `Fetch Plan`.
- The LLM narrative is composed in a single call with a 60s timeout. For very large firms (>500 hires/quarter), the structured reconciliation may exceed Sonnet's context window — split the narrative across multiple LLM calls or use the deterministic table only.
- The flow does not write anywhere except Slack. Variance trends over time live in your data warehouse, not in this flow.

{
  "name": "Headcount plan reconciliation",
  "nodes": [
    {
      "parameters": {
        "rule": {
          "interval": [
            {
              "field": "weeks",
              "weeksInterval": 1,
              "triggerAtDay": [1],
              "triggerAtHour": 8,
              "triggerAtMinute": 0
            }
          ]
        }
      },
      "id": "3a3a3a3a-0001-0000-0000-000000000001",
      "name": "Cron Monday 8am",
      "type": "n8n-nodes-base.scheduleTrigger",
      "typeVersion": 1.2,
      "position": [240, 400],
      "notesInFlow": true,
      "notes": "Fires Mondays at 8am in the workflow's configured timezone (set in workflow settings, NOT here). The node-level timezone defaults to workflow timezone."
    },
    {
      "parameters": {
        "operation": "executeQuery",
        "query": "SELECT\n  team,\n  level,\n  count,\n  target_start_date,\n  req_status,\n  last_updated_at\nFROM headcount_plan\nWHERE quarter = $1\n  AND deleted_at IS NULL;",
        "options": {
          "queryReplacement": "={{ ['Q', Math.floor(new Date().getMonth() / 3) + 1, '-', new Date().getFullYear()].join('') }}"
        }
      },
      "id": "3a3a3a3a-0001-0000-0000-000000000002",
      "name": "Fetch Plan",
      "type": "n8n-nodes-base.postgres",
      "typeVersion": 2.4,
      "position": [460, 280],
      "credentials": {
        "postgres": {
          "id": "PLACEHOLDER_PLAN_DB_CRED_ID",
          "name": "Postgres — headcount plan source"
        }
      },
      "notesInFlow": true,
      "notes": "Reads the current quarter's plan rows. Replace with a Sheets connector if your plan lives in Google Sheets; replace with a Snowflake/BigQuery connector if your plan lives in the data warehouse."
    },
    {
      "parameters": {
        "method": "POST",
        "url": "https://api.ashbyhq.com/job.list",
        "authentication": "predefinedCredentialType",
        "nodeCredentialType": "httpBasicAuth",
        "sendHeaders": true,
        "headerParameters": {
          "parameters": [
            { "name": "Accept", "value": "application/json" },
            { "name": "Content-Type", "value": "application/json" }
          ]
        },
        "sendBody": true,
        "specifyBody": "json",
        "jsonBody": "={\n  \"status\": \"Open\"\n}",
        "options": {
          "response": { "response": { "responseFormat": "json", "neverError": false } }
        }
      },
      "id": "3a3a3a3a-0001-0000-0000-000000000003",
      "name": "Fetch Open Reqs (Ashby)",
      "type": "n8n-nodes-base.httpRequest",
      "typeVersion": 4.2,
      "position": [460, 400],
      "credentials": {
        "httpBasicAuth": {
          "id": "PLACEHOLDER_ASHBY_CRED_ID",
          "name": "Ashby API key (read scope)"
        }
      },
      "notesInFlow": true,
      "notes": "Pulls all open Ashby jobs. POST is correct (Ashby is POST-only). Pagination handled by Ashby's nextCursor — the response includes `nextCursor` if more pages exist; loop in production by re-calling until absent."
    },
    {
      "parameters": {
        "method": "GET",
        "url": "={{ $env.HRIS_BASE_URL }}/api/v1/employees",
        "authentication": "predefinedCredentialType",
        "nodeCredentialType": "httpHeaderAuth",
        "sendQuery": true,
        "queryParameters": {
          "parameters": [
            { "name": "fields", "value": "id,team,level,startDate,employmentType" },
            { "name": "filter", "value": "startDate>={{ $now.startOf('quarter').toISO() }}" }
          ]
        },
        "options": {
          "response": { "response": { "responseFormat": "json", "neverError": false } }
        }
      },
      "id": "3a3a3a3a-0001-0000-0000-000000000004",
      "name": "Fetch Hires (HRIS)",
      "type": "n8n-nodes-base.httpRequest",
      "typeVersion": 4.2,
      "position": [460, 520],
      "credentials": {
        "httpHeaderAuth": {
          "id": "PLACEHOLDER_HRIS_CRED_ID",
          "name": "HRIS API token"
        }
      },
      "notesInFlow": true,
      "notes": "Generic shape; adjust URL and field names for your HRIS (BambooHR, Workday, Rippling all expose similar endpoints). Filter on startDate >= quarter start to avoid pulling unrelated records."
    },
    {
      "parameters": {
        "jsCode": "// Reconcile plan + open reqs + hires by (team, level).\n// Returns a structured table the narrative composer formats.\nconst fs = require('fs');\nconst yaml = require('js-yaml');\n\nconst MAPPING_PATH = $env.TEAM_MAPPING_PATH || '/data/team_mapping.yml';\nlet mapping;\ntry {\n  mapping = yaml.load(fs.readFileSync(MAPPING_PATH, 'utf8'));\n} catch (e) {\n  return [{ json: { status: 'halted', reason: 'team_mapping_load_failed', error: e.message } }];\n}\n\nconst plan = $('Fetch Plan').all().map(r => r.json);\nconst reqs = ($('Fetch Open Reqs (Ashby)').first().json.results?.jobs || []);\nconst hires = ($('Fetch Hires (HRIS)').first().json.employees || []);\n\n// Plan-source freshness check.\nconst planLastUpdated = plan.length ? new Date(Math.max(...plan.map(p => new Date(p.last_updated_at).getTime()))) : null;\nconst planAgeDays = planLastUpdated ? Math.floor((Date.now() - planLastUpdated.getTime()) / 86400000) : null;\nconst planStale = planAgeDays !== null && planAgeDays > 14;\n\n// Build cells keyed by team/level. Map cross-system team names through the mapping file.\nconst cells = new Map();\nconst key = (team, level) => `${team}::${level}`;\nconst getCell = (team, level) => {\n  const k = key(team, level);\n  if (!cells.has(k)) cells.set(k, { team, level, plan: 0, open_reqs: 0, hires: 0, conversions: 0, off_cycle: 0, anomalies: [] });\n  return cells.get(k);\n};\n\nconst mapTeam = (raw, source) => {\n  const m = (mapping.teams || {})[raw];\n  return m ? m.canonical : null;\n};\n\nfor (const p of plan) {\n  const t = mapTeam(p.team, 'plan');\n  if (!t) { getCell(p.team, p.level).anomalies.push(`unmapped_plan_team:${p.team}`); continue; }\n  const c = getCell(t, p.level);\n  c.plan += p.count;\n}\n\nfor (const r of reqs) {\n  const t = mapTeam(r.team?.name || r.department || '', 'ashby');\n  const level = r.customFields?.level || 'unknown';\n  if (!t) { getCell(r.team?.name || 'unknown', level).anomalies.push(`unmapped_ashby_team:${r.team?.name}`); continue; }\n  const c = getCell(t, level);\n  c.open_reqs += 1;\n}\n\nconst quarterStart = new Date(new Date().getFullYear(), Math.floor(new Date().getMonth() / 3) * 3, 1);\nfor (const h of hires) {\n  const t = mapTeam(h.team, 'hris');\n  const level = h.level || 'unknown';\n  if (!t) { getCell(h.team || 'unknown', level).anomalies.push(`unmapped_hris_team:${h.team}`); continue; }\n  const c = getCell(t, level);\n  if (h.employmentType !== 'fte') {\n    if (h.priorEmploymentType === 'contractor') c.conversions += 1;\n    continue;\n  }\n  if (new Date(h.startDate) < quarterStart) c.off_cycle += 1;\n  c.hires += 1;\n}\n\n// Compute variance per cell.\nconst rows = [];\nfor (const c of cells.values()) {\n  const variance = c.plan - c.hires - c.open_reqs;\n  rows.push({ ...c, variance, drift_warning: Math.abs(variance) > 3 });\n}\n\n// Anomaly cells: open reqs with no plan entry, hires with no plan entry.\nconst anomalyRows = rows.filter(r => r.anomalies.length > 0 || (r.open_reqs > 0 && r.plan === 0) || (r.hires > 0 && r.plan === 0));\n\nreturn [{\n  json: {\n    quarter: `Q${Math.floor(new Date().getMonth() / 3) + 1}-${new Date().getFullYear()}`,\n    plan_stale: planStale,\n    plan_age_days: planAgeDays,\n    rows,\n    anomaly_rows: anomalyRows,\n    totals: {\n      plan: rows.reduce((s, r) => s + r.plan, 0),\n      hires: rows.reduce((s, r) => s + r.hires, 0),\n      open_reqs: rows.reduce((s, r) => s + r.open_reqs, 0),\n      conversions: rows.reduce((s, r) => s + r.conversions, 0),\n      off_cycle: rows.reduce((s, r) => s + r.off_cycle, 0),\n    }\n  }\n}];"
      },
      "id": "3a3a3a3a-0001-0000-0000-000000000005",
      "name": "Reconcile",
      "type": "n8n-nodes-base.code",
      "typeVersion": 2,
      "position": [720, 400],
      "notesInFlow": true,
      "notes": "Deterministic reconciliation. Loads team mapping from /data/team_mapping.yml. Joins plan + reqs + hires by canonical team. Surfaces unmapped entities and contractor conversions separately."
    },
    {
      "parameters": {
        "method": "POST",
        "url": "https://api.anthropic.com/v1/messages",
        "sendHeaders": true,
        "headerParameters": {
          "parameters": [
            { "name": "Content-Type", "value": "application/json" },
            { "name": "x-api-key", "value": "={{ $credentials.anthropicApi.apiKey }}" },
            { "name": "anthropic-version", "value": "2023-06-01" }
          ]
        },
        "sendBody": true,
        "specifyBody": "json",
        "jsonBody": "={\n  \"model\": \"claude-sonnet-4-6\",\n  \"max_tokens\": 2000,\n  \"system\": \"You are a recruiting-leadership variance reporter. Take a structured headcount reconciliation table and write a 200-word per-team summary plus a 400-word aggregate summary. Use the deterministic numbers verbatim. DO NOT infer causes — never say 'the team is behind because…'. Surface the cells that have variance, the anomaly cells, the drift signals. Reader is the recruiter-leader and finance partner; they decide what the variance means.\",\n  \"messages\": [\n    {\n      \"role\": \"user\",\n      \"content\": \"{{ JSON.stringify($json) }}\"\n    }\n  ]\n}",
        "options": {
          "response": { "response": { "responseFormat": "json" } },
          "timeout": 60000
        }
      },
      "id": "3a3a3a3a-0001-0000-0000-000000000006",
      "name": "Claude Compose Narrative",
      "type": "n8n-nodes-base.httpRequest",
      "typeVersion": 4.2,
      "position": [940, 400],
      "credentials": {
        "anthropicApi": {
          "id": "PLACEHOLDER_ANTHROPIC_CRED_ID",
          "name": "Anthropic API key"
        }
      },
      "notesInFlow": true,
      "notes": "LLM only narrates the deterministic reconciliation. The system prompt forbids inferring causes — that's the reader's job."
    },
    {
      "parameters": {
        "jsCode": "// Split the LLM response into per-team posts and the aggregate post.\nconst llmResp = $input.first().json;\nconst recon = $('Reconcile').item.json;\nconst text = llmResp.content?.[0]?.text || '';\n\n// Expect the LLM to return JSON with per_team and aggregate keys.\nlet parsed;\ntry {\n  parsed = JSON.parse(text.match(/\\{[\\s\\S]*\\}/)[0]);\n} catch (e) {\n  parsed = { aggregate: text, per_team: {} };\n}\n\nconst staleHeader = recon.plan_stale ? `\\n\\n:warning: Plan last updated ${recon.plan_age_days} days ago — variance may reflect plan staleness, not actual gap.` : '';\n\nconst posts = [];\n\n// Per-team posts to per-team channels.\nfor (const row of recon.rows) {\n  const teamSummary = parsed.per_team?.[row.team] || `${row.team} ${row.level}: plan ${row.plan}, hires ${row.hires}, open ${row.open_reqs}, variance ${row.variance}.`;\n  posts.push({\n    json: {\n      type: 'per_team',\n      channel: `#hiring-${row.team.toLowerCase().replace(/[^a-z0-9]+/g, '-')}`,\n      text: `*Headcount — ${recon.quarter} — week of ${new Date().toISOString().slice(0, 10)}*\\n${teamSummary}${staleHeader}`,\n    }\n  });\n}\n\n// Aggregate post to leadership channel.\nposts.push({\n  json: {\n    type: 'aggregate',\n    channel: '#recruiting-leadership',\n    text: `*${recon.quarter} headcount aggregate — week of ${new Date().toISOString().slice(0, 10)}*\\n\\nPlan: ${recon.totals.plan} · Hires: ${recon.totals.hires} · Open reqs: ${recon.totals.open_reqs} · Conversions: ${recon.totals.conversions} · Off-cycle: ${recon.totals.off_cycle}\\n\\n${parsed.aggregate || 'See per-team channels for breakdowns.'}\\n\\nAnomaly cells: ${recon.anomaly_rows.length}.${staleHeader}`,\n  }\n});\n\nreturn posts;"
      },
      "id": "3a3a3a3a-0001-0000-0000-000000000007",
      "name": "Split Posts",
      "type": "n8n-nodes-base.code",
      "typeVersion": 2,
      "position": [1180, 400]
    },
    {
      "parameters": {
        "method": "POST",
        "url": "https://slack.com/api/chat.postMessage",
        "authentication": "predefinedCredentialType",
        "nodeCredentialType": "slackApi",
        "sendHeaders": true,
        "headerParameters": {
          "parameters": [{ "name": "Content-Type", "value": "application/json; charset=utf-8" }]
        },
        "sendBody": true,
        "specifyBody": "json",
        "jsonBody": "={\n  \"channel\": \"{{ $json.channel }}\",\n  \"text\": \"{{ $json.text }}\",\n  \"unfurl_links\": false\n}",
        "options": {}
      },
      "id": "3a3a3a3a-0001-0000-0000-000000000008",
      "name": "Post to Slack",
      "type": "n8n-nodes-base.httpRequest",
      "typeVersion": 4.2,
      "position": [1400, 400],
      "credentials": {
        "slackApi": {
          "id": "PLACEHOLDER_SLACK_CRED_ID",
          "name": "Slack bot token (chat:write)"
        }
      }
    }
  ],
  "connections": {
    "Cron Monday 8am": {
      "main": [
        [
          { "node": "Fetch Plan", "type": "main", "index": 0 },
          { "node": "Fetch Open Reqs (Ashby)", "type": "main", "index": 0 },
          { "node": "Fetch Hires (HRIS)", "type": "main", "index": 0 }
        ]
      ]
    },
    "Fetch Plan": { "main": [[{ "node": "Reconcile", "type": "main", "index": 0 }]] },
    "Fetch Open Reqs (Ashby)": { "main": [[{ "node": "Reconcile", "type": "main", "index": 0 }]] },
    "Fetch Hires (HRIS)": { "main": [[{ "node": "Reconcile", "type": "main", "index": 0 }]] },
    "Reconcile": { "main": [[{ "node": "Claude Compose Narrative", "type": "main", "index": 0 }]] },
    "Claude Compose Narrative": { "main": [[{ "node": "Split Posts", "type": "main", "index": 0 }]] },
    "Split Posts": { "main": [[{ "node": "Post to Slack", "type": "main", "index": 0 }]] }
  },
  "settings": {
    "executionOrder": "v1",
    "timezone": "America/New_York",
    "saveExecutionProgress": true,
    "saveManualExecutions": true,
    "callerPolicy": "workflowsFromSameOwner"
  },
  "active": false,
  "versionId": "1"
}

# Team mapping for headcount plan reconciliation
#
# The reconciliation flow joins three sources (plan, ATS, HRIS) by team. Each
# source uses its own team labels. This file maps each source's labels to a
# canonical team identifier the flow uses for joining.
#
# Without this mapping, every team surfaces as an anomaly cell. With it,
# ongoing maintenance is low — only when teams are created, renamed, or
# merged.
#
# Save this file as /data/team_mapping.yml (or whatever path TEAM_MAPPING_PATH
# env var in n8n points to).

teams:
  Engineering:
    canonical: engineering
    aliases:
      plan: ["Engineering", "Eng"]
      ashby: ["Engineering", "Engineering Department"]
      hris: ["ENGINEERING", "Engineering"]

  Product:
    canonical: product
    aliases:
      plan: ["Product"]
      ashby: ["Product"]
      hris: ["Product Management", "PM"]

  Design:
    canonical: design
    aliases:
      plan: ["Design"]
      ashby: ["Product Design", "Design"]
      hris: ["Design"]

  Sales:
    canonical: sales
    aliases:
      plan: ["Sales", "Revenue"]
      ashby: ["Sales", "Revenue"]
      hris: ["Sales", "Account Executives"]

  Sales Development:
    canonical: sales-development
    aliases:
      plan: ["SDR", "Sales Development"]
      ashby: ["Sales Development", "BDR"]
      hris: ["Sales Development", "SDR"]

  Customer Success:
    canonical: customer-success
    aliases:
      plan: ["CS", "Customer Success"]
      ashby: ["Customer Success", "Customer Experience"]
      hris: ["Customer Success", "CS"]

  Marketing:
    canonical: marketing
    aliases:
      plan: ["Marketing"]
      ashby: ["Marketing"]
      hris: ["Marketing"]

  Recruiting:
    canonical: recruiting
    aliases:
      plan: ["Recruiting", "TA"]
      ashby: ["Talent Acquisition"]
      hris: ["People Ops - Talent", "TA"]

  People Ops:
    canonical: people-ops
    aliases:
      plan: ["People", "People Ops"]
      ashby: ["People Operations"]
      hris: ["People Ops", "Human Resources"]

  Finance:
    canonical: finance
    aliases:
      plan: ["Finance"]
      ashby: ["Finance & Accounting"]
      hris: ["Finance", "Accounting"]

  Legal:
    canonical: legal
    aliases:
      plan: ["Legal"]
      ashby: ["Legal"]
      hris: ["Legal Department"]

  IT:
    canonical: it
    aliases:
      plan: ["IT"]
      ashby: ["IT", "Information Technology"]
      hris: ["IT"]

# When you create a new team, add it here BEFORE the first req goes into Ashby.
# When you rename a team, ADD the new alias to the existing entry — don't delete
# the old one until the historical reqs and hires under the old name have been
# migrated.
# When you merge two teams, point both old `canonical` values to the merged
# team's canonical; keep the old ones around until the historical data has
# settled.