🗄️ Схема базы данных¶

📊 Обзор¶

База данных системы охраны труда построена на PostgreSQL и содержит 33 таблицы, обеспечивающие полный функционал управления безопасностью на рабочих местах.

🏗️ Архитектура базы данных¶

erDiagram
    safety_users ||--o{ organization_members : "участвует в"
    organizations ||--o{ organization_members : "содержит"
    organizations ||--o{ work_zones : "имеет"
    organizations ||--o{ organization_positions : "содержит"
    organizations ||--o{ ppe_issuance : "ведет учет"
    organizations ||--o{ medical_examinations : "проводит"

    safety_users ||--o{ safety_documents : "загружает"
    safety_users ||--o{ safety_filled_forms : "создает"
    safety_users ||--o{ medical_examinations : "проходит"
    safety_users ||--o{ user_work_zones : "назначен на"

    safety_documents ||--o{ safety_filled_forms : "используется в"
    safety_documents ||--o{ safety_document_access : "имеет доступ"

    positions ||--o{ issue_norms : "имеет нормы"
    protection_types ||--o{ issue_norms : "определяет тип"
    ppe_items ||--o{ issue_norms : "входит в норму"

    hazard_classifiers ||--o{ risk_measures : "требует мер"
    positions ||--o{ risk_measures : "связан с мерами"

👥 Пользователи и аутентификация¶

safety_users¶

Основная таблица пользователей системы.

CREATE TABLE safety_users (
    id SERIAL PRIMARY KEY,
    username VARCHAR(50) UNIQUE NOT NULL,
    email VARCHAR(100) UNIQUE NOT NULL,
    full_name VARCHAR(200) NOT NULL,
    password_hash VARCHAR(255) NOT NULL,
    employee_number VARCHAR(20) UNIQUE,
    position VARCHAR(100),
    department VARCHAR(100),
    role VARCHAR(20) DEFAULT 'user',
    is_active BOOLEAN DEFAULT TRUE,
    hire_date TIMESTAMP,
    password_set BOOLEAN DEFAULT FALSE,
    first_login_completed BOOLEAN DEFAULT FALSE,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- Индексы
CREATE INDEX idx_users_username ON safety_users(username);
CREATE INDEX idx_users_email ON safety_users(email);
CREATE INDEX idx_users_employee_number ON safety_users(employee_number);
CREATE INDEX idx_users_role ON safety_users(role);

Описание полей: - id - уникальный идентификатор пользователя - username - имя пользователя для входа - email - электронная почта - full_name - полное имя пользователя - password_hash - хеш пароля - employee_number - табельный номер сотрудника - position - должность - department - отдел - role - роль в системе (admin, manager, user) - is_active - активность пользователя - hire_date - дата приема на работу - password_set - установлен ли пароль - first_login_completed - завершен ли первый вход

🏢 Организации и участники¶

organizations¶

Таблица организаций с полной информацией из ФНС.

CREATE TABLE organizations (
    id SERIAL PRIMARY KEY,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,

    -- Основные данные
    type VARCHAR NOT NULL, -- ИП или ЮЛ
    inn VARCHAR(12) UNIQUE NOT NULL, -- ИНН (10 для ЮЛ, 12 для ИП)
    ogrn VARCHAR(15), -- ОГРН/ОГРНИП
    registration_date TIMESTAMP,
    status VARCHAR,

    -- Данные для ИП
    full_name VARCHAR,
    short_name VARCHAR,
    gender VARCHAR,
    citizenship VARCHAR,

    -- Адрес
    postal_code VARCHAR,
    region_code VARCHAR,
    full_address TEXT,
    address_date TIMESTAMP,

    -- Контактные данные
    email VARCHAR,
    contacts JSONB,

    -- Коды статистики
    okpo VARCHAR,
    oktmo VARCHAR,
    okfs VARCHAR,
    okogu VARCHAR,

    -- Налоговая информация
    tax_registration_date TIMESTAMP,
    tax_office_reg VARCHAR,
    tax_office_reg_date TIMESTAMP,
    tax_office VARCHAR,
    tax_office_date TIMESTAMP,

    -- Информация ПФР
    pf_registration_number VARCHAR,
    pf_registration_date TIMESTAMP,
    pf_code VARCHAR,

    -- Виды деятельности
    main_activity_code VARCHAR,
    main_activity_text TEXT,
    main_activity_date TIMESTAMP,
    additional_activities JSONB,

    -- История изменений и события
    history JSONB,
    events JSONB,

    -- Система налогообложения
    tax_system VARCHAR,
    tax_system_date TIMESTAMP
);

-- Индексы
CREATE INDEX idx_organizations_inn ON organizations(inn);
CREATE INDEX idx_organizations_type ON organizations(type);

organization_members¶

Связь пользователей с организациями и их ролями.

CREATE TABLE organization_members (
    id SERIAL PRIMARY KEY,
    user_id INTEGER NOT NULL REFERENCES safety_users(id),
    organization_id INTEGER NOT NULL REFERENCES organizations(id),
    role VARCHAR(20) NOT NULL, -- owner, admin, manager, specialist
    is_active BOOLEAN DEFAULT TRUE,
    invited_by INTEGER REFERENCES safety_users(id),
    invited_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    accepted_at TIMESTAMP,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- Индексы
CREATE INDEX idx_org_members_user_id ON organization_members(user_id);
CREATE INDEX idx_org_members_org_id ON organization_members(organization_id);
CREATE INDEX idx_org_members_role ON organization_members(role);

organization_invitations¶

Приглашения пользователей в организации.

CREATE TABLE organization_invitations (
    id SERIAL PRIMARY KEY,
    organization_id INTEGER NOT NULL REFERENCES organizations(id),
    email VARCHAR(100) NOT NULL,
    role VARCHAR(20) NOT NULL,
    invited_by INTEGER NOT NULL REFERENCES safety_users(id),
    token VARCHAR(255) UNIQUE NOT NULL,
    expires_at TIMESTAMP NOT NULL,
    is_accepted BOOLEAN DEFAULT FALSE,
    accepted_at TIMESTAMP,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

📚 Документооборот¶

safety_documents¶

Документы по охране труда.

CREATE TABLE safety_documents (
    id SERIAL PRIMARY KEY,
    name VARCHAR(255) NOT NULL,
    description TEXT,
    file_path VARCHAR(500) NOT NULL,
    file_type VARCHAR(10) NOT NULL, -- xlsx, docx, pdf
    file_size INTEGER,
    original_filename VARCHAR(255),
    document_type VARCHAR(50), -- instruction, form, certificate
    category VARCHAR(100), -- fire_safety, electrical_safety
    is_template BOOLEAN DEFAULT FALSE,
    template_fields JSONB,
    status VARCHAR(20) DEFAULT 'active', -- active, archived, draft
    uploaded_by INTEGER REFERENCES safety_users(id),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- Индексы
CREATE INDEX idx_documents_uploaded_by ON safety_documents(uploaded_by);
CREATE INDEX idx_documents_type ON safety_documents(document_type);
CREATE INDEX idx_documents_category ON safety_documents(category);
CREATE INDEX idx_documents_status ON safety_documents(status);

safety_filled_forms¶

Заполненные формы на основе шаблонов.

CREATE TABLE safety_filled_forms (
    id SERIAL PRIMARY KEY,
    user_id INTEGER NOT NULL REFERENCES safety_users(id),
    document_id INTEGER NOT NULL REFERENCES safety_documents(id),
    form_data JSONB NOT NULL,
    status VARCHAR(20) DEFAULT 'draft', -- draft, submitted, approved, rejected
    submitted_at TIMESTAMP,
    reviewed_by INTEGER REFERENCES safety_users(id),
    reviewed_at TIMESTAMP,
    review_notes TEXT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- Индексы
CREATE INDEX idx_forms_user_id ON safety_filled_forms(user_id);
CREATE INDEX idx_forms_document_id ON safety_filled_forms(document_id);
CREATE INDEX idx_forms_status ON safety_filled_forms(status);

safety_document_access_control¶

Контроль доступа к документам.

CREATE TABLE safety_document_access_control (
    id SERIAL PRIMARY KEY,
    document_id INTEGER NOT NULL REFERENCES safety_documents(id),
    access_level VARCHAR(20) NOT NULL DEFAULT 'organization', -- public, system, organization
    organization_id INTEGER REFERENCES organizations(id),
    system_only BOOLEAN DEFAULT FALSE,
    allow_read BOOLEAN DEFAULT TRUE,
    allow_download BOOLEAN DEFAULT TRUE,
    allow_preview BOOLEAN DEFAULT TRUE,
    created_by INTEGER NOT NULL REFERENCES safety_users(id),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_by INTEGER REFERENCES safety_users(id)
);

🛡️ СИЗ (Средства индивидуальной защиты)¶

positions¶

Справочник должностей.

CREATE TABLE positions (
    id SERIAL PRIMARY KEY,
    name VARCHAR(255) NOT NULL,
    category VARCHAR(100) NOT NULL,
    activity_area VARCHAR(255),
    danger TEXT,
    risk_management TEXT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

protection_types¶

Типы средств защиты.

CREATE TABLE protection_types (
    id SERIAL PRIMARY KEY,
    name VARCHAR(255) UNIQUE NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

ppe_items¶

Наименования СИЗ.

CREATE TABLE ppe_items (
    id SERIAL PRIMARY KEY,
    name TEXT UNIQUE NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

issue_norms¶

Нормы выдачи СИЗ.

CREATE TABLE issue_norms (
    id SERIAL PRIMARY KEY,
    position_id INTEGER NOT NULL REFERENCES positions(id),
    protection_type_id INTEGER NOT NULL REFERENCES protection_types(id),
    ppe_item_id INTEGER NOT NULL REFERENCES ppe_items(id),
    norm_text VARCHAR(255),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

ppe_issuance¶

Журнал выдачи СИЗ.

CREATE TABLE ppe_issuance (
    id SERIAL PRIMARY KEY,
    organization_id INTEGER NOT NULL REFERENCES organizations(id),
    employee_name VARCHAR(255),
    position_name VARCHAR(255),
    protection_type VARCHAR(255),
    ppe_item TEXT,
    ppe_item_id INTEGER REFERENCES ppe_items(id),
    norm_text VARCHAR(255),
    quantity INTEGER DEFAULT 1,
    unit VARCHAR(50),
    employee_signature TEXT,
    issuer_name VARCHAR(255),
    issuer_signature TEXT,
    issue_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    expiry_date TIMESTAMP,
    recipient_user_id INTEGER REFERENCES safety_users(id),
    comment TEXT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- Индексы
CREATE INDEX idx_ppe_issuance_org_id ON ppe_issuance(organization_id);
CREATE INDEX idx_ppe_issuance_recipient ON ppe_issuance(recipient_user_id);
CREATE INDEX idx_ppe_issuance_expiry ON ppe_issuance(expiry_date);

⚠️ Опасности и риски¶

hazard_classifiers¶

Классификатор опасностей.

CREATE TABLE hazard_classifiers (
    id SERIAL PRIMARY KEY,
    number VARCHAR(20),
    code VARCHAR(20),
    name TEXT,
    event TEXT,
    profession TEXT,
    work_types TEXT,
    equipment TEXT,
    materials TEXT,
    source_sheet VARCHAR(50),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

risk_measures¶

Мероприятия по снижению рисков.

CREATE TABLE risk_measures (
    id SERIAL PRIMARY KEY,
    hazard_id INTEGER REFERENCES hazard_classifiers(id),
    position_id INTEGER REFERENCES positions(id),
    title VARCHAR(500) NOT NULL,
    description TEXT,
    responsible VARCHAR(255),
    due_date TIMESTAMP,
    status VARCHAR(50) DEFAULT 'planned', -- planned, in_progress, done, canceled
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

risk_assessments¶

Оценки профессиональных рисков.

CREATE TABLE risk_assessments (
    id SERIAL PRIMARY KEY,
    organization_id INTEGER NOT NULL REFERENCES organizations(id),
    position_id INTEGER NOT NULL REFERENCES organization_positions(id),
    hazard_id INTEGER NOT NULL REFERENCES hazard_classifiers(id),
    severity INTEGER NOT NULL,
    probability INTEGER NOT NULL,
    k1_coefficient FLOAT DEFAULT 1.0,
    k2_coefficient FLOAT DEFAULT 1.0,
    k3_coefficient FLOAT DEFAULT 1.0,
    base_risk_score INTEGER,
    final_risk_score FLOAT,
    risk_level VARCHAR(50),
    risk_zone VARCHAR(20),
    control_measures TEXT,
    responsible_person VARCHAR(255),
    due_date TIMESTAMP,
    status VARCHAR(50) DEFAULT 'planned',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- Индексы
CREATE INDEX idx_risk_assessments_org_id ON risk_assessments(organization_id);
CREATE INDEX idx_risk_assessments_position_id ON risk_assessments(position_id);
CREATE INDEX idx_risk_assessments_hazard_id ON risk_assessments(hazard_id);

🏥 Медицинские осмотры¶

medical_examinations¶

Медицинские осмотры сотрудников.

CREATE TABLE medical_examinations (
    id SERIAL PRIMARY KEY,
    user_id INTEGER NOT NULL REFERENCES safety_users(id),
    organization_id INTEGER NOT NULL REFERENCES organizations(id),
    examination_type VARCHAR(50) NOT NULL, -- preliminary, periodic, extraordinary
    examination_date TIMESTAMP NOT NULL,
    next_examination_date TIMESTAMP,
    result VARCHAR(50) NOT NULL, -- fit, unfit, conditionally_fit
    restrictions TEXT,
    recommendations TEXT,
    medical_organization VARCHAR(255),
    doctor_name VARCHAR(200),
    certificate_number VARCHAR(100),
    notes TEXT,
    is_active BOOLEAN DEFAULT TRUE,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- Индексы
CREATE INDEX idx_med_exams_user_id ON medical_examinations(user_id);
CREATE INDEX idx_med_exams_org_id ON medical_examinations(organization_id);
CREATE INDEX idx_med_exams_type ON medical_examinations(examination_type);

📍 Рабочие зоны¶

work_zones¶

Рабочие зоны организации.

CREATE TABLE work_zones (
    id SERIAL PRIMARY KEY,
    organization_id INTEGER NOT NULL REFERENCES organizations(id),
    name VARCHAR(200) NOT NULL,
    description TEXT,
    features TEXT,
    is_active BOOLEAN DEFAULT TRUE,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- Индексы
CREATE INDEX idx_work_zones_org_id ON work_zones(organization_id);

user_work_zones¶

Связь пользователей с рабочими зонами.

CREATE TABLE user_work_zones (
    id SERIAL PRIMARY KEY,
    user_id INTEGER NOT NULL REFERENCES safety_users(id),
    work_zone_id INTEGER NOT NULL REFERENCES work_zones(id),
    organization_id INTEGER NOT NULL REFERENCES organizations(id),
    start_date TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    end_date TIMESTAMP,
    role_in_zone VARCHAR(200),
    is_primary BOOLEAN DEFAULT FALSE,
    notes TEXT,
    is_active BOOLEAN DEFAULT TRUE,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- Индексы
CREATE INDEX idx_user_work_zones_user_id ON user_work_zones(user_id);
CREATE INDEX idx_user_work_zones_zone_id ON user_work_zones(work_zone_id);
CREATE INDEX idx_user_work_zones_org_id ON user_work_zones(organization_id);

🆘 Система поддержки¶

support_tickets¶

Обращения в поддержку.

CREATE TABLE support_tickets (
    id SERIAL PRIMARY KEY,
    organization_id INTEGER NOT NULL REFERENCES organizations(id),
    created_by INTEGER NOT NULL REFERENCES safety_users(id),
    title VARCHAR(255) NOT NULL,
    description TEXT NOT NULL,
    status VARCHAR(50) DEFAULT 'new', -- new, in_progress, resolved, closed
    contact_name VARCHAR(200) NOT NULL,
    contact_email VARCHAR(100) NOT NULL,
    contact_phone VARCHAR(20),
    organization_name VARCHAR(255) NOT NULL,
    tariff_plan VARCHAR(100),
    priority VARCHAR(20) DEFAULT 'medium', -- low, medium, high, urgent
    category VARCHAR(100),
    tags JSONB,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    resolved_at TIMESTAMP,
    closed_at TIMESTAMP
);

🔔 Уведомления¶

safety_notifications¶

Уведомления пользователей.

CREATE TABLE safety_notifications (
    id SERIAL PRIMARY KEY,
    user_id INTEGER NOT NULL REFERENCES safety_users(id),
    title VARCHAR(255) NOT NULL,
    message TEXT NOT NULL,
    notification_type VARCHAR(50), -- reminder, alert, info
    is_read BOOLEAN DEFAULT FALSE,
    related_document_id INTEGER REFERENCES safety_documents(id),
    related_form_id INTEGER REFERENCES safety_filled_forms(id),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

📊 Индексы и оптимизация¶

Основные индексы¶

-- Составные индексы для частых запросов
CREATE INDEX idx_org_members_user_org ON organization_members(user_id, organization_id);
CREATE INDEX idx_forms_user_status ON safety_filled_forms(user_id, status);
CREATE INDEX idx_ppe_issuance_org_date ON ppe_issuance(organization_id, issue_date);
CREATE INDEX idx_med_exams_user_type ON medical_examinations(user_id, examination_type);

-- Индексы для поиска
CREATE INDEX idx_users_full_name_gin ON safety_users USING GIN (to_tsvector('russian', full_name));
CREATE INDEX idx_organizations_full_name_gin ON organizations USING GIN (to_tsvector('russian', full_name));
CREATE INDEX idx_documents_name_gin ON safety_documents USING GIN (to_tsvector('russian', name));

JSONB индексы¶

-- Индексы для JSONB полей
CREATE INDEX idx_organizations_contacts_gin ON organizations USING GIN (contacts);
CREATE INDEX idx_forms_data_gin ON safety_filled_forms USING GIN (form_data);
CREATE INDEX idx_documents_template_fields_gin ON safety_documents USING GIN (template_fields);

🔄 Миграции¶

Alembic¶

Система использует Alembic для управления миграциями базы данных.

# Пример миграции
"""Add employee number to users

Revision ID: abc123def456
Revises: previous_revision
Create Date: 2024-01-15 10:30:00.000000

"""
from alembic import op
import sqlalchemy as sa

def upgrade():
    op.add_column('safety_users', sa.Column('employee_number', sa.String(20), nullable=True))
    op.create_index('idx_users_employee_number', 'safety_users', ['employee_number'], unique=True)

def downgrade():
    op.drop_index('idx_users_employee_number', table_name='safety_users')
    op.drop_column('safety_users', 'employee_number')

📈 Производительность¶

Партиционирование¶

Для больших таблиц используется партиционирование:

-- Партиционирование по дате для логов
CREATE TABLE safety_audit_logs (
    id BIGSERIAL,
    user_id INTEGER,
    action VARCHAR(100),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
) PARTITION BY RANGE (created_at);

-- Создание партиций по месяцам
CREATE TABLE safety_audit_logs_2024_01 PARTITION OF safety_audit_logs
    FOR VALUES FROM ('2024-01-01') TO ('2024-02-01');

Материализованные представления¶

Для сложных запросов используются материализованные представления:

-- Статистика по организациям
CREATE MATERIALIZED VIEW organization_stats AS
SELECT 
    o.id,
    o.name,
    COUNT(om.user_id) as user_count,
    COUNT(d.id) as document_count,
    COUNT(f.id) as form_count
FROM organizations o
LEFT JOIN organization_members om ON o.id = om.organization_id
LEFT JOIN safety_documents d ON o.id = d.organization_id
LEFT JOIN safety_filled_forms f ON o.id = f.organization_id
GROUP BY o.id, o.name;

-- Обновление материализованного представления
REFRESH MATERIALIZED VIEW organization_stats;

🔒 Безопасность¶

Row Level Security (RLS)¶

Для обеспечения изоляции данных организаций:

-- Включение RLS для таблицы документов
ALTER TABLE safety_documents ENABLE ROW LEVEL SECURITY;

-- Политика доступа для пользователей организации
CREATE POLICY org_documents_policy ON safety_documents
    FOR ALL TO authenticated_users
    USING (
        EXISTS (
            SELECT 1 FROM organization_members om
            WHERE om.user_id = current_user_id()
            AND om.organization_id = (
                SELECT organization_id FROM safety_documents 
                WHERE id = safety_documents.id
            )
        )
    );

Шифрование¶

Чувствительные данные шифруются:

-- Функция для шифрования
CREATE OR REPLACE FUNCTION encrypt_sensitive_data(data TEXT)
RETURNS TEXT AS $$
BEGIN
    RETURN pgp_sym_encrypt(data, current_setting('app.encryption_key'));
END;
$$ LANGUAGE plpgsql;

-- Функция для расшифровки
CREATE OR REPLACE FUNCTION decrypt_sensitive_data(encrypted_data TEXT)
RETURNS TEXT AS $$
BEGIN
    RETURN pgp_sym_decrypt(encrypted_data, current_setting('app.encryption_key'));
END;
$$ LANGUAGE plpgsql;

Схема базы данных оптимизирована для производительности, безопасности и масштабируемости.