Stone City — Data Architecture

01 Overview

Stone City runs three customer-facing systems: a retail storefront on Square POS, a B2B operation through the Stone Profits ERP, and a marketing & lead-capture layer on HubSpot. HubSpot is the aggregator — every paid order, every B2B customer record, and every quote-in-flight mirrors into it nightly so the team has one view of the pipeline.

One-way: Square → HubSpot, SPS → HubSpot, forms → HubSpot. Nothing flows back from HubSpot today. The dotted lines from Sales team mark the dual-entry friction: a rep enters the customer twice (once in HubSpot when it arrives as a lead, again in SPS when they start a quote). Closing that gap requires Stone Profits to expose write endpoints — covered below.

02 Source systems

03 Order Sync — Square POS → HubSpot deals

Daily 11:00 UTC. Pulls completed Square orders from the last 25 hours, creates a HubSpot deal per order in the Square POS pipeline, links the deal to a contact (looked up by email then phone), tags VIPs on $5k+ purchases, and writes line items.

04 Customer Sync — SPS → HubSpot companies

Daily 11:30 UTC. Pulls all B2B customers from Stone Profits (~1,600 active records out of ~28k total), classifies each as B2B vs retail, then creates or updates HubSpot companies — matched first by SPS customer ID, then by name, then by domain. Linked contacts get SPS metadata stamped on them.

05 Quote Sync — SPS → HubSpot deals

Daily 12:00 UTC. Pulls SPS quotes modified in the last 25 hours, creates or updates a HubSpot deal per quote in the Quotes pipeline. Maps SPS transactionStatus / stage to HubSpot stage IDs (Quoted / Accepted / Lost). Tracks revisions.

06 HubSpot — the aggregator

HubSpot is where everything lands. Three pipelines, custom properties stamped by every sync, marketing automation triggered by lead status changes, and a reporting layer the team actually looks at.

Pipelines

Lead status enum

Every contact gets an hs_lead_status tag indicating origin / state. Writes are set-if-blank — earlier tags are never overwritten (so a contact who started as NEW from a form, then became SQUARE_POS from a sale, keeps the SQUARE_POS tag when later customer-sync would otherwise tag them SPS_B2B).

Custom properties (selection)

Marketing automation + ops scripts

Beyond the three syncs, the same repo has setup-once / on-demand scripts for the rest of the HubSpot footprint. Defined in package.json · invoked manually:

07 The dual-entry gap

The current sync architecture is one-way: everything flows into HubSpot, nothing flows out. That works for reporting and unified visibility but creates a manual handoff every time a HubSpot lead becomes an SPS quote.

Step 3 is the friction. The rep typed the customer's information into HubSpot (or it arrived via form) and now retypes it into SPS. Cost: ~5–10 minutes per new B2B customer, plus the data-drift risk if address fields differ between the two records.

Closing the gap requires SPS to expose write endpoints so HubSpot can push a "create customer + create quote" payload when the rep clicks a button in the HubSpot deal page (via a HubSpot custom card extension). The data flow becomes round-trip — see future state.

08 SPS write API ask Sent 2026-04-14

Email sent to the Stone Profits API contact on 2026-04-14 requesting six endpoints to unblock the write direction. Status: awaiting their response.

Must-haves (in priority order)

Nice-to-haves

Open questions sent with the ask

• Does the existing AuthorizedNumber grant write access, or do writes need a different token / OAuth scope?
• Will POST endpoints accept an Idempotency-Key header so we can safely retry on network failures?
• In /v2/Quotes line items, are products looked up by itemId (numeric) or sku (string)?
• Timeline estimate? Even a rough "2 weeks / 2 months / next quarter" lets us sequence the HubSpot custom-card work.

09 Future state (once SPS ships writes)

Once Stone Profits exposes the write endpoints above, a 4th sync direction becomes possible and the dual-entry gap closes.

What changes

• New: HubSpot Deal → SPS Customer + Quote (push, real-time). Triggered by a HubSpot custom-card button on the deal sidebar.
• Sales rep opens a qualified HubSpot deal, clicks "Quote in SPS", a dialog asks for line items + sales rep + expiry, POSTs to /v2/Customers (if SPS doesn't already have one) then /v2/Quotes, stamps the returned quote ID back on the HubSpot deal.
• Existing Quote Sync becomes redundant for in-flight quotes (they're already in HubSpot from creation) but stays useful for status updates and for quotes created directly in SPS (older workflow).
• Webhook listener (if SPS ships webhooks) replaces the daily Quote Sync polling for status changes. Latency drops from up to 24h → seconds.

Implementation footprint

• HubSpot custom card extension (UI extension API) — small React component shown on deal page
• Backend Lambda (or extend the existing Lambdas) handling the POST flow, idempotent via the HubSpot deal ID
• SPS write client in src/clients/sps-customers.ts + sps-quotes.ts (currently read-only)

Time-to-build estimate once SPS endpoints exist: ~2 weeks for the basic create-customer-and-quote flow, +1 week for the webhook listener if webhooks ship.

10 Operational

AWS accounts

Schedule cheat-sheet (all UTC)

Lambda inventory

Monitoring (TODO)

• CloudWatch alarm on Lambda Errors metric → SNS → email [email protected]
• Optional: alarm on Duration > 600s (early warning before 900s timeout)
• Optional: alarm on Invocations < 1 over 24h (cron didn't fire)

Migration plan (master → stonecity-prod)

1. Wait for Free → Paid upgrade on 507024405504 (manual in Billing console, takes up to 24h)
2. Verify S3 + SSM probes return clean in stonecity-prod-sso profile
3. cd ~/img-integration/.worktrees/lambda-migration
4. AWS_PROFILE=master-sso npx sst remove --stage production (remove from master)
5. AWS_PROFILE=stonecity-prod-sso npx sst deploy --stage production (redeploy)
6. Verify next-morning CRON fires cleanly in the new account
7. Unload local LaunchAgents

Quick reference commands

# Tail order sync logs
aws logs tail /aws/lambda/img-integration-production-OrderSyncHandlerFunction-zsezdkkx \
  --profile master-sso --since 5m --follow

# Manual one-off invocation (sync)
aws lambda invoke --profile master-sso \
  --function-name img-integration-production-OrderSyncHandlerFunction-bcebxfuo \
  --invocation-type Event --payload '{}' /tmp/out.json

# Unload local LaunchAgents after Lambda confidence
launchctl unload ~/Library/LaunchAgents/com.img.sync-orders.plist
launchctl unload ~/Library/LaunchAgents/com.img.sync-customers.plist
launchctl unload ~/Library/LaunchAgents/com.img.sync-quotes.plist

# Re-run a sync locally
cd ~/img-integration && npm run sync-orders

11 Code map

Repo: ~/img-integration/