Will Anderson
e121038382
Replaces the broken counter-bump RPC with a per-user share_votes table (PK share_id+user_id, RLS-enforced ownership). One vote per user per card, change direction or undo any time. Auth required for write; read is public. share_cards.upvotes/downvotes/score stay in sync via recalc trigger. New endpoints: POST /api/vote (auth-gated), GET /api/vote-state/:id (auth-aware).
106 lines
4.1 KiB
PL/PgSQL
106 lines
4.1 KiB
PL/PgSQL
-- 20260502115405_share_votes.sql
|
|
--
|
|
-- Per-user vote tracking for the /said share gallery.
|
|
--
|
|
-- Replaces the broken counter-bump RPC (vote_card) with a proper relational
|
|
-- design: one row per (share_card, user) pair, RLS-enforced ownership,
|
|
-- aggregate columns on share_cards kept in sync via trigger. Voters can
|
|
-- change direction or undo at any time. One vote per user per card.
|
|
--
|
|
-- This migration is idempotent: re-running it is a no-op.
|
|
|
|
-- ---------------------------------------------------------------------------
|
|
-- Table
|
|
-- ---------------------------------------------------------------------------
|
|
|
|
create table if not exists public.share_votes (
|
|
share_id text not null references public.share_cards(id) on delete cascade,
|
|
user_id uuid not null references auth.users(id) on delete cascade,
|
|
direction text not null check (direction in ('up','down')),
|
|
created_at timestamptz not null default now(),
|
|
updated_at timestamptz not null default now(),
|
|
primary key (share_id, user_id)
|
|
);
|
|
|
|
create index if not exists share_votes_share_id_idx on public.share_votes (share_id);
|
|
create index if not exists share_votes_user_id_idx on public.share_votes (user_id);
|
|
|
|
-- ---------------------------------------------------------------------------
|
|
-- Row Level Security
|
|
-- ---------------------------------------------------------------------------
|
|
|
|
alter table public.share_votes enable row level security;
|
|
|
|
drop policy if exists "users can read all votes" on public.share_votes;
|
|
drop policy if exists "users can insert their own votes" on public.share_votes;
|
|
drop policy if exists "users can update their own votes" on public.share_votes;
|
|
drop policy if exists "users can delete their own votes" on public.share_votes;
|
|
|
|
create policy "users can read all votes" on public.share_votes
|
|
for select using (true);
|
|
|
|
create policy "users can insert their own votes" on public.share_votes
|
|
for insert with check (auth.uid() = user_id);
|
|
|
|
create policy "users can update their own votes" on public.share_votes
|
|
for update using (auth.uid() = user_id);
|
|
|
|
create policy "users can delete their own votes" on public.share_votes
|
|
for delete using (auth.uid() = user_id);
|
|
|
|
-- ---------------------------------------------------------------------------
|
|
-- Aggregate sync trigger
|
|
--
|
|
-- After every insert / update / delete on share_votes, recompute the three
|
|
-- aggregate columns on the parent share_cards row. SECURITY DEFINER is
|
|
-- required because the calling user only has RLS access to their own vote
|
|
-- but the trigger needs to update share_cards.upvotes/downvotes/score.
|
|
-- ---------------------------------------------------------------------------
|
|
|
|
create or replace function public.recalc_share_card_score()
|
|
returns trigger
|
|
language plpgsql
|
|
security definer
|
|
set search_path = public
|
|
as $$
|
|
declare
|
|
s_id text;
|
|
begin
|
|
s_id := coalesce(NEW.share_id, OLD.share_id);
|
|
update public.share_cards
|
|
set upvotes = (select count(*) from public.share_votes
|
|
where share_id = s_id and direction = 'up'),
|
|
downvotes = (select count(*) from public.share_votes
|
|
where share_id = s_id and direction = 'down'),
|
|
score = (select count(*) filter (where direction = 'up')
|
|
- count(*) filter (where direction = 'down')
|
|
from public.share_votes where share_id = s_id)
|
|
where id = s_id;
|
|
return null;
|
|
end;
|
|
$$;
|
|
|
|
drop trigger if exists share_votes_recalc on public.share_votes;
|
|
create trigger share_votes_recalc
|
|
after insert or update or delete on public.share_votes
|
|
for each row execute function public.recalc_share_card_score();
|
|
|
|
-- ---------------------------------------------------------------------------
|
|
-- updated_at touch trigger (only on UPDATE)
|
|
-- ---------------------------------------------------------------------------
|
|
|
|
create or replace function public.touch_share_votes_updated_at()
|
|
returns trigger
|
|
language plpgsql
|
|
as $$
|
|
begin
|
|
NEW.updated_at := now();
|
|
return NEW;
|
|
end;
|
|
$$;
|
|
|
|
drop trigger if exists share_votes_touch on public.share_votes;
|
|
create trigger share_votes_touch
|
|
before update on public.share_votes
|
|
for each row execute function public.touch_share_votes_updated_at();
|