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

Implement Search Processing

Summary

Implement the search processing subsystem that executes user-defined filter queries against the SQL Server cache database. This phase replaces the legacy T4 text template (QueryTemplate.tt) with a SqlKata fluent query builder, providing type-safe query construction, parameterized SQL generation, and composable filter handling.

Scope

In Scope

• ISearchQueryBuilder interface and SqlKataSearchQueryBuilder implementation
• Filter handler pattern with individual handlers per filter type:
  • WorkOrderFilterHandler
  • ItemNumberFilterHandler
  • ProfitCenterFilterHandler
  • WorkCenterFilterHandler
  • OperatorFilterHandler
  • ComponentLotFilterHandler
  • ItemOperationMisFilterHandler
  • TimespanFilterHandler
• ISearchProcessor interface and SearchProcessor service implementation
• IWorkOrderTraversalService interface for downstream work order traversal
• SearchModel class (reporting model with enriched filter entries)
• Filter entry record types (immutable DTOs with output attributes)
• Result record types: SearchResult, MisSearchResult, MisNonMatchSearchResult
• Table-valued parameter creation helpers
• MIS data extraction query building
• SearchProcessingOptions configuration class
• AddSearchProcessing service registration extension method
• Unit tests with xUnit, Shouldly, and NSubstitute

Out of Scope

• Background job scheduling (handled by Phase 5 data-sync or separate worker phase)
• Excel export generation (Phase 7: excel-export)
• API endpoints for search submission (Phase 8: web-api-auth)
• SignalR real-time status updates (Phase 8: web-api-auth)
• Database schema changes (Phase 1: migrate-database-schema)

Motivation

The legacy T4 text template approach has significant limitations:

• SDK incompatibility: T4 templates are poorly supported in modern .NET SDK-style projects
• Untestable: Generated SQL cannot be unit tested without database execution
• Fragile: String concatenation prone to SQL injection and syntax errors
• Untyped: No compile-time validation of query structure

SqlKata provides:

• Fluent API: Readable, composable query building with IntelliSense support
• Parameterized by default: SQL injection protection built-in
• Testable: Unit test query building without database
• Type-safe: Compile-time checking of method calls
• SQL Server optimized: SqlServerCompiler generates optimized T-SQL

Acceptance Criteria

1. ISearchQueryBuilder interface defined with BuildSearchQuery(SearchModel) method
2. SqlKataSearchQueryBuilder generates equivalent SQL to legacy QueryTemplate.tt
3. All 8 filter handlers implemented with conditional join/where clause generation
4. SearchProcessor orchestrates:
   • Filter entry enrichment via repository lookups
   • Query building via SqlKata
   • Query execution via Dapper
   • Result aggregation into SearchModel.Results
   • MIS data extraction when ExtractMisData = true
5. Downstream work order traversal calls stored procedure dbo.TraverseWorkOrders
6. Table-valued parameters created correctly for all filter types
7. All result types include OutputColumnAttribute and OutputTableAttribute for Excel export
8. Unit tests verify:
   • Query builder generates expected SQL structure
   • Filter handlers apply correct joins and conditions
   • Parameter binding works for all TVP types
9. AddSearchProcessing registers all services with appropriate lifetimes
10. openspec validate implement-search-processing --strict passes

Dependencies

• Phase 1: migrate-database-schema - Database tables, TVP types, stored procedures
• Phase 3: implement-domain-models - Core domain entities (Search, SearchCriteria, etc.)
• Phase 4: implement-data-access - IDbConnectionFactory, ILotFinderRepository for lookups
• NuGet packages: SqlKata, SqlKata.Execution, Dapper, Microsoft.Data.SqlClient

Risks

Risk	Mitigation
Query logic drift from legacy	Codex MCP review comparing generated SQL against QueryTemplate.tt output
Complex filter combinations	Comprehensive unit test matrix covering all filter permutations
MIS extraction query complexity	Retain MIS extraction as separate stored procedure if needed
Performance regression	Benchmark query execution time against legacy implementation
Downstream traversal correctness	Stored procedure dbo.TraverseWorkOrders encapsulates iterative logic

Related Specs

• search-processing - Primary specification for query building and search execution
• domain-models - Entity types used in search criteria and results
• data-access - Repository interfaces for filter enrichment lookups
• database-schema - SQL Server tables and TVP types for search execution