When a Legacy TEXT Array Broke the Admin Dashboard

The Lima admin dashboard went down recently, returning 500 errors across the board. The root cause was a legacy TEXT[] column from before our rebrand, habits.completed_dates, that still stored dates as ‘YYYY-MM-DD’ strings. A database query, countHabitsCompletedSince, was comparing this text array against a timestamp, leading to a type mismatch and a server error.

The Problem: A Dashboard in Crisis

Users trying to access any part of the /admin/stats endpoint were met with a dreaded 500 error. This meant no visibility into crucial metrics, a significant disruption for anyone managing data within the Lima app. The immediate symptom was a failing countHabitsCompletedSince query that powers a core part of the admin view.

The real kicker was that this issue passed every test we had. Our test data, like most well-maintained datasets, adhered to our current schema assumptions. It did not carry the old shape of the habits.completed_dates column. Only specific production rows, which had been migrated directly from the old app before the MoWave One rebrand, contained the legacy TEXT[] date strings. This meant the bug was a silent landmine, waiting for just the right data shape to trigger.

The Technical Cause: Data Shape Drift

The core of the problem lay in how habits.completed_dates was being handled. Before the rebrand, this column was a TEXT[] storing dates as strings. Post-rebrand, our application expected and generally worked with actual date or timestamp types for comparisons. The countHabitsCompletedSince query attempted to compare elements from this TEXT[] column directly against a timestamp. Postgres, when faced with comparing a text string like ‘2023-01-15’ against a true timestamp, does not implicitly convert it in a way that allows for a valid < or >= operation. This type incompatibility caused the query to throw an error, which then bubbled up to a 500 on the API endpoint.

Here is a simplified version of what the problematic query conceptually looked like:

SELECT count(*) FROM habits WHERE completed_dates @> ARRAY[CAST(? AS TEXT)] AND d IN (SELECT UNNEST(completed_dates) FROM habits WHERE UNNEST(completed_dates) >= CAST(? AS TIMESTAMP));

The issue was specifically in the comparison: UNNEST(completed_dates) >= CAST(? AS TIMESTAMP). Comparing a TEXT element from completed_dates directly against a TIMESTAMP without proper casting was the failure point.

The Fix: Proper Type Casting and Regression Testing

The solution involved explicitly casting the text date strings to a DATE type before comparison. This allowed Postgres to perform a valid date comparison against the provided timestamp. We modified the query to ensure that each element d from the completed_dates array was cast to a DATE type. The corrected logic looked something like this:

SELECT count(*) FROM habits WHERE completed_dates @> ARRAY[CAST(? AS TEXT)] AND EXISTS (SELECT 1 FROM UNNEST(completed_dates) AS d WHERE d::date >= CAST(? AS date));

This small but crucial change ensured that the comparison d::date >= CAST(? AS date) was always between two DATE types, resolving the error. Before deploying, we validated this fix against a copy of our production data that contained the problematic legacy rows, confirming that the admin dashboard was fully restored.

To prevent this specific type of regression, we added a new integration test, AdminStatsQueryIT. This test explicitly seeds data with the legacy TEXT[] date string shape in the habits.completed_dates column. This ensures that any future changes to the query or schema will be tested against the real-world data shape that caused this outage. This new test closes the gap that allowed this bug to reach production.

Takeaways

Rebrands and migrations are prime opportunities for data-shape landmines. If a column predates your current schema assumptions, it is highly probable that your existing tests do not account for its old shape. Always consider the full lifecycle of your data, especially when it spans across major application versions or rebrands like MoWave One’s transition to the Lima app (https://getlima.app). Building specific regression tests for these legacy data shapes, once identified, is key to preventing future outages. Test data should ideally reflect the full spectrum of actual production data, including historical anomalies, not just the pristine data conforming to the latest schema.