75 lines
3.7 KiB
PL/PgSQL
75 lines
3.7 KiB
PL/PgSQL
-- VYNDR Web — odds cache + waitlist signups
|
|
-- Idempotent. Safe to apply multiple times.
|
|
|
|
create extension if not exists "pgcrypto";
|
|
|
|
-- ────────────────────────────────────────────────────────────
|
|
-- odds_cache — TTL-backed mirror of upstream Odds API data
|
|
-- Reduces calls to the rate-limited Odds API (500 req/month free tier).
|
|
-- All Next.js route handlers must read from this table, never the
|
|
-- upstream API directly.
|
|
-- ────────────────────────────────────────────────────────────
|
|
create table if not exists public.odds_cache (
|
|
cache_key text primary key,
|
|
sport text not null,
|
|
data_type text not null,
|
|
payload jsonb not null,
|
|
fetched_at timestamptz not null default now(),
|
|
expires_at timestamptz not null default (now() + interval '5 minutes')
|
|
);
|
|
|
|
create index if not exists idx_odds_cache_sport on public.odds_cache(sport);
|
|
create index if not exists idx_odds_cache_expires on public.odds_cache(expires_at);
|
|
|
|
alter table public.odds_cache enable row level security;
|
|
|
|
-- Public read OK (cache contents are public market data anyway).
|
|
-- Writes restricted to service role (bypasses RLS automatically).
|
|
drop policy if exists "anyone can read odds cache" on public.odds_cache;
|
|
create policy "anyone can read odds cache"
|
|
on public.odds_cache for select
|
|
using (true);
|
|
|
|
-- Cleanup helper — call from a cron or trigger if rows accumulate.
|
|
create or replace function public.prune_expired_odds_cache()
|
|
returns integer as $$
|
|
declare
|
|
deleted_count integer;
|
|
begin
|
|
delete from public.odds_cache
|
|
where expires_at < now() - interval '1 hour';
|
|
get diagnostics deleted_count = row_count;
|
|
return deleted_count;
|
|
end;
|
|
$$ language plpgsql security definer set search_path = public;
|
|
|
|
-- ────────────────────────────────────────────────────────────
|
|
-- waitlist_signups — capture email for upcoming launches
|
|
-- Referenced by /api/waitlist (marketplace pre-launch lists).
|
|
-- ────────────────────────────────────────────────────────────
|
|
create table if not exists public.waitlist_signups (
|
|
id uuid primary key default gen_random_uuid(),
|
|
email text not null,
|
|
list text not null default 'general',
|
|
source text,
|
|
created_at timestamptz not null default now(),
|
|
unique (email, list)
|
|
);
|
|
|
|
create index if not exists idx_waitlist_list on public.waitlist_signups(list);
|
|
create index if not exists idx_waitlist_created on public.waitlist_signups(created_at desc);
|
|
|
|
alter table public.waitlist_signups enable row level security;
|
|
-- Inserts/reads restricted to service role only. No public policies.
|
|
|
|
-- ────────────────────────────────────────────────────────────
|
|
-- founder_pricing_seat_count — convenience view used by checkout
|
|
-- to decide if the next paid user still gets the $9.99 lock-in.
|
|
-- ────────────────────────────────────────────────────────────
|
|
create or replace view public.founder_pricing_seats as
|
|
select
|
|
count(*) filter (where founder_pricing) as taken,
|
|
100 as cap,
|
|
greatest(0, 100 - count(*) filter (where founder_pricing)) as remaining
|
|
from public.user_profiles;
|