База данных для экспедитора

Павел Молотивский

🚢 База данных мультимодального форвардера

Полная схема: CRM → Контракты → Перевозки → Финансы → P&L

таблиц

модулей

60+

связей

```

📊 Структура базы данных (ER-диаграмма)

CRM

leads

Лиды (потенциальные клиенты)

opportunities

Сделки / возможности

quotes

Коммерческие предложения

quote_items

Позиции КП

rate_requests

Запросы ставок

rate_responses

Ответы от агентов

activities

Активности (звонки, встречи)

Контрагенты

clients

Клиенты

agents

Агенты / перевозчики

contacts

Контактные лица

client_contracts

Контракты с клиентами

agent_contracts

Контракты с агентами

Перевозки

shipments

Перевозки

operations

Операции

cargo_items

Грузовые позиции

containers

Контейнеры

shipment_tracking

Трекинг

Финансы

invoices

Счета (исх. и вх.)

invoice_items

Позиции счетов

payments

Платежи

bank_accounts

Счета компании

transactions

Движение денег

expenses

Расходы компании

payroll

Зарплаты

tariffs

Тарифы

Справочники

countries

Страны

cities

Города

ports_terminals

Порты / терминалы

currencies

Валюты

operation_types

Типы операций

expense_categories

Категории расходов

🔗 Ключевые связи

leads → opportunities Лид конвертируется в сделку 1:N

opportunities → quotes КП по сделке 1:N

quotes → client_contracts КП → Контракт 1:1

leads → clients Лид → Клиент 1:1

rate_requests → rate_responses Запрос → Ответы агентов 1:N

client_contracts → shipments Перевозки по контракту 1:N

shipments → operations Операции перевозки 1:N

operations → invoice_items Операция → Позиция счёта 1:N

invoices → payments Оплаты по счёту 1:N

payments → transactions Платёж → Транзакция 1:1

employees → payroll Зарплата сотрудника 1:N

payroll → transactions Выплата ЗП 1:1

📈 Бизнес-процесс: от лида до прибыли

leads
Лид

→

opportunities
Сделка

→

quotes
КП

→

client_contracts
Контракт

→

shipments
Перевозка

→

operations
Операции

invoices (out)
Счёт клиенту

←

operations

→

invoices (in)
Счёт от агента

payments (in)
Оплата от клиента

payments (out)
Оплата агенту

expenses
Расходы

payroll
ЗП

P&L
Прибыль

🎯

CRM (до контракта)

7 таблиц

leads — Лиды (потенциальные клиенты) ▼

id UUID Идентификатор PK
lead_number VARCHAR(20) Номер лида (L-00001)
company_name VARCHAR(255) Название компании
contact_name VARCHAR(150) Контактное лицо
email, phone VARCHAR Контакты
source ENUM website, referral, cold_call, exhibition, linkedin
status ENUM new, contacted, qualified, unqualified, converted IDX
estimated_volume_cbm DECIMAL Примерный объём в месяц
interested_routes TEXT Интересующие маршруты
assigned_to_id UUID Ответственный менеджер FK
converted_client_id UUID ID клиента после конверсии FK
notes TEXT Заметки

opportunities — Сделки / возможности ▼

id UUID Идентификатор PK
opportunity_number VARCHAR(20) Номер сделки (OPP-00001)
name VARCHAR(255) Название сделки
lead_id UUID Исходный лид (если есть) FK
client_id UUID Существующий клиент (если есть) FK
stage ENUM qualification, proposal, negotiation, won, lost IDX
probability_percent INTEGER Вероятность закрытия (0-100)
estimated_value DECIMAL(15,2) Ожидаемая сумма
currency_id UUID Валюта FK
expected_close_date DATE Ожидаемая дата закрытия
origin_country_id, destination_country_id UUID Маршрут (откуда-куда) FK
transport_mode ENUM sea, air, rail, road, multimodal
cargo_description TEXT Описание груза
assigned_to_id UUID Ответственный FK
won_contract_id UUID ID контракта (если won) FK
lost_reason TEXT Причина проигрыша

quotes — Коммерческие предложения (КП) ▼

id UUID Идентификатор PK
quote_number VARCHAR(30) Номер КП (Q-2024-00001)
opportunity_id UUID Сделка FK
version INTEGER Версия КП (1, 2, 3...)
status ENUM draft, sent, accepted, rejected, expired IDX
valid_until DATE Действует до
incoterms_id UUID Базис поставки FK
origin_port_id, destination_port_id UUID Маршрут FK
transit_time_days INTEGER Транзитное время
total_amount, currency_id DECIMAL, UUID Итого и валюта
margin_percent DECIMAL(5,2) Заложенная маржа %
document_url VARCHAR(500) PDF файл КП
created_contract_id UUID Созданный контракт FK

quote_items — Позиции КП (услуги) ▼

id UUID Идентификатор PK
quote_id UUID КП FK
operation_type_id UUID Тип услуги FK
description TEXT Описание услуги
buy_rate, sell_rate DECIMAL(15,2) Себестоимость / Продажа
rate_type ENUM per_container, per_kg, per_cbm, lumpsum
rate_response_id UUID Ставка от агента FK

rate_requests — Запросы ставок к агентам ▼

id UUID Идентификатор PK
request_number VARCHAR(30) Номер запроса
opportunity_id UUID Сделка (опционально) FK
origin_port_id, destination_port_id UUID Маршрут FK
transport_mode ENUM Вид транспорта
cargo_type, weight_kg, volume_cbm VARCHAR, DECIMAL Параметры груза
container_type_id, container_qty UUID, INTEGER Контейнеры
target_date DATE Желаемая дата отгрузки
response_deadline TIMESTAMP Дедлайн ответа
status ENUM draft, sent, responses_received, closed

rate_responses — Ответы на запросы ставок ▼

id UUID Идентификатор PK
rate_request_id UUID Запрос FK
agent_id UUID Агент FK
rate_amount, currency_id DECIMAL, UUID Ставка и валюта
rate_type ENUM all_in, basic + surcharges
transit_time_days INTEGER Транзитное время
valid_from, valid_until DATE Срок действия ставки
is_selected BOOLEAN Выбрана для КП
remarks TEXT Примечания агента

activities — Активности (звонки, встречи, письма) ▼

id UUID Идентификатор PK
activity_type ENUM call, email, meeting, task, note
entity_type ENUM lead, opportunity, client, agent
entity_id UUID ID сущности
subject VARCHAR(255) Тема
description TEXT Описание / результат
activity_date TIMESTAMP Дата активности IDX
due_date TIMESTAMP Срок (для задач)
is_completed BOOLEAN Выполнено
assigned_to_id UUID Ответственный FK

💰

Финансы (полный учёт)

10 таблиц

bank_accounts — Банковские счета компании ▼

id UUID Идентификатор PK
account_name VARCHAR(100) Название счёта
account_number VARCHAR(50) Номер счёта
bank_name VARCHAR(200) Банк
bik, correspondent_account VARCHAR БИК, корр. счёт
swift, iban VARCHAR SWIFT, IBAN (для валютных)
currency_id UUID Валюта счёта FK
current_balance DECIMAL(18,2) Текущий остаток
is_active BOOLEAN Активен

transactions — Движение денег (все операции) ▼

id UUID Идентификатор PK
transaction_number VARCHAR(30) Номер транзакции
bank_account_id UUID Счёт компании FK
transaction_type ENUM income, expense, transfer IDX
category ENUM client_payment, agent_payment, salary, expense, tax, other IDX
amount DECIMAL(18,2) Сумма (+ приход, - расход)
transaction_date DATE Дата операции IDX
counterparty_name VARCHAR(255) Контрагент
payment_id UUID Связанный платёж (если есть) FK
expense_id UUID Связанный расход (если есть) FK
payroll_id UUID Связанная ЗП (если есть) FK
description TEXT Назначение платежа
balance_after DECIMAL(18,2) Остаток после операции

invoices — Счета (исходящие и входящие) ▼

id UUID Идентификатор PK
invoice_type ENUM client_invoice, agent_invoice, credit_note, debit_note
direction ENUM outgoing (мы выставляем), incoming (нам выставили) IDX
client_id / agent_id UUID Контрагент FK
shipment_id UUID Перевозка FK
status ENUM draft, sent, partially_paid, paid, overdue, cancelled IDX
total_amount, paid_amount DECIMAL(18,2) Сумма и оплачено

payments — Платежи по счетам ▼

id UUID Идентификатор PK
payment_type ENUM incoming (от клиента), outgoing (агенту/перевозчику) IDX
invoice_id UUID Счёт FK
bank_account_id UUID Счёт компании FK
amount, currency_id DECIMAL, UUID Сумма и валюта
payment_date DATE Дата платежа IDX
status ENUM pending, confirmed, rejected

expense_categories — Категории расходов ▼

id UUID Идентификатор PK
code VARCHAR(50) Код (rent, utilities, marketing, travel...)
name_ru VARCHAR(100) Название
parent_id UUID Родительская категория (иерархия) FK
is_operational BOOLEAN Операционный расход (для P&L)

expenses — Расходы компании (аренда, связь, маркетинг...) ▼

id UUID Идентификатор PK
expense_number VARCHAR(30) Номер
category_id UUID Категория расхода FK
description TEXT Описание
amount, currency_id DECIMAL, UUID Сумма и валюта
expense_date DATE Дата расхода IDX
vendor_name VARCHAR(255) Поставщик / получатель
shipment_id UUID Перевозка (если прямой расход) FK
department_id UUID Отдел FK
bank_account_id UUID Счёт списания FK
status ENUM pending, approved, paid, rejected
approved_by_id UUID Утвердил FK
receipt_url VARCHAR(500) Скан чека / документа

payroll — Зарплаты и выплаты сотрудникам ▼

id UUID Идентификатор PK
employee_id UUID Сотрудник FK
period_year, period_month INTEGER Период (год, месяц) IDX
base_salary DECIMAL(15,2) Оклад
bonus DECIMAL(15,2) Премия
deductions DECIMAL(15,2) Удержания
tax_amount DECIMAL(15,2) НДФЛ
net_amount DECIMAL(15,2) К выплате
employer_taxes DECIMAL(15,2) Налоги работодателя (ФСС, ПФР)
total_cost DECIMAL(15,2) Полная стоимость для компании
currency_id UUID Валюта FK
status ENUM calculated, approved, paid
payment_date DATE Дата выплаты
bank_account_id UUID Счёт выплаты FK

tariffs — Тарифы и ставки ▼

operation_type_id, agent_id UUID Тип операции и агент FK
origin_port_id, destination_port_id UUID Маршрут FK
rate_type ENUM per_container, per_kg, per_cbm, lumpsum
buy_rate, sell_rate DECIMAL(15,4) Закупка / Продажа
validity_start, validity_end DATE Срок действия

exchange_rates — Курсы валют ▼

currency_from_id, currency_to_id UUID Пара валют FK
rate DECIMAL(18,8) Курс
rate_date DATE Дата курса IDX
source VARCHAR(50) Источник (CBR, ECB, manual)

📊 Расчёт прибыли (P&L)

Доход

invoices (outgoing)

Счета клиентам

Прямые расходы

invoices (incoming)

Счета от агентов

Операционные расходы

expenses

Аренда, связь, маркетинг

ФОТ

payroll.total_cost

Зарплаты + налоги

Чистая прибыль =

            Σ invoices(out) − 
            Σ invoices(in) − 
            Σ expenses − 
            Σ payroll
        

🚢

Перевозки

5 таблиц

shipments — Перевозки (ключевая таблица) ▼

shipment_numberVARCHAR(30)Номер перевозки
client_contract_idUUIDКонтрактFK
quote_idUUIDКП (опционально)FK
statusENUMdraft, booked, in_transit, delivered, completedIDX
origin/destinationUUIDcountry, city, port, addressFK
total_revenueDECIMAL(18,2)Выручка по перевозке
total_costDECIMAL(18,2)Себестоимость
profit_marginDECIMAL(18,2)Маржа

operations — Операции (море, ЖД, авиа, таможня, погрузка...) ▼

shipment_idUUIDПеревозкаFK
operation_type_idUUIDТип операцииFK
agent_contract_idUUIDКонтракт с агентомFK
cost_to_us, sell_rateDECIMALЗакупка / Продажа

cargo_items — Грузовые позиции

containers — Контейнеры

shipment_tracking — Трекинг событий

🏢

Контрагенты и контракты

5 таблиц

clients — Клиенты

agents — Агенты / перевозчики

contacts — Контактные лица (полиморфная)

client_contracts — Контракты с клиентами

agent_contracts — Контракты с агентами

👥

Персонал и система

5 таблиц

employees — Сотрудники

departments — Отделы (иерархия)

documents — Документы (универсальная)

audit_log — Журнал изменений

notifications — Уведомления

📚

Справочники

10 таблиц

countries, cities, ports_terminals, border_crossings — География

currencies, exchange_rates — Валюты

transport_modes, operation_types — Виды транспорта и операций

cargo_types, container_types, incoterms — Грузы, контейнеры, условия

💡 Технические рекомендации

UUID как PK — распределённость, безопасность
Полиморфные таблицы: contacts, documents, activities — entity_type + entity_id
Денежные суммы: DECIMAL(18,2), всегда с currency_id
P&L: можно создать VIEW или материализованную таблицу для быстрых отчётов
Индексы: status, dates, FK, shipment_number, invoice_number
Soft delete: deleted_at вместо физического удаления
Аудит: audit_log для всех критичных изменений

```

🚢 База данных мультимодального форвардера

CRM (до контракта)

Финансы (полный учёт)

Перевозки

Контрагенты и контракты

Персонал и система

Справочники

Похожие материалы