Files
mtusi2026lab5/migrations/006_pipeline.sql
Самохвалов Андрей Ремович b23ecc111f feat: Accept durable queued uploads and publish outbox jobs (U2)
Carry forward the lab4 API and storage baseline, stage verified originals in
MinIO, and atomically commit attachment, job, and outbox records before 202.
Publish identifier-only jobs with broker confirms and reclaimable outbox leases.
Clean abandoned and completed staging objects after database checks.
2026-10-02 14:35:35 +03:00

75 lines
3.4 KiB
SQL

-- Lab5: PostgreSQL is the source of truth; RabbitMQ carries only job IDs.
ALTER TABLE attachments
ADD COLUMN staging_key text UNIQUE,
ADD COLUMN pipeline_version integer NOT NULL DEFAULT 1 CHECK (pipeline_version > 0);
CREATE TABLE attachment_jobs (
id uuid PRIMARY KEY,
attachment_id uuid NOT NULL REFERENCES attachments(id) ON DELETE CASCADE,
pipeline_version integer NOT NULL CHECK (pipeline_version > 0),
attempt integer NOT NULL DEFAULT 1 CHECK (attempt > 0),
status text NOT NULL DEFAULT 'queued'
CHECK (status IN ('queued', 'processing', 'ready', 'failed')),
next_attempt_at timestamptz NOT NULL DEFAULT clock_timestamp(),
lease_token uuid,
lease_expires_at timestamptz,
error_code text,
created_at timestamptz NOT NULL DEFAULT clock_timestamp(),
updated_at timestamptz NOT NULL DEFAULT clock_timestamp(),
UNIQUE (attachment_id, pipeline_version),
CHECK ((status = 'processing') =
(lease_token IS NOT NULL AND lease_expires_at IS NOT NULL))
);
CREATE INDEX attachment_jobs_due_idx
ON attachment_jobs (next_attempt_at, id) WHERE status = 'queued';
CREATE INDEX attachment_jobs_expired_idx
ON attachment_jobs (lease_expires_at, id) WHERE status = 'processing';
-- One logical publication per managed attempt. A crash after broker confirm
-- but before published_at may republish; consumers must tolerate that duplicate.
CREATE TABLE attachment_outbox (
id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
job_id uuid NOT NULL REFERENCES attachment_jobs(id) ON DELETE CASCADE,
attempt integer NOT NULL CHECK (attempt > 0),
available_at timestamptz NOT NULL DEFAULT clock_timestamp(),
claim_token uuid,
claim_expires_at timestamptz,
published_at timestamptz,
created_at timestamptz NOT NULL DEFAULT clock_timestamp(),
UNIQUE (job_id, attempt),
CHECK ((claim_token IS NULL) = (claim_expires_at IS NULL))
);
CREATE INDEX attachment_outbox_pending_idx
ON attachment_outbox (available_at, id) WHERE published_at IS NULL;
-- Internal 'original' is stored alongside public thumbnail/crop/optimized
-- so every physical effect has the same versioned identity and checksum.
CREATE TABLE attachment_variants (
attachment_id uuid NOT NULL,
pipeline_version integer NOT NULL,
name text NOT NULL CHECK (name IN ('original', 'thumbnail', 'crop', 'optimized')),
object_key text NOT NULL,
content_type text NOT NULL CHECK (content_type IN ('image/png', 'image/jpeg', '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}$'),
created_at timestamptz NOT NULL DEFAULT clock_timestamp(),
PRIMARY KEY (attachment_id, pipeline_version, name),
FOREIGN KEY (attachment_id, pipeline_version)
REFERENCES attachment_jobs (attachment_id, pipeline_version) ON DELETE CASCADE
);
-- An API upload reserves its staging key before S3 PUT. Cleanup only removes
-- objects without a committed job and without an unexpired upload marker.
CREATE TABLE attachment_uploads (
id uuid PRIMARY KEY,
staging_key text NOT NULL UNIQUE,
lease_expires_at timestamptz NOT NULL,
created_at timestamptz NOT NULL DEFAULT clock_timestamp()
);
CREATE INDEX attachment_uploads_expired_idx ON attachment_uploads (lease_expires_at);