tuning-incremental-sync-config

What this skill does

# Tuning incremental sync config

A sync's configuration lives on the `ExternalDataSchema` and can be changed any time via
`external-data-schemas-partial-update`. Most changes are non-destructive (take effect on the next sync), but a few
(switching sync_type, changing primary keys) require careful handling to avoid corrupting the synced data.

## When to use this skill

- The user wants to change how an already-connected table is synced
- A diagnosis flagged the incremental field or primary key as wrong
- The table is syncing too often / not often enough
- Switching an incremental table to CDC (or vice versa)
- The source table was changed on the other side (new columns, dropped columns) and the sync config needs to catch up

If the user is setting up a brand-new source, use `setting-up-a-data-warehouse-source` instead — configuration is
chosen at creation time there.

## Available tools

| Tool                                                   | Purpose                                                                   |
| ------------------------------------------------------ | ------------------------------------------------------------------------- |
| `external-data-schemas-retrieve`                       | Current sync_type, incremental_field, PKs, sync_frequency                 |
| `external-data-schemas-incremental-fields-create`      | Refresh candidate incremental fields from the live source                 |
| `external-data-schemas-partial-update`                 | Apply the config change                                                   |
| `external-data-schemas-reload`                         | Trigger a sync with the new config                                        |
| `external-data-schemas-resync`                         | Wipe and re-import from scratch when the change invalidates existing data |
| `external-data-schemas-delete-data`                    | Drop the synced table while keeping the schema entry                      |
| `external-data-sources-check-cdc-prerequisites-create` | Pre-flight Postgres CDC (only when switching to/from CDC)                 |
| `external-data-sources-webhook-info-retrieve`          | Current webhook state (when switching to/from sync_type=webhook)          |
| `external-data-sources-create-webhook-create`          | Register a webhook after switching a schema to sync_type=webhook          |
| `external-data-sources-update-webhook-inputs-create`   | Rotate a webhook signing secret                                           |
| `external-data-sources-delete-webhook-create`          | Unregister webhook when switching schemas off sync_type=webhook           |

## The fields you can tune

From the partial-update endpoint:

| Field                    | Values                                                                                           | Notes                                                              |
| ------------------------ | ------------------------------------------------------------------------------------------------ | ------------------------------------------------------------------ |
| `sync_type`              | `full_refresh`, `incremental`, `append`, `cdc`, `webhook`                                        | Source must support the target type — check via incremental-fields |
| `incremental_field`      | Column name from the source                                                                      | Must appear in `incremental_fields` list for the schema            |
| `incremental_field_type` | `datetime`, `date`, `timestamp`, `integer`, `numeric`, `objectid`                                | Must match the column's real type                                  |
| `primary_key_columns`    | Array of column names                                                                            | Required for CDC. Used for upsert dedup on incremental             |
| `cdc_table_mode`         | `consolidated`, `cdc_only`, `both`                                                               | Only meaningful when sync_type=cdc                                 |
| `sync_frequency`         | `1min`, `5min`, `15min`, `30min`, `1hour`, `6hour`, `12hour`, `24hour`, `7day`, `30day`, `never` | Applies to all non-CDC types                                       |
| `sync_time_of_day`       | `HH:MM:SS`                                                                                       | When sync_frequency is daily/weekly-scale                          |
| `should_sync`            | `true` / `false`                                                                                 | Pause the schema without deleting it                               |

## Workflow

### Step 1 — Read the current config

Always start with `external-data-schemas-retrieve({id})`. Understanding the current state prevents mistakes like
"fixing" an incremental_field that's actually correct.

Note:

- Current `sync_type`, `incremental_field`, `incremental_field_type`, `primary_key_columns`
- Current `status` (don't tune a schema that's currently `Running` — wait or cancel first)
- `last_synced_at` (so you can tell if the next sync worked)
- `latest_error` if present (the error often tells you exactly what to change)

### Step 2 — If changing sync_type or incremental_field, refresh candidates

Call `external-data-schemas-incremental-fields-create({id})`. Even though the operation name says "create", it
re-reads the source and returns the current candidate fields — use it to confirm the field you want to set actually
exists on the source and which sync types are now available for this table.

The response:

```text
{
  "incremental_fields": [{"field": "updated_at", "type": "datetime", ...}, ...],
  "incremental_available": true,
  "append_available": true,
  "cdc_available": true,
  "full_refresh_available": true,
  "detected_primary_keys": ["id"],
  "available_columns": [...]
}
```

If your target `incremental_field` isn't in the list, tell the user — they need to either pick a different field or
change the source table to add one.

### Step 3 — Apply the change

Call `external-data-schemas-partial-update({id}, {...changed fields})`.

**Only send the fields that are actually changing.** Partial update means unspecified fields stay as they are.

Examples:

```json
// Switch from full_refresh to incremental
{
  "sync_type": "incremental",
  "incremental_field": "updated_at",
  "incremental_field_type": "datetime"
}

// Change sync frequency to hourly
{"sync_frequency": "1hour"}

// Fix wrong PK on a CDC table
{"primary_key_columns": ["tenant_id", "order_id"]}

// Pause a schema
{"should_sync": false}
```

### Step 4 — Decide whether existing data is still valid

This is the step that's easy to get wrong. Some config changes invalidate the synced data; others don't.

**Changes that DON'T invalidate existing data:**

- `sync_frequency`, `sync_time_of_day` — scheduling only
- `should_sync` — on/off
- `cdc_table_mode` in most cases — next sync will start writing to the new shape, but historical consolidated rows
  stay valid
- Switching between `incremental` and `full_refresh` with the same `incremental_field` — next sync just re-runs
  fresh
- Switching to or from `sync_type: "webhook"` — the synced data stays valid; only the ingestion path changes.
  Remember to register or unregister the webhook (see sections below) alongside the sync_type change.

**Changes that MAY invalidate existing data and need a resync:**

- Changing `incremental_field` to a different column — the high-water mark is from the old column and won't match.
  Without a resync you'll miss rows that were updated between the two fields' histories.
- Changing `primary_key_columns` — existing rows may be deduplicated incorrectly against new PK definitions.
- Switching from `full_refresh` to `append` — the existing rows don't have the version-history shape that append
  expects.
- Switching from `append` to `full_refresh` — opposite problem; you'll end up with