Skip to content

Clean up schema for table users

Per https://gitlab.com/gitlab-com/infrastructure/issues/1709 (private to GitLab employees):

  • Wrong data type: admin_email_unsubscribed_at should be timestamptz
  • Wrong data type: otp_grace_period_started_at should be timestamptz
  • Wrong data type: current_sign_in_ip should be inet or ip4r
  • Wrong data type: last_sign_in_ip should be inet or ip4r
  • Duplicate data: email, unconfirmed_email, notification_email, public_email end up being the same often- better as another table which doesn't repeat the email address
  • Duplicate data: state could be enum or 4-byte integer, save 5MB
  • Time-limited data: reset_password_token, authentication_token, confirmation_token, unlock_token, incoming_email_token. The columns, presumably, are only valid for a certain lifetime, better to have them in another table
  • Latest sign-in info should go into a seperate table
  • Index on low-cardinality column: state
  • Index on boolean column: admin
  • Index on boolean column: ghost
  • Index on boolean column: support_bot
  • Multiple indexes on one column: name (btree, gin)
  • Multiple indexes on one column: username (btree, gin)
  • Missing constriant: UNIQUE lower(email) (would obsolete older UNIQUE(email) constraint also)
  • Missing constraint: created_by_id -> users
  • Wrong data type: otp_backup_codes should be a text array (or bytea array)