Build a reliable ingestion pipeline that receives Square order events, verifies signatures, and writes idempotently to Postgres with proper error handling.
Square Webhook Event Model
Square sends webhooks for order lifecycle events including order.created, order.updated, and payment.updated. Each payload contains an order object with line items, totals, and tender details plus a merchant_id and location_id for multi-tenant routing.
Register a single endpoint URL in the Square Developer Dashboard under Webhooks. Subscribe only to the events your schema needs; over-subscribing increases noise and processing cost.
Compare this approach to the Toast webhook sync pattern where event shape and retry semantics differ by provider.
Endpoint Setup and Signature Verification
Expose a POST handler that returns 200 within 2 seconds. Square retries with exponential backoff on non-2xx responses, so avoid long-running work in the request path.
Verify the x-square-signature header using HMAC-SHA256 against your webhook secret and the raw request body. Reject any request that fails verification to prevent injection.
import crypto from 'crypto';
function verifySquareSignature(body, signature, secret) {
const hmac = crypto.createHmac('sha256', secret);
hmac.update(body);
return hmac.digest('base64') === signature;
}Idempotent Order Upsert
Store the Square order.id as the primary key or a unique constraint. Use INSERT … ON CONFLICT (square_order_id) DO UPDATE to handle duplicate deliveries safely.
Extract line items into a separate order_items table with foreign key to orders. This keeps the schema normalized and supports later analytics queries without JSON parsing at read time.
Wrap the entire write in a transaction so partial failures leave the database consistent.
Connecting to Postgres from the Handler
Use the node-postgres pool with connection limits set to 10–20 for typical webhook traffic. Set idleTimeoutMillis to 30 seconds and maxUses to 7500 to avoid stale connections.
If deploying on serverless, prefer a connection pooler such as PgBouncer or Neon’s serverless driver to prevent connection exhaustion.
Reference the deployment patterns in How to Deploy a Hono API to Cloudflare Workers with D1 and Queues and adapt the queue layer for Postgres instead of D1.
Failure Modes and Retry Strategy
Log the full payload and Square event ID on any database error. Store failed events in a dead-letter table with retry_count and next_attempt_at so you can replay without data loss.
Square will retry up to 72 hours. After that, implement your own reconciliation job that polls Square’s Orders API for recent changes using the updated_at filter.
Never delete webhook events until they have been successfully written and acknowledged; this is the most common source of silent data loss in POS integrations.
Production Hardening
Add a unique index on (merchant_id, location_id, square_order_id) to support multi-location merchants without collisions.
Monitor webhook latency and failure rate with a simple counter in Postgres or an external metrics system. Alert when 5xx responses exceed 1% over a 5-minute window.
Test the full flow with Square’s sandbox before promoting the endpoint. Include at least one order with modifiers and one split-tender payment to cover the edge cases that break most initial implementations.