Spaces:
Running
Running
File size: 4,444 Bytes
1a5863d 06e73d2 1a5863d 06e73d2 | 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 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 | DROP TABLE IF EXISTS user_analysis_history CASCADE;
DROP TABLE IF EXISTS feedback CASCADE;
DROP TABLE IF EXISTS predictions CASCADE;
DROP TABLE IF EXISTS news_articles CASCADE;
DROP TABLE IF EXISTS model_performance CASCADE;
DROP TABLE IF EXISTS user_sessions CASCADE;
DROP VIEW IF EXISTS prediction_stats CASCADE;
DROP VIEW IF EXISTS daily_predictions CASCADE;
DROP VIEW IF EXISTS feedback_accuracy CASCADE;
DROP VIEW IF EXISTS model_comparison CASCADE;
CREATE EXTENSION IF NOT EXISTS "uuid-ossp";
CREATE TABLE predictions (
id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
article_id VARCHAR NOT NULL UNIQUE,
text TEXT,
predicted_label VARCHAR(50) NOT NULL,
confidence FLOAT NOT NULL,
model_name VARCHAR(100) NOT NULL,
explanation JSONB,
created_at TIMESTAMPTZ DEFAULT NOW()
);
CREATE INDEX idx_pred_created ON predictions(created_at DESC);
CREATE INDEX idx_pred_label ON predictions(predicted_label);
CREATE INDEX idx_pred_model ON predictions(model_name);
CREATE TABLE feedback (
id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
article_id VARCHAR NOT NULL,
predicted_label VARCHAR(50) NOT NULL,
actual_label VARCHAR(50) NOT NULL,
user_comment TEXT,
created_at TIMESTAMPTZ DEFAULT NOW()
);
CREATE INDEX idx_fb_created ON feedback(created_at DESC);
CREATE INDEX idx_fb_article ON feedback(article_id);
CREATE TABLE news_articles (
id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
title TEXT NOT NULL,
description TEXT,
content TEXT,
url TEXT NOT NULL UNIQUE,
image_url TEXT,
published_at TIMESTAMPTZ,
source_name VARCHAR(255),
source_url TEXT,
fetched_at TIMESTAMPTZ DEFAULT NOW(),
analyzed BOOLEAN DEFAULT FALSE,
prediction_id UUID
);
CREATE INDEX idx_news_published ON news_articles(published_at DESC);
CREATE INDEX idx_news_analyzed ON news_articles(analyzed);
CREATE TABLE model_performance (
id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
model_name VARCHAR(100) NOT NULL,
accuracy FLOAT,
precision FLOAT,
recall FLOAT,
f1_score FLOAT,
total_predictions INTEGER DEFAULT 0,
correct_predictions INTEGER DEFAULT 0,
evaluated_at TIMESTAMPTZ DEFAULT NOW()
);
CREATE TABLE user_sessions (
id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
session_id VARCHAR NOT NULL UNIQUE,
user_agent TEXT,
ip_address INET,
created_at TIMESTAMPTZ DEFAULT NOW(),
last_activity TIMESTAMPTZ DEFAULT NOW()
);
CREATE TABLE 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
);
CREATE INDEX idx_history_session_created ON user_analysis_history(session_id, created_at DESC);
CREATE INDEX idx_history_article ON user_analysis_history(article_id);
ALTER TABLE predictions DISABLE ROW LEVEL SECURITY;
ALTER TABLE feedback DISABLE ROW LEVEL SECURITY;
ALTER TABLE news_articles DISABLE ROW LEVEL SECURITY;
ALTER TABLE model_performance DISABLE ROW LEVEL SECURITY;
ALTER TABLE user_sessions DISABLE ROW LEVEL SECURITY;
ALTER TABLE user_analysis_history ENABLE ROW LEVEL SECURITY;
CREATE POLICY "allow_all_history" ON user_analysis_history FOR ALL USING (true) WITH CHECK (true);
CREATE VIEW prediction_stats AS
SELECT predicted_label, COUNT(*) AS total_count, AVG(confidence) AS avg_confidence
FROM predictions
GROUP BY predicted_label;
CREATE VIEW feedback_accuracy AS
SELECT predicted_label, actual_label, COUNT(*) AS count
FROM feedback
GROUP BY predicted_label, actual_label
ORDER BY count DESC;
|