Author: Mark

  • Fulfillment – Daily Changelog – October 17, 2025

    Fulfillment – Daily Changelog – October 17, 2025

    Changelog – October 17, 2025

    Blank Category System Implementation

    New Category-Based Blank Selection

    • Problem: Need to group interchangeable blank styles for flexible inventory management
    • Solution: Implemented comprehensive category system for blank products
    • Created 3 new database tables:
      • blank_categories – Define categories (e.g., “Black Short Sleeve Tee – Cotton – Standard Quality”)
      • blank_category_styles – Map styles to categories with preference ranking
      • warehouse_category_items – Track inventory by category instead of specific style
    • Added blank_category_id to mama_tried_product_variants for category assignment
    • Features:
    • Full CRUD API for category management (/api/blank-categories)
    • Style assignment with preference ranking (rank 1 = preferred, rank 2 = fallback)
    • Auto-generation of category names from form fields (material + quality + color + garment type)
    • Updated batch production to auto-select SKUs from categories based on availability
    • Built category management UI at /blank-categories
    • Migration: Migrated 68 black Jerzees variants to new category system
    • Category includes Jerzees 29MR (rank 1) and Gildan 8000 (rank 2) as interchangeable options
    • Files Created:
    • database_migrations/add_blank_categories.sql
    • scripts/migrate_to_black_category.sql
    • scripts/verify_category_migration.sql
    • src/app/blank-categories/page.tsx
    • src/components/blank-categories/category-form.tsx
    • src/components/blank-categories/category-styles-manager.tsx
    • src/components/products/category-selector.tsx
    • Multiple API routes under /api/blank-categories/
    • Documentation:
    • BLANK_CATEGORY_IMPLEMENTATION_SUMMARY.md
    • BLANK_CATEGORY_QUICKSTART.md
    • BLANK_CATEGORY_SYSTEM.md

    Blank Categories API Fix

    • Problem: Category list/detail endpoints failing with 500 errors
    • Root Cause: API tried to join with non-existent blank_colors table
    • Solution: Removed LEFT JOIN references to blank_colors table
    • Files Modified:
    • src/app/api/blank-categories/[id]/route.ts
    • src/app/api/blank-categories/route.ts

    Category Name Auto-Generation

    • Feature: Category names now auto-populate as user fills form fields
    • Behavior:
    • Only auto-generates if name field is empty or matches previous auto-generated name
    • Allows manual override by typing directly in name field
    • Impact: Reduces manual typing and ensures consistent naming conventions
    • File Modified: src/components/blank-categories/category-form.tsx

    Critical QuickBooks Rate Limiting Fix

    QuickBooks API Throttling Issue

    • Problem: Attempting to send 25+ invoices to QuickBooks resulted in HTTP 429 “ThrottleExceeded” errors
    • Root Cause: October 3rd commit changed invoice sending from sequential with delays to parallel execution using Promise.all()
    • Previous code sent invoices one at a time with 500ms delays
    • “Optimization” removed delays and sent all invoices simultaneously
    • Worked with 14 invoices on Oct 3, failed with 25+ invoices today
    • QuickBooks API limits: ~30 calls per minute, 500 calls per 5 minutes
    • Impact: Unable to send 80 uninvoiced shipped orders to QuickBooks, system throttled for extended period
    • Solution:
    • Reverted to sequential processing with increased 2-second delays between requests
    • Applied fix to both invoice sending code paths:
      • invoice-creation-success.tsx – “Send All to QuickBooks” button in creation dialog
      • send-selected-to-quickbooks-button.tsx – Bulk send button on invoices page
    • Added progress indicators showing “Sending X/Y…” so users can track progress
    • Files Modified:
    • src/components/orders/invoice-creation-success.tsx
    • src/components/invoices/send-selected-to-quickbooks-button.tsx
    • Deployments: v871, v876, v877
    • Result: Invoices now send reliably without hitting rate limits, users can see progress during bulk sends

    Database Investigation & Upgrade

    Shipped Orders Without Invoices Audit

    • Query: Identified 80 orders marked as “shipped” status that hadn’t been invoiced yet
    • Findings:
    • Most orders from Oct 2-17, 2025 (expected to be uninvoiced)
    • One older order #5346 from Sept 19, 2025 – potentially had status changed manually
    • All had invoiced = false and no records in mama_tried_invoice_orders junction table
    • Action: Prepared for batch invoicing once rate limit issues resolved

    Database Plan Upgrade

    • Action: Upgraded Heroku Postgres from Essential-0 to Essential-1
    • Cost: $5/mo → $9/mo (+$4/mo)
    • Expected: 60 database connections (3x increase)
    • Actual: 20 connections (no change)
    • Essential-0: $5/mo, 20 connections, 1 GB storage
    • Essential-1: $9/mo, 20 connections, 10 GB storage
    • Lesson Learned: Connection limit increase requires Standard-0 plan ($50/mo, 120 connections)
    • Note: Storage increase not needed (was at 9.67% of 1 GB limit)
    • Status: May downgrade back to Essential-0 as upgrade provided minimal benefit for cost

    Order Data Fixes

    Order #5405 Variant Correction

    • Problem: Order 629 (Shopify #5405) showed XXL variant instead of L variant
    • Customer ordered 2 shirts in size L
    • Database showed line item 669 with XXL variant (5687) after variant data restoration
    • Would have been invoiced incorrectly for more expensive XXL size
    • Solution: Updated line item 669 to correct L size variant (5685)
    • Impact: Order will now invoice correctly for the size that was actually ordered
    • Related: This order already had reprint #5405-REPRINT created for the incorrect 2XL that was shipped

    Minor Fixes

    Reprint Dialog Import Path

    • Fixed: Corrected toast hook import path in create-reprint-dialog.tsx
    • Changed: @/hooks/use-toast@/components/ui/use-toast

    Shipment API Error Logging

    • Enhanced: Added error logging to batch lock failure in mark-as-shipped/route.ts
    • File: src/app/api/shipments/mark-as-shipped/route.ts

    Temporary Files Cleanup

    • Removed experimental scripts created during troubleshooting:
    • scripts/send_draft_invoices_to_qb.js
    • scripts/send_draft_invoices_to_qb.cjs

    Outstanding Issues

    API Endpoint Errors

    • Observation: Some API endpoints returning 500 errors when loading order details
    • /api/orders/629 – Failed to load order data
    • /api/orders/629/line-items – Failed to load line items
    • Suspected Cause: Possibly related to raffle sticker orders with no variants, or snapshot system changes not yet deployed
    • Impact: UI errors but invoicing still functions correctly
    • Status: Deferred for future fix

    Lessons Learned

    1. Rate Limiting: Always preserve delays/throttling when interfacing with external APIs – “optimizations” can break integration limits
    2. Cloud Service Pricing: Always verify current Heroku pricing/features rather than relying on assumptions
    3. Progress Indicators: Critical for long-running operations so users know system is working
    4. Database Connections: Issue was connection pool size (5), not total limit (20) – code optimizations may be more valuable than plan upgrades

    Next Steps

    • Monitor QuickBooks integration for rate limiting issues
    • Consider downgrading database back to Essential-0 if connection limits remain adequate
    • Deploy remaining connection pool optimizations from undeployed commits when ready
    • Address API 500 errors for order detail pages (low priority)
  • Fulfillment – Daily Changelog – October 16, 2025

    Fulfillment – Daily Changelog – October 16, 2025

    Changelog – October 16, 2025

    Order Snapshot System Implementation

    Immutable Order Data Snapshots

    • Problem: Order line items lost critical data when variants were modified or deleted
    • Order #5405 showed incorrect sizes after Oct 10 variant data restoration
    • Line items depended on live variant data, which could change or disappear
    • No historical record of what was actually ordered/shipped
    • Solution: Implemented comprehensive snapshot system to capture immutable order data at creation time
    • Added new columns to mama_tried_order_line_items:
      • snapshot_product_data (JSONB) – Complete product details
      • snapshot_variant_data (JSONB) – Complete variant details including size, color, SKU
      • snapshot_pricing_data (JSONB) – Pricing breakdown (blank + print costs)
    • Captures data from:
      • Product details (title, type flags, QuickBooks ID)
      • Variant details (size, color, SKU, style info)
      • Pricing details (blank price, print price, total)
    • Implementation:
    • Modified order creation endpoint to capture snapshots
    • Updated line items endpoint to include snapshot data
    • Added TypeScript types for snapshot data structures
    • Created backfill script for existing orders
    • Database Migration: database_migrations/add_line_item_snapshots.sql
    • Files Modified:
    • src/app/api/orders/route.ts – Capture snapshots on order creation
    • src/app/api/orders/[id]/line-items/route.ts – Include snapshot data in responses
    • src/app/api/orders/[id]/route.ts – Return snapshot data
    • src/types/orders.ts – Added snapshot type definitions
    • src/lib/orderUtils.ts – Enhanced order type assignment
    • Documentation:
    • SNAPSHOT_IMPLEMENTATION_SUMMARY.md – Complete technical overview
    • SNAPSHOT_QUICKSTART.md – Quick reference guide
    • SNAPSHOT_TESTING_GUIDE.md – Testing procedures
    • Scripts Created:
    • scripts/backfill_order_snapshots.py – Backfill snapshots for existing orders
    • scripts/verify_snapshots.sql – Verify snapshot data integrity

    Raffle Sticker Order Type Fix

    • Problem: 176 raffle sticker orders not being classified correctly after variant data reset
    • Orders not appearing in raffle sticker filtered views
    • assignOrderType() function missing is_raffle_sticker field
    • Root Cause: Order type detection couldn’t identify raffle stickers without the flag
    • Solution:
    • Updated assignOrderType() to include is_raffle_sticker field from product data
    • Database fix applied to update 176 orders with correct raffle-sticker type
    • Removed erroneous variant associations
    • Files Modified: src/lib/orderUtils.ts
    • Impact: All raffle sticker orders now properly classified and filterable

    Create Reprint Functionality

    • Feature: Added ability to create reprint orders for incorrect shipments
    • Implementation:
    • New API endpoint: /api/orders/[id]/create-reprint
    • Creates new order with -REPRINT suffix
    • Copies customer info and allows variant selection
    • Marks as is_reprint=TRUE with original_order_id reference
    • Sets total price to $0.00 (no charge to customer)
    • UI Component: CreateReprintDialog for easy reprint creation
    • Files Created:
    • src/app/api/orders/[id]/create-reprint/route.ts
    • src/components/orders/create-reprint-dialog.tsx
    • Use Case: Created reprint order #5405-REPRINT for incorrect 2XL shipment

    Order #5405 Investigation & Fix

    • Investigation: Comprehensive analysis of variant data corruption impact
    • Verified all orders that shipped since Oct 10 variant restoration
    • Only 1 order (out of 80+ shipped) had incorrect size sent
    • Order #5405 Details:
    • Customer ordered 2 shirts in size L
    • Shipped 1 correct (L Solid Gold) + 1 incorrect (2XL Western Stacked instead of L)
    • Shopify showed correct L variant (41889779384456)
    • System shipped 2XL variant (41889779449992)
    • Resolution: Created reprint order #5405-REPRINT with correct L size
    • Documentation:
    • REAL_IMPACT_SUMMARY.md – Analysis of actual damage
    • ACTUAL_DAMAGE_ASSESSMENT.md – Detailed assessment
    • AFFECTED_ORDERS_SUMMARY.md – Complete order analysis
    • CUSTOMER_CONTACT_PLAN.md – Customer communication strategy
    • VARIANT_DATA_CORRUPTION_INVESTIGATION.md – Technical investigation
    • Scripts:
    • fix_order_variants.py – Tool to detect and fix variant mismatches
    • verify_order_variants.py – Verification script
    • investigate_actual_shipments.py – Analyze what was actually shipped
    • create_reprint_5405.sql – SQL for creating reprint order
    • show_5405_change.sql – Document variant changes

    Shipment Sync Optimization & Hang Prevention

    Shipment Processing Deadlock Fix

    • Problem: Shipment sync operations hanging and blocking other database operations
    • Mark-as-shipped endpoint had long-running transactions
    • Concurrent batch updates causing deadlocks
    • Multiple sync operations exhausting database connection pool
    • Root Cause:
    • No transaction timeouts on long operations
    • Batch updates lacking row-level locking
    • Redundant calls between sync endpoints
    • Each sync creating new database connections
    • Solutions Implemented:
    1. Transaction Timeout: Added 30-second timeout to mark-as-shipped endpoint
    2. Row-Level Locking: Implemented SELECT FOR UPDATE SKIP LOCKED for batch updates
    3. Streamlined Sync Flow:
      • sync-shopify endpoint now handles status updates inline instead of calling mark-as-shipped
      • sync-tracking endpoint handles status updates inline, reducing connections
    4. Server-Side Lock System:
      • New sync operation lock to prevent overlapping syncs
      • Lock check before starting bulk operations
      • Auto-release after timeout
    5. Connection Pool Optimization: Updated src/lib/db.ts with better pool settings
    • New API Endpoints:
    • /api/sync-operations/acquire-lock – Acquire exclusive sync lock
    • /api/sync-operations/check-lock – Check if sync is currently running
    • /api/sync-operations/release-lock – Manually release stuck locks
    • Files Modified:
    • src/app/api/shipments/mark-as-shipped/route.ts – Added timeout & locking
    • src/app/api/shipments/sync-shopify/route.ts – Inline status updates
    • src/app/api/shippingeasy/sync-tracking/route.ts – Inline status updates
    • src/components/shipments/BulkSyncTrackingButton.tsx – Lock checking
    • src/lib/db.ts – Connection pool optimization
    • src/lib/syncLock.ts – New lock management utility
    • src/utils/fetchWithTimeout.ts – Timeout utility
    • Documentation: SHIPMENT_SYNC_HANG_FIX_SUMMARY.md
    • Impact: Eliminated sync hangs, reduced connection exhaustion, improved concurrent operation handling

    Minor Enhancements

    Batch API Improvements

    • Enhanced: Batch endpoints now include proper error handling for variant assignments
    • Files Modified:
    • src/app/api/batches/[id]/bulk-weight/route.ts
    • src/app/api/batches/[id]/send-to-shippingeasy/route.ts
    • src/app/api/batches/[id]/sync-shippingeasy/route.ts

    UI Updates

    • Updated: Order list and detail pages to support new snapshot data
    • Files Modified:
    • src/app/(authenticated)/orders/[id]/page.tsx
    • src/app/(authenticated)/orders/page.tsx

    Auth & Middleware

    • Improved: Authentication handling and middleware logging
    • Files Modified:
    • src/lib/auth.ts
    • src/middleware.ts

    Testing & Verification

    Comprehensive Testing Tools

    • Created Python scripts for data verification:
    • fix_order_variants.py – 380 lines – Detect and fix variant issues
    • verify_order_variants.py – 330 lines – Verify order data integrity
    • investigate_actual_shipments.py – 365 lines – Analyze shipment history
    • check_what_i_changed.py – Track database changes
    • Created SQL verification scripts:
    • scripts/verify_snapshots.sql – Verify snapshot backfill
    • temp_line_items.sql – Temporary line item analysis

    Investigation Results

    • Output Files:
    • shipment_investigation_output.txt – Detailed shipment analysis
    • shipment_investigation_report.json – Structured investigation data
    • variant_verification_results.json – Variant verification results

    Database Backups

    • Created pre-change backup: pre_change_backup.dump (4.1 MB)
    • Backup taken before applying major snapshot system changes

    Summary Statistics

    • Total Changes: 8,501 lines added, 63 lines deleted (snapshot system commit)
    • Additional Changes: 616 lines added, 90 deleted (shipment sync commit)
    • New Files: 47 files created (documentation, scripts, components, API routes)
    • Orders Fixed: 176 raffle sticker orders reclassified
    • Orders Analyzed: 80+ shipped orders verified for data corruption
    • Actual Impact: 1 order shipped with wrong size (out of 80+)

    Deployment Status

    • Not Yet Deployed: Changes committed but awaiting production deployment
    • Reason: Awaiting thorough testing due to scope of changes
    • Next Deployment: Will include both snapshot system and shipment sync optimizations
  • Fulfillment – Daily Changelog – October 13, 2025

    Fulfillment – Daily Changelog – October 13, 2025

    Changelog – October 13, 2025

    UI Improvements

    Fixed Product Title Display on Orders Page

    • Problem: Orders were showing “(Default Title)” where size/variant information should be displayed
    • Root Cause Investigation:
    • Initially thought the issue was in item.title field showing “Default Title”
    • Database investigation revealed the actual problem: Shopify was storing “Default Title” in the variant.size field for raffle stickers and other items without actual sizes
    • This caused inconsistent display – some orders showed it (newer syncs with “Default Title” in size), others didn’t (older syncs with NULL/empty size)
    • Solution: Filter out “Default Title” from both product titles AND variant sizes across all order views
    • Check both item.title === 'Default Title' and item.variant.size === 'Default Title'
    • Only show variant information (parentheses) when there’s actually meaningful size or color data
    • Updated getVariantDisplay() helper function to exclude “Default Title” sizes
    • Files Modified:
    • src/app/(authenticated)/orders/page.tsx – Main orders listing page
    • src/app/(authenticated)/store-manager/orders/page.tsx – Store manager orders view
    • src/app/(authenticated)/admin-dashboard/page.tsx – Admin dashboard orders
    • src/app/(authenticated)/store-manager/orders/[id]/page.tsx – Store manager order details
    • src/app/(authenticated)/orders/[id]/page.tsx – Order details page (including getVariantDisplay function)
    • Impact: Clean display without Shopify’s placeholder text – items without sizes/colors show just the product name, items with actual variant data show it properly
    • Why It Started Suddenly: Shopify changed how they sync variants for products without sizes, now explicitly setting size to “Default Title” instead of NULL

    Temporarily Removed “Address Updated” Badge

    • Problem: Badge was showing up incorrectly on orders that didn’t have address changes
    • Root Cause: Known issue with address comparison logic in updateOrderShippingAddress function (documented in changelog_09192025.md)
    • Solution: Removed the badge display temporarily until the underlying comparison logic can be fixed
    • Files Modified: src/app/(authenticated)/orders/page.tsx
    • Impact: Eliminates false positive indicators that were requiring manual resets
    • Follow-up Required: Need to debug and fix the address comparison logic in updateOrderShippingAddress before re-enabling the badge

    Bug Fixes

    Fixed Database Pool Configuration TypeScript Error

    • Problem: TypeScript compilation error – min property not recognized in Pool configuration
    • Root Cause: The node-postgres pg library’s PoolConfig doesn’t support min (minimum pool size) or acquireTimeoutMillis properties
    • Solution: Removed invalid min and acquireTimeoutMillis properties from Pool configuration
    • Files Modified: src/lib/db.ts
    • Impact: TypeScript compilation now passes without errors, ready for deployment

    Fixed JSX Syntax Error in Store Manager Order Details

    • Problem: TypeScript parsing error due to missing closing brace in JSX return statement
    • Root Cause: When fixing the “Default Title” display issue, the closing brace for the map function’s return statement was accidentally removed
    • Solution: Properly structured the JSX with correct closing braces
    • Files Modified: src/app/(authenticated)/store-manager/orders/[id]/page.tsx
    • Impact: Build errors resolved, page renders correctly

    Fixed Unused Variables in Product Sync Tools

    • Problem: ESLint errors for unused confirmationText and setConfirmationText variables
    • Root Cause: Legacy state variables left over from previous confirmation dialog implementation
    • Solution: Removed unused state variables
    • Files Modified: src/components/products/product-sync-tools.tsx
    • Impact: Clean linting, no warnings

    New Features

    Shopify Product Price Change Webhooks

    • Purpose: Automatically track and notify when product variant prices change in Shopify
    • Implementation:
    • Added support for products/update webhook topic to existing webhook handler
    • Compares incoming variant prices with stored prices in database
    • Only triggers notifications when prices actually change (avoids false positives)
    • Handles new variants by storing prices without notification (first-time setup)
    • Updates prices in mama_tried_product_variants table after change detection
    • Email Notifications:
    • Sends one email per product with all variant price changes listed
    • Includes: store name, product title, old → new price for each variant, percentage change
    • Shows compare at price changes if applicable
    • Provides direct link to Shopify admin product page
    • Beautiful branded HTML email template with color-coded price increases/decreases
    • Files Created:
    • src/lib/emails/types/price-change.ts – TypeScript type definitions
    • src/lib/emails/templates/price-change.ts – HTML email template
    • src/lib/emails/senders/price-change.ts – Email sending logic
    • database_migrations/048_add_price_changes_alert_type.sql – Database migration
    • docs/shopify-price-change-webhooks.md – Comprehensive documentation
    • Files Modified:
    • src/app/api/webhooks/shopify/route.ts – Added products/update handler
    • Setup Required:
    1. Run database migration to add price_changes alert type
    2. Configure email recipients via Settings → Email Notifications
    3. Add products/update webhook in Shopify admin (URL: existing webhook endpoint)
    • Security: Uses same HMAC signature verification as existing order webhooks
    • Documentation: Full setup, testing, and troubleshooting guide in /docs/shopify-price-change-webhooks.md

    Added Manual Step Tracker to Raffle Sticker Batches

    • Problem: Raffle sticker batches didn’t have the manual status tracker that regular batches have, making it impossible to manually complete steps when needed
    • Solution: Added BatchStatusTerminal component to raffle sticker batch page
    • Implementation:
    • Uncommented BatchStatusTerminal import
    • Enabled batch stages query to fetch stage data
    • Added data formatting for stages and batch status
    • Placed component below shipping panel in right column
    • Files Modified: src/app/(authenticated)/batches/[id]/raffle-page.tsx
    • Impact: Raffle sticker batches now have the same manual tracker functionality as regular batches for manually completing steps when database timeouts or other issues occur

    Database Fixes

    Manually Updated Batch Status for Failed Completions

    • Problem: Batches 498, 506, and 511 didn’t get updated to “fulfilled” status, likely due to database timeouts/stalls during shipment sync
    • Root Cause: Database was getting stalled during ShippingEasy shipment sync operations
    • Solution: Manually updated database to set all affected batches to “fulfilled” status
    • Batches Updated:
    • Batch 498 (D251009-2) – raffle_stickers → fulfilled ✅
    • Batch 506 (D251009-6) – raffle_stickers → fulfilled ✅
    • Batch 511 (D251010-1) – raffle_stickers → fulfilled ✅
    • SQL Executed:
      UPDATE mama_tried_batches 
      SET batch_status = 'fulfilled', updated_at = CURRENT_TIMESTAMP 
      WHERE id IN (498, 506, 511);
    • Note: All affected batches were raffle_stickers type; batches before 498 were working normally

    Fixed Raffle Sticker Order Type Assignment

    • Problem: After variant data reset, raffle sticker orders weren’t being classified correctly and didn’t show up when filtering for “Raffle Sticker” orders
    • Root Cause:
    • assignOrderType() function wasn’t selecting is_raffle_sticker field from products table
    • Newer orders never got their order type assigned
    • Immediate Fix (Database):
    • Updated 176 orders (86 + 90) to have correct raffle-sticker order type
    • Removed 4 bogus variants created during variant reset from raffle sticker products
    • Cleared variant references from 112 raffle sticker line items
    • Raffle stickers are now simple products without variants (as intended)
    • Code Fix (Deploy Tomorrow):
    • Updated assignOrderType() in src/lib/orderUtils.ts to include is_raffle_sticker field
    • Files Modified: src/lib/orderUtils.ts
    • Impact: Raffle sticker orders now show up correctly in filtered views
  • Fulfillment – Daily Changelog – October 10, 2025

    Fulfillment – Daily Changelog – October 10, 2025

    Changelog – October 10, 2025

    🚨 CRITICAL DATABASE CONNECTION LEAK FIX 🚨

    Fixed Massive Database Connection Leaks Causing Complete System Failure

    • Problem: The entire application was freezing repeatedly with all API requests failing after 3-6 second timeouts. Database was completely locked up with 489+ zombie connections.
    • Root Cause: CRITICAL BUG in transaction handling across multiple API routes. Routes were using db.query('BEGIN'), db.query('COMMIT'), and db.query('ROLLBACK') which gets a DIFFERENT client from the pool for each call. This meant:
    • BEGIN starts a transaction on Client A, then releases it
    • Subsequent queries use Clients B, C, D (different clients!)
    • The transaction on Client A never gets committed/rolled back
    • Client A is stuck in an open transaction forever → CONNECTION LEAK
    • With Heroku’s 20-connection limit, these leaks quickly exhausted the entire pool
    • Why It Only Broke Recently: This bug was dormant but became critical yesterday/today when:
    • ShippingEasy optimizations increased concurrent database operations
    • More orders being processed simultaneously
    • The sequential processing with retries kept connections open longer
    • Load finally exceeded the threshold where leaked connections could be cleaned up naturally

    Fixed Routes (High Priority)

    1. src/app/api/batches/route.ts – Called on every batch creation
    • Was leaking a connection every time you created a batch
    • Fixed to use dedicated client for entire transaction
    1. src/app/api/orders/sync/route.ts – Called during Shopify order imports
    • Was leaking a connection for EVERY order synced
    • Fixed to use dedicated client per order transaction
    1. src/app/api/products/[id]/processes/route.ts – Product process updates
    • Was leaking connections AND never calling COMMIT (transaction stayed open forever!)
    • Fixed to use dedicated client and added missing COMMIT
    1. src/app/api/products/[id]/apply-default-instructions/route.ts – Production instruction setup
    • Was leaking connections on every default instruction application
    • Fixed to use dedicated client for entire transaction

    The Fix Pattern

    // ❌ WRONG - Causes connection leaks
    await db.query('BEGIN');
    await db.query('SELECT...'); // Different client!
    await db.query('COMMIT');    // Yet another different client!
    
    // ✅ CORRECT - Use dedicated client
    const client = await db.connect();
    try {
      await client.query('BEGIN');
      await client.query('SELECT...'); // Same client
      await client.query('COMMIT');    // Same client
    } catch (error) {
      await client.query('ROLLBACK');
      throw error;
    } finally {
      client.release(); // CRITICAL: Always release
    }

    Remaining Work

    • 35 additional API routes have the same bug but are lower traffic
    • These should be fixed systematically to prevent future issues
    • Routes include: warehouse-inventory, art-files, printed-inventory, various product routes, etc.

    Impact

    • IMMEDIATE: Should eliminate database freezes and API timeouts
    • IMMEDIATE: Should resolve 500 errors across all endpoints
    • IMMEDIATE: System should be stable and responsive again
    • Required: Deploy this fix IMMEDIATELY – every minute costs you orders

    Files Modified

    • src/app/api/batches/route.ts
    • src/app/api/orders/sync/route.ts
    • src/app/api/products/[id]/processes/route.ts
    • src/app/api/products/[id]/apply-default-instructions/route.ts

    Critical Bug Fixes

    Fixed Batch Creation Error for Mixed Orders with Ignored Items

    • Problem: Order #5372 (and likely others) containing both physical items and ignored/externally fulfilled items (like bike entry tickets) were failing with “Server Error” when attempting to add to a batch
    • Root Cause: The isExternallyFulfilled() function was checking for item.variant.requires_shipping, but the SQL query in the batch creation route was returning requires_shipping as a direct property on the item (flattened structure), not as a nested variant object. This caused the function to fail to identify externally fulfilled items, which then caused the batch creation logic to fail.
    • Solution: Updated isExternallyFulfilled() function in src/lib/product-type-utils.ts to check for requires_shipping in both locations:
    • As item.variant.requires_shipping (for nested object structures)
    • As item.requires_shipping (for flattened SQL query results)
    • Files Modified: src/lib/product-type-utils.ts (line 149)
    • Result: Orders containing both physical items (stickers, apparel, etc.) and ignored items (VIP tickets, bike entries) can now be successfully added to batches. The physical items will be batched normally while the ignored items are excluded with appropriate production notes.
    • Impact: This was a critical regression from yesterday’s mixed order fix (10/09/2025) that prevented ANY orders with mixed content from being batched.

    Critical Performance Fixes

    Resolved Database Freezing During Order Processing

    • Problem: Database repeatedly froze during simultaneous ShippingEasy operations (sending orders, retrieving tracking, updating Shopify)
    • Root Causes:
    • Sequential processing bottleneck in BulkSyncTrackingButton creating database connection pool exhaustion
    • Concurrent database updates from multiple operations (webhooks, bulk sync, manual sync)
    • No retry logic for transient API failures
    • Webhook race conditions causing duplicate Shopify sync attempts
    • Long-running operations without proper timeout management
    • Solution: Comprehensive optimization of ShippingEasy integration:

    1. Sequential Processing with Rate Limiting

    • Modified: src/components/shipments/BulkSyncTrackingButton.tsx
    • Changes:
    • Replaced concurrent batch processing with sequential processing
    • Added 300ms delays between shipments to prevent database overload
    • Removed complex promise-based state management that could cause race conditions
    • Result: Prevents database connection pool exhaustion and improves stability

    2. Queued Shopify Sync Operations

    • Modified: src/app/api/webhooks/shippingeasy/route.ts
    • Created: src/app/api/shipments/process-pending-shopify-syncs/route.ts (background processor)
    • Added: database_migrations/040_add_shopify_sync_pending.sql (database schema)
    • Changes:
    • Webhook handler now marks shipments as shopify_sync_pending = true instead of immediate sync
    • Created background processor to handle queued Shopify syncs in controlled batches
    • Added database columns to track pending syncs and prevent duplicate processing
    • Result: Webhooks return immediately, eliminating timeouts and race conditions

    3. Retry Logic for API Calls

    • Created: src/utils/retry.ts (retry utility with exponential backoff)
    • Modified: src/app/api/shippingeasy/sync-tracking/route.ts
    • Modified: src/app/api/shipments/send-to-shippingeasy/route.ts
    • Changes:
    • Added exponential backoff retry logic for ShippingEasy API calls
    • Retries on network errors, timeouts, and 5xx status codes (3 attempts: 1s, 2s, 4s delays)
    • Result: Handles transient network issues and API failures automatically

    4. Improved Database Connection Management

    • Modified: src/app/api/shipments/sync-shopify/route.ts
    • Modified: src/app/api/shipments/process-pending-shopify-syncs/route.ts
    • Changes:
    • Added proper timeout handling for database connections (15s for Shopify API, 10s for internal calls)
    • Improved error handling for connection failures
    • Result: Prevents hanging connections that could freeze the database

    Database Changes

    New Columns Added

    • shopify_sync_pending (BOOLEAN): Marks shipments that need Shopify sync
    • shopify_last_sync_at (TIMESTAMP): Tracks when sync was last attempted

    New Indexes

    • idx_shipments_shopify_sync_pending: Efficiently find pending syncs
    • idx_shipments_shopify_last_sync_at: Order syncs by last attempt time

    New API Endpoints

    Process Pending Shopify Syncs

    • POST /api/shipments/process-pending-shopify-syncs: Processes queued Shopify syncs in batches
    • GET /api/shipments/process-pending-shopify-syncs: Gets pending sync statistics

    Benefits Achieved

    1. ✅ Eliminated Database Freezes: Sequential processing prevents connection pool exhaustion
    2. ✅ Improved Reliability: Retry logic handles transient failures automatically
    3. ✅ Better Error Handling: Proper error logging and recovery mechanisms
    4. ✅ Reduced Race Conditions: Queued processing eliminates concurrent update conflicts
    5. ✅ Enhanced Monitoring: Better visibility into sync status and performance

    Documentation Updates

    ShippingEasy Optimization Guide

    • Created: SHIPPINGEASY_OPTIMIZATION_README.md
    • Content: Complete documentation of optimizations, deployment instructions, and monitoring guidelines

    Mama Tried Product Sync Fixes

    • Issue: Sync at /stores/sync-products failed with JSON.parse error and did not correctly detect “new only” products.
    • Root cause:
    • API attempted to insert products that already existed, triggering DB 23505 duplicate key errors which led to non-JSON responses surfacing to the client.
    • Filtering of “new” products used a customer-scoped existing set; duplicates could slip through if already present for another customer.
    • Fixes:
    • Updated src/app/api/products/sync-new/route.ts to:
      • Use all existing shopify_product_ids (global) for filtering so only truly new Shopify products are processed.
      • Add ON CONFLICT (shopify_product_id) DO NOTHING and skip when no row is returned (already exists).
      • Success message now reads: “Synced N new products”.
    • Updated src/app/(authenticated)/stores/sync-products/page.tsx to:
      • Fallback to response.text() when response.json() fails, preventing UI JSON.parse errors.
    • Impact:
    • Sync now correctly imports only truly new products and reports counts explicitly (e.g., “Synced 2 new products”).
    • Client no longer crashes on non-JSON server responses.

    Files Modified

    • src/app/api/products/sync-new/route.ts
    • src/app/(authenticated)/stores/sync-products/page.tsx

    🚨 CRITICAL: “Sync All Variants” Cascade Deletion Bug & Data Recovery

    Discovered and Fixed Destructive Data Wipe in Variant Sync

    • Problem: The “Sync All Variants” feature was using DELETE FROM mama_tried_product_variants followed by re-insertion from Shopify data. Due to ON DELETE CASCADE foreign key constraints, this was silently deleting:
    • All variant pricing data (mama_tried_variant_pricing)
    • All production files (mama_tried_production_files)
    • All print configurations (mama_tried_product_prints)
    • All SKU mappings (variant_skus)
    • All style/color associations
    • Impact: Running “Sync All Variants” on 2 new products (Flat Out Friday “Checkered Skull” & Mama Tried “AMF” T-Shirts) cascaded and wiped metadata for multiple existing products including Tiger Chains line
    • Root Cause: Original implementation prioritized fresh Shopify data sync over preserving internal metadata. The confirmation dialog warned about color/SKU loss but didn’t mention cascade deletion of pricing and production data.

    Immediate Data Recovery (Friday Night)

    1. Pricing Data Restored:
    • Manually restored variant pricing for 80 affected variants from June 2025 Heroku backup
    • Used pg_restore to extract mama_tried_variant_pricing data
    • Created Python script to map old variant IDs to current IDs via shopify_variant_id
    • Regenerated pricing for 2 new products using correct blank prices ($4.19-$8.70) + print prices ($5.95)
    1. SKU Mappings Restored:
    • Restored variant_skus with base_price and marked_up_price from backup
    • Applied 20% markup to new products manually (discovered workflow issue – see below)
    1. Style Associations Restored:
    • Restored style_color_id links for all affected variants

    Long-term Fix: Made “Sync All Variants” Non-Destructive

    • Modified: src/app/api/products/sync-variants/route.ts
    • Modified: src/app/api/products/[id]/sync-variants/route.ts
    • Modified: src/app/api/products/bulk-sync-variants/route.ts
    • Changes:
    • Replaced DELETE statements with INSERT ... ON CONFLICT (shopify_variant_id) DO UPDATE
    • Now updates existing variants with fresh Shopify data (prices, inventory, position) while preserving all metadata
    • Adds new variants without touching existing ones
    • UPSERT approach preserves: style_color_id, production files, prints, SKU mappings, and all pricing data

    UI Updates

    • Modified: src/components/products/product-sync-tools.tsx
    • Changes:
    • Updated confirmation dialog from “DANGEROUS OPERATION WARNING” to normal confirmation
    • Removed required text input (“SYNC ALL VARIANTS”) since operation is now safe
    • Updated description to clearly state metadata is preserved
    • Changed button styling from destructive (red) to normal (primary)

    Automated Pricing Population Fix

    • Modified: src/components/products/product-sections/product-prints.tsx
    • Changes:
    • “Calculate” button now automatically triggers “Populate Pricing Data” after calculating print prices
    • Eliminates manual step that users were forgetting
    • Ensures variant pricing table stays synchronized with print price changes

    Database Backup Implementation

    • Critical: Discovered Heroku Postgres Essential-0 plan does NOT have automatic daily backups by default
    • Action Taken:
    • Scheduled daily automatic backups for HEROKU_POSTGRESQL_PURPLE database at 02:00 America/Chicago
    • Created immediate manual backup for emergency recovery
    • Retained existing manual backups from June and April 2025

    Production Files & Prints Recovery (Sunday, October 13, 2025)

    • Problem: Production files and prints were cascade-deleted along with variant pricing during Friday’s incident
    • Solution:
    • Created comprehensive Python script to restore both production files and prints in correct dependency order
    • Extracted 122 production files and 118 prints from June backup
    • Mapped old variant IDs to current IDs via shopify_variant_id for accurate restoration
    • Successfully restored all data for MT Tiger Chains Tee (16 prints)
    • Verified MT Tiger Chains Hoodie and Youth Hoodie had no prints in backup (never configured)
    • Result: Complete recovery of all production data without manual recreation

    Pricing Workflow Issue Identified (Needs Future UX Improvement)

    • Current Workflow (confusing):
    1. Set markup percentage on product
    2. Click “Update Blank Prices” in sync menu (not obvious this is required)
    3. Click “Populate Pricing Data” to calculate final prices
    • Issue: Setting markup doesn’t automatically update marked_up_price in variant_skus table
    • UI Problem: Blank prices table DISPLAYS calculated markup but doesn’t SAVE to database until “Update Blank Prices” is clicked
    • Recommendation: Need to discuss with team whether to auto-save markup changes or make the workflow more explicit

    Files Modified

    • src/app/api/products/sync-variants/route.ts (UPSERT fix)
    • src/app/api/products/[id]/sync-variants/route.ts (UPSERT fix)
    • src/app/api/products/bulk-sync-variants/route.ts (UPSERT fix)
    • src/components/products/product-sync-tools.tsx (UI updates)
    • src/components/products/product-sections/product-prints.tsx (auto-populate pricing)

    Python Scripts Created (Temporary, Deleted After Use)

    • restore_pricing_v2.py – Restored variant pricing from backup
    • restore_skus_only.py – Restored SKU mappings with correct column mapping
    • restore_all_metadata.py – Comprehensive metadata restoration
    • restore_production_data.py – Restored production files and prints in dependency order

    Impact

    • ✅ CRITICAL: “Sync All Variants” is now safe to use – preserves all metadata while updating Shopify data
    • ✅ CRITICAL: Daily database backups ensure future recovery capability
    • ✅ IMMEDIATE: All affected products fully restored with pricing, prints, and production files
    • ✅ IMMEDIATE: Variant pricing automatically stays synchronized with print price changes
    • 📝 FUTURE: Markup percentage workflow needs UX improvement to prevent confusion

    Lessons Learned

    • Always use UPSERT/ON CONFLICT instead of DELETE when dealing with foreign key relationships
    • Cascade deletes are dangerous – document them clearly and provide non-destructive alternatives
    • UI warnings must accurately reflect the actual destructive potential of operations
    • Automatic backups are critical and should be verified, not assumed
    • Data recovery from backups is feasible but requires careful ID mapping via stable identifiers (shopify_variant_id)
  • 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
  • Time Clock – Daily Changelog – October 2, 2025

    Time Clock – Daily Changelog – October 2, 2025

    Daily Changelog – October 2, 2025

    Improvements

    • Removed all video game and arcade aesthetic CSS from globals.css including neon effects, pixel animations, glitch effects, and arcade-themed classes
    • Eliminated Orbitron font family from tailwind config in favor of Inter and Alderwood typography
    • Reduced globals.css from 1073 lines to 115 lines by removing aggressive color override rules and simplifying design token system
    • Simplified color token system to use only gray-1 through gray-12 and two yellow accent variables (yellow-9, yellow-11) instead of full 12-shade palettes
    • Removed all CSS rules using !important that forced color conversions from blue/purple/cyan to yellow
    • Removed semantic color tokens (success, info, danger, alert, neutral) from CSS in favor of intentional Tailwind class usage in components
    • Updated @layer base styles to use Tailwind @apply directives for better consistency with design tokens
    • Removed glassmorphism effects from Card component in favor of solid backgrounds with subtle shadows
    • Simplified Card component from rounded-xl with backdrop-blur to clean rounded-lg with shadow-sm styling
    • Updated Button component to use clean minimal variants removing all semantic color options (info, success, danger, alert, neutral)
    • Removed arcade styling from Button component including shadow animations and active state transforms
    • Changed Button base class to use simple transition-colors instead of complex transition-all with duration
    • Rewrote StatusBadge component to use class-variance-authority with clean solid backgrounds instead of semi-transparent overlays
    • Updated StatusBadge variants to success, warning, error, neutral, and info with proper contrast ratios
    • Changed StatusBadge styling to pill-shaped (rounded-full) badges with consistent spacing and clean typography
    • Modernized homepage heading from arcade-style neon effect to clean professional greeting with simple date display
    • Simplified homepage stats cards with subtle gray icon backgrounds (bg-neutral/10) instead of bright colored circles
    • Reduced homepage hero section complexity removing uppercase styling, Orbitron font, and arcade status text
    • Streamlined homepage footer from multi-line status display to single version number line
    • Removed gradient background and backdrop filters from CustomNavbar in favor of clean solid background with subtle border
    • Reduced navbar height from h-20 to h-16 for more compact professional appearance
    • Simplified navbar hover states from bg-accent/60 to bg-muted/50 for subtler interaction feedback
    • Changed navbar link styling to use text-muted-foreground with hover:text-foreground instead of opacity-based colors
    • Updated admin console styling to use consistent muted backgrounds and simplified transitions
    • Removed duration-200 and transition-all animations from navbar in favor of simple transition-colors
    • Replaced arcade-card class with bg-card border border-border rounded-lg across 12 TSX files
    • Replaced arcade-btn class with px-4 py-2 rounded-md transition-colors button styling sitewide
    • Replaced arcade-loader class with standard animate-spin spinner implementation
    • Removed all neon-* classes (neon-cyan, neon-pink, neon-purple, neon-orange, neon-green) from entire codebase
    • Removed all pixel-* animation classes from components
    • Removed all glitch effect classes from headers and titles
    • Removed all Orbitron font-family inline styles from TSX files
    • Replaced arcadeStyles object in TimeEntryEditModal with clean styles object using standard Tailwind classes
    • Replaced arcadeStyles object in time-entry/new page with professional styles removing uppercase and tracking classes
    • Replaced arcadeStyles object in settings/pay-periods page with clean professional styling
    • Changed homepage greeting initialization from “TIME CLOCK ARCADE” to “Good Day”
    • Updated time-entry/new page header from “ADD NEW ENTRY” to “Add New Entry” with descriptive subtitle
    • Changed schedules/nexus page title from “[SCHEDULING ARCADE]” to “Schedule Nexus”
    • Replaced gaming color variables (neon-pink, neon-cyan gradients) with semantic theme colors (text-primary, text-info)
    • Updated TimeEntryEditModal title from “EDIT TIME ENTRY” to “Edit Time Entry” with reduced icon size
    • Simplified all loading states from arcade-themed to standard spinner with “Loading…” text
    • Removed “PERSONNEL TIME LOGGING SYSTEM” arcade-style subtitle in favor of plain English descriptions
    • Standardized all rounded corners from rounded-xl and rounded-2xl to consistent rounded-lg across all pages
    • Unified main container padding from py-8 to py-12 for consistent page spacing
    • Standardized card padding from p-8 to p-6 across all cards and panels
    • Changed grid gaps from gap-8 to gap-6 for more compact, professional layouts
    • Reduced large margins from mt-16 to mt-12 for better visual rhythm
    • Removed all backdrop-blur effects for cleaner, simpler card styling
    • Removed shadow-sm and shadow-xl in favor of simple border-based separation
    • Updated time-clock page to use container mx-auto px-4 py-12 max-w-7xl for consistent width
    • Changed employees page container padding from py-8 to py-12
    • Standardized payroll page grid from gap-8 to gap-6
    • Updated payroll card transition from transition-all duration-200 to simple transition-colors
    • Fixed error state layouts to use consistent container and card spacing
    • Ensured all section spacing uses mb-6, mb-8, or mb-12 from Tailwind scale
    • Standardized all page titles (h1) to text-3xl font-semibold across all pages
    • Changed section headings (h2) from text-2xl font-bold to text-xl font-semibold
    • Updated subsection headings (h3) from text-xl font-bold to text-lg font-semibold or text-base font-semibold
    • Replaced all font-bold with font-semibold for headings and labels (except display numbers)
    • Removed all uppercase text transforms for more natural, readable typography
    • Removed tracking-wide and tracking-wider letter-spacing for cleaner text
    • Fixed homepage h1 from text-4xl to text-3xl for consistency
    • Updated time-clock page h1 from text-2xl to text-3xl for proper hierarchy
    • Changed quick action labels from font-bold to font-medium
    • Standardized error messages and alerts to use font-semibold
    • Fixed text-muted-foreground900 typos to text-muted-foreground
    • Ensured proper text contrast with semantic foreground colors
    • Replaced standard Tailwind colors in button.tsx with brand colors (bg-red-600 → bg-[#8F4224], hover:bg-red-700 → hover:bg-[#7E371B])
    • Updated status-badge.tsx to use brand colors: success (#718F24), warning (#E5B919), error (#8F4224), info (#3978AC)
    • Fixed palette validation build errors by ensuring all background colors use approved brand hex values
    • Fixed syntax error in payroll/pay-periods/page.tsx by removing stray comma from style object
    • Fixed syntax error in schedules/nexus/page.tsx by removing stray comma from style object
    • Fixed syntax error in time-clock/page.tsx by adding missing closing div tag and fixing indentation
    • Updated StatusBadge variant from “alert” to “warning” in employee/components/ClockPanel.tsx
    • Changed StatusBadge variant from “alert” to “warning” in time-off-requests/page.tsx
    • Updated StatusBadge variant from “alert” to “warning” in entries/page.tsx for consistency
    • Changed Button variant from “error” to “destructive” in schedules/weekly-schedules/[weekStart]/page.tsx
    • Updated SemanticVariant type in variant-map.tsx from alert/danger to warning/error
    • Fixed statusToVariant function to return warning instead of alert for pending/paused statuses
    • Fixed priorityToVariant function to return error/warning instead of danger/alert
    • Updated getVariantIcon function to handle warning and error variants
    • Replaced all StatusBadge variant=”danger” with variant=”error” across entire codebase
    • Replaced all StatusBadge variant=”alert” with variant=”warning” across entire codebase
    • Fixed variant comparisons in entries/page.tsx to use warning/error instead of alert/danger
    • Updated sick-days page to use error/warning variants instead of danger/alert
    • Updated tasks page subtask badges to use error/warning variants
    • Removed withIcon prop from StatusBadge components (not supported by component)
    • Replaced all CSS color variables with warm palette from mama-tried-fulfillment app (#8F6624 primary, #E5B919 accent, #8F4224 destructive)
    • Updated light theme colors to use warm off-white (#FDFDFC) background with warm foreground (#120E07) for better color temperature
    • Updated dark theme colors to use warm dark brown backgrounds (#120E07, #1A1612) instead of cold grays
    • Changed muted backgrounds from gray (#eff0f3) to warm beige (#F4F3F1) for warmer overall appearance
    • Updated border colors from neutral gray (#d8d9e0) to warm tan (#D1CDC7) matching fulfillment app
    • Added primary color scale (100-900) to Tailwind config with warm amber/gold shades from fulfillment app
    • Added gray color scale (100-900) with warm gray tones matching fulfillment app palette
    • Updated Button component to match fulfillment app exactly with gap-2, svg sizing, and ring-offset-2 focus states
    • Changed Button hover states from opacity changes to bg-primary with opacity-90 for consistent warm feel
    • Added primary and success button variants to match fulfillment app button options
    • Updated Input component to match fulfillment app with file:text-foreground styling and responsive text sizing (text-base md:text-sm)
    • Changed Table wrapper from rounded-xl with backdrop-blur to rounded-md with solid bg-white/dark:bg-card matching fulfillment app
    • Updated Table default zebra striping to false to match cleaner fulfillment app table aesthetic
    • Changed TableRow hover state from hover:bg-muted to hover:bg-muted/50 for subtler interaction
    • Updated TableHeader and TableHead backgrounds from bg-muted/30 to full bg-muted for stronger header contrast
    • Changed container padding to match fulfillment app exactly (px-4 md:px-8 lg:px-16)
    • Updated navbar styling from backdrop-blur to solid bg-card with shadow-sm for cleaner appearance
    • Changed navbar padding to match fulfillment app container width system
    • Applied font-alderwood and text-primary to all major page headings for consistent brand typography
    • Updated EmployeePageHeader to use primary colors instead of info blue for brand consistency
    • Changed homepage greeting to use Alderwood font with primary color for warm branded appearance
    • Updated task management page title to use Alderwood font with primary color
    • Changed employee portal welcome header to use Alderwood font with primary color for brand identity
    • Fixed palette validation error by replacing bg-green-600/bg-green-700 with approved brand green colors (#718F24/#627E1B)
    • Updated Textarea component to match fulfillment app with responsive text sizing (text-base md:text-sm)
    • Updated Badge component variants to use approved brand colors from brand_colors.svg palette
    • Changed Badge warning variant to use Alert palette (#F7F0D4 light, #614D05 text, #F5E3A3 hover)
    • Updated Badge success variant to use Success palette (#F3F5F0 light, #42570F text, #D3DBBD hover)
    • Changed Badge info variant to use Info palette (#F0F3F5 light, #0F3757 text, #BDCEDB hover)
    • Updated Badge processing variant to use warm gray tones for neutral status indication
    • Added dark mode variants for all Badge colors maintaining brand palette consistency

    Notes

    • Design system now follows modern SaaS aesthetic similar to Linear, Vercel, and Notion with minimal styling and intentional color usage
    • Components now use proper Tailwind utilities and design tokens without fighting CSS override rules
    • Color decisions are now made at the component level rather than forced through global CSS overrides
    • All pages now use consistent spacing scale (p-4, p-6, gap-4, gap-6, mb-6, mb-8, mb-12) for professional rhythm
    • Container widths standardized with max-w-7xl for main content areas and max-w-6xl for centered grids
    • Spacing system creates breathable layouts with predictable visual hierarchy
    • Typography hierarchy now consistent: h1 (text-3xl font-semibold), h2 (text-xl font-semibold), h3 (text-lg/base font-semibold)
    • All uppercase transforms and excessive letter-spacing removed for natural readability
    • Font weights simplified to semibold for headings, medium for labels, normal for body text
    • Time Clock app now visually matches mama-tried-fulfillment app with identical color palette, warm temperature, and consistent component styling
    • Both apps share warm amber/gold primary colors (#8F6624), warm backgrounds, and Alderwood font for headers creating unified Orchard Street Press brand identity
    • Table styling now consistent between apps with clean borders, muted headers, and subtle hover states
    • Button and input components use identical styling patterns for seamless cross-app consistency
    • Container widths and padding match exactly between apps for unified layout experience
  • 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
  • Deco Order Manager – Daily Changelog – September 30, 2025

    Deco Order Manager – Daily Changelog – September 30, 2025

    Changelog – September 30, 2025

    Email Communication System Implementation

    Overview

    Implemented comprehensive email communication system using Mailgun for user management workflows.

    New Features

    1. User Deletion

    • Added “Delete User” button to User Management page
    • Confirmation dialog to prevent accidental deletions
    • Permanently removes user and all associated data
    • Useful for cleaning up test accounts and unactivated users

    2. Email Integration with Mailgun

    • Integrated Mailgun email service using HTTP API
    • Created email service utility (/src/lib/email.ts)
    • Implemented three types of automated emails:
    Email Types:
    1. Signup Notification (Admin)
    • Sent to admin when new user signs up
    • Includes user details and link to user management
    • Helps admins quickly respond to new signups
    1. User Invitation Email
    • Sent when admin creates a new user account
    • Includes assigned stores and login link
    • Professional welcome message with HTML formatting
    1. Account Approval Email
    • Sent when admin activates a user account
    • Notifies user they can now access the system
    • Includes direct login link

    Technical Changes

    New Files

    • src/lib/email.ts – Email service with Mailgun integration
    • EMAIL_SETUP.md – Comprehensive email setup documentation
    • HEROKU_ENV_SETUP.md – Quick reference for Heroku configuration

    Modified Files

    • src/app/api/auth/signup/route.ts – Added admin notification email
    • src/app/api/admin/users/route.ts – Added invitation email on user creation
    • src/app/api/users/route.ts – Added approval email when user activated
    • src/app/dashboard/users/page.tsx – Added delete user functionality
    • package.json – Added mailgun.js and form-data dependencies

    New Dependencies

    • mailgun.js – Official Mailgun API client
    • form-data – Required for mailgun.js

    Environment Variables Required

    MAILGUN_API_KEY          # Provided by Heroku Mailgun addon
    MAILGUN_DOMAIN           # Provided by Heroku Mailgun addon
    MAILGUN_FROM_EMAIL       # Set manually: "Deco Order Manager <noreply@domain.com>"
    ADMIN_EMAIL              # Set manually: admin email for notifications

    Setup Instructions

    1. Install Dependencies:
       npm install
    1. Configure Environment Variables on Heroku:
       heroku config:set ADMIN_EMAIL="your-admin@email.com"
       heroku config:set MAILGUN_FROM_EMAIL="Deco Order Manager <noreply@yourdomain.com>"
    1. Verify Mailgun Config:
       heroku config | grep MAILGUN
    1. Deploy:
       git add .
       git commit -m "Add email communication and user deletion"
       git push heroku main

    Testing

    1. Test Signup Email:
    • Visit signup page
    • Create a test account
    • Admin should receive notification email
    1. Test Invitation Email:
    • Log in as admin
    • Create a new user
    • Check new user’s email inbox
    1. Test Approval Email:
    • Log in as admin
    • Activate a pending user
    • Check user’s email inbox
    1. Test User Deletion:
    • Log in as admin
    • Go to User Management
    • Click “Delete User” on a test account
    • Confirm deletion

    Important Notes

    • Emails are sent asynchronously and won’t block user operations
    • Email failures are logged but don’t prevent user operations
    • Mailgun sandbox domains require recipient authorization
    • For production, verify a custom domain in Mailgun
    • All emails include both HTML and plain text versions

    Benefits

    1. Better User Experience:
    • Users know when their account is approved
    • New users receive proper onboarding
    • Clear communication at each step
    1. Improved Admin Workflow:
    • Instant notifications of new signups
    • Automated invitation emails save time
    • No manual communication needed
    1. Professional Image:
    • Branded email templates
    • Timely automated responses
    • Consistent communication

    Additional Updates (Later in Day)

    3. Mailgun Production Upgrade

    • Upgraded Mailgun from sandbox to production plan (50k emails/month)
    • Fixed email delivery issues by removing sandbox restrictions
    • Now supports sending to any email address

    4. Password Reset/Set Functionality

    • Implemented /api/auth/reset-password endpoint
    • Created /set-password page for users to set/reset passwords
    • Added “Forgot Password” link on login page
    • Includes password strength validation (12+ chars, uppercase, special char)

    5. User Invitation Workflow Simplification

    • Changed from creating accounts to sending invitation emails
    • Users now sign up normally with their own password
    • Simplified “Add User” dialog – only requires email address
    • Users fill in name, password, store name during their own signup
    • More secure and less admin overhead

    6. UI/UX Improvements

    • Fixed table hover states for both light and dark modes
    • Updated email branding from “Deco Order Manager” to “Orchard Street Press – Store Manager”
    • Improved invitation email content and formatting
    • Added proper CSS context awareness for theme switching

    7. Bug Fixes

    • Fixed syntax error in users table component
    • Resolved build failures from missing imports
    • Fixed email domain configuration issues
    • Corrected table hover visibility in dark mode

    Technical Files Updated

    • src/app/api/auth/reset-password/route.ts – New password reset endpoint
    • src/app/set-password/page.tsx – New password setting page
    • src/app/page.tsx – Added “Forgot Password” link
    • src/app/globals.css – Fixed table hover for both themes
    • src/lib/email.ts – Updated branding and invitation workflow
    • src/app/dashboard/users/page.tsx – Simplified user creation dialog

    Current Status

    ✅ Email system fully functional with production Mailgun
    ✅ Password reset/set functionality working
    ✅ User invitation workflow streamlined
    ✅ UI hover states fixed for all themes
    ✅ Branding consistent across login and emails
    ✅ All build errors resolved

    Next Steps (Optional Enhancements)

    • Store assignment notification emails
    • Order status notification emails
    • Weekly summary emails for admins
    • Email template customization interface
  • 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-500bg-danger
    • text-red-600text-danger
    • bg-green-500bg-success
    • text-green-600text-success
    • bg-blue-500bg-info
    • text-blue-600text-info
    • bg-yellow-500bg-alert
    • text-yellow-600text-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