ebuilder-sql-step

name

ebuilder-sql-step

description

Generation playbook for SQL step authoring inside mutation steps in configs/sql.*.yml. USE FOR: define step keys, choose step operation type (command/query/task/mutation/jsExpression), configure save strategies, write checksBefore/checksAfter, map catchErrors, and design deterministic step chaining. DO NOT USE FOR: full mutation envelope design (use ebuilder-sql-mutation) or query-* root authoring (use ebuilder-sql-query).

eBuilder SQL Step Generation Playbook

Purpose

Use this skill when generating or reviewing steps entries inside mutation-* definitions in configs/sql.*.yml.

This skill is step-centric. It focuses on:

• per-step operation shape
• result persistence (save)
• guard checks (checksBefore, checksAfter)
• error mapping (catchErrors)
• deterministic chaining across step outputs

Use ebuilder-sql-mutation for mutation-level envelope concerns (inputParams, datasource, authorize, transaction level, response, callbacks).

Scope

• File target: configs/sql.*.yml
• Valid context: mutation.<mutation-name>.steps[]
• Out of scope:
  • mutation root and envelope strategy (ebuilder-sql-mutation)
  • query root definitions (ebuilder-sql-query)
  • $EB_* symbol reference and portability catalog (ebuilder-eb-sql-symbols)

Step Blueprint

steps:
  - key: validateItem
    query: query-item-exists
    queryParams:
      inputParams:
        itemId: ${itemId}
    checksAfter:
      - if: ${{ ${validateItem}.length > 0 }}
        elseThrowErrorCode: 404

  - key: updateItem
    command: |
      UPDATE ec_item
      SET title = ${title}, updated_at = $EB_DATETIME_NOW
      WHERE id_item = ${itemId}

  - key: result
    command: |
      SELECT id_item AS "itemId", title, updated_at AS "updatedAt"
      FROM ec_item
      WHERE id_item = ${itemId}
    save: first-row-as-object

Step Properties

Property	Type	Required	Notes
key	string	Yes	Unique step id in chain.
command	string or engine object	No	SQL command for this step.
save	enum	No	scalar, last-insert-id, first-row, first-row-as-object, all-rows, column-values, output-params.
extra	object	No	Save-mode options (identityCol, column).
checksBefore	array	No	Guard checks before execution.
checksAfter	array	No	Guard checks after execution.
catchErrors	object	No	DB error-code to response mapping.
defaultResult	any	No	Result when checks fail and no throw.
omitResult	boolean	No	Exclude step output from downstream context.
task	string	No	Call eBuilder task.
taskParams	any	No	Params for task.
mutation	string	No	Call nested eBuilder mutation.
mutationParams	any	No	Params for mutation.
query	string	No	Call eBuilder query.
queryParams	object	No	Query execution options.
placeholders	object	No	Per-step placeholder dictionary.
jsExpression	string	No	Compute step output via JS expression.
emitResult	boolean	No	Emit step result in response/event context.
log	object	No	Step entry logging definition.
datasource	string	No	Optional datasource override.
subSteps	array of sqlStep	No	Nested step chain.

Important behavior for query + queryParams:

• Step output is always a list, regardless of the called query responseType.
• For existence checks, use length > 0 / length === 0, not null checks.

Save Strategies

Strategy	Result Type	Use Case	Extra
scalar	Single value	COUNT/SUM/id-like scalar responses	None
last-insert-id	Single id	Capture generated id after INSERT	extra.identityCol required
first-row	Flat mapped props	Access with ${stepKey___column}	None
first-row-as-object	Object	Consume row as object	None
all-rows	Array[object]	Full list result	None
column-values	Array[value]	Extract one column into array	extra.column required
output-params	Object	Stored procedure OUT params	Engine dependent

Operation Patterns

SQL command step

- key: updateStatus
  command: |
    UPDATE items
    SET status = 'processed'
    WHERE id = ${itemId}

Query-backed step

- key: existingRows
  query: query-item-by-id
  queryParams: # This support all params for eb-query like take, skip, filters, sorts, distinct...
    inputParams:
      itemId: ${itemId}
# ${existingRows} is always a list so you can use `take: 1` if you only expect one result or want to check existence with `${existingRows.length > 0}`

Task-backed step

- key: sendNotification
  task: task-send-email
  taskParams:
    recipientId: ${userId}
    subject: Item created

Nested mutation step

- key: createAudit
  mutation: mutation-create-audit-log
  mutationParams:
    itemId: ${itemId}

JS expression step

- key: pricing
  jsExpression: |
    ${{
      const basePrice = ${baseAmount};
      const discount = $EB_CONTEXT(user.discountPercent) || 0;
      const tax = 0.1;
      const finalPrice = basePrice * (1 - discount / 100) * (1 + tax);
      return { basePrice, discount, tax, finalPrice };
    }}

Rules for jsExpression:

• Use multi-line function body.
• Include explicit return.
• Keep deterministic and side-effect free.

Guard Checks

Before-step guard

- key: deleteItem
  command: DELETE FROM items WHERE id = ${itemId}
  checksBefore:
    - if: ${{ ${itemId} > 0 && $EB_CONTEXT(user.roles)['admin'] }}
      elseThrowErrorCode: 403

After-step guard

- key: reduceInventory
  command: |
    UPDATE inventory
    SET quantity = quantity - ${orderQty}
    WHERE product_id = ${productId}
  checksAfter:
    - if: ${{ ${reduceInventory} > 0 }}
      elseThrowErrorCode: 409

Error Mapping

- key: insertUnique
  command: |
    INSERT INTO users (email, name)
    VALUES (${email}, ${name})
  catchErrors:
    2627:
      statusCode: 409
      message: EMAIL_EXISTS
    23505:
      statusCode: 409
      message: EMAIL_EXISTS

Deterministic Checklist (Step Level)

1. key exists and is unique within the enclosing step chain.
2. Exactly one operation mode is clear per step (command or query or task or mutation or jsExpression) unless sub-step orchestration intentionally combines behavior.
3. Save strategy is valid for the operation result type.
4. last-insert-id includes extra.identityCol.
5. column-values includes extra.column.
6. All user input references use parameter binding (${paramName}).
7. Checks use if expressions (no deprecated param/op/value).
8. Query-based existence checks use list length (${step}.length).
9. Step-output references match actual save shape (${stepKey___column} vs ${stepKey}).
10. No side effects in jsExpression.

Do Not Generate

• Query or mutation root envelopes in this skill output.
• Steps without key.
• Colliding step keys.
• Raw SQL interpolation of unsanitized input.
• Null checks for query-step existence (!= null) when result is a list.
• Deprecated check keys (param, op, value, throw, silentThrow).