stackrox · stehessel · Apr 14, 2026
diff --git a/central/graphql/resolvers/generated.go b/central/graphql/resolvers/generated.go
@@ -0,0 +1,34 @@
+package m223tom224
+
+import (
+	"context"
+
+	"github.com/stackrox/rox/migrator/migrations/m_223_to_m_224_add_deleted_at_index_and_set_deployment_state/schema"
+	"github.com/stackrox/rox/migrator/types"
+	"github.com/stackrox/rox/pkg/postgres/pgutils"
+	"github.com/stackrox/rox/pkg/sac"
+)
+
+const (
+	addIndexStmt       = "CREATE INDEX IF NOT EXISTS deployments_deletedat ON deployments (deletedat)"
+	setActiveStateStmt = "UPDATE deployments SET state = 1 WHERE state IS NULL OR state = 0"
+)
+
+func migrate(database *types.Databases) error {
+	ctx := sac.WithAllAccess(context.Background())
+
+	// Add deletedat and state columns if they do not already exist.
+	pgutils.CreateTableFromModel(ctx, database.GormDB, schema.CreateTableDeploymentsStmt)
+
+	// Add an index on deletedat for efficient soft-delete queries.
+	if _, err := database.PostgresDB.Exec(database.DBCtx, addIndexStmt); err != nil {
+		return err
+	}
+
+	// Set all existing deployments with STATE_UNSPECIFIED (0) to STATE_ACTIVE (1).
+	if _, err := database.PostgresDB.Exec(database.DBCtx, setActiveStateStmt); err != nil {
+		return err
+	}
+
+	return nil
+}
@@ -0,0 +1,116 @@
+//go:build sql_integration
+
+package m223tom224
+
+import (
+	"context"
+	"testing"
+
+	"github.com/stackrox/rox/generated/storage"
+	updatedSchema "github.com/stackrox/rox/migrator/migrations/m_223_to_m_224_add_deleted_at_index_and_set_deployment_state/schema"
+	oldSchema "github.com/stackrox/rox/migrator/migrations/m_223_to_m_224_add_deleted_at_index_and_set_deployment_state/test/schema"
+	pghelper "github.com/stackrox/rox/migrator/migrations/postgreshelper"
+	"github.com/stackrox/rox/migrator/types"
+	"github.com/stackrox/rox/pkg/postgres/pgutils"
+	"github.com/stackrox/rox/pkg/sac"
+	"github.com/stackrox/rox/pkg/uuid"
+	"github.com/stretchr/testify/suite"
+)
+
+type migrationTestSuite struct {
+	suite.Suite
+
+	db  *pghelper.TestPostgres
+	ctx context.Context
+}
+
+func TestMigration(t *testing.T) {
+	suite.Run(t, new(migrationTestSuite))
+}
+
+func (s *migrationTestSuite) SetupSuite() {
+	s.ctx = sac.WithAllAccess(context.Background())
+	s.db = pghelper.ForT(s.T(), false)
+}
+
+func (s *migrationTestSuite) TestMigration() {
+	db := s.db.DB
+	dbs := &types.Databases{
+		GormDB:     s.db.GetGormDB(),
+		PostgresDB: db,
+		DBCtx:      s.ctx,
+	}
+
+	// Create the old schema (without deletedat and state columns).
+	pgutils.CreateTableFromModel(s.ctx, dbs.GormDB, oldSchema.CreateTableDeploymentsStmt)
+
+	// Insert test deployments.
+	numDeployments := 5
+	deploymentIDs := make([]string, numDeployments)
+	for i := range numDeployments {
+		id := uuid.NewV4().String()
+		deploymentIDs[i] = id
+
+		dep := &storage.Deployment{Id: id, Name: "test-deployment"}
+		serialized, err := dep.MarshalVT()
+		s.Require().NoError(err)
+
+		_, err = db.Exec(s.ctx,
+			"INSERT INTO deployments (id, name, hash, type, namespace, namespaceid, orchestratorcomponent, created, clusterid, clustername, priority, serviceaccount, serviceaccountpermissionlevel, riskscore, platformcomponent, serialized) VALUES ($1, $2, 0, 'Deployment', 'default', $3, false, now(), $4, 'test-cluster', 0, 'default', 0, 0, false, $5)",
+			id, dep.GetName(), uuid.NewV4().String(), uuid.NewV4().String(), serialized,
+		)
+		s.Require().NoError(err)
+	}
+
+	// Apply the new schema to add deletedat and state columns.
+	pgutils.CreateTableFromModel(s.ctx, dbs.GormDB, updatedSchema.CreateTableDeploymentsStmt)
+
+	// Set half of the deployments to state = 0 (STATE_UNSPECIFIED) for testing.
+	// The other half remain NULL.
+	halfCount := numDeployments / 2
+	for i := 0; i < halfCount; i++ {
+		_, err := db.Exec(s.ctx, "UPDATE deployments SET state = 0 WHERE id = $1", deploymentIDs[i])
+		s.Require().NoError(err)
+	}
+
+	// Verify we have deployments with both NULL state and state = 0 before migration.
+	var nullCount int
+	err := db.QueryRow(s.ctx, "SELECT COUNT(*) FROM deployments WHERE state IS NULL").Scan(&nullCount)
+	s.Require().NoError(err)
+	s.Equal(numDeployments-halfCount, nullCount, "expected deployments with NULL state")
+
+	var zeroCount int
+	err = db.QueryRow(s.ctx, "SELECT COUNT(*) FROM deployments WHERE state = 0").Scan(&zeroCount)
+	s.Require().NoError(err)
+	s.Equal(halfCount, zeroCount, "expected deployments with state = 0")
+
+	// Run migration.
+	s.Require().NoError(migration.Run(dbs))
+
+	// Verify all deployments now have state = 1 (STATE_ACTIVE).
+	var activeCount int
+	err = db.QueryRow(s.ctx, "SELECT COUNT(*) FROM deployments WHERE state = 1").Scan(&activeCount)
+	s.Require().NoError(err)
+	s.Equal(numDeployments, activeCount)
+
+	// Verify no deployments have NULL state or state = 0 (STATE_UNSPECIFIED) after migration.
+	var unspecifiedCount int
+	err = db.QueryRow(s.ctx, "SELECT COUNT(*) FROM deployments WHERE state IS NULL OR state = 0").Scan(&unspecifiedCount)
+	s.Require().NoError(err)
+	s.Equal(0, unspecifiedCount)
+
+	// Verify index exists on deletedat.
+	var indexExists bool
+	err = db.QueryRow(s.ctx,
+		"SELECT EXISTS(SELECT 1 FROM pg_indexes WHERE tablename = 'deployments' AND indexname = 'deployments_deletedat')").Scan(&indexExists)
+	s.Require().NoError(err)
+	s.True(indexExists, "index deployments_deletedat should exist")
+
+	// Run migration again to verify idempotency.
+	s.Require().NoError(migration.Run(dbs))
+
+	// Verify state is still STATE_ACTIVE after second run.
+	err = db.QueryRow(s.ctx, "SELECT COUNT(*) FROM deployments WHERE state = 1").Scan(&activeCount)
+	s.Require().NoError(err)
+	s.Equal(numDeployments, activeCount)
+}
@@ -0,0 +1,50 @@
+// Code originally generated by pg-bindings generator.
+
+package schema
+
+import (
+	"time"
+
+	"github.com/lib/pq"
+	"github.com/stackrox/rox/generated/storage"
+	"github.com/stackrox/rox/pkg/postgres"
+)
+
+var (
+	// CreateTableDeploymentsStmt holds the create statement for table `deployments`.
+	CreateTableDeploymentsStmt = &postgres.CreateStmts{
+		GormModel: (*Deployments)(nil),
+		Children:  []*postgres.CreateStmts{},
+	}
+)
+
+const (
+	// DeploymentsTableName specifies the name of the table in postgres.
+	DeploymentsTableName = "deployments"
+)
+
+// Deployments holds the Gorm model for Postgres table `deployments`.
+type Deployments struct {
+	ID                            string                  `gorm:"column:id;type:uuid;primaryKey"`
+	Name                          string                  `gorm:"column:name;type:varchar"`
+	Hash                          uint64                  `gorm:"column:hash;type:numeric"`
+	Type                          string                  `gorm:"column:type;type:varchar"`
+	Namespace                     string                  `gorm:"column:namespace;type:varchar;index:deployments_sac_filter,type:btree"`
+	NamespaceID                   string                  `gorm:"column:namespaceid;type:uuid"`
+	OrchestratorComponent         bool                    `gorm:"column:orchestratorcomponent;type:bool"`
+	Labels                        map[string]string       `gorm:"column:labels;type:jsonb"`
+	PodLabels                     map[string]string       `gorm:"column:podlabels;type:jsonb"`
+	Created                       *time.Time              `gorm:"column:created;type:timestamp"`
+	ClusterID                     string                  `gorm:"column:clusterid;type:uuid;index:deployments_sac_filter,type:btree"`
+	ClusterName                   string                  `gorm:"column:clustername;type:varchar"`
+	Annotations                   map[string]string       `gorm:"column:annotations;type:jsonb"`
+	Priority                      int64                   `gorm:"column:priority;type:bigint"`
+	ImagePullSecrets              *pq.StringArray         `gorm:"column:imagepullsecrets;type:text[]"`
+	ServiceAccount                string                  `gorm:"column:serviceaccount;type:varchar"`
+	ServiceAccountPermissionLevel storage.PermissionLevel `gorm:"column:serviceaccountpermissionlevel;type:integer"`
+	RiskScore                     float32                 `gorm:"column:riskscore;type:numeric;index:deployments_riskscore,type:btree"`
+	PlatformComponent             bool                    `gorm:"column:platformcomponent;type:bool"`
+	DeletedAt                     *time.Time              `gorm:"column:deletedat;type:timestamp"`
+	State                         storage.DeploymentState `gorm:"column:state;type:integer"`
+	Serialized                    []byte                  `gorm:"column:serialized;type:bytea"`
+}
@@ -0,0 +1,44 @@
+// Code originally generated by pg-bindings generator.
+// This is the frozen schema before the migration (without deletedat and state columns).
+
+package schema
+
+import (
+	"time"
+
+	"github.com/lib/pq"
+	"github.com/stackrox/rox/generated/storage"
+	"github.com/stackrox/rox/pkg/postgres"
+)
+
+var (
+	// CreateTableDeploymentsStmt holds the create statement for table `deployments`.
+	CreateTableDeploymentsStmt = &postgres.CreateStmts{
+		GormModel: (*Deployments)(nil),
+		Children:  []*postgres.CreateStmts{},
+	}
+)
+
+// Deployments holds the Gorm model for Postgres table `deployments`.
+type Deployments struct {
+	ID                            string                  `gorm:"column:id;type:uuid;primaryKey"`
+	Name                          string                  `gorm:"column:name;type:varchar"`
+	Hash                          uint64                  `gorm:"column:hash;type:numeric"`
+	Type                          string                  `gorm:"column:type;type:varchar"`
+	Namespace                     string                  `gorm:"column:namespace;type:varchar;index:deployments_sac_filter,type:btree"`
+	NamespaceID                   string                  `gorm:"column:namespaceid;type:uuid"`
+	OrchestratorComponent         bool                    `gorm:"column:orchestratorcomponent;type:bool"`
+	Labels                        map[string]string       `gorm:"column:labels;type:jsonb"`
+	PodLabels                     map[string]string       `gorm:"column:podlabels;type:jsonb"`
+	Created                       *time.Time              `gorm:"column:created;type:timestamp"`
+	ClusterID                     string                  `gorm:"column:clusterid;type:uuid;index:deployments_sac_filter,type:btree"`
+	ClusterName                   string                  `gorm:"column:clustername;type:varchar"`
+	Annotations                   map[string]string       `gorm:"column:annotations;type:jsonb"`
+	Priority                      int64                   `gorm:"column:priority;type:bigint"`
+	ImagePullSecrets              *pq.StringArray         `gorm:"column:imagepullsecrets;type:text[]"`
+	ServiceAccount                string                  `gorm:"column:serviceaccount;type:varchar"`
+	ServiceAccountPermissionLevel storage.PermissionLevel `gorm:"column:serviceaccountpermissionlevel;type:integer"`
+	RiskScore                     float32                 `gorm:"column:riskscore;type:numeric;index:deployments_riskscore,type:btree"`
+	PlatformComponent             bool                    `gorm:"column:platformcomponent;type:bool"`
+	Serialized                    []byte                  `gorm:"column:serialized;type:bytea"`
+}
diff --git a/pkg/migrations/internal/seq_num.go b/pkg/migrations/internal/seq_num.go