forked from mtusi2026/mtusi2026lab7
Майорова Софья, БВТ2301, на 5. Трек1. HTTPS/TLS/ACL,18 API tests,13 pipeline groups,3 compromise runs,clean clone,persistence. Original tests/API contract preserved; residual CVE triage in REPORT/security.
41 lines
1.6 KiB
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()
|
|
)
|
|
;
|