Poll Cookbook

Day-to-day recipes for creating polls. Everything runs in the Supabase SQL Editor.

Before anything — install the helper (once)

Every recipe below uses create_poll(). If you haven't installed it yet, run this once and never again.

create or replace function public.create_poll(
  p_question  text,
  p_slot      text,
  p_options   text[],
  p_xp        integer     default 100,
  p_opens_at  timestamptz default now(),
  p_closes_at timestamptz default null
)
returns uuid
language plpgsql
set search_path = public
as $function$
declare
  v_id uuid;
begin
  if array_length(p_options, 1) is null or array_length(p_options, 1) < 2 then
    raise exception 'A poll needs at least 2 options';
  end if;

  insert into public.polls (question, slot, xp_award, opens_at, closes_at)
  values (p_question, nullif(trim(p_slot), ''), p_xp, p_opens_at, p_closes_at)
  returning id into v_id;

  insert into public.poll_options (poll_id, label, sort)
  select v_id, o, i
  from unnest(p_options) with ordinality as t(o, i);

  return v_id;
end;
$function$;

revoke execute on function public.create_poll(text,text,text[],integer,timestamptz,timestamptz)
  from anon, authenticated;

Apostrophes. Inside SQL strings, a single quote must be doubled. 'You don''t care' — correct. 'You don't care' — syntax error.

1 · Weekly poll

Slot: weekly · XP: 50 · Window: Monday → Monday

select public.create_poll(
  'Which 2026 release has held up best?',
  'weekly',
  array[
    'Clair Obscur: Expedition 33',
    'Death Stranding 2',
    'Hollow Knight: Silksong',
    'Ghost of Yotei'
  ],
  50,
  date_trunc('week', now()),
  date_trunc('week', now()) + interval '7 days'
);

Appears immediately in the homepage hub (WEEKLY tab) and anywhere you've mounted data-poll-slot="weekly". Retires itself when the window closes.

2 · Monthly poll

Slot: monthly · XP: 100 · Window: 1st → 1st

select public.create_poll(
  'How do you feel about Sony abandoning physical media?',
  'monthly',
  array[
    'You care, and collect physical',
    'You care, but lean towards digital',
    'You don''t care, digital is better anyway'
  ],
  100,
  date_trunc('month', now()),
  date_trunc('month', now()) + interval '1 month'
);

3 · Article poll

Slot: post-<post-slug> · XP: 100 · Window: never closes

Step 1 — lock the Post URL first

In the Ghost editor, open Settings → Post URL and confirm the slug is final.

Ghost rewrites the slug while a draft is untitled. If you create the poll against post-untitled and then title the post, the mount looks for a slot that no longer exists and hides itself — leaving the poll orphaned.

Step 2 — create the poll

Slot must equal the Post URL exactly, prefixed with post-.

select public.create_poll(
  'How does Silksong hold up against Hollow Knight?',
  'post-silksong-review',
  array['Surpasses it','Matches it','Falls short','Too early to say']
);

Step 3 — nothing

post.hbs already carries an auto-mount on every post. The poll appears between the article body and the comments on its own. Posts without a poll render nothing.

To override placement — put it mid-article instead of after it — drop an HTML card where you want it:

<div class="tarrmu-poll" data-poll-slot="post-silksong-review"></div>

A hand-placed card automatically suppresses the auto-mount, so you never get two.

Two articles about the same game

Not a problem. The slot is the post slug, not the game, and Ghost guarantees post slugs are unique. post-silksong-review and post-silksong-two-years-on are separate polls with separate votes.

Scheduling ahead

Polls in the same slot with different windows coexist. The live one shows, future ones wait, rotation happens on its own. No deleting, no flag flipping.

Next month's monthly, written today:

select public.create_poll(
  'Next month''s question?',
  'monthly',
  array['Option A','Option B','Option C'],
  100,
  date_trunc('month', now()) + interval '1 month',
  date_trunc('month', now()) + interval '2 months'
);

A quarter of weeklies in one statement:

select public.create_poll(
  'Weekly poll — week ' || to_char(wk, 'IW'),
  'weekly',
  array['Option A','Option B','Option C'],
  50, wk, wk + interval '7 days'
)
from generate_series(
  date_trunc('week', now()) + interval '1 week',
  date_trunc('week', now()) + interval '12 weeks',
  interval '1 week'
) as wk;

Checking your work

select * from public.poll_status;

You want exactly one live = true row per slot.

Fixing things

Kill a poll early — falls through to the previous one in that slot:

select public.close_poll('monthly');

Extend a window:

update public.polls set closes_at = closes_at + interval '7 days'
 where slot = 'weekly' and is_open;

Post slug changed after you made the poll — re-point, don't recreate. Votes live on poll_id, so nothing is lost:

update public.polls set slot = 'post-new-slug' where slot = 'post-old-slug';

Find orphans — article polls whose slug no longer matches a post:

select slot, question, created_at from public.polls
where slot like 'post-%' order by created_at desc;

Two polls fighting over one slot — only happens if you gave them identical windows. Close one:

select public.close_poll('monthly');   -- then create the replacement

Never delete a poll that has votes. poll_options, poll_votes and poll_xp_awards all cascade on polls.id. Deleting takes the XP ledger with it, and the ledger is the only thing stopping everyone who voted from earning the XP a second time. Close instead — old polls cost nothing and are your archive.

Cheat sheet

XP on top of the award: milestone quests fire at 1, 5, 10, 25 and 50 polls voted (+50 / +100 / +150 / +300 / +500), for 1,100 lifetime. Retract never claws back XP.

Widget states