[proposal] schedules: a where value may be a moment relative to now

[delchev]

The problem

The archetypal schedule is a staleness sweep: "rows still provisioning after 30 minutes",
"quotations unanswered for 7 days", "carts abandoned for an hour". schedules: is built exactly for
this — query an entity, act per matching row — and it can express every part of such a sweep except
the one that makes it a sweep: how old is too old.

A where value is a literal or the current moment (CURRENT_DATE, and its timestamp counterpart).
There is no way to write a moment relative to now, so the closest expressible query is:

- { field: modifiedAt, op: lt, value: CURRENT_TIMESTAMP }

which matches every row ever modified. The author's only remaining options are to store the
deadline as a column and keep it updated (modelling the clock into the data), or to drop out of
schedules: into a hand-written scheduled job that runs the very query the format can otherwise
describe. The second is what happens in practice — and it is a hand-off that buys nothing, because
both the query and the action were already inside the boundary.

Relative moments are already normal one construct over: a report filter compares against the current
date (filter: "due <= CURRENT_DATE AND balance > 0"). The schedule's where is the one place a
time window cannot be stated.

The proposed shape

An offset on the existing moment tokens, in the existing value slot — no new operator, no new key:

schedules:
  - name: stuckProvisioning
    cron: "0 */5 * * * ?"
    entity: TenantApplication
    where:
      - { field: provisioningStatus, op: eq, value: Provisioning }
      - { field: modifiedAt,         op: lt, value: "CURRENT_TIMESTAMP-PT30M" }
    notify:
      to: ops@example.com
      subject: "Tenant application {id} has been provisioning for over 30 minutes"
      body: "It may need an operator."

  - name: unansweredQuotations
    cron: "0 0 8 * * ?"
    entity: Quotation
    where:
      - { field: status,   op: eq, value: Sent }
      - { field: sentOn,   op: lt, value: "CURRENT_DATE-P7D" }
    notify: { to: "owner.email", subject: "Quotation {id} has had no answer for a week" }

Expected behaviour

Normative, stated platform-neutrally.

• A value of the form <moment token><+|-><ISO-8601 duration> resolves, at each firing, against
  that run's clock. CURRENT_DATE-P7D is seven days before today; CURRENT_TIMESTAMP-PT30M is
  thirty minutes ago. The forward form (+) is admitted symmetrically, for "falls due within the
  next week" sweeps.
• The result is compared in the queried field's own shape — a date field against a date, a
  timestamp field against an instant — the same rule defaults: { X: now } already follows for
  the target field's shape.
• An offset applied to a non-temporal field is a parse error, named as such, rather than a
  comparison that silently never matches.
• This is a moment vocabulary, not an expression language: exactly one offset on one token. No
  arithmetic between fields, no nesting, no other operators — a generator rejects anything else, so
  the construct cannot grow into an embedded query language by accident.
• Existing files are unaffected: today's values keep their meaning exactly.

Prior art / workarounds

A hand-written scheduled job that re-implements the query and then calls the same notification the
format would have generated. Or a stored "deadline" column, maintained by every writer, so the
comparison can be against a literal — modelling the clock into the data to work around a missing
value form. The requirement came out of a provisioning service that needed one sweep for runs stuck
mid-flight, and had to write a job for it.

[delchev]

Implementation tracked in the Dirigible reference implementation as eclipse-dirigible/dirigible#6764 — it follows this proposal rather than leading it.