relspecgo/docs/JOB_FILES.md

# RelSpec Job Files

Job files let you declare named, repeatable RelSpec workflows in YAML and run
them with `relspec job run <name>` instead of retyping long command lines.

```bash
relspec job list                     # deterministic list of discovered jobs
relspec job run build-schema --plan  # validate + print plan, execute nothing
relspec job run build-schema         # run the job (and its dependencies)
```

## Design contract

This is a deliberately small, safe contract. Every capability is offline-testable
except live database execution (`scripts-exec`), which is validated and planned
offline and only connects at run time.

### Not a shell

`command` is a **closed allow-list**. There is no field anywhere that accepts a
shell string, an executable path, or arbitrary arguments. Adding a new command
means adding a vetted adapter in the RelSpec source.

| command        | what it does                                                        |
|----------------|--------------------------------------------------------------------|
| `convert`      | read one or more input schemas, additively merge them, write one output |
| `merge`        | like `convert` but requires ≥2 inputs and exposes `skip_*` merge options |
| `split`        | read one or more schemas, keep the selected schemas/tables, write one output |
| `scripts-list` | deterministically list SQL scripts across one or more directories   |
| `scripts-exec` | execute SQL scripts across one or more directories against a live PostgreSQL database |
| `templ`        | apply a custom Go text template to one or more input schemas          |
| `inspect`      | validate one or more schemas against rules and write a report       |
| `diff`         | compare exactly two schemas and write a differences report          |

`convert`, `merge` and `split` are **producers**: their file output can be fed
directly into another job with `from_job` (see below).

### Discovery and precedence

`relspec job` (no `--file`) scans `--dir` (default `.`) for:

1. `relspec.yml` / `relspec.yaml` (the default file), then
2. `relspec.<name>.yml` / `relspec.<name>.yaml` (extra files),

each group sorted lexically. Order is stable across runs. Use `--file <path>`
(repeatable) to load explicit files and skip discovery.

All discovered/selected files are merged into one job namespace. A job name
defined by **more than one file is a hard error** naming both files. YAML maps
already forbid duplicate keys within a single file.

### Paths

* Every path (`inputs[].path`, `output.path`, `report.path`, `rules`,
  `script_dirs[]`, `template`, `logfile`) is **relative to the directory
  containing the job file that declared the job**, not the process working
  directory.
* Absolute paths, `~`-relative paths and any path that resolves outside the job
  file directory (`../`, `a/../../b`, …) are **rejected during validation** —
  before anything runs.
* At run time each path is additionally resolved through its symlinks: a symlink
  inside the job-file directory that points outside it is rejected before the
  path is opened.

### Credentials

* Database inputs (`format: pgsql` / `mssql`) and database execution outputs
  (`format: pgsql` with `conn_env`) reference an **environment variable name**
  via `conn_env:`. The connection string itself is never stored in the
  manifest.
* A `conn_env` value that looks like a connection string (contains `:`, `/`,
  `@`, `=`, spaces) is rejected.
* Missing/empty environment variables are reported during pre-flight, before
  execution.
* Job logs and `--plan` output show `env:<NAME>`, never the value. Resolved
  secret values and anything matching a connection-string password are
  redacted (`***`) from the logfile and diagnostics.

### Validation happens before execution

`relspec job list` and `relspec job run` both fully validate the selected set
first. Nothing is read, written, connected to, or executed if validation fails.
Checks include:

* schema `version` — **forward-permissive**: any version `>= 1` is accepted.
  An omitted `version` is treated as the current one. A version newer than this
  build understands loads best-effort (unknown YAML fields are ignored and a
  warning is printed); at the current version unknown YAML fields are still
  rejected.
* duplicate job names across files
* unknown / missing `command`
* per-command input/output shape:
  * `convert` needs ≥1 input + output; `merge` needs ≥2 inputs + output
  * `split` needs ≥1 input + a file output, plus an optional `select:` block
  * `scripts-list` needs `script_dirs` and forbids inputs/output
  * `scripts-exec` needs `script_dirs` and `output.conn_env` (pgsql only)
  * `inspect` needs ≥1 input + `report:` (format `markdown`|`json`)
  * `diff` needs **exactly 2** inputs + `report:` (format `summary`|`json`|`html`)
* unknown input/output `format`
* `from_job` targets exist, are producers (`convert`/`merge`/`split`) and write a
  single-file output
* path traversal / absolute / home-relative paths
* `depends_on` and `from_job` targets exist
* dependency cycles over the combined `depends_on` + `from_job` graph
  (reported as `a -> b -> c -> a`)

Then, immediately before running, per-job pre-flight resolves paths and checks:

* every input file exists and is a file (a `from_job` input is exempt — its
  producer runs earlier in the same plan)
* every `script_dir` exists and is a directory
* every `conn_env` variable is set
* `output.path` / `report.path` does not already exist unless the matching
  `overwrite: true` is set
* `rules` (inspect), when given, exists and is a file
* symlinks in every resolved path stay inside the job-file directory

If any pre-flight check fails for **any** job in the plan, **no** job runs.

### Execution and exit codes

* `relspec job run <name>` runs the job's dependency closure first
  (`depends_on` plus any `from_job` producers), in topological order
  (deterministic), then the job. `--no-deps` runs only the named job and is
  incompatible with `from_job` inputs.
* `--dry-run` (alias `--plan`) prints the resolved plan and exits 0 without
  touching inputs, outputs or databases.
* A failing job returns the underlying non-zero status (the process exits 1)
  and the error names the job. The logfile records `FAILED: <error>`; a
  successful job records `OK`. No separate success-marker file is written, so a
  failure can never leave a stale "success".
* `inspect` fails the job when the report contains rule **errors** (enforced
  rules); warnings do not fail it. `diff` never fails on differences.
* Single-file outputs and reports are written to a temporary file in the target
  directory and atomically renamed into place, so an interrupted run never
  leaves a partial file. Directory-emitting formats (`gorm`, `bun`, `drizzle`,
  `typeorm`, `prisma`) are written in place.

### Logfile rotation

When a job has a `logfile`, it is size-rotated before each run. Defaults are
**5 MB** with **3** rotated files kept (`build.log` → `build.log.1` → …). Override
per job with `log_max_size` / `log_keep`, or for a whole file with a top-level
`defaults:` block. `log_max_size` accepts `B`/`KB`/`MB`/`GB` suffixes (e.g.
`"512KB"`, `"5MB"`).

## Schema reference

```yaml
version: 1                       # optional; any value >= 1 is accepted
defaults:                        # optional, file-wide
  log_max_size: 5MB              # B / KB / MB / GB
  log_keep: 3
jobs:
  <job-name>:
    command: convert | merge | split | scripts-list | scripts-exec | templ | inspect | diff
    description: "free text"                   # optional, shown by `job list`
    depends_on: [other-job, ...]               # optional
    inputs:                                    # convert (≥1) / merge (≥2) / split (≥1) / inspect (≥1) / diff (exactly 2)
      - path: relative/file.dbml               # file inputs
        format: dbml
      - format: pgsql                          # live-connection inputs
        conn_env: SOURCE_DB_URL                # env var NAME
      - from_job: build-schema                 # consume another job's file output
    script_dirs:                               # scripts-list / scripts-exec (≥1)
      - migrations/core
      - migrations/tenant
    template: templates/schema.tmpl             # templ (required)
    mode: table                                 # templ: database/schema/script/table
    filename_pattern: "{{.Name}}.go"           # templ multi-output modes
    select:                                    # split (optional; default = keep everything)
      schemas: [public]
      tables: [users, orders]
      exclude_schemas: []
      exclude_tables: []
      database_name: SubsetDB                  # optional rename of the output database
    rules: .relspec-rules.yaml                 # inspect (optional; built-in defaults if omitted)
    report:                                    # inspect (required) / diff (required)
      format: json                             # inspect: markdown|json ; diff: summary|json|html
      path: build/report.json                  # required, except a diff "summary" (goes to the log)
      overwrite: false
    output:                                    # convert / merge / split (required); scripts-exec (required, conn_env)
      format: pgsql
      path: build/schema.sql                   # file output, OR:
      conn_env: TARGET_DB_URL                  # execute against DB (pgsql only)
      overwrite: false                         # default false
    options:
      flatten_schema: false
      schema: public
      package: models                          # for gorm/bun output
      continue_on_error: false                 # pgsql / scripts-exec output
      skip_relations: false                    # merge only
      skip_enums: false
      skip_views: false
      skip_domains: false
      skip_sequences: false
    logfile: .relspec/log/<job-name>.log       # optional; appended to, size-rotated
    log_max_size: 5MB                           # optional per-job override
    log_keep: 3                                 # optional per-job override
```

For `templ`, `inputs` use the same file or `pgsql`/`conn_env` source forms as
schema conversion. `output` is optional (empty means stdout); when present it
contains only `path` and `overwrite`, because templates do not select a schema
writer format.

A `from_job` input takes no `path`, `format` or `conn_env`: it resolves to the
named job's `output.path` and inherits its format, and implies a dependency on
that job. The producer must be a `convert`, `merge` or `split` job writing a
single-file output.

### Supported input formats

`dbml`, `dctx`, `drawdb`, `graphql`, `json`, `yaml`, `gorm`, `bun`, `drizzle`,
`prisma`, `typeorm`, `sqlite` (file, via `path`); `pgsql`, `mssql`
(live, via `conn_env`).

### Supported output formats

`dbml`, `dctx`, `drawdb`, `graphql`, `json`, `yaml`, `gorm`, `bun`, `drizzle`,
`prisma`, `typeorm`, `pgsql`, `mssql`, `sqlite` (file, via `path`); `pgsql` also
supports `conn_env` to execute the generated DDL against a live database.

## Examples

### Merge many schema files, emit PostgreSQL DDL

```yaml
version: 1
jobs:
  build-schema:
    command: convert
    inputs:
      - { path: schema/core.dbml, format: dbml }
      - { path: schema/billing.dbml, format: dbml }
      - { path: schema/tenant.dbml, format: dbml }
    output:
      format: pgsql
      path: build/schema.sql
      overwrite: true
    logfile: .relspec/log/build-schema.log
```

### Multiple script directories

```yaml
version: 1
jobs:
  migration-order:
    command: scripts-list
    script_dirs:
      - migrations/core
      - migrations/tenant
      - migrations/reporting
    logfile: .relspec/log/migration-order.log
```

### Job depending on another job

```yaml
version: 1
jobs:
  build-schema:
    command: convert
    inputs:
      - { path: schema/core.dbml, format: dbml }
      - { path: schema/tenant.dbml, format: dbml }
    output: { format: json, path: build/schema.json, overwrite: true }
  build-docs:
    command: convert
    depends_on: [build-schema]
    inputs:
      - { path: schema/core.dbml, format: dbml }
    output: { format: yaml, path: build/schema.yaml, overwrite: true }
```

### Reading from a remote database

```yaml
version: 1
jobs:
  snapshot-prod:
    command: convert
    inputs:
      - format: pgsql
        conn_env: PROD_DB_URL      # export PROD_DB_URL=postgres://...
    output:
      format: dbml
      path: snapshots/prod.dbml
      overwrite: true
```

### Chain jobs with `from_job`, then lint the result

```yaml
version: 1
jobs:
  build-json:
    command: convert
    inputs:
      - { path: schema/core.dbml, format: dbml }
      - { path: schema/tenant.dbml, format: dbml }
    output: { format: json, path: build/schema.json, overwrite: true }
  lint-schema:
    command: inspect
    inputs:
      - from_job: build-json      # implies depends_on: [build-json]
    rules: .relspec-rules.yaml    # optional; built-in rules if omitted
    report:
      format: markdown
      path: build/lint-report.md
      overwrite: true
```

`relspec job run lint-schema` runs `build-json` first, then inspects its output.
The job fails (exit 1) if any enforced rule is violated.

### Split a subset out of a larger schema

```yaml
version: 1
jobs:
  posts-only:
    command: split
    inputs:
      - { path: schema/core.dbml, format: dbml }
      - { path: schema/tenant.dbml, format: dbml }
    select:
      tables: [posts]
    output: { format: dbml, path: build/posts.dbml, overwrite: true }
```

### Diff two schemas

```yaml
version: 1
jobs:
  drift:
    command: diff
    inputs:                       # exactly two
      - { path: build/schema.json, format: json }
      - format: pgsql
        conn_env: PROD_DB_URL
    report:
      format: summary             # summary → logfile; json/html need a path
```

`diff` reports differences and always exits 0.

### Execute migration scripts against a live database

```yaml
version: 1
jobs:
  apply-migrations:
    command: scripts-exec
    script_dirs:
      - migrations/core
      - migrations/tenant
    output:
      conn_env: TARGET_DB_URL     # pgsql only; no path
    options:
      continue_on_error: false
    logfile: .relspec/log/apply-migrations.log
```