php-auth/db/migrations/000001_create_users_table.up.sql

create table clients
(
    id             VARCHAR(26) not null
        constraint client_pk
            primary key,
    client_id      varchar     not null
        constraint client_client_id_key
            unique,
    client_secret  varchar     not null,
    name           varchar     not null,
    description    varchar              default '',
    private_key    text        not null,
    encryption_key text        not null,
    grant_types    jsonb       not null default '[]'::jsonb,
    capabilities   jsonb       not null default '[]'::jsonb,
    confidential   boolean     not null default true,
    created_at     timestamp            default now(),
    updated_at     timestamp            default now()
);

create table client_redirect_uris
(
    id           VARCHAR(26) not null
        constraint client_redirect_uris_pk
            primary key,
    client_id    VARCHAR(26) not null
        constraint client_redirect_uris_client_id_fk
            references clients
            on delete cascade,
    redirect_uri varchar     not null
);

create table scopes
(
    id          VARCHAR(26) not null
        constraint scopes_pk
            primary key,
    name        varchar     not null
        constraint scopes_name_key
            unique,
    description varchar     not null,
    created_at  timestamp   default now(),
    updated_at  timestamp   default now()
);

create table client_scopes
(
    id        VARCHAR(26) not null
        constraint client_scopes_pk
            primary key,
    client_id VARCHAR(26) not null
        constraint client_scopes_client_id_fk
            references clients
            on delete cascade,
    scope_id  VARCHAR(26) not null
        constraint client_scopes_scope_id_fk
            references scopes
            on delete cascade,
    constraint client_scopes_client_id_scope_id_key
        unique (client_id, scope_id)
);

create table users
(
    id         VARCHAR(26) not null
        constraint users_pk
            primary key,
    first_name varchar     not null,
    last_name  varchar     not null,
    email      varchar     not null
        constraint users_email_key
            unique,
    password   varchar     not null,
    created_at timestamp default now(),
    updated_at timestamp default now()
);

create table client_users
(
    id        VARCHAR(26) not null
        constraint client_users_pk
            primary key,
    client_id VARCHAR(26) not null
        constraint client_users_client_id_fk
            references clients
            on delete cascade,
    user_id   VARCHAR(26) not null
        constraint client_users_user_id_fk
            references users
            on delete cascade,
    constraint client_users_client_id_user_id_key
        unique (client_id, user_id)
);

create table user_scopes
(
    id       VARCHAR(26) not null
        constraint user_scopes_pk
            primary key,
    user_id  VARCHAR(26) not null
        constraint user_scopes_user_id_fk
            references users
            on delete cascade,
    scope_id VARCHAR(26) not null
        constraint user_scopes_scope_id_fk
            references scopes
            on delete cascade,
    client_id VARCHAR(26) not null
        constraint user_scopes_client_id_fk
            references clients
            on delete cascade,
    constraint user_scopes_user_id_scope_id_key
        unique (user_id, scope_id, client_id)
);