Add Sql Fields

---
name: add-sql-fields
description: TRIGGER when user asks to add fields, properties, or columns to a SQL CRUD microservice's object. Affects the struct, SQL migration, column mapping, query filter, validation, and tests.
---

**CRITICAL**: Read and analyze this microservice before starting. Do NOT explore or analyze other microservices unless explicitly instructed to do so. The instructions in this skill are self-contained to this microservice.

**IMPORTANT**: `MyNoun`, `MyNounKey`, `mynoun`, and `mynounapi` are placeholders for the actual object, its key, directory, and API package of the microservice.

**IMPORTANT**: Do not remove the `Example` field or code related to it from the code since it is required by various tests.

## Workflow

Copy this checklist and track your progress:

```
Adding fields to the object:
- [ ] Step 1: Read Local CLAUDE.md File
- [ ] Step 2: Update the Type Definition of the Object
- [ ] Step 3: Update the Type Definition of the Query
- [ ] Step 4: Update Database Schema
- [ ] Step 5: Map Column Names to Object Fields
- [ ] Step 6: Add Query Conditions
- [ ] Step 7: Update Integration Tests
- [ ] Step 8: Housekeeping
```

#### Step 1: Read Local `CLAUDE.md` File

Read the local `CLAUDE.md` file in the microservice's directory. It contains microservice-specific instructions that should take precedence over global instructions.

#### Step 2: Update the Type Definition of the Object

Find the type definition of the object in `mynounapi/object.go`.
Add the new fields to the type definition of the struct.
Fields can be primitive or complex types that serialize to JSON.
Be sure to include a JSON tag. Use camelCase for the JSON name, and specify to `omitzero`.

When referring to an object persisted by another microservices, use its respective key.

```go
type MyNoun struct {
	Key MyNounKey `json:"key,omitzero"`

	// HINT: Define the fields of the object here
	MyFieldString   string             `json:"myFieldString,omitzero"`
	MyFieldInteger  int                `json:"myFieldInteger,omitzero"`
	MyFieldNullable string             `json:"myFieldNullable,omitzero"`
	MyFieldTime     time.Time          `json:"myFieldTime,omitzero"`
	MyFieldTags     map[string]string  `json:"myFieldTags,omitzero"`
	
	MyOtherObjectKey otherobjectapi.OtherObjectKey `json:"myOtherObjectKey,omitzero"`
}
```

Modify the object's `Validate` method appropriately to return an error if the values of the new fields do not meet the validation requirements. Be sure to strip strings of extra spaces using `strings.TrimSpace` if appropriate.

```go
// Validate validates the object before storing it.
func (obj *MyNoun) Validate(ctx context.Context) error {
	// ...

	// HINT: Validate the fields of the object here as required
	obj.MyFieldString = strings.TrimSpace(obj.MyFieldString)
	if len([]rune(obj.MyFieldString)) > 256 {
		return errors.New("length of MyFieldString must not exceed 256 characters")
	}
	if obj.MyFieldInteger < 0 {
		return errors.New("MyFieldInteger must not be negative")
	}
	if obj.MyFieldTime.After(time.Now()) {
		return errors.New("MyFieldTime must not be in the future")
	}
	if obj.MyOtherObjectKey.IsZero() {
		return errors.New("MyOtherObjectKey is required")
	}
	return nil
}
```

#### Step 3: Update the Type Definition of the Query

Find the type definition of the `Query` in `mynounapi/query.go`.
To allow filtering by the new fields, add them to the type definition of the struct.
Fields can be primitive or complex types that serialize to JSON.
Be sure to include a JSON tag. Use camelCase for the JSON name, and specify to `omitzero`.

When referring to a parent object that is persisted by another SQL CRUD microservices, use its respective key as the field type.

```go
type Query struct {
	Key MyNounKey `json:"key,omitzero"`

	// HINT: Define the fields of the object here
	MyFieldInteger  int       `json:"myFieldInteger,omitzero"`
	MyFieldNullable string    `json:"myFieldNullable,omitzero"`
	MyFieldTimeGTE  time.Time `json:"myFieldTimeStart,omitzero"`
	MyFieldTimeLT   time.Time `json:"myFieldTimeEnd,omitzero"`
	
	ParentKey parentapi.ParentKey `json:"parentKey,omitzero"`
}
```

Modify the `Query`'s `Validate` method appropriately to return an error if the values of the new fields do not meet the validation requirements. Be sure to strip strings of extra spaces using `strings.TrimSpace` if appropriate.

```go
// Validate validates the filtering options of the query.
func (q *Query) Validate(ctx context.Context) error {
	// ...

	// HINT: Validate filtering options here as required
	if q.MyFieldInteger < 0 {
		return errors.New("MyFieldInteger must not be negative")
	}
	q.MyFieldNullable = strings.TrimSpace(q.MyFieldNullable)
	if len([]rune(q.MyFieldNullable)) > 256 {
		return errors.New("length of MyFieldNullable must not exceed 256 characters")
	}
	if q.MyFieldTimeGTE.After(time.Now()) {
		return errors.New("MyFieldTimeGTE must not be in the future")
	}
	if q.MyFieldTimeLT.After(time.Now()) {
		return errors.New("MyFieldTimeLT must not be in the future")
	}
	if q.MyFieldTimeGTE.After(q.MyFieldTimeLT) {
		return errors.New("MyFieldTimeGTE must not be after MyFieldTimeLT")
	}
	if q.ParentKey.IsZero() {
		return errors.New("ParentKey is required")
	}
	return nil
}
```

#### Step 4: Update Database Schema

Create a new migration script file in `resources/sql` with an incremental file name. **IMPORTANT**: Do not edit an existing migration file.

Append `ALTER TABLE` statements to define the schema of the new columns. Define a `DEFAULT` value for all new columns that are `NOT NULL` in order to avoid the migration from failing on tables already populated with data. Columns holding IDs of parent objects should be named after the table they refer to with an `_id` suffix, e.g. `parent_table_id`.

```sql
-- DRIVER: mysql
ALTER TABLE my_noun
	ADD my_field_integer BIGINT NOT NULL DEFAULT 0,
	ADD my_field_nullable TEXT NULL,
	ADD my_field_time DATETIME NULL,
	ADD my_field_tags MEDIUMBLOB NULL,
	ADD parent_table_id BIGINT NOT NULL DEFAULT 0;

-- DRIVER: pgx
ALTER TABLE my_noun
	ADD COLUMN my_field_integer BIGINT NOT NULL DEFAULT 0,
	ADD COLUMN my_field_nullable TEXT NULL,
	ADD COLUMN my_field_time TIMESTAMP WITH TIME ZONE NULL,
	ADD COLUMN my_field_tags BYTEA NULL,
	ADD COLUMN parent_table_id BIGINT NOT NULL DEFAULT 0;

-- DRIVER: mssql
ALTER TABLE my_noun ADD
	my_field_integer BIGINT NOT NULL DEFAULT 0,
	my_field_nullable NVARCHAR(MAX) NULL,
	my_field_time DATETIME2 NULL,
	my_field_tags VARBINARY(MAX) NULL,
	parent_table_id BIGINT NOT NULL DEFAULT 0;

-- DRIVER: sqlite
ALTER TABLE my_noun ADD COLUMN my_field_integer INTEGER NOT NULL DEFAULT 0;
-- DRIVER: sqlite
ALTER TABLE my_noun ADD COLUMN my_field_nullable TEXT;
-- DRIVER: sqlite
ALTER TABLE my_noun ADD COLUMN my_field_time DATETIME;
-- DRIVER: sqlite
ALTER TABLE my_noun ADD COLUMN my_field_tags BLOB;
-- DRIVER: sqlite
ALTER TABLE my_noun ADD COLUMN parent_table_id INTEGER NOT NULL DEFAULT 0;
```

**IMPORTANT**: SQLite only supports adding one column per `ALTER TABLE` statement. Each column must be a separate statement prefixed with `-- DRIVER: sqlite`.

Append `CREATE INDEX` or `CREATE UNIQUE INDEX` statements to add indices for columns that will be heavily searchable. Always include the `tenant_id` as the first column in a composite index. Name the index by concatenating the name of the table, followed by `idx` and the columns it includes (excluding the `tenant_id` column). For example, `my_noun_idx_my_field_integer` is a composite index of `(tenant_id, my_field_integer)` in the `my_noun` table. If you are not sure what columns are worth indexing, ask the user for guidance.

```sql
-- DRIVER: mysql
CREATE INDEX my_noun_idx_my_field_integer ON my_noun (tenant_id, my_field_integer);

-- DRIVER: pgx
CREATE INDEX my_noun_idx_my_field_integer ON my_noun (tenant_id, my_field_integer);

-- DRIVER: mssql
CREATE INDEX my_noun_idx_my_field_integer ON my_noun (tenant_id, my_field_integer);

-- DRIVER: sqlite
CREATE INDEX my_noun_idx_my_field_integer ON my_noun (tenant_id, my_field_integer);
```

**Production tables on Postgres**: plain `CREATE INDEX` takes an `ACCESS EXCLUSIVE` lock that blocks writes for the duration of the build - fine for small tables, painful for large hot ones. If the target `pgx` table has significant production write traffic, ask the user whether to use `CREATE INDEX CONCURRENTLY IF NOT EXISTS` instead. It builds without blocking writes (sequel runs each migration statement outside a transaction, so `CONCURRENTLY` is allowed), at the cost of: (a) two table scans instead of one, and (b) non-atomic failure mode - if interrupted, Postgres leaves an `INVALID` index that must be `DROP`-ed manually before the migration can be retried. Plain `CREATE INDEX` is the safer default for prototyping and for tables that are still small.

#### Step 5: Map Column Names to Object Fields

Update the mapping of the database column names to their corresponding object fields in `service.go`.
**IMPORTANT** : Do not remove the mappings of the `example` column to the `Example` field since they are required by various tests.

In `mapColumnsOnInsert`, map the column names that can be set during the initial insertion of the object.
For nullable columns, wrap the value in `sequel.Nullify` to store the Go zero value as `NULL` in the database. To use a SQL statement as value, wrap a string in `sequel.UnsafeSQL`.

```go
func (svc *Service) mapColumnsOnInsert(ctx context.Context, obj *serviceapi.Obj) (columnMapping map[string]any, err error) {
	tags, err := json.Marshal(obj.Tags)
	if err != nil {
		return errors.Trace(err)
	}
	columnMapping := map[string]any{
		"my_field_integer":  obj.MyFieldInteger,
		"my_field_nullable": sequel.Nullify(obj.MyFieldNullable),
		"my_field_time":     sequel.UnsafeSQL(svc.db.NowUTC()),
		"my_field_tags":     tags,
		"parent_table_id":   obj.ParentKey.ID,
	}
	return columnMapping, nil
}
```

In `mapColumnsOnUpdate`, map the columns that can be modified after the initial insertion of the object.
For nullable columns, wrap the value in `sequel.Nullify` to store the Go zero value as `NULL` in the database. To use a SQL statement as value, wrap a string in `sequel.UnsafeSQL`.

```go
func (svc *Service) mapColumnsOnUpdate(ctx context.Context, obj *serviceapi.Obj) (columnMapping map[string]any, err error) {
	tags, err := json.Marshal(obj.Tags)
	if err != nil {
		return errors.Trace(err)
	}
	columnMapping := map[string]any{
		"my_field_integer":  obj.MyFieldInteger,
		"my_field_nullable": sequel.Nullify(obj.MyFieldNullable),
		"my_field_time":     sequel.UnsafeSQL(svc.db.NowUTC()),
		"my_field_tags":     tags,
		"parent_table_id":   obj.ParentKey.ID,
	}
	return columnMapping, nil
}
```

In `mapColumnsOnSelect`, map the columns that can be read.
For nullable columns, wrap the reference to the variable in `sequel.Nullable` in order to interpret a database `NULL` value as the zero value of the Go data type. Use `sequel.Bind` to transform and apply the value manually to the object.

```go
func (svc *Service) mapColumnsOnSelect(ctx context.Context, obj *serviceapi.Obj) (columnMapping map[string]any, err error) {
	columnMapping := map[string]any{
		"my_field_integer":  &obj.MyFieldInteger,
		"my_field_nullable": sequel.Nullable(&obj.MyFieldNullable),
		"my_field_time":     &obj.MyFieldTime,
		"my_field_tags": sequel.Bind(func(value []byte) (err error) {
			return json.Unmarshal(value, &obj.Tags)
		}),
		"parent_table_id": &obj.ParentKey.ID,
	}
	return columnMapping, nil
}
```

#### Step 6: Add Query Conditions

Prepare appropriate query conditions in `prepareWhereClauses` in `service.go` for new `Query` fields. Only add a condition if the `Query` field is not its zero value. Add the names of any textual and searchable columns to the `searchableColumns` array.

```go
func (svc *Service) prepareWhereClauses(ctx context.Context, query serviceapi.Query) (conditions []string, args []any, err error) {
	if strings.TrimSpace(query.Q) != "" {
		searchableColumns := []string{
			"my_field_nullable",
		}
		// ...
	}
	// ...
	if query.MyFieldInteger != 0 {
		conditions = append(conditions,"my_field_integer=?")
		args = append(args, query.MyFieldInteger)
	}
	query.MyFieldNullable = strings.TrimSpace(query.MyFieldNullable)
	if query.MyFieldNullable != "" {
		conditions = append(conditions,"my_field_nullable=?")
		args = append(args, query.MyFieldNullable)
	}
	if !query.MyFieldTimeGTE.IsZero() {
		conditions = append(conditions,"my_field_time>=?")
		args = append(args, query.MyFieldTimeGTE)
	}
	if !query.MyFieldTimeLT.IsZero() {
		conditions = append(conditions,"my_field_time<?")
		args = append(args, query.MyFieldTimeLT)
	}
	if !query.ParentKey.IsZero() {
		conditions = append(conditions,"parent_table_id=?")
		args = append(args, query.ParentKey.ID)
	}
	return conditions, args, nil
}
```

#### Step 7: Update Integration Tests

The `NewObject` function in `service_test.go` is used by tests to construct a new object to pass to `Create`. Adjust the constructor function to initialize all required fields so that they pass validation. You may introduce a measure of randomness.

Extend the integration tests to take into account the schema changes. Look for the `HINT`s to guide you. In particular:
- Set, modify and verify the new fields in `TestMyNoun_ColumnMappings` in `service_test.go`
- Add validation test cases for new object fields in `TestMyNoun_ValidateObject` in `mynounapi/object_test.go`
- Add validation test cases for new query fields in `TestMyNoun_ValidateQuery` in `mynounapi/query_test.go`

#### Step 8: Housekeeping

Follow the `housekeeping` skill. Skip the manifest, topology and tidy up steps.