Files
2026-11-06 17:30:30 +03:00

151 lines
6.0 KiB
SQL

-- +goose Up
-- Lab5 async attachment pipeline: durable intent (jobs + transactional outbox),
-- per-version results (variants), and operator audit (events).
ALTER TABLE attachments DROP CONSTRAINT attachments_status_check;
ALTER TABLE attachments ADD CONSTRAINT attachments_status_check CHECK (
status IN ('pending', 'queued', 'processing', 'ready', 'failed')
) NOT VALID;
ALTER TABLE attachments VALIDATE CONSTRAINT attachments_status_check;
ALTER TABLE attachments DROP CONSTRAINT attachments_error_consistency;
ALTER TABLE attachments ADD CONSTRAINT attachments_error_consistency CHECK (
(status IN ('ready', 'queued', 'processing') AND error_code IS NULL) OR
(status = 'failed' AND error_code IS NOT NULL) OR
status = 'pending'
) NOT VALID;
ALTER TABLE attachments VALIDATE CONSTRAINT attachments_error_consistency;
ALTER TABLE attachments
ADD COLUMN pipeline_version integer NOT NULL DEFAULT 1
CHECK (pipeline_version >= 1),
ADD COLUMN staging_key text,
ADD COLUMN ready_at timestamptz,
ADD COLUMN failed_at timestamptz;
CREATE UNIQUE INDEX attachments_staging_key_key
ON attachments (staging_key)
WHERE staging_key IS NOT NULL;
CREATE INDEX attachments_pipeline_status_idx
ON attachments (status, pipeline_version);
-- One logical job per (attachment, pipeline version). job_id is stable across
-- redelivery; attempt is the managed attempt number (1-based).
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 CHECK (pipeline_version >= 1),
source_key text NOT NULL,
attempt integer NOT NULL DEFAULT 1 CHECK (attempt >= 1),
max_attempts integer NOT NULL CHECK (max_attempts BETWEEN 1 AND 100),
status text NOT NULL CHECK (
status IN ('queued', 'processing', 'ready', 'failed', 'superseded')
),
available_at timestamptz NOT NULL DEFAULT now(),
lease_owner text,
lease_expires_at timestamptz,
last_error_code text,
replay_count integer NOT NULL DEFAULT 0,
created_at timestamptz NOT NULL DEFAULT now(),
updated_at timestamptz NOT NULL DEFAULT now(),
completed_at timestamptz,
CONSTRAINT attachment_jobs_attachment_version_key UNIQUE (attachment_id, pipeline_version),
CONSTRAINT attachment_jobs_lease_consistency CHECK (
(status = 'processing' AND lease_owner IS NOT NULL AND lease_expires_at IS NOT NULL) OR
(status <> 'processing')
)
);
CREATE INDEX attachment_jobs_due_idx
ON attachment_jobs (status, available_at);
CREATE INDEX attachment_jobs_lease_idx
ON attachment_jobs (status, lease_expires_at);
-- Delivery ledger. available_at is the only retry scheduler; RabbitMQ is the
-- durable transport. No UNIQUE(job_id, attempt): a graceful shutdown may
-- re-publish the same attempt, and duplicates are harmless (worker CAS).
CREATE TABLE outbox_messages (
id bigserial PRIMARY KEY,
job_id uuid NOT NULL REFERENCES attachment_jobs (job_id) ON DELETE CASCADE,
attempt integer NOT NULL CHECK (attempt >= 1),
kind text NOT NULL DEFAULT 'publish',
routing_key text NOT NULL,
payload jsonb NOT NULL,
available_at timestamptz NOT NULL DEFAULT now(),
published_at timestamptz,
claimed_by text,
claimed_until timestamptz,
publish_attempts integer NOT NULL DEFAULT 0,
last_error text,
created_at timestamptz NOT NULL DEFAULT now()
);
CREATE INDEX outbox_due_idx
ON outbox_messages (available_at, id)
WHERE published_at IS NULL;
CREATE INDEX outbox_claim_expiry_idx
ON outbox_messages (claimed_until)
WHERE published_at IS NULL AND claimed_by IS NOT NULL;
-- Effect key (attachment_id, pipeline_version, name). The primary key is the
-- idempotency boundary for redelivery and versioned pipelines.
CREATE TABLE attachment_variants (
attachment_id uuid NOT NULL REFERENCES attachments (id) ON DELETE CASCADE,
pipeline_version integer NOT NULL CHECK (pipeline_version >= 1),
name text NOT NULL CHECK (name IN ('thumbnail', 'crop', 'optimized')),
content_type text NOT NULL CHECK (
content_type IN ('image/jpeg', 'image/png', 'image/webp')
),
size_bytes bigint NOT NULL CHECK (size_bytes > 0),
width integer NOT NULL CHECK (width > 0),
height integer NOT NULL CHECK (height > 0),
sha256 text NOT NULL CHECK (sha256 ~ '^[0-9a-f]{64}$'),
object_key text NOT NULL UNIQUE,
created_at timestamptz NOT NULL DEFAULT now(),
PRIMARY KEY (attachment_id, pipeline_version, name)
);
CREATE TABLE attachment_job_events (
id bigserial PRIMARY KEY,
job_id uuid NOT NULL REFERENCES attachment_jobs (job_id) ON DELETE CASCADE,
event text NOT NULL,
attempt integer,
detail jsonb,
created_at timestamptz NOT NULL DEFAULT now()
);
CREATE INDEX attachment_job_events_job_idx
ON attachment_job_events (job_id, created_at);
-- +goose Down
DROP TABLE attachment_job_events;
DROP TABLE attachment_variants;
DROP TABLE outbox_messages;
DROP TABLE attachment_jobs;
DROP INDEX attachments_pipeline_status_idx;
DROP INDEX attachments_staging_key_key;
ALTER TABLE attachments
DROP COLUMN failed_at,
DROP COLUMN ready_at,
DROP COLUMN staging_key,
DROP COLUMN pipeline_version;
ALTER TABLE attachments DROP CONSTRAINT attachments_error_consistency;
ALTER TABLE attachments ADD CONSTRAINT attachments_error_consistency CHECK (
(status = 'ready' AND error_code IS NULL) OR
(status = 'failed' AND error_code IS NOT NULL) OR
status = 'pending'
) NOT VALID;
ALTER TABLE attachments DROP CONSTRAINT attachments_status_check;
ALTER TABLE attachments ADD CONSTRAINT attachments_status_check CHECK (
status IN ('pending', 'ready', 'failed')
) NOT VALID;