UUID type for hydra_client primary key

[grantzvolsky]

Is your feature request related to a problem? Please describe.

Currently, the primary key of the hydra_client table is an integer sequence. Since we don't benefit from the guarantees that a sequence provides, it would be better to just use UUID, to reduce insert complexity[1] and avoid other potential issues[2].

Additional context

The hydra_client table has a public ID (id) and an internal ID (pk, sequential primary key). Other tables reference the public id. The primary key is not used in foreign keys, so it can be changed with minimal disruption in cockroachdb, mysql, and postgres. sqlite requires the table to be recreated.

This change will involve generating new primary keys in application code rather than in the DB because we would like to minimize the work done by the database and it is the standard practice. The migration, however, requires that we generate UUIDs for existing rows, which could be tricky with sqlite or even postgres without an extension.

Describe the solution you'd like

Change hydra_client primary key to UUID while making it possible to revert the change and without imposing any new configuration requirements such as the postgres UUID extension. It is not required for this migration to be online.

Describe alternatives you've considered

Keep using an integer sequence for the hydra_client primary key.

[1] https://www.cockroachlabs.com/docs/stable/create-sequence.html#considerations
[2] #2765

[aeneasr]

👍