Design Initial Database Schema

[lschuermann]

Initial database schema proposed as part of #11.

[max-cura]

Current schema at https://github.com/treadmill-tb/treadmill/blob/main/switchboard/sql/SCHEMA.sql.

[max-cura]

After meeting today (attendence: @lschuermann, @charles37, and me), we arrived at the following schema:

Schema (PostgreSQL)

```sql -- -- Switchboard database schema. -- -- CORE STRUCTURE ---------------- ---------------- ---------------- ---------------- ---------------- ---------------- drop table if exists job_results; drop type if exists exit_status; drop table if exists job_parameters; drop table if exists jobs; drop table if exists images; drop type if exists restart_policy; -- drop table if exists maintenance_schedule; drop table if exists supervisors; drop table if exists api_token_privileges; drop table if exists user_privileges; drop table if exists api_tokens; drop type if exists api_token_cancellation; drop table if exists users; drop type if exists user_type; drop table if exists events; -- EVENT LOGGING create table events ( event_id uuid not null primary key, data jsonb not null ); -- BASE ENTITIES create type user_type as enum ('normal', 'system'); create table users ( user_id uuid not null primary key, name text not null unique, email text not null unique, password_hash text not null, user_type user_type not null, created_at timestamp with time zone not null, last_login_at timestamp with time zone not null, locked bool not null ); create type api_token_cancellation as ( canceled_at timestamp with time zone, cancellation_reason text ); create table api_tokens ( -- internal identifier of the token token_id uuid not null primary key, -- actual contents of the token; random nonce token bytea not null unique, -- the user who created this token user_id uuid not null references users on delete cascade, inherits_user_permissions bool not null, -- whether the token was revoked/invalidated/etc. or not canceled api_token_cancellation, -- expiration date created_at timestamp with time zone not null, expires_at timestamp with time zone not null ); -- PERMISSIONS create table user_privileges ( user_id uuid not null references users on delete cascade, permission text not null, unique (user_id, permission) ); create table api_token_privileges ( token_id uuid not null references api_tokens on delete cascade, permission text not null, unique (token_id, permission) ); -- SUPERVISORS create table supervisors ( supervisor_id uuid not null primary key, name text not null, last_connected_at timestamp with time zone not null, public_key text not null, tags text[] not null ); -- BUSINESS LOGIC ---------------- ---------------- ---------------- ---------------- ---------------- ---------------- create type restart_policy as ( restart_count integer ); create table images ( image_id uuid not null primary key ); create table jobs ( job_id uuid not null primary key, resume_job_id uuid references jobs on delete no action, image_id uuid references images (image_id) on delete no action, ssh_keys text[] not null, -- run_command text, restart_policy restart_policy not null, queued bool not null default true, enqueued_by_user_id uuid references users (user_id) on delete no action, enqueued_by_token_id uuid references api_tokens (token_id) on delete no action, check ( (enqueued_by_user_id is not null and enqueued_by_token_id is null) or (enqueued_by_user_id is null and enqueued_by_token_id is not null)), tag_config text not null, check ( (resume_job_id is null and image_id is not null) or (resume_job_id is not null and image_id is null)) ); create table job_parameters ( job_id uuid not null references jobs (job_id) on delete cascade, key text not null, value text not null, secret bool not null, primary key (job_id, key) ); create type exit_status as enum ( -- no supervisors in "supervisors" that can run this that you have permissions for 'failed_to_match', -- too long in queue 'queue_timeout', -- failed to launch this on a supervisor (may incur a retry) 'host_start_failure', -- e.g., supervisor timeout, image failed to fetch, -- host reported an error (i.e., through puppet) 'host_terminated_with_error', -- host reported success 'host_terminated_with_success', -- job timeout 'host_terminated_timeout' ); create table job_results ( job_id uuid not null references jobs (job_id), supervisor_id uuid not null references supervisors (supervisor_id) on delete no action, run_number integer not null, exit_status exit_status not null, host_output jsonb, enqueued_at timestamp with time zone not null, started_at timestamp with time zone not null, terminated_at timestamp with time zone not null ); ```

[lschuermann]

Closing as #17 is merged.