ms-auth/migrations/20240608163806_initial.sql

-- +goose Up
-- +goose StatementBegin
CREATE TABLE IF NOT EXISTS users
(
    id         serial             NOT NULL,
    username   VARCHAR(70) UNIQUE NOT NULL,
    hashed_pwd VARCHAR(60)        NOT NULL,
    email      VARCHAR(70) UNIQUE,
    role       INT                NOT NULL DEFAULT 0,
    expires_at TIMESTAMPTZ        NOT NULL,
    created_at TIMESTAMPTZ        NOT NULL DEFAULT NOW(),
    updated_at TIMESTAMPTZ        NOT NULL DEFAULT NOW(),

    PRIMARY KEY (id),
    CHECK (length(username) != 0 AND username = lower(username) AND username = trim(username)),
    CHECK (length(email) != 0 AND email = lower(email) AND email = trim(email)),
    CHECK (lower(username) != lower(email)),
    CHECK (length(hashed_pwd) != 0),
    CHECK (role BETWEEN 0 AND 3)
);

CREATE INDEX ON users (id);
CREATE INDEX ON users (username);
CREATE INDEX ON users (email);

CREATE FUNCTION usr_upd_trg_fn() RETURNS TRIGGER
    LANGUAGE plpgsql AS
$$
BEGIN
    NEW.updated_at = NOW();
    RETURN NEW;
END;
$$;

CREATE TRIGGER usr_upd_trg
    BEFORE UPDATE
    ON users
    FOR EACH ROW
EXECUTE PROCEDURE usr_upd_trg_fn();
-- +goose StatementEnd

-- +goose Down
DROP TABLE IF EXISTS users;