initial commit

1 files changed, 78 insertions, 0 deletions

diff --git a/schema/20181116.sql b/schema/20181116.sql
new file mode 100644
index 0000000..cb97acf
--- /dev/null
+++ b/schema/20181116.sql
@@ -0,0 +1,78 @@
+BEGIN;
+CREATE TABLE notifier (
+    id serial NOT NULL primary key,
+    name text NOT NULL
+);
+
+CREATE TABLE groups (
+    id serial NOT NULL primary key,
+    name text NOT NULL
+);
+
+CREATE TABLE nodes (
+    id bigserial NOT NULL primary key,
+    name text NOT NULL,
+    updated timestamp with time zone DEFAULT now() NOT NULL,
+    created timestamp with time zone DEFAULT now() NOT NULL,
+    message text NOT NULL
+);
+
+CREATE TABLE nodes_groups (
+    node_id bigint not null references nodes(id) on delete cascade,
+    group_id int not null references groups(id) on delete cascade,
+    unique(node_id, group_id)
+);
+
+CREATE TABLE commands (
+    id serial NOT NULL primary key,
+    name text NOT NULL,
+    command text NOT NULL,
+    updated timestamp with time zone DEFAULT now() NOT NULL,
+    created timestamp with time zone DEFAULT now() NOT NULL,
+    message text NOT NULL
+);
+
+CREATE TABLE checks (
+    id bigserial NOT NULL primary key,
+    node_id integer references nodes(id) on delete cascade,
+    command_id integer references commands(id) on delete cascade,
+    intval interval DEFAULT '00:05:00'::interval NOT NULL,
+    options jsonb DEFAULT '{}'::jsonb NOT NULL,
+    updated timestamp with time zone DEFAULT now() NOT NULL,
+    last_refresh timestamp with time zone,
+    enabled boolean not null DEFAULT true,
+    notifier_id integer NOT NULL,
+    notify boolean DEFAULT true NOT NULL,
+    message text NOT NULL,
+    unique(node_id, command_id)
+);
+
+CREATE TABLE notifications (
+    id bigserial NOT NULL primary key,
+    check_id bigint not null unique references checks(id) on delete cascade,
+    states integer[] not null,
+    output text,
+    inserted timestamp with time zone DEFAULT now() NOT NULL,
+    sent timestamp with time zone
+);
+
+CREATE TABLE active_checks (
+    check_id bigint NOT NULL references checks(id) on delete cascade,
+    cmdline text[] NOT NULL,
+    next_time timestamp with time zone DEFAULT now() NOT NULL,
+    states integer[] DEFAULT ARRAY[0] NOT NULL,
+    intval interval NOT NULL,
+    enabled boolean NOT NULL,
+    notify boolean NOT NULL,
+    notice text
+);
+
+CREATE INDEX ON active_checks(next_time) WHERE enabled;
+CREATE INDEX ON checks(updated, last_refresh nulls first);
+CREATE INDEX ON checks(node_id);
+CREATE INDEX ON checks(command_id);
+CREATE INDEX ON notifications(inserted) WHERE sent is null;
+CREATE INDEX ON notifications (check_id, inserted desc);
+CREATE INDEX ON commands(updated);
+CREATE INDEX ON nodes(updated);
+COMMIT;