1 files changed, 23 insertions, 0 deletions
diff --git a/migrations/030_abandon_stale_recurrences.sql b/migrations/030_abandon_stale_recurrences.sql
new file mode 100644
index 0000000..f8ec225
--- /dev/null
+++ b/migrations/030_abandon_stale_recurrences.sql
@@ -0,0 +1,23 @@
+-- A recurring task that wasn't done before its next iteration arrived is stale,
+-- not still-owed: the next one supersedes it. Without a way to record that, every
+-- skipped occurrence stayed open forever and piled up as permanent overdue items
+-- (found live: "Bills" and "Bones's meds" each sitting in the list twice, an
+-- unfinished instance alongside its already-generated successor).
+--
+-- Deliberately a separate flag rather than reusing `completed`: these were never
+-- done, and marking them completed would falsely credit them and pollute
+-- completion history.
+ALTER TABLE native_tasks ADD COLUMN abandoned BOOLEAN DEFAULT 0;
+
+-- Backfill: any uncompleted recurring instance that already has a later sibling
+-- in its series was superseded before this flag existed.
+UPDATE native_tasks
+SET abandoned = 1
+WHERE completed = 0
+  AND recurrence_series_id != ''
+  AND due_date IS NOT NULL
+  AND EXISTS (
+    SELECT 1 FROM native_tasks later
+    WHERE later.recurrence_series_id = native_tasks.recurrence_series_id
+      AND later.due_date > native_tasks.due_date
+  );