1  -- Migration 054: Add constraint to prevent assets_captured with null screenshot_path
 2  -- This prevents the bug fixed in commit a3bd55b from recurring
 3  
 4  -- SQLite doesn't support adding CHECK constraints to existing tables directly
 5  -- So we create a trigger instead
 6  
 7  -- Drop trigger if exists (for idempotency)
 8  DROP TRIGGER IF EXISTS prevent_assets_captured_without_screenshots;
 9  
10  -- Create trigger to enforce constraint
11  CREATE TRIGGER IF NOT EXISTS prevent_assets_captured_without_screenshots
12  BEFORE UPDATE OF status ON sites
13  FOR EACH ROW
14  WHEN NEW.status = 'assets_captured' AND NEW.screenshot_path IS NULL
15  BEGIN
16    SELECT RAISE(ABORT, 'Cannot set status to assets_captured when screenshot_path is NULL');
17  END;
18  
19  -- Also prevent INSERT with this invalid state
20  DROP TRIGGER IF EXISTS prevent_insert_assets_captured_without_screenshots;
21  
22  CREATE TRIGGER IF NOT EXISTS prevent_insert_assets_captured_without_screenshots
23  BEFORE INSERT ON sites
24  FOR EACH ROW
25  WHEN NEW.status = 'assets_captured' AND NEW.screenshot_path IS NULL
26  BEGIN
27    SELECT RAISE(ABORT, 'Cannot insert site with status assets_captured when screenshot_path is NULL');
28  END;