Order Service Proposal

# Order/Transaction Service Proposal
 
**Status:** Draft
**Author:** Claude Code + Dan
**Date:** 2026-02-05
 
---
 
## Overview
 
RGL8R needs a unified Order/Transaction service that serves two complementary use cases:
 
1. **Pre-Order (Real-Time):** Compliance check and landed cost estimate before accepting an order
2. **Post-Order (Batch):** Order data ingestion for audit linkage and SKU-level analytics
 
Both use cases share the same underlying data (catalog, compliance rules, rates) but differ in timing and interface.
 
---
 
## Use Cases
 
### Pre-Order: Real-Time Quote API
 
**User Story:** As an importer, I want to check compliance and estimate landed cost before accepting an order, so I can make informed decisions and avoid surprises.
 
**Trigger:** Customer's checkout flow, order management system, or manual check
 
**Input:**
```typescript
interface QuoteRequest {
  skus: Array<{
    sku: string
    quantity: number
    unitValue?: number  // For duty calculation
  }>
  origin: {
    country: string
    postalCode?: string
  }
  destination: {
    country: string
    postalCode?: string
  }
  carrier?: string      // Optional: for shipping estimate
  serviceLevel?: string // Optional: for shipping estimate
}
```
 
**Output:**
```typescript
interface QuoteResponse {
  requestId: string
  timestamp: string
 
  // Compliance screening
  compliance: {
    trade: {
      atRisk: Array<{
        sku: string
        measureCode: string
        measureName: string
        estimatedDuty: number
        confidence: number
      }>
      needsReview: Array<{ sku: string; reason: string }>
      cleared: string[]
    }
    restrictions: Array<{
      sku: string
      type: 'PROHIBITED' | 'RESTRICTED' | 'LICENSED'
      description: string
    }>
  }
 
  // Cost estimates (when carrier/service provided)
  estimates?: {
    shipping: number
    duties: number
    taxes: number
    fees: number
    landedCost: number
    currency: string
  }
 
  // Actionable warnings
  warnings: string[]
 
  // Evidence for audit trail
  evidence: {
    simaConfigVersion: string
    catalogDataAsOf: string
    rateDataAsOf?: string
  }
}
```
 
**Endpoint:** `POST /api/orders/quote`
 
**SLA:** < 500ms response time (cached catalog + rules)
 
---
 
### Post-Order: Batch Ingestion
 
**User Story:** As an importer, I want to upload my order history so RGL8R can link shipments to SKUs and provide SKU-level analytics and catalog fix recommendations.
 
**Trigger:** Manual CSV upload or scheduled sync
 
**Input Format (CSV):**
```csv
order_number,sku,quantity,tracking_number,ship_date,carrier,destination_country
ORD-12345,ABC123,2,1Z999AA10123456784,2026-01-15,UPS,US
ORD-12345,XYZ789,1,1Z999AA10123456784,2026-01-15,UPS,US
```
 
**Processing:**
1. Parse and validate CSV
2. Upsert into `order_line_items` table
3. Link to existing shipments via `tracking_number`
4. Trigger SKU-level finding enrichment
5. Generate catalog mismatch recommendations
 
**Endpoint:** `POST /api/orders/upload`
 
**Output:**
```typescript
interface OrderUploadResponse {
  jobId: string
  status: 'pending' | 'processing' | 'completed' | 'failed'
  stats: {
    rowsProcessed: number
    rowsSkipped: number
    ordersCreated: number
    shipmentsLinked: number
    skuMismatchesFound: number
  }
}
```
 
---
 
## Data Model
 
### Core Tables
 
```prisma
// Order header (one per order)
model Order {
  id              String    @id @default(dbgenerated("gen_random_uuid()")) @db.Uuid
  tenantId        String    @map("tenant_id") @db.Uuid
  orderNumber     String    @map("order_number") @db.VarChar(100)
  orderDate       DateTime? @map("order_date") @db.Date
  sourceSystem    String?   @map("source_system") @db.VarChar(50) // shopify, wms, manual
  customerRef     String?   @map("customer_ref") @db.VarChar(100)
  destinationCountry String? @map("destination_country") @db.VarChar(2)
  destinationPostal  String? @map("destination_postal") @db.VarChar(20)
  metadata        Json?
  createdAt       DateTime  @default(now()) @map("created_at")
  updatedAt       DateTime  @updatedAt @map("updated_at")
 
  lineItems       OrderLineItem[]
 
  @@unique([tenantId, sourceSystem, orderNumber], name: "tenant_source_order")
  @@index([tenantId, orderDate])
  @@map("orders")
  @@schema("app")
}
 
// Order line items (SKU-level)
model OrderLineItem {
  id              String    @id @default(dbgenerated("gen_random_uuid()")) @db.Uuid
  tenantId        String    @map("tenant_id") @db.Uuid
  orderId         String    @map("order_id") @db.Uuid
  sku             String    @db.VarChar(100)
  quantity        Int       @default(1)
  unitValue       Decimal?  @map("unit_value") @db.Decimal(12, 2)
  trackingNumber  String?   @map("tracking_number") @db.VarChar(100)
  shipDate        DateTime? @map("ship_date") @db.Date
  carrier         String?   @db.VarChar(50)
  serviceLevel    String?   @map("service_level") @db.VarChar(100)
  metadata        Json?
  createdAt       DateTime  @default(now()) @map("created_at")
 
  order           Order     @relation(fields: [orderId], references: [id], onDelete: Cascade)
 
  @@unique([orderId, sku, trackingNumber], name: "order_sku_tracking")
  @@index([tenantId, sku])
  @@index([tenantId, trackingNumber])
  @@map("order_line_items")
  @@schema("app")
}
 
// Quote requests (audit trail for pre-order checks)
model QuoteRequest {
  id              String    @id @default(dbgenerated("gen_random_uuid()")) @db.Uuid
  tenantId        String    @map("tenant_id") @db.Uuid
  requestPayload  Json      @map("request_payload")
  responsePayload Json?     @map("response_payload")
  durationMs      Int?      @map("duration_ms")
  errorMessage    String?   @map("error_message")
  createdAt       DateTime  @default(now()) @map("created_at")
 
  @@index([tenantId, createdAt])
  @@map("quote_requests")
  @@schema("app")
}
```
 
### Linkage Updates
 
Add to existing `Shipment` model:
```prisma
// In Shipment model, add:
orderLineItems  OrderLineItem[] @relation("ShipmentOrderLines")
```
 
Add to existing `Finding` model:
```prisma
// In Finding model, add:
sku             String?   @db.VarChar(100)
orderNumber     String?   @map("order_number") @db.VarChar(100)
catalogMismatch Json?     @map("catalog_mismatch") // { field: 'weight', catalog: 10, billed: 15 }
```
 
---
 
## API Endpoints
 
| Endpoint | Method | Purpose |
|----------|--------|---------|
| `/api/orders/quote` | POST | Real-time compliance/cost check |
| `/api/orders/upload` | POST | Batch order data ingestion |
| `/api/orders` | GET | List orders with filters |
| `/api/orders/:id` | GET | Order detail with line items + linked shipments |
| `/api/orders/sku-summary` | GET | SKU-level cost/finding aggregation |
 
---
 
## Processing Flow
 
### Pre-Order Quote Flow
 
```
Request → Validate SKUs exist in catalog
        → Check SIMA coverage for each SKU
        → Check restrictions (future: denied party, embargoes)
        → Calculate duty estimates (if destination provided)
        → Calculate shipping estimate (if carrier provided)
        → Log request for audit trail
        → Return response
```
 
**Performance considerations:**
- Cache SIMA rules in memory (reload on config change)
- Cache catalog data per tenant (TTL-based)
- No DB writes in hot path (log async)
 
### Post-Order Ingestion Flow
 
```
Upload CSV → Create Job
          → Parse rows
          → Upsert Order + OrderLineItems
          → Match tracking_number → Shipment
          → For each linked shipment:
              → Look up SKU catalog data (SkuAttribute)
              → Compare catalog dims/weight vs billed
              → If mismatch: create CATALOG_DATA_MISMATCH finding
              → Enrich existing findings with SKU
          → Generate summary stats
          → Complete job
```
 
---
 
## Catalog Mismatch Detection
 
When order data links a shipment to SKUs, compare:
 
| Field | Catalog Source | Billed Source | Finding Type |
|-------|---------------|---------------|--------------|
| Weight | SkuAttribute.attributes.weight | Shipment.billedWeight | WEIGHT_MISMATCH |
| Length | SkuAttribute.attributes.length | Shipment.billedLength | DIM_MISMATCH |
| Width | SkuAttribute.attributes.width | Shipment.billedWidth | DIM_MISMATCH |
| Height | SkuAttribute.attributes.height | Shipment.billedHeight | DIM_MISMATCH |
 
**Output:** Two recommendations per mismatch:
1. **Carrier dispute** (if catalog is correct): "Carrier billed 15 lbs, catalog shows 10 lbs"
2. **Catalog fix** (if carrier is correct): "Update SKU ABC123 weight from 10 lbs → 15 lbs"
 
The system flags for review; human decides which is correct.
 
---
 
## Integration with Existing Modules
 
### SHIP Integration
- OrderLineItem.trackingNumber → Shipment.trackingNumber
- Enables SKU-level cost allocation
- Enables catalog mismatch detection
 
### TRADE Integration
- Quote API uses SIMA rules from `packages/modules/trade`
- Order destination triggers compliance check
- Pre-order warnings surface SIMA exposure before commitment
 
### Catalog Integration
- SkuAttribute table provides dims/weight for comparison
- AttributeValidation provides confidence scores
- Mismatches feed back into catalog improvement loop
 
---
 
## Implementation Phases
 
### Phase A: Post-Order Ingestion (Week 1)
- [ ] Add Order, OrderLineItem to Prisma schema
- [ ] Add sku, orderNumber to Finding model
- [ ] Create `POST /api/orders/upload` endpoint
- [ ] Implement CSV parsing and upsert
- [ ] Implement tracking → shipment linkage
- [ ] Implement SKU enrichment on existing findings
 
### Phase B: Catalog Mismatch Detection (Week 2)
- [ ] Add catalogMismatch field to Finding
- [ ] Implement comparison logic (catalog vs billed)
- [ ] Create CATALOG_DATA_MISMATCH finding type
- [ ] Surface in /audit endpoint with recommendation
 
### Phase C: Pre-Order Quote API (Week 3)
- [ ] Add QuoteRequest table for audit trail
- [ ] Create `POST /api/orders/quote` endpoint
- [ ] Implement SIMA check integration
- [ ] Implement response caching
- [ ] Add rate limiting
 
### Phase D: UI Integration (Week 4)
- [ ] Order list view
- [ ] SKU cost summary view
- [ ] Catalog mismatch recommendations UI
- [ ] Quote API playground/tester
 
---
 
## Open Questions
 
1. **Multi-SKU shipments:** How to allocate shipment costs across SKUs?
   - Option A: Equal split
   - Option B: Proportional by weight
   - Option C: Proportional by value
   - **Recommendation:** Proportional by quantity (simplest, matches v1.2 addendum)
 
2. **Catalog source of truth:** When catalog ≠ billed, which is right?
   - **Recommendation:** Flag for human review, don't auto-correct either
 
3. **Quote API authentication:** Same JWT auth or lighter API key for real-time?
   - **Recommendation:** Same auth, add rate limiting
 
4. **Order source systems:** Support direct integrations (Shopify, etc.) or CSV-only for MVP?
   - **Recommendation:** CSV-only for March, integrations in Q2
 
---
 
## References
 
- `specs/rgl8r-ship-module-prd-v1.2-addendum.md` - SKU analytics, order ingestion spec
- `specs/rgl8r-ship-rate-calc-v3.md` - Rate calculation engine (Phase 2)
- `packages/modules/trade/src/sima.ts` - SIMA validation logic
- `apps/api/prisma/schema.prisma` - Current schema