[{"_1":2,"_179":-5,"_180":-5},"loaderData",{"_3":4,"_93":94},"root",{"_5":6,"_36":37,"_66":67},"ctx",{"_7":8,"_23":-5,"_24":25,"_26":27,"_28":20,"_29":20,"_30":20,"_31":-5,"_32":33,"_34":20,"_35":20},"auth",{"_9":-5,"_10":-5,"_11":-5,"_12":-5,"_13":-5,"_14":-5,"_15":-5,"_16":-5,"_17":-5,"_18":-5,"_19":20,"_21":22},"sessionClaims","sessionId","sessionStatus","userId","actor","orgId","orgRole","orgSlug","orgPermissions","factorVerificationAge","isAuthenticated",false,"tokenType","session_token","user","preferencesKeybindings","default","preferencesTheme","light","preferencesFixers","preferencesCopilot","preferencesSelectionAi","preferencesChatPanelState","notificationCount",0,"flagDenols","isTeamOrgMember","ENV",{"_38":39,"_40":41,"_42":43,"_44":45,"_46":47,"_48":49,"_50":51,"_52":53,"_54":55,"_56":57,"_58":59,"_60":61,"_62":63,"_64":65},"REACT_APP_SENTRY_DSN","https://586020d86f05489db63a554690acf1e9@o1398359.ingest.sentry.io/6724499","VAL_RUNTIME_NAME","prod","RENDER_GIT_COMMIT","6050acf0705e6826bb1b68e27572c5b8979c5bd9","RENDER_GIT_BRANCH","main","CLERK_PUBLISHABLE_KEY","pk_live_Y2xlcmsudmFsLnRvd24k","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","API_URL","https://api.val.town","clerkState",{"_68":69},"__internal_clerk_state",{"_70":71,"_72":47,"_73":74,"_75":74,"_76":20,"_77":74,"_78":74,"_79":74,"_80":74,"_81":82,"_91":20,"_92":20},"__clerk_ssr_state",{"_21":22,"_9":-5,"_10":-5,"_11":-5,"_12":-5,"_13":-5,"_14":-5,"_15":-5,"_16":-5,"_17":-5,"_18":-5,"_19":20},"__publishableKey","__proxyUrl","","__domain","__isSatellite","__signInUrl","__signUpUrl","__afterSignInUrl","__afterSignUpUrl","__clerk_debug",{"_83":20,"_19":20,"_84":74,"_85":86,"_87":74,"_88":47,"_89":20,"_90":74},"isSignedIn","proxyUrl","reason","session-token-and-uat-missing","message","publishableKey","isSatellite","domain","__telemetryDisabled","__telemetryDebug","routes/embed.x.$handle.$name.$",{"_95":96,"_97":98,"_99":100,"_101":102,"_158":159,"_176":177,"_178":-5},"handle","dcm31","name","crm","path","db.ts","project",{"_103":104,"_97":98,"_105":106,"_107":-5,"_108":20,"_109":110,"_111":112,"_113":-5,"_12":114,"_115":116,"_117":118,"_119":-5,"_120":-5,"_121":33,"_14":122,"_123":124,"_125":126,"_127":128,"_129":128,"_130":131,"_132":131,"_133":131,"_134":128,"_135":128,"_23":136,"_139":140,"_144":145,"_157":20},"id","ab39ba3e-02dc-11f1-8b18-42dde27851f2","createdAt",["D",1770328221392],"deletedAt","legacyEnvVars","privacy","public","description","Open source CRM - contacts, companies, deals, activities","imageUrl","9319c175-2593-4f88-ba21-04f2ef7b1c28","pinnedFileId","6756222e-02de-11f1-b5a7-42dde27851f2","pinPreview",true,"pinMigrated","screenshotVersion","likeCount","d663a026-6449-42f6-9156-1d4a2d64d38c","tursoName","ab39ba3e02dc11f18b1842dde27851f2","tursoHostname","ab39ba3e02dc11f18b1842dde27851f2-val-town.aws-us-east-2.turso.io","userPerm","r","valPerm","blobPerm","rw","emailPerm","sqlitePerm","projectPerm","telemetryPerm",{"_95":96,"_137":138},"profileImageUrl","https://imagedelivery.net/iHX6Ovru0O7AjmyT5yZRoA/260ec866-bb5d-4641-4bfe-526cca896a00/square512","org",{"_95":96,"_141":138,"_12":114,"_142":143},"avatar","tier","pro","branch",{"_146":147,"_97":45,"_103":148,"_149":104,"_150":-5,"_151":-5,"_152":-5,"_153":-5,"_154":-5,"_155":98,"_156":96},"version",26,"ab3a05a2-02dc-11f1-8b18-42dde27851f2","projectId","forkedBranchId","forkedBranchVersion","forkedProjectPrivacy","forkedProjectId","forkedProjectOrgId","projectName","userHandle","isOwned","file",{"_103":160,"_97":100,"_161":162,"_163":-5,"_164":148,"_165":166,"_167":118,"_105":168,"_146":33,"_169":170,"_171":172,"_173":20,"_174":-5,"_99":175},"f038abbc-02dd-11f1-9a5d-42dde27851f2","fileId","f038acb6-02dd-11f1-9a5d-42dde27851f2","parentId","branchId","branchVersion",10,"latest",["D",1770328766639],"fileType","script","content","import sqlite from \"https://esm.town/v/std/db/main.ts\";\n\n// --- Schema ---\n\nawait sqlite.execute(`CREATE TABLE IF NOT EXISTS companies (\n id INTEGER PRIMARY KEY AUTOINCREMENT,\n name TEXT NOT NULL,\n domain TEXT,\n industry TEXT,\n size TEXT,\n city TEXT,\n country TEXT,\n notes TEXT,\n created_at TEXT DEFAULT (datetime('now')),\n updated_at TEXT DEFAULT (datetime('now'))\n)`);\n\nawait sqlite.execute(`CREATE TABLE IF NOT EXISTS contacts (\n id INTEGER PRIMARY KEY AUTOINCREMENT,\n first_name TEXT NOT NULL,\n last_name TEXT,\n email TEXT,\n phone TEXT,\n job_title TEXT,\n company_id INTEGER REFERENCES companies(id) ON DELETE SET NULL,\n city TEXT,\n country TEXT,\n notes TEXT,\n created_at TEXT DEFAULT (datetime('now')),\n updated_at TEXT DEFAULT (datetime('now'))\n)`);\n\nawait sqlite.execute(`CREATE TABLE IF NOT EXISTS deals (\n id INTEGER PRIMARY KEY AUTOINCREMENT,\n name TEXT NOT NULL,\n stage TEXT NOT NULL DEFAULT 'lead',\n amount REAL DEFAULT 0,\n currency TEXT DEFAULT 'USD',\n probability INTEGER DEFAULT 0,\n close_date TEXT,\n contact_id INTEGER REFERENCES contacts(id) ON DELETE SET NULL,\n company_id INTEGER REFERENCES companies(id) ON DELETE SET NULL,\n notes TEXT,\n created_at TEXT DEFAULT (datetime('now')),\n updated_at TEXT DEFAULT (datetime('now'))\n)`);\n\nawait sqlite.execute(`CREATE TABLE IF NOT EXISTS activities (\n id INTEGER PRIMARY KEY AUTOINCREMENT,\n type TEXT NOT NULL,\n title TEXT NOT NULL,\n body TEXT,\n contact_id INTEGER REFERENCES contacts(id) ON DELETE CASCADE,\n company_id INTEGER REFERENCES companies(id) ON DELETE CASCADE,\n deal_id INTEGER REFERENCES deals(id) ON DELETE CASCADE,\n due_date TEXT,\n completed INTEGER DEFAULT 0,\n created_at TEXT DEFAULT (datetime('now'))\n)`);\n\n// --- Types ---\n\nexport interface Company {\n id: number;\n name: string;\n domain: string | null;\n industry: string | null;\n size: string | null;\n city: string | null;\n country: string | null;\n notes: string | null;\n created_at: string;\n updated_at: string;\n}\n\nexport interface Contact {\n id: number;\n first_name: string;\n last_name: string | null;\n email: string | null;\n phone: string | null;\n job_title: string | null;\n company_id: number | null;\n city: string | null;\n country: string | null;\n notes: string | null;\n created_at: string;\n updated_at: string;\n company_name?: string | null;\n}\n\nexport interface Deal {\n id: number;\n name: string;\n stage: string;\n amount: number;\n currency: string;\n probability: number;\n close_date: string | null;\n contact_id: number | null;\n company_id: number | null;\n notes: string | null;\n created_at: string;\n updated_at: string;\n contact_name?: string | null;\n company_name?: string | null;\n}\n\nexport interface Activity {\n id: number;\n type: string;\n title: string;\n body: string | null;\n contact_id: number | null;\n company_id: number | null;\n deal_id: number | null;\n due_date: string | null;\n completed: number;\n created_at: string;\n contact_name?: string | null;\n}\n\nexport const STAGES = [\n { id: \"lead\", label: \"Lead\", color: \"indigo\" },\n { id: \"qualified\", label: \"Qualified\", color: \"blue\" },\n { id: \"proposal\", label: \"Proposal\", color: \"purple\" },\n { id: \"negotiation\", label: \"Negotiation\", color: \"amber\" },\n { id: \"won\", label: \"Won\", color: \"emerald\" },\n { id: \"lost\", label: \"Lost\", color: \"red\" },\n] as const;\n\nexport const ACTIVITY_TYPES = [\"note\", \"call\", \"email\", \"meeting\", \"task\"] as const;\n\n// --- Companies ---\n\nexport async function getCompanies(search?: string): Promise {\n if (search) {\n return (await sqlite.execute(\n `SELECT * FROM companies WHERE name LIKE ? ORDER BY updated_at DESC`,\n [`%${search}%`],\n )).rows as unknown as Company[];\n }\n return (await sqlite.execute(`SELECT * FROM companies ORDER BY updated_at DESC`)).rows as unknown as Company[];\n}\n\nexport async function getCompany(id: number): Promise {\n const r = await sqlite.execute(`SELECT * FROM companies WHERE id = ?`, [id]);\n return (r.rows[0] as unknown as Company) || null;\n}\n\nexport async function createCompany(data: Partial) {\n await sqlite.execute(\n `INSERT INTO companies (name, domain, industry, size, city, country, notes) VALUES (?,?,?,?,?,?,?)`,\n [data.name!, data.domain || null, data.industry || null, data.size || null, data.city || null, data.country || null, data.notes || null],\n );\n}\n\nexport async function updateCompany(id: number, data: Partial) {\n await sqlite.execute(\n `UPDATE companies SET name=?, domain=?, industry=?, size=?, city=?, country=?, notes=?, updated_at=datetime('now') WHERE id=?`,\n [data.name!, data.domain || null, data.industry || null, data.size || null, data.city || null, data.country || null, data.notes || null, id],\n );\n}\n\nexport async function deleteCompany(id: number) {\n await sqlite.execute(`DELETE FROM companies WHERE id=?`, [id]);\n}\n\n// --- Contacts ---\n\nexport async function getContacts(search?: string): Promise {\n const base = `SELECT c.*, co.name as company_name FROM contacts c LEFT JOIN companies co ON c.company_id = co.id`;\n if (search) {\n return (await sqlite.execute(\n `${base} WHERE c.first_name LIKE ? OR c.last_name LIKE ? OR c.email LIKE ? ORDER BY c.updated_at DESC`,\n [`%${search}%`, `%${search}%`, `%${search}%`],\n )).rows as unknown as Contact[];\n }\n return (await sqlite.execute(`${base} ORDER BY c.updated_at DESC`)).rows as unknown as Contact[];\n}\n\nexport async function getContact(id: number): Promise {\n const r = await sqlite.execute(\n `SELECT c.*, co.name as company_name FROM contacts c LEFT JOIN companies co ON c.company_id = co.id WHERE c.id = ?`,\n [id],\n );\n return (r.rows[0] as unknown as Contact) || null;\n}\n\nexport async function createContact(data: Partial) {\n await sqlite.execute(\n `INSERT INTO contacts (first_name, last_name, email, phone, job_title, company_id, city, country, notes) VALUES (?,?,?,?,?,?,?,?,?)`,\n [data.first_name!, data.last_name || null, data.email || null, data.phone || null, data.job_title || null, data.company_id || null, data.city || null, data.country || null, data.notes || null],\n );\n}\n\nexport async function updateContact(id: number, data: Partial) {\n await sqlite.execute(\n `UPDATE contacts SET first_name=?, last_name=?, email=?, phone=?, job_title=?, company_id=?, city=?, country=?, notes=?, updated_at=datetime('now') WHERE id=?`,\n [data.first_name!, data.last_name || null, data.email || null, data.phone || null, data.job_title || null, data.company_id || null, data.city || null, data.country || null, data.notes || null, id],\n );\n}\n\nexport async function deleteContact(id: number) {\n await sqlite.execute(`DELETE FROM contacts WHERE id=?`, [id]);\n}\n\n// --- Deals ---\n\nexport async function getDeals(stage?: string): Promise {\n const base = `SELECT d.*, c.first_name || ' ' || COALESCE(c.last_name,'') as contact_name, co.name as company_name\n FROM deals d LEFT JOIN contacts c ON d.contact_id = c.id LEFT JOIN companies co ON d.company_id = co.id`;\n if (stage) {\n return (await sqlite.execute(`${base} WHERE d.stage = ? ORDER BY d.updated_at DESC`, [stage])).rows as unknown as Deal[];\n }\n return (await sqlite.execute(`${base} ORDER BY d.updated_at DESC`)).rows as unknown as Deal[];\n}\n\nexport async function getDeal(id: number): Promise {\n const r = await sqlite.execute(\n `SELECT d.*, c.first_name || ' ' || COALESCE(c.last_name,'') as contact_name, co.name as company_name\n FROM deals d LEFT JOIN contacts c ON d.contact_id = c.id LEFT JOIN companies co ON d.company_id = co.id WHERE d.id = ?`,\n [id],\n );\n return (r.rows[0] as unknown as Deal) || null;\n}\n\nexport async function createDeal(data: Partial) {\n await sqlite.execute(\n `INSERT INTO deals (name, stage, amount, currency, probability, close_date, contact_id, company_id, notes) VALUES (?,?,?,?,?,?,?,?,?)`,\n [data.name!, data.stage || \"lead\", data.amount || 0, data.currency || \"USD\", data.probability || 0, data.close_date || null, data.contact_id || null, data.company_id || null, data.notes || null],\n );\n}\n\nexport async function updateDeal(id: number, data: Partial) {\n await sqlite.execute(\n `UPDATE deals SET name=?, stage=?, amount=?, currency=?, probability=?, close_date=?, contact_id=?, company_id=?, notes=?, updated_at=datetime('now') WHERE id=?`,\n [data.name!, data.stage!, data.amount || 0, data.currency || \"USD\", data.probability || 0, data.close_date || null, data.contact_id || null, data.company_id || null, data.notes || null, id],\n );\n}\n\nexport async function updateDealStage(id: number, stage: string) {\n await sqlite.execute(`UPDATE deals SET stage=?, updated_at=datetime('now') WHERE id=?`, [stage, id]);\n}\n\nexport async function deleteDeal(id: number) {\n await sqlite.execute(`DELETE FROM deals WHERE id=?`, [id]);\n}\n\n// --- Activities ---\n\nexport async function getActivities(filters?: { contact_id?: number; company_id?: number; deal_id?: number }): Promise {\n let where = \"1=1\";\n const args: any[] = [];\n if (filters?.contact_id) { where += \" AND a.contact_id = ?\"; args.push(filters.contact_id); }\n if (filters?.company_id) { where += \" AND a.company_id = ?\"; args.push(filters.company_id); }\n if (filters?.deal_id) { where += \" AND a.deal_id = ?\"; args.push(filters.deal_id); }\n return (await sqlite.execute(\n `SELECT a.*, c.first_name || ' ' || COALESCE(c.last_name,'') as contact_name\n FROM activities a LEFT JOIN contacts c ON a.contact_id = c.id WHERE ${where} ORDER BY a.created_at DESC LIMIT 100`,\n args,\n )).rows as unknown as Activity[];\n}\n\nexport async function createActivity(data: Partial) {\n await sqlite.execute(\n `INSERT INTO activities (type, title, body, contact_id, company_id, deal_id, due_date, completed) VALUES (?,?,?,?,?,?,?,?)`,\n [data.type!, data.title!, data.body || null, data.contact_id || null, data.company_id || null, data.deal_id || null, data.due_date || null, data.completed ? 1 : 0],\n );\n}\n\nexport async function deleteActivity(id: number) {\n await sqlite.execute(`DELETE FROM activities WHERE id=?`, [id]);\n}\n\n// --- Dashboard ---\n\nexport async function getDashboardStats() {\n const [contacts, companies, openDeals, wonDeals] = await Promise.all([\n sqlite.execute(`SELECT COUNT(*) as c FROM contacts`),\n sqlite.execute(`SELECT COUNT(*) as c FROM companies`),\n sqlite.execute(`SELECT COUNT(*) as c, COALESCE(SUM(amount),0) as total FROM deals WHERE stage NOT IN ('won','lost')`),\n sqlite.execute(`SELECT COUNT(*) as c, COALESCE(SUM(amount),0) as total FROM deals WHERE stage = 'won'`),\n ]);\n return {\n totalContacts: contacts.rows[0].c as number,\n totalCompanies: companies.rows[0].c as number,\n openDeals: openDeals.rows[0].c as number,\n openPipelineValue: openDeals.rows[0].total as number,\n wonDeals: wonDeals.rows[0].c as number,\n wonRevenue: wonDeals.rows[0].total as number,\n };\n}\n","deleted","label",[100],"permalink","https://www.val.town/x/dcm31/crm/code/db.ts","extension","actionData","errors"]