max / goingson
| 1 | -- Re-anchor recurrence chains to their earliest-created member. |
| 2 | -- |
| 3 | -- Migration 047 backfilled recurrence_parent_id using MIN(id) over random |
| 4 | -- UUIDv4 ids, so each reconstructed chain was anchored at an arbitrary sibling |
| 5 | -- rather than its first instance. Runtime-created chains always anchor at the |
| 6 | -- first instance (the earliest task has recurrence_parent_id IS NULL); this |
| 7 | -- aligns the backfilled historical data with that single invariant. |
| 8 | -- |
| 9 | -- Within each existing chain group (the set of tasks sharing one effective root, |
| 10 | -- COALESCE(recurrence_parent_id, id)), the earliest task by created_at -- with id |
| 11 | -- as a deterministic tie-break so every device computes the same result -- becomes |
| 12 | -- the root, and every other member points to it. Chain membership is unchanged; |
| 13 | -- only which member is the anchor changes. The operation is idempotent. |
| 14 | |
| 15 | -- Snapshot the current grouping up front so the UPDATE below cannot read its own |
| 16 | -- partial writes: the chosen anchor depends only on this immutable snapshot and on |
| 17 | -- tasks.id (never on the recurrence_parent_id column being rewritten). |
| 18 | CREATE TEMP TABLE _chain_member AS |
| 19 | SELECT id AS member_id, |
| 20 | COALESCE(recurrence_parent_id, id) AS root_id, |
| 21 | created_at |
| 22 | FROM tasks |
| 23 | WHERE recurrence_parent_id IS NOT NULL OR recurrence != 'None'; |
| 24 | |
| 25 | -- Point every member at the earliest member of its group. The chosen anchor |
| 26 | -- temporarily points to itself and is nulled in the next statement. |
| 27 | UPDATE tasks |
| 28 | SET recurrence_parent_id = ( |
| 29 | SELECT ( |
| 30 | SELECT m2.member_id |
| 31 | FROM _chain_member m2 |
| 32 | WHERE m2.root_id = m1.root_id |
| 33 | ORDER BY m2.created_at ASC, m2.member_id ASC |
| 34 | LIMIT 1 |
| 35 | ) |
| 36 | FROM _chain_member m1 |
| 37 | WHERE m1.member_id = tasks.id |
| 38 | ) |
| 39 | WHERE recurrence_parent_id IS NOT NULL OR recurrence != 'None'; |
| 40 | |
| 41 | -- The anchor pointed at itself above; null it so it is the chain root. |
| 42 | UPDATE tasks |
| 43 | SET recurrence_parent_id = NULL |
| 44 | WHERE recurrence_parent_id = id; |
| 45 | |
| 46 | _chain_member; |
| 47 |