PostgreSQL/new_script.sql - master - NoWay1co/AudioAnalytics

PostgreSQL/new_script.sql
377 строк12 KB
Ivan
Добавлен функционал загрузки аудио записей с использованием MinIO для хранения файлов. Реализован новый эндпоинт для загрузки записей, обновлены схемы и интерфейсы для обработки статусов загрузки. Добавлены компоненты для отображения прогресса загрузки и управления записями на странице ConversationsPage. Обновлен .gitignore для исключения кэша Python.
23 янв 2026, 04:53
23 янв 2026, 04:53a7dce59

CREATE TABLE IF NOT EXISTS subscription_plans (
    id UUID PRIMARY KEY,
    name VARCHAR(255),
    description TEXT,
    max_salons INTEGER,
    max_employees INTEGER,
    max_recordings_per_month INTEGER,
    price_monthly DECIMAL,
    price_yearly DECIMAL,
    currency VARCHAR(30),
    features JSONB,
    is_active BOOL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE IF NOT EXISTS users (
    id UUID PRIMARY KEY,
    email VARCHAR(100) UNIQUE NOT NULL,
    password_hash VARCHAR(255),
    first_name VARCHAR(50),
    last_name VARCHAR(50),
    phone VARCHAR(20),
    role VARCHAR(30),
    organization_id UUID,
    salon_id UUID,
    is_active BOOL,
    is_verified BOOL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    last_login_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE IF NOT EXISTS organizations (
    id UUID PRIMARY KEY,
    name VARCHAR(255) NOT NULL,
    legal_name VARCHAR(255) NOT NULL,
    inn VARCHAR(20) NOT NULL,
    email VARCHAR(100) UNIQUE NOT NULL,
    phone VARCHAR(20),
    owner_id UUID,
    settings JSONB,
    is_active BOOL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE IF NOT EXISTS salons (
    id UUID PRIMARY KEY,
    organization_id UUID,
    name VARCHAR(255) NOT NULL,
    address TEXT NOT NULL,
    city VARCHAR(50) NOT NULL,
    phone VARCHAR(20),
    manager_id UUID,
    working_hours JSONB,
    is_active BOOL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE IF NOT EXISTS employees (
    id UUID PRIMARY KEY,
    salon_id UUID,
    email VARCHAR(100) UNIQUE NOT NULL,
    first_name VARCHAR(50),
    last_name VARCHAR(50),
    phone VARCHAR(20),
    telegram_id VARCHAR(20),
    position VARCHAR(30),
    is_active BOOL,
    avg_quality_score DECIMAL,
    avg_sentiment DECIMAL,
    total_recordings INTEGER,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE IF NOT EXISTS scripts (
    id UUID PRIMARY KEY,
    organization_id UUID,
    salon_id UUID,
    name VARCHAR(50),
    description TEXT,
    type VARCHAR(30),
    current_version INTEGER,
    is_active BOOL,
    is_default BOOL,
    created_by UUID,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE IF NOT EXISTS script_steps (
    id UUID PRIMARY KEY,
    script_id UUID,
    order_index INTEGER,
    label VARCHAR(50),
    description TEXT,
    weight INTEGER,
    is_required BOOL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE IF NOT EXISTS script_versions (
    id UUID PRIMARY KEY,
    script_id UUID,
    version INTEGER,
    steps_snapshot JSONB,
    created_by UUID,
    change_note TEXT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE IF NOT EXISTS recordings (
    id UUID PRIMARY KEY,
    organization_id UUID,
    salon_id UUID,
    employee_id UUID,
    file_url VARCHAR(255),
    file_size_bytes BIGINT,
    duration_seconds INTEGER,
    channel VARCHAR(50),
    source VARCHAR(20),
    recorded_at TIMESTAMP,
    processing_status VARCHAR(20),
    processing_error TEXT,
    is_analyzed BOOL,
    has_risk BOOL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE IF NOT EXISTS transcripts (
    id UUID PRIMARY KEY,
    recording_id UUID,
    full_text TEXT,
    segments JSONB,
    language VARCHAR(30),
    confidence DECIMAL,
    word_count INTEGER,
    model_version VARCHAR(10),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE IF NOT EXISTS analyses (
    id UUID PRIMARY KEY,
    transcript_id UUID,
    recording_id UUID,
    script_id UUID,
    script_version INTEGER,
    quality_score INTEGER,
    compliance_score INTEGER,
    sentiment_score DECIMAL,
    sentiment_class VARCHAR(30),
    summary TEXT,
    key_moments JSONB,
    risk_type VARCHAR(30),
    risk_severity VARCHAR(30),
    checklist JSONB,
    model_version VARCHAR(10),
    tokens_used INTEGER,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE IF NOT EXISTS violations (
    id UUID PRIMARY KEY,
    analysis_id UUID,
    script_step_id UUID,
    type VARCHAR(30),
    severity VARCHAR(50),
    description TEXT,
    timestamp_start TIMESTAMP,
    timestamp_end TIMESTAMP,
    quote TEXT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE IF NOT EXISTS recommendations (
    id UUID PRIMARY KEY,
    employee_id UUID,
    recording_id UUID,
    analysis_id UUID,
    type VARCHAR(30),
    priority VARCHAR(30),
    title VARCHAR(255),
    description TEXT,
    example_recording_id UUID,
    status VARCHAR(30),
    sent_at TIMESTAMP,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE IF NOT EXISTS transactions (
    id UUID PRIMARY KEY,
    organization_id UUID,
    type VARCHAR(255) NOT NULL,
    amount DECIMAL,
    currency VARCHAR(20),
    balance_after DECIMAL,
    description TEXT,
    external_id VARCHAR(30),
    payment_method VARCHAR(30),
    status VARCHAR(30),
    recording_id UUID,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE IF NOT EXISTS balances (
    id UUID PRIMARY KEY,
    organization_id UUID,
    amount DECIMAL,
    currency VARCHAR(30),
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE IF NOT EXISTS subscriptions (
    id UUID PRIMARY KEY,
    organization_id UUID,
    plan_id UUID,
    billing_period VARCHAR(30),
    started_at TIMESTAMP,
    expires_at TIMESTAMP,
    status VARCHAR(30),
    external_subscription_id VARCHAR(30),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE IF NOT EXISTS notification_settings (
    id UUID PRIMARY KEY,
    user_id UUID,
    email_quality_drop BOOL,
    email_negative_spike BOOL,
    email_weekly_digest BOOL,
    quality_threshold INTEGER,
    negative_treshold DECIMAL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE IF NOT EXISTS notifications (
    id UUID PRIMARY KEY,
    user_id UUID,
    organization_id UUID,
    type VARCHAR(30),
    channel VARCHAR(30),
    title VARCHAR(30),
    recording_id UUID,
    salon_id UUID,
    employee_id UUID,
    sent_at TIMESTAMP,
    read_at TIMESTAMP,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE IF NOT EXISTS insights (
    id UUID PRIMARY KEY,
    organization_id UUID,
    salon_id UUID,
    period_start DATE,
    period_end DATE,
    type VARCHAR(30),
    category VARCHAR(30),
    title VARCHAR(30),
    description TEXT,
    metadata JSONB,
    importance_score DECIMAL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE IF NOT EXISTS audit_logs (
    id UUID PRIMARY KEY,
    user_id UUID,
    action VARCHAR(50),
    entity_type VARCHAR(30),
    entity_id UUID,
    old_values JSONB,
    new_values JSONB,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE IF NOT EXISTS sessions (
    id UUID PRIMARY KEY,
    user_id UUID,
    token_hash VARCHAR(255),
    ip_address INET,
    expires_at TIMESTAMP,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE IF NOT EXISTS ratings (
    id UUID PRIMARY KEY,
    entity_type VARCHAR(50),
    entity_id UUID,
    period_type VARCHAR(50),
    period_start DATE,
    period_end DATE,
    score DECIMAL,
    recordings_count INTEGER,
    change_percent DECIMAL,
    avg_quality DECIMAL,
    avg_sentiment DECIMAL,
    violations_count INTEGER,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

ALTER TABLE users 
ADD CONSTRAINT fk_users_organization_id FOREIGN KEY (organization_id) REFERENCES organizations(id) ON DELETE CASCADE,
ADD CONSTRAINT fk_users_salon_id FOREIGN KEY (salon_id) REFERENCES salons(id) ON DELETE CASCADE;

ALTER TABLE organizations 
ADD CONSTRAINT fk_organizations_owner_id FOREIGN KEY (owner_id) REFERENCES users(id) ON DELETE CASCADE;

ALTER TABLE salons 
ADD CONSTRAINT fk_salons_organization_id FOREIGN KEY (organization_id) REFERENCES organizations(id) ON DELETE CASCADE,
ADD CONSTRAINT fk_salons_manager_id FOREIGN KEY (manager_id) REFERENCES users(id) ON DELETE CASCADE;

ALTER TABLE employees 
ADD CONSTRAINT fk_employees_salon_id FOREIGN KEY (salon_id) REFERENCES salons(id) ON DELETE CASCADE;

ALTER TABLE scripts 
ADD CONSTRAINT fk_scripts_organization_id FOREIGN KEY (organization_id) REFERENCES organizations(id) ON DELETE CASCADE,
ADD CONSTRAINT fk_scripts_salon_id FOREIGN KEY (salon_id) REFERENCES salons(id) ON DELETE CASCADE,
ADD CONSTRAINT fk_scripts_created_by FOREIGN KEY (created_by) REFERENCES users(id) ON DELETE CASCADE;

ALTER TABLE script_steps 
ADD CONSTRAINT fk_script_steps_script_id FOREIGN KEY (script_id) REFERENCES scripts(id) ON DELETE CASCADE;

ALTER TABLE script_versions 
ADD CONSTRAINT fk_script_versions_script_id FOREIGN KEY (script_id) REFERENCES scripts(id) ON DELETE CASCADE,
ADD CONSTRAINT fk_script_versions_created_by FOREIGN KEY (created_by) REFERENCES users(id) ON DELETE CASCADE;

ALTER TABLE recordings 
ADD CONSTRAINT fk_recordings_organization_id FOREIGN KEY (organization_id) REFERENCES organizations(id) ON DELETE CASCADE,
ADD CONSTRAINT fk_recordings_salon_id FOREIGN KEY (salon_id) REFERENCES salons(id) ON DELETE CASCADE,
ADD CONSTRAINT fk_recordings_employee_id FOREIGN KEY (employee_id) REFERENCES employees(id) ON DELETE CASCADE;

ALTER TABLE transcripts 
ADD CONSTRAINT fk_transcripts_recording_id FOREIGN KEY (recording_id) REFERENCES recordings(id) ON DELETE CASCADE;

ALTER TABLE analyses 
ADD CONSTRAINT fk_analyses_transcript_id FOREIGN KEY (transcript_id) REFERENCES transcripts(id) ON DELETE CASCADE,
ADD CONSTRAINT fk_analyses_script_id FOREIGN KEY (script_id) REFERENCES scripts(id) ON DELETE CASCADE,
ADD CONSTRAINT fk_analyses_recording_id FOREIGN KEY (recording_id) REFERENCES recordings(id) ON DELETE CASCADE;

ALTER TABLE violations 
ADD CONSTRAINT fk_violations_analysis_id FOREIGN KEY (analysis_id) REFERENCES analyses(id) ON DELETE CASCADE,
ADD CONSTRAINT fk_violations_script_step_id FOREIGN KEY (script_step_id) REFERENCES script_steps(id) ON DELETE CASCADE;

ALTER TABLE recommendations 
ADD CONSTRAINT fk_recommendations_employee_id FOREIGN KEY (employee_id) REFERENCES employees(id) ON DELETE CASCADE,
ADD CONSTRAINT fk_recommendations_recording_id FOREIGN KEY (recording_id) REFERENCES recordings(id) ON DELETE CASCADE,
ADD CONSTRAINT fk_recommendations_analysis_id FOREIGN KEY (analysis_id) REFERENCES analyses(id) ON DELETE CASCADE,
ADD CONSTRAINT fk_recommendations_example_recording_id FOREIGN KEY (example_recording_id) REFERENCES recordings(id);

ALTER TABLE transactions 
ADD CONSTRAINT fk_transactions_organization_id FOREIGN KEY (organization_id) REFERENCES organizations(id) ON DELETE CASCADE,
ADD CONSTRAINT fk_transactions_recording_id FOREIGN KEY (recording_id) REFERENCES recordings(id) ON DELETE CASCADE;

ALTER TABLE balances 
ADD CONSTRAINT fk_balances_organization_id FOREIGN KEY (organization_id) REFERENCES organizations(id) ON DELETE CASCADE;

ALTER TABLE subscriptions 
ADD CONSTRAINT fk_subscriptions_organization_id FOREIGN KEY (organization_id) REFERENCES organizations(id) ON DELETE CASCADE,
ADD CONSTRAINT fk_subscriptions_plan_id FOREIGN KEY (plan_id) REFERENCES subscription_plans(id) ON DELETE CASCADE;

ALTER TABLE notification_settings 
ADD CONSTRAINT fk_notification_settings_user_id FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE;

ALTER TABLE notifications 
ADD CONSTRAINT fk_notifications_user_id FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
ADD CONSTRAINT fk_notifications_organization_id FOREIGN KEY (organization_id) REFERENCES organizations(id) ON DELETE CASCADE,
ADD CONSTRAINT fk_notifications_recording_id FOREIGN KEY (recording_id) REFERENCES recordings(id) ON DELETE CASCADE,
ADD CONSTRAINT fk_notifications_salon_id FOREIGN KEY (salon_id) REFERENCES salons(id) ON DELETE CASCADE,
ADD CONSTRAINT fk_notifications_employee_id FOREIGN KEY (employee_id) REFERENCES employees(id) ON DELETE CASCADE;

ALTER TABLE insights 
ADD CONSTRAINT fk_insights_organization_id FOREIGN KEY (organization_id) REFERENCES organizations(id) ON DELETE CASCADE,
ADD CONSTRAINT fk_insights_salon_id FOREIGN KEY (salon_id) REFERENCES salons(id) ON DELETE CASCADE;

ALTER TABLE audit_logs 
ADD CONSTRAINT fk_audit_logs_user_id FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE;

ALTER TABLE sessions 
ADD CONSTRAINT fk_sessions_user_id FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE;