objectstack-fleet · Oct 16, 2026 · Oct 15, 2026 · Oct 15, 2026 · Oct 15, 2026 · Oct 15, 2026
diff --git a/packages/drivers/driver-sql/src/sql-driver-legacy-tenant-composite-null-distinct.test.ts b/packages/drivers/driver-sql/src/sql-driver-legacy-tenant-composite-null-distinct.test.ts
@@ -0,0 +1,125 @@
+// Copyright (c) 2026 ObjectStack. Licensed under the Apache-2.0 license.
+
+import { describe, it, expect, beforeEach, afterEach } from 'vitest';
+import { isUniqueViolationError } from '@objectstack/types';
+import { SqlDriver, classifyIndexKeyPart, parseIndexDdl } from '../src/index.js';
+
+/**
+ * #23603 row 7 — the OLD hand-written table composite
+ * `indexes: [{ fields: ['organization_id', X], unique }]` constrains NOTHING on
+ * untenanted rows, because SQL UNIQUE is NULL-distinct; field-level
+ * `unique: true` does, because its organization key part is NULL-safe
+ * (ADR-0120 D3: `COALESCE(organization_id, '__global__')`).
+ *
+ * Replaces the driver half of objectstack-ai/hotcrm
+ * `test/case-number-tenant-scope.test.ts` (the R residue in #15953's
+ * classification table, row 7: its "the OLD table-composite spelling
+ * constrained nothing untenanted" reverse verification), so hotcrm can retire
+ * it (objectstack-ai/hotcrm#1583). The derivable half — every field declared
+ * unique collides within one organization and never across — is #15953's
+ * unique-scope family in `@objectstack/verify`; hotcrm's K residue (that
+ * `crm_case.case_number` is unique at all) stays in hotcrm.
+ *
+ * Why the hole matters: `organization_id` is kernel-injected and NULL on every
+ * row of a single-organization or untenanted install, and an autonumber
+ * sequence is keyed per tenant — so under the old spelling duplicate numbers
+ * were prevented only by the accident of a single sequence, never by the
+ * database. The old index still refuses a duplicate WITHIN one organization,
+ * which is exactly what hid the hole; both halves are pinned, against the
+ * field-level spelling on the same driver.
+ *
+ * A declared index's `unique: true` is the positional spelling of `'global'`
+ * (verbatim columns; ADR-0120 D2 converts stored rows to `'global'` and the
+ * spec refuses bare `true` since protocol 18). Both spellings are measured,
+ * because stored metadata older than the conversion reaches the driver bare.
+ */
+describe('row 7 — the old table composite is NULL-distinct, field-level unique is NULL-safe (hotcrm case-number-tenant-scope)', () => {
+  let driver: SqlDriver;
+
+  beforeEach(() => {
+    driver = new SqlDriver({ client: 'better-sqlite3', connection: { filename: ':memory:' }, useNullAsDefault: true });
+  });
+  afterEach(async () => {
+    await driver.disconnect();
+  });
+
+  /** Unique index name → key parts; the NULL-safe part reads `COALESCE(col)`. */
+  async function uniqueKeyParts(table: string): Promise<Record<string, string[]>> {
+    const k = (driver as any).knex;
+    const master: any = await k.raw(`SELECT name, sql FROM sqlite_master WHERE type = 'index' AND tbl_name = ?`, [table]);
+    const out: Record<string, string[]> = {};
+    for (const r of master as Array<{ name: string; sql: string | null }>) {
+      if (!r.sql || !/^CREATE UNIQUE/i.test(r.sql)) continue;
+      const parsed = parseIndexDdl(r.sql);
+      out[r.name] = (parsed?.keyParts ?? []).map((p) => {
+        const part = classifyIndexKeyPart(p);
+        if (part.kind === 'column') return part.column;
+        return part.column === null ? p : `COALESCE(${part.column})`;
+      });
+    }
+    return out;
+  }
+
+  const refusal = (p: Promise<unknown>) => p.then(() => undefined, (e: unknown) => e);
+
+  describe('field-level unique: true (the fix)', () => {
+    beforeEach(async () => {
+      await driver.initObjects([
+        { name: 'kase', fields: { organization_id: { type: 'string' }, case_number: { type: 'string', unique: true } } },
+      ] as any);
+    });
+
+    it('materializes the per-organization unique with a NULL-safe organization key part', async () => {
+      expect(await uniqueKeyParts('kase')).toEqual({
+        uniq_kase_organization_id_case_number: ['COALESCE(organization_id)', 'case_number'],
+      });
+    });
+
+    it('accepts the same number in two organizations and refuses it twice in one', async () => {
+      await driver.create('kase', { organization_id: 'org_a', case_number: 'CASE-1' });
+      await driver.create('kase', { organization_id: 'org_b', case_number: 'CASE-1' });
+      const err = await refusal(driver.create('kase', { organization_id: 'org_a', case_number: 'CASE-1' }));
+      expect(isUniqueViolationError(err)).toBe(true);
+    });
+
+    it('refuses a duplicate on an UNTENANTED install too', async () => {
+      await driver.create('kase', { case_number: 'CASE-2' });
+      const err = await refusal(driver.create('kase', { case_number: 'CASE-2' }));
+      expect(isUniqueViolationError(err)).toBe(true);
+    });
+  });
+
+  describe.each([
+    ['bare unique: true', true],
+    ["unique: 'global'", 'global'],
+  ] as const)('the old table composite, %s (the reverse verification)', (_label, unique) => {
+    beforeEach(async () => {
+      await driver.initObjects([
+        {
+          name: 'kase',
+          fields: { organization_id: { type: 'string' }, case_number: { type: 'string' } },
+          indexes: [{ fields: ['organization_id', 'case_number'], unique }],
+        },
+      ] as any);
+    });
+
+    it('materializes a plain composite with no NULL-safe key part', async () => {
+      expect(await uniqueKeyParts('kase')).toEqual({
+        uniq_kase_organization_id_case_number: ['organization_id', 'case_number'],
+      });
+    });
+
+    it('still refuses a duplicate within one organization — which is what hid the hole', async () => {
+      await driver.create('kase', { organization_id: 'org_a', case_number: 'CASE-10' });
+      const err = await refusal(driver.create('kase', { organization_id: 'org_a', case_number: 'CASE-10' }));
+      expect(isUniqueViolationError(err)).toBe(true);
+    });
+
+    it('ACCEPTS a duplicate untenanted number — SQL UNIQUE is NULL-distinct', async () => {
+      const first = await driver.create('kase', { case_number: 'CASE-11' });
+      const second = await driver.create('kase', { case_number: 'CASE-11' });
+      expect(second.id).not.toBe(first.id);
+      expect([first.organization_id ?? null, second.organization_id ?? null]).toEqual([null, null]);
+    });
+  });
+});
diff --git a/packages/objectql/src/engine-filter-token-today-spelling.test.ts b/packages/objectql/src/engine-filter-token-today-spelling.test.ts
@@ -0,0 +1,129 @@
+// Copyright (c) 2026 ObjectStack. Licensed under the Apache-2.0 license.
+
+/**
+ * #23603 row 11 — the query layer's filter-token vocabulary: `resolveFilterTokens`
+ * REFUSES the `{TODAY()}` spelling and KNOWS `{today}`.
+ *
+ * Replaces the two query-layer PREMISE cases of objectstack-ai/hotcrm
+ * `test/flow-filter-today-token.test.ts` (row 11 of #15953's classification
+ * table), so hotcrm can retire them (objectstack-ai/hotcrm#1583). The
+ * service-automation half — the flow pre-pass that resolves its own date
+ * spelling before the query — is its own card and is not pinned here, and the
+ * static census of shipped flow filters is #15953's D residue.
+ *
+ * hotcrm pinned this because its flow sweeps rest on the two layers
+ * DISAGREEING about the token: the query layer must refuse `{TODAY()}` loudly
+ * (`FILTER_TOKEN_UNKNOWN` / 400) rather than compare it as a literal string —
+ * which matches nothing and reads exactly like an empty answer — and must
+ * resolve `{today}` to the calendar day, so `$lt '{today}'` excludes a row
+ * dated today. Both are pinned at the resolver and on the engine's read path
+ * (the refusal before the driver is reached, the resolution on the rows it
+ * selects).
+ */
+
+import { describe, it, expect, beforeEach } from 'vitest';
+import { resolveFilterTokens } from '@objectstack/core';
+import type { EngineQueryOptions } from '@objectstack/spec/data';
+import { ObjectQL } from './engine.js';
+
+const ymd = (offsetDays: number) => {
+    const d = new Date();
+    d.setUTCDate(d.getUTCDate() + offsetDays);
+    return d.toISOString().slice(0, 10);
+};
+const UTC = { timezone: 'UTC' };
+
+function makeDriver() {
+    const stores = new Map<string, Map<string, any>>();
+    const storeFor = (o: string) => { let st = stores.get(o); if (!st) { st = new Map(); stores.set(o, st); } return st; };
+    const matchOne = (stored: any, spec: any): boolean => {
+        if (spec !== null && typeof spec === 'object') {
+            return Object.entries(spec).every(([op, cmp]: [string, any]) => {
+                if (op === '$lt') return typeof stored === 'string' && typeof cmp === 'string' && stored < cmp;
+                if (op === '$eq') return stored === cmp;
+                throw new Error(`fake driver: unsupported operator ${op}`);
+            });
+        }
+        return stored === spec;
+    };
+    const matches = (row: any, where: any): boolean =>
+        !where || typeof where !== 'object' || Object.entries(where).every(([k, v]) => matchOne(row?.[k], v));
+    const driver: any = {
+        name: 'memory', version: '0.0.0', supports: {},
+        async connect() {}, async disconnect() {}, async checkHealth() { return true; }, async execute() { return null; },
+        async find(o: string, ast: any) {
+            const hit = Array.from(storeFor(o).values()).filter((r) => matches(r, ast?.where));
+            return typeof ast?.limit === 'number' ? hit.slice(0, ast.limit) : hit;
+        },
+        async findOne(o: string, ast: any) { return Array.from(storeFor(o).values()).find((r) => matches(r, ast?.where)) ?? null; },
+        async create(o: string, data: any) { storeFor(o).set(data.id, data); return data; },
+        async update(o: string, id: string, data: any) { const r = { ...storeFor(o).get(id), ...data, id }; storeFor(o).set(id, r); return r; },
+        async delete(o: string, id: string) { return storeFor(o).delete(id); },
+        async count(o: string) { return storeFor(o).size; },
+        async bulkCreate() { return []; }, async bulkUpdate() { return []; }, async bulkDelete() {},
+        async beginTransaction() { return { commit: async () => {}, rollback: async () => {} }; }, async commit() {}, async rollback() {},
+    };
+    return { driver, storeFor };
+}
+
+describe('row 11 — the query layer refuses {TODAY()} and knows {today} (hotcrm flow-filter-today-token)', () => {
+    describe('resolveFilterTokens', () => {
+        it('refuses {TODAY()} as FILTER_TOKEN_UNKNOWN / 400 instead of passing it through as a literal', () => {
+            let err: any;
+            try { resolveFilterTokens({ end_date: { $lt: '{TODAY()}' } }, UTC); } catch (e) { err = e; }
+            expect(err).toMatchObject({ code: 'FILTER_TOKEN_UNKNOWN', status: 400, token: 'TODAY()' });
+        });
+
+        it('resolves {today} to the calendar day, YYYY-MM-DD', () => {
+            const now = new Date('2026-03-10T23:30:00.000Z');
+            expect(resolveFilterTokens({ end_date: { $lt: '{today}' } }, { ...UTC, now }))
+                .toEqual({ end_date: { $lt: '2026-03-10' } });
+        });
+    });
+
+    describe('on the engine read path', () => {
+        let engine: ObjectQL;
+        let d: ReturnType<typeof makeDriver>;
+        let driverReads: number;
+
+        beforeEach(async () => {
+            const quiet: any = { warn() {}, debug() {}, info() {}, error() {}, trace() {}, fatal() {}, child() { return quiet; } };
+            engine = new ObjectQL({ logger: quiet });
+            d = makeDriver();
+            // Every read that reaches the driver, counted outside the double.
+            driverReads = 0;
+            for (const verb of ['find', 'findOne'] as const) {
+                const real = d.driver[verb];
+                d.driver[verb] = (...args: unknown[]) => { driverReads += 1; return real.apply(d.driver, args); };
+            }
+            engine.registerDriver(d.driver, true);
+            await engine.init();
+            engine.registry.registerObject({
+                name: 'contract',
+                fields: { status: { type: 'text' }, end_date: { type: 'date' } },
+            } as any);
+            for (const [id, offset] of [['k_past', -3], ['k_yesterday', -1], ['k_today', 0], ['k_future', 30]] as const) {
+                d.storeFor('contract').set(id, { id, status: 'activated', end_date: ymd(offset) });
+            }
+        });
+
+        it('a find carrying {TODAY()} is refused before the driver is reached', async () => {
+            const query: EngineQueryOptions = {
+                where: { status: 'activated', end_date: { $lt: '{TODAY()}' } }, context: UTC,
+            };
+            const err = await engine.find('contract', query).then(() => undefined, (e: any) => e);
+
+            expect(err).toMatchObject({ code: 'FILTER_TOKEN_UNKNOWN', status: 400 });
+            expect(driverReads).toBe(0);
+        });
+
+        it('a find carrying {today} selects exactly the rows dated before today', async () => {
+            const query: EngineQueryOptions = {
+                where: { status: 'activated', end_date: { $lt: '{today}' } }, context: UTC,
+            };
+            const rows = await engine.find('contract', query);
+
+            expect(rows.map((r: any) => r.id).sort()).toEqual(['k_past', 'k_yesterday']);
+        });
+    });
+});
diff --git a/packages/objectql/src/engine-readonly-update-strip-system-gate.test.ts b/packages/objectql/src/engine-readonly-update-strip-system-gate.test.ts
@@ -0,0 +1,125 @@
+// Copyright (c) 2026 ObjectStack. Licensed under the Apache-2.0 license.
+
+/**
+ * #23603 row 8 — on the UPDATE path a statically `readonly` field the CALLER
+ * supplies is stripped if and only if the context is not system; a value a
+ * `beforeUpdate` hook stamps survives under every context.
+ *
+ * Replaces the engine half of objectstack-ai/hotcrm
+ * `test/readonly-write-semantics.test.ts` (row 8 of #15953's classification
+ * table: its "plain caller PATCH" and "beforeUpdate hook stamp" describes, each
+ * measured under three caller contexts), so hotcrm can retire them
+ * (objectstack-ai/hotcrm#1583). The flow-writer describes (`runAs` decides
+ * `isSystem`) are service-automation's and are not pinned here; hotcrm's K
+ * residue (how its escalate path splits privilege) stays in hotcrm.
+ *
+ * ## The variable is `isSystem`, nothing else
+ *
+ * The strip's gate is `!opCtx.context?.isSystem` on the update path, and it
+ * takes only a key the caller supplied. hotcrm measured every writer against
+ * all three contexts so the reading names the variable instead of confounding
+ * it with the writer, and so does this file: `{ userId, isSystem }`,
+ * `{ isSystem }`, and `{ userId }` without `isSystem`. A `userId` does not buy
+ * the write; `isSystem` does, with or without one.
+ *
+ * Each case carries a CONTROL column, identical but for the flag: a run in
+ * which the control also fails to land measured a broken harness, not a strip.
+ */
+
+import { describe, it, expect, beforeEach } from 'vitest';
+import { ObjectQL } from './engine.js';
+
+function makeDriver() {
+    const stores = new Map<string, Map<string, any>>();
+    const storeFor = (o: string) => { let s = stores.get(o); if (!s) { s = new Map(); stores.set(o, s); } return s; };
+    const matches = (row: any, where: any): boolean =>
+        !where || typeof where !== 'object' || Object.entries(where).every(([k, v]) => {
+            if (k.startsWith('$')) throw new Error(`fake driver: unsupported operator ${k}`);
+            return row?.[k] === v;
+        });
+    let n = 0;
+    const driver: any = {
+        name: 'memory', version: '0.0.0', supports: {},
+        async connect() {}, async disconnect() {}, async checkHealth() { return true; }, async execute() { return null; },
+        async find(o: string, ast: any) {
+            const rows = Array.from(storeFor(o).values()).filter((r) => matches(r, ast?.where));
+            return typeof ast?.limit === 'number' ? rows.slice(0, ast.limit) : rows;
+        },
+        async findOne(o: string, ast: any) { for (const r of storeFor(o).values()) if (matches(r, ast?.where)) return r; return null; },
+        async create(o: string, data: Record<string, unknown>) {
+            n += 1; const id = (data.id as string) ?? `r_${n}`; const row = { ...data, id }; storeFor(o).set(id, row); return row;
+        },
+        async update(o: string, id: string, data: Record<string, unknown>) {
+            const s = storeFor(o); const row = { ...s.get(id), ...data, id }; s.set(id, row); return row;
+        },
+        async delete(o: string, id: string) { return storeFor(o).delete(id); },
+        async count() { return 0; },
+        async bulkCreate(o: string, rows: Record<string, unknown>[]) { return Promise.all(rows.map((r) => this.create(o, r))); },
+        async bulkUpdate() { return []; }, async bulkDelete() {},
+        async beginTransaction() { return { commit: async () => {}, rollback: async () => {} }; }, async commit() {}, async rollback() {},
+    };
+    return { driver, storeFor };
+}
+
+const PROBE = 'probe_readonly';
+
+/** The three caller contexts hotcrm varies, by name. */
+const CONTEXTS: Array<[label: string, ctx: Record<string, unknown>]> = [
+    ['{ userId, isSystem }', { userId: 'user_1', isSystem: true }],
+    ['{ isSystem }', { isSystem: true }],
+    ['{ userId } (no isSystem)', { userId: 'user_1' }],
+];
+
+describe('row 8 — the update-path readonly strip runs iff the context is not system (hotcrm readonly-write-semantics)', () => {
+    let engine: ObjectQL;
+    let storeFor: ReturnType<typeof makeDriver>['storeFor'];
+
+    beforeEach(async () => {
+        const quiet: any = { warn() {}, debug() {}, info() {}, error() {}, trace() {}, fatal() {}, child() { return quiet; } };
+        engine = new ObjectQL({ logger: quiet });
+        const d = makeDriver();
+        storeFor = d.storeFor;
+        engine.registerDriver(d.driver, true);
+        await engine.init();
+        engine.registry.registerObject({
+            name: PROBE,
+            fields: {
+                name: { type: 'text' },
+                locked_flag: { type: 'boolean', readonly: true },
+                open_flag: { type: 'boolean' },
+            },
+        } as any);
+        // The hook writer: stamps the locked column — a value the caller did
+        // not supply — keyed off a sentinel so it fires only for those cases.
+        engine.registerHook('beforeUpdate', async (ctx: any) => {
+            if (ctx.input?.data?.name !== 'stamp-me') return;
+            ctx.input.data.locked_flag = true;
+            ctx.input.data.open_flag = true;
+        }, { object: PROBE });
+    });
+
+    const seedRow = () => {
+        const id = `p_${storeFor(PROBE).size + 1}`;
+        storeFor(PROBE).set(id, { id, name: 'probe', locked_flag: false, open_flag: false });
+        return id;
+    };
+    const row = (id: string) => storeFor(PROBE).get(id);
+
+    it.each(CONTEXTS)('a caller-supplied readonly value under %s lands exactly when isSystem', async (_label, context) => {
+        const id = seedRow();
+
+        await engine.update(PROBE, { id, locked_flag: true, open_flag: true }, { context } as any);
+
+        expect(row(id).open_flag, 'the CONTROL column did not land — harness fault, not a strip').toBe(true);
+        expect(row(id).locked_flag).toBe(context.isSystem === true);
+    });
+
+    it.each(CONTEXTS)('a beforeUpdate hook stamp under %s survives — it was not supplied by the caller', async (_label, context) => {
+        const id = seedRow();
+
+        await engine.update(PROBE, { id, name: 'stamp-me' }, { context } as any);
+
+        expect(row(id).open_flag, 'the CONTROL column did not land — harness fault, not a strip').toBe(true);
+        expect(row(id).locked_flag).toBe(true);
+    });
+});