Developer Handover Pack

Developer Handover Pack

3PL / Warehouse Coordination Automation

This agent is the entry point of the automation. It connects to the ShipBob API on both a daily schedule and an event-driven webhook trigger, pulls current inventory levels, inbound receipt statuses, and outbound dispatch records, then normalises all SKU references against the internal SKU mapping table before passing a clean, structured dataset downstream to the Reconciliation and Discrepancy Agent. It does not write to any system; its sole responsibility is clean data delivery. Estimated build time: 10 hours. Complexity: Moderate.

// Input
trigger_type: 'schedule' | 'webhook'
webhook_event?: 'inventory_updated' | 'receiving_order_created' | 'outbound_order_fulfilled'
webhook_payload?: ShipBobWebhookPayload
schedule_date: ISO8601 date string

// ShipBob API calls made by this agent
GET /v2/inventory          -> raw_inventory_lines[]
GET /v2/receiving-orders   -> raw_asn_lines[]
GET /v2/orders?status=Fulfilled -> raw_outbound_lines[]

// SKU normalisation (mapping table lookup)
shipbob_sku        -> internal_sku   (via Google Sheets SKU mapping table)
shipbob_location   -> warehouse_code
shipbob_quantity   -> quantity_on_hand (integer)
shipbob_asn_status -> asn_status: 'confirmed' | 'pending' | 'missing'

// Output
normalised_dataset: {
  run_id: UUID,
  run_timestamp: ISO8601,
  trigger_type: string,
  inventory_lines: [
    {
      internal_sku: string,
      shipbob_sku: string,
      warehouse_code: string,
      quantity_on_hand: integer,
      asn_status: string,
      asn_id?: string,
      last_updated: ISO8601
    }
  ],
  unmapped_skus: string[]   // SKUs with no mapping table entry
}

This agent receives the normalised dataset from the Data Fetch agent and performs the core reconciliation logic. It calls the Cin7 API to retrieve live inventory counts for each internal SKU, compares them line by line against the ShipBob-sourced quantities, and classifies each SKU as matched, minor variance, or major discrepancy based on configured threshold values agreed with the operations manager. It also flags any SKU with an asn_status of missing or pending beyond the agreed age threshold. Results are written as a new row to the Google Sheets reconciliation log, and the classified output is passed to the Update and Notification Agent. Estimated build time: 16 hours. Complexity: Complex.

// Input
run_id: UUID
run_timestamp: ISO8601
inventory_lines: [
  { internal_sku, shipbob_sku, warehouse_code, quantity_on_hand, asn_status, asn_id?, last_updated }
]
unmapped_skus: string[]

// Cin7 API call per SKU
GET /v1/inventory?sku={internal_sku}  ->  cin7_quantity_on_hand: integer
                                      ->  cin7_product_id: string
                                      ->  cin7_last_modified: ISO8601

// Classification logic
variance = shipbob_quantity_on_hand - cin7_quantity_on_hand
variance_pct = abs(variance) / cin7_quantity_on_hand
classification:
  'matched'           if variance_pct <= minor_threshold (default 2%)
  'minor_variance'    if minor_threshold < variance_pct <= major_threshold (default 10%)
  'major_discrepancy' if variance_pct > major_threshold OR abs(variance) > major_unit_threshold (default 10 units)
  'missing_asn'       if asn_status == 'missing' OR (asn_status == 'pending' AND age > asn_age_threshold_hours, default 24)

// Output (passed to Update and Notification Agent)
reconciliation_result: {
  run_id: UUID,
  run_timestamp: ISO8601,
  classified_lines: [
    {
      internal_sku: string,
      shipbob_sku: string,
      cin7_product_id: string,
      warehouse_code: string,
      shipbob_qty: integer,
      cin7_qty: integer,
      variance: integer,
      variance_pct: float,
      classification: 'matched' | 'minor_variance' | 'major_discrepancy' | 'missing_asn',
      asn_id?: string,
      asn_status: string
    }
  ],
  unmapped_skus: string[],
  total_skus_processed: integer,
  matched_count: integer,
  minor_variance_count: integer,
  major_discrepancy_count: integer,
  missing_asn_count: integer
}

// Google Sheets append (reconciliation log)
Sheet: 'Reconciliation Log'
Row fields: run_id, run_timestamp, total_skus_processed, matched_count,
            minor_variance_count, major_discrepancy_count, missing_asn_count,
            unmapped_skus (comma-separated), status: 'complete' | 'has_exceptions'

This agent acts on the classified reconciliation results. For all SKUs classified as matched or minor variance, it applies the confirmed stock quantity directly to Cin7 via the inventory adjustment endpoint and then pushes the updated level to Shopify per product variant. For all SKUs classified as major discrepancy or missing ASN, it posts a structured alert to the designated Slack fulfilment channel instead of auto-correcting. At the end of each run it assembles and posts a daily summary message to the Slack fulfilment channel covering all SKUs processed, variances found, adjustments made, and open exceptions. Estimated build time: 12 hours. Complexity: Moderate.

// Input
reconciliation_result: { ... }  // full object from Reconciliation Agent

// Branch A: auto-update (matched and minor_variance SKUs)
POST /v1/inventory/adjustments  (Cin7)
  body: { productId: cin7_product_id, quantity: shipbob_qty, note: 'Auto-reconciled by automation run_id {run_id} at {run_timestamp}' }

GET /admin/api/2024-01/variants.json?sku={internal_sku}  (Shopify)
  -> variant_id, inventory_item_id, location_id
POST /admin/api/2024-01/inventory_levels/set.json  (Shopify)
  body: { location_id, inventory_item_id, available: shipbob_qty }

// Branch B: exception alert (major_discrepancy and missing_asn SKUs)
POST https://slack.com/api/chat.postMessage
  channel: '#fulfilment-ops'
  blocks: [
    { type: 'header', text: ':rotating_light: Stock Discrepancy Alert' },
    { type: 'section', fields: [
      'SKU: {internal_sku}',
      'ShipBob qty: {shipbob_qty}',
      'Cin7 qty: {cin7_qty}',
      'Variance: {variance} units ({variance_pct}%)',
      'Classification: {classification}',
      'ASN ID: {asn_id | none}',
      'Cin7 record: https://app.cin7.com/inventory/{cin7_product_id}'
    ]}
  ]

// Daily summary message (posted after all lines processed)
POST https://slack.com/api/chat.postMessage
  channel: '#fulfilment-ops'
  text: 'Daily 3PL Reconciliation Summary — {run_timestamp}'
  blocks: [
    'Total SKUs processed: {total_skus_processed}',
    'Matched and auto-updated: {matched_count + minor_variance_count}',
    'Major discrepancies (action required): {major_discrepancy_count}',
    'Missing ASNs (action required): {missing_asn_count}',
    'Unmapped SKUs (mapping table update required): {unmapped_skus}'
  ]

// Output
update_summary: {
  run_id: UUID,
  cin7_adjustments_applied: integer,
  shopify_variants_updated: integer,
  slack_alerts_sent: integer,
  daily_summary_sent: boolean,
  errors: [{ sku: string, system: string, error_message: string }]
}

// ============================================================
// TRIGGER
// ============================================================
TRIGGER: daily_schedule (06:00 local) OR shipbob_webhook_received
  -> trigger_type: 'schedule' | 'webhook'
  -> webhook_event?: 'inventory_updated' | 'receiving_order_created' | 'outbound_order_fulfilled'
  -> schedule_date: ISO8601
  -> run_id: UUID (generated at trigger)

// ============================================================
// AGENT 1: 3PL Data Fetch and Normalisation Agent
// ============================================================
ShipBob API calls:
  GET /v2/inventory          -> raw_inventory_lines[]
  GET /v2/receiving-orders   -> raw_asn_lines[]
  GET /v2/orders?status=Fulfilled -> raw_outbound_lines[]

SKU mapping table lookup (Google Sheets):
  shipbob_sku       -> internal_sku
  unmapped shipbob_skus -> unmapped_skus[]  (surfaced in summary, not dropped)

Fields normalised per inventory line:
  shipbob_sku, internal_sku, warehouse_code,
  quantity_on_hand (integer), asn_status ('confirmed'|'pending'|'missing'),
  asn_id (string|null), last_updated (ISO8601)

Dedupe check: if run_id already exists in reconciliation log -> abort, log warning

// HANDOFF: normalised_dataset -> Reconciliation and Discrepancy Agent
// Payload: { run_id, run_timestamp, trigger_type, inventory_lines[], unmapped_skus[] }

// ============================================================
// AGENT 2: Reconciliation and Discrepancy Agent
// ============================================================
For each inventory_line in normalised_dataset.inventory_lines:
  Cin7 API call:
    GET /v1/inventory?sku={internal_sku}
      -> cin7_quantity_on_hand (integer)
      -> cin7_product_id (string)
      -> cin7_last_modified (ISO8601)

  Compute:
    variance = quantity_on_hand - cin7_quantity_on_hand
    variance_pct = abs(variance) / cin7_quantity_on_hand

  Classify:
    variance_pct <= 0.02                                  -> 'matched'
    0.02 < variance_pct <= 0.10 AND abs(variance) <= 10   -> 'minor_variance'
    variance_pct > 0.10 OR abs(variance) > 10             -> 'major_discrepancy'
    asn_status == 'missing' OR pending age > 24h          -> 'missing_asn' (overrides all)

Google Sheets append (Reconciliation Log tab):
  Fields: run_id, run_timestamp, total_skus_processed, matched_count,
          minor_variance_count, major_discrepancy_count, missing_asn_count,
          unmapped_skus (comma-separated), status

Google Sheets append (Finance Summary tab):
  Fields: run_id, run_timestamp, total_adjustments_value, open_exceptions_count

// HANDOFF: reconciliation_result -> Update and Notification Agent
// Payload: { run_id, run_timestamp, classified_lines[], unmapped_skus[],
//           total_skus_processed, matched_count, minor_variance_count,
//           major_discrepancy_count, missing_asn_count }

// ============================================================
// AGENT 3: Update and Notification Agent
// ============================================================
Branch A: classification IN ('matched', 'minor_variance')
  Cin7 inventory adjustment:
    POST /v1/inventory/adjustments
    body: { productId: cin7_product_id, quantity: shipbob_qty,
            note: 'Auto-reconciled by automation run_id {run_id} at {run_timestamp}' }
    -> cin7_adjustment_id (string)

  Shopify variant lookup (if native sync not active):
    GET /admin/api/2024-01/variants.json?sku={internal_sku}
    -> variant_id, inventory_item_id, location_id

  Shopify inventory level update:
    POST /admin/api/2024-01/inventory_levels/set.json
    body: { location_id, inventory_item_id, available: shipbob_qty }
    -> updated: boolean

Branch B: classification IN ('major_discrepancy', 'missing_asn')
  Slack alert per exception SKU:
    POST chat.postMessage -> channel: '#fulfilment-ops'
    fields: internal_sku, shipbob_qty, cin7_qty, variance,
            variance_pct, classification, asn_id, cin7_product_id (link)

End of run (all branches complete):
  Slack daily summary:
    POST chat.postMessage -> channel: '#fulfilment-ops'
    fields: run_timestamp, total_skus_processed, auto_updated_count,
            major_discrepancy_count, missing_asn_count, unmapped_skus,
            failed_updates (if any)

Error events (any API failure after 1 retry):
  -> Supabase error log table: run_id, sku, system, error_message, timestamp
  -> Included in daily Slack summary 'Failed updates' field

// ============================================================
// TERMINAL STATE
// ============================================================
Reconciliation log row: written (Google Sheets)
Finance summary row: written (Google Sheets)
Cin7 stock levels: updated for matched + minor_variance SKUs
Shopify inventory: updated for same SKUs (if native sync inactive)
Slack alerts: posted for each major_discrepancy and missing_asn SKU
Slack daily summary: posted to #fulfilment-ops
Error log: written to Supabase for any failed API calls
Human action required: Operations Coordinator reviews Slack alerts
                       for major discrepancies and missing ASNs