triggers: scope groups to the guild that created them

4 files changed, 64 insertions, 24 deletions

diff --git a/src/commands/autocomplete.rs b/src/commands/autocomplete.rs
index a909738..d05a48b 100644
--- a/src/commands/autocomplete.rs
+++ b/src/commands/autocomplete.rs
@@ -208,8 +208,11 @@ pub async fn any_file(ctx: Context<'_>, partial: &str) -> Vec<serenity::Autocomp
 }
 
 pub async fn trigger_name(ctx: Context<'_>, partial: &str) -> Vec<String> {
+    let Some(guild_id) = ctx.guild_id() else {
+        return Vec::new();
+    };
     let db_pool = &ctx.data().db_pool;
-    match crate::database::list_triggers(db_pool).await {
+    match crate::database::list_triggers(db_pool, guild_id.get() as i64).await {
         Ok(triggers) => {
             let names: Vec<_> = triggers
                 .into_iter()
diff --git a/src/commands/trigger.rs b/src/commands/trigger.rs
index 3a91edd..5d481e4 100644
--- a/src/commands/trigger.rs
+++ b/src/commands/trigger.rs
@@ -27,7 +27,8 @@ pub async fn create(
     #[autocomplete = "trigger_name"]
     name: String,
 ) -> Result<()> {
-    match database::create_trigger(&ctx.data().db_pool, &name).await {
+    let guild_id = ctx.guild_id().ok_or_else(lang::err_not_in_guild)?;
+    match database::create_trigger(&ctx.data().db_pool, guild_id.get() as i64, &name).await {
         Ok(_) => {
             ctx.data().reload_triggers().await;
             ctx.say(lang::fmt_trigger_created(&name)).await?;
@@ -49,7 +50,8 @@ pub async fn add(
     #[description = "Keywords separated by \\"] trigger_keywords: Option<String>,
     #[description = "Responses separated by \\"] response: Option<String>,
 ) -> Result<()> {
-    let t = database::get_trigger_by_name(&ctx.data().db_pool, &name)
+    let guild_id = ctx.guild_id().ok_or_else(lang::err_not_in_guild)?;
+    let t = database::get_trigger_by_name(&ctx.data().db_pool, guild_id.get() as i64, &name)
         .await?
         .ok_or_else(lang::err_trigger_not_found)?;
 
@@ -90,7 +92,8 @@ pub async fn remove(
     #[description = "Keyword to remove"] trigger_keyword: Option<String>,
     #[description = "Response to remove"] response: Option<String>,
 ) -> Result<()> {
-    let t = database::get_trigger_by_name(&ctx.data().db_pool, &name)
+    let guild_id = ctx.guild_id().ok_or_else(lang::err_not_in_guild)?;
+    let t = database::get_trigger_by_name(&ctx.data().db_pool, guild_id.get() as i64, &name)
         .await?
         .ok_or_else(lang::err_trigger_not_found)?;
 
@@ -119,11 +122,12 @@ pub async fn delete(
     #[autocomplete = "trigger_name"]
     name: String,
 ) -> Result<()> {
-    let t = database::get_trigger_by_name(&ctx.data().db_pool, &name)
+    let guild_id = ctx.guild_id().ok_or_else(lang::err_not_in_guild)?;
+    let t = database::get_trigger_by_name(&ctx.data().db_pool, guild_id.get() as i64, &name)
         .await?
         .ok_or_else(lang::err_trigger_not_found)?;
 
-    database::delete_trigger(&ctx.data().db_pool, t.id).await?;
+    database::delete_trigger(&ctx.data().db_pool, t.id, guild_id.get() as i64).await?;
     ctx.data().reload_triggers().await;
     ctx.say(lang::fmt_trigger_deleted(&name)).await?;
     Ok(())
@@ -137,7 +141,8 @@ pub async fn list(
     #[autocomplete = "trigger_name"]
     name: String,
 ) -> Result<()> {
-    let t = database::get_trigger_by_name(&ctx.data().db_pool, &name)
+    let guild_id = ctx.guild_id().ok_or_else(lang::err_not_in_guild)?;
+    let t = database::get_trigger_by_name(&ctx.data().db_pool, guild_id.get() as i64, &name)
         .await?
         .ok_or_else(lang::err_trigger_not_found)?;
 
diff --git a/src/database/mod.rs b/src/database/mod.rs
index 9679811..e81e448 100644
--- a/src/database/mod.rs
+++ b/src/database/mod.rs
@@ -75,14 +75,24 @@ async fn run_migrations(pool: &DbPool) -> Result<()> {
     sqlx::query(
         "CREATE TABLE IF NOT EXISTS triggers (
             id INTEGER PRIMARY KEY AUTOINCREMENT,
-            name TEXT UNIQUE NOT NULL,
+            guild_id INTEGER NOT NULL DEFAULT 0,
+            name TEXT NOT NULL,
             created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
-            last_updated TIMESTAMP DEFAULT CURRENT_TIMESTAMP
+            last_updated TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
+            UNIQUE(guild_id, name)
         )",
     )
     .execute(pool)
     .await?;
 
+    // Add guild_id for pre-existing trigger tables. (The old table also kept a
+    // global UNIQUE(name), so existing databases still require unique trigger
+    // names across guilds; new databases use UNIQUE(guild_id, name).)
+    sqlx::query("ALTER TABLE triggers ADD COLUMN guild_id INTEGER NOT NULL DEFAULT 0")
+        .execute(pool)
+        .await
+        .ok();
+
     sqlx::query(
         "CREATE TABLE IF NOT EXISTS trigger_keywords (
             id INTEGER PRIMARY KEY AUTOINCREMENT,
@@ -416,6 +426,7 @@ pub async fn set_cron_job_active(
 #[derive(Debug, Clone, FromRow)]
 pub struct Trigger {
     pub id: i64,
+    pub guild_id: i64,
     pub name: String,
     pub created_at: Option<DateTime<Utc>>,
     pub last_updated: Option<DateTime<Utc>>,
@@ -437,33 +448,45 @@ pub struct TriggerResponse {
     pub response_text: String,
 }
 
-pub async fn create_trigger(pool: &DbPool, name: &str) -> Result<i64> {
-    let result = sqlx::query("INSERT INTO triggers (name) VALUES (?)")
+pub async fn create_trigger(pool: &DbPool, guild_id: i64, name: &str) -> Result<i64> {
+    let result = sqlx::query("INSERT INTO triggers (guild_id, name) VALUES (?, ?)")
+        .bind(guild_id)
         .bind(name)
         .execute(pool)
         .await?;
     Ok(result.last_insert_rowid())
 }
 
-pub async fn delete_trigger(pool: &DbPool, id: i64) -> Result<bool> {
-    let result = sqlx::query("DELETE FROM triggers WHERE id = ?")
+pub async fn delete_trigger(pool: &DbPool, id: i64, guild_id: i64) -> Result<bool> {
+    let result = sqlx::query("DELETE FROM triggers WHERE id = ? AND guild_id = ?")
         .bind(id)
+        .bind(guild_id)
         .execute(pool)
         .await?;
     Ok(result.rows_affected() > 0)
 }
 
-pub async fn get_trigger_by_name(pool: &DbPool, name: &str) -> Result<Option<Trigger>> {
-    Ok(sqlx::query_as("SELECT * FROM triggers WHERE name = ?")
-        .bind(name)
-        .fetch_optional(pool)
-        .await?)
+pub async fn get_trigger_by_name(
+    pool: &DbPool,
+    guild_id: i64,
+    name: &str,
+) -> Result<Option<Trigger>> {
+    Ok(
+        sqlx::query_as("SELECT * FROM triggers WHERE guild_id = ? AND name = ?")
+            .bind(guild_id)
+            .bind(name)
+            .fetch_optional(pool)
+            .await?,
+    )
 }
 
-pub async fn list_triggers(pool: &DbPool) -> Result<Vec<Trigger>> {
-    Ok(sqlx::query_as("SELECT * FROM triggers ORDER BY name")
-        .fetch_all(pool)
-        .await?)
+pub async fn list_triggers(pool: &DbPool, guild_id: i64) -> Result<Vec<Trigger>> {
+    Ok(
+        sqlx::query_as("SELECT * FROM triggers WHERE guild_id = ? ORDER BY name")
+            .bind(guild_id)
+            .fetch_all(pool)
+            .await?,
+    )
 }
 
 pub async fn add_keyword(pool: &DbPool, trigger_id: i64, keyword: &str) -> Result<()> {
@@ -526,7 +549,11 @@ pub async fn get_responses(pool: &DbPool, trigger_id: i64) -> Result<Vec<String>
 pub async fn load_all_triggers_with_keywords_and_responses(
     pool: &DbPool,
 ) -> Result<Vec<(Trigger, Vec<String>, Vec<String>)>> {
-    let triggers = list_triggers(pool).await?;
+    // The event cache needs every guild's triggers; `/trigger list` and
+    // mutations use the guild-scoped helpers above.
+    let triggers: Vec<Trigger> = sqlx::query_as("SELECT * FROM triggers ORDER BY name")
+        .fetch_all(pool)
+        .await?;
     let mut result = Vec::new();
     for t in triggers {
         let keywords = get_keywords(pool, t.id).await?;
diff --git a/src/events/mod.rs b/src/events/mod.rs
index 5006444..5fb6a30 100644
--- a/src/events/mod.rs
+++ b/src/events/mod.rs
@@ -380,10 +380,15 @@ pub async fn handle_message(
         }
 
     let content_lower = new_message.content.to_lowercase();
+    let message_guild_id = new_message.guild_id.map(|guild| guild.get() as i64);
     let response_to_send = {
         let triggers = data.triggers.lock().await;
         let mut chosen = None;
-        for (_trigger, keywords, responses) in triggers.iter() {
+        for (trigger, keywords, responses) in triggers.iter() {
+            // Triggers belong to one guild and never fire in DMs.
+            if message_guild_id != Some(trigger.guild_id) {
+                continue;
+            }
             if keywords.iter().any(|k| content_lower.contains(k)) {
                 chosen = responses.choose(&mut rand::rng()).cloned();
                 break;