petrowsky/data-migrate

data-migrate

Move records from an external data source into a live FileMaker solution via OData POST requests. Handles field mapping, type coercion, referential integrity, and error reporting.

---

Step 1: Resolve OData configuration

Read agent/config/automation.json and identify the target solution:

1. If agent/CONTEXT.json exists, read CONTEXT.json["solution"] to get the solution name.
2. Look up automation.json["solutions"][solution_name]["odata"] for the OData credentials and base URL.
3. If no odata block exists for the solution, stop and inform the developer:

OData is not configured for this solution. Add an odata block to agent/config/automation.json under solutions.{solution name} with base_url, database, username, password, and script_bridge. See CLAUDE.md for the structure.

---

Step 2: Read and parse the source data

The developer provides a file path or describes the data source. Auto-detect the format:

CSV

• Read the file and detect the delimiter (comma, tab, semicolon, pipe).
• Detect encoding: try UTF-8 first, fall back to Latin-1/ISO-8859-1 if UTF-8 decoding fails.
• Parse the header row for column names.
• Parse all data rows.
• Report: number of rows, column names, sample values (first 3 rows).

JSON

• Read the file as UTF-8.
• Detect structure:
  • Array of objects: [ { "field": "value" }, ... ] -- each object is a record.
  • Object with array property: { "data": [ ... ] } or { "records": [ ... ] } -- unwrap the array.
  • Newline-delimited JSON (NDJSON): one JSON object per line.
• Parse all records.
• Report: number of records, field names, sample values (first 3 records).

SQL dump

• Parse INSERT INTO statements to extract table name, column names, and values.
• Handle quoted identifiers and escaped values.
• Report: target table, number of rows, column names.

Other formats

If the format is not recognized, ask the developer to describe the structure or convert to CSV/JSON.

---

Step 3: Discover the target schema

Use the same schema discovery as data-seed (Step 2), in order of preference:

1. CONTEXT.json -- tables and fields with types
2. Index files -- agent/context/{solution}/fields.index
3. OData $metadata -- fetch and parse EntityType elements

Identify the target table. If the developer has not specified which FM table to import into:

• If the source file name or SQL table name matches an FM table, suggest it.
• Otherwise, list available tables and ask.

---

Step 4: Build and present the field mapping

For each source column, propose a mapping to an FM field:

Auto-mapping rules

1. Exact name match (case-insensitive): source column "Email" maps to FM field "Email".
2. Normalized match: strip spaces, underscores, hyphens and compare (e.g., "first_name" matches "FirstName").
3. Common aliases: "fname"/"first" -> "FirstName", "lname"/"last" -> "LastName", "amt" -> "Amount", "qty" -> "Quantity", "desc" -> "Description".

Fields to exclude from mapping

• PrimaryKey -- auto-generated by FM (unless the developer explicitly wants to preserve source IDs)
• Global fields -- read-only via OData
• Container fields -- binary data, not supported via OData
• Calculation fields -- read-only
• Summary fields -- read-only
• CreationTimestamp / ModificationTimestamp -- auto-enter
• CreatedBy / ModifiedBy -- auto-enter

Type coercion rules

| Source type | FM field type | Coercion | |------------|---------------|----------| | String number ("42.5") | Number | Parse as float | | ISO date ("2025-01-15") | Date | Reformat to MM/DD/YYYY | | European date ("15/01/2025") | Date | Detect DD/MM/YYYY and reformat to MM/DD/YYYY | | ISO timestamp ("2025-01-15T10:30:00") | Timestamp | Reformat to MM/DD/YYYY HH:MM:SS | | Unix timestamp (1705334400) | Timestamp | Convert to MM/DD/YYYY HH:MM:SS | | Boolean (true/false, yes/no, 1/0) | Number | Convert to 1 or 0 | | NULL / empty | Any | Omit the field from the POST body (let FM use default) | | String | Number | Attempt parse; if not numeric, log warning and skip field for that record |

Present the mapping

Display a mapping table for developer review:

Field mapping: source -> {Table Name}

| # | Source Column | FM Field | Type Coercion | Notes | |---|-------------|----------|---------------|-------| | 1 | first_name | FirstName | none | exact match | | 2 | last_name | LastName | none | normalized match | | 3 | email | Email | none | exact match | | 4 | phone_number | Phone | none | normalized match | | 5 | signup_date | DateCreated | ISO -> MM/DD/YYYY | date format conversion | | 6 | is_active | Active | true/false -> 1/0 | boolean conversion | | 7 | legacy_id | -- | UNMAPPED | no matching FM field | | 8 | -- | PrimaryKey | SKIPPED | auto-enter serial | | 9 | -- | CreationTimestamp | SKIPPED | auto-enter |

Source records: 247 Unmapped source columns: legacy_id Duplicate handling: (not yet specified)

Please review and confirm, or specify changes (e.g., "map legacy_id to ExternalID", "skip phone_number").

---

Step 5: Get developer approval

Wait for the developer to confirm or adjust the mapping. Key decisions to resolve:

1. Unmapped columns: Should any unmapped source columns map to FM fields? Should they be ignored?
2. Duplicate handling: What to do if a record with the same key already exists?
   • Skip -- check for existing record first, skip if found
   • Error -- fail and log the duplicate
   • No check -- just create the record (fastest, may create duplicates)
3. ForeignKey resolution: If importing child records, how to resolve foreign keys?
   • Source data contains the FK values that match existing FM PrimaryKeys
   • Source data uses a different identifier -- need a lookup step
   • Parent records are being migrated in the same session -- capture PKs as they are created

Do not proceed until the developer has confirmed the mapping.

---

Step 6: Execute the migration

Pre-flight checks

1. Verify OData connectivity with a simple GET request.
2. If duplicate checking is enabled, determine the lookup field (usually PrimaryKey or a unique business key).

Migration order for related tables

If migrating multiple related tables:

1. Identify parent-child relationships from the schema.
2. Migrate parent tables first.
3. Capture PrimaryKey values from created parent records.
4. Use captured PKs as ForeignKey values when migrating child tables.

Record creation loop

For each source record:

1. Apply the field mapping -- rename source columns to FM field names.
2. Apply type coercion -- convert values according to the coercion rules.
3. Skip NULL/empty fields (omit from POST body).
4. If duplicate checking is enabled, query for existing record:

   GET {base_url}/{database}/{table}?$filter={lookupField} eq '{value}'&$top=1
   

   If found, handle according to the developer's duplicate policy.
5. POST the record:

   POST {base_url}/{database}/{TableOccurrenceName}
   Authorization: Basic <base64(username:password)>
   Content-Type: application/json
   
   {
     "FieldName1": "coerced_value1",
     "FieldName2": 42
   }
   

6. On success, capture the PrimaryKey from the response (needed for child table FK wiring).
7. On failure, log the error with the source row number, status code, and response body.

Chunking and progress

• Process records in chunks of 25.
• After each chunk, report progress: "{N} of {total} records processed ({success} created, {failed} failed)".
• If the failure rate within a chunk exceeds 50%, pause and report the error pattern to the developer before continuing.

Error handling

• 401 Unauthorized -- stop immediately, credentials are wrong.
• 404 Not Found -- table occurrence name is wrong, stop and report.
• 400 Bad Request -- log the specific field/value that caused the error. Continue with next record.
• 500 Server Error -- FM validation rule failure. Log and continue.
• 429 Too Many Requests -- wait and retry with exponential backoff.
• Network timeout -- retry once, then log and continue.

---

Step 7: Report results

Present a migration summary:

Migration complete: {source file} -> {Table Name}

| Metric | Count | |--------|-------| | Source records | 247 | | Created | 241 | | Skipped (duplicate) | 3 | | Failed | 3 |

Field mapping used: | Source | FM Field | Coercions applied | |--------|----------|-------------------| | first_name | FirstName | 0 | | signup_date | DateCreated | 241 (ISO -> MM/DD/YYYY) | | is_active | Active | 241 (bool -> number) |

Errors (3):

• Row 42: 400 Bad Request -- field "Amount" value "N/A" is not a valid number
• Row 118: 500 Server Error -- "Field validation failed for Status"
• Row 203: 400 Bad Request -- field "Amount" value "TBD" is not a valid number

Recommendation: Rows 42 and 203 have non-numeric values in the Amount column. Clean the source data and re-run for those rows, or manually enter them in FileMaker.

If any failed records share a common pattern (e.g., all failures are due to the same field), highlight that pattern and suggest a fix.

---

Key considerations

• Encoding: Always try UTF-8 first for source files. If decoding fails, try Latin-1. If the developer knows the encoding, accept it as input.
• Large datasets: For files with 1000+ records, warn the developer that the migration will take time (roughly 1-2 seconds per record via individual OData POSTs). Suggest breaking into batches if the developer wants to do a partial test first.
• Date ambiguity: Dates like "01/02/2025" are ambiguous (Jan 2 or Feb 1). If the source data appears to use DD/MM/YYYY format (values > 12 in the first position), ask the developer to confirm the date format before proceeding.
• Empty rows: Skip entirely empty rows in CSV files without counting them as errors.
• Header variations: Handle BOM (byte order mark) in CSV files -- strip the BOM from the first column name if present.
• Preserving source IDs: If the developer wants to preserve a source ID (e.g., for cross-referencing), map it to a dedicated FM field rather than PrimaryKey (which should remain auto-generated).

---

Examples

Example 1 -- Simple CSV import

User: "Import clients.csv into the Clients table"

1. Read automation.json -- OData configured
2. Read clients.csv -- 50 rows, columns: name, email, phone, city, state
3. Fetch FM schema -- Clients table has: PrimaryKey, Name, Email, Phone, City, State, CreationTimestamp
4. Auto-map: name->Name, email->Email, phone->Phone, city->City, state->State. Skip PrimaryKey, CreationTimestamp.
5. Present mapping, developer confirms
6. POST 50 records, all succeed
7. Report: 50/50 created, 0 errors

Example 2 -- JSON with related tables

User: "Migrate orders.json -- it has orders and line items"

1. Read orders.json -- contains { "orders": [...], "line_items": [...] }
2. Map orders to Invoices table, line_items to LineItems table
3. Present both mappings with FK resolution plan: create Invoices first, capture PKs, wire LineItems ForeignKeyInvoice
4. Developer confirms
5. Migrate Invoices (parent), then LineItems (child) with captured PKs
6. Report per-table results

Example 3 -- Duplicate handling

User: "Import updated client list, skip existing clients"

1. Read source data
2. Present mapping with duplicate check on Email field
3. Developer confirms: skip duplicates based on Email
4. For each record, GET by Email first. If found, skip. If not, POST.
5. Report: 200 source records, 150 created, 50 skipped (already exist)

Example 4 -- OData not configured

User: "Import this CSV into FileMaker"

1. Read automation.json -- no odata block
2. Report: "OData is not configured. Add an odata block to automation.json for this solution to enable data migration."