03 — Inventory, Catalog & Pack Hierarchy Specification

1. The Core Liquor Inventory Challenge: Pack Disaggregation

In standard grocery or apparel retail, a product arrives in a box, is unpacked, and sold as a single item with a single UPC. In retail beer, wine, and spirits, inventory relationships are fluid and multi-tiered:

                          ┌─────────────────────────────┐
                          │     MASTER CASE (24 Cans)   │
                          │ • Vendor Invoiced Unit      │
                          │ • Case UPC / ITF-14 Barcode │
                          │ • Cost: $36.00 ($1.50/can)  │
                          └──────────────┬──────────────┘
                                         │
                 ┌───────────────────────┴───────────────────────┐
                 ▼                                               ▼
   ┌───────────────────────────┐                   ┌───────────────────────────┐
   │    FOUR 6-PACKS (6 Cans)  │                   │  TWENTY-FOUR SINGLES (1)  │
   │ • Retail 6-Pk UPC         │                   │ • Retail Single Can UPC   │
   │ • Retail Price: $13.99    │                   │ • Retail Price: $2.99     │
   │ • Margin: 35.7%           │                   │ • Margin: 49.8%           │
   └───────────────────────────┘                   └───────────────────────────┘

The Failure of Generic POS

Generic POS systems require clerks to perform manual "inventory conversions" or "break-pack" adjustments every time a case is opened to restock a shelf or single-can craft cooler. Because clerks skip this step during rush periods, single-can inventory shows negative stock while case inventory shows phantom excess.

The LQPOS Master Base-Unit Architecture

LQPOS solves this fundamentally at the database level:

1. Atomic Base Unit Storage:
   • Every product tracks inventory in its Smallest Sellable Common Unit (e.g. 1 can, 1 bottle).
   • A 24-can case of beer is defined with pack_qty = 24.
   • A 6-pack is defined with pack_qty = 6.
   • A single can is defined with pack_qty = 1.
2. Synchronized Atomic Ledger:
   • When a cashier scans a 6-pack at the register, the system deducts 6 units from the master item's stock pool.
   • When a cashier scans a single can, the system deducts 1 unit.
   • When receiving 10 cases from a distributor invoice, the system adds 240 units (10 cases * 24).
   • No manual "break-pack" transactions are ever required. Shelf restockers simply tear open cardboard and stock the shelf.

2. Multi-Barcode Aliasing & Barcode Mapping

A single sellable liquor item often has multiple valid barcodes:

• The manufacturer updates their packaging with a new UPC.
• The vendor places an internal distributor sticker over the manufacturer UPC.
• Case packaging features an ITF-14 barcode or GS1-128 barcode.
• Custom in-store barcodes for vintage wines or special cellar releases.

Product Barcode Relationship

CREATE TABLE product_barcodes (
    id SERIAL PRIMARY KEY,
    product_id INT NOT NULL REFERENCES products(id) ON DELETE CASCADE,
    barcode VARCHAR(64) UNIQUE NOT NULL,      -- Normalized (stripped of leading/trailing spaces)
    barcode_type VARCHAR(20) DEFAULT 'UPC-A', -- 'UPC-A', 'EAN-13', 'ITF-14', 'CUSTOM'
    is_primary BOOLEAN DEFAULT FALSE,
    notes VARCHAR(100),                       -- e.g. "Distributor Sticker 2026", "New 2026 Can Graphic"
    created_at TIMESTAMPTZ DEFAULT NOW()
);

Fast Barcode Normalization Rules

Before querying the database, scanned strings undergo normalization:

• Strip non-printable ASCII characters.
• If input is 13 digits starting with 0, check both 12-digit UPC-A and full 13-digit EAN-13.
• If input is an ITF-14 case code, determine if it maps directly to a master case pack size.

3. Real-Time Immutable Inventory Ledger (inventory_journal)

Rather than relying solely on a mutable stock_count integer (which provides zero auditability when discrepancies occur), LQPOS implements an Immutable Inventory Journal. Every single addition, deduction, transfer, and adjustment is recorded as an immutable ledger entry.

CREATE TYPE inventory_movement_type AS ENUM (
    'invoice_receipt',        -- Inbound delivery from distributor invoice
    'pos_sale',               -- Register checkout deduction
    'pos_return',             -- Customer return back to sellable stock
    'pos_void',               -- Post-void restoration
    'break_pack_conversion',  -- Manual container disassembly (if tracked separately)
    'shrinkage_damaged',      -- Broken bottle in aisle or crushed can
    'shrinkage_theft',        -- Suspected theft / missing count
    'cycle_count_audit',      -- Physical inventory count adjustment
    'transfer_out'            -- Inter-store or wholesale transfer
);

CREATE TABLE inventory_journal (
    id BIGSERIAL PRIMARY KEY,
    product_id INT NOT NULL REFERENCES products(id),
    movement_type inventory_movement_type NOT NULL,
    quantity_delta INT NOT NULL,              -- Positive for incoming, negative for outgoing (in base units)
    balance_after INT NOT NULL,               -- Snapshot of stock level after movement
    unit_cost_snapshot NUMERIC(10, 4) NOT NULL,
    reference_id VARCHAR(100),                -- e.g. sale_uuid, invoice_number, audit_id
    employee_id INT REFERENCES employees(id),
    notes TEXT,
    created_at TIMESTAMPTZ DEFAULT NOW()
);

Advantages of the Ledger

• Discrepancy Forensics: If inventory shows -4 bottles of Macallan 12, managers can click "View Ledger" to inspect every sale, invoice delivery, and adjustment with exact timestamps and cashier IDs.
• Audit Compliance: Instant calculation of inventory asset value on any historical date for tax and accounting purposes.

4. Mobile Cycle Counting & Stock Auditing

Full physical inventory counts once a year are insufficient to catch breakage and shrinkage in high-volume liquor retail. LQPOS supports continuous Rolling Cycle Counts:

┌────────────────────────────────────────────────────────────────────────┐
│                       MOBILE AUDIT / CYCLE COUNT                       │
├────────────────────────────────────────────────────────────────────────┤
│ Category: BOURBON & WHISKEY (Aisle 3)                                  │
│ Auditor: Kevin B. | Mode: Blind Count                                  │
├────────────────────────────────────────────────────────────────────────┤
│ [Scan Barcode] ──► 088004000305                                        │
│ Item: Tito's Handmade Vodka 1.75L                                      │
│ Physical Count: [ 18 ] Bottles                                         │
│ System Expected: [HIDDEN - Blind Mode Active]                          │
├────────────────────────────────────────────────────────────────────────┤
│ [Submit & Next Item]                                                   │
└────────────────────────────────────────────────────────────────────────┘

Audit Best Practices

1. Blind Counting Mode: The employee counting the shelf is never shown what the system expects to find. Showing the expected quantity encourages counters to guess or skip physically counting every bottle.
2. Discrepancy Thresholds:
   • Discrepancies under $20 cost variance are queued for manager review.
   • Discrepancies over $50 (or high-dollar bottles like premium tequila and single malt scotch) trigger immediate notification.
3. Delta Commit: Once reviewed by the store manager, the audit commits a single cycle_count_audit row to the inventory_journal, updating current stock to the verified physical count.

5. Sales Velocity & Automated Purchase Order (PO) Drafting

In a liquor store, running out of top-selling domestic beer or vodka on a Friday afternoon directly forfeits revenue.

Dynamic Reorder Formula

interface ReorderCalculation {
  leadTimeDays: number;         // Days between PO submission and physical delivery (e.g. 3 days)
  reviewPeriodDays: number;     // Days until the next distributor delivery window (e.g. 7 days)
  safetyStockDays: number;      // Buffer days for unexpected demand spikes (e.g. 2 days)
  salesVelocityPerDay: number;  // Weighted 30-day average daily sales (higher weight on Fri-Sun)
}

function calculateSuggestedOrder(stockOnHand: number, params: ReorderCalculation): number {
  const targetDays = params.leadTimeDays + params.reviewPeriodDays + params.safetyStockDays;
  const targetInventory = Math.ceil(targetDays * params.salesVelocityPerDay);
  const neededUnits = targetInventory - stockOnHand;
  
  return neededUnits > 0 ? neededUnits : 0;
}

Automated Distributor PO Generation

• Distributor Grouping: Generates distinct purchase orders segmented by distributor delivery schedules:
  • MolsonCoors Distributor (Tuesday delivery)
  • Anheuser-Busch Distributor (Wednesday delivery)
  • Breakthru Beverage / Wine (Thursday delivery)
  • Southern Glazer's Wine & Spirits (Thursday delivery)
• Full Case Rounding: Automatically rounds suggested bottle quantities up to full case increments if distributor ordering requires full case minimums.