Deep Dive: GST Reconciliation Software in GSTBot

#python #react #fastapi #postgres

Reconciliation: Turning Bulk Invoices into Accurate ITC Claims

GSTBot’s core value lies in automating the reconciliation of a company’s purchase register against the GSTR‑2B data supplied by the tax authority. For Indian SMBs, the manual process—matching each invoice line, calculating input tax credit (ITC), and flagging discrepancies—consumes weeks of effort every month. The tool replaces that with a repeatable, tolerant workflow that runs in seconds. This is where gst reconciliation software pays off in practice.

The Problem

Exact matching fails – Vendor names, invoice numbers, or tax amounts may differ slightly between the purchase ledger and the GSTR‑2B feed. A strict string match would flag every minor variation as an error, drowning users in false positives.
Volume is high – Small businesses can receive hundreds of invoices per month. A batch‑processing solution is required to avoid bottlenecks.
Regulatory nuance – Rules 37, 42, and 43 introduce reversals that must be reflected in the reconciliation, otherwise ITC calculations become inaccurate.

The Solution

GSTBot introduces a tolerance‑aware reconciliation engine that:

Normalizes data – Vendor names and HSN codes are lower‑cased and stripped of punctuation before comparison.
Applies configurable thresholds – Users set acceptable differences for amounts (e.g., ±1 %) and dates (±2 days). The engine uses these to decide whether two records represent the same transaction.
Locks per period – To avoid race conditions when multiple reconciliation jobs run concurrently, a Redis‑based advisory lock (see commit fix(reconciliation): serialise runs per period with an advisory lock) ensures only one job processes a given tax period at a time.
Classifies exceptions – Each mismatch is labeled (e.g., Amount mismatch, Vendor mismatch) and a recommended action is attached. The UI presents these in a concise table.

The engine is built on a stack that keeps the codebase lightweight yet scalable:

Layer	Tech
API	FastAPI
Background jobs	Celery, Redis
Persistence	PostgreSQL (SQLAlchemy)
Frontend	React (Vite)
Data processing	Pandas (Python)

How It Works – Behind the Code

Below is a simplified view of the reconciliation workflow. The real implementation contains additional error handling and audit logging. Getting gst reconciliation software right early saves rework later.

# reconciliation.py
import pandas as pd
from sqlalchemy.orm import Session
from redis import Redis
from celery import shared_task
from fastapi import APIRouter, Depends

router = APIRouter()

@shared_task
def reconcile_period(period: str, tolerance: dict):
    """Run reconciliation for a tax period."""
    lock_key = f"reconcile:{period}"
    with Redis().lock(lock_key, blocking_timeout=30):
        # 1. Pull local ledger
        ledger = pd.read_sql(
            "SELECT vendor, gstin, inv_no, amount, tax_rate, hsn FROM purchase_register WHERE period=%s",
            con=engine,
            params=(period,)
        )

        # 2. Pull GSTR‑2B feed
        gstr2b = pd.read_sql(
            "SELECT vendor, gstin, inv_no, amount, tax_rate, hsn FROM gstr2b WHERE period=%s",
            con=engine,
            params=(period,)
        )

        # 3. Normalise and compare
        ledger['key'] = ledger.apply(normalise_key, axis=1)
        gstr2b['key'] = gstr2b.apply(normalise_key, axis=1)

        merged = ledger.merge(gstr2b, on='key', suffixes=('_local', '_remote'))

        # 4. Apply tolerance
        def is_within(row):
            amt_diff = abs(row['amount_local'] - row['amount_remote'])
            return amt_diff <= tolerance['amount'] * row['amount_local']

        merged['ok'] = merged.apply(is_within, axis=1)

        # 5. Persist results
        merged.to_sql('reconciliation', engine, if_exists='replace', index=False)

def normalise_key(row):
    """Create a deterministic key for matching."""
    return f"{row['vendor'].lower().replace(' ', '')}|{row['gstin']}|{row['inv_no']}"

# API endpoint
@router.post("/reconcile")
async def trigger_reconciliation(
    period: str,
    tolerance: dict = Depends(get_tolerance),
    db: Session = Depends(get_db)
):
    reconcile_period.delay(period, tolerance)
    return {"status": "queued"}

The normalise_key function demonstrates the simple string normalization that reduces false mismatches. The is_within predicate applies the user‑defined amount tolerance.

The background task writes the reconciliation report to a dedicated table. The React UI consumes this table and presents a grid where each row shows the local amount, the GSTR‑2B amount, and the status (OK or Exception). Clicking an exception opens a modal with the suggested action—often “Adjust amount” or “Verify vendor”.

Using the Feature

Upload invoices – Drag a ZIP of PDFs or an Excel file onto the /upload page. The backend runs OCR and a lightweight transformer model to extract fields.
Set tolerances – In the settings panel, specify an amount tolerance of 0.5 % and a date tolerance of 1 day.
Run reconciliation – Hit “Reconcile” for the current month. The job starts in the background; you receive a toast notification when it completes.
Review results – The table shows 98 % of lines as OK. The remaining 2 % are flagged with clear reasons and actions.

Because the reconciliation engine runs in a single thread per period, you can safely trigger multiple reconciliations for different periods without interference. The Redis lock guarantees that only one job touches a period’s data at a time, preventing race conditions that could corrupt the report.

Impact

The tolerance‑aware reconciliation reduces manual effort by eliminating the need to chase every minor discrepancy. By presenting actionable exceptions, it empowers finance staff to correct entries quickly, ensuring accurate ITC claims and compliance with GSTR‑2B rules. For SMBs, this means a reliable, low‑cost alternative to enterprise‑grade reconciliation tools that are priced beyond their reach. Good gst reconciliation software keeps the rest of the workflow predictable.

Once the reconciliation table is complete, GSTBot feeds the reconciled amounts into the GSTR‑1 and GSTR‑3B preparation modules. The same tolerance logic ensures that the ITC reported on the portal matches the reconciled ledger, preventing downstream filing errors.

The Celery workers can be scaled horizontally; each worker processes a period lock, so adding more workers speeds up multi‑period runs.

DEV Community