forked from mtusi2026/mtusi2026lab6
96 lines
2.5 KiB
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);
|