teradata-gcfr-mcp-server

An MCP (Model Context Protocol) server that exposes Teradata GCFR (Global Control Framework Repository) operational reporting as natural-language tools consumable by Claude Desktop, Claude Code, VS Code Copilot Chat, and any other MCP-compatible client. Connect Claude to your Teradata environment and ask questions like "show me failed processes since yesterday" or "what are the slowest streams this week" — without writing SQL.

---

Prerequisites

| Requirement | Notes | | --- | --- | | Python 3.11+ | Earlier versions not supported | | uv | Package manager and runner — pip install uv | | teradatasql Python driver | Installed automatically by uv sync | | Network access to Teradata | Direct TCP to port 1025, or via ODBC gateway |

---

How it works

Server architecture:

Entry point (server.py) — Initializes the connection pool, registers all tools, applies profile filtering, and starts the MCP server.
Connection pool (db.py) — Thread-safe pooling of Teradata connections with configurable size, overflow, and timeout. Queries have automatic reconnect-once on transient failures.
Tool modules (tools/*.py) — 7 categories of MCP tools:

Streams (3 tools): Live stream status and business date tracking
Processes (3 tools): Process execution history and current status
Loads (3 tools): Data ingestion statistics and registration audit
Transforms (5 tools): Transform statistics, performance ranking, and trend analysis
Errors (3 tools): Error log, execution trace, and failed process diagnostics
SLA (2 tools): Service-level agreement compliance reporting
Lineage (2 tools): Data lineage tracing and health checks

Custom tools (tool_loader.py) — YAML-defined SQL tools loaded from CONFIG_DIR at startup, allowing site-specific reporting without Python code.

Query execution:

All SQL uses parameterized queries (? placeholders) to prevent injection.
Schema/table names come from settings.py constants, never user input.
Per-query timeout enforced via GCFR_QUERY_TIMEOUT (default 120s).
Queries are capped at GCFR_MAX_ROWS (default 500 rows).
Results returned as structured error dicts on failure — no exceptions.

Transport modes:

| Transport | Best for | Visibility | | --- | --- | --- | | stdio | Claude Desktop, local REPL | Silent (stdout = MCP protocol) | | sse | Development, debugging, VS Code | Log output on stderr | | streamable-http | Web dashboards, REST clients | HTTP on configured port/path |

---

Recent improvements

Query timeout enforcement (2025-04-02)

GCFR_QUERY_TIMEOUT is now wired to teradatasql.connect() at connection initialization
Queries that exceed the timeout are interrupted at the database level (no more runaway queries)
Timeout applies to all tool queries uniformly

HTTP mount path support (2025-04-02)

MCP_PATH setting is now properly passed to FastMCP's mcp.run() call
HTTP transports now mount at the configured path (e.g., /mcp/ → http://127.0.0.1:8001/mcp/)
Enables better URL hierarchy and multi-server configurations

Connection pool robustness

Automatic reconnect-once on transient failures (stale connections, temporary network issues)
QueryBand set on all connections for Teradata workload-management attribution
Graceful handling of connection exhaustion with timeout-aware blocking

---

Quick start

Local development (recommended)

git clone <repo-url>
cd teradata-gcfr-mcp-server
uv sync
cp .env.example .env          # Edit with your Teradata credentials
MCP_TRANSPORT=sse uv run teradata-gcfr-mcp-server  # Or use stdio for Claude Desktop

The MCP_TRANSPORT defaults to stdio (for Claude Desktop), but sse is useful for debugging with visible log output on stderr.

---

Development install

git clone <repo-url>
cd teradata-gcfr-mcp-server

# Install all dependencies including dev extras
uv sync

# Run linting and type checks before making changes
uv run ruff check src/
uv run mypy src/

# Run unit tests (no Teradata connection required)
uv run pytest tests/unit/ -v

# Run the server locally in development mode
MCP_TRANSPORT=sse uv run teradata-gcfr-mcp-server

Copy .env.example to .env and update with your Teradata credentials. The server will use environment variables automatically.

Verification gate (run before committing):

All three checks must pass with zero errors:

uv run ruff check src/        # Linting
uv run mypy src/              # Type checking (strict)
uv run pytest tests/unit/ -v  # Unit tests (76 tests)

---

Configuration reference

All settings are read from environment variables or a .env file in the working directory.

| Variable | Type | Default | Description | | --- | --- | --- | --- | | DATABASE_URI | str | _(required)_ | teradata://user:pass@host:1025/db | | LOGMECH | str | TD2 | Auth mechanism: TD2, LDAP, TDNEGO, KRB5 | | TD_POOL_SIZE | int | 5 | Persistent connections in the pool | | TD_MAX_OVERFLOW | int | 10 | Extra connections allowed under burst load | | TD_POOL_TIMEOUT | int | 30 | Seconds to wait for a free connection | | GCFR_VIEW_DB | str | GDEV1V_GCFR | Base view layer — registration/metadata tools | | GCFR_OPR_DB | str | GDEV1V_OPR | Operational reporting views (GCFR_RV_) | | GCFR_UTLFW_DB | str | GDEV1V_UTLFW | BKEY/BMAP surrogate-key views | | GCFR_TABLE_DB | str | GDEV1T_GCFR | Physical tables — health-check only | | GCFR_MAX_ROWS | int | 500 | Maximum rows any single tool may return | | GCFR_QUERY_TIMEOUT | int | 120 | Per-query timeout in seconds (enforced at connection init) | | MCP_TRANSPORT | str | stdio | stdio \| streamable-http \| sse | | MCP_HOST | str | 127.0.0.1 | _(read-only)_ Bind host for HTTP/SSE — not configurable at runtime | | MCP_PORT | int | 8001 | _(read-only)_ Bind port for HTTP/SSE — not configurable at runtime | | MCP_PATH | str | /mcp/ | URL path prefix for HTTP transports | | PROFILE | str | all | Active tool profile (see Profiles below) | | LOGGING_LEVEL | str | WARNING | Python logging level | | CONFIG_DIR | str | . | Directory scanned for _tools.yml custom tools |

Notes on transport configuration:

MCP_HOST and MCP_PORT are FastMCP internal settings and cannot be changed at runtime. The server binds to these values but the MCP framework controls the actual binding. Modify them only if you understand the implications.
MCP_PATH is properly wired and controls the HTTP mount point (e.g., /mcp/ → http://host:port/mcp/).
GCFR_QUERY_TIMEOUT is now wired to teradatasql.connect(), ensuring all queries respect the configured timeout.

---

Profiles

Profiles limit which tools are exposed to the MCP client. Set via the PROFILE env var or the --profile CLI flag.

`all` (default)

Every tool is available.

`ops`

Focused on live operational monitoring:

gcfr_stream_status, gcfr_current_stream_status, gcfr_stream_business_date, gcfr_current_process_status, gcfr_process_history, gcfr_process_status_summary, gcfr_failed_processes, gcfr_error_log, gcfr_execution_log, gcfr_load_status, gcfr_health_check

`performance`

Focused on SLA and throughput analysis:

gcfr_sla_process_report, gcfr_sla_stream_report, gcfr_top_slowest_processes, gcfr_top_slowest_streams, gcfr_data_trend_loads, gcfr_data_trend_transforms, gcfr_stream_status, gcfr_health_check

`lineage`

Focused on data lineage and registration audit:

gcfr_data_lineage, gcfr_dataset_registered, gcfr_load_stats, gcfr_transform_stats, gcfr_health_check

---

Available MCP tools (22 total)

All tools are read-only queries against GCFR operational views. None modify data.

Streams (3 tools)

gcfr_stream_status — History and completion state for a date range
gcfr_current_stream_status — Real-time stream status (running now)
gcfr_stream_business_date — Current, previous, next business date for a stream

Processes (3 tools)

gcfr_process_status_summary — All processes for a business date (completed vs incomplete)
gcfr_current_process_status — Real-time process status
gcfr_process_history — Execution history with timing and outcomes

Loads (3 tools)

gcfr_load_status — Which staging tables loaded successfully and row counts
gcfr_load_stats — Detailed load statistics (rejections, ET/UV violations, errors)
gcfr_dataset_registered — Source datasets registered for processing

Transforms (5 tools)

gcfr_transform_stats — Rows inserted/updated/deleted per process
gcfr_top_slowest_processes — Top N slowest processes by elapsed time
gcfr_top_slowest_streams — Top N slowest streams by elapsed time
gcfr_data_trend_loads — Daily load volume trends
gcfr_data_trend_transforms — Daily transform volume trends

Errors (3 tools)

gcfr_failed_processes — Failed process instances with error details
gcfr_error_log — Raw error log entries for root cause investigation
gcfr_execution_log — Step-level execution trace (debug level only)

SLA (2 tools)

gcfr_sla_process_report — Expected vs actual process timing and SLA compliance
gcfr_sla_stream_report — Expected vs actual stream duration and SLA compliance

Lineage (2 tools)

gcfr_data_lineage — Trace target table back to source objects
gcfr_health_check — Verify GCFR databases are reachable

---

Database naming

GCFR uses two distinct tiers of databases:

| Tier | Name pattern | Purpose | | --- | --- | --- | | View layer (V) | GDEV1V_GCFR, GDEV1V_OPR, GDEV1V_UTLFW | All GCFR_RV_* operational views — use these | | Table layer (T) | GDEV1T_GCFR | Physical base tables — referenced only by the health-check |

Never reference GDEV1_GCFR (no T or V suffix) — that database does not exist. All tool queries target the GDEV1V_* view layer. Only gcfr_health_check touches GDEV1T_GCFR to verify the physical tables are reachable.

---

Required Teradata permissions

The server account needs SELECT privilege on the three view-layer databases:

GRANT SELECT ON GDEV1V_GCFR  TO <your_user>;
GRANT SELECT ON GDEV1V_OPR   TO <your_user>;
GRANT SELECT ON GDEV1V_UTLFW TO <your_user>;
-- For health-check (optional):
GRANT SELECT ON GDEV1T_GCFR  TO <your_user>;

No INSERT, UPDATE, DELETE, or DDL privileges are required — the server is read-only.

---

Claude Desktop configuration

Add the following to your claude_desktop_config.json (replace credential values):

{
  "mcpServers": {
    "teradata-gcfr": {
      "command": "uvx",
      "args": ["teradata-gcfr-mcp-server"],
      "env": {
        "DATABASE_URI": "teradata://myuser:mypass@gdev1-host:1025/GDEV1V_GCFR",
        "LOGMECH": "TD2",
        "GCFR_VIEW_DB": "GDEV1V_GCFR",
        "GCFR_OPR_DB": "GDEV1V_OPR",
        "GCFR_UTLFW_DB": "GDEV1V_UTLFW",
        "GCFR_TABLE_DB": "GDEV1T_GCFR",
        "MCP_TRANSPORT": "stdio",
        "PROFILE": "all"
      }
    }
  }
}

Config file location:

macOS: ~/Library/Application Support/Claude/claude_desktop_config.json
Windows: %APPDATA%\Claude\claude_desktop_config.json

---

VS Code / Copilot Chat configuration

Add to your VS Code settings.json or workspace .vscode/mcp.json. The SSE transport is recommended for VS Code:

{
  "mcp": {
    "servers": {
      "teradata-gcfr": {
        "type": "sse",
        "url": "http://127.0.0.1:8001/sse",
        "env": {}
      }
    }
  }
}

Then run the server with:

MCP_TRANSPORT=sse uv run teradata-gcfr-mcp-server

---

Docker quick start

# Build image
docker build -t gcfr-mcp .

# Run with an .env file
docker run --rm --env-file .env -p 8001:8001 gcfr-mcp

# Or use docker-compose (starts with streamable-http transport)
docker compose up

The docker-compose.yml mounts ./gcfr_custom_tools.yml into the container at /app/gcfr_custom_tools.yml (read-only). Create this file to add site-specific tools; if it does not exist, the container starts without custom tools.

---

Custom tools (YAML)

Add read-only SQL tools without writing Python by placing a *_tools.yml file in CONFIG_DIR (defaults to ., the current working directory).

Example — gcfr_custom_tools.yml:

tools:
  - name: gcfr_my_site_report
    description: "Latest 20 stream records for this site"
    sql: >
      SELECT TOP 20
        Stream_Key, Stream_Name, Business_Date, Stream_Status
      FROM {gcfr_opr_db}.GCFR_RV_Stream
      ORDER BY Business_Date DESC

Supported SQL placeholders:

| Placeholder | Expands to | Purpose | | --- | --- | --- | | {gcfr_opr_db} | GCFR_OPR_DB setting | Operational reporting views (GCFR_RV_*) | | {gcfr_view_db} | GCFR_VIEW_DB setting | Base registration/metadata views | | {gcfr_utlfw_db} | GCFR_UTLFW_DB setting | BKEY/BMAP surrogate-key reference data |

Custom tools are zero-argument — they execute their SQL directly with a GCFR_MAX_ROWS row limit applied automatically. Tool names must follow the gcfr_ prefix convention so that profile filtering and naming conventions are consistent.

---

Sample questions

The following questions work out-of-the-box with Claude once the server is connected:

"Show me all failed processes since yesterday."
"What is the current status of stream 42?"
"Which streams have not completed today's business date?"
"Give me the top 10 slowest processes this week."
"Show the SLA report for process LOAD_CUSTOMER_DAILY from 2024-01-01 to 2024-01-31."
"What datasets are registered in GCFR?"
"List the lineage for target table CUSTOMER_DIM."
"Show me transform statistics for the last 7 days."
"Are the GCFR databases reachable? Run a health check."
"What errors occurred in the execution log today?"

---

Linting and type checking

# Lint
uv run ruff check src/

# Auto-fix lint issues
uv run ruff check --fix src/

# Type checking (strict)
uv run mypy src/

---

Running tests

Unit tests (no Teradata connection required)

uv run pytest tests/unit/ -v

All database calls are mocked — unit tests run offline.

Integration tests (requires GDEV1 network access)

uv run pytest tests/integration/ -v

Integration tests are not yet implemented. Contributions welcome — see CLAUDE.md for the pending work list.

Skipping slow tests

uv run pytest tests/unit/ -v -m "not slow"

---

Architecture and design patterns

See CLAUDE.md in the repository for comprehensive developer documentation including:

Async/sync split — Why MCP tool wrappers are async but DB logic is sync
Dynamic date defaults — How to avoid frozen dates in function signatures
Parameterised SQL only — Security model for user input vs schema names
Reconnect-once pattern — Transient failure handling in the connection pool
TOP clause injection — Why and how row limits are applied transparently
Testing patterns — How to mock database calls without hitting Teradata
Custom tool loading — YAML-driven tool registration and placeholder substitution
Profile filtering — How role-based access control works at startup

---

Design validation

This server was validated against the upstream Teradata/teradata-mcp-server for architectural best practices and lessons learned. Key differences:

| Aspect | This server | Upstream | | --- | --- | --- | | Connection layer | Direct teradatasql | SQLAlchemy + teradatasqlalchemy | | DB abstraction | Hand-rolled connection pool | SQLAlchemy QueuePool | | Tool registration | Module-based + YAML | Python (auto-discovery) + YAML + progressive disclosure | | Async strategy | asyncio.to_thread in wrappers | Sync blocking in handlers (thread pool implicit) | | Type checking | mypy --strict | Gradual mypy (strict disabled) | | Testing | 3 per handler (normal/empty/error) | Integration tests against live DB | | Error handling | Structured error dicts | Some handlers may raise | | Database timeout | ✓ Enforced at connection | Optional SQLAlchemy pool timeout | | HTTP path mounting | ✓ Wired to mcp.run() | Configuration-only |

Both implementations are production-ready and differ mainly in scope (GCFR-specific vs general Teradata) and deployment strategy (lightweight vs feature-rich).

---

Troubleshooting

| Symptom | Likely cause | Fix | | --- | --- | --- | | OSError: Teradata connection failed | Wrong host/port in DATABASE_URI | Verify host resolves and port 1025 is reachable; check firewall | | [Error 3524] No access or permission denied | Missing SELECT grant | Run the GRANT SELECT ON ... statements in the Required Teradata permissions section | | Tool returns {"error": "...", "sql": "..."} | Query execution failed or timeout | Check GCFR_QUERY_TIMEOUT setting; look at the sql field for the failing query; check Teradata error message | | Query hangs or times out | GCFR_QUERY_TIMEOUT too low or network latency | Increase GCFR_QUERY_TIMEOUT in .env; default is 120s | | Claude Desktop shows no tools | Server not running or wrong transport | Confirm MCP_TRANSPORT=stdio; restart Claude Desktop after server starts | | SSE transport shows Connection refused | Server not running or wrong host/port | Verify server is running with MCP_TRANSPORT=sse; check MCP_HOST and MCP_PORT in .env | | INTERVAL columns appear as "0:01:23" string | Expected — Teradata INTERVAL serialized to string | The HH:MM:SS format is correct; this is standard JSON serialization of intervals | | Custom tools not appearing | Wrong CONFIG_DIR or file not named _tools.yml | Set CONFIG_DIR to the directory containing your _tools.yml file; restart server | | Profile filter not working | Tool name doesn't match pattern | Tool names must start with gcfr_ to be subject to profile filtering | | Server starts but no output | MCP_TRANSPORT=stdio silences logs | Use MCP_TRANSPORT=sse or MCP_TRANSPORT=streamable-http to see startup logs on stderr |

teradata-gcfr-mcp-server