forked from mtusi2026/mtusi2026lab6
21 lines
926 B
SQL
21 lines
926 B
SQL
ALTER TABLE jobs ADD COLUMN next_attempt_at timestamptz NOT NULL DEFAULT now();
|
|
|
|
CREATE TABLE outbox (
|
|
id bigserial PRIMARY KEY,
|
|
payload jsonb NOT NULL CHECK (jsonb_typeof(payload) = 'object'),
|
|
published boolean NOT NULL DEFAULT false,
|
|
created_at timestamptz NOT NULL DEFAULT now(),
|
|
available_at timestamptz NOT NULL DEFAULT now()
|
|
);
|
|
|
|
CREATE INDEX outbox_pending_idx ON outbox (available_at, id) WHERE NOT published;
|
|
|
|
-- Upgrade jobs accepted before outbox existed; duplicates are safe.
|
|
INSERT INTO outbox (payload, available_at)
|
|
SELECT jsonb_build_object(
|
|
'schema_version', 1, 'type', 'attachment.process.v1',
|
|
'job_id', id::text, 'attachment_id', attachment_id::text,
|
|
'pipeline_version', 1, 'attempt', retries + 1,
|
|
'created_at', to_char(created_at AT TIME ZONE 'UTC', 'YYYY-MM-DD"T"HH24:MI:SS"Z"')
|
|
), next_attempt_at FROM jobs WHERE status IN ('queued', 'processing');
|