-- Guestbook only. Run once in your own Supabase SQL Editor. -- It can coexist with chatroom.sql; shared rate-limit objects are safe to recreate. begin; create table if not exists public.widget_guestbook ( id uuid primary key default gen_random_uuid(), name text not null check (char_length(btrim(name)) between 1 and 24), message text not null check (char_length(btrim(message)) between 1 and 500), website text check (char_length(website) <= 250 and website ~ '^https?://'), owner_reply text check (char_length(owner_reply) <= 500), created_at timestamptz not null default now() ); alter table public.widget_guestbook add column if not exists contact text check (char_length(contact) <= 250); create index if not exists widget_guestbook_recent_all on public.widget_guestbook (created_at desc, id desc); alter table public.widget_guestbook enable row level security; revoke all on public.widget_guestbook from public, anon, authenticated, service_role; grant select (id,name,message,created_at,website,owner_reply) on public.widget_guestbook to service_role; grant insert (name,message,website,contact) on public.widget_guestbook to service_role; -- The Worker sends a keyed hash, never the raw visitor address. create table if not exists public.widget_write_limit ( visitor_hash text primary key check (visitor_hash ~ '^[0-9a-f]{64}$'), minute_bucket bigint not null, uses integer not null check (uses between 1 and 6) ); create index if not exists widget_write_limit_expiry on public.widget_write_limit (minute_bucket); alter table public.widget_write_limit enable row level security; revoke all on public.widget_write_limit from public, anon, authenticated, service_role; grant select, insert, update, delete on public.widget_write_limit to service_role; create or replace function public.widget_allow_write(visitor text) returns boolean language plpgsql security invoker set search_path = '' as $$ declare minute_now bigint := floor(extract(epoch from statement_timestamp()) / 60); accepted integer; begin delete from public.widget_write_limit where visitor_hash in ( select visitor_hash from public.widget_write_limit where minute_bucket < minute_now - 1440 order by minute_bucket limit 50 for update skip locked ); insert into public.widget_write_limit as existing (visitor_hash, minute_bucket, uses) values (visitor, minute_now, 1) on conflict (visitor_hash) do update set minute_bucket = excluded.minute_bucket, uses = case when existing.minute_bucket < excluded.minute_bucket then 1 else existing.uses + 1 end where existing.minute_bucket < excluded.minute_bucket or (existing.minute_bucket = excluded.minute_bucket and existing.uses < 6) returning uses into accepted; return accepted is not null; end; $$; revoke all on function public.widget_allow_write(text) from public, anon, authenticated, service_role; grant execute on function public.widget_allow_write(text) to service_role; commit;