Skip to content

JTL Service

Purpose

The JTL Service is a specialized integration component designed to bridge the gap between the JTL-FFN (Fulfillment Network) API and internal operations managed via Google Sheets. Its primary function is Inbound Verification: ensuring that stock sent to fulfillment centers is correctly booked and reflected in the ERP system.

High-Level Architecture

The service operates as a Docker container (service-jtl) exposing an API on port 8004.

  • Upstream: Triggered by Cron Jobs (internal scheduler) or manual API calls.
  • Downstream:
  • JTL FFN API: The source of truth for stock levels and product data.
  • Google Sheets: The user interface for tracking inbound shipments.
  • Postgres: Stores credentials, configuration, and job schedules.

Database Schema

JTLCredentials (jtl_credentials)

Stores authentication details for the JTL Fulfillment Network API.

  • Key Fields: client_id, client_secret, refresh_token, bearer_token.
  • Logic: Tokens are short-lived. The service automatically refreshes the bearer_token using the refresh_token when it expires.

SheetSettings (sheet_settings)

Maps the "Inbound Control" Google Sheet columns to internal data structures.

  • Key Fields: spreadsheet_id, sheet_id.
  • Column Mappings:
  • asin_sku_column: The identifier used to look up the product (Merchant SKU).
  • unit_column: The expected quantity sent.
  • send_status_column: Where the service writes the result (e.g., "MATCH", "MISMATCH").

CronJobDB (cron_jobs)

Persists the schedule for background tasks.

  • Key Fields: cron_expression (e.g., 0 8 * * * for 8:00 AM daily), is_active.
  • Purpose: Ensures the schedule survives container restarts.

Application Structure

  • app.py: Entry point. Initializes FastAPI and the Rate Limiter.
  • JTL/:
  • jtl_manager.py: The core API client. Handles pagination, token refreshing, and SKU mapping.
  • jtl_credentials.py: Helper class for managing OAuth tokens.
  • routers/:
  • jtl_router.py: Manages the Cron Scheduler and exposes endpoints for manual triggers.
  • sheets_router.py: Handles reading/writing to the Google Sheet.

Key Workflows

1. Inbound Stock Verification (The "Checker")

This process verifies if the stock sent to the warehouse has actually arrived.

graph TD Cron[APScheduler] -->|Trigger| Checker[Inbound Checker] Checker -->|Read Sheet| GSheets[Google Sheets] GSheets -->|Return Rows| Checker Checker -->|Extract SKUs| Logic[Logic Layer] Logic -->|Get JFSKU Map| JTL[JTL API] JTL -->|Return Products| Logic Logic -->|Get Stock Level| JTL Logic -->|Compare Expected vs Actual| Result{Match?} Result -->|Yes| Update[Mark SUCCESS] Result -->|No| Update[Mark FAIL] Update -->|Write Back| GSheets

2. JFSKU Mapping

JTL uses an internal ID (jfsku) which differs from the user-facing merchantSku.

  • Step 1: Fetch all products from /api/v1/merchant/products.
  • Step 2: Build a dictionary: { "MerchantSKU": "JFSKU" }.
  • Step 3: Use jfsku to query specific stock endpoints.

Technical Details

APScheduler Integration

The service uses AsyncIOScheduler to run background tasks.

  • Timezone: Configured for Europe/Berlin.
  • Persistence: The schedule is loaded from the cron_jobs table on startup.
  • Concurrency: Jobs are configured with max_instances=1 and coalesce=True to prevent overlapping executions if a run takes longer than expected.

Rate Limiting

A custom RateLimiter class protects the API from abuse.

  • Limit: 100 requests per 60 seconds per IP.
  • Implementation: In-memory sliding window counter.

Token Management

The Jtl_Manager implements a robust "check-and-refresh" mechanism. Before every API call, it checks is_token_expired(). If true, it calls refresh_access_token() to get a new bearer token, ensuring long-running jobs don't fail due to auth timeouts.