Data Model

Clima uses a PostgreSQL relational database version 12.14. The following tables are included in the clima database.

analytics

Column	Type	Primary key	Foreign key
id	integer	✔️
name	character varying(100)
code	text
opt_out_code	text

auth_assignment

This table stores the history of the different statuses that each user had and the ones that currently poses (bronze, silver, gold etc).

Column	Type	Primary key	Foreign key
item_name	character varying(64)	✔️	✔️ (auth_assignment_item_name_fkey) references: auth_item (name)
user_id	integer	✔️	✔️ (auth_assignment_user_id_fkey) references: user (id)
created_at	integer

auth_item

Column	Type	Primary key	Foreign key
name	character varying(64)	✔️
type	integer
description	text
rule_name	character varying(64)		✔️ (auth_item_rule_name_fkey) references: auth_rule (name)
data	text
created_at	integer
updated_at	integer
group_code	character varying(64)		✔️ (fk_auth_item_group_code) references: auth_item_group (code)

auth_item_child

Column	Type	Primary key	Foreign key
parent	character varying(64)	✔️
child	character varying(64)	✔️

auth_item_group

Column	Type	Primary key	Foreign key
code	character varying(64)	✔️
name	character varying(255)
created_at	integer
updated_at	integer

auth_rule

Column	Type	Primary key	Foreign key
name	character varying(64)	✔️
data	text
created_at	integer
updated_at	character varying(100)

cold_storage_autoaccept

Similar tables: ondemand_autoaccept, service_autoaccept, jupyter_autoaccept.
In HYPATIA a user can create a vm with a specific upper storage limit, according to each user status. However it is possible that some users have higher storage demands. As a solution to the aforementioned problem HYPATIA provides individuals storage volumes that its users can create and attach to a specific vm. This table contains the upper limit of storage (in GBs) that are automatically accepted, according to each status category (bronze, silver and gold) and also the maximum number of storage volumes (autoaccept_number ) that a user is allowed to automatically create.

Column	Type	Primary key	Foreign key
storage	double precision
user_type	character varying(15)
id	integer	✔️
autoaccept_number	integer

cold_storage_limits

Similar tables: machine_compute_limits, ondemand_limits, service_limits, jupyter_limits.
This table stores the maximun number of the different storage volumes (number_of_projects) and the upper limit of storage (in GBs) that a user is able to create (both automatically and after review), according to the status category that the user belongs to (bronze, silver and gold).

Column	Type	Primary key	Foreign key
storage	bigint
user_type	character varying(15)
id	integer	✔️
duration currently not used	integer
number_of_projects	integer

cold_storage_request

Similar tables: machine_compute_request, ondemand_request, service_request, jupyter_request_n.
This table stores all submitted requests of a storage volume project. Such requests may include the creation of a new storage volume or every kind of modification that this storage undergoes (for example the modification of its description). Some fields of the table include the request_id , the project's description , the type of the requested storage (hot or cold), etc.

Column	Type	Primary key	Foreign key
id	bigint	✔️
request_id	bigint
storage	double precision
description	text
additional_resources	text
type	text
vm_type	integer
num_of_volumes	integer

configuration

Column	Type	Primary key	Foreign key
reviewer_num	integer
home_page	integer
privacy_page	integer
help_page	integer
schema_url	text

cron_job

This table contains the logs of cron jobs.

Column	Type	Primary key	Foreign key
id_cron_job	integer	✔️
controller	character varying(255)
action	character varying(255)
limit	integer
offset	integer
running	smallint
success	smallint
started_at	integer
ended_at	integer
last_execution_time	double precision

email

This table contains information about the email notifications which are sent to HYPATIA users, administrators and moderators. Such notifications may include emails about the submission of a project, information about its progress (whether the project is approved, rejected or modified) and emails about the imminent deletion of a project (30, 15, 5 and 1 days prior to deletion). Administrators and moderators are as well notified, whenever a new user is registered to HYPATIA and also receive information about newly created tickets.

Column	Type	Primary key	Foreign key
id	integer	✔️
recipient_ids`	integer[]
type	text
sent_at	timestamp without time zone
message	text
project_id	integer

email_events

Similar tables: email_events_admin, email_events_moderator, email_events_user.
Different users are notified for different events. This table contains information about which users are notified for every event category (user_creation, new_project, project_decision etc).

Column	Type	Primary key	Foreign key
id	integer	✔️
user_id	integer
user_creation	boolean
new_project	boolean
project_decision	boolean
new_ticket	boolean
expires_30	boolean
expires_15	boolean
edit_project	boolean
expires_1	boolean
expires_5	boolean

email_events_admin

Similar tables: email_events, email_events_moderator, email_events_user.
This table contains information only for the administartors of HYPATIA.

Column	Type	Primary key	Foreign key
id	integer	✔️
user_id	integer
user_creation	boolean
new_ticket	boolean
expires_1	boolean
expires_5	boolean

email_events_moderator

Similar tables: email_events, email_events_admin, email_events_user.
This table stores information only for the moderators of HYPATIA.

Column	Type	Primary key	Foreign key
id	integer	✔️
user_id	integer
new_project	boolean
project_decision	boolean
expires_30	boolean
expires_15	boolean
expires_1	boolean
expires_5	boolean

email_events_user

Similar tables: email_events, email_events_admin, email_events_moderator.
This table stores information only about the users of HYPATIA.

Column	Type	Primary key	Foreign key
id	integer	✔️
user_id	integer
project_decision	boolean
expires_30	boolean
expires_15	boolean
expires_1	boolean
expires_5	boolean

email_verification

This table stores information about the users who have been involved in the email verification process. The verification_token field stores the hash of the verification token that has been sent to the user's email , the expiry indicates the validity period of the token, the status of the token (pending, completed, revoked), etc.

Column	Type	Primary key	Foreign key
id	integer	✔️
verification_token	character varying(100)
user_id	integer
created_at	timestamp without time zone
email	character varying(128)
expiry	timestamp without time zone
status	smallint

hot_volumes

This table includes details about the hot storage volumes. Such information may include the name of the storage volume, the project_id and the vm type that the storage is mounted to, whether it is active or not, etc.

Column	Type	Primary key	Foreign key
id	integer	✔️
name	text
project_id	bigint
volume_id	text
mountpoint	text
accepted_at	timestamp without time zone
vm_type	integer
active	boolean
deleted_at	timestamp without time zone
deleted_by	text
vm_id	bigint
created_at	timestamp without time zone
mult_order	integer

jupyter_autoaccept

Similar tables: ondemand_autoaccept, service_autoaccept,cold_storage_autoaccept.
This table stores the upper limits of resources (cores, ram)and the maximum number of automatically accepted on-demand notebooks projects (autoaccept_number), that a user can request according to the status that they poses (bronze, silver or gold).

Column	Type	Primary key	Foreign key
cores	integer
ram	double precision
user_type	character varying(15)
id	integer	✔️
autoaccept_number	integer

jupyter_images

This table stores the available server types for on-demand notebooks projects. Currently HYPATIA supports 13 different jupyter images, such as: Base notebook, Minimal notebook, R notebook etc. This table stores the name of the server type (description), its path on dockerhub (image) and whether on not that image uses gpu (gpu)

Column	Type	Primary key	Foreign key
description	text
image	text
id	integer	✔️
gpu	boolean

jupyter_limits

Similar tables: cold_storage_limits, machine_compute_limits, service_limits, ondemand_limits.
This table stores the upper limits of resources (cores, ram, duration) and the maximum number of participants (participants), that a user can request according to their status (bronze, silver or gold) during the creation of an on-demand notebooks project, as well as the maximum number of on-demand notebooks projects (number_of_projects) that a user is allowed to create, both automatically and after review.

Column	Type	Primary key	Foreign key
cores	integer
ram	double precision
user_type	character varying(15)
id	integer	✔️
duration	integer
number_of_projects	integer
participants	integer

jupyter_request_n

Similar tables: cold_storage_request, machine_compute_request, service_request, ondemand_request.
This table stores all submitted requests of an on-demand notebooks project. Such requests may include the creation of a new project or every kind of modification that this project undergoes. This table includes the requested resources of the project (cores, ram), the selected jupyter server type (image), the number of the users participating at the project (participants_number), etc.

Column	Type	Primary key	Foreign key
id	bigint	✔️
request_id	bigint
description	text
containerized	boolean
cores	integer
ram	double precision
additional_resources	text
image	text
image_id	bigint
participant_view	text
participants_number	integer

jupyter_server

This table stores information about both, active and inactive jupyter servers that exist in HYPATIA platform. Such information includes: the project (project) and the user (created_by), that the server belongs to, whether is active or not (active), the url that the server can be accessed (url), etc.

Column	Type	Primary key	Foreign key
id	bigint	✔️
manifest	character varying(100)
image	text
project	character varying(100)
server_id	character varying(20)
created_at	timestamp without time zone
deleted_at	timestamp without time zone
created_by	text
deleted_by	text
project_end_date	timestamp without time zone
url	text
active	boolean
expires_on	timestamp without time zone
state	character varying(20)
image_id	bigint

machine_compute_limits

Similar tables: cold_storage_limits, ondemand_limits, service_limits, jupyter_limits.
This table stores the maximun number of on-demand computation machines projects a user is allowed to create, according to the status category that the user belongs to (bronze, silver and gold).

Column	Type	Primary key	Foreign key
id	integer	✔️
user_type	character varying(15)
number_of_projects	integer

machine_compute_request

Similar tables: cold_storage_request, ondemand_request, service_request, jupyter_request_n.
This table stores all submitted requests of an on-demand computation machines project. Such requests include the creation of a new project or every kind of modification that this project undergoes. Every request includes the description of the project, the requested resources (num_of_vms , num_of_cores , ram , storage ), etc.

Column	Type	Primary key	Foreign key
id	bigint	✔️
description	text
num_of_vms	smallint
num_of_cores	smallint
num_of_ips	smallint
ram	double precision
storage	double precision
request_id	bigint
vm_flavour	text
disk	integer
additional_resources	text

migration

This table stores a small description and the date of each database migration.

Column	Type	Primary key	Foreign key
version	character varying(180)	✔️
apply_time	integer

notification

This table stores every notification that the users receive through HYPATIA website. It stores notifications about projects' submission, creation amd modification. The fields of this table include the message which is displayed to a list of users (recipient_id), whether a meassage have been read (seen), the timestamp of the notification's creation (created_at), etc.

Column	Type	Primary key	Foreign key
id	integer	✔️
recipient_id	integer
message	text
seen	boolean
type	integer
created_at	timestamp without time zone
read_at	timestamp without time zone
url	text

ondemand_autoaccept

Similar tables: cold_storage_autoaccept, service_autoaccept, jupyter_autoaccept.
This table stores the upper limits of resources (num_of_jobs, cores, ram) and the maximum number of automatically accepted on-demand computation projects (autoaccept_number), that a user can request according to the status that they poses (bronze, silver or gold).

Column	Type	Primary key	Foreign key
num_of_jobs`	integer
cores	integer
ram	double precision
user_type	character varying(15)
id	integer	✔️
autoaccept_number	integer

ondemand_limits

Similar tables: cold_storage_limits, machine_compute_limits, service_limits, jupyter_limits.
This table stores the upper limits of resources (number_of_jobs, cores, ram, duration of each job), that a user can request according to their status (bronze, silver or gold) during the creation of an on-demand computation project, as well as the maximum number of on-demand computation projects (number_of_projects) that a user is allowed to create, both automatically and after review.

Column	Type	Primary key	Foreign key
number_of_jobs	integer
cores	integer
ram	double precision
user_type	character varying(15)
id	integer	✔️
duration	integer
number_of_projects	integer

ondemand_request

Similar tables: cold_storage_request, machine_compute_request, service_request, jupyter_request_n.
This table stores all submitted requests of an on-demand computation project. Such requests may include the creation of a new project or every kind of modification that this project undergoes. This table includes the requested resources of the project (number_of_jobs, cores, ram), whether the project is containerized, any additional_resources needed, etc.

Column	Type	Primary key	Foreign key
id	bigint	✔️
request_id	bigint
description	text
maturity	maturity_type
analysis_type	character varying(200)
containerized	boolean
number_of_jobs	integer
cores	integer
ram	double precision
additional_resources	text

openstack

This table contains information about openstack.

Column	Type	Primary key	Foreign key
nova_url	text
keystone_url	text
cinder_url	text
neutron_url	text
glance_url	text
tenant_id	text
floating_net_id	text
cred_id	text
cred_secret	text
id	integer	✔️
internal_net_id	text

openstack_machines

This table contains information about the openstack vms.

Column	Type	Primary key	Foreign key
nova_url	text
keystone_url	text
cinder_url	text
neutron_url	text
glance_url	text
tenant_id	text
floating_net_id	text
cred_id	text
cred_secret	text
id	integer	✔️
internal_net_id	text

pages

This table contains information about the 3 main pages of HYPATIA (home, privacy and help page).

Column	Type	Primary key	Foreign key
id	integer	✔️
title	text
content	text

project

This table stores information about each project. Some indicative fields of the table are the name of the project, its type (24/7 service, on-demand computation, storage volume, on-demand notebooks or on-demand computation machines) and its status (active, expired, deleted, etc). Another important field of the table is the latest_project_request_id that contains the latest project request id which is also stored, according to project's type, in one of the request tables(cold_storage_request, machine_compute_request, ondemand_request, jupyter_request_n or service_request)

Column	Type	Primary key	Foreign key
id	bigint	✔️
name	character varying(200)
status	smallint
latest_project_request_id	bigint
pending_request_id	bigint
updated_at	integer
project_type	smallint
favorite	boolean
start_date	timestamp without time zone

project_request

This table stores some additional information about each project, regardless its type.

Column	Type	Primary key	Foreign key
id	bigint	✔️
name	character varying(200)
duration	integer
user_num	integer
user_list	integer[]
backup_services	boolean
viewed	boolean
status	smallint
submitted_by	integer
submission_date	timestamp without time zone
assigned_to	bigint
project_type	smallint
project_id	bigint
approval_date	timestamp without time zone
approved_by	integer
deletion_date	timestamp without time zone
end_date	date
additional_resources	text
louros	boolean

service_autoaccept

Similar tables: cold_storage_autoaccept, ondemand_autoaccept, jupyter_autoaccept.
This table contains the upper limit of resources (vms , cores , storage , ram ) and the maximum number of 24/7 projects that are automatically accepted (autoaccept_number ), according to each status category (bronze, silver and gold).

Column	Type	Primary key	Foreign key
vms	integer
cores	integer
ips	integer
ram	double precision
storage	double precision
user_type	character varying(15)
id	integer	✔️
autoaccept_number	integer

service_limits

Similar tables: cold_storage_limits, machine_compute_limits, ondemand_limits.
This table stores the upper limits of resources (vms , cores , storage , ram , duration ), that a user can request according to their status (bronze, silver or gold) during the creation of a 24/7 project, as well as the maximum number of 24/7 projects (number_of_projects) that the user is allowed to create, both automatically and after review.

Column	Type	Primary key	Foreign key
vms	integer
cores	integer
ips	integer
ram	double precision
storage	double precision
user_type	character varying(15)
duration	integer
id	integer	✔️
number_of_projects	integer

service_request

Similar tables: cold_storage_request, machine_compute_request, ondemand_request, jupyter_request_n.
This table stores all submitted requests of a 24/7 project. Such requests may include the creation of a new project or every kind of modification that this project undergoes. This table includes the project's requested resources (num_of_vms , num_of_cores , storage , ram), any additional_resources needed, etc.

Column	Type	Primary key	Foreign key
id	bigint	✔️
name	character varying(200)
version	character varying(50)
description	text
url	text
num_of_vms	smallint
num_of_cores	smallint
ram	double precision
storage	double precision
request_id	bigint
trl	smallint
vm_flavour	text
disk	integer
additional_resources	text

smtp

Column	Type	Primary key	Foreign key
id	integer	✔️
encryption	text
host	text
username	text
port	text
password	text

ticket_body

Column	Type	Primary key	Foreign key
id	integer	✔️
id_head	integer
name_user	character varying(255)
text	text
client	integer
date	timestamp(0) without time zone

ticket_file

Column	Type	Primary key	Foreign key
id	integer	✔️
id_body	integer		✔️ references: ticket_body (id)
fileName	character varying(255)
document_name	character varying(255)

ticket_head

Column	Type	Primary key	Foreign key
id	integer	✔️
user_id	integer		✔️ references: user (id)
department	character varying(255)
topic	character varying(255)
status	integer
date_update	timestamp(0) without time zone
page	text

user

This table stores various information for all registered HYPATIA users, such as their name and surname, their username, whether they are superuser or not, their status (bronze, silver or gold), etc.

Column	Type	Primary key	Foreign key
id	integer	✔️
username	character varying(255)
auth_key	character varying(32)
password_hash	character varying(255)
confirmation_token	character varying(255)
status	character varying(255)
superadmin	smallint
created_at	integer
updated_at	integer
email_confirmed	smallint
name	character varying(100)
surname	character varying(100)

user_visit_log

This table stores the history of each user log in to HYPATIA.

Column	Type	Primary key	Foreign key
id	integer	✔️
token	character varying(255)
ip	character varying(15)
language	character (2)
user_agent	character varying(255)
user_id	integer		✔️ references: user (id)
visit_time	integer
browser	character varying(30)
os	character varying(20)

vm

Similar tables: vm_machines.
This table stores information about the vms that the users created at their 24/7 service projects. Some of the fields stored at this table are the name of the project related to the vm, the user that created the vm (created_by) and whether it is still active.

Column	Type	Primary key	Foreign key
id	bigint	✔️
ip_address	character varying(100)
ip_id	text
vm_id	text
public_key	text
image_id	text
image_name	character varying(100)
request_id	integer
active	boolean
keypair_name	character varying(255)
created_by	integer
deleted_by	integer
volume_id	text
created_at	timestamp without time zone
deleted_at	timestamp without time zone
do_not_delete_disk	boolean
windows_unique_id	text
read_win_password	boolean
project_id	bigint
name	character varying(200)

vm_machines

Similar tables: vm.
This table stores information about the vms that the users created at their ondemand machines projects. Some of the fields stored at this table are the name of the project related to the vm, the user that created the vm (created_by) and whether it is still active.

Column	Type	Primary key	Foreign key
id	bigint	✔️
ip_address	character varying(100)
ip_id	text
vm_id	text
public_key	text
image_id	text
image_name	character varying(100)
request_id	integer
active	boolean
keypair_name	character varying(255)
created_by	integer
deleted_by	integer
volume_id	text
created_at	timestamp without time zone
deleted_at	timestamp without time zone
do_not_delete_disk	boolean
windows_unique_id	text
read_win_password	boolean
project_id	bigint
name	character varying(200)