# gstrecon

Reconcile GSTR-2B against your purchase register in seconds instead of days. gstrecon reads the government's GSTR-2B (JSON or Excel) and a Tally/Zoho/SAP export, matches invoices through five ordered passes (exact, GSTIN + invoice number, amount + date, fuzzy invoice number, one-to-many subset sum), explains every mismatch, applies ITC rules (sec 17(5), sec 16(4), sec 16(2) 180-day rule, reverse charge), carries unmatched books invoices into next month automatically, and writes an Excel workbook, JSON and vendor follow-up email drafts.

## Features

- GSTIN validation: format, state code and the official mod-36 check digit.
- Invoice number normalisation: `INV/2024-25/0012` and `INV12` are the same invoice.
- Five logged matching passes and eight statuses: `MATCHED`, `AMOUNT_MISMATCH`, `DATE_MISMATCH`, `INV_NO_MISMATCH`, `TAX_HEAD_MISMATCH`, `RATE_MISMATCH`, `ONLY_IN_2B`, `ONLY_IN_BOOKS`.
- Credit notes, debit notes and imports (IMPG) are matched like invoices, within their own document kind.
- ITC rules: blocked credits by HSN/category (configurable), reverse charge, time limit warnings and expiry, 180-day payment rule with reversal and 18% interest estimate.
- Multi-period state in SQLite: unmatched books invoices are carried into the next period's run and resolved when the supplier files.
- Output: Rich console summary, formatted Excel workbook (summary, vendor pivot, one sheet per status, ITC flags, data issues, pass log), JSON, one email draft per vendor.
- `gstrecon explain <id>` shows why a pair matched, or why an unmatched invoice found no partner.
- Column-mapping profiles (TOML) for Tally, Zoho, SAP and generic exports; bring your own.

## Install

```bash
uv venv .venv && uv pip install -p .venv/bin/python -e '.[dev]'
source .venv/bin/activate
```

Python 3.12+. Dependencies: click, rich, openpyxl.

## Quickstart

```bash
python examples/generate_examples.py          # optional: regenerates the synthetic data
gstrecon run --2b examples/2b_2026_08.json --books examples/purchases_2026_08.csv \
    --profile zoho --period 2026-08
gstrecon run --2b examples/2b_2026_09.json --books examples/purchases_2026_09.xlsx \
    --profile tally --period 2026-09 -o recon.xlsx --json recon.json --emails-dir emails/
gstrecon vendors --status ONLY_IN_BOOKS
gstrecon carry-forward
gstrecon explain M-0249
gstrecon validate-gstin 27AAPFU0939F1ZV
```

The two runs are chained on purpose: six August books invoices were missing from August's 2B and appear in September's, so the September run resolves them through the carry-forward ledger.

## Usage

### `gstrecon run`

| Option | Meaning |
| --- | --- |
| `--2b FILE` | GSTR-2B `.json` or Excel export |
| `--books FILE` | purchase register `.csv` or `.xlsx` |
| `--profile NAME` | `tally`, `zoho`, `sap`, `generic` or a path to a `.toml` profile (default `generic`) |
| `--period YYYY-MM` | return period; warns if the 2B file's `rtnprd` disagrees |
| `-o FILE` | Excel workbook |
| `--json FILE` | full result as JSON |
| `--emails-dir DIR` | one `.txt` follow-up per vendor |
| `--rules FILE` | TOML overriding the blocked-credit config |
| `--as-of YYYY-MM-DD` | date for time-limit checks (default: end of period) |
| `--sheet NAME` | books worksheet (default: first) |
| `--no-carry-forward` | neither load nor save carried-forward invoices |
| `--db FILE` | SQLite state file (default `gstrecon.db`, env `GSTRECON_DB`) |

Real output for the September run on the generated examples (300 invoices, 40 vendors, planted cases for every status):

```
Reconciliation 2026-09: 299 2B documents, 303 books documents (8 carried forward)
                                               Matching passes                                                
┏━━━━━━━━━━━━━━━━━━━┳━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━┳━━━━━━━┓
┃ Pass              ┃ Rule                                                                           ┃ Pairs ┃
┡━━━━━━━━━━━━━━━━━━━╇━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━╇━━━━━━━┩
│ exact             │ GSTIN + invoice no + date + every amount within Rs 1                           │ 248   │
│ gstin_invoice     │ GSTIN + normalised invoice no (amount/date/head may differ)                    │ 24    │
│ gstin_amount_date │ GSTIN + taxable and tax within Rs 1 + date within 3 days (invoice no differs)  │ 3     │
│ fuzzy_invoice     │ GSTIN + invoice no similarity >= 0.85 (date within 45 days, taxable within 5%) │ 3     │
│ one_to_many       │ GSTIN subset-sum (<= 4 docs from 12 candidates)                                │ 5     │
└───────────────────┴────────────────────────────────────────────────────────────────────────────────┴───────┘
Summary                                                                                                   
┏━━━━━━━━━━━━━━━━━━━┳━━━━━━━┳━━━━━━━━━━━━━━┳━━━━━━━━━━━━━━┳━━━━━━━━━━━━━━┳━━━━━━━━━━━━┳━━━━━━━━━━━━━━━━━━┓
┃ Status            ┃ Count ┃ ITC 2B       ┃ ITC books    ┃ Claimable    ┃ At risk    ┃ In 2B not booked ┃
┡━━━━━━━━━━━━━━━━━━━╇━━━━━━━╇━━━━━━━━━━━━━━╇━━━━━━━━━━━━━━╇━━━━━━━━━━━━━━╇━━━━━━━━━━━━╇━━━━━━━━━━━━━━━━━━┩
│ MATCHED           │ 253   │ 8,204,619.75 │ 8,138,354.35 │ 7,977,811.78 │ 0.01       │ 0.00             │
│ AMOUNT_MISMATCH   │ 8     │ 247,665.82   │ 245,138.06   │ 243,631.01   │ 1,507.05   │ 0.00             │
│ DATE_MISMATCH     │ 6     │ 282,947.27   │ 282,947.27   │ 282,947.27   │ 0.00       │ 0.00             │
│ INV_NO_MISMATCH   │ 6     │ 162,152.13   │ 162,152.13   │ 162,152.13   │ 0.00       │ 0.00             │
│ TAX_HEAD_MISMATCH │ 5     │ 196,063.40   │ 196,063.38   │ 196,063.38   │ 0.00       │ 0.00             │
│ RATE_MISMATCH     │ 5     │ 191,332.11   │ 127,554.73   │ 127,554.73   │ 0.00       │ 0.00             │
│ ONLY_IN_2B        │ 14    │ 388,751.12   │ 0.00         │ 0.00         │ 0.00       │ 388,751.12       │
│ ONLY_IN_BOOKS     │ 15    │ 0.00         │ 487,322.54   │ 48,968.88    │ 400,724.59 │ 0.00             │
├───────────────────┼───────┼──────────────┼──────────────┼──────────────┼────────────┼──────────────────┤
│ TOTAL             │ 312   │ 9,673,531.60 │ 9,639,532.46 │ 9,039,129.18 │ 402,231.65 │ 388,751.12       │
└───────────────────┴───────┴──────────────┴──────────────┴──────────────┴────────────┴──────────────────┘
180-day reversal: Rs 78,247.74, interest estimate Rs 7,901.48
Flags: BLOCKED_17_5 x3, CARRIED_FORWARD x8, INELIGIBLE_IN_BOOKS x2, INVALID_GSTIN x2, PAYMENT_180D_BREACH x3, PAYMENT_180D_RISK x2, 
REVERSE_CHARGE x2, TIME_LIMIT_EXPIRED x1, TIME_LIMIT_NEAR x6
4 data issues (see the Data Issues sheet)
Excel workbook: out/recon_2026_09.xlsx
JSON: out/recon_2026_09.json
25 vendor follow-up drafts in out/emails
```

### `gstrecon vendors`

Vendor worklist for any status (`--details` lists the invoices, `--period` picks a run, default latest):

```
                        ONLY_IN_BOOKS by vendor, 2026-09                         
┏━━━━━━━━━━━━━━━━━┳━━━━━━━━━━━━━━━━━━━━━━━━━━━━┳━━━━━━┳━━━━━━━━━━━━┳━━━━━━━━━━━━┓
┃ GSTIN           ┃ Vendor                     ┃ Docs ┃ Taxable    ┃ Tax        ┃
┡━━━━━━━━━━━━━━━━━╇━━━━━━━━━━━━━━━━━━━━━━━━━━━━╇━━━━━━╇━━━━━━━━━━━━╇━━━━━━━━━━━━┩
│ 27STTPB9320C1Z9 │ Bharat Stationers Pvt Ltd  │ 2    │ 593,474.60 │ 106,825.44 │
│ 27MMGFZ2169W1ZX │ Lotus Logistics Pvt Ltd    │ 1    │ 333,435.23 │ 60,018.34  │
│ 36JCGFG9179N1Z7 │ Kaveri Enterprises Pvt Ltd │ 1    │ 380,615.06 │ 45,673.81  │
│ 07HUUFV8019V1ZF │ Indus Logistics Pvt Ltd    │ 1    │ 334,472.20 │ 40,136.66  │
│ 27CGHCU4257Z1ZN │ Bharat Traders Pvt Ltd     │ 1    │ 171,782.70 │ 30,920.88  │
```

### `gstrecon carry-forward`

Lists books invoices still waiting for a 2B (`--all` includes resolved ones):

```
13 item(s), tax Rs 438,353.66
```

### `gstrecon explain <id>`

Accepts a match id (`M-0249`), a document id (`BK-2026-09-0024`) or an invoice number. For unmatched documents it lists the closest same-GSTIN candidates on the other side and why they were rejected.

```
M-0249  TAX_HEAD_MISMATCH  (pass: gstin_invoice, similarity 1.00)
Vendor: Fortune Logistics Pvt Ltd  GSTIN: 27PAFFP5065J1ZJ
┏━━━━━━━┳━━━━━━━━━━━━━━━━━┳━━━━━━━━━┳━━━━━━━┳━━━━━
┃ Side  ┃ Doc             ┃ Invoice ┃ Norm  ┃ Date       ┃ Taxable    ┃ IGST      ┃ CGST      ┃ SGST      ┃ Cess ┃ Rate % ┃
┡━━━━━━━╇━━━━━━━━━━━━━━━━━╇━━━━━━━━━╇━━━━━━━╇━━━━━
│ 2B    │ 2B-2026-09-0094 │ TX-386  │ TX386 │ 2026-09-13 │ 447,603.98 │ 0.00      │ 11,190.10 │ 11,190.10 │ 0.00 │ 5      │
│ Books │ BK-2026-09-0024 │ TX-386  │ TX386 │ 2026-09-13 │ 447,603.98 │ 22,380.20 │ 0.00      │ 0.00      │ 0.00 │ 5      │
└───────┴─────────────────┴─────────┴───────┴─────
Why
  - tax head differs: 2B charges CGST_SGST, books record IGST (place-of-supply issue)
Passes tried, in order
  exact             GSTIN + invoice no + date + every amount within Rs 1 
  gstin_invoice     GSTIN + normalised invoice no (amount/date/head may differ) <- matched here
  gstin_amount_date GSTIN + taxable and tax within Rs 1 + date within 3 days (invoice no differs) 
  fuzzy_invoice     GSTIN + invoice no similarity >= 0.85 (date within 45 days, taxable within 5%) 
  one_to_many       GSTIN subset-sum (<= 4 docs from 12 candidates) 
ITC  2B 22,380.20  books 22,380.20  claimable 22,380.20  at risk 0.00  in 2B not booked 0.00
```

### `gstrecon validate-gstin`

```
$ gstrecon validate-gstin 27AAPFU0939F1ZV
VALID 27AAPFU0939F1ZV  state: Maharashtra  PAN: AAPFU0939F (Firm / LLP)
$ gstrecon validate-gstin 27AAPFU0939F1ZA
INVALID 27AAPFU0939F1ZA: checksum mismatch (expected check character V, got A)
```

Exit code is 1 for an invalid GSTIN, so it works in scripts.

### Excel workbook

Sheets: `Summary` (counts and ITC per status, ITC position, flag counts), `Vendor Pivot` (status counts and ITC per vendor, sorted by ITC at risk), one sheet for each of the eight statuses (filters, frozen header, totals row), `ITC Flags`, `Data Issues` (bad rows, invalid GSTINs, duplicate entries) and `Pass Log`. Numbers are real values, so the file can be post-processed with openpyxl or pandas.

### Vendor follow-up drafts

One file per vendor with invoices to chase. Section A lists invoices in your books but missing from 2B (reverse charge, blocked and ineligible items are left out, as chasing them is pointless). Section B lists invoices present on both sides with differences and the reason.

## Configuration

Column profiles live in `src/gstrecon/profiles/*.toml`. Each field lists header aliases; matching is case and whitespace insensitive and the header row is auto-detected within the first 20 rows:

```toml
[columns]
gstin = ["GSTIN/UIN"]
invoice_no = ["Supplier Invoice No."]
date = ["Supplier Invoice Date"]
taxable = ["Taxable Value"]
igst = ["Integrated Tax Amount"]   # also cgst, sgst, cess, vendor, hsn, itc_eligible, doc_type, rcm, payment_date, category, rate

[options]
date_formats = ["%d-%m-%Y"]        # tried before the built-in formats
eligible_no = ["N", "NO", "INELIGIBLE"]
```

`gstin`, `invoice_no`, `date` and `taxable` are required. `doc_type` text containing "credit" or "debit" yields credit/debit notes, "import" yields IMPG. Blocked credits (sec 17(5)):

```toml
[blocked]
hsn_prefixes = ["8703", "9963"]
keywords = ["food", "club"]       # matched against the books category/ledger column
```

## Architecture

| Module | Role |
| --- | --- |
| `gstin.py` | format, state table, mod-36 checksum, generator |
| `normalize.py` | invoice number normalisation, date/amount parsing, Levenshtein |
| `models.py` | `Doc` (one invoice/note/import) and `Match` |
| `parse_2b.py` | GSTR-2B JSON and Excel readers (fields documented in the module docstring) |
| `parse_books.py` | profile-driven CSV/XLSX reader with row-level issue reporting |
| `matcher.py` | the five passes, status classification, `diagnose` |
| `itc.py` | blocked credit, time limit, 180-day, reverse charge, ITC amounts |
| `engine.py` | orchestration, summary, carry-forward integration |
| `store.py` | SQLite: stored runs and the carry-forward ledger |
| `report.py` | Excel, JSON, vendor emails, vendor pivot |
| `synth.py` | deterministic synthetic data with planted cases |
| `cli.py` | click commands and Rich output |

More in [docs/matching-algorithm.md](docs/matching-algorithm.md), [docs/gst-rules.md](docs/gst-rules.md) and [docs/limitations.md](docs/limitations.md).

## Tests

```bash
.venv/bin/python -m pytest -q
```

108 tests cover the GSTIN checksum, the normalisation table, every pass and status, subset-sum, ITC rules, parsers, carry-forward, Excel contents and the CLI end to end against generated examples.

## Limitations

Short list; see [docs/limitations.md](docs/limitations.md): ITC is computed on total tax, not per head; the 180-day interest is an estimate; amendments (B2BA, CDNRA), ISD and SEZ sections of 2B are not read; one run per period is kept.