what2watch/migrations/20240116054222_users_and_invites.up.sql

create table if not exists users (
  id blob not null primary key default (julid_new()),
  username text not null unique,
  displayname text,
  email text,
  last_seen text,
  pwhash blob not null,
  invited_by blob not null,
  is_active boolean not null default true,
  last_updated text not null default CURRENT_TIMESTAMP,
  foreign key (invited_by) references users (id)
);
create index if not exists users_username_dex on users (lower(username));
create index if not exists users_email_dex on users (lower(email));
create index if not exists users_invited_by_dex on users (invited_by);

create trigger if not exists update_last_updated_users
  after update on users
  when OLD.last_updated = NEW.last_updated or OLD.last_updated is null
BEGIN
  update users set last_updated = CURRENT_TIMESTAMP where id=NEW.id;
END;

-- invitations
create table if not exists invites (
  id blob not null primary key default (julid_new()),
  owner blob not null,
  expires_at text,
  remaining int not null default 1,
  last_updated text not null default CURRENT_TIMESTAMP,
  foreign key (owner) references users (id) on delete cascade on update no action
);
create index if not exists invites_owner_dex on invites (owner);

create trigger if not exists update_last_updated_invites
  after update on invites
  when OLD.last_updated = NEW.last_updated or OLD.last_updated is null
BEGIN
  update invites set last_updated = CURRENT_TIMESTAMP where id=NEW.id;
END;