moviedice/supabase/migrations/00001_initial_schema.sql

-- 00001_initial_schema.sql
-- MovieDice: initial database schema
-- Tables: users, groups, group_members, movies, landing_reel_posters

-- =============================================================================
-- users
-- =============================================================================
CREATE TABLE public.users (
  id            uuid PRIMARY KEY,  -- maps to auth.users.id (Supabase Anonymous Sign-In)
  display_name  text NOT NULL
    CONSTRAINT users_display_name_length CHECK (char_length(display_name) BETWEEN 1 AND 30)
    CONSTRAINT users_display_name_no_html CHECK (display_name !~ '[<>]')
    CONSTRAINT users_display_name_no_control CHECK (display_name !~ '[\x00-\x1F\x7F]'),
  avatar_color  text
    CONSTRAINT users_avatar_color_hex CHECK (avatar_color IS NULL OR avatar_color ~ '^#[0-9a-fA-F]{6}$'),
  recovery_code text,  -- Argon2id hashed, nullable, single-use
  last_active_at timestamptz NOT NULL DEFAULT now(),
  created_at     timestamptz NOT NULL DEFAULT now()
);

-- =============================================================================
-- groups
-- =============================================================================
CREATE TABLE public.groups (
  id          uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  name        text NOT NULL
    CONSTRAINT groups_name_length CHECK (char_length(name) BETWEEN 1 AND 50)
    CONSTRAINT groups_name_no_html CHECK (name !~ '[<>]')
    CONSTRAINT groups_name_no_control CHECK (name !~ '[\x00-\x1F\x7F]'),
  invite_code text NOT NULL UNIQUE,
  created_by  uuid NOT NULL REFERENCES public.users(id),
  created_at  timestamptz NOT NULL DEFAULT now()
);

-- Note: invite_code already indexed by the UNIQUE constraint

-- =============================================================================
-- group_members
-- =============================================================================
CREATE TABLE public.group_members (
  group_id  uuid NOT NULL REFERENCES public.groups(id) ON DELETE CASCADE,
  user_id   uuid NOT NULL REFERENCES public.users(id) ON DELETE CASCADE,
  role      text NOT NULL DEFAULT 'member'
    CONSTRAINT group_members_role_valid CHECK (role IN ('admin', 'member')),
  joined_at timestamptz NOT NULL DEFAULT now(),
  PRIMARY KEY (group_id, user_id)
);

CREATE INDEX idx_group_members_user_id ON public.group_members (user_id);
-- Note: group_id already indexed as leading column of the composite PK

-- =============================================================================
-- movies
-- =============================================================================
CREATE TABLE public.movies (
  id                    uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  group_id              uuid NOT NULL REFERENCES public.groups(id) ON DELETE CASCADE,
  tmdb_id               integer NOT NULL,
  title                 text NOT NULL,
  year                  integer,
  poster_path           text,
  genres                text[],
  trailer_url           text
    CONSTRAINT movies_trailer_url_domain CHECK (
      trailer_url IS NULL
      OR trailer_url ~ '^https://(www\.)?(youtube\.com|themoviedb\.org|imdb\.com)/'
    ),
  trailer_url_refreshed_at timestamptz,
  metadata_refreshed_at    timestamptz,
  added_by              uuid REFERENCES public.users(id) ON DELETE SET NULL,
  watched               boolean NOT NULL DEFAULT false,
  watched_at            timestamptz,
  added_at              timestamptz NOT NULL DEFAULT now()
);

CREATE INDEX idx_movies_group_id ON public.movies (group_id);
CREATE INDEX idx_movies_added_by ON public.movies (added_by);

-- =============================================================================
-- landing_reel_posters
-- =============================================================================
CREATE TABLE public.landing_reel_posters (
  id           serial PRIMARY KEY,
  tmdb_id      integer NOT NULL,
  poster_path  text NOT NULL,
  title        text NOT NULL,
  refreshed_at timestamptz NOT NULL DEFAULT now()
);