mini-prog/backend/migrations/0012_managed_assets.sql

BEGIN;

CREATE TABLE managed_assets (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  asset_public_id TEXT NOT NULL UNIQUE,
  asset_type TEXT NOT NULL,
  asset_code TEXT NOT NULL,
  version TEXT NOT NULL,
  title TEXT,
  source_mode TEXT NOT NULL CHECK (source_mode IN ('uploaded', 'external_link')),
  storage_provider TEXT NOT NULL CHECK (storage_provider IN ('oss', 'external')),
  object_key TEXT,
  public_url TEXT NOT NULL,
  file_name TEXT,
  content_type TEXT,
  file_size_bytes BIGINT,
  checksum_sha256 TEXT,
  status TEXT NOT NULL DEFAULT 'active' CHECK (status IN ('draft', 'active', 'disabled', 'archived')),
  metadata_jsonb JSONB NOT NULL DEFAULT '{}'::jsonb,
  created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
  updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
  UNIQUE (asset_type, asset_code, version)
);

CREATE INDEX managed_assets_asset_type_idx ON managed_assets(asset_type);
CREATE INDEX managed_assets_asset_code_idx ON managed_assets(asset_code);
CREATE INDEX managed_assets_status_idx ON managed_assets(status);

CREATE TRIGGER managed_assets_set_updated_at
BEFORE UPDATE ON managed_assets
FOR EACH ROW EXECUTE FUNCTION set_updated_at();

COMMIT;