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