Files
mtusi2026lab6/migrations/005_async_jobs.sql

96 lines
2.5 KiB
SQL

-- Allow asynchronous attachment states.
ALTER TABLE attachments
DROP CONSTRAINT IF EXISTS attachment_status_valid;
ALTER TABLE attachments
ADD CONSTRAINT attachment_status_valid
CHECK (
status IN (
'queued',
'processing',
'ready',
'failed'
)
);
-- Staging object stored in the private S3 bucket.
ALTER TABLE attachments
ADD COLUMN IF NOT EXISTS staging_key TEXT;
-- Machine-readable final error.
ALTER TABLE attachments
ADD COLUMN IF NOT EXISTS error_code TEXT;
-- Last metadata update timestamp.
ALTER TABLE attachments
ADD COLUMN IF NOT EXISTS updated_at
TIMESTAMPTZ NOT NULL DEFAULT NOW();
-- Worker jobs and durable publication intent.
CREATE TABLE IF NOT EXISTS attachment_jobs (
id UUID PRIMARY KEY,
attachment_id UUID NOT NULL UNIQUE
REFERENCES attachments(id),
status TEXT NOT NULL DEFAULT 'queued',
attempts INTEGER NOT NULL DEFAULT 0,
max_attempts INTEGER NOT NULL DEFAULT 5,
published_at TIMESTAMPTZ,
started_at TIMESTAMPTZ,
completed_at TIMESTAMPTZ,
next_attempt_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
CONSTRAINT attachment_job_status_valid
CHECK (
status IN (
'queued',
'processing',
'ready',
'failed'
)
),
CONSTRAINT attachment_job_attempts_valid
CHECK (
attempts >= 0
AND max_attempts BETWEEN 1 AND 20
)
);
CREATE INDEX IF NOT EXISTS idx_attachment_jobs_pending
ON attachment_jobs(status, next_attempt_at);
-- Image variants created by the worker.
CREATE TABLE IF NOT EXISTS attachment_variants (
attachment_id UUID NOT NULL
REFERENCES attachments(id),
variant_name TEXT NOT NULL,
object_key TEXT NOT NULL,
content_type TEXT NOT NULL,
size_bytes BIGINT NOT NULL,
sha256 CHAR(64) NOT NULL,
width INTEGER NOT NULL,
height INTEGER NOT NULL,
created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
PRIMARY KEY (attachment_id, variant_name),
CONSTRAINT variant_name_valid
CHECK (variant_name IN ('thumbnail')),
CONSTRAINT variant_size_valid
CHECK (size_bytes > 0),
CONSTRAINT variant_dimensions_valid
CHECK (width > 0 AND height > 0)
);
CREATE UNIQUE INDEX IF NOT EXISTS idx_attachment_variants_key
ON attachment_variants(object_key);