BlueGit

timfa questward

Files

questward / server / src / persistence / stateDb.js

stateDb.js

stateDb.js 56.8 KiB Download Raw
1import { openDatabase } from "./database.js";
2import { emptyEquipment, emptySlots, insertStack, insertStackGrowable, normalizeVault, normalizeInventorySlots, publicSlots, migrateBagCoins, migrateInheritanceCoins } from "../../../shared/items/inventory.js";
3import { attachOrigins, godsOrigin } from "../../../shared/items/origin.js";
4import { itemDefinition, ItemId } from "../../../shared/definitions/items.js";
5import { buildStarterBagContents } from "../../../shared/items/starterLoadout.js";
6import { normalizePlayerSkills, defaultPlayerSkills, storedSkillXp } from "../../../shared/skills/state.js";
7import { normalizePlayerSpells, defaultPlayerSpells, storedPlayerSpells } from "../../../shared/spells/state.js";
8import { decayKillCounter } from "../../../shared/combat/skull.js";
9import {
10 applyBountyDailyDecay,
11 bountyDecayDayKey,
12 bountyDecayDaysBetween
13} from "../../../shared/bounty/pricing.js";
14
15const STATE_SCHEMA = `
16CREATE TABLE IF NOT EXISTS accounts (
17 account_id INTEGER PRIMARY KEY AUTOINCREMENT,
18 auth_subject TEXT NOT NULL UNIQUE,
19 email TEXT,
20 is_admin INTEGER NOT NULL DEFAULT 0
21);
22
23CREATE TABLE IF NOT EXISTS surname_claims (
24 surname_normalized TEXT PRIMARY KEY,
25 account_id INTEGER NOT NULL
26);
27
28CREATE TABLE IF NOT EXISTS characters (
29 character_id INTEGER PRIMARY KEY AUTOINCREMENT,
30 account_id INTEGER NOT NULL,
31 first_name TEXT NOT NULL,
32 last_name TEXT NOT NULL,
33 first_normalized TEXT NOT NULL,
34 last_normalized TEXT NOT NULL,
35 sex TEXT NOT NULL,
36 tile_x INTEGER NOT NULL DEFAULT 8,
37 tile_y INTEGER NOT NULL DEFAULT 8,
38 gait TEXT NOT NULL DEFAULT 'walk',
39 is_dead INTEGER NOT NULL DEFAULT 0,
40 deletion_scheduled_at INTEGER,
41 hair_style TEXT,
42 hair_hue REAL NOT NULL DEFAULT 30,
43 hair_saturation REAL NOT NULL DEFAULT 0.55,
44 hair_darkness REAL NOT NULL DEFAULT 0.45
45);
46CREATE TABLE IF NOT EXISTS friend_requests (
47 request_id INTEGER PRIMARY KEY AUTOINCREMENT,
48 from_character_id INTEGER NOT NULL,
49 to_character_id INTEGER NOT NULL,
50 created_at INTEGER NOT NULL,
51 UNIQUE (from_character_id, to_character_id)
52);
53
54CREATE TABLE IF NOT EXISTS friendships (
55 character_id_a INTEGER NOT NULL,
56 character_id_b INTEGER NOT NULL,
57 created_at INTEGER NOT NULL,
58 PRIMARY KEY (character_id_a, character_id_b)
59);
60
61CREATE TABLE IF NOT EXISTS persistent_entities (
62 entity_id INTEGER PRIMARY KEY,
63 tile_x INTEGER NOT NULL,
64 tile_y INTEGER NOT NULL,
65 components_blob TEXT NOT NULL
66);
67
68CREATE TABLE IF NOT EXISTS character_bags (
69 character_id INTEGER PRIMARY KEY,
70 inventory_blob TEXT NOT NULL,
71 equipment_blob TEXT NOT NULL,
72 currency INTEGER NOT NULL DEFAULT 0,
73 vault_blob TEXT NOT NULL,
74 vault_currency INTEGER NOT NULL DEFAULT 0,
75 skills_blob TEXT NOT NULL DEFAULT '{}'
76);
77
78CREATE TABLE IF NOT EXISTS account_inheritance (
79 account_id INTEGER PRIMARY KEY,
80 slots_blob TEXT NOT NULL,
81 currency INTEGER NOT NULL DEFAULT 0
82);
83
84CREATE TABLE IF NOT EXISTS ground_items (
85 ground_item_id INTEGER PRIMARY KEY,
86 tile_x INTEGER NOT NULL,
87 tile_y INTEGER NOT NULL,
88 stack_blob TEXT NOT NULL,
89 dropped_at INTEGER NOT NULL,
90 expires_at INTEGER NOT NULL,
91 source TEXT NOT NULL,
92 dropped_by_character_id INTEGER
93);
94
95CREATE TABLE IF NOT EXISTS courier_transits (
96 courier_id INTEGER PRIMARY KEY AUTOINCREMENT,
97 sender_character_id INTEGER NOT NULL,
98 destination_kind TEXT NOT NULL,
99 destination_character_id INTEGER,
100 cargo_blob TEXT NOT NULL,
101 origin_x REAL NOT NULL,
102 origin_y REAL NOT NULL,
103 dest_x REAL NOT NULL,
104 dest_y REAL NOT NULL,
105 traveled REAL NOT NULL DEFAULT 0,
106 status TEXT NOT NULL,
107 entity_id INTEGER,
108 updated_at INTEGER NOT NULL,
109 created_at INTEGER NOT NULL
110);
111
112CREATE TABLE IF NOT EXISTS market_orders (
113 order_id INTEGER PRIMARY KEY AUTOINCREMENT,
114 character_id INTEGER NOT NULL,
115 side TEXT NOT NULL,
116 item_id INTEGER NOT NULL,
117 quantity INTEGER NOT NULL,
118 limit_unit_price INTEGER NOT NULL,
119 escrow_coins INTEGER NOT NULL DEFAULT 0,
120 escrow_stack_blob TEXT,
121 listing_key TEXT,
122 created_at INTEGER NOT NULL,
123 status TEXT NOT NULL
124);
125
126CREATE TABLE IF NOT EXISTS market_trades (
127 trade_id INTEGER PRIMARY KEY AUTOINCREMENT,
128 buy_order_id INTEGER NOT NULL,
129 sell_order_id INTEGER NOT NULL,
130 item_id INTEGER NOT NULL,
131 quantity INTEGER NOT NULL,
132 unit_price INTEGER NOT NULL,
133 buyer_character_id INTEGER NOT NULL,
134 seller_character_id INTEGER NOT NULL,
135 created_at INTEGER NOT NULL
136);
137
138CREATE TABLE IF NOT EXISTS item_last_sales (
139 item_id INTEGER PRIMARY KEY,
140 last_trade_price INTEGER NOT NULL,
141 last_trade_at INTEGER NOT NULL,
142 price_from INTEGER NOT NULL
143);
144
145CREATE TABLE IF NOT EXISTS bank_rest_notices (
146 notice_id INTEGER PRIMARY KEY AUTOINCREMENT,
147 character_id INTEGER NOT NULL,
148 item_id INTEGER NOT NULL,
149 item_name TEXT NOT NULL,
150 quantity INTEGER NOT NULL,
151 source_name TEXT NOT NULL,
152 created_at INTEGER NOT NULL
153);
154
155CREATE TABLE IF NOT EXISTS held_courier_deliveries (
156 hold_id INTEGER PRIMARY KEY AUTOINCREMENT,
157 character_id INTEGER NOT NULL,
158 sender_character_id INTEGER NOT NULL,
159 origin_tile_x INTEGER NOT NULL,
160 origin_tile_y INTEGER NOT NULL,
161 destination_kind TEXT NOT NULL,
162 destination_character_id INTEGER,
163 vault_character_id INTEGER,
164 cargo_blob TEXT NOT NULL,
165 created_at INTEGER NOT NULL
166);
167
168CREATE TABLE IF NOT EXISTS character_bounties (
169 character_id INTEGER PRIMARY KEY,
170 amount INTEGER NOT NULL DEFAULT 0,
171 last_decay_day TEXT
172);
173
174CREATE TABLE IF NOT EXISTS character_quest_state (
175 character_id INTEGER NOT NULL,
176 quest_id TEXT NOT NULL,
177 state_value TEXT NOT NULL,
178 progress_blob TEXT NOT NULL DEFAULT '{}',
179 started_at INTEGER,
180 completed_at INTEGER,
181 PRIMARY KEY (character_id, quest_id)
182);
183
184CREATE TABLE IF NOT EXISTS character_dialogue_heard (
185 character_id INTEGER NOT NULL,
186 dialogue_id TEXT NOT NULL,
187 step_id TEXT NOT NULL,
188 heard_at INTEGER NOT NULL,
189 PRIMARY KEY (character_id, dialogue_id, step_id)
190);
191
192CREATE TABLE IF NOT EXISTS character_quest_journal (
193 entry_id INTEGER PRIMARY KEY AUTOINCREMENT,
194 character_id INTEGER NOT NULL,
195 quest_id TEXT NOT NULL,
196 step_id TEXT NOT NULL,
197 text TEXT NOT NULL,
198 written_at INTEGER NOT NULL
199);
200
201CREATE TABLE IF NOT EXISTS character_dialogue_triggers (
202 character_id INTEGER NOT NULL,
203 dialogue_id TEXT NOT NULL,
204 trigger_id TEXT NOT NULL,
205 latched_at INTEGER NOT NULL,
206 PRIMARY KEY (character_id, dialogue_id, trigger_id)
207);
208
209CREATE TABLE IF NOT EXISTS character_visited_dimensions (
210 character_id INTEGER NOT NULL,
211 dimension_id TEXT NOT NULL,
212 first_visited_at INTEGER NOT NULL,
213 PRIMARY KEY (character_id, dimension_id)
214);
215`;
216
217export class StateDatabase
218{
219 constructor(filePath)
220 {
221 this.db = openDatabase(filePath);
222 this.db.exec(STATE_SCHEMA);
223 this.#migrateSchema();
224 }
225
226 #migrateSchema ()
227 {
228 const bagColumns = this.db.prepare("PRAGMA table_info(character_bags)").all();
229 if (!bagColumns.some((column) => column.name === "skills_blob"))
230 {
231 this.db.exec("ALTER TABLE character_bags ADD COLUMN skills_blob TEXT NOT NULL DEFAULT '{}'");
232 }
233 const characterColumns = this.db.prepare("PRAGMA table_info(characters)").all();
234 if (!characterColumns.some((column) => column.name === "is_dead"))
235 { this.db.exec("ALTER TABLE characters ADD COLUMN is_dead INTEGER NOT NULL DEFAULT 0"); }
236 if (!characterColumns.some((column) => column.name === "hair_style"))
237 {
238 this.db.exec("ALTER TABLE characters ADD COLUMN hair_style TEXT");
239 this.db.exec("ALTER TABLE characters ADD COLUMN hair_hue REAL NOT NULL DEFAULT 30");
240 this.db.exec("ALTER TABLE characters ADD COLUMN hair_saturation REAL NOT NULL DEFAULT 0.55");
241 this.db.exec("ALTER TABLE characters ADD COLUMN hair_darkness REAL NOT NULL DEFAULT 0.45");
242 }
243 if (!characterColumns.some((column) => column.name === "dimension_id"))
244 { this.db.exec("ALTER TABLE characters ADD COLUMN dimension_id TEXT NOT NULL DEFAULT 'overworld'"); }
245 this.db.exec(`
246 CREATE TABLE IF NOT EXISTS character_visited_dimensions (
247 character_id INTEGER NOT NULL,
248 dimension_id TEXT NOT NULL,
249 first_visited_at INTEGER NOT NULL,
250 PRIMARY KEY (character_id, dimension_id)
251 )
252 `);
253 const courierColumns = this.db.prepare("PRAGMA table_info(courier_transits)").all();
254 if (!courierColumns.some((column) => column.name === "vault_character_id"))
255 { this.db.exec("ALTER TABLE courier_transits ADD COLUMN vault_character_id INTEGER"); }
256 if (!bagColumns.some((column) => column.name === "spells_blob"))
257 {
258 this.db.exec("ALTER TABLE character_bags ADD COLUMN spells_blob TEXT NOT NULL DEFAULT '{}'");
259 }
260 if (!characterColumns.some((column) => column.name === "health_current"))
261 { this.db.exec("ALTER TABLE characters ADD COLUMN health_current INTEGER"); }
262 const accountColumns = this.db.prepare("PRAGMA table_info(accounts)").all();
263 if (!accountColumns.some((column) => column.name === "username"))
264 { this.db.exec("ALTER TABLE accounts ADD COLUMN username TEXT"); }
265 if (!characterColumns.some((column) => column.name === "kill_counter"))
266 { this.db.exec("ALTER TABLE characters ADD COLUMN kill_counter REAL NOT NULL DEFAULT 0"); }
267 if (!characterColumns.some((column) => column.name === "kill_counter_at"))
268 { this.db.exec("ALTER TABLE characters ADD COLUMN kill_counter_at INTEGER"); }
269 if (!characterColumns.some((column) => column.name === "cowardice_forgiven_hour"))
270 { this.db.exec("ALTER TABLE characters ADD COLUMN cowardice_forgiven_hour INTEGER"); }
271 const marketColumns = this.db.prepare("PRAGMA table_info(market_orders)").all();
272 if (!marketColumns.some((column) => column.name === "listing_key"))
273 { this.db.exec("ALTER TABLE market_orders ADD COLUMN listing_key TEXT"); }
274 const bountyColumns = this.db.prepare("PRAGMA table_info(character_bounties)").all();
275 if (!bountyColumns.some((column) => column.name === "last_decay_day"))
276 { this.db.exec("ALTER TABLE character_bounties ADD COLUMN last_decay_day TEXT"); }
277 this.db.exec(`
278 CREATE TABLE IF NOT EXISTS account_friend_requests (
279 request_id INTEGER PRIMARY KEY AUTOINCREMENT,
280 from_account_id INTEGER NOT NULL,
281 to_account_id INTEGER NOT NULL,
282 created_at INTEGER NOT NULL,
283 UNIQUE (from_account_id, to_account_id)
284 )
285 `);
286 this.db.exec(`
287 CREATE TABLE IF NOT EXISTS account_friendships (
288 account_id_a INTEGER NOT NULL,
289 account_id_b INTEGER NOT NULL,
290 created_at INTEGER NOT NULL,
291 PRIMARY KEY (account_id_a, account_id_b)
292 )
293 `);
294 this.#migrateCharacterFriendsToAccounts();
295 this.db.exec(`
296 CREATE TABLE IF NOT EXISTS auth_sessions (
297 session_id TEXT PRIMARY KEY,
298 auth_subject TEXT NOT NULL,
299 username TEXT,
300 email TEXT,
301 access_token TEXT,
302 expires_at INTEGER NOT NULL
303 )
304 `);
305 this.db.exec(`
306 CREATE INDEX IF NOT EXISTS auth_sessions_subject ON auth_sessions(auth_subject)
307 `);
308 this.#grantLanternsToExistingCharacters();
309 }
310
311 /** One-time: lift legacy character friendships / requests to account scope. */
312 #migrateCharacterFriendsToAccounts ()
313 {
314 const legacyFriends = this.db.prepare("SELECT character_id_a, character_id_b, created_at FROM friendships").all();
315 const insertFriend = this.db.prepare(`
316 INSERT OR IGNORE INTO account_friendships (account_id_a, account_id_b, created_at)
317 VALUES (?, ?, ?)
318 `);
319 for (const row of legacyFriends)
320 {
321 const a = this.getCharacter(row.character_id_a);
322 const b = this.getCharacter(row.character_id_b);
323 if (!a || !b || a.account_id === b.account_id)
324 { continue; }
325 const [idA, idB] = pairIds(a.account_id, b.account_id);
326 insertFriend.run(idA, idB, row.created_at | 0 || Date.now());
327 }
328 const legacyRequests = this.db.prepare(`
329 SELECT from_character_id, to_character_id, created_at FROM friend_requests
330 `).all();
331 const insertReq = this.db.prepare(`
332 INSERT OR IGNORE INTO account_friend_requests (from_account_id, to_account_id, created_at)
333 VALUES (?, ?, ?)
334 `);
335 for (const row of legacyRequests)
336 {
337 const from = this.getCharacter(row.from_character_id);
338 const to = this.getCharacter(row.to_character_id);
339 if (!from || !to || from.account_id === to.account_id)
340 { continue; }
341 insertReq.run(from.account_id, to.account_id, row.created_at | 0 || Date.now());
342 }
343 if (legacyFriends.length || legacyRequests.length)
344 {
345 this.db.exec("DELETE FROM friendships");
346 this.db.exec("DELETE FROM friend_requests");
347 }
348 }
349
350 createAuthSession ({ sessionId, authSubject, username = null, email = null, accessToken = null, expiresAtMs })
351 {
352 this.db.prepare(`
353 INSERT INTO auth_sessions (session_id, auth_subject, username, email, access_token, expires_at)
354 VALUES (?, ?, ?, ?, ?, ?)
355 `).run(sessionId, authSubject, username, email, accessToken, expiresAtMs);
356 }
357
358 getAuthSession (sessionId)
359 {
360 return this.db.prepare(`
361 SELECT session_id, auth_subject, username, email, access_token, expires_at
362 FROM auth_sessions
363 WHERE session_id = ?
364 `).get(sessionId) ?? null;
365 }
366
367 deleteAuthSession (sessionId)
368 {
369 if (!sessionId)
370 { return; }
371 this.db.prepare("DELETE FROM auth_sessions WHERE session_id = ?").run(sessionId);
372 }
373
374 #grantLanternsToExistingCharacters ()
375 {
376 const def = itemDefinition(ItemId.lantern);
377 if (!def)
378 { return; }
379 const rows = this.db.prepare("SELECT character_id, inventory_blob, equipment_blob FROM character_bags").all();
380 const update = this.db.prepare(`
381 UPDATE character_bags
382 SET inventory_blob = ?, equipment_blob = ?
383 WHERE character_id = ?
384 `);
385 for (const row of rows)
386 {
387 const inventory = JSON.parse(row.inventory_blob || "[]");
388 const equipment = JSON.parse(row.equipment_blob || "{}");
389 if (bagContainsItem(inventory, equipment, ItemId.lantern))
390 { continue; }
391 if (!equipment.slots?.offhand)
392 {
393 equipment.slots = equipment.slots ?? {};
394 equipment.slots.offhand = attachOrigins({
395 itemId: ItemId.lantern,
396 quantity: 1,
397 origins: [{ origin: godsOrigin(), quantity: 1 }]
398 });
399 }
400 else
401 {
402 const result = insertStack(inventory, attachOrigins({
403 itemId: ItemId.lantern,
404 quantity: 1,
405 origins: [{ origin: godsOrigin(), quantity: 1 }]
406 }), def);
407 if (!result.ok)
408 { continue; }
409 inventory.splice(0, inventory.length, ...result.slots);
410 }
411 update.run(JSON.stringify(inventory), JSON.stringify(equipment), row.character_id);
412 }
413 }
414
415 getAccountBySubject (authSubject)
416 {
417 return this.db.prepare("SELECT * FROM accounts WHERE auth_subject = ?").get(authSubject) ?? null;
418 }
419
420 countAccounts ()
421 {
422 return this.db.prepare("SELECT COUNT(*) AS count FROM accounts").get().count;
423 }
424
425 countGameAdmins ()
426 {
427 return this.db.prepare("SELECT COUNT(*) AS count FROM accounts WHERE is_admin = 1").get().count;
428 }
429
430 findAccountByUsername (username)
431 {
432 const normalized = String(username ?? "").trim().toLowerCase();
433 if (!normalized)
434 { return null; }
435 return this.db.prepare(`
436 SELECT * FROM accounts
437 WHERE lower(username) = ?
438 ORDER BY account_id ASC
439 LIMIT 1
440 `).get(normalized) ?? null;
441 }
442
443 listGameAdmins ()
444 {
445 return this.db.prepare(`
446 SELECT account_id, auth_subject, username, email, is_admin
447 FROM accounts
448 WHERE is_admin = 1
449 ORDER BY account_id ASC
450 `).all();
451 }
452
453 #syncAccountProfile (accountId, { email, username })
454 {
455 const fields = [];
456 const values = [];
457 if (email != null)
458 {
459 fields.push("email = ?");
460 values.push(email);
461 }
462 if (username != null)
463 {
464 fields.push("username = ?");
465 values.push(username);
466 }
467 if (!fields.length)
468 { return; }
469 values.push(accountId);
470 this.db.prepare(`UPDATE accounts SET ${fields.join(", ")} WHERE account_id = ?`).run(...values);
471 }
472
473 resolveAccountFromAuth ({ authSubject, email = null, username = null })
474 {
475 const existing = this.getAccountBySubject(authSubject);
476 if (existing)
477 {
478 this.#syncAccountProfile(existing.account_id, { email, username });
479 return this.getAccountBySubject(authSubject);
480 }
481 const isFirstAccount = this.countAccounts() === 0;
482 return this.createAccount(authSubject, isFirstAccount, email, username);
483 }
484
485 setGameAdmin (authSubject, isAdmin)
486 {
487 const account = this.getAccountBySubject(authSubject);
488 if (!account)
489 {
490 const error = new Error("Account not found.");
491 error.userFacing = true;
492 throw error;
493 }
494 if (!isAdmin && account.is_admin && this.countGameAdmins() <= 1)
495 {
496 const error = new Error("Cannot remove the last game admin.");
497 error.userFacing = true;
498 throw error;
499 }
500 this.db.prepare("UPDATE accounts SET is_admin = ? WHERE auth_subject = ?").run(isAdmin ? 1 : 0, authSubject);
501 return this.getAccountBySubject(authSubject);
502 }
503
504 grantGameAdminByUsername (username)
505 {
506 const account = this.findAccountByUsername(username);
507 if (!account)
508 {
509 const error = new Error("No account with that username has signed in yet.");
510 error.userFacing = true;
511 throw error;
512 }
513 return this.setGameAdmin(account.auth_subject, true);
514 }
515
516 createAccount (authSubject, isAdmin, email = null, username = null)
517 {
518 this.db.prepare(`
519 INSERT INTO accounts (auth_subject, email, username, is_admin)
520 VALUES (?, ?, ?, ?)
521 `).run(
522 authSubject,
523 email,
524 username,
525 isAdmin ? 1 : 0
526 );
527 return this.getAccountBySubject(authSubject);
528 }
529
530 ensureAccount (authSubject, isAdmin, email = null, username = null)
531 {
532 const existing = this.getAccountBySubject(authSubject);
533 if (existing)
534 {
535 this.#syncAccountProfile(existing.account_id, { email, username });
536 return existing;
537 }
538
539 return this.createAccount(authSubject, isAdmin, email, username);
540 }
541
542 listCharacters (accountId)
543 {
544 return this.db.prepare(`
545 SELECT * FROM characters
546 WHERE account_id = ?
547 ORDER BY character_id ASC
548 `).all(accountId);
549 }
550
551 getCharacter (characterId)
552 {
553 return this.db.prepare("SELECT * FROM characters WHERE character_id = ?").get(characterId) ?? null;
554 }
555
556 /** All character ids (for world-settings fan-out such as initial quests). */
557 listAllCharacterIds ()
558 {
559 return this.db.prepare("SELECT character_id FROM characters").all()
560 .map((row) => row.character_id | 0)
561 .filter((id) => id > 0);
562 }
563
564 findSurnameClaim (surnameNormalized)
565 {
566 return this.db.prepare("SELECT * FROM surname_claims WHERE surname_normalized = ?").get(surnameNormalized) ?? null;
567 }
568
569 createCharacter (accountId, firstName, lastName, sex, tileX, tileY, hair = null, starterSettings = null, creationClothing = null)
570 {
571 const firstNormalized = normalizeName(firstName);
572 const lastNormalized = normalizeName(lastName);
573 const hairStyle = hair?.style ?? null;
574 const hairHue = Number(hair?.hue) || 30;
575 const hairSaturation = Number(hair?.saturation);
576 const hairDarkness = Number(hair?.darkness);
577 this.db.exec("BEGIN");
578 try
579 {
580 this.db.prepare(`
581 INSERT INTO characters (
582 account_id, first_name, last_name, first_normalized, last_normalized, sex, tile_x, tile_y,
583 dimension_id, hair_style, hair_hue, hair_saturation, hair_darkness
584 ) VALUES (?, ?, ?, ?, ?, ?, ?, ?, 'overworld', ?, ?, ?, ?)
585 `).run(
586 accountId,
587 firstName,
588 lastName,
589 firstNormalized,
590 lastNormalized,
591 sex,
592 tileX,
593 tileY,
594 hairStyle,
595 hairHue,
596 Number.isFinite(hairSaturation) ? hairSaturation : 0.55,
597 Number.isFinite(hairDarkness) ? hairDarkness : 0.45
598 );
599 const characterId = Number(this.db.prepare("SELECT last_insert_rowid() AS id").get().id);
600
601 const claim = this.findSurnameClaim(lastNormalized);
602 if (!claim)
603 {
604 this.db.prepare("INSERT INTO surname_claims (surname_normalized, account_id) VALUES (?, ?)").run(
605 lastNormalized,
606 accountId
607 );
608 }
609
610 this.db.exec("COMMIT");
611 this.#ensureBag(characterId, starterSettings, creationClothing);
612 return this.getCharacter(characterId);
613 }
614 catch (error)
615 {
616 this.db.exec("ROLLBACK");
617 throw error;
618 }
619 }
620
621 updateCharacterHair (characterId, { style, hue, saturation, darkness })
622 {
623 this.db.prepare(`
624 UPDATE characters
625 SET hair_style = ?, hair_hue = ?, hair_saturation = ?, hair_darkness = ?
626 WHERE character_id = ?
627 `).run(
628 style ?? null,
629 Number(hue) || 30,
630 Number.isFinite(Number(saturation)) ? Number(saturation) : 0.55,
631 Number.isFinite(Number(darkness)) ? Number(darkness) : 0.45,
632 characterId | 0
633 );
634 return this.getCharacter(characterId);
635 }
636
637 scheduleDeletion (characterId, at)
638 {
639 this.db.prepare("UPDATE characters SET deletion_scheduled_at = ? WHERE character_id = ?").run(at, characterId);
640 }
641
642 cancelDeletion (characterId)
643 {
644 this.db.prepare("UPDATE characters SET deletion_scheduled_at = NULL WHERE character_id = ?").run(characterId);
645 }
646
647 deleteCharacter (characterId)
648 {
649 const character = this.getCharacter(characterId);
650 if (!character)
651 { return; }
652
653 this.db.exec("BEGIN");
654 try
655 {
656 this.#moveGoodsToInheritance(character);
657 this.db.prepare("DELETE FROM character_bags WHERE character_id = ?").run(characterId);
658 this.db.prepare("DELETE FROM characters WHERE character_id = ?").run(characterId);
659 this.db.prepare("DELETE FROM character_bounties WHERE character_id = ?").run(characterId);
660 // Account friendships persist; only clear legacy character rows if any remain.
661 this.db.prepare("DELETE FROM friend_requests WHERE from_character_id = ? OR to_character_id = ?").run(
662 characterId,
663 characterId
664 );
665 this.db.prepare("DELETE FROM friendships WHERE character_id_a = ? OR character_id_b = ?").run(
666 characterId,
667 characterId
668 );
669 const remaining = this.db.prepare(`
670 SELECT COUNT(*) AS count FROM characters
671 WHERE account_id = ? AND last_normalized = ?
672 `).get(character.account_id, character.last_normalized);
673 if (!remaining.count)
674 {
675 this.db.prepare("DELETE FROM surname_claims WHERE surname_normalized = ? AND account_id = ?").run(
676 character.last_normalized,
677 character.account_id
678 );
679 }
680 this.db.exec("COMMIT");
681 }
682 catch (error)
683 {
684 this.db.exec("ROLLBACK");
685 throw error;
686 }
687 }
688
689 saveCharacterPosition (characterId, tileX, tileY, gait, dimensionId = "overworld")
690 {
691 this.db.prepare(`
692 UPDATE characters SET tile_x = ?, tile_y = ?, gait = ?, dimension_id = ?
693 WHERE character_id = ?
694 `).run(
695 tileX,
696 tileY,
697 gait,
698 String(dimensionId ?? "overworld"),
699 characterId
700 );
701 }
702
703 markDimensionVisited (characterId, dimensionId, now = Date.now())
704 {
705 const dim = String(dimensionId ?? "overworld");
706 this.db.prepare(`
707 INSERT INTO character_visited_dimensions (character_id, dimension_id, first_visited_at)
708 VALUES (?, ?, ?)
709 ON CONFLICT(character_id, dimension_id) DO NOTHING
710 `).run(characterId | 0, dim, now);
711 }
712
713 hasVisitedDimension (characterId, dimensionId)
714 {
715 const row = this.db.prepare(`
716 SELECT 1 AS ok FROM character_visited_dimensions
717 WHERE character_id = ? AND dimension_id = ?
718 `).get(characterId | 0, String(dimensionId ?? "overworld"));
719 return !!row;
720 }
721
722 listVisitedDimensions (characterId)
723 {
724 return this.db.prepare(`
725 SELECT dimension_id, first_visited_at
726 FROM character_visited_dimensions
727 WHERE character_id = ?
728 ORDER BY first_visited_at ASC
729 `).all(characterId | 0).map((row) => ({
730 dimensionId: row.dimension_id,
731 firstVisitedAt: row.first_visited_at
732 }));
733 }
734
735 saveCharacterHealth (characterId, current)
736 {
737 this.db.prepare("UPDATE characters SET health_current = ? WHERE character_id = ?").run(
738 Math.max(0, current | 0),
739 characterId
740 );
741 }
742
743 setCharacterDead (characterId, isDead)
744 {
745 this.db.prepare("UPDATE characters SET is_dead = ? WHERE character_id = ?").run(
746 isDead ? 1 : 0,
747 characterId
748 );
749 }
750
751 listDueDeletions (now)
752 {
753 return this.db.prepare(`
754 SELECT * FROM characters
755 WHERE deletion_scheduled_at IS NOT NULL AND deletion_scheduled_at <= ?
756 `).all(now);
757 }
758
759 getCharacterByName (firstName, lastName)
760 {
761 return this.db.prepare(`
762 SELECT * FROM characters
763 WHERE first_normalized = ? AND last_normalized = ?
764 `).get(normalizeName(firstName), normalizeName(lastName)) ?? null;
765 }
766
767 /**
768 * Admin gift search: match first name, last name, "first last", or account username.
769 * @param {string} query
770 * @param {number} [limit]
771 */
772 searchCharactersForGift (query, limit = 40)
773 {
774 const raw = String(query ?? "").trim();
775 if (!raw)
776 { return []; }
777 const cap = Math.min(100, Math.max(1, limit | 0 || 40));
778 const needle = `%${normalizeName(raw).replace(/%/g, "")}%`;
779 const parts = raw.split(/\s+/u).filter(Boolean);
780 const firstPart = parts[0] ? normalizeName(parts[0]) : "";
781 const lastPart = parts.length >= 2 ? normalizeName(parts.slice(1).join(" ")) : "";
782
783 if (parts.length >= 2 && firstPart && lastPart)
784 {
785 return this.db.prepare(`
786 SELECT
787 c.character_id,
788 c.first_name,
789 c.last_name,
790 c.account_id,
791 a.username AS account_username
792 FROM characters c
793 LEFT JOIN accounts a ON a.account_id = c.account_id
794 WHERE (c.first_normalized = ? AND c.last_normalized = ?)
795 OR c.first_normalized LIKE ?
796 OR c.last_normalized LIKE ?
797 OR lower(COALESCE(a.username, '')) LIKE ?
798 OR (c.first_normalized || ' ' || c.last_normalized) LIKE ?
799 ORDER BY c.last_normalized ASC, c.first_normalized ASC, c.character_id ASC
800 LIMIT ?
801 `).all(firstPart, lastPart, needle, needle, needle, needle, cap);
802 }
803
804 return this.db.prepare(`
805 SELECT
806 c.character_id,
807 c.first_name,
808 c.last_name,
809 c.account_id,
810 a.username AS account_username
811 FROM characters c
812 LEFT JOIN accounts a ON a.account_id = c.account_id
813 WHERE c.first_normalized LIKE ?
814 OR c.last_normalized LIKE ?
815 OR lower(COALESCE(a.username, '')) LIKE ?
816 OR (c.first_normalized || ' ' || c.last_normalized) LIKE ?
817 ORDER BY c.last_normalized ASC, c.first_normalized ASC, c.character_id ASC
818 LIMIT ?
819 `).all(needle, needle, needle, needle, cap);
820 }
821
822 getCharacterBounty (characterId, now = Date.now())
823 {
824 return this.applyCharacterBountyDecay(characterId, now);
825 }
826
827 /**
828 * Daily −1 bounty decay (lazy). First touch after migration stamps today without retroactive decay.
829 * Never moves `last_decay_day` backward (simulation clocks in tests can be epoch-relative).
830 */
831 applyCharacterBountyDecay (characterId, now = Date.now())
832 {
833 const id = characterId | 0;
834 const row = this.db.prepare(`
835 SELECT amount, last_decay_day FROM character_bounties WHERE character_id = ?
836 `).get(id);
837 if (!row)
838 { return 0; }
839 const day = bountyDecayDayKey(now);
840 let amount = Math.max(0, row.amount | 0);
841 const last = row.last_decay_day ? String(row.last_decay_day) : "";
842 if (!last)
843 {
844 this.db.prepare(`
845 UPDATE character_bounties SET last_decay_day = ? WHERE character_id = ?
846 `).run(day, id);
847 return amount;
848 }
849 if (last === day)
850 { return amount; }
851 const days = bountyDecayDaysBetween(last, day);
852 if (days <= 0)
853 {
854 // `now` is on/before the stamped day (clock skew / harness) — leave the stamp alone.
855 return amount;
856 }
857 amount = applyBountyDailyDecay(amount, days);
858 this.db.prepare(`
859 UPDATE character_bounties SET amount = ?, last_decay_day = ? WHERE character_id = ?
860 `).run(amount, day, id);
861 return amount;
862 }
863
864 /** Add coins to a bounty; returns the new total. */
865 addCharacterBounty (characterId, amount, now = Date.now())
866 {
867 const id = characterId | 0;
868 const add = Math.max(0, amount | 0);
869 if (add <= 0)
870 { return this.getCharacterBounty(id, now); }
871 const day = bountyDecayDayKey(now);
872 this.applyCharacterBountyDecay(id, now);
873 this.db.prepare(`
874 INSERT INTO character_bounties (character_id, amount, last_decay_day) VALUES (?, ?, ?)
875 ON CONFLICT(character_id) DO UPDATE SET amount = amount + excluded.amount
876 `).run(id, add, day);
877 return this.getCharacterBounty(id, now);
878 }
879
880 /** Reduce bounty by up to `amount`; returns the new total. */
881 reduceCharacterBounty (characterId, amount, now = Date.now())
882 {
883 const id = characterId | 0;
884 const cut = Math.max(0, amount | 0);
885 const current = this.getCharacterBounty(id, now);
886 if (cut <= 0)
887 { return current; }
888 const next = Math.max(0, current - cut);
889 const day = bountyDecayDayKey(now);
890 this.db.prepare(`
891 INSERT INTO character_bounties (character_id, amount, last_decay_day) VALUES (?, ?, ?)
892 ON CONFLICT(character_id) DO UPDATE SET amount = excluded.amount
893 `).run(id, next, day);
894 return next;
895 }
896
897 /** Zero out and return the previous bounty amount. */
898 takeCharacterBounty (characterId, now = Date.now())
899 {
900 const id = characterId | 0;
901 const amount = this.getCharacterBounty(id, now);
902 if (amount <= 0)
903 { return 0; }
904 const day = bountyDecayDayKey(now);
905 this.db.prepare(`
906 INSERT INTO character_bounties (character_id, amount, last_decay_day) VALUES (?, 0, ?)
907 ON CONFLICT(character_id) DO UPDATE SET amount = 0
908 `).run(id, day);
909 return amount;
910 }
911
912 getQuestState (characterId, questId)
913 {
914 const row = this.db.prepare(`
915 SELECT state_value, progress_blob, started_at, completed_at
916 FROM character_quest_state
917 WHERE character_id = ? AND quest_id = ?
918 `).get(characterId | 0, String(questId));
919 if (!row)
920 { return { state: "not-started", progress: {}, startedAt: null, completedAt: null }; }
921 const progress = JSON.parse(row.progress_blob || "{}");
922 let state = row.state_value;
923 if (state !== "not-started" && state !== "completed")
924 { state = Number(state) | 0; }
925 return {
926 state,
927 progress,
928 startedAt: row.started_at ?? null,
929 completedAt: row.completed_at ?? null
930 };
931 }
932
933 listQuestStates (characterId)
934 {
935 return this.db.prepare(`
936 SELECT quest_id, state_value, progress_blob, started_at, completed_at
937 FROM character_quest_state
938 WHERE character_id = ?
939 `).all(characterId | 0).map((row) =>
940 {
941 let state = row.state_value;
942 if (state !== "not-started" && state !== "completed")
943 { state = Number(state) | 0; }
944 return {
945 questId: row.quest_id,
946 state,
947 progress: JSON.parse(row.progress_blob || "{}"),
948 startedAt: row.started_at ?? null,
949 completedAt: row.completed_at ?? null
950 };
951 });
952 }
953
954 saveQuestState (characterId, questId, state, progress = {}, startedAt = null, completedAt = null)
955 {
956 const stateValue = state === "not-started" || state === "completed" ? state : String(state | 0);
957 this.db.prepare(`
958 INSERT INTO character_quest_state
959 (character_id, quest_id, state_value, progress_blob, started_at, completed_at)
960 VALUES (?, ?, ?, ?, ?, ?)
961 ON CONFLICT(character_id, quest_id) DO UPDATE SET
962 state_value = excluded.state_value,
963 progress_blob = excluded.progress_blob,
964 started_at = excluded.started_at,
965 completed_at = excluded.completed_at
966 `).run(
967 characterId | 0,
968 String(questId),
969 stateValue,
970 JSON.stringify(progress ?? {}),
971 startedAt,
972 completedAt
973 );
974 }
975
976 isDialogueHeard (characterId, dialogueId, stepId)
977 {
978 const row = this.db.prepare(`
979 SELECT 1 FROM character_dialogue_heard
980 WHERE character_id = ? AND dialogue_id = ? AND step_id = ?
981 `).get(characterId | 0, String(dialogueId), String(stepId));
982 return !!row;
983 }
984
985 markDialogueHeard (characterId, dialogueId, stepId)
986 {
987 this.db.prepare(`
988 INSERT OR IGNORE INTO character_dialogue_heard
989 (character_id, dialogue_id, step_id, heard_at)
990 VALUES (?, ?, ?, ?)
991 `).run(characterId | 0, String(dialogueId), String(stepId), Date.now());
992 }
993
994 listDialogueHeard (characterId)
995 {
996 return this.db.prepare(`
997 SELECT dialogue_id, step_id, heard_at
998 FROM character_dialogue_heard
999 WHERE character_id = ?
1000 `).all(characterId | 0);
1001 }
1002
1003 isDialogueTriggerLatched (characterId, dialogueId, triggerId)
1004 {
1005 const row = this.db.prepare(`
1006 SELECT 1 FROM character_dialogue_triggers
1007 WHERE character_id = ? AND dialogue_id = ? AND trigger_id = ?
1008 `).get(characterId | 0, String(dialogueId), String(triggerId));
1009 return !!row;
1010 }
1011
1012 latchDialogueTrigger (characterId, dialogueId, triggerId)
1013 {
1014 this.db.prepare(`
1015 INSERT OR IGNORE INTO character_dialogue_triggers
1016 (character_id, dialogue_id, trigger_id, latched_at)
1017 VALUES (?, ?, ?, ?)
1018 `).run(characterId | 0, String(dialogueId), String(triggerId), Date.now());
1019 }
1020
1021 appendQuestJournal (characterId, questId, stepId, text)
1022 {
1023 const trimmed = String(text ?? "").trim();
1024 if (!trimmed)
1025 { return null; }
1026 const writtenAt = Date.now();
1027 const result = this.db.prepare(`
1028 INSERT INTO character_quest_journal
1029 (character_id, quest_id, step_id, text, written_at)
1030 VALUES (?, ?, ?, ?, ?)
1031 `).run(characterId | 0, String(questId), String(stepId), trimmed, writtenAt);
1032 return {
1033 entryId: Number(result.lastInsertRowid),
1034 questId: String(questId),
1035 stepId: String(stepId),
1036 text: trimmed,
1037 writtenAt
1038 };
1039 }
1040
1041 listQuestJournal (characterId, questId = null)
1042 {
1043 if (questId)
1044 {
1045 return this.db.prepare(`
1046 SELECT entry_id, quest_id, step_id, text, written_at
1047 FROM character_quest_journal
1048 WHERE character_id = ? AND quest_id = ?
1049 ORDER BY written_at ASC, entry_id ASC
1050 `).all(characterId | 0, String(questId));
1051 }
1052 return this.db.prepare(`
1053 SELECT entry_id, quest_id, step_id, text, written_at
1054 FROM character_quest_journal
1055 WHERE character_id = ?
1056 ORDER BY written_at ASC, entry_id ASC
1057 `).all(characterId | 0);
1058 }
1059
1060 listFriendRecords (characterId)
1061 {
1062 return this.listAccountFriendRecords(this.accountIdOfCharacter(characterId));
1063 }
1064
1065 accountIdOfCharacter (characterId)
1066 {
1067 const row = this.getCharacter(characterId);
1068 return row?.account_id | 0;
1069 }
1070
1071 getAccount (accountId)
1072 {
1073 return this.db.prepare(`
1074 SELECT account_id, auth_subject, username, email, is_admin
1075 FROM accounts WHERE account_id = ?
1076 `).get(accountId | 0) ?? null;
1077 }
1078
1079 accountDisplayName (accountId)
1080 {
1081 const row = this.getAccount(accountId);
1082 if (!row)
1083 { return "Unknown"; }
1084 const name = String(row.username ?? "").trim();
1085 return name || `Account ${row.account_id}`;
1086 }
1087
1088 listAccountFriendRecords (accountId)
1089 {
1090 const id = accountId | 0;
1091 if (!id)
1092 { return []; }
1093 return this.db.prepare(`
1094 SELECT
1095 CASE WHEN account_id_a = ? THEN account_id_b ELSE account_id_a END AS account_id,
1096 created_at
1097 FROM account_friendships
1098 WHERE account_id_a = ? OR account_id_b = ?
1099 ORDER BY created_at ASC
1100 `).all(id, id, id);
1101 }
1102
1103 areAccountFriends (accountId, otherAccountId)
1104 {
1105 const aId = accountId | 0;
1106 const bId = otherAccountId | 0;
1107 if (!aId || !bId)
1108 { return false; }
1109 if (aId === bId)
1110 { return true; }
1111 const [a, b] = pairIds(aId, bId);
1112 return !!this.db.prepare(`
1113 SELECT 1 FROM account_friendships WHERE account_id_a = ? AND account_id_b = ?
1114 `).get(a, b);
1115 }
1116
1117 areFriends (characterId, otherId)
1118 {
1119 const left = this.getCharacter(characterId);
1120 const right = this.getCharacter(otherId);
1121 if (!left || !right)
1122 { return false; }
1123 return this.areAccountFriends(left.account_id, right.account_id);
1124 }
1125
1126 insertAccountFriendship (accountId, otherId)
1127 {
1128 const [a, b] = pairIds(accountId, otherId);
1129 this.db.prepare(`
1130 INSERT OR IGNORE INTO account_friendships (account_id_a, account_id_b, created_at)
1131 VALUES (?, ?, ?)
1132 `).run(a, b, Date.now());
1133 }
1134
1135 insertFriendship (characterId, otherId)
1136 {
1137 const left = this.getCharacter(characterId);
1138 const right = this.getCharacter(otherId);
1139 if (!left || !right)
1140 { return; }
1141 this.insertAccountFriendship(left.account_id, right.account_id);
1142 }
1143
1144 deleteAccountFriendship (accountId, otherId)
1145 {
1146 const [a, b] = pairIds(accountId, otherId);
1147 this.db.prepare(`
1148 DELETE FROM account_friendships WHERE account_id_a = ? AND account_id_b = ?
1149 `).run(a, b);
1150 }
1151
1152 deleteFriendship (characterId, otherId)
1153 {
1154 const left = this.getCharacter(characterId);
1155 const right = this.getCharacter(otherId);
1156 if (!left || !right)
1157 { return; }
1158 this.deleteAccountFriendship(left.account_id, right.account_id);
1159 }
1160
1161 listIncomingAccountRequests (accountId)
1162 {
1163 return this.db.prepare(`
1164 SELECT request_id, from_account_id, to_account_id, created_at
1165 FROM account_friend_requests
1166 WHERE to_account_id = ?
1167 ORDER BY created_at ASC
1168 `).all(accountId | 0);
1169 }
1170
1171 listOutgoingAccountRequests (accountId)
1172 {
1173 return this.db.prepare(`
1174 SELECT request_id, from_account_id, to_account_id, created_at
1175 FROM account_friend_requests
1176 WHERE from_account_id = ?
1177 ORDER BY created_at ASC
1178 `).all(accountId | 0);
1179 }
1180
1181 listIncomingRequests (characterId)
1182 {
1183 return this.listIncomingAccountRequests(this.accountIdOfCharacter(characterId));
1184 }
1185
1186 listOutgoingRequests (characterId)
1187 {
1188 return this.listOutgoingAccountRequests(this.accountIdOfCharacter(characterId));
1189 }
1190
1191 getAccountFriendRequest (fromAccountId, toAccountId)
1192 {
1193 return this.db.prepare(`
1194 SELECT * FROM account_friend_requests
1195 WHERE from_account_id = ? AND to_account_id = ?
1196 `).get(fromAccountId | 0, toAccountId | 0) ?? null;
1197 }
1198
1199 getFriendRequest (fromId, toId)
1200 {
1201 const from = this.getCharacter(fromId);
1202 const to = this.getCharacter(toId);
1203 if (!from || !to)
1204 { return null; }
1205 return this.getAccountFriendRequest(from.account_id, to.account_id);
1206 }
1207
1208 insertAccountFriendRequest (fromAccountId, toAccountId)
1209 {
1210 this.db.prepare(`
1211 INSERT INTO account_friend_requests (from_account_id, to_account_id, created_at)
1212 VALUES (?, ?, ?)
1213 `).run(fromAccountId | 0, toAccountId | 0, Date.now());
1214 }
1215
1216 insertFriendRequest (fromId, toId)
1217 {
1218 const from = this.getCharacter(fromId);
1219 const to = this.getCharacter(toId);
1220 if (!from || !to)
1221 { return; }
1222 this.insertAccountFriendRequest(from.account_id, to.account_id);
1223 }
1224
1225 deleteAccountFriendRequest (fromAccountId, toAccountId)
1226 {
1227 this.db.prepare(`
1228 DELETE FROM account_friend_requests
1229 WHERE from_account_id = ? AND to_account_id = ?
1230 `).run(fromAccountId | 0, toAccountId | 0);
1231 }
1232
1233 deleteFriendRequest (fromId, toId)
1234 {
1235 const from = this.getCharacter(fromId);
1236 const to = this.getCharacter(toId);
1237 if (!from || !to)
1238 { return; }
1239 this.deleteAccountFriendRequest(from.account_id, to.account_id);
1240 }
1241
1242 /** Decayed kill counter at `now` (does not write). */
1243 peekKillCounter (characterId, now = Date.now())
1244 {
1245 const row = this.getCharacter(characterId);
1246 if (!row)
1247 { return 0; }
1248 const current = Math.max(0, Number(row.kill_counter) || 0);
1249 const at = row.kill_counter_at | 0;
1250 if (!(current > 0) || !at || now <= at)
1251 { return current; }
1252 return decayKillCounter(current, now - at).killCounter;
1253 }
1254
1255 getKillCounterState (characterId)
1256 {
1257 const row = this.getCharacter(characterId);
1258 if (!row)
1259 { return { killCounter: 0, updatedAt: Date.now() }; }
1260 return {
1261 killCounter: Math.max(0, Number(row.kill_counter) || 0),
1262 updatedAt: row.kill_counter_at | 0 || Date.now()
1263 };
1264 }
1265
1266 setKillCounter (characterId, killCounter, updatedAt = Date.now())
1267 {
1268 const value = Math.max(0, Number(killCounter) || 0);
1269 this.db.prepare(`
1270 UPDATE characters SET kill_counter = ?, kill_counter_at = ? WHERE character_id = ?
1271 `).run(value, updatedAt | 0, characterId | 0);
1272 return value;
1273 }
1274
1275 addKillCounter (characterId, amount, now = Date.now())
1276 {
1277 const state = this.getKillCounterState(characterId);
1278 const decayed = decayKillCounter(state.killCounter, Math.max(0, now - (state.updatedAt | 0)));
1279 const next = decayed.killCounter + Math.max(0, Number(amount) || 0);
1280 return this.setKillCounter(characterId, next, now);
1281 }
1282
1283 getCowardiceForgivenHour (characterId)
1284 {
1285 const row = this.getCharacter(characterId);
1286 if (!row || row.cowardice_forgiven_hour == null)
1287 { return null; }
1288 return row.cowardice_forgiven_hour | 0;
1289 }
1290
1291 setCowardiceForgivenHour (characterId, hourKey)
1292 {
1293 this.db.prepare(`
1294 UPDATE characters SET cowardice_forgiven_hour = ? WHERE character_id = ?
1295 `).run(hourKey | 0, characterId | 0);
1296 }
1297
1298 listPersistentInRegion (minX, minY, maxX, maxY)
1299 {
1300 return this.db.prepare(`
1301 SELECT entity_id, tile_x, tile_y, components_blob
1302 FROM persistent_entities
1303 WHERE tile_x >= ? AND tile_x <= ? AND tile_y >= ? AND tile_y <= ?
1304 `).all(minX, maxX, minY, maxY);
1305 }
1306
1307 replacePersistentInRegion (minX, minY, maxX, maxY, entities)
1308 {
1309 this.db.exec("BEGIN");
1310 try
1311 {
1312 this.db.prepare(`
1313 DELETE FROM persistent_entities
1314 WHERE tile_x >= ? AND tile_x <= ? AND tile_y >= ? AND tile_y <= ?
1315 `).run(minX, maxX, minY, maxY);
1316 const insert = this.db.prepare(`
1317 INSERT INTO persistent_entities (entity_id, tile_x, tile_y, components_blob)
1318 VALUES (?, ?, ?, ?)
1319 `);
1320 for (const entity of entities)
1321 {
1322 insert.run(
1323 entity.entityId,
1324 entity.tileX,
1325 entity.tileY,
1326 JSON.stringify(entity.payload?.components ?? entity.components)
1327 );
1328 }
1329 this.db.exec("COMMIT");
1330 }
1331 catch (error)
1332 {
1333 this.db.exec("ROLLBACK");
1334 throw error;
1335 }
1336 }
1337
1338 #ensureBag (characterId, starterSettings = null, creationClothing = null)
1339 {
1340 const existing = this.db.prepare("SELECT * FROM character_bags WHERE character_id = ?").get(characterId);
1341 if (existing)
1342 { return parseBag(existing, characterId); }
1343 const contents = buildStarterBagContents(starterSettings, creationClothing);
1344 this.db.prepare(`
1345 INSERT INTO character_bags (
1346 character_id, inventory_blob, equipment_blob, currency, vault_blob, vault_currency, skills_blob
1347 ) VALUES (?, ?, ?, 0, ?, 0, ?)
1348 `).run(
1349 characterId,
1350 JSON.stringify(contents.slots),
1351 JSON.stringify(contents.equipment),
1352 JSON.stringify({ slots: [], reserved: [] }),
1353 JSON.stringify(storedSkillXp(defaultPlayerSkills()))
1354 );
1355 return this.getBag(characterId);
1356 }
1357
1358 getBag (characterId)
1359 {
1360 const row = this.db.prepare("SELECT * FROM character_bags WHERE character_id = ?").get(characterId);
1361 if (row)
1362 { return parseBag(row, characterId); }
1363 if (this.getCharacter(characterId))
1364 { return this.#ensureBag(characterId); }
1365 return null;
1366 }
1367
1368 saveBag (characterId, bag)
1369 {
1370 this.#ensureBag(characterId);
1371 const migrated = migrateBagCoins(bag);
1372 const vault = normalizeVault(
1373 migrated.vaultReserved != null
1374 ? { slots: migrated.vault, reserved: migrated.vaultReserved }
1375 : migrated.vault
1376 );
1377 this.db.prepare(`
1378 UPDATE character_bags
1379 SET inventory_blob = ?, equipment_blob = ?, currency = 0, vault_blob = ?, vault_currency = 0
1380 WHERE character_id = ?
1381 `).run(
1382 JSON.stringify(normalizeInventorySlots(migrated.inventory ?? emptySlots())),
1383 JSON.stringify(migrated.equipment ?? emptyEquipment()),
1384 JSON.stringify({ slots: vault.slots, reserved: vault.reserved }),
1385 characterId
1386 );
1387 }
1388
1389 getSkills (characterId)
1390 {
1391 const row = this.db.prepare("SELECT skills_blob FROM character_bags WHERE character_id = ?").get(characterId);
1392 if (row)
1393 {
1394 const parsed = JSON.parse(row.skills_blob || "{}");
1395 const skills = normalizePlayerSkills(parsed);
1396 // Persist floors (Health ≥ 5, others ≥ 1) for legacy under-leveled rows.
1397 if (this.#skillsBlobNeedsRewrite(parsed, skills))
1398 { this.saveSkills(characterId, skills); }
1399 return skills;
1400 }
1401 if (this.getCharacter(characterId))
1402 {
1403 this.#ensureBag(characterId);
1404 return this.getSkills(characterId);
1405 }
1406 return null;
1407 }
1408
1409 /** True when stored blob is below the default minimum levels and must be rewritten. */
1410 #skillsBlobNeedsRewrite (parsed, normalized)
1411 {
1412 const raw = parsed?.xp && typeof parsed.xp === "object" ? parsed.xp : parsed;
1413 if (!raw || typeof raw !== "object")
1414 { return true; }
1415 for (const skillId of Object.keys(normalized.xp))
1416 {
1417 const stored = Math.max(0, Math.floor(Number(raw[skillId]) || 0));
1418 if (stored < normalized.xp[skillId])
1419 { return true; }
1420 }
1421 return false;
1422 }
1423
1424 saveSkills (characterId, skills)
1425 {
1426 this.#ensureBag(characterId);
1427 const xp = storedSkillXp(skills?.xp ? skills : { xp: skills });
1428 this.db.prepare("UPDATE character_bags SET skills_blob = ? WHERE character_id = ?").run(
1429 JSON.stringify({ xp }),
1430 characterId
1431 );
1432 }
1433
1434 getSpells (characterId)
1435 {
1436 const row = this.db.prepare("SELECT spells_blob FROM character_bags WHERE character_id = ?").get(characterId);
1437 if (row)
1438 {
1439 return normalizePlayerSpells(JSON.parse(row.spells_blob || "{}"));
1440 }
1441 if (this.getCharacter(characterId))
1442 {
1443 this.#ensureBag(characterId);
1444 return this.getSpells(characterId);
1445 }
1446 return null;
1447 }
1448
1449 saveSpells (characterId, spells)
1450 {
1451 this.#ensureBag(characterId);
1452 this.db.prepare("UPDATE character_bags SET spells_blob = ? WHERE character_id = ?").run(
1453 JSON.stringify(storedPlayerSpells(spells)),
1454 characterId
1455 );
1456 }
1457 getInheritance (accountId)
1458 {
1459 const row = this.db.prepare("SELECT * FROM account_inheritance WHERE account_id = ?").get(accountId);
1460 if (!row)
1461 { return { accountId, slots: emptySlots() }; }
1462 return migrateInheritanceCoins({
1463 accountId,
1464 slots: JSON.parse(row.slots_blob),
1465 currency: row.currency
1466 });
1467 }
1468
1469 saveInheritance (accountId, inheritance)
1470 {
1471 const migrated = migrateInheritanceCoins(inheritance);
1472 this.db.prepare(`
1473 INSERT INTO account_inheritance (account_id, slots_blob, currency)
1474 VALUES (?, ?, 0)
1475 ON CONFLICT(account_id) DO UPDATE SET slots_blob = excluded.slots_blob, currency = 0
1476 `).run(accountId, JSON.stringify(migrated.slots ?? emptySlots()));
1477 }
1478
1479 #moveGoodsToInheritance (character)
1480 {
1481 const bag = this.getBag(character.character_id) ?? {
1482 inventory: emptySlots(),
1483 equipment: emptyEquipment(),
1484 vault: [],
1485 vaultReserved: []
1486 };
1487 const inheritance = this.getInheritance(character.account_id);
1488 const vault = normalizeVault(
1489 bag.vaultReserved != null
1490 ? { slots: bag.vault, reserved: bag.vaultReserved }
1491 : bag.vault
1492 );
1493 // Void outstanding cheques issued by this character: reservations return to available vault.
1494 for (const reserved of vault.reserved ?? [])
1495 {
1496 const stack = {
1497 itemId: reserved.itemId,
1498 quantity: reserved.quantity,
1499 origin: reserved.origin ?? null,
1500 instance: reserved.instance ?? null
1501 };
1502 const def = itemDefinition(stack.itemId);
1503 if (!def)
1504 { continue; }
1505 const restored = insertStackGrowable(vault.slots, stack, def);
1506 vault.slots = restored.slots;
1507 }
1508 vault.reserved = [];
1509 const incoming = [
1510 ...(bag.inventory ?? []),
1511 ...Object.values(bag.equipment?.slots ?? {}),
1512 ...(vault.slots ?? [])
1513 ].filter(Boolean);
1514 let slots = inheritance.slots ?? emptySlots();
1515 for (const stack of incoming)
1516 {
1517 const def = itemDefinition(stack.itemId);
1518 if (!def)
1519 { continue; }
1520 const result = insertStack(slots, stack, def);
1521 if (result.ok)
1522 { slots = result.slots; }
1523 else
1524 {
1525 const extra = result.slots;
1526 for (const leftover of extra)
1527 {
1528 if (leftover)
1529 { slots.push(leftover); }
1530 }
1531 slots.push({ ...stack, quantity: result.remainder });
1532 }
1533 }
1534 this.saveInheritance(character.account_id, {
1535 slots: publicSlots(slots)
1536 });
1537 }
1538
1539 nextGroundItemId ()
1540 {
1541 const row = this.db.prepare("SELECT MAX(ground_item_id) AS id FROM ground_items").get();
1542 return (row?.id ?? 0) + 1;
1543 }
1544
1545 listGroundItemsInRegion (minX, minY, maxX, maxY)
1546 {
1547 return this.db.prepare(`
1548 SELECT * FROM ground_items
1549 WHERE tile_x >= ? AND tile_x <= ? AND tile_y >= ? AND tile_y <= ?
1550 `).all(minX, maxX, minY, maxY).map(parseGroundItem);
1551 }
1552
1553 upsertGroundItem (item)
1554 {
1555 this.db.prepare(`
1556 INSERT INTO ground_items (
1557 ground_item_id, tile_x, tile_y, stack_blob, dropped_at, expires_at, source, dropped_by_character_id
1558 ) VALUES (?, ?, ?, ?, ?, ?, ?, ?)
1559 ON CONFLICT(ground_item_id) DO UPDATE SET
1560 tile_x = excluded.tile_x,
1561 tile_y = excluded.tile_y,
1562 stack_blob = excluded.stack_blob,
1563 dropped_at = excluded.dropped_at,
1564 expires_at = excluded.expires_at,
1565 source = excluded.source,
1566 dropped_by_character_id = excluded.dropped_by_character_id
1567 `).run(
1568 item.groundItemId,
1569 item.tileX,
1570 item.tileY,
1571 JSON.stringify(item.stack),
1572 item.droppedAt,
1573 item.expiresAt,
1574 item.source,
1575 item.droppedByCharacterId ?? null
1576 );
1577 }
1578
1579 deleteGroundItem (groundItemId)
1580 {
1581 this.db.prepare("DELETE FROM ground_items WHERE ground_item_id = ?").run(groundItemId);
1582 }
1583
1584 insertCourierTransit (transit)
1585 {
1586 const now = transit.updatedAt ?? Date.now();
1587 const result = this.db.prepare(`
1588 INSERT INTO courier_transits (
1589 sender_character_id, destination_kind, destination_character_id, cargo_blob,
1590 origin_x, origin_y, dest_x, dest_y, traveled, status, entity_id, updated_at, created_at,
1591 vault_character_id
1592 ) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
1593 `).run(
1594 transit.senderCharacterId,
1595 transit.destinationKind,
1596 transit.destinationCharacterId ?? null,
1597 JSON.stringify(transit.cargo),
1598 transit.originX,
1599 transit.originY,
1600 transit.destX,
1601 transit.destY,
1602 transit.traveled ?? 0,
1603 transit.status ?? "in_transit",
1604 transit.entityId ?? null,
1605 now,
1606 transit.createdAt ?? now,
1607 transit.vaultCharacterId ?? null
1608 );
1609 return this.getCourierTransit(Number(result.lastInsertRowid));
1610 }
1611
1612 getCourierTransit (courierId)
1613 {
1614 const row = this.db.prepare("SELECT * FROM courier_transits WHERE courier_id = ?").get(courierId);
1615 return row ? parseCourierTransit(row) : null;
1616 }
1617
1618 listActiveCourierTransits ()
1619 {
1620 return this.db.prepare(`
1621 SELECT * FROM courier_transits WHERE status = 'in_transit' ORDER BY courier_id ASC
1622 `).all().map(parseCourierTransit);
1623 }
1624
1625 updateCourierTransit (transit)
1626 {
1627 this.db.prepare(`
1628 UPDATE courier_transits
1629 SET traveled = ?, status = ?, entity_id = ?, updated_at = ?,
1630 origin_x = ?, origin_y = ?, dest_x = ?, dest_y = ?,
1631 destination_kind = ?, destination_character_id = ?, vault_character_id = ?
1632 WHERE courier_id = ?
1633 `).run(
1634 transit.traveled ?? 0,
1635 transit.status,
1636 transit.entityId ?? null,
1637 transit.updatedAt ?? Date.now(),
1638 transit.originX,
1639 transit.originY,
1640 transit.destX,
1641 transit.destY,
1642 transit.destinationKind,
1643 transit.destinationCharacterId ?? null,
1644 transit.vaultCharacterId ?? null,
1645 transit.courierId
1646 );
1647 }
1648
1649 appendBankRestNotice (characterId, entry)
1650 {
1651 const qty = Math.max(1, entry.quantity | 0);
1652 this.db.prepare(`
1653 INSERT INTO bank_rest_notices (character_id, item_id, item_name, quantity, source_name, created_at)
1654 VALUES (?, ?, ?, ?, ?, ?)
1655 `).run(
1656 characterId,
1657 entry.itemId | 0,
1658 String(entry.itemName ?? "Item"),
1659 qty,
1660 String(entry.sourceName ?? "unknown source"),
1661 entry.createdAt ?? Date.now()
1662 );
1663 }
1664
1665 listBankRestNotices (characterId)
1666 {
1667 return this.db.prepare(`
1668 SELECT * FROM bank_rest_notices WHERE character_id = ? ORDER BY notice_id ASC
1669 `).all(characterId).map((row) => ({
1670 noticeId: row.notice_id,
1671 characterId: row.character_id,
1672 itemId: row.item_id,
1673 itemName: row.item_name,
1674 quantity: row.quantity,
1675 sourceName: row.source_name,
1676 createdAt: row.created_at
1677 }));
1678 }
1679
1680 clearBankRestNotices (characterId)
1681 {
1682 this.db.prepare("DELETE FROM bank_rest_notices WHERE character_id = ?").run(characterId);
1683 }
1684
1685 insertHeldCourierDelivery (delivery)
1686 {
1687 const now = delivery.createdAt ?? Date.now();
1688 const result = this.db.prepare(`
1689 INSERT INTO held_courier_deliveries (
1690 character_id, sender_character_id, origin_tile_x, origin_tile_y,
1691 destination_kind, destination_character_id, vault_character_id, cargo_blob, created_at
1692 ) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?)
1693 `).run(
1694 delivery.characterId,
1695 delivery.senderCharacterId,
1696 delivery.originTileX | 0,
1697 delivery.originTileY | 0,
1698 delivery.destinationKind,
1699 delivery.destinationCharacterId ?? null,
1700 delivery.vaultCharacterId ?? null,
1701 JSON.stringify(delivery.cargo),
1702 now
1703 );
1704 return Number(result.lastInsertRowid);
1705 }
1706
1707 listHeldCourierDeliveries (characterId)
1708 {
1709 return this.db.prepare(`
1710 SELECT * FROM held_courier_deliveries WHERE character_id = ? ORDER BY hold_id ASC
1711 `).all(characterId).map(parseHeldCourierDelivery);
1712 }
1713
1714 deleteHeldCourierDelivery (holdId)
1715 {
1716 this.db.prepare("DELETE FROM held_courier_deliveries WHERE hold_id = ?").run(holdId);
1717 }
1718
1719 insertMarketOrder (order)
1720 {
1721 const result = this.db.prepare(`
1722 INSERT INTO market_orders (
1723 character_id, side, item_id, quantity, limit_unit_price,
1724 escrow_coins, escrow_stack_blob, listing_key, created_at, status
1725 ) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
1726 `).run(
1727 order.characterId,
1728 order.side,
1729 order.itemId | 0,
1730 order.quantity | 0,
1731 order.limitUnitPrice | 0,
1732 order.escrowCoins | 0 || 0,
1733 order.escrowStack ? JSON.stringify(order.escrowStack) : null,
1734 order.listingKey ?? null,
1735 order.createdAt ?? Date.now(),
1736 order.status ?? "open"
1737 );
1738 return this.getMarketOrder(Number(result.lastInsertRowid));
1739 }
1740
1741 getMarketOrder (orderId)
1742 {
1743 const row = this.db.prepare("SELECT * FROM market_orders WHERE order_id = ?").get(orderId);
1744 return row ? parseMarketOrder(row) : null;
1745 }
1746
1747 updateMarketOrder (order)
1748 {
1749 this.db.prepare(`
1750 UPDATE market_orders
1751 SET quantity = ?, escrow_coins = ?, escrow_stack_blob = ?, status = ?
1752 WHERE order_id = ?
1753 `).run(
1754 order.quantity | 0,
1755 order.escrowCoins | 0 || 0,
1756 order.escrowStack ? JSON.stringify(order.escrowStack) : null,
1757 order.status,
1758 order.orderId
1759 );
1760 return this.getMarketOrder(order.orderId);
1761 }
1762
1763 listOpenMarketOrdersForCharacter (characterId)
1764 {
1765 return this.db.prepare(`
1766 SELECT * FROM market_orders
1767 WHERE character_id = ? AND status = 'open'
1768 ORDER BY created_at ASC, order_id ASC
1769 `).all(characterId).map(parseMarketOrder);
1770 }
1771
1772 listOpenBuyOrdersForItem (itemId, listingKey = null)
1773 {
1774 if (listingKey)
1775 {
1776 return this.db.prepare(`
1777 SELECT * FROM market_orders
1778 WHERE status = 'open' AND side = 'buy' AND item_id = ? AND listing_key = ?
1779 ORDER BY limit_unit_price DESC, created_at ASC, order_id ASC
1780 `).all(itemId | 0, listingKey).map(parseMarketOrder);
1781 }
1782 return this.db.prepare(`
1783 SELECT * FROM market_orders
1784 WHERE status = 'open' AND side = 'buy' AND item_id = ?
1785 AND (listing_key IS NULL OR listing_key = '')
1786 ORDER BY limit_unit_price DESC, created_at ASC, order_id ASC
1787 `).all(itemId | 0).map(parseMarketOrder);
1788 }
1789
1790 listOpenSellOrdersForItem (itemId, listingKey = null)
1791 {
1792 if (listingKey)
1793 {
1794 return this.db.prepare(`
1795 SELECT * FROM market_orders
1796 WHERE status = 'open' AND side = 'sell' AND item_id = ? AND listing_key = ?
1797 ORDER BY limit_unit_price ASC, created_at ASC, order_id ASC
1798 `).all(itemId | 0, listingKey).map(parseMarketOrder);
1799 }
1800 return this.db.prepare(`
1801 SELECT * FROM market_orders
1802 WHERE status = 'open' AND side = 'sell' AND item_id = ?
1803 AND (listing_key IS NULL OR listing_key = '')
1804 ORDER BY limit_unit_price ASC, created_at ASC, order_id ASC
1805 `).all(itemId | 0).map(parseMarketOrder);
1806 }
1807
1808 listExpiredMarketOrders (createdBefore)
1809 {
1810 return this.db.prepare(`
1811 SELECT * FROM market_orders
1812 WHERE status = 'open' AND created_at < ?
1813 ORDER BY created_at ASC, order_id ASC
1814 `).all(createdBefore).map(parseMarketOrder);
1815 }
1816
1817 insertMarketTrade (trade)
1818 {
1819 const result = this.db.prepare(`
1820 INSERT INTO market_trades (
1821 buy_order_id, sell_order_id, item_id, quantity, unit_price,
1822 buyer_character_id, seller_character_id, created_at
1823 ) VALUES (?, ?, ?, ?, ?, ?, ?, ?)
1824 `).run(
1825 trade.buyOrderId,
1826 trade.sellOrderId,
1827 trade.itemId | 0,
1828 trade.quantity | 0,
1829 trade.unitPrice | 0,
1830 trade.buyerCharacterId,
1831 trade.sellerCharacterId,
1832 trade.createdAt ?? Date.now()
1833 );
1834 return Number(result.lastInsertRowid);
1835 }
1836
1837 upsertItemLastSale (sale)
1838 {
1839 this.db.prepare(`
1840 INSERT INTO item_last_sales (item_id, last_trade_price, last_trade_at, price_from)
1841 VALUES (?, ?, ?, ?)
1842 ON CONFLICT(item_id) DO UPDATE SET
1843 last_trade_price = excluded.last_trade_price,
1844 last_trade_at = excluded.last_trade_at,
1845 price_from = excluded.price_from
1846 `).run(
1847 sale.itemId | 0,
1848 sale.lastTradePrice | 0,
1849 sale.lastTradeAt,
1850 Math.round(Number(sale.priceFrom) || 0)
1851 );
1852 }
1853
1854 listItemLastSales ()
1855 {
1856 return this.db.prepare("SELECT * FROM item_last_sales").all().map((row) => ({
1857 itemId: row.item_id,
1858 lastTradePrice: row.last_trade_price,
1859 lastTradeAt: row.last_trade_at,
1860 priceFrom: row.price_from
1861 }));
1862 }
1863
1864 close ()
1865 {
1866 this.db.close();
1867 }
1868}
1869
1870export function normalizeName (value)
1871{
1872 return String(value ?? "").trim().toLocaleLowerCase();
1873}
1874
1875function pairIds (left, right)
1876{
1877 return left < right ? [left, right] : [right, left];
1878}
1879
1880function parseGroundItem (row)
1881{
1882 return {
1883 groundItemId: row.ground_item_id,
1884 tileX: row.tile_x,
1885 tileY: row.tile_y,
1886 stack: JSON.parse(row.stack_blob),
1887 droppedAt: row.dropped_at,
1888 expiresAt: row.expires_at,
1889 source: row.source,
1890 droppedByCharacterId: row.dropped_by_character_id
1891 };
1892}
1893
1894function parseMarketOrder (row)
1895{
1896 return {
1897 orderId: row.order_id,
1898 characterId: row.character_id,
1899 side: row.side,
1900 itemId: row.item_id,
1901 quantity: row.quantity,
1902 limitUnitPrice: row.limit_unit_price,
1903 escrowCoins: row.escrow_coins | 0,
1904 escrowStack: row.escrow_stack_blob ? JSON.parse(row.escrow_stack_blob) : null,
1905 listingKey: row.listing_key ?? null,
1906 createdAt: row.created_at,
1907 status: row.status
1908 };
1909}
1910
1911function bagContainsItem (inventory, equipment, itemId)
1912{
1913 for (const stack of inventory ?? [])
1914 {
1915 if (stack?.itemId === itemId)
1916 { return true; }
1917 }
1918 for (const stack of Object.values(equipment?.slots ?? {}))
1919 {
1920 if (stack?.itemId === itemId)
1921 { return true; }
1922 }
1923 return false;
1924}
1925
1926function parseBag (row, characterId)
1927{
1928 const vault = normalizeVault(JSON.parse(row.vault_blob));
1929 return migrateBagCoins({
1930 characterId,
1931 inventory: normalizeInventorySlots(JSON.parse(row.inventory_blob)),
1932 equipment: JSON.parse(row.equipment_blob),
1933 currency: row.currency,
1934 vault: vault.slots,
1935 vaultReserved: vault.reserved,
1936 vaultCurrency: row.vault_currency
1937 });
1938}
1939
1940function parseCourierTransit (row)
1941{
1942 return {
1943 courierId: row.courier_id,
1944 senderCharacterId: row.sender_character_id,
1945 destinationKind: row.destination_kind,
1946 destinationCharacterId: row.destination_character_id,
1947 vaultCharacterId: row.vault_character_id ?? null,
1948 cargo: JSON.parse(row.cargo_blob),
1949 originX: row.origin_x,
1950 originY: row.origin_y,
1951 destX: row.dest_x,
1952 destY: row.dest_y,
1953 traveled: row.traveled,
1954 status: row.status,
1955 entityId: row.entity_id,
1956 updatedAt: row.updated_at,
1957 createdAt: row.created_at
1958 };
1959}
1960
1961function parseHeldCourierDelivery (row)
1962{
1963 return {
1964 holdId: row.hold_id,
1965 characterId: row.character_id,
1966 senderCharacterId: row.sender_character_id,
1967 originTileX: row.origin_tile_x,
1968 originTileY: row.origin_tile_y,
1969 destinationKind: row.destination_kind,
1970 destinationCharacterId: row.destination_character_id,
1971 vaultCharacterId: row.vault_character_id,
1972 cargo: JSON.parse(row.cargo_blob),
1973 createdAt: row.created_at
1974 };
1975}