Repository navigation
Expand file tree
/
Copy pathschema.sql
More file actions
107 lines (96 loc) · 3.8 KB
/
Copy pathschema.sql
File metadata and controls
107 lines (96 loc) · 3.8 KB
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
CREATE TABLE IF NOT EXISTS monitors (
id TEXT PRIMARY KEY,
name TEXT NOT NULL,
monitor_type TEXT NOT NULL DEFAULT 'http',
url TEXT NOT NULL,
interval_minutes INTEGER NOT NULL DEFAULT 1,
timeout_ms INTEGER NOT NULL DEFAULT 10000,
alert_webhook TEXT,
expected_status_code INTEGER,
retry_count INTEGER DEFAULT 2,
json_path TEXT,
json_status_map TEXT,
keyword TEXT,
manual_status TEXT,
grace_period_minutes INTEGER NOT NULL DEFAULT 5,
push_token TEXT,
created_at INTEGER NOT NULL DEFAULT (unixepoch()),
active INTEGER NOT NULL DEFAULT 1,
last_checked_at INTEGER,
last_heartbeat_at INTEGER
);
CREATE UNIQUE INDEX IF NOT EXISTS idx_monitors_push_token ON monitors(push_token);
CREATE TABLE IF NOT EXISTS checks (
id INTEGER PRIMARY KEY AUTOINCREMENT,
monitor_id TEXT NOT NULL REFERENCES monitors(id) ON DELETE CASCADE,
status_code INTEGER NOT NULL DEFAULT 0,
ok INTEGER NOT NULL,
degraded INTEGER NOT NULL DEFAULT 0,
latency_ms INTEGER,
error TEXT,
json_value TEXT,
checked_at INTEGER NOT NULL DEFAULT (unixepoch())
);
CREATE INDEX IF NOT EXISTS idx_checks_monitor_checked ON checks(monitor_id, checked_at DESC);
CREATE INDEX IF NOT EXISTS idx_checks_checked_at ON checks(checked_at);
-- Incrementally maintained by cron.ts on every check insert: one row per
-- monitor per fixed 8-hour epoch-aligned window. The public status page and
-- the admin dashboard's uptime% both read only this table, instead of
-- scanning the full checks table (which can be tens of thousands of rows per
-- monitor over 30 days) on every request.
CREATE TABLE IF NOT EXISTS uptime_bucket_rollups (
monitor_id TEXT NOT NULL REFERENCES monitors(id) ON DELETE CASCADE,
bucket_start INTEGER NOT NULL,
cnt INTEGER NOT NULL DEFAULT 0,
up_cnt INTEGER NOT NULL DEFAULT 0,
degraded_cnt INTEGER NOT NULL DEFAULT 0,
PRIMARY KEY (monitor_id, bucket_start)
);
CREATE TABLE IF NOT EXISTS incidents (
id INTEGER PRIMARY KEY AUTOINCREMENT,
monitor_id TEXT NOT NULL REFERENCES monitors(id) ON DELETE CASCADE,
started_at INTEGER NOT NULL,
resolved_at INTEGER,
trigger_status_code INTEGER,
trigger_error TEXT
);
CREATE INDEX IF NOT EXISTS idx_incidents_monitor ON incidents(monitor_id, started_at DESC);
CREATE TABLE IF NOT EXISTS status_pages (
id TEXT PRIMARY KEY,
name TEXT NOT NULL,
slug TEXT NOT NULL UNIQUE,
description TEXT,
custom_domain TEXT UNIQUE,
logo_url TEXT,
incident_history_days INTEGER NOT NULL DEFAULT 30,
min_incident_duration_minutes INTEGER NOT NULL DEFAULT 0,
header_template TEXT NOT NULL DEFAULT 'centered',
brand_color TEXT,
show_latency INTEGER NOT NULL DEFAULT 1,
created_at INTEGER NOT NULL DEFAULT (unixepoch())
);
CREATE TABLE IF NOT EXISTS status_page_monitors (
status_page_id TEXT NOT NULL REFERENCES status_pages(id) ON DELETE CASCADE,
monitor_id TEXT NOT NULL REFERENCES monitors(id) ON DELETE CASCADE,
display_order INTEGER NOT NULL DEFAULT 0,
PRIMARY KEY (status_page_id, monitor_id)
);
CREATE TABLE IF NOT EXISTS notices (
id TEXT PRIMARY KEY,
status_page_id TEXT NOT NULL REFERENCES status_pages(id) ON DELETE CASCADE,
message TEXT NOT NULL,
severity TEXT NOT NULL DEFAULT 'info',
created_at INTEGER NOT NULL DEFAULT (unixepoch()),
resolved_at INTEGER
);
CREATE INDEX IF NOT EXISTS idx_notices_page ON notices(status_page_id, created_at DESC);
-- Singleton row tracking whether the self-monitoring health check currently
-- considers the Worker unhealthy (checks not landing on schedule). Read and
-- written by the separate */15 cron tick in health.ts, kept apart from the
-- monitors/checks tables so it never touches the 1-minute check loop.
CREATE TABLE IF NOT EXISTS worker_health (
id INTEGER PRIMARY KEY CHECK (id = 1),
unhealthy INTEGER NOT NULL DEFAULT 0,
updated_at INTEGER
);
INSERT OR IGNORE INTO worker_health (id, unhealthy) VALUES (1, 0);