48 lines
1.7 KiB
SQL
48 lines
1.7 KiB
SQL
CREATE TYPE public.vm_status AS ENUM (
|
|
'provisioning',
|
|
'running',
|
|
'stopped',
|
|
'suspended',
|
|
'failed',
|
|
'deleting',
|
|
'deleted'
|
|
);
|
|
|
|
CREATE TABLE public.vms (
|
|
id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
|
|
project_id uuid NOT NULL REFERENCES public.projects(id) ON DELETE CASCADE,
|
|
cluster_id uuid NOT NULL REFERENCES public.clusters(id) ON DELETE RESTRICT,
|
|
proxmox_vmid integer NOT NULL,
|
|
node text NOT NULL,
|
|
name text NOT NULL,
|
|
status public.vm_status NOT NULL DEFAULT 'provisioning',
|
|
vcpu integer NOT NULL,
|
|
ram_mb integer NOT NULL,
|
|
disk_gb integer NOT NULL,
|
|
created_at timestamptz NOT NULL DEFAULT now(),
|
|
updated_at timestamptz NOT NULL DEFAULT now(),
|
|
CONSTRAINT vms_proxmox_vmid_check CHECK (proxmox_vmid > 0),
|
|
CONSTRAINT vms_node_not_blank_check CHECK (btrim(node) <> ''),
|
|
CONSTRAINT vms_name_not_blank_check CHECK (btrim(name) <> ''),
|
|
CONSTRAINT vms_vcpu_check CHECK (vcpu > 0),
|
|
CONSTRAINT vms_ram_mb_check CHECK (ram_mb > 0),
|
|
CONSTRAINT vms_disk_gb_check CHECK (disk_gb > 0)
|
|
);
|
|
|
|
CREATE UNIQUE INDEX vms_cluster_id_proxmox_vmid_key ON public.vms (cluster_id, proxmox_vmid);
|
|
CREATE INDEX vms_project_id_idx ON public.vms (project_id);
|
|
CREATE INDEX vms_cluster_id_idx ON public.vms (cluster_id);
|
|
CREATE INDEX vms_status_idx ON public.vms (status);
|
|
|
|
CREATE TABLE public.ssh_keys (
|
|
id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
|
|
tenant_id uuid NOT NULL REFERENCES public.tenants(id) ON DELETE CASCADE,
|
|
name text NOT NULL,
|
|
public_key text NOT NULL,
|
|
created_at timestamptz NOT NULL DEFAULT now(),
|
|
CONSTRAINT ssh_keys_name_not_blank_check CHECK (btrim(name) <> ''),
|
|
CONSTRAINT ssh_keys_public_key_not_blank_check CHECK (btrim(public_key) <> '')
|
|
);
|
|
|
|
CREATE INDEX ssh_keys_tenant_id_idx ON public.ssh_keys (tenant_id);
|