D1 Database Schema
Relevant source files
The following files were used as context for generating this wiki page:
Introduction
The D1 Database Schema serves as the durable storage layer for StatusBeam, specifically handling time-series uptime data and incident management. Unlike previous solutions that relied on Git commit history, StatusBeam utilizes Cloudflare D1 (SQLite) to store check results, incident lifecycles, and update history. This architecture enables efficient range scans and aggregation for rendering uptime bars and response-time charts at the edge.
Sources: ARCHITECTURE.md:12-25, README.md:27-40
The database is primarily managed by the Check Layer (Cloudflare Worker), which performs batch inserts of check results and manages incident states. The Display Layer (Astro site) acts as a read-only consumer, querying the schema to generate snapshots for KV storage and direct page rendering.
Sources: ARCHITECTURE.md:83-93, README.md:65-80
Database Architecture
The schema is defined in a single SQL file and is designed to be idempotent using CREATE TABLE IF NOT EXISTS statements. The architecture consists of three primary entities: checks, incidents, and incident_updates.
Sources: apps/worker/schema.sql:1-40, packages/cli/src/lib/wrangler.ts:86-89
Entity Relationship Diagram
The following diagram illustrates the relationships between the uptime monitoring data and the incident management system.
Sources: apps/worker/schema.sql:1-40
Data Models
Checks Table
The checks table stores individual probe results for every configured service. It replaces the traditional "Git-as-a-database" model with a structure optimized for time-series reads.
Sources: apps/worker/schema.sql:4-6, README.md:36-40
| Field | Type | Constraints | Description |
|---|---|---|---|
id | INTEGER | PRIMARY KEY AUTOINCREMENT | Unique identifier for the check run. |
slug | TEXT | NOT NULL | Character-constrained identifier for the monitored site. |
status | TEXT | NOT NULL, CHECK | Outcome of the check: up, degraded, or down. |
code | INTEGER | NOT NULL | HTTP status code or connection result. |
response_time | INTEGER | NOT NULL | Latency in milliseconds. |
checked_at | TEXT | NOT NULL | ISO8601 timestamp of the probe. |
| Sources: apps/worker/schema.sql:7-14, ARCHITECTURE.md:121-124 |
Incident Management Tables
Incidents are tracked through two tables that separate the core incident metadata from the chronological timeline of updates.
Sources: apps/worker/schema.sql:24-40
Incidents Table
Defines the high-level state of a service disruption.
Sources: apps/worker/schema.sql:24-31
| Field | Type | Description |
|---|---|---|
slug | TEXT | The site identifier the incident belongs to. |
severity | TEXT | Incident impact level. |
started_at | TEXT | Timestamp when the incident began. |
resolved_at | TEXT | Timestamp when the incident was marked resolved (nullable). |
Incident Updates Table
Tracks the progression of an incident through its lifecycle: investigating, identified, monitoring, and resolved.
Sources: apps/worker/schema.sql:33-40
Performance Optimization
The schema includes specific indexes to ensure that the status page remains fast even as historical data grows. These indexes support the 90-day history aggregate which involves range scans across all service slugs.
Sources: apps/worker/schema.sql:16-22
idx_checks_slug_time: Supports querying the most recent status for a specific service.idx_checks_time: Achecked_at-first index designed for sargable range scans for 90-day history rendering.
Sources: apps/worker/schema.sql:16-22
Deployment and Schema Management
Schema application is handled via the wrangler d1 execute command. It is performed during the initial setup and subsequent deployments to ensure the remote database matches the local definition.
Sources: scripts/setup.sh:162-164, packages/cli/src/commands/deploy.ts:42-45, apps/worker/package.json:28-29
# Applying schema remotely during deploy
wrangler d1 execute statusbeam --remote --file=./schema.sql --yes
Sources: apps/worker/package.json:29
Conclusion
The D1 Database Schema is a critical component of StatusBeam's edge-native architecture. By moving from best-effort Git-based storage to a structured SQLite environment on Cloudflare's edge, the system achieves reliable time-series recording and high-performance rendering of status data without the constraints of third-party API rate limits.
Sources: README.md:27-44, ARCHITECTURE.md:36-40