1/// <reference types="@cloudflare/workers-types" />2/**3 * Repo layer over D1 (schema: migrations/0001_init.sql).4 *5 * Conventions:6 * - All timestamps are unix epoch seconds (INTEGER), matching the schema.7 * - Functions returning `D1PreparedStatement` (stmt* prefix) are building8 * blocks for `db.batch()` so multi-row writes are atomic.9 * - Script/sim reads join star info so the service can apply the liveness10 * rule: live iff (expires_at IS NULL OR expires_at > now) OR starred by11 * anyone. `starred_by_me` is relative to the passed viewer id (0/1).12 */13import { LIMITS } from '@tribes/protocol';14import { nanoid, nowSec } from './util';15import { friendlyId, isUniqueViolation } from './friendly-id';1617// ---------------------------------------------------------------------------18// Row types19// ---------------------------------------------------------------------------2021export interface UserRow {22 id: string;23 external_id: string;24 name: string | null;25 created_at: number;26 public_sharing: number;27 name_changed_at: number | null;28}2930export interface ScriptRow {31 id: string;32 owner_id: string;33 name: string;34 lang: string;35 version: number;36 hash: string;37 status: 'validated' | 'rejected';38 error_report: string | null;39 created_at: number;40 expires_at: number | null;41 title: string | null;42 description: string | null;43 source_shared: number;44 model_id: string | null;45 deleted_at: number | null;46 linked_script_id: string | null;47}4849/** ScriptRow plus star aggregates and the computed LISTED bit (see module doc). */50export interface ScriptRowX extends ScriptRow {51 star_count: number;52 starred_by_me: number;53 listed: number;54}5556export interface SimRow {57 id: string;58 creator_id: string;59 seed: string;60 config_json: string;61 created_at: number;62 expires_at: number | null;63 visibility: 'private' | 'public';64 kind: 'user' | 'ladder';65}6667export interface SimRowX extends SimRow {68 star_count: number;69 starred_by_me: number;70}7172export interface SimScriptRow {73 sim_id: string;74 slot: number;75 script_id: string;76 script_name: string;77 version: number;78 hash: string;79}8081export interface TokenRow {82 id: string;83 user_id: string;84 token_hash: string;85 name: string;86 created_at: number;87}8889// ---------------------------------------------------------------------------90// Users91// ---------------------------------------------------------------------------9293export async function getIdentity(94 db: D1Database,95 externalId: string,96): Promise<{ user_id: string } | null> {97 return db98 .prepare(`SELECT user_id FROM user_identities WHERE external_id = ?1`)99 .bind(externalId)100 .first<{ user_id: string }>();101}102103export async function listIdentities(104 db: D1Database,105 userId: string,106): Promise<Array<{ external_id: string; created_at: number }>> {107 const res = await db108 .prepare(109 `SELECT external_id, created_at FROM user_identities WHERE user_id = ?1 ORDER BY created_at`,110 )111 .bind(userId)112 .all<{ external_id: string; created_at: number }>();113 return res.results;114}115116/** Remove an identity; the last one is protected (a keyless account is lost). */117export async function removeIdentity(118 db: D1Database,119 userId: string,120 externalId: string,121): Promise<'removed' | 'last' | 'missing'> {122 const rows = await listIdentities(db, userId);123 if (!rows.some((r) => r.external_id === externalId)) return 'missing';124 if (rows.length <= 1) return 'last';125 await db.batch([126 db127 .prepare(`DELETE FROM user_identities WHERE user_id = ?1 AND external_id = ?2`)128 .bind(userId, externalId),129 // Revoke the device's wire token too (named after the key prefix).130 db131 .prepare(`DELETE FROM api_tokens WHERE user_id = ?1 AND name = ?2`)132 .bind(userId, deviceTokenName(externalId.startsWith('key:') ? externalId.slice(4) : externalId)),133 ]);134 return 'removed';135}136137/** One active wire token per device key: the stable name it is stored under. */138export function deviceTokenName(publicKey: string): string {139 return `device key ${publicKey.slice(0, 8)}`;140}141142export async function insertIdentity(143 db: D1Database,144 externalId: string,145 userId: string,146 now: number,147): Promise<void> {148 await db149 .prepare(150 `INSERT OR IGNORE INTO user_identities (external_id, user_id, created_at) VALUES (?1, ?2, ?3)`,151 )152 .bind(externalId, userId, now)153 .run();154}155156/**157 * Merge `fromId`'s account into `toId` (keypair -> shoo link). Owned rows158 * move over; duplicate stars/hearts collapse; the from-user's identities159 * re-point (so the keypair keeps authenticating as the merged account);160 * script name collisions under UNIQUE(owner_id, name) get suffixed.161 */162export async function mergeUsers(db: D1Database, fromId: string, toId: string): Promise<void> {163 await db.batch([164 db165 .prepare(166 `UPDATE scripts SET name = name || '~' || id WHERE owner_id = ?1 AND name IN167 (SELECT name FROM scripts WHERE owner_id = ?2)`,168 )169 .bind(fromId, toId),170 db.prepare(`UPDATE scripts SET owner_id = ?2 WHERE owner_id = ?1`).bind(fromId, toId),171 db.prepare(`UPDATE sims SET creator_id = ?2 WHERE creator_id = ?1`).bind(fromId, toId),172 db.prepare(`UPDATE OR IGNORE stars SET user_id = ?2 WHERE user_id = ?1`).bind(fromId, toId),173 db.prepare(`DELETE FROM stars WHERE user_id = ?1`).bind(fromId), // dup leftovers174 db.prepare(`UPDATE OR IGNORE hearts SET user_id = ?2 WHERE user_id = ?1`).bind(fromId, toId),175 db.prepare(`DELETE FROM hearts WHERE user_id = ?1`).bind(fromId),176 db.prepare(`UPDATE api_tokens SET user_id = ?2 WHERE user_id = ?1`).bind(fromId, toId),177 db.prepare(`UPDATE user_identities SET user_id = ?2 WHERE user_id = ?1`).bind(fromId, toId),178 // Sharing survives the merge: if EITHER account had a public portfolio,179 // the merged one does — merging must never silently delist.180 db181 .prepare(182 `UPDATE users SET public_sharing = 1 WHERE id = ?2183 AND EXISTS (SELECT 1 FROM users u WHERE u.id = ?1 AND u.public_sharing = 1)`,184 )185 .bind(fromId, toId),186 // users.external_id is UNIQUE and vestigial post-identities; the from-row goes.187 db.prepare(`DELETE FROM users WHERE id = ?1`).bind(fromId),188 ]);189}190191export async function updateUserName(192 db: D1Database,193 userId: string,194 name: string | null,195 now: number,196): Promise<void> {197 await db198 .prepare(`UPDATE users SET name = ?2, name_changed_at = ?3 WHERE id = ?1`)199 .bind(userId, name, now)200 .run();201}202203export async function upsertUser(204 db: D1Database,205 externalId: string,206 name: string | null,207): Promise<UserRow> {208 // Identity table first: a user may hold several external identities209 // (device keypairs, shoo, dev) — users.external_id is only the FIRST one.210 const identity = await getIdentity(db, externalId);211 if (identity !== null) {212 const row = await getUserById(db, identity.user_id);213 if (row !== null) {214 if (name !== null && row.name === null) {215 await db.prepare(`UPDATE users SET name = ?2 WHERE id = ?1`).bind(row.id, name).run();216 row.name = name;217 }218 return row;219 }220 }221 // Friendly id seeded by the external id; on the (rare) id collision with a222 // DIFFERENT user, retry with the next attempt, then fall back to a nanoid.223 for (let attempt = 0; ; attempt++) {224 const id =225 attempt > 8 ? 'usr_' + nanoid() : 'usr_' + friendlyId('user', externalId, attempt);226 try {227 const row = await db228 .prepare(229 `INSERT INTO users (id, external_id, name, created_at)230 VALUES (?1, ?2, ?3, ?4)231 ON CONFLICT(external_id) DO UPDATE SET name = COALESCE(excluded.name, users.name)232 RETURNING *`,233 )234 .bind(id, externalId, name, nowSec())235 .first<UserRow>();236 if (!row) throw new Error('user upsert returned no row');237 await insertIdentity(db, externalId, row.id, nowSec());238 return row;239 } catch (err) {240 // ON CONFLICT only absorbs external_id conflicts; a PRIMARY KEY clash241 // (same friendly id, different user) surfaces as a unique violation.242 if (!isUniqueViolation(err) || attempt > 9) throw err;243 }244 }245}246247export function getUserById(db: D1Database, id: string): Promise<UserRow | null> {248 return db.prepare(`SELECT * FROM users WHERE id = ?1`).bind(id).first<UserRow>();249}250251export async function setUserSharing(252 db: D1Database,253 userId: string,254 publicSharing: boolean,255): Promise<void> {256 await db257 .prepare(`UPDATE users SET public_sharing = ?2 WHERE id = ?1`)258 .bind(userId, publicSharing ? 1 : 0)259 .run();260}261262/**263 * Get-or-create a system user ('system:ghost', 'system:ladder'). The ghost264 * needs public_sharing=1 so anonymized canonical scripts stay LISTED.265 */266export async function getOrCreateSystemUser(267 db: D1Database,268 externalId: string,269 name: string,270 publicSharing: boolean,271): Promise<UserRow> {272 const row = await upsertUser(db, externalId, name);273 if (publicSharing && row.public_sharing !== 1) {274 await setUserSharing(db, row.id, true);275 row.public_sharing = 1;276 }277 return row;278}279280// ---------------------------------------------------------------------------281// API tokens282// ---------------------------------------------------------------------------283284export async function insertToken(285 db: D1Database,286 t: { id: string; userId: string; tokenHash: string; name: string; createdAt: number },287): Promise<void> {288 await db289 .prepare(290 `INSERT INTO api_tokens (id, user_id, token_hash, name, created_at)291 VALUES (?1, ?2, ?3, ?4, ?5)`,292 )293 .bind(t.id, t.userId, t.tokenHash, t.name, t.createdAt)294 .run();295}296297export async function listTokensByUser(db: D1Database, userId: string): Promise<TokenRow[]> {298 const res = await db299 .prepare(`SELECT * FROM api_tokens WHERE user_id = ?1 ORDER BY created_at DESC`)300 .bind(userId)301 .all<TokenRow>();302 return res.results;303}304305/** Returns number of rows deleted (0 when the token isn't the user's). */306export async function deleteToken(db: D1Database, userId: string, id: string): Promise<number> {307 const res = await db308 .prepare(`DELETE FROM api_tokens WHERE id = ?1 AND user_id = ?2`)309 .bind(id, userId)310 .run();311 return res.meta.changes;312}313314export function getTokenUser(315 db: D1Database,316 tokenHash: string,317): Promise<{ user_id: string; external_id: string } | null> {318 return db319 .prepare(320 `SELECT u.id AS user_id, u.external_id AS external_id321 FROM api_tokens t JOIN users u ON u.id = t.user_id322 WHERE t.token_hash = ?1`,323 )324 .bind(tokenHash)325 .first<{ user_id: string; external_id: string }>();326}327328// ---------------------------------------------------------------------------329// Blobs330// ---------------------------------------------------------------------------331332export function stmtUpsertBlob(333 db: D1Database,334 hash: string,335 source: string,336 now: number,337 normHash: string | null = null,338 inR2 = 0,339): D1PreparedStatement {340 // norm_hash backfills onto pre-existing blobs but never flips once set341 // (the normalizer is deterministic, so a set value is already correct).342 // in_r2 only ever ratchets up: once bytes are in R2 they stay there.343 return db344 .prepare(345 `INSERT INTO blobs (hash, source, norm_hash, created_at, in_r2) VALUES (?1, ?2, ?3, ?4, ?5)346 ON CONFLICT(hash) DO UPDATE SET347 norm_hash = COALESCE(blobs.norm_hash, excluded.norm_hash),348 in_r2 = MAX(blobs.in_r2, excluded.in_r2)`,349 )350 .bind(hash, source, normHash, now, inR2);351}352353/**354 * Dedup soft-link target: the oldest LISTED script owned by someone ELSE355 * whose current source normalizes to the same hash. Own duplicates never link.356 */357export async function findCanonicalListed(358 db: D1Database,359 normHash: string,360 excludeOwnerId: string,361): Promise<string | null> {362 const row = await db363 .prepare(364 `SELECT s.id AS id FROM scripts s JOIN blobs b ON b.hash = s.hash365 WHERE b.norm_hash = ?1 AND s.owner_id <> ?2 AND ${LISTED_WHERE}366 ORDER BY s.created_at, s.id LIMIT 1`,367 )368 .bind(normHash, excludeOwnerId)369 .first<{ id: string }>();370 return row?.id ?? null;371}372373export async function getBlobSource(db: D1Database, hash: string): Promise<string | null> {374 const row = await db375 .prepare(`SELECT source FROM blobs WHERE hash = ?1`)376 .bind(hash)377 .first<{ source: string }>();378 return row?.source ?? null;379}380381// ---------------------------------------------------------------------------382// Scripts383// ---------------------------------------------------------------------------384385/**386 * The LISTED predicate: a script is publicly browsable iff its owner opted in387 * to public sharing, the owner starred it, it is validated, not trashed, and388 * not a dedup soft-link to someone else's canonical script. Every public389 * query MUST filter through this.390 */391export function listedWhere(alias: string): string {392 return `(${alias}.status = 'validated'393 AND ${alias}.deleted_at IS NULL394 AND ${alias}.linked_script_id IS NULL395 AND EXISTS (SELECT 1 FROM users lu WHERE lu.id = ${alias}.owner_id AND lu.public_sharing = 1)396 AND EXISTS (SELECT 1 FROM stars ls WHERE ls.kind = 'script' AND ls.target_id = ${alias}.id AND ls.user_id = ${alias}.owner_id))`;397}398399/** LISTED for the conventional `scripts s` alias. */400export const LISTED_WHERE = listedWhere('s');401402/** ?1 is always the viewer id (for starred_by_me). */403const SCRIPT_SELECT = `404 SELECT s.*,405 (SELECT COUNT(*) FROM stars st WHERE st.kind = 'script' AND st.target_id = s.id) AS star_count,406 EXISTS (SELECT 1 FROM stars st WHERE st.kind = 'script' AND st.target_id = s.id AND st.user_id = ?1) AS starred_by_me,407 ${LISTED_WHERE} AS listed408 FROM scripts s`;409410export function getScriptById(411 db: D1Database,412 id: string,413 viewerId: string | null,414): Promise<ScriptRowX | null> {415 return db.prepare(SCRIPT_SELECT + ` WHERE s.id = ?2`).bind(viewerId, id).first<ScriptRowX>();416}417418export function getScriptByOwnerName(419 db: D1Database,420 ownerId: string,421 name: string,422): Promise<ScriptRowX | null> {423 return db424 .prepare(SCRIPT_SELECT + ` WHERE s.owner_id = ?2 AND s.name = ?3`)425 .bind(ownerId, ownerId, name)426 .first<ScriptRowX>();427}428429/** Live scripts owned by `ownerId` (liveness applied in SQL). `trashed`430 * selects the 14-day trash instead of the default (non-trashed) listing. */431export async function listScriptsByOwner(432 db: D1Database,433 ownerId: string,434 now: number,435 trashed = false,436): Promise<ScriptRowX[]> {437 const res = await db438 .prepare(439 SCRIPT_SELECT +440 ` WHERE s.owner_id = ?2441 AND s.deleted_at ${trashed ? 'IS NOT NULL' : 'IS NULL'}442 AND (s.expires_at IS NULL OR s.expires_at > ?3443 OR EXISTS (SELECT 1 FROM stars st WHERE st.kind = 'script' AND st.target_id = s.id))444 ORDER BY s.created_at DESC`,445 )446 .bind(ownerId, ownerId, now)447 .all<ScriptRowX>();448 return res.results;449}450451export function stmtInsertScript(452 db: D1Database,453 s: {454 id: string;455 ownerId: string;456 name: string;457 version: number;458 hash: string;459 createdAt: number;460 expiresAt: number | null;461 linkedScriptId?: string | null;462 },463): D1PreparedStatement {464 return db465 .prepare(466 `INSERT INTO scripts (id, owner_id, name, lang, version, hash, status, error_report, created_at, expires_at, linked_script_id)467 VALUES (?1, ?2, ?3, 'js', ?4, ?5, 'validated', NULL, ?6, ?7, ?8)`,468 )469 .bind(s.id, s.ownerId, s.name, s.version, s.hash, s.createdAt, s.expiresAt, s.linkedScriptId ?? null);470}471472/** Successful resubmits also revive from the trash (deleted_at cleared) and473 * recompute the dedup soft-link for the new source. */474export function stmtBumpScript(475 db: D1Database,476 id: string,477 version: number,478 hash: string,479 expiresAt: number | null,480 linkedScriptId: string | null = null,481): D1PreparedStatement {482 return db483 .prepare(484 `UPDATE scripts SET version = ?2, hash = ?3, status = 'validated', error_report = NULL,485 expires_at = ?4, deleted_at = NULL, linked_script_id = ?5486 WHERE id = ?1`,487 )488 .bind(id, version, hash, expiresAt, linkedScriptId);489}490491/** Soft delete / restore: sets or clears deleted_at (stars stay intact). */492export function stmtSetDeletedAt(493 db: D1Database,494 id: string,495 deletedAt: number | null,496): D1PreparedStatement {497 return db.prepare(`UPDATE scripts SET deleted_at = ?2 WHERE id = ?1`).bind(id, deletedAt);498}499500/** Owner-editable public metadata; only the provided fields are updated. */501export async function updateScriptMeta(502 db: D1Database,503 id: string,504 fields: {505 title?: string | null;506 description?: string | null;507 modelId?: string | null;508 sourceShared?: boolean;509 },510): Promise<void> {511 const sets: string[] = [];512 const params: unknown[] = [id];513 const add = (clause: string, value: unknown): void => {514 params.push(value);515 sets.push(`${clause} = ?${params.length}`);516 };517 if (fields.title !== undefined) add('title', fields.title);518 if (fields.description !== undefined) add('description', fields.description);519 if (fields.modelId !== undefined) add('model_id', fields.modelId);520 if (fields.sourceShared !== undefined) add('source_shared', fields.sourceShared ? 1 : 0);521 if (sets.length === 0) return;522 await db523 .prepare(`UPDATE scripts SET ${sets.join(', ')} WHERE id = ?1`)524 .bind(...params)525 .run();526}527528export function stmtInsertScriptVersion(529 db: D1Database,530 scriptId: string,531 version: number,532 hash: string,533 now: number,534): D1PreparedStatement {535 return db536 .prepare(537 `INSERT INTO script_versions (script_id, version, hash, created_at) VALUES (?1, ?2, ?3, ?4)`,538 )539 .bind(scriptId, version, hash, now);540}541542export function stmtDeleteScript(db: D1Database, id: string): D1PreparedStatement {543 return db.prepare(`DELETE FROM scripts WHERE id = ?1`).bind(id);544}545546export async function getVersionHash(547 db: D1Database,548 scriptId: string,549 version: number,550): Promise<string | null> {551 const row = await db552 .prepare(`SELECT hash FROM script_versions WHERE script_id = ?1 AND version = ?2`)553 .bind(scriptId, version)554 .first<{ hash: string }>();555 return row?.hash ?? null;556}557558// ---------------------------------------------------------------------------559// Sims560// ---------------------------------------------------------------------------561562const SIM_SELECT = `563 SELECT s.*,564 (SELECT COUNT(*) FROM stars st WHERE st.kind = 'sim' AND st.target_id = s.id) AS star_count,565 EXISTS (SELECT 1 FROM stars st WHERE st.kind = 'sim' AND st.target_id = s.id AND st.user_id = ?1) AS starred_by_me566 FROM sims s`;567568export function getSimById(569 db: D1Database,570 id: string,571 viewerId: string | null,572): Promise<SimRowX | null> {573 return db.prepare(SIM_SELECT + ` WHERE s.id = ?2`).bind(viewerId, id).first<SimRowX>();574}575576export async function listSimsByCreator(577 db: D1Database,578 creatorId: string,579 now: number,580): Promise<SimRowX[]> {581 const res = await db582 .prepare(583 SIM_SELECT +584 ` WHERE s.creator_id = ?2585 AND (s.expires_at IS NULL OR s.expires_at > ?3586 OR EXISTS (SELECT 1 FROM stars st WHERE st.kind = 'sim' AND st.target_id = s.id))587 ORDER BY s.created_at DESC`,588 )589 .bind(creatorId, creatorId, now)590 .all<SimRowX>();591 return res.results;592}593594export function stmtInsertSim(595 db: D1Database,596 s: {597 id: string;598 creatorId: string;599 seed: string;600 configJson: string;601 createdAt: number;602 expiresAt: number | null;603 visibility?: 'private' | 'public';604 kind?: 'user' | 'ladder';605 },606): D1PreparedStatement {607 return db608 .prepare(609 `INSERT INTO sims (id, creator_id, seed, config_json, created_at, expires_at, visibility, kind)610 VALUES (?1, ?2, ?3, ?4, ?5, ?6, ?7, ?8)`,611 )612 .bind(613 s.id,614 s.creatorId,615 s.seed,616 s.configJson,617 s.createdAt,618 s.expiresAt,619 s.visibility ?? 'private',620 s.kind ?? 'user',621 );622}623624export function stmtInsertSimScript(625 db: D1Database,626 r: { simId: string; slot: number; scriptId: string; scriptName: string; version: number; hash: string },627): D1PreparedStatement {628 return db629 .prepare(630 `INSERT INTO sim_scripts (sim_id, slot, script_id, script_name, version, hash)631 VALUES (?1, ?2, ?3, ?4, ?5, ?6)`,632 )633 .bind(r.simId, r.slot, r.scriptId, r.scriptName, r.version, r.hash);634}635636export async function getSimScripts(db: D1Database, simId: string): Promise<SimScriptRow[]> {637 const res = await db638 .prepare(`SELECT * FROM sim_scripts WHERE sim_id = ?1 ORDER BY slot`)639 .bind(simId)640 .all<SimScriptRow>();641 return res.results;642}643644/** All sim_scripts rows for every sim of a creator (avoids N+1 in listSims). */645export async function getSimScriptsByCreator(646 db: D1Database,647 creatorId: string,648): Promise<SimScriptRow[]> {649 const res = await db650 .prepare(651 `SELECT ss.* FROM sim_scripts ss JOIN sims s ON s.id = ss.sim_id652 WHERE s.creator_id = ?1 ORDER BY ss.sim_id, ss.slot`,653 )654 .bind(creatorId)655 .all<SimScriptRow>();656 return res.results;657}658659export async function getSimSources(660 db: D1Database,661 simId: string,662): Promise<Array<{ hash: string; source: string; in_r2: number }>> {663 const res = await db664 .prepare(665 `SELECT b.hash AS hash, b.source AS source, b.in_r2 AS in_r2 FROM blobs b666 WHERE b.hash IN (SELECT hash FROM sim_scripts WHERE sim_id = ?1)`,667 )668 .bind(simId)669 .all<{ hash: string; source: string; in_r2: number }>();670 return res.results;671}672673// ---------------------------------------------------------------------------674// Stars675// ---------------------------------------------------------------------------676677export async function countStarsByUser(db: D1Database, userId: string): Promise<number> {678 const row = await db679 .prepare(`SELECT COUNT(*) AS n FROM stars WHERE user_id = ?1`)680 .bind(userId)681 .first<{ n: number }>();682 return row?.n ?? 0;683}684685export async function countStarsOnTarget(686 db: D1Database,687 kind: 'script' | 'sim',688 targetId: string,689): Promise<number> {690 const row = await db691 .prepare(`SELECT COUNT(*) AS n FROM stars WHERE kind = ?1 AND target_id = ?2`)692 .bind(kind, targetId)693 .first<{ n: number }>();694 return row?.n ?? 0;695}696697export async function hasStar(698 db: D1Database,699 userId: string,700 kind: 'script' | 'sim',701 targetId: string,702): Promise<boolean> {703 const row = await db704 .prepare(`SELECT 1 AS x FROM stars WHERE user_id = ?1 AND kind = ?2 AND target_id = ?3`)705 .bind(userId, kind, targetId)706 .first();707 return row !== null;708}709710export function stmtInsertStar(711 db: D1Database,712 userId: string,713 kind: 'script' | 'sim',714 targetId: string,715 now: number,716): D1PreparedStatement {717 // Cap enforced inside the INSERT itself: the service pre-checks the count718 // for a friendly error, but two concurrent stars could both pass that read719 // (TOCTOU), so the statement re-checks atomically and inserts nothing at720 // the limit.721 return db722 .prepare(723 `INSERT OR IGNORE INTO stars (user_id, kind, target_id, created_at)724 SELECT ?1, ?2, ?3, ?4725 WHERE (SELECT COUNT(*) FROM stars WHERE user_id = ?1) < ?5`,726 )727 .bind(userId, kind, targetId, now, LIMITS.MAX_STARS);728}729730/** Returns rows deleted (0 = the user hadn't starred the target). */731export async function deleteStar(732 db: D1Database,733 userId: string,734 kind: 'script' | 'sim',735 targetId: string,736): Promise<number> {737 const res = await db738 .prepare(`DELETE FROM stars WHERE user_id = ?1 AND kind = ?2 AND target_id = ?3`)739 .bind(userId, kind, targetId)740 .run();741 return res.meta.changes;742}743744export function stmtDeleteStarsOnTarget(745 db: D1Database,746 kind: 'script' | 'sim',747 targetId: string,748): D1PreparedStatement {749 return db.prepare(`DELETE FROM stars WHERE kind = ?1 AND target_id = ?2`).bind(kind, targetId);750}751752export async function listStarsByUser(753 db: D1Database,754 userId: string,755): Promise<Array<{ kind: 'script' | 'sim'; target_id: string }>> {756 const res = await db757 .prepare(`SELECT kind, target_id FROM stars WHERE user_id = ?1 ORDER BY created_at DESC`)758 .bind(userId)759 .all<{ kind: 'script' | 'sim'; target_id: string }>();760 return res.results;761}762763/** expires_at setter used by star (NULL) and unstar-last (now + TTL). */764export function stmtSetExpiry(765 db: D1Database,766 kind: 'script' | 'sim',767 id: string,768 expiresAt: number | null,769): D1PreparedStatement {770 const table = kind === 'script' ? 'scripts' : 'sims';771 return db.prepare(`UPDATE ${table} SET expires_at = ?2 WHERE id = ?1`).bind(id, expiresAt);772}773774// ---------------------------------------------------------------------------775// Hearts776// ---------------------------------------------------------------------------777778export async function insertHeart(779 db: D1Database,780 userId: string,781 scriptId: string,782 now: number,783): Promise<void> {784 await db785 .prepare(786 `INSERT OR IGNORE INTO hearts (user_id, script_id, created_at) VALUES (?1, ?2, ?3)`,787 )788 .bind(userId, scriptId, now)789 .run();790}791792export async function deleteHeart(db: D1Database, userId: string, scriptId: string): Promise<number> {793 const res = await db794 .prepare(`DELETE FROM hearts WHERE user_id = ?1 AND script_id = ?2`)795 .bind(userId, scriptId)796 .run();797 return res.meta.changes;798}799800export async function hasHeart(db: D1Database, userId: string, scriptId: string): Promise<boolean> {801 const row = await db802 .prepare(`SELECT 1 AS x FROM hearts WHERE user_id = ?1 AND script_id = ?2`)803 .bind(userId, scriptId)804 .first();805 return row !== null;806}807808// ---------------------------------------------------------------------------809// Models810// ---------------------------------------------------------------------------811812export interface ModelRow {813 id: string;814 name: string;815 lab: string;816 open_weights: number;817 release_date: string | null;818 reasoning: number | null;819}820821export async function modelExists(db: D1Database, id: string): Promise<boolean> {822 const row = await db.prepare(`SELECT 1 AS x FROM models WHERE id = ?1`).bind(id).first();823 return row !== null;824}825826/** Escapes LIKE wildcards; use with `ESCAPE '\\'`. */827export function escapeLike(s: string): string {828 return s.replace(/[\\%_]/g, (c) => '\\' + c);829}830831export async function listModelRows(832 db: D1Database,833 opts: { q?: string; lab?: string; openWeights?: boolean; limit: number },834): Promise<ModelRow[]> {835 const where: string[] = [];836 const params: unknown[] = [];837 if (opts.q !== undefined) {838 const pat = `%${escapeLike(opts.q)}%`;839 params.push(pat, pat, pat);840 where.push(841 `(id LIKE ?${params.length - 2} ESCAPE '\\' OR name LIKE ?${params.length - 1} ESCAPE '\\' OR lab LIKE ?${params.length} ESCAPE '\\')`,842 );843 }844 if (opts.lab !== undefined) {845 params.push(opts.lab);846 where.push(`lab = ?${params.length}`);847 }848 if (opts.openWeights !== undefined) {849 params.push(opts.openWeights ? 1 : 0);850 where.push(`open_weights = ?${params.length}`);851 }852 params.push(opts.limit);853 const sql = `SELECT id, name, lab, open_weights, release_date, reasoning FROM models854 ${where.length > 0 ? 'WHERE ' + where.join(' AND ') : ''}855 ORDER BY release_date IS NULL, release_date DESC, id LIMIT ?${params.length}`;856 const res = await db.prepare(sql).bind(...params).all<ModelRow>();857 return res.results;858}859860// ---------------------------------------------------------------------------861// Ratings & server-verified sim results862// ---------------------------------------------------------------------------863864export function stmtEnsureRating(db: D1Database, scriptId: string, now: number): D1PreparedStatement {865 return db866 .prepare(867 `INSERT OR IGNORE INTO ratings (script_id, rating, games, wins, battles, updated_at)868 VALUES (?1, 1000, 0, 0, 0, ?2)`,869 )870 .bind(scriptId, now);871}872873export async function getRatings(874 db: D1Database,875 scriptIds: readonly string[],876): Promise<Map<string, number>> {877 if (scriptIds.length === 0) return new Map();878 const marks = scriptIds.map((_, i) => `?${i + 1}`).join(', ');879 const res = await db880 .prepare(`SELECT script_id, rating FROM ratings WHERE script_id IN (${marks})`)881 .bind(...scriptIds)882 .all<{ script_id: string; rating: number }>();883 return new Map(res.results.map((r) => [r.script_id, r.rating]));884}885886export async function hasRatingEvents(db: D1Database, simId: string): Promise<boolean> {887 const row = await db888 .prepare(`SELECT 1 AS x FROM rating_events WHERE sim_id = ?1 LIMIT 1`)889 .bind(simId)890 .first();891 return row !== null;892}893894/** Plain INSERT on purpose: inside the rating batch a PK clash aborts the895 * whole transaction, which is exactly the idempotency we want. */896export function stmtInsertRatingEvent(897 db: D1Database,898 e: { simId: string; scriptId: string; delta: number; ratingAfter: number; createdAt: number },899): D1PreparedStatement {900 return db901 .prepare(902 `INSERT INTO rating_events (sim_id, script_id, delta, rating_after, created_at)903 VALUES (?1, ?2, ?3, ?4, ?5)`,904 )905 .bind(e.simId, e.scriptId, e.delta, e.ratingAfter, e.createdAt);906}907908export function stmtApplyRating(909 db: D1Database,910 r: { scriptId: string; rating: number; winInc: number; battlesInc: number; now: number },911): D1PreparedStatement {912 return db913 .prepare(914 `UPDATE ratings SET rating = ?2, games = games + 1, wins = wins + ?3,915 battles = battles + ?4, updated_at = ?5916 WHERE script_id = ?1`,917 )918 .bind(r.scriptId, r.rating, r.winInc, r.battlesInc, r.now);919}920921export function stmtInsertSimResult(922 db: D1Database,923 r: { simId: string; slot: number; scriptId: string; score: number; place: number; battles: number },924): D1PreparedStatement {925 return db926 .prepare(927 `INSERT OR REPLACE INTO sim_results (sim_id, slot, script_id, score, place, battles)928 VALUES (?1, ?2, ?3, ?4, ?5, ?6)`,929 )930 .bind(r.simId, r.slot, r.scriptId, r.score, r.place, r.battles);931}932933/** rating_events within a window, for recent-roster dedup in the ladder. */934export async function listRecentRatingEvents(935 db: D1Database,936 sinceSec: number,937): Promise<Array<{ sim_id: string; script_id: string }>> {938 const res = await db939 .prepare(`SELECT sim_id, script_id FROM rating_events WHERE created_at >= ?1`)940 .bind(sinceSec)941 .all<{ sim_id: string; script_id: string }>();942 return res.results;943}944945// ---------------------------------------------------------------------------946// Browse (public listings) — one shared query builder for browse, hearts,947// leaderboard, and the public script detail head.948// ---------------------------------------------------------------------------949950export interface BrowseRow {951 id: string;952 title: string;953 description: string | null;954 owner_id: string;955 owner_name: string | null;956 model_id: string | null;957 model_name: string | null;958 model_lab: string | null;959 model_open_weights: number | null;960 model_release_date: string | null;961 model_reasoning: number | null;962 rating: number;963 games: number;964 wins: number;965 battles: number;966 hearts: number;967 hearted_by_me: number;968 source_shared: number;969 version: number;970 created_at: number;971 updated_at: number;972 window_delta: number;973}974975export interface BrowseQuery {976 viewerId: string | null;977 sort: 'top' | 'rising' | 'updated' | 'battled';978 /** null = all-time; windowDelta is then a constant 0. */979 windowSeconds: number | null;980 now: number;981 openWeights?: boolean;982 lab?: string;983 releasedAfter?: string;984 releasedBefore?: string;985 q?: string;986 limit: number;987 offset: number;988 /** Restrict to scripts hearted by this user (GET /api/hearts). */989 heartedBy?: string;990 /** Restrict to a single script id (public detail head). */991 id?: string;992}993994/** Exported for tests: builds the LISTED browse SQL with anonymous params995 * bound strictly in order of appearance. */996export function buildBrowseSql(q: BrowseQuery): { sql: string; params: unknown[] } {997 const params: unknown[] = [];998 const deltaExpr = q.windowSeconds === null ? '0' : 'COALESCE(w.delta, 0)';999 params.push(q.viewerId); // hearted_by_me EXISTS1000 let sql = `SELECT1001 s.id AS id,1002 COALESCE(NULLIF(s.title, ''), s.name) AS title,1003 s.description AS description,1004 s.owner_id AS owner_id, ou.name AS owner_name,1005 m.id AS model_id, m.name AS model_name, m.lab AS model_lab,1006 m.open_weights AS model_open_weights, m.release_date AS model_release_date,1007 m.reasoning AS model_reasoning,1008 COALESCE(r.rating, 1000) AS rating, COALESCE(r.games, 0) AS games,1009 COALESCE(r.wins, 0) AS wins, COALESCE(r.battles, 0) AS battles,1010 (SELECT COUNT(*) FROM hearts h WHERE h.script_id = s.id) AS hearts,1011 EXISTS (SELECT 1 FROM hearts h WHERE h.script_id = s.id AND h.user_id = ?) AS hearted_by_me,1012 s.source_shared AS source_shared, s.version AS version, s.created_at AS created_at,1013 COALESCE((SELECT MAX(sv.created_at) FROM script_versions sv WHERE sv.script_id = s.id), s.created_at) AS updated_at,1014 ${deltaExpr} AS window_delta1015 FROM scripts s1016 JOIN users ou ON ou.id = s.owner_id1017 LEFT JOIN models m ON m.id = s.model_id1018 LEFT JOIN ratings r ON r.script_id = s.id`;1019 if (q.windowSeconds !== null) {1020 sql += `1021 LEFT JOIN (SELECT script_id, SUM(delta) AS delta FROM rating_events WHERE created_at >= ? GROUP BY script_id) w1022 ON w.script_id = s.id`;1023 params.push(q.now - q.windowSeconds);1024 }1025 if (q.heartedBy !== undefined) {1026 sql += `1027 JOIN hearts hb ON hb.script_id = s.id AND hb.user_id = ?`;1028 params.push(q.heartedBy);1029 }1030 sql += `1031 WHERE ${LISTED_WHERE}`;1032 if (q.id !== undefined) {1033 sql += ` AND s.id = ?`;1034 params.push(q.id);1035 }1036 // Scripts without a model tag drop out ONLY when a model filter is applied1037 // (any m.* condition is NULL — thus false — for the un-tagged).1038 const modelFiltered =1039 q.openWeights !== undefined ||1040 q.lab !== undefined ||1041 q.releasedAfter !== undefined ||1042 q.releasedBefore !== undefined;1043 if (modelFiltered) sql += ` AND m.id IS NOT NULL`;1044 if (q.openWeights !== undefined) {1045 sql += ` AND m.open_weights = ?`;1046 params.push(q.openWeights ? 1 : 0);1047 }1048 if (q.lab !== undefined) {1049 sql += ` AND m.lab = ?`;1050 params.push(q.lab);1051 }1052 if (q.releasedAfter !== undefined) {1053 sql += ` AND m.release_date >= ?`;1054 params.push(q.releasedAfter);1055 }1056 if (q.releasedBefore !== undefined) {1057 sql += ` AND m.release_date <= ?`;1058 params.push(q.releasedBefore);1059 }1060 if (q.q !== undefined) {1061 const pat = `%${escapeLike(q.q)}%`;1062 sql += ` AND (s.title LIKE ? ESCAPE '\\' OR s.name LIKE ? ESCAPE '\\' OR s.description LIKE ? ESCAPE '\\')`;1063 params.push(pat, pat, pat);1064 }1065 const order = {1066 top: 'rating DESC, games DESC, s.id',1067 rising: 'window_delta DESC, rating DESC, s.id',1068 updated: 'updated_at DESC, s.id',1069 battled: 'battles DESC, rating DESC, s.id',1070 }[q.sort];1071 sql += `1072 ORDER BY ${order} LIMIT ? OFFSET ?`;1073 params.push(q.limit, q.offset);1074 return { sql, params };1075}10761077export async function browseListings(db: D1Database, q: BrowseQuery): Promise<BrowseRow[]> {1078 const { sql, params } = buildBrowseSql(q);1079 const res = await db.prepare(sql).bind(...params).all<BrowseRow>();1080 return res.results;1081}10821083// ---------------------------------------------------------------------------1084// Public script detail1085// ---------------------------------------------------------------------------10861087export async function getScriptVersions(1088 db: D1Database,1089 scriptId: string,1090): Promise<Array<{ version: number; hash: string; created_at: number }>> {1091 const res = await db1092 .prepare(1093 `SELECT version, hash, created_at FROM script_versions WHERE script_id = ?1 ORDER BY version DESC`,1094 )1095 .bind(scriptId)1096 .all<{ version: number; hash: string; created_at: number }>();1097 return res.results;1098}10991100export interface PublicResultRow {1101 sim_id: string;1102 slot: number;1103 script_id: string;1104 score: number;1105 place: number;1106 created_at: number;1107 kind: 'user' | 'ladder';1108}11091110/** All slot results of the `limit` most recent PUBLIC sims this script rated in. */1111export async function getPublicSimResultsForScript(1112 db: D1Database,1113 scriptId: string,1114 limit: number,1115): Promise<PublicResultRow[]> {1116 const res = await db1117 .prepare(1118 `SELECT sr.sim_id AS sim_id, sr.slot AS slot, sr.script_id AS script_id,1119 sr.score AS score, sr.place AS place, si.created_at AS created_at, si.kind AS kind1120 FROM sim_results sr JOIN sims si ON si.id = sr.sim_id1121 WHERE sr.sim_id IN (1122 SELECT sr2.sim_id FROM sim_results sr2 JOIN sims si2 ON si2.id = sr2.sim_id1123 WHERE sr2.script_id = ?1 AND si2.visibility = 'public'1124 ORDER BY si2.created_at DESC LIMIT ?2)1125 ORDER BY si.created_at DESC, sr.sim_id, sr.place, sr.slot`,1126 )1127 .bind(scriptId, limit)1128 .all<PublicResultRow>();1129 return res.results;1130}11311132/** id -> public title (title falling back to name) for the given script ids. */1133export async function getScriptTitles(1134 db: D1Database,1135 ids: readonly string[],1136): Promise<Map<string, string>> {1137 if (ids.length === 0) return new Map();1138 const marks = ids.map((_, i) => `?${i + 1}`).join(', ');1139 const res = await db1140 .prepare(1141 `SELECT id, COALESCE(NULLIF(title, ''), name) AS title FROM scripts WHERE id IN (${marks})`,1142 )1143 .bind(...ids)1144 .all<{ id: string; title: string }>();1145 return new Map(res.results.map((r) => [r.id, r.title]));1146}11471148// ---------------------------------------------------------------------------1149// Account deletion (anonymize-or-delete)1150// ---------------------------------------------------------------------------11511152/** Scripts of `ownerId` that must survive account deletion: someone links to1153 * them, hearted them, or they carry rating history. */1154export async function listCanonicalScriptIds(db: D1Database, ownerId: string): Promise<string[]> {1155 const res = await db1156 .prepare(1157 `SELECT s.id AS id FROM scripts s1158 WHERE s.owner_id = ?1 AND (1159 EXISTS (SELECT 1 FROM scripts o WHERE o.linked_script_id = s.id)1160 OR EXISTS (SELECT 1 FROM hearts h WHERE h.script_id = s.id)1161 OR EXISTS (SELECT 1 FROM rating_events re WHERE re.script_id = s.id))`,1162 )1163 .bind(ownerId)1164 .all<{ id: string }>();1165 return res.results.map((r) => r.id);1166}11671168/** Reassign a canonical script to the ghost. The name is suffixed with the1169 * (unique) script id to dodge the UNIQUE(owner_id, name) constraint when two1170 * deleted users owned same-named scripts. */1171export function stmtReassignScript(db: D1Database, id: string, ghostId: string): D1PreparedStatement {1172 return db1173 .prepare(`UPDATE scripts SET owner_id = ?2, name = name || '~' || id WHERE id = ?1`)1174 .bind(id, ghostId);1175}11761177/** Copy the owner's listing star to the ghost so the script stays LISTED1178 * after the owner row (and its stars) cascade away. */1179export function stmtTransferStarToGhost(1180 db: D1Database,1181 ownerId: string,1182 ghostId: string,1183 scriptId: string,1184): D1PreparedStatement {1185 return db1186 .prepare(1187 `INSERT OR IGNORE INTO stars (user_id, kind, target_id, created_at)1188 SELECT ?2, 'script', target_id, created_at FROM stars1189 WHERE user_id = ?1 AND kind = 'script' AND target_id = ?3`,1190 )1191 .bind(ownerId, ghostId, scriptId);1192}11931194export function stmtDeleteUser(db: D1Database, id: string): D1PreparedStatement {1195 return db.prepare(`DELETE FROM users WHERE id = ?1`).bind(id);1196}11971198// ---------------------------------------------------------------------------1199// Ladder1200// ---------------------------------------------------------------------------12011202export interface LadderScriptRow {1203 id: string;1204 name: string;1205 version: number;1206 hash: string;1207 rating: number;1208}12091210/** LISTED canonical scripts, best rating first (unrated at the 1000 default). */1211export async function listListedForLadder(db: D1Database): Promise<LadderScriptRow[]> {1212 const res = await db1213 .prepare(1214 `SELECT s.id AS id, s.name AS name, s.version AS version, s.hash AS hash,1215 COALESCE(r.rating, 1000) AS rating1216 FROM scripts s LEFT JOIN ratings r ON r.script_id = s.id1217 WHERE ${LISTED_WHERE}1218 ORDER BY rating DESC, s.id`,1219 )1220 .all<LadderScriptRow>();1221 return res.results;1222}