Database
The following diagram visualises the WindAutomat database schema. The three core components of the data are tasks, machines, and deployments.
Tasks
The tasks table functions as a message queue for scheduling backup and update tasks. On updates to the tasks table, the postgres notify function is executed adding a message to the queue to which the WindAutomat task listener is subscribed.
Machines
Machines represent baremetal or virtual machines managed by the WindAutomat. All machines have a related operating system which dictates which module to load for the backup and update tasks. There are additional tables for virtual machines containing the hypervisor and the virtual machine details required to perform backup and restore operations.
Deployments
Deployments combine an application with a machine. This separation is required as applications often have different update processes as the operating systems they are running on. There are a number of configuration tables containing application specific configuration variables such as api keys and urls, and other important factors to be considered in the update process.
Diagram
---
title: WindAutomat Schema
---
erDiagram
APPLICATION {
id UUID "DEFAULT gen_random_uuid ()"
name VARCHAR(80)
version_available VARCHAR(80)
config_table VARCHAR(32) "REFERENCES config_tables(name)"
includes_os_upgrade BOOLEAN "DEFAULT false"
module_name VARCHAR(128)
}
CONFIG_TABLE {
name VARCHAR(32)
}
OPERATING_SYSTEM {
id UUID "DEFAULT gen_random_uuid ()"
name VARCHAR(36) "NOT NULL"
version_available VARCHAR(36)
module_name VARCHAR(128)
}
DEPLOYMENT {
id UUID "DEFAULT gen_random_uuid ()"
application_id UUID "REFERENCES applications(id)"
version_installed VARCHAR(32)
auto_update BOOLEAN "DEFAULT false"
machine_id UUID "REFERENCES machines(id)"
application_update_available BOOLEAN
}
CONFIG_OPNSENSE {
id UUID "DEFAULT gen_random_uuid ()"
deployment_id UUID "REFERENCES deployments(id)"
url VARCHAR(128)
api_key__bitwarden_secret_id UUID
api_secret__bitwarden_secret_id UUID
}
DEPLOYMENT ||--|O CONFIG_OPNSENSE: "has config"
CONFIG_UNIVENTION {
id UUID "DEFAULT gen_random_uuid ()"
deployment_id varchar(36)
is_primary boolean
}
DEPLOYMENT ||--|O CONFIG_UNIVENTION: "has config"
APPLICATION ||--|| CONFIG_TABLE: "Has config schema"
TASK }O--|| MACHINE: "Runs on"
TASK }O--O| DEPLOYMENT: "Belongs to"
APPLICATION ||--O{ DEPLOYMENT: "is deployed in"
OPERATING_SYSTEM ||--O{ MACHINE: "Has Base OS"
HYPERVISOR {
id UUID "DEFAULT gen_random_uuid ()"
FQDN VARCHAR(128) "NOT NULL"
node_name VARCHAR(128)
pve_token_id VARCHAR(64)
pve_secret__bitwarden_secret_id UUID
pve_backup_storage VARCHAR(128)
pve_storage_pool VARCHAR(128)
}
MACHINE {
id UUID "DEFAULT gen_random_uuid (),"
type machine_type "baremetal | virtual"
operating_system_id UUID "REFERENCES operating_systems(id)"
FQDN VARCHAR(128)
ssh_private_key__bitwarden_secret_id UUID
operating_system_update_available BOOLEAN
}
VIRTUAL_MACHINE_DETAILS {
id INTEGER "proxmox vm id"
hypervisor_id UUID "REFERENCES hypervisors(id)"
machine_id UUID "REFERENCES machines(id)"
}
MACHINE ||--|| VIRTUAL_MACHINE_DETAILS: "Is"
TMUX_SESSION }O--|| MACHINE: "On"
VIRTUAL_MACHINE_DETAILS }O--|| HYPERVISOR: "On"
DEPLOYMENT }O--|| MACHINE: "on"
TMUX_SESSION {
id UUID
machine_id UUID "References machines(id)"
active BOOLEAN
}
TASK {
id UUID
needs_task UUID "REFERENCES tasks(id)"
machine_id UUID "REFERENCES machines(id)"
deployment_id UUID "REFERENCES deployments(id)"
options JSONB "{application_id, operating_system_id, tmux_session_id}"
status status "scheduled|running|success|failed"
scheduled_at TIMESTAMP_WITH_TIME_ZONE "DEFAULT now()"
finished_at TIMESTAMP_WITH_TIME_ZONE
}
TASK ||--|| TASK: "Needs previous task"
MIGRATION {
id number
}