oauth postgres schema

schema.sql

SET statement_timeout = 0;
SET client_encoding = 'UTF8';
SET standard_conforming_strings = on;
SET check_function_bodies = false;
SET client_min_messages = warning;

CREATE EXTENSION IF NOT EXISTS plpgsql WITH SCHEMA pg_catalog;
COMMENT ON EXTENSION plpgsql IS 'PL/pgSQL procedural language';

CREATE EXTENSION IF NOT EXISTS "uuid-ossp";
COMMENT ON EXTENSION "uuid-ossp" IS 'UUID generation functions';

SET search_path = public, pg_catalog;
SET default_tablespace = '';
SET default_with_oids = false;

CREATE TABLE "user" (
  id uuid PRIMARY KEY DEFAULT uuid_generate_v1() NOT NULL,
  given_name text NOT NULL,
  surname text NOT NULL,
  email text NOT NULL,
  username text NOT NULL,
  roles text,
  createdAt timestamp(6) WITH TIME ZONE,
  updatedAt timestamp(6) WITH TIME ZONE,
  UNIQUE (email),
  UNIQUE (username)
);

CREATE TABLE user_credential (
  user_id uuid REFERENCES "user" (id) NOT NULL,
  password text NOT NULL,
  PRIMARY KEY (user_id, password)
);

CREATE TABLE oauth_client (
  id text NOT NULL,
  secret text NOT NULL,
  redirect_uri text NOT NULL,
  PRIMARY KEY (id, secret)
);

CREATE TABLE oauth_access_token (
  access_token text PRIMARY KEY NOT NULL,
  client_id text NOT NULL,
  user_id uuid REFERENCES "user" (id) NOT NULL,
  expires timestamp without time zone NOT NULL
);

CREATE TABLE oauth_refresh_token (
  refresh_token text PRIMARY KEY NOT NULL,
  client_id text NOT NULL,
  user_id uuid REFERENCES "user" (id) NOT NULL,
  expires timestamp without time zone NOT NULL
);