Files
mtusi2026lab6/migrations/005_pipeline.sql

41 lines
1.6 KiB
SQL

ALTER TABLE attachments
DROP CONSTRAINT attachments_state_check
, ADD CONSTRAINT attachments_state_check CHECK (state IN ('uploading', 'queued', 'processing', 'ready', 'failed', 'storage_missing'))
, ADD COLUMN pipeline_version INTEGER NOT NULL DEFAULT 1
, ADD COLUMN staging_key TEXT
, ADD COLUMN variants JSONB NOT NULL DEFAULT '[]'
, ADD COLUMN error_code TEXT
, ADD COLUMN current_job_id UUID
;
CREATE TABLE attachment_jobs (
id UUID PRIMARY KEY
, attachment_id UUID NOT NULL REFERENCES attachments(id)
, pipeline_version INTEGER NOT NULL
, source_key TEXT NOT NULL
, state TEXT NOT NULL DEFAULT 'queued' CHECK (state IN ('queued', 'processing', 'ready', 'failed', 'superseded'))
, attempt INTEGER NOT NULL DEFAULT 0
, max_attempts INTEGER NOT NULL DEFAULT 5
, lease_token UUID
, lease_until TIMESTAMPTZ
, next_attempt_at TIMESTAMPTZ NOT NULL DEFAULT clock_timestamp()
, published_at TIMESTAMPTZ
, error_code TEXT
, created_at TIMESTAMPTZ NOT NULL DEFAULT clock_timestamp()
, updated_at TIMESTAMPTZ NOT NULL DEFAULT clock_timestamp()
, UNIQUE (attachment_id, pipeline_version)
)
;
ALTER TABLE attachments
ADD CONSTRAINT attachments_current_job FOREIGN KEY (current_job_id) REFERENCES attachment_jobs(id)
;
CREATE INDEX jobs_due ON attachment_jobs (state, next_attempt_at)
;
CREATE TABLE job_events (
id BIGSERIAL PRIMARY KEY
, job_id UUID NOT NULL REFERENCES attachment_jobs(id)
, event TEXT NOT NULL
, attempt INTEGER NOT NULL
, created_at TIMESTAMPTZ NOT NULL DEFAULT clock_timestamp()
)
;