Files
mtusi2026lab5/migrations/000004_async_attachments.up.sql
2026-11-10 16:21:46 +03:00

66 lines
3.1 KiB
PL/PgSQL

BEGIN;
ALTER TABLE attachments
DROP CONSTRAINT attachments_status_supported,
DROP CONSTRAINT attachments_status_error_consistency;
UPDATE attachments SET status = 'queued' WHERE status = 'pending';
ALTER TABLE attachments
ADD COLUMN staging_key TEXT,
ADD COLUMN pipeline_version INTEGER NOT NULL DEFAULT 1,
ADD CONSTRAINT attachments_staging_key_unique UNIQUE (staging_key),
ADD CONSTRAINT attachments_staging_key_not_blank CHECK (staging_key IS NULL OR btrim(staging_key) <> ''),
ADD CONSTRAINT attachments_pipeline_version_positive CHECK (pipeline_version > 0),
ADD CONSTRAINT attachments_status_supported CHECK (status IN ('queued', 'processing', 'ready', 'failed')),
ADD CONSTRAINT attachments_status_error_consistency CHECK (
(status = 'failed' AND error_code IS NOT NULL AND btrim(error_code) <> '')
OR (status IN ('queued', 'processing', 'ready') AND error_code IS NULL)
);
CREATE TABLE attachment_variants (
attachment_id UUID NOT NULL REFERENCES attachments(id) ON DELETE CASCADE,
pipeline_version INTEGER NOT NULL,
name VARCHAR(32) NOT NULL,
object_key TEXT NOT NULL UNIQUE,
content_type VARCHAR(32) NOT NULL,
size_bytes BIGINT NOT NULL,
width INTEGER NOT NULL,
height INTEGER NOT NULL,
sha256 BYTEA NOT NULL,
created_at TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP,
PRIMARY KEY (attachment_id, pipeline_version, name),
CONSTRAINT attachment_variants_name_supported CHECK (name IN ('thumbnail', 'crop', 'optimized')),
CONSTRAINT attachment_variants_content_type_supported CHECK (content_type IN ('image/jpeg', 'image/png', 'image/webp')),
CONSTRAINT attachment_variants_values_valid CHECK (
pipeline_version > 0 AND btrim(object_key) <> '' AND size_bytes > 0
AND width > 0 AND height > 0 AND octet_length(sha256) = 32
)
);
CREATE TABLE attachment_jobs (
job_id UUID PRIMARY KEY,
attachment_id UUID NOT NULL REFERENCES attachments(id) ON DELETE CASCADE,
schema_version INTEGER NOT NULL DEFAULT 1,
job_type TEXT NOT NULL DEFAULT 'attachment.process.v1',
pipeline_version INTEGER NOT NULL,
attempt INTEGER NOT NULL DEFAULT 1,
max_attempts INTEGER NOT NULL,
status VARCHAR(16) NOT NULL DEFAULT 'queued',
error_code TEXT,
published_at TIMESTAMPTZ,
created_at TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP,
updated_at TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP,
UNIQUE (attachment_id, pipeline_version),
CONSTRAINT attachment_jobs_schema_supported CHECK (schema_version = 1 AND job_type = 'attachment.process.v1'),
CONSTRAINT attachment_jobs_attempts_valid CHECK (attempt BETWEEN 1 AND max_attempts AND max_attempts > 0),
CONSTRAINT attachment_jobs_status_supported CHECK (status IN ('queued', 'processing', 'succeeded', 'failed')),
CONSTRAINT attachment_jobs_error_consistency CHECK (
(status = 'failed' AND error_code IS NOT NULL AND btrim(error_code) <> '') OR status <> 'failed'
)
);
CREATE INDEX attachment_jobs_dispatch_idx ON attachment_jobs (created_at, job_id)
WHERE status = 'queued' AND published_at IS NULL;
COMMIT;