Skip to content

Introduce a central users table #6347

Open

Description

@mnonnenmacher

Turn the existing user_display_names table into a central users table that the other tables can reference. This step needs no Keycloak calls and is a prerequisite for the sync Task.

Database (Flyway)

  • Rename user_display_names to users. Keep user_id (the JWT sub claim) as the primary key. Update the foreign key from ort_runs.user_id.
  • Remove the UNIQUE constraint on users.username and add a non-unique index on it instead.
    • This fixes a bug: the upsert in UserDisplayNameDao.insertOrUpdate only handles conflicts on user_id. If user A is renamed or deleted and a new user B gets A's old name, creating a run as B fails with a unique constraint violation.
  • Add the columns:

Application changes

  • Rename UserDisplayNamesTable / UserDisplayNameDao to UsersTable / UserDao, and the UserDisplayName model class to match. The API model UserDisplayName stays as it is, to keep the API compatible.
  • Upsert the authenticated user (ID, username, full name) into users when they log in, so the table stays up to date between syncs. Use a cache (for example per user ID with a TTL) so this doesn't cause a database write on every request.
  • Upserting a user clears deleted_at. If the user can log in, they exist.
  • KeycloakUserService.deleteUser: set deleted_at for the deleted user.
  • Never delete rows from users. Runs and events reference them for auditing.

Acceptance criteria

  • A user who logs in appears in users with their current username and full name, even if they have never triggered a run.
  • Triggering a run as a user whose name was previously used by another user works.
  • Deleting a user through the API sets deleted_at and keeps the row.

Metadata

Metadata

Assignees

No one assigned

    Labels

    backendIssues related to the backend.

    Projects

    No projects

      Milestone

      No milestone

      Relationships

      None yet

      Development

      No branches or pull requests

      Issue actions