d1 init

Signed-off-by: corvofeng <corvofeng@gmail.com>

config.yaml

@@ -1,5 +1,5 @@
 settings:
-  title: 'Status Page'
+  title: 'Status Page powered by Workers and D1'
   url: 'https://status-page.eidam.dev' # used for Slack messages
   logo: logo-192x192.png # image in ./public/ folder
   daysInHistogram: 90 # number of days you want to display in histogram
@@ -13,27 +13,3 @@ settings:
   dayInHistogramNoData: 'No data'
   dayInHistogramOperational: 'All good'
   dayInHistogramNotOperational: ' incident(s)' # xx incident(s) recorded
-
-monitors:
-  - id: workers-cloudflare-com # unique identifier
-    name: workers.cloudflare.com
-    description: 'You write code. They handle the rest.' # default=empty
-    url: 'https://workers.cloudflare.com/' # URL to fetch
-    method: GET # default=GET
-    expectStatus: 200 # operational status, default=200
-    followRedirect: false # should fetch follow redirects, default=false
-    linkable: false # allows the title to be a link, default=true
-
-  - id: www-cloudflare-com
-    name: www.cloudflare.com
-    description: 'Built for anything connected to the Internet.'
-    url: 'https://www.cloudflare.com'
-    method: GET
-    expectStatus: 200
-    linkable: true # allows the title to be a link, default=true
-
-  - id: blog-cloudflare-com
-    name: The Cloudflare Blog
-    url: 'https://blog.cloudflare.com/'
-    method: GET
-    expectStatus: 200

flareact.config.js

@@ -5,7 +5,7 @@ module.exports = {
       type: 'json',
       use: 'yaml-loader',
     })
-
+    
     return config
   },
 }

package.json

@@ -9,22 +9,26 @@
     "dev": "flareact dev",
     "build": "yarn css && flareact build",
     "deploy": "yarn build && flareact publish",
-    "kv-gc": "node ./src/cli/gcMonitors.js",
     "format": "prettier --write '**/*.{js,css,json,md}'",
-    "css": "postcss public/tailwind.css -o public/style.css"
+    "css": "postcss public/tailwind.css -o public/style.css",
+    "postinstall": "patch-package"
   },
   "dependencies": {
-    "flareact": "^0.10.0",
+    "@cloudflare/d1": "^1.4.1",
+    "flareact": "^1.5.0",
     "laco": "^1.2.1",
     "laco-react": "^1.1.0",
     "react": "^17.0.1",
-    "react-dom": "^17.0.1"
+    "react-dom": "^17.0.1",
+    "wrangler": "^0.0.0-d35c69f"
   },
   "devDependencies": {
     "autoprefixer": "^10.0.2",
     "node-fetch": "^2.6.1",
+    "patch-package": "^6.4.7",
     "postcss": "^8.2.10",
     "postcss-cli": "^8.3.0",
+    "postinstall-postinstall": "^2.1.0",
     "prettier": "^2.2.0",
     "tailwindcss": "^2.0.1",
     "yaml-loader": "^0.6.0"

pages/api/triggerCron.js

@@ -2,5 +2,5 @@ import { processCronTrigger } from '../../src/functions/cronTrigger'
 
 export default async (event) => {
   // used only for local debugging
-  //return processCronTrigger(event)
+  return processCronTrigger(event)
 }

pages/index.js

@@ -2,45 +2,29 @@ import { Store } from 'laco'
 import { useStore } from 'laco-react'
 import Head from 'flareact/head'
 
-import { getKVMonitors, useKeyPress } from '../src/functions/helpers'
+import { loadData, useKeyPress } from '../src/functions/helpers'
 import config from '../config.yaml'
 import MonitorCard from '../src/components/monitorCard'
-import MonitorFilter from '../src/components/monitorFilter'
 import MonitorStatusHeader from '../src/components/monitorStatusHeader'
 import ThemeSwitcher from '../src/components/themeSwitcher'
 
-const MonitorStore = new Store({
-  monitors: config.monitors,
-  visible: config.monitors,
-  activeFilter: false,
-})
-
-const filterByTerm = (term) =>
-  MonitorStore.set((state) => ({
-    visible: state.monitors.filter((monitor) =>
-      monitor.name.toLowerCase().includes(term),
-    ),
-  }))
-
 export async function getEdgeProps() {
-  // get KV data
-  const kvMonitors = await getKVMonitors()
+  const { monitors, checks } = await loadData()
+
+  console.log(monitors, checks)
 
   return {
     props: {
       config,
-      kvMonitors: kvMonitors ? kvMonitors.monitors : {},
-      kvMonitorsLastUpdate: kvMonitors ? kvMonitors.lastUpdate : {},
+      monitors: monitors || {},
+      checks,
     },
     // Revalidate these props once every x seconds
     revalidate: 5,
   }
 }
 
-export default function Index({ config, kvMonitors, kvMonitorsLastUpdate }) {
-  const state = useStore(MonitorStore)
-  const slash = useKeyPress('/')
-
+export default function Index({ config, monitors, checks }) {
   return (
     <div className="min-h-screen">
       <Head>
@@ -75,16 +59,15 @@ export default function Index({ config, kvMonitors, kvMonitorsLastUpdate }) {
           </div>
           <div className="flex flex-row items-center">
             {typeof window !== 'undefined' && <ThemeSwitcher />}
-            <MonitorFilter active={slash} callback={filterByTerm} />
           </div>
         </div>
-        <MonitorStatusHeader kvMonitorsLastUpdate={kvMonitorsLastUpdate} />
-        {state.visible.map((monitor, key) => {
+        <MonitorStatusHeader monitors={monitors} />
+        {monitors.map((monitor, key) => {
           return (
             <MonitorCard
               key={key}
               monitor={monitor}
-              data={kvMonitors[monitor.id]}
+              checks={checks.filter(x => x.monitor_id === monitor.id )}
             />
           )
         })}

patches/flareact+1.5.0.patch

@@ -0,0 +1,101 @@
+diff --git a/node_modules/flareact/configs/webpack.worker.config.js b/node_modules/flareact/configs/webpack.worker.config.js
+index 5b9f088..3bfb62f 100644
+--- a/node_modules/flareact/configs/webpack.worker.config.js
++++ b/node_modules/flareact/configs/webpack.worker.config.js
+@@ -26,6 +26,10 @@ module.exports = function (env, argv) {
+     ...baseConfig({ dev, isServer }),
+     target: "webworker",
+     entry: path.resolve(projectDir, "./index"),
++    output: {
++      path: path.resolve(projectDir, "./worker"),
++      filename:'script.js'
++    }
+   };
+ 
+   config.plugins.push(
+diff --git a/node_modules/flareact/src/bin/flareact.js b/node_modules/flareact/src/bin/flareact.js
+index ed2ea89..bb53cf0 100755
+--- a/node_modules/flareact/src/bin/flareact.js
++++ b/node_modules/flareact/src/bin/flareact.js
+@@ -17,20 +17,22 @@ dotenv.config();
+ const yargs = require("yargs");
+ 
+ let rootPath = "";
+-let webpackConfigPath =
++let webpackClientConfigPath =
+   "node_modules/flareact/configs/webpack.client.config.js";
++let webpackWorkerConfigPath =
++  "node_modules/flareact/configs/webpack.worker.config.js";
+ 
+-let isWebpackConfigFound = () => fs.existsSync(rootPath + webpackConfigPath);
++let isWebpackClientConfigFound = () => fs.existsSync(rootPath + webpackClientConfigPath);
+ 
+ for (let i = 0; i < 3; i++) {
+-  if (isWebpackConfigFound()) {
++  if (isWebpackClientConfigFound()) {
+     break;
+   }
+   rootPath += "../";
+ }
+ 
+-if (isWebpackConfigFound()) {
+-  webpackConfigPath = rootPath + webpackConfigPath;
++if (isWebpackClientConfigFound()) {
++  webpackClientConfigPath = rootPath + webpackClientConfigPath;
+ } else {
+   const firstLine =
+     "⚠ Cannot find node_modules/flareact/configs/webpack.client.config.js.";
+@@ -73,13 +75,13 @@ if (argv._.includes("dev")) {
+   concurrently(
+     [
+       {
+-        command: "wrangler dev",
+-        name: "worker",
+-        env: { WORKER_DEV: true, IS_WORKER: true },
++        command: `webpack --config ${webpackWorkerConfigPath} --mode production && webpack --config ${webpackClientConfigPath} --mode production && npx wrangler@d1 dev --local`,
++        name: "build",
++        env: { NODE_ENV: "development", WORKER_DEV: true, IS_WORKER: true },
+       },
+       {
+-        command: `webpack-dev-server --config ${webpackConfigPath} --mode development --port ${argv.port}`,
+-        name: "client",
++        command: `webpack-dev-server --config ${webpackClientConfigPath} --mode development --port ${argv.port}`,
++        name: "dev-server",
+         env: { NODE_ENV: "development" },
+       },
+     ],
+@@ -101,7 +103,7 @@ if (argv._.includes("publish")) {
+ 
+   console.log(`Publishing your Flareact project to ${destination}...`);
+ 
+-  let wranglerPublish = `wrangler publish`;
++  let wranglerPublish = `npx wrangler@d1 publish`;
+ 
+   if (argv.env) {
+     wranglerPublish += ` --env ${argv.env}`;
+@@ -110,10 +112,10 @@ if (argv._.includes("publish")) {
+   concurrently(
+     [
+       {
+-        command: `webpack --config ${webpackConfigPath} --out ./out --mode production && ${wranglerPublish}`,
+-        name: "publish",
+-        env: { NODE_ENV: "production", IS_WORKER: true },
+-      },
++        command: `webpack --config ${webpackClientConfigPath} --mode production && webpack --config ${webpackWorkerConfigPath} --mode production && ${wranglerPublish}`,
++        name: "build",
++        env: { NODE_ENV: "production",IS_WORKER: true },
++      }
+     ],
+     {
+       prefix: "name",
+@@ -134,8 +136,8 @@ if (argv._.includes("build")) {
+   concurrently(
+     [
+       {
+-        command: `webpack --config ${webpackConfigPath} --out ./out --mode production`,
+-        name: "publish",
++        command: `webpack --config ${webpackWorkerConfigPath} --mode production && webpack --config ${webpackClientConfigPath} --mode production`,
++        name: "build",
+         env: { NODE_ENV: "production" },
+       },
+     ],

schema.sql

@@ -0,0 +1,38 @@
+/* npx wrangler@d1 d1 execute <database-name> --file=./schema.sql */
+
+CREATE TABLE IF NOT EXISTS monitors (
+    id INTEGER PRIMARY KEY AUTOINCREMENT,
+    name TEXT NOT NULL,
+    url TEXT NOT NULL,
+    operational INTEGER DEFAULT 0,
+    description TEXT DEFAULT NULL,
+    method TEXT DEFAULT "GET",
+    expect_status INTEGER DEFAULT 200,
+    follow_redirect INTEGER DEFAULT 1,
+    linkable INTEGER DEFAULT 0,
+    operational INTEGER DEFAULT 0,
+    last_updated TEXT DEFAULT CURRENT_TIMESTAMP
+);
+
+/*
+INSERT INTO monitors (name, url) 
+    VALUES 
+        ("workers.cloudflare.com", "https://www.cloudflare.com"),
+        ("www.cloudflare.com", "https://www.cloudflare.com"),
+        ("The Cloudflare Blog", "https://blog.cloudflare.com"),
+        ("Cloudflare community", "https://cloudflare.community");
+*/
+
+CREATE TABLE IF NOT EXISTS monitors_checks (
+    monitor_id INTEGER NOT NULL,
+    location TEXT NOT NULL,
+    res_ms INT NOT NULL,
+    operational INTEGER NOT NULL,
+    date TEXT DEFAULT CURRENT_DATE,
+    timestamp TEXT DEFAULT CURRENT_TIMESTAMP,
+    
+    CONSTRAINT fk_monitors
+        FOREIGN KEY (monitor_id)
+        REFERENCES monitors(id)
+        ON DELETE CASCADE
+);

src/components/monitorCard.js

@@ -1,6 +1,7 @@
 import config from '../../config.yaml'
 import MonitorStatusLabel from './monitorStatusLabel'
 import MonitorHistogram from './monitorHistogram'
+import { locations } from '../functions/locations'
 
 const infoIcon = (
   <svg
@@ -17,7 +18,9 @@ const infoIcon = (
   </svg>
 )
 
-export default function MonitorCard({ key, monitor, data }) {
+export default function MonitorCard({ key, monitor, checks }) {
+  console.log(checks)
+
   return (
     <div key={key} className="card">
       <div className="flex flex-row justify-between items-center mb-2">
@@ -30,7 +33,7 @@ export default function MonitorCard({ key, monitor, data }) {
               </div>
             </div>
           )}
-          {(monitor.linkable === true || monitor.linkable === undefined) ?
+          {monitor.linkable ?
             (
               <a href={monitor.url} target="_blank">
                 <div className="text-xl">{monitor.name}</div>
@@ -45,14 +48,14 @@ export default function MonitorCard({ key, monitor, data }) {
           }
 
         </div>
-        <MonitorStatusLabel kvMonitor={data} />
+        <MonitorStatusLabel monitor={monitor} />
       </div>
 
-      <MonitorHistogram monitorId={monitor.id} kvMonitor={data} />
+      {/*<MonitorHistogram monitorId={monitor.id} kvMonitor={data} />*/}
 
       <div className="flex flex-row justify-between items-center text-gray-400 text-sm">
-        <div>{config.settings.daysInHistogram} days ago</div>
-        <div>Today</div>
+        <div>&nbsp;</div>
+        <div>{checks.map(x => `${locations[x.location] || x.location}: ${Math.floor(x.avg_res)}ms avg - ${x.count} check(s)`).join(" | ")}</div>
       </div>
     </div>
   )

src/components/monitorDayAverage.js

@@ -1,4 +1,4 @@
-import { locations } from '../functions/helpers'
+import { locations } from '../functions/locations'
 
 export default function MonitorDayAverage({ location, avg }) {
   return (

src/components/monitorStatusHeader.js

@@ -1,5 +1,5 @@
 import config from '../../config.yaml'
-import { locations } from '../functions/helpers'
+import { locations } from '../functions/locations'
 
 const classes = {
   green:
@@ -8,25 +8,30 @@ const classes = {
     'bg-yellow-200 text-yellow-700 dark:bg-yellow-700 dark:text-yellow-200 border-yellow-300 dark:border-yellow-600',
 }
 
-export default function MonitorStatusHeader({ kvMonitorsLastUpdate }) {
+export default function MonitorStatusHeader({ monitors }) {
   let color = 'green'
   let text = config.settings.allmonitorsOperational
 
-  if (!kvMonitorsLastUpdate.allOperational) {
+  if (monitors.find(x => !x.operational)) {
     color = 'yellow'
     text = config.settings.notAllmonitorsOperational
   }
 
+  const updates = monitors.map(m => {
+    return new Date(m.last_updated).getTime();
+  });
+
+  const lastUpdated = Math.max(...updates);
+
   return (
     <div className={`card mb-4 font-semibold ${classes[color]}`}>
       <div className="flex flex-row justify-between items-center">
         <div>{text}</div>
-        {kvMonitorsLastUpdate.time && typeof window !== 'undefined' && (
+        {lastUpdated && typeof window !== 'undefined' && (
           <div className="text-xs font-light">
             checked{' '}
-            {Math.round((Date.now() - kvMonitorsLastUpdate.time) / 1000)} sec
-            ago (from{' '}
-            {locations[kvMonitorsLastUpdate.loc] || kvMonitorsLastUpdate.loc})
+            {Math.round((Date.now() - lastUpdated) / 1000)} sec
+            ago
           </div>
         )}
       </div>

src/components/monitorStatusLabel.js

@@ -7,12 +7,12 @@ const classes = {
     'bg-yellow-200 text-yellow-800 dark:bg-yellow-800 dark:text-yellow-200',
 }
 
-export default function MonitorStatusLabel({ kvMonitor }) {
+export default function MonitorStatusLabel({ monitor }) {
   let color = 'gray'
   let text = 'No data'
 
-  if (typeof kvMonitor !== 'undefined') {
-    if (kvMonitor.lastCheck.operational) {
+  if (typeof monitor !== 'undefined') {
+    if (monitor.operational) {
       color = 'green'
       text = config.settings.monitorLabelOperational
     } else {

src/d1.js

@@ -0,0 +1,122 @@
+export class Database {
+    binding;
+    constructor(binding) {
+        this.binding = binding;
+    }
+    prepare(query) {
+        return new PreparedStatement(this, query);
+    }
+    async dump() {
+        const response = await this.binding.fetch("/dump", {
+            method: "POST",
+            headers: {
+                "content-type": "application/json",
+            },
+        });
+        if (response.status !== 200) {
+            const err = (await response.json());
+            throw new Error("D1_DUMP_ERROR", {
+                cause: new Error(err.error),
+            });
+        }
+        return await response.arrayBuffer();
+    }
+    async batch(statements) {
+        const exec = await this._send("/query", statements.map((s) => s.statement), statements.map((s) => s.params));
+        return exec;
+    }
+    async exec(query) {
+        const lines = query.trim().split("\n");
+        const exec = await this._send("/query", lines, []);
+        const error = exec
+            .map((r) => {
+            return r.error ? 1 : 0;
+        })
+            .indexOf(1);
+        if (error !== -1) {
+            throw new Error("D1_EXEC_ERROR", {
+                cause: new Error(`Error in line ${error + 1}: ${lines[error]}: ${exec[error].error}`),
+            });
+        }
+        else {
+            return {
+                count: exec.length,
+                duration: exec.reduce((p, c) => {
+                    return p.duration + c.duration;
+                }),
+            };
+        }
+    }
+    async _send(endpoint, query, params) {
+        const body = JSON.stringify(typeof query == "object"
+            ? query.map((s, index) => {
+                return { sql: s, params: params[index] };
+            })
+            : {
+                sql: query,
+                params: params,
+            });
+        const response = await this.binding.fetch(endpoint, {
+            method: "POST",
+            headers: {
+                "content-type": "application/json",
+            },
+            body,
+        });
+        if (response.status !== 200) {
+            const err = (await response.json());
+            throw new Error("D1_ERROR", { cause: new Error(err.error) });
+        }
+        const answer = await response.json();
+        return Array.isArray(answer) ? answer : answer;
+    }
+}
+class PreparedStatement {
+    statement;
+    database;
+    params;
+    constructor(database, statement, values) {
+        this.database = database;
+        this.statement = statement;
+        this.params = values || [];
+    }
+    bind(...values) {
+        return new PreparedStatement(this.database, this.statement, values);
+    }
+    async first(colName) {
+        const info = await this.database._send("/query", this.statement, this.params);
+        const results = info.results;
+        if (results.length < 1) {
+            throw new Error("D1_NORESULTS", { cause: new Error("No results") });
+        }
+        const result = results[0];
+        if (colName !== undefined) {
+            if (result[colName] === undefined) {
+                throw new Error("D1_COLUMN_NOTFOUND", {
+                    cause: new Error(`Column not found`),
+                });
+            }
+            return result[colName];
+        }
+        else {
+            return result;
+        }
+    }
+    async run() {
+        return this.database._send("/execute", this.statement, this.params);
+    }
+    async all() {
+        return await this.database._send("/query", this.statement, this.params);
+    }
+    async raw() {
+        const s = await this.database._send("/query", this.statement, this.params);
+        const raw = [];
+        for (var r in s.results) {
+            const entry = Object.keys(s.results[r]).map((k) => {
+                return s.results[r][k];
+            });
+            raw.push(entry);
+        }
+        return raw;
+    }
+}

src/functions/cronTrigger.js

@@ -1,12 +1,9 @@
 import config from '../../config.yaml'
+import { Database } from '../d1'
 
 import {
-  notifySlack,
-  notifyTelegram,
   getCheckLocation,
-  getKVMonitors,
-  setKVMonitors,
-  notifyDiscord,
+  getMonitors,
 } from './helpers'
 
 function getDate() {
@@ -18,33 +15,21 @@ export async function processCronTrigger(event) {
   const checkLocation = await getCheckLocation()
   const checkDay = getDate()
 
-  // Get monitors state from KV
-  let monitorsState = await getKVMonitors()
+  const db = new Database(D1UNSAFE)
 
-  // Create empty state objects if not exists in KV storage yet
-  if (!monitorsState) {
-    monitorsState = { lastUpdate: {}, monitors: {} }
-  }
+  const monitors = await getMonitors()
+  let statements = []
+  const updateMonitorStatement = db.prepare('UPDATE monitors SET operational = ?2, last_updated = ?3 WHERE id = ?1')
+  const insertCheckStatement = db.prepare('INSERT INTO monitors_checks (monitor_id, location, res_ms, operational, date, timestamp) VALUES (?1, ?2, ?3, ?4, ?5, ?6)')
 
-  // Reset default all monitors state to true
-  monitorsState.lastUpdate.allOperational = true
-
-  for (const monitor of config.monitors) {
+  for (const monitor of monitors) {
     // Create default monitor state if does not exist yet
-    if (typeof monitorsState.monitors[monitor.id] === 'undefined') {
-      monitorsState.monitors[monitor.id] = {
-        firstCheck: checkDay,
-        lastCheck: {},
-        checks: {},
-      }
-    }
-
     console.log(`Checking ${monitor.name} ...`)
 
     // Fetch the monitors URL
     const init = {
       method: monitor.method || 'GET',
-      redirect: monitor.followRedirect ? 'follow' : 'manual',
+      redirect: monitor.follow_redirect ? 'follow' : 'manual',
       headers: {
         'User-Agent': config.settings.user_agent || 'cf-worker-status-page',
       },
@@ -57,103 +42,15 @@ export async function processCronTrigger(event) {
 
     // Determine whether operational and status changed
     const monitorOperational =
-      checkResponse.status === (monitor.expectStatus || 200)
-    const monitorStatusChanged =
-      monitorsState.monitors[monitor.id].lastCheck.operational !==
-      monitorOperational
-
-    // Save monitor's last check response status
-    monitorsState.monitors[monitor.id].lastCheck = {
-      status: checkResponse.status,
-      statusText: checkResponse.statusText,
-      operational: monitorOperational,
-    }
-
-    // Send Slack message on monitor change
-    if (
-      monitorStatusChanged &&
-      typeof SECRET_SLACK_WEBHOOK_URL !== 'undefined' &&
-      SECRET_SLACK_WEBHOOK_URL !== 'default-gh-action-secret'
-    ) {
-      event.waitUntil(notifySlack(monitor, monitorOperational))
-    }
-
-    // Send Telegram message on monitor change
-    if (
-      monitorStatusChanged &&
-      typeof SECRET_TELEGRAM_API_TOKEN !== 'undefined' &&
-      SECRET_TELEGRAM_API_TOKEN !== 'default-gh-action-secret' &&
-      typeof SECRET_TELEGRAM_CHAT_ID !== 'undefined' &&
-      SECRET_TELEGRAM_CHAT_ID !== 'default-gh-action-secret'
-    ) {
-      event.waitUntil(notifyTelegram(monitor, monitorOperational))
-    }
-
-    // Send Discord message on monitor change
-    if (
-      monitorStatusChanged &&
-      typeof SECRET_DISCORD_WEBHOOK_URL !== 'undefined' &&
-      SECRET_DISCORD_WEBHOOK_URL !== 'default-gh-action-secret'
-    ) {
-      event.waitUntil(notifyDiscord(monitor, monitorOperational))
-    }
-
-    // make sure checkDay exists in checks in cases when needed
-    if (
-      (config.settings.collectResponseTimes || !monitorOperational) &&
-      !monitorsState.monitors[monitor.id].checks.hasOwnProperty(checkDay)
-    ) {
-      monitorsState.monitors[monitor.id].checks[checkDay] = {
-        fails: 0,
-        res: {},
-      }
-    }
-
-    if (config.settings.collectResponseTimes && monitorOperational) {
-      // make sure location exists in current checkDay
-      if (
-        !monitorsState.monitors[monitor.id].checks[checkDay].res.hasOwnProperty(
-          checkLocation,
-        )
-      ) {
-        monitorsState.monitors[monitor.id].checks[checkDay].res[
-          checkLocation
-        ] = {
-          n: 0,
-          ms: 0,
-          a: 0,
-        }
-      }
-
-      // increment number of checks and sum of ms
-      const no = ++monitorsState.monitors[monitor.id].checks[checkDay].res[
-        checkLocation
-      ].n
-      const ms = (monitorsState.monitors[monitor.id].checks[checkDay].res[
-        checkLocation
-      ].ms += requestTime)
-
-      // save new average ms
-      monitorsState.monitors[monitor.id].checks[checkDay].res[
-        checkLocation
-      ].a = Math.round(ms / no)
-    } else if (!monitorOperational) {
-      // Save allOperational to false
-      monitorsState.lastUpdate.allOperational = false
-
-      // Increment failed checks, only on status change (maybe call it .incidents instead?)
-      if (monitorStatusChanged) {
-        monitorsState.monitors[monitor.id].checks[checkDay].fails++
-      }
-    }
+      checkResponse.status === (monitor.expect_status || 200) ? 1 : 0
+    
+      statements.push(
+        insertCheckStatement.bind( monitor.id, checkLocation, requestTime, monitorOperational, checkDay, new Date().toISOString()),
+        updateMonitorStatement.bind( monitor.id, monitorOperational, new Date().toISOString())
+      )
   }
 
-  // Save last update information
-  monitorsState.lastUpdate.time = Date.now()
-  monitorsState.lastUpdate.loc = checkLocation
+  const test = await db.batch(statements)
 
-  // Save monitorsState to KV storage
-  await setKVMonitors(monitorsState)
-
-  return new Response('OK')
+  return new Response(JSON.stringify(test))
 }

src/functions/helpers.js

@@ -1,21 +1,37 @@
 import config from '../../config.yaml'
 import { useEffect, useState } from 'react'
+import { Database } from "../d1"; // this will be native at some point, see above
 
 const kvDataKey = 'monitors_data_v1_1'
 
-export const locations = {
-  WAW: 'Warsaw',
-  SCL: 'Santiago de Chile',
-  MEL: 'Melbourne',
-  SIN: 'Singapore',
-}
-
 export async function getKVMonitors() {
   // trying both to see performance difference
   return KV_STATUS_PAGE.get(kvDataKey, 'json')
   //return JSON.parse(await KV_STATUS_PAGE.get(kvDataKey, 'text'))
 }
 
+export async function getMonitors() {
+  const db = new Database(D1UNSAFE)
+  const { results } = await db.prepare(
+    "SELECT * FROM monitors"
+  ).all();
+
+  return results
+}
+
+export async function loadData() {
+  const db = new Database(D1UNSAFE)
+  const batch = await db.batch([
+    db.prepare(
+      "SELECT * FROM monitors"
+    ),
+    db.prepare("SELECT monitor_id, AVG(res_ms) as avg_res, count(*) as count, location FROM monitors_checks WHERE date >= ?1 GROUP BY monitor_id, date, location ORDER BY timestamp ASC")
+      .bind(`${new Date().toISOString().split('T')[0]}`)
+  ])
+
+  return { monitors: batch[0].results, checks: batch[1].results}
+}
+
 export async function setKVMonitors(data) {
   return setKV(kvDataKey, JSON.stringify(data))
 }
@@ -34,6 +50,7 @@ export async function notifySlack(monitor, operational) {
   const payload = {
     attachments: [
       {
+        fallback: `Monitor ${monitor.name} changed status to ${getOperationalLabel(operational)}`,
         color: operational ? '#36a64f' : '#f2c744',
         blocks: [
           {
@@ -70,7 +87,7 @@ export async function notifySlack(monitor, operational) {
 }
 
 export async function notifyTelegram(monitor, operational) {
-  const text = `Monitor *${monitor.name.replace(
+  const text = `Monitor *${monitor.name.replaceAll(
     '-',
     '\\-',
   )}* changed status to *${getOperationalLabel(operational)}*

src/functions/locations.js

@@ -0,0 +1,226 @@
+export const locations = {
+  ADL: 'Adelaide',
+  AKL: 'Auckland',
+  ALG: 'Algiers',
+  AMM: 'Amman',
+  AMS: 'Amsterdam',
+  ARI: 'Arica',
+  ARN: 'Stockholm',
+  ASU: 'Asunción',
+  ATH: 'Athens',
+  ATL: 'Atlanta',
+  BAH: 'Manama',
+  BCN: 'Barcelona',
+  BEG: 'Belgrade',
+  BEL: 'Belém',
+  BEY: 'Beirut',
+  BGW: 'Baghdad',
+  BKK: 'Bangkok',
+  BLR: 'Bangalore',
+  BNA: 'Nashville',
+  BNE: 'Brisbane',
+  BNU: 'Blumenau',
+  BOG: 'Bogotá',
+  BOM: 'Mumbai',
+  BOS: 'Boston',
+  BRU: 'Brussels',
+  BSB: 'Brasilia',
+  BUD: 'Budapest',
+  BUF: 'Buffalo',
+  BWN: 'Bandar Seri Begawan',
+  CAN: 'Guangzhou',
+  CBR: 'Canberra',
+  CCU: 'Kolkata',
+  CDG: 'Paris',
+  CEB: 'Cebu',
+  CFC: 'Caçador',
+  CGK: 'Jakarta',
+  CGO: 'Zhengzhou',
+  CGP: 'Chittagong',
+  CKG: 'Chongqing',
+  CLT: 'Charlotte',
+  CMB: 'Colombo',
+  CMH: 'Columbus',
+  CMN: 'Casablanca',
+  CNF: 'Belo Horizonte',
+  CPH: 'Copenhagen',
+  CPT: 'Cape Town',
+  CSX: 'Zhuzhou',
+  CTU: 'Chengdu',
+  CUR: 'Willemstad',
+  CWB: 'Curitiba',
+  DAC: 'Dhaka',
+  DAR: 'Dar Es Salaam',
+  DEL: 'New Delhi',
+  DEN: 'Denver',
+  DFW: 'Dallas',
+  DKR: 'Dakar',
+  DME: 'Moscow',
+  DMM: 'Dammam',
+  DOH: 'Doha',
+  DTW: 'Detroit',
+  DUB: 'Dublin',
+  DUR: 'Durban',
+  DUS: 'Düsseldorf',
+  DXB: 'Dubai',
+  EDI: 'Edinburgh',
+  EVN: 'Yerevan',
+  EWR: 'Newark',
+  EZE: 'Buenos Aires',
+  FCO: 'Rome',
+  FLN: 'Florianopolis',
+  FOR: 'Fortaleza',
+  FRA: 'Frankfurt',
+  GIG: 'Rio de Janeiro',
+  GND: 'St. George’s',
+  GOT: 'Gothenburg',
+  GRU: 'São Paulo',
+  GUA: 'Guatemala City',
+  GVA: 'Geneva',
+  GYD: 'Baku',
+  GYE: 'Guayaquil',
+  HAM: 'Hamburg',
+  HAN: 'Hanoi',
+  HEL: 'Helsinki',
+  HKG: 'Hong Kong ',
+  HNL: 'Honolulu',
+  HRE: 'Harare',
+  HYD: 'Hyderabad',
+  IAD: 'Ashburn',
+  IAH: 'Houston',
+  ICN: 'Seoul',
+  IND: 'Indianapolis',
+  ISB: 'Islamabad',
+  IST: 'Istanbul',
+  ITJ: 'Itajaí',
+  JAX: 'Jacksonville',
+  JIB: 'Djibouti City',
+  JNB: 'Johannesburg',
+  JSR: 'Jashore',
+  KBP: 'Kyiv',
+  KEF: 'Reykjavík',
+  KGL: 'Kigali',
+  KHI: 'Karachi',
+  KIV: 'Chișinău',
+  KIX: 'Osaka',
+  KJA: 'Krasnoyarsk',
+  KTM: 'Kathmandu',
+  KUL: 'Kuala Lumpur',
+  KWI: 'Kuwait City',
+  LAD: 'Luanda',
+  LAS: 'Las Vegas',
+  LAX: 'Los Angeles',
+  LCA: 'Nicosia',
+  LED: 'Saint Petersburg',
+  LHE: 'Lahore',
+  LHR: 'London',
+  LIM: 'Lima',
+  LIS: 'Lisbon',
+  LOS: 'Lagos',
+  LUX: 'Luxembourg City',
+  MAA: 'Chennai',
+  MAD: 'Madrid',
+  MAN: 'Manchester',
+  MBA: 'Mombasa',
+  MCI: 'Kansas City',
+  MCT: 'Muscat',
+  MDE: 'Medellín',
+  MEL: 'Melbourne',
+  MEM: 'Memphis',
+  MEX: 'Mexico City',
+  MFE: 'McAllen',
+  MFM: 'Macau ',
+  MGM: 'Montgomery',
+  MIA: 'Miami',
+  MLE: 'Malé',
+  MNL: 'Manila',
+  MPM: 'Maputo',
+  MRS: 'Marseille',
+  MRU: 'Port Louis',
+  MSP: 'Minneapolis',
+  MUC: 'Munich',
+  MXP: 'Milan',
+  NAG: 'Nagpur',
+  NBG: 'Ningbo',
+  NBO: 'Nairobi',
+  NOU: 'Noumea',
+  NRT: 'Tokyo',
+  OMA: 'Omaha',
+  ORD: 'Chicago',
+  ORF: 'Norfolk',
+  OSL: 'Oslo',
+  OTP: 'Bucharest',
+  PAP: 'Port',
+  PBH: 'Thimphu',
+  PBM: 'Paramaribo',
+  PDX: 'Portland',
+  PER: 'Perth',
+  PHL: 'Philadelphia',
+  PHX: 'Phoenix',
+  PIT: 'Pittsburgh',
+  PMO: 'Palermo',
+  PNH: 'Phnom Penh',
+  POA: 'Porto Alegre',
+  PRG: 'Prague',
+  PTY: 'Panama City',
+  QRO: 'Queretaro',
+  QWJ: 'Americana',
+  RAO: 'Ribeirao Preto',
+  RGN: 'Yangon',
+  RIC: 'Richmond',
+  RIX: 'Riga',
+  ROB: 'Monrovia',
+  RUH: 'Riyadh',
+  RUN: 'Réunion',
+  SAN: 'San Diego',
+  SCL: 'Santiago',
+  SEA: 'Seattle',
+  SGN: 'Ho Chi Minh City',
+  SHA: 'Shanghai',
+  SIN: 'Singapore',
+  SJC: 'San Jose',
+  SJO: 'San José',
+  SJP: 'São José do Rio Preto',
+  SKG: 'Thessaloniki',
+  SLC: 'Salt Lake City',
+  SMF: 'Sacramento',
+  SOD: 'Sorocaba',
+  SOF: 'Sofia',
+  SSA: 'Salvador',
+  STL: 'St. Louis',
+  SVX: 'Yekaterinburg',
+  SYD: 'Sydney',
+  SZV: 'Suzhou',
+  TBS: 'Tbilisi',
+  TGU: 'Tegucigalpa',
+  TLH: 'Tallahassee',
+  TLL: 'Tallinn',
+  TLV: 'Tel Aviv',
+  TNA: 'Jinan',
+  TNR: 'Antananarivo',
+  TPA: 'Tampa',
+  TPE: 'Taipei ',
+  TSN: 'Tianjin',
+  TUN: 'Tunis',
+  TXL: 'Berlin',
+  UIO: 'Quito',
+  ULN: 'Ulaanbaatar',
+  URT: 'Surat Thani',
+  VCP: 'Campinas',
+  VIE: 'Vienna',
+  VNO: 'Vilnius',
+  VTE: 'Vientiane',
+  WAW: 'Warsaw',
+  WUH: 'Wuhan',
+  WUX: 'Wuxi',
+  XIY: 'Xi’an',
+  YUL: 'Montréal',
+  YVR: 'Vancouver',
+  YWG: 'Winnipeg',
+  YXE: 'Saskatoon',
+  YYC: 'Calgary',
+  YYZ: 'Toronto',
+  ZAG: 'Zagreb',
+  ZDM: 'Ramallah ',
+  ZRH: 'Zürich',
+}

wrangler.toml

@@ -1,18 +1,21 @@
 name = "cf-workers-status-page"
 workers_dev = true
-account_id = ""
-type = "webpack"
-webpack_config = "node_modules/flareact/webpack"
+compatibility_date = "2022-07-10"
+main = "./worker/script.js"
+
+routes = [
+    "status-d1.eidam.cf/*"
+]
+
+kv_namespaces = [{binding="KV_STATUS_PAGE", id="07c4d4e1aee94340abd97af34b2f78cc", preview_id="d5c948a04a3248898e05c84b684eeae2"}]
 
 [triggers]
 crons = ["* * * * *"]
 
 [site]
 bucket = "out"
-entry-point = "./"
 
-# uncomment and adjust following if you are not using GitHub Actions
-#[env.production]
-#kv-namespaces = [{binding="KV_STATUS_PAGE", id="xxxx", preview_id=""}]
-#zone_id="xxx"
-#route="xxx"
+[[ unsafe.bindings ]]
+name = "D1UNSAFE"
+type = "d1"
+id = "9fefade8-08c7-41fe-83a5-81417eb955df"