kener/migrations/20250111153517_init.ts

import type { Knex } from "knex";

export async function up(knex: Knex): Promise<void> {
  if (!(await knex.schema.hasTable("monitoring_data"))) {
    await knex.schema.createTable("monitoring_data", (table) => {
      table.string("monitor_tag", 255).notNullable();
      table.integer("timestamp").notNullable();
      table.text("status");
      table.float("latency", 8, 2);
      table.text("type");
      table.primary(["monitor_tag", "timestamp"]);
    });
  }

  if (!(await knex.schema.hasTable("monitor_alerts"))) {
    await knex.schema.createTable("monitor_alerts", (table) => {
      table.increments("id").primary();
      table.string("monitor_tag", 255).notNullable();
      table.string("monitor_status", 255).notNullable();
      table.string("alert_status", 255).notNullable();
      table.integer("health_checks").notNullable();
      table.integer("incident_number").defaultTo(0);
      table.timestamp("created_at").defaultTo(knex.fn.now());
      table.timestamp("updated_at").defaultTo(knex.fn.now());
    });
  }

  // Add index (IF NOT EXISTS not supported by all DBs, so use try/catch)
  try {
    await knex.schema.raw("CREATE INDEX idx_monitor_tag_created_at ON monitor_alerts (monitor_tag, created_at)");
  } catch (_e) {
    // Index already exists
  }

  if (!(await knex.schema.hasTable("site_data"))) {
    await knex.schema.createTable("site_data", (table) => {
      table.increments("id").primary();
      table.string("key", 255).notNullable().unique();
      table.text("value").notNullable();
      table.string("data_type", 255).notNullable();
      table.timestamp("created_at").defaultTo(knex.fn.now());
      table.timestamp("updated_at").defaultTo(knex.fn.now());
    });
  }

  if (!(await knex.schema.hasTable("monitors"))) {
    await knex.schema.createTable("monitors", (table) => {
      table.increments("id").primary();
      table.string("tag", 255).notNullable().unique();
      table.string("name", 255).notNullable().unique();
      table.text("description");
      table.text("image");
      table.string("cron", 255);
      table.string("default_status", 255);
      table.string("status", 255);
      table.string("category_name", 255);
      table.string("monitor_type", 255);
      table.string("down_trigger", 255);
      table.string("degraded_trigger", 255);
      table.text("type_data");
      table.integer("day_degraded_minimum_count");
      table.integer("day_down_minimum_count");
      table.string("include_degraded_in_downtime", 255).defaultTo("NO");
      table.timestamp("created_at").defaultTo(knex.fn.now());
      table.timestamp("updated_at").defaultTo(knex.fn.now());
    });
  }

  if (!(await knex.schema.hasTable("triggers"))) {
    await knex.schema.createTable("triggers", (table) => {
      table.increments("id").primary();
      table.string("name", 255).notNullable().unique();
      table.string("trigger_type", 255);
      table.text("trigger_desc");
      table.string("trigger_status", 255);
      table.text("trigger_meta");
      table.timestamp("created_at").defaultTo(knex.fn.now());
      table.timestamp("updated_at").defaultTo(knex.fn.now());
    });
  }

  if (!(await knex.schema.hasTable("users"))) {
    await knex.schema.createTable("users", (table) => {
      table.increments("id").primary();
      table.string("email", 255).notNullable().unique();
      table.string("name", 255).notNullable();
      table.string("password_hash", 255).notNullable();
      table.integer("is_active").defaultTo(1);
      table.integer("is_verified").defaultTo(0);
      table.string("role", 255).defaultTo("user");
      table.timestamp("created_at").defaultTo(knex.fn.now());
      table.timestamp("updated_at").defaultTo(knex.fn.now());
    });
  }

  if (!(await knex.schema.hasTable("api_keys"))) {
    await knex.schema.createTable("api_keys", (table) => {
      table.increments("id").primary();
      table.string("name", 255).notNullable().unique();
      table.string("hashed_key", 255).notNullable().unique();
      table.string("masked_key", 255).notNullable();
      table.string("status", 255).defaultTo("ACTIVE");
      table.timestamp("created_at").defaultTo(knex.fn.now());
      table.timestamp("updated_at").defaultTo(knex.fn.now());
    });
  }

  if (!(await knex.schema.hasTable("incidents"))) {
    await knex.schema.createTable("incidents", (table) => {
      table.increments("id").primary();
      table.string("title", 255).notNullable();
      table.integer("start_date_time").notNullable();
      table.integer("end_date_time");
      table.timestamp("created_at").defaultTo(knex.fn.now());
      table.timestamp("updated_at").defaultTo(knex.fn.now());
      table.string("status", 255).defaultTo("ACTIVE");
      table.string("state", 255).defaultTo("INVESTIGATING");
    });
  }

  if (!(await knex.schema.hasTable("incident_monitors"))) {
    await knex.schema.createTable("incident_monitors", (table) => {
      table.increments("id").primary();
      table.string("monitor_tag", 255).notNullable();
      table.string("monitor_impact", 255);
      table.timestamp("created_at").defaultTo(knex.fn.now());
      table.timestamp("updated_at").defaultTo(knex.fn.now());
      table.integer("incident_id").notNullable();
      table.unique(["monitor_tag", "incident_id"]);
    });
  }

  if (!(await knex.schema.hasTable("incident_comments"))) {
    await knex.schema.createTable("incident_comments", (table) => {
      table.increments("id").primary();
      table.text("comment").notNullable();
      table.integer("incident_id").notNullable();
      table.integer("commented_at").notNullable();
      table.timestamp("created_at").defaultTo(knex.fn.now());
      table.timestamp("updated_at").defaultTo(knex.fn.now());
      table.string("status", 255).defaultTo("ACTIVE");
      table.string("state", 255).defaultTo("INVESTIGATING");
    });
  }
}

export async function down(knex: Knex): Promise<void> {
  await knex.schema
    .dropTableIfExists("monitor_alerts")
    .dropTableIfExists("monitoring_data")
    .dropTableIfExists("site_data")
    .dropTableIfExists("monitors")
    .dropTableIfExists("triggers")
    .dropTableIfExists("users")
    .dropTableIfExists("api_keys")
    .dropTableIfExists("incidents")
    .dropTableIfExists("incident_monitors")
    .dropTableIfExists("incident_comments");
}