48 lines
2.7 KiB
SQL
48 lines
2.7 KiB
SQL
|
-- Prepared (remux/transcode) download artifacts, deduplicated by
|
||
|
|
-- (media_file_id, format, params_hash). The attempts/lease_*/next_retry_at
|
||
|
|
-- columns make this table a durable, recoverable job queue so a crash mid-encode
|
||
|
|
-- cannot strand a download in `preparing`. See
|
||
|
|
-- docs/superpowers/specs/2026-06-18-offline-sync-mobile-design.md ("Durable artifact queue").
|
||
|
|
|
||
|
|
-- +goose Up
|
||
|
|
-- +goose StatementBegin
|
||
|
|
CREATE TABLE public.download_artifacts (
|
||
|
|
id text NOT NULL,
|
||
|
|
media_file_id integer NOT NULL REFERENCES public.media_files(id) ON DELETE CASCADE,
|
||
|
|
format text NOT NULL, -- 'remux' | 'transcode'
|
||
|
|
params_hash text NOT NULL, -- sha256 of the encode parameters
|
||
|
|
container text NOT NULL DEFAULT 'mp4',
|
||
|
|
codec_video text NOT NULL DEFAULT '',
|
||
|
|
codec_audio text NOT NULL DEFAULT '',
|
||
|
|
resolution text NOT NULL DEFAULT '',
|
||
|
|
audio_track_index integer NOT NULL DEFAULT -1,
|
||
|
|
target_bitrate_kbps integer NOT NULL DEFAULT 0,
|
||
|
|
output_path text NOT NULL DEFAULT '', -- absolute path on the server's artifact volume
|
||
|
|
file_size bigint NOT NULL DEFAULT 0,
|
||
|
|
status text NOT NULL DEFAULT 'queued',
|
||
|
|
error_message text NOT NULL DEFAULT '',
|
||
|
|
-- Durable-queue / crash-recovery columns.
|
||
|
|
attempts integer NOT NULL DEFAULT 0,
|
||
|
|
max_attempts integer NOT NULL DEFAULT 3,
|
||
|
|
lease_owner text, -- worker/node id holding the running lease
|
||
|
|
lease_expires_at timestamptz, -- NULL unless status='running'
|
||
|
|
next_retry_at timestamptz, -- backoff gate for re-enqueue after a failure
|
||
|
|
created_at timestamptz NOT NULL DEFAULT now(),
|
||
|
|
completed_at timestamptz,
|
||
|
|
last_used_at timestamptz NOT NULL DEFAULT now(),
|
||
|
|
CONSTRAINT download_artifacts_pkey PRIMARY KEY (id),
|
||
|
|
CONSTRAINT download_artifacts_unique UNIQUE (media_file_id, format, params_hash),
|
||
|
|
CONSTRAINT download_artifacts_status_check CHECK (status IN ('queued','running','ready','failed'))
|
||
|
|
);
|
||
|
|
|
||
|
|
CREATE INDEX download_artifacts_lru_idx ON public.download_artifacts (last_used_at) WHERE status = 'ready';
|
||
|
|
-- Claimable work: queued rows whose backoff has elapsed, plus running rows whose lease has expired.
|
||
|
|
CREATE INDEX download_artifacts_claimable_idx ON public.download_artifacts (status, next_retry_at);
|
||
|
|
CREATE INDEX download_artifacts_lease_idx ON public.download_artifacts (lease_expires_at) WHERE status = 'running';
|
||
|
|
-- +goose StatementEnd
|
||
|
|
|
||
|
|
-- +goose Down
|
||
|
|
-- +goose StatementBegin
|
||
|
|
DROP TABLE IF EXISTS public.download_artifacts;
|
||
|
|
-- +goose StatementEnd
|