Tweet Metrics Tracker

\n View Source\n

Track tweets and their engagement metrics over time

\n\n

Search Tweets

\n \n \n

Fetch User Tweets

\n \n \n

\n \n

Tweet Feed

\n \n \n

\n\n

\n \n

Loading tweets...

\n\n

\n \n

\n\n \n

\n\n \n\n",[],[228,230],"a3e370b0-32e7-11f0-bf83-569c3dd06744","app.js","a3e37164-32e7-11f0-bf83-569c3dd06744",13,["D",1747463091684],"// Tweet Metrics Tracker - Frontend Application\n\n// State management\nconst state = {\n tweets: [],\n currentPage: 0,\n pageSize: 10,\n hasMore: true,\n currentView: 'latest', // 'latest' or 'top'\n metricsChart: null,\n};\n\n// DOM Elements\nconst elements = {\n tweetsList: document.getElementById('tweetsList'),\n loadMoreBtn: document.getElementById('loadMoreBtn'),\n loadMoreContainer: document.getElementById('loadMoreContainer'),\n loadingIndicator: document.getElementById('loadingIndicator'),\n errorMessage: document.getElementById('errorMessage'),\n searchInput: document.getElementById('searchInput'),\n searchButton: document.getElementById('searchButton'),\n usernameInput: document.getElementById('usernameInput'),\n fetchButton: document.getElementById('fetchButton'),\n latestTweetsBtn: document.getElementById('latestTweetsBtn'),\n topTweetsBtn: document.getElementById('topTweetsBtn'),\n metricsModal: document.getElementById('metricsModal'),\n closeModalBtn: document.getElementById('closeModalBtn'),\n tweetContent: document.getElementById('tweetContent'),\n metricsChart: document.getElementById('metricsChart'),\n viewSourceLink: document.getElementById('viewSourceLink'),\n};\n\n// Set view source link\nif (window.__PROJECT_URL__) {\n elements.viewSourceLink.href = window.__PROJECT_URL__;\n}\n\n// API Functions\nasync function fetchTweets(page = 0, pageSize = 10) {\n showLoading();\n try {\n const response = await fetch(`/api/tweets?offset=${page * pageSize}&limit=${pageSize}`);\n if (!response.ok) throw new Error('Failed to fetch tweets');\n const data = await response.json();\n hideLoading();\n return data.data;\n } catch (error) {\n showError(error.message);\n hideLoading();\n return [];\n }\n}\n\nasync function fetchTopTweets(limit = 10) {\n showLoading();\n try {\n const response = await fetch(`/api/top-tweets?limit=${limit}`);\n if (!response.ok) throw new Error('Failed to fetch top tweets');\n const data = await response.json();\n hideLoading();\n return data.data;\n } catch (error) {\n showError(error.message);\n hideLoading();\n return [];\n }\n}\n\nasync function searchTweets(query) {\n showLoading();\n try {\n const response = await fetch(`/api/search?q=${encodeURIComponent(query)}`);\n if (!response.ok) throw new Error('Failed to search tweets');\n const data = await response.json();\n hideLoading();\n return data.data;\n } catch (error) {\n showError(error.message);\n hideLoading();\n return [];\n }\n}\n\nasync function fetchUserTweets(username) {\n showLoading();\n try {\n const response = await fetch(`/api/fetch/${encodeURIComponent(username)}`);\n if (!response.ok) throw new Error('Failed to fetch user tweets');\n const data = await response.json();\n hideLoading();\n return data.data;\n } catch (error) {\n showError(error.message);\n hideLoading();\n return [];\n }\n}\n\nasync function fetchTweetMetrics(tweetId) {\n showLoading();\n try {\n const response = await fetch(`/api/tweets/${tweetId}/metrics`);\n if (!response.ok) throw new Error('Failed to fetch tweet metrics');\n const data = await response.json();\n hideLoading();\n return data.data;\n } catch (error) {\n showError(error.message);\n hideLoading();\n return [];\n }\n}\n\n// UI Functions\nfunction renderTweets(tweets) {\n if (tweets.length === 0) {\n elements.tweetsList.innerHTML = '

No tweets found

';\n elements.loadMoreContainer.classList.add('hidden');\n return;\n }\n\n const tweetsHTML = tweets.map(tweet => createTweetCard(tweet)).join('');\n elements.tweetsList.innerHTML = tweetsHTML;\n \n // Add event listeners to metrics buttons\n document.querySelectorAll('.metrics-btn').forEach(btn => {\n btn.addEventListener('click', () => showMetrics(btn.dataset.tweetId));\n });\n \n // Show load more button if we have tweets\n if (state.hasMore) {\n elements.loadMoreContainer.classList.remove('hidden');\n } else {\n elements.loadMoreContainer.classList.add('hidden');\n }\n}\n\nfunction appendTweets(tweets) {\n if (tweets.length === 0) {\n state.hasMore = false;\n elements.loadMoreContainer.classList.add('hidden');\n return;\n }\n\n const tweetsHTML = tweets.map(tweet => createTweetCard(tweet)).join('');\n elements.tweetsList.innerHTML += tweetsHTML;\n \n // Add event listeners to new metrics buttons\n document.querySelectorAll('.metrics-btn').forEach(btn => {\n if (!btn.hasListener) {\n btn.addEventListener('click', () => showMetrics(btn.dataset.tweetId));\n btn.hasListener = true;\n }\n });\n}\n\nfunction createTweetCard(tweet) {\n const date = new Date(tweet.created_at);\n const formattedDate = date.toLocaleDateString('en-US', { \n year: 'numeric', \n month: 'short', \n day: 'numeric',\n hour: '2-digit',\n minute: '2-digit'\n });\n \n return `\n

\n ${tweet.user.profile_image_url \n ? ` $\"${tweet.user.name}\"$ `\n : `

\n ${tweet.user.name.charAt(0)}\n

`\n }\n

${tweet.user.name}

\n @${tweet.user.username}\n ${formattedDate}\n

${formatTweetText(tweet.text)}

\n ${tweet.retweet_count} Retweets\n

\n ${tweet.like_count} Likes\n

\n ${tweet.reply_count} Replies\n

\n \n

\n `;\n}\n\nfunction formatTweetText(text) {\n // Convert URLs to links\n const urlRegex = /(https?:\\/\\/[^\\s]+)/g;\n text = text.replace(urlRegex, url => `${url}`);\n \n // Convert hashtags\n const hashtagRegex = /#(\\w+)/g;\n text = text.replace(hashtagRegex, (match, hashtag) => \n `#${hashtag}`\n );\n \n // Convert mentions\n const mentionRegex = /@(\\w+)/g;\n text = text.replace(mentionRegex, (match, username) => \n `@${username}`\n );\n \n return text;\n}\n\nasync function showMetrics(tweetId) {\n try {\n // Find the tweet in our state\n const tweet = state.tweets.find(t => t.id === tweetId);\n if (!tweet) {\n throw new Error('Tweet not found');\n }\n \n // Show the tweet content\n elements.tweetContent.innerHTML = `\n

\n ${tweet.user.profile_image_url \n ? ` $\"${tweet.user.name}\"$ `\n : `

\n ${tweet.user.name.charAt(0)}\n

`\n }\n

${tweet.user.name}

\n @${tweet.user.username}\n

${formatTweetText(tweet.text)}

\n `;\n \n // Fetch metrics history\n const metrics = await fetchTweetMetrics(tweetId);\n \n // Show modal\n elements.metricsModal.classList.remove('hidden');\n \n // Create chart\n createMetricsChart(metrics);\n \n } catch (error) {\n showError(error.message);\n }\n}\n\nfunction createMetricsChart(metrics) {\n // Destroy existing chart if it exists\n if (state.metricsChart) {\n state.metricsChart.destroy();\n }\n \n if (!metrics || metrics.length === 0) {\n elements.metricsChart.parentElement.innerHTML = '

No metrics data available

';\n return;\n }\n \n // Sort metrics by date\n metrics.sort((a, b) => new Date(a.recorded_at) - new Date(b.recorded_at));\n \n // Prepare data\n const labels = metrics.map(m => {\n const date = new Date(m.recorded_at);\n return date.toLocaleDateString() + ' ' + date.toLocaleTimeString([], { hour: '2-digit', minute: '2-digit' });\n });\n \n const retweetData = metrics.map(m => m.retweet_count);\n const likeData = metrics.map(m => m.like_count);\n const replyData = metrics.map(m => m.reply_count);\n const quoteData = metrics.map(m => m.quote_count);\n \n // Create chart\n const ctx = elements.metricsChart.getContext('2d');\n state.metricsChart = new Chart(ctx, {\n type: 'line',\n data: {\n labels: labels,\n datasets: [\n {\n label: 'Retweets',\n data: retweetData,\n borderColor: 'rgb(59, 130, 246)',\n backgroundColor: 'rgba(59, 130, 246, 0.1)',\n tension: 0.1\n },\n {\n label: 'Likes',\n data: likeData,\n borderColor: 'rgb(239, 68, 68)',\n backgroundColor: 'rgba(239, 68, 68, 0.1)',\n tension: 0.1\n },\n {\n label: 'Replies',\n data: replyData,\n borderColor: 'rgb(16, 185, 129)',\n backgroundColor: 'rgba(16, 185, 129, 0.1)',\n tension: 0.1\n },\n {\n label: 'Quotes',\n data: quoteData,\n borderColor: 'rgb(245, 158, 11)',\n backgroundColor: 'rgba(245, 158, 11, 0.1)',\n tension: 0.1\n }\n ]\n },\n options: {\n responsive: true,\n maintainAspectRatio: false,\n plugins: {\n legend: {\n position: 'top',\n },\n tooltip: {\n mode: 'index',\n intersect: false,\n }\n },\n scales: {\n y: {\n beginAtZero: true\n }\n }\n }\n });\n}\n\nfunction showLoading() {\n elements.loadingIndicator.classList.remove('hidden');\n}\n\nfunction hideLoading() {\n elements.loadingIndicator.classList.add('hidden');\n}\n\nfunction showError(message) {\n elements.errorMessage.textContent = message;\n elements.errorMessage.classList.remove('hidden');\n setTimeout(() => {\n elements.errorMessage.classList.add('hidden');\n }, 5000);\n}\n\n// Event Handlers\nasync function loadInitialTweets() {\n state.currentPage = 0;\n state.hasMore = true;\n \n const tweets = await fetchTweets(state.currentPage, state.pageSize);\n state.tweets = tweets;\n renderTweets(tweets);\n \n if (tweets.length < state.pageSize) {\n state.hasMore = false;\n elements.loadMoreContainer.classList.add('hidden');\n }\n}\n\nasync function loadMoreTweets() {\n state.currentPage++;\n const tweets = await fetchTweets(state.currentPage, state.pageSize);\n \n if (tweets.length > 0) {\n state.tweets = [...state.tweets, ...tweets];\n appendTweets(tweets);\n }\n \n if (tweets.length < state.pageSize) {\n state.hasMore = false;\n elements.loadMoreContainer.classList.add('hidden');\n }\n}\n\nasync function loadTopTweets() {\n const tweets = await fetchTopTweets(20);\n state.tweets = tweets;\n renderTweets(tweets);\n elements.loadMoreContainer.classList.add('hidden');\n}\n\nasync function handleSearch() {\n const query = elements.searchInput.value.trim();\n if (!query) return;\n \n const tweets = await searchTweets(query);\n state.tweets = tweets;\n renderTweets(tweets);\n elements.loadMoreContainer.classList.add('hidden');\n}\n\nasync function handleFetchUser() {\n const username = elements.usernameInput.value.trim();\n if (!username) return;\n \n const tweets = await fetchUserTweets(username);\n state.tweets = tweets;\n renderTweets(tweets);\n elements.loadMoreContainer.classList.add('hidden');\n}\n\nfunction closeMetricsModal() {\n elements.metricsModal.classList.add('hidden');\n if (state.metricsChart) {\n state.metricsChart.destroy();\n state.metricsChart = null;\n }\n}\n\nfunction switchToLatestTweets() {\n if (state.currentView === 'latest') return;\n \n state.currentView = 'latest';\n elements.latestTweetsBtn.classList.remove('bg-gray-200', 'text-gray-800');\n elements.latestTweetsBtn.classList.add('bg-blue-500', 'text-white');\n \n elements.topTweetsBtn.classList.remove('bg-blue-500', 'text-white');\n elements.topTweetsBtn.classList.add('bg-gray-200', 'text-gray-800');\n \n loadInitialTweets();\n}\n\nfunction switchToTopTweets() {\n if (state.currentView === 'top') return;\n \n state.currentView = 'top';\n elements.topTweetsBtn.classList.remove('bg-gray-200', 'text-gray-800');\n elements.topTweetsBtn.classList.add('bg-blue-500', 'text-white');\n \n elements.latestTweetsBtn.classList.remove('bg-blue-500', 'text-white');\n elements.latestTweetsBtn.classList.add('bg-gray-200', 'text-gray-800');\n \n loadTopTweets();\n}\n\n// Event Listeners\nelements.loadMoreBtn.addEventListener('click', loadMoreTweets);\nelements.searchButton.addEventListener('click', handleSearch);\nelements.searchInput.addEventListener('keypress', e => {\n if (e.key === 'Enter') handleSearch();\n});\nelements.fetchButton.addEventListener('click', handleFetchUser);\nelements.usernameInput.addEventListener('keypress', e => {\n if (e.key === 'Enter') handleFetchUser();\n});\nelements.closeModalBtn.addEventListener('click', closeMetricsModal);\nelements.metricsModal.addEventListener('click', e => {\n if (e.target === elements.metricsModal) closeMetricsModal();\n});\nelements.latestTweetsBtn.addEventListener('click', switchToLatestTweets);\nelements.topTweetsBtn.addEventListener('click', switchToTopTweets);\n\n// Initialize\nloadInitialTweets();",[],[228,238],"aedbde30-32e7-11f0-ae51-569c3dd06744","aedbdebc-32e7-11f0-ae51-569c3dd06744",16,["D",1747463110089],"# Frontend\n\nThis directory contains the client-side code for the Tweet Metrics Tracker application.\n\n## Structure\n\n- `index.html` - Main HTML template\n- `app.js` - Frontend JavaScript application\n- `style.css` - Custom CSS styles\n\n## Features\n\n- Display tweets in a responsive layout\n- Search tweets by keyword\n- Fetch tweets for specific users\n- View top tweets by engagement\n- View metrics history for individual tweets with charts\n- Pagination for loading more tweets\n\n## Dependencies\n\n- Tailwind CSS for styling\n- Chart.js for metrics visualization",[],[228,192],"7db76888-32e7-11f0-92c8-569c3dd06744",["D",1747463027634],[183,184,185,186],[228],"713d720a-32e7-11f0-92c8-569c3dd06744","index.ts",10,["D",1747463006703],"http","import { Hono } from \"https://esm.sh/hono@3.11.7\";\nimport { initDatabase } from \"./database/migrations.ts\";\nimport apiRouter from \"./routes/api.ts\";\nimport staticRouter from \"./routes/static.ts\";\n\n// Create main app\nconst app = new Hono();\n\n// Unwrap Hono errors to see original error details\napp.onError((err, c) => {\n console.error(\"Application error:\", err);\n throw err;\n});\n\n// Initialize database on startup\napp.use(\"*\", async (c, next) => {\n try {\n await initDatabase();\n } catch (error) {\n console.error(\"Database initialization error:\", error);\n }\n return next();\n});\n\n// Mount routers\napp.route(\"/api\", apiRouter);\napp.route(\"/\", staticRouter);\n\n// This is the entry point for HTTP vals\nexport default app.fetch;",[],[152,257],"5eadea5c-32e7-11f0-864f-569c3dd06744","cron.ts","5cf8b1ba-32e7-11f0-83e7-569c3dd06744",6,["D",1747462975559],"interval","import { TwitterClient } from \"./services/twitter.ts\";\nimport { initDatabase } from \"./database/migrations.ts\";\nimport { sqlite } from \"https://esm.town/v/stevekrouse/sqlite\";\nimport { TWEETS_TABLE } from \"./database/migrations.ts\";\n\n/**\n * Cron job to fetch and update tweets\n */\nexport default async function() {\n console.log(\"Starting tweet update cron job...\");\n \n try {\n // Initialize database if needed\n await initDatabase();\n \n const twitterClient = new TwitterClient();\n \n // 1. Fetch new tweets from specified accounts\n const accounts = [\"valtown\", \"stevekrouse\", \"twitter\"];\n for (const account of accounts) {\n console.log(`Fetching tweets for ${account}...`);\n await twitterClient.getUserTweets(account, 20);\n }\n \n // 2. Update metrics for existing tweets (most recent 100)\n console.log(\"Updating metrics for existing tweets...\");\n const result = await sqlite.execute(`\n SELECT id FROM ${TWEETS_TABLE}\n ORDER BY created_at DESC\n LIMIT 100\n `);\n \n const tweetIds = result.rows.map(row => row.id as string);\n await twitterClient.updateTweetMetrics(tweetIds);\n \n // 3. Search for trending topics\n const searchTerms = [\"valtown\", \"typescript\", \"javascript\", \"deno\"];\n for (const term of searchTerms) {\n console.log(`Searching tweets for \"${term}\"...`);\n await twitterClient.searchTweets(term, 10);\n }\n \n console.log(\"Tweet update cron job completed successfully\");\n } catch (error) {\n console.error(\"Error in tweet update cron job:\", error);\n }\n}",[],[152,265],"abfcd1c4-32e7-11f0-87b2-569c3dd06744","abfcd296-32e7-11f0-87b2-569c3dd06744",15,["D",1747463105202],"# Backend\n\nThis directory contains the server-side code for the Tweet Metrics Tracker application.\n\n## Structure\n\n- `index.ts` - Main entry point for the HTTP API\n- `cron.ts` - Cron job for periodically fetching and updating tweets\n- `database/` - Database schema and queries\n- `routes/` - API routes and static file serving\n- `services/` - External API integrations (Twitter API)\n\n## API Endpoints\n\n- `GET /api/tweets` - Get tweets with pagination\n- `GET /api/tweets/:id` - Get a single tweet by ID\n- `GET /api/tweets/:id/metrics` - Get metrics history for a tweet\n- `GET /api/top-tweets` - Get top tweets by engagement\n- `GET /api/fetch/:username` - Fetch tweets for a specific user\n- `GET /api/search?q=query` - Search tweets by keyword\n\n## Environment Variables\n\n- `TWITTER_API_KEY` - Twitter API key\n- `TWITTER_API_SECRET` - Twitter API secret\n- `TWITTER_BEARER_TOKEN` - Twitter bearer token",[],[152,192],["D",1747462964187],[],[152,72,64],["D",1747462964187],[178],[152,72],"6bb73c76-32e7-11f0-90dc-569c3dd06744","static.ts","6bb73d20-32e7-11f0-90dc-569c3dd06744","cbff6097-76e6-4d45-8027-83e00a282d9b",8,["D",1747462997444],"import { Hono } from \"https://esm.sh/hono@3.11.7\";\nimport { readFile, serveFile } from \"https://esm.town/v/std/utils/index.ts\";\n\n// Create static router\nconst staticRouter = new Hono();\n\n// Serve frontend files\nstaticRouter.get(\"/frontend/*\", (c) => serveFile(c.req.path, import.meta.url));\nstaticRouter.get(\"/shared/*\", (c) => serveFile(c.req.path, import.meta.url));\n\n// Serve index.html with initial data\nstaticRouter.get(\"/\", async (c) => {\n try {\n let html = await readFile(\"/frontend/index.html\", import.meta.url);\n \n // Get project info for view source link\n const projectUrl = import.meta.url.replace(\"esm.sh\", \"val.town\");\n \n // Inject data to avoid extra round-trips\n const dataScript = ``;\n \n html = html.replace(\"\", `${dataScript}`);\n return c.html(html);\n } catch (error) {\n console.error(\"Error serving index.html:\", error);\n return c.text(\"Error loading application\", 500);\n }\n});\n\nexport default staticRouter;",[],[152,295,287],"routes","670ae7b8-32e7-11f0-83e7-569c3dd06744","api.ts","670ae86c-32e7-11f0-83e7-569c3dd06744",7,["D",1747462989599],"import { Hono } from \"https://esm.sh/hono@3.11.7\";\nimport { cors } from \"https://esm.sh/hono@3.11.7/middleware\";\nimport { \n getTweets, \n getTopTweets, \n getTweet, \n getTweetMetricsHistory \n} from \"../database/queries.ts\";\nimport { TwitterClient } from \"../services/twitter.ts\";\n\n// Create API router\nconst api = new Hono();\n\n// Enable CORS\napi.use(\"/*\", cors());\n\n// Get tweets with pagination\napi.get(\"/tweets\", async (c) => {\n const limit = parseInt(c.req.query(\"limit\") || \"50\");\n const offset = parseInt(c.req.query(\"offset\") || \"0\");\n \n try {\n const tweets = await getTweets(limit, offset);\n return c.json({ success: true, data: tweets });\n } catch (error) {\n console.error(\"Error fetching tweets:\", error);\n return c.json({ success: false, error: \"Failed to fetch tweets\" }, 500);\n }\n});\n\n// Get a single tweet by ID\napi.get(\"/tweets/:id\", async (c) => {\n const id = c.req.param(\"id\");\n \n try {\n const tweet = await getTweet(id);\n if (!tweet) {\n return c.json({ success: false, error: \"Tweet not found\" }, 404);\n }\n return c.json({ success: true, data: tweet });\n } catch (error) {\n console.error(`Error fetching tweet ${id}:`, error);\n return c.json({ success: false, error: \"Failed to fetch tweet\" }, 500);\n }\n});\n\n// Get metrics history for a tweet\napi.get(\"/tweets/:id/metrics\", async (c) => {\n const id = c.req.param(\"id\");\n \n try {\n const metrics = await getTweetMetricsHistory(id);\n return c.json({ success: true, data: metrics });\n } catch (error) {\n console.error(`Error fetching metrics for tweet ${id}:`, error);\n return c.json({ success: false, error: \"Failed to fetch metrics\" }, 500);\n }\n});\n\n// Get top tweets\napi.get(\"/top-tweets\", async (c) => {\n const limit = parseInt(c.req.query(\"limit\") || \"10\");\n \n try {\n const tweets = await getTopTweets(limit);\n return c.json({ success: true, data: tweets });\n } catch (error) {\n console.error(\"Error fetching top tweets:\", error);\n return c.json({ success: false, error: \"Failed to fetch top tweets\" }, 500);\n }\n});\n\n// Fetch tweets for a specific user\napi.get(\"/fetch/:username\", async (c) => {\n const username = c.req.param(\"username\");\n const count = parseInt(c.req.query(\"count\") || \"10\");\n \n try {\n const twitterClient = new TwitterClient();\n const tweets = await twitterClient.getUserTweets(username, count);\n return c.json({ success: true, data: tweets });\n } catch (error) {\n console.error(`Error fetching tweets for ${username}:`, error);\n return c.json({ success: false, error: \"Failed to fetch tweets\" }, 500);\n }\n});\n\n// Search tweets\napi.get(\"/search\", async (c) => {\n const query = c.req.query(\"q\");\n if (!query) {\n return c.json({ success: false, error: \"Query parameter 'q' is required\" }, 400);\n }\n \n const count = parseInt(c.req.query(\"count\") || \"10\");\n \n try {\n const twitterClient = new TwitterClient();\n const tweets = await twitterClient.searchTweets(query, count);\n return c.json({ success: true, data: tweets });\n } catch (error) {\n console.error(`Error searching tweets for \"${query}\":`, error);\n return c.json({ success: false, error: \"Failed to search tweets\" }, 500);\n }\n});\n\nexport default api;",[],[152,295,297],"670b0ed2-32e7-11f0-83e7-569c3dd06744",["D",1747462989599],[175,176],[152,295],"462ae304-32e7-11f0-8ebf-569c3dd06744","queries.ts","462ae3ae-32e7-11f0-8ebf-569c3dd06744","fa4bba96-c674-4352-9413-03a1619a5b54",3,["D",1747462934449],"import { sqlite } from \"https://esm.town/v/stevekrouse/sqlite\";\nimport { TWEETS_TABLE, METRICS_TABLE, USERS_TABLE } from \"./migrations.ts\";\n\n// Types\nexport interface TwitterUser {\n id: string;\n username: string;\n name: string;\n profile_image_url?: string;\n followers_count: number;\n following_count: number;\n created_at: string;\n}\n\nexport interface Tweet {\n id: string;\n user_id: string;\n text: string;\n created_at: string;\n retweet_count: number;\n reply_count: number;\n like_count: number;\n quote_count: number;\n}\n\nexport interface TweetMetrics {\n tweet_id: string;\n retweet_count: number;\n reply_count: number;\n like_count: number;\n quote_count: number;\n recorded_at: string;\n}\n\nexport interface TweetWithUser extends Tweet {\n user: TwitterUser;\n}\n\n// User queries\nexport async function upsertUser(user: TwitterUser): Promise {\n await sqlite.execute(`\n INSERT INTO ${USERS_TABLE} (\n id, username, name, profile_image_url, followers_count, following_count, created_at, last_updated\n ) VALUES (?, ?, ?, ?, ?, ?, ?, CURRENT_TIMESTAMP)\n ON CONFLICT(id) DO UPDATE SET\n username = excluded.username,\n name = excluded.name,\n profile_image_url = excluded.profile_image_url,\n followers_count = excluded.followers_count,\n following_count = excluded.following_count,\n last_updated = CURRENT_TIMESTAMP\n `, [\n user.id,\n user.username,\n user.name,\n user.profile_image_url || null,\n user.followers_count,\n user.following_count,\n user.created_at\n ]);\n}\n\nexport async function getUser(userId: string): Promise {\n const result = await sqlite.execute(`\n SELECT * FROM ${USERS_TABLE} WHERE id = ?\n `, [userId]);\n \n return result.rows.length > 0 ? result.rows[0] as TwitterUser : null;\n}\n\n// Tweet queries\nexport async function upsertTweet(tweet: Tweet): Promise {\n await sqlite.execute(`\n INSERT INTO ${TWEETS_TABLE} (\n id, user_id, text, created_at, retweet_count, reply_count, like_count, quote_count, last_updated\n ) VALUES (?, ?, ?, ?, ?, ?, ?, ?, CURRENT_TIMESTAMP)\n ON CONFLICT(id) DO UPDATE SET\n retweet_count = excluded.retweet_count,\n reply_count = excluded.reply_count,\n like_count = excluded.like_count,\n quote_count = excluded.quote_count,\n last_updated = CURRENT_TIMESTAMP\n `, [\n tweet.id,\n tweet.user_id,\n tweet.text,\n tweet.created_at,\n tweet.retweet_count,\n tweet.reply_count,\n tweet.like_count,\n tweet.quote_count\n ]);\n}\n\nexport async function getTweet(tweetId: string): Promise {\n const result = await sqlite.execute(`\n SELECT * FROM ${TWEETS_TABLE} WHERE id = ?\n `, [tweetId]);\n \n return result.rows.length > 0 ? result.rows[0] as Tweet : null;\n}\n\nexport async function getTweets(limit = 50, offset = 0): Promise {\n const result = await sqlite.execute(`\n SELECT \n t.*,\n u.id as user_id,\n u.username,\n u.name,\n u.profile_image_url,\n u.followers_count,\n u.following_count,\n u.created_at as user_created_at\n FROM ${TWEETS_TABLE} t\n JOIN ${USERS_TABLE} u ON t.user_id = u.id\n ORDER BY t.created_at DESC\n LIMIT ? OFFSET ?\n `, [limit, offset]);\n \n return result.rows.map(row => {\n const tweet = {\n id: row.id,\n user_id: row.user_id,\n text: row.text,\n created_at: row.created_at,\n retweet_count: row.retweet_count,\n reply_count: row.reply_count,\n like_count: row.like_count,\n quote_count: row.quote_count,\n user: {\n id: row.user_id,\n username: row.username,\n name: row.name,\n profile_image_url: row.profile_image_url,\n followers_count: row.followers_count,\n following_count: row.following_count,\n created_at: row.user_created_at\n }\n } as TweetWithUser;\n return tweet;\n });\n}\n\n// Metrics queries\nexport async function recordMetrics(metrics: TweetMetrics): Promise {\n await sqlite.execute(`\n INSERT INTO ${METRICS_TABLE} (\n tweet_id, retweet_count, reply_count, like_count, quote_count\n ) VALUES (?, ?, ?, ?, ?)\n `, [\n metrics.tweet_id,\n metrics.retweet_count,\n metrics.reply_count,\n metrics.like_count,\n metrics.quote_count\n ]);\n}\n\nexport async function getTweetMetricsHistory(tweetId: string): Promise {\n const result = await sqlite.execute(`\n SELECT * FROM ${METRICS_TABLE}\n WHERE tweet_id = ?\n ORDER BY recorded_at ASC\n `, [tweetId]);\n \n return result.rows as TweetMetrics[];\n}\n\nexport async function getTopTweets(limit = 10): Promise {\n const result = await sqlite.execute(`\n SELECT \n t.*,\n u.id as user_id,\n u.username,\n u.name,\n u.profile_image_url,\n u.followers_count,\n u.following_count,\n u.created_at as user_created_at\n FROM ${TWEETS_TABLE} t\n JOIN ${USERS_TABLE} u ON t.user_id = u.id\n ORDER BY (t.retweet_count + t.like_count + t.quote_count) DESC\n LIMIT ?\n `, [limit]);\n \n return result.rows.map(row => {\n const tweet = {\n id: row.id,\n user_id: row.user_id,\n text: row.text,\n created_at: row.created_at,\n retweet_count: row.retweet_count,\n reply_count: row.reply_count,\n like_count: row.like_count,\n quote_count: row.quote_count,\n user: {\n id: row.user_id,\n username: row.username,\n name: row.name,\n profile_image_url: row.profile_image_url,\n followers_count: row.followers_count,\n following_count: row.following_count,\n created_at: row.user_created_at\n }\n } as TweetWithUser;\n return tweet;\n });\n}",[],[152,317,309],"database","39691dac-32e7-11f0-b04c-569c3dd06744","migrations.ts","39691e60-32e7-11f0-b04c-569c3dd06744",2,["D",1747462913044],"import { sqlite } from \"https://esm.town/v/stevekrouse/sqlite\";\n\n// Table names - use versioning for schema changes\nexport const TWEETS_TABLE = \"tweets_v1\";\nexport const METRICS_TABLE = \"metrics_v1\";\nexport const USERS_TABLE = \"twitter_users_v1\";\n\n/**\n * Initialize database tables\n */\nexport async function initDatabase() {\n // Create users table\n await sqlite.execute(`\n CREATE TABLE IF NOT EXISTS ${USERS_TABLE} (\n id TEXT PRIMARY KEY,\n username TEXT NOT NULL,\n name TEXT NOT NULL,\n profile_image_url TEXT,\n followers_count INTEGER,\n following_count INTEGER,\n created_at TIMESTAMP,\n last_updated TIMESTAMP DEFAULT CURRENT_TIMESTAMP\n )\n `);\n\n // Create tweets table\n await sqlite.execute(`\n CREATE TABLE IF NOT EXISTS ${TWEETS_TABLE} (\n id TEXT PRIMARY KEY,\n user_id TEXT NOT NULL,\n text TEXT NOT NULL,\n created_at TIMESTAMP,\n retweet_count INTEGER DEFAULT 0,\n reply_count INTEGER DEFAULT 0,\n like_count INTEGER DEFAULT 0,\n quote_count INTEGER DEFAULT 0,\n last_updated TIMESTAMP DEFAULT CURRENT_TIMESTAMP,\n FOREIGN KEY (user_id) REFERENCES ${USERS_TABLE}(id)\n )\n `);\n\n // Create metrics table for storing historical metrics\n await sqlite.execute(`\n CREATE TABLE IF NOT EXISTS ${METRICS_TABLE} (\n id INTEGER PRIMARY KEY AUTOINCREMENT,\n tweet_id TEXT NOT NULL,\n retweet_count INTEGER DEFAULT 0,\n reply_count INTEGER DEFAULT 0,\n like_count INTEGER DEFAULT 0,\n quote_count INTEGER DEFAULT 0,\n recorded_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,\n FOREIGN KEY (tweet_id) REFERENCES ${TWEETS_TABLE}(id)\n )\n `);\n\n console.log(\"Database initialized successfully\");\n}",[],[152,317,319],"b24e249c-32e7-11f0-be83-569c3dd06744","b24e2578-32e7-11f0-be83-569c3dd06744",17,["D",1747463115871],"# Database\n\nThis directory contains the database schema and query functions for the Tweet Metrics Tracker application.\n\n## Files\n\n- `migrations.ts` - Database schema definitions and initialization\n- `queries.ts` - Database query functions\n\n## Tables\n\n- `twitter_users_v1` - Stores Twitter user information\n- `tweets_v1` - Stores tweets\n- `metrics_v1` - Stores historical metrics for tweets\n\n## Schema Versioning\n\nWhen modifying the database schema, create new tables with incremented version numbers (e.g., `tweets_v2`) rather than altering existing tables.",[],[152,317,192],"396926b2-32e7-11f0-b04c-569c3dd06744",["D",1747462913044],[171,172,173],[152,317],"396924f0-32e7-11f0-b04c-569c3dd06744",["D",1747462913044],[170,174,177,179,180,181],[152],["D",1747462792492],{"_11":12,"_122":70},{"_11":12,"_118":-5,"_69":70,"_31":68,"_119":120,"_121":30},{"_39":109,"_13":16,"_33":36,"_110":75,"_111":-5,"_112":-5,"_113":-5,"_114":-5,"_115":-5,"_116":14,"_117":12},"ctx",{"_52":-5,"_141":378,"_379":380,"_381":382,"_383":30,"_384":30,"_385":30,"_386":24,"_387":-5,"_388":-7,"_389":-7,"_390":26,"_391":30,"_392":30,"_393":-5,"_394":30,"_395":30,"_396":30,"_397":-5},"ENV",{"_349":350,"_351":352,"_353":16,"_354":355,"_356":357,"_358":359,"_360":361,"_362":363,"_364":365,"_366":367,"_368":369,"_370":371,"_372":373,"_374":375,"_376":377},"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",[],"preferencesKeybindings","default","preferencesTheme","light","preferencesFixers","preferencesCopilot","preferencesSelectionAi","preferencesCloseBrackets","preferencesChatPanelState","preferencesDprint","preferencesDenofmt","notificationCount","flagDenols","isBusinessOrgMember","businessOrgHandle","isBusinessOrgAdmin","isHigherTownieTier","navCollapsed","currentOrgId"]

Search Tweets

Fetch User Tweets

Tweet Feed

Tweet Metrics History

${tweet.user.name}

${tweet.user.name}

History · services