SAP ERP Materials API

Build a fast, cost-effective materials inventory API from SAP ERP data. This example shows end-to-end configuration with caching, authentication, and MCP integration.

Overview

Use Case: Provide real-time material inventory data to web applications, mobile apps, and AI agents without overloading SAP.

Challenge:

Direct SAP queries: 10-30 seconds
High RFC load on production SAP
Expensive ABAP custom development

Solution:

flAPI with 1-hour cache refresh
Millisecond API responses
Zero SAP load during API serving

Project Structure

sap-materials-api/
├── flapi.yaml                 # Main configuration
├── sqls/
│   ├── materials.yaml         # Endpoint config
│   ├── materials.sql          # API query template
│   └── materials_cache.sql    # Cache materialization
└── .env                       # Environment variables (not committed)

Step 1: Configuration

# flapi.yaml
# ═══════════════════════════════════════════════════════════════
# SAP Materials Inventory API
# ═══════════════════════════════════════════════════════════════
project-name: sap-materials-api
project-description: Fast materials inventory API from SAP ERP

template:
  path: './sqls'
  environment-whitelist:
    - '^SAP_.*'
    - '^JWT_.*'

duckdb:
  threads: 4
  max_memory: 4GB

# DuckLake-backed cache for incremental refresh and snapshots
ducklake:
  enabled: true
  alias: cache
  metadata-path: ./data/cache.ducklake
  data-path: ./data/cache

connections:
  sap-prod:
    init: |
      INSTALL 'erpl' FROM 'http://get.erpl.io';
      LOAD 'erpl';

      CREATE OR REPLACE PERSISTENT SECRET sap_prod (
        TYPE sap_rfc,
        ASHOST '{{env.SAP_HOST}}',
        SYSNR  '{{env.SAP_SYSNR}}',
        CLIENT '{{env.SAP_CLIENT}}',
        USER   '{{env.SAP_USER}}',
        PASSWD '{{env.SAP_PASSWORD}}',
        LANG   'EN'
      );

enforce-https:
  enabled: true

Step 2: Cache Template

-- sqls/materials_cache.sql
-- ═══════════════════════════════════════════════════════════════
-- Materialize SAP material master and inventory data.
-- flAPI executes this SELECT on the configured schedule and writes the
-- result into {{cache.catalog}}.{{cache.schema}}.{{cache.table}}.
-- ═══════════════════════════════════════════════════════════════

WITH
  mara AS (SELECT * FROM sap_read_table('MARA')),
  makt AS (SELECT * FROM sap_read_table('MAKT') WHERE SPRAS = 'E'),
  marc AS (SELECT * FROM sap_read_table('MARC')),
  mard AS (SELECT * FROM sap_read_table('MARD')),
  mbew AS (SELECT * FROM sap_read_table('MBEW'))
SELECT
  -- Material master (MARA)
  m.MATNR as material_number,
  m.MTART as material_type,
  m.MATKL as material_group,
  m.MEINS as base_unit_of_measure,
  m.BRGEW as gross_weight,
  m.NTGEW as net_weight,
  m.GEWEI as weight_unit,

  -- Material description (MAKT)
  t.MAKTX as material_description,
  t.MAKTG as material_description_short,

  -- Plant data (MARC)
  p.WERKS as plant,
  p.DISPO as mrp_controller,
  p.DISMM as mrp_type,
  p.PLIFZ as planned_delivery_time,

  -- Storage location data (MARD)
  s.LGORT as storage_location,
  s.LABST as unrestricted_stock,
  s.INSME as quality_inspection_stock,
  s.SPEME as blocked_stock,
  s.UMLME as stock_in_transfer,

  -- Valuation (MBEW)
  v.STPRS as standard_price,
  v.PEINH as price_unit,
  v.BKLAS as valuation_class,
  v.SALK3 as total_stock_value,

  -- Calculated fields
  (s.LABST + s.INSME) as available_stock,
  CASE
    WHEN s.LABST = 0 THEN 'OUT_OF_STOCK'
    WHEN s.LABST < 10 THEN 'LOW_STOCK'
    ELSE 'IN_STOCK'
  END as stock_status,

  CURRENT_TIMESTAMP as cache_updated_at

FROM mara m
LEFT JOIN makt t ON m.MATNR = t.MATNR
LEFT JOIN marc p ON m.MATNR = p.MATNR
LEFT JOIN mard s ON m.MATNR = s.MATNR AND p.WERKS = s.WERKS
LEFT JOIN mbew v ON m.MATNR = v.MATNR AND p.WERKS = v.BWKEY

WHERE m.LVORM IS NULL
  AND (m.LOEKZ IS NULL OR m.LOEKZ = '')
  AND (s.LABST > 0 OR p.MMSTA = 'A')

ORDER BY m.MATNR, p.WERKS, s.LGORT

Step 3: Endpoint Configuration

# sqls/materials.yaml
# ═══════════════════════════════════════════════════════════════
# Materials Inventory API Endpoint
# GET /materials/?plant=1000&material_type=FERT
# ═══════════════════════════════════════════════════════════════

url-path: /materials/
method: GET

description: |
  Get material inventory data from SAP ERP with millisecond response times.
  Data is refreshed hourly from SAP production system.

# ───────────────────────────────────────────────────────────────
# Authentication (per-endpoint, single auth type)
# ───────────────────────────────────────────────────────────────
auth:
  enabled: true
  type: bearer
  jwt-secret: '{{env.JWT_SECRET}}'
  jwt-issuer: my-auth-server

# ───────────────────────────────────────────────────────────────
# Rate Limiting (SAP RFC calls are expensive — protect production)
# ───────────────────────────────────────────────────────────────
rate-limit:
  enabled: true
  max: 30                    # 30 requests
  interval: 60               # per 60-second window per client

# ───────────────────────────────────────────────────────────────
# Caching Configuration (Essential for SAP)
# ───────────────────────────────────────────────────────────────
cache:
  enabled: true
  table: sap_materials_inventory
  schema: analytics
  schedule: 60m              # Refresh every hour
  template-file: materials_cache.sql

# ───────────────────────────────────────────────────────────────
# Request Parameters
# ───────────────────────────────────────────────────────────────
request:
  - field-name: plant
    field-in: query
    description: Plant code (e.g., 1000, 2000)
    required: false
    validators:
      - type: string
        regex: '^\d{4}$'

  - field-name: material_type
    field-in: query
    description: Material type (FERT=Finished goods, HALB=Semi-finished, ROH=Raw materials)
    required: false
    validators:
      - type: enum
        allowedValues: [FERT, HALB, ROH, HIBE]

  - field-name: material_group
    field-in: query
    description: Material group code
    required: false
    validators:
      - type: string
        max: 50

  - field-name: stock_status
    field-in: query
    description: Stock availability status
    required: false
    validators:
      - type: enum
        allowedValues: [IN_STOCK, LOW_STOCK, OUT_OF_STOCK]

  - field-name: search
    field-in: query
    description: Search in material number or description
    required: false
    validators:
      - type: string
        min: 3
        max: 100

# ───────────────────────────────────────────────────────────────
# SQL Template
# ───────────────────────────────────────────────────────────────
template-source: materials.sql
connection:
  - sap-prod

# Built-in pagination provides 'limit' and 'offset' parameters.
with-pagination: true

# ───────────────────────────────────────────────────────────────
# MCP Tool Configuration (for AI agents)
# ───────────────────────────────────────────────────────────────
mcp-tool:
  name: get_sap_materials
  description: |
    Query SAP ERP materials inventory data. Returns material master information,
    stock levels, pricing, and availability status.

    Use this tool when you need to:
    - Check material availability and stock levels
    - Get material descriptions and specifications
    - Find materials by plant or material type
    - Analyze inventory across locations
  result-mime-type: application/json

Step 4: API Query Template

-- sqls/materials.sql
-- ═══════════════════════════════════════════════════════════════
-- Fast queries against materialized cache
-- ═══════════════════════════════════════════════════════════════

SELECT 
  material_number,
  material_description,
  material_type,
  material_group,
  plant,
  storage_location,
  unrestricted_stock,
  quality_inspection_stock,
  blocked_stock,
  available_stock,
  stock_status,
  base_unit_of_measure,
  standard_price,
  price_unit,
  total_stock_value,
  mrp_controller,
  planned_delivery_time,
  cache_updated_at
FROM {{cache.catalog}}.{{cache.schema}}.{{cache.table}}
WHERE 1=1

{{#params.plant}}
  AND plant = '{{{params.plant}}}'
{{/params.plant}}

{{#params.material_type}}
  AND material_type = '{{{params.material_type}}}'
{{/params.material_type}}

{{#params.material_group}}
  AND material_group = '{{{params.material_group}}}'
{{/params.material_group}}

{{#params.stock_status}}
  AND stock_status = '{{{params.stock_status}}}'
{{/params.stock_status}}

{{#params.search}}
  AND (
    material_number LIKE '%{{{params.search}}}%'
    OR LOWER(material_description) LIKE LOWER('%{{{params.search}}}%')
  )
{{/params.search}}

ORDER BY
  plant,
  material_number

When with-pagination: true is set on the endpoint, flAPI automatically applies LIMIT / OFFSET from the ?limit=…&offset=… query parameters and returns pagination metadata, so the template does not need to handle it.

Step 5: Environment Configuration

# .env (NEVER commit to git)
# ═══════════════════════════════════════════════════════════════
# SAP ERP Connection
# ═══════════════════════════════════════════════════════════════
export SAP_HOST="sap-prod.company.com"
export SAP_SYSNR="00"
export SAP_CLIENT="100"
export SAP_USER="RFC_API_USER"
export SAP_PASSWORD="secure-sap-password"

# ═══════════════════════════════════════════════════════════════
# Security
# ═══════════════════════════════════════════════════════════════
export JWT_SECRET="your-super-secret-jwt-key-min-32-chars"

# ═══════════════════════════════════════════════════════════════
# Load environment
# ═══════════════════════════════════════════════════════════════
source .env

Step 6: Run flAPI

# Start the server with the flAPI binary, pointing at your config file
./flapi -c flapi.yaml

# Output:
# [INFO] Loading configuration...
# [INFO] Connecting to SAP ERP...
# [INFO] Initializing cache: sap_materials_inventory
# [INFO] Cache refresh scheduled: every 60 minutes
# [INFO] Server running on http://0.0.0.0:8080

Step 7: Test the API

Basic Query

curl -H "Authorization: Bearer $TOKEN" \
  "http://localhost:8080/materials/?limit=5"

Response:

{
  "data": [
    {
      "material_number": "000000000010000001",
      "material_description": "Centrifugal Pump Model X100",
      "material_type": "FERT",
      "material_group": "PUMPS",
      "plant": "1000",
      "storage_location": "0001",
      "unrestricted_stock": 45.0,
      "quality_inspection_stock": 0.0,
      "blocked_stock": 2.0,
      "available_stock": 45.0,
      "stock_status": "IN_STOCK",
      "base_unit_of_measure": "EA",
      "standard_price": 1250.00,
      "price_unit": 1,
      "total_stock_value": 56250.00,
      "mrp_controller": "001",
      "planned_delivery_time": 14,
      "cache_updated_at": "2024-01-15T10:00:00Z"
    }
  ],
  "metadata": {
    "total": 2847,
    "limit": 5,
    "offset": 0
  }
}

Filter by Plant

curl -H "Authorization: Bearer $TOKEN" \
  "http://localhost:8080/materials/?plant=1000&material_type=FERT"

Search Materials

curl -H "Authorization: Bearer $TOKEN" \
  "http://localhost:8080/materials/?search=pump&limit=10"

Check Low Stock

curl -H "Authorization: Bearer $TOKEN" \
  "http://localhost:8080/materials/?stock_status=LOW_STOCK"

Performance Comparison

Method	Query Time	Concurrent Users	SAP Load
Direct SAP RFC	10-30 seconds	5-10	High
flAPI Cached	1-5ms	10,000+	Minimal (hourly refresh)

Cost Savings:

SAP RFC load reduced by 99%
Response time improved by 10,000x
Can serve millions of API calls with single hourly SAP query

AI Agent Integration (MCP)

// Claude/GPT can now query SAP inventory
const response = await mcp.call("get_sap_materials", {
  plant: "1000",
  stock_status: "LOW_STOCK"
});

console.log(`Found ${response.data.length} low stock materials`);

Expose the materials schema as an MCP resource

The mcp-tool block above lets an agent call the materials endpoint. Agents also benefit from a static resource that describes the result shape up front, so they can plan queries without trial-and-error. Drop a second YAML file alongside materials.yaml to publish one:

sqls/materials-mcp-resource.yaml
mcp-resource:
  name: materials_schema
  description: |
    JSON schema definition for the SAP materials inventory endpoint —
    field names, SQL types, and human-readable descriptions. Use this
    resource before calling the get_sap_materials tool so you know what
    columns and filters are available.
  mime-type: application/json

template-source: materials-schema.sql
connection:
  - sap-prod

# Resources are read by tooling, not humans — light rate limit is plenty.
rate-limit:
  enabled: true
  max: 10
  interval: 60

The companion SQL template returns a JSON document describing the columns the materials endpoint serves:

sqls/materials-schema.sql
SELECT json_object(
  'endpoint', '/materials/',
  'description', 'SAP material master + inventory + valuation',
  'columns', json_array(
    json_object('name', 'material_number', 'type', 'VARCHAR', 'description', 'SAP material number (MARA-MATNR)'),
    json_object('name', 'material_description', 'type', 'VARCHAR', 'description', 'Long description (MAKT-MAKTX, English)'),
    json_object('name', 'material_type', 'type', 'VARCHAR', 'description', 'FERT=finished, HALB=semi-finished, ROH=raw, HIBE=operating supplies'),
    json_object('name', 'material_group', 'type', 'VARCHAR', 'description', 'Material group (MARA-MATKL)'),
    json_object('name', 'plant', 'type', 'VARCHAR', 'description', '4-digit plant code (MARC-WERKS)'),
    json_object('name', 'storage_location', 'type', 'VARCHAR', 'description', 'Storage location within plant (MARD-LGORT)'),
    json_object('name', 'unrestricted_stock', 'type', 'DOUBLE', 'description', 'Unrestricted-use stock quantity (MARD-LABST)'),
    json_object('name', 'available_stock', 'type', 'DOUBLE', 'description', 'unrestricted + quality-inspection stock'),
    json_object('name', 'stock_status', 'type', 'VARCHAR', 'description', 'IN_STOCK, LOW_STOCK, or OUT_OF_STOCK'),
    json_object('name', 'standard_price', 'type', 'DOUBLE', 'description', 'Standard price per price_unit (MBEW-STPRS)'),
    json_object('name', 'cache_updated_at', 'type', 'TIMESTAMP', 'description', 'When the cached snapshot was materialized')
  ),
  'filters', json_array('plant', 'material_type', 'material_group', 'stock_status', 'search')
) as schema_definition;

An agent connected over MCP now sees both a get_sap_materials tool and a materials_schema resource. Most coding agents fetch resources up front, so calls to the tool arrive already correctly typed.

Next Steps

SAP ERP Connection Guide: Detailed SAP ERP setup
SAP BW Connection: Connect to SAP BW/4HANA
Caching Strategy: Understand optimization techniques
Caching Setup: Configure caching for SAP
Authentication: Secure production deployment
MCP Protocol: How tools, resources, and prompts fit together
MCP Resources recipe: More patterns for exposing schemas and reference data to agents
BigQuery Example: Cloud warehouse alternative
Deployment: Deploy to production

Overview​

Project Structure​

Step 1: Configuration​

Step 2: Cache Template​

Step 3: Endpoint Configuration​

Step 4: API Query Template​

Step 5: Environment Configuration​

Step 6: Run flAPI​

Step 7: Test the API​

Basic Query​

Filter by Plant​

Search Materials​

Check Low Stock​

Performance Comparison​

AI Agent Integration (MCP)​

Expose the materials schema as an MCP resource​

Next Steps​