Skip to content

builder_spec.md

Latest commit

 

History

History
93 lines (58 loc) · 6.55 KB

builder_spec.md

File metadata and controls

93 lines (58 loc) · 6.55 KB
Raw

pg_timetable Studio — Authoring & Chain Builder

How adding and editing chains is made fast and safe. Pairs with gui_spec §7 (screen summary) and charts_spec G1 (the pipeline graph this reuses).

The four frictions we remove

Authoring pg_timetable by hand means fighting four things. The builder's whole job is to delete them:

  1. JSONB parameter shapes — each BUILTIN wants a different JSON; SQL wants a positional array; PROGRAM wants argv. → kind-aware forms, never raw JSON.
  2. task_order math — it's a double; inserting/reordering means picking fractions. → the UI owns task_order; you drag.
  3. Cron + timezone guesswork — 5 fields, @every/@after/@reboot, all at PostgreSQL server time. → modes + presets + next-run preview + dual-TZ labels.
  4. No validation until it runs — a typo surfaces hours later in execution_log. → validate, diff, and test-run before it's ever scheduled.

1. On-ramps — pick your speed

Most chains are not authored from a blank canvas. Offer six entry points:

  1. Quick Job — name + schedule + one SQL statement → add_job(). The 30-second path for the 80% case (one-task chains).
  2. Template gallery — pre-built recipes with blanks to fill: nightly VACUUM, REFRESH MATERIALIZED VIEW, pg_dump backup (PROGRAM), download→import ETL, email report (SendMail), log cleanup (@reboot TRUNCATE timetable.log).
  3. Clone — duplicate any existing chain (including across installations, e.g. staging→prod) and tweak.
  4. Import YAML — pg_timetable's native job YAML format.
  5. Full builder — the visual canvas (below) for anything multi-step.
  6. From a snippet — paste an existing SQL statement → "make this a job."

2. The builder is the graph (edit-on-canvas)

The single most important decision: the Chain Builder uses the same pipeline component as the read-only Chain Detail view (G1), just switched to edit mode. Reading and editing look identical — no mental context-switch, and what you see is exactly what runs.

  • Add — a "+" insertion affordance sits between every step and at both ends. Click it → step-type picker → a new node appears at that exact position. Studio computes the task_order (the double between its neighbours, renumbering cleanly when they get too close) so you never see or touch it.
  • Reorder — drag the grip handle. Maps to move_task_up() / move_task_down() or a recomputed task_order.
  • Remove / disable — delete a step (delete_task), or disable it (detach chain_id → NULL) to park it without losing the command.
  • Select — click a node to open the inspector on the right.

This is also why the visualization is "the best way possible": there's one canvas, it's always truthful, and it's directly manipulable.

3. Kind-aware step inspector — no hand-written JSONB

Selecting a step opens an inspector whose form morphs to the step's kind:

  • SQL — CodeMirror editor (highlighting + schema-aware autocomplete); a positional parameter-array editor with typed chips; run_as role picker; autonomous toggle (with the hint "needed for VACUUM / CREATE DATABASE / CALL"); optional database_connection for cross-database tasks.
  • PROGRAM — command path + an argv list editor (string chips you add/remove/reorder).
  • BUILTIN — a dropdown of the 10 built-ins; each renders a purpose-built form:
    • SendMail → mail form · Download → URLs + destination + workers · Sleep → seconds · Log → message · CopyFromFile/CopyToFile → SQL + filename · CopyFromProgram/CopyToProgram → SQL + cmd + args · NoOp/Shutdown → no params.

Every form shows a read-only "parameter sent to pg_timetable" JSONB preview — you don't write it, but you can see it (trust + learning), and a raw-JSON escape hatch is always there for power users.

Per-step error policy is a visible choice — Stop chain vs Continue (ignore_error) — plus a timeout. When ignore_error is on, the inspector notes that this step breaks the chain's all-or-nothing transaction.

4. Schedule without tears

A dedicated schedule editor with modes instead of a raw cron box:

  • On demand (run_at = NULL, trigger manually) · Every N (@every) · After start (@after) · At reboot (@reboot) · Cron (5 fields + presets like "Daily at 02:00", "Weekdays at 18:00", "Hourly").
  • Live "explain in words" and a next-5-runs preview.
  • Timezone clarity — every time is labelled PostgreSQL server time and shown alongside the viewer's local time. This kills the single most common pg_timetable mistake.
  • Validation casts to the target's own ::timetable.cron domain (so rules always match that installation's version); the preview is computed in Go and labelled "estimated — the worker is authoritative."

5. Chain-level settings, in plain language

live (Draft / Active) · max_instances ("max concurrent runs") · exclusive_execution ("pause all other chains while this runs") · self_destruct ("run once, then delete") · client_name (worker picker populated from active_session, or "any worker") · timeout · on_error SQL (with a snippet helper).

6. Safety rails — trust and learning

  • Draft by default — new chains are live = false; nothing fires until you flip Activate.
  • Validate — one button checks: cron valid · SQL parses · params match the BUILTIN's schema · run_as role exists · client_name matches a known worker (or "any").
  • Diff before save — a human-readable diff and the exact SQL / function calls Studio will execute. Builds trust and teaches the schema.
  • Test run once — persist as a draft, fire notify_chain_start on a chosen worker, and stream the resulting execution_log row inline. See it work before you schedule it.
  • Unsaved-changes guard + autosaved drafts; every save recorded in gui_audit with before/after.

7. Speed for power users

  • ⌘K command palette — "Add SQL step", "Add SendMail", "Set schedule daily 02:00".
  • Snippet library (saved SQL + parameter sets), duplicate-step, keyboard reorder.

8. Mapping & phasing

Builder writes go through pg_timetable's own functions where possible (add_job, add_task, move_task_up/down, delete_task, delete_job, pause_job/resume_job, notify_chain_start); other fields use parameterized UPDATE/INSERT in a transaction with application_name set. The visual builder + inspector + schedule editor land in M4; templates, import, and clone in M4/M7.