mini-prog/backend/migrations/0005_config_pipeline.sql

BEGIN;

CREATE TABLE event_config_sources (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  event_id UUID NOT NULL REFERENCES events(id) ON DELETE CASCADE,
  source_version_no INTEGER NOT NULL,
  source_kind TEXT NOT NULL DEFAULT 'event_bundle' CHECK (source_kind IN ('event_bundle', 'manifest_only', 'preset')),
  schema_id TEXT NOT NULL DEFAULT 'event-source',
  schema_version TEXT NOT NULL DEFAULT '1',
  status TEXT NOT NULL DEFAULT 'draft' CHECK (status IN ('draft', 'active', 'archived')),
  source_jsonb JSONB NOT NULL DEFAULT '{}'::jsonb,
  notes TEXT,
  created_by_user_id UUID REFERENCES users(id) ON DELETE SET NULL,
  created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
  UNIQUE (event_id, source_version_no)
);

CREATE INDEX event_config_sources_event_id_idx ON event_config_sources(event_id);
CREATE INDEX event_config_sources_status_idx ON event_config_sources(status);

CREATE TABLE event_config_builds (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  event_id UUID NOT NULL REFERENCES events(id) ON DELETE CASCADE,
  source_id UUID NOT NULL REFERENCES event_config_sources(id) ON DELETE CASCADE,
  build_no INTEGER NOT NULL,
  build_status TEXT NOT NULL DEFAULT 'success' CHECK (build_status IN ('pending', 'running', 'success', 'failed', 'cancelled')),
  build_log TEXT,
  manifest_jsonb JSONB NOT NULL DEFAULT '{}'::jsonb,
  asset_index_jsonb JSONB NOT NULL DEFAULT '[]'::jsonb,
  created_by_user_id UUID REFERENCES users(id) ON DELETE SET NULL,
  created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
  UNIQUE (event_id, build_no)
);

CREATE INDEX event_config_builds_event_id_idx ON event_config_builds(event_id);
CREATE INDEX event_config_builds_source_id_idx ON event_config_builds(source_id);
CREATE INDEX event_config_builds_status_idx ON event_config_builds(build_status);

ALTER TABLE event_releases
ADD COLUMN build_id UUID REFERENCES event_config_builds(id) ON DELETE SET NULL;

CREATE INDEX event_releases_build_id_idx ON event_releases(build_id);

CREATE TABLE event_release_assets (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  event_release_id UUID NOT NULL REFERENCES event_releases(id) ON DELETE CASCADE,
  asset_type TEXT NOT NULL CHECK (asset_type IN ('manifest', 'mapmeta', 'tiles', 'playfield', 'content_html', 'media', 'other')),
  asset_key TEXT NOT NULL,
  asset_path TEXT,
  asset_url TEXT NOT NULL,
  checksum TEXT,
  size_bytes BIGINT,
  meta_jsonb JSONB NOT NULL DEFAULT '{}'::jsonb,
  created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
  UNIQUE (event_release_id, asset_key)
);

CREATE INDEX event_release_assets_release_id_idx ON event_release_assets(event_release_id);
CREATE INDEX event_release_assets_asset_type_idx ON event_release_assets(asset_type);

COMMIT;