Repository navigation
Expand file tree
/
Copy pathsql.ts
More file actions
1150 lines (1064 loc) · 43.9 KB
/
Copy pathsql.ts
File metadata and controls
1150 lines (1064 loc) · 43.9 KB
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
297
298
299
300
301
302
303
304
305
306
307
308
309
310
311
312
313
314
315
316
317
318
319
320
321
322
323
324
325
326
327
328
329
330
331
332
333
334
335
336
337
338
339
340
341
342
343
344
345
346
347
348
349
350
351
352
353
354
355
356
357
358
359
360
361
362
363
364
365
366
367
368
369
370
371
372
373
374
375
376
377
378
379
380
381
382
383
384
385
386
387
388
389
390
391
392
393
394
395
396
397
398
399
400
401
402
403
404
405
406
407
408
409
410
411
412
413
414
415
416
417
418
419
420
421
422
423
424
425
426
427
428
429
430
431
432
433
434
435
436
437
438
439
440
441
442
443
444
445
446
447
448
449
450
451
452
453
454
455
456
457
458
459
460
461
462
463
464
465
466
467
468
469
470
471
472
473
474
475
476
477
478
479
480
481
482
483
484
485
486
487
488
489
490
491
492
493
494
495
496
497
498
499
500
501
502
503
504
505
506
507
508
509
510
511
512
513
514
515
516
517
518
519
520
521
522
523
524
525
526
527
528
529
530
531
532
533
534
535
536
537
538
539
540
541
542
543
544
545
546
547
548
549
550
551
552
553
554
555
556
557
558
559
560
561
562
563
564
565
566
567
568
569
570
571
572
573
574
575
576
577
578
579
580
581
582
583
584
585
586
587
588
589
590
591
592
593
594
595
596
597
598
599
600
601
602
603
604
605
606
607
608
609
610
611
612
613
614
615
616
617
618
619
620
621
622
623
624
625
626
627
628
629
630
631
632
633
634
635
636
637
638
639
640
641
642
643
644
645
646
647
648
649
650
651
652
653
654
655
656
657
658
659
660
661
662
663
664
665
666
667
668
669
670
671
672
673
674
675
676
677
678
679
680
681
682
683
684
685
686
687
688
689
690
691
692
693
694
695
696
697
698
699
700
701
702
703
704
705
706
707
708
709
710
711
712
713
714
715
716
717
718
719
720
721
722
723
724
725
726
727
728
729
730
731
732
733
734
735
736
737
738
739
740
741
742
743
744
745
746
747
748
749
750
751
752
753
754
755
756
757
758
759
760
761
762
763
764
765
766
767
768
769
770
771
772
773
774
775
776
777
778
779
780
781
782
783
784
785
786
787
788
789
790
791
792
793
794
795
796
797
798
799
800
801
802
803
804
805
806
807
808
809
810
811
812
813
814
815
816
817
818
819
820
821
822
823
824
825
826
827
828
829
830
831
832
833
834
835
836
837
838
839
840
841
842
843
844
845
846
847
848
849
850
851
852
853
854
855
856
857
858
859
860
861
862
863
864
865
866
867
868
869
870
871
872
873
874
875
876
877
878
879
880
881
882
883
884
885
886
887
888
889
890
891
892
893
894
895
896
897
898
899
900
901
902
903
904
905
906
907
908
909
910
911
912
913
914
915
916
917
918
919
920
921
922
923
924
925
926
927
928
929
930
931
932
933
934
935
936
937
938
939
940
941
942
943
944
945
946
947
948
949
950
951
952
953
954
955
956
957
958
959
960
961
962
963
964
965
966
967
968
969
970
971
972
973
974
975
976
977
978
979
980
981
982
983
984
985
986
987
988
989
990
991
992
993
994
995
996
997
998
999
1000
/**
* SQL query builder utilities for user-defined tables.
*
* Uses JSONB containment operator (@>) for equality to leverage GIN index.
* Uses text extraction (->>) for comparisons and pattern matching.
*/
import { isRecordLike } from '@sim/utils/object'
import { truncate } from '@sim/utils/string'
import type { SQL } from 'drizzle-orm'
import { sql } from 'drizzle-orm'
import { getColumnId } from '@/lib/table/column-keys'
import {
columnTypeById,
columnTypeOf,
filterOperatorsFor,
MULTI_SELECT_OPERATORS,
SINGLE_SELECT_OPERATORS,
} from '@/lib/table/column-types'
import { NAME_PATTERN } from '@/lib/table/constants'
import { normalizeDateCellValue } from '@/lib/table/dates'
import { TableQueryValidationError } from '@/lib/table/errors'
import type {
ColumnDefinition,
ConditionOperators,
Filter,
FilterOp,
JsonValue,
Predicate,
PredicateNode,
Sort,
TablePredicate,
} from '@/lib/table/types'
/**
* Re-exported: the `$`-prefixed wire whitelists now live with the `select` type
* definition, but this module is where callers and tests already look for them.
*/
export { MULTI_SELECT_OPERATORS, SINGLE_SELECT_OPERATORS }
type ColumnType = ColumnDefinition['type']
type ColumnMap = ReadonlyMap<string, ColumnDefinition>
/**
/**
* The same allowlists in the v2 bare-operator grammar, applied inside
* `fieldPredicate` so both wire formats gate identically. Not derived from the
* `$` sets above by string surgery because the mapping is not 1:1 — `$empty`
* splits into `isEmpty`/`isNotEmpty`. `isNull`/`isNotNull` have no `$`
* equivalent and are allowed on both: a strict null check is meaningful on any
* column, select included.
*/
const SINGLE_SELECT_OPS = new Set<FilterOp>([
'eq',
'ne',
'in',
'nin',
'isEmpty',
'isNotEmpty',
'isNull',
'isNotNull',
])
const MULTI_SELECT_OPS = new Set<FilterOp>([
'contains',
'ncontains',
'isEmpty',
'isNotEmpty',
'isNull',
'isNotNull',
])
/**
* Returns the Postgres cast needed to compare a JSONB text value of the given
* column type, or `null` when text comparison is correct. Single source of
* truth for both filter range operators and sort ordering — keeps the two
* paths from drifting apart.
*/
function jsonbCastForType(type: ColumnType | undefined): 'numeric' | 'timestamptz' | null {
return columnTypeById(type).jsonbCast
}
/**
* Maps a column's **stable id** (the JSONB storage key, via `getColumnId`) to its
* definition. Filter/sort objects arrive keyed by column id, so the lookups in the
* clause builders use ids — not display names. The full definition (not just the
* type) is kept so the select branches can read `options`/`multiple`.
*/
function buildColumnMap(columns: ColumnDefinition[]): ColumnMap {
return new Map(columns.map((col) => [getColumnId(col), col]))
}
/**
* Whitelist of allowed operators for query filtering.
* Only these operators can be used in filter conditions.
*/
const ALLOWED_OPERATORS = new Set([
'$eq',
'$ne',
'$gt',
'$gte',
'$lt',
'$lte',
'$in',
'$nin',
'$contains',
'$ncontains',
'$startsWith',
'$endsWith',
'$like',
'$ilike',
'$nlike',
'$nilike',
'$empty',
'$isNull',
'$isNotNull',
])
/**
* Builds a WHERE clause from a filter object.
* Recursively processes logical operators ($or, $and) and field conditions.
*
* Index behavior: equality ($eq, $in) uses the JSONB containment operator (@>) and
* can leverage the GIN index on `user_table_rows.data` (jsonb_path_ops). Range
* operators ($gt, $gte, $lt, $lte), pattern matches ($contains, $ncontains,
* $startsWith, $endsWith), and emptiness checks ($empty) fall back to text
* extraction via `data->>'field'`, which defeats the GIN index and produces
* a sequential scan over the table's rows (bounded by a btree prefix on
* `table_id`). Prefer equality filters on hot paths; assume range filters are
* O(rows per table) until a per-column expression index is added.
*
* @param filter - Filter object with field conditions and logical operators
* @param tableName - Table name for the query (e.g., 'user_table_rows')
* @param columns - Column definitions; drives type-aware JSONB casts (numeric for numbers, timestamptz for dates)
* @returns SQL WHERE clause or undefined if no filter specified
* @throws {TableQueryValidationError} if field name is invalid or operator is not allowed
*
* @example
* // Simple equality
* buildFilterClause({ name: 'John' }, 'user_table_rows', [{ name: 'name', type: 'string' }])
*
* // Range on a date column — emits `::timestamptz` on both sides
* buildFilterClause(
* { birthDate: { $gte: '2024-01-01' } },
* 'user_table_rows',
* [{ name: 'birthDate', type: 'date' }],
* )
*
* // Logical operators
* buildFilterClause(
* { $or: [{ status: 'active' }, { verified: true }] },
* 'user_table_rows',
* [{ name: 'status', type: 'string' }, { name: 'verified', type: 'boolean' }],
* )
*/
export function buildFilterClause(
filter: Filter,
tableName: string,
columns: ColumnDefinition[]
): SQL | undefined {
const columnMap = buildColumnMap(columns)
return buildFilterClauseInternal(filter, tableName, columnMap)
}
function buildFilterClauseInternal(
filter: Filter,
tableName: string,
columnMap: ColumnMap
): SQL | undefined {
const conditions: SQL[] = []
for (const [field, condition] of Object.entries(filter)) {
if (condition === undefined) {
continue
}
// This represents a case where the filter is a logical OR of multiple filters
// e.g. { $or: [{ status: 'active' }, { status: 'pending' }] }
if (field === '$or' && Array.isArray(condition)) {
const orClause = buildLogicalClause(condition as Filter[], tableName, 'OR', columnMap)
if (orClause) {
conditions.push(orClause)
}
continue
}
// This represents a case where the filter is a logical AND of multiple filters
// e.g. { $and: [{ status: 'active' }, { status: 'pending' }] }
if (field === '$and' && Array.isArray(condition)) {
const andClause = buildLogicalClause(condition as Filter[], tableName, 'AND', columnMap)
if (andClause) {
conditions.push(andClause)
}
continue
}
// Skip arrays for regular fields - arrays are only valid for $or and $and.
// A v2 predicate tree (`{ all | any: [...] }`) that reaches this legacy
// compiler is a VERSION MISMATCH — a caller speaking the newer grammar
// against an older server. Skipping it as "an array on a regular field"
// compiles to no WHERE clause at all, which on a bulk delete means every
// row rather than none. Fail fast and name the mismatch instead.
if ((field === 'all' || field === 'any') && Array.isArray(condition)) {
throw new TableQueryValidationError(
`Filter looks like a v2 predicate tree ("${field}" group) but reached the legacy filter compiler. ` +
'This usually means a client is sending the predicate grammar to a server that predates it.'
)
}
// If we encounter an array here, it's likely malformed input (e.g., { name: [filter1, filter2] })
// which doesn't have a clear semantic meaning, so we skip it.
if (Array.isArray(condition)) {
continue
}
// Build SQL conditions for this field. Returns array of SQL fragments for each operator.
const fieldConditions = buildFieldCondition(
tableName,
field,
condition as JsonValue | ConditionOperators,
columnMap.get(field)
)
conditions.push(...fieldConditions)
}
if (conditions.length === 0) return undefined
if (conditions.length === 1) return conditions[0]
return sql.join(conditions, sql.raw(' AND '))
}
/**
* Builds a WHERE clause from a v2 `TablePredicate` (nestable `all`/`any` groups
* of `{ field, op, value }` leaves). Sibling of `buildFilterClause`: same engine,
* same `fieldPredicate` leaf — only the grammar differs. Returns `undefined` when
* the tree contributes no conditions (empty groups, all-no-op leaves).
*
* @throws {TableQueryValidationError} if a field name is invalid or an operator is not allowed
*/
export function buildPredicateClause(
predicate: TablePredicate,
tableName: string,
columns: ColumnDefinition[]
): SQL | undefined {
return buildPredicateNode(predicate, tableName, buildColumnMap(columns))
}
function isPredicateGroup(node: PredicateNode): node is TablePredicate {
return 'all' in node || 'any' in node
}
function buildPredicateNode(
node: PredicateNode,
tableName: string,
columnMap: ColumnMap
): SQL | undefined {
if (isPredicateGroup(node)) {
const isAll = 'all' in node
const members = isAll ? node.all : node.any
const clauses: SQL[] = []
for (const member of members) {
const clause = buildPredicateNode(member, tableName, columnMap)
if (clause) clauses.push(clause)
}
if (clauses.length === 0) return undefined
if (clauses.length === 1) return clauses[0]
return sql`(${sql.join(clauses, sql.raw(isAll ? ' AND ' : ' OR '))})`
}
const leaf = node as Predicate
return fieldPredicate(tableName, leaf.field, leaf.op, leaf.value, columnMap.get(leaf.field))
}
/**
* Builds an ORDER BY clause from a sort object.
*
* @param sort - Sort object with field names and directions
* @param tableName - Table name for the query (e.g., 'user_table_rows')
* @param columns - Column definitions; drives type-aware casts (numeric for numbers, timestamptz for dates)
* @returns SQL ORDER BY clause or undefined if no sort specified
* @throws {TableQueryValidationError} if field name or sort direction is invalid
*
* @example
* buildSortClause(
* { name: 'asc' },
* 'user_table_rows',
* [{ name: 'name', type: 'string' }],
* )
* // Returns: ORDER BY user_table_rows.data->>'name' ASC
*
* @example
* buildSortClause(
* { salary: 'desc' },
* 'user_table_rows',
* [{ name: 'salary', type: 'number' }],
* )
* // Returns: ORDER BY (user_table_rows.data->>'salary')::numeric DESC NULLS LAST
*/
export function buildSortClause(
sort: Sort,
tableName: string,
columns: ColumnDefinition[]
): SQL | undefined {
const clauses: SQL[] = []
const columnMap = buildColumnMap(columns)
for (const [field, direction] of Object.entries(sort)) {
validateFieldName(field)
if (direction !== 'asc' && direction !== 'desc') {
throw new TableQueryValidationError(
`Invalid sort direction "${direction}". Must be "asc" or "desc".`
)
}
clauses.push(buildSortFieldClause(tableName, field, direction, columnMap.get(field)))
}
return clauses.length > 0 ? sql.join(clauses, sql.raw(', ')) : undefined
}
/**
* Validates a field name to prevent SQL injection.
* Field names must match the NAME_PATTERN (alphanumeric + underscore, starting with letter/underscore).
*
* @param field - The field name to validate
* @throws {TableQueryValidationError} if field name is invalid
*/
function validateFieldName(field: string): void {
if (!field || typeof field !== 'string') {
throw new TableQueryValidationError('Field name must be a non-empty string')
}
if (!NAME_PATTERN.test(field)) {
throw new TableQueryValidationError(
`Invalid field name "${field}". Field names must start with a letter or underscore, followed by alphanumeric characters or underscores.`
)
}
}
/**
* Validates an operator to ensure it's in the allowed list.
*
* @param operator - The operator to validate
* @throws {TableQueryValidationError} if operator is not allowed
*/
function validateOperator(operator: string): void {
if (!ALLOWED_OPERATORS.has(operator)) {
throw new TableQueryValidationError(
`Invalid operator "${operator}". Allowed operators: ${Array.from(ALLOWED_OPERATORS).join(', ')}`
)
}
}
/**
* The caller-facing name for a field in an error message.
*
* Filters reach the SQL builders **storage-keyed** — the boundaries translate
* column name → column id first — so interpolating the raw `field` reports a
* `col_…` id the caller never sent and cannot look up. The definition already in
* hand carries the display name; fall back to `field` for a system column
* (`createdAt`), an unknown key, or a legacy column whose id IS its name.
*/
function columnLabel(field: string, column: ColumnDefinition | undefined): string {
return column?.name ?? field
}
/**
* Validates that a range-operator value matches its column's expected JS type
* before it reaches Postgres. Surfaces an actionable, column-named error at the
* SQL builder layer instead of a generic `invalid input syntax for type numeric`
* from the database.
*/
function validateComparisonValue(
label: string,
columnType: ColumnType | undefined,
cast: 'numeric' | 'timestamptz',
value: number | string
): void {
if (cast === 'numeric' && typeof value !== 'number') {
const typeLabel = columnType ?? 'number'
throw new TableQueryValidationError(
`Range operator on column "${label}" (${typeLabel}) requires a number, got ${typeof value}`
)
}
if (cast === 'timestamptz') {
if (typeof value !== 'string') {
throw new TableQueryValidationError(
`Range operator on column "${label}" (date) requires a date string, got ${typeof value}`
)
}
if (normalizeDateCellValue(value) === null) {
throw new TableQueryValidationError(
`Range operator on column "${label}" (date) requires a parseable date string, got "${truncate(value, 64)}"`
)
}
}
}
/**
* Equality/membership operators. Their operand is compared by JSONB
* containment, which is exact and untyped: `{"score": 8} @> {"score": "8"}` is
* simply false, so a wrongly-typed operand never matches and the caller cannot
* tell that from a genuinely empty table.
*/
const CONTAINMENT_OPS = new Set<FilterOp>(['eq', 'ne', 'in', 'nin'])
/**
* Column types whose containment operand is left byte-exact.
*
* `select` — its operands are option **names**, already resolved to stored ids
* upstream by `resolvePredicateSelectValues` / `resolveFilterSelectValues`, and
* its `coerce` returns an array for a multi-select: the wrong shape for a
* membership clause.
*
* `date` — its `coerce` is **not idempotent**. `normalizeDateCellValue` rebuilds
* the string without a fractional part, so `"2024-01-31T10:00:00.000Z"` — the
* form the write path stores — comes back as `"2024-01-31T10:00:00Z"` and no
* longer matches the stored bytes. `fieldPredicate` is not only used for
* user-facing filters: it also compiles the unique-constraint probes
* (`checkUniqueConstraintsDb`, `checkBatchUniqueConstraintsDb`) and the upsert
* conflict probe, whose operands were **already** coerced by `coerceRowToSchema`
* earlier in the same request. Re-coercing them there would make a unique `date`
* probe stop matching, letting a duplicate row through inside the write
* transaction with no error. Fixing the stored date format is a far larger
* change than a read-path alignment should carry.
*/
const CONTAINMENT_COERCION_EXCLUDED_TYPES = new Set<ColumnType>(['select', 'date'])
/**
* Reads an equality/membership operand the way the **write path** reads a cell,
* so `eq` compares like against like — best-effort, never fatal.
*
* The column type's own `coerce` is the single definition of "what this column
* can hold": a write of `"8"` to a number column stores `8`, so a filter for
* `"8"` must look for `8` or it reports zero rows for a row that exists.
*
* When `coerce` refuses, the ORIGINAL operand is passed through unchanged and
* the clause compiles exactly as it always did — matching nothing, since JSONB
* containment is exact. Refusing loudly is not an option here: the v2 predicate
* grammar is not operand-type-checked at the boundary (leaf `value` is
* `z.unknown()`), so a throw would land not at submission but inside the
* background runners that compile the same predicate later — a filter-scoped
* cancel that can no longer compile would leave those cells uncancellable.
*
* `null` and `''` are passed through untouched. Neither is a typed operand:
* `null` is a real containment query for a JSON-null cell, and `''` is the
* cleared-cell sentinel the grid writes. Coercing either would change what an
* existing caller's filter means rather than fix it. `select` and `date` are
* excluded wholesale — see `CONTAINMENT_COERCION_EXCLUDED_TYPES`.
*/
function coerceContainmentOperand(column: ColumnDefinition, value: JsonValue): JsonValue {
if (value === null || value === '') return value
const result = columnTypeOf(column).coerce(value, column)
return result.ok ? (result.value as JsonValue) : value
}
/**
* Guards a bound that is about to be bound into a `::timestamptz` cast on a
* system timestamp column (`createdAt`/`updatedAt`).
*
* The type check alone was not enough: any string went straight into the cast,
* so `not-a-date` raised `invalid input syntax for type timestamp with time
* zone` inside the driver. That throw carries no classification the route layer
* recognizes, so a malformed filter — caller input — surfaced as a 500. Parsing
* with the same normalizer the `date` column type uses to store cells keeps the
* filter grammar and the storage grammar in agreement.
*/
function assertParseableTimestampBound(field: string, value: JsonValue | undefined): void {
if (typeof value !== 'string' || normalizeDateCellValue(value) === null) {
throw new TableQueryValidationError(
`Operator on column "${field}" requires a parseable date string, got ${
typeof value === 'string' ? `"${truncate(value, 64)}"` : typeof value
}`
)
}
}
/**
* Builds SQL conditions for a single field based on the provided condition.
*
* Supports both simple equality checks (using JSONB containment) and complex
* operators like comparison, membership, and pattern matching. Field names are
* validated to prevent SQL injection, and operators are validated against an
* allowed whitelist.
*
* @param tableName - The name of the table to query (used for SQL table reference)
* @param field - The field name to filter on (must match NAME_PATTERN)
* @param condition - Either a simple value (for equality) or a ConditionOperators
* object with operators like $eq, $gt, $in, etc.
* @returns Array of SQL condition fragments. Multiple conditions are returned
* when the condition object contains multiple operators.
* @throws {TableQueryValidationError} if field name is invalid or operator is not allowed
*/
function buildFieldCondition(
tableName: string,
field: string,
condition: JsonValue | ConditionOperators,
column: ColumnDefinition | undefined
): SQL[] {
validateFieldName(field)
const columnType = column?.type
const isSelect = columnType === 'select'
const isMultiSelect = isSelect && column?.multiple === true
const label = columnLabel(field, column)
// Types whose stored value is opaque (a select's option ids) restrict which
// operators mean anything; `null` means the type accepts them all.
const allowedOperators = column ? filterOperatorsFor(column) : null
const conditions: SQL[] = []
if (isRecordLike(condition)) {
for (const [op, value] of Object.entries(condition)) {
// Validate against the legacy `$`-whitelist, then normalize onto the shared
// `FilterOp` so v1 and v2 emit byte-identical leaf SQL.
validateOperator(op)
if (allowedOperators && !allowedOperators.has(op)) {
throw new TableQueryValidationError(
`Operator "${op}" is not supported on ${isMultiSelect ? 'multi-select' : columnType} column "${label}". Allowed: ${Array.from(allowedOperators).join(', ')}`
)
}
if (op === '$empty') {
// `$empty: true/false` maps onto the valueless v2 ops.
const filterOp: FilterOp = coerceEmptyFlag(label, value) ? 'isEmpty' : 'isNotEmpty'
const clause = fieldPredicate(tableName, field, filterOp, undefined, column)
if (clause) conditions.push(clause)
continue
}
// Every other `$op` is `op` minus the leading `$` (e.g. `$gte` → `gte`).
const clause = fieldPredicate(tableName, field, op.slice(1) as FilterOp, value, column)
if (clause) conditions.push(clause)
}
} else {
// Simple value (primitive or null) - shorthand for equality.
// Example: { name: 'John' } is equivalent to { name: { $eq: 'John' } }
// isRecordLike's negation can't structurally exclude ConditionOperators (no index
// signature), so the JsonValue-only shape of this branch is asserted, not inferred.
// Routes through the unified `fieldPredicate` leaf like every other matcher,
// so equality semantics stay defined in exactly one place.
//
// On a multi-select the shorthand reads as "holds this option" — the cell is
// an array of option ids, so scalar equality can never be true. It maps to
// `contains` (membership). An EXPLICIT `$eq` on a multi-select still errors
// via the select allowlist: writing it out is a mistake worth naming, while
// the shorthand has an unambiguous intent.
const shorthandOp: FilterOp = column?.type === 'select' && column.multiple ? 'contains' : 'eq'
const clause = fieldPredicate(tableName, field, shorthandOp, condition as JsonValue, column)
if (clause) conditions.push(clause)
}
return conditions
}
/**
* The single leaf primitive: compiles one `field op value` into SQL. Every
* matcher routes through here — both filter compilers (`buildFilterClause` for
* the legacy `$`-grammar, `buildPredicateClause` for the v2 grammar), the upsert
* conflict probe, and the unique-constraint checks. Centralizing the leaf means
* equality/case/null/cast semantics are defined exactly once, so "find the row"
* and "is this value unique" can never disagree.
*
* Returns `undefined` when the predicate is a no-op (empty `in`/`nin` array),
* matching the legacy behavior of emitting no clause.
*
* Equality (`eq`/`ne`/`in`/`nin`) uses case-sensitive JSONB containment (GIN
* indexed). Text matches (`contains`/`ncontains`/`startsWith`/`endsWith`) are
* ILIKE (case-insensitive). Ranges cast per column type.
*/
export function fieldPredicate(
tableName: string,
field: string,
op: FilterOp,
value: JsonValue | undefined,
column: ColumnDefinition | undefined
): SQL | undefined {
validateFieldName(field)
// System columns (`createdAt`/`updatedAt`/`id`) are real row columns, not
// JSONB keys — dispatch before the `data->>` builders below, which would
// silently match nothing (the key never exists in `data`).
if (isSystemColumn(field)) {
return buildSystemColumnClause(tableName, field, op, value)
}
const columnType = column?.type
// Messages must name what the CALLER sent. `field` is the storage key by the
// time it reaches here (the boundaries translate name → id before building
// SQL), so a raw `field` reports a `col_…` the caller never supplied.
const label = columnLabel(field, column)
const isSelect = columnType === 'select'
// A multi-select cell holds an ARRAY of option ids, so equality against a
// scalar can never be true; the question is membership. Gating and clause
// choice both live here rather than in `buildFieldCondition` so the v2
// predicate grammar gets the identical treatment.
const isMultiSelect = isSelect && column?.multiple === true
if (isSelect) {
const allowed = isMultiSelect ? MULTI_SELECT_OPS : SINGLE_SELECT_OPS
if (!allowed.has(op)) {
throw new TableQueryValidationError(
`Operator "${op}" is not supported on ${isMultiSelect ? 'multi-select' : 'select'} column "${label}". Allowed: ${Array.from(allowed).join(', ')}`
)
}
}
if (isMultiSelect) {
switch (op) {
case 'contains':
return buildArrayMembershipClause(tableName, field, value as JsonValue)
case 'ncontains':
return sql`NOT (${buildArrayMembershipClause(tableName, field, value as JsonValue)})`
case 'isEmpty':
return buildEmptyClause(tableName, field, true, true)
case 'isNotEmpty':
return buildEmptyClause(tableName, field, false, true)
default:
break
}
}
// Equality/membership compiles to exact JSONB containment, so an operand of
// the wrong JS type is not a narrower match — it is no match at all, reported
// as an empty 200 while the row it meant exists. Read the operand through the
// column type first, exactly as a write would. Best-effort only: an operand
// the type refuses passes through unchanged and the clause compiles as it
// always did. Skipped for a field with no schema entry (ad-hoc legacy keys),
// which has no declared type to read it with, and for the types in
// `CONTAINMENT_COERCION_EXCLUDED_TYPES`.
const coercesContainment =
column !== undefined &&
!CONTAINMENT_COERCION_EXCLUDED_TYPES.has(column.type) &&
CONTAINMENT_OPS.has(op)
const containmentValue: JsonValue | undefined =
coercesContainment && column
? Array.isArray(value)
? value.map((v) => coerceContainmentOperand(column, v as JsonValue))
: coerceContainmentOperand(column, value as JsonValue)
: value
switch (op) {
case 'eq':
return buildContainmentClause(tableName, field, containmentValue as JsonValue)
case 'ne':
return sql`NOT (${buildContainmentClause(tableName, field, containmentValue as JsonValue)})`
case 'gt':
return buildComparisonClause(tableName, field, column, '>', value as number | string)
case 'gte':
return buildComparisonClause(tableName, field, column, '>=', value as number | string)
case 'lt':
return buildComparisonClause(tableName, field, column, '<', value as number | string)
case 'lte':
return buildComparisonClause(tableName, field, column, '<=', value as number | string)
case 'in': {
const values = containmentValue
if (!Array.isArray(values) || values.length === 0) return undefined
if (values.length === 1) return buildContainmentClause(tableName, field, values[0])
const inConditions = values.map((v) => buildContainmentClause(tableName, field, v))
return sql`(${sql.join(inConditions, sql.raw(' OR '))})`
}
case 'nin': {
const values = containmentValue
if (!Array.isArray(values) || values.length === 0) return undefined
const ninConditions = values.map(
(v) => sql`NOT (${buildContainmentClause(tableName, field, v)})`
)
return sql`(${sql.join(ninConditions, sql.raw(' AND '))})`
}
case 'contains':
return buildLikeClause(tableName, field, label, value as string, 'contains')
case 'ncontains':
return buildLikeClause(tableName, field, label, value as string, 'contains', { negate: true })
case 'startsWith':
return buildLikeClause(tableName, field, label, value as string, 'startsWith')
case 'endsWith':
return buildLikeClause(tableName, field, label, value as string, 'endsWith')
case 'like':
return buildPatternClause(tableName, field, value as string, { caseInsensitive: false })
case 'ilike':
return buildPatternClause(tableName, field, value as string, { caseInsensitive: true })
case 'nlike':
return buildPatternClause(tableName, field, value as string, {
caseInsensitive: false,
negate: true,
})
case 'nilike':
return buildPatternClause(tableName, field, value as string, {
caseInsensitive: true,
negate: true,
})
case 'isEmpty':
return buildEmptyClause(tableName, field, true)
case 'isNotEmpty':
return buildEmptyClause(tableName, field, false)
case 'isNull':
return buildNullClause(tableName, field, true)
case 'isNotNull':
return buildNullClause(tableName, field, false)
default:
throw new TableQueryValidationError(`Invalid operator "${op}"`)
}
}
/**
* Builds SQL clauses from nested filters and joins them with the specified operator.
*
* @example
* // OR operator
* buildLogicalClause(
* [{ status: 'active' }, { status: 'pending' }],
* 'user_table_rows',
* 'OR'
* )
* // Returns: (data @> '{"status":"active"}'::jsonb OR data @> '{"status":"pending"}'::jsonb)
*
* @example
* // AND operator
* buildLogicalClause(
* [{ age: { $gte: 18 } }, { verified: true }],
* 'user_table_rows',
* 'AND'
* )
* // Returns: ((data->>'age')::numeric >= 18 AND data @> '{"verified":true}'::jsonb)
*/
function buildLogicalClause(
subFilters: Filter[],
tableName: string,
operator: 'OR' | 'AND',
columnMap: ColumnMap
): SQL | undefined {
const clauses: SQL[] = []
for (const subFilter of subFilters) {
const clause = buildFilterClauseInternal(subFilter, tableName, columnMap)
if (clause) {
clauses.push(clause)
}
}
if (clauses.length === 0) return undefined
if (clauses.length === 1) return clauses[0]
return sql`(${sql.join(clauses, sql.raw(` ${operator} `))})`
}
/**
* Row columns that are addressable in filters/sorts but live on the row itself
* rather than inside the JSONB `data` blob. The docs advertise all three as
* filterable and sortable; without this dispatch they compile to a `data->>'…'`
* extraction of a key that never exists, so they silently match nothing.
*/
const SYSTEM_COLUMNS: Readonly<Record<string, { column: string; kind: 'timestamp' | 'text' }>> = {
createdAt: { column: 'created_at', kind: 'timestamp' },
updatedAt: { column: 'updated_at', kind: 'timestamp' },
id: { column: 'id', kind: 'text' },
}
function isSystemColumn(field: string): boolean {
return Object.hasOwn(SYSTEM_COLUMNS, field)
}
/**
* Builds a predicate against a system column. Timestamp columns bind ISO strings
* normalized to UTC wall clock; the text column (`id`) binds as text and also
* accepts the pattern ops. Anything else is rejected with an actionable error.
*/
function buildSystemColumnClause(
tableName: string,
field: string,
op: FilterOp,
value: JsonValue | undefined
): SQL | undefined {
const spec = SYSTEM_COLUMNS[field]
const col = sql.raw(`${tableName}.${spec.column}`)
// `created_at`/`updated_at` are `timestamp WITHOUT time zone` holding UTC wall
// clock. A bare `::timestamptz` comparison promotes the column using the session
// `TimeZone` GUC, so identical queries return different rows per environment and
// day-boundary ranges land off by the offset. Normalizing the bound to UTC wall
// clock is session-independent and still honors an explicit offset in the input.
const ts = (v: JsonValue | undefined) => {
assertParseableTimestampBound(field, v)
return sql`${String(v)}::timestamptz AT TIME ZONE 'UTC'`
}
const bind = spec.kind === 'timestamp' ? ts : (v: JsonValue | undefined) => sql`${String(v)}`
/**
* Mirrors the JSONB pattern builders: `*` is the caller's only wildcard, an
* empty pattern is rejected (it would collapse to `%` and match every row),
* and the negated forms keep NULL cells so "does not contain X" retains them.
*/
const like = (
v: JsonValue | undefined,
pattern: (escaped: string) => string,
ci: boolean,
negate = false
) => {
const text = String(v ?? '')
if (text.length === 0) {
throw new TableQueryValidationError(
`Operator "${op}" on column "${field}" requires a non-empty value`
)
}
const p = pattern(escapeLikePattern(text))
const match = ci ? sql`${col} ILIKE ${p}` : sql`${col} LIKE ${p}`
return negate ? sql`NOT (${match})` : match
}
// The text system column (`id`) additionally supports the pattern ops;
// timestamps fall through to the unsupported-operator error below.
if (spec.kind === 'text') {
const star = (e: string) => e.replace(/\*/g, '%')
switch (op) {
case 'like':
return like(value, star, false)
case 'ilike':
return like(value, star, true)
case 'nlike':
return like(value, star, false, true)
case 'nilike':
return like(value, star, true, true)
case 'contains':
return like(value, (e) => `%${e}%`, true)
case 'ncontains':
return like(value, (e) => `%${e}%`, true, true)
case 'startsWith':
return like(value, (e) => `${e}%`, true)
case 'endsWith':
return like(value, (e) => `%${e}`, true)
default:
break
}
}
switch (op) {
case 'eq':
return sql`${col} = ${bind(value)}`
case 'ne':
return sql`${col} <> ${bind(value)}`
case 'gt':
return sql`${col} > ${bind(value)}`
case 'gte':
return sql`${col} >= ${bind(value)}`
case 'lt':
return sql`${col} < ${bind(value)}`
case 'lte':
return sql`${col} <= ${bind(value)}`
case 'in': {
if (!Array.isArray(value) || value.length === 0) return undefined
return sql`${col} IN (${sql.join(value.map(bind), sql.raw(', '))})`
}
case 'nin': {
if (!Array.isArray(value) || value.length === 0) return undefined
return sql`${col} NOT IN (${sql.join(value.map(bind), sql.raw(', '))})`
}
case 'isNull':
case 'isEmpty':
return sql`${col} IS NULL`
case 'isNotNull':
case 'isNotEmpty':
return sql`${col} IS NOT NULL`
default:
throw new TableQueryValidationError(
`Operator "${op}" is not supported on the built-in column "${field}" — use eq, ne, gt, gte, lt, lte, in, nin, isNull, isNotNull.`
)
}
}
/** Builds JSONB containment clause: `data @> '{"field": value}'::jsonb` (uses GIN index) */
function buildContainmentClause(tableName: string, field: string, value: JsonValue): SQL {
const jsonObj = JSON.stringify({ [field]: value })
return sql`${sql.raw(`${tableName}.data`)} @> ${jsonObj}::jsonb`
}
/**
* Builds an array-membership clause for a multi-select cell:
* `data @> '{"field": [value]}'::jsonb`.
*
* The value is wrapped in an array because Postgres containment requires
* matching structure — `{"t":["a","b"]} @> {"t":"a"}` is **false**, while
* `{"t":["a","b"]} @> {"t":["a"]}` is true. Same GIN index as the scalar form.
*/
function buildArrayMembershipClause(tableName: string, field: string, value: JsonValue): SQL {
const jsonObj = JSON.stringify({ [field]: [value] })
return sql`${sql.raw(`${tableName}.data`)} @> ${jsonObj}::jsonb`
}
/**
* Builds a typed range comparison against a JSONB cell.
*
* `number` columns cast both sides to `numeric`; `date` columns cast both sides
* to `timestamptz` so date strings compare chronologically and timezone offsets
* in ISO strings (e.g. `2024-01-01T00:00:00Z`) are preserved rather than
* silently stripped (which would make results depend on the server's TimeZone
* setting). `string` columns compare lexicographically as text. `boolean`/`json`
* columns have no meaningful ordering and are rejected. Columns with no schema
* entry fall back to `numeric` (legacy default — preserves behavior for ad-hoc
* fields). The right-hand value is cast explicitly because drizzle parameterizes
* it as `text`; without the cast, Postgres would compare `text <op> text` and
* silently produce lexicographic results.
*
* Cannot use the GIN index — falls back to a sequential scan over the table's
* rows (bounded by the btree prefix on `table_id`).
*/
function buildComparisonClause(
tableName: string,
field: string,
column: ColumnDefinition | undefined,
operator: '>' | '>=' | '<' | '<=',
value: number | string
): SQL {
const escapedField = field.replace(/'/g, "''")
const label = columnLabel(field, column)
const columnType = column?.type
if (columnType === 'boolean' || columnType === 'json') {
throw new TableQueryValidationError(
`Range operator on column "${label}" (${columnType}) is not supported — ${columnType} values have no ordering.`
)
}
if (columnType === 'string') {
const cell = sql.raw(`${tableName}.data->>'${escapedField}'`)
return sql`${cell} ${sql.raw(operator)} ${String(value)}`
}
const cast = jsonbCastForType(columnType) ?? 'numeric'
validateComparisonValue(label, columnType, cast, value)
const cell = sql.raw(`(${tableName}.data->>'${escapedField}')::${cast}`)
return cast === 'timestamptz'
? sql`${cell} ${sql.raw(operator)} ${value}::timestamptz`
: sql`${cell} ${sql.raw(operator)} ${value}`
}
/** Escapes LIKE/ILIKE wildcard characters so they match literally */
export function escapeLikePattern(value: string): string {
return value.replace(/[\\%_]/g, '\\$&')
}
/**
* General LIKE/ILIKE pattern match (the `like`/`ilike` ops). The caller's `*`
* is the only wildcard — it maps to SQL `%`; any literal `%`/`_`/`\` in the
* value is escaped so it matches itself. Empty/exact patterns are allowed (an
* empty pattern matches only the empty string, not every row, so it's not the
* footgun the positional `buildLikeClause` guards against). `negate` inverts
* the match and keeps null cells — "does not match" retains empty rows,
* mirroring `buildLikeClause`'s ncontains semantics. Cannot use the GIN index;
* sequential scan bounded by the `table_id` btree prefix.
*/
function buildPatternClause(
tableName: string,
field: string,
value: string,
options: { caseInsensitive: boolean; negate?: boolean }
): SQL {
const escapedField = field.replace(/'/g, "''")
const pattern = String(value)
.replace(/[\\%_]/g, '\\$&')
.replace(/\*/g, '%')
const cell = sql.raw(`${tableName}.data->>'${escapedField}'`)
const match = options.caseInsensitive
? sql`${cell} ILIKE ${pattern}`
: sql`${cell} LIKE ${pattern}`
if (!options.negate) return match
return options.caseInsensitive
? sql`(${cell} IS NULL OR ${cell} NOT ILIKE ${pattern})`
: sql`(${cell} IS NULL OR ${cell} NOT LIKE ${pattern})`
}
/**
* Builds a case-insensitive pattern match against a JSONB cell using ILIKE.
* `position` controls wildcard placement: `contains` → `%value%`, `startsWith`
* → `value%`, `endsWith` → `%value`. When `negate` is set the match is inverted
* and null cells are included — "does not contain X" should keep empty rows,
* mirroring `$ne` (which also surfaces nulls). Cannot use the GIN index; falls
* back to a sequential scan bounded by the `table_id` btree prefix.
*/
function buildLikeClause(
tableName: string,
field: string,
label: string,
value: string,
position: 'contains' | 'startsWith' | 'endsWith',
options?: { negate?: boolean }
): SQL {
const escapedField = field.replace(/'/g, "''")
// Coerce defensively: filters arriving via the raw v1 API / tools may carry a
// non-string value (e.g. `{ $contains: 123 }`), and ILIKE compares text anyway.
const text = String(value)
// An empty pattern collapses to `%`/`%%`, which matches every non-null row —
// a silent footgun for raw-API callers (the UI gates empty values out). Reject
// it, consistent with the range/`$empty` operand validation.
if (text.length === 0) {
const opName = position === 'contains' && options?.negate ? 'ncontains' : position