SPB Git

Repository Public

automation.md 14.4 KB

# Automation

Workflows are saved sequences of steps that open or build a workbook, import and transform data, recalculate, check and export. The same engine runs them from three places: the Automation studio inside the app, the orix-sheet command-line tool, and App Intents (Shortcuts). This document describes the model, the staging of outputs, the trust rules of each entry point and what has not been verified.

Related documents: overview.md (packages), data-engine.md (SQL steps), python-runtime.md (Python step), ../security.md (threat model, section 7), ../release/signing.md (embedding the tool in the app).

# Packages

Package Content
SheetAutomation library SheetAutomation (model, validator, WorkflowEngine, stores), library AutomationPDF (sheets to PDF through SheetUI's print renderer), executable orix-sheet
FeatureAutomation the Automation studio pane, the schedule panel, the App Intents, executable automation-preview

AutomationFeature.register() (called from App/Sources/Features.swift) registers a studio pane with the identifier automation and a Tools action automation.studio. Registering runs nothing.

Source: Packages/SheetAutomation/Package.swift, Packages/FeatureAutomation/Package.swift, Packages/FeatureAutomation/Sources/FeatureAutomation/AutomationFeature.swift.

# Workflow model

A Workflow is a value serialised as JSON with sorted keys, in a file with the extension .orixflow (schema version 1; a file written by a newer schema is refused when decoded).

Field Meaning
id, name, summary identity and description
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}}
steps ordered list of WorkflowItem: a step, its identifier and an optional error-policy override
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)

Where workflows are stored (Store/Store.swift):

Place Location
Inside a workbook attachment workflows/<id>.json; loading a workbook lists them and never runs anything
The user's library ~/Library/Application Support/ORIX Sheet/Workflows/<id>.orixflow
Exchange files any .orixflow file; import refuses a file over 8 MiB and only decodes it
Run history workflow-history.jsonl in the same support directory, bounded to 300 runs
Approvals workflow-trust.json in the same support directory, mode 0600

Four built-in templates exist (Store/Templates.swift): CSV report, Refresh Excel workbook, Convert CSV folder to Excel, Monthly report. A template is duplicated into the library before it is edited.

Source: Packages/SheetAutomation/Sources/SheetAutomation/Model/Workflow.swift, Store/Store.swift, Store/Templates.swift.

# Step kinds

The vocabulary is closed: 25 kinds (StepKind). No kind starts a program or opens a network connection.

Group Kinds
Workbook openWorkbook, newWorkbook, saveWorkbook, addSheet, renameSheet, deleteSheet
Data in importFile, importDataset, rangeToDataset
Transform runTransform, runSQL, cleanRange, sort
Cells setCell, setFormulaRange, formatRange, recalculate
Analysis objects createPivot, createChart
Checks validate (includes the model auditor, level off, error or warning)
Outputs exportFile, exportPDF, exportChart (PNG, PDF or SVG)
Scripts runPython
Batch forEachFile (nested steps run once per file of a folder)

Two kinds carry restrictions of their own:

  • runSQL and runTransform execute in the DuckDB engine under the configuration described in data-engine.md: no extension loading, no file access outside the engine's staging and spill directories.
  • runPython runs only a script stored in the workbook whose exact text the user already approved for restricted mode in Python Studio (PythonTrust.isTrusted(…, restricted: true)). It always runs in restricted mode with networking off and a 300 s timeout, on a clone of the workbook. A workflow cannot approve a script.

Source: Model/Steps.swift, Steps/PythonStep.swift, Engine/WorkflowEngine.swift (class comment).

# Validation, staging and the end of a run

WorkflowEngine is an actor; one engine runs one workflow at a time and cancel() may be called from any thread.

  1. Static validation. WorkflowValidator checks the workflow and its parameter values before anything runs, including whether the host authorises every path the steps name. An error prevents the run (WorkflowError.invalid).
  2. Execution. Steps run in order. A failed step either stops the run or is recorded, according to its error policy.
  3. Staged outputs. Every file a step produces is written into a private folder (orix-workflow-<UUID> in the temporary directory) by OutputStage, not at its destination.
  4. Commit or discard. If the run finishes without a stopping failure and without cancellation, each staged file is moved next to its destination and swapped in with one rename (replaceItemAt when the destination exists). Otherwise the staging folder is deleted: a failed or cancelled run writes no file, and no destination is left half-written.
  5. Workbook. The workbook handed to the engine is the engine's to modify. The studio passes a clone of the open document and swaps the result in as one undoable step only when the run succeeded; a failed or cancelled run returns no workbook.

Run statuses: succeeded, completedWithErrors (steps failed under continue; files are still committed), failed, cancelled.

Source: Engine/WorkflowEngine.swift, Engine/Host.swift (OutputStage), Engine/Validator.swift; tests EngineTests, OutputTests, and in FeatureAutomation runOnTheOpenWorkbookIsOneUndoStep and aFailedRunLeavesTheOpenWorkbookUntouched.

# Trust: three entry points, three rules

File access goes through a WorkflowHost. StandardWorkflowHost.isAuthorized(url, for:) compares the path — standardised, with symbolic links resolved — with a list of readable and writable roots, unless the host was created with unrestricted: true, in which case it answers true for every path.

Entry point Host Approval check
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
orix-sheet run unrestricted none
Scheduled run (LaunchAgent) unrestricted (it is the same command) none, at install time or at run time
App Intent "Run ORIX Workflow" may read the input file, may write only inside a private working folder none; the confinement replaces it

WorkflowTrust stores the SHA-256 of the workflow's compact JSON. An edit made in the studio keeps the approval of the text it was made from; a change made anywhere else produces another hash, so the workflow is unapproved again.

The command line does not consult WorkflowTrust, by design. RunCommand.swift creates StandardWorkflowHost(baseDirectory:, unrestricted: true, …) with the comment "the user typed this command: every location they named, or that the workflow names, is theirs to use". The reasoning is that a command typed in a terminal, or a schedule installed by the user, is itself the explicit decision that the studio obtains with its confirmation sheet. What follows from it must be stated plainly: a .orixflow file received from someone else and run with orix-sheet run can read and overwrite any file the user can, within the 25 step kinds. orix-sheet run --validate-only prints the diagnostics without running; reading the file, which is JSON, is the way to see what it touches. The approval list protects runs started from the studio only.

Source: Packages/SheetAutomation/Sources/SheetAutomation/Engine/Host.swift, Store/Store.swift (WorkflowTrust), Sources/orix-sheet/RunCommand.swift, Packages/FeatureAutomation/Sources/FeatureAutomation/AutomationModel+Run.swift, Intents.swift; tests unapprovedWorkflowsAskBeforeRunningAndOnlyGetWhatWasListed, runWorkflowIsConfinedToItsWorkingFolder.

# The orix-sheet command-line tool

The release build embeds the tool in the app at Contents/MacOS/orix-sheet, signed with the app (../release/signing.md). It works on local files only and has no interactive prompt.

Command Purpose
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
recalc <file> [--out file] recalculate every formula and save
eval <file> <A1|range|formula> [--sheet N] [--json] [--recalc] print the value of a cell, a range or a formula evaluated in the workbook
info <file> [--json] sheets, dimensions, formulas, functions used, unimplemented functions, compatibility issues, macros and external links
audit <file> [--format md|json] [--fail-on warning|error] [--recalc] run the model auditor and print its report
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)
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
templates [--export NAME --out file.orixflow] list the built-in workflow templates or write one to a file
functions [--category X] [--json] list the worksheet functions with their signatures
version print the version

Exit status: 0 success; 1 failure (unreadable file, failed or invalid workflow, write error); 2 a requested check did not pass (--fail-on, --fail-on-loss, steps failed under continue); 64 usage error; 130 cancelled. Ctrl-C during run cancels the engine, and nothing is written.

Source: Packages/SheetAutomation/Sources/orix-sheet/main.swift, Support.swift, RunCommand.swift; tests CLITests (10 tests that start the built binary).

# App Intents

Four intents run without opening the app's interface (openAppWhenRun is false):

Intent Parameters Result
Convert Spreadsheet spreadsheet file, format, optional sheet the converted file
Recalculate Workbook workbook file a recalculated file in the same format
Run ORIX Workflow workflow file, optional input file the files the run produced
Get Cell Value spreadsheet file, optional sheet, cell (default A1) the stored value as text

The app target declares ORIXSheetIntents (App/Sources/TemplateChooser.swift), an AppIntentsPackage that includes the package's intents; the built bundle contains Contents/Resources/Metadata.appintents. ORIXSheetShortcuts provides one phrase per intent.

Source: Packages/FeatureAutomation/Sources/FeatureAutomation/Intents.swift; tests IntentServiceTests.

# Scheduling

An application that is not running cannot schedule itself. The supported mechanism is a per-user LaunchAgent that the user installs explicitly from the schedule panel; nothing is installed implicitly.

  • Only a workflow saved in the library can be scheduled, and only if it validates for an unattended run (no open workbook, nobody to ask).
  • The panel shows the exact command line (orix-sheet run <library file> --param … --base-dir …), which can be copied for use in a terminal or a crontab.
  • Install writes ~/Library/LaunchAgents/ai.spboucher.orix.sheet.workflow.<workflow id>.plist with StartCalendarInterval (daily, selected weekdays, or a day of the month from 1 to 28), RunAtLoad false and ProcessType Background, then runs /bin/launchctl bootstrap gui/<uid>. Removal runs bootout and deletes the file.
  • Output goes to ~/Library/Logs/ORIX Sheet/workflow-<workflow id>.log.
  • The program of the agent is the orix-sheet inside the running app bundle (WorkflowSchedule.bundledTool); outside an installed app the Install button has no tool to point at. Moving or deleting the app breaks installed schedules.
  • A scheduled run has the trust rule of the command line (previous section): unrestricted host, no approval check. The agent names the library file, so a later edit of that workflow changes what the schedule does.

Source: Packages/FeatureAutomation/Sources/FeatureAutomation/Schedule.swift, ScheduleView.swift, AutomationModel+Run.swift; tests ScheduleTests, schedulingNeedsALibraryWorkflowThatCanRunUnattended.

# Tests

docs/reports/tests.json records 78 test functions for SheetAutomation and 28 for FeatureAutomation, all passing (2026-11-21T08:47:01Z). They cover the model and its JSON, the validator, every step group, staged outputs and their atomic commit, workbook merge, the stores, the command-line binary, workflow editing in the studio model, the confirmation flow, the property list of a schedule, localization and the service behind the intents.

# Not verified

  • No test loads a LaunchAgent. The tests build and read back the property list in a temporary directory; launchctl bootstrap, a run started by launchd at the scheduled time, and the behaviour after a logout, a restart or an app update were not exercised.
  • Shortcuts discovery. The header of Intents.swift states that whether the Shortcuts app lists the actions "can only be verified in the built, installed app". The intents' logic is tested through AutomationIntents; no run from Shortcuts, Spotlight or Siri is recorded.
  • The embedded tool was checked for starting only: Scripts/sign.sh runs orix-sheet version from inside the signed bundle. The CLITests run the binary built by SwiftPM, not the embedded copy.
  • The studio was reviewed through screenshots of automation-preview; there is no automated test of its views.
  • No performance measurement of workflow runs exists (../development/performance.md).
Files main
Menu

Theme