Abstract

This document describes the core functionality and architecture required to implement a Demand Partner Integration system. The goal is to enable ingestion and management of merchant and offer data, redemption feed processing, and a stub for aggregate reporting within a partner platform. The implementation leverages both SFTP file-based and REST API-based ingestion, enforces strict JSON schema validation, and provides foundational backend structures for further development.

How It Works

The integration is divided into several core components:

1. Merchant Ingestion:

   • Supports SFTP-based full-dump JSON file uploads and REST API upsert/delete endpoints.
   • All merchant data is validated against a defined schema, supporting statuses like NEW, UPDATED, UPSERT, DELETED, and NO-CHANGE.
   • Records are inserted, updated, or deleted in the platform database based on status, always processing the full merchant list.
   • File overwrites are allowed; schema validation failures result in rejection.

2. Offer Ingestion:

   • Mirrors the merchant ingestion process for partner offers, via both SFTP and REST API.
   • Data is validated against an offer schema and supports the same status values and operational logic.

3. Redemption Feed:

   • Hourly SFTP download of a redemption feed, containing transaction and partner identifiers and reconciliation details.
   • Data is imported to the database for reporting.

4. Aggregate Reporting API (Stub):

   • Provides a stubbed REST API endpoint for partners to query aggregate metrics (e.g., purchases, revenue, reach, impressions, activations, customer postcodes, diner segmentation, travel distance).
   • The reporting implementation itself is out of scope for MVP.

5. Deliverables:

   • Backend: SFTP watchers, REST endpoints, schema validation, database models, stub reporting API, logging, and error handling.
   • Documentation: API references and sample payloads.

6. Out of Scope:

   • Authentication/authorization, quotas, UI, full reporting, or activation/customer-facing features.

7. Example Directory Structure:

   • Provides a sample file/folder organization demonstrating modular separation of schemas, APIs, SFTP watchers, and database models.

8. Example Implementations:

   • Includes code snippets for minimal Express.js endpoints (merchant upsert/delete) and an SFTP merchant file watcher using Node.js and chokidar.

9. Schema Extract:

   • Partial JSON schema for merchants, detailing required fields and structure.

Overall, this document serves as a blueprint to onboard demand partners through file and API-based integrations, while supporting schema validation, basic CRUD operations, redemption handling, and a foundation for future reporting enhancements.

1. Merchant Ingestion

a. SFTP File Ingestion

• Parse JSON files matching <Partner_Name>MerchantsCCYYMMDD.json.
• Validate JSON against the Merchant Schema (see below).
• Support all merchantStatus values: NEW, UPDATED, UPSERT, DELETED, NO-CHANGE.
• Insert, update, or delete merchant records in the platform DB according to status.
• Always process full merchant list (full dump), not deltas.
• Overwrite file if same filename exists.
• Reject file if schema validation fails.

b. REST API Support

• Implement endpoint:
  • PUT /api/v1/partner/merchants/{external_merchant_id}
    • Upsert merchant with provided JSON (see schema below).
    • Derive merchantStatus from HTTP method (PUT → UPSERT).
  • DELETE /api/v1/partner/merchants/{external_merchant_id}
    • Remove merchant by ID (status = DELETED).

Merchant Schema (Core Fields)

• merchantId, merchantName, merchantCategoryCode, merchantSubCategories
• paymentChannels, paymentSubChannels, merchantUrl, merchantApps
• stores: Array, each with:
  • storeId, storeName, address1, city, state, postalCode, countryCode
  • latitude, longitude, storePhoneNumbers, storeEmails, storeUrl
  • processorMidChangedFlag, processorMidRecords (see detailed schema above)
• See full JSON example in document.

2. Offer Ingestion

a. SFTP File Ingestion

• Parse JSON files matching <Partner_Name>OffersCCYYMMDD.json.
• Validate JSON against Offer Schema (see below).
• Support all offerStatus values: NEW, UPDATED, UPSERT, DELETED, NO-CHANGE.
• Insert, update, or delete offer records in the platform DB according to status.
• Always process full offer list.
• Overwrite file if same filename exists.
• Reject file if schema validation fails.

b. REST API Support

• Implement endpoint:
  • PUT /api/v1/partner/merchants/{external_merchant_id}/offers/{external_offer_id}
    • Upsert offer (see schema below).
    • Derive offerStatus from HTTP method (PUT → UPSERT).
  • DELETE /api/v1/partner/merchants/{external_merchant_id}/offers/{external_offer_id}
    • Remove offer by ID (status = DELETED).

Offer Schema (Core Fields)

• offerId, merchantId, offerName
• assets:
  • logo (with either url or content),
  • largeRectangle (either url or content)
• See full JSON example in document.

3. Redemption Feed

• SFTP download of redemption feed (provided by CDLX hourly).
• Parse file with schema:
  • transaction_id, cardholder_id, partner_merchant_id, partner_offer_id, partner_store_id
  • partner_merchant_name, card_type, card_last_four, transaction_amount, transaction_timestamp
• Import to DB for reporting and reconciliation.

4. Aggregate Reporting API (Stub Only)

• POST /api/v1/partner/merchants/{external_merchant_id}/reports
  • Accepts empty request (all offers) or filter by offerIds and timeRange.
  • Returns: purchases, revenue, reach, impressions, activations, list of customer postal codes, new/repeat diners, avg. travel distance.

5. Deliverables

• Backend

  • SFTP watcher for Merchant/Offer files.
  • REST endpoints for Merchant/Offer upsert & delete.
  • JSON schema validation.
  • Database models for merchants, offers, stores, redemptions.
  • Simple reporting API stub.
  • Logging and error handling.

• Documentation

  • API reference for REST endpoints.
  • Sample SFTP and REST payloads.

6. Out of Scope

• Partner authentication & authorization.
• Quota/RPS enforcement.
• UI/dashboard.
• Full reporting implementation.
• Offer activation or customer-facing functionality.

7. Example Directory Structure

/integration-mvp/
  /schemas/
    merchant.schema.json
    offer.schema.json
    redemption.schema.json
  /api/
    merchants.js
    offers.js
    reporting.js
  /sftp/
    merchantWatcher.js
    offerWatcher.js
    redemptionFetcher.js
  /db/
    models.js
  README.md

8. Example: Minimal Merchant Upsert Endpoint (Express/Node.js)

// api/merchants.js
const express = require('express');
const router = express.Router();
const { validateMerchant } = require('../schemas/merchant.schema');
const { upsertMerchant, deleteMerchant } = require('../db/models');

// Upsert
router.put('/api/v1/partner/merchants/:merchantId', async (req, res) => {
  const merchant = req.body;
  const valid = validateMerchant(merchant);
  if (!valid) return res.status(400).json({ error: 'Invalid merchant payload.' });
  await upsertMerchant(merchant);
  res.json({ message: 'Request accepted for processing', trace_id: `req-${Date.now()}` });
});

// Delete
router.delete('/api/v1/partner/merchants/:merchantId', async (req, res) => {
  await deleteMerchant(req.params.merchantId);
  res.json({ message: 'Request accepted for processing', trace_id: `req-${Date.now()}` });
});

module.exports = router;

9. Example: Minimal SFTP Merchant File Watcher

// sftp/merchantWatcher.js
const chokidar = require('chokidar');
const fs = require('fs');
const { validateMerchantFile } = require('../schemas/merchant.schema');
const { upsertMerchant, deleteMerchant } = require('../db/models');

chokidar.watch('/mnt/sftp-drop/*.json').on('add', async (path) => {
  const raw = fs.readFileSync(path);
  const data = JSON.parse(raw);
  if (!validateMerchantFile(data)) return; // log error
  for (const rec of data.merchantRecords) {
    if (rec.merchantStatus === 'DELETED') await deleteMerchant(rec.merchantId);
    else await upsertMerchant(rec);
  }
});

10. Example: Merchant Schema (JSON Schema extract)

{
  "type": "object",
  "properties": {
    "merchantId": { "type": "string" },
    "merchantName": { "type": "string" },
    "merchantCategoryCode": { "type": "string" },
    "merchantSubCategories": {
      "type": "array",
      "items": {
        "type": "object",
        "properties": {
          "categoryType": { "type": "string" },
          "categoryItems": { "type": "array", "items": { "type": "string" } }
        }
      }
    },
    "paymentChannels": { "type": "array", "items": { "type": "string" } },
    "stores": {
      "type": "array",
      "items": {
        "type": "object",
        "properties": {
          "storeId": { "type": "string" },
          "storeName": { "type": "string" },
          "address1": { "type": "string" },
          "city": { "type": "string" },
          "state": { "type": "string" },
          "postalCode": { "type": "string" },
          "countryCode": { "type": "string" }
        },
        "required": ["storeId", "storeName", "address1", "city", "state", "postalCode", "countryCode"]
      }
    }
  },
  "required": ["merchantId", "merchantName", "paymentChannels", "stores"]
}

Summary

This document provides the minimum framework required to ingest, validate, and store merchant/offers information, handle redemptions, and stub reporting, as specified by the Demand Partner Integration v1.2 document.