sori.studio/db/migrations/069_media_catalog.sql

ALTER TABLE media_metadata
  ADD COLUMN IF NOT EXISTS id BIGINT GENERATED BY DEFAULT AS IDENTITY,
  ADD COLUMN IF NOT EXISTS file_name TEXT,
  ADD COLUMN IF NOT EXISTS storage_key TEXT,
  ADD COLUMN IF NOT EXISTS extension TEXT,
  ADD COLUMN IF NOT EXISTS kind TEXT,
  ADD COLUMN IF NOT EXISTS role TEXT,
  ADD COLUMN IF NOT EXISTS size_bytes BIGINT,
  ADD COLUMN IF NOT EXISTS modified_at TIMESTAMPTZ,
  ADD COLUMN IF NOT EXISTS parent_id BIGINT,
  ADD COLUMN IF NOT EXISTS origin TEXT,
  ADD COLUMN IF NOT EXISTS status TEXT,
  ADD COLUMN IF NOT EXISTS last_seen_at TIMESTAMPTZ,
  ADD COLUMN IF NOT EXISTS missing_since TIMESTAMPTZ,
  ADD COLUMN IF NOT EXISTS error_detail TEXT;

UPDATE media_metadata
SET
  file_name = COALESCE(file_name, regexp_replace(url, '^.*/', '')),
  storage_key = COALESCE(storage_key, regexp_replace(url, '^/uploads/', '')),
  extension = COALESCE(extension, lower(substring(url from '\.[^.?#/]+$'))),
  kind = COALESCE(kind, 'file'),
  role = COALESCE(role, 'original'),
  origin = COALESCE(origin, 'external'),
  status = COALESCE(status, 'staging')
WHERE file_name IS NULL
  OR storage_key IS NULL
  OR kind IS NULL
  OR role IS NULL
  OR origin IS NULL
  OR status IS NULL;

ALTER TABLE media_metadata
  ALTER COLUMN file_name SET NOT NULL,
  ALTER COLUMN storage_key SET NOT NULL,
  ALTER COLUMN kind SET NOT NULL,
  ALTER COLUMN role SET NOT NULL,
  ALTER COLUMN origin SET NOT NULL,
  ALTER COLUMN status SET NOT NULL;

CREATE UNIQUE INDEX IF NOT EXISTS media_metadata_id_unique_idx
  ON media_metadata (id);

CREATE UNIQUE INDEX IF NOT EXISTS media_metadata_storage_key_unique_idx
  ON media_metadata (storage_key);

CREATE INDEX IF NOT EXISTS media_metadata_status_updated_idx
  ON media_metadata (status, modified_at DESC);

CREATE INDEX IF NOT EXISTS media_metadata_kind_role_idx
  ON media_metadata (kind, role);

CREATE INDEX IF NOT EXISTS media_metadata_parent_idx
  ON media_metadata (parent_id)
  WHERE parent_id IS NOT NULL;

CREATE INDEX IF NOT EXISTS media_metadata_last_seen_idx
  ON media_metadata (last_seen_at DESC);

DO $$
BEGIN
  IF NOT EXISTS (
    SELECT 1
    FROM pg_constraint
    WHERE conname = 'media_metadata_kind_check'
  ) THEN
    ALTER TABLE media_metadata
      ADD CONSTRAINT media_metadata_kind_check
      CHECK (kind IN ('image', 'video', 'audio', 'file'));
  END IF;

  IF NOT EXISTS (
    SELECT 1
    FROM pg_constraint
    WHERE conname = 'media_metadata_status_check'
  ) THEN
    ALTER TABLE media_metadata
      ADD CONSTRAINT media_metadata_status_check
      CHECK (status IN ('staging', 'active', 'missing', 'deleting', 'error'));
  END IF;

  IF NOT EXISTS (
    SELECT 1
    FROM pg_constraint
    WHERE conname = 'media_metadata_origin_check'
  ) THEN
    ALTER TABLE media_metadata
      ADD CONSTRAINT media_metadata_origin_check
      CHECK (origin IN ('upload', 'import', 'external', 'derived', 'system'));
  END IF;

  IF NOT EXISTS (
    SELECT 1
    FROM pg_constraint
    WHERE conname = 'media_metadata_parent_fk'
  ) THEN
    ALTER TABLE media_metadata
      ADD CONSTRAINT media_metadata_parent_fk
      FOREIGN KEY (parent_id)
      REFERENCES media_metadata (id)
      ON DELETE SET NULL;
  END IF;
END
$$;

CREATE TABLE IF NOT EXISTS media_references (
  id BIGINT GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
  media_id BIGINT REFERENCES media_metadata (id) ON DELETE CASCADE,
  media_url TEXT NOT NULL,
  reference_type TEXT NOT NULL,
  reference_id TEXT NOT NULL,
  reference_field TEXT NOT NULL,
  reference_title TEXT NOT NULL DEFAULT '',
  reference_slug TEXT NOT NULL DEFAULT '',
  reference_status TEXT NOT NULL DEFAULT '',
  created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
  updated_at TIMESTAMPTZ NOT NULL DEFAULT now(),
  CONSTRAINT media_references_target_unique
    UNIQUE (media_url, reference_type, reference_id, reference_field)
);

CREATE INDEX IF NOT EXISTS media_references_media_id_idx
  ON media_references (media_id)
  WHERE media_id IS NOT NULL;

CREATE INDEX IF NOT EXISTS media_references_url_idx
  ON media_references (media_url);

CREATE INDEX IF NOT EXISTS media_references_target_idx
  ON media_references (reference_type, reference_id);

CREATE TABLE IF NOT EXISTS media_catalog_state (
  singleton BOOLEAN PRIMARY KEY DEFAULT true,
  ready BOOLEAN NOT NULL DEFAULT false,
  reconciled_at TIMESTAMPTZ,
  filesystem_count BIGINT NOT NULL DEFAULT 0,
  filesystem_bytes BIGINT NOT NULL DEFAULT 0,
  catalog_count BIGINT NOT NULL DEFAULT 0,
  catalog_bytes BIGINT NOT NULL DEFAULT 0,
  error_detail TEXT,
  updated_at TIMESTAMPTZ NOT NULL DEFAULT now(),
  CONSTRAINT media_catalog_state_singleton_check
    CHECK (singleton = true)
);

INSERT INTO media_catalog_state (singleton, ready)
VALUES (true, false)
ON CONFLICT (singleton) DO NOTHING;