migrateTasksSessionIDUnique creates the partial unique index on tasks(session_id) WHERE session_id IS NOT NULL. Older DBs may have two tasks sharing a session_id (the old `flow update task --session-id` flag could silently overwrite a binding without clearing the prior owner; or a user manually edit
(db *sql.DB)
| 418 | // CREATE UNIQUE INDEX in indexesPostMigrate would fail on a DB with |
| 419 | // pre-existing duplicates. |
| 420 | if err := migrateTasksSessionIDUnique(db); err != nil { |
| 421 | return fmt.Errorf("migrate session-id uniqueness: %w", err) |
| 422 | } |
| 423 | return nil |
| 424 | } |
| 425 | |
| 426 | // migrateTasksSessionIDUnique creates the partial unique index on |
| 427 | // tasks(session_id) WHERE session_id IS NOT NULL. Older DBs may have |
| 428 | // two tasks sharing a session_id (the old `flow update task |
| 429 | // --session-id` flag could silently overwrite a binding without |
| 430 | // clearing the prior owner; or a user manually edited the row). A |
| 431 | // flat CREATE UNIQUE INDEX would fail on those DBs, so this function |
| 432 | // first deduplicates by: |
| 433 | // |
| 434 | // 1. Listing every session_id that appears on 2+ tasks. |
| 435 | // 2. For each such session_id, ordering the carrier tasks by |
| 436 | // updated_at DESC, slug ASC. The first row keeps the binding. |
| 437 | // 3. The remaining rows get session_id=NULL, session_started=NULL, |
| 438 | // and status='backlog' (the only state legal for a NULL |
| 439 | // session_id under the invariant). A stderr summary explains |
| 440 | // which task kept the session and which were demoted. |
| 441 | // |
| 442 | // Idempotent: probes sqlite_master for the index first; subsequent |
| 443 | // calls are no-ops once the index exists. |
| 444 | func migrateTasksSessionIDUnique(db *sql.DB) error { |
| 445 | var existing sql.NullString |
| 446 | err := db.QueryRow( |
| 447 | `SELECT sql FROM sqlite_master WHERE type='index' AND name='idx_tasks_session_id'`, |
| 448 | ).Scan(&existing) |
| 449 | if err == nil && existing.Valid { |
| 450 | return nil |
| 451 | } |
| 452 | if err != nil && err != sql.ErrNoRows { |
| 453 | return fmt.Errorf("probe unique index: %w", err) |
| 454 | } |
| 455 | |
| 456 | rows, err := db.Query( |
| 457 | `SELECT session_id FROM tasks |
| 458 | WHERE session_id IS NOT NULL |
| 459 | GROUP BY session_id |
| 460 | HAVING COUNT(*) > 1 |
| 461 | ORDER BY session_id`, |
| 462 | ) |
| 463 | if err != nil { |
| 464 | return fmt.Errorf("scan duplicates: %w", err) |
| 465 | } |
| 466 | var dupedSIDs []string |
| 467 | for rows.Next() { |
| 468 | var sid string |
| 469 | if err := rows.Scan(&sid); err != nil { |
| 470 | rows.Close() |
| 471 | return err |
| 472 | } |
| 473 | dupedSIDs = append(dupedSIDs, sid) |
| 474 | } |
| 475 | if err := rows.Err(); err != nil { |
| 476 | rows.Close() |
| 477 | return err |
no test coverage detected