If you've ever sat in a migration kickoff call where the new vendor says "just export your data and we'll import it," you already know what's coming. The export lands as a 14-column CSV with sibling relationships encoded in a free-text notes field, a Discount column that says "10% + referral" for one family and "$50 off" for another, and birth dates in three different formats depending on which staff member typed them in. That's how a project schedule slips by two weeks before you've moved a single record.
This post gives you the thing that prevents that: a canonical schema your data actually conforms to, published as machine-readable artifacts (JSON Schema + OpenAPI snippets) so your engineers aren't guessing, plus concrete source→canonical mapping tables with transformation rules. The earlier childcare technology migration playbook covered the operational side of not breaking your center during a cutover. This one is narrower and more technical — it's about the childcare canonical schema itself and the field-level mechanics of getting messy source data into it cleanly.
Scope is deliberately tight: four entities — Families, Enrollments, Invoices, Attendance — plus the mapping template. That's it.
Why a canonical schema beats direct source-to-target mapping
The instinct on most migrations is to map old system columns straight to new system columns. That works fine until you have a second source. The moment you're consolidating two acquired sites on different platforms, or pulling attendance from a sign-in kiosk while pulling billing from an accounting tool, direct mapping multiplies into a maintenance nightmare: N sources × M targets.
A canonical schema sits in the middle. Every source maps into canonical. The target system maps out of canonical once. Teams that define a canonical layer first tend to finish discovery faster, because arguments about "what does status = 3 mean" happen once, against a documented standard, instead of repeatedly against a moving target.
The model below is intentionally opinionated about three things that cause the most pain: identity (how you key a child across systems), time (timezones and DOB), and money (how discounts and pro-rations are represented). Everything else is secondary.
The four canonical entities
Here's the shape. JSON Schema for each, then the required/optional breakdown, then example records. These are drafted against JSON Schema draft 2020-12 and are directly reusable as OpenAPI components/schemas entries.
Simplify your childcare center’s daily operations.
Totsyly helps you manage enrollments, staff, and communications efficiently so you can focus on care.
- Streamlined enrollment & waitlist management
- Automated billing & payment tracking
- Staff scheduling & attendance monitoring
No credit card required
Family
``json
{
"$schema": "https://json-schema.org/draft/2020-12/schema",
"$id": "https://canonical.example.com/schemas/family.json",
"title": "Family",
"type": "object",
"required": ["familyid", "primarycontact", "children"],
"properties": {
"familyid": {
"type": "string",
"description": "Stable canonical ID. Never reuse source PKs directly; see mapping rules.",
"pattern": "^fam[A-Za-z0-9]{8,}$"
},
"primarycontact": {
"type": "object",
"required": ["fullname", "email"],
"properties": {
"fullname": { "type": "string", "minLength": 1 },
"email": { "type": "string", "format": "email" },
"phonee164": { "type": "string", "pattern": "^\\+[1-9]\\d{6,14}$" }
}
},
"secondarycontacts": {
"type": "array",
"items": { "$ref": "#/properties/primarycontact" },
"default": []
},
"children": {
"type": "array",
"minItems": 1,
"items": {
"type": "object",
"required": ["childid", "firstname", "lastname", "dob"],
"properties": {
"childid": { "type": "string", "pattern": "^chd[A-Za-z0-9]{8,}$" },
"firstname": { "type": "string", "minLength": 1 },
"lastname": { "type": "string", "minLength": 1 },
"dob": {
"type": "string",
"format": "date",
"description": "ISO 8601 date, no time, no timezone. 2021-03-14."
},
"siblinggroup_id": {
"type": ["string", "null"],
"description": "Shared across children in the same family for discount logic."
}
}
}
}
}
}
``
Required: familyid, primarycontact (with fullname, email), children (at least one, each with childid, firstname, lastname, dob).
Optional: secondarycontacts, phonee164, siblinggroupid.
The siblinggroupid matters more than it looks. It's the hook your billing logic uses for sibling discounts. If you leave sibling relationships implicit — "same last name, same address" — you'll mis-apply discounts constantly. Step-siblings, hyphenated names, and two unrelated kids who happen to share a surname all break that heuristic.
Enrollment
``json
{
"$schema": "https://json-schema.org/draft/2020-12/schema",
"$id": "https://canonical.example.com/schemas/enrollment.json",
"title": "Enrollment",
"type": "object",
"required": ["enrollmentid", "childid", "program", "startdate", "status"],
"properties": {
"enrollmentid": { "type": "string", "pattern": "^enr[A-Za-z0-9]{8,}$" },
"childid": { "type": "string", "pattern": "^chd[A-Za-z0-9]{8,}$" },
"program": {
"type": "string",
"description": "Canonical program code, not the source label.",
"enum": ["infant", "toddler", "preschool", "prek", "schoolage", "dropin"]
},
"scheduledays": {
"type": "array",
"items": { "type": "string", "enum": ["mon","tue","wed","thu","fri","sat","sun"] },
"default": []
},
"startdate": { "type": "string", "format": "date" },
"end_date": { "type": ["string", "null"], "format": "date" },
"status": {
"type": "string",
"enum": ["active", "waitlisted", "withdrawn", "graduated", "pending"]
}
}
}
``
Required: enrollmentid, childid, program, startdate, status.
Optional: scheduledays, end_date.
The enum on status is the whole point. Source systems love numeric status codes — 1, 2, 3, 7 — and nobody documents them. Forcing a canonical enum means someone has to write down what 3 meant during discovery, which is exactly the forcing function you need.
Invoice
``json
{
"$schema": "https://json-schema.org/draft/2020-12/schema",
"$id": "https://canonical.example.com/schemas/invoice.json",
"title": "Invoice",
"type": "object",
"required": ["invoiceid", "familyid", "periodstart", "periodend", "currency", "lineitems", "totalcents"],
"properties": {
"invoiceid": { "type": "string", "pattern": "^inv[A-Za-z0-9]{8,}$" },
"familyid": { "type": "string", "pattern": "^fam[A-Za-z0-9]{8,}$" },
"periodstart": { "type": "string", "format": "date" },
"periodend": { "type": "string", "format": "date" },
"currency": { "type": "string", "pattern": "^[A-Z]{3}$", "default": "USD" },
"lineitems": {
"type": "array",
"minItems": 1,
"items": {
"type": "object",
"required": ["description", "amountcents", "type"],
"properties": {
"description": { "type": "string" },
"childid": { "type": ["string", "null"] },
"type": {
"type": "string",
"enum": ["tuition", "registration", "latefee", "discount", "credit", "supplyfee"]
},
"amountcents": {
"type": "integer",
"description": "Signed. Discounts and credits are negative."
}
}
}
},
"total_cents": { "type": "integer" },
"status": {
"type": "string",
"enum": ["draft", "issued", "paid", "partial", "void"]
}
}
}
``
Required: invoiceid, familyid, periodstart, periodend, currency, lineitems, totalcents.
Optional: status, child_id on line items.
Two rules that save you later: money is integer cents, never floats, and discounts are negative line items, not a separate percentage field floating off to the side. Storing discount_percent: 10 next to amount: 420.00 and recomputing it slightly differently in two places causes more reconciliation mismatches than almost anything else. Materialize the discount as a line item and the invoice total becomes auditable by simple addition.
Attendance
``json
{
"$schema": "https://json-schema.org/draft/2020-12/schema",
"$id": "https://canonical.example.com/schemas/attendance.json",
"title": "Attendance",
"type": "object",
"required": ["attendanceid", "childid", "date", "checkin", "sitetimezone"],
"properties": {
"attendanceid": { "type": "string", "pattern": "^att[A-Za-z0-9]{8,}$" },
"childid": { "type": "string", "pattern": "^chd[A-Za-z0-9]{8,}$" },
"date": {
"type": "string",
"format": "date",
"description": "Local calendar date at the site. Derived from checkin in sitetimezone."
},
"checkin": { "type": "string", "format": "date-time", "description": "RFC 3339, UTC with offset." },
"checkout": { "type": ["string", "null"], "format": "date-time" },
"sitetimezone": {
"type": "string",
"description": "IANA tz name, e.g. America/Chicago."
},
"recordedby": { "type": ["string", "null"] }
}
}
``
Required: attendanceid, childid, date, checkin, sitetimezone.
Optional: checkout, recordedby.
Attendance is where timezone bugs hide. It's the single most common silent data corruption in childcare migrations — more on that in the mismatch patterns below.
OpenAPI component snippet
If you're wiring these into an API contract, drop them under components/schemas and reference them:
``yaml
components:
schemas:
Family:
$ref: './schemas/family.json'
Enrollment:
$ref: './schemas/enrollment.json'
Invoice:
$ref: './schemas/invoice.json'
Attendance:
$ref: './schemas/attendance.json'
responses:
FamilyList:
description: Paginated families
content:
application/json:
schema:
type: object
properties:
data:
type: array
items: { $ref: '#/components/schemas/Family' }
next_cursor:
type: ["string", "null"]
``
Keeping the JSON Schema files external and $ref-ing them means the same artifact validates your import pipeline and documents your API. One source of truth.
The mismatch patterns that actually break migrations
This is the part no vendor import wizard warns you about. These four show up in nearly every childcare data set.
DOB formats
A single source export will contain birth dates in multiple formats because different staff typed them in over the years. Typical culprits:
-
03/14/2021(US) vs14/03/2021(entered by someone who defaulted to DD/MM) -
3-14-21with a two-digit year -
Excel silently converting
03/14/2021to a serial number like44269 -
A trailing midnight timestamp
2021-03-14T00:00:00Z
The dangerous one is ambiguous day/month. 02/03/2021 is either February 3 or March 2, and you cannot tell from the value alone. The fix isn't a cleverer parser — it's checking whether any value in the column has a day component above 12. If 25/06/2021 appears anywhere, the whole column is DD/MM and you parse accordingly. If nothing exceeds 12, flag the column for human confirmation. Never guess silently on DOB. A wrong birth date can push a child into the wrong ratio category, and that's a licensing problem, not just a data problem.
Timezone normalization on attendance
A center in Chicago exports check-ins as naive local timestamps — 2024-11-03 08:15:00, no offset. The import treats them as UTC. Now every attendance record is off by five or six hours. Most of the time nobody notices because the date still looks right. But a 7:45 PM late pickup becomes 1:45 AM the next day, and your attendance-date derivation pushes it onto the wrong calendar day. Suddenly a child shows as attending on a day they were home sick.
The rule: store checkin as RFC 3339 with an explicit offset, store sitetimezone as an IANA name, and derive the date field from the timestamp in the site's timezone — not in UTC. During discovery, ask the source vendor one question: "are these timestamps UTC, local, or server-local?" Get the answer in writing.
Sibling links
Three common encodings of sibling relationships in source data, roughly in order of how often they appear:
-
Implicit by shared account or household ID (best case)
-
Implicit by matching last name + address (fragile)
-
Buried in a notes field
"sister is Maya in Room 4"
Map all of these onto siblinggroupid. When the source has a real household ID, generate one siblinggroupid per household. When it's heuristic, generate candidates and route them to human review — don't auto-link on surname alone. The downstream cost of a wrong sibling link is a wrong discount applied every single billing cycle until a parent catches it.
Discount rules
The ugliest column in any childcare export. You'll find 10%, $50, sibling, staff, scholarship, 10% + $25 referral, and blanks that actually mean "full price" all in the same field. Canonical rule from above: every discount becomes a negative line_item of type: discount with a human-readable description. A 10% + $25 referral splits into two negative line items. This makes the invoice total reproducible by summing line items — no hidden recomputation — and it preserves the reason for the discount, which you need for audit and for the occasional family conversation.
Source → canonical mapping tables (worked examples)
Here's what a filled-in mapping table looks like for a few real fields. The transformation rule column is where the value is — anyone can match column names, but the rules are what gets lost when the person who did discovery leaves.
| Canonical field | Source field | Transformation rule | Example in → out |
|---|---|---|---|
children[].dob | BirthDate | Detect format via day>12 heuristic; reject ambiguous, parse to ISO date | 14/03/2021 → 2021-03-14 |
enrollment.status | Status (int) | Lookup: 1→active, 2→waitlisted, 3→withdrawn, 7→pending | 3 → withdrawn |
enrollment.program | ClassName (free text) | Map via agreed lookup; unmapped → review queue | "Busy Bees AM" → toddler |
invoice.total_cents | Total (decimal) | Multiply by 100, round half-up, store integer | 420.00 → 42000 |
lineitems[].amountcents (discount) | Discount (text) | Parse %/$, apply to tuition base, emit negative cents | "10%" on $420 → -4200 |
attendance.check_in | SignIn (naive local) | Attach site_timezone offset, convert to RFC 3339 | 2024-11-03 08:15 + America/Chicago → 2024-11-03T08:15:00-06:00 |
attendance.date | derived | Local calendar date of checkin in sitetimezone | → 2024-11-03 |
child_id | StudentID | Prefix + hash source PK; keep crosswalk table | 8841 → chd_8f3a91c2 |
Notice child_id isn't a straight copy of the source primary key. Hash it with a prefix and keep a crosswalk table mapping source PK → canonical ID. When you discover a second source later, or need to re-run the import after a bug fix, that crosswalk is what prevents duplicate children from appearing in your system.
The mapping-table template to populate during discovery
Give this to whoever runs discovery and have them fill one row per source field, per source system. Empty cells are the whole point — an unfilled "transformation rule" is a flag that nobody actually understands that field yet.
-
Canonical field — the target path, e.g.
enrollment.status -
Source system — which platform or site this row describes
-
Source field — exact column or path name
-
Source type/format — what the raw value actually looks like
-
Required? — is the canonical field required
-
Transformation rule — the actual logic, written in plain language
-
Default / fallback — what to do when the source value is empty
-
Validation check — what failure looks like
-
Unmapped behavior — reject, default, or send to review queue
-
Owner — who confirmed this mapping
-
Example in → out — one concrete round-trip
Leave transformation-rule cells blank during initial passes to force vendor or staff clarification rather than assuming mappings.
Run it like this:
-
Export a real sample — not a demo record — from each source. A few hundred rows is the minimum for format variation to actually surface.
-
Profile every column
distinct values, null rate, min/max, format variants. This is where you catch the DOB-over-12 cases and the mystery status codes.
-
Fill one template row per source field. Leave transformation rules blank until someone can state them clearly.
-
Resolve blanks with the source vendor or a staff member who knows the history. Write answers in the table, not in Slack.
-
Validate transformed output against the JSON Schema above before it ever touches the target system.
-
Keep the crosswalk tables and the filled template as permanent migration artifacts — not scratch files.
Visual workflow for running the mapping-table process:
Keep the crosswalk tables and the filled template as permanent migration artifacts — not scratch files.
A short real scenario
A two-site preschool group consolidating onto one platform had around 180 active families. Their first import attempt — straight column mapping, no canonical layer — produced roughly 40 invoices that didn't reconcile and a handful of children showing in the wrong program. Root causes were exactly the patterns above: discounts stored as a percent field that got recomputed differently in two places, and a dozen DOBs parsed as DD/MM when they were actually MM/DD.
The redo used a canonical schema and a filled mapping table. Profiling the DOB column up front flagged the ambiguous format before import instead of after. Discounts were materialized as negative line items, so every invoice total was reproducible by addition. The second import validated clean against the schema on the first full pass, and reconciliation came down to two genuine edge cases that needed a human decision rather than forty silent errors. The difference wasn't tooling horsepower — it was defining the target shape and the transformation rules before moving a single row.
When a canonical layer is overkill
If you're migrating a single site from one system to one system, and the source export is reasonably clean and documented, you can skip the canonical middle layer and map directly. The overhead isn't worth it for one small, tidy source.
Where it earns its keep: multiple sources, acquisitions, phased cutovers, or any situation where you'll re-run the import more than once. The second you have two systems feeding one target, the canonical schema stops being bureaucracy and starts being what keeps your reconciliation honest.
The schemas, mismatch patterns, and mapping template above are enough to run a disciplined discovery without building the structure from scratch. Fill the table against real sample data, validate against the JSON Schema before cutover, and keep your crosswalks. That's the difference between a migration that reconciles on the first pass and one that generates weeks of invoice disputes.
Ready to elevate your childcare management?
Join hundreds of childcare providers using Totsyly to save time, improve communication, and grow their centers.