[{"_1":2,"_3":-5,"_4":-5},"loaderData",{"_5":6,"_7":8,"_9":10},"actionData","errors","root",{"_233":234,"_235":236},"routes/_app.x.$handle.$name",{"_102":101,"_32":51,"_110":92,"_27":111,"_13":14,"_112":-5,"_11":113,"_114":115,"_116":117,"_118":119,"_120":121,"_21":-5,"_122":-5,"_123":-5,"_124":25,"_125":126,"_127":25,"_128":-5,"_129":-5,"_130":131},"routes/_app.x.$handle.$name.branch.$branch.version.$version.diff",{"_11":12,"_13":14,"_15":16,"_17":-5,"_18":-5},"project",{"_49":50,"_32":51,"_37":52,"_53":-5,"_54":25,"_55":56,"_57":56,"_58":-5,"_59":-5,"_18":60,"_61":62,"_63":64,"_65":64,"_66":-5,"_67":68,"_69":70,"_71":-5,"_72":-5,"_73":-5,"_74":-5,"_75":76,"_77":76,"_78":79,"_80":79,"_81":79,"_82":76,"_83":76,"_84":85,"_86":87,"_88":89,"_90":25},"version",7,"changes",[19,20],"aiChatInfo","userId",{"_21":42,"_23":-7,"_24":25,"_26":25,"_27":43,"_29":43},{"_21":22,"_23":-7,"_24":25,"_26":25,"_27":28,"_29":28},"file",{"_30":31,"_32":33,"_34":35,"_36":-5,"_37":38,"_39":14,"_40":41,"_24":25},"previous","deleted",false,"deletedHere","path","backend/routes","latestPath","fileId","cbff6097-76e6-4d45-8027-83e00a282d9b","name","routes","fileType","directory","content","createdAt",["D",1747462989599],"branchVersion","branchId","f18e3a30-32e6-11f0-92c8-569c3dd06744",{"_30":44,"_32":45,"_34":46,"_36":47,"_37":48,"_39":14,"_40":41,"_24":25},"backend/routes/api.ts","670ae86c-32e7-11f0-83e7-569c3dd06744","api.ts","script","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;",["D",1747462989599],"id","f18dd69e-32e6-11f0-92c8-569c3dd06744","first",["D",1747462792492],"deletedAt","legacyEnvVars","privacy","public","httpPrivacy","description","imageUrl","6188a9ab-68f8-4c46-8cb2-69799954d7f7","pinnedFileId","6fbab686-32e7-11f0-b23a-569c3dd06744","pinPreview",true,"pinMigrated","screenshotVersion","likeCount",0,"orgId","8dacdf4f-8a9d-4420-a8ca-3e5e0405428c","tursoName","tursoHostname","tursoMeasuredAt","pinned","userPerm","r","valPerm","blobPerm","rw","emailPerm","sqlitePerm","projectPerm","telemetryPerm","user",{"_102":101,"_109":105},"org",{"_102":101,"_103":-5,"_104":105,"_18":60,"_106":107,"_108":25},"branch",{"_13":91,"_32":92,"_49":41,"_93":50,"_94":-5,"_95":-5,"_96":-5,"_97":-5,"_98":-5,"_99":51,"_100":101},"isOwned",18,"main","projectId","forkedBranchId","forkedBranchVersion","forkedProjectPrivacy","forkedProjectId","forkedProjectOrgId","projectName","userHandle","gthmbri","handle","displayName","avatar","https://img.clerk.com/eyJ0eXBlIjoicHJveHkiLCJzcmMiOiJodHRwczovL2ltYWdlcy5jbGVyay5kZXYvb2F1dGhfZ2l0aHViL2ltZ18yZ2JnSkFHMmhJV0N2ZmtVNnNrYkdyRDdpMFoifQ","tier","free","canRestrictVals","profileImageUrl","branchName","","adoptOrgId",{"_49":50,"_32":51,"_37":229,"_53":-5,"_54":25,"_55":56,"_57":56,"_58":-5,"_59":-5,"_18":60,"_61":62,"_63":64,"_65":64,"_66":-5,"_67":68,"_69":70,"_71":-5,"_72":-5,"_73":-5,"_74":-5,"_75":76,"_77":76,"_78":79,"_80":79,"_81":79,"_82":76,"_83":76,"_84":230,"_86":231,"_88":232,"_90":25},"tree",[160,169],"files",[160,161,162,163,164,165,166,167,168,169],"counts",{"_150":68,"_151":138,"_152":68,"_153":154,"_155":156},"contents",[132,133],"upstreamProject","upstreamBranch","emptyEnvVars","flags",[],"hasHttpFiles","grants","orgs","customDomains",{},{"_49":144,"_32":145,"_30":146,"_137":-5,"_40":41,"_39":147,"_139":64,"_37":148,"_13":68,"_34":35,"_36":-5,"_24":25,"_142":-5,"_27":149},{"_49":134,"_32":135,"_30":136,"_137":-5,"_40":41,"_39":138,"_139":64,"_37":140,"_13":68,"_34":21,"_36":141,"_24":25,"_142":-5,"_27":143},"349b548e-32e7-11f0-998d-569c3dd06744","README.md","349b5560-32e7-11f0-998d-569c3dd06744","parentId",1,"latest",["D",1747462904985],"# Tweet Metrics Tracker\n\nThis application scrapes tweets and other metrics from Twitter/X API, stores them in a SQLite database, and displays them in a web interface.\n\n## Features\n\n- Fetches tweets and metrics from Twitter/X API\n- Stores data in SQLite database\n- Periodically updates data using a cron job\n- Displays tweets and metrics in a web interface\n- Provides filtering and sorting options\n\n## Project Structure\n\n```\n├── backend/\n│ ├── database/\n│ │ ├── migrations.ts # Schema definitions\n│ │ └── queries.ts # DB query functions\n│ ├── services/\n│ │ └── twitter.ts # Twitter API integration\n│ ├── routes/\n│ │ ├── api.ts # API routes\n│ │ └── static.ts # Static file serving\n│ ├── cron.ts # Cron job for data fetching\n│ └── index.ts # Main entry point\n├── frontend/\n│ ├── components/\n│ │ ├── TweetList.js # Tweet list component\n│ │ └── MetricsChart.js # Metrics visualization\n│ ├── index.html # Main HTML template\n│ ├── app.js # Frontend JS entry point\n│ └── style.css # Custom styles\n└── shared/\n └── types.ts # Shared types and interfaces\n```\n\n## Setup\n\n1. Set up the following environment variables in Val Town:\n - `TWITTER_API_KEY` - Your Twitter/X API key\n - `TWITTER_API_SECRET` - Your Twitter/X API secret\n - `TWITTER_BEARER_TOKEN` - Your Twitter/X bearer token\n\n2. Deploy the application on Val Town\n\n## Usage\n\n- Access the web interface to view tweets and metrics\n- The data is automatically updated via a cron job\n- Use the API endpoints to access the data programmatically","label",[135],"396924f0-32e7-11f0-b04c-569c3dd06744","backend","2ad76b0f-eed1-4879-b879-e24417c894bc",2,["D",1747462913044],[145],"pulls","branches","forks","envVars",{"_157":68,"_159":68},"connections",{"_157":68,"_158":68},"count","unfilledCount","emptyCount",{"_49":144,"_32":145,"_30":146,"_137":-5,"_40":41,"_39":147,"_139":64,"_37":226,"_13":68,"_34":35,"_36":-5,"_24":25,"_142":-5,"_171":227,"_27":228},{"_49":222,"_32":214,"_30":208,"_137":146,"_40":41,"_39":147,"_139":64,"_37":223,"_13":68,"_34":35,"_36":-5,"_24":25,"_142":-5,"_171":224,"_27":225},{"_49":215,"_32":216,"_30":217,"_137":208,"_40":41,"_39":147,"_139":64,"_37":218,"_13":68,"_34":46,"_36":219,"_24":25,"_142":-5,"_171":220,"_27":221},{"_49":205,"_32":206,"_30":207,"_137":208,"_40":41,"_39":209,"_139":64,"_37":210,"_13":68,"_34":46,"_36":211,"_24":25,"_142":-5,"_171":212,"_27":213},{"_49":201,"_32":33,"_30":31,"_137":146,"_40":41,"_39":14,"_139":64,"_37":202,"_13":68,"_34":35,"_36":-5,"_24":25,"_142":-5,"_171":203,"_27":204},{"_49":197,"_32":45,"_30":44,"_137":31,"_40":41,"_39":14,"_139":64,"_37":198,"_13":68,"_34":46,"_36":47,"_24":25,"_142":-5,"_171":199,"_27":200},{"_49":193,"_32":192,"_30":186,"_137":146,"_40":41,"_39":187,"_139":64,"_37":194,"_13":68,"_34":35,"_36":-5,"_24":25,"_142":-5,"_171":195,"_27":196},{"_49":183,"_32":184,"_30":185,"_137":186,"_40":41,"_39":187,"_139":64,"_37":188,"_13":68,"_34":46,"_36":189,"_24":25,"_142":-5,"_171":190,"_27":191},{"_49":174,"_32":175,"_30":176,"_137":146,"_40":41,"_39":177,"_139":64,"_37":178,"_13":138,"_34":179,"_36":180,"_24":25,"_142":-5,"_171":181,"_27":182},{"_49":134,"_32":135,"_30":136,"_137":-5,"_40":41,"_39":138,"_139":64,"_37":170,"_13":68,"_34":21,"_36":141,"_24":25,"_142":-5,"_171":172,"_27":173},["D",1747462904985],"children",[],[135],"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}",[],[145,175],"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}",[],[145,192,184],"services","57e5352c-32e7-11f0-9358-569c3dd06744",["D",1747462964187],[167],[145,192],"670ae7b8-32e7-11f0-83e7-569c3dd06744",["D",1747462989599],[],[145,33,45],"670b0ed2-32e7-11f0-83e7-569c3dd06744",["D",1747462989599],[165],[145,33],"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}",[],[145,214,206],"database","39691dac-32e7-11f0-b04c-569c3dd06744","migrations.ts","39691e60-32e7-11f0-b04c-569c3dd06744",["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}",[],[145,214,216],"396926b2-32e7-11f0-b04c-569c3dd06744",["D",1747462913044],[162,163],[145,214],["D",1747462913044],[161,164,166,168],[145],["D",1747462792492],{"_102":101,"_109":105},{"_102":101,"_103":-5,"_104":105,"_18":60,"_106":107,"_108":25},{"_13":91,"_32":92,"_49":41,"_93":50,"_94":-5,"_95":-5,"_96":-5,"_97":-5,"_98":-5,"_99":51,"_100":101},"ctx",{"_84":-5,"_129":266,"_267":268,"_269":270,"_271":25,"_272":25,"_273":25,"_274":64,"_275":-5,"_276":-7,"_277":-7,"_278":68,"_279":25,"_280":25,"_281":-5,"_282":25,"_283":25,"_284":25,"_285":-5},"ENV",{"_237":238,"_239":240,"_241":92,"_242":243,"_244":245,"_246":247,"_248":249,"_250":251,"_252":253,"_254":255,"_256":257,"_258":259,"_260":261,"_262":263,"_264":265},"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"]

Diff · v7