data-migrate

name	data-migrate
description	Move records from an external source (CSV, JSON, SQL dump, or API response) into a live FileMaker solution via OData. Reads the source data, maps source columns to FM fields with type coercion, gets developer approval on the mapping, then executes the migration with error tracking. Use when the developer asks to "migrate data", "import records", "move data into FileMaker", "load CSV", or "import JSON".
compatibility	Requires OData access to a hosted FileMaker Server solution.

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."