github.com/10ego/gthp@v0.0.0-20241025155251-e1514fa71fbb/internal/database/schema/0001_user.sql (about)

     1  -- +goose up
     2  -- +goose statementbegin
     3  create type staff_status as enum (
     4  	'Active',
     5  	'On Assignment',
     6  	'Leave of Absence',
     7  	'Inactive'
     8  );
     9  create type org_type as enum (
    10  	'Division',
    11  	'Bureau',
    12  	'Directorate',
    13  	'Branch'
    14  );
    15  -- recursively organized hierarchical list
    16  create table if not exists organizations (
    17  	id char(40) primary key default concat('org_', gen_random_uuid()),
    18  	name text not null,
    19  	type org_type not null,	
    20  	parent_id char(40), -- self references organization(id), 
    21  	unique(name, parent_id)
    22  );
    23  
    24  create table if not exists users (
    25  	id char(41) primary key default concat('user_', gen_random_uuid()),
    26  	uid text not null,
    27  	name text not null,
    28  	job_title text not null,
    29  	office text not null,
    30  	organizations text not null references organizations(id),
    31  	status staff_status not null,
    32  	telephone text,
    33  	unique(uid)
    34  );
    35  -- +goose statementend