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)

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

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

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

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

✅ Eliminated Database Freezes: Sequential processing prevents connection pool exhaustion
✅ Improved Reliability: Retry logic handles transient failures automatically
✅ Better Error Handling: Proper error logging and recovery mechanisms
✅ Reduced Race Conditions: Queued processing eliminates concurrent update conflicts
✅ 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)

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)

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)

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):

Set markup percentage on product
Click “Update Blank Prices” in sync menu (not obvious this is required)
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 10, 2025

Changelog – October 10, 2025

🚨 CRITICAL DATABASE CONNECTION LEAK FIX 🚨

Fixed Massive Database Connection Leaks Causing Complete System Failure

Fixed Routes (High Priority)

The Fix Pattern

Remaining Work

Impact

Files Modified

Critical Bug Fixes

Fixed Batch Creation Error for Mixed Orders with Ignored Items

Critical Performance Fixes

Resolved Database Freezing During Order Processing

1. Sequential Processing with Rate Limiting

2. Queued Shopify Sync Operations

3. Retry Logic for API Calls

4. Improved Database Connection Management

Database Changes

New Columns Added

New Indexes

New API Endpoints

Process Pending Shopify Syncs

Benefits Achieved

Documentation Updates

ShippingEasy Optimization Guide

Mama Tried Product Sync Fixes

Files Modified

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

Discovered and Fixed Destructive Data Wipe in Variant Sync

Immediate Data Recovery (Friday Night)

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

UI Updates

Automated Pricing Population Fix

Database Backup Implementation

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

Pricing Workflow Issue Identified (Needs Future UX Improvement)

Files Modified

Python Scripts Created (Temporary, Deleted After Use)

Impact

Lessons Learned

More posts

Fulfillment – Daily Changelog – October 17, 2025

Fulfillment – Daily Changelog – October 16, 2025

Fulfillment – Daily Changelog – October 13, 2025

Fulfillment – Daily Changelog – October 10, 2025