Files

T

Joseph Doherty c6aeb20d9c docs: update documentation for extraction functions migration

- Add ExtractionFunctions.md reference document
- Update database-schema spec with 11 extraction functions
- Update data-access spec to document extraction function approach
- Update search-processing spec with new query builder interface
- Add Database.Tests to Testing.md architecture doc
- Update DataFlow.md with extraction function flow

2026-01-06 14:54:10 -05:00

2.8 KiB

Raw Permalink Blame History

Data Flow

The system has two primary data flows: search execution and data synchronization.

Search Flow

The search flow mirrors the legacy pattern, modernized for ASP.NET Core:

1. User submits search via Blazor UI
   └─> POST /api/search (SearchCriteria JSON)

2. SearchController validates and stores in SQL Server
   └─> Search record created with Status = "Queued"
   └─> Returns SearchId to client

3. Client connects to SignalR StatusHub
   └─> Subscribes to updates for their SearchId

4. SearchProcessorService (BackgroundService) polls
   └─> Finds queued searches
   └─> Passes SearchId to query builder
   └─> SQL extraction functions read criteria from Search.Criteria JSON
   └─> Executes query against local cache
   └─> Generates Excel via ClosedXML
   └─> Stores result in Search.Results (VARBINARY)
   └─> Updates Status = "Complete"

5. StatusHub pushes update to client
   └─> Client shows "Complete" status

6. User clicks download
   └─> GET /api/search/{id}/download
   └─> Returns Excel file stream

Search Status States

Status	Description
`Queued`	Search submitted, waiting for processing
`Processing`	Background service is executing the search
`Generating`	Query complete, generating Excel file
`Complete`	Excel ready for download
`Failed`	Error occurred during processing

Data Sync Flow

The DataSyncService runs on a schedule to keep the local SQL Server cache current:

DataSyncService runs on schedule:
├── Mass refresh: Full reload (weekly or manual trigger)
├── Daily refresh: Last 24-48 hours of changes
└── Hourly refresh: Incremental updates

Each sync:
1. Determine tables to sync based on schedule
2. Query JDE/CMS Oracle for changes since last sync
3. Bulk insert/update to SQL Server cache
4. Update DataUpdate table with timestamp

Sync Schedules

Schedule	Frequency	Scope
Mass	Weekly (Sunday 2 AM) or manual	Full reload of all cached tables
Daily	Daily (3 AM)	Changes from last 48 hours
Hourly	Every hour	Incremental changes since last sync

The schedules are configured via cron expressions in appsettings.json and parsed using the Cronos library.

Database Connections

Database	Purpose	Driver
SQL Server	Local cache, search storage	Microsoft.Data.SqlClient
JDE Oracle	Enterprise work order data	Oracle.ManagedDataAccess.Core
CMS Oracle	Enterprise CMS data (migrated from Sybase)	Oracle.ManagedDataAccess.Core

2.8 KiB Raw Permalink Blame History