Description
Summary Project Overview We are developing a robust inventory management and buying platform using Microsoft Excel as the primary frontend dashboard. We need a low-code integration specialist to design and build a stable, scalable data pipeline that syncs data between our Point of Sale system, Heartland Retail (Global Payments), and Microsoft Excel. The system handles approximately 45,000 SKUs (20,000 active, 25,000 inactive) with heavy usage of item matrices (parent/child item styles for sizes and colors). Because Excel cannot reliably process raw API requests at this scale, you will build a hybrid data-warehouse architecture using a low-code cloud database middleware to ensure optimal performance. Scope of Work Phase 1: Inbound Architecture (Heartland POS ➔ Excel) - Set up a cloud database middleware (such as Airtable, Supabase, or Azure SQL) to act as our data staging area. - Build automated workflows using an integration platform (Make.com, Zapier, or native scripts) to pull raw data from Heartland Retail’s core REST API endpoints. - Effectively manage Heartland's API pagination limits to successfully extract all 45,000 SKUs without timeouts. - Handle the item matrices by extracting nested JSON data from the /api/items endpoints and flattening it into standard rows (ensuring parent attributes like brand/vendor carry down to every individual child SKU). - Segment the middleware database into structured tables: Active Items (20k rows), Inactive Archive (25k rows), and Historical Sales Logs. - Establish the native connection from the cloud database to Microsoft Excel via Power Query so data can be refreshed instantly by non-technical users. Phase 2: Outbound Architecture (Excel ➔ Heartland POS) - Configure a visual workflow using Microsoft Power Automate (or a similar low-code workflow engine) to enable two-way sync capabilities. - Build a flow triggered by a designated action in Excel (e.g., a status change or a table update) that packages our finalized buying spreadsheet rows into JSON payloads. - Map these payloads securely back to Heartland Retail's /api/purchasing/purchase_orders or /api/inventory/on_hand endpoints to generate purchase orders and adjust inventory levels in bulk. Required Technical Skills - Proven experience working with REST APIs, handling Bearer Token authentication, and building API loops to manage strict pagination rules. - Expertise in low-code automation tools, specifically Microsoft Power Automate, Make.com (Integromat), or Zapier. - Strong background working with cloud databases (Airtable, Supabase, or similar relational databases). - Advanced familiarity with Microsoft Excel data connections (Power Query and connecting to external relational databases). - Prior experience integrating Point of Sale (POS) systems, retail inventory data, or ERP systems is highly preferred. Experience with Heartland Retail specifically is a major plus. Deliverables 1. A fully functioning middleware cloud database synced smoothly with Heartland Retail. 2. A fast, stable Excel workbook template that pulls clean, flattened matrix data natively via Power Query. 3. A reliable outbound automation workflow that successfully pushes custom-built purchase orders from Excel directly back into Heartland Retail. 4. Brief handover documentation outlining how to refresh the tokens or modify data mappings in the future.