[{"_1":2,"_3":-5,"_4":-5},"loaderData",{"_5":6,"_7":8},"actionData","errors","root",{"_105":106,"_107":108},"routes/embed.x.$handle.$name.$",{"_9":10,"_11":12,"_13":14,"_15":16,"_17":18,"_19":20,"_21":-5},"handle","drmy01bsp6bi","name","signup-login","path","db.ts","project",{"_22":45,"_11":12,"_33":46,"_47":-5,"_48":42,"_49":50,"_51":50,"_52":53,"_54":-5,"_55":56,"_57":58,"_59":32,"_60":-5,"_61":-5,"_62":36,"_63":64,"_65":66,"_67":68,"_69":70,"_71":-5,"_72":73,"_74":73,"_75":76,"_77":76,"_78":76,"_79":73,"_80":73,"_81":82,"_83":84,"_85":86,"_87":42},"file",{"_22":23,"_11":14,"_24":25,"_26":-5,"_27":28,"_29":30,"_31":32,"_33":34,"_35":36,"_37":38,"_39":40,"_41":42,"_43":-5,"_13":44},"permalink","https://www.val.town/x/drmy01bsp6bi/signup-login/code/db.ts","extension","id","bfe521f7-4c90-4468-8fb0-97f26f3ef3bd","fileId","56b52bd8-b2ae-11f1-99cb-1607ee4eb77e","parentId","branchId","e1904dc2-ab4b-4aeb-8c8c-626dd25ed021","branchVersion",2,"latest",true,"createdAt",["D",1789659727383],"version",0,"fileType","script","content","import { sqlite } from \"https://esm.town/v/std/sqlite/main.ts\";\n\nexport type User = {\n id: string;\n email: string;\n password_hash: string;\n created_at: string;\n verified_at: string | null;\n};\n\nexport type LoginCode = {\n id: string;\n user_id: string;\n code_hash: string;\n created_at: string;\n expires_at: string;\n consumed_at: string | null;\n attempts: number;\n};\n\nexport type SessionUser = User & { session_expires_at: string };\n\n// 建表。用 CREATE TABLE IF NOT EXISTS，所以每次冷启动跑一遍也是安全的。\nlet schemaReady: Promise | undefined;\n\nexport function initDb(): Promise {\n schemaReady ??= sqlite.batch([\n {\n sql: `CREATE TABLE IF NOT EXISTS users (\n id TEXT PRIMARY KEY,\n email TEXT NOT NULL COLLATE NOCASE UNIQUE,\n password_hash TEXT NOT NULL,\n created_at TEXT NOT NULL,\n verified_at TEXT\n )`,\n },\n {\n sql: `CREATE TABLE IF NOT EXISTS login_codes (\n id TEXT PRIMARY KEY,\n user_id TEXT NOT NULL,\n code_hash TEXT NOT NULL,\n created_at TEXT NOT NULL,\n expires_at TEXT NOT NULL,\n consumed_at TEXT,\n attempts INTEGER NOT NULL DEFAULT 0\n )`,\n },\n {\n sql: `CREATE TABLE IF NOT EXISTS sessions (\n token_hash TEXT PRIMARY KEY,\n user_id TEXT NOT NULL,\n created_at TEXT NOT NULL,\n expires_at TEXT NOT NULL\n )`,\n },\n { sql: \"CREATE INDEX IF NOT EXISTS idx_login_codes_user ON login_codes(user_id, created_at)\" },\n ]).then(() => undefined);\n return schemaReady;\n}\n\nasync function query(sql: string, args: (string | number)[] = []): Promise {\n const result = await sqlite.execute({ sql, args });\n return result.rows as unknown as T[];\n}\n\nexport async function findUserByEmail(email: string): Promise {\n const [user] = await query(\"SELECT * FROM users WHERE email = ?\", [email]);\n return user ?? null;\n}\n\nexport async function createUser(user: {\n id: string;\n email: string;\n passwordHash: string;\n createdAt: string;\n}): Promise {\n await sqlite.execute({\n sql: \"INSERT INTO users (id, email, password_hash, created_at) VALUES (?, ?, ?, ?)\",\n args: [user.id, user.email, user.passwordHash, user.createdAt],\n });\n}\n\nexport async function markVerified(userId: string, at: string): Promise {\n await sqlite.execute({\n sql: \"UPDATE users SET verified_at = ? WHERE id = ? AND verified_at IS NULL\",\n args: [at, userId],\n });\n}\n\nexport async function insertCode(code: {\n id: string;\n userId: string;\n codeHash: string;\n createdAt: string;\n expiresAt: string;\n}): Promise {\n await sqlite.execute({\n sql: `INSERT INTO login_codes (id, user_id, code_hash, created_at, expires_at)\n VALUES (?, ?, ?, ?, ?)`,\n args: [code.id, code.userId, code.codeHash, code.createdAt, code.expiresAt],\n });\n}\n\n/** 最近一个还没用掉的验证码（可能是过期的，调用方自己判断）。 */\nexport async function findActiveCode(userId: string): Promise {\n const [row] = await query(\n \"SELECT * FROM login_codes WHERE user_id = ? AND consumed_at IS NULL ORDER BY created_at DESC LIMIT 1\",\n [userId],\n );\n return row ?? null;\n}\n\nexport async function consumeCode(codeId: string, at: string): Promise {\n await sqlite.execute({\n sql: \"UPDATE login_codes SET consumed_at = ? WHERE id = ? AND consumed_at IS NULL\",\n args: [at, codeId],\n });\n}\n\nexport async function bumpCodeAttempts(codeId: string): Promise {\n await sqlite.execute({\n sql: \"UPDATE login_codes SET attempts = attempts + 1 WHERE id = ?\",\n args: [codeId],\n });\n}\n\n/** 某个时间点之后这个用户一共申请过几次验证码（用于限流）。 */\nexport async function countCodesSince(userId: string, since: string): Promise {\n const [row] = await query<{ n: number }>(\n \"SELECT COUNT(*) AS n FROM login_codes WHERE user_id = ? AND created_at >= ?\",\n [userId, since],\n );\n return Number(row?.n ?? 0);\n}\n\nexport async function createSession(session: {\n tokenHash: string;\n userId: string;\n createdAt: string;\n expiresAt: string;\n}): Promise {\n await sqlite.execute({\n sql: \"INSERT INTO sessions (token_hash, user_id, created_at, expires_at) VALUES (?, ?, ?, ?)\",\n args: [session.tokenHash, session.userId, session.createdAt, session.expiresAt],\n });\n}\n\nexport async function findSessionUser(tokenHash: string): Promise {\n const [row] = await query(\n `SELECT u.*, s.expires_at AS session_expires_at\n FROM sessions s JOIN users u ON u.id = s.user_id\n WHERE s.token_hash = ?`,\n [tokenHash],\n );\n return row ?? null;\n}\n\nexport async function deleteSession(tokenHash: string): Promise {\n await sqlite.execute({ sql: \"DELETE FROM sessions WHERE token_hash = ?\", args: [tokenHash] });\n}","deleted",false,"label",[14],"4b3bb8fa-4cb4-4853-aec2-dc10c1c339dc",["D",1789659645926],"deletedAt","legacyEnvVars","privacy","public","httpPrivacy","description","邮箱注册 + 登录 + 验证码校验，SQLite 存用户，Hono JSX 页面","imageUrl","userId","8f5f4f03-5bf7-4d70-9213-3b0f291169e6","pinnedFileId","01a0b007-082c-75f1-b566-c43cc36ce931","pinPreview","pinMigrated","screenshotVersion","likeCount","orgId","01a0b001-2963-77e8-86ca-da7e1d5b44e5","tursoName","4b3bb8fa4cb44853aec2dc10c1c339dc","tursoHostname","4b3bb8fa4cb44853aec2dc10c1c339dc-val-town.aws-us-east-2.turso.io","tursoMeasuredAt",["D",1789660265060],"pinned","userPerm","r","valPerm","blobPerm","rw","emailPerm","sqlitePerm","projectPerm","telemetryPerm","user",{"_9":10,"_104":99},"org",{"_9":10,"_98":99,"_100":99,"_55":56,"_101":102,"_103":42},"branch",{"_35":88,"_11":89,"_22":28,"_90":45,"_91":-5,"_92":-5,"_93":-5,"_94":-5,"_95":-5,"_96":12,"_97":10},"isOwned",10,"main","projectId","forkedBranchId","forkedBranchVersion","forkedProjectPrivacy","forkedProjectId","forkedProjectOrgId","projectName","userHandle","displayName","","avatar","tier","free","canRestrictVals","profileImageUrl","ctx",{"_81":-5,"_138":139,"_140":141,"_142":143,"_144":42,"_145":42,"_146":42,"_147":32,"_148":-5,"_149":-7,"_150":-7,"_151":36,"_152":42,"_153":42,"_154":-5,"_155":42,"_156":42,"_157":42,"_158":-5},"ENV",{"_109":110,"_111":112,"_113":89,"_114":115,"_116":117,"_118":119,"_120":121,"_122":123,"_124":125,"_126":127,"_128":129,"_130":131,"_132":133,"_134":135,"_136":137},"REACT_APP_SENTRY_DSN","https://586020d86f05489db63a554690acf1e9@o1398359.ingest.sentry.io/6724499","RENDER_GIT_COMMIT","71c1b20804b3232a7e513be260cfffc1932a9e97","RENDER_GIT_BRANCH","POSTHOG_API_TOKEN","phc_nmoyuAkStEZuUp69rYlg1DZgrIt19Gx0Y4Hwf8tGgJP","POSTHOG_API_HOST","https://wart.val.town","WEB_API_TEMPLATE","https://{val}.web.val.run","APP_URL","https://www.val.town","MODULE_URL","https://esm.town","VANITY_WEB_API_TEMPLATE","https://{label}.val.run/","DENOLS_URL","https://lsp.val.town","NODE_ENV","production","CLOUDFLARE_TURNSTILE_SITE_KEY","0x4AAAAAACtaIZJH7s9rBMZ6","GOOGLE_CLIENT_ID","875989696899-jtsqkhlbdv5jrlsk8631p7ipjg3c6o62.apps.googleusercontent.com","STRIPE_PUBLISHABLE_KEY","pk_live_51N2zvWEpZTTLipIkVEwXrUG9AT0yeOOEYxszarZT1jpsZV58Ly6wlg3SltZpHN26CUalOBmUSY7adQRQiSkCZz2500qSXNkf8Y","API_URL","https://api.val.town","orgs",[],"preferencesKeybindings","default","preferencesTheme","light","preferencesFixers","preferencesCopilot","preferencesSelectionAi","preferencesCloseBrackets","preferencesChatPanelState","preferencesDprint","preferencesDenofmt","notificationCount","flagDenols","isBusinessOrgMember","businessOrgHandle","isBusinessOrgAdmin","isHigherTownieTier","navCollapsed","currentOrgId"]