Repository navigation
Expand file tree
/
Copy pathdb.js
More file actions
2022 lines (1847 loc) · 77.7 KB
/
Copy pathdb.js
File metadata and controls
2022 lines (1847 loc) · 77.7 KB
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
297
298
299
300
301
302
303
304
305
306
307
308
309
310
311
312
313
314
315
316
317
318
319
320
321
322
323
324
325
326
327
328
329
330
331
332
333
334
335
336
337
338
339
340
341
342
343
344
345
346
347
348
349
350
351
352
353
354
355
356
357
358
359
360
361
362
363
364
365
366
367
368
369
370
371
372
373
374
375
376
377
378
379
380
381
382
383
384
385
386
387
388
389
390
391
392
393
394
395
396
397
398
399
400
401
402
403
404
405
406
407
408
409
410
411
412
413
414
415
416
417
418
419
420
421
422
423
424
425
426
427
428
429
430
431
432
433
434
435
436
437
438
439
440
441
442
443
444
445
446
447
448
449
450
451
452
453
454
455
456
457
458
459
460
461
462
463
464
465
466
467
468
469
470
471
472
473
474
475
476
477
478
479
480
481
482
483
484
485
486
487
488
489
490
491
492
493
494
495
496
497
498
499
500
501
502
503
504
505
506
507
508
509
510
511
512
513
514
515
516
517
518
519
520
521
522
523
524
525
526
527
528
529
530
531
532
533
534
535
536
537
538
539
540
541
542
543
544
545
546
547
548
549
550
551
552
553
554
555
556
557
558
559
560
561
562
563
564
565
566
567
568
569
570
571
572
573
574
575
576
577
578
579
580
581
582
583
584
585
586
587
588
589
590
591
592
593
594
595
596
597
598
599
600
601
602
603
604
605
606
607
608
609
610
611
612
613
614
615
616
617
618
619
620
621
622
623
624
625
626
627
628
629
630
631
632
633
634
635
636
637
638
639
640
641
642
643
644
645
646
647
648
649
650
651
652
653
654
655
656
657
658
659
660
661
662
663
664
665
666
667
668
669
670
671
672
673
674
675
676
677
678
679
680
681
682
683
684
685
686
687
688
689
690
691
692
693
694
695
696
697
698
699
700
701
702
703
704
705
706
707
708
709
710
711
712
713
714
715
716
717
718
719
720
721
722
723
724
725
726
727
728
729
730
731
732
733
734
735
736
737
738
739
740
741
742
743
744
745
746
747
748
749
750
751
752
753
754
755
756
757
758
759
760
761
762
763
764
765
766
767
768
769
770
771
772
773
774
775
776
777
778
779
780
781
782
783
784
785
786
787
788
789
790
791
792
793
794
795
796
797
798
799
800
801
802
803
804
805
806
807
808
809
810
811
812
813
814
815
816
817
818
819
820
821
822
823
824
825
826
827
828
829
830
831
832
833
834
835
836
837
838
839
840
841
842
843
844
845
846
847
848
849
850
851
852
853
854
855
856
857
858
859
860
861
862
863
864
865
866
867
868
869
870
871
872
873
874
875
876
877
878
879
880
881
882
883
884
885
886
887
888
889
890
891
892
893
894
895
896
897
898
899
900
901
902
903
904
905
906
907
908
909
910
911
912
913
914
915
916
917
918
919
920
921
922
923
924
925
926
927
928
929
930
931
932
933
934
935
936
937
938
939
940
941
942
943
944
945
946
947
948
949
950
951
952
953
954
955
956
957
958
959
960
961
962
963
964
965
966
967
968
969
970
971
972
973
974
975
976
977
978
979
980
981
982
983
984
985
986
987
988
989
990
991
992
993
994
995
996
997
998
999
1000
const path = require('path');
const fs = require('fs');
const { openInventoryDatabase } = require('./lib/open-database');
const { computeItemCompleteness } = require('./lib/completeness');
const {
profileById,
sanitizeInstrumentType,
sanitizeInstrumentSpecs,
instrumentDetails
} = require('./lib/instrument-profiles');
const DATA_DIR = process.env.STUDIO_DATA_DIR
? path.resolve(process.env.STUDIO_DATA_DIR)
: path.join(__dirname, 'data');
const DB_PATH = path.join(DATA_DIR, 'inventory.db');
const UPLOADS_DIR = path.join(DATA_DIR, 'uploads');
for (const dir of [DATA_DIR, UPLOADS_DIR, path.join(UPLOADS_DIR, 'photos'),
path.join(UPLOADS_DIR, 'manuals'), path.join(UPLOADS_DIR, 'software'),
path.join(UPLOADS_DIR, 'software-licenses'), path.join(UPLOADS_DIR, 'receipts'),
path.join(UPLOADS_DIR, 'logos'), path.join(UPLOADS_DIR, 'wall-photos'),
path.join(DATA_DIR, 'backups')]) {
if (!fs.existsSync(dir)) fs.mkdirSync(dir, { recursive: true });
}
const LOGOS_DIR = path.join(UPLOADS_DIR, 'logos');
/**
* Record IDs become folder names under uploads/. Accept only positive integers
* so a crafted ID such as "../../x" can never point outside the data folder.
*/
function safeRecordId(id) {
const text = String(id ?? '').trim();
if (!/^[1-9]\d{0,15}$/.test(text)) {
const err = new Error('Invalid record id');
err.status = 400;
throw err;
}
return text;
}
/** Absolute path for a stored upload path, or '' when it would leave uploads/. */
function resolveUploadPath(relativePath) {
const rel = String(relativePath || '').replace(/\\/g, '/').replace(/^\/+/, '');
if (!rel || rel.split('/').some(part => part === '..')) return '';
const root = path.resolve(UPLOADS_DIR);
const full = path.resolve(root, rel);
return full.startsWith(root + path.sep) ? full : '';
}
function removeUploadFile(relativePath) {
const full = resolveUploadPath(relativePath);
if (full && fs.existsSync(full) && fs.statSync(full).isFile()) fs.unlinkSync(full);
}
let db = null;
let catalogLock = null;
try {
db = openInventoryDatabase(DB_PATH);
} catch (err) {
if (err.code !== 'CATALOG_LOCKED') throw err;
catalogLock = err.message;
}
const DEFAULT_CATEGORIES = [
'Guitar', 'Bass', 'Keyboard', 'Brass Instrument', 'Bowed String Instrument',
'Drum Kit', 'Electronic Drum Kit', 'Electronic Drum Component',
'Acoustic Drum Component', 'Drum Hardware', 'Instrument Accessory',
'Microphone', 'Audio Interface',
'Mixer', 'Control Surface', 'Speaker/Monitor', 'Amplifier', 'Pedal',
'Cable/Accessory', 'Other'
];
const DEFAULT_LOCATIONS = [
'Main Rack', 'Desk', 'Storage', 'Practice Area', 'Other'
];
const DRIVER_CATEGORIES = new Set([
'Audio Interface', 'Mixer', 'Control Surface', 'Keyboard'
]);
const SOFTWARE_CATEGORIES = ['DAW', 'Plugin', 'Instrument', 'Utility', 'Notation', 'Bundle', 'Other'];
const LICENSE_TYPES = ['perpetual', 'subscription', 'educational', 'nfr', 'oem', 'rent_to_own'];
const ACTIVATION_METHODS = ['account', 'ilok', 'ilok_cloud', 'challenge', 'machine', 'usb_dongle', 'other'];
const PLUGIN_FORMATS = ['vst3', 'au', 'aax', 'standalone', 'multiple', 'other'];
function runMigrations() {
db.transaction(migrateSchema)();
}
function migrateSchema() {
const itemCols = db.prepare('PRAGMA table_info(items)').all().map(c => c.name);
if (!itemCols.includes('update_checks_enabled')) {
db.exec('ALTER TABLE items ADD COLUMN update_checks_enabled INTEGER NOT NULL DEFAULT 1');
}
if (!itemCols.includes('warranty_end_date')) {
db.exec("ALTER TABLE items ADD COLUMN warranty_end_date TEXT DEFAULT ''");
}
if (!itemCols.includes('warranty_note')) {
db.exec("ALTER TABLE items ADD COLUMN warranty_note TEXT DEFAULT ''");
}
if (!itemCols.includes('studio_status')) {
db.exec("ALTER TABLE items ADD COLUMN studio_status TEXT NOT NULL DEFAULT 'in_studio'");
}
if (!itemCols.includes('studio_status_note')) {
db.exec("ALTER TABLE items ADD COLUMN studio_status_note TEXT DEFAULT ''");
}
if (!itemCols.includes('disposition_date')) {
db.exec("ALTER TABLE items ADD COLUMN disposition_date TEXT DEFAULT ''");
}
db.exec(`
CREATE TABLE IF NOT EXISTS item_value_events (
id INTEGER PRIMARY KEY AUTOINCREMENT,
item_id INTEGER NOT NULL,
amount REAL NOT NULL,
note TEXT DEFAULT '',
recorded_at TEXT DEFAULT (datetime('now')),
FOREIGN KEY (item_id) REFERENCES items(id) ON DELETE CASCADE
);
CREATE INDEX IF NOT EXISTS idx_value_events_item ON item_value_events(item_id, recorded_at);
CREATE TABLE IF NOT EXISTS item_audit (
id INTEGER PRIMARY KEY AUTOINCREMENT,
item_id INTEGER NOT NULL,
field TEXT NOT NULL,
old_value TEXT DEFAULT '',
new_value TEXT DEFAULT '',
recorded_at TEXT DEFAULT (datetime('now')),
FOREIGN KEY (item_id) REFERENCES items(id) ON DELETE CASCADE
);
CREATE INDEX IF NOT EXISTS idx_item_audit_item ON item_audit(item_id, recorded_at);
CREATE TABLE IF NOT EXISTS studio_secrets (
id INTEGER PRIMARY KEY CHECK (id = 1),
guest_token TEXT DEFAULT '',
scan_link_secret TEXT DEFAULT '',
owner_pin_hash TEXT DEFAULT '',
owner_pin_salt TEXT DEFAULT '',
owner_session_token TEXT DEFAULT '',
owner_session_tokens TEXT DEFAULT '[]'
);
`);
if (!itemCols.includes('value_updated_at')) {
db.exec('ALTER TABLE items ADD COLUMN value_updated_at TEXT DEFAULT NULL');
db.exec(`UPDATE items SET value_updated_at = updated_at WHERE replacement_value > 0 AND value_updated_at IS NULL`);
}
if (!itemCols.includes('parent_item_id')) {
db.exec('ALTER TABLE items ADD COLUMN parent_item_id INTEGER DEFAULT NULL REFERENCES items(id) ON DELETE SET NULL');
}
if (!itemCols.includes('depreciated_value')) {
db.exec('ALTER TABLE items ADD COLUMN depreciated_value REAL DEFAULT 0');
}
if (!itemCols.includes('on_insurance_policy')) {
db.exec('ALTER TABLE items ADD COLUMN on_insurance_policy INTEGER NOT NULL DEFAULT 0');
}
if (!itemCols.includes('insurance_policy_note')) {
db.exec("ALTER TABLE items ADD COLUMN insurance_policy_note TEXT DEFAULT ''");
}
if (!itemCols.includes('wall_cutout_path')) {
db.exec("ALTER TABLE items ADD COLUMN wall_cutout_path TEXT DEFAULT ''");
db.exec('ALTER TABLE items ADD COLUMN wall_cutout_width_ft REAL DEFAULT 0');
db.exec('ALTER TABLE items ADD COLUMN wall_cutout_height_ft REAL DEFAULT 0');
db.exec("ALTER TABLE items ADD COLUMN wall_cutout_calibration_json TEXT DEFAULT '{}'");
}
if (!itemCols.includes('requires_power')) {
db.exec('ALTER TABLE items ADD COLUMN requires_power INTEGER NOT NULL DEFAULT 0');
db.exec("ALTER TABLE items ADD COLUMN power_adapter_voltage TEXT DEFAULT ''");
db.exec("ALTER TABLE items ADD COLUMN power_adapter_current TEXT DEFAULT ''");
db.exec("ALTER TABLE items ADD COLUMN power_adapter_polarity TEXT DEFAULT ''");
db.exec("ALTER TABLE items ADD COLUMN power_adapter_notes TEXT DEFAULT ''");
}
if (!itemCols.includes('instrument_type')) {
db.exec("ALTER TABLE items ADD COLUMN instrument_type TEXT DEFAULT ''");
}
if (!itemCols.includes('instrument_specs_json')) {
db.exec("ALTER TABLE items ADD COLUMN instrument_specs_json TEXT NOT NULL DEFAULT '{}'");
}
const attCols2 = db.prepare('PRAGMA table_info(attachments)').all().map(c => c.name);
if (!attCols2.includes('extracted_text')) {
db.exec("ALTER TABLE attachments ADD COLUMN extracted_text TEXT DEFAULT ''");
}
db.exec(`
CREATE TABLE IF NOT EXISTS maintenance_log (
id INTEGER PRIMARY KEY AUTOINCREMENT,
item_id INTEGER NOT NULL,
service_date TEXT NOT NULL DEFAULT (date('now')),
service_type TEXT NOT NULL DEFAULT 'maintenance',
note TEXT DEFAULT '',
created_at TEXT DEFAULT (datetime('now')),
FOREIGN KEY (item_id) REFERENCES items(id) ON DELETE CASCADE
);
CREATE INDEX IF NOT EXISTS idx_maintenance_item ON maintenance_log(item_id);
CREATE TABLE IF NOT EXISTS loan_log (
id INTEGER PRIMARY KEY AUTOINCREMENT,
item_id INTEGER NOT NULL,
borrower_name TEXT NOT NULL,
borrower_contact TEXT DEFAULT '',
loaned_at TEXT NOT NULL DEFAULT (date('now')),
due_date TEXT DEFAULT NULL,
returned_at TEXT DEFAULT NULL,
note TEXT DEFAULT '',
condition_out TEXT DEFAULT '',
condition_in TEXT DEFAULT '',
created_at TEXT DEFAULT (datetime('now')),
FOREIGN KEY (item_id) REFERENCES items(id) ON DELETE CASCADE
);
CREATE INDEX IF NOT EXISTS idx_loan_item ON loan_log(item_id);
CREATE INDEX IF NOT EXISTS idx_loan_active ON loan_log(item_id, returned_at);
CREATE TABLE IF NOT EXISTS racks (
id INTEGER PRIMARY KEY AUTOINCREMENT,
name TEXT NOT NULL,
location TEXT DEFAULT '',
notes TEXT DEFAULT '',
sort_order INTEGER DEFAULT 0,
created_at TEXT DEFAULT (datetime('now'))
);
CREATE TABLE IF NOT EXISTS rack_items (
rack_id INTEGER NOT NULL,
item_id INTEGER NOT NULL,
position INTEGER NOT NULL DEFAULT 0,
slot_label TEXT DEFAULT '',
PRIMARY KEY (rack_id, item_id),
FOREIGN KEY (rack_id) REFERENCES racks(id) ON DELETE CASCADE,
FOREIGN KEY (item_id) REFERENCES items(id) ON DELETE CASCADE
);
CREATE TABLE IF NOT EXISTS signal_chains (
id INTEGER PRIMARY KEY AUTOINCREMENT,
name TEXT NOT NULL,
description TEXT DEFAULT '',
sort_order INTEGER DEFAULT 0,
created_at TEXT DEFAULT (datetime('now'))
);
CREATE TABLE IF NOT EXISTS signal_chain_items (
chain_id INTEGER NOT NULL,
item_id INTEGER NOT NULL,
position INTEGER NOT NULL DEFAULT 0,
PRIMARY KEY (chain_id, item_id),
FOREIGN KEY (chain_id) REFERENCES signal_chains(id) ON DELETE CASCADE,
FOREIGN KEY (item_id) REFERENCES items(id) ON DELETE CASCADE
);
CREATE INDEX IF NOT EXISTS idx_items_parent ON items(parent_item_id);
CREATE TABLE IF NOT EXISTS floorplans (
id INTEGER PRIMARY KEY AUTOINCREMENT,
location TEXT NOT NULL UNIQUE,
image_path TEXT DEFAULT '',
width INTEGER DEFAULT 0,
height INTEGER DEFAULT 0,
notes TEXT DEFAULT '',
created_at TEXT DEFAULT (datetime('now')),
updated_at TEXT DEFAULT (datetime('now'))
);
CREATE TABLE IF NOT EXISTS floorplan_items (
floorplan_id INTEGER NOT NULL,
item_id INTEGER NOT NULL,
x_pct REAL NOT NULL DEFAULT 50,
y_pct REAL NOT NULL DEFAULT 50,
placement TEXT NOT NULL DEFAULT 'floor',
wall_edge INTEGER DEFAULT NULL,
wall_t REAL DEFAULT NULL,
height_ft REAL DEFAULT NULL,
icon_mode TEXT NOT NULL DEFAULT 'logo',
wall_photo_path TEXT DEFAULT '',
photo_width_ft REAL DEFAULT 0,
photo_height_ft REAL DEFAULT 0,
rotation_deg REAL DEFAULT 0,
photo_calibration_json TEXT DEFAULT '{}',
wall_display INTEGER NOT NULL DEFAULT 1,
PRIMARY KEY (floorplan_id, item_id),
FOREIGN KEY (floorplan_id) REFERENCES floorplans(id) ON DELETE CASCADE,
FOREIGN KEY (item_id) REFERENCES items(id) ON DELETE CASCADE
);
CREATE TABLE IF NOT EXISTS software_licenses (
id INTEGER PRIMARY KEY AUTOINCREMENT,
name TEXT NOT NULL,
publisher TEXT DEFAULT '',
version TEXT DEFAULT '',
category TEXT DEFAULT 'Plugin',
license_key TEXT DEFAULT '',
license_type TEXT NOT NULL DEFAULT 'perpetual',
activation_method TEXT NOT NULL DEFAULT 'account',
plugin_format TEXT DEFAULT 'vst3',
seats INTEGER NOT NULL DEFAULT 1,
renewal_date TEXT DEFAULT NULL,
purchase_date TEXT DEFAULT '',
purchase_price REAL DEFAULT 0,
replacement_value REAL DEFAULT 0,
host_item_id INTEGER DEFAULT NULL,
screenshot_path TEXT DEFAULT '',
notes TEXT DEFAULT '',
created_at TEXT DEFAULT (datetime('now')),
updated_at TEXT DEFAULT (datetime('now')),
FOREIGN KEY (host_item_id) REFERENCES items(id) ON DELETE SET NULL
);
CREATE INDEX IF NOT EXISTS idx_software_name ON software_licenses(name);
CREATE INDEX IF NOT EXISTS idx_software_publisher ON software_licenses(publisher);
CREATE INDEX IF NOT EXISTS idx_software_category ON software_licenses(category);
CREATE INDEX IF NOT EXISTS idx_software_renewal ON software_licenses(renewal_date);
`);
// Column upgrades for the floorplan tables run after the tables exist, so a
// brand-new catalog gets every column on its first start.
const fpCols = db.prepare('PRAGMA table_info(floorplans)').all().map(c => c.name);
if (fpCols.length && !fpCols.includes('map_mode')) {
db.exec("ALTER TABLE floorplans ADD COLUMN map_mode TEXT NOT NULL DEFAULT 'draw'");
db.exec("ALTER TABLE floorplans ADD COLUMN polygon_json TEXT DEFAULT ''");
db.exec("ALTER TABLE floorplans ADD COLUMN unit TEXT NOT NULL DEFAULT 'ft'");
db.exec("ALTER TABLE floorplans ADD COLUMN bounds_width REAL DEFAULT 0");
db.exec("ALTER TABLE floorplans ADD COLUMN bounds_depth REAL DEFAULT 0");
db.exec("ALTER TABLE floorplans ADD COLUMN wall_lengths_json TEXT DEFAULT ''");
db.exec(`UPDATE floorplans SET map_mode = 'photo' WHERE image_path IS NOT NULL AND image_path != ''`);
}
const fpiCols = db.prepare('PRAGMA table_info(floorplan_items)').all().map(c => c.name);
if (fpiCols.length && !fpiCols.includes('placement')) {
db.exec("ALTER TABLE floorplan_items ADD COLUMN placement TEXT NOT NULL DEFAULT 'floor'");
db.exec('ALTER TABLE floorplan_items ADD COLUMN wall_edge INTEGER DEFAULT NULL');
db.exec('ALTER TABLE floorplan_items ADD COLUMN wall_t REAL DEFAULT NULL');
}
if (fpCols.length && !fpCols.includes('ceiling_height')) {
db.exec('ALTER TABLE floorplans ADD COLUMN ceiling_height REAL NOT NULL DEFAULT 9.5');
}
if (fpiCols.length && !fpiCols.includes('height_ft')) {
db.exec('ALTER TABLE floorplan_items ADD COLUMN height_ft REAL DEFAULT NULL');
db.exec("ALTER TABLE floorplan_items ADD COLUMN icon_mode TEXT NOT NULL DEFAULT 'logo'");
db.exec("ALTER TABLE floorplan_items ADD COLUMN wall_photo_path TEXT DEFAULT ''");
db.exec('ALTER TABLE floorplan_items ADD COLUMN photo_width_ft REAL DEFAULT 0');
db.exec('ALTER TABLE floorplan_items ADD COLUMN photo_height_ft REAL DEFAULT 0');
db.exec("ALTER TABLE floorplan_items ADD COLUMN photo_calibration_json TEXT DEFAULT '{}'");
}
if (fpCols.length && !fpCols.includes('wall_photos_json')) {
db.exec("ALTER TABLE floorplans ADD COLUMN wall_photos_json TEXT NOT NULL DEFAULT '{}'");
}
if (fpCols.length && !fpCols.includes('floor_image_scale')) {
db.exec('ALTER TABLE floorplans ADD COLUMN floor_image_scale REAL NOT NULL DEFAULT 1');
db.exec('ALTER TABLE floorplans ADD COLUMN floor_image_x REAL NOT NULL DEFAULT 0.5');
db.exec('ALTER TABLE floorplans ADD COLUMN floor_image_y REAL NOT NULL DEFAULT 0.5');
db.exec("ALTER TABLE floorplans ADD COLUMN floor_image_fit TEXT NOT NULL DEFAULT 'cover'");
}
if (fpiCols.length && !fpiCols.includes('wall_display')) {
db.exec('ALTER TABLE floorplan_items ADD COLUMN wall_display INTEGER NOT NULL DEFAULT 1');
}
if (fpiCols.length && !fpiCols.includes('rotation_deg')) {
db.exec('ALTER TABLE floorplan_items ADD COLUMN rotation_deg REAL DEFAULT 0');
}
db.exec(`
CREATE VIRTUAL TABLE IF NOT EXISTS manual_fts USING fts5(
attachment_id UNINDEXED,
item_name,
file_name,
body,
tokenize='porter'
);
`);
const attCols = db.prepare('PRAGMA table_info(attachments)').all().map(c => c.name);
if (!attCols.includes('relative_path')) {
db.exec(`
ALTER TABLE attachments ADD COLUMN relative_path TEXT DEFAULT '';
ALTER TABLE attachments ADD COLUMN version TEXT DEFAULT '';
ALTER TABLE attachments ADD COLUMN description TEXT DEFAULT '';
ALTER TABLE attachments ADD COLUMN source_url TEXT DEFAULT '';
ALTER TABLE attachments ADD COLUMN metadata TEXT DEFAULT '{}';
`);
db.exec(`UPDATE attachments SET relative_path = filename WHERE relative_path = '' OR relative_path IS NULL`);
}
}
function brandSlug(name) {
return String(name).trim().toLowerCase().replace(/[^a-z0-9]+/g, '-').replace(/^-|-$/g, '');
}
function initSchema() {
db.exec(`
CREATE TABLE IF NOT EXISTS items (
id INTEGER PRIMARY KEY AUTOINCREMENT,
name TEXT NOT NULL,
common_name TEXT DEFAULT '',
category TEXT DEFAULT '',
instrument_type TEXT DEFAULT '',
instrument_specs_json TEXT NOT NULL DEFAULT '{}',
brand TEXT DEFAULT '',
model TEXT DEFAULT '',
serial_number TEXT DEFAULT '',
year TEXT DEFAULT '',
purchase_date TEXT DEFAULT '',
purchase_price REAL DEFAULT 0,
replacement_value REAL DEFAULT 0,
replacement_value_note TEXT DEFAULT '',
condition TEXT DEFAULT 'Good',
condition_notes TEXT DEFAULT '',
location TEXT DEFAULT '',
description TEXT DEFAULT '',
quantity INTEGER DEFAULT 1,
requires_power INTEGER NOT NULL DEFAULT 0,
power_adapter_voltage TEXT DEFAULT '',
power_adapter_current TEXT DEFAULT '',
power_adapter_polarity TEXT DEFAULT '',
power_adapter_notes TEXT DEFAULT '',
update_checks_enabled INTEGER NOT NULL DEFAULT 1,
created_at TEXT DEFAULT (datetime('now')),
updated_at TEXT DEFAULT (datetime('now'))
);
CREATE TABLE IF NOT EXISTS tags (
id INTEGER PRIMARY KEY AUTOINCREMENT,
name TEXT UNIQUE NOT NULL COLLATE NOCASE
);
CREATE TABLE IF NOT EXISTS item_tags (
item_id INTEGER NOT NULL,
tag_id INTEGER NOT NULL,
PRIMARY KEY (item_id, tag_id),
FOREIGN KEY (item_id) REFERENCES items(id) ON DELETE CASCADE,
FOREIGN KEY (tag_id) REFERENCES tags(id) ON DELETE CASCADE
);
CREATE TABLE IF NOT EXISTS attachments (
id INTEGER PRIMARY KEY AUTOINCREMENT,
item_id INTEGER NOT NULL,
filename TEXT NOT NULL,
original_name TEXT NOT NULL,
relative_path TEXT NOT NULL DEFAULT '',
mime_type TEXT DEFAULT '',
type TEXT DEFAULT 'other',
version TEXT DEFAULT '',
description TEXT DEFAULT '',
source_url TEXT DEFAULT '',
metadata TEXT DEFAULT '{}',
created_at TEXT DEFAULT (datetime('now')),
FOREIGN KEY (item_id) REFERENCES items(id) ON DELETE CASCADE
);
CREATE INDEX IF NOT EXISTS idx_items_name ON items(name);
CREATE INDEX IF NOT EXISTS idx_items_category ON items(category);
CREATE INDEX IF NOT EXISTS idx_items_location ON items(location);
CREATE INDEX IF NOT EXISTS idx_items_condition ON items(condition);
CREATE INDEX IF NOT EXISTS idx_attachments_item ON attachments(item_id);
CREATE INDEX IF NOT EXISTS idx_attachments_type ON attachments(type);
CREATE TABLE IF NOT EXISTS brands (
id INTEGER PRIMARY KEY AUTOINCREMENT,
name TEXT UNIQUE NOT NULL COLLATE NOCASE,
logo_path TEXT DEFAULT '',
is_custom INTEGER NOT NULL DEFAULT 0,
created_at TEXT DEFAULT (datetime('now'))
);
CREATE INDEX IF NOT EXISTS idx_items_brand ON items(brand);
CREATE INDEX IF NOT EXISTS idx_brands_name ON brands(name);
`);
runMigrations();
backfillOpeningValues();
}
function backfillOpeningValues() {
const rows = db.prepare(`
SELECT id, replacement_value, value_updated_at
FROM items
WHERE replacement_value > 0
AND NOT EXISTS (SELECT 1 FROM item_value_events e WHERE e.item_id = items.id)
`).all();
if (!rows.length) return 0;
const insert = db.prepare(`
INSERT INTO item_value_events (item_id, amount, note, recorded_at)
VALUES (?, ?, 'Opening snapshot', ?)
`);
const now = new Date().toISOString();
db.transaction((list) => {
for (const row of list) {
const text = String(row.value_updated_at || '').trim();
insert.run(row.id, row.replacement_value, /^\d{4}-\d{2}-\d{2}/.test(text) ? text : now);
}
})(rows);
return rows.length;
}
function ensureBrand(brandName) {
const name = String(brandName || '').trim().slice(0, 150);
if (!name) return null;
const existing = db.prepare('SELECT * FROM brands WHERE name = ? COLLATE NOCASE').get(name);
if (existing) return existing;
const result = db.prepare('INSERT INTO brands (name, logo_path) VALUES (?, ?)').run(name, '');
return db.prepare('SELECT * FROM brands WHERE id = ?').get(result.lastInsertRowid);
}
function getBrandsWithCounts() {
return db.prepare(`
SELECT b.id, b.name, b.logo_path, b.is_custom,
(SELECT COUNT(*) FROM items i WHERE i.brand = b.name COLLATE NOCASE) as item_count
FROM brands b
ORDER BY item_count DESC, b.name ASC
`).all();
}
function syncBrandsFromItems() {
const itemBrands = db.prepare(`
SELECT DISTINCT brand FROM items WHERE brand != '' ORDER BY brand
`).all();
for (const { brand } of itemBrands) ensureBrand(brand);
}
function getTagsForItem(itemId) {
return db.prepare(`
SELECT t.id, t.name FROM tags t
JOIN item_tags it ON it.tag_id = t.id
WHERE it.item_id = ?
ORDER BY t.name
`).all(itemId);
}
function getAttachmentsForItem(itemId) {
return db.prepare(`
SELECT id, filename, original_name, relative_path, mime_type, type,
version, description, source_url, metadata, created_at
FROM attachments WHERE item_id = ? ORDER BY type, created_at DESC, id
`).all(itemId).map(a => ({
...a,
metadata: safeJsonParse(a.metadata)
}));
}
function safeJsonParse(str) {
try { return JSON.parse(str || '{}'); } catch { return {}; }
}
function itemInstrumentData(item) {
const instrument_type = sanitizeInstrumentType(item?.instrument_type);
const instrument_specs = sanitizeInstrumentSpecs(
instrument_type,
item?.instrument_specs_json || item?.instrument_specs
);
const profile = profileById.get(instrument_type);
return {
instrument_type,
instrument_type_label: profile?.label || '',
instrument_specs,
instrument_details: instrumentDetails(instrument_type, instrument_specs)
};
}
function parseItemWallCutout(item) {
const path = String(item?.wall_cutout_path || '').trim();
if (!path) return null;
return {
path,
width_ft: Math.max(0, parseFloat(item.wall_cutout_width_ft) || 0),
height_ft: Math.max(0, parseFloat(item.wall_cutout_height_ft) || 0),
calibration: safeJsonParse(item.wall_cutout_calibration_json)
};
}
function saveItemWallCutout(itemId, data = {}) {
const item = db.prepare('SELECT id FROM items WHERE id = ?').get(itemId);
if (!item) throw new Error('Item not found');
const wall_cutout_path = String(data.wall_photo_path || data.wall_cutout_path || '').slice(0, 500);
const wall_cutout_width_ft = Math.max(0, parseFloat(data.photo_width_ft) || 0);
const wall_cutout_height_ft = Math.max(0, parseFloat(data.photo_height_ft) || 0);
const wall_cutout_calibration_json = JSON.stringify(
data.photo_calibration && typeof data.photo_calibration === 'object'
? data.photo_calibration : safeJsonParse(data.photo_calibration_json)
);
db.prepare(`
UPDATE items SET wall_cutout_path = ?, wall_cutout_width_ft = ?, wall_cutout_height_ft = ?,
wall_cutout_calibration_json = ?, updated_at = datetime('now')
WHERE id = ?
`).run(wall_cutout_path, wall_cutout_width_ft, wall_cutout_height_ft,
wall_cutout_calibration_json, itemId);
return enrichItem(db.prepare('SELECT * FROM items WHERE id = ?').get(itemId));
}
function clearItemWallCutout(itemId) {
const item = db.prepare('SELECT id FROM items WHERE id = ?').get(itemId);
if (!item) throw new Error('Item not found');
db.prepare(`
UPDATE items SET wall_cutout_path = '', wall_cutout_width_ft = 0, wall_cutout_height_ft = 0,
wall_cutout_calibration_json = '{}', updated_at = datetime('now')
WHERE id = ?
`).run(itemId);
return enrichItem(db.prepare('SELECT * FROM items WHERE id = ?').get(itemId));
}
function enrichItem(item) {
if (!item) return null;
return enrichItems([item])[0];
}
const cachedStatements = new Map();
function cachedStatement(sql) {
let stmt = cachedStatements.get(sql);
if (!stmt) {
stmt = db.prepare(sql);
cachedStatements.set(sql, stmt);
}
return stmt;
}
function groupRows(rows, key = 'item_id') {
const groups = new Map();
for (const row of rows) {
const id = row[key];
if (!groups.has(id)) groups.set(id, []);
groups.get(id).push(row);
}
return groups;
}
/** SQLite's NOCASE collation folds only ASCII letters. */
function nocaseKey(value) {
return String(value).replace(/[A-Z]+/g, letters => letters.toLowerCase());
}
/**
* Enrich many items with a fixed number of queries (about ten, however many
* items there are) instead of a dozen queries per item. Each result is the
* same as the one-item-at-a-time version produced; the item-list, dashboard,
* guest and export endpoints depend on that.
*/
function enrichItems(items) {
const list = (items || []).filter(Boolean);
if (!list.length) return [];
const idsJson = JSON.stringify(list.map(item => item.id));
const inList = 'SELECT value FROM json_each(?)';
const childrenByParent = groupRows(cachedStatement(
`SELECT * FROM items WHERE parent_item_id IN (${inList}) ORDER BY name, id`
).all(idsJson), 'parent_item_id');
const withChildren = new Set(list.map(item => item.id));
for (const rows of childrenByParent.values()) for (const child of rows) withChildren.add(child.id);
const allIdsJson = JSON.stringify([...withChildren]);
const attachmentsByItem = new Map();
for (const { item_id, ...attachment } of cachedStatement(`
SELECT item_id, id, filename, original_name, relative_path, mime_type, type,
version, description, source_url, metadata, created_at
FROM attachments WHERE item_id IN (${inList}) ORDER BY type, created_at DESC, id
`).all(allIdsJson)) {
if (!attachmentsByItem.has(item_id)) attachmentsByItem.set(item_id, []);
attachmentsByItem.get(item_id).push({ ...attachment, metadata: safeJsonParse(attachment.metadata) });
}
const tagsByItem = new Map();
for (const { item_id, ...tag } of cachedStatement(`
SELECT it.item_id, t.id, t.name FROM tags t
JOIN item_tags it ON it.tag_id = t.id
WHERE it.item_id IN (${inList})
ORDER BY t.name
`).all(allIdsJson)) {
if (!tagsByItem.has(item_id)) tagsByItem.set(item_id, []);
tagsByItem.get(item_id).push(tag);
}
const maintenanceByItem = groupRows(cachedStatement(`
SELECT id, item_id, service_date, service_type, note, created_at
FROM maintenance_log WHERE item_id IN (${inList}) ORDER BY service_date DESC, id DESC
`).all(idsJson));
const loansByItem = groupRows(cachedStatement(`
SELECT id, item_id, borrower_name, borrower_contact, loaned_at, due_date,
returned_at, note, condition_out, condition_in, created_at
FROM loan_log WHERE item_id IN (${inList}) ORDER BY loaned_at DESC, id DESC
`).all(idsJson));
const latestValueByItem = new Map();
for (const { item_id, ...event } of cachedStatement(`
SELECT item_id, amount, note, recorded_at FROM item_value_events
WHERE item_id IN (${inList}) ORDER BY recorded_at DESC, id DESC
`).all(idsJson)) {
if (!latestValueByItem.has(item_id)) latestValueByItem.set(item_id, event);
}
const parentIds = [...new Set(list.map(item => item.parent_item_id).filter(Boolean))];
const parents = new Map(parentIds.length
? cachedStatement(`SELECT id, name, common_name FROM items WHERE id IN (${inList})`)
.all(JSON.stringify(parentIds)).map(row => [row.id, row])
: []);
const logoByBrand = new Map();
for (const brand of cachedStatement('SELECT name, logo_path FROM brands ORDER BY id').all()) {
const key = nocaseKey(brand.name);
if (!logoByBrand.has(key)) logoByBrand.set(key, brand.logo_path);
}
const placementByItem = new Map();
for (const row of cachedStatement(`${MAP_PLACEMENT_SQL} WHERE fi.item_id IN (${inList}) ORDER BY fi.item_id, fi.floorplan_id`).all(idsJson)) {
if (!placementByItem.has(row.item_id)) placementByItem.set(row.item_id, mapPlacementFromRow(row));
}
return list.map(item => {
const attachments = attachmentsByItem.get(item.id) || [];
const loans = loansByItem.get(item.id) || [];
let activeLoan = null;
for (const loan of loans) {
if (loan.returned_at === null && (!activeLoan || loan.id > activeLoan.id)) activeLoan = loan;
}
const brand_logo_path = item.brand ? (logoByBrand.get(nocaseKey(item.brand)) || '') : '';
const photos = attachments.filter(a => a.type === 'photo');
const manuals = attachments.filter(a => a.type === 'manual' || a.type === 'document');
const receipts = attachments.filter(a => a.type === 'receipt');
const enriched = {
...item,
...itemInstrumentData(item),
brand_logo_path,
update_checks_enabled: item.update_checks_enabled !== 0,
requires_power: item.requires_power !== 0,
tags: tagsByItem.get(item.id) || [],
attachments,
photos,
manuals,
software: attachments.filter(a => a.type === 'software'),
receipts,
maintenance: maintenanceByItem.get(item.id) || [],
loans,
activeLoan: enrichLoanRow(activeLoan),
latest_value_event: latestValueByItem.get(item.id) || null,
parent: item.parent_item_id ? (parents.get(item.parent_item_id) || null) : null,
accessories: (childrenByParent.get(item.id) || []).map(child => {
const att = attachmentsByItem.get(child.id) || [];
return {
...child,
...itemInstrumentData(child),
on_insurance_policy: child.on_insurance_policy !== 0,
update_checks_enabled: child.update_checks_enabled !== 0,
requires_power: child.requires_power !== 0,
photos: att.filter(a => a.type === 'photo'),
tags: tagsByItem.get(child.id) || []
};
}),
on_insurance_policy: item.on_insurance_policy !== 0,
completeness: null, // filled in below; keeps its place in the JSON
map_placement: placementByItem.get(item.id) || null,
wall_cutout: parseItemWallCutout(item)
};
// Completeness reads only the serial number, value dates and the photo,
// manual and receipt lists, which the enriched item carries unchanged.
enriched.completeness = computeItemCompleteness(enriched);
return enriched;
});
}
function setItemTags(itemId, tagNames) {
db.prepare('DELETE FROM item_tags WHERE item_id = ?').run(itemId);
const findTag = db.prepare('SELECT id FROM tags WHERE name = ? COLLATE NOCASE');
const insertTag = db.prepare('INSERT OR IGNORE INTO tags (name) VALUES (?)');
const linkTag = db.prepare('INSERT OR IGNORE INTO item_tags (item_id, tag_id) VALUES (?, ?)');
for (const raw of tagNames || []) {
const name = String(raw).trim();
if (!name) continue;
insertTag.run(name);
const tag = findTag.get(name);
if (tag) linkTag.run(itemId, tag.id);
}
}
const FORMER_STATUSES = ['sold', 'stolen', 'destroyed', 'given_away'];
const ACTIVE_STATUSES = ['in_studio', 'loaned', 'in_repair', 'storage', 'away'];
function isFormerStatus(status) {
return FORMER_STATUSES.includes(status);
}
function ownedStatusSql(column = 'studio_status') {
return `${column} NOT IN ('sold','stolen','destroyed','given_away')`;
}
const AUDIT_FIELDS = ['serial_number', 'purchase_price', 'replacement_value', 'quantity', 'studio_status'];
function valuesDiffer(field, before, after) {
if (['purchase_price', 'replacement_value', 'quantity'].includes(field)) {
return Number(before || 0) !== Number(after || 0);
}
return String(before ?? '') !== String(after ?? '');
}
function recordAuditChange(itemId, field, oldValue, newValue) {
db.prepare(`
INSERT INTO item_audit (item_id, field, old_value, new_value, recorded_at)
VALUES (?, ?, ?, ?, ?)
`).run(itemId, field, String(oldValue ?? ''), String(newValue ?? ''), new Date().toISOString());
}
function recordItemChanges(itemId, before, after) {
if (!before || !after) return;
for (const field of AUDIT_FIELDS) {
if (valuesDiffer(field, before[field], after[field])) {
recordAuditChange(itemId, field, before[field], after[field]);
}
}
}
function recordReplacementValue(itemId, amount, note) {
db.prepare(`
INSERT INTO item_value_events (item_id, amount, note, recorded_at)
VALUES (?, ?, ?, ?)
`).run(itemId, Number(amount) || 0, String(note || ''), new Date().toISOString());
}
function getValueEvents(itemId) {
return db.prepare(`
SELECT id, item_id, amount, note, recorded_at
FROM item_value_events WHERE item_id = ?
ORDER BY recorded_at DESC, id DESC
`).all(itemId);
}
function getItemAudit(itemId) {
return db.prepare(`
SELECT id, item_id, field, old_value, new_value, recorded_at
FROM item_audit WHERE item_id = ?
ORDER BY recorded_at DESC, id DESC
`).all(itemId);
}
function cascadeFormerStatus(parentId, status, note, date) {
const children = db.prepare(
'SELECT id, studio_status FROM items WHERE parent_item_id = ?'
).all(parentId);
const update = db.prepare(`
UPDATE items
SET studio_status = ?, studio_status_note = ?, disposition_date = ?, updated_at = datetime('now')
WHERE id = ?
`);
for (const child of children) {
if (isFormerStatus(child.studio_status)) continue;
recordAuditChange(child.id, 'studio_status', child.studio_status, status);
update.run(status, note, date, child.id);
cascadeFormerStatus(child.id, status, note, date);
}
}
function restoreCascadedChildren(parentId, previous) {
const children = db.prepare(`
SELECT id, studio_status, studio_status_note, disposition_date
FROM items WHERE parent_item_id = ?
`).all(parentId);
const update = db.prepare(`
UPDATE items
SET studio_status = ?, studio_status_note = '', disposition_date = '', updated_at = datetime('now')
WHERE id = ?
`);
const priorStatus = db.prepare(`
SELECT old_value FROM item_audit
WHERE item_id = ? AND field = 'studio_status' AND new_value = ?
ORDER BY recorded_at DESC, id DESC LIMIT 1
`);
for (const child of children) {
if (child.studio_status !== previous.studio_status) continue;
if ((child.disposition_date || '') !== (previous.disposition_date || '')) continue;
if ((child.studio_status_note || '') !== (previous.studio_status_note || '')) continue;
const prior = priorStatus.get(child.id, previous.studio_status);
const nextStatus = prior && !isFormerStatus(prior.old_value) ? prior.old_value : 'in_studio';
recordAuditChange(child.id, 'studio_status', child.studio_status, nextStatus);
update.run(nextStatus, child.id);
restoreCascadedChildren(child.id, previous);
}
}
// Largest price or value accepted for one record (well past any real gear).
const MAX_MONEY = 1e9;
function sanitizeItemInput(body) {
const str = (v, max = 2000) => String(v ?? '').trim().slice(0, max);
const num = (v) => {
const n = parseFloat(v);
return Number.isFinite(n) ? Math.min(Math.max(0, n), MAX_MONEY) : 0;
};
const qty = (v) => {
const n = parseInt(v, 10);
return isNaN(n) || n < 1 ? 1 : Math.min(n, 9999);
};
const conditions = ['New', 'Excellent', 'Good', 'Fair', 'Poor'];
const condition = conditions.includes(body.condition) ? body.condition : 'Good';
const updateChecks = body.update_checks_enabled === false || body.update_checks_enabled === 0 || body.update_checks_enabled === '0' ? 0 : 1;
const statuses = [...ACTIVE_STATUSES, ...FORMER_STATUSES];
const studio_status = statuses.includes(body.studio_status) ? body.studio_status : 'in_studio';
const disposition_date = isFormerStatus(studio_status) ? str(body.disposition_date, 20) : '';
if (isFormerStatus(studio_status)) {
if (!/^\d{4}-\d{2}-\d{2}$/.test(disposition_date)) {
throw new Error('A date is required when gear is sold, stolen, destroyed, or given away');
}
if (!str(body.studio_status_note, 500)) {
throw new Error('A short note is required when gear leaves the collection');
}
}
const parentId = body.parent_item_id != null && body.parent_item_id !== ''
? parseInt(body.parent_item_id, 10) : null;
const instrument_type = sanitizeInstrumentType(body.instrument_type);
const instrument_specs = sanitizeInstrumentSpecs(
instrument_type,
body.instrument_specs ?? body.instrument_specs_json
);
return {
name: str(body.name, 300) || 'Unnamed Item',
common_name: str(body.common_name, 300),
category: str(body.category, 100),
instrument_type,
instrument_specs_json: JSON.stringify(instrument_specs),
brand: str(body.brand, 150),
model: str(body.model, 150),
serial_number: str(body.serial_number, 200),
year: str(body.year, 50),
purchase_date: str(body.purchase_date, 20),
purchase_price: num(body.purchase_price),
replacement_value: num(body.replacement_value),
replacement_value_note: str(body.replacement_value_note, 500),
condition,
condition_notes: str(body.condition_notes, 1000),
location: str(body.location, 150),
description: str(body.description, 5000),
quantity: qty(body.quantity),
update_checks_enabled: updateChecks,
warranty_end_date: str(body.warranty_end_date, 20),
warranty_note: str(body.warranty_note, 500),
studio_status,
studio_status_note: str(body.studio_status_note, 500),
disposition_date,
parent_item_id: parentId && !isNaN(parentId) ? parentId : null,
depreciated_value: num(body.depreciated_value),
on_insurance_policy: body.on_insurance_policy === true || body.on_insurance_policy === 1 || body.on_insurance_policy === '1' ? 1 : 0,
insurance_policy_note: str(body.insurance_policy_note, 500),
requires_power: body.requires_power === true || body.requires_power === 1 || body.requires_power === '1' ? 1 : 0,
power_adapter_voltage: str(body.power_adapter_voltage, 100),
power_adapter_current: str(body.power_adapter_current, 100),
power_adapter_polarity: str(body.power_adapter_polarity, 150),
power_adapter_notes: str(body.power_adapter_notes, 1000)
};
}
function getParentSummary(parentId) {
if (!parentId) return null;
const p = db.prepare('SELECT id, name, common_name FROM items WHERE id = ?').get(parentId);
return p || null;
}
function getAccessoryItems(parentId) {
return db.prepare('SELECT * FROM items WHERE parent_item_id = ? ORDER BY name, id').all(parentId);
}
function getAssemblyTotals(itemId) {
const root = db.prepare('SELECT purchase_price,replacement_value,quantity FROM items WHERE id=?').get(itemId);
if (!root) return null;
const components = db.prepare(`
WITH RECURSIVE descendants(id) AS (
SELECT id FROM items WHERE parent_item_id = ?
UNION
SELECT child.id FROM items child JOIN descendants parent ON child.parent_item_id = parent.id
)
SELECT COUNT(*) AS component_count,
COALESCE(SUM(purchase_price * quantity), 0) AS component_purchase,
COALESCE(SUM(replacement_value * quantity), 0) AS component_replacement
FROM items WHERE id IN (SELECT id FROM descendants) AND ${ownedStatusSql('studio_status')}
`).get(itemId);
const itemPurchase = Number(root.purchase_price || 0) * Number(root.quantity || 1);
const itemReplacement = Number(root.replacement_value || 0) * Number(root.quantity || 1);
return {
component_count: Number(components.component_count || 0),
component_purchase: Number(components.component_purchase || 0),
component_replacement: Number(components.component_replacement || 0),
total_purchase: itemPurchase + Number(components.component_purchase || 0),
total_replacement: itemReplacement + Number(components.component_replacement || 0)
};
}
function getRacks() {
const racks = db.prepare('SELECT * FROM racks ORDER BY sort_order, name').all();
return racks.map(rack => ({
...rack,
items: db.prepare(`
SELECT ri.position, ri.slot_label, i.id, i.name, i.brand, i.model, i.category, i.replacement_value
FROM rack_items ri
JOIN items i ON i.id = ri.item_id
WHERE ri.rack_id = ?
ORDER BY ri.position ASC, i.name ASC
`).all(rack.id)
}));
}
function parsePolygonJson(raw) {
try {
const v = JSON.parse(raw || '[]');
return Array.isArray(v) ? v.filter(p => p && Number.isFinite(p.x) && Number.isFinite(p.y)) : [];
} catch {
return [];
}
}
function parseWallLengthsJson(raw) {
try {