jdescopingtool/openspec/changes/archive/2026-01-01-implement-search-processing/specs/search-processing/spec.md

Search Processing Specification Delta

Purpose

This document captures ADDED and MODIFIED requirements for the search processing subsystem specific to the .NET 10 migration. It supplements the base specification at openspec/specs/search-processing/spec.md.

ADDED Requirements

Requirement: SqlKata Query Builder Integration

The system SHALL use SqlKata fluent query builder instead of T4 text templates for dynamic SQL generation.

Inputs

• SearchModel containing filter criteria and enriched filter entries
• SqlServerCompiler for T-SQL generation

Outputs

• SearchQueryResult record containing:
  • Sql: Parameterized T-SQL query string
  • Parameters: Dictionary of named parameter values
  • TempTableSetupSql: List of temp table creation/population statements

Business Rules

• SqlKata SqlServerCompiler SHALL be registered as a singleton (thread-safe)
• All queries SHALL use named parameters (not positional)
• Parameter names SHALL match legacy convention (@p_* prefix)
• Generated SQL SHALL produce equivalent results to legacy QueryTemplate.tt

Scenario: Build query with SqlKata

• WHEN ISearchQueryBuilder.BuildSearchQuery(model) is called
• THEN SqlKata generates parameterized SQL with named bindings
• AND the SearchQueryResult.Sql is executable via Dapper
• AND SearchQueryResult.Parameters contains all TVP parameters

---

Requirement: Filter Handler Pattern

The system SHALL use a composable filter handler pattern for modular query building.

Inputs

• SearchModel with active filter criteria
• SqlServerCompiler for SQL compilation

Outputs

• FilterResult record containing:
  • SetupSql: List of temp table setup statements
  • Parameters: Dictionary of parameters for this filter

Business Rules

• Each filter type SHALL have a dedicated IFilterHandler implementation
• Filter handlers SHALL be registered in dependency injection container
• Handlers SHALL execute in priority order (lower priority = earlier execution)
• Handler priorities SHALL ensure dependent temp tables exist before use:
  • WorkOrder: 10
  • ItemNumber: 20
  • ComponentLot: 30
  • ProfitCenter: 40
  • WorkCenter: 50
  • Operator: 60
  • ItemOperationMis: 70
  • Timespan: 80

Scenario: Execute filter handlers in order

• WHEN search criteria includes work orders, items, and operators
• THEN WorkOrderFilterHandler executes first (priority 10)
• AND ItemNumberFilterHandler executes second (priority 20)
• AND OperatorFilterHandler executes later (priority 60)

---

Requirement: IAsyncEnumerable Result Streaming

The system SHALL support streaming large result sets using IAsyncEnumerable<T>.

Inputs

• SearchModel with executed query
• CancellationToken for cooperative cancellation

Outputs

• IAsyncEnumerable<SearchResult> streaming results one at a time

Business Rules

• Streaming SHALL use Dapper's QueryUnbufferedAsync method
• Cancellation SHALL be supported via [EnumeratorCancellation] attribute
• Memory allocation SHALL remain constant regardless of result set size
• Consumer MAY materialize results using ToListAsync() when needed

Scenario: Stream large result set

• WHEN search returns 10,000 work orders
• THEN results stream via IAsyncEnumerable<SearchResult>
• AND memory usage remains constant during enumeration
• AND await foreach consumes results incrementally

---

Requirement: Async-First Design

The system SHALL use async methods throughout the search processing pipeline.

Business Rules

• All repository methods SHALL accept CancellationToken parameter
• All database operations SHALL use async Dapper methods (QueryAsync, ExecuteAsync)
• Long-running operations SHALL respect cancellation tokens
• ISearchProcessor.ExecuteSearchAsync SHALL be the primary entry point

Scenario: Cancel long-running search

• WHEN a search is in progress and cancellation is requested
• THEN the operation throws OperationCanceledException
• AND database connections are properly disposed

---

Requirement: Configuration via IOptions Pattern

The system SHALL use IOptions<SearchProcessingOptions> for configuration.

Inputs

• appsettings.json section: SearchProcessing

Outputs

• Strongly-typed SearchProcessingOptions injected via DI

Configuration Properties

Property	Type	Default	Description
QueryTimeoutSeconds	int	600	SQL query timeout
MaxTraversalIterations	int	20	Downstream traversal limit
EnableDebugSql	bool	false	Write SQL to debug files
DebugSqlPath	string?	null	Path for debug SQL files

Scenario: Configure query timeout

• WHEN SearchProcessingOptions.QueryTimeoutSeconds is set to 900
• THEN Dapper queries use commandTimeout: 900

---

Requirement: Service Registration Extension

The system SHALL provide an AddSearchProcessing extension method for DI registration.

Service Lifetimes

Service	Lifetime	Rationale
SqlServerCompiler	Singleton	Thread-safe, stateless
IFilterHandler implementations	Scoped	Per-request state
ISearchQueryBuilder	Scoped	Uses scoped handlers
ISearchProcessor	Scoped	Uses scoped repositories
IWorkOrderTraversalService	Scoped	Uses connection factory

Scenario: Register search processing services

• WHEN services.AddSearchProcessing(configuration) is called
• THEN all search processing services are registered
• AND ISearchProcessor can be resolved from service provider

---

MODIFIED Requirements

Requirement: Work Order Traversal

The system SHALL execute downstream work order traversal via stored procedure dbo.TraverseWorkOrders instead of inline WHILE loop in generated SQL.

Inputs

• Active database connection with #Temp_WO temporary table populated
• Maximum iteration count (default 20)

Outputs

• Updated #Temp_WO table with downstream work orders flagged

Business Rules

• IWorkOrderTraversalService.TraverseDownstreamAsync SHALL call stored procedure
• Stored procedure SHALL contain iterative WHILE loop logic
• Single stored procedure call SHALL replace 20 inline iterations
• Transaction scope SHALL be maintained within stored procedure

Scenario: Execute downstream traversal via stored procedure

• WHEN initial work orders are flagged in #Temp_WO
• THEN dbo.TraverseWorkOrders stored procedure is called
• AND downstream work orders are added with PartsList and CARDEX flags
• AND split orders are detected and flagged

---

Requirement: Filter Entry Types

The system SHALL use C# record types for filter entry DTOs to provide immutability and value semantics.

Inputs

• Raw filter values from SearchCriteria
• Reference data lookups from ILotFinderRepository

Outputs

• Immutable record instances with output attributes for Excel export

Business Rules

• All filter entry types SHALL be declared as C# records
• Records SHALL use primary constructor syntax
• Output attributes SHALL be applied using [property:] target
• Records SHALL provide value-based equality for testing

Scenario: Create immutable filter entry record

• WHEN a WorkOrderFilterEntry is created with WorkOrderNumber 12345 and ItemNumber "ITEM-001"
• THEN the record is immutable (properties are init-only)
• AND two records with same values are considered equal

---

Requirement: SQL Client Package

The system SHALL use Microsoft.Data.SqlClient instead of deprecated System.Data.SqlClient for SQL Server connectivity.

Business Rules

• All SQL Server connections SHALL use Microsoft.Data.SqlClient.SqlConnection
• All SQL commands SHALL use Microsoft.Data.SqlClient.SqlCommand
• NuGet package Microsoft.Data.SqlClient version 5.2+ SHALL be referenced
• Code SHALL NOT reference System.Data.SqlClient namespace

Scenario: Create SQL connection with modern client

• WHEN IDbConnectionFactory.CreateLotFinderConnectionAsync is called
• THEN a Microsoft.Data.SqlClient.SqlConnection instance is returned
• AND connection supports all modern SQL Server features

---

Migration Notes

Legacy Pattern	New Pattern	Status
T4 Text Template	SqlKata fluent builder	ADDED
Inline WHILE loop	Stored procedure	MODIFIED
Filter entry classes	Record types	MODIFIED
Synchronous Dapper	Async Dapper	MODIFIED
System.Data.SqlClient	Microsoft.Data.SqlClient	MODIFIED
Static class methods	DI-registered services	MODIFIED
Newtonsoft.Json	System.Text.Json	Retained in domain models

---

Open Questions

None - all design decisions resolved per base specification.