gitfor/platform

-- Identity and account security: email verification, 2FA, passkeys, scoped tokens, notification
-- preferences, follows, avatars and rename/transfer redirects.
ALTER TABLE users ADD COLUMN email_verified_at INTEGER;
ALTER TABLE users ADD COLUMN totp_secret TEXT;
ALTER TABLE users ADD COLUMN totp_iv TEXT;
ALTER TABLE users ADD COLUMN totp_enabled_at INTEGER;
ALTER TABLE users ADD COLUMN totp_last_step INTEGER;
ALTER TABLE users ADD COLUMN sessions_revoked_at INTEGER;
ALTER TABLE accounts ADD COLUMN avatar_key TEXT;

CREATE TABLE recovery_codes (
  user_id TEXT NOT NULL REFERENCES accounts(id) ON DELETE CASCADE,
  code_hash TEXT NOT NULL,
  used_at INTEGER,
  PRIMARY KEY (user_id, code_hash)
);

CREATE TABLE passkeys (
  id TEXT PRIMARY KEY,
  user_id TEXT NOT NULL REFERENCES accounts(id) ON DELETE CASCADE,
  name TEXT NOT NULL,
  public_key TEXT NOT NULL,
  algorithm INTEGER NOT NULL,
  sign_count INTEGER NOT NULL DEFAULT 0,
  transports TEXT NOT NULL DEFAULT '[]',
  last_used_at INTEGER,
  created_at INTEGER NOT NULL
);
CREATE INDEX passkeys_user ON passkeys(user_id);

-- Fine-grained personal access tokens: NULL permissions means a classic (full-access) token.
ALTER TABLE access_tokens ADD COLUMN permissions TEXT;
ALTER TABLE access_tokens ADD COLUMN all_repos INTEGER NOT NULL DEFAULT 1;
CREATE TABLE access_token_repos (
  token_id TEXT NOT NULL REFERENCES access_tokens(id) ON DELETE CASCADE,
  repo_id TEXT NOT NULL REFERENCES repos(id) ON DELETE CASCADE,
  PRIMARY KEY (token_id, repo_id)
);
CREATE INDEX access_token_repos_repo ON access_token_repos(repo_id);