1  -- Migration: Add 'ignore' status to sites table
 2  -- Created: 2026-02-03
 3  -- Reason: Allow manually parking sites to prevent further processing
 4  
 5  -- SQLite doesn't support modifying CHECK constraints directly
 6  -- We need to recreate the table with the new constraint
 7  
 8  -- Step 1: Create new table with updated CHECK constraint
 9  CREATE TABLE sites_new (
10      id INTEGER PRIMARY KEY AUTOINCREMENT,
11      domain TEXT NOT NULL,
12      landing_page_url TEXT NOT NULL,
13      keyword TEXT NOT NULL,
14      screenshot_path TEXT,
15      html_dom TEXT,
16      http_status_code INTEGER,
17      score_json TEXT,
18      score REAL,
19      grade TEXT,
20      scored_at DATETIME,
21      rescored_at DATETIME,
22      enriched_at DATETIME,
23      competitor_domain TEXT,
24      contacts_json TEXT,
25      city TEXT,
26      country_code TEXT,
27      last_outreach_at DATETIME,
28      status TEXT DEFAULT 'found' CHECK(status IN (
29          'found', 'assets_captured', 'scored', 'rescored', 'enriched',
30          'proposals_drafted', 'outreach_sent', 'ignore'
31      )),
32      error_message TEXT,
33      created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
34      updated_at DATETIME DEFAULT CURRENT_TIMESTAMP
35  );
36  
37  -- Step 2: Copy all data from old table
38  INSERT INTO sites_new SELECT * FROM sites;
39  
40  -- Step 3: Drop old table
41  DROP TABLE sites;
42  
43  -- Step 4: Rename new table to sites
44  ALTER TABLE sites_new RENAME TO sites;
45  
46  -- Step 5: Recreate indexes
47  CREATE INDEX IF NOT EXISTS idx_sites_keyword ON sites(keyword);
48  CREATE INDEX IF NOT EXISTS idx_sites_status ON sites(status);
49  CREATE INDEX IF NOT EXISTS idx_sites_score ON sites(score);
50  CREATE INDEX IF NOT EXISTS idx_sites_last_outreach ON sites(last_outreach_at);
51  CREATE INDEX IF NOT EXISTS idx_sites_country ON sites(country_code);