-- Chatroom only. Run once in your own Supabase SQL Editor. -- It can coexist with guestbook.sql; shared rate-limit objects are safe to recreate. begin; create table if not exists public.widget_chat ( 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 240), created_at timestamptz not null default now() ); alter table public.widget_chat add column if not exists is_private boolean not null default false; alter table public.widget_chat add column if not exists contact text check (char_length(contact) <= 250); create index if not exists widget_chat_recent on public.widget_chat (created_at desc, id desc); alter table public.widget_chat enable row level security; revoke all on public.widget_chat from public, anon, authenticated, service_role; grant select (id,name,message,created_at,is_private) on public.widget_chat to service_role; grant insert (name,message,contact,is_private) on public.widget_chat 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;