add new schema

This still needs some cleanup, but oh well. The most important thing is,
that it works for now.

1 files changed, 84 insertions, 0 deletions

diff --git a/schema/20181210.sql b/schema/20181210.sql
new file mode 100644
index 0000000..757055d
--- /dev/null
+++ b/schema/20181210.sql
@@ -0,0 +1,84 @@
+CREATE TABLE public.notifier (
+    id serial NOT NULL primary key,
+    name text NOT NULL
+);
+
+CREATE TABLE public.groups (
+    id serial NOT NULL,
+    name text NOT NULL
+);
+
+CREATE TABLE public.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 public.nodes_groups (
+    node_id bigint NOT NULL,
+    group_id integer NOT NULL,
+    unique(node_id, group_id)
+);
+
+CREATE TABLE public.commands (
+    id serial NOT NULL primary key,
+    name text NOT NULL unique,
+    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 public.checks (
+    id bigserial NOT NULL primary key,
+    node_id integer not null references nodes(id) on delete cascade,
+    command_id integer not null references commands(id) on delete restrict,
+    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 DEFAULT true NOT NULL,
+    notifier_id integer NOT NULL,
+    notify boolean DEFAULT true NOT NULL,
+    message text NOT NULL,
+    unique(node_id, command_id, options)
+);
+
+CREATE TABLE public.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,
+    msg text NOT NULL,
+    acknowledged boolean DEFAULT false NOT NULL
+);
+
+CREATE TABLE public.notifications (
+    id bigserial NOT NULL primary key,
+    check_id bigint NOT NULL 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 INDEX ON public.active_checks USING btree (check_id);
+
+CREATE INDEX ON public.active_checks USING btree (next_time) WHERE enabled;
+
+CREATE INDEX ON public.checks USING btree (command_id);
+
+CREATE INDEX ON public.checks USING btree (node_id);
+
+CREATE INDEX ON public.checks USING btree (updated, last_refresh NULLS FIRST);
+
+CREATE INDEX ON public.notifications USING btree (check_id, inserted DESC);
+
+CREATE INDEX ON public.notifications USING btree (inserted) WHERE (sent IS NULL);