DDL (PostgreSQL)¶
Целевая СУБД — PostgreSQL 16. Конструкции, специфичные для PostgreSQL, помечены; переносимый на MySQL 8 вариант указан в примечаниях (см. обоснование выбора).
Соглашения: snake_case; идентификаторы — bigint GENERATED ALWAYS AS IDENTITY; время — timestamptz (всегда с зоной, требование FR-C-8); денежные и медицинские числа — numeric, не float.
Типы-перечисления¶
CREATE TYPE provider_axis AS ENUM ('smp', 'geo', 'mis');
CREATE TYPE message_type AS ENUM ('transport_request', 'transport_notification',
'transport_health_status', 'transport_cancel',
'transport_complete');
CREATE TYPE message_status AS ENUM ('received', 'processed', 'failed', 'duplicate');
CREATE TYPE bed_decision AS ENUM ('accepted', 'rejected', 'conditional');
CREATE TYPE transport_status AS ENUM ('requested', 'notified', 'en_route',
'delivered', 'cancelled');
CREATE TYPE appeal_source AS ENUM ('smp', 'self');
CREATE TYPE appeal_status AS ENUM ('open', 'triaged', 'queued', 'in_registration',
'registered', 'closed', 'cancelled');
CREATE TYPE triage_color AS ENUM ('red', 'yellow', 'green');
CREATE TYPE triage_mode AS ENUM ('rules', 'manual', 'mass_casualty');
CREATE TYPE handoff_kind AS ENUM ('appeal', 'team_request', 'cancel_notice');
CREATE TYPE handoff_status AS ENUM ('pending', 'sent', 'failed', 'acknowledged');
CREATE TYPE device_type AS ENUM ('bedside', 'btn_nurse', 'btn_doctor',
'btn_presence', 'lamp', 'monitor');
CREATE TYPE device_status AS ENUM ('online', 'offline', 'fault', 'disabled');
CREATE TYPE call_urgency AS ENUM ('routine', 'urgent', 'emergency');
CREATE TYPE call_status AS ENUM ('new', 'routed', 'accepted', 'closed', 'expired');
CREATE TYPE log_direction AS ENUM ('in', 'out');
Конфигурация и справочники¶
CREATE TABLE facilities (
id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
code varchar(32) NOT NULL UNIQUE,
name varchar(255) NOT NULL,
timezone varchar(64) NOT NULL DEFAULT 'Europe/Minsk',
settings jsonb NOT NULL DEFAULT '{}',
created_at timestamptz NOT NULL DEFAULT now()
);
CREATE TABLE departments (
id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
facility_id bigint NOT NULL REFERENCES facilities(id),
code varchar(32) NOT NULL,
name varchar(255) NOT NULL,
profile varchar(64), -- профиль: кардиология, травматология…
is_icu boolean NOT NULL DEFAULT false,
UNIQUE (facility_id, code)
);
CREATE TABLE providers (
id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
facility_id bigint NOT NULL REFERENCES facilities(id),
code varchar(64) NOT NULL,
axis provider_axis NOT NULL,
display_name varchar(255) NOT NULL,
adapter_class varchar(255) NOT NULL, -- какой ACL инстанцировать
is_active boolean NOT NULL DEFAULT true,
settings jsonb NOT NULL DEFAULT '{}',
created_at timestamptz NOT NULL DEFAULT now(),
UNIQUE (facility_id, code, axis)
);
COMMENT ON COLUMN providers.settings IS
'poll_interval_sec, rate_limit, tz, code_map_version — см. architecture/adapters.md';
CREATE TABLE provider_endpoints (
id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
provider_id bigint NOT NULL REFERENCES providers(id) ON DELETE CASCADE,
purpose varchar(64) NOT NULL, -- inbound | positions | mis_api
url text,
listen_port int,
ip_allowlist inet[],
CHECK (url IS NOT NULL OR listen_port IS NOT NULL)
);
-- Секреты в БД не хранятся: только ссылка на внешнее хранилище.
CREATE TABLE provider_credentials (
id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
provider_id bigint NOT NULL REFERENCES providers(id) ON DELETE CASCADE,
kind varchar(32) NOT NULL, -- mtls_cert | api_key | hmac_secret
secret_ref varchar(255) NOT NULL, -- путь в секрет-хранилище
valid_from timestamptz,
valid_to timestamptz,
rotated_at timestamptz
);
Секреты не хранятся в БД
provider_credentials.secret_ref — ссылка, а не значение. Ключи и сертификаты вендоров лежат во внешнем секрет-хранилище. Дамп БД не должен давать доступ к внешним системам; кроме того, требования ОАЦ № 66 предполагают контроль ротации, а не хранение секретов вперемешку с бизнес-данными.
Пользователи и роли¶
CREATE TABLE users (
id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
facility_id bigint NOT NULL REFERENCES facilities(id),
login varchar(128) NOT NULL UNIQUE,
full_name varchar(255) NOT NULL,
password_hash varchar(255), -- NULL, если вход только по ЭЦП
cert_thumbprint varchar(128), -- ЭЦП (FR-C-3)
nfc_card_uid varchar(64) UNIQUE, -- кнопка присутствия (FR-N-5)
mis_practitioner_ref varchar(128),
is_active boolean NOT NULL DEFAULT true,
last_login_at timestamptz,
created_at timestamptz NOT NULL DEFAULT now()
);
CREATE TABLE roles (
id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
code varchar(64) NOT NULL UNIQUE,
name varchar(255) NOT NULL
);
CREATE TABLE user_roles (
user_id bigint NOT NULL REFERENCES users(id) ON DELETE CASCADE,
role_id bigint NOT NULL REFERENCES roles(id) ON DELETE CASCADE,
PRIMARY KEY (user_id, role_id)
);
INSERT INTO roles (code, name) VALUES
('operator', 'Оператор центрального пульта'),
('triage_nurse', 'Медсестра сортировки'),
('registrar', 'Регистратор оформления'),
('ward_nurse', 'Постовая медсестра'),
('doctor', 'Врач'),
('icu_doctor', 'Врач реанимации'),
('admin', 'Администратор системы'),
('security', 'Администратор безопасности'),
('integration', 'Сервисная учётная запись интеграции');
Группа 1 — Запросы наличия мест¶
CREATE TABLE inbound_messages (
id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
provider_id bigint NOT NULL REFERENCES providers(id),
message_uid varchar(255) NOT NULL,
message_type message_type NOT NULL,
correlation_id varchar(128),
raw_body jsonb NOT NULL, -- сырой Bundle как есть
status message_status NOT NULL DEFAULT 'received',
error_text text,
normalization_issues jsonb, -- неразобранные коды
received_at timestamptz NOT NULL DEFAULT now(),
processed_at timestamptz,
UNIQUE (provider_id, message_uid) -- ADR-3: идемпотентность
);
CREATE INDEX ix_inbound_type_time ON inbound_messages (message_type, received_at DESC);
CREATE INDEX ix_inbound_raw_gin ON inbound_messages USING gin (raw_body);
CREATE TABLE bed_requests (
id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
facility_id bigint NOT NULL REFERENCES facilities(id),
inbound_message_id bigint NOT NULL REFERENCES inbound_messages(id),
external_call_id varchar(128),
patient_gender varchar(16),
patient_age int,
reason_code varchar(64),
reason_text text,
smp_priority varchar(32),
requested_profile varchar(64),
decision bed_decision NOT NULL,
decision_reason text,
decided_by bigint REFERENCES users(id), -- NULL = решение автоматическое
decided_at timestamptz NOT NULL DEFAULT now(),
responded_at timestamptz
);
CREATE INDEX ix_bed_requests_time ON bed_requests (facility_id, decided_at DESC);
Группа 2 — Направление пациента¶
CREATE TABLE patients (
id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
personal_no bytea, -- личный номер, ШИФРУЕТСЯ на уровне приложения
personal_no_hash varchar(64) UNIQUE, -- HMAC для поиска без расшифровки
policy_no bytea,
family_name bytea NOT NULL,
given_name bytea,
patronymic bytea,
birth_date date,
age_approx int, -- «со слов», п. 4 набора данных
gender varchar(16),
address bytea,
phone bytea,
mis_patient_ref varchar(128),
created_at timestamptz NOT NULL DEFAULT now(),
updated_at timestamptz NOT NULL DEFAULT now()
);
COMMENT ON TABLE patients IS
'ПДн. Чувствительные поля — bytea (AES-GCM на уровне приложения). Доступ по RBAC, чтение → audit_log';
CREATE TABLE ambulances (
id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
provider_id bigint NOT NULL REFERENCES providers(id),
external_id varchar(128) NOT NULL,
board_number varchar(64), -- номер машины
brigade_number varchar(64), -- номер бригады, п. 8
vin varchar(64),
model varchar(128),
contact_phone varchar(64),
UNIQUE (provider_id, external_id)
);
CREATE TABLE practitioners (
id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
provider_id bigint REFERENCES providers(id),
external_id varchar(128),
full_name varchar(255) NOT NULL,
qualification varchar(64), -- врач СМП / фельдшер, п. 40
UNIQUE (provider_id, external_id)
);
CREATE TABLE transport_notifications (
id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
facility_id bigint NOT NULL REFERENCES facilities(id),
provider_id bigint NOT NULL REFERENCES providers(id),
inbound_message_id bigint NOT NULL REFERENCES inbound_messages(id),
bed_request_id bigint REFERENCES bed_requests(id), -- NULL: везут без запроса
external_call_id varchar(128) NOT NULL,
patient_id bigint REFERENCES patients(id),
ambulance_id bigint REFERENCES ambulances(id),
practitioner_id bigint REFERENCES practitioners(id),
status transport_status NOT NULL DEFAULT 'notified',
smp_priority varchar(32),
reason_code varchar(64),
reason_text text,
preliminary_dx varchar(255), -- п. 22
is_trauma boolean DEFAULT false, -- п. 15
allergy_text text, -- п. 20, если не структурировано
call_address text, -- п. 2
target_department_id bigint REFERENCES departments(id),
recommended_color triage_color, -- подсказка оператору, не решение
transport_started_at timestamptz,
eta_at timestamptz,
cancelled_at timestamptz,
cancel_reason text,
created_at timestamptz NOT NULL DEFAULT now(),
UNIQUE (provider_id, external_call_id)
);
CREATE INDEX ix_tn_active ON transport_notifications (facility_id, status)
WHERE status IN ('notified', 'en_route');
-- История смены статусов направления
CREATE TABLE transport_events (
id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
notification_id bigint NOT NULL REFERENCES transport_notifications(id) ON DELETE CASCADE,
from_status transport_status,
to_status transport_status NOT NULL,
source varchar(32) NOT NULL, -- provider | operator | system
note text,
occurred_at timestamptz NOT NULL DEFAULT now()
);
personal_no_hash — как искать по зашифрованному полю
Личный номер шифруется, поэтому прямой поиск WHERE personal_no = ... невозможен. Детерминированный HMAC от нормализованного значения даёт индексируемый ключ поиска, не раскрывая исходных данных при компрометации дампа (в отличие от простого хеша, ключ HMAC хранится отдельно). Совпадение хеша — кандидат на сопоставление, окончательное решение принимается после расшифровки.
Группа 3 — Изменение состояния при транспортировке¶
CREATE TABLE vital_observations (
id bigint GENERATED ALWAYS AS IDENTITY,
notification_id bigint NOT NULL REFERENCES transport_notifications(id),
inbound_message_id bigint REFERENCES inbound_messages(id),
loinc_code varchar(32) NOT NULL,
code_display varchar(255),
value_num numeric(12,3),
value_text text,
value_int int,
unit_ucum varchar(32),
interpretation varchar(16), -- N | H | L | A
component varchar(32), -- systolic | diastolic для АД
effective_at timestamptz NOT NULL,
received_at timestamptz NOT NULL DEFAULT now(),
PRIMARY KEY (id, effective_at)
) PARTITION BY RANGE (effective_at);
-- Дедупликация повторных доставок одного измерения
CREATE UNIQUE INDEX ux_vitals_dedup ON vital_observations
(notification_id, loinc_code, coalesce(component, ''), effective_at);
CREATE INDEX ix_vitals_notif ON vital_observations (notification_id, effective_at DESC);
CREATE TABLE vital_observations_2026q3 PARTITION OF vital_observations
FOR VALUES FROM ('2026-07-01') TO ('2026-10-01');
Переносимость на MySQL
PARTITION BY RANGE в PostgreSQL объявляется на таблице и требует включения ключа партиции в первичный ключ — отсюда PRIMARY KEY (id, effective_at). В MySQL 8 синтаксис партиционирования иной (PARTITION BY RANGE (TO_DAYS(effective_at))), а jsonb заменяется на json с потерей GIN-индексов. Различия локализованы в трёх таблицах: vital_observations, monitor_observations, ambulance_locations и журналах.
Группа 4 — Лог запросов положения кареты¶
CREATE TABLE location_requests (
id bigint GENERATED ALWAYS AS IDENTITY,
provider_id bigint NOT NULL REFERENCES providers(id),
requested_ids text[],
http_status int,
duration_ms int,
is_success boolean NOT NULL,
error_text text,
requested_at timestamptz NOT NULL DEFAULT now(),
PRIMARY KEY (id, requested_at)
) PARTITION BY RANGE (requested_at);
CREATE TABLE ambulance_locations (
id bigint GENERATED ALWAYS AS IDENTITY,
ambulance_id bigint NOT NULL REFERENCES ambulances(id),
notification_id bigint REFERENCES transport_notifications(id),
request_id bigint,
latitude numeric(9,6) NOT NULL,
longitude numeric(9,6) NOT NULL,
speed_kmh numeric(6,2),
heading_deg numeric(5,2),
accuracy_m numeric(8,2),
source_time timestamptz NOT NULL, -- время ИСТОЧНИКА, не опроса
captured_at timestamptz NOT NULL DEFAULT now(),
PRIMARY KEY (id, captured_at)
) PARTITION BY RANGE (captured_at);
CREATE INDEX ix_loc_amb ON ambulance_locations (ambulance_id, source_time DESC);
source_time и captured_at — разные вещи
source_time — когда координата была зафиксирована навигационным оборудованием; captured_at — когда мы её получили. Разница может достигать минут при плохой связи. Расчёт ETA и отображение «координаты устарели N мин» обязаны опираться на source_time, иначе система покажет свежую метку у давно потерянной машины. См. «Адаптеры».
Группа 5 — Доставка¶
CREATE TABLE deliveries (
id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
notification_id bigint NOT NULL UNIQUE REFERENCES transport_notifications(id),
inbound_message_id bigint REFERENCES inbound_messages(id),
delivered_at timestamptz NOT NULL, -- отсечка по данным СМП, п. 33
accepted_at timestamptz, -- подтверждение медработником УЗ
accepted_by bigint REFERENCES users(id), -- п. 36
handover_from bigint REFERENCES practitioners(id), -- п. 35
mileage_km numeric(8,2), -- п. 38
state_after_transport text, -- п. 32
notes text, -- п. 37
created_at timestamptz NOT NULL DEFAULT now()
);
Группа 6 — Обращение и очередь на сортировку¶
CREATE TABLE appeals (
id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
facility_id bigint NOT NULL REFERENCES facilities(id),
patient_id bigint REFERENCES patients(id), -- NULL до оформления (FR-T-15)
notification_id bigint REFERENCES transport_notifications(id), -- NULL для самообращений
source appeal_source NOT NULL,
status appeal_status NOT NULL DEFAULT 'open',
appeal_number varchar(32) NOT NULL,
opened_at timestamptz NOT NULL DEFAULT now(),
registered_at timestamptz,
closed_at timestamptz,
closed_reason varchar(64),
UNIQUE (facility_id, appeal_number),
CHECK ((source = 'smp' AND notification_id IS NOT NULL)
OR (source = 'self' AND notification_id IS NULL))
);
CREATE INDEX ix_appeals_open ON appeals (facility_id, status)
WHERE status NOT IN ('closed', 'cancelled');
CREATE TABLE appeal_events (
id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
appeal_id bigint NOT NULL REFERENCES appeals(id) ON DELETE CASCADE,
from_status appeal_status,
to_status appeal_status NOT NULL,
actor_id bigint REFERENCES users(id),
note text,
occurred_at timestamptz NOT NULL DEFAULT now()
);
CREATE TABLE triage_queue (
id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
appeal_id bigint NOT NULL UNIQUE REFERENCES appeals(id) ON DELETE CASCADE,
color triage_color NOT NULL,
ticket_code varchar(16) NOT NULL,
priority_rank int NOT NULL,
enqueued_at timestamptz NOT NULL DEFAULT now(),
called_at timestamptz,
left_at timestamptz,
sla_deadline timestamptz NOT NULL, -- норматив выбытия (FR-T-21)
sla_breached boolean NOT NULL DEFAULT false
);
CREATE INDEX ix_queue_order ON triage_queue (priority_rank, enqueued_at)
WHERE called_at IS NULL;
patient_id допускает NULL — это не недосмотр
FR-T-15 требует анонимной очереди: до оформления паспортная часть не заполняется. Обращение существует, стоит в очереди и имеет талон, но пациента как записи ПДн ещё нет. Это соответствует требованию минимизации персональных данных: ПДн появляются только на этапе оформления.
Группа 7 — Результаты сортировки и передача в МИС¶
CREATE TABLE triage_rules (
id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
facility_id bigint NOT NULL REFERENCES facilities(id),
code varchar(64) NOT NULL,
name varchar(255) NOT NULL,
description text,
UNIQUE (facility_id, code)
);
CREATE TABLE triage_rule_versions (
id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
rule_id bigint NOT NULL REFERENCES triage_rules(id) ON DELETE CASCADE,
version int NOT NULL,
conditions jsonb NOT NULL, -- декларативное условие (ADR-7)
result_color triage_color NOT NULL,
priority int NOT NULL, -- порядок проверки правил
is_active boolean NOT NULL DEFAULT false,
approved_by bigint REFERENCES users(id),
approved_at timestamptz,
valid_from timestamptz,
valid_to timestamptz,
created_at timestamptz NOT NULL DEFAULT now(),
UNIQUE (rule_id, version)
);
CREATE TABLE triage_assessments (
id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
appeal_id bigint NOT NULL REFERENCES appeals(id) ON DELETE CASCADE,
color triage_color NOT NULL,
ticket_code varchar(16) NOT NULL,
mode triage_mode NOT NULL,
rule_version_id bigint REFERENCES triage_rule_versions(id), -- NULL при manual
override_reason text, -- если оператор изменил результат правил
assessed_by bigint REFERENCES users(id),
signature_ref varchar(255), -- ЭЦП результата (FR-C-3)
assessed_at timestamptz NOT NULL DEFAULT now()
);
CREATE INDEX ix_assess_appeal ON triage_assessments (appeal_id, assessed_at DESC);
-- Какие значения были на входе — иначе решение невоспроизводимо
CREATE TABLE triage_assessment_inputs (
id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
assessment_id bigint NOT NULL REFERENCES triage_assessments(id) ON DELETE CASCADE,
loinc_code varchar(32),
param_name varchar(64) NOT NULL,
value_num numeric(12,3),
value_text text,
unit_ucum varchar(32),
source varchar(32) NOT NULL -- smp | triage_form | monitor
);
CREATE TABLE mis_handoffs (
id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
appeal_id bigint NOT NULL REFERENCES appeals(id),
provider_id bigint NOT NULL REFERENCES providers(id),
kind handoff_kind NOT NULL,
status handoff_status NOT NULL DEFAULT 'pending',
payload jsonb,
mis_reference varchar(255),
initiated_by bigint REFERENCES users(id), -- FR-T-11: ручное действие
retry_count int NOT NULL DEFAULT 0,
last_error text,
created_at timestamptz NOT NULL DEFAULT now(),
sent_at timestamptz,
acknowledged_at timestamptz
);
CREATE INDEX ix_handoff_pending ON mis_handoffs (status, created_at)
WHERE status IN ('pending', 'failed');
Журналы¶
CREATE TABLE audit_log (
id bigint GENERATED ALWAYS AS IDENTITY,
user_id bigint REFERENCES users(id),
action varchar(64) NOT NULL,
entity_type varchar(64),
entity_id bigint,
before_state jsonb,
after_state jsonb,
ip_address inet,
user_agent text,
created_at timestamptz NOT NULL DEFAULT now(),
PRIMARY KEY (id, created_at)
) PARTITION BY RANGE (created_at);
CREATE INDEX ix_audit_entity ON audit_log (entity_type, entity_id, created_at DESC);
CREATE TABLE integration_log (
id bigint GENERATED ALWAYS AS IDENTITY,
provider_id bigint REFERENCES providers(id),
direction log_direction NOT NULL,
operation varchar(128) NOT NULL,
correlation_id varchar(128),
http_status int,
duration_ms int,
is_success boolean NOT NULL,
error_text text,
created_at timestamptz NOT NULL DEFAULT now(),
PRIMARY KEY (id, created_at)
) PARTITION BY RANGE (created_at);
CREATE TABLE security_events (
id bigint GENERATED ALWAYS AS IDENTITY,
event_type varchar(64) NOT NULL,
user_id bigint REFERENCES users(id),
source_ip inet,
is_success boolean NOT NULL,
details jsonb,
created_at timestamptz NOT NULL DEFAULT now(),
PRIMARY KEY (id, created_at)
) PARTITION BY RANGE (created_at);
CREATE INDEX ix_sec_type ON security_events (event_type, created_at DESC);
Срок хранения security_events — не менее года
[ТЗ] Требование ОАЦ № 66: централизованный сбор и хранение информации о событиях ИБ не менее одного года. Партиции security_events нельзя отцеплять раньше этого срока, в отличие от ambulance_locations. Политики ретеншна различаются по таблицам — см. «Нефункциональные требования».
Автоматизация партиций¶
-- Ежемесячное создание партиций на 3 месяца вперёд и отцепление
-- устаревших согласно политике ретеншна конкретной таблицы.
-- Реализация: pg_partman или задание планировщика приложения.
-- ВАЖНО: security_events, audit_log — retention >= 12 мес (ОАЦ № 66).
Соответствие таблиц семи обязательным группам данных — в «Соответствии таблиц».