How to manage users on backend when using Auth0 Lock?

How to identify users

You're not explicit about which database technology you use, but in general you should be able to use regular strings as identifiers/keys. You do mention that you're using SQL variant so that may be the source of the issue; you should probably use a more specific text-based data type with a fixed length enough.

The user_id is the result of concatenating the Auth0 identity provider identifier with the user identifier within that provider so we could argue that reaching a definitive max length is a little trickier. However, you can decide on arbitrary value, for example, something like 640 character ought to be enough for anyone.

You can also identify your users by email; this works if every authentication provider being used by your application requires users to provide their email and you also don't intend to support different accounts with the same email address.

A final alternative is for you to assign each user your own unique identifier that is better suited for how you intend to use it. You can achieve this by having an Auth0 rule update your user metadata with this new attribute and then request this attribute to be included in the generated token upon user authentication by the means of scopes.

Depending on the approach you would neither need a simple lookup table mapping one form of identifier to your internal one or in the case you update the user metadata with your internal identifier you could skip that lookup table entirely and just the value coming from the JWT.

How to handle first-time users

Like you mentioned, you could at each API request make sure that if this is the first request issued by a new user then you create your notion of application profile before processing the request.

The alternative to this would be triggering this application profile creation from within Auth0 when you detect that the user signup for the first time and then on the API always assume the profile exists.

Both approaches are valid; I would go with the one that would leave you with a simpler implementation and still meets your requirements.

How to handle users being banned

If you do need to support the ability to immediately ban a user and don't allow any other request to the API then you'll always have to have some kind of query at each API request to see if the user was banned or not. This increases the complexity significantly so do consider that you can tolerate a solution where the lifetime of a token is shorter and banned users may still call your API within that short time frame.