schema.sql

CREATE TABLE IF NOT EXISTS credentials (
  server TEXT NOT NULL,
  username TEXT NOT NULL,
  password TEXT NOT NULL
);

CREATE TABLE IF NOT EXISTS authentication (
  id TEXT PRIMARY KEY NOT NULL,
  data TEXT
);

CREATE TABLE IF NOT EXISTS schedules (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  subject TEXT NOT NULL,
  body TEXT NOT NULL,
  cron TEXT NOT NULL,
  group_id TEXT NOT NULL,
  is_running BOOLEAN DEFAULT FALSE,
  include_graphs BOOLEAN DEFAULT FALSE,
  paused BOOLEAN DEFAULT FALSE,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (group_id) REFERENCES groups(id) ON DELETE NO ACTION
);

CREATE TABLE IF NOT EXISTS dashboards (
  id TEXT PRIMARY KEY NOT NULL,
  display_name TEXT NOT NULL
);

CREATE TABLE IF NOT EXISTS schedules_dashboards (
  schedule_id INTEGER NOT NULL,
  dashboard_id TEXT NOT NULL,
  FOREIGN KEY (schedule_id) REFERENCES schedules(id) ON DELETE CASCADE,
  FOREIGN KEY (dashboard_id) REFERENCES dashboards(id) ON DELETE NO ACTION
);

CREATE TABLE IF NOT EXISTS groups (
  id TEXT PRIMARY KEY NOT NULL,
  display_name TEXT NOT NULL
);

CREATE TABLE IF NOT EXISTS schedule_log (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  schedule_id INTEGER,
  last_run_duration INTEGER,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (schedule_id) REFERENCES schedule(id) ON DELETE CASCADE
);