[{"_1":2,"_3":-5,"_4":-5},"loaderData",{"_5":6,"_7":8},"actionData","errors","root",{"_114":115,"_116":117},"routes/embed.x.$handle.$name.$",{"_9":10,"_11":12,"_13":14,"_15":16,"_17":18,"_19":20,"_21":-5},"handle","the_haven","name","haven-housing-directory","path","backend/db.ts","project",{"_22":48,"_11":12,"_35":49,"_50":-5,"_51":44,"_52":53,"_54":53,"_55":56,"_57":58,"_59":60,"_61":62,"_63":34,"_64":-5,"_65":-5,"_66":67,"_68":69,"_70":71,"_72":73,"_74":75,"_76":-5,"_77":78,"_79":78,"_80":81,"_82":81,"_83":81,"_84":78,"_85":78,"_86":87,"_88":89,"_90":91,"_92":44},"file",{"_22":23,"_11":24,"_25":26,"_27":28,"_29":30,"_31":32,"_33":34,"_35":36,"_37":38,"_39":40,"_41":42,"_43":44,"_45":-5,"_13":46},"permalink","https://www.val.town/x/the_haven/haven-housing-directory/code/backend/db.ts","extension","id","6415ea70-8baa-455a-981c-c26bad485335","db.ts","fileId","2c1523d0-7c97-11f1-83ac-1607ee4eb77e","parentId","102650c1-16df-4425-91f4-ad09aa6fdd92","branchId","cb93a182-7d33-477a-951e-56f9a7b9bf82","branchVersion",108,"latest",true,"createdAt",["D",1789310196556],"version",16,"fileType","script","content","import { sqlite } from \"https://esm.town/v/std/sqlite/main.ts\";\n\nconst TABLE = \"listings_v2\";\nconst USERS = \"users_v1\";\nconst SESSIONS = \"sessions_v1\";\nconst RESETS = \"password_resets_v1\";\nconst HITS = \"site_hits_v1\";\n\nexport const LISTING_TYPES = [\n \"Rental Agency\",\n \"Senior Living\",\n \"Rental Property\",\n \"Private Landlord\",\n \"Support Services\",\n] as const;\nexport type ListingType = (typeof LISTING_TYPES)[number];\n// Backwards-friendly alias\nexport const HOUSING_TYPES = LISTING_TYPES;\n\nexport const AREAS = [\n \"Charlottesville\",\n \"Albemarle\",\n \"Louisa\",\n \"Greene\",\n \"Nelson\",\n \"Fluvanna\",\n \"Multiple\",\n] as const;\n\nexport const ROLES = [\"admin\", \"editor\", \"viewer\"] as const;\nexport type Role = (typeof ROLES)[number];\n\nexport interface Listing {\n id: number;\n location: string; // comma-separated list of AREAS, may be blank\n name: string;\n website: string;\n type: string;\n phone: string;\n contact_name: string;\n notes: string;\n // staff-only fields (never sent to non-logged-in users)\n staff_notes: string;\n unsuitable_notes: string;\n date_validated: string;\n validated_by: string;\n created_at: string;\n updated_at: string;\n}\n\nexport type ListingInput = Omit;\n\nconst LISTING_COLS =\n \"id, location, name, website, type, phone, contact_name, notes, staff_notes, unsuitable_notes, date_validated, validated_by, created_at, updated_at\";\n\nconst STAFF_ONLY_COLS: (keyof Listing)[] = [\n \"staff_notes\",\n \"unsuitable_notes\",\n \"date_validated\",\n \"validated_by\",\n];\n\n/** Remove staff-only fields before sending a listing to the public. */\nexport function stripStaffFields(listing: Listing): Partial {\n const copy: any = { ...listing };\n for (const col of STAFF_ONLY_COLS) delete copy[col];\n return copy;\n}\n\nexport async function ensureTableAndSeed() {\n await sqlite.execute(`CREATE TABLE IF NOT EXISTS ${TABLE} (\n id INTEGER PRIMARY KEY AUTOINCREMENT,\n location TEXT NOT NULL DEFAULT '',\n name TEXT NOT NULL DEFAULT '',\n website TEXT NOT NULL DEFAULT '',\n type TEXT NOT NULL DEFAULT 'Rental Property',\n phone TEXT NOT NULL DEFAULT '',\n contact_name TEXT NOT NULL DEFAULT '',\n notes TEXT NOT NULL DEFAULT '',\n staff_notes TEXT NOT NULL DEFAULT '',\n unsuitable_notes TEXT NOT NULL DEFAULT '',\n date_validated TEXT NOT NULL DEFAULT '',\n validated_by TEXT NOT NULL DEFAULT '',\n created_at TEXT NOT NULL DEFAULT (datetime('now')),\n updated_at TEXT NOT NULL DEFAULT (datetime('now'))\n )`);\n\n // One-time migration: drop the old v1 table (restructured schema; data was cleared)\n await sqlite.execute(`CREATE TABLE IF NOT EXISTS meta_v1 (key TEXT PRIMARY KEY, value TEXT)`);\n const done = await sqlite.execute({ sql: `SELECT value FROM meta_v1 WHERE key = 'v1_dropped'` });\n if (done.rows.length === 0) {\n try {\n await sqlite.execute(`DROP TABLE IF EXISTS listings_v1`);\n } catch {\n // ignore\n }\n await sqlite.execute({ sql: `INSERT INTO meta_v1 (key, value) VALUES ('v1_dropped', datetime('now'))` });\n }\n\n await sqlite.execute(`CREATE TABLE IF NOT EXISTS ${USERS} (\n id INTEGER PRIMARY KEY AUTOINCREMENT,\n email TEXT NOT NULL UNIQUE,\n password_hash TEXT NOT NULL,\n first_name TEXT NOT NULL DEFAULT '',\n last_name TEXT NOT NULL DEFAULT '',\n role TEXT NOT NULL DEFAULT 'viewer',\n active INTEGER NOT NULL DEFAULT 1,\n created_at TEXT NOT NULL DEFAULT (datetime('now')),\n last_login TEXT NOT NULL DEFAULT ''\n )`);\n\n await sqlite.execute(`CREATE TABLE IF NOT EXISTS ${SESSIONS} (\n token TEXT PRIMARY KEY,\n user_id INTEGER NOT NULL,\n created_at TEXT NOT NULL DEFAULT (datetime('now'))\n )`);\n\n await sqlite.execute(`CREATE TABLE IF NOT EXISTS ${HITS} (\n id INTEGER PRIMARY KEY AUTOINCREMENT,\n ts TEXT NOT NULL DEFAULT (datetime('now')),\n kind TEXT NOT NULL DEFAULT 'directory_view',\n staff INTEGER NOT NULL DEFAULT 0\n )`);\n\n await sqlite.execute(`CREATE TABLE IF NOT EXISTS ${RESETS} (\n token TEXT PRIMARY KEY,\n user_id INTEGER NOT NULL,\n expires_at TEXT NOT NULL,\n used INTEGER NOT NULL DEFAULT 0\n )`);\n}\n\n// ---------- Listings ----------\n\nexport async function listListings(): Promise {\n const result = await sqlite.execute(\n `SELECT ${LISTING_COLS} FROM ${TABLE} ORDER BY type, CASE WHEN name = '' THEN 1 ELSE 0 END, name COLLATE NOCASE`,\n );\n return result.rows as unknown as Listing[];\n}\n\nexport function validateListingInput(input: any): string | null {\n if (input.type && !(LISTING_TYPES as readonly string[]).includes(input.type)) {\n return `Invalid type: ${input.type}`;\n }\n if (input.location) {\n const parts = String(input.location).split(\",\").map((s: string) => s.trim()).filter(Boolean);\n const bad = parts.filter((p: string) => !(AREAS as readonly string[]).includes(p));\n if (bad.length) return `Invalid location — must be one or more of: ${AREAS.join(\", \")}`;\n }\n return null;\n}\n\nexport async function createListing(input: ListingInput): Promise {\n await sqlite.execute({\n sql: `INSERT INTO ${TABLE} (location, name, website, type, phone, contact_name, notes, staff_notes, unsuitable_notes, date_validated, validated_by)\n VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)`,\n args: [\n input.location ?? \"\", input.name ?? \"\", input.website ?? \"\",\n input.type ?? \"Rental Property\", input.phone ?? \"\", input.contact_name ?? \"\",\n input.notes ?? \"\", input.staff_notes ?? \"\", input.unsuitable_notes ?? \"\",\n input.date_validated ?? \"\", input.validated_by ?? \"\",\n ],\n });\n}\n\nexport async function getListing(id: number): Promise {\n const result = await sqlite.execute({ sql: `SELECT ${LISTING_COLS} FROM ${TABLE} WHERE id = ?`, args: [id] });\n return (result.rows[0] as Listing) ?? null;\n}\n\nexport async function updateListing(id: number, input: ListingInput): Promise {\n await sqlite.execute({\n sql: `UPDATE ${TABLE}\n SET location = ?, name = ?, website = ?, type = ?, phone = ?, contact_name = ?, notes = ?,\n staff_notes = ?, unsuitable_notes = ?, date_validated = ?, validated_by = ?,\n updated_at = datetime('now')\n WHERE id = ?`,\n args: [\n input.location ?? \"\", input.name ?? \"\", input.website ?? \"\",\n input.type ?? \"Rental Property\", input.phone ?? \"\", input.contact_name ?? \"\",\n input.notes ?? \"\", input.staff_notes ?? \"\", input.unsuitable_notes ?? \"\",\n input.date_validated ?? \"\", input.validated_by ?? \"\",\n id,\n ],\n });\n}\n\nexport async function deleteListing(id: number): Promise {\n await sqlite.execute({ sql: `DELETE FROM ${TABLE} WHERE id = ?`, args: [id] });\n}\n\n// ---------- Users / auth ----------\n\nexport interface User {\n id: number;\n email: string;\n first_name: string;\n last_name: string;\n role: Role;\n active: number;\n created_at: string;\n last_login: string;\n}\n\nconst USER_COLS = \"id, email, password_hash, first_name, last_name, role, active, created_at, last_login\";\n\nexport async function getUserByEmail(email: string): Promise<(User & { password_hash: string }) | null> {\n const r = await sqlite.execute({ sql: `SELECT ${USER_COLS} FROM ${USERS} WHERE email = ? COLLATE NOCASE`, args: [email.trim()] });\n return (r.rows[0] as any) ?? null;\n}\n\nexport async function getUserById(id: number): Promise {\n const r = await sqlite.execute({ sql: `SELECT id, email, first_name, last_name, role, active, created_at, last_login FROM ${USERS} WHERE id = ?`, args: [id] });\n return (r.rows[0] as User) ?? null;\n}\n\nexport async function listUsers(): Promise {\n const r = await sqlite.execute(`SELECT id, email, first_name, last_name, role, active, created_at, last_login FROM ${USERS} ORDER BY email COLLATE NOCASE`);\n return r.rows as unknown as User[];\n}\n\nexport async function createUser(u: { email: string; password_hash: string; first_name: string; last_name: string; role: Role }): Promise {\n await sqlite.execute({\n sql: `INSERT INTO ${USERS} (email, password_hash, first_name, last_name, role) VALUES (?, ?, ?, ?, ?)`,\n args: [u.email.trim(), u.password_hash, u.first_name, u.last_name, u.role],\n });\n}\n\nexport async function updateUserPassword(id: number, password_hash: string): Promise {\n await sqlite.execute({ sql: `UPDATE ${USERS} SET password_hash = ? WHERE id = ?`, args: [password_hash, id] });\n}\n\nexport async function updateUserRole(id: number, role: Role): Promise {\n await sqlite.execute({ sql: `UPDATE ${USERS} SET role = ? WHERE id = ?`, args: [role, id] });\n}\n\nexport async function setUserActive(id: number, active: boolean): Promise {\n await sqlite.execute({ sql: `UPDATE ${USERS} SET active = ? WHERE id = ?`, args: [active ? 1 : 0, id] });\n}\n\nexport async function stampLogin(id: number): Promise {\n await sqlite.execute({ sql: `UPDATE ${USERS} SET last_login = datetime('now') WHERE id = ?`, args: [id] });\n}\n\nexport async function countAdmins(): Promise {\n const r = await sqlite.execute(`SELECT COUNT(*) AS n FROM ${USERS} WHERE role = 'admin' AND active = 1`);\n return Number((r.rows[0] as any).n);\n}\n\n// ---------- Sessions ----------\n\nexport async function createSession(token: string, userId: number): Promise {\n await sqlite.execute({ sql: `INSERT INTO ${SESSIONS} (token, user_id) VALUES (?, ?)`, args: [token, userId] });\n}\n\nexport async function getSessionUser(token: string): Promise {\n const r = await sqlite.execute({\n sql: `SELECT u.id, u.email, u.first_name, u.last_name, u.role, u.active, u.created_at, u.last_login\n FROM ${SESSIONS} s JOIN ${USERS} u ON u.id = s.user_id WHERE s.token = ?`,\n args: [token],\n });\n const user = r.rows[0] as User | undefined;\n return user && user.active ? user : null;\n}\n\nexport async function deleteSession(token: string): Promise {\n await sqlite.execute({ sql: `DELETE FROM ${SESSIONS} WHERE token = ?`, args: [token] });\n}\n\n// ---------- Password resets ----------\n\nexport async function createResetToken(token: string, userId: number): Promise {\n await sqlite.execute({ sql: `DELETE FROM ${RESETS} WHERE user_id = ?`, args: [userId] });\n const expires = new Date(Date.now() + 60 * 60 * 1000).toISOString();\n await sqlite.execute({ sql: `INSERT INTO ${RESETS} (token, user_id, expires_at) VALUES (?, ?, ?)`, args: [token, userId, expires] });\n}\n\nexport async function consumeResetToken(token: string): Promise {\n const r = await sqlite.execute({ sql: `SELECT user_id, expires_at, used FROM ${RESETS} WHERE token = ?`, args: [token] });\n const row = r.rows[0] as any;\n if (!row || row.used) return null;\n if (new Date(row.expires_at) < new Date()) return null;\n await sqlite.execute({ sql: `UPDATE ${RESETS} SET used = 1 WHERE token = ?`, args: [token] });\n return row.user_id as number;\n}\n// ---------- Site hits (activity tracking) ----------\n\nexport async function recordHit(staff: boolean): Promise {\n await sqlite.execute({\n sql: `INSERT INTO site_hits_v1 (staff) VALUES (?)`,\n args: [staff ? 1 : 0],\n });\n}\n\nexport interface HitStats {\n total: number;\n totalPublic: number;\n totalStaff: number;\n last7: number;\n last30: number;\n today: number;\n byDay: { day: string; total: number; public: number; staff: number }[];\n}\n\nexport async function getHitStats(): Promise {\n const one = async (sql: string): Promise =>\n Number((await sqlite.execute(sql)).rows[0]?.n ?? 0);\n const total = await one(`SELECT COUNT(*) AS n FROM site_hits_v1`);\n const totalStaff = await one(`SELECT COUNT(*) AS n FROM site_hits_v1 WHERE staff = 1`);\n const last7 = await one(`SELECT COUNT(*) AS n FROM site_hits_v1 WHERE ts >= datetime('now', '-7 days')`);\n const last30 = await one(`SELECT COUNT(*) AS n FROM site_hits_v1 WHERE ts >= datetime('now', '-30 days')`);\n const today = await one(`SELECT COUNT(*) AS n FROM site_hits_v1 WHERE date(ts) = date('now')`);\n const byDayRes = await sqlite.execute(`\n SELECT date(ts) AS day, COUNT(*) AS total,\n SUM(CASE WHEN staff = 0 THEN 1 ELSE 0 END) AS public_hits,\n SUM(CASE WHEN staff = 1 THEN 1 ELSE 0 END) AS staff_hits\n FROM site_hits_v1\n WHERE ts >= datetime('now', '-30 days')\n GROUP BY date(ts) ORDER BY day DESC`);\n return {\n total,\n totalPublic: total - totalStaff,\n totalStaff,\n last7,\n last30,\n today,\n byDay: byDayRes.rows.map((r: any) => ({\n day: r.day,\n total: Number(r.total),\n public: Number(r.public_hits),\n staff: Number(r.staff_hits),\n })),\n };\n}\n","deleted",false,"label",[47,24],"backend","6d79585e-4f87-4a0f-9f9e-e0782f6ee9c5",["D",1783712246617],"deletedAt","legacyEnvVars","privacy","public","httpPrivacy","description","Searchable rental directory for The Haven (Charlottesville)","imageUrl","https://imagedelivery.net/iHX6Ovru0O7AjmyT5yZRoA/64dc00cc-94d9-47dd-81b1-dde02610d900/square512?exp=1789563599&sig=c3b06339283e4bc07165fa54c09036aa25cea7ac6108dec2f68b1acc60fad95f","userId","a0bf3b31-15a5-4d5c-880e-4b1e22c9bc18","pinnedFileId","019f4d88-ebd3-7218-9092-185c08774ad1","pinPreview","pinMigrated","screenshotVersion","likeCount",0,"orgId","019f4d98-342f-73e3-90b3-c7f210ff5067","tursoName","6d79585e4f874a0f9f9ee0782f6ee9c5","tursoHostname","6d79585e4f874a0f9f9ee0782f6ee9c5-val-town.aws-us-east-2.turso.io","tursoMeasuredAt",["D",1789525245864],"pinned","userPerm","r","valPerm","blobPerm","rw","emailPerm","sqlitePerm","projectPerm","telemetryPerm","user",{"_9":10,"_113":-5},"org",{"_9":10,"_107":108,"_109":-5,"_59":-5,"_110":111,"_112":34},"branch",{"_37":93,"_11":94,"_22":30,"_95":48,"_96":97,"_98":99,"_100":53,"_101":102,"_103":104,"_105":12,"_106":10},"isOwned",128,"main","projectId","forkedBranchId","961078ea-f528-11ef-aa68-569c3dd06744","forkedBranchVersion",89,"forkedProjectPrivacy","forkedProjectId","96102714-f528-11ef-aa68-569c3dd06744","forkedProjectOrgId","019aa807-52c2-709c-928b-2029bae9a558","projectName","userHandle","displayName","The Haven","avatar","tier","business","canRestrictVals","profileImageUrl","ctx",{"_86":-5,"_147":148,"_149":150,"_151":152,"_153":44,"_154":44,"_155":44,"_156":34,"_157":-5,"_158":-7,"_159":-7,"_160":67,"_161":44,"_162":44,"_163":-5,"_164":44,"_165":44,"_166":44,"_167":-5},"ENV",{"_118":119,"_120":121,"_122":94,"_123":124,"_125":126,"_127":128,"_129":130,"_131":132,"_133":134,"_135":136,"_137":138,"_139":140,"_141":142,"_143":144,"_145":146},"REACT_APP_SENTRY_DSN","https://586020d86f05489db63a554690acf1e9@o1398359.ingest.sentry.io/6724499","RENDER_GIT_COMMIT","e87eb04e707cf64e7e532c8e8ec395f209e17f15","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"]