Ниже — предложение реляционной схемы (PostgreSQL-подобный DDL), три «сложных» SQL‑запроса (включая запрос на поиск перекрёстных рецензий и изменение авторства между версиями) и обсуждение компромиссов по нормализации, индексам и использованию событийной/исторической модели.

1) Схема (основные таблицы)

подход: статья — каноническая сущность (articles), каждая версия статьи — отдельная запись в article_versions; авторство привязываем к версии (article_authors) — это даёт корректную историю авторов. Рецензии привязываем к конкретной версии. История правок — отдельная append-only таблица audit_events (может заполняться триггерами/логикой приложения).

DDL (сокращённо, PostgreSQL):
CREATE TABLE users (
user_id BIGSERIAL PRIMARY KEY,
name TEXT NOT NULL,
email TEXT UNIQUE NOT NULL,
affiliation TEXT,
created_at TIMESTAMPTZ DEFAULT now()
);

CREATE TABLE articles (
article_id BIGSERIAL PRIMARY KEY,
canonical_doi TEXT UNIQUE, -- при наличии
current_version_id BIGINT, -- FK на article_versions, nullable
status TEXT, -- draft/submitted/under_review/published/archived
created_at TIMESTAMPTZ DEFAULT now()
);

CREATE TABLE article_versions (
version_id BIGSERIAL PRIMARY KEY,
article_id BIGINT NOT NULL REFERENCES articles(article_id) ON DELETE CASCADE,
version_number INTEGER NOT NULL, -- 1,2,3...
title TEXT NOT NULL,
abstract TEXT,
content TEXT, -- либо хранить отдельно (blob/file store)
created_by BIGINT REFERENCES users(user_id),
created_at TIMESTAMPTZ DEFAULT now(),
change_summary TEXT,
is_published BOOLEAN DEFAULT FALSE,
UNIQUE(article_id, version_number)
);

CREATE TABLE article_authors (
article_version_id BIGINT NOT NULL REFERENCES article_versions(version_id) ON DELETE CASCADE,
user_id BIGINT NOT NULL REFERENCES users(user_id),
author_order INT NOT NULL,
corresponding BOOLEAN DEFAULT FALSE,
PRIMARY KEY (article_version_id, user_id)
);

CREATE TABLE reviewer_assignments (
assignment_id BIGSERIAL PRIMARY KEY,
article_version_id BIGINT NOT NULL REFERENCES article_versions(version_id) ON DELETE CASCADE,
reviewer_id BIGINT NOT NULL REFERENCES users(user_id),
assigned_at TIMESTAMPTZ DEFAULT now(),
assigned_by BIGINT REFERENCES users(user_id),
due_at TIMESTAMPTZ,
state TEXT NOT NULL DEFAULT 'assigned' -- assigned/accepted/declined/submitted
);

CREATE TABLE reviews (
review_id BIGSERIAL PRIMARY KEY,
assignment_id BIGINT NOT NULL REFERENCES reviewer_assignments(assignment_id) ON DELETE CASCADE,
reviewer_id BIGINT NOT NULL REFERENCES users(user_id),
submitted_at TIMESTAMPTZ,
recommendation TEXT, -- accept/revise/reject
content TEXT,
declared_conflict BOOLEAN DEFAULT FALSE
);

-- явные декларации COI между пользователей (может заводиться пользователем или админом)
CREATE TABLE conflicts_of_interest (
coi_id BIGSERIAL PRIMARY KEY,
user_id BIGINT NOT NULL REFERENCES users(user_id),
other_user_id BIGINT NOT NULL REFERENCES users(user_id),
relation_type TEXT, -- e.g. 'same_institution', 'recent_collab', 'family', 'declared'
created_by BIGINT REFERENCES users(user_id),
created_at TIMESTAMPTZ DEFAULT now(),
UNIQUE(user_id, other_user_id)
);

-- аудит / история изменений (append-only). change_data — свободная структура с деталями
CREATE TABLE audit_events (
event_id BIGSERIAL PRIMARY KEY,
entity_type TEXT NOT NULL, -- e.g. 'article_version','article_authors','review'
entity_id BIGINT NOT NULL,
action TEXT NOT NULL, -- insert/update/delete
changed_by BIGINT REFERENCES users(user_id),
changed_at TIMESTAMPTZ DEFAULT now(),
change_data JSONB
);

Замечания:

Авторство привязано к версии (article_authors) — позволяет точно воспроизвести кто был автором в конкретной версии.reviews привязаны к assignment, а assignment — к версии. Это позволяет различать назначение рецензента и отправленную рецензию.conflicts_of_interest хранит заранее объявленные COI; автоматически выводимые COI (например, недавнее соавторство) можно рассчитывать по истории публикаций/versions и/или хранить кэш (см. обсуждение).

2) Индексы — базовые и для часто используемых запросов
CREATE INDEX idx_article_versions_article_id ON article_versions(article_id);
CREATE INDEX idx_article_versions_created_at ON article_versions(created_at);
CREATE INDEX idx_article_authors_user_id ON article_authors(user_id);
CREATE INDEX idx_article_authors_version ON article_authors(article_version_id);
CREATE INDEX idx_reviews_reviewer ON reviews(reviewer_id);
CREATE INDEX idx_assignments_article_version ON reviewer_assignments(article_version_id);
CREATE INDEX idx_coi_pair ON conflicts_of_interest(user_id, other_user_id);
-- полнотекст/поиск
CREATE INDEX idx_article_versions_title_fts ON article_versions USING GIN (to_tsvector('russian', coalesce(title,'') || ' ' || coalesce(abstract,'')));

3) Три сложных запроса

Запрос A — найти пары статей/пользователей с перекрёстными рецензиями (mutual review): A reviewed B и B reviewed A.
WITH rv AS (
SELECT r.review_id, ra.assignment_id, ra.article_version_id, ra.reviewer_id, av.article_id,
ra.assigned_at, r.submitted_at
FROM reviewer_assignments ra
JOIN reviews r ON r.assignment_id = ra.assignment_id
JOIN article_versions av ON av.version_id = ra.article_version_id
)
SELECT
a1.article_id AS article_A,
a2.article_id AS article_B,
rv1.reviewer_id AS reviewer_of_B_who_is_Author_of_A, -- пользователь, который рецензировал B
rv2.reviewer_id AS reviewer_of_A_who_is_Author_of_B,
rv1.review_id AS review_on_B,
rv2.review_id AS review_on_A,
rv1.submitted_at AS submitted_on_B,
rv2.submitted_at AS submitted_on_A
FROM rv rv1
JOIN rv rv2 ON rv1.reviewer_id = rv2.reviewer_id -- not used; removed
-- правильная логика: нужно, чтобы rv1.reviewer_id был автором article A,
-- и rv2.reviewer_id был автором article B, и rv1.article_id = article_B, rv2.article_id = article_A
-- корректируем:
JOIN article_authors aa1 ON aa1.article_version_id = (
SELECT version_id FROM article_versions v WHERE v.article_id = rv2.article_id AND v.version_id = rv2.article_version_id
) -- (упрощённо)
-- Проще: используем author ↔ reviewed article cross-check вне subselects:

WITH rv AS (
SELECT r.review_id, ra.assignment_id, ra.article_version_id, ra.reviewer_id, av.article_id, av.version_id AS av_version
FROM reviewer_assignments ra
JOIN reviews r ON r.assignment_id = ra.assignment_id
JOIN article_versions av ON av.version_id = ra.article_version_id
)
SELECT DISTINCT
rvA.article_id AS article_A,
rvB.article_id AS article_B,
rvA.reviewer_id AS reviewer_who_reviewed_A,
rvB.reviewer_id AS reviewer_who_reviewed_B,
rvA.review_id AS review_on_A,
rvB.review_id AS review_on_B
FROM rv rvA
JOIN rv rvB
ON rvA.article_id <> rvB.article_id
-- условие взаимности: рецензент A для статьи X должен быть (в какой-то версии) автором статьи Y,
-- и рецензент B для статьи Y — автором статьи X
JOIN article_authors aaA ON aaA.user_id = rvB.reviewer_id AND aaA.article_version_id = (
SELECT v.version_id FROM article_versions v WHERE v.article_id = rvA.article_id ORDER BY v.version_number DESC LIMIT 1
)
JOIN article_authors aaB ON aaB.user_id = rvA.reviewer_id AND aaB.article_version_id = (
SELECT v.version_id FROM article_versions v WHERE v.article_id = rvB.article_id ORDER BY v.version_number DESC LIMIT 1
);

Пояснение: выше — шаблонный запрос. На практике нужно определить точный критерий: «авторство» — в конкретной версии (какая версия считать актуальной для проверки COI/перекрёстных связей?), либо по любой версии. В приведённом варианте проверяем наличие авторства в самой последней версии статьи. Можно заменить это на проверку авторства в версии, существовавшей на дату assignment/submission.

Запрос B — найти версии статей, где изменился список авторов по сравнению с предыдущей версией; вывести добавленных и удалённых авторов.
WITH ver_auth AS (
SELECT
v.version_id,
v.article_id,
v.version_number,
v.created_at,
(SELECT array_agg(user_id ORDER BY author_order) FROM article_authors aa WHERE aa.article_version_id = v.version_id) AS authors_arr
FROM article_versions v
),
ver_prev AS (
SELECT
va.*,
LAG(va.authors_arr) OVER (PARTITION BY va.article_id ORDER BY va.version_number) AS prev_authors_arr
FROM ver_auth va
)
SELECT
version_id,
article_id,
version_number,
created_at,
-- массив добавленных: элементы из authors_arr, которых нет в prev_authors_arr
(SELECT COALESCE(array_agg(x), ARRAY[]::BIGINT[]) FROM unnest(authors_arr) AS x WHERE NOT (x = ANY(prev_authors_arr))) AS added_authors,
(SELECT COALESCE(array_agg(x), ARRAY[]::BIGINT[]) FROM unnest(prev_authors_arr) AS x WHERE NOT (x = ANY(authors_arr))) AS removed_authors
FROM ver_prev
WHERE prev_authors_arr IS NOT NULL
AND (authors_arr IS DISTINCT FROM prev_authors_arr);

Пояснения:

Используем агрегированные массивы авторов, LAG для предыдущей версии, а затем через unnest вычисляем добавленных/удалённых.Если порядок авторов важен, дополнительно можно вернуть diff по позициям (например, изменился порядок).

Запрос C — найти рецензии, в которых у рецензента есть конфликт интересов с любым автором версии, но рецензент этого не задекларировал (undeclared COI). Конфликт считается либо в conflicts_of_interest, либо как «недавнее соавторство» (здесь пример: соавторство за последние 3 года).
WITH review_info AS (
SELECT r.review_id, r.assignment_id, r.reviewer_id, ra.article_version_id, ra.assigned_at, r.submitted_at
FROM reviews r
JOIN reviewer_assignments ra ON ra.assignment_id = r.assignment_id
),
authors_for_version AS (
SELECT article_version_id, array_agg(user_id) AS authors
FROM article_authors GROUP BY article_version_id
)
SELECT ri.review_id, ri.reviewer_id, ri.article_version_id, auth.authors
FROM review_info ri
JOIN authors_for_version auth ON auth.article_version_id = ri.article_version_id
WHERE COALESCE((ri.reviewer_id = ANY(auth.authors)), FALSE) = FALSE -- исключаем саморецензию
AND (
-- явный задекларированный COI в таблице conflicts_of_interest
EXISTS (
SELECT 1 FROM conflicts_of_interest coi
WHERE (coi.user_id = ri.reviewer_id AND coi.other_user_id = ANY(auth.authors))
OR (coi.user_id = ANY(auth.authors) AND coi.other_user_id = ri.reviewer_id)
)
-- или недавнее соавторство: reviewer и автор были соавторами на одной статье в последние 3 года
OR EXISTS (
SELECT 1
FROM article_authors aa1
JOIN article_versions v1 ON v1.version_id = aa1.article_version_id
JOIN article_authors aa2 ON aa1.article_version_id = aa2.article_version_id
WHERE aa1.user_id = ri.reviewer_id
AND aa2.user_id = ANY(auth.authors)
AND v1.created_at >= now() - INTERVAL '3 years'
AND aa1.user_id <> aa2.user_id
)
)
AND (SELECT declared_conflict FROM reviews WHERE review_id = ri.review_id) = FALSE; -- не задекларировал

Пояснение:

Этот запрос ищет рецензии, где существует либо задекларированный COI в таблице, либо inferred COI (соавторство за последние 3 года), но reviewer не отметил declared_conflict.Можно расширить критерии inferred COI (одна организация, наставничество и т.д.).

4) Обсуждение компромиссов: нормализация, индексация и событие/история

Нормализация

Плюсы высокой нормализации:
Ясные ограничения целостности (FK, уникальности).Малая избыточность данных, точная история (каждая версия отдельно).Легче отслеживать изменения и корректно выявлять COI на момент времени (по версии).Минусы:
Для некоторых аналитических запросов (например, агрегаты по авторам, поиск связанных сущностей) требуется много join'ов, что снижает производительность.Более сложные транзакции (вставка версии + множество авторов + assignment + ревью) — больше точек согласованности.

Денормализация и кэширование

Рекомендуется локально денормализовать/кэшировать часто используемые агрегаты:
хранить authors_array/author_list или current_authors в article_versions или articles (как computed column/JSONB) для ускорения чтения.хранить current_version_id в articles.Materialized views для «тяжёлых» аналитических запросов (например, граф связей рецензентов/авторов) и обновлять их по расписанию или при событиях.

Индексация

Индексировать все FK и поля, используемые в WHERE/ORDER BY:
article_versions(article_id, version_number) — для быстрой выборки последней версии.article_authors(user_id) — быстрый поиск статей по автору.reviewer_assignments(article_version_id) и reviews(reviewer_id, submitted_at) — для обзора назначений и историй рецензий.Частичные индексы:
индекс только на активные/опубликованные версии: WHERE is_published = true.индекс на reviews WHERE submitted_at IS NOT NULL (для быстрых запросов по завершённым рецензиям).GIN/TSVECTOR для полнотекстового поиска по title/abstract/content.Композитные покрытия (covering indexes) для часто выполняемых запросов (например (article_id, created_at, version_number)).

Событийная логика / append-only исторические таблицы

Модели:
Audit table (audit_events) + триггеры: сохраняет инциденты изменений; удобна для восстановления/разбора инцидентов.Event sourcing: все изменения моделируются как события; состояние восстанавливается путем «проигрывания» событий.Компромиссы:
Audit / append-only:Плюсы: лёгко восстановить прошлое состояние, простое хранение «что и когда изменилось». Хорошо подходит для требований аудита/соответствия.Минусы: дополнительные ресурсы (рост объёма), сложнее быстрые выборки «текущее состояние» (нужны materialized views или проекции).Event sourcing:Плюсы: гибкость, полная история действий, возможна реконструция любых проекций.Минусы: сложнее реализация, преобразование событий в текущие проекции требует дополнительного кода/инфраструктуры, транзакционные гарантии между проекциями и основными таблицами сложнее обеспечить.Практическая рекомендация: использовать hybrid-подход:
Основные сущности в нормализованной форме (article_versions, article_authors, reviews).Append-only audit_events для всех изменений (триггер/приложение пишет событие). Это даёт и быстрый доступ к текущему состоянию, и полную историю.Для тяжёлых аналитических задач/COI-детекций — проекции/материализованные представления, обновляемые через background jobs или при коммитах.

Производительность и согласованность

Согласованность:
FK-констрейнты + транзакции обеспечивают согласованность между версиями/авторами/рецензиями.Для атомарной вставки версии + списка авторов + назначений — одна транзакция.Использовать optimistic locking (например, поле row_version / xmin) для предотвращения lost update при редактировании версии.Производительность:
Для горячих таблиц (reviews, reviewer_assignments) — индексы, партиционирование по времени (если данные очень большие).Отложенные/асинхронные вычисления COI (например, infered COI через соавторство) — вычислять в background и кэшировать результаты в таблице conflicts_cache с timestamp. Позволяет быстрые проверки при назначении рецензента.Для долгих JOIN-операций — materialized view или precomputed graph (например, edges reviewer↔author) обновляемый инкрементально.

Другие практические замечания

Не удалять записи физически — лучше soft delete + audit. Это важно для аудита и повторного вычисления COI.Зафиксируйте, на какой «момент времени» проверяется COI/перекрёстность (например, на момент assignment, на момент submission или «актуальное» состояние). Это критично для корректности выявления конфликтов.При высоких требованиях к поиску и аналитике используйте специализированные сервисы: Elasticsearch для полнотекста/сложного поиска; графовые БД или OLAP для анализа связей reviewer-author.Тестируйте сценарии race conditions: назначение рецензента + изменение авторов почти одновременно — нужно согласованно логировать оба события и, возможно, отклонить назначение или потребовать перерассмотр при изменении авторов.

5) Итоговые рекомендации

Модель с article_versions + article_authors даёт корректную историю авторов и гибкость.Храните audit_events (append-only) и используйте background jobs/материализованные представления для тяжёлой аналитики (COI, графы перекрёстных рецензий).Индексируйте FK и поля, участвующие в фильтрах/сортировках; используйте частичные/покрывающие индексы и GIN для полнотекста.Для обеспечения согласованности применяйте транзакции и, при необходимости, optimistic locking. Для производительности — кэшировать выводимые/сложные проекции и пересчитывать их асинхронно.

Если хотите, могу:

предоставить более точный и исполняемый DDL (с FK/ON DELETE поведение и триггерами аудита),оптимизировать и протестировать конкретные SQL-запросы на ваших примерах данных,написать пример background-job-пайплайна для кэширования inferred COI и обновления materialized views.