Spaces:
Running
Running
File size: 3,475 Bytes
1a5863d | 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 | -- Phase 2: User Analysis History Database Migration
-- This script creates the user_analysis_history table and related indexes
-- Execute this in your Supabase SQL Editor
-- Step 1: Create the user_analysis_history table
CREATE TABLE IF NOT EXISTS user_analysis_history (
id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
session_id VARCHAR(36) NOT NULL,
article_id VARCHAR(36) NOT NULL UNIQUE,
text_preview VARCHAR(200) NOT NULL,
predicted_label VARCHAR(50) NOT NULL CHECK (predicted_label IN ('True', 'Fake', 'Satire', 'Bias')),
confidence FLOAT NOT NULL CHECK (confidence >= 0.0 AND confidence <= 1.0),
model_name VARCHAR(100) NOT NULL,
created_at TIMESTAMPTZ DEFAULT NOW() NOT NULL,
CONSTRAINT fk_article FOREIGN KEY (article_id) REFERENCES predictions(article_id) ON DELETE CASCADE
);
-- Step 2: Create indexes for efficient queries
CREATE INDEX IF NOT EXISTS idx_history_session_created ON user_analysis_history(session_id, created_at DESC);
CREATE INDEX IF NOT EXISTS idx_history_article ON user_analysis_history(article_id);
-- Step 3: Enable row-level security
ALTER TABLE user_analysis_history ENABLE ROW LEVEL SECURITY;
-- Step 4: Create policy to allow all operations (for development)
-- Note: In production, you should restrict this based on your security requirements
DROP POLICY IF EXISTS "allow_all_history" ON user_analysis_history;
CREATE POLICY "allow_all_history" ON user_analysis_history FOR ALL USING (true) WITH CHECK (true);
-- Step 5: Verify the table was created
SELECT
table_name,
column_name,
data_type,
is_nullable,
column_default
FROM information_schema.columns
WHERE table_name = 'user_analysis_history'
ORDER BY ordinal_position;
-- Step 6: Verify indexes were created
SELECT
indexname,
indexdef
FROM pg_indexes
WHERE tablename = 'user_analysis_history';
-- Step 7: Verify RLS policy was created
SELECT
policyname,
permissive,
roles,
cmd
FROM pg_policies
WHERE tablename = 'user_analysis_history';
-- Optional: Insert a test record to verify everything works
-- Uncomment the following lines to test (replace with actual article_id from predictions table)
/*
DO $$
DECLARE
test_article_id VARCHAR(36);
test_session_id VARCHAR(36);
BEGIN
-- First, insert a test prediction
test_article_id := gen_random_uuid()::text;
test_session_id := gen_random_uuid()::text;
INSERT INTO predictions (article_id, text, predicted_label, confidence, model_name)
VALUES (test_article_id, 'Test article for migration verification', 'True', 0.95, 'ensemble');
-- Then, insert a test history record
INSERT INTO user_analysis_history (session_id, article_id, text_preview, predicted_label, confidence, model_name)
VALUES (test_session_id, test_article_id, 'Test article for migration verification', 'True', 0.95, 'ensemble');
-- Verify the record was inserted
IF EXISTS (SELECT 1 FROM user_analysis_history WHERE article_id = test_article_id) THEN
RAISE NOTICE 'Test record inserted successfully!';
ELSE
RAISE EXCEPTION 'Test record insertion failed!';
END IF;
-- Clean up test data
DELETE FROM user_analysis_history WHERE article_id = test_article_id;
DELETE FROM predictions WHERE article_id = test_article_id;
RAISE NOTICE 'Test data cleaned up. Migration verification complete!';
END $$;
*/
|