# bulk-shopify-onboarding

Three Lambda functions that automate Shopify data connector onboarding via Fivetran.

## Architecture

```mermaid
flowchart TD
    GS([Google Sheet]) -->|synced| AS[(ARTIST_STORES)]
    CRM[(CRM_ECOMMERCE_DATA)] -.->|excluded| L1
    FAN[(Fansifter)] -.->|excluded| L1

    EB1([EventBridge — hourly]) --> L1["Lambda 1: connect-shopify-stores\nScan ARTIST_STORES for new domains\nExclude duplicates, CRM, Fansifter\nWrite initial registry row, queue to SQS"]
    AS -->|store metadata| L1
    L1 -->|initial row| REG[(ECOMMERCE_STORE_REGISTRY)]
    L1 --> SQS([SQS])

    SQS --> L2["Lambda 2: onboard-shopify-stores\nCreate Fivetran connector\nIssue Connect Card URL\nWrite onboarding state"]
    L2 <-->|connector + Connect Card| FT([Fivetran])
    L2 -->|onboarding state| STATE[(D2C_ONBOARDING_STATE)]

    EB2([EventBridge — every 15 min]) --> L3["Lambda 3: sync-shopify-data-connectors\npending_oauth → poll for OAuth completion\npending_configure → apply schema config, trigger sync\npending_sync → watch for SHOP table, enrich registry"]
    STATE -->|in-flight stores| L3
    L3 <-->|status| FT
    L3 -->|advance state| STATE
    FT -->|data sync| SHOP[(SHOPIFY_STORES_GLOBAL)]
    SHOP -->|store name| L3
    FACTS[(FACTS)] -->|GP + vendor| L3
    L3 -->|connector_id on pending_sync\nfull enrichment on sync_complete| REG
```

### Store state machine

```mermaid
stateDiagram-v2
    [*] --> pending_oauth: connector created, Connect Card issued
    pending_oauth --> pending_configure: OAuth completed
    pending_oauth --> broken: OAuth not completed within 14 days
    pending_configure --> pending_sync: schema configured, sync triggered
    pending_configure --> sync_complete: SHOP already present (fast path)
    pending_configure --> broken: auth revoked before configure
    pending_sync --> sync_complete: SHOP table present in Snowflake
    sync_complete --> [*]
    broken --> [*]
```

`setup_state` values (`incomplete` / `connected` / `broken`) are from the [Fivetran Connection API](https://fivetran.com/docs/rest-api/api-reference/connections). Schema configuration uses the [Connector Management API](https://fivetran.com/docs/rest-api/connectors/config).

## Lambda descriptions

### Lambda 1 — connect-shopify-stores _(hourly)_

Scans `ARTIST_STORES` (synced from a Google Sheet) for newly-added myshopify domains. Excludes stores already in the onboarding pipeline or registry, CRM-managed stores, Fansifter stores, and stores already physically synced to `SHOPIFY_STORES_GLOBAL` (a `SHOP` table exists) — the last guards against re-onboarding legacy or registry-deleted stores. Queues each new store to SQS and writes an initial row to `ECOMMERCE_STORE_REGISTRY` (`MYSHOPIFY_DOMAIN`, `SCHEMA_NAME`, `MERCH_COMPANY`, `SELLING_COUNTRY`, `IS_ACTIVE`, `ALT_MYSHOPIFY_DOMAIN`, `CUSTOM_DOMAIN`, `SOURCE`). This Lambda owns `IS_ACTIVE`; the sync Lambda's enrichment never touches it.

### Lambda 2 — onboard-shopify-stores _(SQS-triggered)_

For each store dequeued from SQS: creates a Fivetran connector for the myshopify domain, then issues a pre-signed Connect Card URL — a short-lived JWT that takes a store owner through the Fivetran OAuth / app installation flow using the connector details just created. Writes the full onboarding state record to `D2C_ONBOARDING_STATE` (`pending_oauth` status, connector ID, Connect Card URL and expiry, schema name, merch company, selling country).

### Lambda 3 — sync-shopify-data-connectors _(every 15 min)_

Sweeps all in-flight connectors and advances each one through the state machine:

- **`pending_oauth`** — polls Fivetran `setup_state`; if `connected`, writes `pending_configure` and returns (configure happens on the next sweep). If the Connect Card has expired, regenerates it. If OAuth is still incomplete 14 days after the connector was created, the store is marked `broken` and card regeneration stops.
- **`pending_configure`** — applies the table schema allowlist to the connector via the Fivetran schema config API, then triggers the initial sync. Writes `pending_sync` (or `sync_complete` directly if the `SHOP` table is already present). If the connector is `broken`, writes `broken`.
- **`pending_sync`** — checks `SHOPIFY_STORES_GLOBAL.INFORMATION_SCHEMA` for the store's `SHOP` table. Once present, resolves vendor ID and Global Participant from the store name, enriches `ECOMMERCE_STORE_REGISTRY`, and writes `sync_complete`. The destination schema is read live from the connector (`schema`), not the derived name in state, which can drift.

## Repository layout

```
lambda/
  connect-shopify-stores/       # lambda-d2c-connect-shopify-stores
  onboard-shopify-stores/       # lambda-d2c-onboard-shopify-stores
  sync-shopify-data-connectors/ # lambda-d2c-sync-shopify-data-connectors
```

Each Lambda is self-contained: `Dockerfile`, `pyproject.toml`, `uv.lock`, `config.py`, `src/`.

## Local development

### Prerequisites

- [Colima](https://github.com/abiosoft/colima) or Docker Desktop
- [uv](https://docs.astral.sh/uv/) — `brew install uv`
- Snowflake private key at `~/.ssh/snowflake/rsa_key.p8` (or adjust the mount path below)
- AWS SSO credentials exported in your shell

### Setup

```bash
cp lambda/connect-shopify-stores/.env.shadow lambda/connect-shopify-stores/.env
# fill in SNOWFLAKE_ACCOUNT, SNOWFLAKE_USER, SNOWFLAKE_ROLE, SNOWFLAKE_KEY_PASSPHRASE
```

Repeat for the other two Lambdas if needed.

### Build

```bash
colima start

docker build -t lambda-d2c-connect-shopify-stores:local lambda/connect-shopify-stores/
docker build -t lambda-d2c-onboard-shopify-stores:local lambda/onboard-shopify-stores/
docker build -t lambda-d2c-sync-shopify-data-connectors:local lambda/sync-shopify-data-connectors/
```

### Run and invoke

Start the container (substitute your actual private key host path):

```bash
docker run -p 9000:8080 \
  --env-file lambda/connect-shopify-stores/.env \
  -v ~/.ssh/snowflake/rsa_key.p8:/var/task/rsa_key.p8 \
  lambda-d2c-connect-shopify-stores:local
```

In a second terminal, invoke the handler:

```bash
curl -XPOST "http://localhost:9000/2015-03-31/functions/function/invocations" -d '{}'
```

Same pattern for the other two Lambdas — use the corresponding image name and `.env` file.

### Notes

- `ENVIRONMENT=local` bypasses Secrets Manager; Snowflake credentials are read from env directly.
- The Datadog extension will log API key errors locally — this is expected and harmless.
- `SQS_QUEUE_URL` can be left empty to test the Snowflake queries; the Lambda will fail at the SQS push step.
- `D2C_QA_ROLE` is a QA-only Snowflake role created by Terraform — use your personal role locally.

## Testing

Each Lambda has its own isolated test suite under `lambda/<name>/tests/`. Tests run via `uv run pytest` from inside the Lambda directory so that the correct `.venv` is used.

```bash
(cd lambda/connect-shopify-stores       && uv run pytest)
(cd lambda/onboard-shopify-stores       && uv run pytest)
(cd lambda/sync-shopify-data-connectors && uv run pytest)
```

To run all three in one go:

```bash
./scripts/test.sh
```

With coverage:

```bash
cd lambda/<name> && uv run pytest --cov=src --cov-report=term-missing
```

## Linting

Each Lambda has its own Ruff config in its `pyproject.toml`. Lint all Lambdas at once:

```bash
./scripts/lint.sh          # check only (no modifications)
./scripts/lint.sh --fix    # check + auto-fix + reformat
```

Or lint a single Lambda from its directory:

```bash
cd lambda/connect-shopify-stores
uv run ruff check --fix .
uv run ruff format .
```

## Updating dependencies

```bash
cd lambda/<name>
uv add <package>        # adds to pyproject.toml and updates uv.lock
# rebuild and re-push the Docker image after updating dependencies
```

## Deployment

Images are built and pushed to ECR by CI. Terraform in
`terraform-infra/ecommerce/qa/shopify-data-connector-onboarding/` manages the Lambda
functions, SQS queue, EventBridge rules, and IAM.