Ofsted Providers Companies House Enrichment

Pricing

Rating

Developer

Actor stats

Categories

Share

Match UK Ofsted-registered care providers against Companies House and store verified company profiles, registered addresses, and active directors directly into your MySQL or Supabase database. Automatically skips already-matched providers, deduplicates aliases, and handles rate limiting — so you can run it repeatedly as your provider list grows.

New to Ofsted data? Use the Ofsted Reports Data Scraper first to extract provider names and inspection records into your database, then run this Actor to enrich them with Companies House data.

Built and maintained by Alkausari M.

✦ Highlights

• 🏥 Ofsted-focused — designed specifically for care home and children's service providers
• 🔍 Fuzzy matching — finds the right Companies House record even with name inconsistencies
• 🏢 Full company profile — registration number, status, type, SIC codes, and registered address
• 👤 Active directors — names, roles, appointment dates, nationalities, and service addresses
• 🗄 MySQL & Supabase — write results to whichever database you use
• ♻️ Incremental runs — skips already-matched providers; only processes new or unresolved names
• 🔁 Alias deduplication — variant names that resolve to the same company are remembered and skipped on future runs

⚙ How it works

1. Get your Ofsted data — use the Ofsted Reports Data Scraper to extract provider names and inspection records into your database first.
2. Connect your database — provide MySQL or Supabase credentials.
3. Point to your source table — specify the table and column that holds your Ofsted provider names.
4. Click Start — the Actor reads provider names, searches Companies House for each, fuzzy-matches the best result (score ≥ 85), fetches the full profile and active directors, and writes everything to your database.
5. Run again anytime — already-matched providers are skipped automatically; only new names are processed.

// Example input — MySQL
{
 "api_key": "your-companies-house-api-key",
 "source_table": "ofsted_reports",
 "source_column": "provider_name",
 "db_type": "mysql",
 "db_host": "your-host",
 "db_database": "your-database",
 "db_user": "your-user",
 "db_password": "your-password"
}

// Example input — Supabase
{
 "api_key": "your-companies-house-api-key",
 "source_table": "ofsted_reports",
 "source_column": "provider_name",
 "db_type": "supabase",
 "supabase_url": "https://xyz.supabase.co",
 "supabase_api_key": "your-service-role-key"
}

[!IMPORTANT] A free Companies House API key is required. The API allows 600 requests per 5 minutes. The Actor handles rate limiting automatically with exponential backoff.

📦 What you get back

Each matched provider produces one record pushed to the Apify dataset and upserted into your database:

{
"searched_name":"abc care home ltd",
"matched_name":"ABC CARE HOME LIMITED",
"company_number":"12345678",
"registered_address":{
"address_line_1":"123 High Street",
"address_line_2":"Anytown",
"locality":"Birmingham",
"region":"West Midlands",
"country":"England",
"postal_code":"B1 1AA",
"door_number":"123"
},
"active_directors":[
{
"full_name":"SMITH, John William",
"title":"",
"first_name":"John",
"middle_name":"William",
"last_name":"Smith",
"role":"director",
"appointed_on":"2018-04-01",
"nationality":"British",
"service_address":{
"premises":"123 High Street",
"address_line_1":"Anytown",
"locality":"Birmingham",
"country":"England",
"postal_code":"B1 1AA"
}
}
],
"company_profile":{
"status":"active",
"type":"ltd",
"creation_date":"2010-06-15",
"sic_codes":["87100","87200"]
}
}

🗄 Database tables

The Actor writes to three tables. For MySQL, tables are created automatically on first run. For Supabase, run the SQL below once in the Supabase SQL Editor before running the Actor.

MySQL

Tables are created automatically — no setup required.

Supabase — run once in SQL Editor

-- ── 1. ofsted_providers ───────────────────────────────────────────────────────
CREATETABLEIFNOTEXISTS ofsted_providers (
 id BIGSERIAL PRIMARYKEY,
 searched_name VARCHAR(255)NOTNULL,
 matched_name VARCHAR(255),
 company_number VARCHAR(20),
 company_status VARCHAR(50),
 company_type VARCHAR(50),
 creation_date DATE,
 sic_codes_json JSONB,
 address_line_1 VARCHAR(255),
 address_line_2 VARCHAR(255),
 locality VARCHAR(100),
 region VARCHAR(100),
 country VARCHAR(100),
 postal_code VARCHAR(20),
 search_count SMALLINTDEFAULT0,
 created_at TIMESTAMPTZ DEFAULTNOW(),
 updated_at TIMESTAMPTZ DEFAULTNOW(),
CONSTRAINT uq_searched_name UNIQUE(searched_name),
CONSTRAINT uq_company_number UNIQUE(company_number)
);
CREATEINDEXIFNOTEXISTS idx_status ON ofsted_providers (company_status);
CREATEINDEXIFNOTEXISTS idx_type ON ofsted_providers (company_type);
CREATEINDEXIFNOTEXISTS idx_postcode ON ofsted_providers (postal_code);
CREATEINDEXIFNOTEXISTS idx_creation_date ON ofsted_providers (creation_date);
CREATEINDEXIFNOTEXISTS idx_search_count ON ofsted_providers (search_count);
-- ── 2. ofsted_directors ───────────────────────────────────────────────────────
CREATETABLEIFNOTEXISTS ofsted_directors (
 id BIGSERIAL PRIMARYKEY,
 company_number VARCHAR(20)NOTNULL,
 full_name VARCHAR(255)NOTNULL,
 title VARCHAR(50),
 first_name VARCHAR(100),
 middle_name VARCHAR(100),
 last_name VARCHAR(100),
 role VARCHAR(50),
 appointed_on DATE,
 nationality VARCHAR(100),
 service_address_premises VARCHAR(100),
 service_address_address_line_1 VARCHAR(255),
 service_address_address_line_2 VARCHAR(255),
 service_address_locality VARCHAR(100),
 service_address_region VARCHAR(100),
 service_address_country VARCHAR(100),
 service_address_postal_code VARCHAR(20),
 created_at TIMESTAMPTZ DEFAULTNOW(),
 updated_at TIMESTAMPTZ DEFAULTNOW(),
CONSTRAINT uq_director UNIQUE(company_number, full_name, role),
CONSTRAINT fk_director_company FOREIGNKEY(company_number)
REFERENCES ofsted_providers (company_number)
ONDELETECASCADE
ONUPDATECASCADE
);
CREATEINDEXIFNOTEXISTS idx_director_company ON ofsted_directors (company_number);
CREATEINDEXIFNOTEXISTS idx_director_full_name ON ofsted_directors (full_name);
CREATEINDEXIFNOTEXISTS idx_director_appointed_on ON ofsted_directors (appointed_on);
CREATEINDEXIFNOTEXISTS idx_director_role ON ofsted_directors (role);
CREATEINDEXIFNOTEXISTS idx_director_nationality ON ofsted_directors (nationality);
-- ── 3. ofsted_provider_aliases ────────────────────────────────────────────────
CREATETABLEIFNOTEXISTS ofsted_provider_aliases (
 id BIGSERIAL PRIMARYKEY,
 alias_name VARCHAR(255)NOTNULL,
 company_number VARCHAR(20)NOTNULL,
 created_at TIMESTAMPTZ DEFAULTNOW(),
CONSTRAINT uq_alias_name UNIQUE(alias_name)
);
CREATEINDEXIFNOTEXISTS idx_alias_company_num ON ofsted_provider_aliases (company_number);
-- ── 4. updated_at trigger (replaces MySQL's ON UPDATE CURRENT_TIMESTAMP) ──────
CREATEORREPLACEFUNCTION set_updated_at()
RETURNSTRIGGERAS $$
BEGIN
 NEW.updated_at =NOW();
RETURN NEW;
END;
$$ LANGUAGE plpgsql;
CREATEORREPLACETRIGGER trg_providers_updated_at
 BEFORE UPDATEON ofsted_providers
FOR EACH ROWEXECUTEFUNCTION set_updated_at();
CREATEORREPLACETRIGGER trg_directors_updated_at
 BEFORE UPDATEON ofsted_directors
FOR EACH ROWEXECUTEFUNCTION set_updated_at();

[!NOTE] Safe to run multiple times — all statements use IF NOT EXISTS and CREATE OR REPLACE.

📋 Input

💡 Use cases

• Due diligence — verify that registered care providers are legitimate, active companies.
• Director lookup — identify who is legally responsible for each Ofsted-registered service.
• Compliance monitoring — track company status changes (active → dissolved) across your provider list.
• Data enrichment — add SIC codes, incorporation dates, and registered addresses to your existing Ofsted dataset.
• Scheduled refresh — run on a schedule to keep director and address information current as Companies House records change.

❓ FAQ

What happens if a provider name doesn't match any Companies House record? The provider is marked with search_count = 1 and permanently skipped on future runs. To retry it, reset search_count to 0 in your database.

What is the fuzzy match threshold? The Actor uses token_sort_ratio from RapidFuzz. Any match scoring below 85 out of 100 is rejected to prevent false positives from similarly-named but unrelated companies.

Can I run it on a schedule? Yes — use Apify's built-in scheduler. Each run only processes providers not yet matched, so repeated runs are safe and efficient.

What if a provider name appears under a different name in Companies House? Once matched, the canonical name is stored as matched_name. The original searched name is preserved in searched_name. Variant names that resolve to the same company number are saved as aliases and skipped on all future runs.

Does it handle rate limiting? Yes — the Actor automatically retries on HTTP 429 responses with exponential backoff (5s → 10s → 20s).

📮 Support

Bugs, feature requests, or custom work — open an issue on Apify or email alkausarimujahid@gmail.com.