Spaces:
Sleeping
Sleeping
File size: 6,624 Bytes
aa9892b | 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 | -- 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;
|