Questions about ZF-Oauth "official" database schema

Question

I have a couple of questions about zf-oauth (Apigility), and php-oauth server database schema.

The database schema is:

CREATE TABLE oauth_clients (
    client_id VARCHAR(80) NOT NULL,
    client_secret VARCHAR(80) NOT NULL,
    redirect_uri VARCHAR(2000) NOT NULL,
    grant_types VARCHAR(80),
    scope VARCHAR(2000),
    user_id VARCHAR(255),
    CONSTRAINT clients_client_id_pk PRIMARY KEY (client_id)
);
CREATE TABLE oauth_access_tokens (
    access_token VARCHAR(40) NOT NULL,
    client_id VARCHAR(80) NOT NULL,
    user_id VARCHAR(255),
    expires TIMESTAMP NOT NULL,
    scope VARCHAR(2000),
    CONSTRAINT access_token_pk PRIMARY KEY (access_token)
);
CREATE TABLE oauth_authorization_codes (
    authorization_code VARCHAR(40) NOT NULL,
    client_id VARCHAR(80) NOT NULL,
    user_id VARCHAR(255),
    redirect_uri VARCHAR(2000),
    expires TIMESTAMP NOT NULL,
    scope VARCHAR(2000),
    id_token VARCHAR(2000),
    CONSTRAINT auth_code_pk PRIMARY KEY (authorization_code)
);
CREATE TABLE oauth_refresh_tokens (
    refresh_token VARCHAR(40) NOT NULL,
    client_id VARCHAR(80) NOT NULL,
    user_id VARCHAR(255),
    expires TIMESTAMP NOT NULL,
    scope VARCHAR(2000),
    CONSTRAINT refresh_token_pk PRIMARY KEY (refresh_token)
);
CREATE TABLE oauth_users (
    username VARCHAR(255) NOT NULL,
    password VARCHAR(2000),
    first_name VARCHAR(255),
    last_name VARCHAR(255),
    CONSTRAINT username_pk PRIMARY KEY (username)
);
CREATE TABLE oauth_scopes (
    type VARCHAR(255) NOT NULL DEFAULT "supported",
    scope VARCHAR(2000),
    client_id VARCHAR (80),
    is_default SMALLINT DEFAULT NULL
);
CREATE TABLE oauth_jwt (
    client_id VARCHAR(80) NOT NULL,
    subject VARCHAR(80),
    public_key VARCHAR(2000),
    CONSTRAINT jwt_client_id_pk PRIMARY KEY (client_id)
);

You can find it at:

https://github.com/zfcampus/zf-oauth2

https://bshaffer.github.io/oauth2-server-php-docs/

https://apigility.org/documentation/modules/zf-oauth2

So, this schema seems really "standard" because is adopted by Zend Technologies (zend framework and apigility), but I have some questions:

1. Why aren't any "ordinary" primary keys? (int auto increment field).
   I would to use oauth_users table for registration account and the form's fields are "email" and "password". I could store email into oauth_users.username field, but It does not seem a good idea to use this field as a foreign key. Finally, my url looks better in profile-url/[:id] than profile-url/[:username/] (email in my case). So, I have to create a custom primary key or I could go against problems?

2. Why aren't any foreign keys? this questions is related to the first. I have to add foreign keys or I could go against problems? using oauth_users.username as foreign key for my other tables, It does not seem a good idea.

Answer 1

As you can see in the composer json require list ZF-Oauth2 is built on top of bshaffer/oauth2-server-php for which you can find the original documentation here. It has some nice explanations on the working of OAuth2. It is also the same repository that @Adam is referring to.

You can definitely customize these OAuth modules to a certain extend (like use your own table names). To do this you can introduce a custom OAuthAdapter with your own logic. There are also other modules available that do exactly that, like for example this zf-oauth2-doctrine module that is made for using OAuth2 with Doctrine 2.

That one comes with the following database diagram
(source: https://github.com/API-Skeletons/zf-oauth2-doctrine):