MUL-2526 feat: add member(user_id, workspace_id) index + upgrade sqlc to v1.31.1

- Add migration 106: CREATE INDEX CONCURRENTLY on member(user_id, workspace_id)
- Rewrite ListWorkspaces to drive from member table with explicit fields
- Regenerate all sqlc code with v1.31.1 (intentional version upgrade)

Co-authored-by: multica-agent <github@multica.ai>

server/migrations/106_member_user_workspace_index.down.sql

@@ -0,0 +1 @@
+DROP INDEX CONCURRENTLY IF EXISTS idx_member_user_workspace;

server/migrations/106_member_user_workspace_index.up.sql

@@ -0,0 +1 @@
+CREATE INDEX CONCURRENTLY IF NOT EXISTS idx_member_user_workspace ON member(user_id, workspace_id);

server/pkg/db/generated/activity.sql.go

@@ -1,6 +1,6 @@
 // Code generated by sqlc. DO NOT EDIT.
 // versions:
-//   sqlc v1.30.0
+//   sqlc v1.31.1
 // source: activity.sql
 
 package db

server/pkg/db/generated/agent.sql.go

@@ -1,6 +1,6 @@
 // Code generated by sqlc. DO NOT EDIT.
 // versions:
-//   sqlc v1.30.0
+//   sqlc v1.31.1
 // source: agent.sql
 
 package db
@@ -517,65 +517,6 @@ func (q *Queries) ClaimAgentTask(ctx context.Context, agentID pgtype.UUID) (Agen
 	return i, err
 }
 
-const reclaimStaleDispatchedTaskForRuntime = `-- name: ReclaimStaleDispatchedTaskForRuntime :one
-UPDATE agent_task_queue
-SET dispatched_at = now()
-WHERE id = (
-    SELECT atq.id FROM agent_task_queue atq
-    WHERE atq.runtime_id = $1
-      AND atq.status = 'dispatched'
-      AND atq.started_at IS NULL
-      AND atq.dispatched_at < now() - make_interval(secs => $2::double precision)
-    ORDER BY atq.priority DESC, atq.dispatched_at ASC
-    LIMIT 1
-    FOR UPDATE SKIP LOCKED
-)
-RETURNING id, agent_id, issue_id, status, priority, dispatched_at, started_at, completed_at, result, error, created_at, context, runtime_id, session_id, work_dir, trigger_comment_id, chat_session_id, autopilot_run_id, attempt, max_attempts, parent_task_id, failure_reason, trigger_summary, force_fresh_session, is_leader_task
-`
-
-type ReclaimStaleDispatchedTaskForRuntimeParams struct {
-	RuntimeID         pgtype.UUID `json:"runtime_id"`
-	ClaimRecoverySecs float64     `json:"claim_recovery_secs"`
-}
-
-// Re-delivers a task whose previous claim likely succeeded server-side but
-// whose response never reached the daemon. The task is still in `dispatched`
-// with no `started_at`, so the daemon has not acknowledged it via StartTask.
-// Refresh dispatched_at so the server-side dispatch timeout measures from the
-// recovered delivery attempt.
-func (q *Queries) ReclaimStaleDispatchedTaskForRuntime(ctx context.Context, arg ReclaimStaleDispatchedTaskForRuntimeParams) (AgentTaskQueue, error) {
-	row := q.db.QueryRow(ctx, reclaimStaleDispatchedTaskForRuntime, arg.RuntimeID, arg.ClaimRecoverySecs)
-	var i AgentTaskQueue
-	err := row.Scan(
-		&i.ID,
-		&i.AgentID,
-		&i.IssueID,
-		&i.Status,
-		&i.Priority,
-		&i.DispatchedAt,
-		&i.StartedAt,
-		&i.CompletedAt,
-		&i.Result,
-		&i.Error,
-		&i.CreatedAt,
-		&i.Context,
-		&i.RuntimeID,
-		&i.SessionID,
-		&i.WorkDir,
-		&i.TriggerCommentID,
-		&i.ChatSessionID,
-		&i.AutopilotRunID,
-		&i.Attempt,
-		&i.MaxAttempts,
-		&i.ParentTaskID,
-		&i.FailureReason,
-		&i.TriggerSummary,
-		&i.ForceFreshSession,
-		&i.IsLeaderTask,
-	)
-	return i, err
-}
-
 const clearAgentMcpConfig = `-- name: ClearAgentMcpConfig :one
 UPDATE agent SET mcp_config = NULL, updated_at = now()
 WHERE id = $1
@@ -2027,6 +1968,65 @@ func (q *Queries) ListWorkspaceAgentTaskSnapshot(ctx context.Context, workspaceI
 	return items, nil
 }
 
+const reclaimStaleDispatchedTaskForRuntime = `-- name: ReclaimStaleDispatchedTaskForRuntime :one
+UPDATE agent_task_queue
+SET dispatched_at = now()
+WHERE id = (
+    SELECT atq.id FROM agent_task_queue atq
+    WHERE atq.runtime_id = $1
+      AND atq.status = 'dispatched'
+      AND atq.started_at IS NULL
+      AND atq.dispatched_at < now() - make_interval(secs => $2::double precision)
+    ORDER BY atq.priority DESC, atq.dispatched_at ASC
+    LIMIT 1
+    FOR UPDATE SKIP LOCKED
+)
+RETURNING id, agent_id, issue_id, status, priority, dispatched_at, started_at, completed_at, result, error, created_at, context, runtime_id, session_id, work_dir, trigger_comment_id, chat_session_id, autopilot_run_id, attempt, max_attempts, parent_task_id, failure_reason, trigger_summary, force_fresh_session, is_leader_task
+`
+
+type ReclaimStaleDispatchedTaskForRuntimeParams struct {
+	RuntimeID         pgtype.UUID `json:"runtime_id"`
+	ClaimRecoverySecs float64     `json:"claim_recovery_secs"`
+}
+
+// Re-delivers a task whose previous claim likely succeeded server-side but
+// whose response never reached the daemon. The task is still in `dispatched`
+// with no `started_at`, so the daemon has not acknowledged it via StartTask.
+// Refresh dispatched_at so the server-side dispatch timeout measures from the
+// recovered delivery attempt.
+func (q *Queries) ReclaimStaleDispatchedTaskForRuntime(ctx context.Context, arg ReclaimStaleDispatchedTaskForRuntimeParams) (AgentTaskQueue, error) {
+	row := q.db.QueryRow(ctx, reclaimStaleDispatchedTaskForRuntime, arg.RuntimeID, arg.ClaimRecoverySecs)
+	var i AgentTaskQueue
+	err := row.Scan(
+		&i.ID,
+		&i.AgentID,
+		&i.IssueID,
+		&i.Status,
+		&i.Priority,
+		&i.DispatchedAt,
+		&i.StartedAt,
+		&i.CompletedAt,
+		&i.Result,
+		&i.Error,
+		&i.CreatedAt,
+		&i.Context,
+		&i.RuntimeID,
+		&i.SessionID,
+		&i.WorkDir,
+		&i.TriggerCommentID,
+		&i.ChatSessionID,
+		&i.AutopilotRunID,
+		&i.Attempt,
+		&i.MaxAttempts,
+		&i.ParentTaskID,
+		&i.FailureReason,
+		&i.TriggerSummary,
+		&i.ForceFreshSession,
+		&i.IsLeaderTask,
+	)
+	return i, err
+}
+
 const recoverOrphanedTasksForRuntime = `-- name: RecoverOrphanedTasksForRuntime :many
 UPDATE agent_task_queue
 SET status = 'failed',

server/pkg/db/generated/attachment.sql.go

@@ -1,6 +1,6 @@
 // Code generated by sqlc. DO NOT EDIT.
 // versions:
-//   sqlc v1.30.0
+//   sqlc v1.31.1
 // source: attachment.sql
 
 package db

server/pkg/db/generated/autopilot.sql.go

@@ -1,6 +1,6 @@
 // Code generated by sqlc. DO NOT EDIT.
 // versions:
-//   sqlc v1.30.0
+//   sqlc v1.31.1
 // source: autopilot.sql
 
 package db

server/pkg/db/generated/chat.sql.go

@@ -1,6 +1,6 @@
 // Code generated by sqlc. DO NOT EDIT.
 // versions:
-//   sqlc v1.30.0
+//   sqlc v1.31.1
 // source: chat.sql
 
 package db
@@ -514,8 +514,9 @@ FOR UPDATE
 // their FK check after we commit the delete.
 func (q *Queries) LockChatSessionForDelete(ctx context.Context, id pgtype.UUID) (pgtype.UUID, error) {
 	row := q.db.QueryRow(ctx, lockChatSessionForDelete, id)
-	err := row.Scan(&id)
-	return id, err
+	var id_2 pgtype.UUID
+	err := row.Scan(&id_2)
+	return id_2, err
 }
 
 const markChatSessionRead = `-- name: MarkChatSessionRead :exec

server/pkg/db/generated/comment.sql.go

@@ -1,6 +1,6 @@
 // Code generated by sqlc. DO NOT EDIT.
 // versions:
-//   sqlc v1.30.0
+//   sqlc v1.31.1
 // source: comment.sql
 
 package db

server/pkg/db/generated/daemon_token.sql.go

@@ -1,6 +1,6 @@
 // Code generated by sqlc. DO NOT EDIT.
 // versions:
-//   sqlc v1.30.0
+//   sqlc v1.31.1
 // source: daemon_token.sql
 
 package db

server/pkg/db/generated/db.go

@@ -1,6 +1,6 @@
 // Code generated by sqlc. DO NOT EDIT.
 // versions:
-//   sqlc v1.30.0
+//   sqlc v1.31.1
 
 package db
 

server/pkg/db/generated/feedback.sql.go

@@ -1,6 +1,6 @@
 // Code generated by sqlc. DO NOT EDIT.
 // versions:
-//   sqlc v1.30.0
+//   sqlc v1.31.1
 // source: feedback.sql
 
 package db

server/pkg/db/generated/github.sql.go

@@ -1,6 +1,6 @@
 // Code generated by sqlc. DO NOT EDIT.
 // versions:
-//   sqlc v1.30.0
+//   sqlc v1.31.1
 // source: github.sql
 
 package db

server/pkg/db/generated/inbox.sql.go

@@ -1,6 +1,6 @@
 // Code generated by sqlc. DO NOT EDIT.
 // versions:
-//   sqlc v1.30.0
+//   sqlc v1.31.1
 // source: inbox.sql
 
 package db

server/pkg/db/generated/invitation.sql.go

@@ -1,6 +1,6 @@
 // Code generated by sqlc. DO NOT EDIT.
 // versions:
-//   sqlc v1.30.0
+//   sqlc v1.31.1
 // source: invitation.sql
 
 package db

server/pkg/db/generated/issue.sql.go

@@ -1,6 +1,6 @@
 // Code generated by sqlc. DO NOT EDIT.
 // versions:
-//   sqlc v1.30.0
+//   sqlc v1.31.1
 // source: issue.sql
 
 package db

server/pkg/db/generated/issue_label.sql.go

@@ -1,6 +1,6 @@
 // Code generated by sqlc. DO NOT EDIT.
 // versions:
-//   sqlc v1.30.0
+//   sqlc v1.31.1
 // source: issue_label.sql
 
 package db

server/pkg/db/generated/issue_reaction.sql.go

@@ -1,6 +1,6 @@
 // Code generated by sqlc. DO NOT EDIT.
 // versions:
-//   sqlc v1.30.0
+//   sqlc v1.31.1
 // source: issue_reaction.sql
 
 package db

server/pkg/db/generated/member.sql.go

@@ -1,6 +1,6 @@
 // Code generated by sqlc. DO NOT EDIT.
 // versions:
-//   sqlc v1.30.0
+//   sqlc v1.31.1
 // source: member.sql
 
 package db

server/pkg/db/generated/models.go

@@ -1,6 +1,6 @@
 // Code generated by sqlc. DO NOT EDIT.
 // versions:
-//   sqlc v1.30.0
+//   sqlc v1.31.1
 
 package db
 

server/pkg/db/generated/notification_preference.sql.go

@@ -1,6 +1,6 @@
 // Code generated by sqlc. DO NOT EDIT.
 // versions:
-//   sqlc v1.30.0
+//   sqlc v1.31.1
 // source: notification_preference.sql
 
 package db

server/pkg/db/generated/personal_access_token.sql.go

@@ -1,6 +1,6 @@
 // Code generated by sqlc. DO NOT EDIT.
 // versions:
-//   sqlc v1.30.0
+//   sqlc v1.31.1
 // source: personal_access_token.sql
 
 package db

server/pkg/db/generated/pinned_item.sql.go

@@ -1,6 +1,6 @@
 // Code generated by sqlc. DO NOT EDIT.
 // versions:
-//   sqlc v1.30.0
+//   sqlc v1.31.1
 // source: pinned_item.sql
 
 package db

server/pkg/db/generated/project.sql.go

@@ -1,6 +1,6 @@
 // Code generated by sqlc. DO NOT EDIT.
 // versions:
-//   sqlc v1.30.0
+//   sqlc v1.31.1
 // source: project.sql
 
 package db

server/pkg/db/generated/project_resource.sql.go

@@ -1,6 +1,6 @@
 // Code generated by sqlc. DO NOT EDIT.
 // versions:
-//   sqlc v1.30.0
+//   sqlc v1.31.1
 // source: project_resource.sql
 
 package db

server/pkg/db/generated/reaction.sql.go

@@ -1,6 +1,6 @@
 // Code generated by sqlc. DO NOT EDIT.
 // versions:
-//   sqlc v1.30.0
+//   sqlc v1.31.1
 // source: reaction.sql
 
 package db

server/pkg/db/generated/runtime.sql.go

@@ -1,6 +1,6 @@
 // Code generated by sqlc. DO NOT EDIT.
 // versions:
-//   sqlc v1.30.0
+//   sqlc v1.31.1
 // source: runtime.sql
 
 package db

server/pkg/db/generated/runtime_usage.sql.go

@@ -1,6 +1,6 @@
 // Code generated by sqlc. DO NOT EDIT.
 // versions:
-//   sqlc v1.30.0
+//   sqlc v1.31.1
 // source: runtime_usage.sql
 
 package db

server/pkg/db/generated/skill.sql.go

@@ -1,6 +1,6 @@
 // Code generated by sqlc. DO NOT EDIT.
 // versions:
-//   sqlc v1.30.0
+//   sqlc v1.31.1
 // source: skill.sql
 
 package db

server/pkg/db/generated/squad.sql.go

@@ -1,6 +1,6 @@
 // Code generated by sqlc. DO NOT EDIT.
 // versions:
-//   sqlc v1.30.0
+//   sqlc v1.31.1
 // source: squad.sql
 
 package db

server/pkg/db/generated/subscriber.sql.go

@@ -1,6 +1,6 @@
 // Code generated by sqlc. DO NOT EDIT.
 // versions:
-//   sqlc v1.30.0
+//   sqlc v1.31.1
 // source: subscriber.sql
 
 package db

server/pkg/db/generated/task_message.sql.go

@@ -1,6 +1,6 @@
 // Code generated by sqlc. DO NOT EDIT.
 // versions:
-//   sqlc v1.30.0
+//   sqlc v1.31.1
 // source: task_message.sql
 
 package db

server/pkg/db/generated/task_usage.sql.go

@@ -1,6 +1,6 @@
 // Code generated by sqlc. DO NOT EDIT.
 // versions:
-//   sqlc v1.30.0
+//   sqlc v1.31.1
 // source: task_usage.sql
 
 package db

server/pkg/db/generated/user.sql.go

@@ -1,6 +1,6 @@
 // Code generated by sqlc. DO NOT EDIT.
 // versions:
-//   sqlc v1.30.0
+//   sqlc v1.31.1
 // source: user.sql
 
 package db

server/pkg/db/generated/verification_code.sql.go

@@ -1,6 +1,6 @@
 // Code generated by sqlc. DO NOT EDIT.
 // versions:
-//   sqlc v1.30.0
+//   sqlc v1.31.1
 // source: verification_code.sql
 
 package db

server/pkg/db/generated/webhook_delivery.sql.go

@@ -1,6 +1,6 @@
 // Code generated by sqlc. DO NOT EDIT.
 // versions:
-//   sqlc v1.30.0
+//   sqlc v1.31.1
 // source: webhook_delivery.sql
 
 package db

server/pkg/db/generated/workspace.sql.go

@@ -1,6 +1,6 @@
 // Code generated by sqlc. DO NOT EDIT.
 // versions:
-//   sqlc v1.30.0
+//   sqlc v1.31.1
 // source: workspace.sql
 
 package db
@@ -121,8 +121,11 @@ func (q *Queries) IncrementIssueCounter(ctx context.Context, id pgtype.UUID) (in
 }
 
 const listWorkspaces = `-- name: ListWorkspaces :many
-SELECT w.id, w.name, w.slug, w.description, w.settings, w.created_at, w.updated_at, w.context, w.repos, w.issue_prefix, w.issue_counter FROM workspace w
-JOIN member m ON m.workspace_id = w.id
+SELECT w.id, w.name, w.slug, w.description, w.settings,
+       w.created_at, w.updated_at, w.context, w.repos,
+       w.issue_prefix, w.issue_counter
+FROM member m
+JOIN workspace w ON w.id = m.workspace_id
 WHERE m.user_id = $1
 ORDER BY w.created_at ASC
 `

server/pkg/db/queries/workspace.sql

@@ -1,6 +1,9 @@
 -- name: ListWorkspaces :many
-SELECT w.* FROM workspace w
-JOIN member m ON m.workspace_id = w.id
+SELECT w.id, w.name, w.slug, w.description, w.settings,
+       w.created_at, w.updated_at, w.context, w.repos,
+       w.issue_prefix, w.issue_counter
+FROM member m
+JOIN workspace w ON w.id = m.workspace_id
 WHERE m.user_id = $1
 ORDER BY w.created_at ASC;