Specifications Handoff

Invoice & Ledger Automator

Unified Product Requirements Document for Laxmi Ratan Material Trading Invoicing & Ledgers

Generated Date

June 11, 2026

1. Project Overview

The **Invoice & Ledger Automator** is a comprehensive, keyboard-first web application designed to manage client relationships, inventory records, and invoicing needs for a material trading business. By moving operational data away from separate offline spreadsheets and local databases, it establishes a singular source of truth for generating GST-compliant billing, managing customer credit cycles, and handling Chartered Accountant auditing workflows.

A key objective of this project is high-performance, single-screen layouts containing robust text-parsing drawers. This allows operators to copy transaction messages directly from platforms like WhatsApp, paste them into the system, and automatically populate multi-item invoice drafts in seconds.

2. Business Goals & System Pain Points

Current System Pain Points

  • Excel Client Ledgers: Inward/outward collections mapped by hand. Error-prone running balances and slow pdf reporting for customer payment requests.
  • Google Sheet 1 (GST Invoices): Row duplication to issue new bills, manually searching past customer histories to verify past pricing models.
  • Google Sheet 2 (Auxiliary Documents): Quotes, Proforma Invoices, and Delivery Challans kept in another sheet to keep tax books clean, resulting in fragmented records.

Core Business Goals

  • Invoicing Speed: Reduce typical invoice creation cycles by over 50% using keyboard hotkeys and text parsing drawers.
  • Consolidated Dashboard: Monitor client credit cycle health with color-coded invoice statuses and real-time outstanding balances.
  • Price Consistency: Auto-resolve product rates using historical customer transaction records.
  • CA Auditing Readiness: Provide read-only views for tax records and generate single-click GSTR1 exports.
Scope Exclusion (Non-Goals): The application will not integrate with third-party WhatsApp APIs directly or use heavy AI/ML parsers. Parsing actions are client-side, using optimized regular expressions and master keyword matching.

3. Multi-Organization Architecture

To support multiple business verticals under the same operator, the system includes native **Multi-Organization Management**. Each organization maintains separate billing profiles, bank details, tax rates, and sequential numbering, but can optionally share master files.

Company Profiles

Separate profiles for Company Name, Billing Address, Bank Account Details, Seller GSTIN, and UPI IDs.

Pre-built PDF Templates

Configure distinct design templates for each organization, allowing tailored branding for different business lines.

Shared Catalog Master

Clients and product masters can be shared globally or isolated, ensuring operators write details only once.

Dynamic Tax Rules: If a Seller's profile is missing its GSTIN, the system automatically disables all GST tax fields across invoice editors and renders clean, tax-exempt bills.

4. Master Registries

Client Master

Stores customer credentials and billing metadata. Unique identifiers connect clients to custom pricing indexes and automatic ledgers.

Field Type Role
name VARCHAR(255) Official Billing Name
short_name VARCHAR(100) Parser Keyword (Unique)
gst_number VARCHAR(15) Standard Indian GSTIN (Optional)
billing_address TEXT Address details
state VARCHAR(100) Determines IGST vs CGST/SGST
default_due_days INT Credit Days (Default: 30)

Product Master

Maintains product lines, HSN classifications, standard unit metrics, and default tax groups.

Field Type Role
name VARCHAR(255) Standard Descriptive Name
short_name VARCHAR(100) Parser Keyword (Unique)
hsn_code VARCHAR(15) HSN code for GST validation
unit VARCHAR(50) e.g. Bags, Pcs, Kgs
default_gst_rate DECIMAL(5,2) Default GST percentage
base_price DECIMAL(15,2) Fallback catalog pricing

Product Tax Rate & Validation Rules:

  • Individual GST Rates: Different products can have different GST rates (e.g., 5%, 12%, 18%, 28%) which are configured in the `default_gst_rate` field of the Product Master.
  • Dynamic Tax Application: When composing an invoice, the system must resolve and apply the tax rate specific to each product selected in the line items.
  • Mixed Tax Rate Warning Alert: If a product is selected and its GST rate differs from the GST rate(s) of other items already added to the active invoice, the system must trigger a prominent but non-blocking UI alert. This ensures the operator is aware that the invoice contains items with mixed tax rates.

5. Document Lifecycles & Flow

To keep audit logs clean and prevent tax reporting errors, documents are separated into **Official Tax Invoices** and **Auxiliary Documents**. CA access is restricted exclusively to finalized Tax Invoices, completely hiding pre-sales draft documents.

Step 1

1. Quote

Pre-sales pricing document. Does not map to financial ledgers.

Step 2

2. Proforma Invoice

Pre-shipment payment request document. Keeps bookkeeping clear.

Step 3

3. Delivery Challan

Transport and packing slip. Does not apply tax amounts.

Step 4

4. Tax Invoice

Official GST tax invoice. Generates ledger entries and locks on finalization.

Conversion Workflow Logic

  • Convert Quote: Can be converted to either a Proforma Invoice or a Delivery Challan.
  • Convert Challan/Proforma: Instantly converts to a Draft Tax Invoice, copying all client references, line items, and quantities to speed up final bill execution.

Invoice Numbering & Sequence Controls

  • Strict Sequential Order: Finalized invoices must follow a strict sequential numbering scheme (e.g., `001/26-27`, `002/26-27`) without gaps.
  • Financial Year Suffix: The numbering suffix changes on **April 1st** of each year to represent the new financial year (e.g., `/26-27`, `/27-28`) and resets the sequential counter to `001`.
  • Organization Isolation: Each business organization maintains its own **separate invoice sequences**. An invoice generated under Org A (e.g. `001/26-27`) will not increment or affect the sequence counter of Org B.
  • Trigger for Sequence Assignment (Tax Applicability): Sequence numbers are assigned ONLY when an invoice is Finalized and becomes Tax-Applicable. Drafts, Quotes, Proforma Invoices, and Delivery Challans are assigned `NULL` in the database field and do not consume sequence numbers. This prevents gaps in tax sequencing due to unpaid/deleted drafts.

6. Smart Parser

Pasting raw details (like WhatsApp shipping requests) generates drafts instantly. Try out the **interactive parsing playground** below to see the regex parser in action:

Client Short Name: -
Product ID Quantity
No parsed items. Run parser to test.
* Parsing Rule: Line 1 extracts the client ID. Subsequent lines extract the last number as quantity, and the preceding text as the product identifier.

7. Price Intelligence Engine

Avoid manually searching sheets to recall previous sales agreements. When a product line is added, the billing engine resolves the default unit price using the following prioritized search hierarchy:

1

Priority 1: Client Specific Price History

Queries the `client_product_prices` history table. If this client bought this item previously, the most recent rate is selected.

2

Priority 2: Recent Global Sales Price

If the client has no sales history for this item, the engine resolves to the last finalized sales rate of this item to any other client.

3

Priority 3: Catalog Base Rate

If no transactional history is found for this item, the fallback standard catalog rate (`base_price` from the Product Master) is loaded.

Real-time Update Trigger: The `client_product_prices` table is updated automatically only when a Draft Invoice changes state to **Finalized**. Manual editing of rates is always allowed in the editor before finalization.

8. Ledger Management & FIFO Cashflow

Keeps track of cash collection performance. Finalized tax invoices post automatic debit records to the client ledger, and payment receipts register credits that split across outstanding balances.

Ledger Entries

  • Debit entries: Generated automatically when a Tax Invoice is finalized.
  • Credit entries: Added when payments are registered (recording Date, Mode, Amount, UPI Ref, and Notes).

FIFO Allocation

Received payments automatically distribute to the oldest outstanding debit balances. Operators can override allocations to apply payments to specific invoices manually.

Status Tracking Colors

Paid Pending Partial Overdue

Client Ledger Exporter

Allows operators to select a client and date range to export a printable ledger summary PDF. Renders a chronological statement of payments and invoices, displaying the running outstanding balance.

9. Audit & Accounting Edit-Locking Controls

To maintain strict consistency between local ledger balances and GSTR1 tax filings, finalized invoices are locked to prevent retroactive updates.

Edit-Locking Configuration

invoice_edit_lock_day = 5
Locking Threshold:

Invoices automatically lock after the 5th day of the next month following their issue date.

Example: Invoices dated March 12th automatically lock on April 6th at 00:00.

Impact of Locking:
  • Invoice line items, client profiles, and tax fields become read-only.
  • Deletions or returning the invoice back to "Draft" state are blocked.
  • Allowed: Logging payment collections and mapping ledger balances remain unlocked.

10. Architecture & Technology Stack

Frontend Layer

  • React 19 SPA (Vite)
  • TypeScript for type safety
  • Tailwind CSS 4 for layouts
  • Zustand for fast client state
  • TanStack Query for caching

Backend & DB

  • Laravel 12 API Framework
  • MySQL (Local WAMP or cPanel)
  • Laravel Sanctum token auth
  • REST API structure

Reporting & Visuals

  • html2canvas & jsPDF (PDF export)
  • Recharts (Dashboard charts)
  • Lucide React for clear icons

Unified cPanel Deployment Strategy

To simplify local WAMP setups and cPanel hosting configurations, the React frontend is compiled to static assets and copied directly into Laravel's public directory. Laravel uses a catch-all route that serves the React single-page `index.html` file for non-API requests.

11. SQL Database Tables Schema

1. clients

CREATE TABLE clients (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(255) NOT NULL,
    short_name VARCHAR(100) UNIQUE NOT NULL,
    gst_number VARCHAR(15) UNIQUE NULL,
    billing_address TEXT NOT NULL,
    state VARCHAR(100) NOT NULL,
    default_due_days INT UNSIGNED DEFAULT 30,
    contact_phone VARCHAR(50) NULL,
    contact_whatsapp VARCHAR(50) NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

2. products

CREATE TABLE products (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(255) NOT NULL,
    short_name VARCHAR(100) UNIQUE NOT NULL,
    hsn_code VARCHAR(15) NOT NULL,
    unit VARCHAR(50) NOT NULL,
    default_gst_rate DECIMAL(5,2) DEFAULT 18.00,
    base_price DECIMAL(15,2) DEFAULT 0.00,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

3. invoices

CREATE TABLE invoices (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    document_type ENUM('invoice', 'quote', 'proforma', 'challan') DEFAULT 'invoice',
    invoice_number VARCHAR(50) UNIQUE NULL,
    client_id INT UNSIGNED NOT NULL,
    date DATE NOT NULL,
    total_amount DECIMAL(15,2) NOT NULL DEFAULT 0.00,
    paid_amount DECIMAL(15,2) NOT NULL DEFAULT 0.00,
    status ENUM('draft', 'finalized', 'cancelled') DEFAULT 'draft',
    finalized_at TIMESTAMP NULL,
    locked_at TIMESTAMP NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (client_id) REFERENCES clients(id) ON DELETE RESTRICT
);

4. invoice_items

CREATE TABLE invoice_items (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    invoice_id INT UNSIGNED NOT NULL,
    product_id INT UNSIGNED NOT NULL,
    quantity DECIMAL(15,3) NOT NULL,
    rate DECIMAL(15,2) NOT NULL,
    gst_rate DECIMAL(5,2) NOT NULL,
    amount DECIMAL(15,2) NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (invoice_id) REFERENCES invoices(id) ON DELETE CASCADE,
    FOREIGN KEY (product_id) REFERENCES products(id) ON DELETE RESTRICT
);

5. payments

CREATE TABLE payments (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    client_id INT UNSIGNED NOT NULL,
    amount DECIMAL(15,2) NOT NULL,
    payment_date DATE NOT NULL,
    payment_mode VARCHAR(50) NOT NULL,
    transaction_reference VARCHAR(255) NULL,
    notes TEXT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (client_id) REFERENCES clients(id) ON DELETE RESTRICT
);

6. payment_invoice_map

CREATE TABLE payment_invoice_map (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    payment_id INT UNSIGNED NOT NULL,
    invoice_id INT UNSIGNED NOT NULL,
    amount_applied DECIMAL(15,2) NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (payment_id) REFERENCES payments(id) ON DELETE CASCADE,
    FOREIGN KEY (invoice_id) REFERENCES invoices(id) ON DELETE CASCADE
);

7. client_product_prices

CREATE TABLE client_product_prices (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    client_id INT UNSIGNED NOT NULL,
    product_id INT UNSIGNED NOT NULL,
    last_price DECIMAL(15,2) NOT NULL,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (client_id) REFERENCES clients(id) ON DELETE CASCADE,
    FOREIGN KEY (product_id) REFERENCES products(id) ON DELETE CASCADE,
    UNIQUE KEY uq_client_product (client_id, product_id)
);

12. Reference Client Documents (Examples)

Below are links to download and inspect real-world documents representing existing transaction sheets and ledger data. These serve as templates and reference structures for generated invoices and statement summaries for client RR:

Invoice Example

RR - Siya Engineering

Sample client invoice dated June 11, 2026. Shows typical item quantities and GST calculations.

Open Invoice PDF
Tax Invoice 049

RR Packaging GST Invoice

Finalized GST tax invoice number 049 dated June 11, 2026. Use as template reference.

Open Invoice PDF
Ledger Statement

RR Packaging Ledger Summary

Historical ledger summary tracking payments and outstanding invoices up to June 4, 2026.

Open Ledger PDF