07 — Data Model, REST API & WebSocket Contracts Specification

1. Normalized Relational Entity Relationship (PostgreSQL 16)

┌──────────────────────┐         ┌──────────────────────┐         ┌──────────────────────┐
│       vendors        │         │      categories      │         │ promotional_rules    │
├──────────────────────┤         ├──────────────────────┤         ├──────────────────────┤
│ id (PK)              │◄──┐     │ id (PK)              │◄──┐     │ id (PK)              │
│ name                 │   │     │ name                 │   │     │ name                 │
│ short_code           │   │     │ parent_id (FK)       │   │     │ discount_type        │
│ edi_identifier       │   │     │ default_markup_pct   │   │     │ discount_value       │
└──────────────────────┘   │     │ tax_type             │   │     │ target_category_id   │
                           │     └──────────────────────┘   │     │ min_quantity         │
                           │                                │     └──────────────────────┘
                           │     ┌──────────────────────┐   │
                           │     │       products       │   │
                           │     ├──────────────────────┤   │
                           ├─────┤ id (PK)              │   │
                           │     │ parent_product_id(FK)├───┤ (Self-referencing for packs)
                           │     │ name                 │   │
                           │     │ category_id (FK)     ├───┘
                           │     │ uom (enum)           │
                           │     │ pack_quantity        │
                           │     │ current_cost         │
                           │     │ retail_price         │
                           │     │ target_markup_pct    │
                           │     │ stock_master_units   │
                           │     └──────────▲───────────┘
                           │                │
            ┌──────────────┴────────┐       │ 1:N
            │                       │       ▼
            ▼                       │ ┌──────────────────────┐
┌──────────────────────┐            │ │   product_barcodes   │
│       invoices       │            │ ├──────────────────────┤
├──────────────────────┤            │ │ id (PK)              │
│ id (PK)              │            │ │ product_id (FK)      │
│ vendor_id (FK)       │            │ │ barcode (UNIQUE)     │
│ invoice_number       │            │ │ barcode_type         │
│ invoice_date         │            │ │ is_primary           │
│ status               │            │ └──────────────────────┘
│ total_amount         │            │
└──────────┬───────────┘            │
           │ 1:N                    │ 1:N
           ▼                        ▼
┌──────────────────────┐  ┌───────────────────────────┐
│    invoice_items     │  │     inventory_journal     │
├──────────────────────┤  ├───────────────────────────┤
│ id (PK)              │  │ id (BIGPK)                │
│ invoice_id (FK)      │  │ product_id (FK)           │
│ product_id (FK)      │  │ movement_type (enum)      │
│ vendor_sku           │  │ quantity_delta            │
│ unit_cost            │  │ balance_after             │
│ extended_cost        │  │ unit_cost_snapshot        │
│ suggested_retail     │  │ reference_id              │
└──────────────────────┘  └───────────────────────────┘

┌──────────────────────┐  ┌───────────────────────────┐  ┌───────────────────────────┐
│      employees       │  │           sales           │  │        sale_items         │
├──────────────────────┤  ├───────────────────────────┤  ├───────────────────────────┤
│ id (PK)              │  │ id (PK)                   │  │ id (PK)                   │
│ first_name           │  │ sale_uuid (UUID)          │  │ sale_id (FK)              │
│ pin_hash             │  │ register_lane             │  │ product_id (FK)           │
│ role                 │  │ employee_id (FK)          │  │ quantity                  │
└──────────┬───────────┘  │ customer_id (FK)          │  │ unit_price                │
           │              │ subtotal                  │  │ cost_at_sale              │
           │ 1:N          │ discount_total            │  │ discount_amount           │
           ▼              │ tax_total                 │  │ line_total                │
┌──────────────────────┐  │ grand_total               │  └───────────────────────────┘
│   daily_registers    │  │ age_check_method          │  ┌───────────────────────────┐
├──────────────────────┤  │ age_audit_hash            │  │       sale_payments       │
│ id (PK)              │  │ synced_from_offline       │  ├───────────────────────────┤
│ lane_number          │  └─────────────┬─────────────┘  │ id (PK)                   │
│ opened_by (FK)       │                │ 1:N            │ sale_id (FK)              │
│ opening_float        │                ▼                │ method (enum)             │
│ counted_cash         │      ┌───────────────────┐      │ amount                    │
│ variance             │      │ sale_items        │      │ transaction_ref           │
└──────────────────────┘      └───────────────────┘      └───────────────────────────┘

2. Core REST API Endpoint Specifications

All endpoints return standard JSON envelopes with request tracing headers:

A. Cashier Register APIs

1. Instant Barcode Lookup & Normalization

• POST /api/v1/pos/scan
• Request:

  {
    "raw_barcode": "071990300174",
    "cart_context": {
      "item_count": 2,
      "categories": [ "Wine" ]
    }
  }
  

• Response (200 OK):

  {
    "product": {
      "id": 1420,
      "name": "Coors Light 12oz Cans 24-Pack Case",
      "brand": "Coors",
      "retail_price": 23.99,
      "uom": "case_24",
      "pack_quantity": 24,
      "age_required": 21,
      "tax_rate_pct": 7.5,
      "deposit_amount": 0.00,
      "stock_on_hand_units": 72
    },
    "dynamic_rules": {
      "eligible_promotions": []
    }
  }
  

2. Driver's License Age Verification

• POST /api/v1/pos/verify-age
• Request:

  {
    "raw_pdf417": "@\n\u001e\rANSI 636000080002DL00410287...DBB19950412...DBA20280412..."
  }
  

• Response (200 OK):

  {
    "status": "APPROVED_21+",
    "calculated_age": 31,
    "birthdate": "1995-04-12",
    "expiration_date": "2028-04-12",
    "is_expired": false,
    "audit_hash": "e3b0c44298fc1c149afbf4c8996fb92427ae41e4649b934ca495991b7852b855"
  }
  

3. Transaction Finalization & Payment Commit

• POST /api/v1/pos/checkout
• Header: Idempotency-Key: c9d8e7b6-1234-4567-89ab-cdef01234567
• Request:

  {
    "lane_number": 1,
    "employee_id": 4,
    "customer_id": null,
    "age_audit": {
      "method": "pdf417_scanned",
      "hash": "e3b0c44298fc1c149afbf4c8996fb92427ae41e4649b934ca495991b7852b855"
    },
    "items": [
      {
        "product_id": 1420,
        "quantity": 1,
        "unit_price": 23.99,
        "discount_amount": 0.00
      }
    ],
    "payments": [
      {
        "method": "cash",
        "amount": 23.99,
        "tendered": 30.00,
        "change_due": 6.01
      }
    ],
    "summary": {
      "subtotal": 23.99,
      "discount_total": 0.00,
      "tax_total": 1.80,
      "deposit_total": 0.00,
      "grand_total": 25.79
    }
  }
  

B. Automated Invoice Ingestion APIs

1. Ingest Raw Electronic Invoice

• POST /api/v1/invoices/upload
• Content-Type: multipart/form-data (file: fintech_20261105.csv or dsdlink_order.pdf)
• Response (202 Accepted):

  {
    "invoice_id": 892,
    "vendor": "Breakthru Beverage",
    "invoice_number": "INV-78401",
    "file_hash": "a1b2c3d4...",
    "detected_format": "Fintech_CSV",
    "status": "processing_parsing"
  }
  

2. Get Staged Invoice Diff & Margin Simulation

• GET /api/v1/invoices/892/diff
• Response (200 OK):

  {
    "invoice_id": 892,
    "total_lines": 38,
    "matched_count": 36,
    "unmatched_count": 2,
    "cost_increases_count": 3,
    "items": [
      {
        "vendor_sku": "014294",
        "product_id": 512,
        "name": "Tito's Handmade Vodka 1.75L",
        "case_pack": 6,
        "cases_received": 10,
        "new_unit_cost": 24.50,
        "old_unit_cost": 22.80,
        "cost_change_pct": 7.45,
        "current_retail": 31.99,
        "current_margin_pct": 28.7,
        "suggested_retail": 34.99,
        "suggested_margin_pct": 29.9
      }
    ]
  }
  

3. Batch Commit Invoice

• POST /api/v1/invoices/892/commit
• Request:

  {
    "approved_price_updates": [
      { "product_id": 512, "new_retail_price": 34.99 }
    ],
    "created_products": [
      {
        "vendor_sku": "99401",
        "upc": "850012345678",
        "name": "New Craft Double Hazy IPA 16oz 4pk",
        "category_id": 4,
        "retail_price": 14.99,
        "pack_quantity": 4
      }
    ]
  }
  

3. Real-Time WebSocket Event Bus Contracts

Registers and Backoffice nodes subscribe to WebSocket channels on the In-Store Edge Server (ws://192.168.1.100:9120/ws/v1):

// 1. Remote Cart Mirroring (Customer Facing Display)
interface WSCartUpdateEvent {
  event: 'CART_UPDATED';
  lane_number: number;
  payload: {
    items: Array<{ name: string; qty: number; price: number; line_total: number }>;
    subtotal: number;
    discounts: number;
    tax: number;
    total: number;
  };
}

// 2. Real-Time Price Broadcast (Immediate lane sync after invoice approval)
interface WSPriceBroadcastEvent {
  event: 'PRICE_CHANGED';
  payload: {
    product_id: number;
    barcode: string;
    new_retail_price: number;
    effective_at: string;
  };
}

// 3. Low Stock / Stock Exhaustion Alert
interface WSStockAlertEvent {
  event: 'STOCK_DEPLETED';
  payload: {
    product_id: number;
    name: string;
    remaining_units: number;
  };
}