Add SQL Fields

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.

Express field-level constraints (trimming, length limits, value ranges, required values) as dv8 tags on the fields; they are enforced automatically when the object is validated (see the add-type skill).

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

	// HINT: Define the fields of the object here, with dv8 tags for field-level constraints
	MyFieldString   string             `json:"myFieldString,omitzero" dv8:"trim,len<=256"`
	MyFieldInteger  int                `json:"myFieldInteger,omitzero" dv8:"val>=0"`
	MyFieldNullable string             `json:"myFieldNullable,omitzero" dv8:"trim,len<=256"`
	MyFieldTime     time.Time          `json:"myFieldTime,omitzero"`
	MyFieldTags     map[string]string  `json:"myFieldTags,omitzero" dv8:"each len<=256"`
	
	MyOtherObjectKey otherobjectapi.OtherObjectKey `json:"myOtherObjectKey,omitzero" dv8:"notzero"`
}

Constraints that the dv8 tag grammar cannot express - comparisons against the current time, invariants that span multiple fields - go in the object's Validate method, which is called automatically after the tags are enforced.

// Validate validates invariants that span multiple fields of the object.
// It is called automatically when the object is validated, after the dv8 field tags are enforced.
func (obj *MyNoun) Validate(ctx context.Context) error {
	// ...

	// HINT: Validate invariants that span multiple fields here as required
	if obj.MyFieldTime.After(time.Now()) {
		return errors.New("MyFieldTime must not be in the future")
	}
	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.

Express field-level constraints as dv8 tags on the fields, matching the constraints of the object fields they filter by (e.g. the same length limit).

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

	// HINT: Define the fields of the object here, with dv8 tags for field-level constraints
	MyFieldInteger  int       `json:"myFieldInteger,omitzero" dv8:"val>=0"`
	MyFieldNullable string    `json:"myFieldNullable,omitzero" dv8:"trim,len<=256"`
	MyFieldTimeGTE  time.Time `json:"myFieldTimeStart,omitzero"`
	MyFieldTimeLT   time.Time `json:"myFieldTimeEnd,omitzero"`
	
	ParentKey parentapi.ParentKey `json:"parentKey,omitzero" dv8:"notzero"`
}

Constraints that the dv8 tag grammar cannot express go in the Query's Validate method, which is called automatically after the tags are enforced.

// Validate validates the filtering options of the query that cannot be expressed as dv8 field tags.
// It is called automatically when the query is validated, after the dv8 field tags are enforced.
func (q *Query) Validate(ctx context.Context) error {
	// ...

	// HINT: Validate filtering options that cannot be expressed as dv8 tags here as required
	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")
	}
	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.

-- 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.

-- 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.