Files

37 lines
1.6 KiB
SQL

ALTER TABLE attachments DROP CONSTRAINT attachments_state_check;
ALTER TABLE attachments ADD CONSTRAINT attachments_state_check CHECK(state IN ('pending','queued','processing','ready','failed'));
ALTER TABLE attachments ADD COLUMN pipeline_version INTEGER NOT NULL DEFAULT 1 CHECK(pipeline_version>0);
ALTER TABLE attachments ADD COLUMN staging_key TEXT;
ALTER TABLE attachments ADD COLUMN variants JSONB NOT NULL DEFAULT '[]';
CREATE TABLE jobs (
id UUID PRIMARY KEY,
attachment_id UUID NOT NULL REFERENCES attachments(id),
pipeline_version INTEGER NOT NULL CHECK(pipeline_version>0),
state TEXT NOT NULL CHECK(state IN ('queued','processing','ready','failed','superseded')),
attempt INTEGER NOT NULL DEFAULT 0,
lease_until TIMESTAMPTZ,
next_at TIMESTAMPTZ NOT NULL DEFAULT now(),
error_code TEXT,
created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
updated_at TIMESTAMPTZ NOT NULL DEFAULT now(),
UNIQUE(attachment_id,pipeline_version)
);
CREATE TABLE outbox (
job_id UUID PRIMARY KEY REFERENCES jobs(id),
published_at TIMESTAMPTZ,
deliveries INTEGER NOT NULL DEFAULT 0
);
CREATE TABLE job_audit (
id BIGSERIAL PRIMARY KEY,
job_id UUID REFERENCES jobs(id),
event TEXT NOT NULL,
created_at TIMESTAMPTZ NOT NULL DEFAULT now()
);
CREATE INDEX jobs_due ON jobs(state,next_at);
-- Upgrade existing lab4 ready originals through the asynchronous pipeline.
UPDATE attachments SET staging_key=object_key,state='queued',updated_at=now() WHERE state IN ('ready','pending');
INSERT INTO jobs(id,attachment_id,pipeline_version,state)
SELECT gen_random_uuid(),id,pipeline_version,'queued' FROM attachments WHERE state='queued';
INSERT INTO outbox(job_id) SELECT id FROM jobs;