Skip to main content

Fix: Receipt Number Lookup for Returns/Exchanges

Date: 2026-02-28
Issue: Receipt lookup was failing with "Sale not found" error
Root Cause: receipt_number field was not being populated when creating sales

Problem​

When attempting to look up a sale by receipt number for returns or exchanges, the API would return a 404 "Sale not found" error. This occurred because:

  1. The receipt_number column was added to the sale table in migration 2026-02-28t17-08-33-add-return-exchange-support.mjs
  2. However, the column was not being populated when creating new sales
  3. Existing sales had NULL values for receipt_number
  4. The lookup query was searching by receipt_number, which was always NULL

Solutions Implemented​

1. Frontend: Graceful Error Handling​

Files Modified:

  • apps/frontend-pwa/src/pages/returns/ReturnProcessingPage.tsx
  • apps/frontend-pwa/src/pages/exchanges/ExchangeProcessingPage.tsx

Changes:

  • Added early validation in useEffect to check if required query parameters (receiptNumber or saleId) are present
  • If missing, show a user-friendly toast message and redirect to the lookup page
  • Prevents the component from throwing errors when accessed without parameters

2. Backend: Populate receipt_number on Sale Creation​

File Modified: apps/backend/src/sales/application/sales.service.ts

Changes:

const toCreate: InsertableSale = {
  ...sale,
  sessionId,
  ...(documentNumber ? { documentNumber: documentNumber } : {}),
  ...(documentNumber ? { receiptNumber: documentNumber } : {}), // ← Added
  ...(costDetail ? { costDetail: costDetail } : {}),
};

Now when a sale is created, receiptNumber is automatically set to the same value as documentNumber.

3. Backend: Flexible Receipt Number Lookup​

File Modified: apps/backend/src/retail-return/infrastructure/return-transaction.repository.ts

Changes:

  • Updated findSaleByReceiptNumber() to search by both receipt_number and document_number
  • Handles both exact matches and numeric lookups with auto-padding
  • If the receipt number is numeric (e.g., "195"), it will also try with leading zeros (e.g., "000195")
  • This allows users to enter receipt numbers with or without leading zeros

Logic:

  1. Try exact match on both receipt_number and document_number
  2. If not found and receipt number is numeric, pad to 6 digits with leading zeros and try again on both fields

4. Database: Data Migration​

File Created: packages/backend/database/src/migrations/2026-02-28t21-22-00-populate-receipt-numbers.mjs

Purpose:

  • Populates receipt_number from document_number for all existing sales
  • Ensures historical data is accessible via receipt lookup

SQL:

UPDATE sale 
SET receipt_number = document_number 
WHERE receipt_number IS NULL 
AND document_number IS NOT NULL

5. Manual Database Update (Development)​

For the local development database, we manually ran:

UPDATE sale SET receipt_number = document_number 
WHERE receipt_number IS NULL AND document_number IS NOT NULL;

This updated 198 sales records.

6. Backend: Allow Returns for "submitted" Sales​

Files Modified:

  • apps/backend/src/retail-return/application/return-transaction.service.ts
  • apps/backend/src/retail-return/application/exchange-transaction.service.ts

Issue: Returns were failing with "Can only return completed sales" because all sales in the database have status "submitted", not "completed".

Changes:

  • Updated validation to accept both "completed" and "submitted" status for returns/exchanges
  • This aligns with the current sale workflow where sales are finalized with status "submitted"

7. Frontend: Use Actual User ID for createdBy​

Files Modified:

  • apps/frontend-pwa/src/pages/returns/ReturnProcessingPage.tsx
  • apps/frontend-pwa/src/pages/exchanges/ExchangeProcessingPage.tsx

Issue: Returns were failing with foreign key constraint error because createdBy was being set to businessId instead of a valid userId.

Changes:

  • Extract db_user_id from Firebase authentication claims
  • Pass the actual user ID as createdBy when initiating returns/exchanges
  • Added validation to ensure user ID is present before proceeding

8. Backend: Fix Inventory Ledger createdBy for Returns​

Files Modified:

  • apps/backend/src/retail-return/application/return-transaction.service.ts

Issue: Return commit was failing with foreign key constraint error: Key (updated_by)=(cash) is not present in table "user". The createdBy field for inventory ledger entries was incorrectly falling back to params.refundMethods[0]?.paymentMethodId, which is a string like "cash", not a user UUID.

Changes:

  • Changed fallback from params.refundMethods[0]?.paymentMethodId to updatedReturn.createdBy
  • Ensures inventory ledger entries always use a valid user UUID

9. Backend: Fix Inventory Ledger createdBy for Exchanges​

Files Modified:

  • apps/backend/src/retail-return/application/exchange-transaction.service.ts

Issue: Exchange commit was failing with foreign key constraint error: invalid input syntax for type uuid: "cash". The createdBy field for inventory ledger entries was incorrectly falling back to settlementMethods[0]?.paymentMethodId, which is a string like "cash", not a user UUID.

Changes:

  • Changed fallback from settlementMethods[0]?.paymentMethodId to updatedExchange.createdBy
  • Ensures inventory ledger entries always use a valid user UUID

10. Backend: Fix Inventory Ledger Quantity Constraint for Exchanges​

Files Modified:

  • apps/backend/src/retail-return/application/return-inventory.service.ts

Issue: Exchange commit was failing with check constraint error: value for domain quantity_dec violates check constraint "quantity_dec_check". The database domain quantity_dec requires values >= 0, but the code was inserting negative quantities for sale lines using -line.quantity.

Root Cause: The inventory ledger schema uses an absolute value approach where:

  • The quantity field must always be positive (>= 0)
  • The direction (in/out) is determined by the source_type field

Changes:

  • Changed quantity: -line.quantity to quantity: Math.abs(line.quantity) for sale lines
  • Changed amount, baseAmount, cost, and baseCost to also use Math.abs() for consistency
  • The source_type field ("sale") already indicates this is a deduction from inventory

11. Backend: Add Product Name to Exchange Sale Lines​

Files Modified:

  • apps/backend/src/retail-return/application/exchange-transaction.service.ts

Issue: When adding new items to an exchange, the product name was not being populated, causing the frontend to display "Unknown" for the product name.

Changes:

  • Injected ProductsRepository into ExchangeTransactionService
  • Modified addExchangeSaleLine to fetch product details and include productName in the sale line item
  • Product name is now properly populated when creating sale lines

12. Frontend: Fix Dark Mode Selector Visibility in Exchange and Return Processing​

Files Modified:

  • apps/frontend-pwa/src/pages/exchanges/ExchangeProcessingPage.tsx
  • apps/frontend-pwa/src/pages/returns/ReturnProcessingPage.tsx

Issue: In dark mode, the "Return Reason" and "Condition" dropdown selectors appeared as blank white rectangles. The selected values were present but invisible because the text color was not contrasting properly with the background.

Changes:

  • Changed selector classes from hardcoded border-gray-300 to theme-aware classes:
    • border-input - adapts border color to theme
    • bg-background - uses theme background color
    • text-foreground - uses theme text color
  • Applied fix to both Exchange and Return processing pages
  • Selectors now properly display text in both light and dark modes

13. Frontend: Fix Product Search Results Visibility in Dark Mode​

Files Modified:

  • apps/frontend-pwa/src/components/common/ProductQuickAdd.tsx

Issue: In dark mode, when searching for products, the product names and details were not visible in the search results dropdown. The text was using hardcoded light theme colors that didn't contrast with dark backgrounds.

Changes:

  • Updated search results dropdown to use theme-aware classes:
    • bg-popover instead of bg-white - adapts background to theme
    • border-border instead of border-gray-200 - adapts border color
    • text-foreground instead of default - ensures product name is visible
    • text-muted-foreground instead of text-gray-500 - for SKU and price
    • hover:bg-accent instead of hover:bg-gray-50 - adapts hover state
    • divide-border instead of divide-gray-100 - adapts divider color
  • Search icon and loading states also updated to use text-muted-foreground
  • Selected product preview now uses bg-accent and text-muted-foreground
  • Product names and details now properly visible in both light and dark modes

Testing​

After the fixes:

✅ Receipt lookup with full number works: GET /returns/sales/000195/lookup
✅ Receipt lookup with short number works: GET /returns/sales/195/lookup
✅ Frontend gracefully handles missing parameters
✅ New sales automatically get receiptNumber populated
✅ Returns work with "submitted" status sales
✅ Returns use correct user ID from authentication claims
✅ Return inventory ledger entries use valid user UUID
✅ Exchange inventory ledger entries use valid user UUID
✅ Exchange inventory ledger quantities are positive (constraint satisfied)
✅ Product names display correctly in exchange "New Items" section
✅ Selectors (Return Reason, Condition) are visible in dark mode
✅ Product search results (name, SKU, price) are visible in dark mode

Migration Plan​

For staging and production environments:

  1. Deploy backend changes (sales.service.ts, return-transaction.repository.ts)
  2. Run migration 2026-02-28t21-22-00-populate-receipt-numbers.mjs to populate existing sales
  3. Deploy frontend changes (ReturnProcessingPage.tsx, ExchangeProcessingPage.tsx)
  • Schema: packages/backend/database/src/migrations/2026-02-28t17-08-33-add-return-exchange-support.mjs
  • Specs: specs/014-exchange-return/
  • Documentation: specs/014-exchange-return/PHASE-6-COMPLETE.md

Future Considerations​

  • Consider adding a database constraint or trigger to ensure receipt_number is always populated
  • Consider making receipt_number a required field (NOT NULL) after ensuring all records are populated
  • Consider adding a unique constraint on (business_id, receipt_number) if receipt numbers should be unique per business