Dynamic Search Engine & Security

Exposing arbitrary database filtering to frontend clients is traditionally dangerous. ZCore's SearchEngine provides a declarative, expressive JSON search protocol that compiles down to safe, optimized SQLAlchemy 2.0 AST expressions while enforcing strict security boundaries.

1. The Compilation Pipeline

When a SearchRequest payload is received (typically via POST /search or POST /lookup), the SearchEngine coordinates execution across three distinct phases:

1. Validation & Security Assertion: Inspects all requested field paths, sort columns, and inclusion routes against ctx.restricted_fields and schema mappers.
2. Expression Building & Type Coercion: Recursively translates structured JSON comparison operators (eq, ne, gt, lt, ge, le, like, ilike, contains, startswith, endswith, in, between, is_null), inverted operators (not_like, not_ilike, not_contains, not_startswith, not_endswith, not_in, not_between, is_not_null), and logical grouping blocks (and, or, not) into SQLAlchemy comparison clauses while safely coercing input types (int, float, date, datetime, UUID, bool, and set).
3. Execution & Eager-Loading: Injects relation loaders (joinedload / selectinload), applies column projection (fields via load_only), custom SQLAlchemy options (options), sorting rules, and passes the query to the repository paginator.

2. Supported Filter Operators & Logical Negation

The compiler supports a comprehensive catalog of relational, pattern-matching, range, and boolean operators:

# Example: Nested logical NOT compilation in search.py
if f.op == "not":
    if f.items:
        sub_exprs = [self._get_operator_expression(item) for item in f.items if item is not None]
        return not_(and_(*sub_exprs)) if sub_exprs else None
    elif f.field:
        expr = self._build_expression_for_field(self.model, f.field, "eq", f.value)
        return not_(expr) if expr is not None else None

3. Security: Column & Relation Level Access Control

The defining security feature of SearchEngine is its seamless integration with ZContext.

Before compiling any comparison clause, the engine invokes _validate_filter_field. This recursively resolves dot-paths (e.g., assignee.profile.salary) and validates them against the active user's ctx.restricted_fields:

# Internal security validation in search.py
if self._is_path_restricted(current_path, restricted):
    raise ForbiddenError(f"Filtering by restricted path '{current_path}' is forbidden.")

4. Security: Denial of Service (DoS) Defense & Bounds

Malicious clients can craft massive, deeply nested AND/OR trees or infinite recursive relation joins to exhaust server memory and CPU cycles.

ZCore enforces strict, hierarchical boundaries during the validation sweep:

• Search Depth Hierarchy: Filter nesting and relation eager-loading depth (include) are resolved hierarchically:

  model.max_search_depth $\longrightarrow$ settings.SEARCH_MAX_DEPTH $\longrightarrow$ Default (3)

  Exceeding this threshold raises a ValidationError("Search query filter structure is too complex.").

• Automatic Page Size Clamping: The size parameter in SearchRequest is validated via Pydantic V2 model_validator(mode="after"). If omitted, it defaults to settings.PAGINATION_DEFAULT_SIZE (default: 20). If specified, it is strictly clamped between 1 and settings.PAGINATION_MAX_SIZE (default: 100), preventing massive unbounded database dumps.

5. Selective Projection & Custom Options (fields & options)

In addition to dynamic compilation, the search() method in both BaseRepository and BaseService accepts explicit column projection and SQLAlchemy options:

# Selective loading and custom SQLAlchemy options
results = await task_repo.search(
    search_request,
    fields=[Task.id, Task.title],           # Applies load_only(*fields)
    options=[selectinload(Task.assignee)],   # Applies custom executable options
    pagination=CursorParams(size=20)
)

This ensures that endpoints like /lookup can load only minimal columns while executing dynamic search and relation joins safely.

6. Intelligent Eager-Loading Optimization

The include parameter allows clients to request relational graphs dynamically without triggering N+1 database queries. The engine inspects SQLAlchemy model mappers to choose the optimal loader strategy:

• To-Many Relationships (rel.uselist == True): Automatically compiled with selectinload to prevent Cartesian product row duplication.
• To-One Relationships (rel.uselist == False): Automatically compiled with joinedload for single-query JOIN efficiency.

7. Relational Dot-Path Resolution & Type Safety

When filtering across foreign relations (e.g., { "field": "author.email", "op": "eq", "value": "[email protected]" }), the engine resolves the target model dynamically:

• For To-One relations: Compiles to Model.author.has(Author.email == "[email protected]").
• For To-Many relations: Compiles to Model.comments.any(Comment.status == "approved").