OSP Projects

Tag: database

  • Fulfillment – Daily Changelog – October 29, 2025

    Fulfillment – Daily Changelog – October 29, 2025

    Changelog – October 29, 2025

    🚀 New Features & Improvements

    Raffle Sticker Batch Page Enhancements

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

    4×6 Packing Label Enhancements

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

    Avery Packing Label Enhancements (5160 & 5167)

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

    🔧 Technical Improvements

    Frontend Components

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

    API Route Updates

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

    Styling Updates

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

    🎯 User Experience Improvements

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

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

    🖨️ Packing Lists Overhaul (Batches)

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

    Files:

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

    🧰 Database Transaction Leak Remediation (Critical)

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

    Representative routes fixed (subset):

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

    📦 Warehouse & POD Inventory Improvements

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

    Files:

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

    🛍️ Shopify Product Metadata Sync in Order Processing

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

    Files:

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

    🔤 New Supporting APIs

    • Added sizes API route to support inventory/product flows

    Files:

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

    Fulfillment – Daily Changelog – October 23, 2025

    Changelog – October 23, 2025

    🚀 New Features & Improvements

    New Batch Page Enhancements

    • Fixed ShippingEasy Preview: Resolved “shippingeasy” preview not working on new batch page by removing non-existent database columns (o.shipping_address2, o.shipping_phone)
    • Customer Information Display: Fixed missing customer information display on new batch page by updating display logic to check order.shipping_name || order.customer_name || order.shipping_address1
    • Individual Shipment Creation: Fixed “Create Shipment” button to create shipments for individual orders instead of adding all line items from all orders to one shipment
    • Individual Packing Lists: Implemented one packing list per order instead of per batch, with individual “Print Packing List” buttons for each order
    • Bulk Packing Lists: Added “Print Packing Lists” button at batch level to print all order packing lists at once
    • Work Order Generation: Modified work order creation to group by decoration process instead of product, creating one work order with multiple pages (001, 002, 003, etc.)
    • Bulk Work Orders: Added “Print Work Orders” button to print all work orders for a batch

    Database & API Improvements

    • Connection Pool Management: Fixed “too many clients already” errors by replacing individual pg.Pool instances with shared db connection from @/lib/db in multiple API routes
    • Bulk Packing Lists API: Created new /api/batches/[id]/bulk-packing-lists endpoint to fetch all packing list data for multiple orders in a single query
    • Color Data Integration: Fixed missing color information in packing lists by joining with blank_style_colors table and using bc.color_name
    • Database Schema Corrections: Updated queries to use correct column names (snapshot_variant_size, snapshot_variant_color_name, snapshot_product_title)

    Print System Overhaul

    • Print Layout Fixes: Resolved multiple print layout issues including:
    • Blank pages between packing lists
    • Incorrect page dimensions causing 2 pages per packing list
    • Single page printing all packing lists
    • Width overflow issues
    • Print Styles Optimization: Implemented multiPagePrintStyles utility for consistent print formatting across packing lists
    • Individual Packing List Pages: Created /orders/[id]/packing-list pages with auto-print functionality
    • Bulk Packing List Pages: Created /batches-new/[id]/packing-lists for printing multiple packing lists with proper page breaks

    Inventory & Shipment Management

    • Inventory Check Bypass: Added force: true parameter to shipment creation to bypass inventory checks for new batch page workflow
    • Shipment Dialog Fixes: Removed undefined state variable references (setPendingShipmentData, setNotReadyItems, setShowOverrideDialog)
    • ShippingEasy Integration: Updated ShippingEasy data preparation to match exact format of working send-to-shippingeasy endpoint

    🐛 Bug Fixes

    Database Issues

    • Column Existence Errors: Fixed multiple “column does not exist” errors by removing references to non-existent columns in mama_tried_orders table
    • Type Mismatches: Fixed string vs number ID comparison issues in shipment creation filtering
    • Connection Leaks: Resolved database connection limit errors by standardizing on shared db connection

    Print System Issues

    • Sidebar in Print: Fixed sidebar appearing in printed packing lists by adding proper CSS print media queries
    • Checkbox Issues: Removed header row checkbox and set item checkboxes to checked by default
    • Date Display: Removed unnecessary date from packing list headers
    • Page Break Issues: Fixed multiple page break problems causing incorrect pagination

    UI/UX Issues

    • Customer Information: Fixed missing customer information display on new batch page
    • Shipment Creation: Fixed shipment creation to work with individual orders instead of all orders at once
    • Button Functionality: Fixed “Create Shipment” button to properly filter line items by selected order

    🔧 Technical Improvements

    API Route Updates

    • src/app/api/orders/prepare-shippingeasy-data/route.ts: Fixed column references and data structure
    • src/app/api/orders/[id]/shipment-references/route.ts: Replaced Pool with db connection
    • src/app/api/orders/bulk-dimensions/route.ts: Replaced Pool with db connection
    • src/app/api/shipments/route.ts: Added force parameter for inventory check bypass
    • src/app/api/batches/[id]/bulk-packing-lists/route.ts: New endpoint for bulk packing list data

    Frontend Components

    • src/app/(authenticated)/batches-new/[id]/page.tsx: Enhanced with individual shipment creation and bulk printing
    • src/app/(authenticated)/orders/[id]/packing-list/page.tsx: New individual packing list page
    • src/app/(authenticated)/batches-new/[id]/packing-lists/page.tsx: New bulk packing list page
    • src/components/orders/shipment-dialog.tsx: Fixed undefined state variables and added force parameter

    Database Schema

    • Updated queries to use correct column names from mama_tried_order_line_items
    • Added proper joins to blank_style_colors for color information
    • Implemented COALESCE for fallback data when snapshot data is null

    📊 Performance Improvements

    • Single Query Optimization: Replaced multiple API calls with single bulk queries for packing list data
    • Connection Pool Efficiency: Standardized database connections to prevent connection leaks
    • Print Performance: Optimized print styles to reduce layout calculations and improve rendering speed

    🎯 User Experience Improvements

    • Simplified Workflow: New batch page now “just works” without inventory check impediments
    • Bulk Operations: Added bulk printing capabilities for both packing lists and work orders
    • Visual Consistency: Fixed print layouts to match working individual packing lists
    • Error Prevention: Added proper error handling and data validation throughout the workflow

    📝 Documentation Updates

    • Updated database management instructions
    • Added local server management guidelines
    • Enhanced API documentation for new endpoints

    Total Commits: 7
    Files Modified: 15+
    New Files Created: 4
    Major Features Added: Bulk packing lists, individual shipment creation, print system overhaul

  • 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