ScreenTinker 73f41c3288 fix(zone-id): restore zone-aware playlist_items wiring (issue #3 follow-up) ... Phase 2 (assignments -> playlist_items) dropped zone_id during the conversion: migrateAssignmentsToPlaylists INSERTed only (playlist_id, content_id, widget_id, sort_order, duration_sec), and the new playlist_items DDL omitted the zone_id column entirely. Every write path on top of playlist_items inherited that omission - the multi-zone layout assignment feature stopped working. Frontend always sent zone_id correctly (device-detail.js:1015,1072 POST and PUT both include it; api.addAssignment and api.updateAssignment forward the body verbatim). Server silently dropped it. The assignments.js PUT route was the most direct evidence: it destructured zone_id from req.body but never added it to the updates array. Schema: - schema.sql: add zone_id TEXT REFERENCES layout_zones(id) ON DELETE SET NULL to fresh-install DDL. - database.js migrations[]: add idempotent ALTER TABLE for existing installs (the surrounding try/catch loop handles duplicate-column). Backfill (new gated migration phase2_zone_id_backfill): - Pre-migration snapshot copied to db/remote_display.pre-zone-id- backfill-<ts>.db (one-off for this migration; the general every-migration-snapshot framework is a separate concern, not built here). - Best-effort UPDATE playlist_items.zone_id from surviving assignments rows via device.playlist_id + content_id/widget_id match, LIMIT 1 for the multi-match edge case. - Regenerates published_snapshot for every published playlist so the JSON the player consumes carries zone_id going forward. Even with zero rows backfilled (the common case post-Phase-2 cleanup) this closes the snapshot-staleness gap. - Stamps schema_migrations regardless so it won't re-run on next boot. - On the live local DB: 0 playlist_items backfilled, 18 published_snapshots regenerated. On the April 13 prod fixture (sandboxed copy): 0 backfilled, 7 regenerated. Expected and matches our pre-flight finding that assignments was effectively scrubbed of zone_id everywhere. Route wiring (7 sites + 1 shared constant): - assignments.js ITEM_SELECT: project pi.zone_id (read path so the frontend display at device-detail.js:500 surfaces the value). - assignments.js POST INSERT: include zone_id column + value. - assignments.js PUT: actually use the already-destructured zone_id in the updates allow-list. Treats undefined as "no change" so a PUT that omits zone_id leaves the existing value intact; any explicit value (including null) is written. - assignments.js copy-to INSERT: preserve a.zone_id during device-to-device playlist copy. - playlists.js buildSnapshotItems: project pi.zone_id so the snapshot JSON carries it. This is what the player's renderZones loop reads (player/index.html:1338 matches a.zone_id === zone.id). - playlists.js discard-revert INSERT: restore zone_id from snapshot item on revert. Out of scope (verified safe by SQL semantics + UI inspection): - playlists.js POST item-add and PUT item-update in the playlist-detail surface: the UI there doesn't expose zone editing, and their SQL leaves zone_id NULL on insert / untouched on update. No regression. - Other playlists.js SELECT projections (lines 141, 190, 240, 265, 334, 379, 419) all use SELECT pi.* and auto-pick zone_id once the column exists. - Kiosk-page assign at device-detail.js:1027 doesn't send zone_id; separate pre-existing gap, not part of this regression. Tests (all local, no push, no prod deploy): - Migration boot on live local DB: clean, idempotent (second boot skips the gated function). - Migration boot on April 13 prod fixture (sandboxed copy at /tmp/zone-fix-fixtures/test-run.db): cleanly runs the full migration stack (multi-tenancy + 5 other phases the fixture predated) then the new zone_id backfill. Live local DB untouched. - 8 SQL-level route behavior tests pass: INSERT stores zone_id, PUT changes/clears zone_id, ITEM_SELECT and buildSnapshotItems projections include zone_id, copy-to preserves, discard-revert restores from snapshot JSON, undefined zone_id in PUT leaves existing value intact. Not verified: end-to-end multi-zone playback on a real device. The SQL + snapshot JSON layer is correct (player consumes playlist.find(a => a.zone_id === zone.id) and now gets the right zone_id back from the snapshot); confirming render-to-correct-zone on actual hardware is the next step before prod deploy. Co-Authored-By: Claude Opus 4.7 (1M context) <noreply@anthropic.com>		2026-05-14 19:20:44 -05:00
..
database.js	fix(zone-id): restore zone-aware playlist_items wiring (issue #3 follow-up)	2026-05-14 19:20:44 -05:00
schema.sql	fix(zone-id): restore zone-aware playlist_items wiring (issue #3 follow-up)	2026-05-14 19:20:44 -05:00