GSTIN Vendor Master Validation with Kestra and DuckDB

id: gstin-vendor-master-validation
namespace: company.team
description: |
  Validate every GSTIN in a vendor master before it reaches invoices, e-way bills
  or input tax credit claims: structure, state code, embedded PAN, check digit,
  duplicates, and whether the registration state matches the billing address.

inputs:
  - id: vendor_file
    type: FILE
    required: false
    description: CSV with the columns vendor_id, vendor_name, gstin and
      billing_state (state name, e.g. "Tamil Nadu"). Leave empty to run against
      the built-in sample vendor master.
  - id: notify_slack
    type: BOOL
    defaults: false
    description: Post the findings to Slack when at least one GSTIN is invalid.
      Requires the SLACK_WEBHOOK_URL secret.

triggers:
  - id: weekly_vendor_audit
    type: io.kestra.plugin.core.trigger.Schedule
    description: Audit the vendor master every Monday at 07:00 IST, before the
      week's purchase invoices are booked. Shipped disabled; enable it once the
      flow reads your real vendor export instead of the sample.
    cron: "0 7 * * MON"
    timezone: Asia/Kolkata
    disabled: true

tasks:
  - id: sample_vendor_master
    type: io.kestra.plugin.core.storage.Write
    description: A small vendor master with one example of every problem the audit
      catches. The GSTINs are synthetic (except the GSTN format example
      27AAPFU0939F1ZV) and only used when no vendor_file is provided.
    extension: .csv
    content: |
      vendor_id,vendor_name,gstin,billing_state
      V001,Sahyadri Packaging LLP,27AAPFU0939F1ZV,Maharashtra
      V002,Kaveri Agro Foods Pvt Ltd,29AAKCK4821M1Z0,Karnataka
      V003,Deccan Steel Traders,36AAFFD7319Q1ZW,Telangana
      V004,Ganga Textiles Pvt Ltd,09AABCG2210P1ZM,Bihar
      V005,Coromandel Logistics Pvt Ltd,33AAGCC5512R1ZM,Tamil Nadu
      V006,Malabar Spice Exports, 32aahfm6603k1zc ,Kerala
      V007,Thar Solar Systems Pvt Ltd,08AACCT9142N1Z,Rajasthan
      V008,Kaveri Agro Foods Private Limited,29AAKCK4821M1Z0,Karnataka
      V009,Kaveri Agro Foods Pvt Ltd (Chennai),33AAKCK4821M1ZB,Tamil Nadu
      V010,Brahmaputra Tea Estates,40AAACB7781L1ZK,Assam
      V011,Nilgiri Handicrafts,33AABXN4410D1Z0,Tamil Nadu
      V012,Konkan Fisheries,30AAJFK2093H1XN,Goa
      V013,Shree Ganesh Kirana Supplies,,Uttar Pradesh
      V014,Ladakh Apricot Collective,38AAEAL5521C1ZW,Ladakh

  - id: validate_gstins
    type: io.kestra.plugin.jdbc.duckdb.Queries
    description: Normalise each GSTIN and run every check in DuckDB, without calling
      any external service. The check digit uses the Luhn mod 36 algorithm over
      the first 14 characters. The full per-vendor report is exported to CSV and
      the last query returns the summary.
    inputFiles:
      vendors.csv: "{{ inputs.vendor_file ?? outputs.sample_vendor_master.uri }}"
    outputFiles:
      - gstin_report.csv
    fetchType: FETCH_ONE
    sql: |
      CREATE TABLE gst_states AS
      SELECT * FROM (VALUES
        ('01', 'Jammu and Kashmir'), ('02', 'Himachal Pradesh'), ('03', 'Punjab'),
        ('04', 'Chandigarh'), ('05', 'Uttarakhand'), ('06', 'Haryana'), ('07', 'Delhi'),
        ('08', 'Rajasthan'), ('09', 'Uttar Pradesh'), ('10', 'Bihar'), ('11', 'Sikkim'),
        ('12', 'Arunachal Pradesh'), ('13', 'Nagaland'), ('14', 'Manipur'), ('15', 'Mizoram'),
        ('16', 'Tripura'), ('17', 'Meghalaya'), ('18', 'Assam'), ('19', 'West Bengal'),
        ('20', 'Jharkhand'), ('21', 'Odisha'), ('22', 'Chhattisgarh'), ('23', 'Madhya Pradesh'),
        ('24', 'Gujarat'), ('25', 'Dadra and Nagar Haveli and Daman and Diu'),
        ('26', 'Dadra and Nagar Haveli and Daman and Diu'), ('27', 'Maharashtra'),
        ('28', 'Andhra Pradesh'), ('29', 'Karnataka'), ('30', 'Goa'), ('31', 'Lakshadweep'),
        ('32', 'Kerala'), ('33', 'Tamil Nadu'), ('34', 'Puducherry'),
        ('35', 'Andaman and Nicobar Islands'), ('36', 'Telangana'), ('37', 'Andhra Pradesh'),
        ('38', 'Ladakh'), ('97', 'Other Territory'), ('99', 'Centre Jurisdiction')
      ) AS t(code, state_name);

      CREATE TABLE state_aliases AS
      SELECT DISTINCT lower(state_name) AS alias, state_name FROM gst_states
      UNION ALL
      SELECT * FROM (VALUES
        ('orissa', 'Odisha'), ('chattisgarh', 'Chhattisgarh'), ('pondicherry', 'Puducherry'),
        ('new delhi', 'Delhi'), ('daman and diu', 'Dadra and Nagar Haveli and Daman and Diu'),
        ('dadra and nagar haveli', 'Dadra and Nagar Haveli and Daman and Diu'),
        ('andaman and nicobar', 'Andaman and Nicobar Islands'), ('j&k', 'Jammu and Kashmir')
      ) AS t(alias, state_name);

      CREATE TABLE vendors AS
      SELECT
        vendor_id,
        vendor_name,
        coalesce(gstin, '') AS gstin_raw,
        upper(regexp_replace(coalesce(gstin, ''), '[\s-]', '', 'g')) AS gstin,
        trim(coalesce(billing_state, '')) AS billing_state
      FROM read_csv('{{ workingDir }}/vendors.csv', header = true, all_varchar = true);

      CREATE TABLE parsed AS
      SELECT
        v.*,
        length(v.gstin) AS gstin_length,
        regexp_full_match(v.gstin, '[0-9]{2}[A-Z]{5}[0-9]{4}[A-Z][0-9A-Z]{3}') AS pattern_ok,
        substr(v.gstin, 1, 2) AS state_code,
        substr(v.gstin, 3, 10) AS pan,
        substr(v.gstin, 6, 1) AS pan_holder_type,
        s.state_name AS gstin_state,
        b.state_name AS billing_state_canonical,
        count(*) OVER (PARTITION BY v.gstin) AS same_gstin_count,
        string_agg(v.vendor_id, ', ') OVER (PARTITION BY v.gstin) AS same_gstin_vendors,
        count(DISTINCT substr(v.gstin, 1, 2)) OVER (PARTITION BY substr(v.gstin, 3, 10)) AS states_for_pan
      FROM vendors v
      LEFT JOIN gst_states s ON s.code = substr(v.gstin, 1, 2)
      LEFT JOIN state_aliases b ON b.alias = lower(v.billing_state);

      CREATE TABLE checked AS
      SELECT
        *,
        CASE WHEN pattern_ok THEN substr(
          '0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZ',
          (36 - list_sum(list_transform(
            list_transform(range(14), lambda i:
              (instr('0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZ', substr(gstin, i + 1, 1)) - 1) * (1 + i % 2)),
            lambda p: p // 36 + p % 36))::INTEGER % 36) % 36 + 1,
          1) END AS expected_check_digit
      FROM parsed;

      CREATE TABLE report AS
      SELECT
        vendor_id,
        vendor_name,
        gstin_raw,
        gstin,
        CASE
          WHEN len(errors) > 0 THEN 'ERROR'
          WHEN len(warnings) > 0 THEN 'WARNING'
          ELSE 'OK'
        END AS status,
        array_to_string(list_concat(errors, warnings), '; ') AS issues,
        array_to_string(notes, '; ') AS notes,
        gstin_state,
        billing_state,
        CASE WHEN pattern_ok THEN pan END AS pan
      FROM (
        SELECT
          *,
          list_filter([
            CASE WHEN gstin <> '' AND gstin_length <> 15 THEN 'GSTIN must have 15 characters, found ' || gstin_length END,
            CASE WHEN gstin_length = 15 AND NOT pattern_ok THEN 'GSTIN does not follow the pattern 2 digits + PAN + entity number + Z + check digit' END,
            CASE WHEN pattern_ok AND gstin_state IS NULL THEN 'Unknown state code ' || state_code END,
            CASE WHEN pattern_ok AND pan_holder_type NOT IN ('A', 'B', 'C', 'F', 'G', 'H', 'J', 'K', 'L', 'P', 'T') THEN 'Invalid PAN holder type ' || pan_holder_type || ' (4th character of the PAN)' END,
            CASE WHEN pattern_ok AND substr(gstin, 13, 1) = '0' THEN 'Entity number (13th character) cannot be 0' END,
            CASE WHEN pattern_ok AND substr(gstin, 14, 1) <> 'Z' THEN '14th character must be Z, found ' || substr(gstin, 14, 1) END,
            CASE WHEN pattern_ok AND substr(gstin, 15, 1) <> expected_check_digit THEN 'Check digit is ' || substr(gstin, 15, 1) || ' but should be ' || expected_check_digit || ' (typo in the GSTIN?)' END,
            CASE WHEN gstin <> '' AND same_gstin_count > 1 THEN 'Same GSTIN used by vendors ' || same_gstin_vendors || ' (duplicate vendor record?)' END
          ], lambda x: x IS NOT NULL) AS errors,
          list_filter([
            CASE WHEN gstin = '' THEN 'No GSTIN on file: confirm the vendor is unregistered before claiming input tax credit' END,
            CASE WHEN gstin_state IS NOT NULL AND billing_state_canonical IS NOT NULL AND gstin_state <> billing_state_canonical
              THEN 'GSTIN is registered in ' || gstin_state || ' but the billing state is ' || billing_state || ' (IGST vs CGST/SGST may be wrong)' END,
            CASE WHEN billing_state <> '' AND billing_state_canonical IS NULL THEN 'Billing state "' || billing_state || '" is not a recognised state or union territory name' END
          ], lambda x: x IS NOT NULL) AS warnings,
          list_filter([
            CASE WHEN gstin <> '' AND gstin_raw <> gstin THEN 'Normalised from "' || gstin_raw || '"' END,
            CASE WHEN pattern_ok AND states_for_pan > 1 THEN 'PAN ' || pan || ' is registered in ' || states_for_pan || ' states (expected for multi-state businesses)' END
          ], lambda x: x IS NOT NULL) AS notes
        FROM checked
      )
      ORDER BY CASE status WHEN 'ERROR' THEN 0 WHEN 'WARNING' THEN 1 ELSE 2 END, vendor_id;

      COPY report TO '{{ outputFiles["gstin_report.csv"] }}' (HEADER, DELIMITER ',');

      SELECT
        count(*) AS total_vendors,
        count(*) FILTER (WHERE status = 'OK') AS ok_count,
        count(*) FILTER (WHERE status = 'WARNING') AS warning_count,
        count(*) FILTER (WHERE status = 'ERROR') AS error_count,
        coalesce(string_agg(vendor_id || ' ' || vendor_name || ': ' || issues, chr(10)) FILTER (WHERE status = 'ERROR'), '') AS error_details,
        coalesce(string_agg(vendor_id || ' ' || vendor_name || ': ' || issues, chr(10)) FILTER (WHERE status = 'WARNING'), '') AS warning_details
      FROM report;

  - id: route_findings
    type: io.kestra.plugin.core.flow.If
    description: Branch on the audit result. Invalid GSTINs block clean invoicing
      and input tax credit, so they are logged as errors and optionally sent to
      Slack; a clean run is logged so the execution history doubles as an audit
      trail.
    condition: "{{ outputs.validate_gstins.outputs[0].row.error_count > 0 }}"
    then:
      - id: log_errors
        type: io.kestra.plugin.core.log.Log
        level: WARN
        description: Write every failing vendor and the exact reason to the execution logs.
        message: |
          GSTIN audit: {{ outputs.validate_gstins.outputs[0].row.error_count }} invalid, {{ outputs.validate_gstins.outputs[0].row.warning_count }} warning(s), {{ outputs.validate_gstins.outputs[0].row.ok_count }} OK out of {{ outputs.validate_gstins.outputs[0].row.total_vendors }} vendors.
          Errors:
          {{ outputs.validate_gstins.outputs[0].row.error_details }}
          Warnings:
          {{ outputs.validate_gstins.outputs[0].row.warning_details }}

      - id: slack_enabled
        type: io.kestra.plugin.core.flow.If
        description: Only call Slack when the notify_slack input is true, so the flow
          runs out of the box without any secret.
        condition: "{{ inputs.notify_slack }}"
        then:
          - id: alert_finance_team
            type: io.kestra.plugin.slack.notifications.SlackIncomingWebhook
            description: Tell the accounts payable team which vendors to fix before the next
              payment run.
            url: "{{ secret('SLACK_WEBHOOK_URL') }}"
            messageText: |
              :warning: GSTIN audit found {{ outputs.validate_gstins.outputs[0].row.error_count }} invalid GSTIN(s) and {{ outputs.validate_gstins.outputs[0].row.warning_count }} warning(s) across {{ outputs.validate_gstins.outputs[0].row.total_vendors }} vendors.
              {{ outputs.validate_gstins.outputs[0].row.error_details }}
              Full report: execution {{ execution.id }} in flow {{ flow.namespace }}.{{ flow.id }}
    else:
      - id: log_clean
        type: io.kestra.plugin.core.log.Log
        description: Record a clean audit so the execution history shows when the vendor
          master was last verified.
        message: "GSTIN audit passed: {{
          outputs.validate_gstins.outputs[0].row.total_vendors }} vendors
          checked, {{ outputs.validate_gstins.outputs[0].row.warning_count }}
          warning(s), no invalid GSTINs."

errors:
  - id: log_audit_failure
    type: io.kestra.plugin.core.log.Log
    level: ERROR
    description: Make a failed audit loud. If the file is missing or malformed, no
      vendor was checked, and a silent failure looks like a clean vendor master.
    message: "GSTIN audit FAILED in {{ flow.namespace }}.{{ flow.id }} (execution {{
      execution.id }}). No vendors were validated. Check that vendor_file is a
      CSV with the columns vendor_id, vendor_name, gstin and billing_state."

outputs:
  - id: gstin_report
    type: FILE
    description: Per-vendor CSV report with status (ERROR, WARNING or OK), the
      reasons, the normalised GSTIN, the registration state and the PAN.
    value: "{{ outputs.validate_gstins.outputFiles['gstin_report.csv'] }}"
  - id: audit_summary
    type: JSON
    description: 'Counts and details, e.g. {"total_vendors": 14, "ok_count": 5,
      "warning_count": 2, "error_count": 7, ...}.'
    value: "{{ outputs.validate_gstins.outputs[0].row | toJson }}"

A single mistyped character in a vendor's GSTIN is enough to put an invoice on the wrong return, to show up as a mismatch in GSTR-2B, and to put the input tax credit on that purchase at risk. This blueprint audits the whole vendor master on a schedule and tells you exactly which records to fix and why, before the next payment run. Every check runs locally in DuckDB: no GST portal credentials, no paid API, and no vendor data leaving your Kestra instance.

How it works

sample_vendor_master (io.kestra.plugin.core.storage.Write) writes a 14-row vendor master that contains one example of every problem. It is used only when the vendor_file input is empty, so the flow runs out of the box.
validate_gstins (io.kestra.plugin.jdbc.duckdb.Queries) loads the CSV through inputFiles, normalises each GSTIN (trims spaces, removes dashes, upper-cases it) and runs these checks:
- length is 15 characters, and the structure is 2 digits + 10-character PAN + entity number + Z + check digit;
- the state code is one of the GST state and union territory codes (01 to 38, 97, 99);
- the PAN holder type (4th character of the PAN) is valid;
- the 13th character is not 0 and the 14th character is Z;
- the check digit matches the Luhn mod 36 checksum over the first 14 characters;
- the same GSTIN is not attached to two vendor records;
- the registration state matches the vendor's billing state (warning), and vendors without a GSTIN are flagged (warning).
The full per-vendor result is written to gstin_report.csv with outputFiles, and the final SELECT returns the summary row through fetchType: FETCH_ONE, available as outputs.validate_gstins.outputs[0].row.
route_findings (io.kestra.plugin.core.flow.If) logs every error and warning when at least one GSTIN is invalid, and a nested If posts the same findings to Slack when notify_slack is true. A clean audit is logged as well.
The errors block logs a failed audit loudly, because an audit that silently does not run looks the same as a clean vendor master.
The weekly_vendor_audit Schedule trigger (Monday 07:00 IST, shipped disabled) runs the audit before the week's purchase invoices are booked.

What you get

outputs.gstin_report: CSV with vendor_id, vendor_name, gstin_raw, gstin, status (ERROR, WARNING or OK), issues, notes, gstin_state, billing_state and pan, sorted with errors first.
outputs.audit_summary: JSON with total_vendors, ok_count, warning_count, error_count, error_details and warning_details.
Plain-language reasons such as "Check digit is M but should be F (typo in the GSTIN?)" or "GSTIN is registered in Uttar Pradesh but the billing state is Bihar (IGST vs CGST/SGST may be wrong)".
With the sample data: 7 errors (a bad check digit, a 14-character GSTIN, the same GSTIN on two vendor records, unknown state code 40, an invalid PAN holder type, a 14th character that is not Z), 2 warnings (state mismatch, no GSTIN) and 5 valid vendors, including a lower-case GSTIN with spaces that is normalised, a Ladakh (38) registration, and the same PAN registered in two states.

Who it's for

Accounts payable and finance operations teams in India who maintain vendor masters in an ERP, Tally, or a spreadsheet export.
Data teams that load vendor data into a warehouse and want a quality gate before it feeds invoicing or GST reconciliation.
Anyone onboarding vendors in bulk who wants mistakes caught on day one instead of in the GSTR-2B mismatch report.

Why orchestrate this with Kestra

A one-off script can validate a CSV once. Kestra runs the same audit every week, keeps the report from every run in the execution history, branches on the result, and can alert the people who fix vendor records. Because the input is just a file, the same flow can be triggered when a new vendor export lands in S3, SFTP or Google Drive, or called as a subflow from an onboarding process.

Prerequisites

Nothing for the demo: the sample vendor master is built in.
For your own data: a CSV export of the vendor master with the columns vendor_id, vendor_name, gstin and billing_state (the state or union territory name, for example Tamil Nadu).

Inputs

vendor_file (FILE, optional): your vendor master CSV. When empty, the built-in sample is used.
notify_slack (BOOL, default false): post the findings to Slack when at least one GSTIN is invalid.

Secrets

SLACK_WEBHOOK_URL: Slack incoming webhook URL. Only needed when notify_slack is true.

Quick start

Save the flow and run it with the default inputs.
Open the logs of log_errors and download gstin_report from the Outputs tab to see the result for the sample vendors.
Run it again and upload your own vendor export as vendor_file.
Optional: add the SLACK_WEBHOOK_URL secret and set notify_slack to true.
Replace the input with your real source and set disabled: false on weekly_vendor_audit.

Common pitfalls

These checks prove a GSTIN is well formed, not that it is active. A cancelled or suspended registration still passes, so pair this audit with a periodic status check on the GST portal (or your GSP's API) for high-value vendors.
Pass the billing state as a name, not a code. Common older names (Orissa, Pondicherry, Chattisgarh) are mapped automatically. Andhra Pradesh registrations can carry either code 28 or 37, and both are accepted.
Spreadsheets sometimes strip leading zeros, turning 09ABCDE... into 9ABCDE.... The length check catches it; keep the column as text when exporting.

How to extend

Read the vendor master straight from your ERP or warehouse (Postgres, MySQL, BigQuery, Snowflake) instead of a file, and write report back to a table.
Trigger the audit when a new export lands, with the S3, SFTP or Google Drive triggers.
Wrap the alert in a ForEach over the failing vendors to open one ticket per vendor in Jira or ServiceNow.
Add an active-status check for new vendors through your GST Suvidha Provider's API before they are approved.

Validate GSTINs in Your Vendor Master Before They Break Invoices and Input Tax Credit

How it works

What you get

Who it's for

Why orchestrate this with Kestra

Prerequisites

Inputs

Secrets

Quick start

Common pitfalls

How to extend

Links

Tasks

Orchestrate with Kestra

Share this Blueprint

Create a Blueprint

New to Kestra?

Validate GSTINs in Your Vendor Master Before They Break Invoices and Input Tax Credit

How it works

What you get

Who it's for

Why orchestrate this with Kestra

Prerequisites

Inputs

Secrets

Quick start

Common pitfalls

How to extend

Links

Tasks

Orchestrate with Kestra

Share this Blueprint

Create a Blueprint

More Business Blueprints

Autonomous Deep Research and Competitor Market Intelligence Agent

Generate AI Blog Posts in Your Brand Voice and Publish to WordPress as Draft

Convert Natural Language Prompts to Structured Todoist Tasks with Deepseek AI

AI Contract Clause Risk Analyzer and Legal Sign-Off Gate

AI-Powered Inventory Demand Forecasting, Auto-Generate Purchase Orders with OpenAI

AI Customer Call Compliance and Regulatory Disclosure Auditor

More Data Blueprints

Schedule a Daily Sifflet Observability Sweep

Daily Snowflake Digest as a Slack Canvas

Gate a Dashboard Publish on a Sifflet Rule

Incremental Postgres to S3 Parquet replication with Sling and Kestra watermarks

Recheck a Sifflet Rule After an Incident Fix

Run a Sifflet Rule After a Warehouse Load

New to Kestra?