How to Add Crowdsourced Event Planning to Your App — Copy-Paste Prompts Included

1create table public.event_proposals (
2  id uuid primary key default gen_random_uuid(),
3  created_by uuid references auth.users(id) on delete cascade not null,
4  title text not null,
5  description text,
6  location text,
7  category text,
8  status text not null default 'proposed' check (status in ('proposed','voting','approved','rejected')),
9  vote_score int not null default 0,
10  hot_score numeric generated always as (
11    vote_score::numeric / power(
12      (extract(epoch from (now() - created_at)) / 3600.0) + 2,
13      1.5
14    )
15  ) stored,
16  created_at timestamptz not null default now()
17);
18
19create table public.votes (
20  id uuid primary key default gen_random_uuid(),
21  user_id uuid references auth.users(id) on delete cascade not null,
22  proposal_id uuid references public.event_proposals(id) on delete cascade not null,
23  value int not null check (value in (-1, 1)),
24  created_at timestamptz not null default now(),
25  unique(user_id, proposal_id)
26);
27
28create table public.date_poll_options (
29  id uuid primary key default gen_random_uuid(),
30  proposal_id uuid references public.event_proposals(id) on delete cascade not null,
31  date_option date not null,
32  created_at timestamptz not null default now()
33);
34
35create table public.date_poll_responses (
36  id uuid primary key default gen_random_uuid(),
37  user_id uuid references auth.users(id) on delete cascade not null,
38  option_id uuid references public.date_poll_options(id) on delete cascade not null,
39  is_available bool not null,
40  created_at timestamptz not null default now(),
41  unique(user_id, option_id)
42);
43
44create table public.comments (
45  id uuid primary key default gen_random_uuid(),
46  proposal_id uuid references public.event_proposals(id) on delete cascade not null,
47  user_id uuid references auth.users(id) on delete cascade not null,
48  parent_id uuid references public.comments(id) on delete cascade,
49  content text not null,
50  created_at timestamptz not null default now()
51);
52
53-- RLS
54alter table public.event_proposals enable row level security;
55alter table public.votes enable row level security;
56alter table public.date_poll_options enable row level security;
57alter table public.date_poll_responses enable row level security;
58alter table public.comments enable row level security;
59
60-- Proposals: anyone authenticated can read; authenticated users can insert own; admins can update status
61create policy "Authenticated users can read proposals"
62  on public.event_proposals for select
63  using (auth.uid() is not null);
64
65create policy "Authenticated users can propose"
66  on public.event_proposals for insert
67  with check (auth.uid() = created_by);
68
69create policy "Admins can update proposal status"
70  on public.event_proposals for update
71  using (
72    (auth.jwt() -> 'app_metadata' ->> 'role') = 'admin'
73    or auth.uid() = created_by
74  );
75
76-- Votes: authenticated users can vote; unique constraint prevents double-vote
77create policy "Authenticated users can vote"
78  on public.votes for insert
79  with check (auth.uid() = user_id and auth.uid() is not null);
80
81create policy "Users can remove own votes"
82  on public.votes for delete
83  using (auth.uid() = user_id);
84
85create policy "Votes are readable by authenticated users"
86  on public.votes for select
87  using (auth.uid() is not null);
88
89-- Date poll options and responses: readable by all authenticated
90create policy "Authenticated users can read poll options"
91  on public.date_poll_options for select
92  using (auth.uid() is not null);
93
94create policy "Proposal owner can add date options"
95  on public.date_poll_options for insert
96  with check (
97    auth.uid() = (select created_by from public.event_proposals where id = proposal_id)
98  );
99
100create policy "Authenticated users can respond to polls"
101  on public.date_poll_responses for all
102  using (auth.uid() = user_id)
103  with check (auth.uid() = user_id);
104
105-- Comments: readable by authenticated; writeable by authenticated
106create policy "Authenticated users can read comments"
107  on public.comments for select
108  using (auth.uid() is not null);
109
110create policy "Authenticated users can comment"
111  on public.comments for insert
112  with check (auth.uid() = user_id);
113
114-- Trigger to update vote_score on votes insert/delete
115create or replace function public.update_proposal_vote_score()
116returns trigger language plpgsql security definer as $$
117begin
118  if tg_op = 'INSERT' then
119    update public.event_proposals
120    set vote_score = vote_score + new.value
121    where id = new.proposal_id;
122  elsif tg_op = 'DELETE' then
123    update public.event_proposals
124    set vote_score = vote_score - old.value
125    where id = old.proposal_id;
126  end if;
127  return null;
128end;
129$$;
130
131create trigger votes_update_score
132  after insert or delete on public.votes
133  for each row execute function public.update_proposal_vote_score();
134
135-- Indexes
136create index proposals_hot_score_idx on public.event_proposals (hot_score desc);
137create index proposals_status_idx on public.event_proposals (status);
138create index comments_proposal_parent_idx on public.comments (proposal_id, parent_id);