Database Schema

Lab Kiosk stores everything durable in Cloudflare D1 (SQLite at the edge). Live state -- who is connected, the command queue, screen frames -- belongs to each organization's OrgHub Durable Object, which writes back to client_devices here; audit entries older than 180 days move to R2.

The schema has two homes

This is the single most important rule when touching the database, and the test suite enforces it.

Both must be changed together. test/worker.test.ts compares them and fails on drift with Columns of "x" differ between SCHEMA_SQL and migrations/.

Never edit an applied migration. Add a new numbered file — 0019_feature.sql — and mirror the change in SCHEMA_SQL.

# Local
npx wrangler d1 migrations apply labkiosk-db --local

# Production
npx wrangler d1 migrations apply labkiosk-db --remote

A worker with a D1 binding refuses to serve a database whose migrations have not been applied (assertSchemaCurrent()), and never creates tables at runtime.

Migration history

Applied migrations are never edited or renamed: wrangler tracks them by file name, which is why 0008 keeps its original name.

**0011 rebuilds tables that other tables cascade from.** D1 cannot switch foreign keys off, and DROP TABLE deletes a table's rows first, firing ON DELETE CASCADE even with foreign-key checks deferred — a naive rebuild of users deletes every organization. 0011 copies every row into holding tables with no foreign keys, drops leaves first, recreates parents first and copies back with explicit column lists. Export the database before applying it in production (wrangler d1 export labkiosk-db --remote --output backup.sql).

Each migration exists because something concrete broke. Migration 0002 is worth reading in full: before it, anyone who guessed a subdomain could post screenshots and drain that organization's command queue, the allowlist was a mutable module global shared by every tenant, and broadcast commands re-executed on every three-second heartbeat.

Tables

users

Platform and organization administrators.

tenants

One row per organization. This table has accumulated the most columns because it is where an organization's entire configuration lives.

broadcast_url and broadcast_epoch live here rather than in worker memory because isolates are per-colocation and short-lived. When they were module-level state, workstations in different colos disagreed about the current page and a recycled isolate forgot the broadcast entirely. A broadcast sent to selected workstations is recorded on each of their client_devices rows instead; the heartbeat hands a workstation whichever of the two is newer.

sessions

portal_sites

User Portal cards.

client_devices

The fleet registry. OrgHub writes it back on connect, disconnect, a change and every 5 minutes; who is online right now comes from the hub.

workstation_issues

Errors and warnings workstations report, kept apart from audit_logs (which records what people did). Fed by POST /api/devices/boot-report; read by Settings → Errors & Warnings; the hourly cron deletes rows older than 90 days.

bug_reports

One row per distinct redacted problem (signature); several signatures may share one GitHub issue when the reasoning model matched them. Shared by every organization that reports them, with no organization or workstation data (src/bug_reports.ts).

device_tokens

tenant_whitelist

Per-organization permanent domain allowlist. UNIQUE (tenant_id, domain).

The effective allowlist a workstation receives is this table unioned with every portal_sites.domain and, when a broadcast is active, its host. That union is computed by buildEffectiveWhitelist(), cached by the organization's hub and pushed to workstations when it changes; it is not stored.

Commands (not in D1 since 0014)

Queued commands and their per-workstation delivery receipts live in each organization's OrgHub, in its own SQLite (commands, deliveries). A command for "all" records one delivery per workstation, so a broadcast executes exactly once on each machine; commands expire after 60 s.

broadcast_presets

Operator-defined quick-launch shortcuts: id, tenant_id, title, url, created_at.

login_attempts

ui_catalogs

Interface translations for the setup wizard and kiosk bar. **No tenant_id, on purpose**: the text is the same for every organization, and a workstation fetches it before it is enrolled. Written only by a super admin.

tenant_users

Staff accounts delegated by an organization.

An org_admin row, or owning the organization (tenants.user_id), means full access. A delegate holding staff can grant only what they hold themselves and cannot appoint an org_admin.

workstation_groups

audit_logs

Indices

idx_tenants_subdomain              tenants(subdomain)
idx_tenants_status                 tenants(status)
idx_tenants_custom_domain          tenants(custom_domain)          -- UNIQUE
idx_tenants_custom_domain_status   tenants(custom_domain_status)
idx_sessions_user_id               sessions(user_id)
idx_sessions_expires_at            sessions(expires_at)
idx_portal_sites_tenant            portal_sites(tenant_id, order_index)
idx_client_devices_tenant          client_devices(tenant_id)
idx_device_tokens_tenant           device_tokens(tenant_id, client_id)
idx_tenant_whitelist_tenant        tenant_whitelist(tenant_id)
idx_broadcast_presets_tenant       broadcast_presets(tenant_id)
idx_audit_logs_tenant              audit_logs(tenant_id, created_at)
idx_workstation_issues_tenant      workstation_issues(tenant_id, created_at)
idx_bug_reports_issue              bug_reports(issue_number)   -- platform table, no tenant_id

Every index leads with tenant_id wherever the table is tenant-scoped, matching the query shape that db.ts always uses.

Adding a schema change

1. Create migrations/0019_<description>.sql (the next number after 0018). Use ALTER TABLE for new columns; D1 has SQLite's limitations, so plan for additive changes.
2. Mirror the change in SCHEMA_SQL in src/db.ts.
3. Make sure any new query filters by tenant_id.
4. Run pnpm --prefix cloudflare-control test. The drift test will tell you if the two homes disagree.
5. Apply locally with --local, and in production with --remote — the deploy workflow does this automatically before wrangler deploy.

Local testing engine

src/d1_adapter.ts implements the D1 interface on top of Node 22's native node:sqlite, with no npm dependency. Tests run against it with ALLOW_LOCAL_DB=1; without that flag a missing D1 binding is a hard failure rather than silent data loss.

Windows note: Miniflare's db.exec() mis-parses multiline SQL with CRLF line endings, producing D1_EXEC_ERROR: incomplete input. The adapter splits statements on ;, normalises \r\n to \n, and runs each through db.prepare(stmt).run().

→ Control Plane Internals · Testing Guide