OSP Projects

Tag: orders

  • Fulfillment – Daily Changelog – October 29, 2025

    Fulfillment – Daily Changelog – October 29, 2025

    Changelog – October 29, 2025

    🚀 New Features & Improvements

    Raffle Sticker Batch Page Enhancements

    • Visual Order Grouping: Split domestic orders into two distinct sections on raffle sticker batch page
    • “Single Sticker Orders” section for orders with exactly 1 sticker
    • “Multiple Sticker Orders” section for orders with 2+ stickers
    • Each section shows order count and total sticker count for easy packing workflow
    • Color-Coded Quantity Badges: Added brand-approved color-coded badges to quantity display
    • Single sticker (1): Gray – subtle, low priority
    • 2-5 stickers: Green – moderate attention
    • 6-10 stickers: Blue – higher attention
    • 11+ stickers: Primary gold – highest attention
    • Section-Specific Selection: Each order group has its own “Select All” checkbox while maintaining global selection functionality
    • Packing Workflow Optimization: Visual grouping makes it easier to prepare packages by quantity, improving packing efficiency

    4×6 Packing Label Enhancements

    • Multiple Quantity Badge: Added prominent visual indicator on 4×6 packing labels for orders with more than 1 sticker
    • Black badge displaying “X STICKERS” appears in header area next to order number
    • Only shows when quantity > 1 to draw attention to multi-sticker orders
    • Prevents packing errors by making quantity immediately apparent
    • Label Generation Updates: Modified both production and debug label routes to calculate and display total quantity
    • Updated label header layout to accommodate badge
    • Bold, high-contrast styling ensures visibility during physical packing

    Avery Packing Label Enhancements (5160 & 5167)

    • Quantity Badge on Sticky Labels: Added visual quantity badges to Avery label formats for multi-item orders
    • Displays total item quantity (e.g., “3”) in a bold black badge next to order number
    • Works on both Avery 5160 (30 labels/sheet) and Avery 5167 (80 labels/sheet) formats
    • Compact sizing optimized for small label dimensions
    • Only appears when order has more than 1 item
    • Order Picking Optimization: Helps employees quickly identify orders requiring multiple items during pick/pack operations
    • High-contrast black badge with white text for instant visibility
    • Positioned next to order number in flexible layout
    • Scales appropriately for compact (5167) vs standard (5160) label sizes

    🔧 Technical Improvements

    Frontend Components

    • src/app/(authenticated)/batches/[id]/raffle-page.tsx:
    • Added useMemo hook to group domestic orders by quantity
    • Created getQuantityBadgeColor helper function for color-coded badges
    • Created renderOrderRow helper function to DRY up order table rendering
    • Split single orders table into two sections with independent selection logic
    • Updated selection handlers to work across both order groups

    API Route Updates

    • src/app/api/orders/labels-4x6/route.ts: Added quantity badge logic to production 4×6 label generation
    • src/app/api/orders/labels-4x6/debug/route.ts: Added quantity badge logic to debug 4×6 label generation
    • Calculate total quantity across all line items
    • Conditionally render badge HTML when quantity > 1
    • Updated CSS to style badge with high visibility
    • src/app/api/orders/labels/route.ts: Added quantity badge support for Avery label formats (5160 & 5167)
    • Added .order-with-badge and .qty-badge CSS classes
    • Implemented flexible layout to accommodate badge next to order number
    • Responsive sizing for both compact (5167) and standard (5160) formats
    • Uses existing itemCount field which contains total quantity sum

    Styling Updates

    • Added .order-info CSS class for flexible layout of order number and badge
    • Added .qty-badge CSS class with bold styling (black background, white text, increased letter-spacing)
    • Updated header alignment from align-items: baseline to align-items: flex-start for proper badge positioning

    🎯 User Experience Improvements

    • Packing Efficiency: Visual separation of single vs. multiple sticker orders streamlines the physical packing process
    • Error Prevention: Prominent quantity badges on labels reduce risk of packing incorrect number of stickers
    • Quick Identification: Color-coded badges allow instant recognition of order complexity at a glance
    • Workflow Preservation: All existing functionality (send to ShippingEasy, bulk operations) remains unchanged

    Files Modified: 4
    Major Features Added: Order quantity grouping, visual quantity badges on all label types (4×6 packing lists & Avery sticky labels)
    Focus: Packing workflow optimization and error prevention for physical fulfillment operations

    🖨️ Packing Lists Overhaul (Batches)

    • Fixed print layout to hide all app UI (nav, sidebar, submenus) and only render packing lists
    • Ensured one page per order with reliable page breaks and no blank pages
    • Added manual Print button plus auto-print after data load
    • Added professional header:
    • Mama Tried logo and business address (4188 S. Kinnickinnic Ave., St Francis, WI 53235)
    • Moved order number and date to top-right; removed duplicate “#” prefix
    • Removed unused icon import to satisfy lint and unblock deploy

    Files:

    • src/app/(authenticated)/batches/[id]/packing-lists/page.tsx
    • src/app/api/batches/[id]/bulk-packing-lists/route.ts
    • src/app/api/batches/[id]/packing-list/route.ts

    🧰 Database Transaction Leak Remediation (Critical)

    • Audited and fixed transaction handling across 24+ API routes (29 transactions)
    • Replaced db.query('BEGIN'|'COMMIT'|'ROLLBACK') with per-request client via db.connect()
    • Added finally { client.release() } and 10s statement timeout on transactions
    • Resolved pool exhaustion that previously froze the app under load

    Representative routes fixed (subset):

    • orders/[id]/create-reprint, orders/fix-line-items
    • products/* (tags, files, production-files, display-files, toggle-pod, placement-preview)
    • warehouse-items/*, warehouse-inventory/* (transfer, move, reserve, import, pod)
    • navbar/*, available-pages, settings/batch-stages, price-*, roles/*, users/public-pages, art-file-previews

    📦 Warehouse & POD Inventory Improvements

    • Added POD inventory endpoints and UI dialogs
    • Introduced migration for POD blank inventory
    • Fixed lint in inventory dialogs to allow deployment

    Files:

    • database_migrations/add_pod_blank_inventory.sql
    • src/app/api/warehouse-inventory/pod/route.ts
    • src/components/warehouse-inventory/{pod-inventory-view,adjust-pod-inventory-dialog,pod-blank-item-dialog}.tsx

    🛍️ Shopify Product Metadata Sync in Order Processing

    • Added metadata sync from Shopify during order import to improve product classification
    • Order processing now continues even if metadata sync fails (better resilience)

    Files:

    • src/lib/orderUtils.ts
    • ORDER_TYPE_FIX.md

    🔤 New Supporting APIs

    • Added sizes API route to support inventory/product flows

    Files:

    • src/app/api/sizes/route.ts
  • Fulfillment – Daily Changelog – October 27, 2025

    Fulfillment – Daily Changelog – October 27, 2025

    Changelog – October 27, 2025

    🐛 Critical Bug Fix

    Order Type Filtering System Fixed

    • Problem Identified: Order type filtering on orders page was not working correctly
    • Filters showed no results even when matching orders existed
    • Found 9 orders (4 pending, 5 invoiced) missing order_type_id values
    • LEFT JOIN to order_types table returned NULL for orders without type assignment
    • Filter query looking for ot.slug = 'print-on-demand' couldn’t match NULL values

    Solution Implemented

    • Bulk Order Type Assignment: Ran scripts/assign-order-types.js to fix missing order types
    • Analyzed line items for each order to determine correct type
    • Successfully assigned types to all 9 orders:
      • 1 print-on-demand order (#5480)
      • 7 raffle-sticker orders
      • 1 pod-raffle order (mixed POD + raffle sticker)
    • Database Verification: Confirmed all orders now have order_type_id populated
    • 0 orders remaining without order types
    • Filtering now works correctly for all order type combinations

    System Verification

    • Automatic Assignment Confirmed: Verified that new orders automatically get order types assigned
    • Both Shopify orders and manual orders call assignOrderType() on creation
    • Future orders will not experience this issue
    • Filter Testing: Confirmed filtering by “Pending” + “Print-on-demand” now correctly shows order #5480

    🔍 Investigation & Analysis

    Database Query Analysis

    • SQL Filter Logic: Examined order filtering logic in /api/orders route
    • Filter correctly implemented using ot.slug = $N parameter
    • Issue was missing data, not broken code
    • Order Type Structure: Reviewed order_types table and relationships
    • Migration 027 added order_type_id to mama_tried_orders
    • Order types: print-on-demand, inventory, raffle-sticker, pod-raffle, mixed, external

    Root Cause

    • Missing Migration: Some orders created before or during migration didn’t get order types assigned
    • Gaps in Automatic Assignment: While auto-assignment exists, some orders slipped through
    • Data Integrity: No mechanism to detect or alert on missing order types

    🎯 Technical Details

    Database Changes

    -- Before Fix
SELECT COUNT(*) as orders_without_type 
FROM mama_tried_orders 
WHERE order_type_id IS NULL;
-- Result: 9 orders

-- After Fix  
SELECT COUNT(*) as orders_without_type 
FROM mama_tried_orders 
WHERE order_type_id IS NULL;
-- Result: 0 orders

    Order Type Assignments

    • Order #5480 (Scott Coffey): print-on-demand
    • Order #5479 (Randy Gonzalez): raffle-sticker
    • Order #5478 (Nolan Hanke): raffle-sticker
    • Order #5477 (Anthony J Leone Jr): raffle-sticker
    • Order #5476 (Javier Snell): raffle-sticker
    • Order #5475 (Michael Holderbaum): raffle-sticker
    • Order #5474 (Roman Coronado): pod-raffle (2 line items)
    • Order #5473 (Tony Raphael): raffle-sticker
    • Order #5472 (Cristian Jimenez): raffle-sticker

    📊 Impact

    User Experience

    • ✅ Order Filtering Now Works: Users can successfully filter orders by type
    • ✅ Accurate Results: All matching orders appear in filtered views
    • ✅ Complete Data: Every order now properly categorized

    System Health

    • ✅ Data Integrity Restored: All orders have proper type assignments
    • ✅ Future Protection: Automatic assignment prevents recurrence
    • ✅ Filter Reliability: Order type filters now function as designed

    🔧 Scripts Used

    Order Type Assignment Script

    • Script: scripts/assign-order-types.js
    • Function: Analyzes line items and assigns appropriate order type
    • Logic:
    • Examines product types in each order
    • Determines if POD, inventory, raffle-sticker, or mixed
    • Updates order_type_id based on item composition
    • Results: 9/9 orders successfully processed with no errors

    📝 Notes

    Prevention

    • Order type assignment already automated in order creation flow
    • This was a one-time fix for historical data
    • No code changes needed, only data correction

    Testing

    • Verified filtering works with multiple combinations:
    • Status: pending + Type: print-on-demand ✅
    • Status: invoiced + Type: raffle-sticker ✅
    • Mixed filters ✅

    Orders Fixed: 9
    Script Runtime: < 1 second
    Status: Complete – All orders now properly typed and filterable

  • Deco Order Manager – Daily Changelog – September 29, 2025

    Deco Order Manager – Daily Changelog – September 29, 2025

    Daily Change Log – September 29, 2025

    Color Visibility & Accessibility Improvements

    Overview

    Comprehensive overhaul of the application’s color scheme to address visibility issues in both light and dark modes. All changes focused on improving contrast ratios, readability, and overall user experience.

    Files Modified

    • src/app/globals.css – Core color system updates
    • src/components/ui/button.tsx – Button component color improvements
    • src/components/ui/badge.tsx – Badge component color fixes
    • src/components/navbar.tsx – Status indicator color updates
    • src/app/dashboard/campaigns/[id]/page.tsx – Campaign details color fixes
    • src/app/dashboard/campaigns/[id]/verify/page.tsx – Verification page color improvements

    Key Changes

    1. Enhanced Color Contrast System

    • Light Mode Improvements:
    • Updated muted text from --osp-gray-600 to --osp-gray-700 for better readability
    • Enhanced border visibility with --osp-gray-400 instead of --osp-gray-300
    • Improved primary colors using --osp-primary-600 for stronger contrast
    • Enhanced secondary and status colors for better visibility
    • Dark Mode Overhaul:
    • Switched to midnight blue palette for background (--midnight-blue-950)
    • Used card background --midnight-blue-900 for better layering
    • Enhanced muted text with --midnight-blue-300 for improved contrast
    • Brightened accent colors (--osp-primary-400, --osp-blue-400) for dark backgrounds

    2. Global Table Styling Enhancements

    • Added comprehensive table styles with proper border collapse
    • Enhanced table headers with better background colors and font weights
    • Improved row hover states for better user interaction
    • Added consistent border styling using semantic color tokens

    3. Component Color Improvements

    • Button Components:
    • Fixed destructive button text to use --destructive-foreground instead of generic foreground
    • Enhanced outline button borders and focus states
    • Improved ghost button hover states
    • Badge Components:
    • Corrected destructive badge text colors for better contrast
    • Enhanced focus and hover states

    4. Semantic Color Token Migration

    Replaced all hardcoded color classes with semantic tokens:

    • bg-red-500 → bg-danger
    • text-red-600 → text-danger
    • bg-green-500 → bg-success
    • text-green-600 → text-success
    • bg-blue-500 → bg-info
    • text-blue-600 → text-info
    • bg-yellow-500 → bg-alert
    • text-yellow-600 → text-alert

    5. Enhanced Status Indicators

    • Updated scheduler status indicators in navbar to use semantic colors
    • Improved order status badges with proper contrast ratios
    • Enhanced error/success message styling throughout the application

    6. CSS Override System

    Added comprehensive CSS overrides for common color classes to ensure consistency:

    • Global text color overrides for better semantic color usage
    • Enhanced background color classes with proper transparency support
    • Improved hover and focus states across all interactive elements

    Technical Details

    Color Variables Updated

    /* Light Mode Enhancements */
--muted: var(--osp-gray-200);           /* Was: --osp-gray-100 */
--muted-foreground: var(--osp-gray-700); /* Was: --osp-gray-600 */
--primary: var(--osp-primary-600);      /* Was: --osp-primary-500 */
--border: var(--osp-gray-400);          /* Was: --osp-gray-300 */

/* Dark Mode Improvements */
--background: var(--midnight-blue-950); /* Was: --osp-gray-900 */
--card: var(--midnight-blue-900);       /* Was: --osp-gray-800 */
--muted: var(--midnight-blue-800);      /* Was: --osp-gray-700 */
--primary: var(--osp-primary-400);      /* Was: --osp-primary-500 */

    New CSS Rules Added

    • Enhanced table styling for better visibility
    • Improved button hover states
    • Semantic color class overrides
    • Better support for semi-transparent backgrounds using color-mix()

    Impact & Benefits

    • Accessibility: Improved contrast ratios meet WCAG guidelines
    • Consistency: Unified color system across all components
    • Maintainability: Centralized color management through semantic tokens
    • User Experience: Better visual hierarchy and readability
    • Theme Support: Enhanced dark mode with proper contrast ratios

    Testing Notes

    • No linting errors introduced
    • All semantic color tokens properly mapped
    • Button and badge components maintain proper contrast in both themes
    • Table styling enhanced without breaking existing layouts

    Future Considerations

    • Color system now easily extensible through CSS custom properties
    • Semantic tokens allow for easy theme variations
    • Global overrides ensure consistency even with legacy hardcoded classes
  • Fulfillment – Daily Changelog – September 22, 2025

    Fulfillment – Daily Changelog – September 22, 2025

    2025-09-22 – Order Combining Detection System

    Order Management Enhancement – Duplicate Order Detection

    Problem Identified

    • Issue: Multiple pending orders for the same customer are being fulfilled separately
    • Impact: Inefficient shipping, increased costs, poor customer experience
    • Risk: Continued waste of shipping resources and potential customer dissatisfaction
    • Priority: High – addresses operational inefficiency and cost savings opportunity

    Investigation Plan

    • [ ] Analyze current order structure and customer identification methods
    • [ ] Identify how to detect orders that can be combined
    • [ ] Evaluate combining orders in Shopify vs. internal system
    • [ ] Design UI notifications for combinable orders
    • [ ] Implement detection logic for same customer/same address orders

    Current Analysis

    • Order Structure: Need to examine mama_tried_orders and related tables
    • Customer Matching: Identify how customers are linked across orders
    • Address Matching: Determine if shipping addresses can be reliably compared
    • Shopify Integration: Evaluate feasibility of combining orders at Shopify level
    • Internal System: Assess benefits of internal order combining workflow

    Technical Implementation Plan

    Order Detection Logic ✅ IMPLEMENTED

    • Customer Matching: Use customer_id, email, shipping name, and address for identification
    • Status Filtering: Focus on pending orders that haven’t been processed yet
    • Address Validation: Implement fuzzy address matching for slight variations
    • Time Window: Consider orders placed within a reasonable time frame (e.g., same day)
    • API Endpoint: /api/orders/combinable returns groups of combinable orders

    UI/UX Enhancements ✅ IMPLEMENTED

    • Notification System: Alert staff when combinable orders are detected
    • Order Details Page: Show potential order combinations
    • Batch Processing: Option to combine orders before fulfillment
    • Visual Indicators: Clear flags for orders that can be combined

    Integration Considerations

    • Shopify API: Evaluate if Shopify supports order combining
    • Shipping Impact: Ensure combined orders maintain proper shipping calculations
    • Inventory Management: Verify stock levels can handle combined quantities
    • Workflow Integration: Integrate with existing order processing workflow

    Shopify vs Internal Order Combining Evaluation

    Shopify Order Combining Limitations

    • Shopify API Constraints: Shopify does not provide native order combining functionality
    • Order Integrity: Each Shopify order has unique identifiers that cannot be merged
    • Customer Experience: Combining at Shopify level would require order cancellation/recreation
    • Third-party Dependencies: Would need to handle refunds, notifications, and customer communication
    • Timeline Issues: Orders already in fulfillment pipeline cannot be easily combined

    Internal System Combining Advantages

    • Workflow Control: Full control over the combining process within existing workflows
    • Data Integrity: Maintain order history and audit trails for each original order
    • Customer Transparency: Keep original order numbers for customer reference
    • Flexible Logic: Implement business rules for when/how orders can be combined
    • Shipping Optimization: Better control over shipping calculations and methods

    Recommended Approach: Internal System with Notifications

    • Phase 1 (✅ Completed): Detection and notification system (implemented)
    • Phase 2 (Recommended): Internal order combining with virtual consolidation
    • Phase 3 (Optional): Shopify fulfillment grouping for shipping efficiency

    Internal Combining Strategy

    1. Virtual Consolidation: Create combined orders in internal system without modifying Shopify
    2. Batch Processing: Process combined orders together for fulfillment
    3. Shipping Optimization: Apply combined shipping rates and methods
    4. Audit Trail: Maintain links between original and combined orders
    5. Customer Communication: Reference original order numbers in combined shipments

    Current Status

    Completed

    • Analyzed order management system structure
    • Created API endpoint for detecting combinable orders (/api/orders/combinable)
    • Built UI component for displaying combinable order alerts
    • Integrated notification system into orders page
    • Implemented customer-based and address-based matching logic

    In Progress

    • Evaluating technical approaches for order detection and combining

    Next Steps

    • Test with real order data
    • Add order combining functionality (API endpoint to combine orders)
    • Enhance UI with combine action buttons
    • Evaluate Shopify vs internal combining approach

    Notes

    • Operational Efficiency: This addresses a real-world inefficiency observed in shipping
    • Customer Experience: Combined orders provide better service and potentially faster delivery
    • Cost Savings: Reduced shipping costs through order consolidation
    • Implementation Strategy: Start with detection and notifications, then evaluate combining options
  • Fulfillment – Daily Changelog – September 15, 2025

    Fulfillment – Daily Changelog – September 15, 2025

    Changelog – September 15, 2025

    Order Sync Logic Updates

    Enhanced Shipping Address Synchronization

    • Added shipping address update utility in src/lib/orderUtils.ts
    • New updateOrderShippingAddress() function to update shipping fields in mama_tried_orders
    • Updates: shipping_address, shipping_name, shipping_address1, shipping_city, shipping_state, shipping_country, shipping_zip, and updated_at

    Shopify Webhook Enhancements

    • Updated webhook handler in src/app/api/webhooks/shopify/route.ts
    • Added support for orders/updated and orders/edited webhook topics
    • Automatically refreshes shipping address when Shopify order is modified
    • Returns early for order updates to avoid duplicate processing

    Scheduled Sync Improvements

    • Enhanced order sync in src/app/api/orders/sync/route.ts
    • Added shipping address refresh for existing orders during sync
    • Ensures shipping data stays current even for orders not recently updated

    Packing-Time Final Check

    • Added final shipping verification at packing time in multiple endpoints:
    • src/app/api/shipments/route.ts – Before creating shipments
    • src/app/api/batches/[id]/send-to-shippingeasy/route.ts – Before sending to ShippingEasy
    • src/app/api/batches/[id]/bulk-weight/route.ts – Before bulk weight operations
    • Fetches latest Shopify order data and updates shipping fields as final safeguard

    Multi-Layer Protection Strategy

    • Implemented three-tier shipping address sync:
    1. Immediate: Webhook updates on orders/updated events
    2. Scheduled: Regular sync refreshes existing orders
    3. Final: Packing-time check ensures accuracy before shipment creation

    Technical Details

    • Uses existing getShopifyOrder() utility to fetch latest order data
    • Leverages transformRestOrder() for consistent data normalization
    • All updates are idempotent and safe to run multiple times
    • Graceful error handling – failures don’t break core functionality

    Visual Indicators

    • Added “Address Updated” badge on orders page (src/app/(authenticated)/orders/page.tsx)
    • Shows orange badge when shipping_updated_at is set
    • Indicates when shipping address was modified after order creation
    • Helps identify orders with address changes at a glance

    Database Schema Updates

    • Added shipping_updated_at column to mama_tried_orders table
    • Tracks timestamp of actual shipping address changes
    • Only set when address actually changes (not on every order update)
    • Used for visual indicators and change tracking

    Debug and Testing Tools

    • Created comprehensive debug endpoints:
    • /api/debug/shipping-updates – List orders with shipping updates
    • /api/debug/shipping-changes – Show before/after address changes
    • /api/debug/test-webhook-endpoint – Test webhook accessibility
    • /api/debug/webhook-diagnostics – Troubleshooting guide
    • /api/debug/latest-updates – Recent order activity
    • /api/debug/test-shipping-update – Simulate address changes
    • /api/debug/reset-shipping-updated-at – Clear false positives

    Shopify Webhook Configuration

    • Configured three webhook events in Shopify Admin:
    • Order creation → https://fulfillment.orchardstreetpress.com/api/webhooks/shopify
    • Order updated → https://fulfillment.orchardstreetpress.com/api/webhooks/shopify
    • Order edited → https://fulfillment.orchardstreetpress.com/api/webhooks/shopify
    • All webhooks use JSON format and proper HMAC signature verification

    Benefits

    • Ensures shipping addresses are always current when orders are packed
    • Handles edge cases where orders are modified after initial import
    • Provides redundancy to catch any missed updates
    • Maintains data integrity throughout the fulfillment process
    • Visual feedback for staff to quickly identify address changes
    • Comprehensive debugging tools for troubleshooting