84 lines
4.7 KiB
SQL
84 lines
4.7 KiB
SQL
|
-- Reshape the downloads table for downloads v2 (offline sync for mobile).
|
||
|
|
--
|
||
|
|
-- One device-aware, format-aware downloads table carries both lifecycles:
|
||
|
|
-- * device_id IS NULL -> ephemeral / account-level web download (today's behavior)
|
||
|
|
-- * device_id set -> managed device-library entry (Phase 1+)
|
||
|
|
-- Downloads ship default-off with little/no production data, so widening the
|
||
|
|
-- table in place is safe. See docs/superpowers/specs/2026-06-18-offline-sync-mobile-design.md.
|
||
|
|
|
||
|
|
-- +goose Up
|
||
|
|
-- +goose StatementBegin
|
||
|
|
ALTER TABLE public.downloads
|
||
|
|
ADD COLUMN profile_id text, -- NULL for ephemeral/web rows
|
||
|
|
ADD COLUMN device_id text, -- NULL = ephemeral; set = managed device entry
|
||
|
|
ADD COLUMN format text NOT NULL DEFAULT 'original', -- internal delivery format
|
||
|
|
ADD COLUMN quality text NOT NULL DEFAULT 'original', -- client-requested quality preset
|
||
|
|
ADD COLUMN effective_quality text NOT NULL DEFAULT 'original',
|
||
|
|
ADD COLUMN target_bitrate_kbps integer NOT NULL DEFAULT 0,
|
||
|
|
ADD COLUMN revision integer NOT NULL DEFAULT 1,
|
||
|
|
ADD COLUMN artifact_id text; -- set for remux/transcode once prepared
|
||
|
|
|
||
|
|
ALTER TABLE public.downloads
|
||
|
|
ADD CONSTRAINT downloads_format_check CHECK (format IN ('original','remux','transcode'));
|
||
|
|
ALTER TABLE public.downloads
|
||
|
|
ADD CONSTRAINT downloads_quality_check CHECK (quality IN ('original','20mbps','10mbps','5mbps','2mbps','1mbps'));
|
||
|
|
ALTER TABLE public.downloads
|
||
|
|
ADD CONSTRAINT downloads_effective_quality_check CHECK (effective_quality IN ('original','20mbps','10mbps','5mbps','2mbps','1mbps'));
|
||
|
|
ALTER TABLE public.downloads
|
||
|
|
ADD CONSTRAINT downloads_target_bitrate_check CHECK (target_bitrate_kbps >= 0);
|
||
|
|
ALTER TABLE public.downloads
|
||
|
|
ADD CONSTRAINT downloads_revision_check CHECK (revision >= 1);
|
||
|
|
|
||
|
|
-- Widen the status enum to cover the managed-entry lifecycle alongside the existing serve states.
|
||
|
|
ALTER TABLE public.downloads DROP CONSTRAINT downloads_status_check;
|
||
|
|
ALTER TABLE public.downloads ADD CONSTRAINT downloads_status_check
|
||
|
|
CHECK (status IN ('queued','downloading','completed','failed','cancelled', -- existing
|
||
|
|
'preparing','ready','revoked')); -- managed entries
|
||
|
|
|
||
|
|
-- One managed entry per (user, profile, device, content, episode). Ephemeral rows (NULL device_id)
|
||
|
|
-- are exempt via the partial index. COALESCE(episode_id,'') collapses movies (NULL episode_id) to
|
||
|
|
-- one managed entry per (device, content) without NULL-comparison surprises.
|
||
|
|
CREATE UNIQUE INDEX downloads_device_entry_uidx
|
||
|
|
ON public.downloads (user_id, profile_id, device_id, content_id, COALESCE(episode_id, ''))
|
||
|
|
WHERE device_id IS NOT NULL;
|
||
|
|
|
||
|
|
-- No separate (user_id, profile_id, device_id) index: downloads_device_entry_uidx
|
||
|
|
-- already serves that prefix under the same partial predicate.
|
||
|
|
CREATE INDEX downloads_artifact_idx ON public.downloads (artifact_id) WHERE artifact_id IS NOT NULL;
|
||
|
|
|
||
|
|
-- Composite FK to user_devices. MATCH SIMPLE (the default) skips the check whenever any column is NULL,
|
||
|
|
-- so ephemeral rows (NULL device_id) are not constrained while managed entries are.
|
||
|
|
ALTER TABLE public.downloads
|
||
|
|
ADD CONSTRAINT downloads_device_fkey
|
||
|
|
FOREIGN KEY (user_id, profile_id, device_id)
|
||
|
|
REFERENCES public.user_devices(user_id, profile_id, device_id) ON DELETE CASCADE;
|
||
|
|
-- +goose StatementEnd
|
||
|
|
|
||
|
|
-- +goose Down
|
||
|
|
-- +goose StatementBegin
|
||
|
|
ALTER TABLE public.downloads DROP CONSTRAINT IF EXISTS downloads_device_fkey;
|
||
|
|
DROP INDEX IF EXISTS downloads_artifact_idx;
|
||
|
|
DROP INDEX IF EXISTS downloads_device_entry_uidx;
|
||
|
|
ALTER TABLE public.downloads DROP CONSTRAINT IF EXISTS downloads_revision_check;
|
||
|
|
ALTER TABLE public.downloads DROP CONSTRAINT IF EXISTS downloads_target_bitrate_check;
|
||
|
|
ALTER TABLE public.downloads DROP CONSTRAINT IF EXISTS downloads_effective_quality_check;
|
||
|
|
ALTER TABLE public.downloads DROP CONSTRAINT IF EXISTS downloads_quality_check;
|
||
|
|
ALTER TABLE public.downloads DROP CONSTRAINT IF EXISTS downloads_format_check;
|
||
|
|
ALTER TABLE public.downloads DROP CONSTRAINT IF EXISTS downloads_status_check;
|
||
|
|
-- Rollback must not fail validation: collapse managed-lifecycle rows to the
|
||
|
|
-- legacy enum before re-narrowing the CHECK (data loss is expected on a
|
||
|
|
-- reshape Down; a failing Down is not).
|
||
|
|
UPDATE public.downloads SET status = 'cancelled' WHERE status IN ('preparing','ready','revoked');
|
||
|
|
ALTER TABLE public.downloads ADD CONSTRAINT downloads_status_check
|
||
|
|
CHECK (status IN ('queued','downloading','completed','failed','cancelled'));
|
||
|
|
ALTER TABLE public.downloads
|
||
|
|
DROP COLUMN artifact_id,
|
||
|
|
DROP COLUMN revision,
|
||
|
|
DROP COLUMN target_bitrate_kbps,
|
||
|
|
DROP COLUMN effective_quality,
|
||
|
|
DROP COLUMN quality,
|
||
|
|
DROP COLUMN format,
|
||
|
|
DROP COLUMN device_id,
|
||
|
|
DROP COLUMN profile_id;
|
||
|
|
-- +goose StatementEnd
|