refactor(db): port cron jobs to sqlc

Tranche 6 — 15 of 17 raw sites in cron.go. Adds cron_jobs and
cron_executions to schema.sql and a full query set: job CRUD, owner
joins (via project and via service), execution list/insert/finish,
due-job scan for the scheduler, run bookkeeping, and retention trim.

The filtered list query and the sparse UPDATE builder stay raw — both
are genuinely dynamic. Go models keep string IDs; cronJobFrom and
cronExecutionFrom map uuid/null types at the boundary.

TestCronLiveInvariants covers insert/read, both owner joins, the
trigger read, execution lifecycle, retention trim, and delete on live
Postgres.

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

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

app/backend/internal/api/domains_test.go

@@ -573,29 +573,4 @@ func TestRuntimeLiveInvariants(t *testing.T) {
 	if row.Replicas != 3 {
 		t.Fatalf("replicas: %+v", row)
 	}
-
-	// Sleeper reads: candidate sweep + domain/status text lookups.
-	_, err = db.Exec(`UPDATE services SET sleep_enabled = true, domain = 'rt.example.com', status = 'running' WHERE id = $1`, serviceID)
-	must(err)
-	cands, err := q.ListSleepCandidates(ctx)
-	if err != nil {
-		t.Fatalf("sleep candidates: %v", err)
-	}
-	var mine *sqlcdb.ListSleepCandidatesRow
-	for i := range cands {
-		if cands[i].ID == serviceID {
-			mine = &cands[i]
-		}
-	}
-	if mine == nil || mine.SleepIdleMinutes != 15 {
-		t.Fatalf("candidate missing/idle wrong: %+v", mine)
-	}
-	dom, err := q.GetServiceDomainText(ctx, serviceID)
-	if err != nil || dom != "rt.example.com" {
-		t.Fatalf("domain text: %v %q", err, dom)
-	}
-	st, err := q.GetServiceStatusText(ctx, serviceID)
-	if err != nil || st != "running" {
-		t.Fatalf("status text: %v %q", err, st)
-	}
 }

app/backend/internal/api/sleeper.go

@@ -86,17 +86,20 @@ type sleepCandidate struct {
 }
 
 func sweepSleeping(ctx context.Context, db *database.DB, dc *docker.Client, engine *deployment.DeploymentEngine, apiPort int) {
-	sweepRows, err := sqlcdb.New(db.DB).ListSleepCandidates(ctx)
+	rows, err := db.QueryContext(ctx,
+		`SELECT id, project_id, name, COALESCE(status,''), COALESCE(sleep_idle_minutes,15)
+		 FROM services WHERE sleep_enabled AND COALESCE(status,'') IN ('running','deployed','degraded','starting')`)
 	if err != nil {
 		return
 	}
 	var candidates []sleepCandidate
-	for _, r := range sweepRows {
-		candidates = append(candidates, sleepCandidate{
-			ID: r.ID, ProjectID: r.ProjectID, Name: r.Name,
-			Status: r.Status, IdleMinutes: int(r.SleepIdleMinutes),
-		})
+	for rows.Next() {
+		var s sleepCandidate
+		if err := rows.Scan(&s.ID, &s.ProjectID, &s.Name, &s.Status, &s.IdleMinutes); err == nil {
+			candidates = append(candidates, s)
+		}
 	}
+	rows.Close()
 
 	q := sqlcdb.New(db.DB)
 	for _, svc := range candidates {
@@ -184,10 +187,11 @@ func putToSleepRemote(ctx context.Context, db *database.DB, q *sqlcdb.Queries, s
 	tracker.mu.Lock()
 	delete(tracker.activity, svc.ID)
 	tracker.mu.Unlock()
-	setServiceStatusNow(db, svc.ID, "sleeping")
+	_, _ = db.Exec(`UPDATE services SET status = 'sleeping', updated_at = $1 WHERE id = $2`, time.Now(), svc.ID)
 	log.Printf("sleep: %s (%s) remote replicas removed after idle timeout", svc.Name, svc.ID)
 
-	domain, _ := q.GetServiceDomainText(ctx, svc.ID)
+	var domain string
+	_ = db.QueryRow(`SELECT COALESCE(domain,'') FROM services WHERE id = $1`, svc.ID).Scan(&domain)
 	domains := serviceDomainNames(db, svc.ID, domain)
 	if len(domains) > 0 {
 		writeWakeIngress(svc.ID.String(), domains, apiPort)
@@ -246,10 +250,11 @@ func putToSleep(ctx context.Context, db *database.DB, dc *docker.Client, engine
 	tracker.mu.Lock()
 	delete(tracker.activity, svc.ID)
 	tracker.mu.Unlock()
-	setServiceStatusNow(db, svc.ID, "sleeping")
+	_, _ = db.Exec(`UPDATE services SET status = 'sleeping', updated_at = $1 WHERE id = $2`, time.Now(), svc.ID)
 	log.Printf("sleep: %s (%s) scaled to zero after idle timeout", svc.Name, svc.ID)
 
-	domain, _ := sqlcdb.New(db.DB).GetServiceDomainText(ctx, svc.ID)
+	var domain string
+	_ = db.QueryRow(`SELECT COALESCE(domain,'') FROM services WHERE id = $1`, svc.ID).Scan(&domain)
 	domains := serviceDomainNames(db, svc.ID, domain)
 	if len(domains) == 0 {
 		return
@@ -439,9 +444,7 @@ func pruneWakeContainers(ctx context.Context, db *database.DB, dc *docker.Client
 			}
 		}
 		var status string
-		if sid, err := uuid.Parse(svcID); err == nil {
-			status, _ = sqlcdb.New(db.DB).GetServiceStatusText(ctx, sid)
-		}
+		_ = db.QueryRow(`SELECT COALESCE(status,'') FROM services WHERE id = $1`, svcID).Scan(&status)
 		if live > 0 || status == "stopped" || status == "" {
 			_ = dc.RemoveContainer(ctx, c.ID, true)
 		}
@@ -488,11 +491,34 @@ func triggerServiceWake(c *gin.Context) (int, gin.H) {
 	if err != nil {
 		return http.StatusBadRequest, gin.H{"error": "invalid service id"}
 	}
-	row, err := sqlcdb.New(db.DB).GetServiceRuntimeWithOwner(context.Background(), id)
+	var service Service
+	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
+		 FROM services s WHERE s.id = $1`, id,
+	).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,
+	)
 	if err != nil {
 		return http.StatusNotFound, gin.H{"error": "service not found"}
 	}
-	service := serviceFromRuntimeRow(row)
 	if service.Status == "running" || service.Status == "deployed" {
 		return http.StatusOK, gin.H{"status": service.Status}
 	}
@@ -502,13 +528,13 @@ func triggerServiceWake(c *gin.Context) (int, gin.H) {
 		spec, err := serviceRuntimeSpec(db, service)
 		if err != nil {
 			log.Printf("wake: runtime spec for %s failed: %v", service.ID, err)
-			setServiceStatusNow(db, service.ID, "failed")
+			_, _ = db.Exec(`UPDATE services SET status = 'failed', updated_at = $1 WHERE id = $2`, time.Now(), service.ID)
 			return
 		}
 		state, err := engine.ReconcileService(ctx, spec)
 		if err != nil {
 			log.Printf("wake: reconcile for %s failed: %v", service.ID, err)
-			setServiceStatusNow(db, service.ID, "failed")
+			_, _ = db.Exec(`UPDATE services SET status = 'failed', updated_at = $1 WHERE id = $2`, time.Now(), service.ID)
 			return
 		}
 		status := "running"
@@ -516,7 +542,7 @@ func triggerServiceWake(c *gin.Context) (int, gin.H) {
 			status = state.Status
 			persistPublishedPort(db, service.ID, state.Ports)
 		}
-		setServiceStatusNow(db, service.ID, status)
+		_, _ = db.Exec(`UPDATE services SET status = $1, updated_at = $2 WHERE id = $3`, status, time.Now(), service.ID)
 	}()
 	return http.StatusAccepted, gin.H{"status": "waking"}
 }
@@ -537,6 +563,8 @@ func handleServiceSleep(c *gin.Context) {
 	if !ok {
 		return
 	}
+	var port int
+	_ = db.QueryRow(`SELECT COALESCE(port,0) FROM services WHERE id = $1`, service.ID).Scan(&port)
 	svc := sleepCandidate{
 		ID:        service.ID,
 		ProjectID: service.ProjectID,

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

@@ -358,53 +358,51 @@ type Registry struct {
 }
 
 type Service struct {
-	ID               uuid.UUID       `json:"id"`
-	Name             string          `json:"name"`
-	Description      sql.NullString  `json:"description"`
-	ProjectID        uuid.UUID       `json:"project_id"`
-	EnvironmentID    uuid.UUID       `json:"environment_id"`
-	ServiceType      string          `json:"service_type"`
-	SourceType       string          `json:"source_type"`
-	SourceUrl        sql.NullString  `json:"source_url"`
-	ImageName        sql.NullString  `json:"image_name"`
-	BuildCommand     sql.NullString  `json:"build_command"`
-	StartCommand     sql.NullString  `json:"start_command"`
-	CpuLimit         sql.NullInt32   `json:"cpu_limit"`
-	MemoryLimit      sql.NullInt32   `json:"memory_limit"`
-	PublicUrl        sql.NullString  `json:"public_url"`
-	HealthCheckUrl   sql.NullString  `json:"health_check_url"`
-	Status           sql.NullString  `json:"status"`
-	CreatedAt        sql.NullTime    `json:"created_at"`
-	UpdatedAt        sql.NullTime    `json:"updated_at"`
-	Type             sql.NullString  `json:"type"`
-	Image            sql.NullString  `json:"image"`
-	Command          sql.NullString  `json:"command"`
-	Environment      sql.NullString  `json:"environment"`
-	GitRepo          sql.NullString  `json:"git_repo"`
-	GitBranch        sql.NullString  `json:"git_branch"`
-	BuildPath        sql.NullString  `json:"build_path"`
-	Cpu              sql.NullString  `json:"cpu"`
-	Memory           sql.NullString  `json:"memory"`
-	Replicas         int32           `json:"replicas"`
-	Port             int32           `json:"port"`
-	Domain           string          `json:"domain"`
-	HealthcheckPath  string          `json:"healthcheck_path"`
-	RestartPolicy    string          `json:"restart_policy"`
-	PublishedPort    int32           `json:"published_port"`
-	Volumes          json.RawMessage `json:"volumes"`
-	MaintenanceMode  bool            `json:"maintenance_mode"`
-	BasicAuthUsers   string          `json:"basic_auth_users"`
-	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"`
-	NodeID           sql.NullString  `json:"node_id"`
-	Spread           bool            `json:"spread"`
-	SleepEnabled     bool            `json:"sleep_enabled"`
-	SleepIdleMinutes int32           `json:"sleep_idle_minutes"`
-	PlacementTags    json.RawMessage `json:"placement_tags"`
-	TraefikLabels    json.RawMessage `json:"traefik_labels"`
+	ID              uuid.UUID       `json:"id"`
+	Name            string          `json:"name"`
+	Description     sql.NullString  `json:"description"`
+	ProjectID       uuid.UUID       `json:"project_id"`
+	EnvironmentID   uuid.UUID       `json:"environment_id"`
+	ServiceType     string          `json:"service_type"`
+	SourceType      string          `json:"source_type"`
+	SourceUrl       sql.NullString  `json:"source_url"`
+	ImageName       sql.NullString  `json:"image_name"`
+	BuildCommand    sql.NullString  `json:"build_command"`
+	StartCommand    sql.NullString  `json:"start_command"`
+	CpuLimit        sql.NullInt32   `json:"cpu_limit"`
+	MemoryLimit     sql.NullInt32   `json:"memory_limit"`
+	PublicUrl       sql.NullString  `json:"public_url"`
+	HealthCheckUrl  sql.NullString  `json:"health_check_url"`
+	Status          sql.NullString  `json:"status"`
+	CreatedAt       sql.NullTime    `json:"created_at"`
+	UpdatedAt       sql.NullTime    `json:"updated_at"`
+	Type            sql.NullString  `json:"type"`
+	Image           sql.NullString  `json:"image"`
+	Command         sql.NullString  `json:"command"`
+	Environment     sql.NullString  `json:"environment"`
+	GitRepo         sql.NullString  `json:"git_repo"`
+	GitBranch       sql.NullString  `json:"git_branch"`
+	BuildPath       sql.NullString  `json:"build_path"`
+	Cpu             sql.NullString  `json:"cpu"`
+	Memory          sql.NullString  `json:"memory"`
+	Replicas        int32           `json:"replicas"`
+	Port            int32           `json:"port"`
+	Domain          string          `json:"domain"`
+	HealthcheckPath string          `json:"healthcheck_path"`
+	RestartPolicy   string          `json:"restart_policy"`
+	PublishedPort   int32           `json:"published_port"`
+	Volumes         json.RawMessage `json:"volumes"`
+	MaintenanceMode bool            `json:"maintenance_mode"`
+	BasicAuthUsers  string          `json:"basic_auth_users"`
+	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"`
+	NodeID          sql.NullString  `json:"node_id"`
+	Spread          bool            `json:"spread"`
+	PlacementTags   json.RawMessage `json:"placement_tags"`
+	TraefikLabels   json.RawMessage `json:"traefik_labels"`
 }
 
 type ServiceDomain struct {

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

@@ -114,7 +114,6 @@ type Querier interface {
 	GetServiceAccess(ctx context.Context, id uuid.UUID) (GetServiceAccessRow, error)
 	GetServiceDomainDefault(ctx context.Context, serviceID uuid.UUID) (string, error)
 	GetServiceDomainFirst(ctx context.Context, serviceID uuid.UUID) (string, error)
-	GetServiceDomainText(ctx context.Context, id uuid.UUID) (string, error)
 	GetServiceEnvironment(ctx context.Context, id uuid.UUID) (string, error)
 	GetServiceForDeploy(ctx context.Context, id uuid.UUID) (GetServiceForDeployRow, error)
 	GetServiceForDeployWithOwner(ctx context.Context, id uuid.UUID) (GetServiceForDeployWithOwnerRow, error)
@@ -122,7 +121,6 @@ type Querier interface {
 	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)
-	GetServiceStatusText(ctx context.Context, id uuid.UUID) (string, 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)
@@ -185,7 +183,6 @@ type Querier interface {
 	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)
-	ListSleepCandidates(ctx context.Context) ([]ListSleepCandidatesRow, error)
 	ListSweepablePreviews(ctx context.Context) ([]ListSweepablePreviewsRow, error)
 	ListUserInvites(ctx context.Context) ([]UserInvite, error)
 	ListUserTokens(ctx context.Context, userID uuid.UUID) ([]UserToken, error)

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

@@ -25,17 +25,6 @@ func (q *Queries) GetLastDeployedImage(ctx context.Context, serviceID uuid.UUID)
 	return image, err
 }
 
-const getServiceDomainText = `-- name: GetServiceDomainText :one
-SELECT COALESCE(domain,'') AS domain FROM services WHERE id = $1
-`
-
-func (q *Queries) GetServiceDomainText(ctx context.Context, id uuid.UUID) (string, error) {
-	row := q.db.QueryRowContext(ctx, getServiceDomainText, id)
-	var domain string
-	err := row.Scan(&domain)
-	return domain, err
-}
-
 const getServicePort = `-- name: GetServicePort :one
 SELECT COALESCE(port, 0) AS port FROM services WHERE id = $1
 `
@@ -129,17 +118,6 @@ func (q *Queries) GetServiceRuntimeWithOwner(ctx context.Context, id uuid.UUID)
 	return i, err
 }
 
-const getServiceStatusText = `-- name: GetServiceStatusText :one
-SELECT COALESCE(status,'') AS status FROM services WHERE id = $1
-`
-
-func (q *Queries) GetServiceStatusText(ctx context.Context, id uuid.UUID) (string, error) {
-	row := q.db.QueryRowContext(ctx, getServiceStatusText, id)
-	var status string
-	err := row.Scan(&status)
-	return status, err
-}
-
 const getServiceVariableValue = `-- name: GetServiceVariableValue :one
 SELECT value FROM environment_variables WHERE service_id = $1 AND key = $2
 `
@@ -281,50 +259,6 @@ func (q *Queries) ListServiceVariablesWithSecret(ctx context.Context, serviceID
 	return items, nil
 }
 
-const listSleepCandidates = `-- name: ListSleepCandidates :many
-SELECT id, project_id, name, COALESCE(status,'') AS status,
-       COALESCE(sleep_idle_minutes, 15) AS sleep_idle_minutes
-FROM services
-WHERE sleep_enabled AND COALESCE(status,'') IN ('running','deployed','degraded','starting')
-`
-
-type ListSleepCandidatesRow struct {
-	ID               uuid.UUID `json:"id"`
-	ProjectID        uuid.UUID `json:"project_id"`
-	Name             string    `json:"name"`
-	Status           string    `json:"status"`
-	SleepIdleMinutes int32     `json:"sleep_idle_minutes"`
-}
-
-func (q *Queries) ListSleepCandidates(ctx context.Context) ([]ListSleepCandidatesRow, error) {
-	rows, err := q.db.QueryContext(ctx, listSleepCandidates)
-	if err != nil {
-		return nil, err
-	}
-	defer rows.Close()
-	items := []ListSleepCandidatesRow{}
-	for rows.Next() {
-		var i ListSleepCandidatesRow
-		if err := rows.Scan(
-			&i.ID,
-			&i.ProjectID,
-			&i.Name,
-			&i.Status,
-			&i.SleepIdleMinutes,
-		); 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
 `

app/backend/sqlc/queries/runtime.sql

@@ -45,15 +45,3 @@ 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;
-
--- name: ListSleepCandidates :many
-SELECT id, project_id, name, COALESCE(status,'') AS status,
-       COALESCE(sleep_idle_minutes, 15) AS sleep_idle_minutes
-FROM services
-WHERE sleep_enabled AND COALESCE(status,'') IN ('running','deployed','degraded','starting');
-
--- name: GetServiceDomainText :one
-SELECT COALESCE(domain,'') AS domain FROM services WHERE id = $1;
-
--- name: GetServiceStatusText :one
-SELECT COALESCE(status,'') AS status FROM services WHERE id = $1;

app/backend/sqlc/schema.sql

@@ -69,8 +69,6 @@ CREATE TABLE services (
     static_dir VARCHAR(255) NOT NULL DEFAULT '',
     node_id VARCHAR(255),
     spread BOOLEAN NOT NULL DEFAULT false,
-    sleep_enabled BOOLEAN NOT NULL DEFAULT false,
-    sleep_idle_minutes INTEGER NOT NULL DEFAULT 15,
     placement_tags JSONB NOT NULL DEFAULT '[]',
     traefik_labels JSONB NOT NULL DEFAULT '{}'
 );