forked from mtusi2026/mtusi2026lab5
30 lines
1.4 KiB
SQL
30 lines
1.4 KiB
SQL
ALTER TABLE attachments DROP CONSTRAINT attachments_status_check;
|
|
ALTER TABLE attachments ADD CONSTRAINT attachments_status_check CHECK(status IN ('uploading','queued','processing','ready','failed','missing','deleting'));
|
|
ALTER TABLE attachments ADD COLUMN variants jsonb NOT NULL DEFAULT '[]';
|
|
ALTER TABLE attachments ADD COLUMN error_code text;
|
|
ALTER TABLE attachments ADD COLUMN pipeline_version integer NOT NULL DEFAULT 1 CHECK(pipeline_version>0);
|
|
CREATE TABLE attachment_jobs (
|
|
id uuid PRIMARY KEY,
|
|
attachment_id uuid NOT NULL REFERENCES attachments(id),
|
|
pipeline_version integer NOT NULL CHECK(pipeline_version>0),
|
|
attempt integer NOT NULL DEFAULT 0,
|
|
status text NOT NULL DEFAULT 'queued' CHECK(status IN ('queued','processing','ready','failed')),
|
|
staging_key text NOT NULL,
|
|
lease_id uuid,
|
|
lease_until timestamptz,
|
|
next_attempt_at timestamptz NOT NULL DEFAULT clock_timestamp(),
|
|
last_published_at timestamptz,
|
|
error_code text,
|
|
traceparent text NOT NULL DEFAULT '',
|
|
tracestate text NOT NULL DEFAULT '',
|
|
created_at timestamptz NOT NULL DEFAULT clock_timestamp(),
|
|
updated_at timestamptz NOT NULL DEFAULT clock_timestamp(),
|
|
UNIQUE(attachment_id,pipeline_version)
|
|
);
|
|
CREATE INDEX attachment_jobs_due ON attachment_jobs(next_attempt_at) WHERE status IN ('queued','processing');
|
|
CREATE TABLE attachment_job_audit (
|
|
job_id uuid NOT NULL REFERENCES attachment_jobs(id),
|
|
action text NOT NULL,
|
|
at timestamptz NOT NULL DEFAULT clock_timestamp()
|
|
);
|