Canonical field-level schemas and source-to-target mapping toolkit for childcare system migrations

Most migration guides stop right where the real work begins. They tell you to "map your fields" and "clean your data," then hand you a checklist that assumes you already know what a clean target record looks like. You don't — because nobody ever writes down the canonical shape of a Family, an Enrollment, an Invoice, or an Attendance record. So every center invents it on the fly during the migration weekend, which is exactly when you have the least time to think clearly.

This piece fills that gap. It's the field-level companion to the broader childcare technology migration playbook — less about the project plan, more about the actual JSON, the ID strategy, the transform rules, and the reconciliation SQL you'll wish you'd defined before touching a single export.

The goal is a childcare canonical schema you can commit to a repo, hand to whoever's doing the ETL, and reconcile against afterward. Everything below is meant to be copied, adapted, and argued over with your team.

Why the "just map the fields" advice fails in practice

The pattern that repeats across almost every childcare migration that goes sideways: the old system exports 40 columns, the new system imports 55, and the person doing the mapping treats it like a spreadsheet puzzle — line up column A to column B, done.

1. 1
   The legacy system stored parent1name and parent2name as free text like "John & Mary Smith (Mary primary)".
2. 2
   Enrollment "status" in the old system had values like Active, active, ACT, Wait, WL, Withdrew-2023 — no controlled list.
3. 3
   Invoices carried a running balance but no line items, so you can't rebuild what a charge was actually for.
4. 4
   Attendance existed as daily check-in/out timestamps in one system and as billed "units" in another.

None of that is a column-alignment problem. It's a schema definition problem. Without a canonical target shape, each ambiguity gets resolved differently by whoever happens to touch that row. That's how you end up with a family that has two profiles, an invoice that doesn't tie to an enrollment, and an attendance report that doesn't match what you billed.

The fix is to define the target first — fully, with types and required flags — and force every source to conform to it. Not the other way around.

The four core entities and how they relate

Before the schemas, get the relationships straight, because the ID strategy depends on them:

1. 1
   A Family is the billing and contact unit. It has one or more guardians and one or more children.
2. 2
   An Enrollment ties one child to one program/room for a date range at a specific rate. A child can have multiple enrollments over time.
3. 3
   An Invoice belongs to a family, references one or more enrollments, and carries line items.
4. 4
   Attendance records belong to a child (and by extension an enrollment), captured as discrete check-in/out events.

One mistake that comes up constantly: teams attach invoices to children instead of families, then can't produce a single family statement when a parent asks why they were charged $1,840 in March. Bill at the family level, allocate at the child/enrollment level.

Canonical JSON Schemas

These are deliberately minimal-but-strict. Required fields are the ones you cannot reconcile without. Everything else is optional but typed.

Family

``json { "$schema": "http://json-schema.org/draft-07/schema#", "title": "Family", "type": "object", "required": ["familyid", "sourcesystem", "sourceid", "primaryguardian", "status"], "properties": { "familyid": { "type": "string", "format": "uuid" }, "sourcesystem": { "type": "string" }, "sourceid": { "type": "string" }, "status": { "type": "string", "enum": ["active", "inactive", "prospect", "withdrawn"] }, "primaryguardian": { "type": "object", "required": ["firstname", "lastname"], "properties": { "firstname": { "type": "string" }, "lastname": { "type": "string" }, "email": { "type": "string", "format": "email" }, "phone": { "type": "string" } } }, "secondaryguardians": { "type": "array", "items": { "type": "object" } }, "children": { "type": "array", "items": { "type": "object", "required": ["childid", "firstname", "lastname", "dob"], "properties": { "childid": { "type": "string", "format": "uuid" }, "firstname":{ "type": "string" }, "lastname": { "type": "string" }, "dob": { "type": "string", "format": "date" }, "sourceid": { "type": "string" } } } }, "billingaddress": { "type": "object" }, "createdat": { "type": "string", "format": "date-time" } } } ``

Enrollment

``json { "$schema": "http://json-schema.org/draft-07/schema#", "title": "Enrollment", "type": "object", "required": ["enrollmentid", "childid", "program", "status", "startdate", "sourcesystem", "sourceid"], "properties": { "enrollmentid": { "type": "string", "format": "uuid" }, "childid": { "type": "string", "format": "uuid" }, "familyid": { "type": "string", "format": "uuid" }, "program": { "type": "string" }, "room": { "type": "string" }, "status": { "type": "string", "enum": ["enrolled", "waitlisted", "withdrawn", "graduated", "pending"] }, "scheduletype": { "type": "string", "enum": ["fulltime", "parttime", "dropin"] }, "startdate": { "type": "string", "format": "date" }, "enddate": { "type": ["string", "null"], "format": "date" }, "baserate": { "type": "number" }, "rateperiod": { "type": "string", "enum": ["weekly", "monthly", "daily"] }, "sourcesystem": { "type": "string" }, "sourceid": { "type": "string" } } } ``

Invoice

``json { "$schema": "http://json-schema.org/draft-07/schema#", "title": "Invoice", "type": "object", "required": ["invoiceid", "familyid", "issuedate", "status", "totalamount", "sourcesystem", "sourceid"], "properties": { "invoiceid": { "type": "string", "format": "uuid" }, "familyid": { "type": "string", "format": "uuid" }, "issuedate": { "type": "string", "format": "date" }, "duedate": { "type": "string", "format": "date" }, "status": { "type": "string", "enum": ["draft", "open", "paid", "partial", "void", "writtenoff"] }, "totalamount": { "type": "number" }, "amountpaid": { "type": "number" }, "currency": { "type": "string", "default": "USD" }, "lineitems": { "type": "array", "items": { "type": "object", "required": ["description", "amount"], "properties": { "enrollmentid": { "type": ["string", "null"], "format": "uuid" }, "childid": { "type": ["string", "null"], "format": "uuid" }, "description": { "type": "string" }, "quantity": { "type": "number" }, "unitamount": { "type": "number" }, "amount": { "type": "number" }, "code": { "type": "string" } } } }, "sourcesystem": { "type": "string" }, "source_id": { "type": "string" } } } ``

Attendance

``json { "$schema": "http://json-schema.org/draft-07/schema#", "title": "Attendance", "type": "object", "required": ["attendanceid", "childid", "date", "sourcesystem", "sourceid"], "properties": { "attendanceid": { "type": "string", "format": "uuid" }, "childid": { "type": "string", "format": "uuid" }, "enrollmentid": { "type": ["string", "null"], "format": "uuid" }, "date": { "type": "string", "format": "date" }, "checkin": { "type": ["string", "null"], "format": "date-time" }, "checkout": { "type": ["string", "null"], "format": "date-time" }, "checkedinby": { "type": "string" }, "checkedoutby":{ "type": "string" }, "absencereason":{ "type": ["string", "null"] }, "sourcesystem": { "type": "string" }, "sourceid": { "type": "string" } } } ``

sourcesystem and sourceid are required on every entity. That's not optional bookkeeping — it's the backbone of your reconciliation and rollback strategy. More on that below.

Example payloads you can test against

A Family record with two children and a secondary guardian:

``json { "familyid": "b3f1c8e2-4a1d-4f9a-9c2e-1a2b3c4d5e6f", "sourcesystem": "legacyprocare", "sourceid": "FAM-40182", "status": "active", "primaryguardian": { "firstname": "Mara", "lastname": "Ellison", "email": "mara.ellison@example.com", "phone": "555-0142" }, "secondaryguardians": [ { "firstname": "Devon", "lastname": "Ellison", "phone": "555-0198" } ], "children": [ { "childid": "8a7b...", "firstname": "Noah", "lastname": "Ellison", "dob": "2021-03-14", "sourceid": "CHD-88121" }, { "childid": "9c2d...", "firstname": "Ivy", "lastname": "Ellison", "dob": "2023-07-02", "sourceid": "CHD-88122" } ], "created_at": "2022-08-01T00:00:00Z" } ``

An Invoice with itemized lines tied back to enrollments:

``json { "invoiceid": "c1a2...", "familyid": "b3f1c8e2-4a1d-4f9a-9c2e-1a2b3c4d5e6f", "issuedate": "2024-03-01", "duedate": "2024-03-05", "status": "partial", "totalamount": 1840.00, "amountpaid": 920.00, "lineitems": [ { "enrollmentid": "e11...", "childid": "8a7b...", "description": "Toddler FT - March", "quantity": 1, "unitamount": 1120.00, "amount": 1120.00, "code": "TUITFT" }, { "enrollmentid": "e22...", "childid": "9c2d...", "description": "Infant PT - March", "quantity": 1, "unitamount": 780.00, "amount": 780.00, "code": "TUITPT" }, { "enrollmentid": null, "childid": "9c2d...", "description": "Sibling discount", "quantity": 1, "unitamount": -60.00, "amount": -60.00, "code": "DISCSIB" } ], "sourcesystem": "legacyprocare", "sourceid": "INV-772014" } ``

If your test payloads don't validate against the schema before you run the real migration, you don't have a migration — you have a hope.

Source-to-target mapping tables

This is where most of the actual decisions live. A mapping table isn't just "old column → new column." It carries the transform logic and the code-table translation. Here's a representative Enrollment mapping:

And the status code-table translation it references:

The single most important row in any code table is the last one. Every controlled field needs an explicit "everything I didn't anticipate lands here and gets flagged" rule. Migrations break when unmatched values silently default to something plausible-but-wrong — like a withdrawn family quietly becoming enrolled and showing up on tomorrow's ratio count.

The deterministic ID strategy (this is the part people skip)

Random UUIDs feel fine until you have to re-run the migration. Then every entity gets a new ID, nothing ties back to the previous run, and reconciliation becomes archaeology.

Use deterministic, namespaced UUIDs (v5) instead. Derive the UUID from a fixed namespace plus a stable source key, so the same source record always produces the same target ID — no matter how many times you run it.

``python import uuid # One fixed namespace per center, generated once and stored CENTERNS = uuid.UUID("6f1a...c9de") def canonicalid(entitytype, sourcesystem, sourceid): name = f"{entitytype}|{sourcesystem}|{sourceid}" return str(uuid.uuid5(CENTERNS, name)) # Same inputs → same UUID, every run canonicalid("family", "legacy_procare", "FAM-40182") ``

Three rules that make this actually work:

1. 1
   Persist sourcesystem + sourceid on every target record. This is your permanent bridge back to the old system. When a parent disputes a charge from before the migration, you can trace it in seconds.
2. 2
   Keep a separate ID map table (sourcesystem, sourceid, entitytype, canonicalid) that you write during migration. Deterministic UUIDs mean you can regenerate it, but persisting it lets you reconcile without recomputing.
3. 3
   Namespace per center, not globally. Two centers using the same legacy vendor will have overlapping source_ids like FAM-1. Distinct namespaces keep them from colliding if you ever consolidate.

Here's the canonical_id workflow illustrated.

The payoff shows up on re-runs. If your first migration pass has a bug in the invoice logic, you fix it and re-run just invoices — every invoice_id regenerates identically, so relationships stay intact and nothing gets orphaned.

De-duplication and matching rules

Childcare data is full of duplicates — mostly from families who left and came back, or staff who created a second profile because they couldn't find the first one. Don't try to auto-merge everything. Use tiered thresholds with a human in the loop for the ambiguous middle.

1. 1
   Auto-match (high confidence)

   exact match on normalized guardian email or normalized phone and a child DOB match. Merge automatically, keep both source_ids.

2. 2
   Manual review (medium confidence)

   last name match + child DOB match, but email/phone differ. Flag for a human. This catches divorced-parent situations where the same child legitimately spans two "families."

3. 3
   No match (low confidence)

   name similarity only. Treat as distinct; do not merge.

Simple scoring in pseudocode:

`` score = 0 if normalized(emaila) == normalized(emailb): score += 50 if normalized(phonea) == normalized(phoneb): score += 40 if childdobmatch(a, b): score += 30 if lastnamefuzzy(a, b) > 0.9: score += 10 if score >= 70: automerge() elif score >= 40: queueforreview() else: keepseparate() ``

“

Route flagged pairs to a director with both records shown side-by-side and a one-click decision to minimize review friction.

The manual-review workflow matters more than the algorithm. Route flagged pairs to your director or lead admin — someone who actually knows the families — with both records shown side by side and a one-click "same family / different families" decision. On a 120-child center, expect somewhere in the range of 15–40 pairs to review. That's a couple of hours of work that prevents duplicate billing and the awkward "why do you have two accounts for us?" conversation.

Automated reconciliation: prove the migration actually worked

Once the migration finishes, you need to answer one question with data, not gut feel: did everything come across, and does it add up? Build these checks before go-live, not after someone notices a missing invoice.

Row-count reconciliation — the first, dumbest, most important check:

``sql -- Compare counts by entity and status SELECT 'source' AS side, status, COUNT() FROM sourceenrollments GROUP BY status UNION ALL SELECT 'target' AS side, status, COUNT() FROM targetenrollments GROUP BY status ORDER BY status, side; ``

Financial reconciliation — the one that saves you from angry parents:

``sql -- Every source family's outstanding balance must match target SELECT s.sourceid, s.balance AS sourcebalance, t.balance AS targetbalance, (t.balance - s.balance) AS delta FROM sourcefamilybalances s JOIN idmap m ON m.sourceid = s.sourceid AND m.entitytype = 'family' JOIN targetfamilybalances t ON t.familyid = m.canonical_id WHERE ABS(t.balance - s.balance) > 0.01; ``

Any row that comes back is a discrepancy you must resolve before sending a single statement. A delta of even a few dollars usually means a discount or credit didn't map — and if it happened once, it happened on dozens of accounts.

A reconciliation CSV template your team can fill and track:

`` entity,sourceid,canonicalid,checktype,sourcevalue,target_value,delta,status family,FAM-40182,b3f1...,balance,920.00,920.00,0.00,ok family,FAM-40185,c2a2...,balance,1340.00,1280.00,-60.00,review enrollment,ENR-771,e11...,status,enrolled,pending,,review ``

Run reconciliation as a checklist, in this order:

1. 1
   Row counts by entity and status (catches wholesale drops)
2. 2
   Referential integrity — every enrollment resolves to a child, every invoice to a family
3. 3
   Financial totals — family balances, total AR, sum of open invoices
4. 4
   Attendance spot-check — reconcile billed units against attendance records for a sample month
5. 5
   Duplicate scan on the target — confirm your de-dup actually reduced count
6. 6
   Required-field completeness — no null in any required schema field

Don't call the migration done until every item on that list returns a clean result or has an explicit sign-off explaining why a delta is acceptable.

Where this approach makes sense — and where it's overkill

When it's worth the effort: you're moving more than roughly 50 families, you have real AR on the books, or you're consolidating multiple sites into one system. Anytime money and licensing-relevant records are involved, the schema-first, reconcile-everything approach pays for itself the first time a parent disputes a balance.

When it's overkill: a brand-new center with a handful of kids and no billing history. Just enter the data by hand and move on. Building UUID namespacing and reconciliation SQL for two dozen records is engineering theater.

Who should not run this themselves: if nobody on your team can read the SQL above or validate a JSON payload, don't wing it. Either bring in someone who can, or lean on a platform where the canonical model and reconciliation are already built in. The failure mode isn't "the migration is slow" — it's "the migration looks done and is quietly wrong," which is far more expensive to unwind six months later.

A short real scenario

A two-site preschool group, roughly 140 children combined, moved off a legacy desktop system where each site had been entered independently. Because both sites used the same vendor, source IDs overlapped and 9 families with kids at both locations existed as four separate profiles.

Their first migration attempt used random UUIDs and no reconciliation. Two weeks after go-live, three families were billed twice for the same child, and total AR in the new system was off by around $2,600 versus the old ledger — nobody could explain where the gap came from.

The redo used the deterministic ID strategy, per-site namespacing, and the financial reconciliation query above. The re-run flagged 22 family pairs for manual review (the director cleared them in an afternoon) and surfaced 14 invoices where a sibling discount hadn't mapped — the source of most of that $2,600 gap. After reconciliation passed, the AR difference closed to under $20, all explainable rounding.

The difference wasn't better software. It was defining the target schema first and refusing to call the migration done until the numbers tied out.

Bringing it together

The reason childcare migrations quietly go wrong is almost never the tool you're migrating to. It's that the target shape was never written down, unmatched code values defaulted to something wrong, IDs weren't stable across re-runs, and nobody reconciled the money afterward. Every one of those is preventable with artifacts you can define in advance.

Start by committing your childcare canonical schema to a repo. Build your mapping tables with an explicit "everything else gets flagged" row. Generate deterministic UUIDs and persist your source IDs permanently. Then don't call the migration done until the reconciliation SQL returns zero unexplained deltas. If you want the wider project-level view of sequencing, cutover, and rollback, pair this with the childcare technology migration playbook — this toolkit is the field-level detail that playbook assumes you already have.

Start by committing your childcare canonical schema to a repo. Build your mapping tables with an explicit "everything else gets flagged" row. Generate deterministic UUIDs and persist your source IDs permanently. Then don't call the migration done until the reconciliation SQL returns zero unexplained deltas. If you want the wider project-level view of sequencing, cutover, and rollback, pair this with the childcare technology migration playbook — this toolkit is the field-level detail that playbook assumes you already have.