04 — Automated Distributor Invoice Ingestion & Pricing Engine

1. The Core Bottleneck: Weekly Distributor Inbound Deliveries

An independent liquor store processes between 300 and 1,000 cases of alcoholic beverages every week across 4 to 8 major distributors and dozens of local craft breweries.

Why Manual Receiving Destroys Profits

1. Unnoticed Cost Creep: Distributors frequently raise case prices by $0.75 to $2.50 without prior warning. If the store continues selling at the old retail price, gross margins steadily erode.
2. Data Entry Fatigue: Keying in invoice line items by hand takes 10 to 15 hours per week of manager time and leads to catalog corruption, typos, and mismatched UPCs.
3. Split Cases & Quantity Allowances: Distributor invoices contain complex discount lines ("Level 3 Deal: -$2.00/case when ordering 10+ cases", "Distributor Billback allowance", "Bottle deposit $1.20/case").

2. Pluggable Ingestion Pipeline Architecture

LQPOS establishes an automated ingestion pipeline capable of parsing raw electronic files from the two industry-standard platforms as well as direct distributor files:

┌────────────────────────────────────────────────────────────────────────┐
│                        RAW INVOICE SOURCE FILES                        │
│ • Fintech: Electronic Payment & Data feed (CSV / EDI 810)              │
│ • DSDLink: Direct Store Delivery portal (CSV / Structured PDF)         │
│ • Direct Wholesalers: Breakthru, Southern Glazer's, RNDC CSV/XLSX      │
└───────────────────────────────────▲────────────────────────────────────┘
                                    │ Upload (Drag-and-Drop or Email Webhook)
┌───────────────────────────────────▼────────────────────────────────────┐
│                    INGESTION & NORMALIZATION WORKER                    │
├────────────────────────────────────────────────────────────────────────┤
│ 1. File Hash Check: Prevent duplicate import via SHA-256               │
│ 2. Adapter Selection: Detect Fintech vs DSDLink vs Generic CSV         │
│ 3. Row Normalization: Parse SKU, UPC, Pack, Case Cost, Net Unit Cost   │
└───────────────────────────────────▲────────────────────────────────────┘
                                    │
┌───────────────────────────────────▼────────────────────────────────────┐
│                       TIERED MATCHING ENGINE                           │
├────────────────────────────────────────────────────────────────────────┤
│ Tier 1: Exact Barcode Match (UPC-A / EAN-13 / ITF-14)                  │
│ Tier 2: Vendor SKU + Vendor Code Match                                 │
│ Tier 3: Normalized Description + Pack Size Fuzzy Match                 │
└───────────────────────────────────▲────────────────────────────────────┘
                                    │
┌───────────────────────────────────▼────────────────────────────────────┐
│                  MARGIN & VARIANCE PRICING ENGINE                      │
├────────────────────────────────────────────────────────────────────────┤
│ • Compare Invoiced Unit Cost vs Stored Replacement Cost                │
│ • Calculate Suggested Retail = New Cost / (1 - Target Margin %)        │
│ • Apply Store Rounding Rules (e.g. Snap to $.99 or $.49)               │
│ • Flag Cost Increases > 1.5% for Mandatory Review                      │
└───────────────────────────────────▲────────────────────────────────────┘
                                    │
┌───────────────────────────────────▼────────────────────────────────────┐
│                INTERACTIVE STAGING & APPROVAL INTERFACE                │
│             (Visual Diff, Margin Simulation, Batch Commit)             │
└────────────────────────────────────────────────────────────────────────┘

3. Supported File Format Specifications

1. Fintech Electronic Data Feed (CSV / EDI 810)

Fintech is the primary clearinghouse for alcohol electronic funds transfer (EFT) in the United States.

• Fields Extracted:
  • Header: InvoiceNumber, InvoiceDate, DistributorAccountID, TotalInvoiceAmount, TermsCode
  • Line Items: ItemCode, UPC, Description, PackSize, Volume, QuantityCases, CaseCost, DepositAmount, NetExtendedCost
• Handling Allowances: Net cost is derived by subtracting line-item discounts and promotional bill-backs from gross case price.

2. DSDLink (VIP Platform Export)

DSDLink is the primary retail ordering and delivery portal for MolsonCoors, Anheuser-Busch, and independent craft beer wholesalers.

• Fields Extracted:
  • Header: DistributorName, OrderRef, DeliveryDate, CustomerNumber
  • Line Items: ItemNumber, GTIN_UPC, BrandName, ItemDescription, Pack_Unit, CasesDelivered, UnitPrice, DepositTotal

4. Tiered Matching Algorithm

Matching invoiced items to the store catalog must be accurate to avoid creating duplicate products.

interface InvoicedItem {
  vendorSku: string;
  upc: string | null;
  description: string;
  packSize: number;
  invoicedCaseCost: number;
}

async function matchInvoicedItem(item: InvoicedItem, vendorId: number): Promise<MatchResult> {
  // Step 1: Exact Primary or Secondary Barcode Match
  if (item.upc) {
    const cleanUpc = normalizeBarcode(item.upc);
    const barcodeMatch = await db.query(
      `SELECT p.* FROM products p 
       JOIN product_barcodes pb ON p.id = pb.product_id 
       WHERE pb.barcode = $1 LIMIT 1`,
      [cleanUpc]
    );
    if (barcodeMatch.rows.length > 0) {
      return { status: 'EXACT_UPC_MATCH', product: barcodeMatch.rows[0] };
    }
  }

  // Step 2: Vendor SKU + Vendor ID Match
  const skuMatch = await db.query(
    `SELECT p.* FROM products p 
     JOIN vendor_items vi ON p.id = vi.product_id 
     WHERE vi.vendor_sku = $1 AND vi.vendor_id = $2 LIMIT 1`,
    [item.vendorSku, vendorId]
  );
  if (skuMatch.rows.length > 0) {
    return { status: 'VENDOR_SKU_MATCH', product: skuMatch.rows[0] };
  }

  // Step 3: Fuzzy Description Matching (Threshold > 0.85)
  const fuzzyMatches = await findFuzzyNameMatches(item.description, vendorId);
  if (fuzzyMatches.length > 0 && fuzzyMatches[0].confidence >= 0.85) {
    return { status: 'FUZZY_SUGGESTION', suggestedProduct: fuzzyMatches[0].product };
  }

  // Step 4: Truly New Product (Needs Staging)
  return { status: 'UNMATCHED_NEW_PRODUCT', product: null };
}

5. Automated Margin Protection & Price Calculation

When a distributor raises the cost of an item, LQPOS automatically computes the suggested retail price to prevent margin degradation.

Pricing Rules Engine

1. Target Margin Formula: $$\text{Retail Price} = \frac{\text{New Unit Cost}}{1 - \text{Category Target Margin %}}$$
   • Example: Bourbon category has a target margin of $30%$.
   • Old Cost: $$21.00 \rightarrow$ Retail: $$29.99$ ($30.0%$ margin)
   • Invoiced Cost increases to $$23.50$:
   • Suggested Retail: $\frac{$23.50}{1 - 0.30} = $33.57$
2. Psychological Price Snapping (Rounding Conventions):
   • Stores rarely sell liquor at arbitrary pennies like $$33.57$. The engine snaps to defined price points:
     • Standard: Round to nearest $.99 (e.g. $$33.57 \rightarrow $33.99$)
     • Economy/Beer: Round to nearest $.49 or $.99
     • Premium/Allocated: Round to nearest full dollar $0.00 (e.g. $$149.00$)
3. Margin Lock Guardrails:
   • Products marked margin_locked = true will automatically adopt the new snapped retail price without requiring manual approval, saving hours of review time on routine commodity beer price changes.

6. Staging & Approval Workflow

┌────────────────────────────────────────────────────────────────────────────────────────┐
│ INVOICE STAGING #INV-89104 | Vendor: Breakthru Beverage | Date: Nov 05, 2026           │
├────────────────────────────────────────────────────────────────────────────────────────┤
│ [✓] 44 Items Matched & Verified (Unchanged Cost)                                       │
│ [!]  3 Items with Cost Increases Detected                                              │
│ [?]  2 New Unmatched Items Requiring Categorization                                    │
├────────────────────────────────────────────────────────────────────────────────────────┤
│ COST INCREASES REQUIRING REVIEW:                                                       │
│ 1. Buffalo Trace Bourbon 750ml                                                         │
│    Old Cost: $18.50 ──► New Cost: $19.85 (+7.3%)                                       │
│    Current Retail: $25.99 (Margin was 28.8%) ──► Suggested: $27.99 (Margin 29.1%)      │
│    Action: [✓ Accept $27.99]  [Custom: $____.__]  [Keep $25.99 (Erode Margin)]         │
│                                                                                        │
│ 2. Kendall Jackson Vintners Reserve Chardonnay 750ml                                   │
│    Old Cost: $9.20 ──► New Cost: $9.95 (+8.1%)                                         │
│    Current Retail: $12.99 ──► Suggested: $13.99                                        │
│    Action: [✓ Accept $13.99] [Custom: $____.__]  [Keep $12.99]                         │
├────────────────────────────────────────────────────────────────────────────────────────┤
│ [Simulate Overall Basket Margin Impact]    [Commit & Update Stock]   [Save Draft]      │
└────────────────────────────────────────────────────────────────────────────────────────┘

Commit Actions & Atomic Safety

• Atomic Ledger Entry: When the manager clicks [Commit & Update Stock]:
  1. All matched products have their stock increased by their respective case multipliers in the inventory_journal.
  2. Products with approved price updates receive a record in price_change_history.
  3. The raw file and parsed line items are archived.
  4. The invoice status transitions from pending_review to committed.
• 72-Hour Rollback Mechanism: If an incorrect invoice was accidentally approved (e.g. distributor delivered the wrong order), a manager can click [Rollback Invoice]. The system automatically subtracts the received inventory units and restores previous retail prices and replacement costs.