Supabase tables for Estafeta sessions

{
  "code": "Supabase Tables for Estafeta Sessions — Migration SQL provided.\n\nCurrently the WhatsApp conversational engine (whatsapp_conversational.py line 399) uses an in-memory dict: `conversations: Dict[str, ConversationState] = {}`. This needs migration to Supabase for persistence across Cloud Run instances.\n\n## Migration SQL\n\n```sql\n-- Migration: Estafeta WhatsApp Sessions\n-- Replaces in-memory conversation state with persistent Supabase storage\n\nCREATE TABLE IF NOT EXISTS estafeta_sessions (\n    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),\n    phone_number TEXT NOT NULL,           -- E.164 format, e.g. +5215551234567\n    client_id TEXT DEFAULT 'estafeta',    -- White-label client identifier\n    state TEXT NOT NULL DEFAULT 'greeting',  -- greeting, awaiting_product, classifying, presenting_result, awaiting_confirmation, completed\n    language TEXT DEFAULT 'es',           -- Detected language (es/en)\n    context JSONB DEFAULT '{}',          -- Accumulated classification context\n    product_description TEXT,             -- Current product being classified\n    last_hts_code TEXT,                  -- Last classification result\n    last_confidence FLOAT,              -- Confidence of last classification\n    turn_count INT DEFAULT 0,           -- Number of messages in session\n    created_at TIMESTAMPTZ DEFAULT NOW(),\n    updated_at TIMESTAMPTZ DEFAULT NOW(),\n    expires_at TIMESTAMPTZ DEFAULT (NOW() + INTERVAL '30 minutes'),\n    \n    -- Index for fast lookup by phone (most common query)\n    CONSTRAINT estafeta_sessions_phone_idx UNIQUE (phone_number, client_id)\n);\n\nCREATE TABLE IF NOT EXISTS estafeta_messages (\n    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),\n    session_id UUID NOT NULL REFERENCES estafeta_sessions(id) ON DELETE CASCADE,\n    direction TEXT NOT NULL CHECK (direction IN ('inbound', 'outbound')),\n    message_type TEXT NOT NULL DEFAULT 'text',  -- text, image, audio, document, template, interactive\n    content TEXT,                         -- Message text or media description\n    media_url TEXT,                      -- Supabase Storage URL for media\n    meta_message_id TEXT,               -- WhatsApp message ID from Meta API\n    classification_result JSONB,        -- If this message triggered a classification\n    created_at TIMESTAMPTZ DEFAULT NOW()\n);\n\n-- Indexes\nCREATE INDEX IF NOT EXISTS idx_estafeta_messages_session \n    ON estafeta_messages(session_id, created_at DESC);\n\nCREATE INDEX IF NOT EXISTS idx_estafeta_sessions_active \n    ON estafeta_sessions(expires_at) \n    WHERE expires_at > NOW();\n\n-- RLS\nALTER TABLE estafeta_sessions ENABLE ROW LEVEL SECURITY;\nALTER TABLE estafeta_messages ENABLE ROW LEVEL SECURITY;\n\n-- Service role has full access\nCREATE POLICY estafeta_sessions_service ON estafeta_sessions\n    FOR ALL USING (true) WITH CHECK (true);\nCREATE POLICY estafeta_messages_service ON estafeta_messages\n    FOR ALL USING (true) WITH CHECK (true);\n\n-- Auto-update updated_at\nCREATE OR REPLACE FUNCTION update_estafeta_session_timestamp()\nRETURNS TRIGGER AS $$\nBEGIN\n    NEW.updated_at = NOW();\n    NEW.expires_at = NOW() + INTERVAL '30 minutes';\n    RETURN NEW;\nEND;\n$$ LANGUAGE plpgsql;\n\nCREATE TRIGGER estafeta_session_updated\n    BEFORE UPDATE ON estafeta_sessions\n    FOR EACH ROW\n    EXECUTE FUNCTION update_estafeta_session_timestamp();\n```\n\n## Session States\n- `greeting` → Initial state, send welcome message\n- `awaiting_product` → Waiting for product description or image\n- `classifying` → Processing classification (show typing indicator)\n- `presenting_result` → Showing HTS code + duty rate, awaiting confirmation\n- `awaiting_confirmation` → User asked follow-up question\n- `completed` → Session ended (timeout or explicit goodbye)\n\n## Integration Point\nIn whatsapp_conversational.py, replace:\n```python\n# Line 399: conversations: Dict[str, ConversationState] = {}\n# With Supabase queries:\n# GET session: supabase.table('estafeta_sessions').select('*').eq('phone_number', from_number).eq('client_id', client_id).single()\n# UPSERT session: supabase.table('estafeta_sessions').upsert({...}, on_conflict='phone_number,client_id')\n# INSERT message: supabase.table('estafeta_messages').insert({session_id, direction, content, ...})\n```\n\nNote: File not created as migration — needs `supabase migration new estafeta_sessions` to get proper timestamp prefix.",
  "content": "Supabase Tables for Estafeta Sessions — Migration SQL provided.\n\nCurrently the WhatsApp conversational engine (whatsapp_conversational.py line 399) uses an in-memory dict: `conversations: Dict[str, ConversationState] = {}`. This needs migration to Supabase for persistence across Cloud Run instances.\n\n## Migration SQL\n\n```sql\n-- Migration: Estafeta WhatsApp Sessions\n-- Replaces in-memory conversation state with persistent Supabase storage\n\nCREATE TABLE IF NOT EXISTS estafeta_sessions (\n    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),\n    phone_number TEXT NOT NULL,           -- E.164 format, e.g. +5215551234567\n    client_id TEXT DEFAULT 'estafeta',    -- White-label client identifier\n    state TEXT NOT NULL DEFAULT 'greeting',  -- greeting, awaiting_product, classifying, presenting_result, awaiting_confirmation, completed\n    language TEXT DEFAULT 'es',           -- Detected language (es/en)\n    context JSONB DEFAULT '{}',          -- Accumulated classification context\n    product_description TEXT,             -- Current product being classified\n    last_hts_code TEXT,                  -- Last classification result\n    last_confidence FLOAT,              -- Confidence of last classification\n    turn_count INT DEFAULT 0,           -- Number of messages in session\n    created_at TIMESTAMPTZ DEFAULT NOW(),\n    updated_at TIMESTAMPTZ DEFAULT NOW(),\n    expires_at TIMESTAMPTZ DEFAULT (NOW() + INTERVAL '30 minutes'),\n    \n    -- Index for fast lookup by phone (most common query)\n    CONSTRAINT estafeta_sessions_phone_idx UNIQUE (phone_number, client_id)\n);\n\nCREATE TABLE IF NOT EXISTS estafeta_messages (\n    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),\n    session_id UUID NOT NULL REFERENCES estafeta_sessions(id) ON DELETE CASCADE,\n    direction TEXT NOT NULL CHECK (direction IN ('inbound', 'outbound')),\n    message_type TEXT NOT NULL DEFAULT 'text',  -- text, image, audio, document, template, interactive\n    content TEXT,                         -- Message text or media description\n    media_url TEXT,                      -- Supabase Storage URL for media\n    meta_message_id TEXT,               -- WhatsApp message ID from Meta API\n    classification_result JSONB,        -- If this message triggered a classification\n    created_at TIMESTAMPTZ DEFAULT NOW()\n);\n\n-- Indexes\nCREATE INDEX IF NOT EXISTS idx_estafeta_messages_session \n    ON estafeta_messages(session_id, created_at DESC);\n\nCREATE INDEX IF NOT EXISTS idx_estafeta_sessions_active \n    ON estafeta_sessions(expires_at) \n    WHERE expires_at > NOW();\n\n-- RLS\nALTER TABLE estafeta_sessions ENABLE ROW LEVEL SECURITY;\nALTER TABLE estafeta_messages ENABLE ROW LEVEL SECURITY;\n\n-- Service role has full access\nCREATE POLICY estafeta_sessions_service ON estafeta_sessions\n    FOR ALL USING (true) WITH CHECK (true);\nCREATE POLICY estafeta_messages_service ON estafeta_messages\n    FOR ALL USING (true) WITH CHECK (true);\n\n-- Auto-update updated_at\nCREATE OR REPLACE FUNCTION update_estafeta_session_timestamp()\nRETURNS TRIGGER AS $$\nBEGIN\n    NEW.updated_at = NOW();\n    NEW.expires_at = NOW() + INTERVAL '30 minutes';\n    RETURN NEW;\nEND;\n$$ LANGUAGE plpgsql;\n\nCREATE TRIGGER estafeta_session_updated\n    BEFORE UPDATE ON estafeta_sessions\n    FOR EACH ROW\n    EXECUTE FUNCTION update_estafeta_session_timestamp();\n```\n\n## Session States\n- `greeting` → Initial state, send welcome message\n- `awaiting_product` → Waiting for product description or image\n- `classifying` → Processing classification (show typing indicator)\n- `presenting_result` → Showing HTS code + duty rate, awaiting confirmation\n- `awaiting_confirmation` → User asked follow-up question\n- `completed` → Session ended (timeout or explicit goodbye)\n\n## Integration Point\nIn whatsapp_conversational.py, replace:\n```python\n# Line 399: conversations: Dict[str, ConversationState] = {}\n# With Supabase queries:\n# GET session: supabase.table('estafeta_sessions').select('*').eq('phone_number', from_number).eq('client_id', client_id).single()\n# UPSERT session: supabase.table('estafeta_sessions').upsert({...}, on_conflict='phone_number,client_id')\n# INSERT message: supabase.table('estafeta_messages').insert({session_id, direction, content, ...})\n```\n\nNote: File not created as migration — needs `supabase migration new estafeta_sessions` to get proper timestamp prefix."
}