Designing an ERP Database Schema for Indian Manufacturing: A Technical Walkthrough

1 Core Entity Groups in a Manufacturing ERP

A manufacturing ERP schema can be broken into six entity groups, each with its own table cluster. Understanding the boundaries between these groups is critical — cross-group queries should be minimal and well-indexed.

The six groups are: Master Data (items, vendors, customers, employees), Procurement (POs, GRNs, vendor invoices), Production (BOM, work orders, job work), Inventory (stock ledger, transfers, adjustments), Sales (quotations, sales orders, delivery challans, invoices), and Finance (journal entries, payment reconciliation, GST returns).

• items master: item_code, HSN code, unit of measure, standard cost, reorder level
• vendors master: vendor_id, GSTIN, payment terms, bank details, TDS applicable flag
• customers master: customer_id, GSTIN, credit limit, pricing tier, default dispatch route
• employees: employee_id, department, grade, PF/ESI applicability, bank account
• All masters carry created_at, updated_at, created_by, is_active timestamps for audit

2 Bill of Materials (BOM) Schema Design

The BOM is one of the most complex structures in manufacturing ERP. A finished product may have 3–4 levels of sub-assemblies, each with their own component lists. The schema must support recursive BOM structures efficiently.

We use an adjacency list model with recursive CTE queries in PostgreSQL. The bom_headers table stores the parent item and version number. The bom_lines table stores each component with quantity, unit of measure, wastage percentage, and optional substitution items. BOM versioning is critical — production orders reference a specific BOM version, not the latest.

3 GST-Compliant Billing: The Invoice Schema

Indian GST billing requires specific fields that standard international ERP schemas don't include. Every sales invoice must carry: supplier GSTIN, buyer GSTIN, place of supply (for determining CGST+SGST vs IGST), HSN/SAC code per line item, and IRN (Invoice Reference Number) from the e-invoice portal for B2B transactions above ₹5 crores turnover.

We design the billing schema to be e-invoice ready from day one — even if the client isn't yet mandated for e-invoicing. The IRN field is nullable initially and gets populated when the client's turnover crosses the threshold. This prevents a costly schema migration later.

• sales_invoices: invoice_no, date, customer_id, place_of_supply, irn, qr_code_data
• sales_invoice_lines: item_id, hsn_code, qty, unit_price, discount, gst_rate, cgst, sgst, igst
• gst_returns: gstr1_data, gstr3b_data, filing_period, status — stored as JSONB in PostgreSQL
• e_invoices: irn, ack_no, ack_date, qr_code — linked to sales_invoices table
• All amounts stored in paise (integer), not decimal — avoids floating-point rounding errors

4 Inventory Ledger: The Double-Entry Approach

Standard ERP inventory tracking uses a simple quantity balance approach. We use a double-entry inventory ledger — every stock movement creates two entries (one debit, one credit) with a reference to the source transaction. This makes auditing trivial and eliminates the possibility of negative stock without a deliberate override.

The stock_ledger table is append-only — no updates, no deletes. Stock quantity at any point in time is calculated by summing all entries for an item+location up to that date. This is the same principle as financial double-entry accounting, applied to physical inventory.

• stock_ledger: entry_id, item_id, location_id, txn_date, txn_type, qty_in, qty_out, reference_id
• Current stock = SUM(qty_in) - SUM(qty_out) for item+location
• txn_type: GRN, production_issue, production_receipt, sales_delivery, transfer_out, transfer_in, adjustment
• Negative stock check at transaction time — system blocks unless override flag is set
• Stock valuation: weighted average cost updated on every GRN entry