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

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
- Restarts the service
- Runs health check

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)
Pipeline / unattended	App-role token with `UniData.ServiceRead`	`UniData:ServiceAccount` — a dedicated AD user (`svc-unidata-api`) with UniData creds in extensionAttribute2

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.

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 svc-unidata-api (configured in UniData:ServiceAccount)
Pipeline traffic is attributed to the dedicated service user, not any human

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

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.

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.

Dictionary/Schema (New)

Endpoint	Description	Auth
`GET /api/dictionary/tables`	List all UniData tables	Required
`GET /api/dictionary/tables/{tableName}`	Get table schema	Required
`GET /api/dictionary/search?q={query}`	Search field names	Required
`GET /api/query/{tableName}`	Query table data	Required

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). Designed to replace the WPF timesheet app for employees who log in with individual accounts. Authenticated endpoints require Azure AD. The anonymous GET dev endpoint is available in all environments (read-only, service-account-backed). Anonymous write endpoints (dev POST/DELETE) are only registered when ASPNETCORE_ENVIRONMENT=Development.

Endpoint	Description	Auth
`GET /api/timesheets/{empNo}/{weekDate}`	Load timesheet header + lines + machine time for one employee/week	Required
`POST /api/timesheets`	Save timesheet (header + all lines; replaces previous line set)	Required
`DELETE /api/timesheets/{empNo}/{weekDate}`	Delete timesheet header and all lines	Required
`GET /api/timesheets/dev/{empNo}/{weekDate}`	Load timesheet (service account)	Anonymous — all environments
`POST /api/timesheets/dev`	Save timesheet (service account)	Anonymous (Development env only)
`DELETE /api/timesheets/dev/{empNo}/{weekDate}`	Delete timesheet (service account)	Anonymous (Development env only)

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}`	Field 6 cleared on each head save (fire-and-forget)

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).

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).

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": "SERVICEACCOUNT"
  },
  "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