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>

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/cron.go

@@ -65,13 +65,47 @@ type UpdateCronJobRequest struct {
 	Retention int    `json:"retention"`
 }
 
+// cronJobFrom maps the sqlc row onto the string-ID API model.
+func cronJobFrom(j sqlcdb.CronJob) CronJob {
+	return CronJob{
+		ID:         j.ID.String(),
+		ProjectID:  j.ProjectID.String(),
+		ServiceID:  j.ServiceID.String(),
+		Name:       j.Name,
+		Schedule:   j.Schedule,
+		Command:    j.Command,
+		Timezone:   j.Timezone.String,
+		Enabled:    j.Enabled.Bool,
+		LastRunAt:  timePtrNT(j.LastRunAt),
+		NextRunAt:  timePtrNT(j.NextRunAt),
+		LastStatus: j.LastStatus.String,
+		LastOutput: j.LastOutput.String,
+		Retention:  int(j.Retention.Int32),
+		CreatedAt:  j.CreatedAt.Time,
+		UpdatedAt:  j.UpdatedAt.Time,
+	}
+}
+
+// cronExecutionFrom maps the sqlc row onto the string-ID API model.
+func cronExecutionFrom(e sqlcdb.CronExecution) CronExecution {
+	return CronExecution{
+		ID:         e.ID.String(),
+		CronJobID:  e.CronJobID.String(),
+		StartedAt:  e.StartedAt,
+		FinishedAt: timePtrNT(e.FinishedAt),
+		Status:     e.Status.String,
+		Output:     e.Output.String,
+		Error:      e.Error.String,
+	}
+}
+
 // cronJobProjectID resolves the project a cron job belongs to.
 func cronJobProjectID(db *database.DB, jobID string) (uuid.UUID, bool) {
-	var projectID uuid.UUID
-	err := db.QueryRow(
-		`SELECT cj.project_id FROM cron_jobs cj WHERE cj.id = $1`,
-		jobID,
-	).Scan(&projectID)
+	id, err := uuid.Parse(jobID)
+	if err != nil {
+		return uuid.Nil, false
+	}
+	projectID, err := sqlcdb.New(db.DB).GetCronJobProjectID(context.Background(), id)
 	if err != nil {
 		return uuid.Nil, false
 	}
@@ -137,15 +171,14 @@ func handleCreateCronJob(c *gin.Context) {
 		return
 	}
 
-	var ownerCheck string
-	err := db.QueryRow(
-		`SELECT p.owner_id FROM projects p 
-		 JOIN services s ON s.project_id = p.id 
-		 WHERE s.id = $1`,
-		req.ServiceID,
-	).Scan(&ownerCheck)
+	svcID, err := uuid.Parse(req.ServiceID)
+	if err != nil {
+		c.JSON(http.StatusForbidden, gin.H{"error": "Access denied"})
+		return
+	}
+	ownerCheck, err := sqlcdb.New(db.DB).GetCronOwnerViaService(context.Background(), svcID)
 
-	if err != nil || (ownerCheck != userID && !contextIsAdmin(c)) {
+	if err != nil || (ownerCheck.String() != userID && !contextIsAdmin(c)) {
 		c.JSON(http.StatusForbidden, gin.H{"error": "Access denied"})
 		return
 	}
@@ -178,11 +211,20 @@ func handleCreateCronJob(c *gin.Context) {
 		UpdatedAt: time.Now(),
 	}
 
-	_, err = db.Exec(
-		`INSERT INTO cron_jobs (id, project_id, service_id, name, schedule, command, timezone, enabled, next_run_at, retention, created_at, updated_at)
-		 VALUES ($1, $2, $3, $4, $5, $6, $7, $8, $9, $10, $11, $12)`,
-		job.ID, job.ProjectID, job.ServiceID, job.Name, job.Schedule, job.Command, job.Timezone, job.Enabled, job.NextRunAt, job.Retention, job.CreatedAt, job.UpdatedAt,
-	)
+	err = sqlcdb.New(db.DB).InsertCronJob(context.Background(), sqlcdb.InsertCronJobParams{
+		ID:        uuid.MustParse(job.ID),
+		ProjectID: uuid.MustParse(job.ProjectID),
+		ServiceID: uuid.MustParse(job.ServiceID),
+		Name:      job.Name,
+		Schedule:  job.Schedule,
+		Command:   job.Command,
+		Timezone:  sql.NullString{String: job.Timezone, Valid: true},
+		Enabled:   sql.NullBool{Bool: job.Enabled, Valid: true},
+		NextRunAt: ntPtr(job.NextRunAt),
+		Retention: sql.NullInt32{Int32: int32(job.Retention), Valid: true},
+		CreatedAt: sql.NullTime{Time: job.CreatedAt, Valid: true},
+		UpdatedAt: sql.NullTime{Time: job.UpdatedAt, Valid: true},
+	})
 
 	if err != nil {
 		c.JSON(http.StatusInternalServerError, gin.H{"error": "Failed to create cron job"})
@@ -201,22 +243,17 @@ func handleGetCronJob(c *gin.Context) {
 	db := c.MustGet("db").(*database.DB)
 	jobID := c.Param("id")
 
-	var job CronJob
-	err := db.QueryRow(
-		`SELECT cj.id, cj.project_id, cj.service_id, cj.name, cj.schedule, cj.timezone,
-		        cj.enabled, cj.last_run_at, cj.next_run_at, cj.last_status, cj.last_output,
-		        cj.retention, cj.created_at, cj.updated_at
-		 FROM cron_jobs cj
-		 WHERE cj.id = $1`,
-		jobID,
-	).Scan(&job.ID, &job.ProjectID, &job.ServiceID, &job.Name, &job.Schedule, &job.Timezone,
-		&job.Enabled, &job.LastRunAt, &job.NextRunAt, &job.LastStatus, &job.LastOutput,
-		&job.Retention, &job.CreatedAt, &job.UpdatedAt)
-
+	jid, err := uuid.Parse(jobID)
 	if err != nil {
 		c.JSON(http.StatusNotFound, gin.H{"error": "Cron job not found"})
 		return
 	}
+	row, err := sqlcdb.New(db.DB).GetCronJob(context.Background(), jid)
+	if err != nil {
+		c.JSON(http.StatusNotFound, gin.H{"error": "Cron job not found"})
+		return
+	}
+	job := cronJobFrom(row)
 
 	projectID, found := cronJobProjectID(db, jobID)
 	if !found {
@@ -242,15 +279,14 @@ func handleUpdateCronJob(c *gin.Context) {
 		return
 	}
 
-	var ownerCheck string
-	err := db.QueryRow(
-		`SELECT p.owner_id FROM cron_jobs cj
-		 JOIN projects p ON cj.project_id = p.id
-		 WHERE cj.id = $1`,
-		jobID,
-	).Scan(&ownerCheck)
+	jid, err := uuid.Parse(jobID)
+	if err != nil {
+		c.JSON(http.StatusForbidden, gin.H{"error": "Access denied"})
+		return
+	}
+	ownerCheck, err := sqlcdb.New(db.DB).GetCronJobOwner(context.Background(), jid)
 
-	if err != nil || (ownerCheck != userID && !contextIsAdmin(c)) {
+	if err != nil || (ownerCheck.String() != userID && !contextIsAdmin(c)) {
 		c.JSON(http.StatusForbidden, gin.H{"error": "Access denied"})
 		return
 	}
@@ -312,21 +348,19 @@ func handleDeleteCronJob(c *gin.Context) {
 	db := c.MustGet("db").(*database.DB)
 	jobID := c.Param("id")
 
-	var ownerCheck string
-	err := db.QueryRow(
-		`SELECT p.owner_id FROM cron_jobs cj
-		 JOIN projects p ON cj.project_id = p.id
-		 WHERE cj.id = $1`,
-		jobID,
-	).Scan(&ownerCheck)
+	jid, err := uuid.Parse(jobID)
+	if err != nil {
+		c.JSON(http.StatusForbidden, gin.H{"error": "Access denied"})
+		return
+	}
+	ownerCheck, err := sqlcdb.New(db.DB).GetCronJobOwner(context.Background(), jid)
 
-	if err != nil || (ownerCheck != userID && !contextIsAdmin(c)) {
+	if err != nil || (ownerCheck.String() != userID && !contextIsAdmin(c)) {
 		c.JSON(http.StatusForbidden, gin.H{"error": "Access denied"})
 		return
 	}
 
-	_, err = db.Exec("DELETE FROM cron_jobs WHERE id = $1", jobID)
-	if err != nil {
+	if err := sqlcdb.New(db.DB).DeleteCronJob(context.Background(), jid); err != nil {
 		c.JSON(http.StatusInternalServerError, gin.H{"error": "Failed to delete cron job"})
 		return
 	}
@@ -350,29 +384,20 @@ func handleGetCronExecutions(c *gin.Context) {
 		return
 	}
 
-	rows, err := db.Query(
-		`SELECT id, cron_job_id, started_at, finished_at, status, output, error
-		 FROM cron_executions 
-		 WHERE cron_job_id = $1 
-		 ORDER BY started_at DESC 
-		 LIMIT 100`,
-		jobID,
-	)
-
+	jid, err := uuid.Parse(jobID)
+	if err != nil {
+		c.JSON(http.StatusInternalServerError, gin.H{"error": "Failed to fetch executions"})
+		return
+	}
+	rows, err := sqlcdb.New(db.DB).ListCronExecutions(context.Background(), jid)
 	if err != nil {
 		c.JSON(http.StatusInternalServerError, gin.H{"error": "Failed to fetch executions"})
 		return
 	}
-	defer rows.Close()
 
 	var executions []CronExecution
-	for rows.Next() {
-		var exec CronExecution
-		err := rows.Scan(&exec.ID, &exec.CronJobID, &exec.StartedAt, &exec.FinishedAt, &exec.Status, &exec.Output, &exec.Error)
-		if err != nil {
-			continue
-		}
-		executions = append(executions, exec)
+	for _, e := range rows {
+		executions = append(executions, cronExecutionFrom(e))
 	}
 
 	c.JSON(http.StatusOK, gin.H{"executions": executions})
@@ -383,29 +408,33 @@ func handleTriggerCronJob(c *gin.Context) {
 	db := c.MustGet("db").(*database.DB)
 	jobID := c.Param("id")
 
-	var job CronJob
-	var ownerCheck string
-	err := db.QueryRow(
-		`SELECT cj.service_id, cj.command, cj.schedule, cj.timezone, cj.retention, p.owner_id
-		 FROM cron_jobs cj
-		 JOIN projects p ON cj.project_id = p.id
-		 WHERE cj.id = $1`,
-		jobID,
-	).Scan(&job.ServiceID, &job.Command, &job.Schedule, &job.Timezone, &job.Retention, &ownerCheck)
-
-	if err != nil || (ownerCheck != userID && !contextIsAdmin(c)) {
+	jid, err := uuid.Parse(jobID)
+	if err != nil {
 		c.JSON(http.StatusForbidden, gin.H{"error": "Access denied"})
 		return
 	}
+	trig, err := sqlcdb.New(db.DB).GetCronJobForTrigger(context.Background(), jid)
+	if err != nil || (trig.OwnerID.String() != userID && !contextIsAdmin(c)) {
+		c.JSON(http.StatusForbidden, gin.H{"error": "Access denied"})
+		return
+	}
+	job := CronJob{
+		ServiceID: trig.ServiceID.String(),
+		Command:   trig.Command,
+		Schedule:  trig.Schedule,
+		Timezone:  trig.Timezone.String,
+		Retention: int(trig.Retention.Int32),
+	}
 
 	execID := uuid.New().String()
 	now := time.Now()
 
-	_, err = db.Exec(
-		`INSERT INTO cron_executions (id, cron_job_id, started_at, status)
-		 VALUES ($1, $2, $3, $4)`,
-		execID, jobID, now, "running",
-	)
+	_ = sqlcdb.New(db.DB).InsertCronExecution(context.Background(), sqlcdb.InsertCronExecutionParams{
+		ID:        uuid.MustParse(execID),
+		CronJobID: jid,
+		StartedAt: now,
+		Status:    sql.NullString{String: "running", Valid: true},
+	})
 
 	dockerClient, _ := c.Get("docker_client")
 	dockerCli, _ := dockerClient.(*docker.Client)
@@ -458,39 +487,25 @@ func StartCronScheduler(ctx context.Context, db *database.DB, dockerClient *dock
 }
 
 func runDueCronJobs(db *database.DB, dockerClient *docker.Client) {
-	rows, err := db.Query(
-		`SELECT id, project_id, service_id, name, schedule, command, timezone, enabled, retention
-		 FROM cron_jobs
-		 WHERE enabled = TRUE AND next_run_at IS NOT NULL AND next_run_at <= NOW()`)
+	q := sqlcdb.New(db.DB)
+	jobs, err := q.ListDueCronJobs(context.Background())
 	if err != nil {
 		log.Printf("cron scheduler: failed to list due jobs: %v", err)
 		return
 	}
-	defer rows.Close()
-
-	type dueJob struct {
-		CronJob
-		Command string
-	}
-	var jobs []dueJob
-	for rows.Next() {
-		var j dueJob
-		if err := rows.Scan(&j.ID, &j.ProjectID, &j.ServiceID, &j.Name, &j.Schedule,
-			&j.Command, &j.Timezone, &j.Enabled, &j.Retention); err != nil {
-			continue
-		}
-		jobs = append(jobs, j)
-	}
 
 	for _, j := range jobs {
 		execID := uuid.New().String()
-		if _, err := db.Exec(
-			`INSERT INTO cron_executions (id, cron_job_id, started_at, status) VALUES ($1, $2, $3, $4)`,
-			execID, j.ID, time.Now(), "running"); err != nil {
+		if err := q.InsertCronExecution(context.Background(), sqlcdb.InsertCronExecutionParams{
+			ID:        uuid.MustParse(execID),
+			CronJobID: j.ID,
+			StartedAt: time.Now(),
+			Status:    sql.NullString{String: "running", Valid: true},
+		}); err != nil {
 			log.Printf("cron scheduler: failed to record execution for %s: %v", j.ID, err)
 			continue
 		}
-		go executeCronJob(db, dockerClient, j.ID, j.ServiceID, execID, j.Command, j.Schedule, j.Timezone, j.Retention)
+		go executeCronJob(db, dockerClient, j.ID.String(), j.ServiceID.String(), execID, j.Command, j.Schedule, j.Timezone.String, int(j.Retention.Int32))
 	}
 }
 
@@ -584,23 +599,32 @@ func executeCronJob(db *database.DB, dockerClient *docker.Client, jobID, service
 	}
 
 	now := time.Now()
-	db.Exec(
-		`UPDATE cron_executions SET finished_at = $1, status = $2, output = $3, error = $4 WHERE id = $5`,
-		now, status, output, errText, execID,
-	)
+	q := sqlcdb.New(db.DB)
+	if eid, err := uuid.Parse(execID); err == nil {
+		_ = q.FinishCronExecution(context.Background(), sqlcdb.FinishCronExecutionParams{
+			FinishedAt: sql.NullTime{Time: now, Valid: true},
+			Status:     sql.NullString{String: status, Valid: true},
+			Output:     sql.NullString{String: output, Valid: true},
+			Error:      sql.NullString{String: errText, Valid: true},
+			ID:         eid,
+		})
+	}
 
 	next, _ := calculateNextRun(schedule, timezone)
-	db.Exec(
-		`UPDATE cron_jobs SET last_run_at = $1, last_status = $2, last_output = $3, next_run_at = $4 WHERE id = $5`,
-		now, status, output, next, jobID,
-	)
+	if jid, err := uuid.Parse(jobID); err == nil {
+		_ = q.UpdateCronJobRun(context.Background(), sqlcdb.UpdateCronJobRunParams{
+			LastRunAt:  sql.NullTime{Time: now, Valid: true},
+			LastStatus: sql.NullString{String: status, Valid: true},
+			LastOutput: sql.NullString{String: output, Valid: true},
+			NextRunAt:  ntPtr(next),
+			ID:         jid,
+		})
 
-	if retention > 0 {
-		db.Exec(
-			`DELETE FROM cron_executions WHERE cron_job_id = $1 AND id NOT IN (
-				SELECT id FROM cron_executions WHERE cron_job_id = $1
-				ORDER BY started_at DESC LIMIT $2)`,
-			jobID, retention,
-		)
+		if retention > 0 {
+			_ = q.TrimCronExecutions(context.Background(), sqlcdb.TrimCronExecutionsParams{
+				CronJobID: jid,
+				Limit:     int32(retention),
+			})
+		}
 	}
 }

app/backend/internal/api/cron_test.go

@@ -1,9 +1,16 @@
 package api
 
 import (
+	"context"
+	"database/sql"
 	"strings"
 	"testing"
 	"time"
+
+	"containr/internal/database"
+	"containr/internal/database/sqlcdb"
+
+	"github.com/google/uuid"
 )
 
 func TestCalculateNextRun(t *testing.T) {
@@ -64,3 +71,123 @@ func TestCalculateNextRunTimezone(t *testing.T) {
 		t.Logf("utc next: %v / tokyo next: %v", utcNext, tokyoNext)
 	}
 }
+
+// TestCronLiveInvariants exercises the cron.sql queries on live Postgres:
+// insert/read/owner join, execution lifecycle, run bookkeeping, retention trim.
+func TestCronLiveInvariants(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()
+	jobID := uuid.New()
+	suffix := strings.ReplaceAll(jobID.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, "cron-"+suffix+"@test.local")
+	must(err)
+	_, err = db.Exec(`INSERT INTO projects (id, name, owner_id, is_approved) VALUES ($1, $2, $3, true)`,
+		projectID, "cron-"+suffix, userID)
+	must(err)
+	_, err = db.Exec(`INSERT INTO environments (id, name, project_id) VALUES ($1, 'production', $2)`,
+		envID, projectID)
+	must(err)
+	_, err = db.Exec(`INSERT INTO services (id, name, project_id, environment_id, service_type, source_type)
+		VALUES ($1, $2, $3, $4, 'web', 'image')`,
+		serviceID, "cron-"+suffix, projectID, envID)
+	must(err)
+	defer func() { _, _ = db.Exec(`DELETE FROM users WHERE id = $1`, userID) }()
+
+	next, err := calculateNextRun("0 4 * * *", "UTC")
+	if err != nil {
+		t.Fatalf("schedule: %v", err)
+	}
+	must(q.InsertCronJob(ctx, sqlcdb.InsertCronJobParams{
+		ID: jobID, ProjectID: projectID, ServiceID: serviceID,
+		Name: "job-" + suffix, Schedule: "0 4 * * *", Command: "echo hi",
+		Timezone:  sql.NullString{String: "UTC", Valid: true},
+		Enabled:   sql.NullBool{Bool: true, Valid: true},
+		NextRunAt: ntPtr(next),
+	}))
+	job, err := q.GetCronJob(ctx, jobID)
+	if err != nil || job.Name != "job-"+suffix || job.Command != "echo hi" {
+		t.Fatalf("job: %v %+v", err, job)
+	}
+	owner, err := q.GetCronJobOwner(ctx, jobID)
+	if err != nil || owner != userID {
+		t.Fatalf("owner: %v %v", err, owner)
+	}
+	owner2, err := q.GetCronOwnerViaService(ctx, serviceID)
+	if err != nil || owner2 != userID {
+		t.Fatalf("owner via service: %v %v", err, owner2)
+	}
+	pid, err := q.GetCronJobProjectID(ctx, jobID)
+	if err != nil || pid != projectID {
+		t.Fatalf("project: %v %v", err, pid)
+	}
+
+	// Trigger-shaped read.
+	trig, err := q.GetCronJobForTrigger(ctx, jobID)
+	if err != nil || trig.ServiceID != serviceID || trig.OwnerID != userID {
+		t.Fatalf("trigger: %v %+v", err, trig)
+	}
+
+	// Execution lifecycle + retention trim.
+	for i := 0; i < 3; i++ {
+		execID := uuid.New()
+		must(q.InsertCronExecution(ctx, sqlcdb.InsertCronExecutionParams{
+			ID: execID, CronJobID: jobID,
+			StartedAt: time.Now().Add(time.Duration(i) * time.Second),
+			Status:    sql.NullString{String: "running", Valid: true},
+		}))
+		must(q.FinishCronExecution(ctx, sqlcdb.FinishCronExecutionParams{
+			FinishedAt: sql.NullTime{Time: time.Now(), Valid: true},
+			Status:     sql.NullString{String: "success", Valid: true},
+			Output:     sql.NullString{String: "ok", Valid: true},
+			Error:      sql.NullString{String: "", Valid: true},
+			ID:         execID,
+		}))
+	}
+	execs, err := q.ListCronExecutions(ctx, jobID)
+	if err != nil || len(execs) != 3 {
+		t.Fatalf("execs: %v %d", err, len(execs))
+	}
+	if execs[0].Status.String != "success" || !execs[0].FinishedAt.Valid {
+		t.Fatalf("exec row: %+v", execs[0])
+	}
+	must(q.TrimCronExecutions(ctx, sqlcdb.TrimCronExecutionsParams{CronJobID: jobID, Limit: 1}))
+	execs, _ = q.ListCronExecutions(ctx, jobID)
+	if len(execs) != 1 {
+		t.Fatalf("trim kept %d, want 1", len(execs))
+	}
+
+	must(q.UpdateCronJobRun(ctx, sqlcdb.UpdateCronJobRunParams{
+		LastRunAt:  sql.NullTime{Time: time.Now(), Valid: true},
+		LastStatus: sql.NullString{String: "success", Valid: true},
+		LastOutput: sql.NullString{String: "ok", Valid: true},
+		NextRunAt:  ntPtr(next),
+		ID:         jobID,
+	}))
+	job, _ = q.GetCronJob(ctx, jobID)
+	if job.LastStatus.String != "success" || !job.LastRunAt.Valid {
+		t.Fatalf("post-run job: %+v", job)
+	}
+
+	must(q.DeleteCronJob(ctx, jobID))
+	if _, err := q.GetCronJob(ctx, jobID); err == nil {
+		t.Fatal("deleted job still readable")
+	}
+}

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,47 +675,34 @@ 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 {
@@ -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
-			}
-			return false
+			port, err := q.GetServicePort(context.Background(), id)
+			return err == nil && port > 0
 		}
-		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/cron.sql.go

@@ -0,0 +1,332 @@
+// Code generated by sqlc. DO NOT EDIT.
+// versions:
+//   sqlc v1.31.1
+// source: cron.sql
+
+package sqlcdb
+
+import (
+	"context"
+	"database/sql"
+	"time"
+
+	"github.com/google/uuid"
+)
+
+const deleteCronJob = `-- name: DeleteCronJob :exec
+DELETE FROM cron_jobs WHERE id = $1
+`
+
+func (q *Queries) DeleteCronJob(ctx context.Context, id uuid.UUID) error {
+	_, err := q.db.ExecContext(ctx, deleteCronJob, id)
+	return err
+}
+
+const finishCronExecution = `-- name: FinishCronExecution :exec
+UPDATE cron_executions SET finished_at = $1, status = $2, output = $3, error = $4 WHERE id = $5
+`
+
+type FinishCronExecutionParams struct {
+	FinishedAt sql.NullTime   `json:"finished_at"`
+	Status     sql.NullString `json:"status"`
+	Output     sql.NullString `json:"output"`
+	Error      sql.NullString `json:"error"`
+	ID         uuid.UUID      `json:"id"`
+}
+
+func (q *Queries) FinishCronExecution(ctx context.Context, arg FinishCronExecutionParams) error {
+	_, err := q.db.ExecContext(ctx, finishCronExecution,
+		arg.FinishedAt,
+		arg.Status,
+		arg.Output,
+		arg.Error,
+		arg.ID,
+	)
+	return err
+}
+
+const getCronJob = `-- name: GetCronJob :one
+SELECT id, project_id, service_id, name, schedule, command, timezone,
+       enabled, last_run_at, next_run_at, last_status, last_output,
+       retention, created_at, updated_at
+FROM cron_jobs WHERE id = $1
+`
+
+func (q *Queries) GetCronJob(ctx context.Context, id uuid.UUID) (CronJob, error) {
+	row := q.db.QueryRowContext(ctx, getCronJob, id)
+	var i CronJob
+	err := row.Scan(
+		&i.ID,
+		&i.ProjectID,
+		&i.ServiceID,
+		&i.Name,
+		&i.Schedule,
+		&i.Command,
+		&i.Timezone,
+		&i.Enabled,
+		&i.LastRunAt,
+		&i.NextRunAt,
+		&i.LastStatus,
+		&i.LastOutput,
+		&i.Retention,
+		&i.CreatedAt,
+		&i.UpdatedAt,
+	)
+	return i, err
+}
+
+const getCronJobForTrigger = `-- name: GetCronJobForTrigger :one
+SELECT cj.service_id, cj.command, cj.schedule, cj.timezone, cj.retention, p.owner_id
+FROM cron_jobs cj
+JOIN projects p ON cj.project_id = p.id
+WHERE cj.id = $1
+`
+
+type GetCronJobForTriggerRow struct {
+	ServiceID uuid.UUID      `json:"service_id"`
+	Command   string         `json:"command"`
+	Schedule  string         `json:"schedule"`
+	Timezone  sql.NullString `json:"timezone"`
+	Retention sql.NullInt32  `json:"retention"`
+	OwnerID   uuid.UUID      `json:"owner_id"`
+}
+
+func (q *Queries) GetCronJobForTrigger(ctx context.Context, id uuid.UUID) (GetCronJobForTriggerRow, error) {
+	row := q.db.QueryRowContext(ctx, getCronJobForTrigger, id)
+	var i GetCronJobForTriggerRow
+	err := row.Scan(
+		&i.ServiceID,
+		&i.Command,
+		&i.Schedule,
+		&i.Timezone,
+		&i.Retention,
+		&i.OwnerID,
+	)
+	return i, err
+}
+
+const getCronJobOwner = `-- name: GetCronJobOwner :one
+SELECT p.owner_id FROM cron_jobs cj
+JOIN projects p ON cj.project_id = p.id
+WHERE cj.id = $1
+`
+
+func (q *Queries) GetCronJobOwner(ctx context.Context, id uuid.UUID) (uuid.UUID, error) {
+	row := q.db.QueryRowContext(ctx, getCronJobOwner, id)
+	var owner_id uuid.UUID
+	err := row.Scan(&owner_id)
+	return owner_id, err
+}
+
+const getCronJobProjectID = `-- name: GetCronJobProjectID :one
+SELECT project_id FROM cron_jobs WHERE id = $1
+`
+
+func (q *Queries) GetCronJobProjectID(ctx context.Context, id uuid.UUID) (uuid.UUID, error) {
+	row := q.db.QueryRowContext(ctx, getCronJobProjectID, id)
+	var project_id uuid.UUID
+	err := row.Scan(&project_id)
+	return project_id, err
+}
+
+const getCronOwnerViaService = `-- name: GetCronOwnerViaService :one
+SELECT p.owner_id FROM projects p
+JOIN services s ON s.project_id = p.id
+WHERE s.id = $1
+`
+
+func (q *Queries) GetCronOwnerViaService(ctx context.Context, id uuid.UUID) (uuid.UUID, error) {
+	row := q.db.QueryRowContext(ctx, getCronOwnerViaService, id)
+	var owner_id uuid.UUID
+	err := row.Scan(&owner_id)
+	return owner_id, err
+}
+
+const insertCronExecution = `-- name: InsertCronExecution :exec
+INSERT INTO cron_executions (id, cron_job_id, started_at, status)
+VALUES ($1, $2, $3, $4)
+`
+
+type InsertCronExecutionParams struct {
+	ID        uuid.UUID      `json:"id"`
+	CronJobID uuid.UUID      `json:"cron_job_id"`
+	StartedAt time.Time      `json:"started_at"`
+	Status    sql.NullString `json:"status"`
+}
+
+func (q *Queries) InsertCronExecution(ctx context.Context, arg InsertCronExecutionParams) error {
+	_, err := q.db.ExecContext(ctx, insertCronExecution,
+		arg.ID,
+		arg.CronJobID,
+		arg.StartedAt,
+		arg.Status,
+	)
+	return err
+}
+
+const insertCronJob = `-- name: InsertCronJob :exec
+INSERT INTO cron_jobs (id, project_id, service_id, name, schedule, command, timezone, enabled, next_run_at, retention, created_at, updated_at)
+VALUES ($1, $2, $3, $4, $5, $6, $7, $8, $9, $10, $11, $12)
+`
+
+type InsertCronJobParams struct {
+	ID        uuid.UUID      `json:"id"`
+	ProjectID uuid.UUID      `json:"project_id"`
+	ServiceID uuid.UUID      `json:"service_id"`
+	Name      string         `json:"name"`
+	Schedule  string         `json:"schedule"`
+	Command   string         `json:"command"`
+	Timezone  sql.NullString `json:"timezone"`
+	Enabled   sql.NullBool   `json:"enabled"`
+	NextRunAt sql.NullTime   `json:"next_run_at"`
+	Retention sql.NullInt32  `json:"retention"`
+	CreatedAt sql.NullTime   `json:"created_at"`
+	UpdatedAt sql.NullTime   `json:"updated_at"`
+}
+
+func (q *Queries) InsertCronJob(ctx context.Context, arg InsertCronJobParams) error {
+	_, err := q.db.ExecContext(ctx, insertCronJob,
+		arg.ID,
+		arg.ProjectID,
+		arg.ServiceID,
+		arg.Name,
+		arg.Schedule,
+		arg.Command,
+		arg.Timezone,
+		arg.Enabled,
+		arg.NextRunAt,
+		arg.Retention,
+		arg.CreatedAt,
+		arg.UpdatedAt,
+	)
+	return err
+}
+
+const listCronExecutions = `-- name: ListCronExecutions :many
+SELECT id, cron_job_id, started_at, finished_at, status, output, error
+FROM cron_executions
+WHERE cron_job_id = $1
+ORDER BY started_at DESC
+LIMIT 100
+`
+
+func (q *Queries) ListCronExecutions(ctx context.Context, cronJobID uuid.UUID) ([]CronExecution, error) {
+	rows, err := q.db.QueryContext(ctx, listCronExecutions, cronJobID)
+	if err != nil {
+		return nil, err
+	}
+	defer rows.Close()
+	items := []CronExecution{}
+	for rows.Next() {
+		var i CronExecution
+		if err := rows.Scan(
+			&i.ID,
+			&i.CronJobID,
+			&i.StartedAt,
+			&i.FinishedAt,
+			&i.Status,
+			&i.Output,
+			&i.Error,
+		); 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 listDueCronJobs = `-- name: ListDueCronJobs :many
+SELECT id, project_id, service_id, name, schedule, command, timezone, enabled, retention
+FROM cron_jobs
+WHERE enabled = TRUE AND next_run_at IS NOT NULL AND next_run_at <= NOW()
+`
+
+type ListDueCronJobsRow struct {
+	ID        uuid.UUID      `json:"id"`
+	ProjectID uuid.UUID      `json:"project_id"`
+	ServiceID uuid.UUID      `json:"service_id"`
+	Name      string         `json:"name"`
+	Schedule  string         `json:"schedule"`
+	Command   string         `json:"command"`
+	Timezone  sql.NullString `json:"timezone"`
+	Enabled   sql.NullBool   `json:"enabled"`
+	Retention sql.NullInt32  `json:"retention"`
+}
+
+func (q *Queries) ListDueCronJobs(ctx context.Context) ([]ListDueCronJobsRow, error) {
+	rows, err := q.db.QueryContext(ctx, listDueCronJobs)
+	if err != nil {
+		return nil, err
+	}
+	defer rows.Close()
+	items := []ListDueCronJobsRow{}
+	for rows.Next() {
+		var i ListDueCronJobsRow
+		if err := rows.Scan(
+			&i.ID,
+			&i.ProjectID,
+			&i.ServiceID,
+			&i.Name,
+			&i.Schedule,
+			&i.Command,
+			&i.Timezone,
+			&i.Enabled,
+			&i.Retention,
+		); 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 trimCronExecutions = `-- name: TrimCronExecutions :exec
+DELETE FROM cron_executions WHERE cron_executions.cron_job_id = $1 AND cron_executions.id NOT IN (
+    SELECT ce.id FROM cron_executions ce WHERE ce.cron_job_id = $1
+    ORDER BY ce.started_at DESC LIMIT $2)
+`
+
+type TrimCronExecutionsParams struct {
+	CronJobID uuid.UUID `json:"cron_job_id"`
+	Limit     int32     `json:"limit"`
+}
+
+func (q *Queries) TrimCronExecutions(ctx context.Context, arg TrimCronExecutionsParams) error {
+	_, err := q.db.ExecContext(ctx, trimCronExecutions, arg.CronJobID, arg.Limit)
+	return err
+}
+
+const updateCronJobRun = `-- name: UpdateCronJobRun :exec
+UPDATE cron_jobs SET last_run_at = $1, last_status = $2, last_output = $3, next_run_at = $4 WHERE id = $5
+`
+
+type UpdateCronJobRunParams struct {
+	LastRunAt  sql.NullTime   `json:"last_run_at"`
+	LastStatus sql.NullString `json:"last_status"`
+	LastOutput sql.NullString `json:"last_output"`
+	NextRunAt  sql.NullTime   `json:"next_run_at"`
+	ID         uuid.UUID      `json:"id"`
+}
+
+func (q *Queries) UpdateCronJobRun(ctx context.Context, arg UpdateCronJobRunParams) error {
+	_, err := q.db.ExecContext(ctx, updateCronJobRun,
+		arg.LastRunAt,
+		arg.LastStatus,
+		arg.LastOutput,
+		arg.NextRunAt,
+		arg.ID,
+	)
+	return err
+}

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

@@ -98,6 +98,34 @@ type ContainerInstance struct {
 	UpdatedAt     sql.NullTime          `json:"updated_at"`
 }
 
+type CronExecution struct {
+	ID         uuid.UUID      `json:"id"`
+	CronJobID  uuid.UUID      `json:"cron_job_id"`
+	StartedAt  time.Time      `json:"started_at"`
+	FinishedAt sql.NullTime   `json:"finished_at"`
+	Status     sql.NullString `json:"status"`
+	Output     sql.NullString `json:"output"`
+	Error      sql.NullString `json:"error"`
+}
+
+type CronJob struct {
+	ID         uuid.UUID      `json:"id"`
+	ProjectID  uuid.UUID      `json:"project_id"`
+	ServiceID  uuid.UUID      `json:"service_id"`
+	Name       string         `json:"name"`
+	Schedule   string         `json:"schedule"`
+	Command    string         `json:"command"`
+	Timezone   sql.NullString `json:"timezone"`
+	Enabled    sql.NullBool   `json:"enabled"`
+	LastRunAt  sql.NullTime   `json:"last_run_at"`
+	NextRunAt  sql.NullTime   `json:"next_run_at"`
+	LastStatus sql.NullString `json:"last_status"`
+	LastOutput sql.NullString `json:"last_output"`
+	Retention  sql.NullInt32  `json:"retention"`
+	CreatedAt  sql.NullTime   `json:"created_at"`
+	UpdatedAt  sql.NullTime   `json:"updated_at"`
+}
+
 type DatabaseBackup struct {
 	ID          string         `json:"id"`
 	DatabaseID  string         `json:"database_id"`

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

@@ -59,6 +59,7 @@ type Querier interface {
 	DeleteAllServiceDomains(ctx context.Context, serviceID uuid.UUID) error
 	DeleteBackupTargetByIDAndUser(ctx context.Context, arg DeleteBackupTargetByIDAndUserParams) error
 	DeleteBanner(ctx context.Context, id uuid.UUID) error
+	DeleteCronJob(ctx context.Context, id uuid.UUID) error
 	DeleteDatabaseServiceByID(ctx context.Context, id string) error
 	DeleteDatabaseServiceByIDAndUser(ctx context.Context, arg DeleteDatabaseServiceByIDAndUserParams) error
 	DeleteNotificationChannel(ctx context.Context, arg DeleteNotificationChannelParams) (int64, error)
@@ -72,6 +73,7 @@ type Querier interface {
 	DeleteUserInvite(ctx context.Context, id uuid.UUID) error
 	DeleteUserTemplate(ctx context.Context, arg DeleteUserTemplateParams) (int64, error)
 	FailDeployment(ctx context.Context, arg FailDeploymentParams) error
+	FinishCronExecution(ctx context.Context, arg FinishCronExecutionParams) error
 	GetActiveAgentAuthTokenByHash(ctx context.Context, tokenHash string) (AgentAuthToken, error)
 	GetAgent(ctx context.Context, id string) (NodeAgent, error)
 	GetAgentByHostAndIP(ctx context.Context, arg GetAgentByHostAndIPParams) (NodeAgent, error)
@@ -81,6 +83,11 @@ type Querier interface {
 	GetCommandForAgent(ctx context.Context, arg GetCommandForAgentParams) (AgentCommand, error)
 	GetContainer(ctx context.Context, id string) (ContainerInstance, error)
 	GetContainerForAgent(ctx context.Context, arg GetContainerForAgentParams) (ContainerInstance, error)
+	GetCronJob(ctx context.Context, id uuid.UUID) (CronJob, error)
+	GetCronJobForTrigger(ctx context.Context, id uuid.UUID) (GetCronJobForTriggerRow, error)
+	GetCronJobOwner(ctx context.Context, id uuid.UUID) (uuid.UUID, error)
+	GetCronJobProjectID(ctx context.Context, id uuid.UUID) (uuid.UUID, error)
+	GetCronOwnerViaService(ctx context.Context, id uuid.UUID) (uuid.UUID, error)
 	GetDatabaseBackupByIDAndDatabaseAndUser(ctx context.Context, arg GetDatabaseBackupByIDAndDatabaseAndUserParams) (DatabaseBackup, error)
 	GetDatabaseServiceByID(ctx context.Context, id string) (DatabaseService, error)
 	GetDatabaseServiceByIDAndUser(ctx context.Context, arg GetDatabaseServiceByIDAndUserParams) (DatabaseService, error)
@@ -89,6 +96,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,12 +118,17 @@ 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
+	InsertCronExecution(ctx context.Context, arg InsertCronExecutionParams) error
+	InsertCronJob(ctx context.Context, arg InsertCronJobParams) error
 	InsertDeployment(ctx context.Context, arg InsertDeploymentParams) error
 	InsertNotificationChannel(ctx context.Context, arg InsertNotificationChannelParams) (NotificationChannel, error)
 	InsertPendingDeployment(ctx context.Context, arg InsertPendingDeploymentParams) error
@@ -130,14 +143,17 @@ type Querier interface {
 	ListBanners(ctx context.Context) ([]Banner, error)
 	ListCommandsForAgent(ctx context.Context, nodeAgentID string) ([]AgentCommand, error)
 	ListContainersForAgent(ctx context.Context, nodeAgentID string) ([]ContainerInstance, error)
+	ListCronExecutions(ctx context.Context, cronJobID uuid.UUID) ([]CronExecution, error)
 	ListDatabaseBackupsByDatabaseAndUser(ctx context.Context, arg ListDatabaseBackupsByDatabaseAndUserParams) ([]DatabaseBackup, error)
 	ListDatabaseServicesByProject(ctx context.Context, projectID uuid.NullUUID) ([]DatabaseService, error)
 	ListDatabaseServicesByUser(ctx context.Context, userID string) ([]DatabaseService, error)
 	ListDeploymentsForService(ctx context.Context, arg ListDeploymentsForServiceParams) ([]ListDeploymentsForServiceRow, error)
+	ListDueCronJobs(ctx context.Context) ([]ListDueCronJobsRow, error)
 	ListDueDatabaseBackups(ctx context.Context) ([]ListDueDatabaseBackupsRow, error)
 	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 +162,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 +181,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,12 +218,15 @@ 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
 	TouchAgentAuthToken(ctx context.Context, tokenHash string) error
 	TouchServiceDomainChecked(ctx context.Context, id uuid.UUID) error
 	TouchUserToken(ctx context.Context, id uuid.UUID) error
+	TrimCronExecutions(ctx context.Context, arg TrimCronExecutionsParams) error
 	UpdateAgent(ctx context.Context, arg UpdateAgentParams) (NodeAgent, error)
 	UpdateAgentHeartbeat(ctx context.Context, arg UpdateAgentHeartbeatParams) error
 	UpdateBackupTargetByIDAndUser(ctx context.Context, arg UpdateBackupTargetByIDAndUserParams) error
@@ -217,6 +239,7 @@ type Querier interface {
 	// removed replica. $4 is the cumulative rx+tx byte counter.
 	UpdateContainerStateByName(ctx context.Context, arg UpdateContainerStateByNameParams) error
 	UpdateContainerStatus(ctx context.Context, arg UpdateContainerStatusParams) error
+	UpdateCronJobRun(ctx context.Context, arg UpdateCronJobRunParams) error
 	UpdateDatabaseServiceNameAndPlanByIDAndUser(ctx context.Context, arg UpdateDatabaseServiceNameAndPlanByIDAndUserParams) error
 	UpdateDatabaseServiceNameByIDAndUser(ctx context.Context, arg UpdateDatabaseServiceNameByIDAndUserParams) error
 	UpdateDatabaseServicePlanByIDAndUser(ctx context.Context, arg UpdateDatabaseServicePlanByIDAndUserParams) 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/cron.sql

@@ -0,0 +1,58 @@
+-- name: GetCronJobProjectID :one
+SELECT project_id FROM cron_jobs WHERE id = $1;
+
+-- name: GetCronOwnerViaService :one
+SELECT p.owner_id FROM projects p
+JOIN services s ON s.project_id = p.id
+WHERE s.id = $1;
+
+-- name: InsertCronJob :exec
+INSERT INTO cron_jobs (id, project_id, service_id, name, schedule, command, timezone, enabled, next_run_at, retention, created_at, updated_at)
+VALUES ($1, $2, $3, $4, $5, $6, $7, $8, $9, $10, $11, $12);
+
+-- name: GetCronJob :one
+SELECT id, project_id, service_id, name, schedule, command, timezone,
+       enabled, last_run_at, next_run_at, last_status, last_output,
+       retention, created_at, updated_at
+FROM cron_jobs WHERE id = $1;
+
+-- name: GetCronJobOwner :one
+SELECT p.owner_id FROM cron_jobs cj
+JOIN projects p ON cj.project_id = p.id
+WHERE cj.id = $1;
+
+-- name: DeleteCronJob :exec
+DELETE FROM cron_jobs WHERE id = $1;
+
+-- name: ListCronExecutions :many
+SELECT id, cron_job_id, started_at, finished_at, status, output, error
+FROM cron_executions
+WHERE cron_job_id = $1
+ORDER BY started_at DESC
+LIMIT 100;
+
+-- name: GetCronJobForTrigger :one
+SELECT cj.service_id, cj.command, cj.schedule, cj.timezone, cj.retention, p.owner_id
+FROM cron_jobs cj
+JOIN projects p ON cj.project_id = p.id
+WHERE cj.id = $1;
+
+-- name: InsertCronExecution :exec
+INSERT INTO cron_executions (id, cron_job_id, started_at, status)
+VALUES ($1, $2, $3, $4);
+
+-- name: ListDueCronJobs :many
+SELECT id, project_id, service_id, name, schedule, command, timezone, enabled, retention
+FROM cron_jobs
+WHERE enabled = TRUE AND next_run_at IS NOT NULL AND next_run_at <= NOW();
+
+-- name: FinishCronExecution :exec
+UPDATE cron_executions SET finished_at = $1, status = $2, output = $3, error = $4 WHERE id = $5;
+
+-- name: UpdateCronJobRun :exec
+UPDATE cron_jobs SET last_run_at = $1, last_status = $2, last_output = $3, next_run_at = $4 WHERE id = $5;
+
+-- name: TrimCronExecutions :exec
+DELETE FROM cron_executions WHERE cron_executions.cron_job_id = $1 AND cron_executions.id NOT IN (
+    SELECT ce.id FROM cron_executions ce WHERE ce.cron_job_id = $1
+    ORDER BY ce.started_at DESC LIMIT $2);

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;

app/backend/sqlc/schema.sql

@@ -459,3 +459,31 @@ CREATE TABLE instance_metrics (
     created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
     PRIMARY KEY (service_id, timestamp, instance_id)
 );
+
+CREATE TABLE cron_jobs (
+    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
+    project_id UUID NOT NULL REFERENCES projects(id) ON DELETE CASCADE,
+    service_id UUID NOT NULL REFERENCES services(id) ON DELETE CASCADE,
+    name VARCHAR(255) NOT NULL,
+    schedule VARCHAR(100) NOT NULL,
+    command TEXT NOT NULL,
+    timezone VARCHAR(50) DEFAULT 'UTC',
+    enabled BOOLEAN DEFAULT true,
+    last_run_at TIMESTAMP WITH TIME ZONE,
+    next_run_at TIMESTAMP WITH TIME ZONE,
+    last_status VARCHAR(50),
+    last_output TEXT,
+    retention INTEGER DEFAULT 30,
+    created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
+    updated_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
+);
+
+CREATE TABLE cron_executions (
+    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
+    cron_job_id UUID NOT NULL REFERENCES cron_jobs(id) ON DELETE CASCADE,
+    started_at TIMESTAMP WITH TIME ZONE NOT NULL,
+    finished_at TIMESTAMP WITH TIME ZONE,
+    status VARCHAR(50) DEFAULT 'pending',
+    output TEXT,
+    error TEXT
+);