max / goingson
| 1 | -- Backfill recurrence_parent_id for existing recurring tasks. |
| 2 | -- Links recurring tasks that share a description + recurrence pattern + user_id |
| 3 | -- into one chain, anchored at MIN(id). |
| 4 | -- |
| 5 | -- NOTE: MIN(id) over random UUIDv4 ids picks an arbitrary-but-stable sibling as |
| 6 | -- the anchor, NOT the earliest-created one. That is harmless on its own (the |
| 7 | -- anchor is only a join key; chain membership and ordering key off created_at), |
| 8 | -- but migration 051 re-anchors these chains to their earliest member so the |
| 9 | -- stored data matches the runtime invariant (first instance = root). |
| 10 | -- |
| 11 | -- This is best-effort: tasks whose description was edited after spawning won't |
| 12 | -- match, and two genuinely separate series with the same description collapse |
| 13 | -- into one chain -- both are rare and inherent to reconstructing chains from |
| 14 | -- historical rows that predate recurrence_parent_id. |
| 15 | |
| 16 | UPDATE tasks |
| 17 | SET recurrence_parent_id = ( |
| 18 | SELECT MIN(t2.id) FROM tasks t2 |
| 19 | WHERE t2.user_id = tasks.user_id |
| 20 | AND t2.description = tasks.description |
| 21 | AND t2.recurrence = tasks.recurrence |
| 22 | AND t2.recurrence != 'None' |
| 23 | ) |
| 24 | WHERE recurrence != 'None' |
| 25 | AND recurrence_parent_id IS NULL; |
| 26 | |
| 27 | -- Don't set recurrence_parent_id to self (the root task itself). |
| 28 | UPDATE tasks |
| 29 | SET recurrence_parent_id = NULL |
| 30 | WHERE recurrence_parent_id = id; |
| 31 |