"""harness/windows.py — the DATE WINDOW vocabulary (CG-7, owner item 2, 2026-07-26). A filter condition on a measure reads: Where [Sales] [in the last 90 days] [>] [5,000] The middle bracket is this module. It turns a window SPEC — a small JSON object a saved view can persist — into a concrete `(date_from, date_to)` pair of ISO dates. WHY IT IS ITS OWN MODULE, AND WHY IT TAKES `today` AS AN ARGUMENT ----------------------------------------------------------------------------------------------- Two engines must agree on what "this quarter" means: this one (which compiles the SQL) and `customer-grid/windows.ts` (which renders the label the user reads and the bounds the client engine would use). If they disagree by one day, the grid shows a number the label denies, and nothing errors. `aios-web/verify_windows.py` holds them in lock-step over a fixed set of probe dates, exactly as verify_filter_engine.py does for the filter tree. `today` is a PARAMETER, never `date.today()` read inside. Three reasons, all learned: - a gate cannot compare two engines on "now" — the two runs are milliseconds apart and land on different sides of midnight roughly once every few thousand runs, which is the worst kind of flake because it looks like a real divergence - a window resolved during a request must not shift between the count query and the row query - the tenant's day boundary is the tenant's, not the server's DESIGN NOTES THAT ARE EASY TO GET WRONG ----------------------------------------------------------------------------------------------- - Bounds are INCLUSIVE at both ends and are DATES, not timestamps. `store_query` casts both sides to TIMESTAMP and appends 23:59:59 to the upper bound, so a bare date is correct there; the client compares the 10-char ISO prefix, so a bare date is correct there too. - "Last N days" INCLUDES today. Airtable's "the past week" means the last 7 days up to and including now, not the 7 days before yesterday. Off-by-one here silently drops today's orders from every "recent" filter — invisible until someone asks why a sale they just entered is missing. - Week starts MONDAY (ISO 8601). Stated rather than defaulted: Python's weekday() is Monday=0 and JavaScript's getDay() is Sunday=0, so the two engines disagree unless one of them is explicitly corrected. That correction is the single most likely divergence in this file, and the gate probes a Sunday and a Monday for exactly that reason. - A `custom` window with only one bound is legal and means open-ended on the other side. - An UNRESOLVABLE window returns None rather than a default. A window that quietly becomes "all time" would widen a filter while still reporting an authoritative count. """ import datetime as _dt #: Every window kind the UI may offer. Mirrors WINDOW_KINDS in customer-grid/windows.ts; #: verify_windows.py asserts the two lists are identical, because a kind the client can emit and #: the server cannot resolve is a filter that silently stops narrowing. WINDOW_KINDS = ( "all_time", "today", "yesterday", "this_week", "last_week", "this_month", "last_month", "this_quarter", "last_quarter", "this_year", "last_year", "ytd", "ytd_last_year", "ltm", "past_week", "past_month", "past_year", "last_n_days", "next_n_days", "custom", ) #: Kinds that carry an integer `n`. N_KINDS = frozenset({"last_n_days", "next_n_days"}) #: How each kind reads in a sentence. The UI renders these; they are here so the label and the #: arithmetic cannot drift apart in the one place a user would never think to check. WINDOW_LABELS = { "all_time": "all time", "today": "today", "yesterday": "yesterday", "this_week": "this week", "last_week": "last week", "this_month": "this month", "last_month": "last month", "this_quarter": "this quarter", "last_quarter": "last quarter", "this_year": "this year", "last_year": "last year", "ytd": "year to date", # "last year to date" (LYTD), not "year to date, last year": the longer phrasing CLIPPED to # "year to date, last ye" in the 146px window select — and this vocabulary is CLOSED, so # the rule for it is that it stays readable rather than that it fits. Seen in a live # screenshot of the owner's own comparison; every assertion in that run was green. "ytd_last_year": "last year to date", "ltm": "the last 12 months", "past_week": "the past week", "past_month": "the past month", "past_year": "the past year", "last_n_days": "the last {n} days", "next_n_days": "the next {n} days", "custom": "a custom range", } MAX_N = 3650 # ten years; a bound, not a business rule # --- the DATE-VALUE anchors (owner item 3, 2026-07-26) ---------------------------------------- # The second half of a date CONDITION, as distinct from a measure's window: # # Where [Last order] [is before] [one month ago] # ^ op ^ THIS # # A window answers "over what period do I sum"; an anchor answers "which single date am I # comparing against". Both live in this module for one reason: they are the same two-engine # contract, resolved from the same `today`, and a gate that holds one in lock-step and not the # other would leave half the sentence free to drift. #: Anchor modes, in the order the picker offers them. `exact` is the historical behaviour — a #: rule with NO mode is an `exact` rule whose value is an ISO date, which is what every view #: saved before this change carries. ANCHOR_MODES = ( "today", "yesterday", "one_week_ago", "one_month_ago", "n_days_ago", "exact", ) #: Modes that carry NO value. ⚠ This set is load-bearing far outside this module: a rule whose #: value is blank normally reads as INACTIVE, and an inactive rule is IGNORED — which WIDENS the #: result under a count nobody would doubt. Every activeness check (`isRuleActive` in TS, #: `filter_sql.is_rule_active`, and the validator's value handling) has to know these four are #: active with an empty value. ANCHOR_VALUE_FREE = frozenset({"today", "yesterday", "one_week_ago", "one_month_ago"}) ANCHOR_LABELS = { "today": "today", "yesterday": "yesterday", "one_week_ago": "one week ago", "one_month_ago": "one month ago", "n_days_ago": "{n} days ago", "exact": "an exact date", } def _iso(d): return d.isoformat() def _month_start(d): return d.replace(day=1) def _month_end(d): return _next_month(d.replace(day=1)) - _dt.timedelta(days=1) def _next_month(d): return (d.replace(day=28) + _dt.timedelta(days=4)).replace(day=1) def _quarter_start(d): return _dt.date(d.year, 3 * ((d.month - 1) // 3) + 1, 1) def _days_in_month(y, m): return (_dt.date(y + (m == 12), 1 if m == 12 else m + 1, 1) - _dt.timedelta(days=1)).day def _shift_months(d, n): """`d` moved `n` months, CLAMPING the day to the target month's length. Jan 31 back one month is Dec 31, but Mar 31 back one month is Feb 28 (or 29) — there is no Feb 31 to land on. Both engines must clamp identically or "the past month" differs by up to three days for a third of the calendar; the gate probes a 31st and a leap day for exactly it. """ total = (d.year * 12 + (d.month - 1)) + n y, m = divmod(total, 12) m += 1 return _dt.date(y, m, min(d.day, _days_in_month(y, m))) def _parse_date(v): """Accept 'YYYY-MM-DD' (and tolerate a longer ISO timestamp by taking its date part).""" if isinstance(v, _dt.date): return v s = str(v or "").strip()[:10] if not s: return None try: return _dt.date.fromisoformat(s) except ValueError: return None def normalize(spec): """Coerce an untrusted window spec to `{kind, n?, from?, to?}` or None. Fail-closed on the KIND (an unknown kind is not a window), tolerant on the rest — the same split `clean_filter_tree` uses, so one malformed window cannot cost a user their saved view. """ if not isinstance(spec, dict): return None kind = spec.get("kind") if kind not in WINDOW_KINDS: return None out = {"kind": kind} if kind in N_KINDS: try: n = int(spec.get("n")) except (TypeError, ValueError): return None if n < 1 or n > MAX_N: return None out["n"] = n if kind == "custom": f, t = _parse_date(spec.get("from")), _parse_date(spec.get("to")) if f is None and t is None: return None # a custom range with no bounds is not a window if f is not None and t is not None and f > t: f, t = t, f # the builder cannot enforce order; the engine can if f is not None: out["from"] = _iso(f) if t is not None: out["to"] = _iso(t) return out def resolve(spec, today): """Window spec + the tenant's today -> `(date_from, date_to)`, both inclusive ISO dates. Either side may be None, meaning open-ended. Returns None when the spec is not a window at all — callers MUST treat that as "this condition cannot be evaluated" and refuse, never as "no window", which would silently widen the result to all time. """ spec = normalize(spec) if spec is None: return None kind = spec["kind"] d = _parse_date(today) if d is None: raise ValueError("resolve() needs an explicit `today` — see this module's docstring") if kind == "all_time": return (None, None) if kind == "today": return (_iso(d), _iso(d)) if kind == "yesterday": y = d - _dt.timedelta(days=1) return (_iso(y), _iso(y)) # ISO 8601: the week starts MONDAY. Python's weekday() is already Monday=0; the TS mirror # has to correct getDay(), which is Sunday=0. The gate probes both a Sunday and a Monday. if kind == "this_week": start = d - _dt.timedelta(days=d.weekday()) return (_iso(start), _iso(start + _dt.timedelta(days=6))) if kind == "last_week": start = d - _dt.timedelta(days=d.weekday() + 7) return (_iso(start), _iso(start + _dt.timedelta(days=6))) if kind == "this_month": return (_iso(_month_start(d)), _iso(_month_end(d))) if kind == "last_month": prev = _month_start(d) - _dt.timedelta(days=1) return (_iso(_month_start(prev)), _iso(prev)) if kind == "this_quarter": qs = _quarter_start(d) qe = _month_end(_dt.date(qs.year, qs.month + 2, 1)) return (_iso(qs), _iso(qe)) if kind == "last_quarter": prev_end = _quarter_start(d) - _dt.timedelta(days=1) qs = _quarter_start(prev_end) return (_iso(qs), _iso(prev_end)) if kind == "this_year": return (_iso(_dt.date(d.year, 1, 1)), _iso(_dt.date(d.year, 12, 31))) if kind == "last_year": return (_iso(_dt.date(d.year - 1, 1, 1)), _iso(_dt.date(d.year - 1, 12, 31))) if kind == "ytd": # year-to-date ENDS TODAY, unlike this_year which runs to Dec 31. The distinction is the # whole reason both exist: comparing "this year" against last year double-counts the # months that have not happened yet. return (_iso(_dt.date(d.year, 1, 1)), _iso(d)) if kind == "ytd_last_year": # SAME PERIOD last year — Jan 1 LY through today's month/day LY. This is the honest # partner of `ytd`, and it is not a nicety: it mirrors `core.periods.ytd_last_year` # EXACTLY (including the Feb 29 -> Feb 28 clamp), which is what `pool()` computes the # `revenue_ly` column from. `Sales[ytd] < Sales[ytd_last_year]` therefore reproduces the # retired `at_risk > 0` condition rather than approximating it. Comparing `ytd` against # `last_year` instead would pit seven months against twelve. return (_iso(_dt.date(d.year - 1, 1, 1)), _iso(_shift_months(d, -12))) if kind == "ltm": # the last twelve months INCLUDING today: 365 days back, both ends inclusive return (_iso(d - _dt.timedelta(days=364)), _iso(d)) # "The past week/month/year" are ROLLING and end TODAY — Airtable's wording, and NOT the # calendar kinds above: `last_week` is the previous Monday-Sunday, `past_week` is the seven # days ending today. Both are offered because a user means different things by them, and # picking one to serve both would silently answer the other question. # past_week = the same rule as last_n_days(7) — one period back, PLUS ONE DAY, so today # is included and the span is exactly 7 days rather than 8. # past_year agrees with `ltm` except across a leap day, where the calendar shift keeps the # same month/day and the 364-day subtraction cannot. if kind == "past_week": return (_iso(d - _dt.timedelta(days=6)), _iso(d)) if kind == "past_month": return (_iso(_shift_months(d, -1) + _dt.timedelta(days=1)), _iso(d)) if kind == "past_year": return (_iso(_shift_months(d, -12) + _dt.timedelta(days=1)), _iso(d)) if kind == "last_n_days": # INCLUSIVE of today — "the last 7 days" is today and the 6 before it, not the 7 before # yesterday. Getting this wrong drops today's orders from every recent-activity filter. return (_iso(d - _dt.timedelta(days=spec["n"] - 1)), _iso(d)) if kind == "next_n_days": return (_iso(d), _iso(d + _dt.timedelta(days=spec["n"] - 1))) if kind == "custom": return (spec.get("from"), spec.get("to")) raise AssertionError(f"unhandled window kind {kind!r}") # unreachable; WINDOW_KINDS is closed def label(spec): """How a window reads in the condition sentence ('in the last 90 days').""" spec = normalize(spec) if spec is None: return "an invalid range" return WINDOW_LABELS[spec["kind"]].format(n=spec.get("n")) def resolve_anchor(mode, value, today): """A date condition's right-hand side -> ONE inclusive ISO date, or None. `mode` is an entry of ANCHOR_MODES; None/'' means `exact`, which is what every view saved before anchors existed carries (op + an ISO date in `value`). `value` supplies the number for `n_days_ago` and the date for `exact`, and is ignored by the four value-free modes. Returns None when the anchor cannot be resolved — an unknown mode, a non-numeric N, a value that is not a date. The caller must then treat the CONDITION as unanswerable and match nothing. It must NOT fall through to "no condition": that widens the result while the count beside it still looks authoritative, which is the whole reason this returns None rather than a best guess (same contract as `resolve()` above). `n_days_ago` accepts n = 0 where a WINDOW requires n >= 1. A zero-day window is empty and could only be a mistake; "0 days ago" is today, which is a date a person can mean. """ m = mode or "exact" if m not in ANCHOR_MODES: return None d = _parse_date(today) if d is None: raise ValueError("resolve_anchor() needs an explicit `today` — see this module's " "docstring on why the clock is never read here") if m == "today": return _iso(d) if m == "yesterday": return _iso(d - _dt.timedelta(days=1)) if m == "one_week_ago": # A week ago is a DATE (today minus 7), not the past-week RANGE. The two read almost # identically in English and mean different things in a comparison. return _iso(d - _dt.timedelta(days=7)) if m == "one_month_ago": return _iso(_shift_months(d, -1)) if m == "n_days_ago": s = str("" if value is None else value).strip() # `[0-9]` not `\d`: a Python \d matches every Unicode decimal digit, so a fullwidth # '３０' would resolve here and be rejected by the TS mirror's Number() — the exact # two-engine divergence filter_sql.to_num was rewritten to avoid. if not s or not all("0" <= ch <= "9" for ch in s): return None n = int(s) # No `n < 0` guard: the digit test above already excludes a sign, so it would be # unreachable. The negative control proved that — removing it changed nothing, which is # how dead code hides in a defensive-looking line. The CAP is the live guard. if n > MAX_N: return None return _iso(d - _dt.timedelta(days=n)) parsed = _parse_date(value) # exact return None if parsed is None else _iso(parsed) def anchor_label(mode, value): """How an anchor reads in the condition sentence ('one month ago', '30 days ago').""" m = mode or "exact" if m not in ANCHOR_MODES: return "an invalid date" if m == "n_days_ago": return ANCHOR_LABELS[m].format(n=str("" if value is None else value).strip() or "N") if m == "exact": return str("" if value is None else value).strip() or ANCHOR_LABELS[m] return ANCHOR_LABELS[m]