Gatekeeper Subworkflow

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": "Gatekeeper Subworkflow",
  "nodes": [
    {
      "parameters": {
        "workflowInputs": {
          "values": [
            {
              "name": "raw_text"
            },
            {
              "name": "sender_phone"
            },
            {
              "name": "context"
            }
          ]
        }
      },
      "id": "5b1a6b0e-8e0a-4b1a-9b0a-1a2b3c4d5e6f",
      "name": "Gatekeeper Trigger",
      "type": "n8n-nodes-base.executeWorkflowTrigger",
      "typeVersion": 1.1,
      "position": [
        -720,
        -400
      ]
    },
    {
      "parameters": {
        "jsCode": "// Canonical extraction merged from the three previously-duplicated Gatekeeper Code nodes.\nconst raw = (($json.raw_text || \"\").toString()).toLowerCase().trim();\n\n// Level can arrive either as an abbreviation the parent typed (jss1, ss 2, ...)\n// or already normalized to the full DB form by an upstream node (the text-command\n// path passes 'junior secondary 1' straight through) -- match either.\nconst abbrevLevelRegex = /(jss|js|sss|ss)\\s*([1-3])/i;\nconst fullLevelRegex = /(junior|senior)\\s+secondary\\s+([1-3])/i;\n\nlet normalizedLevel = \"unknown\";\nlet levelMatchStr = \"\";\n\nconst fullMatch = raw.match(fullLevelRegex);\nconst abbrevMatch = raw.match(abbrevLevelRegex);\n\nif (fullMatch) {\n    normalizedLevel = `${fullMatch[1].toLowerCase()} secondary ${fullMatch[2]}`;\n    levelMatchStr = fullMatch[0];\n} else if (abbrevMatch) {\n    const type = abbrevMatch[1].toLowerCase();\n    const num = abbrevMatch[2];\n    if (type.includes('j')) {\n        normalizedLevel = `junior secondary ${num}`;\n    } else if (type.includes('s')) {\n        normalizedLevel = `senior secondary ${num}`;\n    }\n    levelMatchStr = abbrevMatch[0];\n}\n\n// Union of noise words from all three original implementations (caption, form, command).\nconst noiseWords = /(payment|attached|receipt|of|fees|school|student|name|is|at|the|dear|bursar|ward|details)/gi;\n\nlet cleanedName = raw;\nif (levelMatchStr) {\n    cleanedName = cleanedName.replace(levelMatchStr, \"\");\n}\ncleanedName = cleanedName\n    .replace(noiseWords, \"\")\n    .replace(/[^\\w\\s]/g, \"\")\n    .trim();\n\nconst nameParts = cleanedName.split(/\\s+/).filter(part => part.length > 2);\nconst part1 = nameParts[0] || \"unknown\";\nconst part2 = nameParts[1] || part1;\n\nreturn {\n    student_name_raw: cleanedName,\n    name_part_1: part1,\n    name_part_2: part2,\n    level: normalizedLevel,\n    sender_phone: $json.sender_phone,\n    context: $json.context,\n    raw_text: raw\n};"
      },
      "id": "6c2b7c1f-9f1b-4c2b-ac1b-2b3c4d5e6f70",
      "name": "Extract Student Info",
      "type": "n8n-nodes-base.code",
      "typeVersion": 2,
      "position": [
        -496,
        -400
      ]
    },
    {
      "parameters": {
        "operation": "executeQuery",
        "query": "SELECT\n    sr.id AS student_id,\n    CONCAT(sr.last_name, ' ', sr.first_name, ' ', COALESCE(sr.other_name, '')) AS full_name,\n    al.level_name\nFROM tb_student_registrations sr\nJOIN tb_academic_levels al ON sr.level_id = al.id\nWHERE al.level_name = '{{ $json.level }}'\n  AND sr.admission_status = 'Active'\n  -- Search: full name must contain both extracted name parts\n  AND (\n      LOWER(CONCAT_WS(' ', sr.last_name, sr.first_name, COALESCE(sr.other_name, '')))\n      LIKE CONCAT('%', LOWER('{{ $json.name_part_1 }}'), '%')\n  )\n  AND (\n      LOWER(CONCAT_WS(' ', sr.last_name, sr.first_name, COALESCE(sr.other_name, '')))\n      LIKE CONCAT('%', LOWER('{{ $json.name_part_2 }}'), '%')\n  )\n  -- Security Gate: sender's phone (last 10 digits) must belong to an authorized party.\n  -- NOTE: the year tutor's phone is intentionally included here -- tutors are treated\n  -- as authorized for BOTH receipt submissions and text commands (balance/invoice/\n  -- status/opt-out). One of the three original duplicated queries had dropped this\n  -- subquery inconsistently; it is restored here for all callers.\n  AND (\n      RIGHT('{{ $json.sender_phone }}', 10) IN (\n          RIGHT(sr.father_phone_no_1, 10), RIGHT(sr.father_phone_no_2, 10),\n          RIGHT(sr.mother_phone_no_1, 10), RIGHT(sr.mother_phone_no_2, 10),\n          RIGHT(sr.guardian_phone_no_1, 10), RIGHT(sr.guardian_phone_no_2, 10),\n          RIGHT(sr.sponsor_phone_no_1, 10), RIGHT(sr.sponsor_phone_no_2, 10),\n          (\n              SELECT RIGHT(fr.faculty_phone_no_1, 10)\n              FROM tb_faculty_registrations fr\n              WHERE fr.id = al.year_tutor_id\n          ),\n          '9162583300'\n      )\n  );",
        "options": {
          "detailedOutput": false
        }
      },
      "id": "7d3c8d20-a01c-4d3c-bd2c-3c4d5e6f7081",
      "name": "Gatekeeper Query",
      "type": "n8n-nodes-base.mySql",
      "typeVersion": 2.5,
      "position": [
        -272,
        -400
      ],
      "credentials": {
        "mySql": {
          "name": "<your credential>"
        }
      }
    },
    {
      "parameters": {
        "jsCode": "// Classify the (now un-LIMITed) result set.\nconst rows = $input.all().map(item => item.json).filter(r => r && r.student_id);\nconst context = $('Extract Student Info').item.json.context;\n\nif (rows.length === 0) {\n    return {\n        status: \"not_found\",\n        student_id: null,\n        full_name: null,\n        level_name: null,\n        candidates: [],\n        context\n    };\n}\n\nif (rows.length === 1) {\n    return {\n        status: \"found\",\n        student_id: rows[0].student_id,\n        full_name: rows[0].full_name,\n        level_name: rows[0].level_name,\n        candidates: [],\n        context\n    };\n}\n\nreturn {\n    status: \"ambiguous\",\n    student_id: null,\n    full_name: null,\n    level_name: null,\n    candidates: rows.map(r => ({\n        student_id: r.student_id,\n        full_name: r.full_name,\n        level_name: r.level_name\n    })),\n    context\n};"
      },
      "id": "8e4d9e31-b12d-4e4d-ce3d-4d5e6f708192",
      "name": "Classify Match",
      "type": "n8n-nodes-base.code",
      "typeVersion": 2,
      "position": [
        -48,
        -400
      ]
    }
  ],
  "connections": {
    "Gatekeeper Trigger": {
      "main": [
        [
          {
            "node": "Extract Student Info",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "Extract Student Info": {
      "main": [
        [
          {
            "node": "Gatekeeper Query",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "Gatekeeper Query": {
      "main": [
        [
          {
            "node": "Classify Match",
            "type": "main",
            "index": 0
          }
        ]
      ]
    }
  },
  "active": false,
  "settings": {
    "executionOrder": "v1"
  },
  "versionId": "00000000-0000-0000-0000-000000000001",
  "meta": {
    "templateCredsSetupCompleted": true
  },
  "id": "GatekeeperSubworkflow0001",
  "tags": []
}

Credentials you'll need

Each integration node will prompt for credentials when you import. We strip credential IDs before publishing — you'll add your own.

• mySql

About this workflow

Gatekeeper Subworkflow. Uses executeWorkflowTrigger, mySql. Event-driven trigger; 4 nodes.

Source: https://github.com/AceCollegeInternational/ace-bursar-bot/blob/main/Gatekeeper_Subworkflow.json — original creator credit. Request a take-down →

More Data & Sheets workflows → · Browse all categories →

Related workflows

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