Backend DB schema

The backend DB is built in SQLLite3 and its mission is to keep all the clusters metadata. It is split in different tables. Let's see them

Table list

• cluster: Stores al the information about each cluster added in the workspace

• node_group: [Not in use] Stores node groups for each cluster

• instance: This table has all the instance details that doesn't change that often

• instance_status: Instance metadata that changes frequently in time.

• promotion_ledger: Keep track of each failover situtaion and stores the coordinates.

• proxy_listener: Proxy information for teh cluster's endpoints.

• auth: [Not in use] User's credentials per workspace

• instance_metric: Some basic metrics per instance.

Table definitions

cluster

create table IF NOT EXISTS cluster (
    name text primary key,
    created_at datetime DEFAULT (datetime('now','localtime')),
    huser text, 
    hpass text,
    maint_mode int default 0,
    promotion_rule text check(promotion_rule IN ('synced', 'available')) default ('synced'),
    proxy_max_allowed_lag int default (0)
);

node_group

create table IF NOT EXISTS node_group (
    id integer primary key AUTOINCREMENT,
    cluster_id int,
    name text,
    created_at datetime DEFAULT (datetime('now','localtime'))
);

instance

create table IF NOT EXISTS instance (
    id integer primary key AUTOINCREMENT,
    node_group_id int,
    name text,
    hostname text,
    port int default (3306),
    version text,
    uuid text,
	arch text check(arch IN ('on-prem', 'rds', 'aurora')) default ('on-prem'),
	binlog_retention int,
    access_level text check(access_level IN ('w', 'r', 'rw', 'na')) default ('rw'),
	role text check(role IN ('primary', 'replica', 'broken-replica', 'unknown', 'int-primary', 'backup')) default ('unknown')    
);

instance_status

create table IF NOT EXISTS instance_status (    
    instance_id integer primary key,
	reachable int,
	io_thread_running text,
	sql_thread_running text,
	io_thread_error text,
	sql_thread_error text,
    lag_sec int,	
	binlog_file text,
	binlog_pos int,
	io_thread_errorno int, 
	sql_thread_errorno int,
    failover_coord json,    
	promotable int default (0),
    gtid_coord text,
    replication_mode check(replication_mode IN ('gtid', 'binlog')) default ('binlog'),
    proxy_status check(proxy_status IN ('up', 'down')) default ('down')
);

promotion_ledger

create table IF NOT EXISTS promotion_ledger (
    id integer primary key AUTOINCREMENT,
    origin_instance_id int,
    primary_id int,
    coord_set text 
);

proxy_listener

create table IF NOT EXISTS proxy_listener (   
    port integer primary key, 
    ng_id int,    
    name text    
);

auth

create table IF NOT EXISTS auth (
    id integer primary key AUTOINCREMENT,
    name text,    
    email text,
    pass text,
    company text,
    role text,
	token text,  
    first_access int default (1), 
    mfa int default (0)
);

instance_metric

create table IF NOT EXISTS instance_metric (
   id integer primary key AUTOINCREMENT,
   instance_id int,
   thread_connected int,
   thread_running int
);