simple-web-app

commit 4f6a4d78d497583e07040969a4e3b1cf47b92539
parent ae9fb1278f141a962d420dbfff1f37452ff77d52
Author: Silas Brack <silasbrack@gmail.com>
Date:   Sun, 14 Jun 2026 14:35:04 +0200

perf: read vote score on writer connection, remove separate read round-trip

toggle_vote now returns (voted, new_score) by reading the score
on the writer connection after the INSERT/DELETE. The trigger
updates the score on the same connection, so it's immediately
visible without a separate read pool round-trip.

Removes get_story_score and get_comment_score methods.

Co-Authored-By: Claude Opus 4.6 (1M context) <noreply@anthropic.com>

Diffstat:
M
src/database.rs
 | 
40
++++++++++++++++++----------------------
M
src/handlers/vote.rs
 | 
8
++------

2 files changed, 20 insertions(+), 28 deletions(-)
diff --git a/src/database.rs b/src/database.rs
@@ -646,29 +646,38 @@ impl Database {
     // Vote queries
     // ======================================================================
 
-    /// Toggle vote: INSERT if not exists, DELETE if exists. Returns true if now voted.
-    pub async fn toggle_vote(&self, user_id: i64, target_type: i64, target_id: i64) -> Result<bool, AppError> {
+    /// Toggle vote and return (voted, new_score). Reads the score on the
+    /// writer connection so it sees the trigger update without a separate round-trip.
+    pub async fn toggle_vote(&self, user_id: i64, target_type: i64, target_id: i64) -> Result<(bool, i64), AppError> {
         self.with_write(move |conn| {
-            // Try to insert
             let result = conn.execute(
                 "INSERT INTO vote (user_id, target_type, target_id, created_at) VALUES (?1, ?2, ?3, ?4)",
                 params![user_id, target_type, target_id, now_epoch()],
             );
 
-            match result {
-                Ok(_) => Ok(true),
+            let voted = match result {
+                Ok(_) => true,
                 Err(rusqlite::Error::SqliteFailure(err, _))
                     if err.code == rusqlite::ffi::ErrorCode::ConstraintViolation =>
                 {
-                    // Already voted, remove it
                     conn.execute(
                         "DELETE FROM vote WHERE user_id = ?1 AND target_type = ?2 AND target_id = ?3",
                         params![user_id, target_type, target_id],
                     ).map_err(|e| AppError::Database(e.to_string()))?;
-                    Ok(false)
+                    false
                 }
-                Err(e) => Err(AppError::Database(e.to_string())),
-            }
+                Err(e) => return Err(AppError::Database(e.to_string())),
+            };
+
+            // Read score on the writer — sees the trigger update immediately
+            let table = if target_type == TARGET_TYPE_STORY { "story" } else { "comment" };
+            let score: i64 = conn.query_row(
+                &format!("SELECT score FROM {} WHERE id = ?1", table),
+                params![target_id],
+                |row| row.get(0),
+            ).map_err(|e| AppError::Database(e.to_string()))?;
+
+            Ok((voted, score))
         }).await
     }
 
@@ -1031,19 +1040,6 @@ impl Database {
         }).await
     }
 
-    pub async fn get_story_score(&self, id: i64) -> Result<i64, AppError> {
-        self.with_read(move |conn| {
-            conn.query_row("SELECT score FROM story WHERE id = ?1", params![id], |row| row.get(0))
-                .map_err(|e| AppError::Database(e.to_string()))
-        }).await
-    }
-
-    pub async fn get_comment_score(&self, id: i64) -> Result<i64, AppError> {
-        self.with_read(move |conn| {
-            conn.query_row("SELECT score FROM comment WHERE id = ?1", params![id], |row| row.get(0))
-                .map_err(|e| AppError::Database(e.to_string()))
-        }).await
-    }
 }
 
 // ==========================================================================
diff --git a/src/handlers/vote.rs b/src/handlers/vote.rs
@@ -37,9 +37,7 @@ pub async fn vote_story(
     let user_id = state.get_user_id(&jar).await
         .ok_or_else(|| AppError::Unauthorized("Login required to vote".to_string()))?;
 
-    let db = state.db();
-    let voted = db.toggle_vote(user_id, TARGET_TYPE_STORY, story_id).await?;
-    let new_score = db.get_story_score(story_id).await?;
+    let (voted, new_score) = state.db().toggle_vote(user_id, TARGET_TYPE_STORY, story_id).await?;
 
     render_vote_button("story", story_id, new_score, voted)
 }
@@ -53,9 +51,7 @@ pub async fn vote_comment(
     let user_id = state.get_user_id(&jar).await
         .ok_or_else(|| AppError::Unauthorized("Login required to vote".to_string()))?;
 
-    let db = state.db();
-    let voted = db.toggle_vote(user_id, TARGET_TYPE_COMMENT, comment_id).await?;
-    let new_score = db.get_comment_score(comment_id).await?;
+    let (voted, new_score) = state.db().toggle_vote(user_id, TARGET_TYPE_COMMENT, comment_id).await?;
 
     render_vote_button("comment", comment_id, new_score, voted)
 }