GSTR-2B Reconciliation Made Simple

#python #react #fastapi #postgres

The Challenge of Manual Reconciliation

For Indian SMBs, reconciling the purchase register against the GSTR‑2B feed is a monthly chore that involves thousands of invoice line items. Each line must be matched by vendor, GSTIN, invoice number, amount, tax rate, and HSN code. The current manual workflow relies on spreadsheets, ad‑hoc filters, and a lot of trial‑and‑error. Even a single misplaced comma can trigger a cascade of “unmatched” flags, forcing finance teams to dig through logs and re‑enter data. This is where gst reconciliation software pays off in practice.

GSTBot’s Reconciliation Engine

GSTBot replaces that pain with an automated, tolerance‑aware engine. The core of the engine is a FastAPI endpoint that accepts a batch of PDFs, photos, or Excel files, forwards them to a Celery worker, and stores the parsed data in PostgreSQL. The worker then performs a fuzzy join against the GSTR‑2B download, using a configurable tolerance window for numeric fields.

# FastAPI route that triggers the reconciliation
@router.post("/reconcile")
async def reconcile_invoices(file: UploadFile = File(...)):
    task_id = await celery_app.send_task("tasks.reconcile", args=[file.filename])
    return {"task_id": task_id}

The Celery task pulls the file from S3, runs the AI extraction model, and writes the results to a parsed_invoices table.

@celery_app.task(name="tasks.reconcile")
def reconcile(file_name: str):
    invoices = extract_invoices(file_name)  # AI extraction
    session.execute(insert(parsed_invoices).values(invoices))
    session.commit()
    perform_match()

The perform_match function performs the heart of the reconciliation: Getting gst reconciliation software right early saves rework later.

# PostgreSQL query that joins parsed invoices with GSTR‑2B entries
query = (
    select(
        pi.id.label("invoice_id"),
        gb.id.label("gstr_id"),
        abs(pi.amount - gb.amount).label("amount_diff")
    )
    .select_from(parsed_invoices.alias("pi"))
    .join(gstr2b.alias("gb"),
          and_(
              pi.vendor == gb.vendor,
              pi.gstin == gb.gstin,
              pi.invoice_no == gb.invoice_no,
              func.abs(pi.amount - gb.amount) <= 0.01 * pi.amount
          ))
)
results = session.execute(query).fetchall()

The tolerance of 1 % is configurable via the settings.py module. If an amount differs by more than the threshold, the engine flags the line as an exception and stores it in an exceptions table for later review.

How the Engine Works

Ingestion – Users upload bulk invoice files through the React UI. The front‑end calls the /reconcile endpoint and receives a task ID.
Extraction – A Celery worker runs the open‑source OCR and NLP pipeline to pull vendor name, GSTIN, invoice number, amount, tax rate, and HSN code. The extraction results are written to PostgreSQL.
Matching – A Postgres query joins the parsed invoices with the GSTR‑2B dataset, applying a 1 % tolerance on amounts. The query uses func.abs to compute the absolute difference.
Exception Classification – Any unmatched rows are routed through a rule engine that tags the exception (e.g., missing vendor, amount mismatch, HSN mismatch) and suggests a corrective action.
Reporting – The results are surfaced in the UI as a drill‑down table. Finance staff can approve, edit, or flag each exception, and the changes propagate back to the parsed_invoices table.

The entire flow is orchestrated by Redis, which holds the Celery task queue. Redis is configured with a short TTL (5 min) so that stalled tasks are automatically retried. The health endpoint (/health/jobs) reports the status of the broker, ensuring that the system is always aware of queue back‑pressure.

Example Flow

A small manufacturer uploads a 50‑page PDF of purchase invoices. The React UI shows a progress bar as the file is uploaded. Once the Celery task starts, the AI extraction takes 12 seconds per page, a 30 % improvement over the legacy OCR tool. The matching query runs in 0.8 seconds, producing 47 matches and 3 exceptions. The UI lists the exceptions with a color‑coded status and a one‑click “Edit” button. Good gst reconciliation software keeps the rest of the workflow predictable.

{
  "exceptions": [
    {"invoice_id": 1023, "reason": "amount mismatch", "suggested_action": "re‑enter amount"},
    {"invoice_id": 1047, "reason": "missing vendor", "suggested_action": "add vendor"}
  ]
}

Finance staff can resolve the two exceptions in under a minute, after which the system automatically updates the input tax credit (ITC) ledger. The reconciled data feeds directly into the GSTR‑1 and GSTR‑3B generation modules, completing the compliance cycle.

Technical Highlights

FastAPI handles the HTTP layer and provides a lightweight, async API.
Celery + Redis decouples heavy extraction and matching logic from the request cycle, enabling horizontal scaling.
PostgreSQL stores parsed invoices and GSTR‑2B data; its jsonb column stores raw OCR output for audit trails.
Configurable Tolerances are exposed via environment variables, allowing a 0.5 % or 2 % threshold depending on business rules.
Robust Error Handling – When the Redis broker is unreachable, the worker logs a 503 with Retry-After instead of a 500, and the UI displays a friendly message.
Security – OAuth providers that redirect to localhost are blocked, and Microsoft’s email_verified claim is enforced to prevent spoofed sign‑ins.

GSTBot’s reconciliation engine exemplifies how a focused, well‑structured stack can transform a manual, error‑prone task into a repeatable, auditable workflow. By leveraging FastAPI, Celery, Redis, and PostgreSQL, the system delivers accurate, tolerance‑aware ITC reconciliation for Indian SMBs, eliminating the need for spreadsheets and reducing month‑end cycle time.

DEV Community