PostgreSQL Schema Design for Multi-Branch Retail Inventory and Financial Auditing

When building business management platforms for small and medium retailers, data consistency is the number one priority. A lost transaction or inaccurate stock count damages business trust immediately.

At EquiSaaS BD, the foundation of our SME Software Suite is a clean, relational PostgreSQL schema engineered around double-entry ledger principles and immutable audit logs.

Here is an architectural walkthrough of our inventory and ledger schema.

---

1. Separation of Stock Definitions and Branch Allocations

A common novice mistake is storing the inventory quantity directly inside a products table. In a multi-branch business with central warehouses and retail counters, this falls apart.

We decouple the global product catalog from branch-specific inventory balances:

-- Global catalog definition
CREATE TABLE products (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    sku VARCHAR(64) UNIQUE NOT NULL,
    barcode VARCHAR(64) INDEX NOT NULL,
    name VARCHAR(255) NOT NULL,
    cost_price NUMERIC(12, 2) NOT NULL,
    selling_price NUMERIC(12, 2) NOT NULL,
    created_at TIMESTAMPTZ DEFAULT NOW()
);

-- Branch stock allocation
CREATE TABLE branch_inventory (
    branch_id UUID NOT NULL REFERENCES branches(id),
    product_id UUID NOT NULL REFERENCES products(id),
    quantity_available INT NOT NULL DEFAULT 0,
    reorder_level INT NOT NULL DEFAULT 5,
    updated_at TIMESTAMPTZ DEFAULT NOW(),
    PRIMARY KEY (branch_id, product_id)
);

---

2. Immutable Inventory Transaction Ledger

Updating quantities with raw UPDATE queries makes historical auditing impossible. When inventory doesn't match physical shelves, you cannot trace who changed it or when.

We record every inventory delta as an immutable ledger entry:

CREATE TYPE inventory_movement_type AS ENUM (
    'PURCHASE_RECEIPT',
    'SALE',
    'RETURN_CUSTOMER',
    'DAMAGE_WRITE_OFF',
    'BRANCH_TRANSFER'
);

CREATE TABLE inventory_movements (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    branch_id UUID NOT NULL REFERENCES branches(id),
    product_id UUID NOT NULL REFERENCES products(id),
    movement_type inventory_movement_type NOT NULL,
    delta INT NOT NULL,
    reference_id UUID,
    performed_by UUID NOT NULL REFERENCES users(id),
    created_at TIMESTAMPTZ DEFAULT NOW()
);

A PostgreSQL trigger or transactional service updates branch_inventory.quantity_available whenever an inventory_movements record is inserted, guaranteeing an unbroken audit trail.

---

Learn More and Explore

For developers interested in contributing to open business software, check out our Open Tech Cooperative and review our practical documentation in the BD ERP POS Client Operating Manual.