1  -- Migration 044: Create prompt feedback and learning tables
 2  -- Date: 2026-02-11
 3  -- Purpose: Track rework patterns to improve prompts over time
 4  
 5  -- Prompt feedback tracking
 6  CREATE TABLE IF NOT EXISTS prompt_feedback (
 7      id INTEGER PRIMARY KEY AUTOINCREMENT,
 8      outreach_id INTEGER REFERENCES outreaches(id) ON DELETE CASCADE,
 9      site_id INTEGER REFERENCES sites(id) ON DELETE CASCADE,
10      prompt_file TEXT NOT NULL,           -- 'PROPOSAL.md', 'CONVERSION-SCORING.md', etc.
11      prompt_version INTEGER DEFAULT 1,    -- Track which version was used
12      feedback_type TEXT NOT NULL CHECK(feedback_type IN (
13          'rework',           -- Operator requested rework
14          'rejected',         -- Operator rejected outreach
15          'approved',         -- Operator approved (positive signal)
16          'conversion',       -- Resulted in sale (strong positive)
17          'no_response'       -- Sent but no response after 14 days (weak negative)
18      )),
19      feedback_text TEXT,                  -- Rework instructions or rejection reason
20      feedback_category TEXT,              -- Auto-categorized: 'tone', 'length', 'urgency', 'personalization', etc.
21      sentiment_before TEXT,               -- Site sentiment before outreach
22      sentiment_after TEXT,                -- Conversation sentiment (if replied)
23      resulted_in_sale BOOLEAN DEFAULT 0,
24      created_at DATETIME DEFAULT CURRENT_TIMESTAMP
25  );
26  
27  CREATE INDEX IF NOT EXISTS idx_prompt_feedback_prompt ON prompt_feedback(prompt_file, prompt_version);
28  CREATE INDEX IF NOT EXISTS idx_prompt_feedback_type ON prompt_feedback(feedback_type);
29  CREATE INDEX IF NOT EXISTS idx_prompt_feedback_category ON prompt_feedback(feedback_category);
30  CREATE INDEX IF NOT EXISTS idx_prompt_feedback_created ON prompt_feedback(created_at);
31  CREATE INDEX IF NOT EXISTS idx_prompt_feedback_outreach ON prompt_feedback(outreach_id);
32  
33  -- Prompt versions tracking table
34  CREATE TABLE IF NOT EXISTS prompt_versions (
35      id INTEGER PRIMARY KEY AUTOINCREMENT,
36      prompt_file TEXT NOT NULL,
37      version INTEGER NOT NULL,
38      content TEXT NOT NULL,              -- Full prompt text
39      change_summary TEXT,                 -- What changed in this version
40      learning_applied TEXT,               -- What feedback patterns influenced this version
41      created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
42      UNIQUE(prompt_file, version)
43  );
44  
45  CREATE INDEX IF NOT EXISTS idx_prompt_versions_file ON prompt_versions(prompt_file, version DESC);
46  
47  -- Store latest export sheet URL for dashboard link
48  CREATE TABLE IF NOT EXISTS export_sheets (
49      id INTEGER PRIMARY KEY AUTOINCREMENT,
50      sheet_id TEXT NOT NULL,
51      sheet_url TEXT NOT NULL,
52      outreach_count INTEGER NOT NULL,
53      created_at DATETIME DEFAULT CURRENT_TIMESTAMP
54  );
55  
56  CREATE INDEX IF NOT EXISTS idx_export_sheets_created ON export_sheets(created_at DESC);