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)