Feature requests: relative date operator + ordered-relation selection (first/last/firstN/lastN) with predicates

[samfarber00]

Two primitives that would let Zealot's advocate health/status rules run natively through toPrisma. Context: deterministic advocate scoring + status transitions where rules are the only decider; LLM output is persisted as structured, append-only per-advocate records the rules reason against. Aron asked for these to be filed here (Slack, 2026-06-10).

1. Relative date operator

Today date conditions only accept absolute ISO dates, so windowed conditions require the caller to pre-compute cutoffs:

// works today
{ field: 'created_at', dateOperator: 'after', value: '2026-04-11T00:00:00Z' }

// requested: resolved to an absolute date at evaluation time
{ field: 'created_at', dateOperator: 'inLast', value: 60, unit: 'days' }

Canonical use: "4+ reference asks in the last 60 days" as a stored rule that stays correct on every evaluation without the FE recalculating dates.

2. Ordered-relation selection with predicate (first / last / firstN / lastN)

Select record(s) from a relation by ordering (+ optional predicate), then apply conditions to the selected record(s):

// requested shape (illustrative)
{
  field: 'advocateFindings',
  select: { mode: 'last', count: 1, orderBy: { field: 'createdAt', direction: 'desc' } },
  where: { field: 'source', operator: 'equals', value: 'gong' },
  condition: { field: 'sentiment', operator: 'equals', value: 'positive' }
}

Canonical use: per-advocate findings records are append-only with history; a rule needs "the most recent Gong record has sentiment = positive". lastN enables trend checks later ("last 3 channel scores declining").

Both need toPrisma support (the existing atLeast/atMost/exactly + nested-condition translation already covers the windowed-count half once relative dates exist).

Happy to add more detail or test cases on either.