create table app.accounts (
    uid bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY NOT NULL,
    id uuid UNIQUE NOT NULL,

    CONSTRAINT fk_user FOREIGN KEY(id) REFERENCES auth.users(id) ON DELETE CASCADE
);

-- Enable RLS on the table
ALTER TABLE app.accounts ENABLE ROW LEVEL SECURITY;

-- Deny all direct access
CREATE POLICY "Deny All Access" ON app.accounts
  FOR ALL
  USING (false);

-- Automatically create a new account when a user is added to auth table.
create or replace function auto_add_account()
returns trigger
language plpgsql
security definer set search_path = ''
as $$
begin
    insert into app.accounts (id) values (new.id);
    return new;
end;
$$;

create trigger on_auth_user_created
    after insert on auth.users
    for each row execute procedure auto_add_account();