init.sql

CREATE EXTENSION IF NOT EXISTS "uuid-ossp";

/* Table 'users' */
CREATE TABLE IF NOT EXISTS users(
    id uuid DEFAULT uuid_generate_v4() NOT NULL,
    username VARCHAR(255) NOT NULL unique,
    PRIMARY KEY (id)
);

CREATE TABLE IF NOT EXISTS machines(
    id uuid DEFAULT uuid_generate_v4() NOT NULL,
    user_id uuid NOT NULL,
    name VARCHAR(255) NOT NULL,
    public_key BYTEA not null,
    encapsulation_key BYTEA,
    PRIMARY KEY (id),
    FOREIGN KEY (user_id) REFERENCES users(id),
    unique (user_id, name)
);

CREATE TABLE IF NOT EXISTS ssh_configs(
    id uuid DEFAULT uuid_generate_v4() NOT NULL,
    user_id uuid NOT NULL,
    host VARCHAR(255) NOT NULL,
    values JSONB NOT NULL,
    identity_files JSONB,
    PRIMARY KEY (id),
    unique (user_id, host)
);

CREATE TABLE IF NOT EXISTS ssh_keys(
    id uuid DEFAULT uuid_generate_v4() NOT NULL,
    user_id uuid NOT NULL,
    filename VARCHAR(255) NOT NULL,
    data BYTEA NOT NULL,
    updated_at TIMESTAMP WITH TIME ZONE,
    PRIMARY KEY (id),
    FOREIGN KEY (user_id) REFERENCES users(id),
    unique (user_id, filename)
);

CREATE TABLE IF NOT EXISTS known_hosts(
    id uuid DEFAULT uuid_generate_v4() NOT NULL,
    user_id uuid NOT NULL,
    host_pattern VARCHAR(1024) NOT NULL,
    key_type VARCHAR(255) NOT NULL,
    key_data TEXT NOT NULL,
    marker VARCHAR(64) NOT NULL DEFAULT '',
    PRIMARY KEY (id),
    FOREIGN KEY (user_id) REFERENCES users(id),
    UNIQUE (user_id, host_pattern, key_type)
);

CREATE TABLE IF NOT EXISTS master_key_rotations(
    id uuid DEFAULT uuid_generate_v4() NOT NULL PRIMARY KEY,
    machine_id uuid NOT NULL REFERENCES machines(id) ON DELETE CASCADE,
    encrypted_master_key BYTEA NOT NULL,
    created_at TIMESTAMP WITH TIME ZONE DEFAULT (now() AT TIME ZONE 'UTC'),
    UNIQUE (machine_id)
);