Skip to main content

Overview

The storefront-backend webservice is integrated with Stitch, a no-code ETL pipeline that performs automated, log-based replication of Postgres data into our Google Cloud Data Warehouse (BigQuery).

Integration Configuration​

Within Stitch, we have a Google Cloud SQL PostgreSQL integration configured to target our production Cloud SQL instance. The integration connects to the database via it's public IP over SSL. To authenticate, we've provisioned a Postgres user with username stitch and a password loaded into Google Cloud Secret Manager. The stitch database user is configured with replication permissions.

Replication is only configured for the production Cloud SQL instance. While a stitch user exists in the staging database, there is no Stitch integration currently configured to target that instance.

Infrastructure Setup​

Stitch's Google Cloud SQL PostgreSQL integration requires exposure of a public IP for connectivity. To ensure security of our database, we are still requiring SSL encrypted traffic only and have specifically whitelisted Stitch's IP ranges. All network configuration is co-located with our database config in Terraform.

Maintenance​

At its time of creation, the Stitch integration was configured to track all tables and columns available in production. Should a new table or schema be added, manual maintenance is required to ensure that Stitch can replicate its data into BigQuery.

To configure new tables for replication:

  1. Open the Stitch integration for storefront
  2. Navigate to the Tables to Replicate tab
  3. Using the checkboxes, check the new table that we want to sync, including any new columns