Automation

Colect·IF Corporate Gifting Platform - Shopify, Airtable & Softr

Systems & Automation Developer / Technical Architect

Business summary
Corporate Gifting & Hospitality • Scalable client portal, relational data model, and automated Stripe billing
  • Single source of truth in Airtable for 100-500 client gift allocations and usage
  • Automated Stripe card/ACH invoicing with instant webhook sync back to Softr portal
  • Automated Zest concierge drawdown routing without changing concierge team workflows

What I built

Architected a scalable program-gifting operations platform for Colect·IF. Re-structured Shopify as the billing foundation with a 15-product catalogue, custom metafield pricing, and branded Liquid Order Printer invoices. Built a 9-table relational Airtable base and a secure Softr client portal with company-level row security. Automated Make.com syncs for Shopify draft orders, Zest concierge gift drawdowns, GraphQL write-backs, Stripe ACH/card invoicing with automated status reconciliation, and HubSpot CRM deal ownership routing by vertical.

Tech stack

ShopifyAirtableSoftrMake.comStripe APIShopify GraphQLLiquidStripe InvoicingACH PaymentsZestHubSpotClient PortalRelational Data ModelingB2B GiftingWebhooksAutomated InvoicingOrder Printer
Deep Dive Case Study

Case Study - Colect·IF Program Gifting Platform

Developer: Faizan Sohail - Systems & Automation Developer
Engagement: Subcontracted via agency (Nomad Devs) for Colect·IF, a US corporate-gifting company
Timeline: May 2026 - September 2026 (build phase May-July, retainer from 19 Aug 2026)
Stack: Shopify · Stripe · Airtable · Make.com · Softr · Zoho Books · Zest (concierge platform) · HubSpot · Squarespace · Liquid · JavaScript · GraphQL


1. The Problem

Colect·IF sells program-based gifting to homebuilders, hospitality groups and corporate clients (e.g. homeowner welcome gifts for large US homebuilders). Each client commits to an annual number of gift units, billed quarterly. When Colect·IF's concierge team ships a gift on the client's behalf, that delivery draws down from the client's allocation.

Before the build, all of this lived in a concierge tool (Zest) plus manual invoicing. There was:

  • no single system of record for what each client had bought vs. used,
  • no branded, consistent invoicing,
  • no way for clients to see their own usage, remaining units or invoices,
  • no structure that would scale from a handful of clients to 100-500.

The brief explicitly asked for clean architecture, scalability, clarity of data relationships and a portal that feels like a client experience, not internal software.


2. What I Built

2.1 Shopify as the billing foundation (pre-phase)

Re-structured Shopify as the system of record for program billing:

  • 15-product program catalogue - 5 verticals (HXP Homeowner, PXP Client, EXP Employee, EVP Event, HLP Hospitality) × 3 tiers (Foundation, Signature, Premium). Unlisted, non-physical, inventory-untracked products used purely as billing units.
  • Metafield model for coverage period, tier, per-client unit price, per-client shipping & handling price, and concierge project number - so every client can have their own pricing without duplicating products.
  • Tag convention (program-allocation, zest-gift, zest-concierge-COL####) separating program allocations from fulfillment orders.
  • Custom Order Printer invoice template (Liquid) - fully branded editorial design (navy / bronze / cream, Playfair Display + Inter). Line items, quantities and totals are driven from metafields, with conditional shipping & handling rows and cents/whole-dollar handling.
  • Draft-order creation via API from Make, with computed SKUs (vertical-tier-quarter-units-company-region), tags and concierge-project note attributes.

2.2 Airtable relational data model

Designed and built a 9-table base built for scale:

Table Purpose
Companies Master client record - slug, region, tier, billing contact, annual commitment, branding (logo, accent colour), status
Programs Reference table for all 15 vertical × tier combinations, with rate and shipping per unit
Allocations Per-quarter commitment per client - units, rollover in/out, units used (rollup), remaining, overage, payment status
Usage One record per gift shipped - recipient, address + lat/lng for a delivery map, scheduled/shipped/delivered dates, tracking, fulfillment status
Concierge Projects Zest project numbers linked to companies - the routing key between Zest orders and clients
Inventory Physical stock at the 3PL warehouse, with a client-visibility formula
Invoices Invoice number, amount, due/paid dates, status, PDF link, Zoho/Stripe IDs
Requests Queue Client gift requests from the portal
Sync Log Audit trail for every automation run

Rollups and formulas calculate units used, remaining, overage and rollover automatically.

2.3 Make.com automation layer

Designed and documented a suite of scenarios (written up as a full build guide), including:

  • sync_shopify_allocation - Shopify program order → Allocation record in Airtable.
  • sync_zest_usage - Zest-originated Shopify order → Usage record, drawing down the right allocation.
  • Write-back pattern - after every Airtable create, the Airtable record ID is written back to Shopify via a GraphQL metafieldsSet mutation, closing the loop and preventing duplicate syncs.
  • Form → order flow - an Airtable form submission creates the Shopify draft order with full program data.
  • Stripe invoicing flow (see 2.5).
  • Error-handling, filtering on tags/empty record IDs, and a shared Sync Log.

2.4 Softr client portal

Built a login-based portal on top of Airtable:

  • Row-level security - every block filtered to the logged-in user's Company, with explicit testing guidance (incognito test logins) because the failure mode is silent cross-client data exposure.
  • Pages: Dashboard (snapshot metrics), Usage & Inventory, Reporting, Invoices, Actions.
  • Editorial luxury theme matching the Colect·IF brand.
  • Used Softr's AI Co-Builder, feeding it precise schema, link-field and filter specs to get reliable output instead of guesses.
  • Plan selection analysis (Basic vs Professional vs Business) based on user counts, API access and branding requirements.

2.5 Invoicing - evolved through three versions

The invoice flow was rebuilt three times as the client's requirements matured:

  1. Manual - branded Order Printer PDF downloaded and sent by the team.
  2. Automated PDF - the same branded PDF generated automatically.
  3. Stripe Invoicing (current) - Make creates a Stripe invoice alongside the Shopify order, Stripe emails it from the company billing address with a Pay button supporting card + ACH, and on invoice.payment_succeeded a webhook flips the invoice to Paid in Airtable so the portal updates automatically. Customer matching uses search-then-create to avoid duplicates.

I laid out the trade-offs for each option (Stripe Invoicing vs. branded PDF + Payment Link vs. Shopify checkout, which fails the ACH requirement) so the client could make an informed decision, and walked through enabling ACH and testing with the Stripe CLI.

2.6 Zest concierge integration

Zest is where the concierge team does daily gifting work. I traced how Zest pushes orders into Shopify and discovered it only reliably populates the concierge project number (not the storefront slug). Rather than forcing an unreliable field, I re-architected the routing to go Order → Concierge Project → Company → active Allocation → Usage - a more robust key that matches how the business actually works (one company can have multiple storefronts and contacts).

2.7 CRM & sales side (HubSpot)

  • Audited the existing HubSpot pipeline, contacts and deal properties before changing anything, to avoid duplicating setup.
  • Installed and verified the HubSpot tracking code on the Squarespace site (moved to site-wide injection, explained how anonymous visits get linked to contacts).
  • Designed rep targets with HubSpot Goals and automatic Deal Owner assignment by vertical via a two-branch workflow.
  • Planned the Closed-Won → Monday → operations handoff and advised on whether Apollo was needed (recommended deferring it - saved the client a subscription).

2.8 Additional tools and scoping

  • BOM / Kit Costing Tool - scoped and wrote the SOW: 3 new Airtable tables (Components, Kits, KitComponents), 6 Softr pages with a manager approval queue, a Liquid PDF quote that hides margin from the end client, and a Make scenario to push approved kits to Shopify. Quoted 40 hrs / 3 weeks.
  • Airtable dedupe script - a reusable Scripting-extension tool: pick table and match fields, choose keep-first or keep-last, preview duplicates, confirm, then delete in batches of 50 with live progress and a summary.
  • Card surcharge / ACH advice - explained why a flat 3% fee belongs in payment config, not the invoice template, and the US compliance limits (credit only, capped at actual processing cost).

3. Process & client management

  • Phased delivery - Shopify foundation → Airtable + automations → Softr portal → CRM integration, each with hour estimates, a 6-week timeline, monthly tool-cost estimate, risk register and architectural-decision log delivered as a branded proposal document.
  • Handoff documents - wrote detailed handoff briefs (architecture, every scenario, schema, open items, next steps) so work could continue cleanly across sessions and be handed to the client team. Wrote an Operations Guide and Client Portal Guide to answer the client's concern about being able to run the system themselves.
  • Agency-safe communication - all client communication routed through the agency, using a two-part format: internal notes for the agency, and a polished client-ready message.
  • Scope discipline - separated defects from new feature requests, priced add-ons separately, and moved to a 3-month retainer (10-15 hrs/week, defined SLA, overage rate) once the build phase closed.
  • Verify before claiming - checked live systems and screenshots before declaring anything fixed or out of scope.

4. Outcomes

  • A single, relational source of truth for every client's commitment, usage, inventory and invoices, designed for 100-500 clients.
  • Automatic usage tracking from concierge orders, without the concierge team changing how they work - they keep creating concierge projects as before.
  • Clients get invoices by email they can pay instantly by card or ACH, with payment status reflected in their portal automatically.
  • A secure, branded client portal where each client sees only their own data.
  • Follow-on work (retainer, BOM tool, next-phase inventory/ordering features) won from the same agency relationship.

5. Skills demonstrated

Relational data modelling · Shopify metafields, draft orders, GraphQL Admin API · Liquid templating · Stripe Invoicing, ACH, webhooks · Make.com scenario design, error handling, idempotent sync · Airtable rollups, formulas, scripting · Softr portals and row-level security · HubSpot pipelines, workflows, goals, tracking · Technical writing (SOWs, proposals, operations guides, handoff docs) · Scoping, estimation and stakeholder communication through an intermediary