OSP Projects

Tag: integration

  • 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 3, 2025

    Fulfillment – Daily Changelog – October 3, 2025

    Changelog – October 3, 2025

    Critical Bug Fixes

    Fixed “Address Updated” Badge False Positives

    • Problem: Badge appeared on EVERY order modification (status changes, fulfillment updates, etc.)
    • Root Cause: Comparison logic included the shipping_address JSON blob which had serialization inconsistencies
    • Solution: Removed JSON blob from comparison, only compare actual address fields:
    • shipping_name
    • shipping_address1
    • shipping_city
    • shipping_state
    • shipping_zip
    • shipping_country
    • Database Cleanup: Cleared all 138 false positive shipping_updated_at timestamps
    • Result: Badge now ONLY appears when actual shipping address fields change

    Fixed Invoice Creation Dialog Crash

    • Problem: Creating invoices crashed with can't access property "toFixed", e.total_amount is undefined
    • Root Cause: API was only returning partial invoice data (id, invoice_number, order_id)
    • Solution:
    • Added optional chaining as immediate fix: total_amount?.toFixed(2) ?? '0.00'
    • Fixed root cause: API now returns complete invoice data including total_amount, customer_name, customer_email, etc.
    • Files Updated:
    • src/components/invoices/send-to-quickbooks-dialog.tsx
    • src/components/orders/invoice-creation-success.tsx
    • src/app/api/invoices/route.ts
    • Result: Invoice creation works properly, displays all information without crashes

    Performance Optimizations

    QuickBooks Invoice Send – Parallel Processing

    • Problem: Both invoice creation dialog and invoices page were sending to QuickBooks sequentially (one at a time)
    • Impact: 12 invoices × 0.5 seconds each = 6+ seconds total wait time
    • Solution: Changed both implementations to use Promise.all / Promise.allSettled for parallel processing
    • Files Updated:
    • src/components/orders/invoice-creation-success.tsx
    • src/components/invoices/send-selected-to-quickbooks-button.tsx
    • Result: All invoices now send simultaneously – ~0.5-1 second total for any batch size

    Documentation Updates

    Order Combining Guide

    • Updated ORDER_COMBINING_GUIDE.md to include external orders
    • Refined order filtering logic documentation
    • Added clarity around which order types can be combined
  • 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