db.js
Rama main · réplica de lectura del repositorio original.
'use strict';
const { DatabaseSync: Database } = require('node:sqlite');
const crypto = require('crypto');
const { tokenInfo } = require('./scripts/rfc3161.js');
const { tokenSignature } = require('./scripts/rfc3161-verify.js');
const fs = require('fs');
const path = require('path');
const SESSION_TTL_MS = 7 * 24 * 60 * 60 * 1000; // 7 days — keep in sync with server.js cookie Max-Age
const MAX_SESSIONS_PER_USER = 10;
// ── Zone colours ─────────────────────────────────────────────
// The colour a zone gets when nobody has picked one. It is *data* — the seed
// writes it into the row, the operator may change it in Settings → Zones, and
// from then on the app shows theirs. So this constant is only ever the starting
// point, and changing it moves nothing on an installation that already exists.
// Migration 77 is what carries the change across, and only where the operator
// never expressed an opinion.
//
// The values are muted on purpose. The originals were the full-saturation
// Tailwind 500s, and two of the three could not be read: as the KPI strip's
// large bold number on white, #0ea5e9 measures 2.77:1 and #10b981 2.54:1,
// under the 3:1 that this repository holds large text to elsewhere
// (test/kpi-kontrast.test.js). These keep the same three hues — a zone stays
// recognisably violet, blue, green — at a third of the saturation, and land on
// 4.20 / 4.22 / 4.20:1. Matched deliberately: a set where one member measures
// twice its neighbours reads as one zone shouting.
const ZONE_SEED_COLOR = {
spawn: '#926bb6',
incubation: '#4d829b',
fruiting: '#438871',
contaminated: '#ef4444'
};
// What the seed used to write. Migration 77 treats a row still carrying one of
// these as "never touched" and upgrades it; anything else is a choice and is
// left alone.
const ZONE_LEGACY_COLOR = {
spawn: '#a855f7',
incubation: '#0ea5e9',
fruiting: '#10b981',
contaminated: '#ef4444'
};
// ── Date helpers ─────────────────────────────────────────────
// Lab day boundary = the server's local timezone midnight (a single physical lab,
// one timezone). KPI snapshots and "due today" comparisons should bucket events
// against this local day, not against UTC — otherwise a 23:00 Berlin event lands
// in the next UTC day and disappears from the wrong KPI bucket.
function localDayString(d = new Date()) {
const offsetMs = d.getTimezoneOffset() * 60_000;
return new Date(d.getTime() - offsetMs).toISOString().slice(0, 10);
}
// ── Schema ───────────────────────────────────────────────────
const SCHEMA = `
CREATE TABLE IF NOT EXISTS batches (
batch_id TEXT PRIMARY KEY,
species TEXT NOT NULL,
strain TEXT,
qty INTEGER NOT NULL,
days INTEGER NOT NULL,
sub_hardwood REAL DEFAULT 0,
sub_wheatbran REAL DEFAULT 0,
sub_rh REAL DEFAULT 0,
sub_gypsum INTEGER DEFAULT 0,
bag_kg REAL DEFAULT 3,
batch_type TEXT DEFAULT 'block',
source_id TEXT,
notes TEXT DEFAULT '',
created TEXT NOT NULL,
due TEXT NOT NULL,
grain_rh REAL DEFAULT 0
);
CREATE TABLE IF NOT EXISTS bags (
bag_id TEXT PRIMARY KEY,
batch_id TEXT NOT NULL REFERENCES batches(batch_id) ON DELETE CASCADE
);
CREATE INDEX IF NOT EXISTS idx_bags_batch ON bags(batch_id);
CREATE TABLE IF NOT EXISTS scan_log (
id INTEGER PRIMARY KEY AUTOINCREMENT,
time TEXT NOT NULL,
action TEXT NOT NULL,
batch TEXT,
bag TEXT,
"from" TEXT,
"to" TEXT,
species TEXT,
strain TEXT,
user_id INTEGER REFERENCES users(id) ON DELETE SET NULL,
client_uuid TEXT
);
CREATE INDEX IF NOT EXISTS idx_scanlog_time ON scan_log(time);
-- I-11 idempotency index is created by migration v39, not here: pre-v39
-- databases reach this SCHEMA block before migrations run, and CREATE TABLE
-- IF NOT EXISTS is a no-op for them (so client_uuid wouldn't exist yet).
-- See PR #382.
CREATE TABLE IF NOT EXISTS harvests (
id INTEGER PRIMARY KEY AUTOINCREMENT,
time TEXT NOT NULL,
batch TEXT,
bag TEXT,
species TEXT,
strain TEXT,
grams REAL NOT NULL,
flush INTEGER DEFAULT 1
);
CREATE INDEX IF NOT EXISTS idx_harvests_batch ON harvests(batch);
CREATE TABLE IF NOT EXISTS cultures (
id TEXT PRIMARY KEY,
type TEXT NOT NULL,
species TEXT,
strain TEXT,
parent_id TEXT,
source TEXT,
status TEXT DEFAULT 'active',
notes TEXT DEFAULT '',
created TEXT NOT NULL
);
CREATE TABLE IF NOT EXISTS manual_tasks (
id INTEGER PRIMARY KEY AUTOINCREMENT,
text TEXT NOT NULL,
priority TEXT DEFAULT 'med',
done INTEGER DEFAULT 0,
created TEXT NOT NULL,
assignee TEXT,
due_date TEXT,
due_time TEXT,
due_end_time TEXT,
description TEXT,
caldav_uid TEXT,
caldav_synced TEXT,
private INTEGER DEFAULT 0,
recurrence TEXT,
recurrence_until TEXT,
-- I-15: SEQUENCE counter for VTODO output (RFC 5545 §3.8.7.4). Bumped on
-- every update so external CalDAV clients can detect changes.
sequence INTEGER NOT NULL DEFAULT 0
);
CREATE TABLE IF NOT EXISTS team_members (
id INTEGER PRIMARY KEY AUTOINCREMENT,
name TEXT NOT NULL UNIQUE,
role TEXT,
added TEXT NOT NULL
);
CREATE TABLE IF NOT EXISTS inventory (
id INTEGER PRIMARY KEY CHECK (id = 1),
stock_hardwood REAL DEFAULT 0,
stock_wheatbran REAL DEFAULT 0,
stock_gypsum REAL DEFAULT 0,
stock_grain REAL DEFAULT 0,
thresh_hardwood REAL DEFAULT 50,
thresh_wheatbran REAL DEFAULT 20,
thresh_gypsum REAL DEFAULT 5,
thresh_grain REAL DEFAULT 10,
avg_hw_pct REAL DEFAULT 75,
avg_wb_pct REAL DEFAULT 25,
avg_rh_pct REAL DEFAULT 63,
avg_bag_kg REAL DEFAULT 3,
avg_grain_bag_kg REAL DEFAULT 1,
avg_grain_rh_pct REAL DEFAULT 52
);
CREATE TABLE IF NOT EXISTS inventory_log (
id INTEGER PRIMARY KEY AUTOINCREMENT,
time TEXT NOT NULL,
mat TEXT NOT NULL,
delta_kg REAL NOT NULL,
running REAL DEFAULT 0,
type TEXT,
ref TEXT,
user_id INTEGER REFERENCES users(id) ON DELETE SET NULL
);
CREATE INDEX IF NOT EXISTS idx_invlog_time ON inventory_log(time);
CREATE TABLE IF NOT EXISTS suppliers (
id INTEGER PRIMARY KEY AUTOINCREMENT,
mat TEXT NOT NULL,
name TEXT NOT NULL,
url TEXT,
phone TEXT,
notes TEXT
);
CREATE TABLE IF NOT EXISTS caldav_config (
id INTEGER PRIMARY KEY CHECK (id = 1),
enabled INTEGER DEFAULT 0,
per_person_calendars INTEGER DEFAULT 0
);
CREATE TABLE IF NOT EXISTS duckdns_config (
id INTEGER PRIMARY KEY CHECK (id = 1),
enabled INTEGER DEFAULT 0,
domain TEXT DEFAULT '',
token TEXT DEFAULT '',
last_ip_update TEXT,
last_ip TEXT,
fallback_last TEXT,
le_enabled INTEGER DEFAULT 0,
le_last_renewal TEXT,
le_expiry TEXT
);
CREATE TABLE IF NOT EXISTS print_bridge_config (
id INTEGER PRIMARY KEY CHECK (id = 1),
enabled INTEGER DEFAULT 0,
url TEXT DEFAULT '',
token TEXT DEFAULT ''
);
CREATE TABLE IF NOT EXISTS harvest_feed_config (
id INTEGER PRIMARY KEY CHECK (id = 1),
enabled INTEGER DEFAULT 0,
url TEXT DEFAULT '',
secret TEXT DEFAULT '',
interval_min INTEGER DEFAULT 15,
fresh_days INTEGER DEFAULT 3,
planned_days INTEGER DEFAULT 28,
lead_days INTEGER DEFAULT 0,
strain INTEGER DEFAULT 1,
site TEXT DEFAULT '',
last_at TEXT,
last_ok INTEGER,
last_error TEXT,
release_mode INTEGER DEFAULT 0,
-- The portions a release is handed out in, in grams: "250,500,1000". One
-- ladder for the whole farm, not one per species — see migration v62.
pack_sizes TEXT DEFAULT ''
);
-- How much of a harvest the shop may sell, per species. See migration v55: the
-- feed otherwise reports what was harvested, which stops being the truth the
-- moment anything is sold anywhere else.
CREATE TABLE IF NOT EXISTS harvest_release (
species TEXT PRIMARY KEY,
grams REAL NOT NULL DEFAULT 0,
valid_until TEXT,
note TEXT DEFAULT '',
updated TEXT NOT NULL
);
-- Pickups the receiver of the harvest feed reported back. See migration v60.
-- The id is the receiver's own key, and the whole reason this is an upsert: the
-- same pickup arrives in every reply until we confirm it.
CREATE TABLE IF NOT EXISTS pickups (
id TEXT PRIMARY KEY,
order_ref TEXT,
slot TEXT,
slot_text TEXT,
place TEXT,
from_time TEXT,
to_time TEXT,
tz TEXT,
items TEXT,
overbooked INTEGER NOT NULL DEFAULT 0,
received TEXT NOT NULL,
updated TEXT NOT NULL,
acked_at TEXT
);
CREATE INDEX IF NOT EXISTS idx_pickups_open ON pickups(acked_at);
CREATE INDEX IF NOT EXISTS idx_pickups_from ON pickups(from_time);
-- Pickups the receiver has withdrawn. See migration v61: the pickup row is
-- deleted, and this is the receipt that keeps the withdrawal confirmable.
CREATE TABLE IF NOT EXISTS pickup_cancellations (
id TEXT PRIMARY KEY,
at TEXT NOT NULL,
acked_at TEXT
);
CREATE INDEX IF NOT EXISTS idx_pickup_cancel_open ON pickup_cancellations(acked_at);
-- Places goods are handed over: the hall, a market stall, a second site. See
-- migration v63. Typing the place into an event title works until the second
-- spelling shows up, and from then on the same market exists twice with nothing
-- to correct centrally — which matters more than usual here, because the name
-- leaves the building on the harvest feed and a receiver matches it literally.
-- Retired via the active flag, never DELETEd: an old location is still named by
-- past pickups, and removing the row would blank the place on events that
-- already happened there.
CREATE TABLE IF NOT EXISTS pickup_locations (
id INTEGER PRIMARY KEY AUTOINCREMENT,
name TEXT NOT NULL,
address TEXT,
sort_order INTEGER NOT NULL DEFAULT 0,
active INTEGER NOT NULL DEFAULT 1
);
-- Camera dashboard (admin-only WIP). The Python mushroom_camera module
-- owns the camera_measurements / snapshots / flags / labels tables and
-- creates them in its own ensure_schema(); the two below are also created
-- here because the Node-side dashboard reads them before the Python module
-- has run for the first time on a fresh install.
CREATE TABLE IF NOT EXISTS camera_cameras (
id INTEGER PRIMARY KEY AUTOINCREMENT,
name TEXT NOT NULL,
rtsp_url TEXT NOT NULL,
zone_id TEXT REFERENCES zones(id) ON DELETE SET NULL,
enabled INTEGER DEFAULT 1,
created TEXT NOT NULL
);
CREATE TABLE IF NOT EXISTS camera_calibration (
id INTEGER PRIMARY KEY CHECK (id = 1),
px_per_mm REAL DEFAULT 2.0,
incubation_bag_radius_px INTEGER DEFAULT 150,
qr_assign_radius_px INTEGER DEFAULT 400,
yolo_conf_threshold REAL DEFAULT 0.4,
pin_max_area_ratio REAL DEFAULT 0.04,
harvest_growth_threshold_pct REAL DEFAULT 2.0,
harvest_stall_readings INTEGER DEFAULT 3,
colonisation_score_threshold REAL DEFAULT 0.85,
colonisation_min_fraction REAL DEFAULT 0.70,
unseen_bag_alert_hours INTEGER DEFAULT 24,
contam_conf_threshold REAL DEFAULT 0.75,
updated_at TEXT
);
CREATE TABLE IF NOT EXISTS calendar_events (
id TEXT PRIMARY KEY,
title TEXT NOT NULL,
description TEXT,
start_date TEXT NOT NULL,
end_date TEXT,
all_day INTEGER DEFAULT 1,
start_time TEXT,
end_time TEXT,
category TEXT DEFAULT 'custom',
color TEXT,
caldav_uid TEXT,
caldav_synced TEXT,
created TEXT NOT NULL,
recurrence TEXT,
recurrence_until TEXT,
team_assignees TEXT,
exception_dates TEXT,
-- I-15: SEQUENCE counter for VEVENT output (RFC 5545 §3.8.7.4). Bumped on
-- every update so external CalDAV clients can detect changes.
sequence INTEGER NOT NULL DEFAULT 0,
-- Where the appointment happens, when that is a place people come to. See
-- migration v63. Nullable and normally null: most appointments are in the lab
-- and the field stays out of the way.
location_id INTEGER REFERENCES pickup_locations(id) ON DELETE SET NULL,
-- How many collection slots this window offers, null = uncapped. See
-- migration v64. A stall with a queue wants a number; a hall that is open all
-- afternoon does not.
pickup_capacity INTEGER
);
CREATE TABLE IF NOT EXISTS calendar_event_assignees (
event_id TEXT NOT NULL REFERENCES calendar_events(id) ON DELETE CASCADE,
user_id INTEGER NOT NULL REFERENCES users(id) ON DELETE CASCADE,
PRIMARY KEY (event_id, user_id)
);
CREATE TABLE IF NOT EXISTS users (
id INTEGER PRIMARY KEY AUTOINCREMENT,
username TEXT NOT NULL UNIQUE,
hash TEXT NOT NULL,
salt TEXT NOT NULL,
role TEXT DEFAULT 'user',
created TEXT NOT NULL
);
CREATE TABLE IF NOT EXISTS sessions (
token TEXT PRIMARY KEY,
user_id INTEGER NOT NULL REFERENCES users(id) ON DELETE CASCADE,
created TEXT NOT NULL,
expires TEXT NOT NULL
);
CREATE INDEX IF NOT EXISTS idx_sessions_expires ON sessions(expires);
CREATE TABLE IF NOT EXISTS notifications (
id INTEGER PRIMARY KEY AUTOINCREMENT,
user_id INTEGER NOT NULL REFERENCES users(id) ON DELETE CASCADE,
type TEXT NOT NULL,
title TEXT NOT NULL,
body TEXT,
link_type TEXT,
link_id TEXT,
created TEXT NOT NULL,
read INTEGER NOT NULL DEFAULT 0
);
CREATE INDEX IF NOT EXISTS idx_notif_user ON notifications(user_id, read, created DESC);
CREATE TABLE IF NOT EXISTS experiments (
id INTEGER PRIMARY KEY AUTOINCREMENT,
code TEXT NOT NULL UNIQUE,
title TEXT NOT NULL,
started TEXT NOT NULL,
ended TEXT,
status TEXT NOT NULL DEFAULT 'running',
archived INTEGER NOT NULL DEFAULT 0,
recipe TEXT DEFAULT '',
outcome TEXT DEFAULT ''
);
CREATE TABLE IF NOT EXISTS experiment_runs (
id INTEGER PRIMARY KEY AUTOINCREMENT,
experiment_id INTEGER NOT NULL REFERENCES experiments(id) ON DELETE CASCADE,
code TEXT NOT NULL,
what TEXT DEFAULT '',
result TEXT DEFAULT '',
outcome TEXT NOT NULL DEFAULT 'open',
culture_id TEXT REFERENCES cultures(id) ON DELETE SET NULL,
batch_id TEXT REFERENCES batches(batch_id) ON DELETE SET NULL,
sort INTEGER NOT NULL DEFAULT 0
);
CREATE INDEX IF NOT EXISTS idx_exp_runs ON experiment_runs(experiment_id, sort);
CREATE TABLE IF NOT EXISTS experiment_photos (
id INTEGER PRIMARY KEY AUTOINCREMENT,
experiment_id INTEGER NOT NULL REFERENCES experiments(id) ON DELETE CASCADE,
run_id INTEGER REFERENCES experiment_runs(id) ON DELETE SET NULL,
uuid TEXT NOT NULL UNIQUE,
rel_path TEXT NOT NULL,
thumb_path TEXT NOT NULL,
width INTEGER,
height INTEGER,
bytes INTEGER NOT NULL,
sha256 TEXT NOT NULL,
uploaded_at TEXT NOT NULL,
uploaded_by INTEGER REFERENCES users(id) ON DELETE SET NULL
);
CREATE INDEX IF NOT EXISTS idx_exp_photos ON experiment_photos(experiment_id, uploaded_at);
CREATE TABLE IF NOT EXISTS project_notes (
id INTEGER PRIMARY KEY AUTOINCREMENT,
tab TEXT NOT NULL,
body TEXT NOT NULL,
created TEXT NOT NULL,
updated TEXT
);
CREATE INDEX IF NOT EXISTS idx_project_notes ON project_notes(tab, id DESC);
CREATE TABLE IF NOT EXISTS project_docs (
tab TEXT PRIMARY KEY,
doc TEXT NOT NULL,
updated TEXT NOT NULL,
updated_by INTEGER REFERENCES users(id) ON DELETE SET NULL
);
-- Who changed what in experiments, runs and photos. See migration v89:
-- append-only, and without foreign keys on purpose.
CREATE TABLE IF NOT EXISTS lab_history (
id INTEGER PRIMARY KEY AUTOINCREMENT,
at TEXT NOT NULL,
user_id INTEGER,
username TEXT NOT NULL DEFAULT '',
entity TEXT NOT NULL,
entity_id INTEGER NOT NULL,
experiment_id INTEGER,
action TEXT NOT NULL,
field TEXT,
old_value TEXT,
new_value TEXT
);
CREATE INDEX IF NOT EXISTS idx_lab_history_exp ON lab_history(experiment_id, id);
CREATE TRIGGER IF NOT EXISTS lab_history_no_update BEFORE UPDATE ON lab_history
BEGIN
SELECT RAISE(ABORT, 'lab_history is append-only');
END;
CREATE TRIGGER IF NOT EXISTS lab_history_no_delete BEFORE DELETE ON lab_history
BEGIN
SELECT RAISE(ABORT, 'lab_history is append-only');
END;
-- Die Pruefkette ueber den Verlauf und die Zeitstempel auf ihr (v97). Beide
-- nur anhaengen, wie lab_history; die Trigger setzt openDb jedes Mal neu.
CREATE TABLE IF NOT EXISTS lab_history_chain (
history_id INTEGER PRIMARY KEY,
hash TEXT NOT NULL
);
CREATE TABLE IF NOT EXISTS lab_timestamps (
id INTEGER PRIMARY KEY AUTOINCREMENT,
history_id INTEGER NOT NULL,
hash TEXT NOT NULL,
tsa TEXT NOT NULL,
requested TEXT NOT NULL,
gen_time TEXT NOT NULL,
serial TEXT NOT NULL,
token BLOB NOT NULL
);
-- The work packages of a project; an experiment can belong to one
-- (experiments.work_package_id, migration v93). Hidden, never deleted.
CREATE TABLE IF NOT EXISTS work_packages (
id INTEGER PRIMARY KEY AUTOINCREMENT,
code TEXT NOT NULL,
title TEXT NOT NULL,
archived INTEGER NOT NULL DEFAULT 0,
sort INTEGER NOT NULL DEFAULT 0,
created TEXT NOT NULL
);
CREATE UNIQUE INDEX IF NOT EXISTS idx_work_packages_code ON work_packages(code COLLATE NOCASE);
CREATE TABLE IF NOT EXISTS meta (
key TEXT PRIMARY KEY,
value TEXT
);
`;
// ── Schema Migrations ───────────────────────────────────────
// Each migration runs exactly once, tracked by schema_version.
// To add a new migration: append an entry to MIGRATIONS array.
const MIGRATIONS = [
// v1: baseline — all tables already created via SCHEMA DDL
{
version: 2,
description: 'Add private flag to manual_tasks for CalDAV visibility',
fn(db) {
const has = db.prepare("SELECT COUNT(*) as c FROM pragma_table_info('manual_tasks') WHERE name='private'").get();
if (!has.c) db.exec('ALTER TABLE manual_tasks ADD COLUMN private INTEGER DEFAULT 0');
}
},
{
version: 3,
description: 'Add user_id to scan_log for user tracking',
fn(db) {
const has = db.prepare("SELECT COUNT(*) as c FROM pragma_table_info('scan_log') WHERE name='user_id'").get();
if (!has.c) db.exec('ALTER TABLE scan_log ADD COLUMN user_id INTEGER REFERENCES users(id) ON DELETE SET NULL');
}
},
{
version: 4,
description: 'Add calendar_event_assignees junction table',
fn(db) {
db.exec(`CREATE TABLE IF NOT EXISTS calendar_event_assignees (
event_id TEXT NOT NULL REFERENCES calendar_events(id) ON DELETE CASCADE,
user_id INTEGER NOT NULL REFERENCES users(id) ON DELETE CASCADE,
PRIMARY KEY (event_id, user_id)
)`);
}
},
{
version: 5,
description: 'Add performance indexes for multi-user workloads',
fn(db) {
db.exec(`
CREATE INDEX IF NOT EXISTS idx_scanlog_batch ON scan_log(batch);
CREATE INDEX IF NOT EXISTS idx_scanlog_bag ON scan_log(bag);
CREATE INDEX IF NOT EXISTS idx_scanlog_user ON scan_log(user_id);
CREATE INDEX IF NOT EXISTS idx_harvests_bag ON harvests(bag);
CREATE INDEX IF NOT EXISTS idx_cultures_parent ON cultures(parent_id);
CREATE INDEX IF NOT EXISTS idx_tasks_assignee ON manual_tasks(assignee);
CREATE INDEX IF NOT EXISTS idx_tasks_due ON manual_tasks(due_date);
CREATE INDEX IF NOT EXISTS idx_calevents_start ON calendar_events(start_date);
CREATE INDEX IF NOT EXISTS idx_calassign_user ON calendar_event_assignees(user_id);
`);
}
},
{
version: 6,
description: 'Add unique constraints on caldav_uid columns',
fn(db) {
db.exec(`
CREATE UNIQUE INDEX IF NOT EXISTS idx_tasks_caldav_uid ON manual_tasks(caldav_uid) WHERE caldav_uid IS NOT NULL;
CREATE UNIQUE INDEX IF NOT EXISTS idx_calevents_caldav_uid ON calendar_events(caldav_uid) WHERE caldav_uid IS NOT NULL;
`);
}
},
{
version: 7,
description: 'Add zones and racks tables for dynamic location management',
fn(db) {
db.exec(`
CREATE TABLE IF NOT EXISTS zones (
id TEXT PRIMARY KEY,
name TEXT NOT NULL,
role TEXT NOT NULL,
color TEXT NOT NULL,
sort_order INTEGER DEFAULT 0,
created TEXT NOT NULL
);
CREATE TABLE IF NOT EXISTS racks (
id TEXT PRIMARY KEY,
zone_id TEXT NOT NULL REFERENCES zones(id) ON DELETE CASCADE,
sort_order INTEGER DEFAULT 0,
created TEXT NOT NULL
);
CREATE INDEX IF NOT EXISTS idx_racks_zone ON racks(zone_id);
`);
// Seed default zones. Names are stored in English; the UI maps the
// canonical IDs (SPAWN/INC/TENT1-3/CONTAM) to localised display
// names via KNOWN_ZONE_I18N in app.js. Operators who prefer custom
// names can rename them in Settings → Zones (the rename writes the
// chosen name to the row and the i18n shadowing falls away once
// the localised key no longer matches).
const now = new Date().toISOString();
const insZ = db.prepare('INSERT OR IGNORE INTO zones(id,name,role,color,sort_order,created) VALUES(?,?,?,?,?,?)');
insZ.run('SPAWN', 'Spawn Run', 'spawn', ZONE_SEED_COLOR.spawn, 1, now);
insZ.run('INC', 'Incubation', 'incubation', ZONE_SEED_COLOR.incubation, 2, now);
insZ.run('TENT1', 'Tent 1', 'fruiting', ZONE_SEED_COLOR.fruiting, 3, now);
insZ.run('TENT2', 'Tent 2', 'fruiting', ZONE_SEED_COLOR.fruiting, 4, now);
insZ.run('TENT3', 'Tent 3', 'fruiting', ZONE_SEED_COLOR.fruiting, 5, now);
insZ.run('CONTAM', 'Contamination', 'contaminated', '#ef4444', 99, now);
// Seed default racks
const insR = db.prepare('INSERT OR IGNORE INTO racks(id,zone_id,sort_order,created) VALUES(?,?,?,?)');
insR.run('SPAWN_R1', 'SPAWN', 1, now);
insR.run('SPAWN_R2', 'SPAWN', 2, now);
for (let i = 1; i <= 10; i++) insR.run('INC_R' + i, 'INC', i, now);
}
},
{
version: 8,
description: 'Add optional max_capacity to zones',
fn(db) {
db.exec('ALTER TABLE zones ADD COLUMN max_capacity INTEGER DEFAULT NULL');
}
},
{
version: 9,
description: "Add duckdns_config table for DuckDNS and Let's Encrypt",
fn(db) {
db.exec(`CREATE TABLE IF NOT EXISTS duckdns_config (
id INTEGER PRIMARY KEY CHECK (id = 1),
enabled INTEGER DEFAULT 0,
domain TEXT DEFAULT '',
token TEXT DEFAULT '',
last_ip_update TEXT,
last_ip TEXT,
le_enabled INTEGER DEFAULT 0,
le_last_renewal TEXT,
le_expiry TEXT
)`);
}
},
{
version: 10,
description: 'Enable CalDAV sync by default',
fn(db) {
db.prepare('UPDATE caldav_config SET enabled = 1 WHERE id = 1').run();
}
},
{
version: 11,
description: 'Add mcp_config table for MCP server settings',
fn(db) {
db.exec(`CREATE TABLE IF NOT EXISTS mcp_config (
id INTEGER PRIMARY KEY CHECK (id = 1),
enabled INTEGER DEFAULT 0,
api_token TEXT DEFAULT ''
)`);
}
},
{
version: 12,
description: 'Drop unused caldav_username/caldav_password columns (rebuild caldav_config)',
fn(db) {
const row = db.prepare('SELECT enabled, per_person_calendars FROM caldav_config WHERE id = 1').get();
db.exec('DROP TABLE IF EXISTS caldav_config');
db.exec(`CREATE TABLE caldav_config (
id INTEGER PRIMARY KEY CHECK (id = 1),
enabled INTEGER DEFAULT 0,
per_person_calendars INTEGER DEFAULT 0
)`);
if (row) {
db.prepare('INSERT INTO caldav_config (id, enabled, per_person_calendars) VALUES (1, ?, ?)').run(
row.enabled || 0,
row.per_person_calendars || 0
);
}
}
},
{
version: 13,
description: 'Add OAuth 2.0 tables for MCP authentication',
fn(db) {
db.exec(`CREATE TABLE IF NOT EXISTS oauth_clients (
client_id TEXT PRIMARY KEY,
client_name TEXT DEFAULT '',
redirect_uris TEXT NOT NULL DEFAULT '[]',
created TEXT NOT NULL DEFAULT (datetime('now'))
)`);
db.exec(`CREATE TABLE IF NOT EXISTS oauth_codes (
code TEXT PRIMARY KEY,
client_id TEXT NOT NULL,
user_id INTEGER NOT NULL,
redirect_uri TEXT NOT NULL,
code_challenge TEXT NOT NULL,
code_challenge_method TEXT NOT NULL DEFAULT 'S256',
expires TEXT NOT NULL,
used INTEGER DEFAULT 0
)`);
db.exec(`CREATE TABLE IF NOT EXISTS oauth_tokens (
token TEXT PRIMARY KEY,
token_type TEXT NOT NULL,
client_id TEXT NOT NULL,
user_id INTEGER NOT NULL,
expires TEXT NOT NULL,
revoked INTEGER DEFAULT 0,
created TEXT NOT NULL DEFAULT (datetime('now')),
refresh_token_ref TEXT
)`);
db.exec(`CREATE INDEX IF NOT EXISTS idx_oauth_tokens_expires ON oauth_tokens(expires)`);
}
},
{
version: 14,
description: 'Add client_secret_hash and revoked to oauth_clients for admin-managed OAuth',
fn(db) {
db.exec(`ALTER TABLE oauth_clients ADD COLUMN client_secret_hash TEXT DEFAULT NULL`);
db.exec(`ALTER TABLE oauth_clients ADD COLUMN revoked INTEGER DEFAULT 0`);
}
},
{
version: 15,
description: 'Add resource column to oauth_codes for RFC 8707 resource indicator support',
fn(db) {
db.exec(`ALTER TABLE oauth_codes ADD COLUMN resource TEXT DEFAULT ''`);
}
},
{
version: 16,
description: 'Add recurrence + team_assignees to calendar_events',
fn(db) {
const addCol = (col, def) => {
if (!/^[a-z_][a-z0-9_]*$/i.test(col)) throw new Error('invalid column name: ' + col);
const has = db
.prepare("SELECT COUNT(*) as c FROM pragma_table_info('calendar_events') WHERE name = ?")
.get(col);
if (!has.c) db.exec(`ALTER TABLE calendar_events ADD COLUMN ${col} ${def}`);
};
addCol('recurrence', 'TEXT');
addCol('recurrence_until', 'TEXT');
addCol('team_assignees', 'TEXT');
}
},
{
version: 17,
description: 'Add recurrence columns to manual_tasks',
fn(db) {
const addCol = (col, def) => {
if (!/^[a-z_][a-z0-9_]*$/i.test(col)) throw new Error('invalid column name: ' + col);
const has = db.prepare("SELECT COUNT(*) as c FROM pragma_table_info('manual_tasks') WHERE name = ?").get(col);
if (!has.c) db.exec(`ALTER TABLE manual_tasks ADD COLUMN ${col} ${def}`);
};
addCol('recurrence', 'TEXT');
addCol('recurrence_until', 'TEXT');
}
},
{
version: 18,
description: 'Add due_time/due_end_time to manual_tasks for time-slot scheduling',
fn(db) {
const addCol = (col, def) => {
if (!/^[a-z_][a-z0-9_]*$/i.test(col)) throw new Error('invalid column name: ' + col);
const has = db.prepare("SELECT COUNT(*) as c FROM pragma_table_info('manual_tasks') WHERE name = ?").get(col);
if (!has.c) db.exec(`ALTER TABLE manual_tasks ADD COLUMN ${col} ${def}`);
};
addCol('due_time', 'TEXT');
addCol('due_end_time', 'TEXT');
}
},
{
version: 19,
description: 'Add mushroom_strains table and migrate existing strain data',
fn(db) {
const now = new Date().toISOString();
db.exec(`CREATE TABLE IF NOT EXISTS mushroom_strains (
id INTEGER PRIMARY KEY AUTOINCREMENT,
name TEXT NOT NULL UNIQUE,
kuerzel TEXT NOT NULL UNIQUE,
description TEXT DEFAULT '',
created TEXT NOT NULL,
updated TEXT
)`);
const hasBatch = db
.prepare("SELECT COUNT(*) as c FROM pragma_table_info('batches') WHERE name='strain_id'")
.get();
if (!hasBatch.c) db.exec('ALTER TABLE batches ADD COLUMN strain_id INTEGER REFERENCES mushroom_strains(id)');
const hasCulture = db
.prepare("SELECT COUNT(*) as c FROM pragma_table_info('cultures') WHERE name='strain_id'")
.get();
if (!hasCulture.c) db.exec('ALTER TABLE cultures ADD COLUMN strain_id INTEGER REFERENCES mushroom_strains(id)');
// Collect unique (species, strain) pairs from existing batches + cultures
const pairs = new Map();
const batchRows = db
.prepare("SELECT DISTINCT species, strain FROM batches WHERE strain IS NOT NULL AND TRIM(strain) != ''")
.all();
const cultureRows = db
.prepare("SELECT DISTINCT species, strain FROM cultures WHERE strain IS NOT NULL AND TRIM(strain) != ''")
.all();
for (const row of [...batchRows, ...cultureRows]) {
const key = (row.species || '').toLowerCase() + '|' + row.strain.toLowerCase();
if (!pairs.has(key)) pairs.set(key, { species: (row.species || '').trim(), strain: row.strain.trim() });
}
// Generate unique name + kuerzel for each pair
const kuerzelUsed = new Set();
const nameUsed = new Set();
const pairToId = new Map();
const insMS = db.prepare('INSERT INTO mushroom_strains(name,kuerzel,description,created) VALUES(?,?,?,?)');
for (const [key, { species, strain }] of pairs) {
// Name: use species if unique, else append strain to distinguish
let name = species || strain;
if (nameUsed.has(name.toLowerCase())) name = (species ? species + ' ' : '') + strain;
let nameSuffix = 2;
let finalName = name;
while (nameUsed.has(finalName.toLowerCase())) {
finalName = name + ' ' + nameSuffix;
nameSuffix++;
}
nameUsed.add(finalName.toLowerCase());
// Kuerzel: up to 6 chars from strain, alphanumeric+hyphen, deduplicated
let kuerzel =
strain
.slice(0, 6)
.toUpperCase()
.replace(/[^A-Z0-9\-]/g, '') || 'UNK';
const kuerzelBase = kuerzel.slice(0, 5);
let kuerzelSuffix = 1;
while (kuerzelUsed.has(kuerzel)) {
kuerzel = kuerzelBase + kuerzelSuffix;
kuerzelSuffix++;
}
kuerzelUsed.add(kuerzel);
const result = insMS.run(finalName, kuerzel, '', now);
pairToId.set(key, result.lastInsertRowid);
}
// Link existing batches to their mushroom_strain
const updateBatch = db.prepare('UPDATE batches SET strain_id=? WHERE batch_id=?');
for (const b of db
.prepare("SELECT batch_id, species, strain FROM batches WHERE strain IS NOT NULL AND TRIM(strain) != ''")
.all()) {
const key = (b.species || '').toLowerCase() + '|' + b.strain.toLowerCase();
const id = pairToId.get(key);
if (id) updateBatch.run(id, b.batch_id);
}
// Link existing cultures to their mushroom_strain
const updateCulture = db.prepare('UPDATE cultures SET strain_id=? WHERE id=?');
for (const c of db
.prepare("SELECT id, species, strain FROM cultures WHERE strain IS NOT NULL AND TRIM(strain) != ''")
.all()) {
const key = (c.species || '').toLowerCase() + '|' + c.strain.toLowerCase();
const id = pairToId.get(key);
if (id) updateCulture.run(id, c.id);
}
}
},
{
version: 20,
description: 'Add barcodes table — numeric barcode registry for all entities',
fn(db) {
db.exec(`CREATE TABLE IF NOT EXISTS barcodes (
barcode INTEGER PRIMARY KEY,
entity_type TEXT NOT NULL,
entity_id TEXT NOT NULL,
created TEXT NOT NULL
)`);
db.exec('CREATE UNIQUE INDEX IF NOT EXISTS idx_barcodes_entity ON barcodes(entity_type, entity_id)');
const now = new Date().toISOString();
let nextBarcode = 1000000;
const ins = db.prepare('INSERT INTO barcodes(barcode, entity_type, entity_id, created) VALUES(?,?,?,?)');
// Assign barcodes to all existing bags
for (const r of db.prepare('SELECT bag_id FROM bags ORDER BY bag_id').all()) {
ins.run(nextBarcode++, 'bag', r.bag_id, now);
}
// Assign barcodes to all existing cultures
for (const r of db.prepare('SELECT id FROM cultures ORDER BY created, id').all()) {
ins.run(nextBarcode++, 'culture', r.id, now);
}
// Assign barcodes to all existing assets. The asset register was removed
// in v52, so on a fresh database `assets` never exists — only pre-v52
// databases replaying this migration still have rows to backfill.
if (db.prepare("SELECT 1 FROM sqlite_master WHERE type='table' AND name='assets'").get()) {
for (const r of db.prepare('SELECT asset_id FROM assets ORDER BY asset_id').all()) {
ins.run(nextBarcode++, 'asset', r.asset_id, now);
}
}
// Assign barcodes to all existing zones
for (const r of db.prepare('SELECT id FROM zones ORDER BY sort_order, id').all()) {
ins.run(nextBarcode++, 'zone', r.id, now);
}
// Assign barcodes to all existing racks
for (const r of db.prepare('SELECT id FROM racks ORDER BY zone_id, sort_order, id').all()) {
ins.run(nextBarcode++, 'rack', r.id, now);
}
}
},
{
version: 21,
description: 'Add strain_text column to batches for free-text strain annotation',
fn(db) {
const has = db.prepare("SELECT COUNT(*) as c FROM pragma_table_info('batches') WHERE name='strain_text'").get();
if (!has.c) db.exec("ALTER TABLE batches ADD COLUMN strain_text TEXT DEFAULT ''");
}
},
{
version: 22,
description: 'Add kpi_snapshots table for daily KPI history',
fn(db) {
db.exec(`
CREATE TABLE IF NOT EXISTS kpi_snapshots (
date TEXT PRIMARY KEY,
bags_created INTEGER DEFAULT 0,
grain_used_kg REAL DEFAULT 0,
harvest_kg REAL DEFAULT 0,
hardwood_used_kg REAL DEFAULT 0,
wheatbran_used_kg REAL DEFAULT 0,
avg_yield_g REAL DEFAULT 0,
contam_rate_pct REAL DEFAULT 0,
contam_bags INTEGER DEFAULT 0,
total_bags_placed INTEGER DEFAULT 0,
days_since_contam INTEGER,
flush_2plus INTEGER DEFAULT 0,
bags_spawn INTEGER DEFAULT 0,
bags_incubation INTEGER DEFAULT 0,
bags_fruiting INTEGER DEFAULT 0,
bags_contaminated INTEGER DEFAULT 0,
total_batches INTEGER DEFAULT 0,
stock_hardwood_kg REAL DEFAULT 0,
stock_wheatbran_kg REAL DEFAULT 0,
stock_grain_kg REAL DEFAULT 0
)
`);
// date is PRIMARY KEY — no extra index needed
}
},
{
version: 23,
description: 'Add lab threshold columns to inventory table',
fn(db) {
db.exec('ALTER TABLE inventory ADD COLUMN lab_thresh_mc INTEGER DEFAULT 0');
db.exec('ALTER TABLE inventory ADD COLUMN lab_thresh_pd INTEGER DEFAULT 0');
db.exec('ALTER TABLE inventory ADD COLUMN lab_thresh_lc INTEGER DEFAULT 0');
db.exec('ALTER TABLE inventory ADD COLUMN lab_thresh_g2g INTEGER DEFAULT 0');
db.exec('ALTER TABLE inventory ADD COLUMN lab_thresh_gs INTEGER DEFAULT 0');
}
},
{
version: 24,
description: 'Add reason column to scan_log for contamination tracking',
fn(db) {
db.exec('ALTER TABLE scan_log ADD COLUMN reason TEXT');
}
},
{
version: 25,
description: 'Add recipes table for reusable substrate mixtures',
fn(db) {
db.exec(`
CREATE TABLE IF NOT EXISTS recipes (
id INTEGER PRIMARY KEY AUTOINCREMENT,
name TEXT NOT NULL UNIQUE,
hardwood_pct REAL DEFAULT 0,
wheatbran_pct REAL DEFAULT 0,
gypsum_pct REAL DEFAULT 0,
rh_pct REAL DEFAULT 0,
notes TEXT,
created TEXT NOT NULL
)
`);
}
},
{
version: 26,
description: 'Add quality and notes columns to harvests',
fn(db) {
db.exec('ALTER TABLE harvests ADD COLUMN quality TEXT');
db.exec('ALTER TABLE harvests ADD COLUMN notes TEXT');
}
},
{
version: 27,
description: 'Add maintenance_log table for equipment/zone maintenance tracking',
fn(db) {
db.exec(`
CREATE TABLE IF NOT EXISTS maintenance_log (
id INTEGER PRIMARY KEY AUTOINCREMENT,
asset_id TEXT,
zone_id TEXT,
type TEXT NOT NULL,
description TEXT,
scheduled_date TEXT,
completed_date TEXT,
completed_by TEXT,
notes TEXT,
FOREIGN KEY (asset_id) REFERENCES assets(asset_id),
FOREIGN KEY (zone_id) REFERENCES zones(id)
)
`);
db.exec('CREATE INDEX IF NOT EXISTS idx_maint_asset ON maintenance_log(asset_id)');
db.exec('CREATE INDEX IF NOT EXISTS idx_maint_zone ON maintenance_log(zone_id)');
db.exec('CREATE INDEX IF NOT EXISTS idx_maint_scheduled ON maintenance_log(scheduled_date)');
}
},
{
version: 28,
description:
'Remove UNIQUE constraint on mushroom_strains.name so multiple strains of the same species are allowed',
disableForeignKeys: true,
fn(db) {
// SQLite doesn't support ALTER TABLE DROP CONSTRAINT, so recreate the table.
// disableForeignKeys flag ensures PRAGMA foreign_keys=OFF runs before BEGIN.
db.exec(`
CREATE TABLE mushroom_strains_new (
id INTEGER PRIMARY KEY AUTOINCREMENT,
name TEXT NOT NULL,
kuerzel TEXT NOT NULL UNIQUE,
description TEXT DEFAULT '',
created TEXT NOT NULL,
updated TEXT
)
`);
db.exec('INSERT INTO mushroom_strains_new SELECT * FROM mushroom_strains');
db.exec('DROP TABLE mushroom_strains');
db.exec('ALTER TABLE mushroom_strains_new RENAME TO mushroom_strains');
}
},
{
version: 29,
description: 'Clean up orphaned scan_log and harvests entries from previously deleted batches',
fn(db) {
db.exec('DELETE FROM scan_log WHERE batch IS NOT NULL AND batch NOT IN (SELECT batch_id FROM batches)');
db.exec('DELETE FROM harvests WHERE batch IS NOT NULL AND batch NOT IN (SELECT batch_id FROM batches)');
}
},
{
version: 30,
description: 'Add strain_text column to cultures for free-text strain annotation',
fn(db) {
const has = db.prepare("SELECT COUNT(*) as c FROM pragma_table_info('cultures') WHERE name='strain_text'").get();
if (!has.c) db.exec("ALTER TABLE cultures ADD COLUMN strain_text TEXT DEFAULT ''");
}
},
{
version: 31,
description: 'Add per-bag weight column to bags table and backfill from batch',
fn(db) {
db.exec('ALTER TABLE bags ADD COLUMN bag_kg REAL');
db.exec('UPDATE bags SET bag_kg = (SELECT bag_kg FROM batches WHERE batches.batch_id = bags.batch_id)');
}
},
{
version: 32,
description: 'Add exception_dates to calendar_events for per-occurrence recurring deletes',
fn(db) {
const has = db
.prepare(`SELECT COUNT(*) as c FROM pragma_table_info('calendar_events') WHERE name='exception_dates'`)
.get();
if (!has.c) db.exec(`ALTER TABLE calendar_events ADD COLUMN exception_dates TEXT`);
}
},
{
version: 33,
description: 'Add notifications table for per-user in-app alerts',
fn(db) {
db.exec(`
CREATE TABLE IF NOT EXISTS notifications (
id INTEGER PRIMARY KEY AUTOINCREMENT,
user_id INTEGER NOT NULL REFERENCES users(id) ON DELETE CASCADE,
type TEXT NOT NULL,
title TEXT NOT NULL,
body TEXT,
link_type TEXT,
link_id TEXT,
created TEXT NOT NULL,
read INTEGER NOT NULL DEFAULT 0
);
CREATE INDEX IF NOT EXISTS idx_notif_user ON notifications(user_id, read, created DESC);
`);
}
},
{
version: 34,
description: 'Add grain hydration fields: batches.grain_rh and inventory.avg_grain_rh_pct',
fn(db) {
const hasBatchCol = db
.prepare("SELECT COUNT(*) as c FROM pragma_table_info('batches') WHERE name='grain_rh'")
.get();
if (!hasBatchCol.c) db.exec('ALTER TABLE batches ADD COLUMN grain_rh REAL DEFAULT 0');
const hasInvCol = db
.prepare("SELECT COUNT(*) as c FROM pragma_table_info('inventory') WHERE name='avg_grain_rh_pct'")
.get();
if (!hasInvCol.c) db.exec('ALTER TABLE inventory ADD COLUMN avg_grain_rh_pct REAL DEFAULT 52');
}
},
{
version: 35,
description: 'Add print_bridge_config table for editable Windows print bridge settings',
fn(db) {
db.exec(`CREATE TABLE IF NOT EXISTS print_bridge_config (
id INTEGER PRIMARY KEY CHECK (id = 1),
enabled INTEGER DEFAULT 0,
url TEXT DEFAULT '',
token TEXT DEFAULT ''
)`);
}
},
{
version: 36,
description: 'Contamination reports + types + photos (audit Section 2 MVP)',
fn(db) {
db.exec(`
CREATE TABLE IF NOT EXISTS contamination_types (
id INTEGER PRIMARY KEY AUTOINCREMENT,
key TEXT NOT NULL UNIQUE,
name_de TEXT NOT NULL,
name_en TEXT NOT NULL,
name_pt TEXT NOT NULL,
color TEXT NOT NULL,
sort_order INTEGER DEFAULT 0,
active INTEGER NOT NULL DEFAULT 1,
created TEXT NOT NULL
);
CREATE TABLE IF NOT EXISTS contamination_reports (
id INTEGER PRIMARY KEY AUTOINCREMENT,
reported_at TEXT NOT NULL,
user_id INTEGER REFERENCES users(id) ON DELETE SET NULL,
bag_id TEXT,
batch_id TEXT,
zone_id TEXT,
type_id INTEGER NOT NULL REFERENCES contamination_types(id),
severity TEXT NOT NULL DEFAULT 'minor',
notes TEXT DEFAULT '',
scan_log_id INTEGER REFERENCES scan_log(id) ON DELETE SET NULL,
resolved_at TEXT,
resolved_by INTEGER REFERENCES users(id) ON DELETE SET NULL,
resolution TEXT
);
CREATE INDEX IF NOT EXISTS idx_contam_batch ON contamination_reports(batch_id);
CREATE INDEX IF NOT EXISTS idx_contam_zone ON contamination_reports(zone_id);
CREATE INDEX IF NOT EXISTS idx_contam_type ON contamination_reports(type_id);
CREATE INDEX IF NOT EXISTS idx_contam_time ON contamination_reports(reported_at);
CREATE TABLE IF NOT EXISTS contamination_photos (
id INTEGER PRIMARY KEY AUTOINCREMENT,
report_id INTEGER NOT NULL REFERENCES contamination_reports(id) ON DELETE CASCADE,
uuid TEXT NOT NULL UNIQUE,
rel_path TEXT NOT NULL,
thumb_path TEXT NOT NULL,
width INTEGER,
height INTEGER,
bytes INTEGER NOT NULL,
sha256 TEXT NOT NULL,
uploaded_at TEXT NOT NULL,
uploaded_by INTEGER REFERENCES users(id) ON DELETE SET NULL
);
CREATE INDEX IF NOT EXISTS idx_contam_photos_report ON contamination_photos(report_id);
CREATE UNIQUE INDEX IF NOT EXISTS idx_contam_photos_sha ON contamination_photos(sha256);
`);
// Seed the default contamination type list. Admins can extend via a future
// Settings UI; soft-deletes (active=0) preserve historical references.
const now = new Date().toISOString();
const seed = db.prepare(`INSERT OR IGNORE INTO contamination_types
(key, name_de, name_en, name_pt, color, sort_order, active, created)
VALUES (?, ?, ?, ?, ?, ?, 1, ?)`);
const types = [
['tricho', 'Trichoderma (Grünschimmel)', 'Trichoderma (green mold)', 'Trichoderma (mofo verde)', '#16a34a', 10],
['cobweb', 'Spinnweben (Dactylium)', 'Cobweb (Dactylium)', 'Teia de aranha (Dactylium)', '#94a3b8', 20],
['bacterial_wet_rot', 'Bakterielle Nassfäule', 'Bacterial wet rot', 'Podridão úmida bacteriana', '#0891b2', 30],
['aspergillus', 'Aspergillus', 'Aspergillus', 'Aspergillus', '#facc15', 40],
[
'penicillium',
'Penicillium (Blauschimmel)',
'Penicillium (blue mold)',
'Penicillium (mofo azul)',
'#3b82f6',
50
],
['wet_spot', 'Nassflecken', 'Wet spot', 'Mancha úmida', '#92400e', 60],
['pin_set_defect', 'Pin-Set-Defekt', 'Pin-set defect / aborts', 'Defeito de pin-set', '#a855f7', 70],
['mites', 'Milben', 'Mites', 'Ácaros', '#dc2626', 80],
['verticillium', 'Verticillium', 'Verticillium', 'Verticillium', '#f97316', 90],
['unknown_other', 'Unbekannt / Sonstiges', 'Unknown / other', 'Desconhecido / outro', '#64748b', 999]
];
for (const t of types) seed.run(...t, now);
}
},
{
version: 37,
description: 'Add minor indexes flagged by the audit (Section 3.2)',
fn(db) {
db.exec(`
-- Used by getContaminationReport (server.js / db.js:3629). Partial
-- index keeps it small since most scan_log entries have NULL reason.
CREATE INDEX IF NOT EXISTS idx_scanlog_action_reason
ON scan_log(action) WHERE reason IS NOT NULL;
-- Batches order-by-created in readAll / getAllBatches.
CREATE INDEX IF NOT EXISTS idx_batches_created ON batches(created);
`);
}
},
{
version: 38,
description: 'Add audit columns to mcp_config for the static MCP token',
fn(db) {
// SQLite doesn't support `ADD COLUMN ... IF NOT EXISTS`, so check the
// current schema and add only the columns that are missing.
const cols = db
.prepare("SELECT name FROM pragma_table_info('mcp_config')")
.all()
.map((r) => r.name);
if (!cols.includes('last_used_at')) {
db.exec('ALTER TABLE mcp_config ADD COLUMN last_used_at TEXT');
}
if (!cols.includes('created_at')) {
db.exec('ALTER TABLE mcp_config ADD COLUMN created_at TEXT');
}
if (!cols.includes('revoked_at')) {
db.exec('ALTER TABLE mcp_config ADD COLUMN revoked_at TEXT');
}
}
},
{
version: 39,
description: 'Add client_uuid + sequence for scan idempotency and iCal RFC 5545 conformance',
fn(db) {
// I-11: client-supplied idempotency key on scan_log so the offline
// queue (sw.js) can replay POSTs without creating duplicates when a
// network partition times out the request but the server has already
// committed it. Partial unique index — legacy rows have NULL.
const scanCols = db
.prepare("SELECT name FROM pragma_table_info('scan_log')")
.all()
.map((r) => r.name);
if (!scanCols.includes('client_uuid')) {
db.exec('ALTER TABLE scan_log ADD COLUMN client_uuid TEXT');
}
db.exec(
'CREATE UNIQUE INDEX IF NOT EXISTS idx_scanlog_client_uuid ON scan_log(client_uuid) WHERE client_uuid IS NOT NULL'
);
// I-15: SEQUENCE counter for VTODO/VEVENT iCal output. Bumped on every
// update so external CalDAV clients can detect changes (RFC 5545 §3.8.7.4).
const taskCols = db
.prepare("SELECT name FROM pragma_table_info('manual_tasks')")
.all()
.map((r) => r.name);
if (!taskCols.includes('sequence')) {
db.exec('ALTER TABLE manual_tasks ADD COLUMN sequence INTEGER NOT NULL DEFAULT 0');
}
const evtCols = db
.prepare("SELECT name FROM pragma_table_info('calendar_events')")
.all()
.map((r) => r.name);
if (!evtCols.includes('sequence')) {
db.exec('ALTER TABLE calendar_events ADD COLUMN sequence INTEGER NOT NULL DEFAULT 0');
}
}
},
{
version: 40,
description: 'Add user_id to inventory_log for actor accountability',
fn(db) {
// I-22: every stock change should record who performed it. Existing rows
// pre-date this column and stay NULL — we don't backfill since the
// information is not recoverable from elsewhere. ON DELETE SET NULL so
// removing a user keeps the audit trail (just anonymises it).
const cols = db
.prepare("SELECT name FROM pragma_table_info('inventory_log')")
.all()
.map((r) => r.name);
if (!cols.includes('user_id')) {
db.exec('ALTER TABLE inventory_log ADD COLUMN user_id INTEGER REFERENCES users(id) ON DELETE SET NULL');
}
}
},
{
version: 41,
description: 'Camera dashboard: ensure camera_cameras + camera_calibration exist (admin tab WIP)',
fn(db) {
// The Python mushroom_camera module creates `camera_cameras` (and other
// camera_* tables) in its own ensure_schema(). The Node.js dashboard
// needs camera_cameras to exist before the operator has run the Python
// module even once, so we create it here with the same shape. Calibration
// values were env-vars only; this migration moves them into a singleton
// row that the admin UI can edit. Defaults match config.py.
db.exec(`
CREATE TABLE IF NOT EXISTS camera_cameras (
id INTEGER PRIMARY KEY AUTOINCREMENT,
name TEXT NOT NULL,
rtsp_url TEXT NOT NULL,
zone_id TEXT REFERENCES zones(id) ON DELETE SET NULL,
enabled INTEGER DEFAULT 1,
created TEXT NOT NULL
);
CREATE TABLE IF NOT EXISTS camera_calibration (
id INTEGER PRIMARY KEY CHECK (id = 1),
px_per_mm REAL DEFAULT 2.0,
incubation_bag_radius_px INTEGER DEFAULT 150,
qr_assign_radius_px INTEGER DEFAULT 400,
yolo_conf_threshold REAL DEFAULT 0.4,
pin_max_area_ratio REAL DEFAULT 0.04,
harvest_growth_threshold_pct REAL DEFAULT 2.0,
harvest_stall_readings INTEGER DEFAULT 3,
colonisation_score_threshold REAL DEFAULT 0.85,
colonisation_min_fraction REAL DEFAULT 0.70,
unseen_bag_alert_hours INTEGER DEFAULT 24,
contam_conf_threshold REAL DEFAULT 0.75,
updated_at TEXT
);
INSERT OR IGNORE INTO camera_calibration (id) VALUES (1);
`);
}
},
{
version: 42,
description: 'Order hub (Phase 0): sales channels, products, orders, customers, allocations',
fn(db) {
// Sales-side layer on top of the production tables. See ORDERS_HUB_DESIGN.md.
// Tables are created in FK-dependency order. Channel-credential and sync-log
// tables are created here for schema completeness; their helper functions
// arrive with the live-channel work (Phase 1+).
db.exec(`
CREATE TABLE IF NOT EXISTS sales_channel_config (
channel TEXT PRIMARY KEY, -- 'wix' | 'etsy' | 'ebay'
enabled INTEGER DEFAULT 0,
api_key TEXT DEFAULT '',
site_id TEXT DEFAULT '',
client_id TEXT DEFAULT '',
client_secret TEXT DEFAULT '',
access_token TEXT DEFAULT '',
refresh_token TEXT DEFAULT '',
token_expires TEXT,
webhook_secret TEXT DEFAULT '',
last_sync TEXT,
last_cursor TEXT,
last_error TEXT,
created TEXT
);
CREATE TABLE IF NOT EXISTS products (
id INTEGER PRIMARY KEY AUTOINCREMENT,
sku TEXT UNIQUE,
name TEXT NOT NULL,
category TEXT, -- growkit|spawn|culture|fresh|supply
species TEXT,
strain TEXT,
active INTEGER DEFAULT 1,
notes TEXT DEFAULT '',
created TEXT NOT NULL
);
CREATE TABLE IF NOT EXISTS product_components (
id INTEGER PRIMARY KEY AUTOINCREMENT,
product_id INTEGER NOT NULL REFERENCES products(id) ON DELETE CASCADE,
fulfill_type TEXT NOT NULL DEFAULT 'produce', -- produce|harvest|stock
batch_type TEXT, -- block|grain (matches batches.batch_type)
species TEXT,
strain TEXT,
recipe_id INTEGER REFERENCES recipes(id),
lead_days INTEGER DEFAULT 0,
grams REAL,
qty_per_unit REAL NOT NULL DEFAULT 1,
notes TEXT DEFAULT ''
);
CREATE INDEX IF NOT EXISTS idx_prodcomp_product ON product_components(product_id);
CREATE TABLE IF NOT EXISTS product_channel_map (
id INTEGER PRIMARY KEY AUTOINCREMENT,
channel TEXT NOT NULL,
channel_sku TEXT,
listing_id TEXT,
product_id INTEGER REFERENCES products(id) ON DELETE CASCADE,
created TEXT NOT NULL,
UNIQUE (channel, channel_sku, listing_id)
);
CREATE TABLE IF NOT EXISTS customers (
id INTEGER PRIMARY KEY AUTOINCREMENT,
email TEXT UNIQUE, -- lowercased; primary dedup key
name TEXT,
country TEXT,
first_channel TEXT,
first_order TEXT,
last_order TEXT,
order_count INTEGER DEFAULT 0,
total_spent REAL DEFAULT 0,
currency TEXT,
notes TEXT DEFAULT '',
created TEXT NOT NULL
);
CREATE TABLE IF NOT EXISTS customer_identities (
id INTEGER PRIMARY KEY AUTOINCREMENT,
customer_id INTEGER NOT NULL REFERENCES customers(id) ON DELETE CASCADE,
channel TEXT NOT NULL,
handle TEXT NOT NULL, -- email, eBay username, Etsy buyer id
created TEXT NOT NULL,
UNIQUE (channel, handle)
);
CREATE TABLE IF NOT EXISTS orders (
id INTEGER PRIMARY KEY AUTOINCREMENT,
channel TEXT NOT NULL,
channel_order_id TEXT NOT NULL,
status TEXT NOT NULL DEFAULT 'new', -- new|in_production|ready|shipped|cancelled
order_date TEXT,
ship_by TEXT,
customer_id INTEGER REFERENCES customers(id) ON DELETE SET NULL,
customer_name TEXT,
customer_email TEXT,
ship_country TEXT,
total_amount REAL,
currency TEXT,
raw_json TEXT,
imported TEXT NOT NULL,
updated TEXT NOT NULL,
UNIQUE (channel, channel_order_id)
);
CREATE INDEX IF NOT EXISTS idx_orders_status ON orders(status);
CREATE INDEX IF NOT EXISTS idx_orders_channel ON orders(channel);
CREATE INDEX IF NOT EXISTS idx_orders_customer ON orders(customer_id);
CREATE TABLE IF NOT EXISTS order_items (
id INTEGER PRIMARY KEY AUTOINCREMENT,
order_id INTEGER NOT NULL REFERENCES orders(id) ON DELETE CASCADE,
channel_sku TEXT,
listing_id TEXT,
title TEXT,
qty INTEGER NOT NULL DEFAULT 1,
product_id INTEGER REFERENCES products(id) ON DELETE SET NULL,
unit_price REAL
);
CREATE INDEX IF NOT EXISTS idx_orderitems_order ON order_items(order_id);
CREATE INDEX IF NOT EXISTS idx_orderitems_product ON order_items(product_id);
CREATE TABLE IF NOT EXISTS order_allocations (
id INTEGER PRIMARY KEY AUTOINCREMENT,
order_item_id INTEGER NOT NULL REFERENCES order_items(id) ON DELETE CASCADE,
batch_id TEXT REFERENCES batches(batch_id) ON DELETE SET NULL,
qty REAL NOT NULL,
status TEXT NOT NULL DEFAULT 'reserved', -- reserved|produced|shipped
created TEXT NOT NULL,
UNIQUE (order_item_id, batch_id)
);
CREATE INDEX IF NOT EXISTS idx_alloc_batch ON order_allocations(batch_id);
CREATE INDEX IF NOT EXISTS idx_alloc_item ON order_allocations(order_item_id);
CREATE TABLE IF NOT EXISTS order_sync_log (
id INTEGER PRIMARY KEY AUTOINCREMENT,
time TEXT NOT NULL,
channel TEXT NOT NULL,
ok INTEGER NOT NULL,
fetched INTEGER DEFAULT 0,
upserted INTEGER DEFAULT 0,
message TEXT
);
`);
}
},
{
version: 43,
description: 'Order hub v2: finished-goods stock, materials + BOM, MRP fields',
fn(db) {
const cols = db
.prepare("SELECT name FROM pragma_table_info('products')")
.all()
.map((r) => r.name);
if (!cols.includes('stock')) db.exec('ALTER TABLE products ADD COLUMN stock REAL DEFAULT 0');
if (!cols.includes('lead_days')) db.exec('ALTER TABLE products ADD COLUMN lead_days INTEGER DEFAULT 0');
if (!cols.includes('producible')) db.exec('ALTER TABLE products ADD COLUMN producible INTEGER DEFAULT 1');
const allocCols = db
.prepare("SELECT name FROM pragma_table_info('order_allocations')")
.all()
.map((r) => r.name);
if (!allocCols.includes('source')) {
db.exec("ALTER TABLE order_allocations ADD COLUMN source TEXT DEFAULT 'batch'");
}
db.exec(`
CREATE TABLE IF NOT EXISTS materials (
id INTEGER PRIMARY KEY AUTOINCREMENT,
name TEXT NOT NULL,
unit TEXT DEFAULT 'Stk', -- 'kg' | 'L' | 'Stk'
stock REAL DEFAULT 0,
threshold REAL DEFAULT 0,
notes TEXT DEFAULT '',
created TEXT NOT NULL
);
CREATE TABLE IF NOT EXISTS product_bom (
id INTEGER PRIMARY KEY AUTOINCREMENT,
product_id INTEGER NOT NULL REFERENCES products(id) ON DELETE CASCADE,
material_id INTEGER NOT NULL REFERENCES materials(id) ON DELETE CASCADE,
qty_per_unit REAL NOT NULL DEFAULT 1,
notes TEXT DEFAULT ''
);
CREATE INDEX IF NOT EXISTS idx_bom_product ON product_bom(product_id);
CREATE INDEX IF NOT EXISTS idx_bom_material ON product_bom(material_id);
`);
}
},
{
version: 44,
description:
'Order hub v3: per-product production spec on products, coir in inventory, drop v2 materials/product_bom',
fn(db) {
// The order-hub no longer keeps its own materials/BOM tables. Instead each
// product carries a production spec mirroring the batch (charge) form, and
// the demand engine consumes the shared `inventory` ledger (grain/hardwood/
// wheatbran/gypsum/coir) via the same hydration math as batches.
const pcols = db
.prepare("SELECT name FROM pragma_table_info('products')")
.all()
.map((r) => r.name);
const addP = (name, ddl) => {
if (!pcols.includes(name)) db.exec('ALTER TABLE products ADD COLUMN ' + ddl);
};
addP('prod_type', "prod_type TEXT DEFAULT 'buy'"); // allinone|block|grain|buy
addP('prod_species', 'prod_species TEXT');
addP('prod_strain', 'prod_strain TEXT');
addP('prod_days', 'prod_days INTEGER DEFAULT 14');
addP('prod_bag_kg', 'prod_bag_kg REAL DEFAULT 0'); // substrate wet kg / unit
addP('prod_substrate', 'prod_substrate TEXT'); // holzkleie|cvg
addP('prod_hardwood_pct', 'prod_hardwood_pct REAL DEFAULT 0');
addP('prod_wheatbran_pct', 'prod_wheatbran_pct REAL DEFAULT 0');
addP('prod_coir_pct', 'prod_coir_pct REAL DEFAULT 0');
addP('prod_gypsum', 'prod_gypsum INTEGER DEFAULT 0');
addP('prod_rh_pct', 'prod_rh_pct REAL DEFAULT 0'); // substrate hydration %
addP('prod_grain_kg', 'prod_grain_kg REAL DEFAULT 0'); // grain spawn wet kg / unit
addP('prod_grain_rh_pct', 'prod_grain_rh_pct REAL DEFAULT 52');
// Coir/CVG raw material in the shared inventory ledger
const icols = db
.prepare("SELECT name FROM pragma_table_info('inventory')")
.all()
.map((r) => r.name);
if (!icols.includes('stock_coir')) db.exec('ALTER TABLE inventory ADD COLUMN stock_coir REAL DEFAULT 0');
if (!icols.includes('thresh_coir')) db.exec('ALTER TABLE inventory ADD COLUMN thresh_coir REAL DEFAULT 0');
// Drop the v2 order-hub BOM tables — superseded by the per-product spec.
db.exec('DROP TABLE IF EXISTS product_bom');
db.exec('DROP TABLE IF EXISTS materials');
}
},
{
version: 45,
description: 'All-in-One/CVG batches: coir substrate % + raw-grain portion on block batches',
fn(db) {
// An "All-in-One" charge is internally a block batch that also carries a
// coir/CVG fraction (sub_coir %) and a raw-grain portion (grain_kg wet per
// bag, hydrated by grain_rh). computeBatchMaterialDeltas reads these to
// deduct/credit grain + coir alongside the usual hardwood/wheatbran/gypsum.
const cols = db
.prepare("SELECT name FROM pragma_table_info('batches')")
.all()
.map((r) => r.name);
if (!cols.includes('sub_coir')) db.exec('ALTER TABLE batches ADD COLUMN sub_coir REAL DEFAULT 0');
if (!cols.includes('grain_kg')) db.exec('ALTER TABLE batches ADD COLUMN grain_kg REAL DEFAULT 0');
}
},
{
version: 46,
description: 'Sorte production recipe defaults on mushroom_strains (rec_* columns)',
fn(db) {
// Each Pilzsorte can carry a default production recipe so a Charge or a
// Laborarbeit can be spun up from the Sorte without re-entering substrate,
// grain and hydration every time. Columns mirror the batch (sub_*) and
// product (prod_*) shapes so the same charge math applies. rec_batch_type
// '' = no recipe defined yet.
const cols = db
.prepare("SELECT name FROM pragma_table_info('mushroom_strains')")
.all()
.map((r) => r.name);
const add = (name, ddl) => {
if (!cols.includes(name)) db.exec('ALTER TABLE mushroom_strains ADD COLUMN ' + ddl);
};
add('rec_batch_type', "rec_batch_type TEXT DEFAULT ''");
add('rec_substrate', "rec_substrate TEXT DEFAULT 'holzkleie'");
add('rec_bag_kg', 'rec_bag_kg REAL DEFAULT 0');
add('rec_hardwood_pct', 'rec_hardwood_pct REAL DEFAULT 0');
add('rec_wheatbran_pct', 'rec_wheatbran_pct REAL DEFAULT 0');
add('rec_coir_pct', 'rec_coir_pct REAL DEFAULT 0');
add('rec_rh_pct', 'rec_rh_pct REAL DEFAULT 0');
add('rec_gypsum', 'rec_gypsum INTEGER DEFAULT 0');
add('rec_grain_kg', 'rec_grain_kg REAL DEFAULT 0');
add('rec_grain_rh_pct', 'rec_grain_rh_pct REAL DEFAULT 52');
add('rec_inc_days', 'rec_inc_days INTEGER DEFAULT 14');
}
},
{
version: 47,
description: 'Phase 4 Versand: shipments + shipping_config + structured ship-to address on orders',
fn(db) {
// Structured shipping address on orders. Channel sync (Phase 1+) fills these
// from raw_json; until then the Versand UI lets the user enter/confirm them.
// ship_country already exists from v42.
const ocols = db
.prepare("SELECT name FROM pragma_table_info('orders')")
.all()
.map((r) => r.name);
const addOrderCol = (name, ddl) => {
if (!ocols.includes(name)) db.exec('ALTER TABLE orders ADD COLUMN ' + ddl);
};
addOrderCol('ship_name', 'ship_name TEXT');
addOrderCol('ship_company', 'ship_company TEXT');
addOrderCol('ship_street', 'ship_street TEXT');
addOrderCol('ship_house', 'ship_house TEXT');
addOrderCol('ship_address2', 'ship_address2 TEXT');
addOrderCol('ship_city', 'ship_city TEXT');
addOrderCol('ship_postal', 'ship_postal TEXT');
addOrderCol('ship_phone', 'ship_phone TEXT');
addOrderCol('ship_weight_g', 'ship_weight_g INTEGER');
// One row per bought label.
db.exec(`
CREATE TABLE IF NOT EXISTS shipments (
id INTEGER PRIMARY KEY AUTOINCREMENT,
order_id INTEGER REFERENCES orders(id) ON DELETE SET NULL,
provider TEXT NOT NULL DEFAULT 'sendcloud',
provider_parcel_id TEXT,
carrier TEXT,
method_id TEXT,
method_name TEXT,
tracking_number TEXT,
tracking_url TEXT,
label_url TEXT,
label_format TEXT, -- pdf_a6 | pdf_a4 | zpl
cost REAL,
currency TEXT,
status TEXT NOT NULL DEFAULT 'created', -- created|announced|in_transit|delivered|cancelled|error
channel_pushed INTEGER DEFAULT 0,
error TEXT,
created TEXT NOT NULL,
updated TEXT
);
CREATE INDEX IF NOT EXISTS idx_shipments_order ON shipments(order_id);
`);
// Provider credentials + defaults (Admin -> Versand). Secrets live here like
// sales_channel_config / mcp_config, never in code.
db.exec(`
CREATE TABLE IF NOT EXISTS shipping_config (
id INTEGER PRIMARY KEY CHECK (id = 1),
provider TEXT DEFAULT 'sendcloud',
enabled INTEGER DEFAULT 0,
public_key TEXT DEFAULT '',
secret_key TEXT DEFAULT '',
mode TEXT DEFAULT 'test', -- test | live
sender_address_id TEXT DEFAULT '',
default_method TEXT DEFAULT '',
default_weight_g INTEGER DEFAULT 1000,
created TEXT
);
`);
db.prepare('INSERT OR IGNORE INTO shipping_config(id, created) VALUES(1, ?)').run(new Date().toISOString());
}
},
{
version: 48,
description: 'Shipping permission: per-user can_ship capability (label buying + ship PII)',
fn(db) {
const cols = db.prepare('PRAGMA table_info(users)').all();
if (!cols.some((c) => c.name === 'can_ship')) {
db.exec('ALTER TABLE users ADD COLUMN can_ship INTEGER DEFAULT 0');
}
}
},
{
version: 49,
description: 'Index scan_log(action) so action-only aggregates stop full-scanning',
fn(db) {
// The existing action index is partial (WHERE reason IS NOT NULL), so
// filters like action='ADD' couldn't use it and fell back to a full scan.
db.exec('CREATE INDEX IF NOT EXISTS idx_scanlog_action ON scan_log(action)');
}
},
{
version: 50,
description: 'Add report_uuid to contamination_reports for offline-replay idempotency',
fn(db) {
const cols = db.prepare('PRAGMA table_info(contamination_reports)').all();
if (!cols.some((c) => c.name === 'report_uuid')) {
db.exec('ALTER TABLE contamination_reports ADD COLUMN report_uuid TEXT');
}
db.exec(
'CREATE UNIQUE INDEX IF NOT EXISTS idx_contam_report_uuid ON contamination_reports(report_uuid) WHERE report_uuid IS NOT NULL'
);
}
},
{
version: 51,
description: 'Sorte recipe: expected days in the fruiting tent (rec_fruit_days)',
fn(db) {
// rec_inc_days only describes colonisation — it says nothing about how
// long a strain then sits in the tent, which differs by species (oysters
// ~7 days, Reishi ~60). 0 means "not set": the harvest card falls back to
// showing elapsed tent days with no target.
const cols = db.prepare('PRAGMA table_info(mushroom_strains)').all();
if (!cols.some((c) => c.name === 'rec_fruit_days')) {
db.exec('ALTER TABLE mushroom_strains ADD COLUMN rec_fruit_days INTEGER DEFAULT 0');
}
}
},
{
version: 52,
description: 'Remove the fixed-asset register — accounting does not belong in a cultivation tracker',
disableForeignKeys: true,
fn(db) {
// The asset register was pure bookkeeping: purchase price, useful life,
// AfA depreciation, book value, a Steuerberater CSV and a Stichtag
// valuation. maintenance_log stays — scheduling an autoclave cycle or a
// HEPA filter change is operational, not financial — but it carried a
// FOREIGN KEY into assets, so recreate it without that reference.
// asset_id survives as a free-text equipment identifier (there is no
// register left to validate it against).
if (db.prepare("SELECT 1 FROM sqlite_master WHERE type='table' AND name='maintenance_log'").get()) {
db.exec(`
CREATE TABLE maintenance_log_new (
id INTEGER PRIMARY KEY AUTOINCREMENT,
asset_id TEXT,
zone_id TEXT,
type TEXT NOT NULL,
description TEXT,
scheduled_date TEXT,
completed_date TEXT,
completed_by TEXT,
notes TEXT,
FOREIGN KEY (zone_id) REFERENCES zones(id)
)
`);
db.exec('INSERT INTO maintenance_log_new SELECT * FROM maintenance_log');
db.exec('DROP TABLE maintenance_log');
db.exec('ALTER TABLE maintenance_log_new RENAME TO maintenance_log');
// The old indexes went with the dropped table.
db.exec('CREATE INDEX IF NOT EXISTS idx_maint_asset ON maintenance_log(asset_id)');
db.exec('CREATE INDEX IF NOT EXISTS idx_maint_zone ON maintenance_log(zone_id)');
db.exec('CREATE INDEX IF NOT EXISTS idx_maint_scheduled ON maintenance_log(scheduled_date)');
}
db.exec('DROP TABLE IF EXISTS assets');
// Barcodes printed for inventory tags no longer resolve to anything.
db.exec("DELETE FROM barcodes WHERE entity_type='asset'");
}
},
{
version: 53,
description: 'Add harvest_feed_config so the outbound feed is set up in Settings, not in .env',
fn(db) {
// The feed shipped as environment variables only, which meant editing a
// file on the server and restarting it. Every other integration here —
// DuckDNS, CalDAV, the print bridge, shipping, channels — is a form in
// Settings, and most people running this have a browser open and no shell.
// Environment variables keep working and take over when this row is off,
// so container setups that bake config into the image are unaffected.
db.exec(`CREATE TABLE IF NOT EXISTS harvest_feed_config (
id INTEGER PRIMARY KEY CHECK (id = 1),
enabled INTEGER DEFAULT 0,
url TEXT DEFAULT '',
secret TEXT DEFAULT '',
interval_min INTEGER DEFAULT 15,
fresh_days INTEGER DEFAULT 3,
planned_days INTEGER DEFAULT 28,
lead_days INTEGER DEFAULT 0,
strain INTEGER DEFAULT 1,
site TEXT DEFAULT '',
last_at TEXT,
last_ok INTEGER,
last_error TEXT
)`);
db.prepare('INSERT OR IGNORE INTO harvest_feed_config(id) VALUES(1)').run();
}
},
{
version: 54,
description: 'Make a customer erasure stick: erased_at plus a hashed suppression list',
fn(db) {
// Erasing a customer NULLed their PII, and then the next channel sync put
// it straight back: upsertOrder rewrites customer_name/customer_email/
// raw_json unconditionally and COALESCEs the ship_* columns, so a NULLed
// column simply took the incoming value again. Worse, because the erase
// deleted the customer_identities rows, upsertCustomerFromOrder could no
// longer match the person and inserted a SECOND customer carrying the
// repopulated name and email.
//
// Two pieces fix that. erased_at marks the customer so the sync path knows
// to keep the PII columns empty. erased_identities is a suppression list —
// the marketplace handle is itself identifying, so it is stored only as a
// SHA-256 of "channel|handle", which is enough to recognise the same buyer
// arriving again without keeping anything that names them. Remembering an
// erasure request is the one thing you are expected to retain in order to
// honour it.
const cols = db.prepare('PRAGMA table_info(customers)').all();
if (!cols.some((c) => c.name === 'erased_at')) {
db.exec('ALTER TABLE customers ADD COLUMN erased_at TEXT');
}
db.exec(`CREATE TABLE IF NOT EXISTS erased_identities (
channel TEXT NOT NULL,
handle_hash TEXT NOT NULL,
customer_id INTEGER,
erased_at TEXT NOT NULL,
PRIMARY KEY (channel, handle_hash)
)`);
}
},
{
version: 55,
description: 'Release for sale: how much of a harvest a shop may actually sell, and until when',
fn(db) {
// The feed reports what was harvested. That is not the same as what is
// still there, and the difference is the whole problem: `harvests` only
// ever grows. Sell part of a harvest elsewhere and the feed still reports
// the whole harvest until it falls out of the window.
//
// Recording every sale would fix the arithmetic and will not happen — a
// sale outside the app leaves no trace in it. So the number that goes out is
// not a live stock but a **release**: this much is set aside for the shop.
// Sales that happen elsewhere come out of the rest and can no longer make
// the published figure wrong, because they cannot touch the set-aside.
//
// valid_until is the guardrail. Fresh mushrooms do not keep, and the
// realistic failure here is not a wrong number, it is a forgotten one —
// last week's release quietly selling produce that was eaten days ago. An
// expired release counts as zero, both here and in the receiver.
db.exec(`CREATE TABLE IF NOT EXISTS harvest_release (
species TEXT PRIMARY KEY,
grams REAL NOT NULL DEFAULT 0,
valid_until TEXT,
note TEXT DEFAULT '',
updated TEXT NOT NULL
)`);
const cols = db.prepare('PRAGMA table_info(harvest_feed_config)').all();
// Off by default, and it has to be: switching it on changes what the
// numbers in the feed *mean*, and nobody's shop should start capping
// itself because they pulled a new version.
if (!cols.some((c) => c.name === 'release_mode')) {
db.exec('ALTER TABLE harvest_feed_config ADD COLUMN release_mode INTEGER DEFAULT 0');
}
}
},
{
version: 56,
description: 'Add week_rhythm so the working week has a shape instead of being purely reactive',
fn(db) {
// The dashboard could only answer "what is late", which makes every day a
// reaction to whatever the batches happen to be doing. A farm runs to a
// rhythm anyway — blocks on one day, grain on another, moves to
// fruiting on a third — it just had nowhere to be written down.
//
// One row per weekday, keyed the way JavaScript's getDay() counts (0 =
// Sunday), so the client never has to translate. Nothing is seeded here:
// the editor proposes a rhythm from the farm's own history, and an empty
// table is exactly what tells it to do that rather than to trust a guess
// baked into a migration.
db.exec(`CREATE TABLE IF NOT EXISTS week_rhythm (
weekday INTEGER PRIMARY KEY CHECK (weekday BETWEEN 0 AND 6),
theme TEXT NOT NULL
)`);
}
},
{
version: 57,
description: 'Let a rhythm day carry a target and a Sorte, so it reads as a job rather than a label',
fn(db) {
// "Monday is substrate day" is a category, not an instruction. What makes
// it a task is how much and of what: "N blocks of one Sorte".
//
// The quantity lives here; the mixture does not. Sorten already carry a
// full recipe — bag weight, hardwood/wheatbran/gypsum split, incubation
// days — and the create dialog already builds batches from it. Storing a
// strain_id means the rhythm always states the mixture that Sorte
// currently uses, instead of a copy that silently goes stale the first
// time somebody corrects the recipe.
//
// No foreign key: a Sorte that is deleted should leave the day pointing
// at nothing and still be editable, not make the row unreadable. Readers
// resolve the id and fall back to the bare quantity.
const cols = db
.prepare('PRAGMA table_info(week_rhythm)')
.all()
.map((c) => c.name);
if (!cols.includes('target_qty')) db.exec('ALTER TABLE week_rhythm ADD COLUMN target_qty INTEGER');
if (!cols.includes('strain_id')) db.exec('ALTER TABLE week_rhythm ADD COLUMN strain_id INTEGER');
if (!cols.includes('note')) db.exec('ALTER TABLE week_rhythm ADD COLUMN note TEXT');
}
},
{
version: 58,
description: 'Track what a rhythm day actually produced, so unfinished work carries to the next day',
fn(db) {
// week_rhythm is a template: "Mondays are N blocks of one Sorte". It
// says nothing about whether last Monday's N got made, so a day that was
// missed simply vanished — the card showed the same N again the following
// week and nobody was any wiser.
//
// One row per date, and the plan is COPIED onto it rather than read back
// through the template. That is the whole point: lowering Monday's target
// must not retroactively decide that last Monday was met. What was
// asked for on a date is a fact about that date.
//
// done_qty is a count, not a flag, because a half-finished day is the
// normal case — part of the target made, the rest carried forward.
db.exec(`CREATE TABLE IF NOT EXISTS rhythm_task (
date TEXT PRIMARY KEY,
weekday INTEGER NOT NULL,
theme TEXT NOT NULL,
target_qty INTEGER,
strain_id INTEGER,
note TEXT,
done_qty INTEGER NOT NULL DEFAULT 0,
created TEXT NOT NULL,
updated TEXT
)`);
db.exec('CREATE INDEX IF NOT EXISTS idx_rhythm_task_date ON rhythm_task(date)');
}
},
{
version: 59,
description: 'Release permission: per-user can_release capability (set produce aside for sale)',
fn(db) {
// Twin of can_ship, and for the same reason. What a release does is leave
// the building: it decides the amount a shop may offer, so it is not the
// same act as weighing a bag even though it happens in the same second.
//
// Default 0, so the migration itself grants nothing. Admins qualify
// without the column, exactly as with can_ship.
const cols = db.prepare('PRAGMA table_info(users)').all();
if (!cols.some((c) => c.name === 'can_release')) {
db.exec('ALTER TABLE users ADD COLUMN can_release INTEGER DEFAULT 0');
}
}
},
{
version: 60,
description: 'Pickups reported back by the harvest feed receiver, keyed by the id it assigns',
fn(db) {
// The harvest feed pushes numbers out and throws the answer away. That is
// one useful message short: the system that took an order knows when the
// customer said they would collect it, and this machine has no inbound
// route to be told. Reading the reply to a request we already make costs
// no open port, no certificate and no changing home IP.
//
// `id` comes from the receiver and is the primary key on purpose. There is
// no delivery guarantee in either direction, so the receiver repeats every
// open pickup in every reply until we confirm it — which only works if
// storing the same one twice leaves one row. Upsert, not insert.
//
// `from_time`/`to_time` are LOCAL wall-clock times, stored exactly as they
// arrived, with the zone next to them in `tz`. Converting to server time
// would be a lie the moment the two machines disagree about their offset,
// and "9–10" is what the customer was told.
//
// `acked_at` is what closes the loop: NULL means stored here but not yet
// confirmed to the receiver. Those ids ride along on the next push, and
// only a push that actually succeeded may set this — confirming a message
// that never arrived is how a pickup goes missing on both sides.
db.exec(`CREATE TABLE IF NOT EXISTS pickups (
id TEXT PRIMARY KEY,
order_ref TEXT,
slot TEXT,
slot_text TEXT,
place TEXT,
from_time TEXT,
to_time TEXT,
tz TEXT,
items TEXT,
overbooked INTEGER NOT NULL DEFAULT 0,
received TEXT NOT NULL,
updated TEXT NOT NULL,
acked_at TEXT
)`);
db.exec('CREATE INDEX IF NOT EXISTS idx_pickups_open ON pickups(acked_at)');
db.exec('CREATE INDEX IF NOT EXISTS idx_pickups_from ON pickups(from_time)');
}
},
{
version: 61,
description: 'Withdrawn pickups: delete the row, keep a receipt so the withdrawal can be confirmed',
fn(db) {
// A customer cancels after the pickup was already stored here. The
// receiver cannot reach in and delete it — that is the whole point of the
// architecture — so it says so in its next reply instead, and the row goes
// away here. Without that, the pickup stands in the list for ever and
// somebody packs a crate for nobody.
//
// Deleting alone is not enough, and this table is the reason. The
// withdrawal has to be confirmed on the next push exactly like a booking,
// or the receiver has no way of knowing it landed and repeats it for ever.
// Delete the pickup and there is nothing left to carry that obligation —
// so the receipt outlives the row it removed.
//
// Its own table rather than a `cancelled` flag on `pickups`: a withdrawn
// pickup is not a pickup, and a flag would mean every query that lists,
// counts or prepares work has to remember to exclude it. One that forgets
// shows a crate that nobody is coming for, which is the exact failure this
// migration exists to prevent.
//
// Rows appear here for ids that were never stored, too. The receiver
// reports a withdrawal whether or not its earlier reply got through,
// because it cannot tell — so an unknown id is the normal case, not an
// error, and it still needs confirming.
db.exec(`CREATE TABLE IF NOT EXISTS pickup_cancellations (
id TEXT PRIMARY KEY,
at TEXT NOT NULL,
acked_at TEXT
)`);
db.exec('CREATE INDEX IF NOT EXISTS idx_pickup_cancel_open ON pickup_cancellations(acked_at)');
}
},
{
version: 62,
description: 'Pack sizes: the portions a release is handed out in, one ladder for the whole farm',
fn(db) {
// A release says how much may be sold. It does not say in what portions,
// and a shop cannot ask: it has to offer *something*, so it invents a
// ladder of its own. One shop guessed 250 g and multiples of it, which is a
// reasonable guess and still a guess — a farm that packs in 400 g trays
// had no way to say so, and the choice was silently made for it at the
// far end.
//
// One list for every species, deliberately. Portioning is a property of
// how the packing bench works — trays, bags, the scale's step — not of
// the mushroom in them, and a per-species table would be a form to fill
// in for every new Sorte for an answer that is the same every time. The
// release table is the one thing here filled in daily; this is filled in
// once.
//
// Empty is a real answer and the default: nothing chosen means nothing
// sent, and a receiver has no portions to sell produce in. Unlike the
// release switch, that cannot fail quietly — a shop with no sizes has no
// button, which is loud, and the alternative was a ladder nobody decided
// sitting in a shop window looking authoritative.
const cols = db.prepare('PRAGMA table_info(harvest_feed_config)').all();
if (!cols.some((c) => c.name === 'pack_sizes')) {
db.exec("ALTER TABLE harvest_feed_config ADD COLUMN pack_sizes TEXT DEFAULT ''");
}
}
},
{
version: 63,
description: 'Pickup locations as a managed list, and a location on calendar events',
fn(db) {
// Where an appointment happens has had nowhere to live. `LOCATION:` is
// written today, but only on batch-due events, where it is derived from
// the scan log — a custom event carries no location at all.
//
// A list rather than free text on the event, for one reason that only
// shows up later: as soon as appointments are read outside the lab, the
// place is part of what another system consumes, and a receiver matches
// it literally. Two spellings of one market are then two markets, with
// nothing to correct centrally. The same mistake has already cost a
// release its visibility once, over a species name typed by hand.
//
// `active` and not DELETE. A location that closed is still named by every
// pickup that happened there; removing the row would either orphan those
// or, with ON DELETE SET NULL, quietly blank the place on past events.
db.exec(`CREATE TABLE IF NOT EXISTS pickup_locations (
id INTEGER PRIMARY KEY AUTOINCREMENT,
name TEXT NOT NULL,
address TEXT,
sort_order INTEGER NOT NULL DEFAULT 0,
active INTEGER NOT NULL DEFAULT 1
)`);
// The REFERENCES clause is legal on ADD COLUMN only because the default is
// NULL — SQLite rejects it otherwise. Nullable is also the right shape:
// most appointments have no location worth naming.
const has = db
.prepare("SELECT COUNT(*) as c FROM pragma_table_info('calendar_events') WHERE name = 'location_id'")
.get();
if (!has.c) {
db.exec('ALTER TABLE calendar_events ADD COLUMN location_id INTEGER REFERENCES pickup_locations(id)');
}
}
},
{
version: 64,
description: 'Capacity on a pickup window: how many collections it offers',
fn(db) {
// Null, not 0, for "as many as turn up". A hall that is open all afternoon
// has no meaningful number, and 0 already means something else — a window
// that exists but cannot be booked. Making the difference expressible here
// keeps the receiving end from having to guess which zero it was given.
const has = db
.prepare("SELECT COUNT(*) as c FROM pragma_table_info('calendar_events') WHERE name = 'pickup_capacity'")
.get();
if (!has.c) db.exec('ALTER TABLE calendar_events ADD COLUMN pickup_capacity INTEGER');
}
},
{
version: 65,
description: 'Recipe constants for a substrate mix, and corn meal as a stocked material',
fn(db) {
const addCol = (table, col, decl) => {
const has = db.prepare(`SELECT COUNT(*) as c FROM pragma_table_info('${table}') WHERE name='${col}'`).get();
if (!has.c) db.exec(`ALTER TABLE ${table} ADD COLUMN ${col} ${decl}`);
};
// A recipe may have corn meal in it, which is exactly why it has to be a
// real material: a few percent of a large run is several kilos that
// currently leave the building with nothing recording that they did.
addCol('inventory', 'stock_corn', 'REAL DEFAULT 0');
addCol('inventory', 'thresh_corn', 'REAL DEFAULT 0');
// Bagged pellets and bran arrive with moisture already in them, so the dry
// mix needed to hit a target hydration is larger than (1 - moisture)
// suggests. Ignoring it under-books every mix by roughly 8%. Site-wide
// because it is a property of the delivery, not of the species.
addCol('inventory', 'residual_pct', 'REAL DEFAULT 9');
// Only used to turn litres of water into a hose time on the batch card.
addCol('inventory', 'water_flow_lmin', 'REAL DEFAULT 10');
// Recipe constants the pre-existing rec_* set could not express.
// rec_gypsum (0/1) stays for the older per-bag path; rec_gypsum_pct is the
// share of the dry mix, which is what a mix run actually needs.
addCol('mushroom_strains', 'rec_corn_pct', 'REAL DEFAULT 0');
addCol('mushroom_strains', 'rec_gypsum_pct', 'REAL DEFAULT 0');
addCol('mushroom_strains', 'rec_spawn_pct', 'REAL DEFAULT 0');
// Ranges, kept as text on purpose. "45-70 d + browning" is the honest
// answer for shiitake and no single integer replaces it without lying.
// rec_inc_days keeps driving the due date; this is the reference beside it.
addCol('mushroom_strains', 'rec_colon_text', "TEXT DEFAULT ''");
addCol('mushroom_strains', 'rec_steril_text', "TEXT DEFAULT ''");
}
},
{
version: 66,
description: 'Reserved — recipe figures are seeded by a script, not by a migration',
fn() {
// Deliberately does nothing.
//
// This used to write one farm's blends, block size and spawn rate onto every
// strain whose Kuerzel matched. Two things wrong with that. Another lab runs
// this code — see FORKING.md, and the test that keeps the operator name out
// of the product — and their oyster recipe is not this one. And an operator
// who had already tuned rec_* had it silently overwritten on upgrade, with no
// record of what it had been.
//
// The version number stays, so installations that already ran it are not
// asked to run something different under the same number. The seeding moved
// to scripts/seed-substrate-recipes.js, which reads the figures from a file
// outside the repository, shows what it would change and only writes when
// told to.
}
},
{
version: 67,
description: 'Substrate batches: mixed once, drawn from many times',
fn(db) {
// The substrate is mixed in bulk and portioned into bags afterwards, often
// across several species out of one mix. Charging the raw materials per bag
// therefore books the same pellets twice over: once when the mix is made
// and again for every bag drawn from it. This table is the mix itself —
// species-neutral, because at mixing time nobody has decided yet what will
// be grown in it.
//
// remaining_kg is the whole point. A mix that is only half drawn into
// blocks still holds the rest, and that rest is real stock: it exists,
// it cost money, and it will be gone in three days if nobody uses it.
// Deriving it from the bags each time would work until a bag batch is
// deleted, so it is stored and moved explicitly instead.
db.exec(`CREATE TABLE IF NOT EXISTS substrate_batches (
id INTEGER PRIMARY KEY AUTOINCREMENT,
sub_id TEXT NOT NULL UNIQUE,
-- The recipe this was mixed to. Species-neutral in use: any Sorte may draw
-- from it. Kept as a reference AND as a label snapshot, so renaming a Sorte
-- later does not rewrite what a past mix says it was.
recipe_strain_id INTEGER REFERENCES mushroom_strains(id) ON DELETE SET NULL,
recipe_label TEXT NOT NULL DEFAULT '',
target_kg REAL NOT NULL,
remaining_kg REAL NOT NULL,
-- The blend as mixed, not as the recipe reads today.
hardwood_pct REAL DEFAULT 0,
wheatbran_pct REAL DEFAULT 0,
corn_pct REAL DEFAULT 0,
gypsum_pct REAL DEFAULT 0,
rh_pct REAL DEFAULT 0,
-- What it took and what it measured, so the card never re-derives it.
dry_kg REAL DEFAULT 0,
pellets_kg REAL DEFAULT 0,
bran_kg REAL DEFAULT 0,
corn_kg REAL DEFAULT 0,
gypsum_kg REAL DEFAULT 0,
water_l REAL DEFAULT 0,
moisture_pct REAL DEFAULT 0,
notes TEXT DEFAULT '',
created TEXT NOT NULL,
status TEXT NOT NULL DEFAULT 'open'
)`);
db.exec('CREATE INDEX IF NOT EXISTS idx_subbatch_status ON substrate_batches(status, created)');
const addCol = (table, col, decl) => {
const has = db.prepare(`SELECT COUNT(*) as c FROM pragma_table_info('${table}') WHERE name='${col}'`).get();
if (!has.c) db.exec(`ALTER TABLE ${table} ADD COLUMN ${col} ${decl}`);
};
// NULL means the pre-v67 arrangement: a Charge that carries its own
// substrate and was charged for it per bag. Those keep working untouched.
addCol('batches', 'substrate_batch_id', 'INTEGER REFERENCES substrate_batches(id) ON DELETE SET NULL');
// How much was actually taken, stored rather than recomputed from qty ×
// bag_kg: bags can be added or removed afterwards, and the draw that was
// booked has to be the one that gets returned.
addCol('batches', 'substrate_kg', 'REAL DEFAULT 0');
// A blend may contain corn meal — without this its share has nowhere to live.
addCol('batches', 'sub_corn', 'REAL DEFAULT 0');
}
},
{
version: 68,
description: 'Index the link from a Charge to the mix it came out of',
fn(db) {
// readAll joins batches to substrate_batches on every /api/data, which is
// every SSE event, and the remaining figure is recomputed from the Chargen
// referencing a mix on every draw and every delete. Both walked the whole
// batches table without this.
db.exec('CREATE INDEX IF NOT EXISTS idx_batch_subbatch ON batches(substrate_batch_id)');
}
},
{
version: 69,
description: 'Per-Sorte minimum holdings of grain spawn and liquid culture',
fn(db) {
// The lab already compared grain spawn per Sorte against a minimum, but
// the minimum was one number for the whole farm and it was never set, so
// nothing was ever flagged. Shiitake needs 45-70 days of notice and an
// oyster needs a fortnight; one number cannot mean both.
const addCol = (table, col, decl) => {
const has = db.prepare(`SELECT COUNT(*) as c FROM pragma_table_info('${table}') WHERE name='${col}'`).get();
if (!has.c) db.exec(`ALTER TABLE ${table} ADD COLUMN ${col} ${decl}`);
};
// Kilograms, because that is how grain spawn is counted everywhere else.
addCol('mushroom_strains', 'min_spawn_kg', 'REAL DEFAULT 0');
// Jars, because that is how liquid culture is counted.
addCol('mushroom_strains', 'min_lc', 'INTEGER DEFAULT 0');
}
},
{
version: 70,
description: 'Liquid syringes as a lab type of their own',
fn(db) {
// A syringe is not a jar. It is filled from one, kept separately, counted
// separately and sold, so a farm holding twenty jars and no syringes is not
// the same as one holding both — which is all the old shared LC type could
// say.
const has = db
.prepare("SELECT COUNT(*) as c FROM pragma_table_info('inventory') WHERE name='lab_thresh_sy'")
.get();
if (!has.c) db.exec('ALTER TABLE inventory ADD COLUMN lab_thresh_sy INTEGER DEFAULT 0');
}
},
{
version: 71,
description: 'Store session tokens as SHA-256 hashes, like the MCP token',
fn(db) {
// S-15: the column held the same bytes the browser holds, so one read of
// the database file was every live session for up to seven days. Rewrite
// the existing rows in place rather than emptying the table, so a deploy
// does not log everybody out — the cookies people are holding keep
// working, they just no longer match anything readable in the file.
const rows = db.prepare('SELECT token FROM sessions').all();
const upd = db.prepare('UPDATE sessions SET token = ? WHERE token = ?');
for (const r of rows) {
upd.run(crypto.createHash('sha256').update(String(r.token)).digest('hex'), r.token);
}
}
},
{
version: 72,
description: 'Pin the print bridge certificate (trust on first use)',
fn(db) {
// S-23: the bridge's certificate is self-signed by print-bridge.ps1, so
// there is no chain to validate and the request went out with
// rejectUnauthorized:false — an on-path attacker on the LAN could present
// any certificate, take the X-Bridge-Token and hand back whatever it
// liked. The fingerprint is remembered on first connection and checked on
// every one after, so a swap is caught even without a CA. cert_url keeps
// the pin honest when the bridge is moved to a different address.
const cols = db
.prepare("SELECT name FROM pragma_table_info('print_bridge_config')")
.all()
.map((r) => r.name);
if (!cols.includes('cert_fp')) db.exec("ALTER TABLE print_bridge_config ADD COLUMN cert_fp TEXT DEFAULT ''");
if (!cols.includes('cert_url')) db.exec("ALTER TABLE print_bridge_config ADD COLUMN cert_url TEXT DEFAULT ''");
}
},
{
version: 73,
description: 'App-specific passwords for CalDAV clients',
fn(db) {
// S-25: CalDAV authenticates with the account password, so subscribing a
// phone means typing the password that also opens the web UI — as an
// admin, if that is the account — into iOS or Thunderbird, where it sits
// in a keychain, syncs to a cloud backup, and stays there long after the
// device stops being one you control. One credential, every capability,
// and no way to revoke it that does not change the password for
// everything.
db.exec(`
CREATE TABLE IF NOT EXISTS caldav_app_passwords (
id INTEGER PRIMARY KEY AUTOINCREMENT,
user_id INTEGER NOT NULL REFERENCES users(id) ON DELETE CASCADE,
label TEXT NOT NULL,
hash TEXT NOT NULL,
created TEXT NOT NULL,
last_used_at TEXT
)
`);
db.exec('CREATE INDEX IF NOT EXISTS idx_caldav_apppw_user ON caldav_app_passwords(user_id)');
db.exec('CREATE INDEX IF NOT EXISTS idx_caldav_apppw_hash ON caldav_app_passwords(hash)');
}
},
{
version: 74,
description: 'Remember that an OAuth client was actually used, so the sweep cannot mistake it',
fn(db) {
// The sweep of never-used auto-registered clients (S-25) asked "does this
// client have any codes or tokens?". That is a proxy, and it breaks:
// tokens go away when they expire and when deleteAuthArtifactsNoTxn runs
// — which it does on every password change. One colleague changing their
// password therefore put every MCP registration on a one-hour fuse.
//
// A mark that is only ever set is immune to all of that. NULL means the
// registration never completed a flow, which is the only thing the sweep
// is entitled to act on.
db.exec(`ALTER TABLE oauth_clients ADD COLUMN last_used TEXT DEFAULT NULL`);
// Existing rows: anything that ever got a code or a token has been used,
// whatever has become of those rows since. The rest stay NULL and are
// judged on their own merits.
db.exec(`UPDATE oauth_clients SET last_used = created
WHERE client_id IN (SELECT client_id FROM oauth_tokens)
OR client_id IN (SELECT client_id FROM oauth_codes)`);
}
},
{
version: 75,
description: 'Fold away the duplicate listing mappings the broken upsert left behind',
fn(db) {
// mapListing() meant to correct a mapping and instead added a second row
// beside it: SQLite counts NULLs as distinct in a UNIQUE index, so
// (channel, 'SKU-1001', NULL) never collided with itself and the ON CONFLICT
// never fired. Every mapping made without a listing id — which is every one
// the mapping screen makes — could therefore exist several times over, each
// copy pointing at whatever product was chosen that day.
//
// Stopping the leak does not empty the bucket: the rows are already in
// every database this has ever run against, and resolveProductId takes
// whichever row SQLite hands back first, so an old wrong mapping can still
// outvote the correction that was supposed to replace it.
//
// The newest row wins, by id. That is the correction: whoever last chose a
// product for this article number meant it.
db.exec(`DELETE FROM product_channel_map
WHERE id NOT IN (
SELECT MAX(id) FROM product_channel_map
GROUP BY channel, IFNULL(channel_sku, char(31)), IFNULL(listing_id, char(31))
)`);
}
},
{
version: 76,
description: 'Give the out-of-process DuckDNS fallback its own column',
fn(db) {
// `last_ip_update` answered one question — "when did the in-process
// updater last succeed?" — and the whole admin banner is built on it:
// older than STALE_AFTER_MS means that updater is dead, and the banner
// goes red.
//
// The systemd/Task Scheduler fallback then started writing the same
// column, and that broke two things at once. It hid a permanently broken
// in-process updater, because a fallback refreshing the column every few
// minutes means the staleness alarm can never fire. And it made the
// fallback measure itself: it stood down whenever the column was fresh,
// including when *it* was what made it fresh, so during a real outage it
// updated every fifteen minutes instead of every five.
//
// One column, two writers, three questions. Splitting them is the fix:
// `last_ip_update` goes back to meaning only the server, and the fallback
// records itself here. Neither can now mask or throttle the other.
db.exec('ALTER TABLE duckdns_config ADD COLUMN fallback_last TEXT');
}
},
{
version: 77,
description: 'Mute the seeded zone colours, leaving any the operator chose alone',
fn(db) {
// Zone colours are data, so changing ZONE_SEED_COLOR only reaches
// installations that do not exist yet. This carries it to the ones that
// do — and must not tread on anybody's choice while doing so.
//
// The rule is the narrowest one that works: a row is upgraded only if its
// colour is still *exactly* the value the seed wrote for that role. Any
// other value — a hand-picked colour, a shade from an older seed, a zone
// added later — is an opinion, and opinions are left alone. Matching on
// the role rather than the id is what lets a fourth tent, created by the
// operator but never recoloured, come along too.
//
// Case-insensitive because the colour input yields lowercase while older
// rows were seeded from these literals; comparing raw would skip exactly
// the rows this is for. CONTAM is in the table and unchanged, so its
// branch is a no-op — it is listed so that a future change to the red
// needs no new migration shape, only a new value.
const upd = db.prepare('UPDATE zones SET color = ? WHERE role = ? AND lower(color) = lower(?)');
for (const role of Object.keys(ZONE_SEED_COLOR)) {
if (ZONE_SEED_COLOR[role] === ZONE_LEGACY_COLOR[role]) continue;
upd.run(ZONE_SEED_COLOR[role], role, ZONE_LEGACY_COLOR[role]);
}
}
},
{
version: 78,
description: 'Mark which Sorten are currently in the growing programme',
fn(db) {
// Which Sorten the farm is actually growing right now. It changes with the
// season — some Sorten in winter, others through the summer — and the app
// had nowhere to say so, which meant every derived judgement about a
// Sorte had to assume all of them are always wanted.
//
// That assumption is what makes a supply warning useless: a Sorte nobody
// is growing this half of the year has no grain and nothing incubating by
// definition, so it reports "start one now" every single day, forever. A
// warning that is always on is not a warning, and worse, it drags the
// real ones down with it.
//
// Defaults to 1. On an existing database every Sorte is in the programme
// until somebody says otherwise, which is the only safe direction: the
// alternative silently switches off warnings the farm may be relying on.
const has = db
.prepare("SELECT COUNT(*) AS c FROM pragma_table_info('mushroom_strains') WHERE name='im_programm'")
.get();
if (!has.c) db.exec('ALTER TABLE mushroom_strains ADD COLUMN im_programm INTEGER DEFAULT 1');
}
},
{
version: 79,
description: 'A minimum of its own for slants and petri dishes, beside the one liquid culture had',
fn(db) {
// v69 gave a Sorte two minimums: min_spawn_kg for grain, min_lc for
// liquid culture. strainMinFor() then read min_lc for every culture type
// that was not grain, so slants and petri dishes were held to the
// liquid-culture figure — or, in the branch that returned 0, to nothing
// at all.
//
// They are three different things kept for three different reasons: a
// slant is the long-term backup, a plate is what you work from, a jar of
// LC is what goes into grain. They are stocked in different numbers, and
// one figure could not be right for all three.
//
// Zero means "no minimum set", exactly as min_lc already did, so nothing
// begins warning until somebody enters a number.
const cols = db
.prepare("SELECT name FROM pragma_table_info('mushroom_strains')")
.all()
.map((r) => r.name);
if (!cols.includes('min_mc')) db.exec('ALTER TABLE mushroom_strains ADD COLUMN min_mc REAL DEFAULT 0');
if (!cols.includes('min_pd')) db.exec('ALTER TABLE mushroom_strains ADD COLUMN min_pd REAL DEFAULT 0');
}
},
{
version: 80,
description: 'A weekly bag goal, and the date the counting starts from',
fn(db) {
// Zwei Zahlen, die zusammengehören: wie viele Beutel eine Woche bringen
// soll, und ab wann gezählt wird.
//
// Das Ab-wann ist nicht Zierrat. Sind die Bestandsdaten einer Anlage einmal
// zurückgesetzt worden, würde alles davor als verfehlte Woche dastehen —
// ein Ziel, das mit einer Reihe roter Wochen aufmacht, die niemand mehr
// ändern kann, ist beim ersten Blick schon entwertet.
//
// 0 heißt "kein Ziel gesetzt", wie bei den übrigen Untergrenzen auch:
// nichts wird gemessen, bevor jemand eine Zahl nennt.
const cols = db
.prepare("SELECT name FROM pragma_table_info('inventory')")
.all()
.map((r) => r.name);
if (!cols.includes('week_bag_goal')) db.exec('ALTER TABLE inventory ADD COLUMN week_bag_goal INTEGER DEFAULT 0');
if (!cols.includes('bag_count_from')) db.exec('ALTER TABLE inventory ADD COLUMN bag_count_from TEXT');
}
},
{
version: 81,
description: 'Recurring jobs of the farm’s own naming, on their own interval',
fn(db) {
// Der Wochenrhythmus kann nur, was gezählt werden kann.
//
// Seine Themen sind fest verdrahtet, weil hinter jedem eine Buchung
// steht, aus der sich "gemacht" ableiten lässt: Chargen sind Chargen,
// Körnerbrut sind Gläser. Das ist die Stärke des Rhythmus und zugleich
// seine Grenze — "alle zwei Wochen die Growrooms putzen" ist ebenso
// wiederkehrende Arbeit, hinterlässt aber keine Buchung, aus der sich
// etwas ableiten liesse, und ein festes Thema dafür zu erfinden hiesse,
// beim nächsten Einfall wieder eines zu erfinden.
//
// Also zwei getrennte Dinge nebeneinander statt eines, das beides halb
// kann: der Rhythmus zählt Produktion, und daneben stehen Aufgaben, die
// die Anlage selbst benennt und selbst abhakt.
//
// anchor ist ein Datum, an dem die Aufgabe fällt, und damit beides
// zugleich: der Wochentag und die Phase des Takts. Ein zweites Feld für
// den Wochentag wäre eine zweite Wahrheit, die dem anchor widersprechen
// könnte — dieselbe Falle, aus der der Rhythmus gerade erst herausgeholt
// wurde.
db.exec(`CREATE TABLE IF NOT EXISTS recurring_task (
id INTEGER PRIMARY KEY AUTOINCREMENT,
name TEXT NOT NULL,
every_weeks INTEGER NOT NULL DEFAULT 1,
anchor TEXT NOT NULL,
active INTEGER NOT NULL DEFAULT 1,
created TEXT NOT NULL
)`);
// Ein Haken je Termin, nicht je Aufgabe: welcher Montag geputzt wurde,
// ist die Frage, und ein Zähler auf der Aufgabe könnte sie nicht
// beantworten. Der Primärschlüssel macht doppeltes Tippen wirkungslos.
db.exec(`CREATE TABLE IF NOT EXISTS recurring_done (
task_id INTEGER NOT NULL REFERENCES recurring_task(id) ON DELETE CASCADE,
date TEXT NOT NULL,
done TEXT NOT NULL,
who TEXT,
PRIMARY KEY (task_id, date)
)`);
db.exec('CREATE INDEX IF NOT EXISTS idx_recurring_done_date ON recurring_done(date)');
// Und die Themen, die nie ein Wochentag war, verschwinden aus der Auswahl.
// Fruchtung, Ernte und Labor standen im Dropdown, weil sie zählbar sind —
// geplant hat sie niemand, denn sie folgen aus der Arbeit, statt sie
// vorzugeben. Sechs Einträge, von denen zwei benutzt werden, machen die
// Liste zur Suchaufgabe.
//
// Nur die Vorlage wird umgeschrieben. Aufgezeichnete Tage bleiben, wie sie
// waren, und werden weiterhin gezählt — eine Aufnahme der Vergangenheit
// anzufassen, weil sich das Menü geändert hat, wäre die Umkehrung von
// allem, was an dieser Stelle gilt.
db.exec("UPDATE week_rhythm SET theme = 'free', target_qty = NULL WHERE theme IN ('fruiting', 'harvest', 'lab')");
}
},
{
version: 82,
description: 'Gemessene Ist-Feuchte am Substrat-Ansatz, getrennt vom Rezeptwert',
fn(db) {
// Die Karte des Ansatzes zeigte eine Zeile "Ist-Feuchte" und darin den
// Wert, den computeMixBatch aus dem Rezept gerechnet hatte. Das ist die
// Soll-Feuchte: was der Ansatz treffen sollte, nicht was er getroffen hat.
// Wer nach dem Mischen nachmisst, hatte für das Ergebnis keinen Platz und
// fand seine eigene Messung dem Namen nach schon dort stehen.
//
// NULL heisst "noch nicht gemessen", und das ist nicht dasselbe wie 0 %.
// Ein DEFAULT 0 wie bei den Nachbarspalten würde jeden Ansatz, den es
// heute schon gibt, als knochentrocken gemessen ausweisen. Nur eine Zahl,
// die jemand eingetragen hat, ist eine Messung.
const has = db
.prepare("SELECT COUNT(*) AS c FROM pragma_table_info('substrate_batches') WHERE name='actual_rh_pct'")
.get();
if (!has.c) {
db.exec('ALTER TABLE substrate_batches ADD COLUMN actual_rh_pct REAL');
// Zeitpunkt und Person gehören zur Messung. Eine Zahl allein lässt
// offen, ob sie von direkt nach dem Mischen stammt oder von drei Tagen
// später, und beim Korrigieren wäre nicht mehr zu sehen, dass sie
// korrigiert wurde.
db.exec('ALTER TABLE substrate_batches ADD COLUMN actual_rh_at TEXT');
db.exec('ALTER TABLE substrate_batches ADD COLUMN actual_rh_by TEXT');
}
}
},
{
version: 83,
description: 'Im Autoklav geplatzte Beutel je Ansatz zaehlen',
fn(db) {
// Beutel platzen im Autoklav unter dem Druck. Das kostet Substrat, und
// wie oft es passiert ist die eigentliche Frage — bei welchem Rezept,
// welcher Fuellmenge, welcher Charge Pellets. Bisher konnte man es nur in
// den Kommentar schreiben, und ein Kommentar laesst sich nicht zaehlen.
//
// NOT NULL DEFAULT 0, anders als die Ist-Feuchte daneben. Die ist eine
// Messung: 0 % waere dort eine Behauptung, und "nicht gemessen" muss sich
// davon unterscheiden. Hier wird ein Ereignis gezaehlt, und ein Ansatz,
// an dem niemand etwas eingetragen hat, ist einer, an dem nichts geplatzt
// ist. Waere das NULL, muesste jede Auswertung "unbekannt" von "keins"
// trennen und koennte die Frage "wie oft passiert das" gar nicht
// beantworten.
const has = db
.prepare("SELECT COUNT(*) AS c FROM pragma_table_info('substrate_batches') WHERE name='burst_bags'")
.get();
if (!has.c) db.exec('ALTER TABLE substrate_batches ADD COLUMN burst_bags INTEGER NOT NULL DEFAULT 0');
}
},
{
version: 84,
description: 'Versuche und ihre Ansaetze',
fn(db) {
// Die Schema-DDL legt beide Tabellen auf einer neuen Datenbank an; hier
// kommen sie in eine, die es schon gibt. Dieselbe Definition, damit die
// beiden Wege nicht auseinanderlaufen.
db.exec(`CREATE TABLE IF NOT EXISTS experiments (
id INTEGER PRIMARY KEY AUTOINCREMENT,
code TEXT NOT NULL UNIQUE,
title TEXT NOT NULL,
started TEXT NOT NULL,
ended TEXT,
status TEXT NOT NULL DEFAULT 'running',
archived INTEGER NOT NULL DEFAULT 0,
recipe TEXT DEFAULT '',
outcome TEXT DEFAULT ''
)`);
db.exec(`CREATE TABLE IF NOT EXISTS experiment_runs (
id INTEGER PRIMARY KEY AUTOINCREMENT,
experiment_id INTEGER NOT NULL REFERENCES experiments(id) ON DELETE CASCADE,
code TEXT NOT NULL,
what TEXT DEFAULT '',
result TEXT DEFAULT '',
outcome TEXT NOT NULL DEFAULT 'open',
culture_id TEXT REFERENCES cultures(id) ON DELETE SET NULL,
batch_id TEXT REFERENCES batches(batch_id) ON DELETE SET NULL,
sort INTEGER NOT NULL DEFAULT 0
)`);
db.exec('CREATE INDEX IF NOT EXISTS idx_exp_runs ON experiment_runs(experiment_id, sort)');
}
},
{
version: 85,
description: 'Fotos am Versuch',
fn(db) {
db.exec(`CREATE TABLE IF NOT EXISTS experiment_photos (
id INTEGER PRIMARY KEY AUTOINCREMENT,
experiment_id INTEGER NOT NULL REFERENCES experiments(id) ON DELETE CASCADE,
run_id INTEGER REFERENCES experiment_runs(id) ON DELETE SET NULL,
uuid TEXT NOT NULL UNIQUE,
rel_path TEXT NOT NULL,
thumb_path TEXT NOT NULL,
width INTEGER,
height INTEGER,
bytes INTEGER NOT NULL,
sha256 TEXT NOT NULL,
uploaded_at TEXT NOT NULL,
uploaded_by INTEGER REFERENCES users(id) ON DELETE SET NULL
)`);
db.exec('CREATE INDEX IF NOT EXISTS idx_exp_photos ON experiment_photos(experiment_id, uploaded_at)');
}
},
{
version: 86,
description: 'Notizen je Vorhaben-Reiter',
fn(db) {
db.exec(`CREATE TABLE IF NOT EXISTS project_notes (
id INTEGER PRIMARY KEY AUTOINCREMENT,
tab TEXT NOT NULL,
body TEXT NOT NULL,
created TEXT NOT NULL,
updated TEXT
)`);
db.exec('CREATE INDEX IF NOT EXISTS idx_project_notes ON project_notes(tab, id DESC)');
}
},
{
version: 87,
description: 'Stämme je Pilzsorte',
fn(db) {
// Das Vokabular zum Stamm-Feld an Charge und Kultur. Eindeutig je Sorte und
// ohne Rücksicht auf Groß- und Kleinschreibung: "Nordlicht" und "nordlicht" beim
// Lions Mane sind ein Stamm, "Nordlicht" beim Shiitake ein anderer.
db.exec(`CREATE TABLE IF NOT EXISTS staemme (
id INTEGER PRIMARY KEY AUTOINCREMENT,
strain_id INTEGER NOT NULL REFERENCES mushroom_strains(id) ON DELETE CASCADE,
name TEXT NOT NULL,
notes TEXT DEFAULT '',
created TEXT NOT NULL,
updated TEXT
)`);
db.exec('CREATE UNIQUE INDEX IF NOT EXISTS idx_staemme_name ON staemme(strain_id, name COLLATE NOCASE)');
}
},
{
version: 88,
description: 'Vorhaben-Texte in der Datenbank statt im Quelltext',
fn(db) {
db.exec(`CREATE TABLE IF NOT EXISTS project_docs (
tab TEXT PRIMARY KEY,
doc TEXT NOT NULL,
updated TEXT NOT NULL,
updated_by INTEGER REFERENCES users(id) ON DELETE SET NULL
)`);
}
},
{
version: 89,
description: 'Verlauf des Laborbuchs',
fn(db) {
// Wer wann was geaendert hat, an Versuchen, Gefaessen, Fotos und Notizen.
// Was die Spalten bedeuten, steht im Abschnitt "Verlauf" bei den Versuchen.
//
// Kein Fremdschluessel, weder auf users noch auf die Datensaetze: ON DELETE
// SET NULL oder CASCADE waeren Aenderungen an einer Tabelle, die sich nie
// aendern soll, und die Trigger unten wuerden das Loeschen eines Benutzers
// oder Versuchs dann ganz verhindern. Der Name steht deshalb als Abschrift
// mit drin: ein Eintrag sagt auch dann noch, wer es war, wenn es das Konto
// nicht mehr gibt.
db.exec(`CREATE TABLE IF NOT EXISTS lab_history (
id INTEGER PRIMARY KEY AUTOINCREMENT,
at TEXT NOT NULL,
user_id INTEGER,
username TEXT NOT NULL DEFAULT '',
entity TEXT NOT NULL,
entity_id INTEGER NOT NULL,
experiment_id INTEGER,
action TEXT NOT NULL,
field TEXT,
old_value TEXT,
new_value TEXT
)`);
db.exec('CREATE INDEX IF NOT EXISTS idx_lab_history_exp ON lab_history(experiment_id, id)');
db.exec('CREATE INDEX IF NOT EXISTS idx_lab_history_entity ON lab_history(entity, entity_id, id)');
// Nur anhaengen. Die Anwendung hat keinen Weg, einen Eintrag zu aendern
// oder zu loeschen; die Trigger halten auch jeden anderen auf, der ueber
// dieselbe Datei kommt. Wer wirklich einmal etwas herausnehmen muss, muss
// sie erst mit DROP TRIGGER entfernen, und das ist dann eine bewusste
// Handlung.
db.exec(`CREATE TRIGGER IF NOT EXISTS lab_history_no_update BEFORE UPDATE ON lab_history
BEGIN
SELECT RAISE(ABORT, 'lab_history is append-only');
END`);
db.exec(`CREATE TRIGGER IF NOT EXISTS lab_history_no_delete BEFORE DELETE ON lab_history
BEGIN
SELECT RAISE(ABORT, 'lab_history is append-only');
END`);
// Ein Ausgangsstand je vorhandenem Datensatz. Ohne ihn beginge der erste
// Eintrag nach dieser Migration bei einem "vorher", das nirgends steht,
// und ein Gefaess, das nie wieder angefasst wird, kaeme im Verlauf gar
// nicht vor. Wer es angelegt hat und wann, weiss niemand mehr; der Eintrag
// sagt deshalb nur, was da war, als der Verlauf begann.
const jetzt = new Date().toISOString();
const eintragen = db.prepare(
'INSERT INTO lab_history(at,entity,entity_id,experiment_id,action,new_value) VALUES(?,?,?,?,?,?)'
);
for (const e of db.prepare('SELECT * FROM experiments ORDER BY id').all()) {
eintragen.run(jetzt, 'experiment', e.id, e.id, 'baseline', JSON.stringify(_versuchStand89(e)));
}
for (const r of db.prepare('SELECT * FROM experiment_runs ORDER BY id').all()) {
eintragen.run(jetzt, 'run', r.id, r.experiment_id, 'baseline', JSON.stringify(_ansatzStand(r)));
}
for (const f of db.prepare('SELECT * FROM experiment_photos ORDER BY id').all()) {
eintragen.run(jetzt, 'photo', f.id, f.experiment_id, 'baseline', JSON.stringify(_fotoStand(f)));
}
for (const n of db.prepare('SELECT * FROM project_notes ORDER BY id').all()) {
eintragen.run(jetzt, 'note', n.id, null, 'baseline', JSON.stringify(_notizStand(n)));
}
}
},
{
version: 90,
description: 'Gefaesse und Fotos werden entfernt, nicht geloescht',
fn(db) {
// Wann und von wem ein Gefaess oder ein Foto entfernt wurde. Solange
// removed_at leer ist, steht die Zeile in der Liste; gesetzt, steht sie
// unter "Entfernte" und laesst sich zurueckholen. Siehe _entfernen().
for (const tabelle of ['experiment_runs', 'experiment_photos']) {
const spalten = db
.prepare(`SELECT name FROM pragma_table_info('${tabelle}')`)
.all()
.map((s) => s.name);
if (!spalten.includes('removed_at')) db.exec(`ALTER TABLE ${tabelle} ADD COLUMN removed_at TEXT`);
if (!spalten.includes('removed_by')) {
db.exec(`ALTER TABLE ${tabelle} ADD COLUMN removed_by INTEGER REFERENCES users(id) ON DELETE SET NULL`);
}
}
}
},
{
version: 91,
description: 'Kontaminationsfotos: derselbe Inhalt darf in mehreren Berichten stehen',
fn(db) {
// v36 hat sha256 eindeutig gemacht, ueber die ganze Tabelle. Seit ein
// Bericht mehrere Beutel abdeckt, bekommt jeder Beutel seine eigene Kopie
// der Fotos: dieselben Bytes, dieselbe Pruefsumme. Die zweite Kopie
// scheiterte an diesem Index, die Transaktion rollte zurueck, und von der
// ganzen Meldung blieb nichts. Dasselbe Bild in einem spaeteren Bericht
// scheiterte genauso.
//
// Eindeutig je Bericht waere auch falsch: wer im Formular dasselbe Bild
// zweimal auswaehlt, verloere damit die Meldung. Die Pruefsumme sagt, dass
// zwei Dateien gleich sind, nicht dass es nur eine geben darf. Der Index
// bleibt, nur ohne UNIQUE.
db.exec('DROP INDEX IF EXISTS idx_contam_photos_sha');
db.exec('CREATE INDEX IF NOT EXISTS idx_contam_photos_sha ON contamination_photos(sha256)');
}
},
{
version: 92,
description: 'Notizen aus dem Verlauf des Laborbuchs',
fn(db) {
// Notizen stehen nicht mehr im Verlauf (siehe insertProjectNote): eine
// Notiz ist ein Zettel, und was jemand loescht, soll weg sein, so wie es
// der Dialog sagt. v89 und die Notizfunktionen bis hierher haben jede
// Fassung hineingeschrieben. Die gehen hier heraus, einmal und bewusst:
// dafuer wird der Loesch-Trigger abgehoben, openDb legt ihn danach
// wieder an. secure_delete ueberschreibt die freien Seiten, damit der
// Text nicht in der Datei liegen bleibt; openDb leert danach das WAL.
//
// Dazu der Index nach Art und Datensatz, den keine Abfrage benutzt.
db.exec('PRAGMA secure_delete = ON');
db.exec('DROP TRIGGER IF EXISTS lab_history_no_delete');
const weg = db.prepare("DELETE FROM lab_history WHERE entity='note'").run().changes;
db.exec('PRAGMA secure_delete = OFF');
db.exec('DROP INDEX IF EXISTS idx_lab_history_entity');
if (weg) _walLeeren = true;
}
},
{
version: 93,
description: 'Arbeitspakete, und ein Versuch gehoert zu einem',
fn(db) {
// Ein Vorhaben kann in Arbeitspakete gegliedert sein, und der
// Bericht darueber stellt das Ergebnis je Paket seinem Ziel
// gegenueber. Welche Pakete es gibt, steht im Plan und kommt ueber die
// Oberflaeche herein; hier steht nur die Form. Ausgeblendet wird, nicht
// geloescht: ein Eintrag im Verlauf, der auf ein Paket zeigt, soll es
// auch in fuenf Jahren noch finden.
db.exec(`CREATE TABLE IF NOT EXISTS work_packages (
id INTEGER PRIMARY KEY AUTOINCREMENT,
code TEXT NOT NULL,
title TEXT NOT NULL,
archived INTEGER NOT NULL DEFAULT 0,
sort INTEGER NOT NULL DEFAULT 0,
created TEXT NOT NULL
)`);
db.exec('CREATE UNIQUE INDEX IF NOT EXISTS idx_work_packages_code ON work_packages(code COLLATE NOCASE)');
const spalten = db
.prepare("SELECT name FROM pragma_table_info('experiments')")
.all()
.map((s) => s.name);
if (!spalten.includes('work_package_id')) {
db.exec('ALTER TABLE experiments ADD COLUMN work_package_id INTEGER REFERENCES work_packages(id)');
}
}
},
{
version: 94,
description: 'Reihenfolge der Startseiten-Kacheln je Benutzer',
fn(db) {
// JSON-Liste der Kachel-Kürzel, NULL = Standard. Am Benutzer und nicht am
// Gerät: wer sich auf einem anderen Telefon anmeldet, findet seine
// Reihenfolge dort wieder.
const spalten = db
.prepare("SELECT name FROM pragma_table_info('users')")
.all()
.map((s) => s.name);
if (!spalten.includes('tile_order')) db.exec('ALTER TABLE users ADD COLUMN tile_order TEXT');
}
},
{
version: 95,
description: 'Versuchsfotos behalten ihr Original in voller Aufloesung',
fn(db) {
// Das Bild, das die Oberflaeche zeigt, ist auf 1280 px verkleinert; was
// die Kamera aufgenommen hat, liegt daneben als <uuid>_orig.jpg, ohne
// Standort (scripts/jpeg-location.js). Die Pruefsumme ist die der Datei,
// wie sie gespeichert ist. Leer bei Fotos von vorher und bei Bildern,
// die kein JPEG waren: von denen gibt es nur die verkleinerte Fassung.
const spalten = db
.prepare("SELECT name FROM pragma_table_info('experiment_photos')")
.all()
.map((s) => s.name);
const neu = [
['original_path', 'TEXT'],
['original_bytes', 'INTEGER'],
['original_sha256', 'TEXT'],
['original_width', 'INTEGER'],
['original_height', 'INTEGER']
];
for (const [name, typ] of neu) {
if (!spalten.includes(name)) db.exec('ALTER TABLE experiment_photos ADD COLUMN ' + name + ' ' + typ);
}
}
},
{
version: 96,
description: 'Arbeitspakete im Verlauf des Laborbuchs',
fn(db) {
// Seit hier steht im Verlauf, wer ein Arbeitspaket angelegt, umbenannt
// oder ausgeblendet hat (siehe insertWorkPackage). Fuer die Pakete, die
// es schon gibt, ein Ausgangsstand wie in v89: was da war, als der
// Verlauf der Pakete begann. Wer sie angelegt hat, weiss niemand mehr;
// wann, sagt die Spalte created, und die steht deshalb mit drin. Die
// Felder stehen einzeln da und nicht als _arbeitspaketStand, damit v96
// immer dasselbe schreibt.
const jetzt = new Date().toISOString();
const eintragen = db.prepare(
'INSERT INTO lab_history(at,entity,entity_id,experiment_id,action,new_value) VALUES(?,?,?,NULL,?,?)'
);
for (const w of db.prepare('SELECT * FROM work_packages ORDER BY id').all()) {
const stand = { code: w.code, title: w.title, archived: w.archived === 1, created: w.created };
eintragen.run(jetzt, 'work_package', w.id, 'baseline', JSON.stringify(stand));
}
}
},
{
version: 97,
description: 'Pruefkette ueber den Verlauf des Laborbuchs, und Zeitstempel auf ihr',
fn(db) {
// Jeder Eintrag im Verlauf bekommt ein Glied: den SHA-256 ueber das Glied
// davor und den Eintrag selbst (_ketteGlied). Wer einen Eintrag spaeter in
// der Datei aendert, loescht oder dazwischenschiebt, bricht die Kette an
// dieser Stelle; wer danach alle Glieder neu rechnet, passt nicht mehr zu
// den Zeitstempeln, die eine Stelle draussen auf fruehere Glieder gegeben
// hat (scripts/lab-timestamps.js). Siehe "Pruefkette" bei den Versuchen.
//
// Hier bekommt jeder Eintrag, den es schon gibt, sein Glied, in der
// Reihenfolge seiner Kennung. Was vor dieser Migration im Verlauf
// geaendert worden waere, kann die Kette nicht zeigen: sie beginnt hier.
db.exec(`CREATE TABLE IF NOT EXISTS lab_history_chain (
history_id INTEGER PRIMARY KEY,
hash TEXT NOT NULL
)`);
db.exec(`CREATE TABLE IF NOT EXISTS lab_timestamps (
id INTEGER PRIMARY KEY AUTOINCREMENT,
history_id INTEGER NOT NULL,
hash TEXT NOT NULL,
tsa TEXT NOT NULL,
requested TEXT NOT NULL,
gen_time TEXT NOT NULL,
serial TEXT NOT NULL,
token BLOB NOT NULL
)`);
_ketteUebernehmen(db);
}
},
{
version: 98,
description: 'Restfeuchte der Kleie getrennt von der der Pellets',
fn(db) {
// Kleie kommt feuchter an als Pellets. Mit einer Zahl fuer beide war das
// Wasser jedes Ansatzes um den Unterschied zu hoch. Leer heisst: wie die
// Pellets (residual_pct), also aendert sich nichts, bis jemand misst.
const has = db
.prepare("SELECT COUNT(*) AS c FROM pragma_table_info('inventory') WHERE name='residual_bran_pct'")
.get().c;
if (!has) db.exec('ALTER TABLE inventory ADD COLUMN residual_bran_pct REAL');
}
},
{
version: 99,
description: 'Wasser: tatsaechlich zugegeben, vorgeschlagen, und wie viel zurueckgehalten wird',
fn(db) {
const col = (t, c) => db.prepare('SELECT COUNT(*) AS c FROM pragma_table_info(?) WHERE name=?').get(t, c).c > 0;
// Was am Hahn wirklich hineinging. Zusammen mit der gemessenen Feuchte
// sagt es, wie weit die Rechnung daneben lag (siehe waterFactorOf).
if (!col('substrate_batches', 'actual_water_l'))
db.exec('ALTER TABLE substrate_batches ADD COLUMN actual_water_l REAL');
// Was der Ansatz zum Zugeben vorschlug, nach Lernfaktor und Rueckhalt.
if (!col('substrate_batches', 'water_now_l'))
db.exec('ALTER TABLE substrate_batches ADD COLUMN water_now_l REAL');
// Anteil, der erst nach dem Messen zugegeben wird. Leer = Vorgabe.
if (!col('inventory', 'water_holdback_pct')) db.exec('ALTER TABLE inventory ADD COLUMN water_holdback_pct REAL');
}
},
{
version: 100,
description: 'Spritzenbestand je Art an Billbee: SKU und was zuletzt gemeldet wurde',
fn(db) {
// Eine Zeile je Art, die eine Billbee-SKU hat. pushed_count ist die
// Laborzahl, die Billbee zuletzt angenommen hat: nur wenn die heutige davon
// abweicht, geht etwas hinaus. Leer heisst "noch nie gemeldet".
db.exec(`CREATE TABLE IF NOT EXISTS syringe_stock_sku (
strain_id INTEGER PRIMARY KEY REFERENCES mushroom_strains(id) ON DELETE CASCADE,
sku TEXT NOT NULL UNIQUE,
pushed_count INTEGER,
pushed_at TEXT,
billbee_stock REAL,
last_error TEXT,
last_try TEXT
)`);
}
},
{
version: 101,
description: 'Packliste: Gepackt und Hergestellt als eigene Marken der App, mit wer und wann',
fn(db) {
// Billbee's own state cannot say "packed": its rules buy the label and set
// "Versendet" before anybody has packed the parcel. So the packing board
// keeps its own marks, keyed on the channel's order id so they survive any
// rebuild of the order row. Nothing here is ever written back to Billbee.
db.exec(`CREATE TABLE IF NOT EXISTS order_marks (
channel TEXT NOT NULL,
channel_order_id TEXT NOT NULL,
kind TEXT NOT NULL CHECK (kind IN ('packed', 'made')),
marked_by TEXT,
marked_at TEXT NOT NULL,
PRIMARY KEY (channel, channel_order_id, kind)
)`);
// When the marks began. An order whose label was bought before this was
// packed without anyone being able to tap it, so the board counts it as
// done instead of listing a month of history as "heute packen".
db.prepare('INSERT OR IGNORE INTO meta(key, value) VALUES(?, ?)').run(
'order_marks_since',
new Date().toISOString()
);
}
},
{
version: 102,
description: 'Spritzen in 10, 20 und 50 ml: Groesse je Spritze, SKU je Art und Groesse',
fn(db) {
// Bis hierher gab es nur eine Spritze, und die war 10 ml: so heissen die
// Billbee-Artikel (LC10-…), und so ist jede vorhandene Spritze gemeint.
const has = db.prepare("SELECT COUNT(*) as c FROM pragma_table_info('cultures') WHERE name='ml'").get();
if (!has.c) db.exec('ALTER TABLE cultures ADD COLUMN ml INTEGER');
db.exec("UPDATE cultures SET ml = 10 WHERE type = 'SY' AND ml IS NULL");
// Eine 50-ml-Spritze ist im Shop ein anderer Artikel als eine 10er. Also
// ist die SKU je Art und Groesse; die vorhandenen gehoeren zu 10 ml.
db.exec(`CREATE TABLE syringe_stock_sku_neu (
strain_id INTEGER NOT NULL REFERENCES mushroom_strains(id) ON DELETE CASCADE,
ml INTEGER NOT NULL DEFAULT 10,
sku TEXT NOT NULL UNIQUE,
pushed_count INTEGER,
pushed_at TEXT,
billbee_stock REAL,
last_error TEXT,
last_try TEXT,
PRIMARY KEY (strain_id, ml)
)`);
db.exec(`INSERT INTO syringe_stock_sku_neu(strain_id, ml, sku, pushed_count, pushed_at, billbee_stock, last_error, last_try)
SELECT strain_id, 10, sku, pushed_count, pushed_at, billbee_stock, last_error, last_try FROM syringe_stock_sku`);
db.exec('DROP TABLE syringe_stock_sku');
db.exec('ALTER TABLE syringe_stock_sku_neu RENAME TO syringe_stock_sku');
}
},
{
version: 103,
description: 'Petrischalen an Billbee: SKU je Art und Kulturtyp, nicht nur je Spritzengroesse',
fn(db) {
// Petrischalen werden verkauft wie Spritzen und bekommen ihren Bestand
// ebenso aus dem Labor. Eine Petrischale hat keine Groesse, also traegt
// ihre Zeile ml 0; die vorhandenen Zeilen sind alle Spritzen.
db.exec(`CREATE TABLE syringe_stock_sku_neu (
strain_id INTEGER NOT NULL REFERENCES mushroom_strains(id) ON DELETE CASCADE,
type TEXT NOT NULL DEFAULT 'SY' CHECK (type IN ('SY', 'PD')),
ml INTEGER NOT NULL DEFAULT 10,
sku TEXT NOT NULL UNIQUE,
pushed_count INTEGER,
pushed_at TEXT,
billbee_stock REAL,
last_error TEXT,
last_try TEXT,
PRIMARY KEY (strain_id, type, ml)
)`);
db.exec(`INSERT INTO syringe_stock_sku_neu(strain_id, type, ml, sku, pushed_count, pushed_at, billbee_stock, last_error, last_try)
SELECT strain_id, 'SY', ml, sku, pushed_count, pushed_at, billbee_stock, last_error, last_try FROM syringe_stock_sku`);
db.exec('DROP TABLE syringe_stock_sku');
db.exec('ALTER TABLE syringe_stock_sku_neu RENAME TO syringe_stock_sku');
}
},
{
version: 104,
description: 'Blockbuch: gute Bloecke markieren und Notizen je Block, mit Herkunft zum Zeitpunkt',
fn(db) {
// Ein Block, den jemand für gut befunden oder kommentiert hat. lineage ist
// die Herkunft so, wie sie beim Markieren war (Ansatz, Feuchte, Körnerbrut),
// als JSON: eine Charge kann später gelöscht werden, und dann wäre ohne sie
// nicht mehr zu sagen, woraus der gute Block gemacht war.
db.exec(`CREATE TABLE IF NOT EXISTS block_notes (
bag_id TEXT PRIMARY KEY,
batch_id TEXT NOT NULL,
good INTEGER NOT NULL DEFAULT 0,
good_at TEXT,
good_by TEXT,
lineage TEXT NOT NULL DEFAULT '{}',
created TEXT NOT NULL
)`);
// Notizen zu einem Block, beliebig viele. flush ist gesetzt, wenn die Notiz
// beim Ernten geschrieben wurde, sonst leer.
db.exec(`CREATE TABLE IF NOT EXISTS block_comments (
id INTEGER PRIMARY KEY AUTOINCREMENT,
bag_id TEXT NOT NULL REFERENCES block_notes(bag_id) ON DELETE CASCADE,
text TEXT NOT NULL,
flush INTEGER,
created TEXT NOT NULL,
created_by TEXT
)`);
db.exec('CREATE INDEX IF NOT EXISTS idx_block_comments_bag ON block_comments(bag_id, created)');
}
},
{
version: 105,
description: 'Koernerbrut an Billbee: SKU je Art und Beutelgroesse',
fn(db) {
// Bewachsene Koernerbrut wird verkauft wie Spritzen, je Art und Beutel-
// groesse ein Artikel. Ihre Zeile traegt den Typ 'GS' und in ml die
// Beutelgroesse in kg; die Spalte heisst nach dem ersten Artikel, der sie
// brauchte, und ist der Schluessel, keine Einheit.
db.exec(`CREATE TABLE syringe_stock_sku_neu (
strain_id INTEGER NOT NULL REFERENCES mushroom_strains(id) ON DELETE CASCADE,
type TEXT NOT NULL DEFAULT 'SY' CHECK (type IN ('SY', 'PD', 'GS')),
ml INTEGER NOT NULL DEFAULT 10,
sku TEXT NOT NULL UNIQUE,
pushed_count INTEGER,
pushed_at TEXT,
billbee_stock REAL,
last_error TEXT,
last_try TEXT,
PRIMARY KEY (strain_id, type, ml)
)`);
db.exec(`INSERT INTO syringe_stock_sku_neu(strain_id, type, ml, sku, pushed_count, pushed_at, billbee_stock, last_error, last_try)
SELECT strain_id, type, ml, sku, pushed_count, pushed_at, billbee_stock, last_error, last_try FROM syringe_stock_sku`);
db.exec('DROP TABLE syringe_stock_sku');
db.exec('ALTER TABLE syringe_stock_sku_neu RENAME TO syringe_stock_sku');
}
},
{
version: 106,
description: 'Verkaufsbestand: Soll je Artikel',
fn(db) {
// Wie viele von einem Artikel immer da sein sollen. NULL heisst: kein
// Soll, der Artikel wird auf Bestellung gemacht. Es haengt an der Zeile
// mit der SKU, weil nur ein Artikel, den ein Shop verkauft, eins braucht.
const has = db
.prepare("SELECT COUNT(*) AS c FROM pragma_table_info('syringe_stock_sku') WHERE name = 'soll'")
.get();
if (!has.c) db.exec('ALTER TABLE syringe_stock_sku ADD COLUMN soll INTEGER');
}
},
{
version: 107,
description: 'Nachproduzieren: gemeldete Bestellungen je Artikel merken',
fn(db) {
// Wie viel bestellt war, als zuletzt "bestellt, aber nicht da" gemeldet
// wurde. Damit meldet sich jede neue Bestellung einmal, und nicht jeder
// Lauf dieselbe noch einmal (saleAlerts).
const has = db
.prepare("SELECT COUNT(*) AS c FROM pragma_table_info('syringe_stock_sku') WHERE name = 'alert_ordered'")
.get();
if (!has.c) db.exec('ALTER TABLE syringe_stock_sku ADD COLUMN alert_ordered INTEGER');
}
},
{
version: 108,
description: 'Laborbestand je Sorte: das Soll der Petrischale ist die Untergrenze der Sorte',
fn(db) {
// Eine Zahl je Feld. Fuer Petrischalen gab es zwei: min_pd an der Sorte
// (Laborvorrat) und soll an der SKU-Zeile (Shop). Die groessere bleibt,
// an der Sorte, wo sie auch ohne SKU gilt.
db.exec(`UPDATE mushroom_strains
SET min_pd = (SELECT MAX(COALESCE(mushroom_strains.min_pd, 0), s.soll)
FROM syringe_stock_sku s
WHERE s.strain_id = mushroom_strains.id AND s.type = 'PD' AND s.soll IS NOT NULL)
WHERE id IN (SELECT strain_id FROM syringe_stock_sku WHERE type = 'PD' AND soll IS NOT NULL)`);
db.exec("UPDATE syringe_stock_sku SET soll = NULL WHERE type = 'PD'");
}
},
{
version: 109,
description: 'Gedruckte Etiketten: je Kennung, wann, damit ein nicht gespeichertes am selben Tag auffaellt',
fn(db) {
db.exec(`CREATE TABLE IF NOT EXISTS printed_labels (
label_id TEXT PRIMARY KEY,
printed_at TEXT NOT NULL,
checked INTEGER NOT NULL DEFAULT 0
)`);
}
},
{
version: 110,
description: 'Impfduebel: wie viele Duebel ein Beutel hat, fuers Etikett',
fn(db) {
const has = db.prepare("SELECT COUNT(*) as c FROM pragma_table_info('cultures') WHERE name='stueck'").get();
if (!has.c) db.exec('ALTER TABLE cultures ADD COLUMN stueck INTEGER');
}
},
{
version: 111,
description: 'Calciumcarbonat als Rohstoff im Ansatz, Sterilisierzeit je Ansatz, pH je Charge',
fn(db) {
const addCol = (table, col, decl) => {
const has = db.prepare(`SELECT COUNT(*) as c FROM pragma_table_info('${table}') WHERE name='${col}'`).get();
if (!has.c) db.exec(`ALTER TABLE ${table} ADD COLUMN ${col} ${decl}`);
};
// Calciumcarbonat geht wie Gips obendrauf auf die Trockenmischung und vom
// Lager ab — also braucht es einen Bestand, sonst verlassen Kilos das Haus,
// ohne dass es jemand verbucht.
addCol('inventory', 'stock_calcium', 'REAL DEFAULT 0');
addCol('inventory', 'thresh_calcium', 'REAL DEFAULT 0');
addCol('substrate_batches', 'calcium_pct', 'REAL DEFAULT 0');
addCol('substrate_batches', 'calcium_kg', 'REAL DEFAULT 0');
// NULL heisst: nicht eingetragen. 0 Minuten waere eine Aussage.
addCol('substrate_batches', 'steri_min', 'REAL');
// Gemessen beim Beimpfen. Zusammen mit steri_min des Ansatzes zeigt es, wie
// die Sterilisierzeit den pH verschiebt. NULL heisst: nicht gemessen.
addCol('batches', 'ph', 'REAL');
}
}
];
// Setzt eine Migration, die Text aus der Datei holt; openDb leert dann das WAL,
// in dem die alten Seiten sonst noch stuenden.
let _walLeeren = false;
function runMigrations(db) {
db.exec(
'CREATE TABLE IF NOT EXISTS schema_version (version INTEGER PRIMARY KEY, applied TEXT NOT NULL, description TEXT)'
);
const applied = new Set(
db
.prepare('SELECT version FROM schema_version')
.all()
.map((r) => r.version)
);
for (const m of MIGRATIONS) {
if (applied.has(m.version)) continue;
try {
// Migrations that drop/recreate tables with FK references need foreign_keys OFF
// BEFORE the transaction (the pragma is a no-op inside a transaction).
if (m.disableForeignKeys) db.exec('PRAGMA foreign_keys = OFF');
db.exec('BEGIN');
if (m.fn) m.fn(db);
else db.exec(m.sql);
db.prepare('INSERT INTO schema_version(version, applied, description) VALUES(?, ?, ?)').run(
m.version,
new Date().toISOString(),
m.description || ''
);
db.exec('COMMIT');
if (m.disableForeignKeys) db.exec('PRAGMA foreign_keys = ON');
console.log(` Migration v${m.version} applied: ${m.description || ''}`);
} catch (e) {
db.exec('ROLLBACK');
if (m.disableForeignKeys) db.exec('PRAGMA foreign_keys = ON');
// Tolerate "duplicate column" errors — column may already exist from initial schema
if (e.message && e.message.includes('duplicate column')) {
db.exec('BEGIN');
db.prepare('INSERT INTO schema_version(version, applied, description) VALUES(?, ?, ?)').run(
m.version,
new Date().toISOString(),
m.description + ' (already exists)'
);
db.exec('COMMIT');
console.log(` Migration v${m.version} skipped (already applied): ${m.description || ''}`);
} else {
throw new Error(`Migration v${m.version} failed: ${e.message}`);
}
}
}
}
// ── Open / Init ──────────────────────────────────────────────
function backfillBarcodes(db) {
const queries = [
{
type: 'bag',
sql: "SELECT bag_id AS id FROM bags WHERE bag_id NOT IN (SELECT entity_id FROM barcodes WHERE entity_type='bag') ORDER BY bag_id"
},
{
type: 'culture',
sql: "SELECT id FROM cultures WHERE id NOT IN (SELECT entity_id FROM barcodes WHERE entity_type='culture') ORDER BY created, id"
},
{
type: 'zone',
sql: "SELECT id FROM zones WHERE id NOT IN (SELECT entity_id FROM barcodes WHERE entity_type='zone') ORDER BY sort_order, id"
},
{
type: 'rack',
sql: "SELECT id FROM racks WHERE id NOT IN (SELECT entity_id FROM barcodes WHERE entity_type='rack') ORDER BY zone_id, sort_order, id"
}
];
const now = new Date().toISOString();
let count = 0;
for (const q of queries) {
const missing = db.prepare(q.sql).all();
if (missing.length) {
let num = nextBarcodeNumber(db);
const ins = db.prepare('INSERT INTO barcodes(barcode, entity_type, entity_id, created) VALUES(?,?,?,?)');
for (const r of missing) {
ins.run(num++, q.type, r.id, now);
count++;
}
}
}
if (count) console.log(`[barcode-backfill] Assigned ${count} missing barcodes`);
}
function openDb(dbPath) {
const db = new Database(dbPath);
// Scheitert danach etwas, eine Migration etwa, geht der Griff wieder zu.
// Unter Windows hielte er die Datei sonst gesperrt, bis der Prozess endet,
// und nichts koennte sie mehr ersetzen oder zurueckholen; so lief beim
// Zurueckspielen der Weg zum Stand davor ins Leere (swapRestoredDatabase).
try {
_dbEinrichten(db, dbPath);
} catch (e) {
try {
db.close();
} catch {
/* der erste Fehler ist der, der etwas erklaert */
}
throw e;
}
return db;
}
function _dbEinrichten(db, dbPath) {
// R-16: stash the path on the handle so backupDb() can stat the source DB
// for its disk-space pre-flight without having to plumb it through every
// caller. Non-enumerable so it doesn't show up in stringification.
Object.defineProperty(db, '_mpDbPath', { value: dbPath, enumerable: false, writable: false });
db.exec('PRAGMA journal_mode = WAL');
db.exec('PRAGMA foreign_keys = ON');
db.exec('PRAGMA busy_timeout = 5000');
db.exec('PRAGMA synchronous = NORMAL');
db.exec('PRAGMA wal_autocheckpoint = 1000');
db.exec(SCHEMA);
runMigrations(db);
if (_walLeeren) {
db.exec('PRAGMA wal_checkpoint(TRUNCATE)');
_walLeeren = false;
}
// Die Schutz-Trigger des Verlaufs jedes Mal neu: CREATE TRIGGER IF NOT EXISTS
// prueft nur den Namen. Eine zurueckgespielte Datei mit gleichnamigen, aber
// wirkungslosen Triggern behielte sonst ihre.
db.exec(`DROP TRIGGER IF EXISTS lab_history_no_update;
DROP TRIGGER IF EXISTS lab_history_no_delete;
CREATE TRIGGER lab_history_no_update BEFORE UPDATE ON lab_history
BEGIN
SELECT RAISE(ABORT, 'lab_history is append-only');
END;
CREATE TRIGGER lab_history_no_delete BEFORE DELETE ON lab_history
BEGIN
SELECT RAISE(ABORT, 'lab_history is append-only');
END;`);
// Dasselbe fuer die Pruefkette und die Zeitstempel.
db.exec(`DROP TRIGGER IF EXISTS lab_history_chain_no_update;
DROP TRIGGER IF EXISTS lab_history_chain_no_delete;
DROP TRIGGER IF EXISTS lab_timestamps_no_update;
DROP TRIGGER IF EXISTS lab_timestamps_no_delete;
CREATE TRIGGER lab_history_chain_no_update BEFORE UPDATE ON lab_history_chain
BEGIN
SELECT RAISE(ABORT, 'lab_history_chain is append-only');
END;
CREATE TRIGGER lab_history_chain_no_delete BEFORE DELETE ON lab_history_chain
BEGIN
SELECT RAISE(ABORT, 'lab_history_chain is append-only');
END;
CREATE TRIGGER lab_timestamps_no_update BEFORE UPDATE ON lab_timestamps
BEGIN
SELECT RAISE(ABORT, 'lab_timestamps is append-only');
END;
CREATE TRIGGER lab_timestamps_no_delete BEFORE DELETE ON lab_timestamps
BEGIN
SELECT RAISE(ABORT, 'lab_timestamps is append-only');
END;`);
// Eintraege, die eine aeltere Version ohne Glied ans Ende geschrieben hat,
// bekommen ihre Glieder, mit einem Vermerk im Verlauf (_ketteNachtragen).
// BEGIN IMMEDIATE holt die Schreibsperre zuerst und wartet dafuer, wie
// busy_timeout es sagt; erst lesen und dann schreiben wollen scheiterte
// sofort, wenn ein anderer Prozess gerade schreibt. Scheitert es trotzdem,
// startet der Server, und der naechste Eintrag versucht es (_verlauf).
try {
if (_ketteOffen(db)) {
db.exec('BEGIN IMMEDIATE');
try {
const n = _ketteNachtragen(db);
db.exec('COMMIT');
if (n) console.log(`[lab-chain] Linked ${n} history entries written without the chain, and recorded that`);
} catch (e) {
try {
db.exec('ROLLBACK');
} catch {
/* SQLite hat schon zurueckgenommen; der erste Fehler erklaert es */
}
throw e;
}
}
} catch (e) {
console.error('[lab-chain] Could not link history entries written without the chain: ' + e.message);
}
// Ensure singleton rows exist
db.prepare(`INSERT OR IGNORE INTO inventory(id) VALUES(1)`).run();
db.prepare(`INSERT OR IGNORE INTO caldav_config(id) VALUES(1)`).run();
db.prepare(`INSERT OR IGNORE INTO duckdns_config(id) VALUES(1)`).run();
db.prepare(`INSERT OR IGNORE INTO print_bridge_config(id) VALUES(1)`).run();
db.prepare(`INSERT OR IGNORE INTO harvest_feed_config(id) VALUES(1)`).run();
db.prepare(`INSERT OR IGNORE INTO mcp_config(id) VALUES(1)`).run();
db.prepare(`INSERT OR IGNORE INTO camera_calibration(id) VALUES(1)`).run();
// Backfill: assign numeric barcodes to any entities missing them
backfillBarcodes(db);
}
// ── Barcode Registry ────────────────────────────────────────
function nextBarcodeNumber(db) {
const row = db.prepare('SELECT MAX(barcode) as m FROM barcodes').get();
return row && row.m != null ? row.m + 1 : 1000000;
}
function assignBarcode(db, entityType, entityId) {
// Return existing barcode if already assigned
const existing = db
.prepare('SELECT barcode FROM barcodes WHERE entity_type=? AND entity_id=?')
.get(entityType, entityId);
if (existing) return existing.barcode;
const num = nextBarcodeNumber(db);
db.prepare('INSERT INTO barcodes(barcode, entity_type, entity_id, created) VALUES(?,?,?,?)').run(
num,
entityType,
entityId,
new Date().toISOString()
);
return num;
}
function assignBarcodes(db, entityType, entityIds) {
const result = {};
const existing = db
.prepare(
'SELECT barcode, entity_id FROM barcodes WHERE entity_type=? AND entity_id IN (' +
entityIds.map(() => '?').join(',') +
')'
)
.all(entityType, ...entityIds);
for (const r of existing) result[r.entity_id] = r.barcode;
const missing = entityIds.filter((id) => !(id in result));
if (missing.length) {
let num = nextBarcodeNumber(db);
const ins = db.prepare('INSERT INTO barcodes(barcode, entity_type, entity_id, created) VALUES(?,?,?,?)');
const now = new Date().toISOString();
for (const id of missing) {
ins.run(num, entityType, id, now);
result[id] = num++;
}
}
return result;
}
function lookupBarcode(db, barcode) {
return db.prepare('SELECT entity_type, entity_id FROM barcodes WHERE barcode=?').get(barcode) || null;
}
function getAllBarcodes(db) {
return db.prepare('SELECT barcode, entity_type, entity_id FROM barcodes ORDER BY barcode').all();
}
// ── Read All (assembles the JSON shape the client expects) ───
function readAll(db, opts = {}) {
// Mushroom strains
const mushroomStrains = listMushroomStrains(db);
const msById = new Map(mushroomStrains.map((ms) => [ms.id, ms]));
// Batches + bags. Bulk-load bags in ONE query and group by batch_id instead
// of running bagStmt.all() once per batch (the audit-flagged N+1 — at 200
// batches that was 200 statement executions every time readAll fired,
// and readAll is called by /api/data which polls on each SSE event).
// The mix is joined in by name rather than by id: the client needs something
// it can match against the substrate list, and the numeric key means nothing
// on that side.
const batchRows = db
.prepare(
`SELECT b.*, sb.sub_id AS substrate_sub_id
FROM batches b
LEFT JOIN substrate_batches sb ON sb.id = b.substrate_batch_id
ORDER BY b.created`
)
.all();
const bagsByBatch = new Map();
for (const b of db.prepare('SELECT batch_id, bag_id, bag_kg FROM bags ORDER BY batch_id, bag_id').all()) {
let arr = bagsByBatch.get(b.batch_id);
if (!arr) {
arr = [];
bagsByBatch.set(b.batch_id, arr);
}
arr.push(b);
}
const batches = batchRows.map((r) => {
const ms = r.strain_id ? msById.get(r.strain_id) : null;
const bagRows = bagsByBatch.get(r.batch_id) || [];
const bagWeights = {};
for (const b of bagRows) bagWeights[b.bag_id] = b.bag_kg != null ? b.bag_kg : r.bag_kg || 3;
return {
batchId: r.batch_id,
species: r.species,
strain: r.strain,
strainId: r.strain_id || null,
strainName: ms ? ms.name : null,
strainKuerzel: ms ? ms.kuerzel : null,
strainDescriptor: ms ? ms.description || null : null,
qty: r.qty,
days: r.days,
// v67: which mix this Charge was portioned out of, so the substrate tab
// can count its Chargen without asking the server once per row.
substrateSubId: r.substrate_sub_id || null,
substrateKg: r.substrate_kg || 0,
substrate: {
hardwood: r.sub_hardwood,
wheatbran: r.sub_wheatbran,
corn: r.sub_corn || 0,
coir: r.sub_coir || 0,
rh: r.sub_rh,
gypsum: r.sub_gypsum === 1 ? true : false
},
bagKg: r.bag_kg,
batchType: r.batch_type,
grainRh: r.grain_rh || 0,
grainKg: r.grain_kg || 0,
// Gemessen beim Beimpfen; null, solange niemand gemessen hat.
ph: r.ph == null ? null : r.ph,
sourceId: r.source_id,
notes: r.notes,
strainText: r.strain_text || '',
created: r.created,
due: r.due,
bags: bagRows.map((b) => b.bag_id),
bagWeights
};
});
// Scan log — include id for PATCH/DELETE targeting, join username
const scanLog = db
.prepare('SELECT s.*, u.username FROM scan_log s LEFT JOIN users u ON s.user_id = u.id ORDER BY s.id')
.all()
.map((r) => ({
id: r.id,
time: r.time,
action: r.action,
batch: r.batch,
bag: r.bag,
from: r.from,
to: r.to,
species: r.species,
strain: r.strain,
reason: r.reason || null,
userId: r.user_id,
user: r.username || null,
// Must survive a readAll -> writeAll round trip. Without it every restore
// silently drops the offline-replay idempotency keys, leaving the partial
// unique index idx_scanlog_client_uuid nothing to match — so a
// service-worker replay re-inserts a duplicate MOVE instead of hitting
// ON CONFLICT DO NOTHING, and the bag-to-zone derivation drifts.
client_uuid: r.client_uuid || null
}));
// Harvests — include id for targeting
const harvests = db
.prepare('SELECT * FROM harvests ORDER BY id')
.all()
.map((r) => ({
id: r.id,
time: r.time,
batch: r.batch,
bag: r.bag,
species: r.species,
strain: r.strain,
grams: r.grams,
flush: r.flush,
quality: r.quality || null,
notes: r.notes || null
}));
// Cultures
const cultures = db
.prepare('SELECT * FROM cultures ORDER BY created')
.all()
.map((r) => {
const ms = r.strain_id ? msById.get(r.strain_id) : null;
return {
id: r.id,
type: r.type,
species: r.species,
strain: r.strain,
strainId: r.strain_id || null,
strainName: ms ? ms.name : null,
strainKuerzel: ms ? ms.kuerzel : null,
strainDescriptor: ms ? ms.description || null : null,
strainText: r.strain_text || '',
ml: r.ml || null,
stueck: r.stueck || null,
parentId: r.parent_id,
source: r.source,
status: r.status,
notes: r.notes,
created: r.created
};
});
// Manual tasks — include id for PATCH/DELETE targeting
const manualTasks = db
.prepare('SELECT * FROM manual_tasks ORDER BY id')
.all()
.map((r) => ({
id: r.id,
text: r.text,
priority: r.priority,
done: r.done === 1 ? true : false,
created: r.created,
assignee: r.assignee,
dueDate: r.due_date,
dueTime: r.due_time,
dueEndTime: r.due_end_time,
description: r.description,
caldavUid: r.caldav_uid,
caldavSynced: r.caldav_synced,
private: r.private === 1 ? true : false,
recurrence: r.recurrence || null,
recurrenceUntil: r.recurrence_until || null,
sequence: r.sequence || 0
}));
// Team members — include id for DELETE targeting
const teamMembers = db
.prepare('SELECT * FROM team_members ORDER BY id')
.all()
.map((r) => ({
id: r.id,
name: r.name,
role: r.role,
added: r.added
}));
// Inventory (singleton)
const inv = db.prepare('SELECT * FROM inventory WHERE id = 1').get();
const invLogLimit = opts.inventoryLogLimit;
const invLogRaw = invLogLimit
? db.prepare('SELECT * FROM inventory_log ORDER BY id DESC LIMIT ?').all(invLogLimit).reverse()
: db.prepare('SELECT * FROM inventory_log ORDER BY id').all();
const invLog = invLogRaw.map((r) => ({
time: r.time,
mat: r.mat,
deltaKg: r.delta_kg,
running: r.running,
type: r.type,
ref: r.ref,
// I-22: surface the actor for clients that need it (audit views, KPIs).
user_id: r.user_id != null ? r.user_id : null
}));
const inventory = {
stock: {
hardwood: inv.stock_hardwood,
wheatbran: inv.stock_wheatbran,
gypsum: inv.stock_gypsum,
grain: inv.stock_grain,
coir: inv.stock_coir || 0,
corn: inv.stock_corn || 0,
calcium: inv.stock_calcium || 0
},
thresholds: {
hardwood: { minKg: inv.thresh_hardwood },
wheatbran: { minKg: inv.thresh_wheatbran },
gypsum: { minKg: inv.thresh_gypsum },
grain: { minKg: inv.thresh_grain },
coir: { minKg: inv.thresh_coir || 0 },
corn: { minKg: inv.thresh_corn || 0 },
calcium: { minKg: inv.thresh_calcium || 0 }
},
avgComposition: {
hwPct: inv.avg_hw_pct,
wbPct: inv.avg_wb_pct,
rhPct: inv.avg_rh_pct,
bagKg: inv.avg_bag_kg,
grainBagKg: inv.avg_grain_bag_kg,
grainRhPct: inv.avg_grain_rh_pct != null ? inv.avg_grain_rh_pct : 52
},
labThresholds: {
MC: inv.lab_thresh_mc || 0,
PD: inv.lab_thresh_pd || 0,
LC: inv.lab_thresh_lc || 0,
G2G: inv.lab_thresh_g2g || 0,
GS: inv.lab_thresh_gs || 0,
SY: inv.lab_thresh_sy || 0
},
bagCountFrom: inv.bag_count_from || null,
log: invLog
};
// CalDAV config
const cal = db.prepare('SELECT * FROM caldav_config WHERE id = 1').get();
const caldav = {
enabled: cal.enabled === 1 ? true : false
};
// DuckDNS config (expose hasToken flag, never the actual token)
const ddns = db.prepare('SELECT * FROM duckdns_config WHERE id = 1').get();
const duckdns = {
enabled: ddns.enabled === 1,
domain: ddns.domain || '',
hasToken: !!ddns.token,
lastIpUpdate: ddns.last_ip_update || null,
lastIp: ddns.last_ip || null,
leEnabled: ddns.le_enabled === 1,
leLastRenewal: ddns.le_last_renewal || null,
leExpiry: ddns.le_expiry || null
};
// Calendar events
const assigneeMap = getAllCalendarEventAssignees(db);
const calendarEvents = db
.prepare('SELECT * FROM calendar_events ORDER BY start_date')
.all()
.map((r) => ({
id: r.id,
title: r.title,
description: r.description,
startDate: r.start_date,
endDate: r.end_date,
allDay: r.all_day === 1,
startTime: r.start_time,
endTime: r.end_time,
category: r.category,
color: r.color,
caldavUid: r.caldav_uid,
caldavSynced: r.caldav_synced,
created: r.created,
recurrence: r.recurrence || null,
recurrenceUntil: r.recurrence_until || null,
teamAssignees: parseTeamAssignees(r.team_assignees),
exceptionDates: parseExceptionDates(r.exception_dates),
assignees: assigneeMap.get(r.id) || [],
sequence: r.sequence || 0,
locationId: r.location_id || null,
pickupCapacity: r.pickup_capacity === null || r.pickup_capacity === undefined ? null : r.pickup_capacity
}));
// Zones + Racks
const zoneRows = db.prepare('SELECT * FROM zones ORDER BY sort_order, id').all();
const rackStmt = db.prepare(
'SELECT id, zone_id, sort_order, created FROM racks WHERE zone_id = ? ORDER BY sort_order, id'
);
const zones = zoneRows.map((z) => ({
id: z.id,
name: z.name,
role: z.role,
color: z.color,
sortOrder: z.sort_order,
maxCapacity: z.max_capacity || null,
created: z.created,
racks: rackStmt.all(z.id).map((r) => ({ id: r.id, sortOrder: r.sort_order, created: r.created }))
}));
// Suppliers
const suppliers = db.prepare('SELECT * FROM suppliers ORDER BY mat, name').all();
// Pickup locations. Inactive ones ride along: an event that still points at a
// retired location has to be able to name it rather than show a blank.
const pickupLocations = db
.prepare(
'SELECT id, name, address, sort_order AS sortOrder, active FROM pickup_locations ORDER BY sort_order, name'
)
.all()
.map((l) => ({ ...l, active: l.active === 1 }));
// Barcodes
const barcodes = getAllBarcodes(db);
// Weekday → {theme, targetQty, strainId, note}, keyed by getDay() so the
// client can look a day up directly. An empty object means no rhythm has been
// set yet, which is what makes the editor offer one derived from history.
// Snapshot any past day the rhythm applied to before reading them back, so a
// day that was missed is a row that can carry forward rather than a gap.
try {
ensureRhythmTasks(db);
} catch (e) {
// A read must not fail because a snapshot could not be written.
}
const rhythmTasks = listRhythmTasks(db);
const weekRhythm = {};
for (const r of db.prepare('SELECT weekday, theme, target_qty, strain_id, note FROM week_rhythm').all()) {
weekRhythm[r.weekday] = {
theme: r.theme,
targetQty: r.target_qty || null,
strainId: r.strain_id || null,
note: r.note || null
};
}
const version = getDataVersion(db);
return {
mushroomStrains,
staemme: listStaemme(db),
// Carried in the main payload rather than fetched separately: the client needs
// the remaining kilograms on every sync, and /api/data is already the request
// every sync makes.
substrateBatches: listSubstrateBatches(db),
batches,
scanLog,
manualTasks,
weekRhythm,
rhythmTasks,
recurringTasks: listRecurringTasks(db),
harvests,
cultures,
inventory,
teamMembers,
caldav,
duckdns,
calendarEvents,
zones,
suppliers,
pickupLocations,
barcodes,
experiments: listExperiments(db),
workPackages: listWorkPackages(db),
projectNotes: listProjectNotes(db),
projectDocsStamp: getProjectDocsStamp(db),
// Ob ein Block gut ist und was zu ihm notiert wurde: das Beutel-Fenster
// braucht es beim Scannen sofort.
blockNotes: listBlockNotes(db),
version
};
}
// ── Data Versioning ─────────────────────────────────────────
function getDataVersion(db) {
const row = db.prepare('SELECT value FROM meta WHERE key=?').get('data_version');
return row ? parseInt(row.value, 10) : 0;
}
function incrementDataVersion(db) {
const v = getDataVersion(db) + 1;
db.prepare('INSERT INTO meta(key,value) VALUES(?,?) ON CONFLICT(key) DO UPDATE SET value=excluded.value').run(
'data_version',
String(v)
);
return v;
}
// fn in einem SAVEPOINT: alles oder nichts, auch dann, wenn der Aufrufer schon
// eine Transaktion haelt. Ein BEGIN darin scheiterte mit "cannot start a
// transaction within a transaction"; ein SAVEPOINT verschachtelt sich. Das
// Aufraeumen hat seinen eigenen Schutz: ist der Savepoint schon weg, wuerde ein
// Fehler von dort den ersetzen, der den Abbruch erklaert.
function withSavepoint(db, name, fn) {
db.exec('SAVEPOINT ' + name);
try {
const ergebnis = fn();
db.exec('RELEASE ' + name);
return ergebnis;
} catch (e) {
try {
db.exec('ROLLBACK TO ' + name);
db.exec('RELEASE ' + name);
} catch {
/* der erste Fehler ist der, der etwas erklaert */
}
throw e;
}
}
// ── P-06: Bag-zone cache ────────────────────────────────────
// snapshotDailyKPIs and getProductionPipeline both used to do a full
// `SCAN scan_log` to derive the per-bag current zone, then iterate the
// rows in JS to derive zone counts. At 50K scans that's ~80 ms per
// snapshot blocking the writer.
//
// We maintain a process-lifetime in-memory map (bag -> zone_id) that we
// update incrementally on each scan_log insert. The cache is rebuilt
// once on first access (lazy) by reading scan_log once; thereafter every
// write path that inserts into scan_log calls applyScanEntryToBagZoneCache
// with the new entry, so reads are O(1).
//
// Cache key = the database object. We keep a WeakMap so multiple Database
// instances (e.g. in tests) don't cross-contaminate.
const _bagZoneCacheByDb = new WeakMap();
function _readBagZoneFromDb(db) {
const map = new Map();
// Resolve rack ids (underscores, e.g. INC_R1) to their zone once via a local
// map — a plain split(':') leaves the rack id intact, so every rack-placed
// bag was keyed under a non-existent "zone" and dropped from KPI snapshots
// and the production pipeline.
const rackZone = new Map();
for (const r of db.prepare('SELECT id, zone_id FROM racks').all()) rackZone.set(r.id, r.zone_id);
const resolve = (loc) => {
if (!loc) return null;
const base = String(loc).split(':')[0];
return rackZone.get(base) || base;
};
const stmt = db.prepare('SELECT action, bag, "to" FROM scan_log ORDER BY id');
for (const e of stmt.iterate()) {
if (!e.bag) continue;
const toZ = resolve(e.to);
if (e.action === 'ADD' && toZ) map.set(e.bag, toZ);
else if ((e.action === 'MOVE' || e.action === 'MOVE_BATCH') && toZ) map.set(e.bag, toZ);
else if (e.action === 'REMOVE') map.delete(e.bag);
}
return map;
}
/** Get the bag→zone-id map for `db`. Builds once, then returned by
* reference — DO NOT mutate from outside the helpers below. */
function getBagZoneMap(db) {
let cached = _bagZoneCacheByDb.get(db);
if (!cached) {
cached = _readBagZoneFromDb(db);
_bagZoneCacheByDb.set(db, cached);
}
return cached;
}
/** The bags that count as stock: placed in a zone, and that zone is not a
* contamination zone. A contaminated bag still sits somewhere, but it is
* nobody's stock — no strain total, no kg on hand, nothing a shop may sell
* may count it. Per-zone views (zone overview, KPI role counts) read
* getBagZoneMap instead, since they are meant to show the contam zone. */
function getStockBagZoneMap(db) {
const contam = new Set(
db
.prepare("SELECT id FROM zones WHERE role = 'contaminated'")
.all()
.map((z) => z.id)
);
const out = new Map();
for (const [bag, zone] of getBagZoneMap(db)) if (!contam.has(zone)) out.set(bag, zone);
return out;
}
/** Apply a single scan_log entry to the cache (incremental update path).
* Called from every write site that inserts into scan_log so consumers
* never have to re-scan the table. */
function applyScanEntryToBagZoneCache(db, entry) {
const cached = _bagZoneCacheByDb.get(db);
if (!cached || !entry || !entry.bag) return; // not built yet — first read will build it
const toZ = entry.to ? zoneIdOfLocation(db, entry.to) : null;
if (entry.action === 'ADD' && toZ) cached.set(entry.bag, toZ);
else if ((entry.action === 'MOVE' || entry.action === 'MOVE_BATCH') && toZ) cached.set(entry.bag, toZ);
else if (entry.action === 'REMOVE') cached.delete(entry.bag);
}
/** Force a rebuild on next read. Used by writeAll (which replaces all of
* scan_log) and by tests that mutate scan_log directly. */
function invalidateBagZoneCache(db) {
_bagZoneCacheByDb.delete(db);
}
/** Resolve a scan_log location value to its owning zone id.
* `loc` may be a zone id ("INC"), a rack id ("INC_R1", underscores), or the
* legacy "ZONE:rack" colon form. Rack ids must be mapped back to their zone
* via the racks table — a plain split(':') leaves the rack id intact, which
* breaks the optimistic-concurrency zone check (rack moves wrongly 409) and
* KPI/pipeline aggregation (rack-placed bags fall out of every zone bucket).
* Mirrors the client-side toZone() in app.js. */
function zoneIdOfLocation(db, loc) {
if (!loc) return null;
const base = String(loc).split(':')[0];
if (!base) return null;
if (db.prepare('SELECT 1 FROM zones WHERE id = ?').get(base)) return base;
const rack = db.prepare('SELECT zone_id FROM racks WHERE id = ?').get(base);
if (rack && rack.zone_id) return rack.zone_id;
return base;
}
// A section of an incoming payload is only allowed to rewrite its table when it
// actually carries rows. Every section below either DELETEs the table outright or
// diffs against an id set, so a bare `if (incoming.scanLog)` accepted `[]` — which
// is truthy — and truncated the table with nothing to re-insert. One malformed or
// half-serialised payload could therefore wipe the scan log or every batch (bags
// cascade) with no backup and no confirmation.
//
// Restore does not come through here (it closes, renames and reopens the DB file),
// and the client has no writer at all — saveData() was replaced by atomic REST
// endpoints — so nothing legitimately needs to clear a table this way. A caller
// that genuinely must may pass allowEmpty.
function _section(incoming, key, allowEmpty) {
const v = incoming[key];
if (!Array.isArray(v)) return !!v && typeof v === 'object'; // inventory/caldav are plain objects
return allowEmpty ? true : v.length > 0;
}
// ── Write All (diff incoming JSON against DB, apply changes) ─
// Used by backup/restore only — normal mutations use atomic functions below
function writeAll(db, incoming, opts) {
const allowEmpty = !!(opts && opts.allowEmpty);
db.exec('BEGIN');
try {
// ── Batches ──
if (_section(incoming, 'batches', allowEmpty)) {
const existingIds = new Set(
db
.prepare('SELECT batch_id FROM batches')
.all()
.map((r) => r.batch_id)
);
const incomingIds = new Set(incoming.batches.map((b) => b.batchId));
const wegIds = [...existingIds].filter((id) => !incomingIds.has(id));
_verknuepfungGeht(db, 'batchId', wegIds, opts && opts.wer);
for (const id of wegIds) {
db.prepare('DELETE FROM batches WHERE batch_id = ?').run(id);
}
const upsertBatch = db.prepare(`
INSERT INTO batches(batch_id, species, strain, strain_id, strain_text, qty, days, sub_hardwood, sub_wheatbran, sub_rh, sub_gypsum, bag_kg, batch_type, source_id, notes, created, due, grain_rh, sub_coir, grain_kg)
VALUES(?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
ON CONFLICT(batch_id) DO UPDATE SET
species=excluded.species, strain=excluded.strain,
strain_id=excluded.strain_id, strain_text=excluded.strain_text,
qty=excluded.qty, days=excluded.days,
sub_hardwood=excluded.sub_hardwood, sub_wheatbran=excluded.sub_wheatbran,
sub_rh=excluded.sub_rh, sub_gypsum=excluded.sub_gypsum,
bag_kg=excluded.bag_kg, batch_type=excluded.batch_type,
source_id=excluded.source_id, notes=excluded.notes,
created=excluded.created, due=excluded.due,
grain_rh=excluded.grain_rh, sub_coir=excluded.sub_coir, grain_kg=excluded.grain_kg
`);
const deleteBags = db.prepare('DELETE FROM bags WHERE batch_id = ?');
const insertBag = db.prepare('INSERT INTO bags(bag_id, batch_id, bag_kg) VALUES(?, ?, ?)');
for (const b of incoming.batches) {
const sub = b.substrate || {};
upsertBatch.run(
b.batchId,
b.species,
b.strain || null,
b.strainId || null,
b.strainText || '',
b.qty,
b.days,
sub.hardwood || 0,
sub.wheatbran || 0,
sub.rh || 0,
sub.gypsum ? 1 : 0,
b.bagKg || 3,
b.batchType || 'block',
b.sourceId || null,
b.notes || '',
b.created,
b.due,
Number.isFinite(b.grainRh) ? b.grainRh : 0,
sub.coir || 0,
b.grainKg || 0
);
deleteBags.run(b.batchId);
const bagIds = [];
for (const item of b.bags || []) {
if (typeof item === 'string') {
insertBag.run(item, b.batchId, (b.bagWeights && b.bagWeights[item]) || b.bagKg || 3);
bagIds.push(item);
} else {
insertBag.run(item.id, b.batchId, item.bagKg || b.bagKg || 3);
bagIds.push(item.id);
}
}
// Ensure all bags have barcode assignments
if (bagIds.length) {
assignBarcodes(db, 'bag', bagIds);
}
}
}
// ── Scan Log (replace all) ──
if (_section(incoming, 'scanLog', allowEmpty)) {
db.prepare('DELETE FROM scan_log').run();
// P-06: scan_log was wiped — invalidate the in-memory bag-zone cache
// so the next snapshotDailyKPIs / getProductionPipeline rebuilds it
// from the freshly-imported rows.
invalidateBagZoneCache(db);
// I-11: preserve client_uuid on bulk import so re-imported scan entries
// keep their idempotency keys. Older exports won't have the field; the
// column is nullable.
const ins = db.prepare(
'INSERT INTO scan_log(time, action, batch, bag, "from", "to", species, strain, user_id, reason, client_uuid) VALUES(?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)'
);
for (const e of incoming.scanLog) {
ins.run(
e.time,
e.action,
e.batch || null,
e.bag || null,
e.from || null,
e.to || null,
e.species || null,
e.strain || null,
e.userId ?? e.user_id ?? null,
e.reason || null,
e.client_uuid || e.clientUuid || null
);
}
}
// ── Harvests (replace all) ──
if (_section(incoming, 'harvests', allowEmpty)) {
db.prepare('DELETE FROM harvests').run();
const ins = db.prepare(
'INSERT INTO harvests(time, batch, bag, species, strain, grams, flush, quality, notes) VALUES(?, ?, ?, ?, ?, ?, ?, ?, ?)'
);
for (const h of incoming.harvests) {
ins.run(
h.time,
h.batch || null,
h.bag || null,
h.species || null,
h.strain || null,
h.grams,
h.flush || 1,
h.quality || null,
h.notes || null
);
}
}
// ── Cultures ──
if (_section(incoming, 'cultures', allowEmpty)) {
const existingIds = new Set(
db
.prepare('SELECT id FROM cultures')
.all()
.map((r) => r.id)
);
const incomingIds = new Set(incoming.cultures.map((c) => c.id));
const wegIds = [...existingIds].filter((id) => !incomingIds.has(id));
_verknuepfungGeht(db, 'cultureId', wegIds, opts && opts.wer);
for (const id of wegIds) {
db.prepare('DELETE FROM cultures WHERE id = ?').run(id);
}
const upsert = db.prepare(`
INSERT INTO cultures(id, type, species, strain, strain_id, strain_text, parent_id, source, status, notes, created, ml, stueck)
VALUES(?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
ON CONFLICT(id) DO UPDATE SET
type=excluded.type, species=excluded.species, strain=excluded.strain,
strain_id=excluded.strain_id, strain_text=excluded.strain_text,
parent_id=excluded.parent_id, source=excluded.source, status=excluded.status,
notes=excluded.notes, created=excluded.created, ml=excluded.ml, stueck=excluded.stueck
`);
const cultureIds = [];
for (const c of incoming.cultures) {
upsert.run(
c.id,
c.type,
c.species || null,
c.strain || null,
c.strainId || null,
c.strainText || '',
c.parentId || null,
c.source || null,
c.status || 'active',
c.notes || '',
c.created,
syringeMl(c.type, c.ml),
duebelStueck(c.type, c.stueck)
);
cultureIds.push(c.id);
}
// Ensure all cultures have barcode assignments
if (cultureIds.length) {
assignBarcodes(db, 'culture', cultureIds);
}
}
// ── Manual Tasks (replace all) ──
if (_section(incoming, 'manualTasks', allowEmpty)) {
db.prepare('DELETE FROM manual_tasks').run();
const ins = db.prepare(
'INSERT INTO manual_tasks(text, priority, done, created, assignee, due_date, due_time, due_end_time, description, caldav_uid, caldav_synced, private, recurrence, recurrence_until) VALUES(?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)'
);
for (const t of incoming.manualTasks) {
ins.run(
t.text,
t.priority || 'med',
t.done ? 1 : 0,
t.created,
t.assignee || null,
t.dueDate || null,
t.dueTime || null,
t.dueEndTime || null,
t.description || null,
cleanCaldavUid(t.caldavUid),
t.caldavSynced || null,
t.private ? 1 : 0,
t.recurrence || null,
t.recurrenceUntil || null
);
}
}
// ── Team Members ──
if (_section(incoming, 'teamMembers', allowEmpty)) {
db.prepare('DELETE FROM team_members').run();
const ins = db.prepare('INSERT INTO team_members(name, role, added) VALUES(?, ?, ?)');
for (const m of incoming.teamMembers) {
ins.run(m.name, m.role || null, m.added);
}
}
// ── Inventory (config only — stock is managed via delta endpoints) ──
if (incoming.inventory) {
const inv = incoming.inventory;
const thresh = inv.thresholds || {};
const avg = inv.avgComposition || {};
const lt = inv.labThresholds || {};
db.prepare(
`
UPDATE inventory SET
thresh_hardwood=?, thresh_wheatbran=?, thresh_gypsum=?, thresh_grain=?,
avg_hw_pct=?, avg_wb_pct=?, avg_rh_pct=?, avg_bag_kg=?, avg_grain_bag_kg=?, avg_grain_rh_pct=?,
lab_thresh_mc=?, lab_thresh_pd=?, lab_thresh_lc=?, lab_thresh_g2g=?, lab_thresh_gs=?, lab_thresh_sy=?
WHERE id=1
`
).run(
(thresh.hardwood && thresh.hardwood.minKg) ?? 50,
(thresh.wheatbran && thresh.wheatbran.minKg) ?? 20,
(thresh.gypsum && thresh.gypsum.minKg) ?? 5,
(thresh.grain && thresh.grain.minKg) ?? 10,
avg.hwPct ?? 75,
avg.wbPct ?? 25,
avg.rhPct ?? 63,
avg.bagKg ?? 3,
avg.grainBagKg ?? 1,
avg.grainRhPct ?? 52,
lt.MC ?? 0,
lt.PD ?? 0,
lt.LC ?? 0,
lt.G2G ?? 0,
lt.GS ?? 0,
lt.SY ?? 0
);
}
// Backups taken before v52 still carry an `assets` key (the removed
// fixed-asset register). It is ignored rather than restored.
// ── Calendar Events ──
if (_section(incoming, 'calendarEvents', allowEmpty)) {
const existingIds = new Set(
db
.prepare('SELECT id FROM calendar_events')
.all()
.map((r) => r.id)
);
const incomingIds = new Set(incoming.calendarEvents.map((e) => e.id));
for (const id of existingIds) {
if (!incomingIds.has(id)) {
db.prepare('DELETE FROM calendar_events WHERE id = ?').run(id);
}
}
const upsert = db.prepare(`
INSERT INTO calendar_events(id, title, description, start_date, end_date, all_day,
start_time, end_time, category, color, caldav_uid, caldav_synced, created,
recurrence, recurrence_until, team_assignees, exception_dates)
VALUES(?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
ON CONFLICT(id) DO UPDATE SET
title=excluded.title, description=excluded.description, start_date=excluded.start_date,
end_date=excluded.end_date, all_day=excluded.all_day, start_time=excluded.start_time,
end_time=excluded.end_time, category=excluded.category, color=excluded.color,
caldav_uid=excluded.caldav_uid, caldav_synced=excluded.caldav_synced, created=excluded.created,
recurrence=excluded.recurrence, recurrence_until=excluded.recurrence_until,
team_assignees=excluded.team_assignees,
exception_dates=excluded.exception_dates
`);
for (const e of incoming.calendarEvents) {
upsert.run(
e.id,
e.title,
e.description || null,
e.startDate,
e.endDate || null,
e.allDay ? 1 : 0,
e.startTime || null,
e.endTime || null,
e.category || 'custom',
e.color || null,
e.caldavUid || null,
e.caldavSynced || null,
e.created,
e.recurrence || null,
e.recurrenceUntil || null,
serializeTeamAssignees(e.teamAssignees),
serializeExceptionDates(e.exceptionDates)
);
}
// Sync assignees
db.prepare('DELETE FROM calendar_event_assignees').run();
const insAssignee = db.prepare('INSERT OR IGNORE INTO calendar_event_assignees(event_id, user_id) VALUES(?, ?)');
for (const e of incoming.calendarEvents) {
if (e.assignees && e.assignees.length) {
for (const a of e.assignees) insAssignee.run(e.id, a.userId);
}
}
}
// ── Zones & Racks ──
if (_section(incoming, 'zones', allowEmpty)) {
const existingZoneIds = new Set(
db
.prepare('SELECT id FROM zones')
.all()
.map((r) => r.id)
);
const existingRackIds = new Set(
db
.prepare('SELECT id FROM racks')
.all()
.map((r) => r.id)
);
const incomingZoneIds = new Set(incoming.zones.map((z) => z.id));
const incomingRackIds = new Set(incoming.zones.flatMap((z) => (z.racks || []).map((r) => r.id)));
// Delete racks removed from zones that still exist
for (const id of existingRackIds) {
if (!incomingRackIds.has(id)) {
db.prepare('DELETE FROM racks WHERE id = ?').run(id);
}
}
// Delete zones missing from incoming (cascades remaining racks)
for (const id of existingZoneIds) {
if (!incomingZoneIds.has(id)) {
db.prepare('DELETE FROM zones WHERE id = ?').run(id);
}
}
const upsertZone = db.prepare(`
INSERT INTO zones(id, name, role, color, sort_order, created)
VALUES(?, ?, ?, ?, ?, ?)
ON CONFLICT(id) DO UPDATE SET
name=excluded.name, role=excluded.role, color=excluded.color,
sort_order=excluded.sort_order, created=excluded.created
`);
const zoneIds = [];
const rackIds = [];
for (const z of incoming.zones) {
upsertZone.run(z.id, z.name, z.role, z.color, z.sortOrder || 0, z.created || new Date().toISOString());
zoneIds.push(z.id);
}
const upsertRack = db.prepare(`
INSERT INTO racks(id, zone_id, sort_order, created)
VALUES(?, ?, ?, ?)
ON CONFLICT(id) DO UPDATE SET
zone_id=excluded.zone_id, sort_order=excluded.sort_order, created=excluded.created
`);
for (const z of incoming.zones) {
for (const r of z.racks || []) {
upsertRack.run(r.id, z.id, r.sortOrder || 0, r.created || new Date().toISOString());
rackIds.push(r.id);
}
}
// Ensure all zones and racks have barcode assignments
if (zoneIds.length) assignBarcodes(db, 'zone', zoneIds);
if (rackIds.length) assignBarcodes(db, 'rack', rackIds);
}
// ── CalDAV Config ──
if (incoming.caldav) {
const c = incoming.caldav;
db.prepare(`UPDATE caldav_config SET enabled=? WHERE id=1`).run(c.enabled ? 1 : 0);
}
incrementDataVersion(db);
db.exec('COMMIT');
} catch (e) {
db.exec('ROLLBACK');
throw e;
}
}
// ── Backup ───────────────────────────────────────────────────
// R-16: pre-flight disk-space check. VACUUM INTO with no free space leaves
// a half-written file behind and fails part-way through, so we'd rather
// fail loudly upfront. Node's fs.statfsSync was added in 18.15 but is not
// fully available on Windows in all Node 22 builds — treat any throw from
// statfsSync as "platform doesn't support this; log and continue".
function checkDiskSpace(targetPath, requiredBytes) {
try {
const dir = path.dirname(path.resolve(targetPath));
const stats = fs.statfsSync(dir);
const free = Number(stats.bavail) * Number(stats.bsize);
if (free < requiredBytes) {
throw new Error(
'Insufficient disk space: ' +
Math.round(free / 1e6) +
'MB free, ' +
Math.round(requiredBytes / 1e6) +
'MB required'
);
}
return { free, required: requiredBytes, ok: true };
} catch (e) {
if (e.message && e.message.startsWith('Insufficient')) throw e;
// statfsSync not supported (older Node, Windows without polyfill): skip.
return { free: null, required: requiredBytes, ok: true, skipped: true, reason: e.message };
}
}
function backupDb(db, destPath) {
// The target goes in as a bound parameter, so no path is ever SQL text and
// none needs refusing. VACUUM INTO takes any expression for its file name.
// It used to be spliced in as a quoted literal behind a whitelist of plain
// ASCII characters, and that whitelist refused every installation whose path
// had an umlaut, a bracket, an ampersand or an 8.3 "~" in it: the nightly
// backup failed there every night, and so did every update snapshot.
if (typeof destPath !== 'string' || !destPath.length) {
throw new Error('Backup path required');
}
// R-16: pre-flight disk-space check. Require ~3x the current DB size: the
// VACUUM target file is up to 1x, plus headroom for SQLite's own working
// copy. Errors here surface as backup failures rather than corrupting the
// primary DB.
try {
const dbFile = db._mpDbPath || null;
if (dbFile && fs.existsSync(dbFile)) {
const dbSize = fs.statSync(dbFile).size;
checkDiskSpace(destPath, 3 * dbSize);
}
} catch (spaceErr) {
if (spaceErr.message && spaceErr.message.startsWith('Insufficient')) {
throw spaceErr;
}
// Other errors from the space check (e.g. statSync race) shouldn't
// block the backup itself — fall through to VACUUM.
}
db.prepare('VACUUM INTO ?').run(destPath);
return Promise.resolve();
}
// ── Update CalDAV UID on a task after sync ──
function updateTaskCaldavUid(db, text, created, uid, synced) {
// Same rule as the other two stores: a uid we would refuse to write is not
// worth keeping. This one is reached by push-one's private+unassigned branch,
// where writeIcsFile never runs and so never had a chance to object.
db.prepare('UPDATE manual_tasks SET caldav_uid = ?, caldav_synced = ? WHERE text = ? AND created = ?').run(
cleanCaldavUid(uid),
synced,
text,
created
);
incrementDataVersion(db);
}
// ── Update batch due date (for calendar drag or CalDAV bidirectional sync) ──
function updateBatchDue(db, batchId, newDueISO) {
const batch = db.prepare('SELECT created FROM batches WHERE batch_id = ?').get(batchId);
if (!batch) return;
const created = new Date(batch.created);
const newDue = new Date(newDueISO);
const newDays = Math.max(1, Math.round((newDue - created) / 86400000));
db.prepare('UPDATE batches SET due = ?, days = ? WHERE batch_id = ?').run(newDueISO, newDays, batchId);
incrementDataVersion(db);
}
// ── Update task due date (for calendar drag or CalDAV bidirectional sync) ──
function updateTaskDueDate(db, caldavUid, newDueDate) {
// I-15: bump SEQUENCE so the change propagates to CalDAV clients.
db.prepare(
'UPDATE manual_tasks SET due_date = ?, caldav_synced = NULL, sequence = sequence + 1 WHERE caldav_uid = ?'
).run(newDueDate, caldavUid);
incrementDataVersion(db);
}
// ── Read only CalDAV config (lightweight, for auth checks) ──
function readCaldavConfig(db) {
const cal = db.prepare('SELECT * FROM caldav_config WHERE id = 1').get();
if (!cal) return { enabled: false };
return {
enabled: cal.enabled === 1
};
}
// ── Auth helpers ────────────────────────────────────────────
// S-14: account passwords used crypto.scryptSync's defaults — N=16384, about
// 16 MB and 30-50 ms — while the backup KDF a few hundred lines away in
// server.js already used N=131072. OWASP's current floor for scrypt is N=2^17,
// so the accounts were the weakest KDF in the codebase and the backup file the
// strongest, which is backwards.
//
// The parameters live in the salt column rather than in a new column or a
// migration: a salt written by this version carries the "s2$" prefix, one
// written before it is bare hex. Both formats verify, so nothing is locked out,
// and a row upgrades itself the next time its owner logs in. Because the marker
// travels with the row there is no way to apply the wrong cost to a hash.
//
// maxmem has to be raised explicitly — 128 * N * r is 128 MB here, well over
// Node's 32 MB default, and scryptSync throws rather than degrading.
const SCRYPT_PARAMS = { N: 131072, r: 8, p: 1, maxmem: 192 * 1024 * 1024 };
const SCRYPT_SALT_PREFIX = 's2$';
function scryptFor(password, salt) {
return String(salt).startsWith(SCRYPT_SALT_PREFIX)
? crypto.scryptSync(password, salt, 64, SCRYPT_PARAMS)
: crypto.scryptSync(password, salt, 64); // pre-S-14 row: Node's defaults
}
/** Hash a password with the current parameters. Returns { salt, hash } as stored. */
function hashPassword(password) {
const salt = SCRYPT_SALT_PREFIX + crypto.randomBytes(16).toString('hex');
return { salt, hash: scryptFor(password, salt).toString('hex') };
}
/** True when the stored row predates the current parameters and should be re-hashed. */
function passwordNeedsUpgrade(salt) {
return !String(salt || '').startsWith(SCRYPT_SALT_PREFIX);
}
// S-18: the URL segment a user's personal CalDAV calendar lives at. This lives
// in db.js rather than server.js because createUser is what has to enforce it:
// /[^a-z0-9]+/ collapses '.', '_' and '-' to the same character, so bob.smith,
// bob_smith, bob-smith and Bob.Smith all produce the slug "bob-smith" — and
// checkCalendarAccess grants access on nothing more than a slug match. Two such
// accounts could read and write each other's personal calendar. createUser
// already rejected case-insensitive exact duplicates; it did not know about
// this weaker equality. The naming drift that produces it (erika.muster one
// month, erika_muster the next) is exactly what happens in practice.
function caldavSlug(username) {
return String(username)
.toLowerCase()
.replace(/[^a-z0-9]+/g, '-');
}
/** Usernames that share a CalDAV slug. Empty on a healthy database. */
function findCaldavSlugCollisions(db) {
const bySlug = new Map();
for (const r of db.prepare('SELECT username FROM users').all()) {
const slug = caldavSlug(r.username);
if (!bySlug.has(slug)) bySlug.set(slug, []);
bySlug.get(slug).push(r.username);
}
return [...bySlug.entries()].filter(([, names]) => names.length > 1).map(([slug, names]) => ({ slug, names }));
}
function createUser(db, username, password, role) {
// Login matches usernames case-insensitively (getUserByUsernameCaseInsensitive),
// but the column's UNIQUE is case-sensitive. Without this guard 'Admin' could
// be created alongside 'admin' and the ambiguous lookup would lock one of them
// out. Reject case-insensitive duplicates up front.
if (db.prepare('SELECT 1 FROM users WHERE username = ? COLLATE NOCASE').get(username)) {
throw new Error('Username already exists');
}
// S-18: and reject one that collides under the weaker CalDAV equality too.
const slug = caldavSlug(username);
const clash = db
.prepare('SELECT username FROM users')
.all()
.find((u) => caldavSlug(u.username) === slug);
if (clash) {
throw new Error('Username conflicts with existing user: ' + clash.username + ' (same CalDAV calendar name)');
}
const { salt, hash } = hashPassword(password);
const created = new Date().toISOString();
db.prepare('INSERT INTO users(username, hash, salt, role, created) VALUES(?, ?, ?, ?, ?)').run(
username,
hash,
salt,
role || 'user',
created
);
return { username, role: role || 'user', created };
}
function getUserByUsername(db, username) {
return db.prepare('SELECT * FROM users WHERE username = ?').get(username);
}
function getUserByUsernameCaseInsensitive(db, username) {
return db.prepare('SELECT * FROM users WHERE username = ? COLLATE NOCASE').get(username);
}
function verifyPassword(storedHash, salt, password) {
const a = Buffer.from(storedHash, 'hex');
const b = scryptFor(password, salt);
if (a.length !== b.length) return false;
return crypto.timingSafeEqual(a, b);
}
// S-15: sessions.token used to hold the very bytes the browser holds, so a
// single read of the database file — a stray backup, a filesystem snapshot, an
// ops copy handed to somebody for debugging — was every live session for up to
// seven days. The MCP token in the same schema was already stored as a SHA-256
// hash and compared with timingSafeEqual; sessions never got the same
// treatment. A plain digest is right here (unlike for passwords): the token is
// 32 bytes of CSPRNG output, so there is nothing to brute-force and nothing a
// KDF would add, and the lookup stays a single indexed equality.
function hashSessionToken(token) {
return crypto.createHash('sha256').update(String(token)).digest('hex');
}
function createSession(db, userId) {
// Enforce session limit per user — evict oldest when at cap
const count = db.prepare('SELECT COUNT(*) as count FROM sessions WHERE user_id = ?').get(userId).count;
if (count >= MAX_SESSIONS_PER_USER) {
db.prepare(
`DELETE FROM sessions WHERE token IN (
SELECT token FROM sessions WHERE user_id = ? ORDER BY created ASC LIMIT ?
)`
).run(userId, count - MAX_SESSIONS_PER_USER + 1);
}
const token = crypto.randomBytes(32).toString('hex');
const created = new Date().toISOString();
const expires = new Date(Date.now() + SESSION_TTL_MS).toISOString();
// The row stores the hash; the caller gets the token, once, for the cookie.
db.prepare('INSERT INTO sessions(token, user_id, created, expires) VALUES(?, ?, ?, ?)').run(
hashSessionToken(token),
userId,
created,
expires
);
return token;
}
// Kachel-Reihenfolge je Benutzer. Der Server kennt die Kacheln nicht; er nimmt
// eine kurze Liste kurzer Kürzel an und gibt sie unverändert zurück — was davon
// eine Kachel ist, entscheidet die Oberfläche (wkTileOrder in app.js).
const TILE_ORDER_MAX = 20;
function normTileOrder(order) {
if (!Array.isArray(order) || order.length > TILE_ORDER_MAX) return null;
if (!order.every((id) => typeof id === 'string' && /^[a-z]{1,20}$/.test(id))) return null;
return [...new Set(order)];
}
function getUserTileOrder(db, userId) {
const row = db.prepare('SELECT tile_order FROM users WHERE id = ?').get(userId);
if (!row || !row.tile_order) return null;
try {
return normTileOrder(JSON.parse(row.tile_order));
} catch (_) {
return null;
}
}
function setUserTileOrder(db, userId, order) {
const clean = order === null ? null : normTileOrder(order);
if (order !== null && !clean) throw new Error('invalid tile order');
db.prepare('UPDATE users SET tile_order = ? WHERE id = ?').run(clean ? JSON.stringify(clean) : null, userId);
return clean;
}
function getSession(db, token) {
// s.token is deliberately not selected — it is a hash now, and no caller
// wanted it. Returning it would only invite somebody to treat it as the
// cookie value again.
return db
.prepare(
`SELECT s.user_id, s.expires, u.username, u.role, u.can_ship, u.can_release
FROM sessions s JOIN users u ON s.user_id = u.id
WHERE s.token = ? AND s.expires > strftime('%Y-%m-%dT%H:%M:%fZ', 'now')`
)
.get(hashSessionToken(token));
}
function deleteSession(db, token) {
db.prepare('DELETE FROM sessions WHERE token = ?').run(hashSessionToken(token));
}
// Every credential that speaks for one user: browser sessions, OAuth access
// and refresh tokens, and any authorization code not yet exchanged. Kept in one
// place because there are three callers and forgetting one of the tables is
// exactly the bug S-11 was.
function deleteAuthArtifactsNoTxn(db, userId) {
db.prepare('DELETE FROM oauth_tokens WHERE user_id = ?').run(userId);
db.prepare('DELETE FROM oauth_codes WHERE user_id = ?').run(userId);
db.prepare('DELETE FROM sessions WHERE user_id = ?').run(userId);
// S-25: CalDAV app passwords go too. They are deliberately independent of the
// account password day to day — that is the whole point of them — but a
// password change is the response to "this account may be compromised", and
// somebody who had the account could have minted one. The cost is re-adding
// the calendar on each device, which is the same trade every provider makes.
db.prepare('DELETE FROM caldav_app_passwords WHERE user_id = ?').run(userId);
}
// S-11: a password change is the standard answer to "my account may be
// compromised", so it has to end every way in. Deleting the sessions alone left
// an attacker's OAuth access token valid for another hour and their refresh
// token for 30 days — and /mcp accepts a refresh-minted token with the victim's
// live role, admin included. Transactional so a failure halfway cannot leave
// the sessions gone and the tokens alive.
function revokeUserCredentials(db, userId) {
db.exec('BEGIN');
try {
deleteAuthArtifactsNoTxn(db, userId);
db.exec('COMMIT');
} catch (e) {
db.exec('ROLLBACK');
throw e;
}
}
function deleteExpiredSessions(db) {
db.prepare("DELETE FROM sessions WHERE expires < strftime('%Y-%m-%dT%H:%M:%fZ', 'now')").run();
}
// R-10: periodic cleanup helpers (called from server.js setInterval).
// Both return the count of rows deleted so the caller can log totals.
function cleanupExpiredSessions(db) {
const info = db.prepare("DELETE FROM sessions WHERE expires < strftime('%Y-%m-%dT%H:%M:%fZ', 'now')").run();
return info.changes;
}
function cleanupOldNotifications(db) {
// Hold read notifications 30 days, then GC. Unread notifications are kept
// forever so users don't lose anything they haven't seen.
const info = db.prepare("DELETE FROM notifications WHERE read = 1 AND created < datetime('now', '-30 days')").run();
return info.changes;
}
// ── Notifications ──
function createNotification(db, { userId, type, title, body, linkType, linkId }) {
if (!userId || !type || !title) throw new Error('createNotification: userId, type, title required');
const info = db
.prepare(
`INSERT INTO notifications(user_id, type, title, body, link_type, link_id, created, read)
VALUES(?, ?, ?, ?, ?, ?, ?, 0)`
)
.run(userId, type, title, body || null, linkType || null, linkId || null, new Date().toISOString());
return info.lastInsertRowid;
}
// S-20: like createNotification, but a no-op when that user already has an
// unread notification pointing at the same thing.
//
// /api/channels/ebay/deletion is unauthenticated by necessity — eBay calls it
// from its own infrastructure, and the POST carries no signature this server
// verifies yet. findCustomerByIdentity falls back to matching on email, so
// anyone who knows a customer's email address could send that notification
// repeatedly and get one row per admin per request. The handler deliberately
// does not erase anything, so the rows were the whole effect: a notification
// list buried under duplicates, which is how a real one gets missed.
//
// Unread is the right key rather than "ever seen": a second request about a
// customer whose first is still sitting unread adds nothing, while a request
// arriving after the admin has dealt with the last one is a new event and
// should say so.
function createNotificationOnce(db, payload) {
const { userId, type, linkType, linkId } = payload || {};
if (userId && type) {
// `IS` rather than `=` so a NULL link matches a NULL link.
const existing = db
.prepare(
`SELECT id FROM notifications
WHERE user_id = ? AND type = ? AND read = 0 AND link_type IS ? AND link_id IS ?`
)
.get(userId, type, linkType || null, linkId || null);
if (existing) return existing.id;
}
return createNotification(db, payload);
}
function listNotifications(db, userId, limit = 20) {
const lim = Math.max(1, Math.min(100, parseInt(limit, 10) || 20));
return db
.prepare(
// id DESC breaks ties when two rows share a created timestamp
// (notifications inserted in the same millisecond).
`SELECT id, user_id AS userId, type, title, body, link_type AS linkType, link_id AS linkId, created, read
FROM notifications
WHERE user_id = ?
ORDER BY created DESC, id DESC
LIMIT ?`
)
.all(userId, lim);
}
function countUnreadNotifications(db, userId) {
const row = db.prepare('SELECT COUNT(*) AS c FROM notifications WHERE user_id = ? AND read = 0').get(userId);
return row ? row.c : 0;
}
function markNotificationsRead(db, userId, ids) {
if (ids == null) {
// Mark all unread as read
const info = db.prepare('UPDATE notifications SET read = 1 WHERE user_id = ? AND read = 0').run(userId);
return info.changes;
}
if (!Array.isArray(ids) || !ids.length) return 0;
const placeholders = ids.map(() => '?').join(',');
const info = db
.prepare(`UPDATE notifications SET read = 1 WHERE user_id = ? AND id IN (${placeholders})`)
.run(userId, ...ids);
return info.changes;
}
function countUsers(db) {
return db.prepare('SELECT COUNT(*) as count FROM users').get().count;
}
function listUsers(db) {
return db.prepare('SELECT id, username, role, can_ship, can_release, created FROM users ORDER BY id').all();
}
/**
* The same list with everything a non-admin has no business reading removed.
*
* What is left is what an assignee picker needs and nothing else. Kept here
* rather than spelled out at each caller: GET /api/usernames and the MCP
* list_users tool both want it, and two copies of a projection like this is how
* one of them quietly stops matching after a column is added. It is an
* allowlist for the same reason — a new users column is invisible to it until
* somebody decides otherwise.
*/
function listUsersPublic(db) {
return listUsers(db).map((u) => ({ id: u.id, username: u.username }));
}
function deleteUser(db, userId) {
// I-16: clean up all auth artifacts so a freshly-recycled user_id can't
// inherit OAuth grants/tokens/sessions from the deleted account. Wrap in
// a transaction so a partial failure doesn't leave dangling tokens.
db.exec('BEGIN');
try {
deleteAuthArtifactsNoTxn(db, userId);
db.prepare('DELETE FROM users WHERE id = ?').run(userId);
incrementDataVersion(db);
db.exec('COMMIT');
} catch (e) {
db.exec('ROLLBACK');
throw e;
}
}
function updateUserPassword(db, userId, hash, salt) {
db.prepare('UPDATE users SET hash = ?, salt = ? WHERE id = ?').run(hash, salt, userId);
}
// Grant/revoke the per-user shipping capability (admins always qualify regardless).
function setUserCanShip(db, userId, canShip) {
db.prepare('UPDATE users SET can_ship = ? WHERE id = ?').run(canShip ? 1 : 0, userId);
incrementDataVersion(db);
}
// Grant/revoke the per-user release capability (admins always qualify regardless).
function setUserCanRelease(db, userId, canRelease) {
db.prepare('UPDATE users SET can_release = ? WHERE id = ?').run(canRelease ? 1 : 0, userId);
incrementDataVersion(db);
}
function resetUserPassword(db, userId, newPassword) {
const { salt, hash } = hashPassword(newPassword);
db.prepare('UPDATE users SET hash = ?, salt = ? WHERE id = ?').run(hash, salt, userId);
}
// ── Atomic CRUD functions ───────────────────────────────────
// -- Batches --
// `deltas` is an optional array of { mat, deltaKg, type, ref } applied inside
// the same transaction as the batch + bag inserts. Atomicity guarantee: if
// any delta or insert fails, the batch row, bag rows, inventory mutations,
// and inventory_log entries are all rolled back.
// ─── Substrate batches (v67) ──────────────────────────────────────────
// Two levels, because that is how the substrate actually moves. A mix is made
// once, in bulk, from raw materials — that is the only step that touches the
// shelf. Bags are then portioned out of that mix, possibly for several species,
// and cost nothing further except the spawn they are inoculated with.
//
// Everything below keeps those two apart: computeMixBatch prices a mix,
// createSubstrateBatch books it, and drawSubstrate moves kilograms out of it.
// Price a mix. `rec` is a blend, not a species — the substrate does not know
// what will be grown in it. Spawn is deliberately absent: it belongs to the bag
// batch, where the species is finally known.
//
// The subtlety is `residualPct`. Bagged pellets and bran already carry moisture,
// so reaching a target hydration takes more dry mix than (1 - moisture) implies.
// Leaving it out under-books every run by roughly 8% — on a large mix that is
// several kilos of pellets and bran that never leave the books.
function computeMixBatch(rec, targetKg, opts) {
const num = (v, def) => (Number.isFinite(+v) ? +v : def);
const target = num(targetKg, 0);
if (!(target > 0)) throw new Error('Zielmenge muss groesser als 0 kg sein');
const o = opts || {};
const R0 = num(o.residualPct, 9) / 100;
const flow = num(o.flowLmin, 10);
const bran = num(rec.branPct, 0) / 100;
const corn = num(rec.cornPct, 0) / 100;
const gyp = num(rec.gypsumPct, 0) / 100;
// Calciumcarbonat wie Gips: ein Anteil der Trockenmischung, obendrauf.
const calc = num(rec.calciumPct, 0) / 100;
const moist = num(rec.moisturePct, 0) / 100;
// Pellets are the remainder so the three shares can never total anything but
// 100%. A blend that leaves nothing over is a data error, not a 0 kg order.
// The base is whatever the blend is not: pellets on a hardwood recipe, coir
// on a CVG one. Taking the remainder rather than storing it keeps the shares
// from drifting into a blend that does not total 100%.
const baseMat = (rec.substrate || 'holzkleie') === 'cvg' ? 'coir' : 'hardwood';
const pelletShare = 1 - bran - corn;
if (pelletShare <= 0) {
throw new Error(
'Kleie + Maismehl ergeben ' + ((bran + corn) * 100).toFixed(0) + '% — kein Anteil fuer Pellets uebrig'
);
}
if (!(moist > 0 && moist < 1)) throw new Error('Zielfeuchte muss zwischen 0 und 100 % liegen');
// Jeder Rohstoff bringt seine eigene Restfeuchte mit: die Kleie ihre, alles
// andere (Pellets bzw. Kokos, Mais) die der Pellets. Gerechnet wird mit dem
// gewogenen Mittel ueber die Trockenmischung; ohne Kleie-Wert ist es R0.
const Rbran = o.residualBranPct == null || o.residualBranPct === '' ? R0 : num(o.residualBranPct, R0 * 100) / 100;
const R = R0 + bran * (Rbran - R0);
const denom = 1 + gyp + calc - R;
if (!(denom > 0)) throw new Error('Restfeuchte zu hoch fuer dieses Rezept');
const dryKg = (target * (1 - moist)) / denom;
const waterL = target - dryKg * (1 + gyp + calc);
// Negative water means the delivery is already wetter than the recipe target.
// Clamping silently would hand over a mix that cannot reach spec.
if (waterL < 0) {
throw new Error(
'Zielfeuchte ' +
(moist * 100).toFixed(1) +
' % liegt unter der Restfeuchte der Sackware (' +
(R * 100).toFixed(1) +
' %)'
);
}
const pelletsKg = dryKg * pelletShare;
const branKg = dryKg * bran;
const cornKg = dryKg * corn;
const gypsumKg = dryKg * gyp;
const calciumKg = dryKg * calc;
const deltas = [];
const push = (mat, kg) => {
if (kg > 0) deltas.push({ mat, deltaKg: -kg, type: 'mix' });
};
push(baseMat, pelletsKg);
push('wheatbran', branKg);
push('corn', cornKg);
push('gypsum', gypsumKg);
push('calcium', calciumKg);
return {
targetKg: target,
dryKg,
pelletsKg,
branKg,
cornKg,
gypsumKg,
calciumKg,
waterL,
// What the mix will actually measure once the residual moisture is in it —
// the figure to check the halogen analyser against, not the recipe target.
// Named so the card and the detail view can label the line honestly rather
// than always calling it pellets.
baseMat,
moisturePct: ((waterL + dryKg * R) / target) * 100,
waterMinutes: flow > 0 ? waterL / flow : 0,
hardwoodPct: pelletShare * 100,
wheatbranPct: bran * 100,
cornPct: corn * 100,
gypsumPct: gyp * 100,
calciumPct: calc * 100,
rhPct: moist * 100,
deltas
};
}
// Ein Rezept mit den Zahlen, die im Ansatz-Formular stehen.
//
// Das Rezept der Sorte war bisher die Vorschrift: wer einmal anders mischen
// wollte, musste das Rezept der Sorte umschreiben — und damit jeden künftigen
// Ansatz mit. Ein einzelner Versuchsansatz kostete also eine Änderung an der
// Sorte und eine zweite, um sie zurückzunehmen; die zweite vergisst man.
//
// Jetzt ist das Rezept die Vorlage, und der Ansatz darf davon abweichen. Was
// der Ansatz wirklich war, stand ohnehin schon in seinen eigenen Spalten
// (hardwood_pct, wheatbran_pct, … liegen seit jeher pro Ansatz in
// substrate_batches) — es gab nur keinen Weg, sie anders zu füllen als über die
// Sorte.
//
// Übernommen wird nur, was genannt ist; alles andere bleibt beim Rezept. Der
// Hartholzanteil steht nicht in der Liste, weil er keine Eingabe ist: er ist
// der Rest (1 − Kleie − Mais), und zwei Zahlen für dieselbe Größe driften.
function mixWithOverride(recipe, ov) {
if (!ov || typeof ov !== 'object') return { recipe, adjusted: false };
const out = { ...recipe };
let adjusted = false;
for (const k of ['branPct', 'cornPct', 'gypsumPct', 'calciumPct', 'moisturePct']) {
const v = ov[k];
if (v == null || !Number.isFinite(+v)) continue;
if (Math.abs(+v - (+recipe[k] || 0)) < 1e-9) continue;
out[k] = +v;
adjusted = true;
}
return { recipe: out, adjusted };
}
// Read a Sorte's recipe as a blend, plus the site-wide assumptions. Returns null
// when the Sorte has no usable recipe — Reishi and Cordyceps ship that way.
function getMixRecipe(db, strainId) {
const ms = db.prepare('SELECT * FROM mushroom_strains WHERE id=?').get(strainId);
if (!ms) throw new Error('Pilzsorte nicht gefunden');
// A mix needs a hydration target; without one there is no arithmetic to do.
// Gypsum is NOT required — CVG blends legitimately use none, and demanding it
// reported a complete recipe as missing and sent the operator to re-enter one
// that was already there.
if (!(ms.rec_rh_pct > 0)) return null;
const inv =
db.prepare('SELECT residual_pct, residual_bran_pct, water_flow_lmin FROM inventory WHERE id=1').get() || {};
return {
strain: ms,
recipe: {
// Which base the blend is built on. Without it every mix was priced as
// hardwood pellets, so a coir recipe drained the wrong shelf entirely.
substrate: ms.rec_substrate || 'holzkleie',
branPct: ms.rec_wheatbran_pct || 0,
cornPct: ms.rec_corn_pct || 0,
gypsumPct: ms.rec_gypsum_pct || 0,
moisturePct: ms.rec_rh_pct || 0
},
spawnPct: ms.rec_spawn_pct || 0,
blockKg: ms.rec_bag_kg || 0,
opts: {
residualPct: inv.residual_pct != null ? inv.residual_pct : 9,
residualBranPct: inv.residual_bran_pct,
flowLmin: inv.water_flow_lmin != null ? inv.water_flow_lmin : 10
}
};
}
// Read a stored mix (the recipes table) the same way. A Ansatz is species-
// neutral, so the form picks one of these rather than a Sorte; the Sorte path
// above stays for callers that still send recipeStrainId.
function getMixFromRecipe(db, recipeId) {
const r = db.prepare('SELECT * FROM recipes WHERE id=?').get(recipeId);
if (!r) throw new Error('Mischung nicht gefunden');
if (!(r.rh_pct > 0)) return null;
const inv =
db.prepare('SELECT residual_pct, residual_bran_pct, water_flow_lmin FROM inventory WHERE id=1').get() || {};
return {
label: r.name,
recipe: {
substrate: 'holzkleie',
branPct: r.wheatbran_pct || 0,
cornPct: 0,
gypsumPct: r.gypsum_pct || 0,
moisturePct: r.rh_pct || 0
},
opts: {
residualPct: inv.residual_pct != null ? inv.residual_pct : 9,
residualBranPct: inv.residual_bran_pct,
flowLmin: inv.water_flow_lmin != null ? inv.water_flow_lmin : 10
}
};
}
// Either source, one shape: { label, recipe, opts }. recipeId wins.
function resolveMix(db, b) {
if (b.recipeId != null) return getMixFromRecipe(db, b.recipeId);
const found = getMixRecipe(db, b.recipeStrainId);
if (!found) return null;
return {
label: found.strain.name + ' (' + found.strain.kuerzel + ')',
recipe: found.recipe,
opts: found.opts,
strainId: found.strain.id
};
}
// Mix a batch of substrate. This is the only step that draws raw materials.
function createSubstrateBatch(db, b, userId) {
const found = resolveMix(db, b);
if (!found) throw new Error('Diese Mischung hat noch keine Soll-Feuchte. Bitte zuerst im Rezept eintragen.');
const adj = mixWithOverride(found.recipe, b.mix);
const mix = computeMixBatch(adj.recipe, b.targetKg, found.opts);
const label = mixLabel(found.label, found.recipe, mix);
const water = waterPlan(db, mix.waterL, found.opts.flowLmin);
const created = b.created || new Date().toISOString();
db.exec('BEGIN');
try {
db.prepare(
`INSERT INTO substrate_batches
(sub_id, recipe_strain_id, recipe_label, target_kg, remaining_kg,
hardwood_pct, wheatbran_pct, corn_pct, gypsum_pct, rh_pct,
dry_kg, pellets_kg, bran_kg, corn_kg, gypsum_kg, water_l, moisture_pct,
notes, created, status, water_now_l, calcium_pct, calcium_kg, steri_min)
VALUES(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?, 'open', ?, ?, ?, ?)`
).run(
b.subId,
found.strainId != null ? found.strainId : null,
label,
mix.targetKg,
mix.targetKg,
mix.hardwoodPct,
mix.wheatbranPct,
mix.cornPct,
mix.gypsumPct,
mix.rhPct,
mix.dryKg,
mix.pelletsKg,
mix.branKg,
mix.cornKg,
mix.gypsumKg,
mix.waterL,
mix.moisturePct,
b.notes || '',
created,
water.nowL,
mix.calciumPct,
mix.calciumKg,
_steriMinOf(b.steriMin)
);
for (const d of mix.deltas) {
applyInventoryDeltaNoTxn(db, d.mat, d.deltaKg, 'mix', b.subId, userId || null);
}
incrementDataVersion(db);
db.exec('COMMIT');
} catch (e) {
db.exec('ROLLBACK');
throw e;
}
return { subId: b.subId, mix, water };
}
// Der Name eines Ansatzes: das Rezept, und dahinter jede Zahl, in der er davon
// abweicht. Ohne das stuende in der Liste der Rezeptname ueber einer anderen
// Mischung, und zwei Ansaetze, die sich nur im Calciumcarbonat oder im Gips
// unterscheiden, saehen gleich aus — gerade die, die man vergleichen will.
// Calciumcarbonat steht immer da, sobald welches drin ist: Rezepte kennen es
// nicht, also ist es immer eine Angabe dieses einen Ansatzes.
//
// Hier und nur hier gebaut. Die Vorschau gibt denselben Namen zurueck, damit
// Dialog, Formular und Liste nicht je ihre eigene Fassung pflegen.
function mixLabel(base, recipe, mix) {
const r = recipe || {};
const anders = (a, b) => Math.abs((+a || 0) - (+b || 0)) > 1e-9;
const zahl = (v) => String(Math.round(v * 10) / 10).replace('.', ',');
const teile = [];
if (anders(mix.wheatbranPct, r.branPct) || anders(mix.cornPct, r.cornPct)) {
teile.push(Math.round(mix.hardwoodPct) + '/' + Math.round(mix.wheatbranPct));
}
if (anders(mix.gypsumPct, r.gypsumPct)) teile.push('Gips ' + zahl(mix.gypsumPct) + ' %');
if (mix.calciumPct > 0) teile.push('CaCO₃ ' + zahl(mix.calciumPct) + ' %');
if (anders(mix.rhPct, r.moisturePct)) teile.push(zahl(mix.rhPct) + ' % Feuchte');
return (base || '') + teile.map((x) => ' · ' + x).join('');
}
// Sterilisierzeit in Minuten, oder null fuer "nicht eingetragen". Ein leeres
// Feld ist keine 0: 0 Minuten hiesse unsterilisiert.
function _steriMinOf(v) {
if (v == null || v === '') return null;
const n = Number(v);
if (!Number.isFinite(n)) throw new Error('Sterilisierzeit muss eine Zahl sein');
if (n < 0 || n > 1440) throw new Error('Sterilisierzeit zwischen 0 und 1440 Minuten');
return n;
}
// pH einer Charge, oder null fuer "nicht gemessen".
function _phOf(v) {
if (v == null || v === '') return null;
const n = Number(v);
if (!Number.isFinite(n)) throw new Error('pH muss eine Zahl sein');
if (n < 0 || n > 14) throw new Error('pH liegt zwischen 0 und 14');
return n;
}
function _mapSubstrateRow(r) {
return {
subId: r.sub_id,
recipeStrainId: r.recipe_strain_id,
recipeLabel: r.recipe_label,
targetKg: r.target_kg,
remainingKg: r.remaining_kg,
usedKg: r.target_kg - r.remaining_kg,
composition: {
hardwoodPct: r.hardwood_pct,
wheatbranPct: r.wheatbran_pct,
cornPct: r.corn_pct,
gypsumPct: r.gypsum_pct,
calciumPct: r.calcium_pct || 0,
rhPct: r.rh_pct
},
dryKg: r.dry_kg,
pelletsKg: r.pellets_kg,
branKg: r.bran_kg,
cornKg: r.corn_kg,
gypsumKg: r.gypsum_kg,
calciumKg: r.calcium_kg || 0,
steriMin: r.steri_min == null ? null : r.steri_min,
// Der pH der Chargen aus diesem Ansatz, gemessen beim Beimpfen: Mittel und
// wie viele gemessen haben. Neben steriMin die Antwort auf "was macht die
// Sterilisierzeit mit dem pH".
phAvg: r.ph_avg == null ? null : r.ph_avg,
phN: r.ph_n || 0,
waterL: r.water_l,
// moisturePct kommt aus dem Rezept — was der Ansatz treffen sollte.
// actualRhPct ist, was jemand nachgemessen hat, und bleibt null, solange
// niemand gemessen hat. Die beiden werden nie ineinander gerechnet.
moisturePct: r.moisture_pct,
actualRhPct: r.actual_rh_pct == null ? null : r.actual_rh_pct,
actualRhAt: r.actual_rh_at || null,
actualRhBy: r.actual_rh_by || null,
actualWaterL: r.actual_water_l == null ? null : r.actual_water_l,
waterNowL: r.water_now_l == null ? null : r.water_now_l,
waterFactor: waterFactorOf(r),
// Wie viele Beutel dieses Ansatzes im Autoklav geplatzt sind. 0 heisst
// keiner, nicht "unbekannt" — siehe Migration 83.
burstBags: r.burst_bags || 0,
notes: r.notes || '',
created: r.created,
status: r.status
};
}
// Everything a mix is: how it was made, what has come out of it, and what is
// left. The Chargen matter as much as the recipe — a mix that went wrong is
// diagnosed by what was made from it, not by re-reading its percentages.
function getSubstrateBatch(db, subId) {
const row = db.prepare(SUBSTRATE_SELECT + ' WHERE s.sub_id=?').get(subId);
if (!row) return null;
const drawn = db
.prepare(
`SELECT batch_id, species, strain, strain_text, qty, bag_kg, substrate_kg, created, due, ph
FROM batches WHERE substrate_batch_id=? ORDER BY created`
)
.all(row.id)
.map((b) => ({
batchId: b.batch_id,
species: b.species,
strain: b.strain_text || b.strain || '',
qty: b.qty,
bagKg: b.bag_kg,
substrateKg: b.substrate_kg,
created: b.created,
due: b.due,
ph: b.ph == null ? null : b.ph
}));
// What the mix actually took off the shelf, as booked — not as computed.
const ledger = db
.prepare(
"SELECT mat, delta_kg AS deltaKg, time FROM inventory_log WHERE ref = ? AND type IN ('mix','mix-delete') ORDER BY id"
)
.all(subId);
return { ..._mapSubstrateRow(row), drawn, ledger };
}
// A mix that went bad, or a remainder that got thrown out. No credit goes
// back to the shelf: the pellets were mixed and are gone either way. What
// changes is that it stops being offered to new Chargen and stops counting
// as substrate anybody can still use.
function writeOffSubstrateBatch(db, subId, note, userId) {
db.exec('BEGIN');
try {
const row = db.prepare('SELECT * FROM substrate_batches WHERE sub_id=?').get(subId);
if (!row) {
db.exec('ROLLBACK');
return false;
}
const lost = row.remaining_kg;
// Who threw away 100 kg of substrate is worth knowing later, and it makes the
// actor an argument this function actually uses rather than one it carries.
const who = userId ? (db.prepare('SELECT username FROM users WHERE id=?').get(userId) || {}).username : null;
const stamp =
'[' +
new Date().toISOString().slice(0, 10) +
'] ' +
(note || 'verworfen') +
' (' +
lost.toFixed(1) +
' kg)' +
(who ? ' — ' + who : '');
db.prepare(
"UPDATE substrate_batches SET status='written_off', remaining_kg=0, notes = CASE WHEN notes IS NULL OR notes='' THEN ? ELSE notes || ' · ' || ? END WHERE sub_id=?"
).run(stamp, stamp, subId);
incrementDataVersion(db);
db.exec('COMMIT');
return { lostKg: lost };
} catch (e) {
db.exec('ROLLBACK');
throw e;
}
}
// Was beim Nachmessen herauskam.
//
// Getrennt von moisture_pct, das aus dem Rezept stammt und dort auch bleibt: der
// Ansatz ist gemischt, und was er treffen sollte, ändert sich nicht rückwirkend
// dadurch, dass er es nicht getroffen hat. Beide Zahlen nebeneinander sind die
// Auskunft — eine allein wäre nur wieder die halbe.
//
// Ein zweiter Aufruf überschreibt den Wert, statt eine Messreihe anzulegen: wer
// sich vertippt, will die Zahl korrigieren, und eine Historie von Tippfehlern
// beantwortet keine Frage, die jemand stellt. null löscht die Messung wieder.
// ── Wasser: lernen und zurueckhalten ─────────────────────────
// Die Rechnung kennt die Physik, aber nicht den Wasserzaehler, das Messgeraet
// und die Lieferung von heute. Jeder Ansatz, an dem jemand die gemessene
// Feuchte UND das tatsaechlich zugegebene Wasser eingetragen hat, sagt, wie
// viel Wasser das Ziel getroffen haette. Das Verhaeltnis zur Rechnung ist der
// Lernfaktor; der Median der letzten Ansaetze korrigiert den naechsten.
//
// Danach wird ein Teil zurueckgehalten: zu viel Wasser laesst sich nicht wieder
// herausholen, zu wenig laesst sich nach dem Messen nachgeben.
const WATER_HOLDBACK_DEFAULT_PCT = 10;
const WATER_LEARN_LAST = 5;
// Lernfaktor eines Ansatzes, oder null, wenn er nichts Belastbares sagt.
// Gerechnet wird mit dem, was gebucht ist (dry_kg, gypsum_kg): was die
// Trockenmischung an Wasser mitbrachte, ergibt sich aus gemessener Feuchte und
// zugegebenem Wasser; daraus folgt, welche Wassermenge das Ziel getroffen haette.
function waterFactorOf(r) {
const W = r.actual_water_l;
const a = r.actual_rh_pct / 100;
const t = r.rh_pct / 100;
if (!(W > 0) || !(r.water_l > 0) || !(r.dry_kg > 0) || !(a > 0 && a < 1) || !(t > 0 && t < 1)) return null;
const solids = r.dry_kg + (r.gypsum_kg || 0) + (r.calcium_kg || 0);
const ausMaterial = a * (solids + W) - W;
const noetig = (t * solids - ausMaterial) / (1 - t);
const f = noetig / r.water_l;
// Ausserhalb dieses Bereichs ist eher eine Zahl vertippt als die Rechnung so
// weit daneben; so ein Ansatz soll den naechsten nicht verbiegen.
return Number.isFinite(f) && f >= 0.5 && f <= 1.5 ? f : null;
}
function learnedWaterFactor(db) {
const rows = db
.prepare(
`SELECT water_l, dry_kg, gypsum_kg, calcium_kg, rh_pct, actual_rh_pct, actual_water_l FROM substrate_batches
WHERE actual_water_l > 0 AND actual_rh_pct IS NOT NULL ORDER BY created DESC LIMIT 50`
)
.all();
const fs = [];
for (const r of rows) {
const f = waterFactorOf(r);
if (f != null) fs.push(f);
if (fs.length >= WATER_LEARN_LAST) break;
}
if (!fs.length) return { factor: 1, n: 0 };
const sorted = fs.slice().sort((x, y) => x - y);
const mid = sorted.length >> 1;
const factor = sorted.length % 2 ? sorted[mid] : (sorted[mid - 1] + sorted[mid]) / 2;
return { factor, n: fs.length };
}
// Was am Hahn zu tun ist: erst nowL, dann messen, dann hoechstens restL.
function waterPlan(db, waterL, flowLmin) {
const { factor, n } = learnedWaterFactor(db);
const holdbackPct = getResidualMoisture(db).holdbackPct;
const plannedL = waterL * factor;
const nowL = plannedL * (1 - holdbackPct / 100);
const flow = flowLmin > 0 ? flowLmin : 0;
return {
calcL: waterL,
factor,
n,
plannedL,
holdbackPct,
nowL,
restL: plannedL - nowL,
nowMinutes: flow ? nowL / flow : 0
};
}
// Was tatsaechlich zugegeben wurde. null loescht es wieder.
function setSubstrateWater(db, subId, liters) {
const row = db.prepare('SELECT id FROM substrate_batches WHERE sub_id=?').get(subId);
if (!row) return null;
let value = null;
if (liters !== null && liters !== undefined && liters !== '') {
const n = Number(liters);
if (!Number.isFinite(n) || n <= 0 || n > 100000)
throw new Error('Wassermenge muss eine positive Zahl in Litern sein');
value = Math.round(n * 10) / 10;
}
db.prepare('UPDATE substrate_batches SET actual_water_l=? WHERE sub_id=?').run(value, subId);
incrementDataVersion(db);
return { subId, actualWaterL: value };
}
// Restfeuchte der Sackware, wie sie die Mischrechnung liest. branPct null
// heisst: wie die Pellets.
function getResidualMoisture(db) {
const r =
db.prepare('SELECT residual_pct, residual_bran_pct, water_holdback_pct FROM inventory WHERE id=1').get() || {};
return {
pelletsPct: r.residual_pct != null ? r.residual_pct : 9,
branPct: r.residual_bran_pct == null ? null : r.residual_bran_pct,
holdbackPct: r.water_holdback_pct != null ? r.water_holdback_pct : WATER_HOLDBACK_DEFAULT_PCT
};
}
function setResidualMoisture(db, v) {
const check = (x, name) => {
if (x === null || x === undefined || x === '') return null;
const n = Number(x);
// Sackware unter 0 oder ueber 30 % ist ein Tippfehler, kein Messwert, und
// ein Wert dort liesse die Wassermenge negativ werden.
if (!Number.isFinite(n) || n < 0 || n > 30) throw new Error(name + ' muss zwischen 0 und 30 % liegen');
return Math.round(n * 10) / 10;
};
const p = check(v.pelletsPct, 'Restfeuchte Pellets');
const b = check(v.branPct, 'Restfeuchte Kleie');
if (p === null) throw new Error('Restfeuchte Pellets fehlt');
db.prepare('UPDATE inventory SET residual_pct=?, residual_bran_pct=? WHERE id=1').run(p, b);
if (v.holdbackPct !== undefined) {
const h = v.holdbackPct === null || v.holdbackPct === '' ? null : Number(v.holdbackPct);
if (h !== null && (!Number.isFinite(h) || h < 0 || h > 50))
throw new Error('Zurueckgehaltenes Wasser muss zwischen 0 und 50 % liegen');
db.prepare('UPDATE inventory SET water_holdback_pct=? WHERE id=1').run(h === null ? null : Math.round(h * 10) / 10);
}
incrementDataVersion(db);
return getResidualMoisture(db);
}
function setSubstrateMoisture(db, subId, pct, userId) {
const row = db.prepare('SELECT id FROM substrate_batches WHERE sub_id=?').get(subId);
if (!row) return null;
let value = null;
if (pct !== null && pct !== undefined && pct !== '') {
const n = Number(pct);
if (!Number.isFinite(n)) throw new Error('Ist-Feuchte muss eine Zahl sein');
// Weit genug für jede reale Messung, eng genug, dass 650 statt 65 als das
// durchfällt, was es ist. Ein Prozentsatz ausserhalb 0..100 ist kein
// Messwert, sondern ein Tippfehler.
if (n < 0 || n > 100) throw new Error('Ist-Feuchte muss zwischen 0 und 100 % liegen');
value = Math.round(n * 10) / 10;
}
const who =
value === null || !userId ? null : (db.prepare('SELECT username FROM users WHERE id=?').get(userId) || {}).username;
db.prepare('UPDATE substrate_batches SET actual_rh_pct=?, actual_rh_at=?, actual_rh_by=? WHERE sub_id=?').run(
value,
value === null ? null : new Date().toISOString(),
who || null,
subId
);
incrementDataVersion(db);
return { subId, actualRhPct: value };
}
// Der Kommentar am Ansatz.
//
// Schreibt in dasselbe notes-Feld, das beim Anlegen gefuellt wird. Ein zweites
// Textfeld daneben waere eine zweite Wahrheit, und niemand wuesste mehr, in
// welches von beiden er schaut.
//
// Das Verwerfen haengt seinen Stempel an genau dieses Feld an. Deshalb legt die
// Oberflaeche den bestehenden Text zum Bearbeiten vor, statt ein leeres Feld zu
// zeigen: was der Stempel festhaelt - wer wieviel weggeworfen hat - steht dann
// sichtbar da und kann nicht still ueberschrieben werden, ohne dass es jemand
// gesehen hat.
function setSubstrateNotes(db, subId, text) {
const row = db.prepare('SELECT id FROM substrate_batches WHERE sub_id=?').get(subId);
if (!row) return null;
const clean = text == null ? '' : String(text).trim();
// Dieselbe Grenze wie beim Verwerf-Vermerk nebenan, damit ein Feld nicht zwei
// Laengen hat, je nachdem wer hineinschreibt.
if (clean.length > 500) throw new Error('Kommentar darf hoechstens 500 Zeichen haben');
db.prepare('UPDATE substrate_batches SET notes=? WHERE sub_id=?').run(clean, subId);
incrementDataVersion(db);
return { subId, notes: clean };
}
// Wie viele Beutel dieses Ansatzes im Autoklav geplatzt sind.
//
// Kein Zeitstempel und keine Person, anders als bei der Ist-Feuchte: die Zahl
// gehoert zum Ansatz und nicht zum Moment ihrer Eingabe, und die Zeitachse fuer
// "wie oft passiert das" ist das Anlegedatum des Ansatzes. Wer es nachtraegt,
// traegt keine zweite Beobachtung ein, sondern vervollstaendigt dieselbe.
function setSubstrateBurstBags(db, subId, count) {
const row = db.prepare('SELECT id, target_kg FROM substrate_batches WHERE sub_id=?').get(subId);
if (!row) return null;
const n = Number(count == null || count === '' ? 0 : count);
if (!Number.isFinite(n)) throw new Error('Geplatzte Beutel muss eine Zahl sein');
if (!Number.isInteger(n)) throw new Error('Geplatzte Beutel muss eine ganze Zahl sein');
if (n < 0) throw new Error('Geplatzte Beutel kann nicht negativ sein');
// Die Obergrenze kommt aus dem Ansatz selbst: mehr Beutel, als er ueberhaupt
// fuellen kann, ist ein Tippfehler und keine Zahl. Ein Beutel unter 1 kg ist
// kein Block, also ist target_kg die grosszuegigste ehrliche Schranke — und
// sie waechst mit dem Ansatz statt als runde Zahl danebenzustehen.
const max = Math.max(1, Math.ceil(row.target_kg || 0));
if (n > max) throw new Error('So viele Beutel hat der Ansatz nicht — hoechstens ' + max);
db.prepare('UPDATE substrate_batches SET burst_bags=? WHERE sub_id=?').run(n, subId);
incrementDataVersion(db);
return { subId, burstBags: n };
}
// Nachgetragen wie die geplatzten Beutel: sterilisiert wird nach dem Mischen,
// und nicht jeder weiss die Zeit schon, wenn er den Ansatz anlegt. Leer setzt
// auf "nicht eingetragen" zurueck.
function setSubstrateSteriMin(db, subId, minutes) {
const row = db.prepare('SELECT id FROM substrate_batches WHERE sub_id=?').get(subId);
if (!row) return null;
const v = _steriMinOf(minutes);
db.prepare('UPDATE substrate_batches SET steri_min=? WHERE sub_id=?').run(v, subId);
incrementDataVersion(db);
return { subId, steriMin: v };
}
// Der Ansatz mit dem pH seiner Chargen. Nur gemessene Chargen gehen ein: eine
// ohne Messung zaehlt weder als 0 noch ueberhaupt. Einmal gruppiert und
// angehaengt, statt je Ansatz zweimal in batches zu suchen.
const SUBSTRATE_SELECT = `SELECT s.*, p.ph_avg, p.ph_n
FROM substrate_batches s
LEFT JOIN (SELECT substrate_batch_id, AVG(ph) AS ph_avg, COUNT(*) AS ph_n
FROM batches WHERE ph IS NOT NULL AND substrate_batch_id IS NOT NULL
GROUP BY substrate_batch_id) p ON p.substrate_batch_id = s.id`;
function listSubstrateBatches(db, opts) {
const o = opts || {};
const where = o.openOnly ? " WHERE s.status='open' AND s.remaining_kg > 0.0001" : '';
return db
.prepare(SUBSTRATE_SELECT + where + ' ORDER BY s.created DESC')
.all()
.map(_mapSubstrateRow);
}
// What a mix still holds is not a running total to be nudged up and down — it
// is derivable from the Chargen made out of it, and deriving it is what makes
// the two directions agree. The old add-and-cap arithmetic got both edges
// wrong: an overdrawn Charge handed back more than the mix had ever held, and
// a returned Charge silently reopened a mix that had been written off.
//
// `excludeBatchId` exists for the delete path, which has to recompute while the
// row it is removing is still there.
//
// Caller holds the transaction.
function _recalcSubstrateRemaining(db, subId, excludeBatchId) {
const row = db.prepare('SELECT * FROM substrate_batches WHERE sub_id=?').get(subId);
if (!row) return null;
const taken = db
.prepare(
'SELECT COALESCE(SUM(substrate_kg), 0) AS kg FROM batches WHERE substrate_batch_id = ? AND batch_id IS NOT ?'
)
.get(row.id, excludeBatchId || null).kg;
const remaining = Math.max(0, row.target_kg - taken);
// A mix declared unusable stays unusable. Handing a Charge back to it must
// not quietly put contaminated substrate back on the shelf.
const off = row.status === 'written_off';
const status = off ? 'written_off' : remaining > 0.0001 ? 'open' : 'used';
db.prepare('UPDATE substrate_batches SET remaining_kg=?, status=? WHERE sub_id=?').run(
off ? 0 : remaining,
status,
subId
);
return { row, taken, remaining, over: taken > row.target_kg + 1e-9 };
}
function deleteSubstrateBatch(db, subId, userId) {
db.exec('BEGIN');
try {
const row = db.prepare('SELECT * FROM substrate_batches WHERE sub_id=?').get(subId);
if (!row) {
db.exec('ROLLBACK');
return false;
}
const users = db.prepare('SELECT COUNT(*) c FROM batches WHERE substrate_batch_id=?').get(row.id).c;
if (users > 0) {
throw new Error(
'Diese Substrat-Charge wird von ' + users + ' Charge(n) verwendet und kann nicht geloescht werden'
);
}
// Credit back exactly what the ledger says this mix took — not a recomputed
// figure. applyInventoryDeltaNoTxn clamps a deduction to available stock, so
// a mix made while short took less than it asked for, and reversing the ask
// would invent the difference.
const taken = db
.prepare("SELECT mat, -SUM(delta_kg) AS kg FROM inventory_log WHERE ref=? AND type='mix' GROUP BY mat")
.all(subId);
for (const t of taken) {
if (t.kg > 0) applyInventoryDeltaNoTxn(db, t.mat, t.kg, 'mix-delete', subId, userId || null);
}
db.prepare('DELETE FROM substrate_batches WHERE sub_id=?').run(subId);
incrementDataVersion(db);
db.exec('COMMIT');
return true;
} catch (e) {
db.exec('ROLLBACK');
throw e;
}
}
// Create a Charge of bags out of an existing mix. Nothing comes off the shelf
// here. The substrate was paid for when it was mixed, and the grain inside the
// spawn when the grain spawn was set up (createGrainBatch books it, dry). This
// used to book the spawn a second time — rec_spawn_pct of the wet block weight
// against the same raw-grain stock — so every kilo of grain left the shelf twice
// instead of once, when the grain spawn was made.
// spawnKg is still worked out and returned: how much spawn these bags took.
function createBagBatchFromSubstrate(db, b, userId) {
const sub = db.prepare('SELECT * FROM substrate_batches WHERE sub_id=?').get(b.subId);
if (!sub) throw new Error('Substrat-Charge nicht gefunden: ' + b.subId);
const ms = db.prepare('SELECT * FROM mushroom_strains WHERE id=?').get(b.strainId);
if (!ms) throw new Error('Pilzsorte nicht gefunden');
const qty = Number(b.qty);
if (!Number.isFinite(qty) || qty < 1) throw new Error('qty must be >= 1');
const bagKg = Number.isFinite(+b.bagKg) && +b.bagKg > 0 ? +b.bagKg : ms.rec_bag_kg || 5;
const drawKg = qty * bagKg;
const spawnKg = drawKg * ((ms.rec_spawn_pct || 0) / 100);
const days = Number.isFinite(+b.days) && +b.days >= 1 ? +b.days : ms.rec_inc_days || 14;
const created = b.created || new Date().toISOString();
const due = b.due || new Date(new Date(created).getTime() + days * 86400000).toISOString();
// Bestand aus der Inventur (noBooking): die Charge stand schon, bevor sie in
// der App war. Sie bleibt mit dem Ansatz verbunden — seine Mischung, sein
// Maismehl —, zieht aber 0 kg daraus. Die Restmenge, die aus den gezogenen
// Kilogramm abgeleitet wird (_recalcSubstrateRemaining), bleibt damit, wie sie
// war.
const bucht = b.noBooking !== true;
const deltas = [];
const res = insertBatch(
db,
{
batchId: b.batchId,
strainId: b.strainId,
strain: b.strain || 'XXX',
qty,
days,
bagKg,
batchType: 'block',
sourceId: b.sourceId || null,
notes: b.notes || '',
strainText: b.strainText || '',
ph: b.ph,
created,
due,
// The composition is the mix's, not the species' — the bags are made of
// what was actually mixed, even if the Sorte's recipe has moved on since.
substrate: {
hardwood: sub.hardwood_pct,
wheatbran: sub.wheatbran_pct,
corn: sub.corn_pct,
rh: sub.rh_pct,
gypsum: sub.gypsum_pct > 0
},
bags: b.bags && b.bags.length ? b.bags : _defaultBagIds(b.batchId, qty),
// insertBatch performs the draw inside its own transaction. Doing it here
// afterwards would leave a window where the bags exist and the mix still
// says it is full — and the two disagreeing is the exact bookkeeping error
// this whole change is meant to remove.
substrateBatch: { subId: b.subId, id: sub.id, drawKg: bucht ? drawKg : 0 }
},
deltas,
userId
);
const after = db.prepare('SELECT remaining_kg FROM substrate_batches WHERE sub_id=?').get(b.subId);
return {
...res,
drawKg: bucht ? drawKg : 0,
spawnKg: bucht ? spawnKg : 0,
// The due date is worked out here from the Sorte's incubation days, so the
// caller has no other way to learn it — and the calendar push drops any
// batch that arrives without one.
created,
due,
remainingKg: after ? after.remaining_kg : 0,
over: bucht && drawKg > sub.remaining_kg + 1e-9
};
}
function _defaultBagIds(batchId, n) {
const width = String(n).length;
const out = [];
for (let i = 1; i <= n; i++) out.push(batchId + '-' + String(i).padStart(width, '0'));
return out;
}
function insertBatch(db, b, deltas, userId) {
if (!Number.isFinite(b.qty) || b.qty < 1) throw new Error('qty must be >= 1');
if (!Number.isFinite(b.days) || b.days < 1) throw new Error('days must be >= 1');
// Resolve strainId → species + strain text
const strainId = b.strainId || null;
let species = b.species;
let strain = b.strain || null;
if (strainId) {
const ms = db.prepare('SELECT * FROM mushroom_strains WHERE id=?').get(strainId);
if (!ms) throw new Error('Pilzsorte nicht gefunden');
species = ms.name + ' (' + ms.kuerzel + ')';
if (!strain) strain = 'XXX';
}
// I-19: defensive substrate-composition check. Block batches must have
// hardwood + wheatbran summing to 100% (within rounding). The client warns
// before submit, but the API/MCP path is bypassable, so guard here too.
// Skip when batchType is not 'block' (grain/liquid don't use this split) or
// when both percentages are zero (caller opted out of detailed tracking).
const batchType = b.batchType || 'block';
if (batchType === 'block') {
const sub0 = b.substrate || {};
const hw0 = sub0.hardwood || 0;
const wb0 = sub0.wheatbran || 0;
// v67: corn meal can be part of the dry blend on a recipe that uses it,
// so it counts towards the 100% exactly as pellets and bran do.
const cm0 = sub0.corn || 0;
if ((hw0 || wb0 || cm0) && Math.abs(hw0 + wb0 + cm0 - 100) > 0.01) {
throw new Error('Substrate composition must total 100% (got ' + (hw0 + wb0 + cm0).toFixed(1) + '%)');
}
}
// Vor BEGIN geprüft: ein vertippter pH soll nichts anlegen, nicht halb.
const ph = _phOf(b.ph);
db.exec('BEGIN');
try {
const sub = b.substrate || {};
// grain_rh applies to any batch with a grain portion (pure grain batches and
// all-in-one block batches alike); pure block batches simply pass nothing.
const grainRh = Number.isFinite(b.grainRh) ? b.grainRh : 0;
db.prepare(
`INSERT INTO batches(batch_id,species,strain,strain_id,qty,days,sub_hardwood,sub_wheatbran,sub_rh,sub_gypsum,bag_kg,batch_type,source_id,notes,strain_text,created,due,grain_rh,sub_coir,grain_kg) VALUES(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)`
).run(
b.batchId,
species,
strain,
strainId,
b.qty,
b.days,
sub.hardwood || 0,
sub.wheatbran || 0,
sub.rh || 0,
sub.gypsum ? 1 : 0,
b.bagKg || 3,
b.batchType || 'block',
b.sourceId || null,
b.notes || '',
b.strainText || '',
b.created,
b.due,
grainRh,
sub.coir || 0,
b.grainKg || 0
);
if (ph != null) db.prepare('UPDATE batches SET ph=? WHERE batch_id=?').run(ph, b.batchId);
// v67: a Charge portioned out of a mix records which mix and how much,
// and takes those kilograms out of it here — inside this transaction, so
// bags and remaining substrate can never disagree.
if (b.substrateBatch) {
db.prepare(`UPDATE batches SET substrate_batch_id=?, substrate_kg=?, sub_corn=? WHERE batch_id=?`).run(
b.substrateBatch.id,
b.substrateBatch.drawKg,
(b.substrate && b.substrate.corn) || 0,
b.batchId
);
// The kilograms are already on the batch row, so the remainder is derived
// rather than passed — one authority for it instead of two.
_recalcSubstrateRemaining(db, b.substrateBatch.subId);
}
const ins = db.prepare('INSERT INTO bags(bag_id,batch_id,bag_kg) VALUES(?,?,?)');
for (const item of b.bags || []) {
if (typeof item === 'string') {
ins.run(item, b.batchId, b.bagKg || 3);
} else {
ins.run(item.id, b.batchId, item.bagKg || b.bagKg || 3);
}
}
const bagIds = (b.bags || []).map((x) => (typeof x === 'string' ? x : x.id));
// Assign numeric barcodes to all new bags
const bagBarcodes = assignBarcodes(db, 'bag', bagIds);
// Apply inventory deltas inside the same transaction so an under-stock
// failure or invalid material rolls the batch back too.
// I-22: forward `userId` so each row in `inventory_log` records the actor.
if (Array.isArray(deltas)) {
for (const d of deltas) {
applyInventoryDeltaNoTxn(db, d.mat, d.deltaKg, d.type || 'batch', d.ref || b.batchId, userId || null);
}
}
incrementDataVersion(db);
db.exec('COMMIT');
return { bagBarcodes };
} catch (e) {
db.exec('ROLLBACK');
throw e;
}
}
function updateBatchField(db, batchId, fields) {
// Note: qty is intentionally NOT in the allowed list. addBagsToBatch (post
// I-23) and deleteBatchById both compute inventory deltas from the bag rows
// they create/destroy and write the corresponding inventory_log entries; a
// bare qty update here would mutate the count without any of that ledger
// bookkeeping. Use addBagsToBatch to grow a batch.
//
// pH: nachgetragen oder korrigiert an der Charge. Vor BEGIN geprüft, damit
// ein vertippter Wert nichts halb ändert; leer setzt auf "nicht gemessen".
const hatPh = fields && 'ph' in fields;
const ph = hatPh ? _phOf(fields.ph) : null;
db.exec('BEGIN');
try {
if (hatPh) db.prepare('UPDATE batches SET ph=? WHERE batch_id=?').run(ph, batchId);
// Handle strainId update: resolve species+strain from mushroom_strains
if (fields.strainId != null) {
const ms = db.prepare('SELECT * FROM mushroom_strains WHERE id=?').get(fields.strainId);
if (!ms) throw new Error('Pilzsorte nicht gefunden');
db.prepare('UPDATE batches SET strain_id=?,species=? WHERE batch_id=?').run(
fields.strainId,
ms.name + ' (' + ms.kuerzel + ')',
batchId
);
}
const allowed = ['notes', 'species', 'strain', 'days', 'due'];
const cols = Object.keys(fields).filter((k) => allowed.includes(k));
if (cols.length) {
const sets = cols.map((c) => `${c}=?`).join(',');
db.prepare(`UPDATE batches SET ${sets} WHERE batch_id=?`).run(...cols.map((c) => fields[c]), batchId);
}
incrementDataVersion(db);
db.exec('COMMIT');
} catch (e) {
db.exec('ROLLBACK');
throw e;
}
}
// Escape SQL LIKE wildcards (% _ and the escape char itself) so a literal
// value can be matched with `LIKE ? ESCAPE '\'`. Batch ids may contain '_',
// which LIKE would otherwise treat as "any single char".
function escapeLikePattern(s) {
return String(s).replace(/[\\%_]/g, '\\$&');
}
function renameBatch(db, oldId, newId, wer) {
db.exec('BEGIN');
// Defer FK checks to COMMIT so we can update parent (batches) and child (bags)
// without hitting a constraint violation mid-transaction. The schema uses
// ON DELETE CASCADE but not ON UPDATE CASCADE, so without deferral SQLite
// rejects any update to batches.batch_id while bags still reference the old value.
db.exec('PRAGMA defer_foreign_keys = ON');
try {
const existing = db.prepare('SELECT batch_id FROM batches WHERE batch_id=?').get(oldId);
if (!existing) throw new Error('Batch not found: ' + oldId);
const conflict = db.prepare('SELECT batch_id FROM batches WHERE batch_id=?').get(newId);
if (conflict) throw new Error('A batch with ID "' + newId + '" already exists');
db.prepare('UPDATE bags SET bag_id=REPLACE(bag_id,?,?) WHERE batch_id=?').run(oldId, newId, oldId);
db.prepare('UPDATE scan_log SET bag=REPLACE(bag,?,?),batch=? WHERE batch=?').run(oldId, newId, newId, oldId);
// P-06: bag IDs were renamed in scan_log — invalidate the cache.
invalidateBagZoneCache(db);
db.prepare('UPDATE harvests SET bag=REPLACE(bag,?,?),batch=? WHERE batch=?').run(oldId, newId, newId, oldId);
db.prepare('UPDATE inventory_log SET ref=? WHERE ref=?').run(newId, oldId);
// I-06: contamination reports also reference batch_id and bag_id; without these
// updates the reports would orphan and the contamination history for the batch would
// disappear from the UI after a rename.
db.prepare('UPDATE contamination_reports SET batch_id=? WHERE batch_id=?').run(newId, oldId);
// Rewrite the batch prefix of bag_id for THIS batch's bags only. Match
// `oldId-%` (with wildcards in oldId escaped) so a rename of "B-1" cannot
// touch "B-10"'s reports, and rebuild via substr so a recurring id
// fragment in the suffix isn't double-replaced.
db.prepare("UPDATE contamination_reports SET bag_id = ? || substr(bag_id, ?) WHERE bag_id LIKE ? ESCAPE '\\'").run(
newId,
oldId.length + 1,
escapeLikePattern(oldId) + '-%'
);
db.prepare('UPDATE batches SET batch_id=? WHERE batch_id=?').run(newId, oldId);
db.prepare('UPDATE bags SET batch_id=? WHERE batch_id=?').run(newId, oldId);
// Reservierungen aus Bestellungen zeigen auch auf die Charge. Ohne diese
// Zeile scheiterte das COMMIT am Fremdschluessel, sobald eine bestand.
db.prepare('UPDATE order_allocations SET batch_id=? WHERE batch_id=?').run(newId, oldId);
_verknuepfungUmbenannt(db, 'batchId', oldId, newId, wer);
// Update barcode registry: rename entity_id for bags that were renamed
db.prepare(
"UPDATE barcodes SET entity_id = ? || substr(entity_id, ?) WHERE entity_type='bag' AND entity_id LIKE ? ESCAPE '\\'"
).run(newId, oldId.length + 1, escapeLikePattern(oldId) + '-%');
incrementDataVersion(db);
db.exec('COMMIT');
} catch (e) {
db.exec('ROLLBACK');
throw e;
}
}
function renameCulture(db, oldId, newId, wer) {
db.exec('BEGIN');
db.exec('PRAGMA defer_foreign_keys = ON');
try {
const existing = db.prepare('SELECT id FROM cultures WHERE id=?').get(oldId);
if (!existing) throw new Error('Culture not found: ' + oldId);
const conflict = db.prepare('SELECT id FROM cultures WHERE id=?').get(newId);
if (conflict) throw new Error('A culture with ID "' + newId + '" already exists');
// Update parent_id references in child cultures
db.prepare('UPDATE cultures SET parent_id=? WHERE parent_id=?').run(newId, oldId);
// Update source_id references in batches
db.prepare('UPDATE batches SET source_id=? WHERE source_id=?').run(newId, oldId);
// Rename the culture itself
db.prepare('UPDATE cultures SET id=? WHERE id=?').run(newId, oldId);
_verknuepfungUmbenannt(db, 'cultureId', oldId, newId, wer);
// Update barcode registry
db.prepare("UPDATE barcodes SET entity_id=? WHERE entity_type='culture' AND entity_id=?").run(newId, oldId);
incrementDataVersion(db);
db.exec('COMMIT');
} catch (e) {
db.exec('ROLLBACK');
throw e;
}
}
function addBagsToBatch(db, batchId, newBags, newQty, bagKg, userId) {
db.exec('BEGIN');
try {
// I-23: read the full batch row so we can reuse its composition for
// proportional inventory deduction. addBagsToBatch previously bumped
// qty + bag rows without touching inventory, so growing 10→12 bags
// silently consumed real substrate that never hit the ledger.
const batch = db.prepare('SELECT * FROM batches WHERE batch_id=?').get(batchId);
if (!batch) throw new Error('batch not found: ' + batchId);
// Resolve bag weight: explicit param > batch's existing weight
let weight = bagKg;
if (weight == null) weight = batch.bag_kg || 3;
const ins = db.prepare('INSERT OR IGNORE INTO bags(bag_id,batch_id,bag_kg) VALUES(?,?,?)');
for (const id of newBags) ins.run(id, batchId, weight);
if (newQty != null) db.prepare('UPDATE batches SET qty=? WHERE batch_id=?').run(newQty, batchId);
// I-23: compute and apply inventory deltas for the *added* bags only.
// Reuses computeBatchMaterialDeltasForKg so the deduction math matches
// what insertBatch would have charged for those bags originally. Negative
// deltas are clamped against current stock by applyInventoryDeltaNoTxn —
// the same lenient behaviour insertBatch already has when stock is short.
const addedWetKg = weight * newBags.length;
if (batch.substrate_batch_id) {
// The substrate for these bags was bought when the mix was made. What
// they cost now is more of that mix — and nothing off the shelf: the
// grain in their spawn was booked when the grain spawn was set up
// (see createBagBatchFromSubstrate).
db.prepare('UPDATE batches SET substrate_kg = substrate_kg + ? WHERE batch_id=?').run(addedWetKg, batchId);
const sub = db.prepare('SELECT sub_id FROM substrate_batches WHERE id=?').get(batch.substrate_batch_id);
if (sub) _recalcSubstrateRemaining(db, sub.sub_id);
} else {
const deltas = computeBatchMaterialDeltasForKg(batch, addedWetKg);
for (const d of deltas) {
applyInventoryDeltaNoTxn(db, d.mat, -d.deltaKg, 'batch-grow', batchId, userId || null);
}
}
// Assign numeric barcodes to new bags
const bagBarcodes = assignBarcodes(db, 'bag', newBags);
incrementDataVersion(db);
db.exec('COMMIT');
return { bagBarcodes };
} catch (e) {
db.exec('ROLLBACK');
throw e;
}
}
function deleteBatchById(db, batchId, userId) {
db.exec('BEGIN');
try {
// Read batch before deleting so we can reverse inventory deductions
const row = db
.prepare(
'SELECT qty, bag_kg, batch_type, sub_hardwood, sub_wheatbran, sub_rh, sub_gypsum, grain_rh, sub_coir, grain_kg, substrate_batch_id, substrate_kg FROM batches WHERE batch_id=?'
)
.get(batchId);
if (row) {
row.batch_id = batchId;
// A Charge drawn from a mix never took raw materials — it took kilograms
// of finished substrate, and those go back to the mix rather than to the
// shelf. Charges made before 28.09.2026 also had their spawn booked
// against raw grain (the second booking createBagBatchFromSubstrate no
// longer makes); deleting one gives that back, from its own 'spawn' rows
// in the ledger. A newer Charge has none, and gets nothing.
if (row.substrate_batch_id && row.substrate_kg > 0) {
const sub = db.prepare('SELECT sub_id FROM substrate_batches WHERE id=?').get(row.substrate_batch_id);
if (sub) _recalcSubstrateRemaining(db, sub.sub_id, batchId);
}
const deltas = row.substrate_batch_id
? db
.prepare(
"SELECT mat, -SUM(delta_kg) AS deltaKg FROM inventory_log WHERE ref = ? AND type = 'spawn' GROUP BY mat"
)
.all(batchId)
: computeBatchMaterialDeltas(db, row);
// Only credit back what this batch actually took out. The MCP create_batch
// tool deliberately passes deltas = null, so a batch created that way never
// deducted anything — reversing its computed composition on delete invented
// stock out of nothing. The HTTP path is asymmetric too:
// applyInventoryDeltaNoTxn clamps a deduction to available stock via
// Math.max(deltaKg, -cur), so creating a batch while short and then deleting
// it left a permanent surplus. Both drift the running total upward and never
// self-correct, so cap the credit at the recorded deduction for this batch.
//
// Every deduction, which means 'batch-grow' as well as 'batch'.
// addBagsToBatch logs its substrate under 'batch-grow', while
// computeBatchMaterialDeltas above reads the bags table and therefore
// already counts the added bags. Counting the enlargement on the way out
// but not on the way back in meant the extra substrate was deducted and
// never returned: create a batch, add bags, delete it, and the difference
// stayed missing from the shelf for good. That is a wrong number in the
// one place the lab orders stock from, and it compounds per batch.
//
// This does not over-credit. The credit stays a Math.min against what was
// actually taken, so it is capped against a larger, truer figure rather
// than being turned into a sum — the two cases the cap exists for both
// still hold: an MCP batch created with deltas = null still credits back
// nothing, and a batch created while stock was short still nets to zero.
const takenByMat = {};
for (const r of db
.prepare(
"SELECT mat, SUM(delta_kg) AS total FROM inventory_log WHERE ref = ? AND type IN ('batch','batch-grow','spawn') GROUP BY mat"
)
.all(batchId))
takenByMat[r.mat] = Math.abs(r.total || 0);
for (const d of deltas) {
const col = 'stock_' + d.mat;
const credit = Math.min(d.deltaKg, takenByMat[d.mat] || 0);
if (!(credit > 0)) continue;
d.deltaKg = credit;
db.prepare(`UPDATE inventory SET ${col} = ${col} + ? WHERE id=1`).run(d.deltaKg);
const cur = db.prepare(`SELECT ${col} as val FROM inventory WHERE id=1`).get();
// I-22: include user_id so the inventory ledger records who triggered the credit-back.
db.prepare('INSERT INTO inventory_log(time,mat,delta_kg,running,type,ref,user_id) VALUES(?,?,?,?,?,?,?)').run(
new Date().toISOString(),
d.mat,
d.deltaKg,
cur.val,
'batch-delete',
batchId,
userId || null
);
}
}
db.prepare('DELETE FROM harvests WHERE batch=?').run(batchId);
db.prepare('DELETE FROM scan_log WHERE batch=?').run(batchId);
// P-06: scan_log rows for this batch are gone — invalidate the cache.
invalidateBagZoneCache(db);
// I-07: keep contamination history (audit-relevant) by NULLing the FK
// instead of deleting the report rows. The reports list (listContaminationReports)
// already filters by batch_id only when set, so NULL rows remain visible in the
// unfiltered view.
db.prepare('UPDATE contamination_reports SET batch_id = NULL WHERE batch_id = ?').run(batchId);
// Only NULL bag_id for bags of THIS batch ("batchId-%", wildcards escaped) —
// a bare "batchId%" prefix would also clear e.g. "B-10"'s reports when
// deleting "B-1".
db.prepare("UPDATE contamination_reports SET bag_id = NULL WHERE bag_id LIKE ? ESCAPE '\\'").run(
escapeLikePattern(batchId) + '-%'
);
_verknuepfungGeht(db, 'batchId', [batchId], _werZuId(db, userId));
db.prepare('DELETE FROM batches WHERE batch_id=?').run(batchId);
incrementDataVersion(db);
db.exec('COMMIT');
} catch (e) {
db.exec('ROLLBACK');
throw e;
}
}
/** Compute material kg used by a batch row (positive values = what was consumed) */
function computeBatchMaterialDeltas(db, row) {
const deltas = [];
// Read actual per-bag weights from the bags table
const bagWeightRows = db.prepare('SELECT bag_kg FROM bags WHERE batch_id = ?').all(row.batch_id);
const fallbackKg = row.bag_kg || 3;
if (row.batch_type === 'grain') {
// grain_rh = % water added during hydration (e.g. 52 for wheat).
// Dry grain used = wet bag weight * (1 - rh/100). rh=0 preserves legacy behaviour
// for batches created before the hydration field existed.
const rh = row.grain_rh || 0;
let totalDryKg = 0;
if (bagWeightRows.length) {
for (const b of bagWeightRows) {
const kg = b.bag_kg != null ? b.bag_kg : fallbackKg;
totalDryKg += rh > 0 ? kg * (1 - rh / 100) : kg;
}
} else {
const dryKgPerBag = rh > 0 ? fallbackKg * (1 - rh / 100) : fallbackKg;
totalDryKg = row.qty * dryKgPerBag;
}
deltas.push({ mat: 'grain', deltaKg: totalDryKg });
} else {
const hw = row.sub_hardwood || 0;
const wb = row.sub_wheatbran || 0;
const coir = row.sub_coir || 0;
const rh = row.sub_rh || 0;
const gyp = row.sub_gypsum;
if (hw || wb || coir) {
let totalDryKg = 0;
if (bagWeightRows.length) {
for (const b of bagWeightRows) {
const kg = b.bag_kg != null ? b.bag_kg : fallbackKg;
totalDryKg += rh > 0 ? kg * (1 - rh / 100) : kg;
}
} else {
const dryKgPerBag = rh > 0 ? fallbackKg * (1 - rh / 100) : fallbackKg;
totalDryKg = row.qty * dryKgPerBag;
}
const hwUsed = totalDryKg * (hw / 100);
const wbUsed = totalDryKg * (wb / 100);
const coirUsed = totalDryKg * (coir / 100);
if (hwUsed > 0) deltas.push({ mat: 'hardwood', deltaKg: hwUsed });
if (wbUsed > 0) deltas.push({ mat: 'wheatbran', deltaKg: wbUsed });
if (coirUsed > 0) deltas.push({ mat: 'coir', deltaKg: coirUsed });
if (gyp) deltas.push({ mat: 'gypsum', deltaKg: totalDryKg * 0.01 });
}
// All-in-One raw-grain portion mixed into the block (grain_kg wet per bag).
const grainKg = row.grain_kg || 0;
if (grainKg > 0) {
const grh = row.grain_rh || 0;
const bagCount = bagWeightRows.length || row.qty;
const grainDryPerBag = grh > 0 ? grainKg * (1 - grh / 100) : grainKg;
const grainTotal = bagCount * grainDryPerBag;
if (grainTotal > 0) deltas.push({ mat: 'grain', deltaKg: grainTotal });
}
}
return deltas;
}
/**
* I-23: Compute material kg consumed by adding `addedWetKg` (sum of new bags'
* wet weights) to an existing batch. Reuses the batch's stored composition
* (hardwood/wheatbran %, rh %, gypsum flag, grain_rh) so the deduction matches
* what the original `insertBatch` deduction logic would have charged for those
* bags. Returns deltas as positive consumption values; caller flips the sign
* when applying to inventory. Returns [] when the batch has no composition
* (legacy or zero-percent batches).
*/
function computeBatchMaterialDeltasForKg(batch, addedWetKg) {
const deltas = [];
if (!batch || !(addedWetKg > 0)) return deltas;
if (batch.batch_type === 'grain') {
const rh = batch.grain_rh || 0;
const dryKg = rh > 0 ? addedWetKg * (1 - rh / 100) : addedWetKg;
if (dryKg > 0) deltas.push({ mat: 'grain', deltaKg: dryKg });
return deltas;
}
const hw = batch.sub_hardwood || 0;
const wb = batch.sub_wheatbran || 0;
const coir = batch.sub_coir || 0;
const grainKg = batch.grain_kg || 0;
if (!hw && !wb && !coir && !grainKg) return deltas;
const rh = batch.sub_rh || 0;
const dryKg = rh > 0 ? addedWetKg * (1 - rh / 100) : addedWetKg;
const hwUsed = dryKg * (hw / 100);
const wbUsed = dryKg * (wb / 100);
const coirUsed = dryKg * (coir / 100);
if (hwUsed > 0) deltas.push({ mat: 'hardwood', deltaKg: hwUsed });
if (wbUsed > 0) deltas.push({ mat: 'wheatbran', deltaKg: wbUsed });
if (coirUsed > 0) deltas.push({ mat: 'coir', deltaKg: coirUsed });
if (batch.sub_gypsum) deltas.push({ mat: 'gypsum', deltaKg: dryKg * 0.01 });
// All-in-One grain portion scales with the number of added bags
// (addedWetKg of substrate / substrate bag_kg).
if (grainKg > 0 && batch.bag_kg > 0) {
const grh = batch.grain_rh || 0;
const bags = addedWetKg / batch.bag_kg;
const grainDryPerBag = grh > 0 ? grainKg * (1 - grh / 100) : grainKg;
const grainTotal = bags * grainDryPerBag;
if (grainTotal > 0) deltas.push({ mat: 'grain', deltaKg: grainTotal });
}
return deltas;
}
// -- Scan Log --
// Append scan entries inside an existing transaction. Caller is responsible for BEGIN/COMMIT
// and for calling incrementDataVersion(). Returns the inserted row IDs (or the
// existing row id if a client_uuid collision triggered the ON CONFLICT branch).
//
// I-11: client_uuid is the offline-queue idempotency key. SQLite UPSERT
// (ON CONFLICT DO NOTHING) makes the INSERT a no-op when the same UUID is
// replayed; we then look up the original row id so callers (and the
// `_serverId` reconciliation on the client) still see a real id.
function appendScanEntriesNoTxn(db, entries, userId) {
// I-11: SQLite UPSERT against the partial unique index needs the index's
// exact WHERE clause in the conflict target ("partial index conflict
// resolution", https://www.sqlite.org/lang_upsert.html). Without the
// WHERE clause the planner can't match the partial index and raises
// "ON CONFLICT clause does not match any PRIMARY KEY or UNIQUE constraint".
const insIdempotent = db.prepare(
'INSERT INTO scan_log(time,action,batch,bag,"from","to",species,strain,user_id,reason,client_uuid) ' +
'VALUES(?,?,?,?,?,?,?,?,?,?,?) ' +
'ON CONFLICT(client_uuid) WHERE client_uuid IS NOT NULL DO NOTHING'
);
// Fallback for entries without a client_uuid. ON CONFLICT against a partial
// index whose WHERE rejects NULL never fires for NULL keys anyway, but
// keeping a separate plain INSERT keeps the call site explicit and avoids
// depending on that subtle planner detail.
const insPlain = db.prepare(
'INSERT INTO scan_log(time,action,batch,bag,"from","to",species,strain,user_id,reason,client_uuid) ' +
'VALUES(?,?,?,?,?,?,?,?,?,?,?)'
);
const lookupByUuid = db.prepare('SELECT id FROM scan_log WHERE client_uuid = ?');
const ids = [];
for (const e of entries) {
const stmt = e.client_uuid ? insIdempotent : insPlain;
const r = stmt.run(
e.time,
e.action,
e.batch || null,
e.bag || null,
e.from || null,
e.to || null,
e.species || null,
e.strain || null,
userId || null,
e.reason || null,
e.client_uuid || null
);
if (r.changes === 0 && e.client_uuid) {
// Replay: row already exists. Return the existing id so the client can
// still reconcile its in-memory entry with a server id.
const existing = lookupByUuid.get(e.client_uuid);
ids.push(existing ? existing.id : null);
} else {
ids.push(r.lastInsertRowid);
// P-06: keep the in-memory bag→zone cache in sync incrementally so
// snapshotDailyKPIs / getProductionPipeline don't have to re-scan
// scan_log on every call. A REPLAY (changes === 0) is a no-op for
// the cache because the original entry already updated it.
applyScanEntryToBagZoneCache(db, e);
}
}
return ids;
}
function appendScanEntries(db, entries, userId) {
db.exec('BEGIN');
try {
const ids = appendScanEntriesNoTxn(db, entries, userId);
incrementDataVersion(db);
db.exec('COMMIT');
return ids;
} catch (err) {
db.exec('ROLLBACK');
// appendScanEntriesNoTxn already mutated the in-memory bag-zone cache; the
// rollback undid the rows but not the cache, so force a rebuild on next read.
invalidateBagZoneCache(db);
throw err;
}
}
function deleteLastScanEntries(db, n) {
db.exec('BEGIN');
try {
db.prepare('DELETE FROM scan_log WHERE id IN (SELECT id FROM scan_log ORDER BY id DESC LIMIT ?)').run(n);
// P-06: rows removed — incremental update not possible without re-reading,
// so invalidate and let the next read rebuild from scratch.
invalidateBagZoneCache(db);
incrementDataVersion(db);
db.exec('COMMIT');
} catch (e) {
db.exec('ROLLBACK');
throw e;
}
}
// Clean-slate reset (admin "Neustart"): clears operational rows so the farm can
// start over with fresh bags, while KEEPING everything you would otherwise have
// to set up again — zones/racks, Sorten + recipes, users, suppliers, inventory
// and all settings. Irreversible; the HTTP layer takes a verified backup first
// and requires a typed confirmation.
//
// Categories are opt-in via opts so the operator chooses at the moment they run
// it. Returns per-table row counts for the confirmation message.
function resetOperationalData(db, opts, wer) {
opts = opts || {};
const counts = {};
// Table names below are internal literals, never user input.
const wipe = (table, where) => {
const exists = db.prepare("SELECT 1 FROM sqlite_master WHERE type='table' AND name=?").get(table);
if (!exists) return;
const info = db.prepare('DELETE FROM ' + table + (where ? ' WHERE ' + where : '')).run();
if (info.changes) counts[table] = info.changes;
};
db.exec('BEGIN');
try {
if (opts.growing) {
// The reset keeps experiments, but their runs lose the link to every
// batch and culture it clears. Record that in the lab history first.
_verknuepfungGeht(db, 'batchId', null, wer);
_verknuepfungGeht(db, 'cultureId', null, wer);
// Children before parents. bags also cascades from batches, but clearing
// it explicitly keeps the reported counts honest.
wipe('contamination_photos');
wipe('contamination_reports');
wipe('harvests');
// A release says "this much of the harvest is set aside for sale". With
// the harvests gone it would still say it, and keep offering produce that
// no longer exists in any record.
wipe('harvest_release');
wipe('scan_log');
wipe('bags');
wipe('batches');
// The mixes go with the bags: what is left of a mix is a physical thing
// on a shelf, and a clean slate means that shelf was emptied too.
wipe('substrate_batches');
wipe('cultures');
// Barcodes pointing at rows we just cleared; zone and rack barcodes stay valid.
wipe('barcodes', "entity_type IN ('bag','culture','batch')");
}
if (opts.orders) {
wipe('order_allocations');
wipe('order_sync_log');
wipe('order_items');
wipe('shipments');
wipe('orders');
wipe('customer_identities');
wipe('customers');
// A pickup names the order it is for. With the orders gone it is a time
// and a place for something nobody can look up. Unconfirmed ones the
// receiver still holds open come back on the next reply anyway.
wipe('pickups');
wipe('pickup_cancellations');
}
if (opts.planning) {
wipe('calendar_event_assignees');
wipe('calendar_events');
wipe('manual_tasks');
wipe('notifications');
wipe('maintenance_log');
wipe('kpi_snapshots');
}
if (opts.stock) {
// Material quantities and their ledger. Thresholds and the average
// composition are configuration the operator tuned — not operational data
// — so they survive; only the amounts and their history reset, ready for a
// fresh physical count. The ledger goes with them: its rows reference
// batch ids that a growing reset has just cleared, and a running total
// carried over from deleted batches is exactly the drift this is meant to
// clear out.
wipe('inventory_log');
const info = db
.prepare(
'UPDATE inventory SET stock_hardwood=0, stock_wheatbran=0, stock_gypsum=0, stock_grain=0, stock_coir=0 WHERE id=1'
)
.run();
if (info.changes) counts.inventory = info.changes;
}
invalidateBagZoneCache(db);
incrementDataVersion(db);
db.exec('COMMIT');
} catch (e) {
db.exec('ROLLBACK');
invalidateBagZoneCache(db);
throw e;
}
return counts;
}
function getScanEntryById(db, id) {
return db.prepare('SELECT id, user_id, action, time FROM scan_log WHERE id = ?').get(id);
}
function deleteScanEntryById(db, id) {
const info = db.prepare('DELETE FROM scan_log WHERE id = ?').run(id);
if (info.changes > 0) {
invalidateBagZoneCache(db); // P-06: row removed — rebuild cache lazily
incrementDataVersion(db);
}
return info.changes > 0;
}
function clearScanLog(db) {
db.exec('BEGIN');
try {
db.prepare('DELETE FROM scan_log').run();
invalidateBagZoneCache(db); // P-06
incrementDataVersion(db);
db.exec('COMMIT');
} catch (e) {
db.exec('ROLLBACK');
throw e;
}
}
// -- Harvests --
function insertHarvest(db, h) {
if (!Number.isFinite(h.grams) || h.grams < 0) throw new Error('grams must be >= 0');
const r = db
.prepare('INSERT INTO harvests(time,batch,bag,species,strain,grams,flush,quality,notes) VALUES(?,?,?,?,?,?,?,?,?)')
.run(
h.time,
h.batch || null,
h.bag || null,
h.species || null,
h.strain || null,
h.grams,
h.flush || 1,
h.quality || null,
h.notes || null
);
incrementDataVersion(db);
return r.lastInsertRowid;
}
// -- Cultures --
// I-20: allowed parent types per child type. Enforced in insertCultures and
// updateCulture. Block batches are not cultures, so they're not in this map.
// G2G and GS both denote grain spawn cultures and share the same parent rules.
// Existing rows in the DB may already violate these rules — we don't run any
// retroactive cleanup, just enforce the constraint going forward.
const VALID_CULTURE_PARENT_TYPES = {
// A slant is mostly streaked from a good plate, sometimes from a liquid
// culture or another slant. Bought or from a print it has no parent, which
// is why a parent stays optional for every type.
MC: ['MC', 'PD', 'LC', 'SY'],
// A plate streaked from a liquid culture is how an LC is checked and cleaned up.
// A syringe drawn from an LC inoculates plates, new LCs and grain in one go.
PD: ['MC', 'PD', 'LC', 'SY'],
LC: ['MC', 'PD', 'LC', 'SY'],
G2G: ['MC', 'PD', 'LC', 'SY', 'G2G', 'GS'],
GS: ['MC', 'PD', 'LC', 'SY', 'G2G', 'GS'],
// A syringe is drawn from something liquid or from a plate — never from grain,
// which cannot be drawn into a syringe at all.
SY: ['MC', 'PD', 'LC'],
// Impfdübel: a bag of wooden dowels inoculated from a liquid culture, a
// syringe or a plate. Grain spawn inoculates them too, but a grain bag is not
// a culture; it is written off in the scan log as "spawn:<Dübel id>".
DU: ['MC', 'PD', 'LC', 'SY']
};
// Die Groessen, in denen Spritzen abgefuellt werden. Eine Spritze ohne Angabe
// ist eine 10er: so war jede, bevor es die Wahl gab (Migration 102).
const SYRINGE_SIZES = [10, 20, 50];
// Wie viele Duebel in einem Beutel Impfduebel sind, fuers Etikett ("ca. 100").
// Nur bei DU; eine leere oder unsinnige Angabe ist keine.
function duebelStueck(type, n) {
if (type !== 'DU') return null;
const v = Math.round(Number(n));
return Number.isFinite(v) && v > 0 && v <= 100000 ? v : null;
}
function syringeMl(type, ml) {
if (type !== 'SY') return null;
const n = Number(ml);
return SYRINGE_SIZES.includes(n) ? n : 10;
}
function validateCultureParent(db, type, parentId) {
if (!parentId) return null;
const parent = db.prepare('SELECT type FROM cultures WHERE id = ?').get(parentId);
if (!parent) return 'parent culture not found: ' + parentId;
const allowed = VALID_CULTURE_PARENT_TYPES[type];
if (!allowed) return 'unknown culture type: ' + type;
if (allowed.length === 0) return type + ' cultures cannot have a parent';
if (!allowed.includes(parent.type)) {
return type + ' parent must be one of [' + allowed.join(', ') + '], got ' + parent.type;
}
return null;
}
function insertCultures(db, cultures) {
if (!cultures.length) return;
const ins = db.prepare(
`INSERT INTO cultures(id,type,species,strain,strain_id,parent_id,source,status,notes,created,strain_text,ml,stueck) VALUES(?,?,?,?,?,?,?,?,?,?,?,?,?)
ON CONFLICT(id) DO UPDATE SET type=excluded.type, species=excluded.species, strain=excluded.strain, strain_id=excluded.strain_id,
parent_id=excluded.parent_id, source=excluded.source, status=excluded.status, notes=excluded.notes, created=excluded.created, strain_text=excluded.strain_text, ml=excluded.ml, stueck=excluded.stueck`
);
// Wrap the whole batch so a validation failure on culture N doesn't leave
// cultures 1..N-1 committed without barcodes or a version bump (the callers
// — POST /api/cultures and the MCP create_culture tool — don't wrap it).
db.exec('BEGIN');
try {
for (const c of cultures) {
// Reject self-cycles up front so the lineage walker never has to discover them.
if (c.parentId && c.parentId === c.id) {
throw new Error('Culture parent_id must not equal its own id (self-cycle rejected)');
}
// I-20: validate parent type against the child type (defence-in-depth —
// the UI dropdown already filters by allowed types, but the API and MCP
// tools accept arbitrary parentId so the constraint is enforceable here).
const err = validateCultureParent(db, c.type, c.parentId || null);
if (err) throw new Error('Invalid culture parent: ' + err);
// Eine Groesse, die es nicht gibt, ist ein Fehler beim Anlegen; keine
// Angabe ist die 10er, wie jede Spritze vor der Wahl.
if (c.type === 'SY' && c.ml != null && !SYRINGE_SIZES.includes(Number(c.ml))) {
throw new Error('Spritzen gibt es in ' + SYRINGE_SIZES.join(', ') + ' ml, nicht in ' + c.ml);
}
// Resolve strainId if provided
const strainId = c.strainId || null;
let species = c.species || null;
let strain = c.strain || null;
if (strainId) {
const ms = db.prepare('SELECT * FROM mushroom_strains WHERE id=?').get(strainId);
if (ms) {
species = ms.name;
strain = ms.kuerzel;
}
}
ins.run(
c.id,
c.type,
species,
strain,
strainId,
c.parentId || null,
c.source || null,
c.status || 'active',
c.notes || '',
c.created,
c.strainText || '',
syringeMl(c.type, c.ml),
duebelStueck(c.type, c.stueck)
);
}
// Assign numeric barcodes to all new cultures
const cultureBarcodes = assignBarcodes(
db,
'culture',
cultures.map((c) => c.id)
);
incrementDataVersion(db);
db.exec('COMMIT');
return { cultureBarcodes };
} catch (e) {
db.exec('ROLLBACK');
throw e;
}
}
function updateCulture(db, id, fields) {
// Defence-in-depth: even though parent_id isn't in the allowed list today,
// reject self-cycle attempts up front.
if ((fields.parentId != null && fields.parentId === id) || (fields.parent_id != null && fields.parent_id === id)) {
throw new Error('Culture parent_id must not equal its own id (self-cycle rejected)');
}
// I-20: if a future update path ever lets parent_id through the allowed
// list, validate the parent type against the child's existing type. This
// covers both the camelCase and snake_case spellings.
const incomingParent = fields.parentId != null ? fields.parentId : fields.parent_id != null ? fields.parent_id : null;
if (incomingParent) {
const cur = db.prepare('SELECT type FROM cultures WHERE id = ?').get(id);
if (cur) {
const err = validateCultureParent(db, cur.type, incomingParent);
if (err) throw new Error('Invalid culture parent: ' + err);
}
}
// Handle strainId update
if (fields.strainId != null) {
const ms = db.prepare('SELECT * FROM mushroom_strains WHERE id=?').get(fields.strainId);
if (!ms) throw new Error('Pilzsorte nicht gefunden');
db.prepare('UPDATE cultures SET strain_id=?,species=?,strain=? WHERE id=?').run(
fields.strainId,
ms.name,
ms.kuerzel,
id
);
}
// Eine Spritze mit der falschen Groesse angelegt: die Groesse ist korrigierbar,
// aber nur an einer Spritze und nur auf eine, die es gibt.
if (fields.ml != null) {
const cur = db.prepare('SELECT type FROM cultures WHERE id = ?').get(id);
if (!cur || cur.type !== 'SY') throw new Error('Eine Größe hat nur eine Spritze');
if (!SYRINGE_SIZES.includes(Number(fields.ml))) {
throw new Error('Spritzen gibt es in ' + SYRINGE_SIZES.join(', ') + ' ml, nicht in ' + fields.ml);
}
fields.ml = Number(fields.ml);
}
const allowed = ['status', 'notes', 'species', 'strain', 'source', 'strain_text', 'ml'];
// Map camelCase to snake_case for DB
if (fields.strainText != null && fields.strain_text == null) {
fields.strain_text = fields.strainText;
}
const cols = Object.keys(fields).filter((k) => allowed.includes(k));
if (!cols.length && fields.strainId == null) return;
if (cols.length) {
const sets = cols.map((c) => `${c}=?`).join(',');
db.prepare(`UPDATE cultures SET ${sets} WHERE id=?`).run(...cols.map((c) => fields[c]), id);
}
incrementDataVersion(db);
}
/** Get a single culture by ID with strain info and lineage (parent + children) */
function getCultureById(db, id) {
const r = db.prepare('SELECT * FROM cultures WHERE id=?').get(id);
if (!r) return null;
let strainName = null,
strainKuerzel = null;
if (r.strain_id) {
const ms = db.prepare('SELECT name, kuerzel FROM mushroom_strains WHERE id=?').get(r.strain_id);
if (ms) {
strainName = ms.name;
strainKuerzel = ms.kuerzel;
}
}
const parent = r.parent_id
? db.prepare('SELECT id, type, species, strain, status FROM cultures WHERE id=?').get(r.parent_id)
: null;
const children = db
.prepare('SELECT id, type, species, strain, status, created FROM cultures WHERE parent_id=? ORDER BY created')
.all(id);
const batches = db
.prepare(
'SELECT batch_id, species, strain, batch_type, created, due FROM batches WHERE source_id=? ORDER BY created'
)
.all(id)
.map((b) => ({
batchId: b.batch_id,
species: b.species,
strain: b.strain,
batchType: b.batch_type,
created: b.created,
due: b.due
}));
return {
id: r.id,
type: r.type,
species: r.species,
strain: r.strain,
strainId: r.strain_id || null,
strainName,
strainKuerzel,
strainText: r.strain_text || '',
ml: r.ml || null,
stueck: r.stueck || null,
parentId: r.parent_id,
parent: parent || null,
source: r.source,
status: r.status,
notes: r.notes,
created: r.created,
children,
batches
};
}
// Ist aus dieser Kultur schon etwas angesetzt: eine Kultur oder eine Charge?
function cultureHasOffspring(db, id) {
return !!(
db.prepare('SELECT 1 FROM cultures WHERE parent_id = ? LIMIT 1').get(id) ||
db.prepare('SELECT 1 FROM batches WHERE source_id = ? LIMIT 1').get(id)
);
}
function deleteCulture(db, id, wer) {
return withSavepoint(db, 'mt_kultur_weg', () => {
_verknuepfungGeht(db, 'cultureId', [id], wer);
const info = db.prepare('DELETE FROM cultures WHERE id=?').run(id);
if (info.changes > 0) incrementDataVersion(db);
return info.changes > 0;
});
}
// -- Tasks --
// S-23: what a CalDAV uid is allowed to look like — the one definition.
//
// It ends up as a file name (`<uid>.ics`) inside the calendar directory, so the
// separators and NUL have to be out; with those gone a uid cannot address a
// second path component and traversal is impossible on POSIX and Windows alike.
// This is the charset the sync-back paths in server.js have always demanded of
// a uid on the way *out* of a file; it had no counterpart on the way in, which
// is how a request body's caldavUid could name a path.
//
// Kept here rather than in server.js because both the file writer and the two
// rows that store the value need it, and two copies of a rule like this is how
// one of them quietly stops matching.
// 200 and not 120: the read-side regexes have no cap at all, and every
// difference between the two is a value one of them accepts and the other
// refuses. 200 is the length this codebase already uses for an id
// (validateLengths on calendar events, cultures, batches), and `uid + '.ics'`
// still fits any filesystem's 255-byte name limit — which is the only reason
// there is a cap here at all.
const CALDAV_UID_RE = /^[A-Za-z0-9\-_.@]{1,200}$/;
function isValidCaldavUid(uid) {
return typeof uid === 'string' && CALDAV_UID_RE.test(uid);
}
/** A uid we would refuse to write is not worth storing — it becomes null and
* the next sync mints a fresh one, instead of leaving a row that can never
* reach a calendar and never says why. */
function cleanCaldavUid(uid) {
return isValidCaldavUid(uid) ? uid : null;
}
function insertTask(db, t) {
const r = db
.prepare(
'INSERT INTO manual_tasks(text,priority,done,created,assignee,due_date,due_time,due_end_time,description,caldav_uid,caldav_synced,private,recurrence,recurrence_until) VALUES(?,?,?,?,?,?,?,?,?,?,?,?,?,?)'
)
.run(
t.text,
t.priority || 'med',
t.done ? 1 : 0,
t.created,
t.assignee || null,
t.dueDate || null,
t.dueTime || null,
t.dueEndTime || null,
t.description || null,
cleanCaldavUid(t.caldavUid),
t.caldavSynced || null,
t.private ? 1 : 0,
t.recurrence || null,
t.recurrenceUntil || null
);
incrementDataVersion(db);
return r.lastInsertRowid;
}
function updateTaskById(db, id, fields) {
const map = {
done: 'done',
caldavUid: 'caldav_uid',
caldavSynced: 'caldav_synced',
text: 'text',
priority: 'priority',
assignee: 'assignee',
dueDate: 'due_date',
dueTime: 'due_time',
dueEndTime: 'due_end_time',
description: 'description',
private: 'private',
recurrence: 'recurrence',
recurrenceUntil: 'recurrence_until'
};
const entries = Object.entries(fields).filter(([k]) => map[k]);
if (!entries.length) return;
// I-15: bump SEQUENCE on any meaningful update (RFC 5545 §3.8.7.4) so
// CalDAV clients see the change. Skip pure caldavSynced bookkeeping
// updates so we don't spuriously invalidate cached calendar entries.
const meaningful = entries.some(([k]) => k !== 'caldavSynced' && k !== 'caldavUid');
const sets = entries.map(([k]) => `${map[k]}=?`).join(',') + (meaningful ? ', sequence=sequence+1' : '');
// The fourth place that stores a caldav uid, and the last one that did not go
// through the rule. Same reason as the other three: a uid we would refuse to
// write is not worth keeping, and the next sync mints a fresh one.
const vals = entries.map(([k, v]) => {
if (k === 'done' || k === 'private') return v ? 1 : 0;
if (k === 'caldavUid') return cleanCaldavUid(v);
return v;
});
db.prepare(`UPDATE manual_tasks SET ${sets} WHERE id=?`).run(...vals, id);
incrementDataVersion(db);
}
function readTaskById(db, id) {
const r = db.prepare('SELECT * FROM manual_tasks WHERE id=?').get(id);
if (!r) return null;
return {
id: r.id,
text: r.text,
priority: r.priority,
done: r.done === 1,
created: r.created,
assignee: r.assignee,
dueDate: r.due_date,
dueTime: r.due_time,
dueEndTime: r.due_end_time,
description: r.description,
caldavUid: r.caldav_uid,
caldavSynced: r.caldav_synced,
private: r.private === 1,
recurrence: r.recurrence || null,
recurrenceUntil: r.recurrence_until || null
};
}
// Check whether a user is allowed to modify or delete a task.
// Admins can always modify. Otherwise, the user must be named in the
// task's assignee field (comma-separated) OR the task must be
// unassigned (null/empty assignee means "for everyone").
/** The assignee column is a comma-separated list. One place that says so. */
function taskAssignees(raw) {
return String(raw || '')
.split(',')
.map((s) => s.trim())
.filter(Boolean);
}
function canUserModifyTask(db, username, taskId, isAdmin) {
if (isAdmin) return true;
const r = db.prepare('SELECT assignee FROM manual_tasks WHERE id=?').get(taskId);
if (!r) return false;
const assignees = taskAssignees(r.assignee);
if (!assignees.length) return true;
return assignees.includes(username);
}
/**
* May this user see this task at all?
*
* The task dialog has a checkbox reading "only visible to the assigned person".
* It was honoured in exactly one place — autoPushTaskCaldav skips the shared
* calendar for it — and nowhere else. readAll selected every row with no
* filter, GET /api/data handed the payload to any authenticated session, and
* the client read the flag only in the edit dialog, never in a render path. One
* request therefore returned every note somebody had marked private, which for
* a task list means sickness, warnings and personnel matters.
*
* Takes the row rather than an id: the callers already hold whole rows, and a
* SELECT per task turns one payload into hundreds of queries.
*
* ⚠️ **Fails closed when the row does not carry `private`.** A mapper that
* forgets the column must not silently come to mean "public" — that is exactly
* how the MCP briefing kept handing out what the web payload had started
* filtering. Every task mapper in this file carries it; the guard is for the
* next one.
*
* ⚠️ **A private task with no assignee stays visible, and that is deliberate.**
* There is no created_by column, so such a row belongs to nobody; hiding it
* would lose the note for whoever typed it, with no way to get it back. The
* checkbox's own words say nothing about the case where there is no assigned
* person. Making that case impossible is a question for the dialog, not for a
* read filter that would eat data to answer it.
*/
function canUserSeeTask(db, task, username, isAdmin) {
if (isAdmin) return true;
if (!task || task.private === undefined) return false;
if (!task.private) return true;
const assignees = taskAssignees(task.assignee);
if (!assignees.length) return true;
if (assignees.includes(username)) return true;
// ⚠️ **The assignee picker offers two namespaces, and only one of them can
// log in.** app.js merges `users[].username` with `team_members[].name`, and
// a team member is a free-text row with no account and no key to one. A
// private task assigned only to such a name would otherwise be visible to
// nobody but an admin — including whoever typed it, who cannot get it back
// because there is no created_by column. That is the same data loss the
// no-assignee case above is written to avoid, so it takes the same answer:
// when there is no account to keep it for, it is not kept from anyone.
const konten = db
.prepare('SELECT COUNT(*) AS n FROM users WHERE username IN (' + assignees.map(() => '?').join(',') + ')')
.get(...assignees);
return !(konten && konten.n > 0);
}
function readTaskByCaldavUid(db, caldavUid) {
const r = db.prepare('SELECT * FROM manual_tasks WHERE caldav_uid = ?').get(caldavUid);
if (!r) return null;
return {
id: r.id,
text: r.text,
priority: r.priority,
done: r.done === 1,
created: r.created,
assignee: r.assignee,
dueDate: r.due_date,
dueTime: r.due_time,
dueEndTime: r.due_end_time,
description: r.description,
caldavUid: r.caldav_uid,
caldavSynced: r.caldav_synced,
private: r.private === 1,
recurrence: r.recurrence || null,
recurrenceUntil: r.recurrence_until || null
};
}
function readBatchById(db, batchId) {
const r = db.prepare('SELECT * FROM batches WHERE batch_id=?').get(batchId);
if (!r) return null;
const bagRows = db.prepare('SELECT bag_id, bag_kg FROM bags WHERE batch_id = ? ORDER BY bag_id').all(batchId);
return mapBatchRow(r, bagRows, db);
}
function deleteTaskById(db, id) {
db.prepare('DELETE FROM manual_tasks WHERE id=?').run(id);
incrementDataVersion(db);
}
// -- Team Members --
function insertMember(db, m) {
const r = db.prepare('INSERT INTO team_members(name,role,added) VALUES(?,?,?)').run(m.name, m.role || null, m.added);
incrementDataVersion(db);
return r.lastInsertRowid;
}
function deleteMember(db, id) {
db.prepare('DELETE FROM team_members WHERE id=?').run(id);
incrementDataVersion(db);
}
// -- CalDAV Config --
function updateCaldavCfg(db, c) {
db.prepare('UPDATE caldav_config SET enabled=? WHERE id=1').run(c.enabled ? 1 : 0);
incrementDataVersion(db);
}
// -- DuckDNS Config --
function getDuckdnsCfg(db) {
const row = db.prepare('SELECT * FROM duckdns_config WHERE id = 1').get();
return {
enabled: row.enabled === 1,
domain: row.domain || '',
token: row.token || '',
lastIpUpdate: row.last_ip_update || null,
lastIp: row.last_ip || null,
fallbackLast: row.fallback_last || null,
leEnabled: row.le_enabled === 1,
leLastRenewal: row.le_last_renewal || null,
leExpiry: row.le_expiry || null
};
}
// Writes only the fields `cfg` carries; one it leaves out keeps its stored
// value. All four used to be written every time, so a request that sent only
// some of them reset the rest: without `leEnabled` the certificate renewal was
// switched off, without `enabled` the updater stopped, without `domain` the
// name was wiped. Rotating the token with a request that carried nothing but
// the token would have done all three.
function updateDuckdnsCfg(db, cfg) {
const sets = [];
const vals = [];
if (cfg.enabled !== undefined) {
sets.push('enabled=?');
vals.push(cfg.enabled ? 1 : 0);
}
if (cfg.domain !== undefined) {
sets.push('domain=?');
vals.push(cfg.domain || '');
}
if (cfg.token !== undefined) {
sets.push('token=?');
vals.push(cfg.token || '');
}
if (cfg.leEnabled !== undefined) {
sets.push('le_enabled=?');
vals.push(cfg.leEnabled ? 1 : 0);
}
if (!sets.length) return;
db.prepare('UPDATE duckdns_config SET ' + sets.join(',') + ' WHERE id=1').run(...vals);
incrementDataVersion(db);
}
function updateDuckdnsStatus(db, fields) {
const sets = [];
const vals = [];
if (fields.lastIpUpdate !== undefined) {
sets.push('last_ip_update=?');
vals.push(fields.lastIpUpdate);
}
if (fields.lastIp !== undefined) {
sets.push('last_ip=?');
vals.push(fields.lastIp);
}
// Written only by scripts/duckdns-fallback.js. Kept apart from
// last_ip_update so that neither writer can silence the other's alarm.
if (fields.fallbackLast !== undefined) {
sets.push('fallback_last=?');
vals.push(fields.fallbackLast);
}
if (fields.leLastRenewal !== undefined) {
sets.push('le_last_renewal=?');
vals.push(fields.leLastRenewal);
}
if (fields.leExpiry !== undefined) {
sets.push('le_expiry=?');
vals.push(fields.leExpiry);
}
if (sets.length) db.prepare('UPDATE duckdns_config SET ' + sets.join(',') + ' WHERE id=1').run(...vals);
}
// -- Harvest Feed Config --
//
// One row, same shape as the other integrations. `secret` never leaves this
// file towards a client: the API hands out `hasSecret` instead, and an update
// that omits it keeps the stored one — otherwise loading the form and pressing
// Save would silently blank it.
/**
* "250,500,1000" back into [250, 500, 1000].
*
* A split and not a validator. What may go in is decided once, on the way in
* (harvestFeed.packSizes) — repeating the rules here would mean two places to
* change and one of them forgotten. Unreadable entries are dropped rather than
* thrown on: a hand-edited row must not make the settings page unopenable.
*/
function splitPackSizes(raw) {
return (
String(raw || '')
.split(',')
// ⚠️ Number and not parseInt, for the same reason as over there, and this
// is where it was caught: parseInt('9e9') is 9 and parseInt('500g') is 500.
// The feed itself re-checks the range and would have dropped the 9, so the
// damage was confined to the settings page — which offered a 9 g box.
.map((s) => Number(String(s).trim()))
.filter((n) => Number.isInteger(n) && n > 0)
);
}
function getHarvestFeedCfg(db) {
const row = db.prepare('SELECT * FROM harvest_feed_config WHERE id = 1').get();
if (!row) return null;
return {
enabled: row.enabled === 1,
url: row.url || '',
secret: row.secret || '',
intervalMin: row.interval_min ?? 15,
freshDays: row.fresh_days ?? 3,
plannedDays: row.planned_days ?? 28,
leadDays: row.lead_days ?? 0,
strain: row.strain !== 0,
site: row.site || '',
packSizes: splitPackSizes(row.pack_sizes),
lastAt: row.last_at || null,
lastOk: row.last_ok === null || row.last_ok === undefined ? null : row.last_ok === 1,
lastError: row.last_error || null
};
}
function updateHarvestFeedCfg(db, cfg) {
db.prepare(
`UPDATE harvest_feed_config
SET enabled=?, url=?, secret=?, interval_min=?, fresh_days=?,
planned_days=?, lead_days=?, strain=?, site=?, pack_sizes=?, release_mode=?
WHERE id=1`
).run(
cfg.enabled ? 1 : 0,
cfg.url || '',
cfg.secret || '',
cfg.intervalMin ?? 15,
cfg.freshDays ?? 3,
cfg.plannedDays ?? 28,
cfg.leadDays ?? 0,
cfg.strain === false ? 0 : 1,
cfg.site || '',
// Stored as text, canonical: ascending, deduplicated, comma-separated. The
// caller has already put it through harvestFeed.packSizes; anything else
// that reaches here is written as it comes and read back leniently.
Array.isArray(cfg.packSizes) ? cfg.packSizes.join(',') : String(cfg.packSizes || ''),
// The column stays and is always 1. Dropping it would cost a migration, and
// an older build reading this database would read the missing switch as
// "off" and go back to publishing harvest totals — the exact failure this
// change exists to remove.
1
);
incrementDataVersion(db);
}
/**
* Record how the last attempt went.
*
* Kept apart from the config write so a failing receiver cannot bump the data
* version every quarter of an hour — and so "when did this last work?" survives
* a restart. Without it, a feed that quietly stopped delivering looks exactly
* like one that is working.
*/
function updateHarvestFeedStatus(db, { at, ok, error }) {
db.prepare('UPDATE harvest_feed_config SET last_at=?, last_ok=?, last_error=? WHERE id=1').run(
at || new Date().toISOString(),
ok ? 1 : 0,
ok ? null : String(error || '').slice(0, 500)
);
}
// -- Release for sale --
//
// One row per species: how much of it a shop may sell, and until when. Grams,
// because that is what the scale says and what `harvests` stores — the
// conversion to whatever a shop lists in belongs at the far end, once.
/** Today as YYYY-MM-DD, local time. A release runs out at the end of a day, not at UTC midnight. */
function localDay(at) {
const d = at || new Date();
const p = (n) => String(n).padStart(2, '0');
return `${d.getFullYear()}-${p(d.getMonth() + 1)}-${p(d.getDate())}`;
}
/**
* Every species string this database has ever used, verbatim.
*
* The species string is a join key: the feed sends it as it stands ("Lions Mane
* (LM)") and a receiver matches on it literally. Offering this list instead of a
* text field is what stops a hand-typed "Lions Mane" from being recorded as a
* release that matches nothing and disappears without an error.
*
* ⚠️ Both tables, not just `harvests`. Produce can be set aside before it
* comes off the rack — a sale is often planned that way — and a species
* that is only planned has no harvest row yet. Reading `harvests` alone would
* leave exactly those out of the list, which sends the person back to typing.
*
* Same columns buildPayload() reads, and that is the point: what can be picked
* is what the receiver will be matching on.
*/
function listKnownSpecies(db) {
return db
.prepare(
`SELECT species FROM harvests WHERE species IS NOT NULL AND species <> ''
UNION
SELECT species FROM batches WHERE species IS NOT NULL AND species <> ''
ORDER BY species`
)
.all()
.map((r) => r.species);
}
/** Every release, expired ones included — they are the interesting ones in a list. */
function listHarvestReleases(db, at) {
const today = localDay(at);
return db
.prepare('SELECT species, grams, valid_until, note, updated FROM harvest_release ORDER BY species')
.all()
.map((r) => ({
species: r.species,
grams: r.grams || 0,
validUntil: r.valid_until || null,
note: r.note || '',
updated: r.updated,
expired: !!(r.valid_until && r.valid_until < today)
}));
}
/**
* Only what may actually go out right now, keyed by species.
*
* Expired and zero rows are dropped rather than reported as 0, so a caller
* cannot accidentally read "released: 0" as "released, none left" — the two
* mean different things to a shop and only one of them should be published.
*/
function activeHarvestReleases(db, at) {
const today = localDay(at);
const out = new Map();
for (const r of db.prepare('SELECT species, grams, valid_until FROM harvest_release').all()) {
if (!(r.grams > 0)) continue;
if (r.valid_until && r.valid_until < today) continue;
out.set(r.species, { grams: r.grams, validUntil: r.valid_until || null });
}
return out;
}
/**
* May this user set produce aside for sale?
*
* Twin of requireShipping()'s rule in server.js, and here for the same reason it
* exists there: recording a harvest is lab work that every worker does, but a
* release names the quantity a shop may publicly offer. Every other write to
* `harvest_release` sits behind requireAdmin, so without this the harvest route
* would be a way around it.
*
* Not requireAdmin, though. The whole point of releasing at the scale is that the
* person holding the bag does it, and that person is usually not an admin —
* admin-only would move the decision back to a desk, which is the arrangement this
* feature replaced. So: a ca