forked from mtusi2026/mtusi2026lab5
117 lines
7.1 KiB
SQL
117 lines
7.1 KiB
SQL
-- 005 (lab5): асинхронная обработка вложений через брокер.
|
||
--
|
||
-- Жизненный цикл attachments.state (pending — внутренний, в API не виден):
|
||
-- pending — строка-намерение записана ДО S3 PUT в staging (как в lab4);
|
||
-- queued — оригинал целиком лежит в staging/, в ОДНОЙ транзакции с этим
|
||
-- UPDATE записаны attachment_jobs и строка job_outbox -> 202;
|
||
-- processing — worker взял аренду (lease) задания;
|
||
-- ready — worker записал оригинал в final/ и все варианты, затем одной
|
||
-- транзакцией опубликовал результат (variants + state);
|
||
-- failed — постоянная ошибка или исчерпаны попытки (error_code).
|
||
-- Миграция расширяющая: реплики lab4 продолжают работать (pending/ready).
|
||
|
||
ALTER TABLE attachments DROP CONSTRAINT attachments_state;
|
||
ALTER TABLE attachments ADD CONSTRAINT attachments_state
|
||
CHECK (state IN ('pending', 'queued', 'processing', 'ready', 'failed'));
|
||
|
||
-- Ключ staging-объекта (приватный префикс staging/ того же бакета). NULL после
|
||
-- безопасной очистки. object_key — конечный ключ оригинала (final/<id>/original),
|
||
-- назначается сервером при приёме и становится читаемым только в ready.
|
||
ALTER TABLE attachments ADD COLUMN staging_key text;
|
||
ALTER TABLE attachments ADD CONSTRAINT attachments_staging_key_key UNIQUE (staging_key);
|
||
ALTER TABLE attachments ADD COLUMN error_code text;
|
||
-- Версия pipeline, результат которой опубликован (или ожидается) для вложения.
|
||
ALTER TABLE attachments ADD COLUMN pipeline_version integer NOT NULL DEFAULT 1;
|
||
ALTER TABLE attachments ADD CONSTRAINT attachments_error_code
|
||
CHECK ((state = 'failed') = (error_code IS NOT NULL) OR state = 'pending');
|
||
|
||
-- Результат обработки. Ключ эффекта — (attachment_id, pipeline_version, name):
|
||
-- повторная доставка не может создать второй набор вариантов.
|
||
CREATE TABLE attachment_variants (
|
||
attachment_id uuid NOT NULL REFERENCES attachments (id) ON DELETE CASCADE,
|
||
pipeline_version integer NOT NULL,
|
||
name text NOT NULL,
|
||
content_type text NOT NULL,
|
||
size_bytes bigint NOT NULL,
|
||
width integer NOT NULL,
|
||
height integer NOT NULL,
|
||
sha256 text NOT NULL,
|
||
object_key text NOT NULL,
|
||
created_at timestamptz NOT NULL DEFAULT clock_timestamp(),
|
||
PRIMARY KEY (attachment_id, pipeline_version, name),
|
||
CONSTRAINT attachment_variants_object_key_key UNIQUE (object_key),
|
||
CONSTRAINT attachment_variants_name CHECK (name IN ('thumbnail', 'crop', 'optimized')),
|
||
CONSTRAINT attachment_variants_type CHECK (content_type IN ('image/jpeg', 'image/webp', 'image/png')),
|
||
CONSTRAINT attachment_variants_size CHECK (size_bytes > 0 AND width > 0 AND height > 0),
|
||
CONSTRAINT attachment_variants_sha256 CHECK (sha256 ~ '^[a-f0-9]{64}$')
|
||
);
|
||
|
||
-- Одно логическое задание на (вложение, версию pipeline). job_id не меняется
|
||
-- при redelivery и retry; attempt растёт только при управляемой повторной попытке.
|
||
CREATE TABLE attachment_jobs (
|
||
job_id uuid PRIMARY KEY,
|
||
attachment_id uuid NOT NULL REFERENCES attachments (id) ON DELETE CASCADE,
|
||
pipeline_version integer NOT NULL,
|
||
status text NOT NULL DEFAULT 'queued',
|
||
attempt integer NOT NULL DEFAULT 1,
|
||
max_attempts integer NOT NULL,
|
||
replay_count integer NOT NULL DEFAULT 0,
|
||
-- Аренда: токен-«забор» (fencing), владелец (consumer) и срок.
|
||
lease_token uuid,
|
||
lease_owner text,
|
||
lease_until timestamptz,
|
||
next_attempt_at timestamptz,
|
||
last_error_code text,
|
||
created_at timestamptz NOT NULL DEFAULT clock_timestamp(),
|
||
updated_at timestamptz NOT NULL DEFAULT clock_timestamp(),
|
||
started_at timestamptz,
|
||
finished_at timestamptz,
|
||
CONSTRAINT attachment_jobs_effect_key UNIQUE (attachment_id, pipeline_version),
|
||
CONSTRAINT attachment_jobs_status CHECK (status IN ('queued', 'processing', 'succeeded', 'failed')),
|
||
CONSTRAINT attachment_jobs_attempt CHECK (attempt >= 1 AND max_attempts >= 1),
|
||
CONSTRAINT attachment_jobs_lease CHECK ((status = 'processing') = (lease_token IS NOT NULL))
|
||
);
|
||
CREATE INDEX attachment_jobs_lease_idx ON attachment_jobs (lease_until) WHERE status = 'processing';
|
||
CREATE INDEX attachment_jobs_status_idx ON attachment_jobs (status, updated_at);
|
||
|
||
-- Transactional outbox: строка пишется в той же транзакции, что и задание.
|
||
-- Relay публикует в брокер (XADD с fsync), затем удаляет строку. Сбой между
|
||
-- XADD и DELETE даёт повторную публикацию — worker идемпотентен.
|
||
-- available_at: для свежей загрузки = now()+grace (сначала быстрый путь из API),
|
||
-- для retry = время следующей попытки (backoff с jitter).
|
||
CREATE TABLE job_outbox (
|
||
id bigserial PRIMARY KEY,
|
||
job_id uuid NOT NULL REFERENCES attachment_jobs (job_id) ON DELETE CASCADE,
|
||
payload jsonb NOT NULL,
|
||
available_at timestamptz NOT NULL DEFAULT clock_timestamp(),
|
||
created_at timestamptz NOT NULL DEFAULT clock_timestamp(),
|
||
publish_failures integer NOT NULL DEFAULT 0
|
||
);
|
||
CREATE INDEX job_outbox_available_idx ON job_outbox (available_at, id);
|
||
|
||
-- Реестр окончательно упавших заданий (DLQ). Replay — только явный, операторский.
|
||
CREATE TABLE dead_letters (
|
||
job_id uuid PRIMARY KEY REFERENCES attachment_jobs (job_id) ON DELETE CASCADE,
|
||
attachment_id uuid NOT NULL,
|
||
error_code text NOT NULL,
|
||
attempts integer NOT NULL,
|
||
failed_at timestamptz NOT NULL DEFAULT clock_timestamp(),
|
||
replayed_at timestamptz,
|
||
replay_count integer NOT NULL DEFAULT 0
|
||
);
|
||
|
||
-- Аудит переходов задания: claim, retry, fail, replay, no-op повторы, публикации.
|
||
-- Без секретов и без содержимого файлов.
|
||
CREATE TABLE job_events (
|
||
id bigserial PRIMARY KEY,
|
||
job_id uuid NOT NULL,
|
||
attachment_id uuid,
|
||
attempt integer,
|
||
event text NOT NULL,
|
||
detail text,
|
||
actor text,
|
||
at timestamptz NOT NULL DEFAULT clock_timestamp()
|
||
);
|
||
CREATE INDEX job_events_job_idx ON job_events (job_id, id);
|
||
CREATE INDEX job_events_at_idx ON job_events (at);
|