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	353,810	the code finding on a visit - the statute cited (19a-87b-6(e)), a plain-language description, and the regulatory text
inspections	99,378	every licensing inspection: type, status, outcome, with the provider's name and license number
investigations	33,869	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,561 corrective action plans, 7,607 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 200)

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

Low cost

From $0.0001 per record - billed only for the rows you use, at the platform floor. Cost scales with what you pull, not with the size of the register, and because each record is metered individually there's no per-run charge cap to hit on a big pull.

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.

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.