feat: add user_secrets table

coder · evgeniy-scherbina · Aug 7, 2025 · Aug 4, 2025 · Aug 4, 2025 · Aug 5, 2025
commit 104bf2124ce121e176cf7d32156eca7063354d7d
diff --git a/coderd/database/dump.sql b/coderd/database/dump.sql
diff --git a/coderd/database/foreign_key_constraint.go b/coderd/database/foreign_key_constraint.go
diff --git a/coderd/database/migrations/000356_add_user_secrets.down.sql b/coderd/database/migrations/000356_add_user_secrets.down.sql
@@ -0,0 +1,7 @@
+-- Drop the unique indexes first (in reverse order of creation)
+DROP INDEX IF EXISTS user_secrets_user_file_path_idx;
+DROP INDEX IF EXISTS user_secrets_user_env_name_idx;
+DROP INDEX IF EXISTS user_secrets_user_name_idx;
+
+-- Drop the table
+DROP TABLE IF EXISTS user_secrets;
diff --git a/coderd/database/migrations/000356_add_user_secrets.up.sql b/coderd/database/migrations/000356_add_user_secrets.up.sql
@@ -0,0 +1,34 @@
+-- Stores encrypted user secrets (global, available across all organizations)
+CREATE TABLE user_secrets (
+    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
+    user_id UUID NOT NULL REFERENCES users(id) ON DELETE CASCADE,
+    name TEXT NOT NULL,
+    description TEXT NOT NULL,
+
+    -- The encrypted secret value (base64-encoded encrypted data)
+    value TEXT NOT NULL,
+
+    -- Auto-injection settings
+    -- Environment variable name (e.g., "DATABASE_PASSWORD", "API_KEY")
+    -- Empty string means don't inject as env var
+    env_name TEXT NOT NULL DEFAULT '',
+
+    -- File path where secret should be written (e.g., "/home/coder/.ssh/id_rsa")
+    -- Empty string means don't inject as file
+    file_path TEXT NOT NULL DEFAULT '',
+
+    -- Timestamps
+    created_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP NOT NULL,
+    updated_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP NOT NULL
+);
+
+-- Unique constraint: user can't have duplicate secret names
+CREATE UNIQUE INDEX user_secrets_user_name_idx ON user_secrets(user_id, name);
+
+-- Unique constraint: user can't have duplicate env names
+CREATE UNIQUE INDEX user_secrets_user_env_name_idx ON user_secrets(user_id, env_name)
+WHERE env_name != '';
+
+-- Unique constraint: user can't have duplicate file paths
+CREATE UNIQUE INDEX user_secrets_user_file_path_idx ON user_secrets(user_id, file_path)
+WHERE file_path != '';
diff --git a/coderd/database/models.go b/coderd/database/models.go
diff --git a/coderd/database/unique_constraint.go b/coderd/database/unique_constraint.go