backend/schema.sql


1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23

CREATE TABLE users (
    user_id      SERIAL      PRIMARY KEY,
    username     VARCHAR(64) NOT NULL UNIQUE,
    display_name VARCHAR(64) NOT NULL,
    icon_path    VARCHAR(255),
    is_admin     BOOLEAN     NOT NULL,
    created_at   TIMESTAMP   NOT NULL DEFAULT NOW()
);

CREATE TABLE user_auths (
    user_auth_id  SERIAL      PRIMARY KEY,
    user_id       INT         NOT NULL,
    auth_type     VARCHAR(16) NOT NULL,
    password_hash VARCHAR(256),
    CONSTRAINT fk_user_id FOREIGN KEY(user_id) REFERENCES users(user_id)
);

CREATE TABLE games (
    game_id    SERIAL       PRIMARY KEY,
    type       VARCHAR(255) NOT NULL,
    created_at TIMESTAMP    NOT NULL DEFAULT NOW(),
    state      VARCHAR(255) NOT NULL
);