\n );\n}\n\nfunction CustomerInteractionForm({ onSubmit }) {\n const [email, setEmail] = useState(\"\");\n const [firstName, setFirstName] = useState(\"\");\n const [lastName, setLastName] = useState(\"\");\n const [interactionDate, setInteractionDate] = useState(new Date().toISOString().split(\"T\")[0]);\n const [notes, setNotes] = useState(\"\");\n const [additionalNotesLink, setAdditionalNotesLink] = useState(\"\");\n const formRef = useRef(null);\n const emailInputRef = useRef(null);\n const [tags, setTags] = useState([]);\n const [tagInput, setTagInput] = useState(\"\");\n\n // Fetch previous customer details when email changes\n useEffect(() => {\n const fetchCustomerDetails = async () => {\n if (!email) return;\n\n try {\n const response = await fetch(`/get-customer-details?email=${encodeURIComponent(email)}`);\n if (response.ok) {\n const customerDetails = await response.json();\n if (customerDetails) {\n setFirstName(customerDetails.first_name || \"\");\n setLastName(customerDetails.last_name || \"\");\n }\n }\n } catch (error) {\n console.error(\"Error fetching customer details:\", error);\n }\n };\n\n fetchCustomerDetails();\n }, [email]);\n\n // Add keyboard shortcut for form submission\n // Inside CustomerInteractionForm component\n useEffect(() => {\n const handleKeyDown = (e) => {\n // If we're in the tag input, let handleTagInput handle cmd-k\n if (e.target.className === \"tag-input\") {\n return;\n }\n\n // For other inputs, handle cmd-k normally\n if ((e.metaKey || e.ctrlKey) && e.key.toLowerCase() === \"k\") {\n e.preventDefault();\n formRef.current?.requestSubmit();\n }\n };\n\n // Add event listener to window\n window.addEventListener(\"keydown\", handleKeyDown, true);\n\n // Cleanup event listener\n return () => {\n window.removeEventListener(\"keydown\", handleKeyDown, true);\n };\n }, []);\n\n useEffect(() => {\n emailInputRef.current?.focus();\n }, []);\n\n const handleTagInput = (e) => {\n // Stop cmd-k from removing tags\n if ((e.metaKey || e.ctrlKey) && e.key.toLowerCase() === \"k\") {\n e.preventDefault();\n formRef.current?.requestSubmit();\n return;\n }\n\n if (e.key === \"Enter\" || e.key === \",\") {\n e.preventDefault();\n const newTag = tagInput.trim();\n if (newTag && !tags.includes(newTag)) {\n setTags([...tags, newTag]);\n }\n setTagInput(\"\");\n }\n };\n\n const removeTag = (tagToRemove, event) => {\n // If this was triggered by a keyboard event, don't remove the tag\n if (event && (event.metaKey || event.ctrlKey)) {\n return;\n }\n setTags(tags.filter(tag => tag !== tagToRemove));\n };\n\n const handleSubmit = async (e) => {\n e.preventDefault();\n try {\n const response = await fetch(\"/add-interaction\", {\n method: \"POST\",\n headers: { \"Content-Type\": \"application/json\" },\n body: JSON.stringify({\n firstName,\n lastName,\n email,\n interactionDate,\n notes,\n additionalNotesLink,\n tags, // Add this line\n }),\n });\n if (response.ok) {\n setFirstName(\"\");\n setLastName(\"\");\n setEmail(\"\");\n setNotes(\"\");\n setAdditionalNotesLink(\"\");\n setTags([]); // Reset tags\n setInteractionDate(new Date().toLocaleDateString(\"en-US\"));\n onSubmit();\n }\n } catch (error) {\n console.error(\"Error submitting interaction:\", error);\n }\n };\n\n return (\n

\n setEmail(e.target.value)}\n required\n />\n

\n setFirstName(e.target.value)}\n required\n />\n setLastName(e.target.value)}\n required\n />\n

\n setInteractionDate(e.target.value)}\n required\n />\n {/* Move the Key Takeaway and Additional Notes fields BEFORE the tags */}\n setNotes(e.target.value)}\n required\n />\n setAdditionalNotesLink(e.target.value)}\n />\n {/* Tags section moved to the end */}\n

\n {tags.map(tag => (\n \n {tag}\n removeTag(tag, e)}\n className=\"tag-remove\"\n >\n ×\n \n \n ))}\n

\n setTagInput(e.target.value)}\n onKeyDown={handleTagInput}\n className=\"tag-input\"\n />\n

\n \n \n );\n}\n\nfunction InteractionList() {\n const [customerInteractions, setCustomerInteractions] = useState({});\n const [copiedEmail, setCopiedEmail] = useState(null);\n const [currentPage, setCurrentPage] = useState(1);\n const [totalInteractions, setTotalInteractions] = useState(0);\n const [selectedTags, setSelectedTags] = useState([]);\n const [availableTags, setAvailableTags] = useState([]);\n const [tagCounts, setTagCounts] = useState({});\n const [uniqueCustomerCount, setUniqueCustomerCount] = useState(0);\n\n const fetchUniqueCustomerCount = async () => {\n try {\n const response = await fetch(\"/get-unique-customer-count\");\n const data = await response.json();\n setUniqueCustomerCount(data.count);\n } catch (error) {\n console.error(\"Error fetching unique customer count:\", error);\n }\n };\n\n useEffect(() => {\n fetchInteractions();\n fetchUniqueCustomerCount();\n }, [currentPage, selectedTags]);\n\n useEffect(() => {\n setCurrentPage(1);\n }, [selectedTags]);\n\n const handleCopyEmail = async (email) => {\n try {\n await navigator.clipboard.writeText(email);\n setCopiedEmail(email);\n setTimeout(() => setCopiedEmail(null), 2000);\n } catch (err) {\n console.error(\"Failed to copy email:\", err);\n }\n };\n\n const handleDeleteInteraction = async (id) => {\n try {\n const response = await fetch(\"/delete-interaction\", {\n method: \"DELETE\",\n headers: { \"Content-Type\": \"application/json\" },\n body: JSON.stringify({ id }),\n });\n\n if (response.ok) {\n fetchInteractions();\n }\n } catch (error) {\n console.error(\"Error deleting interaction:\", error);\n }\n };\n\n const handleTagSelect = (tag) => {\n setSelectedTags(prev =>\n prev.includes(tag)\n ? prev.filter(t => t !== tag)\n : [...prev, tag]\n );\n };\n\n const fetchInteractions = async () => {\n try {\n const response = await fetch(`/get-interactions?page=${currentPage}`);\n const data = await response.json();\n\n // Set available tags and counts from server data\n setTagCounts(data.tagCounts);\n setAvailableTags(Object.keys(data.tagCounts));\n\n // Filter interactions based on selected tags\n const filteredInteractions = selectedTags.length > 0\n ? data.interactions.filter(interaction => {\n if (!interaction.tags) return false;\n const interactionTags = interaction.tags.split(\",\").map(tag => tag.trim());\n return selectedTags.every(tag => interactionTags.includes(tag));\n })\n : data.interactions;\n\n // Group filtered interactions by customer\n const groupedInteractions = filteredInteractions.reduce((acc, interaction) => {\n const customerKey = `${interaction.first_name} ${interaction.last_name}`;\n if (!acc[customerKey]) {\n acc[customerKey] = [];\n }\n acc[customerKey].push(interaction);\n return acc;\n }, {});\n\n setCustomerInteractions(groupedInteractions);\n\n // Update total count based on whether filters are applied\n if (selectedTags.length > 0) {\n // Need to get total count of filtered interactions from server\n const filteredCountResponse = await fetch(`/get-filtered-count?tags=${selectedTags.join(\",\")}`);\n const filteredCountData = await filteredCountResponse.json();\n setTotalInteractions(filteredCountData.count);\n } else {\n setTotalInteractions(data.total);\n }\n } catch (error) {\n console.error(\"Error fetching interactions:\", error);\n }\n };\n\n useEffect(() => {\n fetchInteractions();\n }, [currentPage, selectedTags]);\n\n return (\n

📋 Customer Conversations

\n \n We've spoken to {uniqueCustomerCount} customers 🙂‍↕️\n \n\n {/* Tag Filter Section */}\n

Filter by Tags

\n {availableTags.map(tag => (\n handleTagSelect(tag)}\n className={`tag-pill ${selectedTags.includes(tag) ? \"selected\" : \"\"}`}\n >\n {tag} {tagCounts[tag] ? `(${tagCounts[tag]})` : \"\"}\n \n ))}\n

\n {selectedTags.length > 0 && (\n setSelectedTags([])}\n className=\"pagination-button\"\n >\n Clear Filters\n \n )}\n

\n\n {/* Display when no results match filters */}\n {Object.keys(customerInteractions).length === 0 && selectedTags.length > 0 && (\n

\n No interactions found with the selected tags.\n

\n )}\n\n {Object.entries(customerInteractions).map(([customerName, interactions]) => (\n

🧑 {customerName}

\n \n 📧{\" \"}\n \n handleCopyEmail(interactions[0].email)}\n className=\"copyable-email\"\n >\n {interactions[0].email}\n {\" \"}\n {copiedEmail === interactions[0].email && \"✓\"}\n \n {interactions.map((interaction) => (\n

📝 {interaction.notes}

\n {interaction.additional_notes_link && (\n

\n 🔗{\" \"}\n \n Notes\n \n

\n )}\n {interaction.tags && (\n

\n {interaction.tags.split(\",\").map(tag => (\n \n {tag.trim()}\n \n ))}\n

\n )}\n

\n \n 📅 {new Date(interaction.interaction_date + \"T00:00:00\").toLocaleDateString(\"en-US\", {\n month: \"short\",\n day: \"numeric\",\n year: \"numeric\",\n timeZone: Intl.DateTimeFormat().resolvedOptions().timeZone,\n })}\n \n handleDeleteInteraction(interaction.id)}\n title=\"Delete interaction\"\n >\n 🗑️\n \n

\n ))}\n

\n ))}\n\n

\n setCurrentPage(prev => Math.max(prev - 1, 1))}\n disabled={currentPage === 1}\n className=\"pagination-button\"\n >\n Previous\n \n Page {currentPage} of {Math.ceil(totalInteractions / 10)}\n setCurrentPage(prev => prev + 1)}\n disabled={currentPage >= Math.ceil(totalInteractions / 10)}\n className=\"pagination-button\"\n >\n Next\n \n

\n );\n}\n\nfunction App() {\n const [isAuthenticated, setIsAuthenticated] = useState(false);\n const [refreshList, setRefreshList] = useState(0);\n\n useEffect(() => {\n // Check authentication status when component mounts\n checkAuth();\n }, []);\n\n const checkAuth = async () => {\n try {\n const response = await fetch(\"/check-auth\");\n const data = await response.json();\n setIsAuthenticated(data.authenticated);\n } catch (error) {\n console.error(\"Error checking authentication:\", error);\n setIsAuthenticated(false);\n }\n };\n\n const handleLogin = () => {\n setIsAuthenticated(true);\n };\n\n const handleLogout = () => {\n setIsAuthenticated(false);\n };\n\n const handleInteractionSubmit = () => {\n setRefreshList(prev => prev + 1);\n };\n\n if (!isAuthenticated) {\n return ;\n }\n\n return (\n

CRM

\n \n \n

\n );\n}\n\nfunction client() {\n createRoot(document.getElementById(\"root\")).render();\n}\nif (typeof document !== \"undefined\") { client(); }\n\nexport default async function server(request: Request): Promise {\n const { sqlite } = await import(\"https://esm.town/v/stevekrouse/sqlite\");\n const KEY = new URL(import.meta.url).pathname.split(\"/\").at(-1);\n const SCHEMA_VERSION = 4; // Increment schema version\n\n // Retrieve the admin password from environment variable\n const ADMIN_PASSWORD = Deno.env.get(\"CRM_TEMPLATE_PASSWORD\") || \"default_fallback_password\";\n\n await sqlite.execute(`\n CREATE TABLE IF NOT EXISTS ${KEY}_interactions_${SCHEMA_VERSION} (\n id INTEGER PRIMARY KEY AUTOINCREMENT,\n first_name TEXT NOT NULL,\n last_name TEXT NOT NULL,\n email TEXT NOT NULL,\n interaction_date DATE NOT NULL,\n notes TEXT NOT NULL,\n additional_notes_link TEXT,\n timestamp DATETIME DEFAULT CURRENT_TIMESTAMP\n )\n `);\n\n await sqlite.execute(`\n CREATE TABLE IF NOT EXISTS ${KEY}_tags_${SCHEMA_VERSION} (\n interaction_id INTEGER,\n tag TEXT NOT NULL,\n FOREIGN KEY (interaction_id) REFERENCES ${KEY}_interactions_${SCHEMA_VERSION}(id),\n PRIMARY KEY (interaction_id, tag)\n )\n `);\n\n const isAuthenticated = (request) => {\n const cookie = request.headers.get(\"cookie\");\n if (!cookie) return false;\n\n const authCookie = cookie.split(\";\")\n .find(c => c.trim().startsWith(\"auth=\"));\n if (!authCookie) return false;\n\n const [_, token] = authCookie.split(\"=\");\n // You might want to use a more secure comparison method in production\n return token === ADMIN_PASSWORD;\n };\n\n // New endpoint to get customer details by email\n if (request.method === \"GET\" && new URL(request.url).pathname === \"/get-customer-details\") {\n if (!isAuthenticated(request)) {\n return new Response(JSON.stringify({ error: \"Unauthorized\" }), {\n status: 401,\n headers: { \"Content-Type\": \"application/json\" },\n });\n }\n const url = new URL(request.url);\n const email = url.searchParams.get(\"email\");\n\n if (!email) {\n return new Response(JSON.stringify(null), {\n headers: { \"Content-Type\": \"application/json\" },\n });\n }\n\n const result = await sqlite.execute(\n `\n SELECT first_name, last_name \n FROM ${KEY}_interactions_${SCHEMA_VERSION} \n WHERE email = ? \n ORDER BY timestamp DESC \n LIMIT 1\n `,\n [email],\n );\n\n return new Response(JSON.stringify(result.rows[0] || null), {\n headers: { \"Content-Type\": \"application/json\" },\n });\n }\n\n if (request.method === \"GET\" && new URL(request.url).pathname === \"/get-unique-customer-count\") {\n if (!isAuthenticated(request)) {\n return new Response(JSON.stringify({ error: \"Unauthorized\" }), {\n status: 401,\n headers: { \"Content-Type\": \"application/json\" },\n });\n }\n const result = await sqlite.execute(`\n SELECT COUNT(DISTINCT email) as count \n FROM ${KEY}_interactions_${SCHEMA_VERSION}\n `);\n\n return new Response(\n JSON.stringify({ count: result.rows[0].count }),\n {\n headers: { \"Content-Type\": \"application/json\" },\n },\n );\n }\n\n if (request.method === \"GET\" && new URL(request.url).pathname === \"/get-filtered-count\") {\n if (!isAuthenticated(request)) {\n return new Response(JSON.stringify({ error: \"Unauthorized\" }), {\n status: 401,\n headers: { \"Content-Type\": \"application/json\" },\n });\n }\n const url = new URL(request.url);\n const tags = url.searchParams.get(\"tags\")?.split(\",\") || [];\n\n if (tags.length === 0) {\n return new Response(\n JSON.stringify({ count: 0 }),\n { headers: { \"Content-Type\": \"application/json\" } },\n );\n }\n\n // Build a query that counts interactions that have ALL the specified tags\n const placeholders = tags.map(() => \"?\").join(\",\");\n const result = await sqlite.execute(\n `\n WITH tagged_interactions AS (\n SELECT interaction_id\n FROM ${KEY}_tags_${SCHEMA_VERSION}\n WHERE tag IN (${placeholders})\n GROUP BY interaction_id\n HAVING COUNT(DISTINCT tag) = ?\n )\n SELECT COUNT(*) as count\n FROM ${KEY}_interactions_${SCHEMA_VERSION} i\n INNER JOIN tagged_interactions ti ON i.id = ti.interaction_id\n `,\n [...tags, tags.length],\n );\n\n return new Response(\n JSON.stringify({ count: result.rows[0].count }),\n { headers: { \"Content-Type\": \"application/json\" } },\n );\n }\n\n if (request.method === \"GET\" && new URL(request.url).pathname === \"/check-auth\") {\n return new Response(\n JSON.stringify({ authenticated: isAuthenticated(request) }),\n { headers: { \"Content-Type\": \"application/json\" } },\n );\n }\n\n if (request.method === \"POST\" && new URL(request.url).pathname === \"/login\") {\n const { password } = await request.json();\n if (password === ADMIN_PASSWORD) {\n return new Response(JSON.stringify({ success: true }), {\n headers: {\n \"Content-Type\": \"application/json\",\n // Set cookie to expire in 30 days\n \"Set-Cookie\": `auth=${password}; Path=/; Max-Age=2592000; SameSite=Strict; HttpOnly`,\n },\n });\n } else {\n return new Response(JSON.stringify({ message: \"Incorrect password\" }), {\n status: 401,\n headers: { \"Content-Type\": \"application/json\" },\n });\n }\n }\n\n if (request.method === \"POST\" && new URL(request.url).pathname === \"/add-interaction\") {\n if (!isAuthenticated(request)) {\n return new Response(JSON.stringify({ error: \"Unauthorized\" }), {\n status: 401,\n headers: { \"Content-Type\": \"application/json\" },\n });\n }\n const { firstName, lastName, email, interactionDate, notes, additionalNotesLink, tags = [] } = await request.json();\n\n try {\n // Insert interaction\n const result = await sqlite.execute(\n `INSERT INTO ${KEY}_interactions_${SCHEMA_VERSION} \n (first_name, last_name, email, interaction_date, notes, additional_notes_link) \n VALUES (?, ?, ?, ?, ?, ?)\n RETURNING id`,\n [firstName, lastName, email, interactionDate, notes, additionalNotesLink || null],\n );\n\n const interactionId = result.rows[0].id;\n\n // Insert tags if there are any\n if (tags && tags.length > 0) {\n for (const tag of tags) {\n await sqlite.execute(\n `INSERT INTO ${KEY}_tags_${SCHEMA_VERSION} (interaction_id, tag) VALUES (?, ?)`,\n [interactionId, tag],\n );\n }\n }\n\n return new Response(JSON.stringify({ success: true }), {\n headers: { \"Content-Type\": \"application/json\" },\n });\n } catch (error) {\n console.error(\"Error in add-interaction:\", error);\n return new Response(\n JSON.stringify({\n success: false,\n error: \"Failed to save interaction\",\n }),\n {\n status: 500,\n headers: { \"Content-Type\": \"application/json\" },\n },\n );\n }\n }\n\n if (request.method === \"GET\" && new URL(request.url).pathname === \"/get-interactions\") {\n if (!isAuthenticated(request)) {\n return new Response(JSON.stringify({ error: \"Unauthorized\" }), {\n status: 401,\n headers: { \"Content-Type\": \"application/json\" },\n });\n }\n const url = new URL(request.url);\n const page = parseInt(url.searchParams.get(\"page\") || \"1\");\n const limit = 10;\n const offset = (page - 1) * limit;\n\n // First, get the paginated interactions\n const result = await sqlite.execute(\n `\n SELECT i.*, GROUP_CONCAT(t.tag) as tags \n FROM ${KEY}_interactions_${SCHEMA_VERSION} i \n LEFT JOIN ${KEY}_tags_${SCHEMA_VERSION} t ON i.id = t.interaction_id \n GROUP BY i.id\n ORDER BY i.interaction_date DESC, i.timestamp DESC \n LIMIT ? OFFSET ?\n `,\n [limit, offset],\n );\n\n // Second, get the total count of interactions\n const countResult = await sqlite.execute(`\n SELECT COUNT(*) as total FROM ${KEY}_interactions_${SCHEMA_VERSION}\n `);\n\n // Third, get the total count for each tag across ALL interactions\n const tagCountResult = await sqlite.execute(`\n SELECT tag, COUNT(DISTINCT interaction_id) as count\n FROM ${KEY}_tags_${SCHEMA_VERSION}\n GROUP BY tag\n `);\n\n // Convert tag counts array to an object for easier frontend consumption\n const tagCounts = {};\n tagCountResult.rows.forEach(row => {\n tagCounts[row.tag] = row.count;\n });\n\n return new Response(\n JSON.stringify({\n interactions: result.rows,\n total: countResult.rows[0].total,\n tagCounts: tagCounts, // Add this to the response\n }),\n {\n headers: { \"Content-Type\": \"application/json\" },\n },\n );\n }\n\n if (request.method === \"DELETE\" && new URL(request.url).pathname === \"/delete-interaction\") {\n if (!isAuthenticated(request)) {\n return new Response(JSON.stringify({ error: \"Unauthorized\" }), {\n status: 401,\n headers: { \"Content-Type\": \"application/json\" },\n });\n }\n const { id } = await request.json();\n try {\n // First delete the tags associated with this interaction\n await sqlite.execute(\n `DELETE FROM ${KEY}_tags_${SCHEMA_VERSION} WHERE interaction_id = ?`,\n [id],\n );\n\n // Then delete the interaction\n await sqlite.execute(\n `DELETE FROM ${KEY}_interactions_${SCHEMA_VERSION} WHERE id = ?`,\n [id],\n );\n\n return new Response(JSON.stringify({ success: true }), {\n headers: { \"Content-Type\": \"application/json\" },\n });\n } catch (error) {\n console.error(\"Error deleting interaction:\", error);\n return new Response(\n JSON.stringify({\n success: false,\n error: \"Failed to delete interaction\",\n }),\n {\n status: 500,\n headers: { \"Content-Type\": \"application/json\" },\n },\n );\n }\n }\n\n return new Response(\n `\n \n \n CRM\n \n \n \n \n \n

\n \n \n \n View Source\n \n \n \n `,\n {\n headers: { \"Content-Type\": \"text/html\" },\n },\n );\n}\n\nconst css = `\nbody {\n font-family: 'Inter', -apple-system, BlinkMacSystemFont, 'Segoe UI', Roboto, Oxygen, Ubuntu, Cantarell, 'Open Sans', 'Helvetica Neue', sans-serif;\n max-width: 600px;\n margin: 0 auto;\n padding: 20px;\n background-color: #1a1a1a;\n color: #e0e0e0;\n}\n\n.login-container {\n display: flex;\n justify-content: center;\n align-items: center;\n height: 100vh;\n background-color: #1a1a1a;\n}\n\n.login-form {\n background-color: #2d2d2d;\n padding: 30px;\n border-radius: 8px;\n box-shadow: 0 4px 6px rgba(0,0,0,0.3);\n width: 100%;\n max-width: 350px;\n display: flex;\n flex-direction: column;\n gap: 15px;\n}\n\n.login-form input {\n padding: 10px;\n border: 1px solid #404040;\n border-radius: 4px;\n background-color: #333333;\n color: #e0e0e0;\n}\n\n.login-form button {\n background-color: #0066cc;\n color: white;\n border: none;\n padding: 10px;\n border-radius: 4px;\n cursor: pointer;\n}\n\n.error-message {\n color: #ff6b6b;\n margin: 0;\n text-align: center;\n}\n\n.crm-header {\n display: flex;\n justify-content: space-between;\n align-items: center;\n margin-bottom: 20px;\n}\n\n.crm-container {\n background-color: #2d2d2d;\n border-radius: 8px;\n box-shadow: 0 4px 6px rgba(0,0,0,0.1);\n padding: 20px;\n}\n\n.interaction-form {\n display: flex;\n flex-direction: column;\n gap: 10px;\n margin-bottom: 20px;\n}\n\n.name-inputs {\n display: flex;\n gap: 10px;\n}\n\n.name-inputs input {\n flex: 1;\n}\n\n.interaction-form input,\n.interaction-form textarea {\n padding: 10px;\n border: 1px solid #404040;\n border-radius: 4px;\n background-color: #333333;\n color: #e0e0e0;\n}\n\n.interaction-form input[type=\"date\"]::-webkit-calendar-picker-indicator {\n filter: invert(1);\n opacity: 0.7;\n cursor: pointer;\n}\n\n.interaction-form input[type=\"date\"]::-webkit-calendar-picker-indicator:hover {\n opacity: 1;\n}\n\n.interaction-form > button {\n background-color: #0066cc;\n color: white;\n border: none;\n padding: 10px;\n border-radius: 4px;\n cursor: pointer;\n}\n\n.interaction-list {\n display: flex;\n flex-direction: column;\n gap: 15px;\n}\n\n.customer-interaction-group {\n background-color: #333333;\n border-radius: 4px;\n padding: 15px;\n border: 1px solid #404040;\n}\n\n.customer-interaction-group h3 {\n margin-top: 0;\n margin-bottom: 10px;\n color: #e0e0e0;\n}\n\n.interaction-card {\n margin-top: 10px;\n background-color: #2d2d2d;\n border-radius: 4px;\n padding: 10px;\n margin-bottom: 10px;\n box-shadow: 0 2px 4px rgba(0,0,0,0.2);\n}\n\n.interaction-card small {\n color: #a0a0a0;\n display: block;\n margin-top: 5px;\n}\n\n.interaction-card a {\n color: #5c9eff;\n text-decoration: none;\n}\n\n.interaction-card a:hover {\n text-decoration: underline;\n}\n\n.copyable-email {\n cursor: pointer;\n}\n\n.copyable-email:hover {\n text-decoration: underline;\n}\n\n.interaction-footer {\n display: flex;\n justify-content: space-between;\n align-items: center;\n margin-top: 5px;\n}\n\n.delete-button {\n cursor: pointer;\n}\n\n.pagination-controls {\n display: flex;\n justify-content: center;\n align-items: center;\n gap: 15px;\n margin-top: 20px;\n padding: 10px;\n}\n\n.pagination-button {\n background-color: #404040;\n color: #e0e0e0;\n border: none;\n padding: 8px 16px;\n border-radius: 4px;\n cursor: pointer;\n margin-top: 10px;\n}\n\n.pagination-button:disabled {\n background-color: #333333;\n color: #666666;\n cursor: not-allowed;\n}\n\n.tags-input-container {\n margin: 10px 0;\n}\n\n.tags-list {\n display: flex;\n flex-wrap: wrap;\n gap: 5px;\n margin-bottom: 5px;\n}\n\n.tag-input {\n width: 100%;\n padding: 8px;\n border: 1px solid #404040;\n border-radius: 4px;\n background-color: #333333;\n color: #e0e0e0;\n}\n\n.tag-pill {\n background-color: #404040;\n color: #e0e0e0;\n padding: 4px 12px;\n border-radius: 16px;\n font-size: 14px;\n display: inline-flex;\n align-items: center;\n margin-right: 5px;\n cursor: pointer;\n border: none;\n transition: all 0.2s ease;\n}\n\n.tag-pill:hover {\n background-color: #505050;\n}\n\n.tag-pill.selected {\n background-color: #0066cc;\n color: white;\n}\n\n.tag-pill.selected:hover {\n background-color: #0056b3;\n}\n\n.tag-remove {\n border: none;\n background-color: transparent;\n color: #fff;\n}\n\n.customer-conversations {\n margin: 0;\n}\n`;","createdAt",["D",1737645253031],"version",3,"currentUserHasLiked","likeCount",1,"forkCount","referenceCount","dependencyCount","commentCount","prCount","mergedPr",{"_114":131,"_132":133,"_134":135,"_136":137},"0769ae56-d99c-11ef-8f67-e6cdfca9ef9f","description","Fix: default the date to today (line 52)","handle","stevekrouse","profileImageUrl","https://images.clerk.dev/uploaded/img_2PqHa2Gsy93xQrjh2w78Xu0cChW.jpeg",{"_134":139,"_136":140},"juecd","https://img.clerk.com/eyJ0eXBlIjoicHJveHkiLCJzcmMiOiJodHRwczovL2ltYWdlcy5jbGVyay5kZXYvb2F1dGhfZ2l0aHViL2ltZ18ya3kyd3VJTE9vOWRJMVhnRVBuVG9xZXZhcEsifQ","forkedVal",{"_114":143,"_134":139,"_99":144,"_119":145,"_146":-5},"7be731a4-d90b-11ef-a74b-e6cdfca9ef9f","CRM",96,"prDisabledHint","interval","slug","juecd/crmtemplate","I needed a lightweight CRM to track customer conversations. I wanted it to be dead simple to use to reduce the amount of manual data entry I have to do. I also want to use keyboard shortcuts as much as possible. I had Townie build 70% of this successfully! == This is a template that you can fork. To preview the the CRM, enter example as the password After forking, set your own password in your Val.Town environment variables as CRM_TEMPLATE_PASSWORD Command+K within the Key Takeaway / Next Step or Additional Notes Link field submits the Customer Interaction. Entering an email address you've previously interacted with auto-populates First Name and Last Name The CRM includes filter by tags as well as pagination Logging in with your password sets a cookie so you don't need to re-login. The cookie is valid for 30 days. Note that tagging happens on individual notes, not the customer. As well, there's currently no concept of Companies, so interactions with multiple people at the same email domain don't roll up to a single view.","versions",[153,156,159,162],{"_119":120,"_154":131,"_117":155,"_114":112},"prId",["D",1737645253031],{"_119":91,"_154":-5,"_117":157,"_114":158},["D",1737642992857],"70deff30-d997-11ef-a2b7-e6cdfca9ef9f",{"_119":123,"_154":-5,"_117":160,"_114":161},["D",1737642820506],"0a24b30c-d997-11ef-a0c1-e6cdfca9ef9f",{"_119":32,"_154":-5,"_117":163,"_114":164},["D",1737642459754],"331f1be0-d996-11ef-a6e1-e6cdfca9ef9f","recentThread","breadcrumbs","comments",[],"isOwner","routes/_app.v.$handle.$name.versions.$(version)",{"_94":172,"_173":174,"_95":175},"single","redirect","/v/juecd/crmtemplate/versions",{"_114":161,"_115":176,"_119":123,"_117":177,"_178":100,"_101":102,"_109":110,"_134":139,"_11":97,"_179":-5,"_180":-5,"_181":-5,"_169":26,"_148":149},"/** @jsxImportSource https://esm.sh/react@18.2.0 */\nimport { createRoot } from \"https://esm.sh/react-dom@18.2.0/client\";\nimport React, { useEffect, useRef, useState } from \"https://esm.sh/react@18.2.0\";\n\nfunction LoginScreen({ onLogin }) {\n const [password, setPassword] = useState(\"\");\n const [error, setError] = useState(\"\");\n\n const handleSubmit = async (e) => {\n e.preventDefault();\n try {\n const response = await fetch(\"/login\", {\n method: \"POST\",\n headers: { \"Content-Type\": \"application/json\" },\n body: JSON.stringify({ password }),\n });\n\n if (response.ok) {\n onLogin();\n setError(\"\");\n } else {\n const result = await response.json();\n setError(result.message || \"Incorrect password\");\n }\n } catch (error) {\n setError(\"Login failed. Please try again.\");\n }\n };\n\n return (\n

\n );\n}\n\nfunction CustomerInteractionForm({ onSubmit }) {\n const [email, setEmail] = useState(\"\");\n const [firstName, setFirstName] = useState(\"\");\n const [lastName, setLastName] = useState(\"\");\n const [interactionDate, setInteractionDate] = useState(new Date().toLocaleDateString(\"en-US\"));\n const [notes, setNotes] = useState(\"\");\n const [additionalNotesLink, setAdditionalNotesLink] = useState(\"\");\n const formRef = useRef(null);\n const emailInputRef = useRef(null);\n const [tags, setTags] = useState([]);\n const [tagInput, setTagInput] = useState(\"\");\n\n // Fetch previous customer details when email changes\n useEffect(() => {\n const fetchCustomerDetails = async () => {\n if (!email) return;\n\n try {\n const response = await fetch(`/get-customer-details?email=${encodeURIComponent(email)}`);\n if (response.ok) {\n const customerDetails = await response.json();\n if (customerDetails) {\n setFirstName(customerDetails.first_name || \"\");\n setLastName(customerDetails.last_name || \"\");\n }\n }\n } catch (error) {\n console.error(\"Error fetching customer details:\", error);\n }\n };\n\n fetchCustomerDetails();\n }, [email]);\n\n // Add keyboard shortcut for form submission\n // Inside CustomerInteractionForm component\n useEffect(() => {\n const handleKeyDown = (e) => {\n // If we're in the tag input, let handleTagInput handle cmd-k\n if (e.target.className === \"tag-input\") {\n return;\n }\n\n // For other inputs, handle cmd-k normally\n if ((e.metaKey || e.ctrlKey) && e.key.toLowerCase() === \"k\") {\n e.preventDefault();\n formRef.current?.requestSubmit();\n }\n };\n\n // Add event listener to window\n window.addEventListener(\"keydown\", handleKeyDown, true);\n\n // Cleanup event listener\n return () => {\n window.removeEventListener(\"keydown\", handleKeyDown, true);\n };\n }, []);\n\n useEffect(() => {\n emailInputRef.current?.focus();\n }, []);\n\n const handleTagInput = (e) => {\n // Stop cmd-k from removing tags\n if ((e.metaKey || e.ctrlKey) && e.key.toLowerCase() === \"k\") {\n e.preventDefault();\n formRef.current?.requestSubmit();\n return;\n }\n\n if (e.key === \"Enter\" || e.key === \",\") {\n e.preventDefault();\n const newTag = tagInput.trim();\n if (newTag && !tags.includes(newTag)) {\n setTags([...tags, newTag]);\n }\n setTagInput(\"\");\n }\n };\n\n const removeTag = (tagToRemove, event) => {\n // If this was triggered by a keyboard event, don't remove the tag\n if (event && (event.metaKey || event.ctrlKey)) {\n return;\n }\n setTags(tags.filter(tag => tag !== tagToRemove));\n };\n\n const handleSubmit = async (e) => {\n e.preventDefault();\n try {\n const response = await fetch(\"/add-interaction\", {\n method: \"POST\",\n headers: { \"Content-Type\": \"application/json\" },\n body: JSON.stringify({\n firstName,\n lastName,\n email,\n interactionDate,\n notes,\n additionalNotesLink,\n tags, // Add this line\n }),\n });\n if (response.ok) {\n setFirstName(\"\");\n setLastName(\"\");\n setEmail(\"\");\n setNotes(\"\");\n setAdditionalNotesLink(\"\");\n setTags([]); // Reset tags\n setInteractionDate(new Date().toLocaleDateString(\"en-US\"));\n onSubmit();\n }\n } catch (error) {\n console.error(\"Error submitting interaction:\", error);\n }\n };\n\n return (\n

\n setEmail(e.target.value)}\n required\n />\n

\n setFirstName(e.target.value)}\n required\n />\n setLastName(e.target.value)}\n required\n />\n

\n {tags.map(tag => (\n \n {tag}\n removeTag(tag, e)}\n className=\"tag-remove\"\n >\n ×\n \n \n ))}\n

\n setTagInput(e.target.value)}\n onKeyDown={handleTagInput}\n className=\"tag-input\"\n />\n

📋 Customer Conversations

\n \n We've spoken to {uniqueCustomerCount} customers 🙂‍↕️\n \n\n {/* Tag Filter Section */}\n

Filter by Tags

\n {selectedTags.length > 0 && (\n setSelectedTags([])}\n className=\"pagination-button\"\n >\n Clear Filters\n \n )}\n

\n\n {/* Display when no results match filters */}\n {Object.keys(customerInteractions).length === 0 && selectedTags.length > 0 && (\n

\n No interactions found with the selected tags.\n

\n )}\n\n {Object.entries(customerInteractions).map(([customerName, interactions]) => (\n

🧑 {customerName}

📝 {interaction.notes}

\n {interaction.additional_notes_link && (\n

\n 🔗{\" \"}\n \n Notes\n \n

\n )}\n {interaction.tags && (\n

\n {interaction.tags.split(\",\").map(tag => (\n \n {tag.trim()}\n \n ))}\n

\n )}\n

\n ))}\n

\n ))}\n\n

CRM

\n \n \n

\n );\n}\n\nfunction client() {\n createRoot(document.getElementById(\"root\")).render();\n}\nif (typeof document !== \"undefined\") { client(); }\n\nexport default async function server(request: Request): Promise {\n const { sqlite } = await import(\"https://esm.town/v/stevekrouse/sqlite\");\n const KEY = new URL(import.meta.url).pathname.split(\"/\").at(-1);\n const SCHEMA_VERSION = 4; // Increment schema version\n\n // Retrieve the admin password from environment variable\n const ADMIN_PASSWORD = Deno.env.get(\"CRM_PWD\") || \"default_fallback_password\";\n\n await sqlite.execute(`\n CREATE TABLE IF NOT EXISTS ${KEY}_interactions_${SCHEMA_VERSION} (\n id INTEGER PRIMARY KEY AUTOINCREMENT,\n first_name TEXT NOT NULL,\n last_name TEXT NOT NULL,\n email TEXT NOT NULL,\n interaction_date DATE NOT NULL,\n notes TEXT NOT NULL,\n additional_notes_link TEXT,\n timestamp DATETIME DEFAULT CURRENT_TIMESTAMP\n )\n `);\n\n await sqlite.execute(`\n CREATE TABLE IF NOT EXISTS ${KEY}_tags_${SCHEMA_VERSION} (\n interaction_id INTEGER,\n tag TEXT NOT NULL,\n FOREIGN KEY (interaction_id) REFERENCES ${KEY}_interactions_${SCHEMA_VERSION}(id),\n PRIMARY KEY (interaction_id, tag)\n )\n `);\n\n const isAuthenticated = (request) => {\n const cookie = request.headers.get(\"cookie\");\n if (!cookie) return false;\n\n const authCookie = cookie.split(\";\")\n .find(c => c.trim().startsWith(\"auth=\"));\n if (!authCookie) return false;\n\n const [_, token] = authCookie.split(\"=\");\n // You might want to use a more secure comparison method in production\n return token === ADMIN_PASSWORD;\n };\n\n // New endpoint to get customer details by email\n if (request.method === \"GET\" && new URL(request.url).pathname === \"/get-customer-details\") {\n if (!isAuthenticated(request)) {\n return new Response(JSON.stringify({ error: \"Unauthorized\" }), {\n status: 401,\n headers: { \"Content-Type\": \"application/json\" },\n });\n }\n const url = new URL(request.url);\n const email = url.searchParams.get(\"email\");\n\n if (!email) {\n return new Response(JSON.stringify(null), {\n headers: { \"Content-Type\": \"application/json\" },\n });\n }\n\n const result = await sqlite.execute(\n `\n SELECT first_name, last_name \n FROM ${KEY}_interactions_${SCHEMA_VERSION} \n WHERE email = ? \n ORDER BY timestamp DESC \n LIMIT 1\n `,\n [email],\n );\n\n return new Response(JSON.stringify(result.rows[0] || null), {\n headers: { \"Content-Type\": \"application/json\" },\n });\n }\n\n if (request.method === \"GET\" && new URL(request.url).pathname === \"/get-unique-customer-count\") {\n if (!isAuthenticated(request)) {\n return new Response(JSON.stringify({ error: \"Unauthorized\" }), {\n status: 401,\n headers: { \"Content-Type\": \"application/json\" },\n });\n }\n const result = await sqlite.execute(`\n SELECT COUNT(DISTINCT email) as count \n FROM ${KEY}_interactions_${SCHEMA_VERSION}\n `);\n\n return new Response(\n JSON.stringify({ count: result.rows[0].count }),\n {\n headers: { \"Content-Type\": \"application/json\" },\n },\n );\n }\n\n if (request.method === \"GET\" && new URL(request.url).pathname === \"/get-filtered-count\") {\n if (!isAuthenticated(request)) {\n return new Response(JSON.stringify({ error: \"Unauthorized\" }), {\n status: 401,\n headers: { \"Content-Type\": \"application/json\" },\n });\n }\n const url = new URL(request.url);\n const tags = url.searchParams.get(\"tags\")?.split(\",\") || [];\n\n if (tags.length === 0) {\n return new Response(\n JSON.stringify({ count: 0 }),\n { headers: { \"Content-Type\": \"application/json\" } },\n );\n }\n\n // Build a query that counts interactions that have ALL the specified tags\n const placeholders = tags.map(() => \"?\").join(\",\");\n const result = await sqlite.execute(\n `\n WITH tagged_interactions AS (\n SELECT interaction_id\n FROM ${KEY}_tags_${SCHEMA_VERSION}\n WHERE tag IN (${placeholders})\n GROUP BY interaction_id\n HAVING COUNT(DISTINCT tag) = ?\n )\n SELECT COUNT(*) as count\n FROM ${KEY}_interactions_${SCHEMA_VERSION} i\n INNER JOIN tagged_interactions ti ON i.id = ti.interaction_id\n `,\n [...tags, tags.length],\n );\n\n return new Response(\n JSON.stringify({ count: result.rows[0].count }),\n { headers: { \"Content-Type\": \"application/json\" } },\n );\n }\n\n if (request.method === \"GET\" && new URL(request.url).pathname === \"/check-auth\") {\n return new Response(\n JSON.stringify({ authenticated: isAuthenticated(request) }),\n { headers: { \"Content-Type\": \"application/json\" } },\n );\n }\n\n if (request.method === \"POST\" && new URL(request.url).pathname === \"/login\") {\n const { password } = await request.json();\n if (password === ADMIN_PASSWORD) {\n return new Response(JSON.stringify({ success: true }), {\n headers: {\n \"Content-Type\": \"application/json\",\n // Set cookie to expire in 30 days\n \"Set-Cookie\": `auth=${password}; Path=/; Max-Age=2592000; SameSite=Strict; HttpOnly`,\n },\n });\n } else {\n return new Response(JSON.stringify({ message: \"Incorrect password\" }), {\n status: 401,\n headers: { \"Content-Type\": \"application/json\" },\n });\n }\n }\n\n if (request.method === \"POST\" && new URL(request.url).pathname === \"/add-interaction\") {\n if (!isAuthenticated(request)) {\n return new Response(JSON.stringify({ error: \"Unauthorized\" }), {\n status: 401,\n headers: { \"Content-Type\": \"application/json\" },\n });\n }\n const { firstName, lastName, email, interactionDate, notes, additionalNotesLink, tags = [] } = await request.json();\n\n try {\n // Insert interaction\n const result = await sqlite.execute(\n `INSERT INTO ${KEY}_interactions_${SCHEMA_VERSION} \n (first_name, last_name, email, interaction_date, notes, additional_notes_link) \n VALUES (?, ?, ?, ?, ?, ?)\n RETURNING id`,\n [firstName, lastName, email, interactionDate, notes, additionalNotesLink || null],\n );\n\n const interactionId = result.rows[0].id;\n\n // Insert tags if there are any\n if (tags && tags.length > 0) {\n for (const tag of tags) {\n await sqlite.execute(\n `INSERT INTO ${KEY}_tags_${SCHEMA_VERSION} (interaction_id, tag) VALUES (?, ?)`,\n [interactionId, tag],\n );\n }\n }\n\n return new Response(JSON.stringify({ success: true }), {\n headers: { \"Content-Type\": \"application/json\" },\n });\n } catch (error) {\n console.error(\"Error in add-interaction:\", error);\n return new Response(\n JSON.stringify({\n success: false,\n error: \"Failed to save interaction\",\n }),\n {\n status: 500,\n headers: { \"Content-Type\": \"application/json\" },\n },\n );\n }\n }\n\n if (request.method === \"GET\" && new URL(request.url).pathname === \"/get-interactions\") {\n if (!isAuthenticated(request)) {\n return new Response(JSON.stringify({ error: \"Unauthorized\" }), {\n status: 401,\n headers: { \"Content-Type\": \"application/json\" },\n });\n }\n const url = new URL(request.url);\n const page = parseInt(url.searchParams.get(\"page\") || \"1\");\n const limit = 10;\n const offset = (page - 1) * limit;\n\n // First, get the paginated interactions\n const result = await sqlite.execute(\n `\n SELECT i.*, GROUP_CONCAT(t.tag) as tags \n FROM ${KEY}_interactions_${SCHEMA_VERSION} i \n LEFT JOIN ${KEY}_tags_${SCHEMA_VERSION} t ON i.id = t.interaction_id \n GROUP BY i.id\n ORDER BY i.interaction_date DESC, i.timestamp DESC \n LIMIT ? OFFSET ?\n `,\n [limit, offset],\n );\n\n // Second, get the total count of interactions\n const countResult = await sqlite.execute(`\n SELECT COUNT(*) as total FROM ${KEY}_interactions_${SCHEMA_VERSION}\n `);\n\n // Third, get the total count for each tag across ALL interactions\n const tagCountResult = await sqlite.execute(`\n SELECT tag, COUNT(DISTINCT interaction_id) as count\n FROM ${KEY}_tags_${SCHEMA_VERSION}\n GROUP BY tag\n `);\n\n // Convert tag counts array to an object for easier frontend consumption\n const tagCounts = {};\n tagCountResult.rows.forEach(row => {\n tagCounts[row.tag] = row.count;\n });\n\n return new Response(\n JSON.stringify({\n interactions: result.rows,\n total: countResult.rows[0].total,\n tagCounts: tagCounts, // Add this to the response\n }),\n {\n headers: { \"Content-Type\": \"application/json\" },\n },\n );\n }\n\n if (request.method === \"DELETE\" && new URL(request.url).pathname === \"/delete-interaction\") {\n if (!isAuthenticated(request)) {\n return new Response(JSON.stringify({ error: \"Unauthorized\" }), {\n status: 401,\n headers: { \"Content-Type\": \"application/json\" },\n });\n }\n const { id } = await request.json();\n try {\n // First delete the tags associated with this interaction\n await sqlite.execute(\n `DELETE FROM ${KEY}_tags_${SCHEMA_VERSION} WHERE interaction_id = ?`,\n [id],\n );\n\n // Then delete the interaction\n await sqlite.execute(\n `DELETE FROM ${KEY}_interactions_${SCHEMA_VERSION} WHERE id = ?`,\n [id],\n );\n\n return new Response(JSON.stringify({ success: true }), {\n headers: { \"Content-Type\": \"application/json\" },\n });\n } catch (error) {\n console.error(\"Error deleting interaction:\", error);\n return new Response(\n JSON.stringify({\n success: false,\n error: \"Failed to delete interaction\",\n }),\n {\n status: 500,\n headers: { \"Content-Type\": \"application/json\" },\n },\n );\n }\n }\n\n return new Response(\n `\n \n \n CRM\n \n \n \n \n \n

Welcome to Your CRM

📋 Customer Conversations

Filter by Tags

🧑 {customerName}

CRM

Welcome to Your CRM

📋 Customer Conversations

Filter by Tags

🧑 {customerName}

CRM

crmtemplate

Version 1