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",[],[243,245],"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();",[],[243,253],"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",[],[243,207],"7db76888-32e7-11f0-92c8-569c3dd06744",["D",1747463027634],[198,199,200,201],[243],"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;",[],[161,272],"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}",[],[161,280],"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",[],[161,207],"57e4f472-32e7-11f0-9358-569c3dd06744","twitter.ts","57e4f51c-32e7-11f0-9358-569c3dd06744","310e2602-e1c6-4c75-bbff-22a1d7dad435",4,["D",1747462964187],"import { Tweet, TwitterUser, upsertTweet, upsertUser, recordMetrics } from \"../database/queries.ts\";\n\n// Twitter API v2 endpoints\nconst TWITTER_API_BASE = \"https://api.twitter.com/2\";\n\n/**\n * Twitter API client\n */\nexport class TwitterClient {\n private bearerToken: string;\n\n constructor() {\n const bearerToken = Deno.env.get(\"TWITTER_BEARER_TOKEN\");\n if (!bearerToken) {\n throw new Error(\"TWITTER_BEARER_TOKEN environment variable is not set\");\n }\n this.bearerToken = bearerToken;\n }\n\n /**\n * Make an authenticated request to the Twitter API\n */\n private async request(endpoint: string, params: Record = {}): Promise {\n const url = new URL(`${TWITTER_API_BASE}${endpoint}`);\n \n // Add query parameters\n Object.entries(params).forEach(([key, value]) => {\n url.searchParams.append(key, value);\n });\n\n const response = await fetch(url.toString(), {\n headers: {\n Authorization: `Bearer ${this.bearerToken}`,\n },\n });\n\n if (!response.ok) {\n const errorText = await response.text();\n throw new Error(`Twitter API error (${response.status}): ${errorText}`);\n }\n\n return response.json();\n }\n\n /**\n * Fetch tweets from a user\n */\n async getUserTweets(username: string, count = 10): Promise {\n // First get the user ID\n const userResponse = await this.request(`/users/by/username/${username}`, {\n \"user.fields\": \"id,username,name,profile_image_url,public_metrics,created_at\",\n });\n\n if (!userResponse.data) {\n throw new Error(`User not found: ${username}`);\n }\n\n const userData = userResponse.data;\n \n // Create user object\n const user: TwitterUser = {\n id: userData.id,\n username: userData.username,\n name: userData.name,\n profile_image_url: userData.profile_image_url,\n followers_count: userData.public_metrics?.followers_count || 0,\n following_count: userData.public_metrics?.following_count || 0,\n created_at: userData.created_at,\n };\n\n // Save user to database\n await upsertUser(user);\n\n // Get user's tweets\n const tweetsResponse = await this.request(`/users/${user.id}/tweets`, {\n max_results: count.toString(),\n \"tweet.fields\": \"id,text,created_at,public_metrics\",\n exclude: \"retweets,replies\",\n });\n\n if (!tweetsResponse.data) {\n return [];\n }\n\n // Process and save tweets\n const tweets: Tweet[] = [];\n for (const tweetData of tweetsResponse.data) {\n const tweet: Tweet = {\n id: tweetData.id,\n user_id: user.id,\n text: tweetData.text,\n created_at: tweetData.created_at,\n retweet_count: tweetData.public_metrics?.retweet_count || 0,\n reply_count: tweetData.public_metrics?.reply_count || 0,\n like_count: tweetData.public_metrics?.like_count || 0,\n quote_count: tweetData.public_metrics?.quote_count || 0,\n };\n\n // Save tweet to database\n await upsertTweet(tweet);\n \n // Record metrics history\n await recordMetrics({\n tweet_id: tweet.id,\n retweet_count: tweet.retweet_count,\n reply_count: tweet.reply_count,\n like_count: tweet.like_count,\n quote_count: tweet.quote_count,\n recorded_at: new Date().toISOString(),\n });\n\n tweets.push(tweet);\n }\n\n return tweets;\n }\n\n /**\n * Search for tweets by keyword\n */\n async searchTweets(query: string, count = 10): Promise {\n const searchResponse = await this.request(`/tweets/search/recent`, {\n query,\n max_results: count.toString(),\n \"tweet.fields\": \"id,text,created_at,public_metrics,author_id\",\n \"user.fields\": \"id,username,name,profile_image_url,public_metrics,created_at\",\n \"expansions\": \"author_id\",\n });\n\n if (!searchResponse.data || searchResponse.data.length === 0) {\n return [];\n }\n\n // Process users\n const users: Record = {};\n if (searchResponse.includes?.users) {\n for (const userData of searchResponse.includes.users) {\n const user: TwitterUser = {\n id: userData.id,\n username: userData.username,\n name: userData.name,\n profile_image_url: userData.profile_image_url,\n followers_count: userData.public_metrics?.followers_count || 0,\n following_count: userData.public_metrics?.following_count || 0,\n created_at: userData.created_at,\n };\n users[user.id] = user;\n await upsertUser(user);\n }\n }\n\n // Process tweets\n const tweets: Tweet[] = [];\n for (const tweetData of searchResponse.data) {\n if (!tweetData.author_id || !users[tweetData.author_id]) {\n continue; // Skip tweets without author info\n }\n\n const tweet: Tweet = {\n id: tweetData.id,\n user_id: tweetData.author_id,\n text: tweetData.text,\n created_at: tweetData.created_at,\n retweet_count: tweetData.public_metrics?.retweet_count || 0,\n reply_count: tweetData.public_metrics?.reply_count || 0,\n like_count: tweetData.public_metrics?.like_count || 0,\n quote_count: tweetData.public_metrics?.quote_count || 0,\n };\n\n // Save tweet to database\n await upsertTweet(tweet);\n \n // Record metrics history\n await recordMetrics({\n tweet_id: tweet.id,\n retweet_count: tweet.retweet_count,\n reply_count: tweet.reply_count,\n like_count: tweet.like_count,\n quote_count: tweet.quote_count,\n recorded_at: new Date().toISOString(),\n });\n\n tweets.push(tweet);\n }\n\n return tweets;\n }\n\n /**\n * Update metrics for existing tweets\n */\n async updateTweetMetrics(tweetIds: string[]): Promise {\n if (tweetIds.length === 0) return;\n \n // Twitter API allows up to 100 tweets per request\n const chunks = [];\n for (let i = 0; i < tweetIds.length; i += 100) {\n chunks.push(tweetIds.slice(i, i + 100));\n }\n \n for (const chunk of chunks) {\n const ids = chunk.join(\",\");\n const response = await this.request(`/tweets`, {\n ids,\n \"tweet.fields\": \"id,public_metrics\",\n });\n \n if (!response.data) continue;\n \n for (const tweetData of response.data) {\n const tweet = {\n id: tweetData.id,\n user_id: \"\", // We'll keep the existing user_id\n text: \"\", // We'll keep the existing text\n created_at: \"\", // We'll keep the existing created_at\n retweet_count: tweetData.public_metrics?.retweet_count || 0,\n reply_count: tweetData.public_metrics?.reply_count || 0,\n like_count: tweetData.public_metrics?.like_count || 0,\n quote_count: tweetData.public_metrics?.quote_count || 0,\n };\n \n // Get existing tweet to preserve fields we don't want to update\n const existingTweet = await getTweet(tweet.id);\n if (existingTweet) {\n tweet.user_id = existingTweet.user_id;\n tweet.text = existingTweet.text;\n tweet.created_at = existingTweet.created_at;\n \n // Update tweet\n await upsertTweet(tweet);\n \n // Record metrics history\n await recordMetrics({\n tweet_id: tweet.id,\n retweet_count: tweet.retweet_count,\n reply_count: tweet.reply_count,\n like_count: tweet.like_count,\n quote_count: tweet.quote_count,\n recorded_at: new Date().toISOString(),\n });\n }\n }\n }\n }\n}\n\n// Helper function to get a tweet by ID\nasync function getTweet(tweetId: string): Promise {\n const result = await sqlite.execute(`\n SELECT * FROM tweets_v1 WHERE id = ?\n `, [tweetId]);\n \n return result.rows.length > 0 ? result.rows[0] as Tweet : null;\n}",[],[161,304,296],"services","57e5352c-32e7-11f0-9358-569c3dd06744",["D",1747462964187],[193],[161,304],["D",1747462997444],[],[161,82,80],["D",1747462989599],[],[161,82,65],["D",1747462989599],[190,191],[161,82],"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}",[],[161,327,319],"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}",[],[161,327,329],"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.",[],[161,327,207],"396926b2-32e7-11f0-b04c-569c3dd06744",["D",1747462913044],[186,187,188],[161,327],"396924f0-32e7-11f0-b04c-569c3dd06744",["D",1747462913044],[185,189,192,194,195,196],[161],["D",1747462792492],{"_11":12,"_132":71},{"_11":12,"_128":-5,"_70":71,"_31":69,"_129":130,"_131":30},{"_40":119,"_13":16,"_34":37,"_120":85,"_121":-5,"_122":-5,"_123":-5,"_124":-5,"_125":-5,"_126":14,"_127":12},"ctx",{"_53":-5,"_151":388,"_389":390,"_391":392,"_393":30,"_394":30,"_395":30,"_396":24,"_397":-5,"_398":-7,"_399":-7,"_400":26,"_401":30,"_402":30,"_403":-5,"_404":30,"_405":30,"_406":30,"_407":-5},"ENV",{"_359":360,"_361":362,"_363":16,"_364":365,"_366":367,"_368":369,"_370":371,"_372":373,"_374":375,"_376":377,"_378":379,"_380":381,"_382":383,"_384":385,"_386":387},"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 · routes