How the server works

The AI Assist MCP server is a thin translation layer built around security. MCP tool calls come in, parameterized read-only SQL goes out to the PeopleTools tables, and structured JSON comes back. The server keeps no copy of your metadata beyond a short-lived cache, so every answer reflects your definitions as they are when you ask.

The server sits between your IDE and the PeopleSoft database. It turns tool calls into read-only SQL and never sees the rest of your conversation.

The layers

Transport

The transport layer receives MCP messages. It uses stdio when your IDE launches the server locally, and Streamable HTTP when the server runs as a shared service. Over HTTP, it also validates each request’s Origin header and authenticates the caller.

Protocol

The protocol layer implements the MCP methods the server supports: server/discover, tools/list, and tools/call, plus the methods for resources and prompts. It checks that each request carries the required _meta fields and a protocol version the server supports. It also accepts the older initialize handshake from clients that haven’t moved to version 2026-07-28. Tools are always listed in the same order, so clients and LLMs can cache the list.

Tool handlers

Each tool has its own handler. A handler validates the tool’s arguments, runs one or more queries, and shapes the rows into the tool’s output: structured JSON, plus the same JSON as text for older clients.

Guardrails

Every call passes through the same checks, whichever tool runs:

Input validation. Definition names must follow PeopleSoft’s naming rules, such as the 15-character limit on record names, before they’re used in a query.
Table allow-list. Each tool can read only the PeopleTools tables it needs. run_select can read only the application tables you allow, and you can turn it off entirely.
Row and time limits. Every query has a row cap and a timeout. When a result reaches a cap, the result says so, so the LLM knows the answer is partial.
Output limits. Long PeopleCode programs and SQL statements come back in chunks rather than all at once, so a single program can’t flood the LLM’s context window.
Masking. run_select masks the columns you mark as sensitive, such as national IDs, birth dates, and bank account numbers.

Query layer

The query layer holds every SQL statement the server can run, as templates with bind variables. Values are never concatenated into SQL. PeopleSoft runs on several database platforms with different SQL dialects, so the query layer keeps a version of each template per platform:

Oracle Database
Microsoft SQL Server

SELECT FIELDNAME
  FROM PSRECFIELD
 WHERE RECNAME = :record
   AND BITAND(USEEDIT, 1) > 0
 ORDER BY FIELDNUM
 FETCH FIRST 200 ROWS ONLY

SELECT TOP (200) FIELDNAME
  FROM PSRECFIELD
 WHERE RECNAME = @record
   AND (USEEDIT & 1) > 0
 ORDER BY FIELDNUM

Connection pool

The server connects with a dedicated database account that has read access to the allowed tables and nothing else. The pool stays small, because the server’s queries are short and never hold locks.

Audit log

Every call is recorded: when it ran, who made it, which client they used, the tool and its arguments, how many rows came back, how long it took, and whether it succeeded. Refused requests are recorded too. Audit log describes the format. When the host sends OpenTelemetry trace context in _meta, the server continues the same trace, so you can follow a slow answer from the IDE all the way down to the SQL.

Anatomy of a tool call

Here’s what happens when the LLM calls describe_record for the JOB record:

The request arrives. The client sends tools/call with the arguments {"record": "job"} and the required _meta fields.
The protocol layer checks it. The protocol version is one the server supports, the tool exists, and the arguments match the tool’s input schema.
The handler normalizes the input. job becomes JOB, and the name is checked against PeopleSoft’s naming rules.
The query layer runs the queries, each with JOB as a bind variable. It reads the record header from PSRECDEFN, the fields from PSRECFIELD joined to PSDBFIELD, the indexes from PSINDEXDEFN and PSKEYDEFN, and the pages that use the record from PSPNLFIELD. Subrecords are expanded recursively, up to a fixed depth.
Guardrails apply their limits. Every query runs under a row cap and a timeout, and any capped result is flagged.
The handler shapes the result. Codes become names: FIELDTYPE 0 becomes Character, the USEEDIT key bits become the record’s list of keys, and RECTYPE 0 becomes SQL Table, with the table name PS_JOB.
The call is logged, and the result goes back to the client.

This is the main query in step 4, in its Oracle Database form:

SELECT F.FIELDNUM, F.FIELDNAME, F.SUBRECORD,
       D.FIELDTYPE, D.LENGTH, D.DECIMALPOS,
       BITAND(F.USEEDIT, 1)   AS KEY_FLAG,
       BITAND(F.USEEDIT, 256) AS REQUIRED_FLAG,
       F.EDITTABLE
  FROM PSRECFIELD F
  LEFT JOIN PSDBFIELD D
    ON D.FIELDNAME = F.FIELDNAME
 WHERE F.RECNAME = :record
 ORDER BY F.FIELDNUM

Rows with SUBRECORD set to Y have no match in PSDBFIELD, because their FIELDNAME holds a subrecord’s name. The handler expands each one by running the same query for the subrecord. EDITTABLE holds the field’s prompt table, if it has one.

Design principles

Read-only, enforced by the database. The server’s database account can’t write, so no bug and no prompt can change your system.
The database is the source of truth. There are no exports to keep in sync, and answers reflect your definitions as they are now.
Small, focused tools. Eleven tools that each do one thing are easier for an LLM to choose between, and easier to secure, than a single “run any SQL” tool.
Structured results. Each tool returns JSON with a declared output schema, so clients can validate it and the LLM can rely on its field names.
Errors the LLM can act on. A misspelled name returns the closest matches instead of an empty result.
Platform-neutral. The tools and their output are the same on every database platform that PeopleSoft supports.

Deployment options

The same server runs in two ways:

	Local (stdio)	Shared (Streamable HTTP)
Where it runs	Each developer’s machine	A server close to the PeopleSoft database
Started by	The IDE, as a subprocess	Your operations team, as a service
Database credentials	In the developer’s local configuration	Held by the service only
Identity in the audit log	Self-reported, from `AIPS_USER`	Each developer’s sign-in, through OAuth 2.1
Audit log	On each developer’s machine	One central log
Best for	Individual developers and evaluations	Teams and regulated environments

To set up either one, start with Set up AI Assist.