pacco/migrations/0000_users.sql

CREATE TYPE user_role AS ENUM ('regular', 'admin');
CREATE TYPE session_kind AS ENUM ('api', 'user');

CREATE TABLE IF NOT EXISTS users (
    username VARCHAR(255) NOT NULL PRIMARY KEY,
    "password" text NOT NULL,
    user_role user_role NOT NULL DEFAULT 'regular'
);

CREATE TABLE IF NOT EXISTS user_session (
    id UUID NOT NULL PRIMARY KEY DEFAULT gen_random_uuid(),
    token text NOT NULL,
    "user" varchar(255) NOT NULL,
    "created" timestamptz NOT NULL DEFAULT NOW(),
    "csrf" UUID NOT NULL DEFAULT gen_random_uuid(),
    "name" varchar(255),
    kind session_kind NOT NULL DEFAULT 'user',
    FOREIGN KEY("user") REFERENCES users(username)
);

CREATE TABLE IF NOT EXISTS user_profile_pic (
    username VARCHAR(255) NOT NULL PRIMARY KEY,
    "image" bytea NOT NULL
);