Skip to main content
Glama
peoplesoft

peoplesoft

by Jcurrin27

PeopleSoft MCP Server

A minimal Model Context Protocol (MCP) server, built as a worked example of how an MCP server is constructed. It exposes a local SQLite contracts/billing database (PROJECT, CA_DETAIL, CA_BILL_PLAN) through a small set of tools, ranging from generic schema introspection to purpose-built semantic queries.

Features

  • Schema introspection tools — discover tables and columns without any prior knowledge of the schema

  • Semantic tools — purpose-built queries for the contracts/billing domain (projects, contract lines, bill plans)

  • Direct SQL tool — an escape hatch for arbitrary queries once the schema is known

  • Direct database access via Python's stdlib sqlite3, wrapped in asyncio.to_thread for async compatibility

Related MCP server: local-mysql

Quick Start

Prerequisites

  • Python 3.11+

  • uv package manager (recommended)

Installation

# Clone the repository
git clone <repo-url>
cd peoplesoft-mcp

# Install dependencies
uv sync

# Create finstg.db and load static data
uv run create_db.py

Configuration

  1. Copy the example environment file:

cp .env.example .env
  1. Edit .env if you want to point at a different SQLite file (defaults to finstg.db in the repo root):

SQLITE_DB_PATH=finstg.db
  1. Copy .cursor/mcp.json.example to .cursor/mcp.json and update the path to your installation.

Running the Server

uv run peoplesoft_server.py

Cursor IDE Integration

The MCP config (.cursor/mcp.json, copied from .cursor/mcp.json.example) should look like:

{
  "mcpServers": {
    "peoplesoft": {
      "command": "uv",
      "args": [
        "--directory",
        "/path/to/mcp_ps/",
        "run",
        "peoplesoft_server.py"
      ]
    }
  }
}

Available Tools

Schema Introspection (2 tools)

Tool

Description

list_tables

List tables in the local database, optionally filtered by name

describe_table

Get table structure (columns, types, primary keys)

Contracts & Billing Module (4 tools)

Tool

Description

list_projects

List projects, filterable by business unit/status/type

get_project_contracts

Get a project plus its contract lines and bill plans

list_contracts

List contract detail lines with their bill plan info

get_bill_plan

Get bill plan details for a specific contract line

Billing Analysis (2 tools)

Tool

Description

check_project_billing_setup

Check each project's contract line(s), bill plan, bill plan type, and effective statuses, to spot billing setup problems

cost_reimbursable_billable_transactions

Summarize undistributed cost-reimbursable billable transactions (sums, counts, date range) per project

Direct Query (1 tool)

Tool

Description

query_peoplesoft_db

Execute custom SQL queries against the local database

Project Structure

peoplesoft-mcp/
├── peoplesoft_server.py    # Main MCP server entry point
├── db.py                   # SQLite connection management
├── create_db.py            # Creates finstg.db and loads data/*.csv
├── db_structure.txt        # Schema definition source for create_db.py
├── data/                   # Static CSV data loaded into finstg.db
│   ├── project.csv
│   ├── ca_detail.csv
│   ├── ca_bill_plan.csv
│   └── proj_resource.csv
├── finstg.db               # Local SQLite database (generated)
├── tools/                  # Semantic tool modules
│   ├── introspection.py    # Schema discovery tools
│   ├── contracts.py        # Contracts/billing tools
│   └── billing.py          # Billing setup & cost-reimbursable analysis tools
├── tests/                  # Test suite
│   └── test_contracts.py
└── pyproject.toml          # Project configuration

Running Tests

uv run pytest tests/ -v -s

Example Queries

  • "List all active projects for business unit UCD"

  • "What contract lines and bill plans belong to project 25B1111?"

  • "What's the bill plan status for contract FAKE4 line 2?"

  • "What tables and columns are in finstg.db?"

Development

Adding New Tools

  1. Create a new module in tools/ or add to an existing module

  2. Define async functions that use db.execute_query()

  3. Add a register_tools(mcp) function

  4. Import and register in peoplesoft_server.py

License

MIT

Changelog

v0.3.0 (2026-07-15)

  • Removed the legacy PeopleSoft HCM Oracle tool modules (hr.py, payroll.py, benefits.py, performance.py, peopletools.py), their docs, their gated/skipped tests, and the Cursor agent/skill configs built around them

  • Removed the 4 MCP resources that served the now-deleted Oracle-schema documentation

  • Repurposed the repo as a standalone worked example of MCP server construction (schema introspection + semantic tools + direct-SQL escape hatch) over a local SQLite database

v0.2.x (2026-03-02 – earlier)

  • Replaced Oracle (oracledb) backend with a local SQLite database (finstg.db)

  • Rewrote schema introspection to use SQLite's own metadata (sqlite_master, PRAGMA table_info)

  • Added tools/contracts.py with semantic tools for the local PROJECT/CA_DETAIL/CA_BILL_PLAN schema

Available Tools

9 tools
check_project_billing_setupB
    Check how projects are set up for billing: each project's contract
    line(s), bill plan, bill plan type, and the effective statuses of
    the project, contract line, and bill plan.

    Use this to spot setup problems, e.g. an active project tied to an
    inactive bill plan, or a contract line with no bill plan at all.

    :param business_unit: Optional business unit code (e.g., 'UCD', 'UCB')
    :param contract_num: Optional contract number
    :param project_id: Optional project ID
    :return: List of project/contract/bill-plan rows with effective statuses
    

Inputs: project_id, contract_num, business_unit.

cost_reimbursable_billable_transactionsA
    Summarize undistributed cost-reimbursable billable transactions
    (PROJ_RESOURCE rows with ANALYSIS_TYPE = 'BIL' and
    BI_DISTRIB_STATUS = 'N', i.e. billable but not yet distributed).

    For each project, returns the summed BIL amount and the earliest/
    latest accounting date in the matched transactions.

    :param business_unit: Optional business unit code (e.g., 'UCD', 'UCB')
    :param project_id: Optional project ID
    :param begin_dt: Optional accounting date lower bound, inclusive (YYYY-MM-DD)
    :param end_dt: Optional accounting date upper bound, inclusive (YYYY-MM-DD)
    :return: Per-project sums of billable amounts with min/max accounting dates
    

Inputs: end_dt, begin_dt, project_id, business_unit.

describe_tableA
    Get the structure of a table including all columns, their types,
    and whether they're part of the primary key.

    Use this tool to understand what fields are available in a table
    before writing queries against it.

    :param table_name: The table name (e.g., 'PROJECT', 'CA_DETAIL', 'CA_BILL_PLAN')
    :return: List of columns with their properties
    

Inputs: table_name (required).

get_bill_planC
    Get the bill plan details for a specific contract line.

    :param business_unit: Business unit code (e.g., 'UCD', 'UCB')
    :param contract_num: Contract number
    :param contract_line_num: Contract line number
    :return: Bill plan details for the contract line
    

Inputs: contract_num (required), business_unit (required), contract_line_num (required).

get_project_contractsA
    Get a project along with all contract lines and bill plans tied to it.

    This is the primary tool for understanding a project's full billing
    picture: the project record, its contract detail lines (CA_DETAIL),
    and each line's bill plan (CA_BILL_PLAN).

    :param business_unit: Business unit code (e.g., 'UCD', 'UCB')
    :param project_id: The project ID
    :return: Project details with related contract lines and bill plans
    

Inputs: project_id (required), business_unit (required).

list_contractsB
    List contract detail lines, optionally filtered by business unit,
    contract number, or effective status. Each line includes its
    associated bill plan type/status.

    :param business_unit: Optional business unit code (e.g., 'UCD', 'UCB')
    :param contract_num: Optional contract number
    :param eff_status: Optional contract line effective status ('A' = Active, 'I' = Inactive)
    :return: List of matching contract lines with bill plan info
    

Inputs: eff_status, contract_num, business_unit.

list_projectsB
    List projects, optionally filtered by business unit, effective status,
    or project type.

    :param business_unit: Optional business unit code (e.g., 'UCD', 'UCB')
    :param eff_status: Optional effective status ('A' = Active, 'I' = Inactive)
    :param project_type: Optional project type code
    :return: List of matching projects
    

Inputs: eff_status, project_type, business_unit.

list_tablesA
    List tables available in the local database, optionally filtered by
    a name pattern.

    Use this tool FIRST to discover what tables exist before writing queries.

    :param pattern: Optional substring to filter table names (case-insensitive)
    :return: List of matching tables
    

Inputs: pattern.

query_peoplesoft_dbB
Query the local SQLite database directly.

When working with this database, follow these guidelines in order:

1. FIRST, discover what tables exist:
   - Use: list_tables()
   - Currently available: PROJECT, CA_DETAIL, CA_BILL_PLAN

2. SECOND, check the structure of a table before querying it:
   - Use: describe_table('PROJECT')
   - This shows all columns, their types, and which are primary keys

3. FINALLY, write your query using the discovered structure
   - Use '?' placeholders for parameters (not Oracle-style ':1')
   - Example: SELECT * FROM PROJECT WHERE BUSINESS_UNIT = ?

AVAILABLE RESOURCES:
- Use list_tables() to discover tables
- Use describe_table() to get table structure
- Use list_projects(), get_project_contracts(), list_contracts(), get_bill_plan()
  for semantic access to the contracts/billing schema

:param sql_query: SQL query to execute (e.g., SELECT * FROM PROJECT WHERE BUSINESS_UNIT = ?)
:param parameters: List of query parameters (optional)
:return: A dictionary containing query results or an error message

Inputs: sql_query (required), parameters.

Tool Schema Changelog

Recent tool additions, removals, and schema changes observed during successful MCP inspections.

Loading tool schema changelog…

    TDQS

    B3.4/5.0

    Scored across 9 tools

    Disambiguation4/5

    Most tools have clearly distinct purposes: describe_table/list_tables are schema introspection, query_peoplesoft_db is raw SQL, and the rest are semantic domain queries. However, check_project_billing_setup overlaps somewhat with get_project_contracts and list_contracts in coverage of the project/contract/bill-plan relationship, and the schema-inspection tools (describe_table, list_tables) somewhat duplicate what the semantic tools already encapsulate.

    Naming Consistency4/5

    Naming follows a mostly consistent verb_noun pattern: describe_table, list_tables, list_projects, list_contracts, get_project_contracts, get_bill_plan. Minor deviations exist such as query_peoplesoft_db, check_project_billing_setup, and the verbose cost_reimbursable_billable_transactions, which break the tight verb_noun convention but are still readable and descriptive.

    Tool Count4/5

    9 tools is within the well-scoped 3-15 range. The count is reasonable for a PeopleSoft contracts/billing domain, though there is some redundancy—the low-level SQL/schema tools (describe_table, list_tables, query_peoplesoft_db) plus the four semantic getters make the surface feel slightly overlapping rather than lean.

    Completeness3/5

    The read-side surface is well covered: projects, contract lines, bill plans, billing setup diagnostics, and billable transaction summaries are all represented. However, the server is entirely read-only—there are no create/update/delete operations for any resource, which is acceptable for a query/data-analysis server but means lifecycle coverage is intentionally partial rather than complete.

    Maintenance

    ActivityInactive
    ResponsivenessNo recent community issues

    Related MCP Connectors

    Related MCP Servers

    • F
      license
      A
      quality
      D
      maintenance
      Provides read-only access to a local MySQL database, enabling SQL queries, database and table listings, and table schema descriptions.
      4
      -
    • A
      license
      A
      quality
      D
      maintenance
      Provides direct SQL access to a locally hosted Reactome database, enabling schema discovery, guarded read-only queries, and ergonomic helpers over the full relational schema.
      9
      MIT