divine-mobile: Skill for Claude Code

Denormalized Priority Column Staleness

Problem

When ordering records by denormalized aggregate columns (like total_loops, vine_count, order_count), the query returns items in the wrong priority order because the denormalized values are stale, unpopulated, or incorrect.

Context / Trigger Conditions

• A batch job processes items in unexpected order
• Top items (by some aggregate metric) are processed last or skipped
• ORDER BY aggregate_column DESC doesn't return expected results
• Aggregate columns show 0 or NULL for records that should have high values
• Only a subset of records have the aggregate column populated
• Backfill scripts may have run for some records but not others

Root Cause

Denormalized columns (copies of aggregated data stored for query performance) can become stale when:

1. Initial data migration didn't populate them
2. Backfill scripts only ran for some records
3. New source records were added without updating the denormalized column
4. The aggregation logic changed but the column wasn't recalculated

Solution

Option 1: Compute at Query Time (Immediate Fix)

Join with the source table to compute actual aggregates:

-- BEFORE (broken): Uses potentially stale denormalized column
SELECT user_id, username
FROM users
WHERE status = 'pending'
ORDER BY total_loops DESC NULLS LAST;

-- AFTER (fixed): Computes actual aggregate from source
SELECT u.user_id, u.username,
       COALESCE(SUM(vm.loops), 0) as actual_total_loops
FROM users u
LEFT JOIN vine_metadata vm ON u.user_id = vm.user_id
WHERE u.status = 'pending'
GROUP BY u.user_id, u.username
ORDER BY actual_total_loops DESC;

Option 2: Backfill the Denormalized Column (Permanent Fix)

Update the denormalized column from the source data:

UPDATE users u
SET total_loops = subq.actual_loops
FROM (
    SELECT user_id, COALESCE(SUM(loops), 0) as actual_loops
    FROM vine_metadata
    GROUP BY user_id
) subq
WHERE u.user_id = subq.user_id;

Option 3: Use Materialized Views (Best of Both)

Create a materialized view for the aggregates: