refactor(db): port runtime ops to sqlc

Tranche 5 — all 24 raw sites in runtime.go. Adds runtime.sql with the
legacy-column COALESCE loader (type/image/command/git_repo fall back to
service_type/image_name/start_command/source_url), env-var reads,
sibling refs, deployed-image concat, and conditional published-port /
replica writes. Status transitions reuse SetServiceStatus.

loadReadableService now shares GetServiceRuntimeWithOwner — the projects
join is safe (FK) and drops a duplicated 24-column select.

TestRuntimeLiveInvariants verifies the COALESCE chain against a service
seeded with only legacy columns, plus refs, port, variables, agent
listing, image concat, and conditional updates on live Postgres.

Generated with [Devin](https://devin.ai)

Co-Authored-By: Devin <158243242+devin-ai-integration[bot]@users.noreply.github.com>

ROADMAP2.md

@@ -537,7 +537,7 @@ and wired refs, one click or one CLI call.
   workspace, CLI `environments` + MCP parity. Per-env vars/domains ride the
   service's own scope (one env per service); promote ships via preview
   promote.
-- [ ] **sqlc port** — ~300 raw `database/sql` sites → `sqlc/queries/`. Done in tranches (dynamic queries stay raw): metrics storage + service domains ported.
+- [ ] **sqlc port** — ~265 raw `database/sql` sites → `sqlc/queries/`. Done in tranches (dynamic queries stay raw). Tranches merged: metrics storage + service domains (#75), service clone/move ops (#76), preview environments (#77), deployments + rollback (#78). Remaining: runtime, compliance, cron, git, security, auth, sleeper, scanner, gateway, and smaller files.
 - [ ] **Tests** — canvas/wizard frontend coverage, deployment/template/
   webhook table tests, Playwright e2e golden path.
 - [x] **First-run wizard** — public `GET /setup/status` + authed

app/backend/internal/api/domains_test.go

@@ -444,3 +444,133 @@ func TestDeploymentsLiveInvariants(t *testing.T) {
 		t.Fatalf("post-fail row: %+v", r)
 	}
 }
+
+// TestRuntimeLiveInvariants exercises the runtime.sql queries: the
+// legacy-column COALESCE chain in GetServiceRuntimeWithOwner, the
+// deployed-image concat, and the conditional published-port update.
+func TestRuntimeLiveInvariants(t *testing.T) {
+	dsn := testDSNString(t)
+	db, err := database.NewConnection(dsn)
+	if err != nil {
+		t.Fatalf("connect: %v", err)
+	}
+	defer db.Close()
+	ctx := context.Background()
+	q := sqlcdb.New(db.DB)
+
+	userID := uuid.New()
+	projectID := uuid.New()
+	envID := uuid.New()
+	serviceID := uuid.New()
+	suffix := strings.ReplaceAll(serviceID.String()[:8], "-", "")
+	must := func(err error) {
+		t.Helper()
+		if err != nil {
+			t.Fatalf("setup: %v", err)
+		}
+	}
+	_, err = db.Exec(`INSERT INTO users (id, email, password_hash, name) VALUES ($1, $2, 'x', 'test')`,
+		userID, "rt-"+suffix+"@test.local")
+	must(err)
+	_, err = db.Exec(`INSERT INTO projects (id, name, owner_id, is_approved) VALUES ($1, $2, $3, true)`,
+		projectID, "rt-"+suffix, userID)
+	must(err)
+	_, err = db.Exec(`INSERT INTO environments (id, name, project_id) VALUES ($1, 'production', $2)`,
+		envID, projectID)
+	must(err)
+	// Only legacy columns populated — the COALESCE chain must surface them.
+	_, err = db.Exec(`INSERT INTO services (id, name, project_id, environment_id, service_type, source_type,
+		source_url, image_name, start_command, port) VALUES ($1, $2, $3, $4, 'worker', 'git', $5, $6, 'run.sh', 8080)`,
+		serviceID, "rt-"+suffix, projectID, envID, "https://git.example/x.git", "repo/img:latest")
+	must(err)
+	defer func() { _, _ = db.Exec(`DELETE FROM users WHERE id = $1`, userID) }()
+
+	row, err := q.GetServiceRuntimeWithOwner(ctx, serviceID)
+	if err != nil {
+		t.Fatalf("runtime row: %v", err)
+	}
+	if row.Type != "worker" || row.Image != "repo/img:latest" || row.Command != "run.sh" ||
+		row.GitRepo != "https://git.example/x.git" || row.OwnerID != userID {
+		t.Fatalf("COALESCE chain lost legacy columns: %+v", row)
+	}
+	svc := serviceFromRuntimeRow(row)
+	if svc.Type != "worker" || svc.Image != "repo/img:latest" || svc.Port != 8080 {
+		t.Fatalf("mapped service wrong: %+v", svc)
+	}
+
+	// Sibling refs + port lookup.
+	refs, err := q.ListProjectServiceRefs(ctx, projectID)
+	if err != nil || len(refs) != 1 || refs[0].Name != "rt-"+suffix {
+		t.Fatalf("refs: %v %+v", err, refs)
+	}
+	port, err := q.GetServicePort(ctx, serviceID)
+	if err != nil || port != 8080 {
+		t.Fatalf("port: %v %d", err, port)
+	}
+
+	// Variables round-trip.
+	_, err = db.Exec(`INSERT INTO environment_variables (id, service_id, key, value, is_secret)
+		VALUES (gen_random_uuid(), $1, 'API_KEY', 'shh', true), (gen_random_uuid(), $1, 'PLAIN', 'p', false)`,
+		serviceID)
+	must(err)
+	vars, err := q.ListServiceVariablesWithSecret(ctx, serviceID)
+	if err != nil || len(vars) != 2 {
+		t.Fatalf("vars: %v %+v", err, vars)
+	}
+	vals, err := q.ListServiceVariableValues(ctx, serviceID)
+	if err != nil || len(vals) != 2 {
+		t.Fatalf("vals: %v %+v", err, vals)
+	}
+	got, err := q.GetServiceVariableValue(ctx, sqlcdb.GetServiceVariableValueParams{ServiceID: serviceID, Key: "API_KEY"})
+	if err != nil || got != "shh" {
+		t.Fatalf("var value: %v %q", err, got)
+	}
+
+	// Node agent listing through container_instances.
+	_, err = db.Exec(`INSERT INTO node_agents (id, name, hostname, ip_address, port) VALUES ($1, $2, 'h', '127.0.0.1', 9999)`,
+		"agent-"+suffix, "agent-"+suffix)
+	must(err)
+	_, err = db.Exec(`INSERT INTO container_instances (id, name, image, project_id, service_id, node_agent_id)
+		VALUES ($1, 'c', 'img', $2, $3, $4)`,
+		"ci-"+suffix, projectID.String(), serviceID.String(), "agent-"+suffix)
+	must(err)
+	agents, err := q.ListNodeAgentsForService(ctx, serviceID.String())
+	if err != nil || len(agents) != 1 || agents[0] != "agent-"+suffix {
+		t.Fatalf("agents: %v %+v", err, agents)
+	}
+
+	// Last deployed image: only 'deployed' rows, latest first.
+	d1, d2 := uuid.New(), uuid.New()
+	must(q.InsertDeployment(ctx, sqlcdb.InsertDeploymentParams{
+		ID: d1, ServiceID: serviceID, Version: "v1",
+		Status: sql.NullString{String: "failed", Valid: true},
+	}))
+	must(q.InsertDeployment(ctx, sqlcdb.InsertDeploymentParams{
+		ID: d2, ServiceID: serviceID, Version: "v2",
+		Status: sql.NullString{String: "deployed", Valid: true},
+	}))
+	must(q.SyncDeploymentProgress(ctx, sqlcdb.SyncDeploymentProgressParams{
+		Status:    sql.NullString{String: "deployed", Valid: true},
+		ImageName: sql.NullString{String: "app", Valid: true},
+		ImageTag:  sql.NullString{String: "v2", Valid: true},
+		UpdatedAt: sql.NullTime{Time: time.Now(), Valid: true},
+		ID:        d2,
+	}))
+	img, err := q.GetLastDeployedImage(ctx, serviceID)
+	if err != nil || img != "app:v2" {
+		t.Fatalf("last image: %v %q", err, img)
+	}
+
+	// Conditional published-port update: writes once, no-ops on repeat.
+	must(q.SetServicePublishedPort(ctx, sqlcdb.SetServicePublishedPortParams{PublishedPort: 30001, ID: serviceID}))
+	p, err := q.GetServicePublishedPort(ctx, serviceID)
+	if err != nil || p != 30001 {
+		t.Fatalf("published port: %v %d", err, p)
+	}
+	must(q.SetServiceReplicas(ctx, sqlcdb.SetServiceReplicasParams{
+		Replicas: 3, UpdatedAt: sql.NullTime{Time: time.Now(), Valid: true}, ID: serviceID}))
+	row, _ = q.GetServiceRuntimeWithOwner(ctx, serviceID)
+	if row.Replicas != 3 {
+		t.Fatalf("replicas: %+v", row)
+	}
+}

app/backend/internal/api/runtime.go

@@ -2,6 +2,7 @@ package api
 
 import (
 	"context"
+	"database/sql"
 	"fmt"
 	"net/http"
 	"regexp"
@@ -10,6 +11,7 @@ import (
 	"time"
 
 	"containr/internal/database"
+	"containr/internal/database/sqlcdb"
 	"containr/internal/deployment"
 	"containr/internal/deployqueue"
 	"containr/internal/secrets"
@@ -22,22 +24,17 @@ import (
 // service — covers pinned placements, spread replicas, and leftovers from a
 // since-cleared pin.
 func remoteAgentsForService(ctx context.Context, db *database.DB, serviceID uuid.UUID) []string {
-	rows, err := db.Query(
-		`SELECT DISTINCT node_agent_id FROM container_instances WHERE service_id = $1`,
-		serviceID,
-	)
+	ids, err := sqlcdb.New(db.DB).ListNodeAgentsForService(ctx, serviceID.String())
 	if err != nil {
 		return nil
 	}
-	defer rows.Close()
-	var ids []string
-	for rows.Next() {
-		var id string
-		if rows.Scan(&id) == nil && id != "" {
-			ids = append(ids, id)
+	out := make([]string, 0, len(ids))
+	for _, id := range ids {
+		if id != "" {
+			out = append(out, id)
 		}
 	}
-	return ids
+	return out
 }
 
 // serviceLifecycle routes a start/stop/restart action to every agent holding
@@ -99,7 +96,9 @@ func serviceRuntimeSpec(db *database.DB, service Service) (deployment.RuntimeSpe
 	// Best effort: reuse the host port from the last live deployment so the
 	// public URL survives restarts. Column exists post-migration; older DBs
 	// silently get ephemeral ports.
-	_ = db.QueryRow(`SELECT published_port FROM services WHERE id = $1`, service.ID).Scan(&spec.PublishedPort)
+	if p, err := sqlcdb.New(db.DB).GetServicePublishedPort(context.Background(), service.ID); err == nil {
+		spec.PublishedPort = p
+	}
 	if cmd := strings.TrimSpace(service.Command); cmd != "" {
 		spec.Command = strings.Fields(cmd)
 	}
@@ -137,42 +136,23 @@ var refVarPattern = regexp.MustCompile(`\$\{\{\s*([A-Za-z0-9_-]+)\.([A-Za-z_][A-
 // secrets) and expands ${{service.KEY}} references. KEY may be a variable on
 // the referenced service, or the builtins HOST (service name alias) and PORT.
 func resolveServiceEnv(db *database.DB, service Service) (map[string]string, error) {
-	rows, err := db.Query(
-		`SELECT key, value FROM environment_variables WHERE service_id = $1`,
-		service.ID,
-	)
+	q := sqlcdb.New(db.DB)
+	vars, err := q.ListServiceVariableValues(context.Background(), service.ID)
 	if err != nil {
 		return nil, err
 	}
-	defer rows.Close()
 
 	env := map[string]string{}
-	for rows.Next() {
-		var k, v string
-		if err := rows.Scan(&k, &v); err != nil {
-			return nil, err
-		}
-		env[k] = secrets.Decrypt(v)
+	for _, v := range vars {
+		env[v.Key] = secrets.Decrypt(v.Value)
 	}
 
-	var refServices []struct{ ID, Name string }
-	svcRows, err := db.Query(
-		`SELECT id, name FROM services WHERE project_id = $1`,
-		service.ProjectID,
-	)
+	refServices, err := q.ListProjectServiceRefs(context.Background(), service.ProjectID)
 	if err != nil {
 		return nil, err
 	}
-	defer svcRows.Close()
-	for svcRows.Next() {
-		var s struct{ ID, Name string }
-		if err := svcRows.Scan(&s.ID, &s.Name); err != nil {
-			return nil, err
-		}
-		refServices = append(refServices, s)
-	}
 
-	byName := map[string]string{}
+	byName := map[string]uuid.UUID{}
 	for _, s := range refServices {
 		byName[strings.ToLower(s.Name)] = s.ID
 	}
@@ -189,17 +169,15 @@ func resolveServiceEnv(db *database.DB, service Service) (map[string]string, err
 		case "HOST":
 			return serviceName, true
 		case "PORT":
-			var port int
-			if err := db.QueryRow(`SELECT COALESCE(port, 0) FROM services WHERE id = $1`, id).Scan(&port); err == nil && port > 0 {
-				return strconv.Itoa(port), true
+			if port, err := q.GetServicePort(context.Background(), id); err == nil && port > 0 {
+				return strconv.Itoa(int(port)), true
 			}
 			return "", false
 		}
-		var value string
-		err := db.QueryRow(
-			`SELECT value FROM environment_variables WHERE service_id = $1 AND key = $2`,
-			id, key,
-		).Scan(&value)
+		value, err := q.GetServiceVariableValue(context.Background(), sqlcdb.GetServiceVariableValueParams{
+			ServiceID: id,
+			Key:       key,
+		})
 		if err != nil {
 			return "", false
 		}
@@ -272,6 +250,15 @@ func parseMemoryLimit(mem string) int64 {
 	return 0
 }
 
+// setServiceStatusNow writes the stored status with a fresh updated_at.
+func setServiceStatusNow(db *database.DB, id uuid.UUID, status string) {
+	_ = sqlcdb.New(db.DB).SetServiceStatus(context.Background(), sqlcdb.SetServiceStatusParams{
+		Status:    sql.NullString{String: status, Valid: true},
+		UpdatedAt: sql.NullTime{Time: time.Now(), Valid: true},
+		ID:        id,
+	})
+}
+
 func getRuntimeEngine(c *gin.Context) (*deployment.DeploymentEngine, *database.DB, bool) {
 	dbValue, exists := c.Get("db")
 	if !exists {
@@ -286,6 +273,22 @@ func getRuntimeEngine(c *gin.Context) (*deployment.DeploymentEngine, *database.D
 	return engineValue.(*deployment.DeploymentEngine), dbValue.(*database.DB), true
 }
 
+// serviceFromRuntimeRow maps the sqlc runtime row (with legacy-column
+// fallbacks applied in SQL) onto the API Service model.
+func serviceFromRuntimeRow(r sqlcdb.GetServiceRuntimeWithOwnerRow) Service {
+	return Service{
+		ID: r.ID, ProjectID: r.ProjectID, Name: r.Name, Type: r.Type,
+		Status: r.Status, Image: r.Image, Command: r.Command,
+		Environment: r.Environment, GitRepo: r.GitRepo, GitBranch: r.GitBranch,
+		BuildPath: r.BuildPath, CPU: r.Cpu, Memory: r.Memory,
+		Replicas: int(r.Replicas), Port: int(r.Port), Domain: r.Domain,
+		HealthCheckPath: r.HealthcheckPath, RestartPolicy: r.RestartPolicy,
+		Builder: r.Builder, CPUReserve: r.CpuReserve,
+		MemoryReserve: r.MemoryReserve, StaticBuildCmd: r.StaticBuildCmd,
+		StaticDir: r.StaticDir, CreatedAt: r.CreatedAt.Time, UpdatedAt: r.UpdatedAt.Time,
+	}
+}
+
 func loadOwnedService(c *gin.Context, db *database.DB) (Service, bool) {
 	serviceID, err := uuid.Parse(c.Param("id"))
 	if err != nil {
@@ -298,43 +301,16 @@ func loadOwnedService(c *gin.Context, db *database.DB) (Service, bool) {
 		return Service{}, false
 	}
 
-	var service Service
-	var owner string
-	err = db.QueryRow(
-		`SELECT s.id, s.project_id, s.name,
-		        COALESCE(s.type, s.service_type, ''), COALESCE(s.status, ''),
-		        COALESCE(s.image, s.image_name, ''), COALESCE(s.command, s.start_command, ''),
-		        COALESCE(s.environment, ''), COALESCE(s.git_repo, s.source_url, ''),
-		        COALESCE(s.git_branch, ''), COALESCE(s.build_path, ''),
-		        COALESCE(s.cpu, ''), COALESCE(s.memory, ''),
-		        COALESCE(s.replicas, 1), COALESCE(s.port, 0),
-		        COALESCE(s.domain, ''), COALESCE(s.healthcheck_path, ''),
-		        COALESCE(s.restart_policy, 'unless-stopped'),
-		        COALESCE(s.builder, 'auto'), COALESCE(s.cpu_reserve, ''),
-		        COALESCE(s.memory_reserve, ''), COALESCE(s.static_build_cmd, ''),
-		        COALESCE(s.static_dir, ''),
-		        s.created_at, s.updated_at, p.owner_id
-		 FROM services s JOIN projects p ON s.project_id = p.id
-		 WHERE s.id = $1`,
-		serviceID,
-	).Scan(
-		&service.ID, &service.ProjectID, &service.Name, &service.Type, &service.Status,
-		&service.Image, &service.Command, &service.Environment, &service.GitRepo,
-		&service.GitBranch, &service.BuildPath, &service.CPU, &service.Memory,
-		&service.Replicas, &service.Port, &service.Domain, &service.HealthCheckPath,
-		&service.RestartPolicy, &service.Builder, &service.CPUReserve,
-		&service.MemoryReserve, &service.StaticBuildCmd, &service.StaticDir,
-		&service.CreatedAt, &service.UpdatedAt, &owner,
-	)
+	row, err := sqlcdb.New(db.DB).GetServiceRuntimeWithOwner(context.Background(), serviceID)
 	if err != nil {
 		c.JSON(http.StatusNotFound, gin.H{"error": "Service not found"})
 		return Service{}, false
 	}
-	if owner != userID.(string) && !isAdminUser(db, userID.(string)) {
+	if row.OwnerID.String() != userID.(string) && !isAdminUser(db, userID.(string)) {
 		c.JSON(http.StatusForbidden, gin.H{"error": "Access denied"})
 		return Service{}, false
 	}
-	return service, true
+	return serviceFromRuntimeRow(row), true
 }
 
 // loadReadableService loads a service when the caller may read its project:
@@ -346,39 +322,13 @@ func loadReadableService(c *gin.Context, db *database.DB) (Service, bool) {
 		return Service{}, false
 	}
 
-	var service Service
-	var projectID uuid.UUID
-	err = db.QueryRow(
-		`SELECT s.id, s.project_id, s.name,
-		        COALESCE(s.type, s.service_type, ''), COALESCE(s.status, ''),
-		        COALESCE(s.image, s.image_name, ''), COALESCE(s.command, s.start_command, ''),
-		        COALESCE(s.environment, ''), COALESCE(s.git_repo, s.source_url, ''),
-		        COALESCE(s.git_branch, ''), COALESCE(s.build_path, ''),
-		        COALESCE(s.cpu, ''), COALESCE(s.memory, ''),
-		        COALESCE(s.replicas, 1), COALESCE(s.port, 0),
-		        COALESCE(s.domain, ''), COALESCE(s.healthcheck_path, ''),
-		        COALESCE(s.restart_policy, 'unless-stopped'),
-		        COALESCE(s.builder, 'auto'), COALESCE(s.cpu_reserve, ''),
-		        COALESCE(s.memory_reserve, ''), COALESCE(s.static_build_cmd, ''),
-		        COALESCE(s.static_dir, ''),
-		        s.created_at, s.updated_at, s.project_id
-		 FROM services s
-		 WHERE s.id = $1`,
-		serviceID,
-	).Scan(
-		&service.ID, &service.ProjectID, &service.Name, &service.Type, &service.Status,
-		&service.Image, &service.Command, &service.Environment, &service.GitRepo,
-		&service.GitBranch, &service.BuildPath, &service.CPU, &service.Memory,
-		&service.Replicas, &service.Port, &service.Domain, &service.HealthCheckPath,
-		&service.RestartPolicy, &service.Builder, &service.CPUReserve,
-		&service.MemoryReserve, &service.StaticBuildCmd, &service.StaticDir,
-		&service.CreatedAt, &service.UpdatedAt, &projectID,
-	)
+	row, err := sqlcdb.New(db.DB).GetServiceRuntimeWithOwner(context.Background(), serviceID)
 	if err != nil {
 		c.JSON(http.StatusNotFound, gin.H{"error": "Service not found"})
 		return Service{}, false
 	}
-	if _, allowed := projectReadAccess(c, db, projectID); !allowed {
+	service := serviceFromRuntimeRow(row)
+	if _, allowed := projectReadAccess(c, db, service.ProjectID); !allowed {
 		c.JSON(http.StatusNotFound, gin.H{"error": "Service not found"})
 		return Service{}, false
 	}
@@ -429,7 +379,7 @@ func reconcileNow(c *gin.Context, engine *deployment.DeploymentEngine, db *datab
 		status = state.Status
 		persistPublishedPort(db, service.ID, state.Ports)
 	}
-	_, _ = db.Exec(`UPDATE services SET status = $1, updated_at = $2 WHERE id = $3`, status, time.Now(), service.ID)
+	setServiceStatusNow(db, service.ID, status)
 	c.JSON(http.StatusOK, gin.H{"runtime": state})
 	return true
 }
@@ -448,7 +398,7 @@ func handleServiceStart(c *gin.Context) {
 		c.JSON(http.StatusBadGateway, gin.H{"error": err.Error()})
 		return
 	}
-	_, _ = db.Exec(`UPDATE services SET status = 'running', updated_at = $1 WHERE id = $2`, time.Now(), service.ID)
+	setServiceStatusNow(db, service.ID, "running")
 	c.JSON(http.StatusOK, gin.H{"status": "running"})
 }
 
@@ -466,7 +416,7 @@ func handleServiceStop(c *gin.Context) {
 		c.JSON(http.StatusBadGateway, gin.H{"error": err.Error()})
 		return
 	}
-	_, _ = db.Exec(`UPDATE services SET status = 'stopped', updated_at = $1 WHERE id = $2`, time.Now(), service.ID)
+	setServiceStatusNow(db, service.ID, "stopped")
 	c.JSON(http.StatusOK, gin.H{"status": "stopped"})
 }
 
@@ -485,7 +435,7 @@ func handleServiceRestart(c *gin.Context) {
 		c.JSON(http.StatusBadGateway, gin.H{"error": err.Error()})
 		return
 	}
-	_, _ = db.Exec(`UPDATE services SET status = 'running', updated_at = $1 WHERE id = $2`, time.Now(), service.ID)
+	setServiceStatusNow(db, service.ID, "running")
 	c.JSON(http.StatusOK, gin.H{"status": "running"})
 }
 
@@ -505,12 +455,8 @@ func handleServiceRedeploy(c *gin.Context) {
 	image := service.Image
 	if image == "" {
 		// Git-sourced service: reuse the last successful deployment image.
-		err := db.QueryRow(
-			`SELECT image_name || ':' || image_tag FROM deployments
-			 WHERE service_id = $1 AND status = 'deployed' AND image_name <> ''
-			 ORDER BY created_at DESC LIMIT 1`,
-			service.ID,
-		).Scan(&image)
+		var err error
+		image, err = sqlcdb.New(db.DB).GetLastDeployedImage(context.Background(), service.ID)
 		if err != nil {
 			c.JSON(http.StatusBadRequest, gin.H{"error": "No deployable image yet — run a deployment first"})
 			return
@@ -556,7 +502,7 @@ func reconcileServiceJob(ctx context.Context, db *database.DB, engine *deploymen
 		if state.Status != "" {
 			status = state.Status
 		}
-		_, _ = db.Exec(`UPDATE services SET status = $1, updated_at = $2 WHERE id = $3`, status, time.Now(), service.ID)
+		setServiceStatusNow(db, service.ID, status)
 	}
 	return state, nil
 }
@@ -575,12 +521,9 @@ func enqueueServiceRedeploy(c *gin.Context, db *database.DB, service Service) bo
 	}
 	image := service.Image
 	if image == "" {
-		if err := db.QueryRow(
-			`SELECT image_name || ':' || image_tag FROM deployments
-			 WHERE service_id = $1 AND status = 'deployed' AND image_name <> ''
-			 ORDER BY created_at DESC LIMIT 1`,
-			service.ID,
-		).Scan(&image); err != nil {
+		var err error
+		image, err = sqlcdb.New(db.DB).GetLastDeployedImage(context.Background(), service.ID)
+		if err != nil {
 			return false
 		}
 	}
@@ -628,10 +571,10 @@ func persistPublishedPort(db *database.DB, serviceID uuid.UUID, ports []uint16)
 	if len(ports) == 0 {
 		return
 	}
-	_, _ = db.Exec(
-		`UPDATE services SET published_port = $1 WHERE id = $2 AND published_port <> $1`,
-		int(ports[0]), serviceID,
-	)
+	_ = sqlcdb.New(db.DB).SetServicePublishedPort(context.Background(), sqlcdb.SetServicePublishedPortParams{
+		PublishedPort: int32(ports[0]),
+		ID:            serviceID,
+	})
 }
 
 // liveServiceStatus reconciles the stored status with real container state.
@@ -654,7 +597,7 @@ func liveServiceStatus(c *gin.Context, db *database.DB, service *Service) {
 	}
 	if state.Status != service.Status {
 		service.Status = state.Status
-		_, _ = db.Exec(`UPDATE services SET status = $1, updated_at = $2 WHERE id = $3`, state.Status, time.Now(), service.ID)
+		setServiceStatusNow(db, service.ID, state.Status)
 	}
 	persistPublishedPort(db, service.ID, state.Ports)
 	if service.Domain != "" {
@@ -676,44 +619,22 @@ func runtimeScaleService(c *gin.Context, serviceID string, replicas int) error {
 		return fmt.Errorf("runtime unavailable")
 	}
 
-	var service Service
-	var owner string
-	err := db.QueryRow(
-		`SELECT s.id, s.project_id, s.name,
-		        COALESCE(s.type, s.service_type, ''), COALESCE(s.status, ''),
-		        COALESCE(s.image, s.image_name, ''), COALESCE(s.command, s.start_command, ''),
-		        COALESCE(s.environment, ''), COALESCE(s.git_repo, s.source_url, ''),
-		        COALESCE(s.git_branch, ''), COALESCE(s.build_path, ''),
-		        COALESCE(s.cpu, ''), COALESCE(s.memory, ''),
-		        COALESCE(s.replicas, 1), COALESCE(s.port, 0),
-		        COALESCE(s.domain, ''), COALESCE(s.healthcheck_path, ''),
-		        COALESCE(s.restart_policy, 'unless-stopped'),
-		        COALESCE(s.builder, 'auto'), COALESCE(s.cpu_reserve, ''),
-		        COALESCE(s.memory_reserve, ''), COALESCE(s.static_build_cmd, ''),
-		        COALESCE(s.static_dir, ''),
-		        s.created_at, s.updated_at, p.owner_id
-		 FROM services s JOIN projects p ON s.project_id = p.id
-		 WHERE s.id = $1`, serviceID,
-	).Scan(
-		&service.ID, &service.ProjectID, &service.Name, &service.Type, &service.Status,
-		&service.Image, &service.Command, &service.Environment, &service.GitRepo,
-		&service.GitBranch, &service.BuildPath, &service.CPU, &service.Memory,
-		&service.Replicas, &service.Port, &service.Domain, &service.HealthCheckPath,
-		&service.RestartPolicy, &service.Builder, &service.CPUReserve,
-		&service.MemoryReserve, &service.StaticBuildCmd, &service.StaticDir,
-		&service.CreatedAt, &service.UpdatedAt, &owner,
-	)
+	q := sqlcdb.New(db.DB)
+	sid, err := uuid.Parse(serviceID)
 	if err != nil {
 		return err
 	}
+	row, err := q.GetServiceRuntimeWithOwner(context.Background(), sid)
+	if err != nil {
+		return err
+	}
+	service := serviceFromRuntimeRow(row)
 	if service.Image == "" {
-		if err := db.QueryRow(
-			`SELECT image_name || ':' || image_tag FROM deployments
-			 WHERE service_id = $1 AND status = 'deployed' AND image_name <> ''
-			 ORDER BY created_at DESC LIMIT 1`, service.ID,
-		).Scan(&service.Image); err != nil {
+		image, err := q.GetLastDeployedImage(context.Background(), service.ID)
+		if err != nil {
 			return fmt.Errorf("no deployed image to scale")
 		}
+		service.Image = image
 	}
 
 	spec, err := serviceRuntimeSpec(db, service)
@@ -731,7 +652,11 @@ func runtimeScaleService(c *gin.Context, serviceID string, replicas int) error {
 	if state != nil {
 		persistPublishedPort(db, service.ID, state.Ports)
 	}
-	_, _ = db.Exec(`UPDATE services SET replicas = $1, updated_at = $2 WHERE id = $3`, replicas, time.Now(), service.ID)
+	_ = q.SetServiceReplicas(context.Background(), sqlcdb.SetServiceReplicasParams{
+		Replicas:  int32(replicas),
+		UpdatedAt: sql.NullTime{Time: time.Now(), Valid: true},
+		ID:        service.ID,
+	})
 	return nil
 }
 
@@ -750,48 +675,35 @@ func handleServiceEnvCheck(c *gin.Context) {
 		return
 	}
 
-	rows, err := db.Query(
-		`SELECT key, value, COALESCE(is_secret, false) FROM environment_variables WHERE service_id = $1`,
-		service.ID,
-	)
+	q := sqlcdb.New(db.DB)
+	vars, err := q.ListServiceVariablesWithSecret(context.Background(), service.ID)
 	if err != nil {
 		c.JSON(http.StatusInternalServerError, gin.H{"error": err.Error()})
 		return
 	}
-	defer rows.Close()
 
 	var unresolved, empty, unreadable []string
 	var raw []struct{ key, val string }
-	for rows.Next() {
-		var k, v string
-		var secret bool
-		if err := rows.Scan(&k, &v, &secret); err != nil {
-			continue
-		}
-		decrypted := secrets.Decrypt(v)
-		if secret && secrets.IsEncrypted(v) && decrypted == v {
+	for _, v := range vars {
+		decrypted := secrets.Decrypt(v.Value)
+		if v.IsSecret && secrets.IsEncrypted(v.Value) && decrypted == v.Value {
 			// Ciphertext failed to decrypt — Decrypt returns the input.
-			unreadable = append(unreadable, k)
+			unreadable = append(unreadable, v.Key)
 			continue
 		}
 		if strings.TrimSpace(decrypted) == "" {
-			empty = append(empty, k)
+			empty = append(empty, v.Key)
 		}
-		raw = append(raw, struct{ key, val string }{k, decrypted})
+		raw = append(raw, struct{ key, val string }{v.Key, decrypted})
 	}
 
 	// Project siblings for ${{name.KEY}} resolution.
-	siblingIDs := map[string]string{}
-	svcRows, err := db.Query(`SELECT id, name FROM services WHERE project_id = $1`, service.ProjectID)
-	if err == nil {
-		for svcRows.Next() {
-			var id, name string
-			if err := svcRows.Scan(&id, &name); err == nil {
-				siblingIDs[strings.ToLower(name)] = id
+	siblingIDs := map[string]uuid.UUID{}
+	if refs, err := q.ListProjectServiceRefs(context.Background(), service.ProjectID); err == nil {
+		for _, s := range refs {
+			siblingIDs[strings.ToLower(s.Name)] = s.ID
 		}
 	}
-		svcRows.Close()
-	}
 
 	resolvable := func(serviceName, key string) bool {
 		id, ok := siblingIDs[strings.ToLower(serviceName)]
@@ -802,20 +714,14 @@ func handleServiceEnvCheck(c *gin.Context) {
 			return true
 		}
 		if strings.EqualFold(key, "PORT") {
-			var port int
-			if err := db.QueryRow(`SELECT COALESCE(port, 0) FROM services WHERE id = $1`, id).Scan(&port); err == nil {
-				return port > 0
+			port, err := q.GetServicePort(context.Background(), id)
+			return err == nil && port > 0
 		}
-			return false
-		}
-		var n int
-		if err := db.QueryRow(
-			`SELECT COUNT(*) FROM environment_variables WHERE service_id = $1 AND key = $2`,
-			id, key,
-		).Scan(&n); err == nil {
-			return n > 0
-		}
-		return false
+		_, err := q.GetServiceVariableValue(context.Background(), sqlcdb.GetServiceVariableValueParams{
+			ServiceID: id,
+			Key:       key,
+		})
+		return err == nil
 	}
 
 	for _, kv := range raw {

app/backend/internal/database/sqlcdb/querier.go

@@ -89,6 +89,7 @@ type Querier interface {
 	GetDeploymentForRollback(ctx context.Context, id uuid.UUID) (GetDeploymentForRollbackRow, error)
 	GetDeploymentWithProject(ctx context.Context, id uuid.UUID) (GetDeploymentWithProjectRow, error)
 	GetLastAgentCommandByType(ctx context.Context, arg GetLastAgentCommandByTypeParams) (AgentCommand, error)
+	GetLastDeployedImage(ctx context.Context, serviceID uuid.UUID) (string, error)
 	GetOutboundWebhookByID(ctx context.Context, id uuid.UUID) (OutboundWebhook, error)
 	GetOutboundWebhookByIDAndUser(ctx context.Context, arg GetOutboundWebhookByIDAndUserParams) (OutboundWebhook, error)
 	GetPreviewEnvironment(ctx context.Context, arg GetPreviewEnvironmentParams) (GetPreviewEnvironmentRow, error)
@@ -110,9 +111,12 @@ type Querier interface {
 	GetServiceForDeploy(ctx context.Context, id uuid.UUID) (GetServiceForDeployRow, error)
 	GetServiceForDeployWithOwner(ctx context.Context, id uuid.UUID) (GetServiceForDeployWithOwnerRow, error)
 	GetServicePlacementBrief(ctx context.Context, id uuid.UUID) (GetServicePlacementBriefRow, error)
+	GetServicePort(ctx context.Context, id uuid.UUID) (int32, error)
 	GetServicePublishedPort(ctx context.Context, id uuid.UUID) (int32, error)
+	GetServiceRuntimeWithOwner(ctx context.Context, id uuid.UUID) (GetServiceRuntimeWithOwnerRow, error)
 	GetServiceTemplateByID(ctx context.Context, id string) (ServiceTemplate, error)
 	GetServiceTypeBrief(ctx context.Context, arg GetServiceTypeBriefParams) (GetServiceTypeBriefRow, error)
+	GetServiceVariableValue(ctx context.Context, arg GetServiceVariableValueParams) (string, error)
 	GetUserInviteByTokenHash(ctx context.Context, tokenHash string) (UserInvite, error)
 	GetUserTokenByHash(ctx context.Context, tokenHash string) (UserToken, error)
 	InsertAgentHeartbeat(ctx context.Context, arg InsertAgentHeartbeatParams) error
@@ -138,6 +142,7 @@ type Querier interface {
 	ListEnabledOutboundWebhooks(ctx context.Context) ([]OutboundWebhook, error)
 	ListExpiredPreviewsForUser(ctx context.Context, arg ListExpiredPreviewsForUserParams) ([]ListExpiredPreviewsForUserRow, error)
 	ListInstanceMetrics(ctx context.Context, arg ListInstanceMetricsParams) ([]ListInstanceMetricsRow, error)
+	ListNodeAgentsForService(ctx context.Context, serviceID string) ([]string, error)
 	ListNodeMetrics(ctx context.Context, arg ListNodeMetricsParams) ([]ListNodeMetricsRow, error)
 	ListNotificationChannelsByUser(ctx context.Context, userID uuid.UUID) ([]NotificationChannel, error)
 	ListNotificationsByUser(ctx context.Context, arg ListNotificationsByUserParams) ([]Notification, error)
@@ -146,6 +151,7 @@ type Querier interface {
 	ListPendingProjects(ctx context.Context) ([]Project, error)
 	ListPreviewEnvironmentsForProject(ctx context.Context, projectID uuid.UUID) ([]ListPreviewEnvironmentsForProjectRow, error)
 	ListProjectEnvironments(ctx context.Context, projectID uuid.UUID) ([]ListProjectEnvironmentsRow, error)
+	ListProjectServiceRefs(ctx context.Context, projectID uuid.UUID) ([]ListProjectServiceRefsRow, error)
 	// Visibility model: a project is readable when it is approved for public
 	// display, when the caller owns or is a member of it, or when the caller is a
 	// platform admin. Anonymous callers pass user_id = uuid.Nil, is_admin = false.
@@ -164,6 +170,8 @@ type Querier interface {
 	ListServiceMetrics(ctx context.Context, arg ListServiceMetricsParams) ([]ListServiceMetricsRow, error)
 	ListServiceTemplatesByCategoryForUser(ctx context.Context, arg ListServiceTemplatesByCategoryForUserParams) ([]ServiceTemplate, error)
 	ListServiceTemplatesForUser(ctx context.Context, ownerID uuid.NullUUID) ([]ServiceTemplate, error)
+	ListServiceVariableValues(ctx context.Context, serviceID uuid.UUID) ([]ListServiceVariableValuesRow, error)
+	ListServiceVariablesWithSecret(ctx context.Context, serviceID uuid.UUID) ([]ListServiceVariablesWithSecretRow, error)
 	ListSweepablePreviews(ctx context.Context) ([]ListSweepablePreviewsRow, error)
 	ListUserInvites(ctx context.Context) ([]UserInvite, error)
 	ListUserTokens(ctx context.Context, userID uuid.UUID) ([]UserToken, error)
@@ -199,6 +207,8 @@ type Querier interface {
 	SetProjectApproved(ctx context.Context, arg SetProjectApprovedParams) (int64, error)
 	SetServiceDomainDefault(ctx context.Context, arg SetServiceDomainDefaultParams) (int64, error)
 	SetServiceLegacyDomain(ctx context.Context, arg SetServiceLegacyDomainParams) error
+	SetServicePublishedPort(ctx context.Context, arg SetServicePublishedPortParams) error
+	SetServiceReplicas(ctx context.Context, arg SetServiceReplicasParams) error
 	SetServiceStatus(ctx context.Context, arg SetServiceStatusParams) error
 	SyncDeploymentProgress(ctx context.Context, arg SyncDeploymentProgressParams) error
 	SyncPreviewStatuses(ctx context.Context) error

app/backend/internal/database/sqlcdb/runtime.sql.go

@@ -0,0 +1,289 @@
+// Code generated by sqlc. DO NOT EDIT.
+// versions:
+//   sqlc v1.31.1
+// source: runtime.sql
+
+package sqlcdb
+
+import (
+	"context"
+	"database/sql"
+
+	"github.com/google/uuid"
+)
+
+const getLastDeployedImage = `-- name: GetLastDeployedImage :one
+SELECT (image_name || ':' || image_tag)::text AS image FROM deployments
+WHERE service_id = $1 AND status = 'deployed' AND image_name <> ''
+ORDER BY created_at DESC LIMIT 1
+`
+
+func (q *Queries) GetLastDeployedImage(ctx context.Context, serviceID uuid.UUID) (string, error) {
+	row := q.db.QueryRowContext(ctx, getLastDeployedImage, serviceID)
+	var image string
+	err := row.Scan(&image)
+	return image, err
+}
+
+const getServicePort = `-- name: GetServicePort :one
+SELECT COALESCE(port, 0) AS port FROM services WHERE id = $1
+`
+
+func (q *Queries) GetServicePort(ctx context.Context, id uuid.UUID) (int32, error) {
+	row := q.db.QueryRowContext(ctx, getServicePort, id)
+	var port int32
+	err := row.Scan(&port)
+	return port, err
+}
+
+const getServiceRuntimeWithOwner = `-- name: GetServiceRuntimeWithOwner :one
+SELECT s.id, s.project_id, s.name,
+       COALESCE(s.type, s.service_type, '') AS type, COALESCE(s.status, '') AS status,
+       COALESCE(s.image, s.image_name, '') AS image, COALESCE(s.command, s.start_command, '') AS command,
+       COALESCE(s.environment, '') AS environment, COALESCE(s.git_repo, s.source_url, '') AS git_repo,
+       COALESCE(s.git_branch, '') AS git_branch, COALESCE(s.build_path, '') AS build_path,
+       COALESCE(s.cpu, '') AS cpu, COALESCE(s.memory, '') AS memory,
+       COALESCE(s.replicas, 1) AS replicas, COALESCE(s.port, 0) AS port,
+       COALESCE(s.domain, '') AS domain, COALESCE(s.healthcheck_path, '') AS healthcheck_path,
+       COALESCE(s.restart_policy, 'unless-stopped') AS restart_policy,
+       COALESCE(s.builder, 'auto') AS builder, COALESCE(s.cpu_reserve, '') AS cpu_reserve,
+       COALESCE(s.memory_reserve, '') AS memory_reserve, COALESCE(s.static_build_cmd, '') AS static_build_cmd,
+       COALESCE(s.static_dir, '') AS static_dir,
+       s.created_at, s.updated_at, p.owner_id
+FROM services s
+JOIN projects p ON s.project_id = p.id
+WHERE s.id = $1
+`
+
+type GetServiceRuntimeWithOwnerRow struct {
+	ID              uuid.UUID    `json:"id"`
+	ProjectID       uuid.UUID    `json:"project_id"`
+	Name            string       `json:"name"`
+	Type            string       `json:"type"`
+	Status          string       `json:"status"`
+	Image           string       `json:"image"`
+	Command         string       `json:"command"`
+	Environment     string       `json:"environment"`
+	GitRepo         string       `json:"git_repo"`
+	GitBranch       string       `json:"git_branch"`
+	BuildPath       string       `json:"build_path"`
+	Cpu             string       `json:"cpu"`
+	Memory          string       `json:"memory"`
+	Replicas        int32        `json:"replicas"`
+	Port            int32        `json:"port"`
+	Domain          string       `json:"domain"`
+	HealthcheckPath string       `json:"healthcheck_path"`
+	RestartPolicy   string       `json:"restart_policy"`
+	Builder         string       `json:"builder"`
+	CpuReserve      string       `json:"cpu_reserve"`
+	MemoryReserve   string       `json:"memory_reserve"`
+	StaticBuildCmd  string       `json:"static_build_cmd"`
+	StaticDir       string       `json:"static_dir"`
+	CreatedAt       sql.NullTime `json:"created_at"`
+	UpdatedAt       sql.NullTime `json:"updated_at"`
+	OwnerID         uuid.UUID    `json:"owner_id"`
+}
+
+func (q *Queries) GetServiceRuntimeWithOwner(ctx context.Context, id uuid.UUID) (GetServiceRuntimeWithOwnerRow, error) {
+	row := q.db.QueryRowContext(ctx, getServiceRuntimeWithOwner, id)
+	var i GetServiceRuntimeWithOwnerRow
+	err := row.Scan(
+		&i.ID,
+		&i.ProjectID,
+		&i.Name,
+		&i.Type,
+		&i.Status,
+		&i.Image,
+		&i.Command,
+		&i.Environment,
+		&i.GitRepo,
+		&i.GitBranch,
+		&i.BuildPath,
+		&i.Cpu,
+		&i.Memory,
+		&i.Replicas,
+		&i.Port,
+		&i.Domain,
+		&i.HealthcheckPath,
+		&i.RestartPolicy,
+		&i.Builder,
+		&i.CpuReserve,
+		&i.MemoryReserve,
+		&i.StaticBuildCmd,
+		&i.StaticDir,
+		&i.CreatedAt,
+		&i.UpdatedAt,
+		&i.OwnerID,
+	)
+	return i, err
+}
+
+const getServiceVariableValue = `-- name: GetServiceVariableValue :one
+SELECT value FROM environment_variables WHERE service_id = $1 AND key = $2
+`
+
+type GetServiceVariableValueParams struct {
+	ServiceID uuid.UUID `json:"service_id"`
+	Key       string    `json:"key"`
+}
+
+func (q *Queries) GetServiceVariableValue(ctx context.Context, arg GetServiceVariableValueParams) (string, error) {
+	row := q.db.QueryRowContext(ctx, getServiceVariableValue, arg.ServiceID, arg.Key)
+	var value string
+	err := row.Scan(&value)
+	return value, err
+}
+
+const listNodeAgentsForService = `-- name: ListNodeAgentsForService :many
+SELECT DISTINCT node_agent_id FROM container_instances WHERE service_id = $1
+`
+
+func (q *Queries) ListNodeAgentsForService(ctx context.Context, serviceID string) ([]string, error) {
+	rows, err := q.db.QueryContext(ctx, listNodeAgentsForService, serviceID)
+	if err != nil {
+		return nil, err
+	}
+	defer rows.Close()
+	items := []string{}
+	for rows.Next() {
+		var node_agent_id string
+		if err := rows.Scan(&node_agent_id); err != nil {
+			return nil, err
+		}
+		items = append(items, node_agent_id)
+	}
+	if err := rows.Close(); err != nil {
+		return nil, err
+	}
+	if err := rows.Err(); err != nil {
+		return nil, err
+	}
+	return items, nil
+}
+
+const listProjectServiceRefs = `-- name: ListProjectServiceRefs :many
+SELECT id, name FROM services WHERE project_id = $1
+`
+
+type ListProjectServiceRefsRow struct {
+	ID   uuid.UUID `json:"id"`
+	Name string    `json:"name"`
+}
+
+func (q *Queries) ListProjectServiceRefs(ctx context.Context, projectID uuid.UUID) ([]ListProjectServiceRefsRow, error) {
+	rows, err := q.db.QueryContext(ctx, listProjectServiceRefs, projectID)
+	if err != nil {
+		return nil, err
+	}
+	defer rows.Close()
+	items := []ListProjectServiceRefsRow{}
+	for rows.Next() {
+		var i ListProjectServiceRefsRow
+		if err := rows.Scan(&i.ID, &i.Name); err != nil {
+			return nil, err
+		}
+		items = append(items, i)
+	}
+	if err := rows.Close(); err != nil {
+		return nil, err
+	}
+	if err := rows.Err(); err != nil {
+		return nil, err
+	}
+	return items, nil
+}
+
+const listServiceVariableValues = `-- name: ListServiceVariableValues :many
+SELECT key, value FROM environment_variables WHERE service_id = $1
+`
+
+type ListServiceVariableValuesRow struct {
+	Key   string `json:"key"`
+	Value string `json:"value"`
+}
+
+func (q *Queries) ListServiceVariableValues(ctx context.Context, serviceID uuid.UUID) ([]ListServiceVariableValuesRow, error) {
+	rows, err := q.db.QueryContext(ctx, listServiceVariableValues, serviceID)
+	if err != nil {
+		return nil, err
+	}
+	defer rows.Close()
+	items := []ListServiceVariableValuesRow{}
+	for rows.Next() {
+		var i ListServiceVariableValuesRow
+		if err := rows.Scan(&i.Key, &i.Value); err != nil {
+			return nil, err
+		}
+		items = append(items, i)
+	}
+	if err := rows.Close(); err != nil {
+		return nil, err
+	}
+	if err := rows.Err(); err != nil {
+		return nil, err
+	}
+	return items, nil
+}
+
+const listServiceVariablesWithSecret = `-- name: ListServiceVariablesWithSecret :many
+SELECT key, value, COALESCE(is_secret, false) AS is_secret
+FROM environment_variables WHERE service_id = $1
+`
+
+type ListServiceVariablesWithSecretRow struct {
+	Key      string `json:"key"`
+	Value    string `json:"value"`
+	IsSecret bool   `json:"is_secret"`
+}
+
+func (q *Queries) ListServiceVariablesWithSecret(ctx context.Context, serviceID uuid.UUID) ([]ListServiceVariablesWithSecretRow, error) {
+	rows, err := q.db.QueryContext(ctx, listServiceVariablesWithSecret, serviceID)
+	if err != nil {
+		return nil, err
+	}
+	defer rows.Close()
+	items := []ListServiceVariablesWithSecretRow{}
+	for rows.Next() {
+		var i ListServiceVariablesWithSecretRow
+		if err := rows.Scan(&i.Key, &i.Value, &i.IsSecret); err != nil {
+			return nil, err
+		}
+		items = append(items, i)
+	}
+	if err := rows.Close(); err != nil {
+		return nil, err
+	}
+	if err := rows.Err(); err != nil {
+		return nil, err
+	}
+	return items, nil
+}
+
+const setServicePublishedPort = `-- name: SetServicePublishedPort :exec
+UPDATE services SET published_port = $1 WHERE id = $2 AND published_port <> $1
+`
+
+type SetServicePublishedPortParams struct {
+	PublishedPort int32     `json:"published_port"`
+	ID            uuid.UUID `json:"id"`
+}
+
+func (q *Queries) SetServicePublishedPort(ctx context.Context, arg SetServicePublishedPortParams) error {
+	_, err := q.db.ExecContext(ctx, setServicePublishedPort, arg.PublishedPort, arg.ID)
+	return err
+}
+
+const setServiceReplicas = `-- name: SetServiceReplicas :exec
+UPDATE services SET replicas = $1, updated_at = $2 WHERE id = $3
+`
+
+type SetServiceReplicasParams struct {
+	Replicas  int32        `json:"replicas"`
+	UpdatedAt sql.NullTime `json:"updated_at"`
+	ID        uuid.UUID    `json:"id"`
+}
+
+func (q *Queries) SetServiceReplicas(ctx context.Context, arg SetServiceReplicasParams) error {
+	_, err := q.db.ExecContext(ctx, setServiceReplicas, arg.Replicas, arg.UpdatedAt, arg.ID)
+	return err
+}

app/backend/sqlc/queries/runtime.sql

@@ -0,0 +1,47 @@
+-- name: ListNodeAgentsForService :many
+SELECT DISTINCT node_agent_id FROM container_instances WHERE service_id = $1;
+
+-- name: ListServiceVariableValues :many
+SELECT key, value FROM environment_variables WHERE service_id = $1;
+
+-- name: ListServiceVariablesWithSecret :many
+SELECT key, value, COALESCE(is_secret, false) AS is_secret
+FROM environment_variables WHERE service_id = $1;
+
+-- name: ListProjectServiceRefs :many
+SELECT id, name FROM services WHERE project_id = $1;
+
+-- name: GetServicePort :one
+SELECT COALESCE(port, 0) AS port FROM services WHERE id = $1;
+
+-- name: GetServiceVariableValue :one
+SELECT value FROM environment_variables WHERE service_id = $1 AND key = $2;
+
+-- name: GetServiceRuntimeWithOwner :one
+SELECT s.id, s.project_id, s.name,
+       COALESCE(s.type, s.service_type, '') AS type, COALESCE(s.status, '') AS status,
+       COALESCE(s.image, s.image_name, '') AS image, COALESCE(s.command, s.start_command, '') AS command,
+       COALESCE(s.environment, '') AS environment, COALESCE(s.git_repo, s.source_url, '') AS git_repo,
+       COALESCE(s.git_branch, '') AS git_branch, COALESCE(s.build_path, '') AS build_path,
+       COALESCE(s.cpu, '') AS cpu, COALESCE(s.memory, '') AS memory,
+       COALESCE(s.replicas, 1) AS replicas, COALESCE(s.port, 0) AS port,
+       COALESCE(s.domain, '') AS domain, COALESCE(s.healthcheck_path, '') AS healthcheck_path,
+       COALESCE(s.restart_policy, 'unless-stopped') AS restart_policy,
+       COALESCE(s.builder, 'auto') AS builder, COALESCE(s.cpu_reserve, '') AS cpu_reserve,
+       COALESCE(s.memory_reserve, '') AS memory_reserve, COALESCE(s.static_build_cmd, '') AS static_build_cmd,
+       COALESCE(s.static_dir, '') AS static_dir,
+       s.created_at, s.updated_at, p.owner_id
+FROM services s
+JOIN projects p ON s.project_id = p.id
+WHERE s.id = $1;
+
+-- name: GetLastDeployedImage :one
+SELECT (image_name || ':' || image_tag)::text AS image FROM deployments
+WHERE service_id = $1 AND status = 'deployed' AND image_name <> ''
+ORDER BY created_at DESC LIMIT 1;
+
+-- name: SetServicePublishedPort :exec
+UPDATE services SET published_port = $1 WHERE id = $2 AND published_port <> $1;
+
+-- name: SetServiceReplicas :exec
+UPDATE services SET replicas = $1, updated_at = $2 WHERE id = $3;