Subsystem Database

Subsystem: Database (SQLite)

Backyard Hero stores all persistent data in a single SQLite file at /data/backyardhero.db (mounted from the host as host/data/backyardhero.db). It's accessed through better-sqlite3 from the Next.js app — host/byh_app/backyardhero/src/util/sqldb.js is the only module that opens it.

The Python daemon also reads from it (via sqlite3 stdlib) for show data and receiver definitions, but the schema is owned by sqldb.js.

When and how it gets created

sqldb.js is imported at Next.js boot. It runs initializeDatabase() which:

1. Creates each table with CREATE TABLE IF NOT EXISTS.
2. Runs idempotent ALTER TABLE ADD COLUMN migrations (catches "duplicate column name" errors and ignores them).
3. Runs migrateInventoryRemoveLegacyTypeCheck() if the inventory table still has the old type IN (...) CHECK constraint (rebuilds the table without it).
4. Runs seedReceiversFromSystemCfgIfEmpty() — if the Receivers table is empty, parses the legacy receivers block from /config/systemcfg.json and inserts a row per receiver. Migration path for existing installs.

There is no separate migration runner. Schema changes are made by editing sqldb.js and shipping it.

Tables

For the full DDL, every column, and a field-by-field breakdown of the JSON stored inside the TEXT blob columns (display_payload, runtime_payload, cells, fuses, cues_data, etc.), see Database schema.

Show

The complete description of one firework show.

Column	Type	Purpose
id	INTEGER PRIMARY KEY AUTOINCREMENT
name	TEXT NOT NULL	Display name.
duration	INTEGER NOT NULL	Show length in seconds.
version	TEXT NOT NULL	Show schema version (UI-managed).
runtime_version	TEXT NOT NULL	Daemon's preferred version.
display_payload	TEXT NOT NULL	JSON: the timeline as the editor sees it (compressed).
runtime_payload	TEXT NOT NULL	JSON: the firing array the daemon executes. Generated from display_payload at load time and cached here.
created_at	TIMESTAMP DEFAULT CURRENT_TIMESTAMP
authorization_code	TEXT NOT NULL	The "did you really mean to load THIS show" password.
protocol	TEXT	Which protocol the show targets, e.g. BKYD_TS_HYBRID.
audio_file	TEXT	JSON: per-track audio file paths + offset.
receiver_locations	TEXT	JSON: lat/lng for each receiver, plus map view metadata.
receiver_labels	TEXT	JSON: receiver id → display label (legacy parallel map; kept in sync from show_receivers).
show_receivers	TEXT	JSON: [{ id, label?, cues }, ...] — the per-show receiver target grid.

inventory

Each pyro product known to the system. Cakes, single shells, fountains, fuses, etc.

Column	Type	Purpose
id	INTEGER PRIMARY KEY AUTOINCREMENT
name	TEXT NOT NULL
type	TEXT NOT NULL	E.g. CAKE_200G, CAKE_500G, CAKE_FOUNTAIN, SHELL, FUSE, etc. (No CHECK constraint; new types can be added without migration.)
duration	REAL	Visible burst length in seconds.
fuse_delay	REAL	Fuse burn time (seconds).
lift_delay	REAL	Time between cue fire and shell breaking (for aerial shells).
burn_rate	REAL	For FUSE type: seconds per foot.
color	TEXT	Comma-separated colors (e.g. "red,gold").
available_ct	INTEGER DEFAULT 0	How many you have on hand.
youtube_link	TEXT	Optional reference video.
image	TEXT	Optional image URL or relative path.
youtube_link_start_sec	INTEGER	Where in the video the product fires.
metadata	TEXT	JSON: any other fields (notably, shells: [...] for shell packs and cakes).
source	TEXT DEFAULT 'user_created'	user_created or catalog (for catalog imports).
unit_cost	REAL	Price per unit.

inventoryFiringProfile

YouTube-derived shot timing data for an inventory item.

Column	Type	Purpose
id	INTEGER PRIMARY KEY AUTOINCREMENT
inventory_id	INTEGER NOT NULL UNIQUE	FK to inventory.id, ON DELETE CASCADE.
youtube_link	TEXT NOT NULL	The video the profile was derived from.
youtube_link_start_sec	INTEGER NOT NULL	Where in the video to start analysis.
shot_timestamps	TEXT NOT NULL	JSON: [[start_ms, end_ms], [start_ms, end_ms, "#hexcolor"], ...]
created_at	TIMESTAMP DEFAULT CURRENT_TIMESTAMP

UNIQUE(inventory_id) — one profile per item.

racks

Per-show rack definitions. Each row describes one physical mortar rack.

Column	Type	Purpose
id	INTEGER PRIMARY KEY AUTOINCREMENT
show_id	INTEGER NOT NULL	FK to Show.id, ON DELETE CASCADE.
name	TEXT NOT NULL
x_rows	INTEGER NOT NULL	Width of rack grid.
x_spacing	REAL NOT NULL	Inches between cells horizontally.
y_rows	INTEGER NOT NULL	Depth of rack grid.
y_spacing	REAL NOT NULL	Inches between cells vertically.
cells	TEXT NOT NULL	JSON: { "x_y": { "shellId": int, "shellNumber": int, "fuseId": string | null } }. Key is "<x>_<y>".
fuses	TEXT NOT NULL	JSON: { "fuseId": { "type": string, "leadIn": number, "cells": ["x_y", ...] } }.
created_at	TIMESTAMP DEFAULT CURRENT_TIMESTAMP

Receivers

Source of truth for the dongle's poll list.

Column	Type	Purpose
id	TEXT PRIMARY KEY NOT NULL	The receiver ident, e.g. RX146.
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: { "<zoneName>": [1, 2, 3, ...] } — what cues this receiver controls under what zone names.
enabled	INTEGER NOT NULL DEFAULT 1	0 or 1. Disabled receivers are not added to the dongle's poll list.
metadata	TEXT NOT NULL DEFAULT '{}'	JSON: any extra fields (e.g. UI annotations).
configuration_version	INTEGER NOT NULL DEFAULT 1	Bumped on every UPDATE so other components can detect changes.
fw_version	INTEGER	Reported by the receiver via RECEIVER_CONFIG_RESPONSE. NULL until first response.
board_version	INTEGER	Same.
cues_available	INTEGER	Reported by the receiver: physically usable cues per its NUM_BOARDS detection.
config_data	TEXT NOT NULL DEFAULT '{}'	JSON: writable per-receiver runtime config, e.g. { "fire_duration_ms": 250 }.
created_at	TIMESTAMP DEFAULT CURRENT_TIMESTAMP
updated_at	TIMESTAMP DEFAULT CURRENT_TIMESTAMP

Important relationships

• A show's show_receivers JSON references receiver ids by string (e.g. RX146). They don't have to exist in the Receivers table for the show to load — but they do for the daemon to actually fire anything to that receiver. The verification helpers (verifyShowReceivers) flag missing/disabled receivers as warnings (rack rendering and editing still work for design without the hardware).
• Racks reference inventory items by shellId. Deleting an inventory item that's referenced from a rack leaves the rack with a dangling reference (renders as "deleted item" in the UI).
• inventoryFiringProfile rows are tied to inventory rows via FK with ON DELETE CASCADE. Deleting an inventory item also deletes its firing profile.

Where each table is read/written

Table	Reader	Writer
Show	UI (Editor, Console), daemon (load_show)	UI (Editor save)
inventory	UI (Inventory, Editor), Python process_firing_profiles.py	UI
inventoryFiringProfile	UI	Python process_firing_profiles.py, UI (manual edits in ShotProfileModal)
racks	UI, daemon (only via show payload)	UI
Receivers	UI, daemon (load_initial_receiver_cfg, reload_receivers_from_db)	UI, daemon (writes back FW config from RECEIVER_CONFIG_RESPONSE)

Backups

There's no automatic backup. To snapshot:

sqlite3 host/data/backyardhero.db ".backup /tmp/backyardhero-$(date +%Y%m%d).db"

(Or just copy the file — SQLite is single-file and WAL mode isn't enabled by default, so a plain copy works as long as no transaction is in progress.)

To restore: replace /data/backyardhero.db and restart the container.

Schema evolution

If you need to add a column:

1. Add it to the CREATE TABLE statement in sqldb.js.
2. Wrap an ALTER TABLE ... ADD COLUMN in a try/catch the same way existing migrations do.
3. Restart Next.js. The migration runs once on every boot; subsequent boots no-op.

If you need to drop or modify a column, you have to recreate the table — see migrateInventoryRemoveLegacyTypeCheck() in sqldb.js for the pattern (CREATE shadow table, copy data, DROP original, RENAME shadow).