forked from mtusi2026/mtusi2026lab5
77 lines
3.6 KiB
SQL
77 lines
3.6 KiB
SQL
-- Attachment processing is a durable asynchronous job.
|
|
-- The API stages bytes in S3, then in ONE transaction writes the attachment
|
|
-- snapshot and its outbox/job row. A separate publisher moves the intent to
|
|
-- RabbitMQ, so a broker outage cannot lose an accepted upload.
|
|
|
|
ALTER TABLE attachments DROP CONSTRAINT IF EXISTS attachments_status;
|
|
ALTER TABLE attachments DROP CONSTRAINT IF EXISTS attachments_error_state;
|
|
|
|
-- The previous "pending" state meant "object PUT in progress". Nothing can
|
|
-- finish it now, because acceptors write "queued" plus a job. Preserve the row
|
|
-- for the operator instead of silently deleting it.
|
|
UPDATE attachments
|
|
SET status = 'failed', error_code = 'legacy_interrupted', updated_at = now()
|
|
WHERE status = 'pending';
|
|
|
|
ALTER TABLE attachments
|
|
ADD COLUMN IF NOT EXISTS staging_key text,
|
|
ADD COLUMN IF NOT EXISTS pipeline_version integer NOT NULL DEFAULT 1,
|
|
ADD COLUMN IF NOT EXISTS variants jsonb NOT NULL DEFAULT '[]'::jsonb,
|
|
ADD COLUMN IF NOT EXISTS ready_at timestamptz;
|
|
|
|
UPDATE attachments SET ready_at = updated_at WHERE status = 'ready' AND ready_at IS NULL;
|
|
|
|
ALTER TABLE attachments
|
|
ADD CONSTRAINT attachments_status
|
|
CHECK (status IN ('queued', 'processing', 'ready', 'failed')),
|
|
ADD CONSTRAINT attachments_error_state CHECK (
|
|
(status IN ('queued', 'processing', 'ready') AND error_code IS NULL) OR
|
|
(status = 'failed' AND error_code IS NOT NULL)
|
|
),
|
|
ADD CONSTRAINT attachments_variants_array
|
|
CHECK (jsonb_typeof(variants) = 'array'),
|
|
ADD CONSTRAINT attachments_pipeline_version CHECK (pipeline_version >= 1);
|
|
|
|
-- One job per logical attachment effect. published_at IS NULL is the outbox
|
|
-- marker: a committed intent that has not received a publisher confirm.
|
|
CREATE TABLE IF NOT EXISTS attachment_jobs (
|
|
job_id uuid PRIMARY KEY,
|
|
attachment_id uuid NOT NULL UNIQUE REFERENCES attachments(id) ON DELETE CASCADE,
|
|
type text NOT NULL,
|
|
pipeline_version integer NOT NULL,
|
|
attempt integer NOT NULL DEFAULT 0,
|
|
max_attempts integer NOT NULL,
|
|
status text NOT NULL,
|
|
error_code text,
|
|
available_at timestamptz NOT NULL,
|
|
published_at timestamptz,
|
|
lease_until timestamptz,
|
|
processing_started_at timestamptz,
|
|
finished_at timestamptz,
|
|
replay_count integer NOT NULL DEFAULT 0,
|
|
last_replay_at timestamptz,
|
|
created_at timestamptz NOT NULL,
|
|
updated_at timestamptz NOT NULL,
|
|
CONSTRAINT attachment_jobs_status
|
|
CHECK (status IN ('queued', 'processing', 'ready', 'failed')),
|
|
CONSTRAINT attachment_jobs_attempts CHECK (attempt >= 0 AND max_attempts >= 1),
|
|
CONSTRAINT attachment_jobs_pipeline_version CHECK (pipeline_version >= 1),
|
|
CONSTRAINT attachment_jobs_error_state CHECK (
|
|
(status IN ('queued', 'processing', 'ready') AND error_code IS NULL) OR
|
|
(status = 'failed' AND error_code IS NOT NULL)
|
|
),
|
|
CONSTRAINT attachment_jobs_finished CHECK (
|
|
(status IN ('queued', 'processing') AND finished_at IS NULL) OR
|
|
(status IN ('ready', 'failed') AND finished_at IS NOT NULL)
|
|
)
|
|
);
|
|
|
|
CREATE INDEX IF NOT EXISTS attachment_jobs_outbox_idx
|
|
ON attachment_jobs (available_at, created_at)
|
|
WHERE published_at IS NULL AND status = 'queued';
|
|
CREATE INDEX IF NOT EXISTS attachment_jobs_lease_idx
|
|
ON attachment_jobs (lease_until)
|
|
WHERE status = 'processing';
|
|
CREATE INDEX IF NOT EXISTS attachment_jobs_status_idx
|
|
ON attachment_jobs (status, created_at);
|