Custom ERP Architecture: Procurement, Inventory Ledger & Tally/GST Reconciliation

Operating workflow and system boundaries

Purchase request → approval → purchase order → goods receipt → inventory ledger → supplier invoice review → Tally/GST export queue. Transfers move through requested, dispatched, in-transit and received states; dispatch is not proof of receipt.

Domain-specific PostgreSQL schema

Illustrative design excerpt. Final tables, constraints and security policies depend on discovery and review.

CREATE TABLE chart_of_accounts (
  id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  company_id bigint NOT NULL,
  code text NOT NULL,
  account_type text NOT NULL,
  UNIQUE (company_id, code)
);
CREATE TABLE purchase_orders (
  id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  company_id bigint NOT NULL,
  supplier_id bigint NOT NULL,
  po_number text NOT NULL,
  status text NOT NULL CHECK (status IN ('draft','approved','received','closed','cancelled')),
  currency char(3) NOT NULL,
  UNIQUE (company_id, po_number)
);
CREATE TABLE warehouse_transfers (
  id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  source_warehouse_id bigint NOT NULL,
  destination_warehouse_id bigint NOT NULL,
  state text NOT NULL CHECK (state IN ('requested','dispatched','received','cancelled')),
  CHECK (source_warehouse_id <> destination_warehouse_id)
);
CREATE TABLE inventory_ledger (
  id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  warehouse_id bigint NOT NULL,
  sku_id bigint NOT NULL,
  quantity_delta numeric(16,4) NOT NULL CHECK (quantity_delta <> 0),
  purchase_order_id bigint REFERENCES purchase_orders(id),
  transfer_id bigint REFERENCES warehouse_transfers(id),
  event_key text NOT NULL UNIQUE,
  occurred_at timestamptz NOT NULL DEFAULT now()
);
CREATE TABLE gst_tally_sync_queue (
  id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  company_id bigint NOT NULL,
  voucher_reference text NOT NULL,
  payload_version integer NOT NULL,
  state text NOT NULL CHECK (state IN ('pending','sent','acknowledged','rejected')),
  external_acknowledgement text,
  UNIQUE (company_id, voucher_reference, payload_version)
);

This is an integration sketch, not a complete accounting engine. Add purchase-order lines, goods receipts, balanced journal headers/lines and tax configuration for the actual scope. Use explicit reversal entries rather than changing posted inventory history. A transaction-level posting service must validate debits and credits; a row-level CHECK cannot validate totals across journal rows.

Failure scenarios and acceptance evidence

• Prevent receiving the same supplier delivery twice through a unique external event key.
• Reconcile transfer dispatch against destination receipt and report damaged or missing quantities.
• Require approval for negative-stock adjustments and retain the original movement.
• Retry a Tally export without generating a duplicate voucher; rejected exports remain visible.
• Migrate opening stock by SKU, batch and warehouse, then sign off count and valuation differences before cutover.

Operational economics with explicit assumptions

Use a discovery baseline. If procurement reconciliation consumes 30 hours/month and the agreed first release removes 40%, the model releases 12 hours. At an assumed $18 loaded hourly cost that is $216/month of capacity. Add separately measured dispatch-error reduction, subtract recurring operation costs and avoid counting the same staff time twice. A $1,500 integration module is a different scope from a complete finance and multi-warehouse ERP.

Migration and release review

Import a representative sample into staging and reconcile source identifiers, record counts and exceptions. Run a supervised pilot, record sign-off criteria, rehearse restoration, and agree the cutover owner. The final migration and rollback procedure must fit this operating workflow.

Technical references and project scope

Primary technical reference. Review the relevant software scope or plan an indicative budget. Fixed-milestone billing uses direct milestone invoicing, with optional third-party escrow by agreement for international contracts. Hosting, API fees and ongoing maintenance are scoped separately.