-- Records who posted each note / comment, set automatically from the JWT on
-- insert (can't be spoofed by the client).

alter table notes    add column if not exists author_id uuid;
alter table notes    add column if not exists author_email text;
alter table comments add column if not exists author_id uuid;
alter table comments add column if not exists author_email text;

create or replace function set_author()
returns trigger
language plpgsql
security definer
set search_path = public
as $$
begin
  new.author_id := auth.uid();
  new.author_email := (select email from auth.users where id = auth.uid());
  return new;
end;
$$;

drop trigger if exists notes_set_author on notes;
create trigger notes_set_author
  before insert on notes
  for each row execute function set_author();

drop trigger if exists comments_set_author on comments;
create trigger comments_set_author
  before insert on comments
  for each row execute function set_author();
