From ceb96326ccf3daff0472cafdaba3e223d3a20724 Mon Sep 17 00:00:00 2001
From: salmonik <salmonik@tuta.io>
Date: Wed, 4 Nov 2026 01:09:10 +0100
Subject: triggers: scope groups to the guild that created them

---
 src/database/mod.rs | 59 ++++++++++++++++++++++++++++++++++++++---------------
 1 file changed, 43 insertions(+), 16 deletions(-)

(limited to 'src/database/mod.rs')

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?;
-- 
cgit v1.2.3