Skip to content

Database Schema

OneSeventyFour edited this page Jun 25, 2026 · 1 revision

Database Schema (SQLite)

A field-by-field reference for the on-device database used by the host app (host/byh_app/backyardhero). For the operational side — how the file is created, migrated, backed up, and who reads/writes each table — see Database subsystem. This page focuses on the schema itself, including the structure of the JSON stored inside the TEXT blob columns.

  • File: /data/backyardhero.db (host path host/data/backyardhero.db)
  • Owner: host/byh_app/backyardhero/src/util/sqldb.js — the only module that creates/migrates the schema. There is no separate migration runner; initializeDatabase() runs CREATE TABLE IF NOT EXISTS + idempotent ALTER TABLE ADD COLUMN on every boot.
  • Access: better-sqlite3 from the Next.js app (via the SQLite repository adapter src/data/sqlite/index.js); the Python daemon also reads it directly via the sqlite3 stdlib.

Many columns are TEXT that hold serialized JSON. SQLite has no JSON column type here — these are plain strings the app JSON.parse/JSON.stringifyes. Their internal shape is documented in JSON blob structures below.

Table overview

Table Key Purpose
Show id (autoinc) One firework show: timeline, runtime firing array, audio, receivers, geo.
inventory id (autoinc) Each pyro product (cake, shell, fountain, fuse, …).
inventoryFiringProfile id (autoinc), UNIQUE(inventory_id) YouTube-derived shot timing for an inventory item.
racks id (autoinc) Per-show physical mortar rack layouts.
Receivers id (text ident) Source of truth for the dongle's receiver poll list.

Relationships

Show 1───∞ racks            racks.show_id        → Show.id            (ON DELETE CASCADE)
inventory 1──1 inventoryFiringProfile
                            inventoryFiringProfile.inventory_id → inventory.id (ON DELETE CASCADE, UNIQUE)

Soft references (string ids inside JSON blobs — no FK enforcement):
  Show.display_payload[].zone   → Receivers.id  /  show_receivers[].id
  Show.show_receivers[].id      → Receivers.id  (native entries only)
  racks.cells["x_y"].shellId    → inventory.id  (an AERIAL_SHELL row)
  racks.cells["x_y"].shellNumber→ inventory.metadata.pack_shell_data.shells[n-1]
  racks.cells["x_y"].fuseId     → racks.fuses key (rack-local)
  racks.fuses[fuseId].type      → inventory.id  (a FUSE row, stored as a string)

Soft references are not enforced by SQLite — deleting a referenced inventory item leaves a dangling id that the UI renders as a "deleted item".


Show

The complete description of one firework show.

CREATE TABLE IF NOT EXISTS Show (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  name TEXT NOT NULL,
  duration INTEGER NOT NULL CHECK(duration >= 0),
  version TEXT NOT NULL,
  runtime_version TEXT NOT NULL,
  display_payload TEXT NOT NULL,   -- JSON (large)
  runtime_payload TEXT NOT NULL,   -- JSON (large)
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP NOT NULL,
  authorization_code TEXT NOT NULL,
  protocol TEXT,
  audio_file TEXT,                 -- JSON
  receiver_locations TEXT,         -- JSON
  receiver_labels TEXT,            -- JSON (added via migration)
  show_receivers TEXT              -- JSON (added via migration)
);
Column Type Purpose
id INTEGER PK AUTOINCREMENT
name TEXT NOT NULL Display name.
duration INTEGER NOT NULL Show length in seconds (>= 0).
version TEXT NOT NULL Show schema version (UI-managed).
runtime_version TEXT NOT NULL Daemon's preferred runtime version.
display_payload TEXT NOT NULL JSON timeline as the editor sees it. → shape
runtime_payload TEXT NOT NULL JSON firing array the daemon executes (compiled at load). → shape
created_at TIMESTAMP
authorization_code TEXT NOT NULL "Did you really mean to load THIS show" confirmation code.
protocol TEXT Target protocol, e.g. BKYD_TS_HYBRID.
audio_file TEXT JSON audio tracks + sync offset. → shape
receiver_locations TEXT JSON per-receiver lat/lng + map viewport. → shape
receiver_labels TEXT JSON receiver id → label (legacy duplicate of show_receivers[].label). → shape
show_receivers TEXT JSON per-show receiver list. → shape

inventory

Each pyro product known to the system.

CREATE TABLE IF NOT EXISTS inventory (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  name TEXT NOT NULL,
  type TEXT NOT NULL,
  duration REAL CHECK(duration >= 0),
  fuse_delay REAL CHECK(fuse_delay >= 0),
  lift_delay REAL CHECK(lift_delay >= 0),
  burn_rate REAL CHECK(burn_rate >= 0),
  color TEXT,
  available_ct INTEGER DEFAULT 0,
  youtube_link TEXT,
  image TEXT,
  youtube_link_start_sec INTEGER,
  metadata TEXT,                                   -- JSON (added via migration)
  source TEXT DEFAULT 'user_created',              -- (added via migration)
  unit_cost REAL CHECK(unit_cost IS NULL OR unit_cost >= 0)  -- (added via migration)
);
Column Type Purpose
id INTEGER PK AUTOINCREMENT
name TEXT NOT NULL
type TEXT NOT NULL E.g. CAKE_200G, CAKE_500G, CAKE_FOUNTAIN, AERIAL_SHELL, FUSE. No CHECK — new types need no migration.
duration REAL Visible burst length (s).
fuse_delay REAL Fuse burn time (s).
lift_delay REAL Cue-fire → shell-break time (aerial).
burn_rate REAL For FUSE: seconds per foot.
color TEXT Comma-separated colors (e.g. "red,gold").
available_ct INTEGER DEFAULT 0 On-hand count.
youtube_link TEXT Reference video.
image TEXT Image URL/relative path.
youtube_link_start_sec INTEGER Where in the video the product fires.
metadata TEXT JSON extras — shell-pack data for AERIAL_SHELL. → shape
source TEXT DEFAULT 'user_created' user_created or catalog import.
unit_cost REAL Price per unit.

A legacy type IN ('CAKE_FOUNTAIN', …) CHECK exists on old DBs. migrateInventoryRemoveLegacyTypeCheck() rebuilds the table without it on boot; new DBs never have it.


inventoryFiringProfile

YouTube-derived shot timing for an inventory item (one per item).

CREATE TABLE IF NOT EXISTS inventoryFiringProfile (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  inventory_id INTEGER NOT NULL,
  youtube_link TEXT NOT NULL,
  youtube_link_start_sec INTEGER NOT NULL,
  shot_timestamps TEXT NOT NULL,   -- JSON
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP NOT NULL,
  FOREIGN KEY (inventory_id) REFERENCES inventory(id) ON DELETE CASCADE,
  UNIQUE(inventory_id)
);
Column Type Purpose
id INTEGER PK AUTOINCREMENT
inventory_id INTEGER NOT NULL FK → inventory.id, ON DELETE CASCADE, UNIQUE.
youtube_link TEXT NOT NULL Source video.
youtube_link_start_sec INTEGER NOT NULL Analysis origin (the 0 ms of the timestamps).
shot_timestamps TEXT NOT NULL JSON array of shots. → shape
created_at TIMESTAMP

racks

Per-show physical mortar rack definitions.

CREATE TABLE IF NOT EXISTS racks (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  show_id INTEGER NOT NULL,
  name TEXT NOT NULL,
  x_rows INTEGER NOT NULL CHECK(x_rows > 0),
  x_spacing REAL NOT NULL CHECK(x_spacing >= 0),
  y_rows INTEGER NOT NULL CHECK(y_rows > 0),
  y_spacing REAL NOT NULL CHECK(y_spacing >= 0),
  cells TEXT NOT NULL,   -- JSON
  fuses TEXT NOT NULL,   -- JSON
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP NOT NULL,
  FOREIGN KEY (show_id) REFERENCES Show(id) ON DELETE CASCADE
);
Column Type Purpose
id INTEGER PK AUTOINCREMENT
show_id INTEGER NOT NULL FK → Show.id, ON DELETE CASCADE.
name TEXT NOT NULL
x_rows INTEGER NOT NULL Grid width (> 0).
x_spacing REAL NOT NULL Horizontal cell spacing (inches).
y_rows INTEGER NOT NULL Grid depth (> 0).
y_spacing REAL NOT NULL Vertical cell spacing (inches).
cells TEXT NOT NULL JSON map of populated cells. → shape
fuses TEXT NOT NULL JSON map of rack-local fuse runs. → shape
created_at TIMESTAMP

Receivers

Source of truth for the dongle's poll list. The id is the receiver ident string.

CREATE TABLE IF NOT EXISTS Receivers (
  id TEXT PRIMARY KEY NOT NULL,
  label TEXT NOT NULL,
  type TEXT NOT NULL,
  cues_data TEXT NOT NULL DEFAULT '{}',   -- JSON
  enabled INTEGER NOT NULL DEFAULT 1 CHECK(enabled IN (0,1)),
  metadata TEXT NOT NULL DEFAULT '{}',    -- JSON
  configuration_version INTEGER NOT NULL DEFAULT 1,
  fw_version INTEGER,        -- (added via migration)
  board_version INTEGER,     -- (added via migration)
  cues_available INTEGER,    -- (added via migration)
  config_data TEXT NOT NULL DEFAULT '{}', -- JSON (added via migration)
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP NOT NULL,
  updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP NOT NULL
);
Column Type Purpose
id TEXT PK NOT NULL Receiver ident, e.g. RX163.
label TEXT NOT NULL Human-readable label.
type TEXT NOT NULL E.g. BKYD_TS_24_1, BILUSOCN_433_TX_ONLY.
cues_data TEXT NOT NULL DEFAULT '{}' JSON zone → cue list. → shape
enabled INTEGER NOT NULL DEFAULT 1 0/1. Disabled receivers aren't added to the poll list.
metadata TEXT NOT NULL DEFAULT '{}' JSON extras (migration bag). → shape
configuration_version INTEGER NOT NULL DEFAULT 1 Bumped on every UPDATE so consumers can detect changes.
fw_version INTEGER Reported via RECEIVER_CONFIG_RESPONSE (FW v22+). NULL until first response.
board_version INTEGER Same.
cues_available INTEGER Receiver-reported usable cues (its own NUM_BOARDS detection).
config_data TEXT NOT NULL DEFAULT '{}' JSON writable per-receiver runtime config. → shape
created_at TIMESTAMP
updated_at TIMESTAMP Bumped on every UPDATE.

_hydrateReceiverRow() (sqldb.js) parses cues_data, metadata, and config_data to objects and converts enabled to a boolean before returning rows to callers.


JSON blob structures

Everything below documents the internal shape of TEXT columns that hold JSON. Field names, types, and examples are taken from the code that constructs and consumes them.

Show.display_payload

A JSON array of timeline cue objects (not wrapped in an object) — the show as the editor renders it. Built by compressItemsForSave() in ShowBuilder.jsx; only a whitelist of keys is persisted (SAVEABLE_ITEM_ATTRIBUTES). Inventory-derived fields (image, color, fuse_delay, …) are not stored — they're re-merged from the inventory table by itemId on load.

Fields common to all cue types

Field Type Meaning
id number Editor-assigned cue id (monotonic; not an inventory id).
startTime number Timeline position, in seconds, where the effect is scheduled.
zone string Receiver ident (native) or Bilusocn zone string (e.g. "12"). Matches show_receivers[].id.
target number 1-based cue number within zone.
type string Cue kind (see below).
name string Display label.
duration number Effect length (s) / timeline bar width.
delay number Total pre-fire delay (s): fuse + lift + meta combined. The daemon fires at startTime − delay.

Cue type variants

  • Inventory-backed single (CAKE_500G, AERIAL_SHELL, FUSE, …): adds itemId (→ inventory.id) and optional multiple (integer ≥ 2 when "fire multiple" is on).
  • GENERIC: placeholder/hand-fire cue; no itemId.
  • FUSED_AERIAL_LINE: a fused shell line. Adds spacing, leadInInches, fuse (full inventory FUSE object at author time), shells (array of full inventory shell objects).
  • FUSED_LINE: multi-step fused item line. Adds firstStepFuseDelay and steps[], where each step is { type, itemId, name, duration, fuseDelay, multiple, fusedShellLine? } (type is an inventory type or "FUSED_SHELL_LINE").
  • RACK_SHELLS: fired from a rack. Adds rackId, rackName, rackCells (["x_y", …]), rackSpacing ({ x, y }), fireableItemId, and fireableItem (a single or fused descriptor whose cellData[] entries mirror racks.cells and whose fuse.type is the fuse inventory id as a string).

Example

[
  {
    "id": 1,
    "startTime": 0.0,
    "itemId": 5,
    "zone": "RX163",
    "target": 1,
    "type": "CAKE_500G",
    "name": "Opening cake",
    "duration": 30,
    "delay": 0
  },
  {
    "id": 2,
    "startTime": 32.0,
    "itemId": 12,
    "zone": "12",
    "target": 4,
    "type": "AERIAL_SHELL",
    "name": "Finale shell",
    "duration": 2,
    "delay": 1.5
  }
]

Show.runtime_payload

The firing array the Python daemon executes. The JS editor saves this as "{}" (empty object); the daemon compiles the real array from display_payload at show-load time (process_display_payload in pc_daemon.py) and writes it back.

Each compiled entry:

Field Type Meaning
startTime number Actual fire time (s) = display startTime − delay. Array is sorted ascending by this.
zone string Receiver ident / Bilusocn zone.
target number 1-based cue number.
id number Source display cue id.

At load the protocol handler enriches each entry with type (receiver hardware type), device_id (resolved ident, e.g. RX163 or a synthesized __bilusocn_z12_5), and async_fire (true = preloaded to a native receiver, false = Bilusocn instant-TX at fire time).

[
  { "startTime": 0.0,  "zone": "RX163", "target": 1, "id": 1, "type": "BKYD_TS_24_1",        "device_id": "RX163",            "async_fire": true  },
  { "startTime": 30.5, "zone": "12",    "target": 4, "id": 2, "type": "BILUSOCN_433_TX_ONLY", "device_id": "__bilusocn_z12_5", "async_fire": false }
]

Until a show has been loaded by the daemon at least once, runtime_payload may still be "{}" in the DB.

Show.audio_file

JSON object of audio tracks + a show-level sync offset (audioTracks.js). NULL when the show has no audio.

{
  "tracks": [
    {
      "id": "t_m3abc_1",
      "url": "/uploads/audio/show_42_track1.mp3",
      "name": "finale.mp3",
      "size": 5242880,
      "type": "audio/mpeg",
      "lastModified": 1719234000000,
      "durationSec": 180.5,
      "bpm": 128,
      "firstBeatOffsetSec": 0.12,
      "beatsPerMeasure": 4,
      "bpmConfidence": 0.91,
      "bpmSource": "auto"
    }
  ],
  "audioOffsetMs": -150
}
Field Type Meaning
tracks[] array Audio tracks (usually one).
tracks[].id string Track id.
tracks[].url string | null Path or signed URL.
tracks[].name string Original filename.
tracks[].size number | null Bytes.
tracks[].type string | null MIME type.
tracks[].lastModified number | null File timestamp.
tracks[].durationSec number | null Length (s).
tracks[].bpm number | null Tempo.
tracks[].firstBeatOffsetSec number First downbeat offset (default 0).
tracks[].beatsPerMeasure number Default 4.
tracks[].bpmConfidence number | null Detection confidence.
tracks[].bpmSource string | null E.g. "auto".
audioOffsetMs number Whole-show music sync trim (ms). + = audio leads cue 0, − = audio delayed.

Legacy rows (single track object, raw array, or playbackOffsetMs) are normalized on read.

Show.receiver_locations

JSON map of receiver geo positions plus a _meta viewport (SpatialLayoutMap.jsx). NULL when empty.

{
  "_meta": { "lat": 42.3601, "lng": -71.0589, "zoom": 18, "address": "123 Main St, Boston, MA" },
  "RX163": { "lat": 42.36015, "lng": -71.05885 },
  "RX164": { "lat": 42.36008, "lng": -71.05892 }
}
Key Type Meaning
_meta object Map viewport: center lat/lng, zoom, optional address.
<receiverId> object Per-receiver { lat, lng } (WGS84 degrees).

Legacy rows may instead hold { x, y } pixel coordinates from an older layout; the spatial map re-seeds missing lat/lng.

Show.receiver_labels

Flat JSON map of receiver id → display label. A legacy duplicate of show_receivers[].label, derived from show_receivers on save and kept for older consumers (ReceiverDisplay). NULL when empty.

{ "RX163": "Front left", "RX164": "Back row", "12": "Bilusocn zone 12" }

Show.show_receivers

JSON array describing the per-show receiver target grid (showReceivers.js). NULL when the show has no receivers.

Field Type Req Meaning
id string yes Native: DB receiver ident ("RX163"). Bilusocn: zone number string ("12"). Matches display_payload[].zone.
kind "native" | "bilusocn" implicit Defaults to "native" if omitted.
cues number yes Native: 8–64 in steps of 8. Bilusocn: always 12.
label string optional Display override.
[
  { "id": "RX163", "kind": "native", "cues": 32, "label": "Main rack" },
  { "id": "RX164", "kind": "native", "cues": 16 },
  { "id": "3", "kind": "bilusocn", "cues": 12, "label": "433MHz zone 3" }
]

Native entries must reference a row in the global Receivers table. Bilusocn entries have no DB row — the daemon synthesizes ephemeral 4-cue shadow devices (__bilusocn_z<zone>_1/_5/_9) at load. Shows predating this column are back-filled from display_payload + receiver_labels.

inventory.metadata

JSON extras. The only sub-key written by current code is pack_shell_data (for AERIAL_SHELL items edited in the Shell Pack Editor). Cakes/fountains store nothing here. NULL for most items.

{
  "pack_shell_data": {
    "shells": [
      { "number": 1, "description": "Red peony with glitter", "colors": ["#FF0000", "#FFD700"], "effects": ["PEONY", "GLITTER"] },
      { "number": 2, "description": "Blue chrysanthemum",      "colors": ["#0000FF"],            "effects": ["CHRYSANTHEMUM"] }
    ]
  }
}
Field Type Meaning
pack_shell_data.shells[] array 1–24 entries describing each shell in a pack.
shells[].number number 1-based index within the pack (rewritten sequentially on save).
shells[].description string Free-text label.
shells[].colors string[] Hex colors.
shells[].effects string[] Effect enum strings (PEONY, GLITTER, WHISTLING, …).

A cell in racks.cells points into this array via shellNumber (shells[shellNumber − 1]).

inventoryFiringProfile.shot_timestamps

JSON array of shots, each a 3-tuple [start_ms, end_ms, color]. Times are milliseconds from youtube_link_start_sec. A legacy 2-tuple [start_ms, end_ms] is still accepted and upgraded to color: null on read. The Python generator (process_firing_profiles.py) always writes null for color; colors are only assigned later in the UI shot editor.

[
  [100, 250, null],
  [500, 700, "#ff0000"],
  [1200, 1450, "#0000ff"]
]
Element Type Meaning
[0] number Shot start (ms).
[1] number Shot end (ms), >= start.
[2] string | null Optional hex color (decorative; not used for firing timing).

racks.cells

JSON object keyed by "x_y" grid coordinate (0-based). Empty cells are absent (not stored as null).

{
  "0_0": { "shellId": 42, "shellNumber": 3,    "fuseId": "fuse_1719234567890" },
  "1_0": { "shellId": 42, "shellNumber": 4,    "fuseId": "fuse_1719234567890" },
  "2_0": { "shellId": 42, "shellNumber": null, "fuseId": null }
}
Field Type Meaning
key "x_y" string Cell coordinate, e.g. "0_0", "2_1".
shellId number inventory.id of an AERIAL_SHELL row.
shellNumber number | null 1-based index into that pack's pack_shell_data.shells; null = "any" shell from the pack.
fuseId string | null Key into this rack's fuses map (rack-local id, not an inventory id).

racks.fuses

JSON object keyed by a rack-local fuse id (generated as `fuse_${Date.now()}`).

{
  "fuse_1719234567890": { "type": "17", "leadIn": 2.5, "cells": ["0_0", "1_0", "1_1"] }
}
Field Type Meaning
key string Rack-local fuse id (referenced by cells[].fuseId).
type string inventory.id of a FUSE row, stored as a string (resolved with parseInt).
leadIn number Lead-in length (inches).
cells string[] Ordered "x_y" cells the fuse runs through (click order; used for burn-delay math and drawing).

Receivers.cues_data

JSON map of zone name → array of positive integer cue positions the receiver owns. Defaults to {}. The daemon resolves a show cue with target in cues[zone].

  • Standard BYH receivers ("id-as-zone"): key is the receiver ident, value a contiguous list.
    { "RX163": [1, 2, 3, 4, 5, 6, 7, 8] }
  • Legacy Bilusocn rows (BILUSOCN_433_TX_ONLY): key is the dipswitch zone number string, value 4 consecutive cues.
    { "12": [5, 6, 7, 8] }

(Validated as object-of-arrays of positive integers in api/receivers/[id].js.)

Receivers.metadata

JSON free-form bag, defaults to {}. In practice only populated by the legacy systemcfg.json seed (any keys on a receiver entry other than label/type/cues, e.g. { "location": "stage-left" }). New receivers get {}; no current UI writes specific keys and no code reads specific keys. Treat as migration/operator storage.

Receivers.config_data

JSON writable per-receiver runtime config, defaults to {}. Two keys are used today:

{ "fire_duration_ms": 250, "force_cues_available": 16 }
Field Type Meaning
fire_duration_ms number Fire pulse width. Integer [50, 5000]. Mirrored back from the receiver after rxcfg so it survives host restarts.
force_cues_available number Operator override of cue count. Integer 0..256; 0/absent = don't force (use receiver-reported cues_available).

The PATCH API replaces the whole config_data object — callers must merge client-side first. The daemon's rxcfg persist path merges instead, so host overrides survive.


See also

Schema source of truth: host/byh_app/backyardhero/src/util/sqldb.js. The Python generators live under host/pythings/. If you change the schema, update this page.

Clone this wiki locally