Files
mtusi2026lab6/migrations/006_pipeline.sql
Самохвалов Андрей Ремович c60b3e652b feat: Carry runnable lab5 business stack into lab6 (U1)
Import committed lab5 baseline 03899d7 via git archive: API replicas,
PostgreSQL, private S3, RabbitMQ, publisher, worker, migrations, and hooks.
Preserve lab6 contracts and the separate telemetry stack. Static checks pass;
Docker runtime integration remains pending.
2026-10-02 14:43:04 +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);