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;