commit f4bedce994cdcaf00e89b6cd7bfb68b50c3412ba
parent cb91fbe80c07ac11a0792795961e322a1cbecce9
Author: Silas Brack <silasbrack@gmail.com>
Date: Tue, 9 Jun 2026 20:07:28 +0200
fix: batch-fetch comment authors and tag queries to eliminate N+1
- Add /enrich_comments WASM endpoint for batch user profile lookup
- Add /stories_by_tag WASM endpoint with proper JOIN/LIMIT/OFFSET
- Replace per-comment author fetch loop with single batch call
- Replace unbounded junction table scan in get_stories_by_tag
- Fix N+1 in tag handler using enrich_stories batch endpoint
Co-Authored-By: Claude Opus 4.6 (1M context) <noreply@anthropic.com>
Diffstat:
6 files changed, 214 insertions(+), 66 deletions(-)
diff --git a/guests/rust/src/lib.rs b/guests/rust/src/lib.rs
@@ -154,11 +154,87 @@ async fn enrich_handler(req: Request) -> Result<Json<Vec<StoryMeta>>, HttpError>
Ok(Json(result))
}
+// GET /enrich_comments?user_ids=abc,def
+// Returns a mapping from user_id to username for a batch of user IDs.
+#[derive(serde::Serialize)]
+struct UserMapping {
+ user_id: String,
+ username: String,
+}
+
+async fn enrich_comments_handler(req: Request) -> Result<Json<Vec<UserMapping>>, HttpError> {
+ let ids_str = req
+ .query_param("user_ids")
+ .ok_or_else(|| HttpError::message(StatusCode::BAD_REQUEST, "Missing 'user_ids' parameter"))?;
+
+ let ids: Vec<&str> = ids_str.split(',').map(|s| s.trim()).filter(|s| !s.is_empty()).collect();
+
+ if ids.is_empty() {
+ return Ok(Json(vec![]));
+ }
+
+ let placeholders: Vec<String> = (1..=ids.len()).map(|i| format!("${}", i)).collect();
+ let placeholder_str = placeholders.join(",");
+
+ let sql = format!(
+ "SELECT user_id, username FROM user_profile WHERE user_id IN ({})",
+ placeholder_str
+ );
+ let params: Vec<Value> = ids.iter().map(|&id| Value::Text(id.to_string())).collect();
+ let rows = query(&sql, params).await.map_err(err)?;
+
+ let result = rows
+ .iter()
+ .map(|row| UserMapping {
+ user_id: as_text(&row[0]),
+ username: as_text(&row[1]),
+ })
+ .collect();
+
+ Ok(Json(result))
+}
+
+// GET /stories_by_tag?category_id=5&limit=31&offset=0
+// Returns story IDs for a category, ordered by published date descending.
+async fn stories_by_tag_handler(req: Request) -> Result<Json<Vec<i64>>, HttpError> {
+ let category_id: i64 = req
+ .query_param("category_id")
+ .and_then(|s| s.parse().ok())
+ .ok_or_else(|| HttpError::message(StatusCode::BAD_REQUEST, "Missing 'category_id' parameter"))?;
+
+ let limit: i64 = req
+ .query_param("limit")
+ .and_then(|s| s.parse().ok())
+ .unwrap_or(31);
+
+ let offset: i64 = req
+ .query_param("offset")
+ .and_then(|s| s.parse().ok())
+ .unwrap_or(0);
+
+ let rows = query(
+ "SELECT s.id
+ FROM story s
+ JOIN story_category sc ON sc.news_item_id = s.id
+ WHERE sc.category_id = $1
+ ORDER BY s.published DESC
+ LIMIT $2 OFFSET $3",
+ [Value::Integer(category_id), Value::Integer(limit), Value::Integer(offset)],
+ )
+ .await
+ .map_err(err)?;
+
+ let ids = rows.iter().map(|row| as_integer(&row[0])).collect();
+ Ok(Json(ids))
+}
+
impl Guest for ApiGuest {
fn http_handlers() -> Vec<HttpRoute> {
vec![
routing::get("/search", search_handler),
routing::get("/enrich", enrich_handler),
+ routing::get("/enrich_comments", enrich_comments_handler),
+ routing::get("/stories_by_tag", stories_by_tag_handler),
]
}
}
diff --git a/src/handlers/feed.rs b/src/handlers/feed.rs
@@ -56,7 +56,7 @@ async fn fetch_feed(
// Fetch stories based on sort and optional tag filter
let stories = match query.tag {
Some(tag_id) => {
- trailbase::get_stories_by_tag(&client, tag_id, PAGE_LIMIT + 1, offset).await?
+ trailbase::get_stories_by_tag(&state.http_client, &state.trailbase_url, &client, tag_id, PAGE_LIMIT + 1, offset).await?
}
None => match sort {
FeedSort::Hot => {
diff --git a/src/handlers/story.rs b/src/handlers/story.rs
@@ -11,9 +11,63 @@ use crate::db::{CommentWithMeta, StoryWithMeta};
use crate::error::AppError;
use crate::handlers::auth::get_tokens_from_cookies;
use crate::state::AppState;
-use crate::templates::{ApplicationTemplate, EditTemplate, StoryTemplate, SubmitTemplate};
+use crate::templates::{ApplicationTemplate, CommentsTemplate, EditTemplate, StoryTemplate, SubmitTemplate};
use crate::trailbase::{self, TARGET_TYPE_COMMENT, TARGET_TYPE_STORY};
+async fn fetch_enriched_comments(
+ state: &AppState,
+ tokens: &Option<trailbase::StoredTokens>,
+ client: &trailbase_client::Client,
+ story_id: i64,
+) -> Result<Vec<CommentWithMeta>, AppError> {
+ let comments = trailbase::get_comments_for_story(client, story_id).await?;
+
+ let comment_ids: Vec<i64> = comments.iter().map(|c| c.id).collect();
+ let voted_comment_ids = if tokens.is_some() && !comment_ids.is_empty() {
+ trailbase::get_user_votes(client, TARGET_TYPE_COMMENT, &comment_ids)
+ .await
+ .inspect_err(|e| tracing::error!("Failed to get comment votes: {}", e))
+ .unwrap_or_default()
+ } else {
+ vec![]
+ };
+
+ // Batch-fetch comment author usernames (1 query instead of N)
+ let unique_user_ids: Vec<&str> = {
+ let mut ids: Vec<&str> = comments.iter().map(|c| c.created_by.as_str()).collect();
+ ids.sort_unstable();
+ ids.dedup();
+ ids
+ };
+ let author_map = trailbase::enrich_comment_authors(
+ &state.http_client,
+ &state.trailbase_url,
+ &unique_user_ids,
+ )
+ .await
+ .inspect_err(|e| tracing::error!("Failed to enrich comment authors: {}", e))
+ .unwrap_or_default();
+
+ let mut enriched = Vec::with_capacity(comments.len());
+ for comment in comments {
+ let comment_author = author_map
+ .iter()
+ .find(|m| m.user_id == comment.created_by)
+ .map(|m| m.username.clone());
+ let comment_time_ago = trailbase::time_ago(&comment.created_at);
+ let comment_voted = voted_comment_ids.contains(&comment.id);
+
+ enriched.push(CommentWithMeta::from_comment(
+ comment,
+ comment_author,
+ comment_time_ago,
+ comment_voted,
+ ));
+ }
+
+ Ok(enriched)
+}
+
pub async fn show_story(
State(state): State<AppState>,
jar: CookieJar,
@@ -30,9 +84,6 @@ pub async fn show_story(
.await?
.ok_or_else(|| AppError::NotFound("Story not found".to_string()))?;
- // Fetch comments (already ordered by path from the API)
- let comments = trailbase::get_comments_for_story(&client, id).await?;
-
// Get tags for the story
let tags = trailbase::get_categories_for_story(&client, id)
.await
@@ -60,39 +111,11 @@ pub async fn show_story(
false
};
- let comment_ids: Vec<i64> = comments.iter().map(|c| c.id).collect();
- let voted_comment_ids = if tokens.is_some() && !comment_ids.is_empty() {
- trailbase::get_user_votes(&client, TARGET_TYPE_COMMENT, &comment_ids)
- .await
- .inspect_err(|e| tracing::error!("Failed to get comment votes: {}", e))
- .unwrap_or_default()
- } else {
- vec![]
- };
-
// Enrich story
let time_ago = trailbase::time_ago(&story.published);
let story_with_meta = StoryWithMeta::from_story(story, tags, author_username, time_ago, story_voted, 0);
- // Enrich comments (flat list sorted by path for display)
- let mut enriched_comments = Vec::with_capacity(comments.len());
- for comment in comments {
- let comment_author = trailbase::get_user_profile_by_id(&client, &comment.created_by)
- .await
- .inspect_err(|e| tracing::error!("Failed to get profile for {}: {}", comment.created_by, e))
- .ok()
- .flatten()
- .map(|p| p.username);
- let comment_time_ago = trailbase::time_ago(&comment.created_at);
- let comment_voted = voted_comment_ids.contains(&comment.id);
-
- enriched_comments.push(CommentWithMeta::from_comment(
- comment,
- comment_author,
- comment_time_ago,
- comment_voted,
- ));
- }
+ let enriched_comments = fetch_enriched_comments(&state, &tokens, &client, id).await?;
let is_logged_in = tokens.is_some();
let current_user_id = tokens
@@ -112,6 +135,28 @@ pub async fn show_story(
Ok(Html(app.render()?))
}
+/// Returns just the comments HTML fragment for a story (used by SSE refresh)
+pub async fn story_comments_fragment(
+ State(state): State<AppState>,
+ jar: CookieJar,
+ Path(id): Path<i64>,
+) -> Result<Html<String>, AppError> {
+ let tokens = get_tokens_from_cookies(&jar);
+ let client = match &tokens {
+ Some(t) => trailbase::client_with_tokens(&state.trailbase_url, t)?,
+ None => trailbase::new_client(&state.trailbase_url)?,
+ };
+
+ let enriched_comments = fetch_enriched_comments(&state, &tokens, &client, id).await?;
+ let is_logged_in = tokens.is_some();
+
+ let template = CommentsTemplate {
+ comments: enriched_comments,
+ is_logged_in,
+ };
+ Ok(Html(template.render()?))
+}
+
pub async fn show_submit(
State(state): State<AppState>,
jar: CookieJar,
diff --git a/src/handlers/tag.rs b/src/handlers/tag.rs
@@ -6,7 +6,7 @@ use axum::{
use axum_extra::extract::cookie::CookieJar;
use serde::Deserialize;
-use crate::db::StoryWithMeta;
+use crate::db::{Category, StoryWithMeta};
use crate::error::AppError;
use crate::handlers::auth::get_tokens_from_cookies;
use crate::state::AppState;
@@ -41,15 +41,14 @@ pub async fn show_tag(
.ok_or_else(|| AppError::NotFound(format!("Tag '{}' not found", tag_name)))?;
let offset = query.page * PAGE_LIMIT;
- let stories = trailbase::get_stories_by_tag(&client, category.id, PAGE_LIMIT + 1, offset).await?;
+ let stories = trailbase::get_stories_by_tag(&state.http_client, &state.trailbase_url, &client, category.id, PAGE_LIMIT + 1, offset).await?;
let reached_end = stories.len() <= PAGE_LIMIT;
let stories: Vec<_> = stories.into_iter().take(PAGE_LIMIT).collect();
- // Get story IDs for vote checking
+ // Get story IDs for vote checking and enrichment
let story_ids: Vec<i64> = stories.iter().map(|s| s.id).collect();
- // Check which stories the user has voted on
let voted_ids = if tokens.is_some() && !story_ids.is_empty() {
trailbase::get_user_votes(&client, TARGET_TYPE_STORY, &story_ids)
.await
@@ -59,25 +58,21 @@ pub async fn show_tag(
vec![]
};
+ // Batch fetch tags and authors
+ let meta = trailbase::enrich_stories(&state.http_client, &state.trailbase_url, &story_ids)
+ .await
+ .inspect_err(|e| tracing::error!("Failed to enrich stories: {}", e))
+ .unwrap_or_default();
+
let mut enriched_stories = Vec::with_capacity(stories.len());
let base_rank = query.page * PAGE_LIMIT;
for (idx, story) in stories.into_iter().enumerate() {
- let tags = trailbase::get_categories_for_story(&client, story.id)
- .await
- .inspect_err(|e| tracing::error!("Failed to get tags for story {}: {}", story.id, e))
+ let story_meta = meta.iter().find(|m| m.story_id == story.id);
+ let tags: Vec<Category> = story_meta
+ .map(|m| m.tags.iter().map(|name| Category { id: 0, name: Some(name.clone()) }).collect())
.unwrap_or_default();
-
- let author_username = if let Some(ref created_by) = story.created_by {
- trailbase::get_user_profile_by_id(&client, created_by)
- .await
- .inspect_err(|e| tracing::error!("Failed to get profile for {}: {}", created_by, e))
- .ok()
- .flatten()
- .map(|p| p.username)
- } else {
- None
- };
+ let author_username = story_meta.and_then(|m| m.author_username.clone());
let time_ago = trailbase::time_ago(&story.published);
let user_voted = voted_ids.contains(&story.id);
diff --git a/src/trailbase.rs b/src/trailbase.rs
@@ -33,6 +33,34 @@ pub struct StoryMeta {
pub tags: Vec<String>,
}
+#[derive(Debug, Clone, Serialize, Deserialize)]
+pub struct UserMapping {
+ pub user_id: String,
+ pub username: String,
+}
+
+pub async fn enrich_comment_authors(
+ http_client: &reqwest::Client,
+ trailbase_url: &str,
+ user_ids: &[&str],
+) -> Result<Vec<UserMapping>, ClientError> {
+ if user_ids.is_empty() {
+ return Ok(vec![]);
+ }
+ let ids_str = user_ids.join(",");
+ let base = trailbase_url.trim_end_matches('/');
+ let url = format!("{}/enrich_comments?user_ids={}", base, ids_str);
+ let response = http_client
+ .get(&url)
+ .send()
+ .await
+ .map_err(|e| ClientError::Auth(format!("Enrich comments request failed: {}", e)))?;
+ response
+ .json()
+ .await
+ .map_err(|e| ClientError::Auth(format!("Enrich comments parse failed: {}", e)))
+}
+
pub async fn enrich_stories(
http_client: &reqwest::Client,
trailbase_url: &str,
@@ -258,36 +286,40 @@ pub async fn create_story(client: &Client, story: CreateStory) -> Result<Story,
/// Get stories by tag/category
pub async fn get_stories_by_tag(
+ http_client: &reqwest::Client,
+ trailbase_url: &str,
client: &Client,
category_id: i64,
limit: usize,
offset: usize,
) -> Result<Vec<Story>, ClientError> {
- // First get story IDs for this category
- let junction_args = ListArguments::new()
- .with_filters(Filter::new("category_id", CompareOp::Equal, category_id.to_string()));
-
- let junction: trailbase_client::ListResponse<serde_json::Value> =
- client.records("story_category").list(junction_args).await?;
-
- let story_ids: Vec<i64> = junction
- .records
- .iter()
- .filter_map(|v| v.get("news_item_id").and_then(|id| id.as_i64()))
- .collect();
+ // Use WASM endpoint for paginated JOIN query
+ let base = trailbase_url.trim_end_matches('/');
+ let url = format!(
+ "{}/stories_by_tag?category_id={}&limit={}&offset={}",
+ base, category_id, limit, offset
+ );
+ let story_ids: Vec<i64> = http_client
+ .get(&url)
+ .send()
+ .await
+ .map_err(|e| ClientError::Auth(format!("stories_by_tag request failed: {}", e)))?
+ .json()
+ .await
+ .map_err(|e| ClientError::Auth(format!("stories_by_tag parse failed: {}", e)))?;
if story_ids.is_empty() {
return Ok(vec![]);
}
- // Build filters for stories (OR of all IDs)
+ // Fetch full story records by ID
let filters: Vec<Filter> = story_ids
.iter()
.map(|id| Filter::new("id", CompareOp::Equal, id.to_string()))
.collect();
let args = ListArguments::new()
- .with_pagination(Pagination::new().with_limit(limit).with_offset(offset))
+ .with_pagination(Pagination::new().with_limit(limit))
.with_order(&["-published"])
.with_filters(filters);
diff --git a/traildepot/wasm/search_guest.wasm b/traildepot/wasm/search_guest.wasm
Binary files differ.
