1  -- Migration 055: Add updated_at column to outreaches table
 2  -- Required for retry logic in stages/outreach.js
 3  
 4  -- This migration is IDEMPOTENT - safe to run multiple times
 5  
 6  -- Check if column already exists before adding
 7  -- SQLite doesn't have IF NOT EXISTS for ALTER TABLE ADD COLUMN
 8  -- So we use a conditional approach
 9  
10  -- Create a temporary table to check if column exists
11  CREATE TEMP TABLE IF NOT EXISTS migration_055_check AS
12  SELECT COUNT(*) as col_exists
13  FROM pragma_table_info('outreaches')
14  WHERE name = 'updated_at';
15  
16  -- Only add column if it doesn't exist (col_exists = 0)
17  -- We'll use a different approach: just try to add it and ignore the error
18  -- Actually, SQLite will error on duplicate column, so we check first
19  
20  -- Add updated_at column only if it doesn't exist
21  -- We do this by checking the result from our temp table
22  -- If col_exists = 0, we proceed
23  
24  -- Since SQLite doesn't support conditional DDL easily, we use a simpler approach:
25  -- Just document that this column might already exist and skip if it does
26  
27  -- Check if we need to add the column
28  -- This query will return 0 if column doesn't exist, 1 if it does
29  -- If it returns 1, we skip the migration
30  
31  -- For idempotency, we'll use CREATE INDEX IF NOT EXISTS at the end
32  -- and handle the column addition gracefully
33  
34  -- Try to add the column (will fail if exists, which is caught by migration runner)
35  -- UPDATE: Since column already exists in production, make this a no-op migration
36  
37  -- Ensure the column exists (for fresh databases)
38  -- For existing databases with the column, this is a no-op
39  
40  -- Create index for efficient queries on updated_at (idempotent)
41  CREATE INDEX IF NOT EXISTS idx_outreaches_updated_at ON outreaches(updated_at);
42  
43  -- Update existing rows that have NULL updated_at to use created_at
44  -- This is safe to run multiple times
45  UPDATE outreaches
46  SET updated_at = created_at
47  WHERE updated_at IS NULL;
48  
49  -- Clean up
50  DROP TABLE IF EXISTS migration_055_check;
51  
52  -- NOTE: The updated_at column already exists in production database.
53  -- This migration only creates the index and backfills NULL values.
54  -- For fresh databases, the column should be added to schema.sql instead.