orchard

  1package main
  2
  3import (
  4	"context"
  5	"database/sql"
  6	"fmt"
  7	"os"
  8	"path/filepath"
  9	"strings"
 10	"time"
 11
 12	"github.com/google/uuid"
 13	_ "modernc.org/sqlite"
 14)
 15
 16// schema is IF NOT EXISTS throughout: pointing this at an existing database has
 17// to be a no-op, since the property UUIDs in it are the addresses of live public
 18// status pages.
 19const schema = `
 20CREATE TABLE IF NOT EXISTS properties (
 21    id                          BLOB PRIMARY KEY,
 22    url                         TEXT NOT NULL,
 23    is_public                   INTEGER NOT NULL DEFAULT 0,
 24    is_protected                INTEGER NOT NULL DEFAULT 0,
 25
 26    last_run_at                 INTEGER,
 27    next_run_at                 INTEGER,
 28
 29    last_run_at_crawler         INTEGER,
 30    next_run_at_crawler         INTEGER,
 31    crawler_insights            TEXT,
 32    crawl_state                 TEXT NOT NULL DEFAULT 'idle',
 33    crawl_started_at            INTEGER,
 34    last_crawl_success_at       INTEGER,
 35    last_crawl_error            TEXT,
 36    last_crawl_duration_ms      INTEGER,
 37    last_crawl_pages_count      INTEGER,
 38
 39    lighthouse_scores           TEXT,
 40    lighthouse_details          TEXT,
 41    last_lighthouse_run_at      INTEGER,
 42    last_lighthouse_success_at  INTEGER,
 43    last_lighthouse_error       TEXT,
 44    last_lighthouse_duration_ms INTEGER,
 45    next_lighthouse_run_at      INTEGER,
 46    lighthouse_state            TEXT NOT NULL DEFAULT 'idle',
 47    lighthouse_started_at       INTEGER,
 48
 49    alert_state                 TEXT NOT NULL DEFAULT 'up',
 50    last_alert_sent             INTEGER,
 51
 52    created_at                  INTEGER NOT NULL,
 53    updated_at                  INTEGER NOT NULL
 54);
 55CREATE INDEX IF NOT EXISTS properties_url ON properties(url);
 56
 57CREATE TABLE IF NOT EXISTS checks (
 58    id           INTEGER PRIMARY KEY AUTOINCREMENT,
 59    property_id  BLOB NOT NULL REFERENCES properties(id) ON DELETE CASCADE,
 60    status_code  INTEGER NOT NULL,
 61    response_ms  INTEGER NOT NULL DEFAULT 0,
 62    headers      TEXT NOT NULL DEFAULT '{}',
 63    created_at   INTEGER NOT NULL,
 64    dns_ms       INTEGER,
 65    tcp_ms       INTEGER,
 66    tls_ms       INTEGER,
 67    ttfb_ms      INTEGER
 68);
 69CREATE INDEX IF NOT EXISTS checks_created_at       ON checks(created_at);
 70CREATE INDEX IF NOT EXISTS checks_property_created ON checks(property_id, created_at DESC);
 71
 72CREATE TABLE IF NOT EXISTS meta (
 73    key   TEXT PRIMARY KEY,
 74    value TEXT NOT NULL
 75);
 76`
 77
 78// phaseColumns are in the CREATE TABLE above as well. SQLite has no ADD COLUMN
 79// IF NOT EXISTS, and CREATE TABLE IF NOT EXISTS does not reconcile columns on a
 80// table that already exists, so ensurePhaseColumns adds them by hand.
 81var phaseColumns = []addedColumn{
 82	{"dns_ms", "INTEGER"},
 83	{"tcp_ms", "INTEGER"},
 84	{"tls_ms", "INTEGER"},
 85	{"ttfb_ms", "INTEGER"},
 86	{"cf_cache_status", "TEXT"},
 87	{"age", "INTEGER"},
 88}
 89
 90type addedColumn struct{ name, typ string }
 91
 92// openDB opens the SQLite database and applies the schema. The pragmas go in the
 93// DSN because they are per connection and database/sql opens connections lazily,
 94// so setting them once after Open would reach only one of them.
 95func openDB(path string) (*sql.DB, error) {
 96	if dir := filepath.Dir(path); dir != "" {
 97		if err := os.MkdirAll(dir, 0o755); err != nil {
 98			return nil, fmt.Errorf("create data dir: %w", err)
 99		}
100	}
101
102	dsn := path +
103		"?_pragma=journal_mode(WAL)" +
104		"&_pragma=synchronous(NORMAL)" +
105		"&_pragma=busy_timeout(5000)" +
106		"&_pragma=foreign_keys(ON)"
107
108	db, err := sql.Open("sqlite", dsn)
109	if err != nil {
110		return nil, fmt.Errorf("open sqlite: %w", err)
111	}
112
113	// SQLite takes a database-wide write lock, so a pool larger than one buys
114	// nothing for writers. It is for readers, which WAL runs during a write.
115	db.SetMaxOpenConns(8)
116	db.SetMaxIdleConns(8)
117	db.SetConnMaxLifetime(time.Hour)
118
119	if err := db.Ping(); err != nil {
120		return nil, fmt.Errorf("ping sqlite: %w", err)
121	}
122	if _, err := db.Exec(schema); err != nil {
123		return nil, fmt.Errorf("apply schema: %w", err)
124	}
125	if err := ensurePhaseColumns(db); err != nil {
126		return nil, err
127	}
128	return db, nil
129}
130
131func ensurePhaseColumns(db *sql.DB) error {
132	rows, err := db.Query("PRAGMA table_info(checks)")
133	if err != nil {
134		return fmt.Errorf("inspect checks table: %w", err)
135	}
136	defer rows.Close()
137
138	have := map[string]bool{}
139	for rows.Next() {
140		var (
141			cid        int
142			name, typ  string
143			notNull    int
144			defaultVal any
145			pk         int
146		)
147		if err := rows.Scan(&cid, &name, &typ, &notNull, &defaultVal, &pk); err != nil {
148			return fmt.Errorf("inspect checks table: %w", err)
149		}
150		have[name] = true
151	}
152	if err := rows.Err(); err != nil {
153		return fmt.Errorf("inspect checks table: %w", err)
154	}
155
156	for _, col := range phaseColumns {
157		if have[col.name] {
158			continue
159		}
160		if _, err := db.Exec("ALTER TABLE checks ADD COLUMN " + col.name + " " + col.typ); err != nil {
161			return fmt.Errorf("add checks.%s: %w", col.name, err)
162		}
163	}
164	return nil
165}
166
167func nowMS() int64 { return time.Now().UnixMilli() }
168
169// Property is one tracked URL. Every nullable column is a pointer rather than a
170// sql.NullInt64, so templates and JSON can test it with a plain nil check.
171type Property struct {
172	ID          uuid.UUID
173	URL         string
174	IsPublic    bool
175	IsProtected bool
176
177	LastRunAt *int64
178	NextRunAt *int64
179
180	LastRunAtCrawler    *int64
181	NextRunAtCrawler    *int64
182	CrawlerInsights     *string
183	CrawlState          string
184	CrawlStartedAt      *int64
185	LastCrawlSuccessAt  *int64
186	LastCrawlError      *string
187	LastCrawlDurationMS *int64
188	LastCrawlPagesCount *int64
189
190	LighthouseScores         *string
191	LighthouseDetails        *string
192	LastLighthouseRunAt      *int64
193	LastLighthouseSuccessAt  *int64
194	LastLighthouseError      *string
195	LastLighthouseDurationMS *int64
196	NextLighthouseRunAt      *int64
197	LighthouseState          string
198	LighthouseStartedAt      *int64
199
200	AlertState    string
201	LastAlertSent *int64
202
203	CreatedAt int64
204	UpdatedAt int64
205}
206
207// Name is the hostname without a leading www. Parsed rather than split on "/",
208// because the URL is operator input and need not be well formed.
209func (p *Property) Name() string {
210	u, err := parseHTTPURL(p.URL)
211	if err != nil || u.Hostname() == "" {
212		return p.URL
213	}
214	return strings.TrimPrefix(u.Hostname(), "www.")
215}
216
217const propertyColumns = `id, url, is_public, is_protected,
218	last_run_at, next_run_at,
219	last_run_at_crawler, next_run_at_crawler, crawler_insights, crawl_state,
220	crawl_started_at, last_crawl_success_at, last_crawl_error,
221	last_crawl_duration_ms, last_crawl_pages_count,
222	lighthouse_scores, lighthouse_details, last_lighthouse_run_at,
223	last_lighthouse_success_at, last_lighthouse_error,
224	last_lighthouse_duration_ms, next_lighthouse_run_at, lighthouse_state,
225	lighthouse_started_at,
226	alert_state, last_alert_sent, created_at, updated_at`
227
228func scanProperty(scan func(...any) error) (*Property, error) {
229	var (
230		p     Property
231		idRaw []byte
232		pub   int64
233		prot  int64
234	)
235	err := scan(
236		&idRaw, &p.URL, &pub, &prot,
237		&p.LastRunAt, &p.NextRunAt,
238		&p.LastRunAtCrawler, &p.NextRunAtCrawler, &p.CrawlerInsights, &p.CrawlState,
239		&p.CrawlStartedAt, &p.LastCrawlSuccessAt, &p.LastCrawlError,
240		&p.LastCrawlDurationMS, &p.LastCrawlPagesCount,
241		&p.LighthouseScores, &p.LighthouseDetails, &p.LastLighthouseRunAt,
242		&p.LastLighthouseSuccessAt, &p.LastLighthouseError,
243		&p.LastLighthouseDurationMS, &p.NextLighthouseRunAt, &p.LighthouseState,
244		&p.LighthouseStartedAt,
245		&p.AlertState, &p.LastAlertSent, &p.CreatedAt, &p.UpdatedAt,
246	)
247	if err != nil {
248		return nil, err
249	}
250	id, err := uuid.FromBytes(idRaw)
251	if err != nil {
252		return nil, fmt.Errorf("property id is not a uuid: %w", err)
253	}
254	p.ID = id
255	p.IsPublic = pub != 0
256	p.IsProtected = prot != 0
257	return &p, nil
258}
259
260// listProperties returns every property, optionally filtered by a substring of
261// the URL.
262func listProperties(ctx context.Context, db *sql.DB, search string) ([]*Property, error) {
263	query := "SELECT " + propertyColumns + " FROM properties ORDER BY url"
264	args := []any{}
265	if search != "" {
266		query = "SELECT " + propertyColumns + " FROM properties WHERE url LIKE ? ORDER BY url"
267		args = append(args, "%"+search+"%")
268	}
269
270	rows, err := db.QueryContext(ctx, query, args...)
271	if err != nil {
272		return nil, err
273	}
274	defer rows.Close()
275
276	var out []*Property
277	for rows.Next() {
278		p, err := scanProperty(rows.Scan)
279		if err != nil {
280			return nil, err
281		}
282		out = append(out, p)
283	}
284	return out, rows.Err()
285}
286
287// getProperty returns the property with this id, or nil when there is none.
288func getProperty(ctx context.Context, db *sql.DB, id uuid.UUID) (*Property, error) {
289	row := db.QueryRowContext(ctx,
290		"SELECT "+propertyColumns+" FROM properties WHERE id = ?", id[:])
291	p, err := scanProperty(row.Scan)
292	if err == sql.ErrNoRows {
293		return nil, nil
294	}
295	return p, err
296}
297
298func createProperty(ctx context.Context, db *sql.DB, rawURL string) (uuid.UUID, error) {
299	id := uuid.New()
300	now := nowMS()
301	_, err := db.ExecContext(ctx,
302		"INSERT INTO properties (id, url, created_at, updated_at) VALUES (?, ?, ?, ?)",
303		id[:], rawURL, now, now)
304	return id, err
305}
306
307// deleteProperty removes a property and, by foreign key, its checks. Protected
308// properties are excluded in the statement itself, so no route can miss it.
309func deleteProperty(ctx context.Context, db *sql.DB, id uuid.UUID) error {
310	_, err := db.ExecContext(ctx,
311		"DELETE FROM properties WHERE id = ? AND is_protected = 0", id[:])
312	return err
313}
314
315// togglePublic flips the public flag and reports the new value. found is false
316// with a nil error when there is no such property.
317func togglePublic(ctx context.Context, db *sql.DB, id uuid.UUID) (isPublic, found bool, err error) {
318	var current int64
319	err = db.QueryRowContext(ctx,
320		"SELECT is_public FROM properties WHERE id = ?", id[:]).Scan(&current)
321	if err == sql.ErrNoRows {
322		return false, false, nil
323	}
324	if err != nil {
325		return false, false, err
326	}
327
328	next := int64(1)
329	if current != 0 {
330		next = 0
331	}
332	if _, err := db.ExecContext(ctx,
333		"UPDATE properties SET is_public = ?, updated_at = ? WHERE id = ?",
334		next, nowMS(), id[:]); err != nil {
335		return false, true, err
336	}
337	return next == 1, true, nil
338}
339
340// Check is one HTTP probe. The four phase timings are nullable, so the chart
341// skips nulls; response_ms is the canonical total.
342type Check struct {
343	StatusCode int64
344	ResponseMS int64
345	Headers    string
346	CreatedAt  int64
347	DNSMS      *int64
348	TCPMS      *int64
349	TLSMS      *int64
350	TTFBMS     *int64
351}
352
353func recentChecks(ctx context.Context, db *sql.DB, id uuid.UUID, limit int) ([]Check, error) {
354	rows, err := db.QueryContext(ctx,
355		`SELECT status_code, response_ms, headers, created_at, dns_ms, tcp_ms, tls_ms, ttfb_ms
356		 FROM checks WHERE property_id = ? ORDER BY created_at DESC LIMIT ?`, id[:], limit)
357	if err != nil {
358		return nil, err
359	}
360	defer rows.Close()
361
362	var out []Check
363	for rows.Next() {
364		var c Check
365		if err := rows.Scan(&c.StatusCode, &c.ResponseMS, &c.Headers, &c.CreatedAt,
366			&c.DNSMS, &c.TCPMS, &c.TLSMS, &c.TTFBMS); err != nil {
367			return nil, err
368		}
369		out = append(out, c)
370	}
371	return out, rows.Err()
372}
373
374// StatusCount is one bar of the status-code chart.
375type StatusCount struct {
376	Code  int64
377	Count int64
378}
379
380func countStatusCodes(ctx context.Context, db *sql.DB, id uuid.UUID) ([]StatusCount, error) {
381	rows, err := db.QueryContext(ctx,
382		`SELECT status_code, COUNT(*) FROM checks WHERE property_id = ?
383		 GROUP BY status_code ORDER BY status_code`, id[:])
384	if err != nil {
385		return nil, err
386	}
387	defer rows.Close()
388
389	var out []StatusCount
390	for rows.Next() {
391		var s StatusCount
392		if err := rows.Scan(&s.Code, &s.Count); err != nil {
393			return nil, err
394		}
395		out = append(out, s)
396	}
397	return out, rows.Err()
398}
399
400func countChecks(ctx context.Context, db *sql.DB, id uuid.UUID) (int64, error) {
401	var n int64
402	err := db.QueryRowContext(ctx,
403		"SELECT COUNT(*) FROM checks WHERE property_id = ?", id[:]).Scan(&n)
404	return n, err
405}
406
407// countUptime returns the all-time up and down counts. The COALESCE is required:
408// SUM over zero rows is NULL, so a never-checked property would fail the scan.
409func countUptime(ctx context.Context, db *sql.DB, id uuid.UUID) (up, down int64, err error) {
410	err = db.QueryRowContext(ctx,
411		`SELECT COALESCE(SUM(CASE WHEN status_code = 200 THEN 1 ELSE 0 END), 0),
412		        COALESCE(SUM(CASE WHEN status_code <> 200 THEN 1 ELSE 0 END), 0)
413		 FROM checks WHERE property_id = ?`, id[:]).Scan(&up, &down)
414	return up, down, err
415}