SFTP Routing and Scheduling

Overview

This document explains how the EWX Integration Platform routes and schedules SFTP-based inbound messages. Unlike SOAP integrations where routing is determined by credentials used in the request, SFTP routing is configured through schedule configurations that explicitly map connections to namespaces.

Key Concepts

Term	Description
ewx_namespace	A unique identifier for an organization, e.g., enrx_org_004
Connection	An SFTP server configuration with host, credentials, and folder paths
Schedule	Configuration that maps a connection to one or more namespaces
Message Type	Identifier extracted from filename (e.g., "DRE" from "DRE_123.xml")
Source Folder	The folder on the SFTP server where files are retrieved from
Archive Folder	The folder where processed files are moved to

SFTP Routing Mechanism

1. Schedule Configuration

SFTP routing is defined in the schedules/schedule_config.json file stored in the configuration bucket. Each schedule explicitly defines which namespaces should process files from a specific SFTP connection. Namespace entries are objects ({"namespace": ...}, with an optional reroute_namespace used by SOAP schedules); legacy plain-string entries are still accepted. A connection whose document sets "enabled": false is skipped by the scheduler.

Example schedule_config.json:

{
  "schedule_types": {
    "every_minute": {
      "type": "interval",
      "description": "Run every minute",
      "interval": "1m",
      "timezone": "UTC"
    },
    "every_5_minutes": {
      "type": "interval",
      "description": "Run every 5 minutes",
      "interval": "5m",
      "timezone": "UTC"
    }
  },
  "schedules": [
    {
      "name": "acme_dre_schedule",
      "enabled": true,
      "type": "sftp",
      "connection_id": "acme_sftp",
      "schedule": "every_5_minutes",
      "namespaces": [{"namespace": "enrx_org_004"}, {"namespace": "enrx_org_006"}],
      "description": "Process DRE files from Acme SFTP server"
    },
    {
      "name": "partner_order_schedule",
      "enabled": true,
      "type": "sftp",
      "connection_id": "partner_sftp",
      "schedule": "every_minute",
      "namespaces": [{"namespace": "enrx_org_008"}],
      "description": "Process order files from the partner SFTP"
    }
  ]
}

Key Fields:

• name: Unique identifier for the schedule
• enabled: Boolean to enable/disable the schedule
• type: Must be "sftp" for SFTP integrations (or "soap" for SOAP)
• connection_id: References a connection configuration file
• schedule: References a schedule_type that defines timing
• namespaces: Array of namespace IDs that will process files from this connection
• description: Human-readable description

2. Connection Configuration

Each SFTP connection is defined in a separate file: connections/<connection_id>.json

Example: connections/acme_sftp.json (Basic Authentication)

{
  "id": "acme_sftp",
  "host": "sftp.acme.example.com",
  "port": 22,
  "auth": {
    "type": "basic",
    "username": "ewx_user",
    "pw_secret_key": "projects/<project-id>/secrets/acme-sftp-password"
  },
  "source_folder": "/outbound",
  "allowed_extensions": ["xml", "txt", "csv"]
}

Example: connections/secure_sftp.json (Certificate Authentication)

{
  "id": "secure_sftp",
  "host": "secure.example.com",
  "port": 22,
  "auth": {
    "type": "certificate",
    "transport_cert": {
      "name": "transport-cert",
      "data": "-----BEGIN CERTIFICATE-----\nMIIC...\n-----END CERTIFICATE-----",
      "expiration_date": "2027-12-31"
    },
    "signing_cert": {
      "name": "signing-cert",
      "data": "-----BEGIN CERTIFICATE-----\nMIIC...\n-----END CERTIFICATE-----",
      "expiration_date": "2027-12-31"
    }
  },
  "source_folder": "/data",
  "allowed_extensions": ["xml"]
}

Key Fields:

• id: Unique identifier (matches connection_id in schedule)
• host: SFTP server hostname or IP address
• port: SFTP port (default: 22)
• auth: Authentication configuration object
• auth.type: Either "basic" or "certificate"
• auth.username: SFTP username (required for type="basic")
• auth.pw_secret_key: Full Secret Manager resource path of the password secret, projects/<project-id>/secrets/<secret-name> (required for type="basic")
• auth.transport_cert: Transport certificate config (required for type="certificate")
• auth.signing_cert: Signing certificate config (required for type="certificate")
• source_folder: Folder to retrieve files from (optional, defaults to null)
• allowed_extensions: List of file extensions to process (optional, defaults to [])
• stream_threshold_bytes: Files larger than this are streamed straight to File Manager instead of buffered in memory (optional, defaults to the SFTP_STREAM_THRESHOLD_BYTES setting)
• max_file_size_bytes: Files larger than this are skipped rather than risking an out-of-memory kill (optional, defaults to the SFTP_MAX_FILE_SIZE_BYTES setting)
• skipped_folder: Folder (relative to source_folder) that permanently-skipped files are moved into, so they leave the inbox and are not re-processed (optional, defaults to the SFTP_SKIPPED_FOLDER setting)

SSH Host Key Verification:

The SFTP connection verifies the server's SSH host key using a secret stored in Google Secret Manager. The secret path is configured in settings_sftp.toml:

[default]
TRUSTED_KEY_SECRET_PATH = "projects/<project-number>/secrets/{id}_trusted_server_key"

The {id} placeholder is replaced with the connection's id field. For example, for connection acme_sftp, the system looks for secret: acme_sftp_trusted_server_key.

To obtain the host key, use:

ssh-keyscan -p 22 -H sftp.example.com

3. Message Retrieval Flow

The SFTP scheduler follows this process:

1. Scheduler Trigger: The cronjob runs based on the configured schedule (e.g., every 5 minutes)

2. Load Schedules: Retrieves all enabled SFTP schedules from schedule_config.json

3. Process Each Schedule: For each enabled schedule:

   a. Load Connection Config: Retrieves the connection configuration using connection_id

   b. Iterate Namespaces: For each namespace in the schedule's namespace list:

   • Load the namespace's config_set
   • Get inbound message types configured for SFTP
   • Connect to SFTP server using connection credentials
   • List files in source_folder with allowed extensions
   • Group files by message type using filename pattern matching
   • Create processing triggers for each group
   • Tag each trigger with the current namespace_id

4. Publish Messages: All processing triggers are published to the integration-service-inbound Pub/Sub topic with metadata:

{
  "namespace_id": "enrx_org_004",
  "message_type": "DRE",
  "inbound_source": "SFTP"
}

4. Message Type Extraction

The message type is extracted from the filename using the group_files_by_message_type function:

Logic:

1. For each file, convert the filename stem (without extension) to uppercase
2. Check if any configured inbound message type appears in the filename (case-insensitive)
3. If a match is found, use that as the message_type
4. If no match is found, use the full filename stem as message_type

Examples:

Filename	Configured Inbound Types	Extracted Message Type
DRE_123_day_24.xml	["DRE", "ORDER"]	DRE
order_file_001.csv	["ORDER", "DRE"]	ORDER
unknown_file.txt	["DRE", "ORDER"]	unknown_file
mixed_dre_order.xml	["DRE", "ORDER"]	DRE (first match)

5. Multi-Namespace SFTP Connections

Can one SFTP connection serve multiple namespaces?

Yes! This is configured in the schedule. The scheduler will:

1. Process the same connection multiple times (once per namespace)
2. Each namespace retrieves the same files from the SFTP server
3. Each namespace processes files according to its own configuration
4. Redis deduplication ensures files aren't processed multiple times by the same namespace

Example Use Case:

If both enrx_org_004 and enrx_org_006 need to process the same DRE files from an Acme SFTP server:

{
  "name": "acme_shared_schedule",
  "type": "sftp",
  "connection_id": "acme_sftp",
  "namespaces": [{"namespace": "enrx_org_004"}, {"namespace": "enrx_org_006"}]
}

Each namespace can have different:

• Validation rules
• Field mappings
• File tags
• Archive strategies

Key Differences from SOAP Routing

Aspect	SOAP	SFTP
Routing Determination	Inferred from credentials used in request	Explicitly configured in schedule
Namespace Assignment	One namespace per credential set	Multiple namespaces can share one connection
Configuration Location	Namespace-specific settings only	Schedule + Connection + Namespace settings
Message Identification	From SOAP header/body elements	From filename patterns
Fallback Namespace	Supported via configuration	Uses same namespace as primary

File Processing

To Publish Messages to the outbound process, (SFTP Server): A message needs to be published to the (custom-)integration-service-outbound Pub/Sub topic with metadata:

{
  "namespace_id": "enrx_org_004",
  "message_type": "DRE",
  "connection": "sftp_connection_id"
  "message_process": "SFTP"
}

Deduplication

To prevent files from being processed multiple times, the SFTP processor tracks processed filenames in a Redis set, one set per namespace per hour:

• Set Key Pattern: {redis_prefix}:{namespace_id}:inbound:sftp:processing:{YYYYMMDDHH} — redis_prefix is the service's configured Redis prefix (e.g. <customer>-integration-service), and the key ends with the current UTC date and hour
• Set Members: the filenames that have been (or are being) processed
• TTL: SFTP_PROCESSING_TTL_SECONDS, default 3600 seconds (1 hour), refreshed when a member is added. Keep it comfortably above the scheduler's poll interval — a TTL equal to the interval expires seconds before the next poll.
• Purpose: a file is processed only if adding its name to the set actually adds a new member; if the name is already present, the file is skipped

Example:

Key: acme-integration-service:enrx_org_004:inbound:sftp:processing:2026022713
Members: {"DRE_2026_02_27.xml", "DRE_2026_02_26.xml"}
TTL: 3600 seconds

If the scheduler runs every 5 minutes and finds the same file, Redis will prevent duplicate processing for up to 1 hour. Note that the key is scoped per namespace, not per connection: two connections delivering a file with the same name to the same namespace within the same window deduplicate against each other.

The forwarded marker

The processing set above throttles re-processing within its window. A second, longer-lived marker guarantees that a file whose content has already reached File Manager is never forwarded again, even if the rest of its cycle fails:

• Key Pattern: {redis_prefix}:{namespace_id}:inbound:sftp:forwarded:{filename}:{size} — keyed on the file name and its remote size, so a re-delivered file with identical content maps to the same marker, while a genuinely new file that happens to reuse the name (and has a different size) does not
• Value: the timestamp and trace ID of the forward, for debugging
• TTL: SFTP_FORWARDED_MARKER_TTL_SECONDS, default 604800 seconds (7 days)
• Not time-scoped: unlike the processing set, this key carries no date or hour component, so it keeps guarding across midnight and across poll cycles for as long as its TTL runs

The marker is written immediately after a successful forward and before the archive is attempted. That ordering is what stops the re-ingestion loop: if moving the file out of source_folder keeps failing — a permission problem on the SFTP folder, for example — the file is still listed on the next poll, but the marker tells the processor its content is already in, so only the archive step is retried instead of the file being ingested again every cycle.

Both deduplication TTLs come from the service's Dynaconf settings (settings_sftp.toml, [default]) and can be overridden per environment.

Archive Behavior

After successful processing:

1. File is moved from source_folder to archive_folder on SFTP server
2. Original file is deleted from source_folder
3. Archive folder structure can be organized by date if needed

Error Scenarios:

Scenario	Behavior
Archive folder doesn't exist	Error logged, file remains in source folder
Archive succeeds but processing fails	File is archived, error logged in audit
Connection lost during archive	File remains in source. On the next run the forwarded marker is found, so only the archive is retried — the file is not ingested again
Archive keeps failing (e.g. folder permissions)	Same: the file is re-listed every poll but never re-forwarded, until the marker's TTL expires
Duplicate file in archive	Existing file is overwritten

Large File Handling

Inbound files are size-checked before download so that a very large file cannot exhaust the pod's memory. The processor stats each file on the SFTP server and then routes it based on two thresholds (per-connection overrides, otherwise the SFTP_STREAM_THRESHOLD_BYTES / SFTP_MAX_FILE_SIZE_BYTES settings):

File size	Behaviour
≤ stream threshold	Downloaded into memory and processed normally (parse/validate/forward).
> stream threshold	Streamed straight from SFTP to File Manager via a chunked, resumable upload (bounded memory), bypassing parsing/validation — only for message types that do not require XSD validation.
> max file size	Skipped (SFTP-005): moved to the connection's skipped folder and dropped from Redis (see below).

A message type that requires XSD validation cannot be streamed unvalidated, so if such a file exceeds the stream threshold it is skipped (SFTP-006) rather than streamed. The two skip cases use distinct error codes so operators can tell them apart in logs/alerts: SFTP-005 = file exceeds the hard maximum size; SFTP-006 = file exceeds the stream threshold but its message type requires validation (so it cannot be streamed).

Skipped files (SFTP-005 / SFTP-006): permanently-skipped files are moved out of source_folder into the skipped folder — skipped_folder on the connection, otherwise the SFTP_SKIPPED_FOLDER setting (default skipped). Moving them out of the inbox stops them being re-listed and re-rejected on every poll, and keeps them visible to the sender. If the move itself fails, the file is left in the Redis processing set as a fallback so retries are throttled to the deduplication TTL rather than firing every cycle.

Troubleshooting

Files Not Being Picked Up

Check:

1. Schedule is enabled in schedule_config.json
2. Connection credentials are correct
3. Source folder path is correct
4. File extension is in allowed_extensions list
5. Message type matches configured inbound types
6. Namespace configuration includes the message type with sftp_inbound_type: true

Files Processed Multiple Times

Check:

1. Redis is running and accessible — if Redis is unreachable neither the processing set nor the forwarded marker can be written
2. TTLs are not set too low (SFTP_PROCESSING_TTL_SECONDS, SFTP_FORWARDED_MARKER_TTL_SECONDS)
3. Different schedules aren't using same connection with overlapping namespaces
4. If the same file is ingested repeatedly and stays in source_folder, the archive step is failing — check the archive folder's permissions; the forwarded marker should prevent re-ingestion, so repeated ingestion points at a missing or expired marker

Connection Failures

Error Code: SFTP-000

• Check host, port, and network connectivity
• Verify username and password in Secret Manager
• Confirm timeout is sufficient for network conditions

Namespace Not Processing Files

Check:

1. Namespace is listed in schedule's namespaces array
2. CONFIG_SET for namespace is correctly configured
3. Message type has sftp_inbound_type: true in message_config.json
4. Connection_id matches the connection configuration file

Configuration Checklist

To add a new SFTP integration:

• Create connection config: connections/<connection_id>.json
• Store SFTP password in Google Secret Manager
• Add schedule to schedule_config.json with correct connection_id and namespaces
• Configure message type in message_config.json with sftp_inbound_type: true
• Create field map config: field_map_configs/<message_type>.json
• Create file tags config: file_tags/<message_type>.json
• (Optional) Add XSD schema if validation is needed
• (Optional) Add validation rules if needed
• Test connection manually with SFTP client
• Enable schedule and monitor first run

See the Quick Start - SFTP Integration guide for the full step-by-step walkthrough.

Security Considerations

Authentication:

• Currently supports username/password authentication
• Passwords stored in Google Secret Manager
• Host key verification path: TRUSTED_KEY_SECRET_PATH (configurable)

Future Enhancements:

• SSH key-based authentication
• Certificate-based authentication
• Mutual TLS support

Performance Considerations

Scheduling Frequency:

• Higher frequency (every minute) = lower latency, more API calls
• Lower frequency (every 5-10 minutes) = better efficiency, higher latency

File Size:

• Large files (>10MB) may need increased timeout values
• Consider batch processing vs individual file processing

Connection Pooling:

• Each scheduler run creates a new SFTP connection
• Connections are closed after file listing/processing
• No persistent connection pooling currently implemented