mini-prog/backend/migrations/0002_launch.sql

BEGIN;

CREATE TABLE events (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  tenant_id UUID REFERENCES tenants(id) ON DELETE SET NULL,
  event_public_id TEXT NOT NULL UNIQUE,
  slug TEXT NOT NULL UNIQUE,
  display_name TEXT NOT NULL,
  summary TEXT,
  status TEXT NOT NULL DEFAULT 'draft' CHECK (status IN ('draft', 'active', 'disabled', 'archived')),
  current_release_id UUID,
  created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
  updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

CREATE INDEX events_tenant_id_idx ON events(tenant_id);
CREATE INDEX events_status_idx ON events(status);

CREATE TRIGGER events_set_updated_at
BEFORE UPDATE ON events
FOR EACH ROW EXECUTE FUNCTION set_updated_at();

CREATE TABLE event_releases (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  release_public_id TEXT NOT NULL UNIQUE,
  event_id UUID NOT NULL REFERENCES events(id) ON DELETE CASCADE,
  release_no INTEGER NOT NULL,
  config_label TEXT NOT NULL,
  manifest_url TEXT NOT NULL,
  manifest_checksum_sha256 TEXT,
  route_code TEXT,
  status TEXT NOT NULL DEFAULT 'published' CHECK (status IN ('draft', 'published', 'retired', 'failed')),
  payload_jsonb JSONB NOT NULL DEFAULT '{}'::jsonb,
  published_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
  UNIQUE (event_id, release_no)
);

CREATE INDEX event_releases_event_id_idx ON event_releases(event_id);
CREATE INDEX event_releases_status_idx ON event_releases(status);

ALTER TABLE events
ADD CONSTRAINT events_current_release_fk
FOREIGN KEY (current_release_id) REFERENCES event_releases(id) ON DELETE SET NULL;

CREATE TABLE game_sessions (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  session_public_id TEXT NOT NULL UNIQUE,
  user_id UUID NOT NULL REFERENCES users(id) ON DELETE RESTRICT,
  event_id UUID NOT NULL REFERENCES events(id) ON DELETE RESTRICT,
  event_release_id UUID NOT NULL REFERENCES event_releases(id) ON DELETE RESTRICT,
  device_key TEXT NOT NULL,
  client_type TEXT NOT NULL CHECK (client_type IN ('app', 'wechat')),
  route_code TEXT,
  status TEXT NOT NULL DEFAULT 'launched' CHECK (status IN ('launched', 'running', 'finished', 'failed', 'cancelled')),
  session_token_hash TEXT NOT NULL UNIQUE,
  session_token_expires_at TIMESTAMPTZ NOT NULL,
  launched_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
  started_at TIMESTAMPTZ,
  ended_at TIMESTAMPTZ,
  created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
  updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

CREATE INDEX game_sessions_user_id_idx ON game_sessions(user_id);
CREATE INDEX game_sessions_event_id_idx ON game_sessions(event_id);
CREATE INDEX game_sessions_status_idx ON game_sessions(status);

CREATE TRIGGER game_sessions_set_updated_at
BEFORE UPDATE ON game_sessions
FOR EACH ROW EXECUTE FUNCTION set_updated_at();

COMMIT;