Ofsted Providers Companies House Enrichment

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 ───────────────────────────────────────────────────────
CREATE TABLE IF NOT EXISTS ofsted_providers (
    id              BIGSERIAL    PRIMARY KEY,
    searched_name   VARCHAR(255) NOT NULL,
    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    SMALLINT     DEFAULT 0,
    created_at      TIMESTAMPTZ  DEFAULT NOW(),
    updated_at      TIMESTAMPTZ  DEFAULT NOW(),
    CONSTRAINT uq_searched_name  UNIQUE (searched_name),
    CONSTRAINT uq_company_number UNIQUE (company_number)
);

CREATE INDEX IF NOT EXISTS idx_status        ON ofsted_providers (company_status);
CREATE INDEX IF NOT EXISTS idx_type          ON ofsted_providers (company_type);
CREATE INDEX IF NOT EXISTS idx_postcode      ON ofsted_providers (postal_code);
CREATE INDEX IF NOT EXISTS idx_creation_date ON ofsted_providers (creation_date);
CREATE INDEX IF NOT EXISTS idx_search_count  ON ofsted_providers (search_count);


-- ── 2. ofsted_directors ───────────────────────────────────────────────────────
CREATE TABLE IF NOT EXISTS ofsted_directors (
    id                              BIGSERIAL    PRIMARY KEY,
    company_number                  VARCHAR(20)  NOT NULL,
    full_name                       VARCHAR(255) NOT NULL,
    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  DEFAULT NOW(),
    updated_at                      TIMESTAMPTZ  DEFAULT NOW(),
    CONSTRAINT uq_director         UNIQUE (company_number, full_name, role),
    CONSTRAINT fk_director_company FOREIGN KEY (company_number)
        REFERENCES ofsted_providers (company_number)
        ON DELETE CASCADE
        ON UPDATE CASCADE
);

CREATE INDEX IF NOT EXISTS idx_director_company      ON ofsted_directors (company_number);
CREATE INDEX IF NOT EXISTS idx_director_full_name    ON ofsted_directors (full_name);
CREATE INDEX IF NOT EXISTS idx_director_appointed_on ON ofsted_directors (appointed_on);
CREATE INDEX IF NOT EXISTS idx_director_role         ON ofsted_directors (role);
CREATE INDEX IF NOT EXISTS idx_director_nationality  ON ofsted_directors (nationality);


-- ── 3. ofsted_provider_aliases ────────────────────────────────────────────────
CREATE TABLE IF NOT EXISTS ofsted_provider_aliases (
    id             BIGSERIAL    PRIMARY KEY,
    alias_name     VARCHAR(255) NOT NULL,
    company_number VARCHAR(20)  NOT NULL,
    created_at     TIMESTAMPTZ  DEFAULT NOW(),
    CONSTRAINT uq_alias_name UNIQUE (alias_name)
);

CREATE INDEX IF NOT EXISTS idx_alias_company_num ON ofsted_provider_aliases (company_number);


-- ── 4. updated_at trigger (replaces MySQL's ON UPDATE CURRENT_TIMESTAMP) ──────
CREATE OR REPLACE FUNCTION set_updated_at()
RETURNS TRIGGER AS $$
BEGIN
    NEW.updated_at = NOW();
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE OR REPLACE TRIGGER trg_providers_updated_at
    BEFORE UPDATE ON ofsted_providers
    FOR EACH ROW EXECUTE FUNCTION set_updated_at();

CREATE OR REPLACE TRIGGER trg_directors_updated_at
    BEFORE UPDATE ON ofsted_directors
    FOR EACH ROW EXECUTE FUNCTION set_updated_at();

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

---

📋 Input

Field	Description	Required
Companies House API Key	API key used as HTTP Basic Auth username	Yes
Source Table Name	Table in your database that contains provider names	Yes
Source Column Name	Column in that table that holds the provider names	Yes
Process All Companies	Re-processes every provider including already-matched ones. Default: OFF	No
Extract Latest Directors Only	Store only the most recently appointed active director per company. Default: OFF	No
Database Type	mysql or supabase. Default: mysql	No
MySQL Host / Database / User / Password	MySQL connection credentials	If MySQL
Supabase Project URL	Your Supabase project URL (https://xyz.supabase.co)	If Supabase
Supabase API Key	Supabase service role key (bypasses RLS)	If Supabase

---

💡 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.

---