# Fivetran Snowflake Sync Setup

Create the `fivetran` MySQL user on the ows-coda Aurora cluster so Fivetran can replicate data to Snowflake via binlog CDC.

## Prerequisites

- VPN connected (RDS is in a private VPC)
- Master password reset (see [RDS Access](rds-access.md), steps 1-2)
- **SSH proxy allowlist updated** — the ows-coda Aurora cluster endpoint must be listed in `terraform-infra/prod/docker-ssh-proxies/fivetran-rds-proxy/variables.tf` under `rds_instances`. Fivetran connects through an SSH tunnel (`fivetran.theorchard.io:2222`), and the proxy only forwards to explicitly allowlisted RDS endpoints. Add and apply before proceeding:

  ```
  "prod-ows-coda.cluster-cb22xqmk0y0q.us-east-1.rds.amazonaws.com:3306",
  ```

  Without this, the connector will fail with `Schema with name 'coda' not found in source` because it can't reach the database at all.

## 1. Generate the fivetran user password

```bash
openssl rand -base64 30 | tr -dc 'A-Za-z0-9!@#$%^&*' | head -c 20; echo
```

Store this in 1Password under the shared Coda vault. Fivetran will need it when configuring the connector.

## 2. Log in as master and create the user

```bash
mysql -h qa-ows-coda-db.theorchard.io -u coda_admin -p
```

```sql
CREATE USER 'fivetran'@'%' IDENTIFIED BY '<password-from-step-1>';
GRANT SELECT, REPLICATION SLAVE, REPLICATION CLIENT ON *.* TO 'fivetran'@'%';
GRANT EXECUTE ON PROCEDURE mysql.rds_kill TO 'fivetran'@'%';
GRANT SELECT ON mysql.rds_heartbeat2 TO 'fivetran'@'%';
GRANT SELECT ON mysql.rds_configuration TO 'fivetran'@'%';
FLUSH PRIVILEGES;
```

> `REPLICATION SLAVE` and `REPLICATION CLIENT` are global grants — they cannot be scoped to a single database. `SELECT` is needed for the initial full-table sync before CDC kicks in. The `rds_kill`, `rds_heartbeat2`, and `rds_configuration` grants are required by Fivetran for binlog replication on RDS/Aurora — `rds_kill` terminates stale replication connections, `rds_heartbeat2` tracks binlog position, and `rds_configuration` reads RDS-specific replication config.

## 3. Verify the user

```bash
mysql -h qa-ows-coda-db.theorchard.io -u fivetran -p coda
```

```sql
SHOW GRANTS FOR 'fivetran'@'%';
SHOW MASTER STATUS;
SELECT 1 FROM mysql.rds_heartbeat2 LIMIT 1;
```

All three should succeed. `SHOW MASTER STATUS` confirms binlog access; the `rds_heartbeat2` query confirms the RDS-specific grants are in place.

## 4. Apply the Terraform connector

The Fivetran connector is defined in `terraform-infra/qa/fivetran/ows-coda/`. The schema config resource requires Fivetran to have already connected and discovered the source schemas, so **apply in two steps**:

```bash
cd ../terraform-infra/qa/fivetran/ows-coda
terraform init

# Step 1: Create the connector and schedule only
terraform apply \
  -target=fivetran_connector.qa_ows_coda_connector \
  -target=fivetran_connector_schedule.qa_ows_coda_connector_schedule
```

After step 1, set the password in the Fivetran dashboard (see step 5) and wait for the connector status to show **Connected**.

```bash
# Step 2: Apply the schema config (after connector is connected)
terraform apply
```

If you run a single `terraform apply` for everything at once, the schema config will fail with `Schema with name 'coda' not found in source` because the connector hasn't completed schema discovery yet.

## 5. Set the password in Fivetran

After step 4's first `terraform apply` creates the connector, open it in the Fivetran dashboard and enter the MySQL password from step 1. Save and test the connection. The connector must show **Connected** before running the second `terraform apply` in step 4.

## 6. Trigger the initial sync

Trigger the first sync manually from the Fivetran dashboard. The initial sync does a full-table copy; subsequent syncs use binlog CDC.

Monitor the sync in Fivetran's logs. Common issues:

- **Connection refused** — check that the RDS security group allows ingress from the SSH tunnel jump box
- **Access denied for replication** — verify the `GRANT` from step 2 was applied correctly
- **Schema with name `coda` not found in source** — the connector hasn't completed schema discovery; follow the two-step apply in step 4, and ensure the password is set and the connector shows **Connected** in Fivetran before applying the schema config
- **Table not found** — all Prisma models use `@@map` to snake_case table names (e.g. `User` → `users`, `MessageToolCall` → `message_tool_calls`); the `variables.tf` table list must use the mapped MySQL names, not the PascalCase model names

## Environments

| Environment | Host                             | Terraform path                           |
| ----------- | -------------------------------- | ---------------------------------------- |
| **QA**      | `qa-ows-coda-db.theorchard.io`   | `terraform-infra/qa/fivetran/ows-coda`   |
| **Prod**    | `prod-ows-coda-db.theorchard.io` | `terraform-infra/prod/fivetran/ows-coda` |

Repeat steps 1-5 for each environment. Production requires the production AWS profile and team coordination in `#abacus-devs`.