Connecticut Child Care & Youth Camp Licensing

Connecticut's Office of Early Childhood licenses every child care provider and youth camp in the state - and publishes its entire licensing file as open data: the violations found on each visit, the inspections that found them, and the investigations that followed complaints. This actor turns those three linked tables into a charged-per-record lookup, filter and aggregate tool, all keyed by the case id that ties them together.

Built for: parents and employers vetting a provider, child-care operators researching the regulatory record, compliance and legal teams, and anyone who needs the enforcement history behind a license - not just a license lookup.

What it covers

Three tables from CT OEC, each keyed by caseidnt:

Corpus	Rows	What it is
violations	~355k	the code finding on a visit - the statute cited (19a-87b-6(e)), a plain-language description, and the regulatory text
inspections	~99.9k	every licensing inspection: type, status, outcome, with the provider's name and license number
investigations	~33.9k	complaints / self-reports and how they resolved, with the provider's name and license number

The through-key is the product. caseidnt links all three: one case query returns a case's violations and its inspections and its investigation in one pull - the full regulatory chain for a single case, across three tables that the state publishes separately (verified: case 381579 returns 101 violations + 6 inspections).

Violation depth: 345,664 findings carry both a plain-language description and the statutory language, tagged by the visit kind (306,979 primary inspections, 27,119 follow-up visits, 14,279 of the alternate 05- inspection spelling, 5,433 untagged). Investigation depth: how it arrived (26,916 complaints, 6,672 self-reported, 168 licensing-initiated) and how it ended (~24.6k corrective action plans, ~7.6k referred to the legal division, and the rest no-action / voluntarily-closed / exempt).

The fine print that matters

The same visit kind is spelled two ways in the source - 1-Inspection & Findings and 05-Inspection & Findings - so a naive substring match on "Inspection" silently welds the two families together (321,258 rows instead of the 306,979 in the primary one). The actor matches the exact source strings, and the filter exposes both. closedate is a text column in MM/DD/YYYY on violations and inspections (investigations use a real date), normalized to ISO on output. And a handful of visit dates are junk far-future values (up to year 2106) - excluded from every query and blanked on output, so a newest-first pull never surfaces them. Some records (28,049 violations, 4,240 inspections) carry no visit date at all; those are kept and sorted after the dated rows, so a date range simply won't match them.

Typical questions

• "Everything on case 381579 - violations, inspections, investigation."
• "Providers under an inspection still IN PROCESS."
• "Investigations that ended in Referred to Legal Division."
• "Every follow-up visit finding this year."
• "Aggregate the register by action type, inspection status or resolution."

Inputs

Input	What it does
mode	violations (default) / inspections / investigations / case / aggregate
caseId	the numeric through-key (use with mode=case)
name / licenseNumber	provider identity
actionType / inspectionType / inspectionStatus	inspection filters
howReceived / resolution	investigation filters
visitFrom/To / openedFrom/To	date ranges
groupBy + corpus	aggregate dimension over a chosen table
maxResults	cap rows (default 50)

Default run = the 50 most recent violation rows - fast for the daily auto-test. For a targeted query add a filter; for a broad view use aggregate.

Example inputs

One case's whole chain - mode=case keys on the case id and returns that case's violations, its inspections and its investigation together.

{ "mode": "case", "caseId": "803551", "maxResults": 100 }

Live inspections - OPENED and IN PROCESS are the visits not yet closed.

{ "mode": "inspections", "inspectionStatus": "IN PROCESS", "maxResults": 50 }

Investigations referred to the legal division

{ "mode": "investigations", "resolution": "Referred to Legal", "maxResults": 50 }

Findings by visit kind - one row per actionType over the violations table.

{ "mode": "aggregate", "corpus": "violations", "groupBy": "actionType" }

Low cost

From $0.00005 per record, down to $0.00003 at Gold - billed only for the rows you use, at the low end of the store. Cost scales with what you pull, not with the size of the register, and each record is metered individually.

The hard part here is that Connecticut splits one regulatory story across three tables that only agree on a case id - and the case id is a bare integer with no shared natural key, so joining violations to inspections to investigations is a real join, not an address match. On top of that the inspection kinds are double-spelled (1- / 05-), so a like '%Inspection%' filter quietly returns the wrong family; the close dates are text in two different formats across the tables; and the visit dates carry junk values out to year 2106 that sort to the top of any newest-first pull. Normalizing the through-key, the double-spelled kinds and the mixed text dates into a schema where a caseId query returns the whole chain and an actionType filter returns exactly one family is the actual product. Every pull is integrity-checked against the register's known shape, so a degraded source fails loudly instead of returning bad rows.

Example output

One finding - caseId=803551 returns violation rows like the one below; a mode=case pull brings back the same case's inspections and investigation too:

{
  "platform": "ct-childcare",
  "source": "ct-oec-violations",
  "corpus": "violations",
  "recordKind": "violation",
  "mode": "violations",
  "groupKey": "",
  "groupCount": "",
  "groupBy": "",
  "caseId": "803551",
  "caseActionId": "2853555",
  "violationId": "21921",
  "violationLinkId": "651510",
  "caseType": "30",
  "actionType": "1-Inspection & Findings",
  "visitDate": "2026-09-21",
  "closeDate": "",
  "statute": "[19a-79-4a(b)]",
  "violationDescription": "021- Comprehensive background checks",
  "statuteLanguage": "The operator shall ensure that all program staff have completed a comprehensive background check before they have unsupervised access to children. The operator shall provide to the Office of Early Childhood any information obtained concerning substantiated child abuse or neglect records or criminal convictions upon request. The operator shall obtain from each prospective employee, who is eighteen years of age or older, for a position that requires the provision of care to a child or involves unsupervised access to a child, a listing of all employment held in the United States for an organization that provides care or services to children in the preceding five years. The operator shall take reasonable steps to contact each previous employer provided by the prospective employee to verify at a minimum the prospective employees job title, description of his/her regular duties, confirmation of employment dates, and whether such previous employer would recommend the prospective employee for rehire.  This information shall be made accessible to the Office upon request. The operator shall not employ program staff, including volunteers, who have a record that the commissioner reasonably believes renders such program staff unsuitable to be employed in a child care center or group child care home.",
  "lastModified": "2026-09-21",
  "caseActionTypeId": "",
  "name": "",
  "licenseNumber": "",
  "respondentContactId": "",
  "respondentCredentialId": "",
  "inspectionNumber": "",
  "inspectionTypeId": "",
  "inspectionType": "",
  "inspectionStatus": "",
  "resolutionId": "",
  "resolutionLinkId": "",
  "resolution": "",
  "caseNumber": "",
  "receivedTypeId": "",
  "howReceived": "",
  "dateOpened": "",
  "dateClosed": "",
  "uniqueKey": ""
}

mode=aggregate, corpus=investigations, groupBy=resolution - how investigations closed, largest first:

Corrective Action Plan      ~24.6k
Referred to Legal Division   ~7.6k

Source

• Connecticut Open Data: Child Care & Youth Camp Licensing - OEC licensing violations, inspections and investigations, refreshed daily. Public open data. Records are the state's own published fields; not an endorsement of any provider.