id: gstr2b-purchase-itc-reconciliation
namespace: company.team
description: |
Reconcile the purchase register with GSTR-2B every month before claiming
input tax credit: match invoices even when the number is written differently,
and flag invoices missing from GSTR-2B, value and tax head mismatches, ITC
marked not available, duplicates and invoices you never booked.
inputs:
- id: purchase_register
type: FILE
required: false
description: CSV of the month's purchase invoices from your books with the
columns entry_id, supplier_gstin, supplier_name, invoice_no, invoice_date
(YYYY-MM-DD), taxable_value, igst, cgst and sgst. Leave empty to use the
built-in sample.
- id: gstr2b
type: FILE
required: false
description: CSV of the GSTR-2B B2B invoices downloaded from the GST portal with
the columns supplier_gstin, trade_name, invoice_no, invoice_date
(YYYY-MM-DD), taxable_value, igst, cgst, sgst and itc_available (Y or N).
Leave empty to use the built-in sample.
- id: tolerance
type: FLOAT
defaults: 1
description: Difference in rupees between books and GSTR-2B that is still
treated as a match, to absorb rounding.
- id: notify_slack
type: BOOL
defaults: false
description: Post the findings to Slack when input tax credit is at risk.
Requires the SLACK_WEBHOOK_URL secret.
triggers:
- id: monthly_after_2b
type: io.kestra.plugin.core.trigger.Schedule
description: Run on the 15th of every month at 09:00 IST, the day after GSTR-2B
is generated and before GSTR-3B is filed. Shipped disabled; enable it once
the flow reads your real purchase register and GSTR-2B export instead of
the samples.
cron: "0 9 15 * *"
timezone: Asia/Kolkata
disabled: true
tasks:
- id: sample_purchase_register
type: io.kestra.plugin.core.storage.Write
description: One month of purchase invoices as booked in the accounts, with one
example of every problem the reconciliation catches. GSTINs and invoice
numbers are synthetic. Only used when purchase_register is empty.
extension: .csv
content: |
entry_id,supplier_gstin,supplier_name,invoice_no,invoice_date,taxable_value,igst,cgst,sgst
PR01,27AAPFU0939F1ZV,Sahyadri Packaging LLP,SP/2425/0118,2026-09-03,50000.00,9000.00,0,0
PR02,29AAKCK4821M1ZX,Kaveri Agro Foods Pvt Ltd,KAF-0042,2026-09-05,20000.00,0,1800.00,1800.00
PR03,33AAGCC5512R1ZM,Coromandel Logistics Pvt Ltd,CL/9981,2026-09-08,120000.00,21600.00,0,0
PR04,29AABCD1234E1ZQ,Deccan Office Supplies,DOS/556,2026-09-10,8000.00,0,720.00,720.00
PR05,07AAACN7781L1ZK,Northline Software Pvt Ltd,NS-2026-077,2026-09-12,75000.00,13500.00,0,0
PR06,29AAFCB9087P1ZT,Bengaluru Print House,BPH/1203,2026-09-15,15000.00,0,1350.00,1350.00
PR07,27AAPFU0939F1ZV,Sahyadri Packaging LLP,SP/2425/0131,2026-09-20,30000.00,5400.00,0,0
PR08,29AAKCK4821M1ZX,Kaveri Agro Foods Pvt Ltd,KAF-0042,2026-09-05,20000.00,0,1800.00,1800.00
PR09,33AAGCC5512R1ZM,Coromandel Logistics Pvt Ltd,CL/9990,2026-09-25,40000.00,7200.00,0,0
PR10,29AABCD1234E1ZQ,Deccan Office Supplies,DOS/561,2026-09-26,12000.00,0,1080.00,1080.00
- id: sample_gstr2b
type: io.kestra.plugin.core.storage.Write
description: The matching GSTR-2B B2B section as downloaded from the GST portal.
Only used when gstr2b is empty.
extension: .csv
content: |
supplier_gstin,trade_name,invoice_no,invoice_date,taxable_value,igst,cgst,sgst,itc_available
27AAPFU0939F1ZV,SAHYADRI PACKAGING LLP,SP/2425/0118,2026-09-03,50000.00,9000.00,0,0,Y
29AAKCK4821M1ZX,KAVERI AGRO FOODS PVT LTD,KAF/42,2026-09-05,20000.00,0,1800.00,1800.00,Y
33AAGCC5512R1ZM,COROMANDEL LOGISTICS PVT LTD,CL/9981,2026-09-08,102000.00,18360.00,0,0,Y
07AAACN7781L1ZK,NORTHLINE SOFTWARE PVT LTD,NS-2026-077,2026-09-12,75000.00,13500.00,0,0,N
29AAFCB9087P1ZT,BENGALURU PRINT HOUSE,BPH/1203,2026-09-15,15000.00,2700.00,0,0,Y
27AAPFU0939F1ZV,SAHYADRI PACKAGING LLP,SP/2425/0131,2026-09-21,30000.00,5400.00,0,0,Y
33AAGCC5512R1ZM,COROMANDEL LOGISTICS PVT LTD,CL/9990,2026-09-25,40000.00,7200.00,0,0,Y
29AABCD1234E1ZQ,DECCAN OFFICE SUPPLIES,DOS/561,2026-09-26,12000.00,0,1080.40,1080.40,Y
29AAFCB9087P1ZT,BENGALURU PRINT HOUSE,BPH/1188,2026-09-02,9000.00,0,810.00,810.00,Y
29AAJCM4455K1ZB,MYSORE SILK TRADERS,MST/77,2026-09-18,60000.00,0,5400.00,5400.00,Y
- id: reconcile
type: io.kestra.plugin.jdbc.duckdb.Queries
description: Normalise GSTINs and invoice numbers, match books to GSTR-2B and
classify every difference in DuckDB, without calling the GST portal or any
other service. The per-invoice report is exported to CSV and the last
query returns the summary.
inputFiles:
books.csv: "{{ inputs.purchase_register ?? outputs.sample_purchase_register.uri }}"
gstr2b.csv: "{{ inputs.gstr2b ?? outputs.sample_gstr2b.uri }}"
outputFiles:
- itc_reconciliation.csv
fetchType: FETCH_ONE
sql: |
-- Invoice key: upper case, separators removed, leading zeros dropped from each number,
-- so KAF-0042, KAF/42 and kaf 042 match.
CREATE MACRO invoice_key(x) AS replace(
regexp_replace(regexp_replace(upper(trim(coalesce(x, ''))), '[^A-Z0-9]+', ' ', 'g'), '\b0+([0-9])', '\1', 'g'),
' ', '');
CREATE MACRO amount(x) AS CAST(coalesce(nullif(trim(x), ''), '0') AS DECIMAL(15, 2));
CREATE TABLE books AS
SELECT
*,
row_number() OVER (PARTITION BY gstin, inv_key ORDER BY line) AS repeat_no,
first_value(entry_id) OVER (PARTITION BY gstin, inv_key ORDER BY line) AS first_entry
FROM (
SELECT
row_number() OVER () AS line,
trim(entry_id) AS entry_id,
upper(regexp_replace(coalesce(supplier_gstin, ''), '\s', '', 'g')) AS gstin,
trim(supplier_name) AS supplier_name,
trim(invoice_no) AS invoice_no,
invoice_key(invoice_no) AS inv_key,
CAST(invoice_date AS DATE) AS invoice_date,
amount(taxable_value) AS taxable_value,
amount(igst) AS igst,
amount(cgst) + amount(sgst) AS cgst_sgst
FROM read_csv('{{ workingDir }}/books.csv', header = true, all_varchar = true)
);
CREATE TABLE twob AS
SELECT
upper(regexp_replace(coalesce(supplier_gstin, ''), '\s', '', 'g')) AS gstin,
trim(trade_name) AS trade_name,
trim(invoice_no) AS invoice_no,
invoice_key(invoice_no) AS inv_key,
CAST(invoice_date AS DATE) AS invoice_date,
amount(taxable_value) AS taxable_value,
amount(igst) AS igst,
amount(cgst) + amount(sgst) AS cgst_sgst,
upper(trim(coalesce(itc_available, 'Y'))) AS itc_available
FROM read_csv('{{ workingDir }}/gstr2b.csv', header = true, all_varchar = true);
CREATE TABLE matched AS
SELECT
b.entry_id,
coalesce(b.gstin, t.gstin) AS supplier_gstin,
coalesce(b.supplier_name, t.trade_name) AS supplier_name,
b.invoice_no AS invoice_no_books,
t.invoice_no AS invoice_no_2b,
b.invoice_date AS date_books,
t.invoice_date AS date_2b,
b.taxable_value AS taxable_books,
t.taxable_value AS taxable_2b,
b.igst + b.cgst_sgst AS tax_books,
t.igst + t.cgst_sgst AS tax_2b,
b.igst AS igst_books,
t.igst AS igst_2b,
b.cgst_sgst AS cgst_sgst_books,
t.cgst_sgst AS cgst_sgst_2b,
t.itc_available
FROM (SELECT * FROM books WHERE repeat_no = 1) b
FULL OUTER JOIN twob t ON t.gstin = b.gstin AND t.inv_key = b.inv_key;
CREATE TABLE report AS
SELECT
CASE WHEN len(errors) > 0 THEN 'ERROR' WHEN len(warnings) > 0 THEN 'WARNING' ELSE 'OK' END AS status,
entry_id,
supplier_gstin,
supplier_name,
invoice_no_books,
invoice_no_2b,
taxable_books,
taxable_2b,
tax_books,
tax_2b,
array_to_string(list_concat(errors, warnings), ' | ') AS issues
FROM (
SELECT
*,
list_filter([
CASE WHEN invoice_no_2b IS NULL THEN 'Not in GSTR-2B: ITC of Rs ' || tax_books || ' is at risk until the supplier reports it in GSTR-1' END,
CASE WHEN itc_available = 'N' AND entry_id IS NOT NULL THEN 'GSTR-2B marks ITC as not available for this invoice' END,
CASE WHEN entry_id IS NOT NULL AND invoice_no_2b IS NOT NULL AND abs(taxable_books - taxable_2b) > {{ inputs.tolerance }}
THEN 'Taxable value is Rs ' || taxable_books || ' in books but Rs ' || taxable_2b || ' in GSTR-2B' END,
CASE WHEN entry_id IS NOT NULL AND invoice_no_2b IS NOT NULL AND (igst_books > 0) <> (igst_2b > 0)
THEN 'Tax head differs: books have ' || CASE WHEN igst_books > 0 THEN 'IGST' ELSE 'CGST+SGST' END || ', GSTR-2B has ' || CASE WHEN igst_2b > 0 THEN 'IGST' ELSE 'CGST+SGST' END || ' (check the place of supply)' END,
CASE WHEN entry_id IS NOT NULL AND invoice_no_2b IS NOT NULL AND (igst_books > 0) = (igst_2b > 0) AND abs(tax_books - tax_2b) > {{ inputs.tolerance }}
THEN 'Tax is Rs ' || tax_books || ' in books but Rs ' || tax_2b || ' in GSTR-2B' END
], lambda x: x IS NOT NULL) AS errors,
list_filter([
CASE WHEN entry_id IS NULL THEN 'In GSTR-2B but not in your books: book it, or ask the supplier if you never bought from them' END,
CASE WHEN entry_id IS NOT NULL AND invoice_no_2b IS NOT NULL AND invoice_no_books <> invoice_no_2b
THEN 'Invoice number is "' || invoice_no_books || '" in books and "' || invoice_no_2b || '" in GSTR-2B' END,
CASE WHEN entry_id IS NOT NULL AND invoice_no_2b IS NOT NULL AND date_books <> date_2b
THEN 'Invoice date is ' || date_books || ' in books and ' || date_2b || ' in GSTR-2B' END
], lambda x: x IS NOT NULL) AS warnings
FROM matched
UNION ALL BY NAME
SELECT
entry_id,
gstin AS supplier_gstin,
supplier_name,
invoice_no AS invoice_no_books,
taxable_value AS taxable_books,
igst + cgst_sgst AS tax_books,
['Booked twice: same supplier and invoice as ' || first_entry || ', so the ITC would be claimed twice'] AS errors,
CAST([] AS VARCHAR[]) AS warnings
FROM books
WHERE repeat_no > 1
)
ORDER BY CASE status WHEN 'ERROR' THEN 0 WHEN 'WARNING' THEN 1 ELSE 2 END, supplier_gstin, coalesce(invoice_no_books, invoice_no_2b);
COPY report TO '{{ outputFiles["itc_reconciliation.csv"] }}' (HEADER, DELIMITER ',');
SELECT
count(*) FILTER (WHERE entry_id IS NOT NULL) AS invoices_in_books,
count(*) FILTER (WHERE invoice_no_2b IS NOT NULL) AS invoices_in_2b,
coalesce(sum(tax_books), 0) AS itc_in_books,
coalesce(sum(tax_books) FILTER (WHERE status <> 'ERROR'), 0) AS itc_matched,
coalesce(sum(tax_books) FILTER (WHERE status = 'ERROR'), 0) AS itc_at_risk,
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(coalesce(entry_id, '-') || ' ' || supplier_name || ' ' || coalesce(invoice_no_books, invoice_no_2b) || ': ' || issues, chr(10)) FILTER (WHERE status = 'ERROR'), '') AS error_details,
coalesce(string_agg(coalesce(entry_id, '-') || ' ' || supplier_name || ' ' || coalesce(invoice_no_books, invoice_no_2b) || ': ' || 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 result. Errors mean input tax credit that cannot be
claimed as booked, so they are logged and optionally sent to Slack; a
clean month is logged so the execution history doubles as the
reconciliation record for the GSTR-3B.
condition: "{{ outputs.reconcile.outputs[0].row.error_count > 0 }}"
then:
- id: log_errors
type: io.kestra.plugin.core.log.Log
level: WARN
description: Write every mismatch and the exact reason to the execution logs.
message: |
ITC reconciliation: Rs {{ outputs.reconcile.outputs[0].row.itc_at_risk | numberFormat('#,##0.00') }} of Rs {{ outputs.reconcile.outputs[0].row.itc_in_books | numberFormat('#,##0.00') }} ITC at risk. {{ outputs.reconcile.outputs[0].row.error_count }} error(s), {{ outputs.reconcile.outputs[0].row.warning_count }} warning(s), {{ outputs.reconcile.outputs[0].row.ok_count }} matched.
Errors:
{{ outputs.reconcile.outputs[0].row.error_details }}
Warnings:
{{ outputs.reconcile.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_tax_team
type: io.kestra.plugin.slack.notifications.SlackIncomingWebhook
description: Tell the tax team which suppliers to follow up with before GSTR-3B
is filed.
url: "{{ secret('SLACK_WEBHOOK_URL') }}"
messageText: |
:warning: GSTR-2B reconciliation: Rs {{ outputs.reconcile.outputs[0].row.itc_at_risk | numberFormat('#,##0.00') }} ITC at risk across {{ outputs.reconcile.outputs[0].row.error_count }} invoice(s).
{{ outputs.reconcile.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 month.
message: "ITC reconciliation passed: all {{
outputs.reconcile.outputs[0].row.invoices_in_books }} booked invoices
are in GSTR-2B (Rs {{ outputs.reconcile.outputs[0].row.itc_in_books |
numberFormat('#,##0.00') }} ITC), {{
outputs.reconcile.outputs[0].row.warning_count }} warning(s)."
errors:
- id: log_reconciliation_failure
type: io.kestra.plugin.core.log.Log
level: ERROR
description: Make a failed reconciliation loud. If a file is missing or
malformed, nothing was matched, and a silent failure must not be mistaken
for a clean month.
message: "GSTR-2B reconciliation FAILED in {{ flow.namespace }}.{{ flow.id }}
(execution {{ execution.id }}). Nothing was matched, so do not file
GSTR-3B from this run. Check the columns of purchase_register and gstr2b."
outputs:
- id: itc_reconciliation
type: FILE
description: One row per invoice with status (ERROR, WARNING or OK), supplier,
invoice number in books and in GSTR-2B, taxable value and tax on both
sides, and the reasons.
value: "{{ outputs.reconcile.outputFiles['itc_reconciliation.csv'] }}"
- id: reconciliation_summary
type: JSON
description: 'Counts and amounts, e.g. {"itc_in_books": 70200.0, "itc_at_risk":
42840.0, "error_count": 5, ...}.'
value: "{{ outputs.reconcile.outputs[0].row | toJson }}"