Spaces:

stylevoice
/

talk

Sleeping

talk / backend /sql /013_playlist_schedules.sql

user

Deploy StyleVoiceChat backend

aa9892b 4 months ago

6.62 kB

-- 013_playlist_schedules.sql
-- 플레이리스트 예약 스케줄 + Web Push (VAPID) 구독 + SMS 전화번호 인증.
--
-- 운영 정책:
--  • playlists 는 *스케줄이 걸린 것만* 서버에 동기화 (그 외에는 클라 localStorage).
--  • playlist_schedules.next_fire_at 을 미리 계산 → cron 1분 tick 이 인덱스 스캔만.
--  • push_subscriptions 는 단말별 (endpoint UNIQUE).
--  • users.phone_e164 는 user_profiles 에 추가 (별도 인증 테이블).

BEGIN;

-- ───────── playlists ─────────
CREATE TABLE IF NOT EXISTS public.playlists (
    id              UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    user_id         UUID NOT NULL REFERENCES auth.users(id) ON DELETE CASCADE,
    name            TEXT NOT NULL,
    items           JSONB NOT NULL DEFAULT '[]'::jsonb,
                    -- [{entryId, repeatCount?, repeatDurationSec?,
                    --   momentId?, slotValues?, presetId?, voice?}]
                    -- moment* 필드는 cache miss 시 다른 단말이 재합성하기 위한 메타.
    client_id       TEXT,           -- 클라이언트가 만든 id (pl_xxx) — 동기화 매칭용
    updated_at      TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    created_at      TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    UNIQUE (user_id, client_id)
);

CREATE INDEX IF NOT EXISTS idx_playlists_user
    ON public.playlists(user_id);

-- ───────── playlist_schedules ─────────
CREATE TABLE IF NOT EXISTS public.playlist_schedules (
    id              UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    user_id         UUID NOT NULL REFERENCES auth.users(id) ON DELETE CASCADE,
    playlist_id     UUID NOT NULL REFERENCES public.playlists(id) ON DELETE CASCADE,

    kind            TEXT NOT NULL CHECK (kind IN ('once', 'daily', 'weekly')),
    -- once  : scheduled_at 사용 (절대 일시)
    -- daily : time_of_day 사용 ('HH:MM')
    -- weekly: time_of_day + weekdays (배열 [0..6], 0=일요일)
    scheduled_at    TIMESTAMPTZ,
    time_of_day     TEXT,           -- 'HH:MM'
    weekdays        SMALLINT[],     -- e.g. {1,3,5} (월수금)
    timezone        TEXT NOT NULL DEFAULT 'Asia/Seoul',

    notify_push     BOOLEAN NOT NULL DEFAULT TRUE,
    notify_sms      BOOLEAN NOT NULL DEFAULT FALSE,

    title           TEXT,           -- 알림 제목 override (null 이면 플레이리스트 이름 사용)
    body            TEXT,           -- 알림 본문 override

    active          BOOLEAN NOT NULL DEFAULT TRUE,
    last_fired_at   TIMESTAMPTZ,
    next_fire_at    TIMESTAMPTZ,    -- 미리 계산. NULL 이면 fired-once 종료.

    created_at      TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    updated_at      TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

CREATE INDEX IF NOT EXISTS idx_schedules_next_fire
    ON public.playlist_schedules(next_fire_at)
    WHERE active = TRUE AND next_fire_at IS NOT NULL;

CREATE INDEX IF NOT EXISTS idx_schedules_user
    ON public.playlist_schedules(user_id);

CREATE INDEX IF NOT EXISTS idx_schedules_playlist
    ON public.playlist_schedules(playlist_id);

-- ───────── push_subscriptions (W3C Web Push + VAPID) ─────────
CREATE TABLE IF NOT EXISTS public.push_subscriptions (
    id              UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    user_id         UUID NOT NULL REFERENCES auth.users(id) ON DELETE CASCADE,
    endpoint        TEXT NOT NULL UNIQUE,   -- FCM push service endpoint
    p256dh          TEXT NOT NULL,          -- public key (base64url)
    auth            TEXT NOT NULL,          -- auth secret (base64url)
    user_agent      TEXT,
    last_used_at    TIMESTAMPTZ,
    created_at      TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

CREATE INDEX IF NOT EXISTS idx_push_sub_user
    ON public.push_subscriptions(user_id);

-- ───────── user_profiles 에 전화번호 컬럼 ─────────
-- user_profiles 테이블이 이미 존재한다고 가정 (이전 마이그레이션에서 생성).
DO $$
BEGIN
    IF EXISTS (
        SELECT 1 FROM information_schema.tables
        WHERE table_schema = 'public' AND table_name = 'user_profiles'
    ) THEN
        BEGIN
            ALTER TABLE public.user_profiles ADD COLUMN IF NOT EXISTS phone_e164 TEXT;
            ALTER TABLE public.user_profiles ADD COLUMN IF NOT EXISTS phone_verified_at TIMESTAMPTZ;
        EXCEPTION WHEN duplicate_column THEN
            -- 이미 추가됨 — 무시
            NULL;
        END;
    END IF;
END $$;

-- ───────── sms_verifications (인증 코드) ─────────
CREATE TABLE IF NOT EXISTS public.sms_verifications (
    id              UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    user_id         UUID NOT NULL REFERENCES auth.users(id) ON DELETE CASCADE,
    phone_e164      TEXT NOT NULL,
    code_hash       TEXT NOT NULL,          -- SHA-256 hex
    attempts        INT NOT NULL DEFAULT 0,
    expires_at      TIMESTAMPTZ NOT NULL,
    consumed_at     TIMESTAMPTZ,
    created_at      TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

CREATE INDEX IF NOT EXISTS idx_sms_verif_user
    ON public.sms_verifications(user_id);

-- ───────── RLS ─────────
ALTER TABLE public.playlists ENABLE ROW LEVEL SECURITY;
ALTER TABLE public.playlist_schedules ENABLE ROW LEVEL SECURITY;
ALTER TABLE public.push_subscriptions ENABLE ROW LEVEL SECURITY;
ALTER TABLE public.sms_verifications ENABLE ROW LEVEL SECURITY;

DO $$
BEGIN
    IF NOT EXISTS (SELECT 1 FROM pg_policies WHERE tablename='playlists' AND policyname='playlists_owner') THEN
        CREATE POLICY playlists_owner ON public.playlists
            USING (auth.uid() = user_id) WITH CHECK (auth.uid() = user_id);
    END IF;
    IF NOT EXISTS (SELECT 1 FROM pg_policies WHERE tablename='playlist_schedules' AND policyname='schedules_owner') THEN
        CREATE POLICY schedules_owner ON public.playlist_schedules
            USING (auth.uid() = user_id) WITH CHECK (auth.uid() = user_id);
    END IF;
    IF NOT EXISTS (SELECT 1 FROM pg_policies WHERE tablename='push_subscriptions' AND policyname='push_sub_owner') THEN
        CREATE POLICY push_sub_owner ON public.push_subscriptions
            USING (auth.uid() = user_id) WITH CHECK (auth.uid() = user_id);
    END IF;
    IF NOT EXISTS (SELECT 1 FROM pg_policies WHERE tablename='sms_verifications' AND policyname='sms_verif_owner') THEN
        CREATE POLICY sms_verif_owner ON public.sms_verifications
            USING (auth.uid() = user_id) WITH CHECK (auth.uid() = user_id);
    END IF;
END $$;

COMMIT;

	-- 013_playlist_schedules.sql
	-- 플레이리스트 예약 스케줄 + Web Push (VAPID) 구독 + SMS 전화번호 인증.
	--
	-- 운영 정책:
	-- • playlists 는 스케줄이 걸린 것만 서버에 동기화 (그 외에는 클라 localStorage).
	-- • playlist_schedules.next_fire_at 을 미리 계산 → cron 1분 tick 이 인덱스 스캔만.
	-- • push_subscriptions 는 단말별 (endpoint UNIQUE).
	-- • users.phone_e164 는 user_profiles 에 추가 (별도 인증 테이블).

	BEGIN;

	-- ───────── playlists ─────────
	CREATE TABLE IF NOT EXISTS public.playlists (
	id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
	user_id UUID NOT NULL REFERENCES auth.users(id) ON DELETE CASCADE,
	name TEXT NOT NULL,
	items JSONB NOT NULL DEFAULT '[]'::jsonb,
	-- [{entryId, repeatCount?, repeatDurationSec?,
	-- momentId?, slotValues?, presetId?, voice?}]
	-- moment* 필드는 cache miss 시 다른 단말이 재합성하기 위한 메타.
	client_id TEXT, -- 클라이언트가 만든 id (pl_xxx) — 동기화 매칭용
	updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
	created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
	UNIQUE (user_id, client_id)
	);

	CREATE INDEX IF NOT EXISTS idx_playlists_user
	ON public.playlists(user_id);

	-- ───────── playlist_schedules ─────────
	CREATE TABLE IF NOT EXISTS public.playlist_schedules (
	id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
	user_id UUID NOT NULL REFERENCES auth.users(id) ON DELETE CASCADE,
	playlist_id UUID NOT NULL REFERENCES public.playlists(id) ON DELETE CASCADE,

	kind TEXT NOT NULL CHECK (kind IN ('once', 'daily', 'weekly')),
	-- once : scheduled_at 사용 (절대 일시)
	-- daily : time_of_day 사용 ('HH:MM')
	-- weekly: time_of_day + weekdays (배열 [0..6], 0=일요일)
	scheduled_at TIMESTAMPTZ,
	time_of_day TEXT, -- 'HH:MM'
	weekdays SMALLINT[], -- e.g. {1,3,5} (월수금)
	timezone TEXT NOT NULL DEFAULT 'Asia/Seoul',

	notify_push BOOLEAN NOT NULL DEFAULT TRUE,
	notify_sms BOOLEAN NOT NULL DEFAULT FALSE,

	title TEXT, -- 알림 제목 override (null 이면 플레이리스트 이름 사용)
	body TEXT, -- 알림 본문 override

	active BOOLEAN NOT NULL DEFAULT TRUE,
	last_fired_at TIMESTAMPTZ,
	next_fire_at TIMESTAMPTZ, -- 미리 계산. NULL 이면 fired-once 종료.

	created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
	updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
	);

	CREATE INDEX IF NOT EXISTS idx_schedules_next_fire
	ON public.playlist_schedules(next_fire_at)
	WHERE active = TRUE AND next_fire_at IS NOT NULL;

	CREATE INDEX IF NOT EXISTS idx_schedules_user
	ON public.playlist_schedules(user_id);

	CREATE INDEX IF NOT EXISTS idx_schedules_playlist
	ON public.playlist_schedules(playlist_id);

	-- ───────── push_subscriptions (W3C Web Push + VAPID) ─────────
	CREATE TABLE IF NOT EXISTS public.push_subscriptions (
	id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
	user_id UUID NOT NULL REFERENCES auth.users(id) ON DELETE CASCADE,
	endpoint TEXT NOT NULL UNIQUE, -- FCM push service endpoint
	p256dh TEXT NOT NULL, -- public key (base64url)
	auth TEXT NOT NULL, -- auth secret (base64url)
	user_agent TEXT,
	last_used_at TIMESTAMPTZ,
	created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
	);

	CREATE INDEX IF NOT EXISTS idx_push_sub_user
	ON public.push_subscriptions(user_id);

	-- ───────── user_profiles 에 전화번호 컬럼 ─────────
	-- user_profiles 테이블이 이미 존재한다고 가정 (이전 마이그레이션에서 생성).
	DO $$
	BEGIN
	IF EXISTS (
	SELECT 1 FROM information_schema.tables
	WHERE table_schema = 'public' AND table_name = 'user_profiles'
	) THEN
	BEGIN
	ALTER TABLE public.user_profiles ADD COLUMN IF NOT EXISTS phone_e164 TEXT;
	ALTER TABLE public.user_profiles ADD COLUMN IF NOT EXISTS phone_verified_at TIMESTAMPTZ;
	EXCEPTION WHEN duplicate_column THEN
	-- 이미 추가됨 — 무시
	NULL;
	END;
	END IF;
	END $$;

	-- ───────── sms_verifications (인증 코드) ─────────
	CREATE TABLE IF NOT EXISTS public.sms_verifications (
	id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
	user_id UUID NOT NULL REFERENCES auth.users(id) ON DELETE CASCADE,
	phone_e164 TEXT NOT NULL,
	code_hash TEXT NOT NULL, -- SHA-256 hex
	attempts INT NOT NULL DEFAULT 0,
	expires_at TIMESTAMPTZ NOT NULL,
	consumed_at TIMESTAMPTZ,
	created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
	);

	CREATE INDEX IF NOT EXISTS idx_sms_verif_user
	ON public.sms_verifications(user_id);

	-- ───────── RLS ─────────
	ALTER TABLE public.playlists ENABLE ROW LEVEL SECURITY;
	ALTER TABLE public.playlist_schedules ENABLE ROW LEVEL SECURITY;
	ALTER TABLE public.push_subscriptions ENABLE ROW LEVEL SECURITY;
	ALTER TABLE public.sms_verifications ENABLE ROW LEVEL SECURITY;

	DO $$
	BEGIN
	IF NOT EXISTS (SELECT 1 FROM pg_policies WHERE tablename='playlists' AND policyname='playlists_owner') THEN
	CREATE POLICY playlists_owner ON public.playlists
	USING (auth.uid() = user_id) WITH CHECK (auth.uid() = user_id);
	END IF;
	IF NOT EXISTS (SELECT 1 FROM pg_policies WHERE tablename='playlist_schedules' AND policyname='schedules_owner') THEN
	CREATE POLICY schedules_owner ON public.playlist_schedules
	USING (auth.uid() = user_id) WITH CHECK (auth.uid() = user_id);
	END IF;
	IF NOT EXISTS (SELECT 1 FROM pg_policies WHERE tablename='push_subscriptions' AND policyname='push_sub_owner') THEN
	CREATE POLICY push_sub_owner ON public.push_subscriptions
	USING (auth.uid() = user_id) WITH CHECK (auth.uid() = user_id);
	END IF;
	IF NOT EXISTS (SELECT 1 FROM pg_policies WHERE tablename='sms_verifications' AND policyname='sms_verif_owner') THEN
	CREATE POLICY sms_verif_owner ON public.sms_verifications
	USING (auth.uid() = user_id) WITH CHECK (auth.uid() = user_id);
	END IF;
	END $$;

	COMMIT;