OSP Projects

Tag: webhooks

  • Fulfillment – Daily Changelog – October 9, 2025

    Fulfillment – Daily Changelog – October 9, 2025

    Changelog – October 9, 2025

    Known Issues

    Fixed Batch 498 – 6 Orders Stuck Without Tracking

    • Problem: 6 orders in batch 498 (#5370, #5376, #5380, #5381, #5387, #5397) got stuck during bulk ShippingEasy sync
    • Root Cause: Bulk sync process got overwhelmed and left these orders in partial state – they had ShippingEasy IDs but no tracking numbers synced
    • Solution: Manually pulled tracking numbers from ShippingEasy and updated database directly
    • Result: All 6 orders now have tracking numbers and are marked as “shipped” – ready for Shopify sync

    Critical Performance Fixes

    Fixed Shipments API Performance Issue

    • Problem: Shipments API was using extremely inefficient nested subqueries with multiple COALESCE statements, causing database performance issues and app slowdowns
    • Root Cause: Query had 8+ nested subqueries running the same JOINs repeatedly for each shipment
    • Solution: Rewrote query using CTE (Common Table Expression) to perform JOINs once and reuse results
    • Performance Impact: Reduced query complexity from O(n²) to O(n) – should eliminate database bottlenecks
    • Files Updated: src/app/api/shipments/route.ts
    • Result: Shipments API should now load quickly without bogging down the database

    New Features

    Mixed Order Batch Creation Logic

    • Problem: Orders containing both fulfillable and ignored products (like VIP tickets + bike entries) were incorrectly creating mixed batches that included ignored items
    • Solution: Implemented proper mixed order handling:
    • Added isExternallyFulfilled() function to identify ignored products (VIP tickets, etc.)
    • Updated batch creation logic to filter out ignored products when determining batch type
    • Batch type now based only on fulfillable items (print, stock, raffle stickers)
    • Orders with ignored items get production notes explaining excluded items
    • Example: Order with 2 VIP tickets + 1 bike entry now creates a raffle_stickers batch (not mixed) with note about ignored tickets
    • Files Updated:
    • src/lib/product-type-utils.ts – Added isExternallyFulfilled() function
    • src/app/api/batches/route.ts – Updated batch creation logic and order note system
    • Result: System now processes only what we actually fulfill while maintaining visibility of ignored items

    Technical Details

    Batch Creation Logic Enhancement

    • Standard Batch Types: Print, Stock, Mixed, Raffle Stickers
    • Special Case – Partial Fulfillment Orders:
    • If order contains ignored products + fulfillable products
    • Create batch based only on fulfillable items
    • Flag order with note indicating ignored items
    • Do NOT include ignored items in fulfillment expectations
    • Do NOT classify entire order as requiring ignored items

    Implementation Notes

    • Uses is_externally_fulfilled product flag and requires_shipping = false variant flag
    • Maintains backward compatibility with existing batch creation
    • Adds production notes to orders for transparency
    • Prevents batch creation if no fulfillable items exist
  • Fulfillment – Daily Changelog – October 1, 2025

    Fulfillment – Daily Changelog – October 1, 2025

    2025-10-01 – Multi-Store Architecture & OSA Integration

    Multi-Store Product Synchronization

    New Store Setup

    • Added Orchard Street Apparel Store: Configured second Shopify store (orchard-street-apparel.myshopify.com) with credentials stored in AWS Secrets Manager (OrchardStreetApparelShopifyAccess)
    • Store Record Created: Added store ID 34 linked to OSP customer (ID 34) in fulfillment_stores table
    • Shopify Scopes Migration: Ran migration 2025-08-22-add-shopify-scopes.sql to create shopify_required_scopes and shopify_scope_verifications tables in production
    • Scope Verification: Verified both Mama Tried and OSA stores have identical 30 Shopify Admin API scopes

    Multi-Store Product Sync Infrastructure

    • Store-Specific Sync: Updated /api/products/sync-new endpoint to accept optional storeId parameter
    • When storeId provided: Uses that store’s Shopify credentials
    • When omitted: Falls back to default Mama Tried store for backward compatibility
    • Store Credential Lookup: Implemented automatic store credential resolution based on product customer_id
    • StoreService.getCustomerStores() retrieves stores for a customer
    • StoreService.getStoreShopifyCredentials() fetches AWS secrets for store-specific API access
    • Sync UI Page: Created /stores/sync-products page with store selector dropdown for importing products from any configured store
    • Product Type Assignment: Auto-assigns product type based on customer (Type 2 “Inventory Item” for OSA, Type 1 “Print-on-demand” for others)

    Database Schema Fixes

    • Product Type Constraint: Added product_type_id to product inserts (was causing null constraint violations)
    • Decoration Process Requirement: Auto-creates “Screen Printing” (ID 3) decoration process for products without one during variant sync
    • Process ID Handling: Modified variant sync to handle products without pre-existing decoration processes

    Multi-Store API Updates

    Variant Sync Endpoints

    • Individual Variant Sync (/api/products/[id]/sync-variants):
    • Looks up product’s customer_id to determine correct Shopify store
    • Initializes ShopifyService with store-specific credentials
    • Auto-creates Screen Printing process for OSA inventory items
    • Falls back to default Mama Tried credentials if no customer assigned
    • Bulk Variant Sync (/api/products/bulk-sync-variants):
    • New endpoint for batch variant synchronization across all active products in a store
    • Processes products sequentially with 250ms delay to avoid Shopify rate limiting
    • Auto-creates decoration processes where missing
    • Returns detailed stats (processed, synced, skipped, errors)

    Price & Description Endpoints

    • Pull Prices (/api/products/[id]/update-prices): Updated to use product’s customer store credentials
    • Pull Description (/api/products/[id]/pull-description): Updated to fetch from correct Shopify store based on product customer

    Product Detail API

    • Added Customer Fields: Updated /api/products/[id] GET endpoint to include:
    • customer_id
    • customer_name
    • customer_sku_prefix
    • Customer Join: Added LEFT JOIN to customers table to populate customer details in product response

    Orchard Street Apparel Import

    Product Sync Results

    • Initial Sync: Successfully imported 250+ products from OSA Shopify store
    • Image Processing: Downloaded and uploaded all product images to S3 (orchardstreetmarket bucket)
    • Art File Records: Created mama_tried_art_files entries for product display images
    • Product Display Files: Linked art files to products via mama_tried_product_display_files

    Product Status Distribution

    • Active: 115 products currently sold
    • Archived: 250 historical/discontinued products
    • Draft: 10 products in preparation

    Variant Sync

    • Bulk Processing: Running bulk variant sync for 115 active OSA products
    • Size/Price Import: Syncing all product variants (XS-XXL, kids sizes, etc.) with Shopify prices
    • Inventory Quantities: Importing current stock levels from OSA store
    • Screen Printing Assignment: Auto-assigning appropriate decoration process to all products

    Code Quality Improvements

    • Removed Email Spam: Eliminated email notifications from bulk product sync (would have sent 250+ emails)
    • Enhanced Logging: Added detailed console logging for store credential lookup and API initialization
    • Error Handling: Improved error messages and validation throughout multi-store flows
    • Case-Insensitive Status Checks: Fixed status filtering to handle uppercase “ACTIVE” vs lowercase “active”

    Technical Notes

    • Architecture: System now supports unlimited Shopify stores, each with separate credentials in AWS Secrets Manager
    • Backward Compatibility: All existing Mama Tried functionality preserved; defaults to original behavior when no store specified
    • Product Association: Products automatically linked to customers/stores during import based on which store they’re synced from
    • Future Ready: Foundation laid for transitioning from Deco Network to OSP Fulfillment Platform for multi-client operations

    Follow-Up Tasks

    • Systematically review remaining product sync tools to ensure multi-store compatibility
    • Consider adding store selector to other bulk operations
    • Monitor Shopify rate limits during large bulk operations
    • Test order sync with OSA products once variant sync completes
  • Fulfillment – Daily Changelog – September 19, 2025

    Fulfillment – Daily Changelog – September 19, 2025

    2025-09-19 – Shipping Address Badge System Fixes

    Critical Bug Fixes

    Fixed False Positive “Address Updated” Badges

    • Problem: Every order was showing “📍 Address Updated” badge even when no shipping changes occurred
    • Root Cause: Both webhook handler and sync process were calling updateOrderShippingAddress with faulty comparison logic
    • Solution: Temporarily disabled automatic shipping address updates in both webhook and sync processes
    • Result: Only legitimate address changes now show the badge (e.g., order #5303 from 8/29)

    Database Cleanup

    • Cleared all false positive shipping_updated_at timestamps from recent orders
    • Restored only the legitimate shipping update for order #5303
    • Prevented system from appearing unreliable to staff/customers

    Code Changes

    • src/app/api/webhooks/shopify/route.ts: Disabled updateOrderShippingAddress calls in webhook handler
    • src/app/api/orders/sync/route.ts: Disabled shipping updates during order sync process
    • Database: Reset shipping_updated_at values to prevent false badge displays

    Build & Deployment Fixes

    Fixed Multiple Build Errors

    • JSX Syntax Error: Fixed missing fragment wrapper in invoice-dialog.tsx
    • Import Path Error: Corrected useToast import path in invoice-creation-success.tsx
    • Linting Errors: Commented out unused variables and fixed unescaped entities
    • Route Conflict: Removed duplicate API route causing Next.js build failures

    Current Status

    Working

    • Badge system now shows accurate shipping address change indicators
    • Invoice creation flow functional
    • Build and deployment process stable

    Root cause: updateOrderShippingAddress comparison logic is faulty – treats every address as “changed”

    What needs to be done RIGHT NOW:

    1. Debug the address comparison in updateOrderShippingAddress function
    2. Fix the logic so it only flags actual changes
    3. Re-enable webhook and sync shipping updates
    4. Test with real address changes to make sure it works

    System Reliability

    • Address root cause of frequent Friday system failures
    • Implement better testing for critical workflows
    • Consider staging environment for testing changes