forked from mtusi2026/mtusi2026lab6
43 lines
1.8 KiB
SQL
43 lines
1.8 KiB
SQL
-- Lab5: существующие ready-вложения lab4 остаются доступными.
|
|
ALTER TABLE attachments
|
|
ADD COLUMN status text NOT NULL DEFAULT 'ready' CHECK (status IN ('queued','processing','ready','failed')),
|
|
ADD COLUMN variants jsonb NOT NULL DEFAULT '[]',
|
|
ADD COLUMN error_code text,
|
|
ADD COLUMN updated_at timestamptz NOT NULL DEFAULT clock_timestamp();
|
|
UPDATE attachments SET updated_at=created_at;
|
|
|
|
CREATE TABLE attachment_jobs (
|
|
id uuid PRIMARY KEY,
|
|
attachment_id uuid NOT NULL UNIQUE REFERENCES attachments(id),
|
|
pipeline_version integer NOT NULL DEFAULT 1 CHECK (pipeline_version=1),
|
|
staging_key text NOT NULL UNIQUE,
|
|
attempt integer NOT NULL DEFAULT 1 CHECK (attempt>=1),
|
|
max_attempts integer NOT NULL CHECK (max_attempts>=1),
|
|
replay_count integer NOT NULL DEFAULT 0,
|
|
lease_token uuid,
|
|
lease_until timestamptz,
|
|
next_attempt_at timestamptz NOT NULL DEFAULT clock_timestamp(),
|
|
last_error text,
|
|
staging_deleted_at timestamptz,
|
|
created_at timestamptz NOT NULL DEFAULT clock_timestamp()
|
|
);
|
|
CREATE INDEX attachment_jobs_lease_idx ON attachment_jobs(lease_until) WHERE lease_until IS NOT NULL;
|
|
|
|
CREATE TABLE attachment_outbox (
|
|
job_id uuid NOT NULL REFERENCES attachment_jobs(id),
|
|
attempt integer NOT NULL,
|
|
available_at timestamptz NOT NULL DEFAULT clock_timestamp(),
|
|
published_at timestamptz,
|
|
PRIMARY KEY(job_id, attempt)
|
|
);
|
|
CREATE INDEX attachment_outbox_due_idx ON attachment_outbox(available_at) WHERE published_at IS NULL;
|
|
|
|
CREATE TABLE attachment_job_events (
|
|
id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
|
|
job_id uuid NOT NULL REFERENCES attachment_jobs(id),
|
|
attempt integer NOT NULL,
|
|
event text NOT NULL,
|
|
error_code text,
|
|
created_at timestamptz NOT NULL DEFAULT clock_timestamp()
|
|
);
|