table_definitions.sql

-- all dates are unix epoch seconds

CREATE TABLE IF NOT EXISTS users
(
    id           uuid PRIMARY KEY DEFAULT gen_random_uuid() not null,
    display_name TEXT NOT NULL,
    -- todo: check if compliance requirements mean that we need to store first name and last name.
    email        TEXT NOT NULL,
    password_hash     TEXT NOT NULL,
    topt_secret       TEXT,
    is_admin       BOOLEAN NOT NULL DEFAULT FALSE,
    creation_date   bigint NOT NULL
);

CREATE TABLE IF NOT EXISTS sessions
(
    token TEXT NOT NULL PRIMARY KEY,
    user_id uuid NOT NULL references users(id),
    creation_time   bigint NOT NULL,
    expiration_time  bigint NOT NULL
);

CREATE TABLE IF NOT EXISTS projects (
    id             uuid PRIMARY KEY DEFAULT gen_random_uuid(),
    project_name   TEXT NOT NULL,
    owner_user_id       uuid NOT NULL references users(id),
    creation_date    bigint NOT NULL
);

CREATE TYPE project_permissions_enum AS ENUM ('admin', 'read_write', 'read');

CREATE TABLE IF NOT EXISTS project_permissions (
    project_id uuid NOT NULL references projects(id),
    user_id uuid NOT NULL references users(id),
    permissions project_permissions_enum NOT NULL
);

CREATE INDEX project_user_permissions_index ON project_permissions (project_id, user_id);

-- server discovery/state is done by a separate scheduler node, so the db doesnt need to store it. hit the scheduler if you need server info.

CREATE TYPE vm_isa as ENUM ('x86', 'arm', 'risc-v');

CREATE TABLE IF NOT EXISTS vm_types (
    id    uuid PRIMARY KEY DEFAULT gen_random_uuid(),
    name     TEXT NOT NULL,
    isa    vm_isa NOT NULL,
    cores    integer NOT NULL,
    ram_capacity   integer NOT NULL,
    storage_amount    integer NOT NULL, -- in gigabytes
    rate            numeric NOT NULL,
    cap              numeric NOT NULL
);

CREATE TABLE IF NOT EXISTS vms (
    id    uuid PRIMARY KEY DEFAULT gen_random_uuid(),
    active     BOOLEAN NOT NULL DEFAULT TRUE,
    owner_project_id     uuid references projects(id),
    vm_type_id   uuid NOT NULL references vm_types(id),
    creation_date        date NOT NULL
);

CREATE TABLE IF NOT EXISTS volumes (
    id    uuid PRIMARY KEY DEFAULT gen_random_uuid(),
    name    TEXT NOT NULL,
    size    integer NOT NULL, -- in gigabytes
    owner_project_id    uuid NOT NULL references projects(id),
    associated_vm_id     uuid -- null means its not associated with any vm
);

CREATE TABLE IF NOT EXISTS payment_periods (
    id      uuid PRIMARY KEY DEFAULT gen_random_uuid(),
    vm_id   uuid NOT NULL references vms(id),
    user_id   uuid NOT NULL references users(id),
    start_date    integer NOT NULL,
    end_date    integer, -- if null, the period is still active
    rate        numeric NOT NULL,
    cap         numeric NOT NULL
);

CREATE TABLE IF NOT EXISTS ip_blocks (
    id    uuid PRIMARY KEY DEFAULT gen_random_uuid(),
    ip_block    cidr NOT NULL
);

CREATE TABLE IF NOT EXISTS ip_assignments (
    ip   inet NOT NULL,
    ip_block_id uuid NOT NULL references ip_blocks(id),
    associated_vm_id uuid references vms(id)
);

CREATE TABLE IF NOT EXISTS ssh_keys (
    id    uuid PRIMARY KEY DEFAULT gen_random_uuid(),
    user_id  uuid NOT NULL references users(id),
    fingerprint  TEXT NOT NULL,
    public_key   TEXT NOT NULL,
    name         TEXT NOT NULL,
    description   TEXT
);

CREATE TABLE IF NOT EXISTS base_images (
    id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
    file_path TEXT NOT NULL,
    name TEXT NOT NULL,
    version TEXT NOT NULL,
    description TEXT NOT NULL,
    isa    vm_isa NOT NULL
);