PSI.UniData.API

REST API gateway providing web application access to the PSI UniData/AFTEC database. Enables modern web apps to query ERP data without direct UniData connectivity.

Overview

Redbook correctness checkpoint (2026-09-08, not deployed)

Local Redbook service changes recognize desktop department codes by multivalue slot, retain compatibility with earlier web Y/N flags, and persist the proper business code on transitions. Unchanged department metadata and unrelated raw record attributes are preserved. Invalid department input is rejected. Priority lookup now matches the desktop: 0-High, 1-Normal, 2-Low.

Regression fixtures are synthetic. Production impact verification and shared atomic concurrency control across desktop/web writers remain outstanding; this checkpoint does not claim deployment or safe concurrent writes.

PSI.UniData.API is a .NET 8 Web API that exposes UniData subroutines via REST endpoints. It uses the U2 Toolkit for .NET for high-performance database access, bypassing the slow WCF/PSI Local Service layer.

Web Apps → REST API → U2 Toolkit → UniData (MRP-PROD)
     ↑                    ↑
  Azure AD           Per-user credentials

Feature	Description
Production URL	https://api.progressivesurface.com
Performance	~200-300ms for full BOM explosion (vs 5-7 min via WCF)
Authentication	Azure AD with per-user UniData credential lookup
Deployment	Windows Service on PS-PROXY with auto-deployment
Documentation	Swagger UI at root URL

Repository: ProgressiveSurface/PSI.UniData.API

Production Deployment

Server Details

Setting	Value
Server	PS-PROXY.AD.PTIHOME.com
Service Name	PSI.UniData.API
Install Path	`C:\Services\PSI.UniData.API`
HTTPS Port	443 (wildcard cert: *.progressivesurface.com)
HTTP Port	80
DNS	api.progressivesurface.com → 192.9.201.217

Auto-Deployment (GitHub Actions)

The repo has a self-hosted GitHub Actions runner on PS-PROXY that handles two workflows:

Deploy to PS-PROXY (API)

Push changes to src/** or deploy/** on master branch
GitHub Actions automatically:
- Backs up production config
- Stops the service (with force-kill if needed)
- Builds and publishes to C:\Services\PSI.UniData.API
- Restores appsettings.Production.json, then re-syncs secret-free config sections (currently Cors) verbatim from deploy/appsettings.Production.json and adds any new top-level sections
- Restarts the service
- Runs health check
- Verifies the CORS allowlist — preflights every production origin against the live service and fails the deploy if any origin is missing its Access-Control-Allow-Origin header

CORS allowlist is config-driven and must not drift. Origins live in Cors:AllowedOrigins in deploy/appsettings.Production.json (source of truth). Because the API uses AllowCredentials(), an origin not in the deployed list receives zero CORS headers and the calling web app fails with No 'Access-Control-Allow-Origin' header. Historically the deploy only added missing config sections and never updated existing ones, so origin changes silently never reached the host (2026-07-14 incident: explorer.progressivesurface.com missing from the live list). The Cors re-sync + post-deploy verification steps above now prevent this. To hot-fix without a full deploy, edit Cors:AllowedOrigins in the live C:\Services\PSI.UniData.API\appsettings.Production.json and restart PSI.UniData.API.

Nightly Data Build (Pipeline)

Runs every night at 1 AM ET (cron 0 6 * * * UTC) — after AFTEC nightly exports finish.

Checks out repo, installs Python 3.12 via actions/setup-python@v5
Extracts LDS Gantt schedules from \\ad.ptihome.com\DFS\LDS\PROJECT
Builds comprehensive dataset from 13 data sources (~60 seconds)
Deploys to \\ad.ptihome.com\DFS\Schedule\SS123\LEADTIME\:
- comprehensive_dataset.csv — 2,569 projects × 152 columns
- detail\ subfolder — 6 full-fidelity detail CSVs (all source columns preserved)
Verifies deployment (row count checks, detail file existence)

See Data Brain for full detail on data sources and outputs.

Monitor deployments: https://progressivesurface.ghe.com/ProgressiveSurface/PSI.UniData.API/actions

Manual trigger: Actions → workflow name → “Run workflow”

Credential Resolution

Every UniData session must open as a real AD user whose decrypted password is stored in extensionAttribute2 — UniData has no app-only login path. The API authenticates the caller via Azure AD, then opens the UniData session as one of two AD users depending on which authorization policy the caller matched:

Caller type	AAD token shape	UniData session opens as
Interactive user (web app)	User token (`upn` claim)	That user’s own SAM (per-user credential passthrough)
Trusted app on behalf of a user	App-role token with `UniData.ActAsUser` + `X-On-Behalf-Of: <upn>` header	The asserted user’s own SAM — per-user session, no service account
Pipeline / unattended	App-role token with `UniData.ServiceRead`	`UniData:ServiceAccount` — a dedicated AD user (`apiservice`, sAMAccountName `API`) with UniData creds in extensionAttribute2. Must never be a person’s login: the account runs all service-account/anonymous UniData work, so pointing it at a person impersonates them and consumes their license/audit.

Trusted delegation (UniData.ActAsUser). Added for the MCP server / Ask the Fleet: a trusted application (one signed in via its own app-role token) can act on behalf of a named user by sending X-On-Behalf-Of: <upn>. The API honors the header only for callers holding the UniData.ActAsUser app role (otherwise 403) and opens that user’s own UniData session — so service-ticket reads via /api/service are audited to the real person, never the service account. The MCP app is granted only UniData.ActAsUser (not ServiceRead), so it is structurally incapable of a service-account session.

Consumers of the delegated path today:

Caller	Identity	Acts as	Reads
psi-machine MCP / Ask the Fleet	its own app registration	the asking user	`/api/service`, fleet reads
IT Help Desk bot (2026-08)	the Function App’s system-assigned managed identity	the HR person who opened the form	`EMPLOYEE.PUBLIC.1287`, `EMPLOYEE`

The bot’s case is worth noting as the pattern to copy: an Azure resource with no UniData credential of its own reads AFTEC as whichever employee is standing at the form, so the AFTEC audit names Gina or Karen. A managed identity plus the app role plus X-On-Behalf-Of is the whole mechanism — nothing is stored, and dropping the header degrades to 403, not to an anonymous read.

Credentials are cached per UPN for 10 minutes (IMemoryCache) to limit AD query load. AD lookups have a 5-second timeout. The AES secret used to decrypt extensionAttribute2 is read from Azure Key Vault (secret psi-unidata-api--credential-secret) in production via the PS-PROXY managed identity.

Validating a stored credential — the `EAAAA` sentinel

A correctly encrypted extensionAttribute2 value is Base64 beginning EAAAA — exactly five A’s. The 6th character varies with the random per-encryption IV, so a check written with six A’s silently misclassifies good values. A real blob is also ≥48 characters (4-byte IV-length prefix + 16-byte IV + at least one AES block), which makes length a useful second signal: anything shorter than 48 characters cannot be ciphertext.

The decrypt path treats a non-sentinel value as unencrypted and returns it unchanged. That is deliberate, but it means a writer that omits the encrypt step produces a system where every reader still works — nothing errors and nothing logs. Anything that writes this attribute must therefore assert the sentinel immediately before the write and refuse the write if it fails. Put the guard at the write boundary, since that is the only point every code path funnels through. Periodically auditing the population is also worthwhile: a value not matching ^EAAAA is always a defect.

Note also that PSILocalService.ValidateLogin never returns a directly storable credential — neither of its two branches produces a value in AD format. The client is always responsible for encrypting, with the client secret, before storage. See docs/psi-credential-encryption.md in the PSI.UniData.API repo for the key derivation, both branch behaviours, and the writer’s contract.

Per-user is mandatory for token-bearing requests — no service-account fallback. A request that arrives with a user’s Entra token always opens that user’s own UniData session. The service account is reserved for automation (app-role UniData.ServiceRead) and Development-only anonymous /dev routes. There is deliberately no silent fallback to the service account when a per-user session can’t be opened — that would route a user’s reads/writes through the service identity and mask a per-user auth regression (GHE issue #11). Instead the request hard-fails with a precise status:

Failure (`UniDataCredentialException.Reason`)	HTTP status	Meaning / action
`CredentialsNotProvisioned`	403	Caller’s AD account has no `extensionAttribute2`. Provision their UniData credentials.
`UserNotFound`	403	Caller’s Entra UPN didn’t resolve to an on-prem AD user.
`SecretNotConfigured`	503	Server-side: the credential-decrypting Key Vault secret isn’t configured (deploy/config issue, not the user’s account).
`DirectoryError`	502	AD unreachable / query timed out. Transient.
`DecryptionFailed`	500	Stored credential couldn’t be decrypted.

Mapping is centralized in Endpoints/CredentialProblem and applied by both per-endpoint catch blocks and the global exception handler, so every endpoint behaves identically. The UniData username is the on-prem SamAccountName (the user’s three initials), never an AD attribute.

Development vs Production routes

/api/**/dev/* routes are Development-environment only — they are registered only when ASPNETCORE_ENVIRONMENT=Development and use the service account. In the production binary on PS-PROXY they simply do not exist in the route table; a request for /api/project/dev/1234/info returns 404, not 401. This prevents misconfiguration from ever re-opening an anonymous bypass against MRP-PROD.

Pipelines (GitHub Actions, scheduled batches) acquire an app-role token via pipeline/api_auth.py (MSAL confidential client / federated credential) and call the authenticated routes — same URLs humans use. No pipeline-specific route survives in production.

PS-MRPSANDBOX — Development Sandbox Server

PS-MRPSANDBOX.ad.ptihome.com is a dedicated UniData sandbox server used for testing write operations against the API without touching the production AFTEC database.

Property	Value
Hostname	`PS-MRPSANDBOX.ad.ptihome.com` (also `PS-MRPSANDBOX`)
Account path	`/home/pro3` (same as MRP-PROD)
Authentication	Same AD user credentials as MRP-PROD
Purpose	Safe write testing — all CRUD operations, schema changes, data seeding
Data	Mirror/copy of MRP-PROD data at time of provisioning

To run the API against the sandbox, override UniData:Server in appsettings.Development.json:

{
  "UniData": {
    "Server": "PS-MRPSANDBOX"
  }
}

Then start with ASPNETCORE_ENVIRONMENT=Development dotnet run. The startup log will confirm:

UniData Server: PS-MRPSANDBOX

The sandbox is the required target for any new write endpoint before merging to master. The timesheet controller (/api/timesheets) was fully verified against PS-MRPSANDBOX before production deployment.

Architecture

Authentication Flow (user request)

User signs in to the web app via Azure AD (MSAL.js)
Web app sends the request with a JWT Bearer token
API validates the JWT and extracts the UPN claim
ActiveDirectoryService.GetCredentialsForUser resolves the UPN to a SAM + decrypted UniData password (cached 10 min)
UniDataConnectionFactory.CreateConnection opens the UniData session as that SAM
Query runs with the user’s own AFTEC permissions; audit trail reflects the real user

Authentication Flow (pipeline / app-role)

Pipeline uses pipeline/api_auth.py → acquires app-role token for api://<clientId>/.default
Request hits an authenticated endpoint with the token
API sees the UniData.ServiceRead role claim, routes through GetServiceAccountCredentials
Connection opens as the dedicated service account API (AD account apiservice, sAMAccountName API; configured in UniData:ServiceAccount)
Pipeline traffic is attributed to the dedicated service user, not any human

Authentication Flow (developer / CLI)

Enabled 2026-07-15 (applied by app-registration owner ADevereaux): Microsoft Azure CLI is a pre-authorized client on the API’s app registration (api.preAuthorizedApplications: client 04b07795-8ddb-461a-bbee-02f9e1bf7b46 → delegated scope user_impersonation, scope id e6b2e13c-00a8-41a9-ae24-8df62ff71de3). A developer signed into az obtains a token as themselves with:

az account get-access-token --resource "api://b3db69d9-5d15-457d-b660-88b336fc00fa" --query accessToken -o tsv

The token then follows the normal user flow above — the UniData session opens with that developer’s AFTEC permissions and the audit trail shows the real user. This grants no standing access to anyone who couldn’t already call the API through a PSI SPA; it exists so command-line tooling (e.g. psi-portal/scripts/verify-account-search.mjs --live, which self-acquires via az) can use authenticated endpoints instead of the anonymous /dev routes, which are being phased out. First use: the oracle-§5 acceptance run on the account search (2026-07-15) — deployed endpoint ids + order verified identical to an ACCOUNT.1287 ground-truth pull for CHROMALLOY / CHROMALLOY GAS TURBINE / GENERAL ELECTRIC.

Why Skip PSI Local Service?

PSI Local Service (WCF)	U2 Toolkit Direct
~100-500ms overhead per call	~1-5ms overhead per call
Single-threaded bottleneck	Connection pooling possible
Legacy .NET Framework 4.x	.NET Standard 2.0 / .NET 8
Must run on user’s machine	Runs on server, shared by all

API Endpoints

Purchase Requests (selected)

Endpoint	Description
`GET /api/purchase-request/mine`	PRs the caller created
`GET /api/purchase-request/awaiting-approval`	PRs pending manager approval
`GET /api/purchase-request/pc-hardware?months=12`	PC-hardware sweep (2026-08-13): every PR in the window with a `PC.<dept>` line — the part code is the paying department. Feeds psi-portal’s PC → department ownership backfill. Gotcha encoded in the source: `OPEN.RFQ.LINE` has no `PART` dict (the part code’s dict is literally `F2`), and selection uses `LIKE "PC..."` — the `= "PC.]"` truncation dialect silently matches nothing via VB_GETLIST.

Health

Endpoint	Description
`GET /api/health`	Basic health check
`GET /api/health/unidata`	UniData connection test

BOM (Bill of Materials)

Recursive BOM explosion using the VB_BOMX.REV1 UniData subroutine. Returns the full multi-level Bill of Materials with inventory, lead times, and MRP classification. Safety limits prevent runaway explosions: max depth of 20 levels, max 10,000 items, and 30-second per-call timeout.

Endpoint	Description	Auth
`GET /api/bom/job/{jobNumber}`	BOM explosion for a job	Required (user or app-role)
`GET /api/bom/part/{partNumber}`	BOM explosion for a part	Required (user or app-role)
`POST /api/bom/jobs`	Batch — explode BOMs for up to 50 jobs in one call	Required (user or app-role)
`GET /api/bom/dev/job/{jobNumber}`	Service-account path	Anonymous (Development env only)
`GET /api/bom/dev/part/{partNumber}`	Service-account path	Anonymous (Development env only)
`POST /api/bom/dev/jobs`	Service-account batch	Anonymous (Development env only)

Batch BOM (POST /api/bom/jobs): Body is a JSON array of job number strings. Opens one UniData session and reuses it for all explosions. Returns { results: { "jobNo": BomResponse, ... }, found, empty, failed, emptyIds, failedIds, elapsedMs, totalDatabaseCalls }. Used by sync_bom_data.py and rockwell_lifecycle.py in the pipeline — both now acquire an app-role token via pipeline/api_auth.py and call this authed endpoint directly. Reduces ~2,600 sequential HTTP calls to ~52 batch calls.

Response-level fields:

Field	Type	Description
`rootIdentifier`	string	Job or part number that was exploded
`totalItems`	int	Total items in BOM
`maxLevel`	int	Deepest hierarchy level
`elapsedMs`	long	Time to retrieve the BOM
`databaseCalls`	int	Number of subroutine calls made
`wasTruncated`	bool	Whether BOM hit depth or item count limit
`truncationReason`	string?	`"max_depth"` or `"max_items"` if truncated
`warnings`	string[]	Errors from failed subroutine calls (partial data indicator)

Item-level fields:

Field	Type	Description
`partNumber`	string	Part number
`description`	string	Part description
`level`	int	BOM depth (0 = root)
`wbsNumber`	string	Work Breakdown Structure position
`parentPartNumber`	string?	Parent part (null for root)
`mrpCode`	string	MRP type from PRODUCT table (`P`=Purchased, `M`=Manufactured, `D`=Document)
`gtCode`	string	GT Code (BOM hierarchy classification)
`isPurchased`	bool	`mrpCode == "P"` or `gtCode == "PU"/"VM"`
`isManufactured`	bool	`mrpCode == "M"`
`qtyPerParent`	decimal	Quantity per parent assembly
`qtyPerLeg`	decimal	Quantity per leg
`unitOfMeasure`	string	Unit of measure (EA, FT, LB, etc.)
`mfgLeadTime`	int	Manufacturing lead time (days)
`purchaseLeadTime`	int	Purchase lead time (days)
`cumulativeLeadTime`	int	Cumulative lead time (days)
`onHand`	decimal	On-hand inventory
`onOrder`	decimal	On order quantity
`available`	decimal	Available quantity
`totalAllocated`	decimal	Total allocated
`hasChildren`	bool	Whether this item has child components
`isTransient`	bool	Temporary assembly flag
`drawingNumber`	string	Parent drawing number

Response Example:

{
  "rootIdentifier": "2242",
  "totalItems": 2066,
  "maxLevel": 5,
  "elapsedMs": 471,
  "databaseCalls": 528,
  "wasTruncated": false,
  "truncationReason": null,
  "warnings": [],
  "items": [
    {
      "level": 1,
      "wbsNumber": "00101",
      "partNumber": "281574",
      "description": "Front Wall Per Print",
      "mrpCode": "P",
      "gtCode": "",
      "isPurchased": true,
      "isManufactured": false,
      "qtyPerParent": 1,
      "unitOfMeasure": "EA",
      "purchaseLeadTime": 15,
      "cumulativeLeadTime": 15,
      "hasChildren": false,
      "onHand": 0,
      "available": 0
    }
  ]
}

Projects (Machine Provenance)

Project/machine metadata from PROJECT.1287, with resolved machine-type, customer, and employee names. Each endpoint has a dev/ anonymous (service-account) twin for SPA development.

Endpoint	Description	Auth
`GET /api/project/{job}/info`	Enriched project metadata (customer, machine type, team, dates)	Required
`GET /api/project/{job}/siblings`	Projects with the same machine type	Required
`GET /api/project/{job}/lineage`	Reference-chain predecessors + successors (REF.PROJ.NO / shared serial)	Required
`GET /api/project/machine-types`	All machine types with project counts	Required
`GET /api/project/by-type/{typeCode}`	All projects for a machine type	Required
`GET /api/project/search?q={query}`	Search projects by description keyword (full scan)	Required
`GET /api/project/by-serial/{serial}`	Look up project(s) by machine serial number (indexed `WITH SERIAL.NO` BSELECT)	Required
`GET /api/project/dev/by-serial/{serial}`	Dev endpoint	Anonymous

Serial lookup (GET /api/project/by-serial/{serial}): resolves a machine serial to its project/job number — used by psi-service (Customer Service Manager)‘s Serial # filter to auto-fill the Project # field. Returns { serial, totalResults, projects: [ { projectNumber, serialNumber, description, customerName, status, shipDate, … } ] }. No match → 200 with an empty projects array (not 404). A serial usually maps to one project, but a list is returned since retrofits can share/reference a serial.

Accounts (ACCOUNT.1287)

Account search replicating the legacy VB6 Account Manager list contract (the vb6-specs acceptance oracle extracted from frmAccount.frm::LoadAccounts). Added 2026-07-15 as the live data source for the psi-portal Customer Data Reconciliation account rail.

Endpoint	Description	Auth
`GET /api/accounts/search?name={q}`	Search accounts by name	Required

Contract: base filter DELETED # 'Y' (always), name match UC.NAME LIKE '...q...' (UniQuery “contains”), ordered BY UC.NAME — character-code sort of the uppercased name, location then key as tiebreaks. Returns { query, count, accounts: [{ accountNo, name, location }], elapsedMs } where accountNo is the full CO!ACCT record key (e.g. 1!0409).

Implementation notes: UC.NAME/UC.LOCATION are dictionary I-descriptors, evaluated via LIST … TOXML (DataQueryService.ListWithCriteriaToXml) — no attribute numbers hardcoded. The search text is uppercased and whitelisted (letters/digits/space/.,&/-, and at least 2 letters/digits so punctuation-only input can’t devolve into a match-everything wildcard) before entering the quoted literal; AccountSearchSanitizerTests covers the gate. No anonymous /dev twin exists in production (it is mapped only in the Development environment) — this endpoint set follows the newer authenticated-only posture.

Acceptance harness: psi-portal/scripts/verify-account-search.mjs pulls ACCOUNT.1287 ground truth, simulates the expected result for a query, and with --live diffs this endpoint’s response (ids + order) against it.

Parts

Endpoint	Description	Auth
`GET /api/parts/{partNumber}`	Part details	Required
`GET /api/parts/search?q={query}`	Search parts	Required
`GET /api/parts/dev/{partNumber}`	Dev endpoint	Anonymous
`GET /api/parts/dev/search?q={query}`	Dev endpoint	Anonymous
`POST /api/parts/dev/batch`	Batch — look up up to 500 parts in one call	Anonymous

Batch Parts (POST /api/parts/dev/batch): Body is a JSON array of part number strings. Returns { parts: { "partNo": PartInfo, ... }, found, notFound, notFoundIds, elapsedMs }. Uses VB_PARTINFO.REV2 with session reuse.

Work Orders

Endpoint	Description	Auth
`GET /api/wo/{workOrderNumber}`	Work order details	Required
`GET /api/wo/dev/{workOrderNumber}`	Dev endpoint	Anonymous

IBM (Issued But Missing)

Endpoint	Description	Auth
`GET /api/ibm/dev/active`	IBM items on active (Released) WOs	Anonymous
`GET /api/ibm/dev/all`	All IBM items (active + completed)	Anonymous
`GET /api/ibm/dev/job/{jobNumber}`	IBM items for a specific job	Anonymous
`GET /api/ibm/dev/summary`	Counts by WO status, grouped by job	Anonymous

Each IBM item includes lineNote (WIPBMF field 27) and notes (from WIPBMF.NOTES table) — the same notes created by the WIPBOM desktop app. See Inventory & Work Orders: IBM for field-level documentation.

Routing / WIP State (2026-08-10, GHE #115)

Read-only routing state that exists in no AFTEC export — built for WIP/routing dashboards (see the psi-analytics wip/ study). App-role (UniData.ServiceRead) callers are supported so nightly pipelines can refresh these unattended; /dev twins are Development-only.

Endpoint	Description	Auth
`GET /api/routing/notrouted`	Live NOTROUTED.1287 queue: per-part description, MW/EA/MA/JS route suggestions (PRODUCT.1287 F9), standard-route existence, job priority/seq/level	Required (app-role OK)
`POST /api/routing/wiproute-lines/batch`	WIPROUTE.LINE detail for up to 200 WOs: routed hours (SETUP-HRS/MAN-HRS), completion flag, plan/actual dates, LAST.LAB.DATE (F38). `includeComplete=false` → open ops only	Required (app-role OK)
`GET /api/routing/promise-drift?job=`	Released WOs (OPENWO PC=“R”) where ORIG.PROM ≠ DUEDATE, with drift days	Required (app-role OK)
`GET /api/routing/wo/{wo}/labor`	Per-entry labor history for one WO (LABORHIST via indexed WO.NO, ~90ms): work date, employee, operation, setup/labor hours, rework flag, overtime — plus per-operation rollup	Required (app-role OK)

Why these exist: wiplabor.csv only exports lines with posted labor (never-started ops have no hours anywhere), wiprouteline.csv is filtered vs the live table, NOTROUTED.1287 and ORIG.PROM are unexported, and the generic /api/query route is per-user-only. Status semantics: OPENWO F18 (PC) is the WO open/closed discriminator (C complete / R released / blank unreleased).

?sandbox= — read PS-MRPSANDBOX from an authenticated route (2026-08-12, GHE #119). All four routing endpoints accept an optional sandbox boolean. Omitted or false reads live MRP-PROD; true reads PS-MRPSANDBOX. This exists because psi-dispatch renders a SANDBOX-banner’d grid from the sandbox and its drill-downs must read the same source the grid came from — otherwise a labor history would silently mix sandbox routing with production hours. Verified live on 2026-08-13: promise-drift?job=2424 returns 110 parts against prod and 49 against PS-MRPSANDBOX. Note the parameter is a nullable bool? — an earlier revision made it required, so a paramless call 400’d; see the same class of bug still open for the vendor AP-history endpoints in GHE #117.

Dictionary/Schema

Live UniData DICT introspection — table field maps with attribute positions, conversion codes, formats, multivalue flags, and I-type (calculated) expressions. Base route is /api/dict (not /api/dictionary). Generic table data reads live under /api/data/* (see Data below), not /api/query.

Endpoint	Description	Auth
`GET /api/dict/tables`	List all UniData tables	Required
`GET /api/dict/tables/{tableName}`	Dictionary definition (fields) for a table	Required
`GET /api/dict/fields/search?q={query}`	Search dictionary entries by field name	Required
`GET /api/dict/dev/tables`	Dev twin	Anonymous
`GET /api/dict/dev/tables/{tableName}`	Dev twin	Anonymous
`GET /api/dict/dev/fields/search?q={query}`	Dev twin	Anonymous
`GET /api/dict/dev/tables/{tableName}/diagnose`	Test which dictionary access methods work for a table	Anonymous

Response fields (per dictionary entry): fieldName, attributePosition, dictionaryType (D = physical attribute, I = calculated), displayName, conversionCode, format, isMultivalue, isSubvalue, associatedField, iTypeExpression, dataType. A table’s fieldCount splits into dataFieldCount (D-type, incl. aliases) + calculatedFieldCount (I-type). See PRODUCT.1287 Field & Program Map for a worked example of how I-type entries encode virtual foreign keys via TRANS().

DICT reads are live and can be slow (~1.2 s for a 61-field table) because the dictionary is read from UniData on each call. A caching layer exists (see Dictionary Cache admin below) but is currently warmed only for the write-path tables. Bulk I-type evaluation (/api/data/dev/{table}/export-fields, which runs LIST … TOXML) is heavy and can time out on wide/large tables — prefer raw export + client-side conversion, or a warmed cache, for those.

Dictionary Cache (admin)

Server-side cache of parsed dictionary schemas, backed by on-disk JSON under data/dictionaries/. Anonymous — internal admin surface, consistent with the other /api/admin/* groups.

Endpoint	Description
`GET /api/admin/dict-cache/status`	Cache status (which tables are cached)
`POST /api/admin/dict-cache/refresh/{tableName}`	Refresh cache for one table
`POST /api/admin/dict-cache/refresh-all`	Refresh all tables (long-running)
`GET /api/admin/dict-cache/{tableName}`	View cached schema for a table
`DELETE /api/admin/dict-cache/{tableName}`	Evict a table from cache

Redbook (RFC Management)

Full CRUD operations for quality issue tracking. See Redbook Web for the frontend application.

RFC Operations

Endpoint	Description	Auth
`GET /api/redbook/rfc/{rfcNo}`	Get single RFC	Required
`POST /api/redbook/rfc`	Create new RFC	Required
`PUT /api/redbook/rfc/{rfcNo}`	Update RFC	Required
`DELETE /api/redbook/rfc/{rfcNo}`	Delete RFC	Required
`POST /api/redbook/search`	Search RFCs	Required
`GET /api/redbook/by-project/{projectNo}`	Get RFCs for project	Required

Lookup Endpoints (Dev)

Endpoint	Description	Data Source
`GET /api/redbook/dev/lookups/all`	All lookups combined	Multiple
`GET /api/redbook/dev/lookups/employees`	Active employees	`EMPLOYEE.PUBLIC.1287`
`GET /api/redbook/dev/lookups/accounts`	Job Shop accounts	`ACCOUNT.1287`
`GET /api/redbook/dev/lookups/cost-centers`	Cost centers	`COCE`
`GET /api/redbook/dev/lookups/audit-types`	Audit types	Static
`GET /api/redbook/dev/lookups/priorities`	Priority levels	Static
`GET /api/redbook/dev/lookups/departments`	Department codes	Static
`GET /api/redbook/dev/lookups/problem-types`	Problem types	Static
`GET /api/redbook/dev/lookups/root-causes`	Root causes	Static
`GET /api/redbook/dev/lookups/statuses`	RFC statuses	Static

Search Request Example:

{
  "projectNo": "95188",
  "status": "Open",
  "department": "ENG",
  "startDate": "2024-01-01",
  "endDate": "2024-12-31",
  "searchText": "design error",
  "skip": 0,
  "take": 50
}

RFC Response Example:

{
  "rfcNo": "12345",
  "projectNo": "95188",
  "status": "Open",
  "priority": "2",
  "problemDescription": "Drawing missing dimensions...",
  "enteredBy": "AMD",
  "enteredDate": "2024-01-15",
  "departmentStatuses": [
    { "department": "ENG", "status": "Complete" },
    { "department": "MFG", "status": "Pending" }
  ],
  "engChanges": [
    { "ecnNo": "ECN-2024-001", "description": "Update dimensions" }
  ]
}

Sales Orders & Quotes

Spare parts quote and sales order lifecycle endpoints. Used by the MCP server’s get_open_quotes, get_quote_detail, get_sales_orders, and get_sales_history tools.

Endpoint	Description	Auth
`GET /api/sales-order/dev/quotes?customer={}&contact={}&email={}`	Open quotes by customer, contact, or email	Anonymous
`GET /api/sales-order/dev/quotes/{quoteNo}`	Quote detail with line items and linked SOs	Anonymous
`GET /api/sales-order/dev/orders?customer={}`	Open sales orders (VB_OPENORDLIST)	Anonymous
`GET /api/sales-order/dev/history?customer={}&begin={}&end={}&part={}`	Invoiced sales history (VB_SODET.REV4)	Anonymous

Data Sources: OPEN.QUOTE.HEAD.1287 (31K+ quotes), OPEN.QUOTE.LINE.1287 (line items), CONTACT.1287 (email lookup), OPEN.ORD.HEAD.1287 (SO linkage), VB_OPENORDLIST, VB_SODET.REV4.

Obsolete Parts

Discover obsolete parts by search criteria or within project BOMs. Returns replacement part hints (parsed from secondary description), manufacturer details, and optionally cross-references. Designed for CS reps to quickly identify obsolete parts and their replacements.

Endpoint	Description	Auth
`GET /api/obsolete-parts/dev/search`	Search obsolete parts with filters (description, manufacturer, designCategory, gtCode, xref). Paginated.	Anonymous
`GET /api/obsolete-parts/dev/by-project/{jobNumber}`	Find obsolete parts in a project’s BOM with BOM location context	Anonymous
`POST /api/obsolete-parts/dev/by-projects`	Find obsolete parts across multiple projects (max 20), deduplicated	Anonymous
`GET /api/obsolete-parts/dev/{partNumber}/chain`	AI-powered replacement chain — follows replacements until active part found	Anonymous
`POST /api/obsolete-parts/dev/warm-cache`	Pre-parse PRODUCT.NOTES for a batch of parts to warm the AI cache	Anonymous

Search filters: description, manufacturer, designCategory, gtCode, xref, includeInactive, includeXrefs, page, pageSize

Key response fields: replacementPartHint (parsed from secondary description — e.g., “RPL AD3616901 REV 2” → “AD3616901 REV 2”), obsolescenceStatus (“O”=Obsolete, “I”=Inactive), manufacturer code/name, design category, GT code, cost, on-hand inventory.

Replacement Chain (/chain): Reads PRODUCT.NOTES free-text notes, uses Azure OpenAI (GPT 5.2) to extract replacement part numbers with context and confidence, then recursively follows the chain until an active part is found. Results are cached (7-day TTL, disk-persisted). First call ~5s (AI), subsequent calls ~200ms (cache). Example: part 054761 (Obsolete) → 054524 (Active, “for parts list”) + 053193 (Active, “for Installation & Operation”).

Data Sources: PRODUCT (INC/OBS flag at attr 8, MANF_CLASS at attr 20), VB_PARTINFO.REV2 (descriptions), ITEMMANF (manufacturer code, design category), MFG.1287 (manufacturer name), PRODXREF/REFXPROD (cross-references), PRODUCT.NOTES (free-text notes for AI parsing), BomService (project BOM explosion), Azure OpenAI (GPT 5.2 for notes parsing).

Spare Parts

Discover spare parts (consumables, recommended spares, general replacements) within a project’s BOM by reading BMF.1287 manual inclusion flags. Each spare part is enriched with MTBF (mean time between failures), obsolescence status, manufacturer details, and a suggested price (2.5x markup on last cost).

Endpoint	Description	Auth
`GET /api/spare-parts/dev/by-project/{jobNumber}`	Find all spare parts in a project’s BOM, categorized	Anonymous
`GET /api/spare-parts/dev/by-project/{jobNumber}/csv`	Export spare parts as CSV (opens in Excel)	Anonymous

Categories: C = Consumable, R = Recommended Spare, G = General Replacement (from BMF.1287 attr 2).

Key response fields: categoryCode/categoryName (C/R/G), recommendedQuantity (from BMF.1287 attr 3), mtbf (hours, from PRODUCT.1287 attr 8), obsolescenceStatus (“O”/“I”/""), manufacturerCode/manufacturerName, designCategory, lastCost, suggestedPrice (lastCost x 2.5), parentAssembly/parentDescription.

Data Sources: BMF.1287 (manual inclusion flag at attr 1, category at attr 2, recommended qty at attr 3), PRODUCT.1287 (MTBF at attr 8), PRODUCT (INC/OBS flag at attr 8), VB_PARTINFO.REV2 (descriptions, GT code, cost, on-hand), ITEMMANF (manufacturer code, design category), MFG.1287 (manufacturer name), BomService (project BOM explosion).

Timesheets

Full CRUD for the AFTEC timesheet lifecycle (week header + detail lines + optional machine time), plus read helpers for the web tester: timesheet list, posted history, dropdown reference data, and employee name search. Designed to replace the WPF timesheet app for employees who log in with individual accounts.

Auth model (dispatch /dev parity): every endpoint — production and /dev — is Azure AD authenticated and runs as the calling user in UniData via per-user credential passthrough. The only difference is the target server: the plain routes hit production AFTEC (MRP-PROD); the /dev twins hit the SANDBOX server (PS-MRPSANDBOX) via CreateSandboxConnection(upn). There are no anonymous timesheet endpoints. (This replaced the earlier anonymous service-account /dev model.)

Endpoint (+ `/dev` twin)	Description
`GET /api/timesheets/{empNo}/{weekDate}`	Load timesheet header + lines + machine time for one employee/week (unposted `TS.HEAD.1287`)
`POST /api/timesheets`	Save timesheet (header + all lines; replaces previous line set)
`DELETE /api/timesheets/{empNo}/{weekDate}`	Delete timesheet header and all lines
`GET /api/timesheets/list/{empNo}`	List an employee’s unposted timesheets (direct-read window over `TS.HEAD.1287`)
`GET /api/timesheets/history/{empNo}?start=&end=`	Posted labor history over a date range via `VB_TIMESHEETINQ.REV3` (posted days are not in `TS.HEAD.1287`)
`GET /api/timesheets/refdata`	Dropdown feeds: cost centers (COCE), work centers (WOCE), operations (OPERATION), misc types (TS.MISC.1287), and the `CWO.XREF.1287` cost-center→work-center→operation cascade
`GET /api/timesheets/employees?q=`	Search employees by name/number over `EMPLOYEE.PUBLIC.1287` (min 2 chars, 400 on blank, capped 100)

The web tester (PSI Portal → Tools → Timesheet, /tools/timesheet) drives all of these through the portal’s own MSAL instance and defaults its target to Sandbox. Its “Find my timesheet” button resolves the signed-in user’s AFTEC employee number from the Entra employeeId (Graph /me).

weekDate format: ISO 8601 date string yyyy-MM-dd (e.g. 2026-04-28). Invalid dates return HTTP 400.

Lock conflict: If another session holds the UPD.LOCK (F10) field on the TS.HEAD.1287 record, POST returns HTTP 409 with { errorMessage: "Record is locked by <value>" }. The API does not acquire locks — it checks on write and fails fast. This covers the migration window while the WPF app may still coexist.

Save request body (POST /api/timesheets):

{
  "header": {
    "empNo": "0042",
    "weekDate": "2026-04-28",
    "regularHours": 40.0,
    "sickHours": 0,
    "vacationHours": 0,
    "holidayHours": 0,
    "offDutyHours": 0,
    "miscHours": 0,
    "miscType": "",
    "miscNote": "",
    "offDutyType": "",
    "offDutyNote": "",
    "employeeType": "D",
    "costCenter": "125",
    "vacationApproval": "",
    "lines": [
      {
        "lineNumber": 1,
        "costCenter": "125",
        "operation": "010",
        "workOrderCenter": "",
        "workOrderNumber": "4900",
        "generalLedgerNumber": "",
        "drawingNumber": "",
        "laborHours": 8.0,
        "setUpHours": 0,
        "partialComplete": "",
        "quantityCompleted": 0,
        "routeLine": "",
        "machineTime": null
      }
    ]
  },
  "oldLineCount": 0,
  "deletedMachTimeId": null
}

oldLineCount — number of lines from the previous save. The service deletes lines 1..N before writing the new set. Pass 0 for a first-time save.

deletedMachTimeId — MACH.TIME.1287 ID (without 1! prefix) to delete before saving. Null if no machine time record is being removed.

Machine time: When a line contains a machineTime object, the service auto-assigns a new MACH.TIME.1287 ID, writes the record, and overwrites that line’s laborHours/setUpHours with machineTime.adjLabor/machineTime.adjSetup. The bare ID (without 1! prefix) is stored in TS.1287 F12. IDs are assigned by scanning the table for the current max on first use (process startup) and incrementing in-memory thereafter, protected by a SemaphoreSlim — verified correct under concurrent writes.

Dept 110 gate: drawingNumber is only written to TS.1287 F6 when header.costCenter == "110". It is blanked for all other departments regardless of what the client sends.

Tables accessed:

Table	Key Format	Operation
`TS.HEAD.1287`	`1!{EMPNO_4pad}!{CONVDATE}`	Read (lock check), Write (save), Delete
`TS.1287`	`1!{EMPNO}!{CONVDATE}!{LINE:D3}`	Delete (old lines), Write (new lines)
`MACH.TIME.1287`	`1!{AUTO_ID}`	Write (new machine time records); also scanned once at startup for max ID
`EMPLOYEE.PUBLIC.1287`	`1!{EMPNO_4pad}`	Field 6 cleared on each head save (fire-and-forget)

Employee record keys — two files, two formats (verified live 2026-08-06). Both take the employee number zero-padded to four, and they differ in whether a ! separates it from the company code:

File	Key	Example (emp 793 / 4200)
`EMPLOYEE.PUBLIC.1287`	`{CO}!{EMPNO_4pad}`	`1!0793`, `1!4200`
`EMPLOYEE` (payroll)	`{CO}{EMPNO_4pad}` — no delimiter	`10793`, `14200`

1!793 is a 404, not an alternate spelling. The concatenated payroll form follows that file’s own dictionary expression (TRANS(EMPLOYEE,CO:EMPNO,…)); it is where START.DATE and hourly/salary STATUS live, while department, shift, USERID, RPT.TO and SUBCONTRACT are on the public record. TITLE exists on the public record but is blank on every record checked — job titles are not usable from AFTEC.

Data (Generic Table Access)

Endpoint	Description	Auth
`GET /api/data/dev/{tableName}?limit={10}`	Sample records from any table	Anonymous
`GET /api/data/dev/{tableName}/{recordId}`	Single record by ID	Anonymous
`GET /api/data/dev/{tableName}/export?page={1}&pageSize={1000}`	Paginated records	Anonymous
`GET /api/data/dev/{tableName}/export-fields?fields={csv}&page={1}&pageSize={1000}`	Records with I-type field evaluation (LIST TOXML)	Anonymous
`POST /api/data/dev/{tableName}/batch`	Batch — read up to 5,000 records by ID	Anonymous

Batch Read (POST /api/data/dev/{tableName}/batch): Body is a JSON array of record ID strings. Opens one UniData session, opens the file handle once, and reads all records in a tight loop. Returns { records: { "id": DataRecord, ... }, notFoundIds, found, notFound, elapsedMs }. This is the foundational batch pattern — 900x faster than sequential reads on large workloads (proven in the notes-based obsolescence scan).

Query / Aggregation (Generic Read-Only)

Run a single read-only RetrieVe/UniQuery command server-side and get native XML (TOXML) or parsed JSON in one call — distinct-value lists, BREAK.ON/TOTAL group-by counts, and column exports without paging the whole file over REST. Backed by the cataloged VB_SYSTEMSTAT.REV1 (EXECUTE … CAPTURING).

Endpoint	Description	Auth / Target
`POST /api/query`	Run a read-only command (`LIST`/`SELECT`/`SSELECT`/`SORT`/`COUNT`)	Per-user · PROD
`POST /api/query/dev`	Same, against the sandbox	Per-user · SANDBOX (PS-MRPSANDBOX)
`GET /api/query/{table}/distinct?field={F}`	Distinct values of a field	Per-user · PROD
`GET /api/query/{table}/aggregate?breakOn={F}`	Group-by counts (`BREAK.ON … TOTAL COUNT`)	Per-user · PROD
(each convenience route has a `/api/query/dev/…` sandbox twin)		Per-user · SANDBOX

Auth model (identical to Dispatch): every route is authenticated per-user (Entra→UniData) — not anonymous. /dev runs the same per-user session against the sandbox server so a UI pilot can exercise it without touching production AFTEC. There is no service-account / X-On-Behalf-Of form.

Request: { "command": "LIST ITEMMANF BREAK.ON F9 TOTAL EVAL \"COUNT(F9)\" TOXML", "format": "json" } — format ∈ xml (native TOXML capture) | json (parsed rows) | raw. TOXML is auto-appended for LIST/SORT when format is xml/json.

Guardrails: read-only verb allow-list (the primary guard — writes/admin verbs like DELETE/CLEARFILE/ED/RUN are rejected), a destructive-keyword deny-list, ;/control-char rejection, table-name validation + optional Query:AllowedTables allow-list, server-side timeout + row/char caps, and per-caller audit of every command. Drove issue #55: a “distinct + counts for GT.DESIGN/GT.MANF/GT.CODE” export that took ~85 /export pages (~8 min) is now one request.

Optional upgrade — VB_WEBQUERY: a custom UniBasic sub (source in unidata/VB_WEBQUERY, runbook in docs/VB_WEBQUERY-DEPLOY.md) adds the SELECT … SAVING UNIQUE distinct-key path and a server-side row cap. It ships as source only; catalog it on UniData and set Query:Backend = VB_WEBQUERY to activate.

Manufacturers

Endpoint	Description	Auth
`GET /api/manufacturer/dev/by-part/{partNumber}`	OEM manufacturer for a part	Anonymous
`POST /api/manufacturer/dev/by-parts`	Batch — manufacturers for up to 200 parts	Anonymous
`GET /api/manufacturer/dev/by-code/{mfgCode}`	Manufacturer details by code	Anonymous
`GET /api/manufacturer/dev/list?page={1}&pageSize={100}`	All 2,400+ manufacturer codes (paginated)	Anonymous

Data Sources: ITEMMANF (part→mfg code, design category), MFG.1287 (mfg code→name/address), PVXREF (part→primary vendor), PRODUCT (INC/OBS flag, manufacturer class).

Product Classes (Product Families)

The product-family registry and membership, so consumers (e.g. psi-docgen) can group content by product class (“Air Dryer”, “Acoustical Enclosure”, …) the way the .NET Document Manager tree does. Replaces the legacy WCF path (remote subs VB_PRODUCTCLASSLIST.REV1 / VB_PRODUCTLIST.REV1) with direct UniData reads. Issue #73.

Endpoint	Description	Auth
`GET /api/product-classes`	Registry of product classes: `{ id (4-char code), name }`	Required
`GET /api/product-classes/{id}/products`	Curated LDS products in a class: `{ productNo, physNo, incObs, inactive }`	Required
`GET /api/products/lds`	Full product↔class membership in one bulk fetch: `{ productNo, physNo, classId, className, incObs, inactive }`	Required
`GET /api/product-classes/dev` · `/dev/{id}/products` · `GET /api/products/dev/lds`	Anonymous dev variants (service account)	Anonymous

Response envelopes: { totalClasses, classes: [...] }, { classId, className, totalProducts, products: [...] }, { totalProducts, products: [...] }. Inactive/obsolete rows are included — callers filter (incObs: ""=active, "I"=inactive, "O"=obsolete; inactive is a convenience bool). Example: class 0011 = AIR DRYER, with 39 products including the 247344/247345 product/physical pair.

Data Sources: PRODUCT.CLASS.1287 (id = key segment 2, name = attr 1 CLASS.NAME), PRODUCT.LDS.1287 (PHYS.NO attr 2, PROD.CLASS attr 4, I-type INACT.OBS = TRANS(PRODUCT,@ID,'INC/OBS')). Reads go through DataQueryService’s LIST … TOXML helpers so dictionary I-type fields are evaluated by UniData; the bulk read chunks LIST … WITH @ID by 100 in one session.

Service Tickets (SERVICE.1287)

Read-only service-ticket endpoints over SERVICE.1287, consumed by the Customer Service Manager web app (psi-service) — the web replacement for the legacy VB6 PTIServiceTicket. Phase 1 is read-only; create/edit, the contact editor, and the activity-log grid are tracked as later phases (GHE issues).

Endpoint	Description	Auth
`GET /api/service`	List service tickets (filterable)	Required
`GET /api/service/search`	Search tickets	Required
`GET /api/service/{ticketNumber}`	Full VB6-parity ticket detail (attribute map + lookups + phone)	Required
`GET /api/service/dev/…`	Dev twins of the three above	Anonymous (Development only)

Trusted delegation: the authenticated routes open the caller’s own per-user Entra→UniData session (per-user AFTEC audit). A trusted app holding the UniData.ActAsUser app role may act on behalf of a named user via the X-On-Behalf-Of: <upn> header (used by the MCP server / Ask-the-Fleet) — there is deliberately no service-account fallback on this route, so a service-ticket read is always attributed to a real person. Opened-by / assigned employee numbers are resolved to names.

Contacts (CONTACT.1287)

Full CRUD for contacts. /api/contacts/* is authenticated (per-user session); /api/contacts/dev/* is anonymous for local SPA development.

Endpoint	Description	Auth
`GET /api/contacts/account/{accountNo}`	Contacts for an account	Required
`GET /api/contacts/{contactNo}`	Single contact	Required
`POST /api/contacts`	Create contact	Required
`PUT /api/contacts/{contactNo}`	Update contact	Required
`DELETE /api/contacts/{contactNo}`	Delete contact	Required
`…/api/contacts/dev/…`	Anonymous twins of all five	Anonymous

Dispatch (WORK.WIPLINE.1287)

Dispatch-list read + shop-floor status writes over WORK.WIPLINE.1287, consumed by the psi-dispatch BFF (UniDataDispatchProvider). All routes use the caller’s per-user session. /{workCenter} reads production (UniData:Server); /dev/{workCenter} reads the sandbox (PS-MRPSANDBOX) so the pilot runs against sandbox data safely.

Endpoint	Description	Auth
`GET /api/dispatch/{workCenter}`	Dispatch list for a work center (production)	Required
`GET /api/dispatch/dev/{workCenter}`	Dispatch list (sandbox)	Required
`POST /api/dispatch/operator-status`	Set operator status on a line	Required
`POST /api/dispatch/notes`	Save a line note	Required
`POST /api/dispatch/dev/operator-status`, `POST /api/dispatch/dev/notes`	Sandbox write twins	Required

Inventory (ITEMQTY / INVHIST)

Current inventory status, transaction history, and 24-month trends. Data sources: ITEMQTY, INVHIST (linked list via INVXREF), PRODUCT, VB_GET.ITEMHIST, VB_INVINQ.2.

Endpoint	Description	Auth
`GET /api/inventory/{partNumber}/status`	Current inventory status for a part	Required
`GET /api/inventory/{partNumber}/transactions`	Transaction history for a part	Required
`GET /api/inventory/{partNumber}/monthly`	24-month inventory trend	Required
`GET /api/inventory/dev/{partNumber}/…`	Dev twins of all three	Anonymous (Development only)

Vendors & Purchasing

Vendor, open-PO, and AP-history lookups.

Endpoint	Description	Auth
`GET /api/vendor/by-part/{partNumber}`	Vendors for a part	Required
`GET /api/vendor/by-po/{poNumber}`	Open PO by PO number	Required
`GET /api/vendor/by-vendor/{vendorNumber}`	Open POs for a vendor	Required
`GET /api/vendor/open-pos`	All open purchased POs	Required
`GET /api/vendor/{vendorNumber}/history`	Vendor AP history	Required
`GET /api/vendor/{vendorNumber}/history/by-po/{poNumber}`	AP history for a specific PO	Required
`GET /api/vendor/dev/…`	Dev twins of all six	Anonymous (Development only)

Floor Stock & Cost Analysis

Work-order classification, cost attribution, and floor-stock WO discovery.

Endpoint	Description	Auth
`GET /api/floorstock/{partNumber}/cost-breakdown`	Cost breakdown for a part	Required
`GET /api/floorstock/{partNumber}/classified-transactions`	Classified transactions for a part	Required
`GET /api/floorstock/floor-stock-wos`	Discover floor-stock work orders	Required
`GET /api/floorstock/{job}/cost-attribution`	Job cost attribution	Required
`GET /api/floorstock/dev/…`	Dev twins of all four	Anonymous (Development only)

Hours

Endpoint	Description	Auth
`GET /api/hours/burn/{jobNumber}`	Monthly labor burn for a project	Required
`GET /api/hours/dev/burn/{jobNumber}`	Dev twin	Anonymous (Development only)

Lead Time

Serves the nightly comprehensive_dataset.csv (2,569 projects × 138 cols) built by the pipeline (see Data Brain) to the PSI Explorer Lead Time / Timeline UI.

Endpoint	Description	Auth
`GET /api/leadtime/…` (dev twins available)	Comprehensive lead-time analysis dataset	Required / Anonymous dev
`GET /api/leadtime/dev/scorecard`	Department scorecard (year × dept metrics)	Anonymous
`GET /api/leadtime/dev/clusters`	Per-project, per-dept work-cluster detail	Anonymous
`GET /api/leadtime/dev/engineers`	Engineer behavior profiles vs project outcomes	Anonymous
`GET /api/leadtime/dev/norms`	Baseline-era norm profiles for cluster metrics	Anonymous
`GET /api/leadtime/customer-segments` (dev twin available)	Customer segmentation — GovSegment, EndMarket, Channel, ValueTier, Recency, GeoRegion + history (one row per customer)	Required / Anonymous dev

Schedule

Department schedules and floor-space/bay assignments, sourced from Excel files + LDS Gantt.

Endpoint	Description	Auth
`GET /api/schedule/dev/departments`	All department schedules	Anonymous
`GET /api/schedule/dev/department/{department}`	Schedule for one department	Anonymous
`GET /api/schedule/dev/floor-space`	Floor space & bay assignments	Anonymous
`GET /api/schedule/dev/projects`	Condensed schedule context for all projects	Anonymous
`GET /api/schedule/dev/lds/{jobNumber}`	LDS Gantt data for a project	Anonymous

Subroutine Registry (admin)

Catalog of the AFTEC VB_* subroutine surface plus a generic executor. POST /call can run a registered subroutine, or an unregistered one when argCount is supplied to bypass the registry.

Endpoint	Description
`GET /api/admin/subroutine/registry`	List all registered subroutines
`GET /api/admin/subroutine/registry/status`	Registry stats by domain / pattern / status
`GET /api/admin/subroutine/registry/search`	Search subroutines
`GET /api/admin/subroutine/registry/domains` / `…/domain/{domain}`	Domains and their subroutines
`GET /api/admin/subroutine/registry/{name}`	Full metadata for one subroutine
`POST /api/admin/subroutine/call`	Execute a subroutine (registered or unregistered)

Activity Dashboard (admin)

Live “who is using the API and via which app” view. The HTML page (/admin/activity) signs the viewer in with MSAL.js and calls the JWT-protected JSON snapshot. Data comes from ActivityTrackerService (live in-process counters + startup-log backfill). Any authenticated PSI user may view (group gating is a later phase).

Endpoint	Description	Auth
`GET /api/admin/activity`	JSON snapshot of current callers	Required
`GET /api/admin/activity/config`	Dashboard client config (MSAL)	Anonymous
`GET /admin/activity`	HTML dashboard page	Anonymous (page shell; data call is authed)

System Status (admin)

Self-hosted recreation of the legacy VB6 ReadDict “System Status” screen: live UniData session list and concurrent-license utilization. Read-only in v1 (no session kill). The HTML page (/admin/system) signs the viewer in with MSAL.js and calls the JWT-protected JSON. Sessions are read by running listuser -i through the catalogued VB_UNIX.COMMAND.REV1 sub and parsing the output by whitespace + header name (robust to numeric usernames and the shared-license IP column); one listuser snapshot is cached ~5s and shared by both endpoints. Login initials (USRNAME) are resolved to full names via EMPLOYEE.PUBLIC.1287 USERID.

Endpoint	Description	Auth
`GET /api/admin/system/sessions`	Active UniData sessions (user, full name, type, TTY, client IP, login time)	Required — `UniDataSystemRead`
`GET /api/admin/system/license`	Seats used vs `UniData:LicensedMaxUsers` (80) + utilization %	Required — `UniDataSystemRead`
`GET /admin/system`	HTML dashboard page	Anonymous (page shell; data calls are authed)

Auth gate: UniDataSystemRead (view) is satisfied by the app roles UniData.SystemRead/UniData.SystemAdmin, the legacy UniData.Admin role, or membership in the AD-synced IT - RF group. Destructive session kill is deliberately reserved for UniDataSystemAdmin only — read never implies kill — and is a future v2.

Service account & self-sessions: these commands need an elevated (root-level) UniData login, so they run on UniData:ServiceAccount (the dedicated API account) regardless of caller — the caller is still gated by policy and audited by UPN. Because the dashboard’s own listuser connection appears in its own output, sessions whose USRNAME equals the service account are filtered out by default (SystemStatus:HideOwnServiceAccountSessions). Spec: docs/session-license-admin-spec.md.

Batch Operations Summary

The API provides high-performance batch endpoints for pipeline and analytics workloads. All batch endpoints reuse a single UniData session, amortizing connection overhead.

Endpoint	Max per call	Use case
`POST /api/bom/dev/jobs`	50 jobs	Pipeline BOM sync, Rockwell extraction
`POST /api/parts/dev/batch`	500 parts	Enrichment workflows
`POST /api/data/dev/{table}/batch`	5,000 records	Generic bulk reads (PRODUCT.NOTES, PRODUCT, etc.)
`POST /api/manufacturer/dev/by-parts`	200 parts	Manufacturer enrichment
`POST /api/obsolete-parts/dev/by-projects`	20 jobs	Fleet obsolescence scanning
`POST /api/obsolete-parts/dev/warm-cache`	Unlimited	Pre-parse replacement chains

Project Structure

PSI.UniData.API/
├── PSI.UniData.API.sln
├── lib/
│   └── U2.Data.Client.dll          # Bundled for build portability
├── src/PSI.UniData.API/
│   ├── PSI.UniData.API.csproj      # .NET 8 Web API
│   ├── Program.cs                   # Startup, Windows Service hosting
│   ├── appsettings.json            # Base configuration
│   ├── Services/
│   │   ├── UniDataConnectionFactory.cs  # U2 Toolkit connections
│   │   ├── ActiveDirectoryService.cs    # AD credential lookup
│   │   ├── EncryptionService.cs         # AES credential decryption
│   │   ├── SubroutineExecutor.cs        # Generic VB_* caller
│   │   ├── BomService.cs                # BOM explosion logic
│   │   ├── PartService.cs               # Part operations
│   │   ├── DictionaryService.cs         # Schema introspection
│   │   ├── DataQueryService.cs          # Generic table queries
│   │   ├── RedbookService.cs            # RFC CRUD operations
│   │   └── RedbookLookupService.cs      # RFC lookup data
│   ├── Endpoints/
│   │   ├── BomEndpoints.cs         # /api/bom/*
│   │   ├── PartEndpoints.cs        # /api/parts/*
│   │   ├── WorkOrderEndpoints.cs   # /api/wo/*
│   │   ├── IbmEndpoints.cs        # /api/ibm/*
│   │   ├── DictionaryEndpoints.cs  # /api/dictionary/*
│   │   ├── QueryEndpoints.cs       # /api/query/*
│   │   ├── RedbookEndpoints.cs     # /api/redbook/*
│   │   └── HealthEndpoints.cs      # /api/health
│   ├── Models/
│   │   ├── BomItem.cs
│   │   ├── PartInfo.cs
│   │   ├── WorkOrder.cs
│   │   ├── TableSchema.cs
│   │   ├── FieldInfo.cs
│   │   └── Redbook/
│   │       ├── RedbookEntry.cs
│   │       ├── RedbookRequests.cs
│   │       └── LookupItem.cs
│   └── Helpers/
│       └── UniDynArrayParser.cs    # UniData result parsing
├── pipeline/                         # Nightly data build pipeline (Python)
│   ├── build_comprehensive_dataset.py  # Main: 13 sources → dataset + detail CSVs
│   ├── extract_lds_gantt.py            # LDS Excel → planned Gantt schedules
│   ├── requirements.txt                # Python deps (openpyxl)
│   └── data/
│       ├── project_customers.csv       # Static input (3,085 projects, committed)
│       └── otd_dataset.csv             # Static input (2,569 projects, committed)
├── deploy/
│   └── appsettings.Production.json # Production config template
└── .github/workflows/
    ├── deploy-ps-proxy.yml         # Auto-deployment (.NET API)
    └── nightly-data-build.yml      # Nightly dataset build (Python pipeline)

Configuration

appsettings.json (Base)

{
  "UniData": {
    "Server": "MRP-PROD",
    "Database": "/home/pro3",
    "Service": "udcs",
    "Pooling": false,
    "ConnectTimeout": 30,
    "ServiceAccount": ""
  },
  "AzureAd": {
    "Instance": "https://login.microsoftonline.com/",
    "TenantId": "YOUR_TENANT_ID",
    "ClientId": "YOUR_CLIENT_ID",
    "Audience": "api://psi-unidata-api"
  },
  "Cors": {
    "AllowedOrigins": [
      "http://localhost:5173",
      "https://bom-explorer-web.azurewebsites.net",
      "https://redbook.progressivesurface.com"
    ]
  }
}

appsettings.Production.json

{
  "UniData": {
    "ServiceAccount": "API"
  },
  "Kestrel": {
    "Endpoints": {
      "Https": {
        "Url": "https://0.0.0.0:443",
        "Certificate": {
          "Path": "C:/Services/PSI.UniData.API/certificate/wildcard.pfx",
          "Password": "cert-password"
        }
      },
      "Http": {
        "Url": "http://0.0.0.0:80"
      }
    }
  }
}

Initial Server Setup

Prerequisites

Windows Server 2019+ with .NET 8.0 ASP.NET Core Hosting Bundle
.NET 8.0 SDK (for building via GitHub Actions)
Network access to MRP-PROD (UniData)
Active Directory domain membership
Wildcard SSL certificate (*.progressivesurface.com)

Service Installation

# Create and configure service
sc.exe create PSI.UniData.API binPath= "C:\Services\PSI.UniData.API\PSI.UniData.API.exe" start= auto DisplayName= "PSI UniData API Gateway"
sc.exe config PSI.UniData.API obj= "AD\ServiceAccount" password= "<YOUR_PASSWORD>"
 
# Configure firewall
New-NetFirewallRule -DisplayName "PSI UniData API HTTPS" -Direction Inbound -LocalPort 443 -Protocol TCP -Action Allow
New-NetFirewallRule -DisplayName "PSI UniData API HTTP" -Direction Inbound -LocalPort 80 -Protocol TCP -Action Allow
 
# Start service
Start-Service PSI.UniData.API

GitHub Actions Runner Setup

# Download from: https://progressivesurface.ghe.com/ProgressiveSurface/PSI.UniData.API/settings/actions/runners
mkdir C:\actions-runner; cd C:\actions-runner
# Extract and configure:
.\config.cmd --url https://progressivesurface.ghe.com/ProgressiveSurface/PSI.UniData.API --token YOUR_TOKEN
# Install as service with admin account:
.\config.cmd --runasservice --windowslogonaccount AD\AdminAccount --windowslogonpassword <YOUR_PASSWORD>

Runner requirements:

Write access to C:\Services\PSI.UniData.API
Permission to stop/start the PSI.UniData.API service
.NET 8 SDK installed
Python 3.12 (installed automatically by actions/setup-python@v5 for nightly build)
Network access to \\ad.ptihome.com\DFS\DATA\..., \\ad.ptihome.com\DFS\LDS\PROJECT, \\ad.ptihome.com\DFS\Schedule\...

Logs

API logs are available on the network share:

\\ps-proxy\logs\

Log File	Description
`api-YYYYMMDD.log`	Daily API request/response log (rolled daily, 30-day retention)
`startup-YYYYMMDD.log`	Service startup diagnostics

Logs use Serilog structured logging. On-disk path: C:\Services\PSI.UniData.API\logs\

Request audit line (2026-06-25)

Every request — including failures — emits one completion line attributable to a caller. The request logging middleware sits above authentication, so 401/403/404/500 all log here; a rejected token is captured separately by the JWT failure/challenge handlers (caller stays anonymous on the completion line because auth never populated the identity).

HTTP {Method} {Path} responded {Status} in {Elapsed} ms (caller {Caller} via {AuthScheme} from {ClientIp}; query {QueryKeys}; roles {Roles}; trace {TraceId})

Caller — UPN for user tokens (ClaimTypes.Upn → preferred_username → ClaimTypes.Email), or the app id (azp/appid) for app/service tokens, else anonymous.
QueryKeys — query parameter names only (e.g. [customer,email]), never values — audit without PII leakage. (Note: ASP.NET’s own Hosting.Diagnostics “Request starting/finished” lines still echo the raw query string.)
Levels — 5xx → [ERR], 4xx → [WRN], success → [INF], /api/health* → [DBG] (kept out of the audit stream).
ClientIp — Kestrel is the TLS edge on PS-PROXY (no reverse proxy), so this is the real client IP; UseForwardedHeaders is deliberately not enabled.

Quick tail of today’s log (PowerShell):

Get-Content '\\ps-proxy\logs\api-20260218.log' -Tail 50

Search for errors:

Select-String -Path '\\ps-proxy\logs\api-*.log' -Pattern '\[ERR\]|\[WRN\]' | Select-Object -Last 20

Find what a specific caller did (audit):

Select-String -Path '\\ps-proxy\logs\api-*.log' -Pattern 'responded' | Where-Object { $_ -match 'caller (jdoe@progressivesurface\.com|<app-guid>)' }

Troubleshooting

Service won’t start

Check Windows Event Viewer for errors
Check startup log: \\ps-proxy\logs\startup-YYYYMMDD.log
Verify .NET 8 ASP.NET Core Hosting Bundle is installed
Check ports aren’t in use: netstat -ano | findstr :443
Verify certificate path/password in appsettings.Production.json

Deployment fails with “Access denied”

Ensure runner service account has write access to C:\Services\PSI.UniData.API
Grant permissions: icacls "C:\Services\PSI.UniData.API" /grant "AD\RunnerAccount":F /T

UniData connection hangs (data requests timeout)

Symptom: /api/health returns 200 but /api/health/unidata and all data endpoints hang
Log signature: Repeated Creating UniData connection to MRP-PROD//home/pro3 for user AMD with no follow-up response

Resolution: Restart the PSI.UniData.API service on PS-PROXY:

Restart-Service "PSI.UniData.API"
# Verify it's back:
Invoke-RestMethod https://api.progressivesurface.com/api/health
Invoke-RestMethod https://api.progressivesurface.com/api/health/unidata

Note: This is an API-side issue — do not restart anything on the UniData server (MRP-PROD)

UniData connection fails

Verify network access to MRP-PROD from PS-PROXY
Check UniData:ServiceAccount has valid credentials in AD
Test with /api/health/unidata endpoint

Running Locally

cd C:\GIT\PSI.UniData.API\src\PSI.UniData.API
dotnet run --urls="http://localhost:5000"

Test endpoints:

curl http://localhost:5000/api/health
curl http://localhost:5000/api/bom/dev/job/95188
curl http://localhost:5000/api/redbook/dev/rfc/12345

Redbook Web - RFC management frontend
PSI Explorer Web - React frontend for BOM data
CLI Export Tool - CLI using same U2 Toolkit patterns
U2 Toolkit Migration - Migration guide from UODOTNET
PSI.All Architecture - UniData subroutine documentation

PSI Knowledge Base

Explorer

unidata-api