Investigate: can a custom-SQL aggregation measure reach the ObjectQL aggregate bridge? (the nine-vs-six `function` mismatch behind #11833)

[os-support-ai]

Chartered in #11833's ruling (maintainer, 2026-08-25, verbatim 「其他接受」; full record = #11833 comment 5406725658). Filed by PM session session_01KWRU3s15AJz7PGW7a7wdCh per the confirmed checklist.

The measured mismatch

Measured on #11833's PR #12011 investigation:

• service-analytics's consumer-local type declares aggregations[].function as string;
• IDataEngine.aggregate declares a six-value enum: count | sum | avg | min | max | count_distinct;
• the authorable AggregationMetricType carries nine values — those six plus number / string / boolean (custom SQL expressions).

⇒ The analytics bridge can, on its declared types, forward a method the engine contract does not admit. The local string widening is what hides the gap today.

The question to answer (measurement first, ⛔ no fix before the answer)

Can an authored custom-SQL measure (number/string/boolean) actually reach the ObjectQL aggregate path at runtime, or is it routed to NativeSQLStrategy before it gets there?

• If routed away (intended split) → the bridge should narrow or reject explicitly and loudly, and the contract member can then be substituted as-is in finding: two consumer-local structural DataEngineLike re-declarations remain after the #11493 deletion (datasource-admin-plugin, service-analytics) #11833's analytics half.
• If reachable (real defect) → it is silently mishandled. ⭐ The closed A number/string/boolean metric's SQL expression is replaced by COUNT(*) #4157 is this exact class biting before: a custom-SQL metric's expression was replaced by COUNT(*). Report the reading and the failure shape; the repair is scoped from there.

Non-negotiables

⛔ Do not close this by widening the local type back to string — that is the concealment, not the fix. ⛔ Do not widen the contract enum from the consumer side (that is a spec-seat contract change if it turns out to be wanted — fork it back). Positive control required: prove the probe reaches the bridge with a measure that is admitted, before concluding anything about one that is not.

Acceptance criterion: a written verdict — routed-away or reachable — with the runtime evidence and the named routing site, plus (if reachable) the observed mishandling; #11833's analytics half consumes the answer.

Refs: #11833 (parent finding, fork 2) · PR #12011 · #4157 (the closed prior instance) · #10576 (the per-aggregation filter)

[yinlianghui-tw]

Triage: domain:services — the probe's subject is the service-analytics bridge into the ObjectQL aggregate path, its verdict is consumed by #11833's analytics half, and both stay in the services lane (parent #11833 is domain:services). Maintainer charter and non-negotiables already on the card; queue label and Bug type already set.

---

Generated by Claude Code

[os-trump]

Claim: PM loop round 3 (services seat)
Session: session_01UQgPSniH1GFM9ZDeGyuGUa
Branch: claude/issue-12053-custom-sql-measure-bridge-probe
Worktree: objectstack-issue-12053
Domain: domain:services
File surface: measurement first — expected to be a probe/test surface under packages/services/service-analytics/** and nothing else. ⛔ No production edit is authorized by this claim: the card's own charter is "measurement first, no fix before the answer". If the verdict is "reachable", the repair is scoped from the reading — stop and report rather than repairing in the same run.
Container & model: M, mode:subagent, model: opus — this round's node scripts/pm/dispatch-gates.mjs --tier reports no path-derived mandate; PM sets the default judgment tier. Not Clause-② as claimed: a measurement changes no contract behaviour and widens no surface. ⚠️ If the run turns into a repair, that judgment is re-made on the repair's content, not inherited from here.
Serial constraints cleared: service-analytics is free — the last writer, #11461's PR #12022, MERGED at 09:50Z (content-verified on origin/main: the measure-filter refusal arm is in objectql-strategy.ts). ⚠️ That file therefore moved today: re-derive every anchor from the merged ref, and note the new third refusal arm sits on the same path this probe drives. Wave siblings: #11900 (plugin-auth) — disjoint.

Premise re-verified at claim, on origin/main

The nine-vs-six mismatch is live: packages/spec/src/data/analytics.zod.ts still declares AggregationMetricType with the custom-SQL members (number at :30, with string/boolean beside it) beyond the engine contract's six. Nothing closed it since filing.

The question, and the two verdicts that answer it

Can an authored custom-SQL measure (number/string/boolean) actually reach the ObjectQL aggregate path at runtime, or is it routed to NativeSQLStrategy before it gets there?

• Routed away (intended split) → say so, name the routing site, and the bridge can then narrow or reject explicitly; finding: two consumer-local structural DataEngineLike re-declarations remain after the #11493 deletion (datasource-admin-plugin, service-analytics) #11833's analytics half consumes that.
• Reachable (real defect) → report the reading and the observed mishandling shape. ⭐ The card names the prior instance: closed A number/string/boolean metric's SQL expression is replaced by COUNT(*) #4157, where a custom-SQL metric's expression was replaced by COUNT(*). Look for that shape specifically; do not assume it is the only one.

A positive control is required and is not optional: prove the probe reaches the bridge with a measure that is admitted, before concluding anything about one that is not. A probe that never arrived cannot distinguish "routed away" from "my harness is broken", and those are the two answers this card exists to separate.

Non-negotiables, carried verbatim from the maintainer's charter

• ⛔ Do not close this by widening the local type back to string — that is the concealment, not the fix.
• ⛔ Do not widen the contract enum from the consumer side — that is a spec-seat contract change if it turns out to be wanted; fork it back instead.

Acceptance

A written verdict — routed-away or reachable — with the runtime evidence and the named routing site, plus the observed mishandling if reachable. ⭐ A verdict with no PR is a complete, valuable delivery here, and premise_still_valid: false is equally legitimate if the mismatch turns out not to hold on the merged ref. Do not manufacture a diff to look productive.

Refs: #11833 (parent finding, fork 2) · PR #12011 · #4157 (the closed prior instance) · #10576 (the per-aggregation filter) · PR #12022 (landed today on the same file)

---

Generated by Claude Code

[os-trump]

Verdict: REACHABLE — it is not routed away, and it is silently mishandled

Measured on origin/main @ 22c42c9b2 (the merged ref, after PR #12022 landed on objectql-strategy.ts at 09:50Z — every anchor below is re-derived from that ref, not from the card or #12011).

An authored custom-SQL measure (AggregationMetricType = number / string / boolean) does reach the ObjectQL engine.aggregate bridge, un-refused, and arrives there as an aggregation the engine contract does not admit. No routing site keeps it away.

PM mechanism assumptions

1. Nine-vs-six mismatch is live — confirmed. AggregationMetricType at packages/spec/src/data/analytics.zod.ts:23-32 declares nine (number at :30). The engine side is AggregationNodeSchema.function = AggregationFunction (packages/spec/src/data/query.zod.ts:263, enum at :149) — six. Asserted against both schemas at runtime, not restated.
2. objectql-strategy.ts moved today — accounted for. All anchors re-derived from the merged ref; fix(service-analytics): refuse a cross-object per-measure filter on both ObjectQL doors #12022's third cross-object refusal arm is at :947 and is discussed below (it turns out to be the contrast case).
3. service-analytics free of in-flight claims — held.

The routing sites, named

There is no measure-type gate on either side of the split:

• ObjectQLStrategy.canHandle — packages/services/service-analytics/src/strategies/objectql-strategy.ts:118-122. It tests query.cube, caps.objectqlAggregate and typeof ctx.executeAggregate === 'function'. It never inspects query.measures. Nothing about a metric's type can make this strategy decline.
• NativeSQLStrategy.canHandle — packages/services/service-analytics/src/strategies/native-sql-strategy.ts:134-247. Its decline arms are: granularity-bucketed time dimension (:144), ADR-0062 external/federated object, [spec] service-analytics' read-scope / Cube filter compilers still refuse $field, so a CEL field-to-field RLS rule 400s on those faces #7598 cross-field $field comparand, An unparseable date comparand on a datetime filter is passed through and compares false — HTTP 200, zero rows, no diagnostic — while an unknown {placeholder} is correctly rejected 400 (17.0.0 GA) #8690 uninterpretable temporal comparand, then the caps.nativeSql capability leg. None is measure-typed.

So the split is by driver capability and query shape, never by metric type. Two ordinary ways in:

• a driver with objectqlAggregate but no raw SQL (Mongo, driver-memory, driver-rest) — ObjectQL wins outright;
• a fully raw-SQL-capable driver plus a granularity — NativeSQLStrategy declines at :144 and hands the query to ObjectQLStrategy. A date-bucketed trend chart over a custom-SQL metric is the ordinary way to land here.

The mishandling shape

ObjectQLStrategy.resolveMeasureAggregation (objectql-strategy.ts:1258-1266) forwards the metric's declared type verbatim as the aggregation method:

method: direct.type === 'count_distinct' ? 'count_distinct' : direct.type,

For an authored { type: 'number', sql: 'SUM(revenue - cost) / NULLIF(SUM(revenue), 0)' } the bridge therefore receives:

{ "field": "SUM(revenue - cost) / NULLIF(SUM(revenue), 0)", "method": "number", "alias": "margin_pct" }

Both halves are wrong: method is not an engine function, and field is not a column but the metric's whole SQL expression. plugin.ts:304 (the default production auto-bridge) then renames it function: a.method verbatim, so engine.aggregate is called with function: 'number'.

Downstream that lands two different ways, neither of which names the real cause:

Backend	Result	Site
driver-sql (Postgres/MySQL/SQLite)	INVALID_QUERY / 400 — "Aggregate function "number" is not a declared aggregate function… Fix the function key of the aggregations[] entry"	packages/drivers/driver-sql/src/sql-driver.ts:1266-1277 (pinned by sql-driver-11455-aggregate-fault-envelope.test.ts:277)
objectql in-memory fallback (driver-memory, driver-rest, non-UTC date buckets)	null for every bucket, silently	packages/objectql/src/in-memory-aggregation.ts:234 — default: out[alias] = null

Measured directly against the built engine:

POSITIVE CONTROL (sum): [{"status":"open","total_revenue":300},{"status":"won","total_revenue":300}]
CUSTOM-SQL (number) : [{"status":"open","margin_pct":null},{"status":"won","margin_pct":null}]

The SQL-driver arm is loud but misdirected — it blames a function key the author never wrote, for a metric the spec says is legal to author. The in-memory arm is the #4157 class exactly: a plausible value aliased under the author's metric name, no error, no log. Not the COUNT(*) variant the card starred, but the same failure shape in its null variant.

Why this exists: #4157 was fixed on one strategy of two

NativeSQLStrategy closed this gap — EXPRESSION_METRIC_TYPES (native-sql-strategy.ts:107), the expression arm at :825, and an explicit throw for anything unrecognised. metric-type-coverage.test.ts pins that the two sets partition the spec vocabulary.

ObjectQLStrategy never got the matching partition. And #4157's own regression test, __tests__/measure-expression-sql.test.ts, imports NativeSQLStrategy only and forces objectqlAggregate: false (:51) — so the pin that was supposed to prevent recurrence covers exactly one of the two strategies. EXPRESSION_METRIC_TYPES has no reader outside native-sql-strategy.ts and that one test.

The refusal already exists — just not on this path

planCrossObject guards every measure against RECOMBINABLE_METHODS at objectql-strategy.ts:947 and refuses a non-recombinable measure with an INVALID_FIELD envelope naming the measure. So a custom-SQL measure is refused loudly when a cross-object dimension is involved, and sails through on the direct path. That is the shape the direct path is missing, and it is already in this file.

A correction to my own prediction (reported per the reverse-verification discipline)

I predicted the terminal fallback return { field: '*', method: 'count' } (objectql-strategy.ts:1287) would make an undeclared measure answer COUNT(*) — #4157's question 1. That prediction was wrong. Measured reading for a typo'd measure total_amont:

[{"field":"total_amont","method":"sum","alias":"total_amont"}]

ensureCube mints the missing member through inferMeasure (analytics-service.ts:2192-2210), whose documented default is sum(<key>), before the strategy is reached. So that COUNT(*) line is not reached through AnalyticsService.query; it is defensive-only on this path. Flagging it for the repair seat as an observation, not a filed card — it lives inside the very function the repair will touch.

Evidence — runtime, with the required positive control

Ran via the shared verify lock; os-verify-lock: VERDICT command-exit 0, Tests 6 passed (6).

• Positive control (mandatory, passed): an admitted type: 'sum' measure on the identical harness reaches executeAggregate as { field: 'amount', method: 'sum', alias: 'total_amount' }. The probe demonstrably arrives, so the custom-SQL reading cannot be "my harness is broken".
• Reverse verification: mutating only objectql-strategy.ts:1265 to refuse expression types turned exactly the predicted three tests red (REACHABLE, ROUTING, CONTRAST) and left the other three green — Tests 3 failed | 3 passed (6). Mutation confirmed on disk before the run (injected marker count 1, original anchor count 0) and restored via an EXIT/INT/TERM trap; restore confirmed byte-clean (git status clean, anchor count back to 1) and the restore-leg re-run returned 6 passed. This proves the probe is sensitive to that exact line — i.e. that :1265 is the site, not a site.
• The cube is parsed through the real CubeSchema, so what was measured is a genuinely authorable surface.

On the durable artifact — deliberately NOT landed

No PR, no diff, no changeset. The probe earns its keep as a measurement, not as a pin, for three reasons:

1. Three of its six assertions bless today's wrong value (method === 'number'). Landing them would make main assert that the defect is correct — and any future refactor that accidentally fixed it would turn CI red and read as a regression.
2. The repo's durable idiom for this exact vocabulary is a partition test (metric-type-coverage.test.ts). The correct durable artifact for ObjectQL is that same partition assertion — which fails today, so it belongs to the repair PR where it turns green, not to a green-CI measurement branch.
3. The card's own charter: measurement first, and a verdict with no PR is a complete delivery.

The probe source is handed over below so the repair seat can reproduce the reading verbatim rather than re-deriving it. Branch claude/issue-12053-custom-sql-measure-bridge-probe was pushed empty as a write-route probe and has been deleted; nothing was left behind.

Probe source — packages/services/service-analytics/src/__tests__/custom-sql-measure-objectql-bridge.test.ts (6 tests, all green on the merged ref)

// Copyright (c) 2026 ObjectStack. Licensed under the Apache-2.0 license.

/**
 * MEASUREMENT (#12053): can an authored custom-SQL aggregation measure
 * (`AggregationMetricType` `number` / `string` / `boolean`) reach the ObjectQL
 * `engine.aggregate` bridge, or is it routed to `NativeSQLStrategy` first?
 *
 * These tests are DESCRIPTIVE, not prescriptive. They pin what the code does
 * today so the reading behind #12053's verdict is reproducible.
 */

import { describe, it, expect } from 'vitest';
import {
 CubeSchema,
 AggregationMetricType,
 AggregationFunction,
 AggregationNodeSchema,
 type Cube,
} from '@objectstack/spec/data';
import { AnalyticsService } from '../analytics-service.js';
import {
 SUPPORTED_AGGREGATE_SQL_KEYS,
 EXPRESSION_METRIC_TYPES,
} from '../strategies/native-sql-strategy.js';

const silentLogger = {
 info: () => {}, warn: () => {}, error: () => {}, debug: () => {},
} as never;

/**
 * An AUTHORED cube carrying both halves of the vocabulary:
 * - `total_amount` (`type: 'sum'`) — admitted by the engine contract. This
 * is the POSITIVE CONTROL: it proves the probe actually reaches the bridge.
 * - `margin_pct` (`type: 'number'`) — a custom SQL expression, one of the
 * three members the engine contract does NOT admit.
 * Both go through `CubeSchema.parse`, so both are things an author can really
 * write today.
 */
const CUBE_LITERAL = {
 name: 'orders',
 title: 'Orders',
 sql: 'orders',
 measures: {
 total_amount: { name: 'total_amount', label: 'Total Amount', type: 'sum', sql: 'amount' },
 margin_pct: {
 name: 'margin_pct',
 label: 'Margin %',
 type: 'number',
 sql: 'SUM(revenue - cost) / NULLIF(SUM(revenue), 0)',
 },
 },
 dimensions: {
 status: { name: 'status', label: 'Status', type: 'string', sql: 'status' },
 created_at: {
 name: 'created_at', label: 'Created At', type: 'time', sql: 'created_at',
 granularities: ['day', 'week', 'month'],
 },
 },
 public: false,
};

/** Calls captured at the `executeAggregate` seam — the ObjectQL bridge itself. */
type AggCall = {
 object: string;
 aggregations?: Array<{ field: string; method: string; alias: string }>;
 groupBy?: unknown;
};

function makeService(captured: AggCall[], caps: { nativeSql: boolean }) {
 const cube = CubeSchema.parse(CUBE_LITERAL) as unknown as Cube;
 return new AnalyticsService({
 cubes: [cube],
 logger: silentLogger,
 queryCapabilities: () => ({
 nativeSql: caps.nativeSql,
 objectqlAggregate: true,
 inMemory: false,
 }),
 // Present so `NativeSQLStrategy.canHandle`'s capability leg can pass when
 // `nativeSql` is true — the decline under measurement must come from a
 // real routing arm, not from a missing raw-SQL bridge.
 executeRawSql: async () => [],
 executeAggregate: async (object: string, options: Record<string, unknown>) => {
 captured.push({
 object,
 aggregations: options.aggregations as AggCall['aggregations'],
 groupBy: options.groupBy,
 });
 return [];
 },
 } as never);
}

describe('#12053 — the authored vocabulary vs. the engine contract', () => {
 it('the nine-vs-six mismatch is live: three authorable metric types are not engine functions', () => {
 const authorable = [...AggregationMetricType.options].sort();
 const engineAdmits = [...AggregationFunction.options].sort();

 expect(authorable).toEqual(
 ['avg', 'boolean', 'count', 'count_distinct', 'max', 'min', 'number', 'string', 'sum'],
 );
 expect(engineAdmits).toEqual(['avg', 'count', 'count_distinct', 'max', 'min', 'sum']);

 const notEngineFunctions = authorable.filter(
 (t) => !(engineAdmits as string[]).includes(t),
 );
 expect(notEngineFunctions).toEqual(['boolean', 'number', 'string']);
 // Same three the native-SQL path classifies as expressions (#4157).
 expect([...EXPRESSION_METRIC_TYPES].sort()).toEqual(notEngineFunctions);
 expect([...SUPPORTED_AGGREGATE_SQL_KEYS].sort()).toEqual(engineAdmits);
 });
});

describe('#12053 — does a custom-SQL measure reach the ObjectQL aggregate bridge?', () => {
 it('POSITIVE CONTROL: an admitted measure (`sum`) reaches `executeAggregate`', async () => {
 const captured: AggCall[] = [];
 const svc = makeService(captured, { nativeSql: false });

 await svc.query({
 cube: 'orders',
 measures: ['total_amount'],
 dimensions: ['status'],
 });

 // The probe ARRIVED. Without this, a later "no call" reading could not tell
 // "routed away" from "the harness never worked" — which is the whole point.
 expect(captured).toHaveLength(1);
 expect(captured[0].object).toBe('orders');
 expect(captured[0].aggregations).toEqual([
 { field: 'amount', method: 'sum', alias: 'total_amount' },
 ]);
 });

 it('REACHABLE: a custom-SQL measure (`type: number`) also reaches the bridge, un-refused', async () => {
 const captured: AggCall[] = [];
 const svc = makeService(captured, { nativeSql: false });

 await svc.query({
 cube: 'orders',
 measures: ['margin_pct'],
 dimensions: ['status'],
 });

 expect(captured).toHaveLength(1);
 const agg = captured[0].aggregations[0];

 // THE READING. `ObjectQLStrategy.resolveMeasureAggregation` forwards
 // `Metric.type` verbatim, so the engine is asked for an aggregation
 // function named "number" — a value `AggregationNodeSchema.function` does
 // not admit — over a `field` that is not a column at all but the metric's
 // whole SQL EXPRESSION.
 expect(agg.method).toBe('number');
 expect(agg.field).toBe('SUM(revenue - cost) / NULLIF(SUM(revenue), 0)');
 expect(agg.alias).toBe('margin_pct');

 expect((AggregationFunction.options as string[])).not.toContain(agg.method);
 });

 it('the emitted node is REJECTED by the engine aggregation contract it is bound for', () => {
 // `plugin.ts`'s auto-bridge — the default production wiring when no custom
 // `executeAggregate` is supplied — renames the strategy's `method` to the
 // contract's `function` verbatim (`function: a.method`).
 const bridged = {
 function: 'number',
 field: 'SUM(revenue - cost) / NULLIF(SUM(revenue), 0)',
 alias: 'margin_pct',
 };
 const parsed = AggregationNodeSchema.safeParse(bridged);
 expect(parsed.success).toBe(false);

 // Positive control on the assertion itself: the same node with an admitted
 // function parses, so the failure above is about `function`, not the shape.
 const ok = AggregationNodeSchema.safeParse({ ...bridged, function: 'sum' });
 expect(ok.success).toBe(true);
 });

 it('ROUTING: a native-SQL driver does NOT protect the path — a granularity re-routes here', async () => {
 // `NativeSQLStrategy` (priority 10) wins on a raw-SQL driver and handles
 // custom-SQL metrics correctly (#4157). But its `canHandle` declines a
 // granularity-bucketed time dimension, and that decline hands the query to
 // `ObjectQLStrategy`.
 const captured: AggCall[] = [];
 const svc = makeService(captured, { nativeSql: true });

 await svc.query({
 cube: 'orders',
 measures: ['margin_pct'],
 timeDimensions: [{ dimension: 'created_at', granularity: 'month' }],
 });

 expect(captured).toHaveLength(1);
 expect(captured[0].aggregations[0].method).toBe('number');
 });

 it('CONTRAST: the cross-object path DOES refuse the same measure, loudly', async () => {
 // `planCrossObject` guards every measure against `RECOMBINABLE_METHODS`, so
 // a custom-SQL measure is refused there with an `INVALID_FIELD` envelope.
 // The refusal exists already — it is simply not on the direct path.
 const captured: AggCall[] = [];
 const cube = CubeSchema.parse({
 ...CUBE_LITERAL,
 joins: { account: { name: 'accounts', relationship: 'many_to_one', sql: 'account_id' } },
 dimensions: {
 ...CUBE_LITERAL.dimensions,
 account_industry: {
 name: 'account_industry', label: 'Industry', type: 'string', sql: 'account.industry',
 },
 },
 }) as unknown as Cube;

 const svc = new AnalyticsService({
 cubes: [cube],
 logger: silentLogger,
 queryCapabilities: () => ({ nativeSql: false, objectqlAggregate: true, inMemory: false }),
 executeAggregate: async (object: string, options: Record<string, unknown>) => {
 captured.push({ object, aggregations: options.aggregations as AggCall['aggregations'] });
 return [];
 },
 } as never);

 await expect(
 svc.query({
 cube: 'orders',
 measures: ['margin_pct'],
 dimensions: ['account_industry'],
 }),
 ).rejects.toThrow(/cannot be recombined|recombined across/);
 expect(captured).toHaveLength(0);
 });
});

What #11833's analytics half consumes

The answer is reachable, so the "substitute the contract member as-is" branch of #11833 does not apply. Narrowing service-analytics's consumer-local aggregations[].function from string to the engine's six is correct and insufficient on its own — it would make the gap a type error at objectql-strategy.ts:1265 rather than closing it. The repair has to decide what ObjectQL does with the three expression types, and the two non-negotiables rule out both cheap exits (widening the local type back to string; widening the contract enum from the consumer side).

Shape of the choice, for whoever scopes the repair — I am not taking it here, per "no fix before the answer":

• (A) Refuse explicitly on the direct path, mirroring the RECOMBINABLE_METHODS refusal already at :947 — an INVALID_FIELD envelope naming the measure and telling the author this metric needs a native-SQL driver. Turns a silent null into a loud, correctly-attributed answer, and matches the "declared = enforced" direction. Does not make the metric work on ObjectQL.
• (B) Make it work — lower the expression through the aggregate AST. Much larger; the engine contract has no place to put a raw SQL expression, so this becomes a spec-seat conversation, not a consumer-side change.

(A) is the smaller, contract-honest move and is what the existing in-file precedent already does for the cross-object case; (B) is a genuine capability question that belongs to the spec seat. That call is the maintainer's, not mine.

---

Generated by Claude Code

---

Generated by Claude Code

[os-trump]

Correction to the verdict comment above, one factual detail only — the verdict itself is unchanged.

It says the probe branch "has been deleted". It is not deleted. The remote refused the deletion push (send-pack: unexpected disconnect / fatal: the remote end hung up unexpectedly, on three attempts with backoff); ref deletion appears not to be permitted from this session. I am not claiming a cleanup I did not achieve.

Current state of claude/issue-12053-custom-sql-measure-bridge-probe: it exists on the remote, pointing at 22c42c9b21a479b502583ae74ac588232564a6a8 — byte-identical to origin/main at the time it was pushed, zero commits, no PR. It was pushed empty as the first-minute write-route probe and never received a commit; the measurement was run in the worktree and the probe file removed before teardown (git status clean, git diff --stat origin/main empty, no commits vs BASE). So it is inert, not stale work — but it will need deleting by someone whose credentials permit it.

---

Generated by Claude Code

---

Generated by Claude Code

[os-trump]

{
 "issue": 12053,
 "status": "done",
 "branch": "claude/issue-12053-custom-sql-measure-bridge-probe",
 "pr": null,
 "premise_still_valid": true,
 "summary": "VERDICT: REACHABLE, not routed away. An authored custom-SQL measure (AggregationMetricType number/string/boolean) reaches the ObjectQL engine.aggregate bridge un-refused. Named routing sites, re-derived on merged ref 22c42c9b2: ObjectQLStrategy.canHandle (objectql-strategy.ts:118-122) never inspects query.measures; NativeSQLStrategy.canHandle (native-sql-strategy.ts:134-247) declines only on granularity/external-object/cross-field/temporal-comparand/capability, none measure-typed. Mishandling: resolveMeasureAggregation (objectql-strategy.ts:1258-1266) forwards Metric.type verbatim as the aggregation method, so the bridge receives {field: '<the whole SQL expression>', method: 'number'}; plugin.ts:304 renames it function: a.method, so engine.aggregate is called with function:'number'. Downstream splits two ways, neither naming the cause: driver-sql throws INVALID_QUERY/400 blaming a 'function' key the author never wrote (sql-driver.ts:1266-1277), and the objectql in-memory fallback answers null for every bucket SILENTLY (in-memory-aggregation.ts:234 'default: out[alias] = null') — the #4157 class in its null variant rather than the starred COUNT(*) variant. Root cause: #4157 was fixed on ONE strategy of two — NativeSQLStrategy got EXPRESSION_METRIC_TYPES (:107, arm at :825) and its regression test measure-expression-sql.test.ts forces objectqlAggregate:false (:51), so the pin covers exactly one strategy and ObjectQLStrategy never got the matching partition. A correct refusal for this measure class ALREADY exists in the same file at :947 (RECOMBINABLE_METHODS, cross-object path) — it is simply absent from the direct path. NO PR by design: per the card's measurement-first charter and the maintainer's non-negotiables, the repair is scoped separately.",
 "tests": "Dependency closure built first (new worktree): pnpm --filter '@objectstack/service-analytics^...' build via the shared lock — 'os-verify-lock: VERDICT command-exit 0 · held the lock 127s · waited 0s'. PROBE (6 tests, incl. the mandatory positive control): vitest run src/__tests__/custom-sql-measure-objectql-bridge.test.ts — 'VERDICT command-exit 0', 'Test Files 1 passed (1)', 'Tests 6 passed (6)'. POSITIVE CONTROL passed: an admitted type:'sum' measure on the identical harness reaches executeAggregate as {field:'amount', method:'sum', alias:'total_amount'}, so the custom-SQL reading cannot be a broken harness. REVERSE VERIFICATION, direction predicted before running: mutated ONLY objectql-strategy.ts:1265 to refuse expression types; predicted 3 red (REACHABLE, ROUTING, CONTRAST) / 3 green, observed exactly that — 'VERDICT command-exit 1', 'Tests 3 failed | 3 passed (6)'. Mutation confirmed ON DISK before the run (injected marker grep -c = 1, original anchor grep -c = 0, printed as 'MUTATION ON DISK: marker=1 origAnchor=0'), applied with trap '<restore>' EXIT INT TERM; restore leg confirmed byte-clean (marker 0, anchor back to 1, git status showing only the untracked probe) and the restore-leg re-run returned 'Tests 6 passed (6)'. No dist/ablation involved: the probe imports the strategy by intra-package relative path, so vitest runs it from src — no rebuild needed between legs, and none was claimed. ENGINE-SIDE measurement against the BUILT objectql dist (pnpm --filter '@objectstack/objectql...' build — 'VERDICT command-exit 0'): applyInMemoryAggregation with the node the auto-bridge really emits gave POSITIVE CONTROL (sum) [{\"status\":\"open\",\"total_revenue\":300},{\"status\":\"won\",\"total_revenue\":300}] vs CUSTOM-SQL (number) [{\"status\":\"open\",\"margin_pct\":null},{\"status\":\"won\",\"margin_pct\":null}]. A PREDICTION I MADE THAT WAS WRONG, reported rather than dropped: I expected the terminal fallback at objectql-strategy.ts:1287 to make an UNDECLARED measure answer COUNT(*) (#4157's question 1); measured reading is [{\"field\":\"total_amont\",\"method\":\"sum\",\"alias\":\"total_amont\"}] because ensureCube mints the missing member via inferMeasure (analytics-service.ts:2192-2210, documented default sum(<key>)) before the strategy is reached — so that line is defensive-only on this path, flagged to the repair seat inside the verdict. NO GATE UNION OWED AND NONE CLAIMED: final tree is byte-identical to origin/main — 'git status --short' empty, 'git diff --stat origin/main' empty, 'git log 22c42c9b2..HEAD' empty — so there is no changeset for scripts/pm/dispatch-gates.mjs to derive from and no CI-relevant diff. NOT MEASURED, stated as such: no repo-wide lint/gate farm run, because no diff exists to gate.",
 "open_questions": [
 {
 "question": "The verdict is 'reachable', so #11833's 'substitute the contract member as-is' branch does not apply. Narrowing service-analytics's local aggregations[].function from string to the engine's six is correct but insufficient alone — it converts the gap into a type error at objectql-strategy.ts:1265 rather than closing it. What should ObjectQLStrategy DO with the three expression metric types? (Not taken here: the card's charter is measurement first, and the repair is PM/maintainer-scoped.)",
 "options": [
 "A — Refuse explicitly on the direct path, mirroring the RECOMBINABLE_METHODS refusal already at objectql-strategy.ts:947: an INVALID_FIELD envelope naming the measure and saying this metric needs a native-SQL driver. Turns a silent null into a loud, correctly-attributed answer; in-file precedent already exists; does NOT make the metric work on ObjectQL.",
 "B — Make it work: lower the custom SQL expression through the aggregate AST. Substantially larger — the engine aggregation contract has no place to carry a raw SQL expression, so this becomes a spec-seat contract question, not a consumer-side change."
 ],
 "recommendation": "A. Real business need: the authorable surface is live and reachable today, and its current answer on the in-memory path is a silent null under the author's own metric name — a wrong number, which is the harm actually in evidence; B serves a capability nobody has been measured to need. Long-term soundness: A is contract-first and honours ADR-0049 declared=enforced by making the boundary explicit, whereas B widens the engine contract to carry raw SQL, which cuts against the platform's structured-AST direction. AI-authored-metadata safety: A fails loudly at the exact measure the author wrote, which is precisely where an AI-generated cube goes wrong; a silent null is the failure mode that propagates undetected. Startup scope discipline: A is a bounded refusal arm in a file that already contains its twin; B is a new capability across spec + every driver. Note this makes A a partial answer by design — it declares that custom-SQL metrics are native-SQL-only rather than making them portable — so if the maintainer judges that portability IS wanted, that is a spec-seat card, forked back per the card's second non-negotiable, not a consumer-side widening."
 }
 ],
 "out_of_scope_findings": []
}

---

Generated by Claude Code

---

Generated by Claude Code

[os-trump]

Second correction — the probe source in the verdict comment does not compile as posted. I read the comment back after publishing and GitHub's body sanitizer ate four fragments, exactly the way AGENTS.md's "GitHub mutates body BYTES" clause describes (it strips inside fenced code blocks too, so the fence bought nothing). The verdict, the line references and the measured readings are all unaffected — this is only about pasting that file back.

Four edits restore it. Two classes, both <-or-!-shaped:

1. < followed by a letter reads as an HTML tag and is deleted. Two sites, both the executeAggregate stub — posted as options: Record), should be:

executeAggregate: async (object: string, options: Record<string, unknown>) => {

(Array<{ … }> a few lines above SURVIVED, because < there is followed by { rather than a letter — that asymmetry is the tell for this rule.)

2. [ reads as Markdown image syntax and is deleted. Two sites, both the non-null assertion:

• posted const agg = captured[0].aggregations[0]; → should be captured[0].aggregations[0]
• posted expect(captured[0].aggregations[0].method) → should be expect(captured[0].aggregations[0].method)

Also stripped, harmlessly: the <details> / <summary> wrapper around the block (same rule 1), which is why the source renders expanded rather than collapsed. And in the prose, inferMeasure's documented default shows as sum() — it is sum( + <key> + ).

Recording it here rather than silently reposting a patched copy, since the mangling is a property of this comment channel that the next agent handing over code through it will hit too.

---

Generated by Claude Code

---

Generated by Claude Code

[os-trump]

os-dev-report

{
 "issue": 12053,
 "status": "done",
 "branch": "claude/issue-12053-custom-sql-measure-bridge-probe",
 "pr": null,
 "premise_still_valid": true,
 "summary": "VERDICT: REACHABLE, not routed away. An authored custom-SQL measure (AggregationMetricType number/string/boolean) reaches the ObjectQL engine.aggregate bridge un-refused. Named routing sites, re-derived on merged ref 22c42c9b2: ObjectQLStrategy.canHandle (objectql-strategy.ts:118-122) never inspects query.measures; NativeSQLStrategy.canHandle (native-sql-strategy.ts:134-247) declines only on granularity/external-object/cross-field/temporal-comparand/capability, none measure-typed. Mishandling: resolveMeasureAggregation (objectql-strategy.ts:1258-1266) forwards Metric.type verbatim as the aggregation method, so the bridge receives field = THE WHOLE SQL EXPRESSION and method = 'number'; plugin.ts:304 renames it function: a.method, so engine.aggregate is called with function:'number'. Downstream splits two ways, neither naming the cause: driver-sql throws INVALID_QUERY/400 blaming a 'function' key the author never wrote (sql-driver.ts:1266-1277), and the objectql in-memory fallback answers null for every bucket SILENTLY (in-memory-aggregation.ts:234, the switch default assigning null) — the #4157 class in its null variant rather than the starred COUNT(*) variant. Root cause: #4157 was fixed on ONE strategy of two — NativeSQLStrategy got EXPRESSION_METRIC_TYPES (:107, arm at :825) and its regression test measure-expression-sql.test.ts forces objectqlAggregate:false (:51), so the pin covers exactly one strategy and ObjectQLStrategy never got the matching partition. A correct refusal for this measure class ALREADY exists in the same file at :947 (RECOMBINABLE_METHODS, cross-object path) — it is simply absent from the direct path. NO PR by design: per the card's measurement-first charter and the maintainer's non-negotiables, the repair is scoped separately. NOTE: this comment supersedes an identical earlier report whose HTML-comment marker the GitHub body sanitizer deleted, making it invisible to the PM scan; see the two correction comments above for the sanitizer damage and its repairs.",
 "tests": "Dependency closure built first (new worktree): pnpm --filter '@objectstack/service-analytics^...' build via the shared lock — 'os-verify-lock: VERDICT command-exit 0 · held the lock 127s · waited 0s'. PROBE (6 tests, incl. the mandatory positive control): vitest run src/__tests__/custom-sql-measure-objectql-bridge.test.ts — 'VERDICT command-exit 0', 'Test Files 1 passed (1)', 'Tests 6 passed (6)'. POSITIVE CONTROL passed: an admitted type:'sum' measure on the identical harness reaches executeAggregate as field:'amount', method:'sum', alias:'total_amount', so the custom-SQL reading cannot be a broken harness. REVERSE VERIFICATION, direction predicted before running: mutated ONLY objectql-strategy.ts:1265 to refuse expression types; predicted 3 red (REACHABLE, ROUTING, CONTRAST) / 3 green, observed exactly that — 'VERDICT command-exit 1', 'Tests 3 failed | 3 passed (6)'. Mutation confirmed ON DISK before the run (injected marker grep -c = 1, original anchor grep -c = 0, printed as 'MUTATION ON DISK: marker=1 origAnchor=0'), applied with a bash trap on EXIT INT TERM that restores the pristine copy; restore leg confirmed byte-clean (marker 0, anchor back to 1, git status showing only the untracked probe) and the restore-leg re-run returned 'Tests 6 passed (6)'. No dist/ablation involved: the probe imports the strategy by intra-package relative path, so vitest runs it from src — no rebuild needed between legs, and none was claimed. ENGINE-SIDE measurement against the BUILT objectql dist (pnpm --filter '@objectstack/objectql...' build — 'VERDICT command-exit 0'): applyInMemoryAggregation with the node the auto-bridge really emits gave POSITIVE CONTROL (sum) = 300 and 300 for the two status buckets, vs CUSTOM-SQL (number) = null and null for the same two buckets. A PREDICTION I MADE THAT WAS WRONG, reported rather than dropped: I expected the terminal fallback at objectql-strategy.ts:1287 to make an UNDECLARED measure answer COUNT(*) (#4157's question 1); measured reading for the typo 'total_amont' is field 'total_amont' / method 'sum' / alias 'total_amont', because ensureCube mints the missing member via inferMeasure (analytics-service.ts:2192-2210, whose documented default is a sum over the key) before the strategy is reached — so that line is defensive-only on this path, flagged to the repair seat inside the verdict. NO GATE UNION OWED AND NONE CLAIMED: final tree is byte-identical to origin/main — 'git status --short' empty, 'git diff --stat origin/main' empty, 'git log 22c42c9b2..HEAD' empty — so there is no changeset for scripts/pm/dispatch-gates.mjs to derive from and no CI-relevant diff. NOT MEASURED, stated as such: no repo-wide lint/gate farm run, because no diff exists to gate.",
 "open_questions": [
 {
 "question": "The verdict is 'reachable', so #11833's 'substitute the contract member as-is' branch does not apply. Narrowing service-analytics's local aggregations[].function from string to the engine's six is correct but insufficient alone — it converts the gap into a type error at objectql-strategy.ts:1265 rather than closing it. What should ObjectQLStrategy DO with the three expression metric types? (Not taken here: the card's charter is measurement first, and the repair is PM/maintainer-scoped.)",
 "options": [
 "A — Refuse explicitly on the direct path, mirroring the RECOMBINABLE_METHODS refusal already at objectql-strategy.ts:947: an INVALID_FIELD envelope naming the measure and saying this metric needs a native-SQL driver. Turns a silent null into a loud, correctly-attributed answer; in-file precedent already exists; does NOT make the metric work on ObjectQL.",
 "B — Make it work: lower the custom SQL expression through the aggregate AST. Substantially larger — the engine aggregation contract has no place to carry a raw SQL expression, so this becomes a spec-seat contract question, not a consumer-side change."
 ],
 "recommendation": "A. Real business need: the authorable surface is live and reachable today, and its current answer on the in-memory path is a silent null under the author's own metric name — a wrong number, which is the harm actually in evidence; B serves a capability nobody has been measured to need. Long-term soundness: A is contract-first and honours ADR-0049 declared=enforced by making the boundary explicit, whereas B widens the engine contract to carry raw SQL, which cuts against the platform's structured-AST direction. AI-authored-metadata safety: A fails loudly at the exact measure the author wrote, which is precisely where an AI-generated cube goes wrong; a silent null is the failure mode that propagates undetected. Startup scope discipline: A is a bounded refusal arm in a file that already contains its twin; B is a new capability across spec + every driver. Note this makes A a partial answer by design — it declares that custom-SQL metrics are native-SQL-only rather than making them portable — so if the maintainer judges that portability IS wanted, that is a spec-seat card, forked back per the card's second non-negotiable, not a consumer-side widening."
 }
 ],
 "out_of_scope_findings": []
}

---

Generated by Claude Code

---

Generated by Claude Code