Something went wrong. Try again.
The IoT platform Cloudflare didn't build. Telemetry, realtime dashboards, and automations on Workers + D1 + R2 + Durable Objects — in your… nodrix.live
Something went wrong. Try again.
123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778-- Devices. Rows appear the first time a board is seen; there is no enrolment.CREATE TABLE IF NOT EXISTS devices ( id TEXT PRIMARY KEY, project_id TEXT NOT NULL REFERENCES projects(id) ON DELETE CASCADE, name TEXT NOT NULL, device_key TEXT, chip TEXT, firmware_version TEXT, is_default INTEGER NOT NULL DEFAULT 0, first_seen INTEGER, last_seen INTEGER, created_at INTEGER NOT NULL);CREATE INDEX IF NOT EXISTS idx_devices_project ON devices(project_id);
-- What the board calls itself, kept apart from the id so renaming is free and a-- MAC never leaks into anything a user reads. NULL on the default device.CREATE UNIQUE INDEX IF NOT EXISTS idx_devices_key ON devices(project_id, device_key) WHERE device_key IS NOT NULL;
-- Exactly one default per project, enforced rather than assumed.CREATE UNIQUE INDEX IF NOT EXISTS idx_devices_default ON devices(project_id) WHERE is_default = 1;
-- Every project gets a default device. Telemetry that names no device lands-- here, so a plain curl with only a token keeps working.INSERT INTO devices (id, project_id, name, is_default, created_at)SELECT 'dev_' || substr(p.id, 5), p.id, 'Default', 1, p.created_atFROM projects pWHERE NOT EXISTS (SELECT 1 FROM devices d WHERE d.project_id = p.id AND d.is_default = 1);
-- project_variables gains device_id. Rebuilt rather than altered: the column is-- NOT NULL with a foreign key, and the unique key it belongs to changes shape.CREATE TABLE project_variables_new ( id TEXT PRIMARY KEY, project_id TEXT NOT NULL REFERENCES projects(id) ON DELETE CASCADE, device_id TEXT NOT NULL REFERENCES devices(id) ON DELETE CASCADE, key TEXT NOT NULL, unit TEXT, created_at INTEGER NOT NULL, updated_at INTEGER NOT NULL, last_seen INTEGER);
INSERT INTO project_variables_new (id, project_id, device_id, key, unit, created_at, updated_at, last_seen)SELECT v.id, v.project_id, d.id, v.key, v.unit, v.created_at, v.updated_at, v.last_seenFROM project_variables vJOIN devices d ON d.project_id = v.project_id AND d.is_default = 1;
DROP TABLE project_variables;ALTER TABLE project_variables_new RENAME TO project_variables;
CREATE UNIQUE INDEX idx_project_variables_key ON project_variables(project_id, device_id, key);
-- Firmware uploaded to this instance. The image itself lives in R2; this is the-- metadata the dashboard and the update check read.CREATE TABLE IF NOT EXISTS firmware ( id TEXT PRIMARY KEY, project_id TEXT NOT NULL REFERENCES projects(id) ON DELETE CASCADE, version TEXT NOT NULL, target TEXT, size INTEGER NOT NULL, sha256 TEXT NOT NULL, r2_key TEXT NOT NULL, notes TEXT, created_by TEXT REFERENCES users(id), created_at INTEGER NOT NULL);CREATE INDEX IF NOT EXISTS idx_firmware_project ON firmware(project_id, created_at DESC);CREATE UNIQUE INDEX IF NOT EXISTS idx_firmware_version ON firmware(project_id, version);
-- Desired state. A device compares its own version against this and pulls when-- they differ; nothing pushes an image at a device.ALTER TABLE devices ADD COLUMN desired_firmware_id TEXT REFERENCES firmware(id) ON DELETE SET NULL;ALTER TABLE devices ADD COLUMN ota_status TEXT;ALTER TABLE devices ADD COLUMN ota_updated_at INTEGER;