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