74 lines
3.5 KiB
SQL
74 lines
3.5 KiB
SQL
-- ---------------------------------------------------------------
|
|
-- 015 — Web push subscriptions + MFA tracking + grace period.
|
|
--
|
|
-- Three concerns bundled in one migration because they all surfaced
|
|
-- in the Phase 5 PWA / payments / security pass:
|
|
-- 1. push_subscriptions — per-user push endpoints from the browser
|
|
-- 2. user_profiles.mfa_setup_prompted — track if we've already asked
|
|
-- a paid user to enable MFA (so we only nag once)
|
|
-- 3. user_profiles.grace_period_until — Stripe payment_failed grants
|
|
-- a 48h grace before tier downgrades take effect
|
|
-- ---------------------------------------------------------------
|
|
|
|
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,
|
|
keys_p256dh text not null,
|
|
keys_auth text not null,
|
|
-- One row per (user, endpoint). A user installing the PWA on two
|
|
-- devices gets two rows. Re-subscribing on the same device upserts.
|
|
sport_preferences text[] not null default '{nba,mlb,wnba,nfl,nhl,ncaab,ncaafb}',
|
|
notify_on_resolution boolean not null default true,
|
|
notify_on_cascade boolean not null default true,
|
|
notify_on_cheatsheet boolean not null default true,
|
|
created_at timestamptz not null default now(),
|
|
updated_at timestamptz not null default now(),
|
|
unique (user_id, endpoint)
|
|
);
|
|
|
|
create index if not exists idx_push_subscriptions_user on public.push_subscriptions(user_id);
|
|
create index if not exists idx_push_subscriptions_sport on public.push_subscriptions using gin (sport_preferences);
|
|
create index if not exists idx_push_subscriptions_resolution
|
|
on public.push_subscriptions(notify_on_resolution) where notify_on_resolution = true;
|
|
|
|
drop trigger if exists set_push_subscriptions_updated_at on public.push_subscriptions;
|
|
create trigger set_push_subscriptions_updated_at
|
|
before update on public.push_subscriptions
|
|
for each row execute function public.touch_updated_at();
|
|
|
|
alter table public.push_subscriptions enable row level security;
|
|
|
|
drop policy if exists "users manage own push subscriptions" on public.push_subscriptions;
|
|
create policy "users manage own push subscriptions"
|
|
on public.push_subscriptions for all
|
|
using (auth.uid() = user_id)
|
|
with check (auth.uid() = user_id);
|
|
|
|
-- ---------------------------------------------------------------
|
|
-- user_profiles + users additions.
|
|
--
|
|
-- Two columns get added to BOTH tables because the codebase has two
|
|
-- user-state tables in flight:
|
|
-- public.users — Express backend / Stripe webhook writes here
|
|
-- public.user_profiles — Next.js routes + AuthContext read here
|
|
-- Unifying them is out of scope; keeping the columns in sync is the
|
|
-- least-risk move.
|
|
-- ---------------------------------------------------------------
|
|
|
|
alter table public.user_profiles
|
|
add column if not exists mfa_setup_prompted boolean not null default false,
|
|
add column if not exists grace_period_until timestamptz;
|
|
|
|
alter table public.users
|
|
add column if not exists mfa_setup_prompted boolean not null default false,
|
|
add column if not exists grace_period_until timestamptz;
|
|
|
|
create index if not exists idx_user_profiles_grace_period
|
|
on public.user_profiles(grace_period_until)
|
|
where grace_period_until is not null;
|
|
|
|
create index if not exists idx_users_grace_period
|
|
on public.users(grace_period_until)
|
|
where grace_period_until is not null;
|