Files

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);