DB Schema

[AdaephonBen]

-- *************** SqlDBM: PostgreSQL ****************;
-- ***************************************************;

-- ************************************** "public"."Team"

CREATE TABLE "public"."Team"
(
 "team_id"              int generated by default as identity NOT NULL,
 "name"                 text NOT NULL,
 "questions_accessible" smallint[] NOT NULL,
 "max_level"            smallint NOT NULL,
 CONSTRAINT "PK_Teams" PRIMARY KEY ( "team_id" ),
 CONSTRAINT "Teams_Name_Unique" UNIQUE ( "name" )
);

-- ************************************** "public"."Statistics"

CREATE TABLE "public"."Statistics"
(

);

-- ************************************** "public"."Question"

CREATE TABLE "public"."Question"
(
 "question_id"     smallint generated by default as identity NOT NULL,
 "question_number" smallint NOT NULL,
 "question"        jsonb NOT NULL,
 "question_type"   smallint NOT NULL,
 "answer"          char(60) NOT NULL,
 CONSTRAINT "PK_Level" PRIMARY KEY ( "question_id" )
);

-- ************************************** "public"."User"

CREATE TABLE "public"."User"
(
 "user_id"       int generated by default as identity NOT NULL,
 "email_id"      varchar(255) NOT NULL,
 "username"      varchar(31) NOT NULL,
 "team_id"       int generated by default as identity NOT NULL,
 "last_modified" timestamp with time zone NOT NULL,
 CONSTRAINT "PK_User" PRIMARY KEY ( "user_id" ),
 CONSTRAINT "User_EmailID_Unique" UNIQUE ( "email_id" ),
 CONSTRAINT "User_Username_Unique" UNIQUE ( "username" ),
 CONSTRAINT "FK_18" FOREIGN KEY ( "team_id" ) REFERENCES "public"."Team" ( "team_id" )
);

CREATE INDEX "fkIdx_18" ON "public"."User"
(
 "team_id"
);

-- ************************************** "public"."Hint"

CREATE TABLE "public"."Hint"
(
 "hint_id"      smallint generated by default as identity NOT NULL,
 "hint_number"  smallint NOT NULL,
 "release_time" timestamp with time zone NOT NULL,
 "question_id"  smallint generated by default as identity NOT NULL,
 "hint"         text NOT NULL,
 CONSTRAINT "PK_Hints" PRIMARY KEY ( "hint_id" ),
 CONSTRAINT "FK_37" FOREIGN KEY ( "question_id" ) REFERENCES "public"."Question" ( "question_id" )
);

CREATE INDEX "fkIdx_37" ON "public"."Hint"
(
 "question_id"
);

Proposed DB Schema.

[AdaephonBen]

Revised DB Schema:

-- *************** SqlDBM: PostgreSQL ****************;
-- ***************************************************;

-- ************************************** "public"."Team"

CREATE TABLE "public"."Team"
(
 "id"             int generated by default as identity NOT NULL,
 "name"           text NOT NULL,
 "score"          smallint NOT NULL,
 "sent_invites"   jsonb NOT NULL,
 "latest_ans_mod" timestamp with time zone NOT NULL,
 "size"           smallint NOT NULL,
 CONSTRAINT "PK_Teams" PRIMARY KEY ( "id" ),
 CONSTRAINT "Teams_Name_Unique" UNIQUE ( "name" )
);

-- ************************************** "public"."Question"

CREATE TABLE "public"."Question"
(
 "id"              smallint generated by default as identity NOT NULL,
 "question_number" smallint NOT NULL,
 "question"        jsonb NOT NULL,
 "question_type"   smallint NOT NULL,
 "answer"          char(60) NOT NULL,
 "req_score"       smallint NOT NULL,
 CONSTRAINT "PK_Level" PRIMARY KEY ( "id" )
);

-- ************************************** "public"."User"

CREATE TABLE "public"."User"
(
 "id"              int generated by default as identity NOT NULL,
 "email_id"        varchar(255) NOT NULL,
 "username"        varchar(31) NOT NULL,
 "latest_team_mod" timestamp with time zone NOT NULL,
 "team_id"         int generated by default as identity NOT NULL,
 "pending_invites" jsonb NOT NULL,
 CONSTRAINT "PK_User" PRIMARY KEY ( "id" ),
 CONSTRAINT "User_EmailID_Unique" UNIQUE ( "email_id" ),
 CONSTRAINT "User_Username_Unique" UNIQUE ( "username" ),
 CONSTRAINT "FK_46" FOREIGN KEY ( "team_id" ) REFERENCES "public"."Team" ( "id" )
);

CREATE INDEX "fkIdx_46" ON "public"."User"
(
 "team_id"
);

-- ************************************** "public"."Hint"

CREATE TABLE "public"."Hint"
(
 "id"           smallint generated by default as identity NOT NULL,
 "hint_number"  smallint NOT NULL,
 "release_time" timestamp with time zone NOT NULL,
 "question_id"  smallint generated by default as identity NOT NULL,
 "hint"         text NOT NULL,
 CONSTRAINT "PK_Hints" PRIMARY KEY ( "id" ),
 CONSTRAINT "FK_37" FOREIGN KEY ( "question_id" ) REFERENCES "public"."Question" ( "id" )
);

CREATE INDEX "fkIdx_37" ON "public"."Hint"
(
 "question_id"
);

[AdaephonBen]

create table "User"
(
    email_id             varchar(255)              not null,
    password             text                      not null,
    recover_selector     text                      not null,
    recover_verifier     text                      not null,
    recover_token_expiry timestamp with time zone  not null,
    oauth2_uid           text                      not null,
    oauth2_provider      text                      not null,
    oauth2_access_token  text                      not null,
    oauth2_refresh_token text                      not null,
    oauth2_expiry        timestamp with time zone  not null,
    username             varchar(31)               not null,
    latest_team_mod      timestamp with time zone,
    pending_invites      jsonb default '[]'::jsonb not null,
    id                   integer generated by default as identity,
    team_id              integer                   not null
);

alter table "User"
    owner to dbmasteruser;

create table "Question"
(
    id              smallint generated by default as identity (maxvalue 32767)
        constraint question_pk
            primary key,
    question_number smallint not null,
    question        jsonb    not null,
    question_type   smallint not null,
    answer          char(60) not null,
    req_score       smallint not null
);

alter table "Question"
    owner to dbmasteruser;

create table "Hint"
(
    id           smallint generated by default as identity (maxvalue 32767)
        constraint hint_pk
            primary key,
    number       smallint                 not null,
    release_time timestamp with time zone not null,
    question_id  smallint                 not null
        constraint hint_question_id_fk
            references "Question",
    hint         text                     not null
);

alter table "Hint"
    owner to dbmasteruser;

create table "Team"
(
    id             integer generated by default as identity,
    name           text                         not null,
    score          smallint default 0           not null,
    sent_invites   jsonb    default '[]'::jsonb not null,
    latest_ans_mod timestamp with time zone     not null,
    size           smallint default 1           not null,
    is_deleted     boolean  default false
);

alter table "Team"
    owner to dbmasteruser;

create index team_score_index
    on "Team" (score desc);

create index team_score_id_name_latest_ans_mod_sent_invites_size_is_deleted_
    on "Team" (score desc, id asc, name asc, latest_ans_mod asc, sent_invites asc, size asc, is_deleted asc);

[AdaephonBen]

create table "Question"
(
    id              smallint generated by default as identity (maxvalue 32767)
        constraint question_pk
            primary key,
    question_number smallint not null,
    question        jsonb    not null,
    question_type   smallint not null,
    answer          char(60) not null,
    req_score       smallint not null
);

alter table "Question"
    owner to postgres;

create table "Hint"
(
    id           smallint generated by default as identity (maxvalue 32767)
        constraint hint_pk
            primary key,
    number       smallint                 not null,
    release_time timestamp with time zone not null,
    question_id  smallint                 not null
        constraint hint_question_id_fk
            references "Question",
    hint         text                     not null
);

alter table "Hint"
    owner to postgres;

create table "Team"
(
    id             integer generated by default as identity
        constraint team_pk
            primary key,
    name           text                     not null,
    score          smallint default 0       not null,
    latest_ans_mod timestamp with time zone not null,
    size           smallint default 1       not null,
    is_deleted     boolean  default false
);

alter table "Team"
    owner to postgres;

create table "User"
(
    email_id             varchar(255)              not null,
    password             text                      not null,
    recover_selector     text                      not null,
    recover_verifier     text                      not null,
    recover_token_expiry timestamp with time zone  not null,
    oauth2_uid           text                      not null,
    oauth2_provider      text                      not null,
    oauth2_access_token  text                      not null,
    oauth2_refresh_token text                      not null,
    oauth2_expiry        timestamp with time zone  not null,
    username             varchar(31)               not null,
    latest_team_mod      timestamp with time zone,
    pending_invites      jsonb default '[]'::jsonb not null,
    id                   integer generated by default as identity
        constraint user_pk
            primary key,
    team_id              integer                   not null
        constraint user_team_id_fk
            references "Team"
);

alter table "User"
    owner to postgres;

create unique index user_id_uindex
    on "User" (id);

create index team_score_index
    on "Team" (score desc);

create unique index team_id_uindex
    on "Team" (id);

create table "Invite"
(
    id      integer generated by default as identity
        constraint invite_pk
            primary key,
    team_id integer                                not null
        constraint invite_team_id_fk
            references "Team",
    user_id integer                                not null
        constraint invite_user_id_fk
            references "User",
    sent_at timestamp with time zone default now() not null
);

alter table "Invite"
    owner to postgres;

create unique index invite_id_uindex
    on "Invite" (id);