Debugging Data Mismatches Between UI and Backend Logic

Ever shown users options in a dropdown that don’t actually work? I recently debugged a classic denormalization issue where the UI displayed 434 currency options based on a cached summary table, but the backend filter logic checked live data in a separate table — and only 79 of those currencies had available inventory.

The problem occurred because two different data sources were being used:

• Display logic: Read from a forecasts table that stored pre-computed aggregates
• Filter logic: Queried the actual orders table for real-time availability

The fix started with comparing aggregate counts to confirm the mismatch:

-- Backend filter (real-time availability)
SELECT COUNT(DISTINCT product_id) 
FROM orders 
WHERE status = 'available';
-- Result: 79

-- Display logic (cached aggregates)
SELECT COUNT(*) 
FROM forecasts 
WHERE JSON_CONTAINS(metadata, '"currency_code"');
-- Result: 434

The root cause? The forecast table wasn’t being updated when inventory sold out. Users saw 434 options but could only actually use 79 of them.

When to Cache vs. Query Live

Caching computed data is powerful, but it introduces a new problem: cache invalidation. Before building a cache layer, ask:

• What triggers a cache update? (e.g., order placed, inventory restocked)
• Can users tolerate stale data? (product listings: yes; checkout: no)
• Is the performance gain worth the complexity?

In this case, the better approach was to either:

1. Build the dropdown from the same real-time query the filter uses
2. Add proper cache invalidation hooks when inventory changes

Lesson: When you split display and business logic across different data sources, always verify your cache invalidation strategy matches your actual business rules. The mismatch becomes obvious when you compare the numbers.