SPB Git

Repository Public

automation.md 14.4 KB 234 lines markdown

1# Automation23Workflows are saved sequences of steps that open or build a workbook, import and transform data,4recalculate, check and export. The same engine runs them from three places: the Automation studio5inside the app, the `orix-sheet` command-line tool, and App Intents (Shortcuts). This document6describes the model, the staging of outputs, the trust rules of each entry point and what has not7been verified.89Related documents: [overview.md](overview.md) (packages), [data-engine.md](data-engine.md) (SQL steps),10[python-runtime.md](python-runtime.md) (Python step), [../security.md](../security.md) (threat model,11section 7), [../release/signing.md](../release/signing.md) (embedding the tool in the app).1213## Packages1415| Package | Content |16|---|---|17| `SheetAutomation` | library `SheetAutomation` (model, validator, `WorkflowEngine`, stores), library `AutomationPDF` (sheets to PDF through SheetUI's print renderer), executable `orix-sheet` |18| `FeatureAutomation` | the Automation studio pane, the schedule panel, the App Intents, executable `automation-preview` |1920`AutomationFeature.register()` (called from `App/Sources/Features.swift`) registers a studio pane21with the identifier `automation` and a Tools action `automation.studio`. Registering runs nothing.2223Source: `Packages/SheetAutomation/Package.swift`, `Packages/FeatureAutomation/Package.swift`,24`Packages/FeatureAutomation/Sources/FeatureAutomation/AutomationFeature.swift`.2526## Workflow model2728A `Workflow` is a value serialised as JSON with sorted keys, in a file with the extension29`.orixflow` (schema version 1; a file written by a newer schema is refused when decoded).3031| Field | Meaning |32|---|---|33| `id`, `name`, `summary` | identity and description |34| `parameters` | values asked when the run starts. Kinds: `text`, `number`, `bool`, `file`, `folder`, `choice`; each has a name, a label, an optional default and a `required` flag. A step setting refers to a parameter as `{{name}}` |35| `steps` | ordered list of `WorkflowItem`: a step, its identifier and an optional error-policy override |36| `onError` | default policy: `stop` (the run ends as failed and nothing it produced is kept) or `continue` (the failure is recorded and the next step runs) |3738Where workflows are stored (`Store/Store.swift`):3940| Place | Location |41|---|---|42| Inside a workbook | attachment `workflows/<id>.json`; loading a workbook lists them and never runs anything |43| The user's library | `~/Library/Application Support/ORIX Sheet/Workflows/<id>.orixflow` |44| Exchange files | any `.orixflow` file; import refuses a file over 8 MiB and only decodes it |45| Run history | `workflow-history.jsonl` in the same support directory, bounded to 300 runs |46| Approvals | `workflow-trust.json` in the same support directory, mode 0600 |4748Four built-in templates exist (`Store/Templates.swift`): CSV report, Refresh Excel workbook, Convert49CSV folder to Excel, Monthly report. A template is duplicated into the library before it is edited.5051Source: `Packages/SheetAutomation/Sources/SheetAutomation/Model/Workflow.swift`, `Store/Store.swift`,52`Store/Templates.swift`.5354## Step kinds5556The vocabulary is closed: 25 kinds (`StepKind`). No kind starts a program or opens a network57connection.5859| Group | Kinds |60|---|---|61| Workbook | `openWorkbook`, `newWorkbook`, `saveWorkbook`, `addSheet`, `renameSheet`, `deleteSheet` |62| Data in | `importFile`, `importDataset`, `rangeToDataset` |63| Transform | `runTransform`, `runSQL`, `cleanRange`, `sort` |64| Cells | `setCell`, `setFormulaRange`, `formatRange`, `recalculate` |65| Analysis objects | `createPivot`, `createChart` |66| Checks | `validate` (includes the model auditor, level `off`, `error` or `warning`) |67| Outputs | `exportFile`, `exportPDF`, `exportChart` (PNG, PDF or SVG) |68| Scripts | `runPython` |69| Batch | `forEachFile` (nested steps run once per file of a folder) |7071Two kinds carry restrictions of their own:7273- **`runSQL` and `runTransform`** execute in the DuckDB engine under the configuration described in74  [data-engine.md](data-engine.md): no extension loading, no file access outside the engine's75  staging and spill directories.76- **`runPython`** runs only a script stored in the workbook whose exact text the user already approved77  for restricted mode in Python Studio (`PythonTrust.isTrusted(…, restricted: true)`). It always runs78  in restricted mode with networking off and a 300 s timeout, on a clone of the workbook. A workflow79  cannot approve a script.8081Source: `Model/Steps.swift`, `Steps/PythonStep.swift`, `Engine/WorkflowEngine.swift` (class comment).8283## Validation, staging and the end of a run8485`WorkflowEngine` is an actor; one engine runs one workflow at a time and `cancel()` may be called86from any thread.87881. **Static validation.** `WorkflowValidator` checks the workflow and its parameter values before89   anything runs, including whether the host authorises every path the steps name. An error90   prevents the run (`WorkflowError.invalid`).912. **Execution.** Steps run in order. A failed step either stops the run or is recorded, according to92   its error policy.933. **Staged outputs.** Every file a step produces is written into a private folder94   (`orix-workflow-<UUID>` in the temporary directory) by `OutputStage`, not at its destination.954. **Commit or discard.** If the run finishes without a stopping failure and without cancellation,96   each staged file is moved next to its destination and swapped in with one rename97   (`replaceItemAt` when the destination exists). Otherwise the staging folder is deleted: a failed98   or cancelled run writes no file, and no destination is left half-written.995. **Workbook.** The workbook handed to the engine is the engine's to modify. The studio passes a100   clone of the open document and swaps the result in as one undoable step only when the run101   succeeded; a failed or cancelled run returns no workbook.102103Run statuses: `succeeded`, `completedWithErrors` (steps failed under `continue`; files are still104committed), `failed`, `cancelled`.105106Source: `Engine/WorkflowEngine.swift`, `Engine/Host.swift` (`OutputStage`), `Engine/Validator.swift`;107tests `EngineTests`, `OutputTests`, and in `FeatureAutomation` `runOnTheOpenWorkbookIsOneUndoStep` and108`aFailedRunLeavesTheOpenWorkbookUntouched`.109110## Trust: three entry points, three rules111112File access goes through a `WorkflowHost`. `StandardWorkflowHost.isAuthorized(url, for:)` compares113the path — standardised, with symbolic links resolved — with a list of readable and writable roots,114unless the host was created with `unrestricted: true`, in which case it answers true for every path.115116| Entry point | Host | Approval check |117|---|---|---|118| Automation studio | restricted to the files and folders listed in the confirmation, plus those picked in panels (`AutomationModel.runHost`) | `WorkflowTrust`: an unapproved workflow first shows every location it reads and writes (`WorkflowValidator.fileAccesses`); the user runs it once or approves it. Built-in templates need no confirmation |119| `orix-sheet run` | **unrestricted** | **none** |120| Scheduled run (LaunchAgent) | **unrestricted** (it is the same command) | **none**, at install time or at run time |121| App Intent "Run ORIX Workflow" | may read the input file, may write only inside a private working folder | none; the confinement replaces it |122123`WorkflowTrust` stores the SHA-256 of the workflow's compact JSON. An edit made in the studio keeps124the approval of the text it was made from; a change made anywhere else produces another hash, so125the workflow is unapproved again.126127**The command line does not consult `WorkflowTrust`, by design.** `RunCommand.swift` creates128`StandardWorkflowHost(baseDirectory:, unrestricted: true, …)` with the comment "the user typed this129command: every location they named, or that the workflow names, is theirs to use". The reasoning is130that a command typed in a terminal, or a schedule installed by the user, is itself the explicit131decision that the studio obtains with its confirmation sheet. What follows from it must be stated132plainly: a `.orixflow` file received from someone else and run with `orix-sheet run` can read and133overwrite any file the user can, within the 25 step kinds. `orix-sheet run --validate-only` prints134the diagnostics without running; reading the file, which is JSON, is the way to see what it touches.135The approval list protects runs started from the studio only.136137Source: `Packages/SheetAutomation/Sources/SheetAutomation/Engine/Host.swift`, `Store/Store.swift`138(`WorkflowTrust`), `Sources/orix-sheet/RunCommand.swift`,139`Packages/FeatureAutomation/Sources/FeatureAutomation/AutomationModel+Run.swift`, `Intents.swift`;140tests `unapprovedWorkflowsAskBeforeRunningAndOnlyGetWhatWasListed`,141`runWorkflowIsConfinedToItsWorkingFolder`.142143## The `orix-sheet` command-line tool144145The release build embeds the tool in the app at `Contents/MacOS/orix-sheet`, signed with the app146([../release/signing.md](../release/signing.md)). It works on local files only and has no147interactive prompt.148149| Command | Purpose |150|---|---|151| `convert <in> <out> [--sheet N] [--fail-on-loss] [--recalc]` | convert a workbook to the format given by the extension of `<out>` (xlsx, xlsm, ods, csv, tsv, json, jsonl, orixsheet, pdf); prints the compatibility report |152| `recalc <file> [--out file]` | recalculate every formula and save |153| `eval <file> <A1\|range\|formula> [--sheet N] [--json] [--recalc]` | print the value of a cell, a range or a formula evaluated in the workbook |154| `info <file> [--json]` | sheets, dimensions, formulas, functions used, unimplemented functions, compatibility issues, macros and external links |155| `audit <file> [--format md\|json] [--fail-on warning\|error] [--recalc]` | run the model auditor and print its report |156| `sql <data-file> "<query>" [--out file] [--limit N] [--json]` | query a CSV, Parquet or JSON file with SQL in the embedded DuckDB engine (the file is the table `data`) |157| `run <workflow.orixflow> [--param name=value …] [--workbook file] [--base-dir dir] [--json] [--validate-only] [--no-history]` | run a workflow; progress on standard error, summary on standard output |158| `templates [--export NAME --out file.orixflow]` | list the built-in workflow templates or write one to a file |159| `functions [--category X] [--json]` | list the worksheet functions with their signatures |160| `version` | print the version |161162Exit status: 0 success; 1 failure (unreadable file, failed or invalid workflow, write error); 2 a163requested check did not pass (`--fail-on`, `--fail-on-loss`, steps failed under `continue`); 64164usage error; 130 cancelled. Ctrl-C during `run` cancels the engine, and nothing is written.165166Source: `Packages/SheetAutomation/Sources/orix-sheet/main.swift`, `Support.swift`, `RunCommand.swift`;167tests `CLITests` (10 tests that start the built binary).168169## App Intents170171Four intents run without opening the app's interface (`openAppWhenRun` is false):172173| Intent | Parameters | Result |174|---|---|---|175| Convert Spreadsheet | spreadsheet file, format, optional sheet | the converted file |176| Recalculate Workbook | workbook file | a recalculated file in the same format |177| Run ORIX Workflow | workflow file, optional input file | the files the run produced |178| Get Cell Value | spreadsheet file, optional sheet, cell (default `A1`) | the stored value as text |179180The app target declares `ORIXSheetIntents` (`App/Sources/TemplateChooser.swift`), an181`AppIntentsPackage` that includes the package's intents; the built bundle contains182`Contents/Resources/Metadata.appintents`. `ORIXSheetShortcuts` provides one phrase per intent.183184Source: `Packages/FeatureAutomation/Sources/FeatureAutomation/Intents.swift`; tests `IntentServiceTests`.185186## Scheduling187188An application that is not running cannot schedule itself. The supported mechanism is a per-user189LaunchAgent that the user installs explicitly from the schedule panel; nothing is installed190implicitly.191192- Only a workflow saved in the library can be scheduled, and only if it validates for an unattended193  run (no open workbook, nobody to ask).194- The panel shows the exact command line (`orix-sheet run <library file> --param … --base-dir …`),195  which can be copied for use in a terminal or a crontab.196- Install writes `~/Library/LaunchAgents/ai.spboucher.orix.sheet.workflow.<workflow id>.plist` with197  `StartCalendarInterval` (daily, selected weekdays, or a day of the month from 1 to 28),198  `RunAtLoad` false and `ProcessType` Background, then runs `/bin/launchctl bootstrap gui/<uid>`.199  Removal runs `bootout` and deletes the file.200- Output goes to `~/Library/Logs/ORIX Sheet/workflow-<workflow id>.log`.201- The program of the agent is the `orix-sheet` inside the running app bundle202  (`WorkflowSchedule.bundledTool`); outside an installed app the Install button has no tool to203  point at. Moving or deleting the app breaks installed schedules.204- A scheduled run has the trust rule of the command line (previous section): unrestricted host, no205  approval check. The agent names the library file, so a later edit of that workflow changes what206  the schedule does.207208Source: `Packages/FeatureAutomation/Sources/FeatureAutomation/Schedule.swift`, `ScheduleView.swift`,209`AutomationModel+Run.swift`; tests `ScheduleTests`,210`schedulingNeedsALibraryWorkflowThatCanRunUnattended`.211212## Tests213214`docs/reports/tests.json` records 78 test functions for `SheetAutomation` and 28 for215`FeatureAutomation`, all passing (2026-11-21T08:47:01Z). They cover the model and its JSON, the216validator, every step group, staged outputs and their atomic commit, workbook merge, the stores,217the command-line binary, workflow editing in the studio model, the confirmation flow, the property218list of a schedule, localization and the service behind the intents.219220## Not verified221222- **No test loads a LaunchAgent.** The tests build and read back the property list in a temporary223  directory; `launchctl bootstrap`, a run started by launchd at the scheduled time, and the224  behaviour after a logout, a restart or an app update were not exercised.225- **Shortcuts discovery.** The header of `Intents.swift` states that whether the Shortcuts app lists226  the actions "can only be verified in the built, installed app". The intents' logic is tested227  through `AutomationIntents`; no run from Shortcuts, Spotlight or Siri is recorded.228- **The embedded tool was checked for starting only**: `Scripts/sign.sh` runs `orix-sheet version`229  from inside the signed bundle. The `CLITests` run the binary built by SwiftPM, not the embedded230  copy.231- **The studio was reviewed through screenshots** of `automation-preview`; there is no automated232  test of its views.233- No performance measurement of workflow runs exists ([../development/performance.md](../development/performance.md)).234
Files main
Menu

Theme