Skip to content

Bulk-Edit the Action Catalog with Excel Workbooks

The preparation-task catalog — Action rows and their ordered ActionStep children — is normally edited one row at a time through the Filament admin panel, which is fine for a single tweak but slow for catalog-wide changes such as re-wording every action's behavior_statement or adjusting payout values across the board. The action-workbook feature solves that by exporting the catalog as an .xlsx file ops can edit in Excel, then re-importing it as an upsert against the database inside a single transaction [@design-doc]. Use this guide when you need to change several Action/ActionStep rows at once, or when you are adding a batch of brand-new actions and steps together.

What the workbook looks like

Export produces a three-sheet workbook, built with the openspout/openspout library: Instructions, Actions, and Action Steps [@export-service]. The Instructions sheet is not decorative — it states the actual editing contract in plain language: leave id blank to create a row, keep id filled to update one, and deleting a row from the workbook does not delete the underlying record [@export-service].

The Actions sheet has one column per Action field: id, kb_id, name, description, behavior_statement, alert_level_fit, timing_hours, feasibility, time_effort_minutes, cost_to_user, incentive_appropriate, payout_value, expires_after_hours, requirements, metadata [@export-service]. The Action Steps sheet is id, action_id, action_kb_id, order, title, description, requires_evidence [@export-service]. metadata round-trips as a JSON string in a single cell — the simplest low-risk representation the design considered, at the cost of not being especially Excel-friendly [@design-doc].

Export comes in two shapes, both from ActionWorkbookExportService::exportActions(): exportAll() for the full catalog (used by the Actions list page's "Export workbook" action) and exportAction() for a single action plus its steps (used by the steps relation manager's "Export steps" action), so ops can work on one action in isolation without pulling in the whole catalog [@export-service] [@list-actions] [@steps-relation-manager].

The upsert rule and why kb_id exists

Import is deliberately upsert-only — there is no delete path through the workbook, so removing a row from the sheet before importing simply leaves that record untouched in the database rather than deleting it [@design-doc]. ActionWorkbookImportService::prepareActions() and prepareSteps() apply the same rule to both sheets: a blank id column means "create a new row," a filled id looks the row up by primary key and errors if it does not exist, and any row entirely absent from the sheet is left alone [@import-service].

The kb_id/action_kb_id pair exists to solve one specific problem: a newly created Action and a newly created child ActionStep in the same import batch neither have a real database id yet at the point validation runs, so a step row cannot reference its new parent by action_id. Instead, the step row's action_kb_id column matches the parent action row's kb_id column, and ActionWorkbookImportService::resolveParentReference() resolves the parent by action_id first, falling back to action_kb_id second, checking both the workbook's own in-memory prepared-actions map and the database for a match [@import-service]. kb_id on the Actions sheet has its own uniqueness check — both within the workbook (prepareActions() tracks $seenKbIds and flags duplicates) and against existing database rows (excluding the row's own id when updating) — because a duplicate kb_id would make this parent-resolution step ambiguous [@import-service].

Atomicity and validation

The whole import — parsing both sheets, resolving every action and step, and writing every row — runs inside one DB::transaction() call, so a bad row anywhere in the workbook rolls back every write from that import, not just the row that failed [@import-service] [@design-doc]. Validation happens entirely before the transaction opens: prepareActions() and prepareSteps() build up an $errors array covering required headers, sheet names, enum values for alert_level_fit and feasibility, numeric/boolean coercion, duplicate kb_id or step id values, duplicate (parent, order) step collisions, and missing parent references, and import() throws ActionWorkbookValidationException before ever touching the database if that array is non-empty [@import-service]. This is why the whole-workbook rollback is safe to rely on: catching data errors is a pre-write validation pass, not a rollback-and-hope pattern. On success, import() returns an ActionWorkbookValidationResult carrying four counters — createdActions, updatedActions, createdSteps, updatedSteps — which the Filament action surfaces directly in its success notification, so ops sees exactly how many rows of each kind the import touched without opening the database [@validation-result].

When validation fails, ActionWorkbookValidationException carries both the structured error list and a path to a generated CSV validation report (sheet, row, field, message columns), and the Filament action in ListActions/StepsRelationManager catches the exception, shows a danger notification, and downloads that report so ops can find and fix the exact bad cells without reading logs [@validation-exception] [@list-actions] [@steps-relation-manager].

Full-catalog import versus single-action import

The Actions list page's "Import workbook" action calls ActionWorkbookImportService::importWorkbook(), which processes both sheets with no owner-action restriction — any action or step in the workbook can be created or updated [@list-actions] [@import-service]. The steps relation manager's "Import steps" action instead calls importStepsWorkbook($action, $path), passing the specific Action the import must stay scoped to: resolveParentReference() rejects any step row whose action_id or action_kb_id points to a different action, and rejects any existing step id that does not already belong to the owner action, so a relation-manager import cannot accidentally reassign or create steps under the wrong parent [@import-service] [@steps-relation-manager].

Doing the edit

  1. From the Actions list page in Filament, use "Export workbook" for a catalog-wide edit, or from a single action's Steps tab, use "Export steps" for a scoped edit [@list-actions] [@steps-relation-manager].
  2. Edit the downloaded .xlsx in Excel. Leave id blank on any row you want created; keep it filled on rows you are updating; do not touch rows you want left alone (or just delete them from the sheet — deleting a workbook row is a no-op against the database, not a delete) [@export-service].
  3. If you added new actions and want to attach new steps to them in the same batch, set the new action's kb_id to a value unique in the workbook, and set the new step's action_kb_id to that same value instead of filling in action_id [@import-service].
  4. Re-upload the file via "Import workbook" or "Import steps," matching whichever export you started from — a full-catalog workbook does not need to be trimmed down before a single-action import, but a single-action import will reject any step row that targets a different action [@import-service].

Recovering from a failed import

A failed import means nothing was written — the transaction never opened because validation ran first — so there is no partial state to clean up [@import-service]. Fix the specific sheet/row/field/message combinations in the downloaded validation report CSV and re-upload the corrected workbook. Common causes worth checking first: a duplicate kb_id across two new-action rows, a step row referencing an action_kb_id that does not match any action row's kb_id exactly (including case and whitespace), or an alert_level_fit/feasibility value that does not match the app's enum values [@import-service].