File size: 11,628 Bytes
ac02020 | 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 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 145 146 147 148 149 150 151 152 153 154 155 156 157 158 159 160 161 162 163 164 165 166 167 168 169 170 171 172 173 174 175 176 177 178 179 180 181 182 183 184 185 186 187 188 189 190 191 192 193 194 195 196 197 198 199 200 201 202 203 204 205 206 207 208 209 210 211 212 213 214 215 216 217 218 219 220 221 222 223 224 225 226 227 228 229 230 231 232 233 234 235 236 237 238 239 240 241 242 243 244 245 246 247 248 249 250 251 252 253 254 255 256 257 258 | -- =============================================================================
-- WHSPR — PostgreSQL Schema (Supabase)
-- =============================================================================
-- Enable UUID generation
CREATE EXTENSION IF NOT EXISTS "pgcrypto";
-- =============================================================================
-- ENUM TYPES
-- =============================================================================
CREATE TYPE user_role AS ENUM ('agent', 'supervisor');
CREATE TYPE risk_level AS ENUM ('Safe', 'Medium', 'Risky');
CREATE TYPE upload_status AS ENUM ('pending', 'processing', 'analyzed', 'failed');
CREATE TYPE emotion AS ENUM ('angry', 'frustrated', 'sad', 'neutral', 'happy', 'satisfied');
CREATE TYPE valence AS ENUM ('positive', 'negative', 'neutral');
CREATE TYPE arousal AS ENUM ('high', 'low', 'neutral');
CREATE TYPE analysis_risk AS ENUM ('Critical', 'High', 'Medium', 'Low');
CREATE TYPE csr_action AS ENUM ('ESCALATE', 'REST', 'MONITOR', 'NONE');
CREATE TYPE csr_urgency AS ENUM ('IMMEDIATE', 'HIGH', 'MEDIUM', 'LOW');
CREATE TYPE action_color AS ENUM ('red', 'orange', 'yellow', 'green');
CREATE TYPE audit_action AS ENUM ('CREATE', 'UPDATE', 'DELETE', 'LOGIN', 'LOGOUT', 'EXPORT');
-- =============================================================================
-- updated_at trigger (reused by every table)
-- =============================================================================
CREATE OR REPLACE FUNCTION set_updated_at()
RETURNS TRIGGER AS $$
BEGIN
NEW.updated_at = NOW();
RETURN NEW;
END;
$$ LANGUAGE plpgsql;
-- =============================================================================
-- USERS
-- =============================================================================
CREATE TABLE users (
id SERIAL PRIMARY KEY,
uuid UUID NOT NULL DEFAULT gen_random_uuid(),
name VARCHAR(100) NOT NULL,
email VARCHAR(150) NOT NULL,
password_hash VARCHAR(255) NOT NULL,
role user_role NOT NULL DEFAULT 'agent',
is_active BOOLEAN NOT NULL DEFAULT TRUE,
last_login_at TIMESTAMPTZ,
created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
CONSTRAINT uq_users_uuid UNIQUE (uuid),
CONSTRAINT uq_users_email UNIQUE (email)
);
CREATE TRIGGER trg_users_updated_at BEFORE UPDATE ON users FOR EACH ROW EXECUTE FUNCTION set_updated_at();
-- =============================================================================
-- CLUSTERS
-- =============================================================================
CREATE TABLE clusters (
id SERIAL PRIMARY KEY,
name VARCHAR(100) NOT NULL,
region VARCHAR(100) NOT NULL,
overall_risk risk_level NOT NULL DEFAULT 'Safe',
created_by INTEGER REFERENCES users (id) ON DELETE SET NULL,
created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
CONSTRAINT uq_clusters_name UNIQUE (name)
);
CREATE TRIGGER trg_clusters_updated_at BEFORE UPDATE ON clusters FOR EACH ROW EXECUTE FUNCTION set_updated_at();
-- =============================================================================
-- AGENTS
-- =============================================================================
CREATE TABLE agents (
id SERIAL PRIMARY KEY,
cluster_id INTEGER NOT NULL REFERENCES clusters (id) ON DELETE RESTRICT,
name VARCHAR(100) NOT NULL,
email VARCHAR(150) NOT NULL,
role VARCHAR(80) NOT NULL DEFAULT 'CSR',
risk_level risk_level NOT NULL DEFAULT 'Safe',
is_active BOOLEAN NOT NULL DEFAULT TRUE,
created_by INTEGER REFERENCES users (id) ON DELETE SET NULL,
created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
CONSTRAINT uq_agents_email UNIQUE (email)
);
CREATE TRIGGER trg_agents_updated_at BEFORE UPDATE ON agents FOR EACH ROW EXECUTE FUNCTION set_updated_at();
-- =============================================================================
-- CALLS
-- =============================================================================
CREATE TABLE calls (
id SERIAL PRIMARY KEY,
uuid UUID NOT NULL DEFAULT gen_random_uuid(),
agent_id INTEGER NOT NULL REFERENCES agents (id) ON DELETE RESTRICT,
cluster_id INTEGER NOT NULL REFERENCES clusters (id) ON DELETE RESTRICT,
filename VARCHAR(255) NOT NULL,
file_path VARCHAR(512),
file_size BIGINT,
duration_sec SMALLINT,
upload_status upload_status NOT NULL DEFAULT 'pending',
uploaded_by INTEGER REFERENCES users (id) ON DELETE SET NULL,
call_date DATE,
created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
CONSTRAINT uq_calls_uuid UNIQUE (uuid)
);
CREATE INDEX ix_calls_agent ON calls (agent_id);
CREATE INDEX ix_calls_cluster ON calls (cluster_id);
CREATE INDEX ix_calls_call_date ON calls (call_date);
CREATE INDEX ix_calls_upload_status ON calls (upload_status);
CREATE TRIGGER trg_calls_updated_at BEFORE UPDATE ON calls FOR EACH ROW EXECUTE FUNCTION set_updated_at();
-- =============================================================================
-- ANALYSIS RESULTS
-- =============================================================================
CREATE TABLE analysis_results (
id SERIAL PRIMARY KEY,
call_id INTEGER NOT NULL UNIQUE REFERENCES calls (id) ON DELETE CASCADE,
predicted_emotion emotion NOT NULL,
confidence NUMERIC(5,4) NOT NULL,
all_probabilities JSONB,
valence valence,
arousal arousal,
risk_level analysis_risk NOT NULL DEFAULT 'Low',
transcription_text TEXT,
transcription_lang VARCHAR(10) DEFAULT 'en',
transcription_duration NUMERIC(8,2),
speaker_mode VARCHAR(50),
agent_channel VARCHAR(10),
caller_channel VARCHAR(10),
analyzed_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);
-- =============================================================================
-- CSR RECOMMENDATIONS
-- =============================================================================
CREATE TABLE csr_recommendations (
id SERIAL PRIMARY KEY,
analysis_result_id INTEGER NOT NULL UNIQUE REFERENCES analysis_results (id) ON DELETE CASCADE,
action csr_action NOT NULL DEFAULT 'NONE',
urgency csr_urgency NOT NULL DEFAULT 'LOW',
reason TEXT,
instruction TEXT,
action_color action_color,
recommended_tone TEXT,
example_phrases JSONB,
do_list JSONB,
dont_list JSONB,
created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);
-- =============================================================================
-- ESCALATIONS
-- =============================================================================
CREATE TABLE escalations (
id SERIAL PRIMARY KEY,
call_id INTEGER NOT NULL REFERENCES calls (id) ON DELETE CASCADE,
agent_id INTEGER NOT NULL REFERENCES agents (id) ON DELETE CASCADE,
escalated_to INTEGER REFERENCES users (id) ON DELETE SET NULL,
reason TEXT,
resolved BOOLEAN NOT NULL DEFAULT FALSE,
resolved_at TIMESTAMPTZ,
resolved_by INTEGER REFERENCES users (id) ON DELETE SET NULL,
notes TEXT,
created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);
CREATE INDEX ix_esc_call ON escalations (call_id);
CREATE INDEX ix_esc_agent ON escalations (agent_id);
CREATE INDEX ix_esc_resolved ON escalations (resolved);
CREATE TRIGGER trg_esc_updated_at BEFORE UPDATE ON escalations FOR EACH ROW EXECUTE FUNCTION set_updated_at();
-- =============================================================================
-- AGENT DAILY STATS
-- =============================================================================
CREATE TABLE agent_daily_stats (
id SERIAL PRIMARY KEY,
agent_id INTEGER NOT NULL REFERENCES agents (id) ON DELETE CASCADE,
stat_date DATE NOT NULL,
calls_count SMALLINT NOT NULL DEFAULT 0,
angry_count SMALLINT NOT NULL DEFAULT 0,
frustrated_count SMALLINT NOT NULL DEFAULT 0,
neutral_count SMALLINT NOT NULL DEFAULT 0,
happy_count SMALLINT NOT NULL DEFAULT 0,
sad_count SMALLINT NOT NULL DEFAULT 0,
escalations SMALLINT NOT NULL DEFAULT 0,
avg_risk_score NUMERIC(5,2) NOT NULL DEFAULT 0.00,
created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
CONSTRAINT uq_agent_daily UNIQUE (agent_id, stat_date)
);
CREATE INDEX ix_agent_daily_date ON agent_daily_stats (stat_date);
CREATE TRIGGER trg_agent_daily_updated_at BEFORE UPDATE ON agent_daily_stats FOR EACH ROW EXECUTE FUNCTION set_updated_at();
-- =============================================================================
-- CLUSTER DAILY STATS
-- =============================================================================
CREATE TABLE cluster_daily_stats (
id SERIAL PRIMARY KEY,
cluster_id INTEGER NOT NULL REFERENCES clusters (id) ON DELETE CASCADE,
stat_date DATE NOT NULL,
calls_count SMALLINT NOT NULL DEFAULT 0,
risky_agents SMALLINT NOT NULL DEFAULT 0,
medium_agents SMALLINT NOT NULL DEFAULT 0,
safe_agents SMALLINT NOT NULL DEFAULT 0,
escalations SMALLINT NOT NULL DEFAULT 0,
created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
CONSTRAINT uq_cluster_daily UNIQUE (cluster_id, stat_date)
);
CREATE INDEX ix_cluster_daily_date ON cluster_daily_stats (stat_date);
CREATE TRIGGER trg_cluster_daily_updated_at BEFORE UPDATE ON cluster_daily_stats FOR EACH ROW EXECUTE FUNCTION set_updated_at();
-- =============================================================================
-- USER SESSIONS
-- =============================================================================
CREATE TABLE user_sessions (
id SERIAL PRIMARY KEY,
user_id INTEGER NOT NULL REFERENCES users (id) ON DELETE CASCADE,
refresh_token VARCHAR(512) NOT NULL,
ip_address VARCHAR(45),
user_agent VARCHAR(255),
expires_at TIMESTAMPTZ NOT NULL,
revoked BOOLEAN NOT NULL DEFAULT FALSE,
created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);
CREATE INDEX ix_sessions_user ON user_sessions (user_id);
CREATE INDEX ix_sessions_token ON user_sessions (refresh_token);
-- =============================================================================
-- AUDIT LOGS
-- =============================================================================
CREATE TABLE audit_logs (
id BIGSERIAL PRIMARY KEY,
user_id INTEGER REFERENCES users (id) ON DELETE SET NULL,
action audit_action NOT NULL,
table_name VARCHAR(64),
record_id INTEGER,
old_values JSONB,
new_values JSONB,
ip_address VARCHAR(45),
created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);
CREATE INDEX ix_audit_user ON audit_logs (user_id);
CREATE INDEX ix_audit_table ON audit_logs (table_name, record_id);
CREATE INDEX ix_audit_created_at ON audit_logs (created_at);
|