# Ofsted Providers Companies House Enrichment (`alkausari_mujahid/ofsted-providers-companies-house-enrichment`) Actor Syncs UK Ofsted provider names from a configurable source table, matches each one against Companies House using fuzzy search, and stores verified company profiles, registered addresses, and active directors back into your MySQL or Supabase database — automatically skipping duplicates and aliases. - **URL**: https://apify.com/alkausari\_mujahid/ofsted-providers-companies-house-enrichment.md - **Developed by:** [Alkausari M](https://apify.com/alkausari_mujahid) (community) - **Categories:** Automation, Integrations, Lead generation - **Stats:** 3 total users, 3 monthly users, 100.0% runs succeeded, NaN bookmarks - **User rating**: No ratings yet ## Pricing from $5.00 / 1,000 results This Actor is paid per event. You are not charged for the Apify platform usage, but only a fixed price for specific events. Learn more: https://docs.apify.com/platform/actors/running/actors-in-store#pay-per-event ## What's an Apify Actor? Actors are a software tools running on the Apify platform, for all kinds of web data extraction and automation use cases. In Batch mode, an Actor accepts a well-defined JSON input, performs an action which can take anything from a few seconds to a few hours, and optionally produces a well-defined JSON output, datasets with results, or files in key-value store. In Standby mode, an Actor provides a web server which can be used as a website, API, or an MCP server. Actors are written with capital "A". ## How to integrate an Actor? If asked about integration, you help developers integrate Actors into their projects. You adapt to their stack and deliver integrations that are safe, well-documented, and production-ready. The best way to integrate Actors is as follows. In JavaScript/TypeScript projects, use official [JavaScript/TypeScript client](https://docs.apify.com/api/client/js.md): ```bash npm install apify-client ``` In Python projects, use official [Python client library](https://docs.apify.com/api/client/python.md): ```bash pip install apify-client ``` In shell scripts, use [Apify CLI](https://docs.apify.com/cli/docs.md): ````bash # MacOS / Linux curl -fsSL https://apify.com/install-cli.sh | bash # Windows irm https://apify.com/install-cli.ps1 | iex ```bash In AI frameworks, you might use the [Apify MCP server](https://docs.apify.com/platform/integrations/mcp.md). If your project is in a different language, use the [REST API](https://docs.apify.com/api/v2.md). For usage examples, see the [API](#api) section below. For more details, see Apify documentation as [Markdown index](https://docs.apify.com/llms.txt) and [Markdown full-text](https://docs.apify.com/llms-full.txt). # README **Match UK Ofsted-registered care providers against [Companies House](https://find-and-update.company-information.service.gov.uk) 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](https://apify.com/alkausari_mujahid/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](https://apify.com/alkausari_mujahid/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. ```jsonc // 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" } ```` ```jsonc // 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](https://developer.company-information.service.gov.uk) 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: ```json { "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 ```sql -- ── 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 ****. *** Built by **Alkausari M**. This Actor uses the official [Companies House Public Data API](https://developer.company-information.service.gov.uk) and is not affiliated with or endorsed by Companies House or Ofsted. # Actor input Schema ## `api_key` (type: `string`): API key for the UK Companies House API (used as HTTP Basic Auth username). ## `source_table` (type: `string`): Name of the table to read provider names from. ## `source_column` (type: `string`): Name of the column in the source table that contains the provider names. ## `process_all` (type: `boolean`): When ON, re-processes every name in the source table including already-matched ones. When OFF (default), only processes names not yet matched in ofsted\_providers. ## `latest_director_only` (type: `boolean`): When ON, only the most recently appointed active director is stored. When OFF (default), all active directors are stored. ## `db_type` (type: `string`): Choose the database backend to write results to. ## `db_host` (type: `string`): MySQL host IP address or hostname. ## `db_database` (type: `string`): Name of the MySQL database. ## `db_user` (type: `string`): MySQL username. ## `db_password` (type: `string`): MySQL password. ## `supabase_url` (type: `string`): Your Supabase project URL (e.g. https://xyz.supabase.co). ## `supabase_api_key` (type: `string`): Supabase service role key (bypasses RLS). ## Actor input object example ```json { "process_all": false, "latest_director_only": false, "db_type": "mysql" } ``` # Actor output Schema ## `results` (type: `string`): No description # API You can run this Actor programmatically using our API. Below are code examples in JavaScript, Python, and CLI, as well as the OpenAPI specification and MCP server setup. ## JavaScript example ```javascript import { ApifyClient } from 'apify-client'; // Initialize the ApifyClient with your Apify API token // Replace the '' with your token const client = new ApifyClient({ token: '', }); // Prepare Actor input const input = {}; // Run the Actor and wait for it to finish const run = await client.actor("alkausari_mujahid/ofsted-providers-companies-house-enrichment").call(input); // Fetch and print Actor results from the run's dataset (if any) console.log('Results from dataset'); console.log(`💾 Check your data here: https://console.apify.com/storage/datasets/${run.defaultDatasetId}`); const { items } = await client.dataset(run.defaultDatasetId).listItems(); items.forEach((item) => { console.dir(item); }); // 📚 Want to learn more 📖? Go to → https://docs.apify.com/api/client/js/docs ``` ## Python example ```python from apify_client import ApifyClient # Initialize the ApifyClient with your Apify API token # Replace '' with your token. client = ApifyClient("") # Prepare the Actor input run_input = {} # Run the Actor and wait for it to finish run = client.actor("alkausari_mujahid/ofsted-providers-companies-house-enrichment").call(run_input=run_input) # Fetch and print Actor results from the run's dataset (if there are any) print("💾 Check your data here: https://console.apify.com/storage/datasets/" + run["defaultDatasetId"]) for item in client.dataset(run["defaultDatasetId"]).iterate_items(): print(item) # 📚 Want to learn more 📖? Go to → https://docs.apify.com/api/client/python/docs/quick-start ``` ## CLI example ```bash echo '{}' | apify call alkausari_mujahid/ofsted-providers-companies-house-enrichment --silent --output-dataset ``` ## MCP server setup ```json { "mcpServers": { "apify": { "command": "npx", "args": [ "mcp-remote", "https://mcp.apify.com/?tools=alkausari_mujahid/ofsted-providers-companies-house-enrichment", "--header", "Authorization: Bearer " ] } } } ``` ## OpenAPI specification ```json { "openapi": "3.0.1", "info": { "title": "Ofsted Providers Companies House Enrichment", "description": "Syncs UK Ofsted provider names from a configurable source table, matches each one against Companies House using fuzzy search, and stores verified company profiles, registered addresses, and active directors back into your MySQL or Supabase database — automatically skipping duplicates and aliases.", "version": "0.0", "x-build-id": "T2QqvEUzqAj4dIMiQ" }, "servers": [ { "url": "https://api.apify.com/v2" } ], "paths": { "/acts/alkausari_mujahid~ofsted-providers-companies-house-enrichment/run-sync-get-dataset-items": { "post": { "operationId": "run-sync-get-dataset-items-alkausari_mujahid-ofsted-providers-companies-house-enrichment", "x-openai-isConsequential": false, "summary": "Executes an Actor, waits for its completion, and returns Actor's dataset items in response.", "tags": [ "Run Actor" ], "requestBody": { "required": true, "content": { "application/json": { "schema": { "$ref": "#/components/schemas/inputSchema" } } } }, "parameters": [ { "name": "token", "in": "query", "required": true, "schema": { "type": "string" }, "description": "Enter your Apify token here" } ], "responses": { "200": { "description": "OK" } } } }, "/acts/alkausari_mujahid~ofsted-providers-companies-house-enrichment/runs": { "post": { "operationId": "runs-sync-alkausari_mujahid-ofsted-providers-companies-house-enrichment", "x-openai-isConsequential": false, "summary": "Executes an Actor and returns information about the initiated run in response.", "tags": [ "Run Actor" ], "requestBody": { "required": true, "content": { "application/json": { "schema": { "$ref": "#/components/schemas/inputSchema" } } } }, "parameters": [ { "name": "token", "in": "query", "required": true, "schema": { "type": "string" }, "description": "Enter your Apify token here" } ], "responses": { "200": { "description": "OK", "content": { "application/json": { "schema": { "$ref": "#/components/schemas/runsResponseSchema" } } } } } } }, "/acts/alkausari_mujahid~ofsted-providers-companies-house-enrichment/run-sync": { "post": { "operationId": "run-sync-alkausari_mujahid-ofsted-providers-companies-house-enrichment", "x-openai-isConsequential": false, "summary": "Executes an Actor, waits for completion, and returns the OUTPUT from Key-value store in response.", "tags": [ "Run Actor" ], "requestBody": { "required": true, "content": { "application/json": { "schema": { "$ref": "#/components/schemas/inputSchema" } } } }, "parameters": [ { "name": "token", "in": "query", "required": true, "schema": { "type": "string" }, "description": "Enter your Apify token here" } ], "responses": { "200": { "description": "OK" } } } } }, "components": { "schemas": { "inputSchema": { "type": "object", "required": [ "api_key", "source_table", "source_column" ], "properties": { "api_key": { "title": "Companies House API Key", "type": "string", "description": "API key for the UK Companies House API (used as HTTP Basic Auth username)." }, "source_table": { "title": "Source Table Name", "type": "string", "description": "Name of the table to read provider names from." }, "source_column": { "title": "Source Column Name", "type": "string", "description": "Name of the column in the source table that contains the provider names." }, "process_all": { "title": "Process All Companies", "type": "boolean", "description": "When ON, re-processes every name in the source table including already-matched ones. When OFF (default), only processes names not yet matched in ofsted_providers.", "default": false }, "latest_director_only": { "title": "Extract Latest Directors Only", "type": "boolean", "description": "When ON, only the most recently appointed active director is stored. When OFF (default), all active directors are stored.", "default": false }, "db_type": { "title": "Database Type", "enum": [ "mysql", "supabase" ], "type": "string", "description": "Choose the database backend to write results to.", "default": "mysql" }, "db_host": { "title": "Host", "type": "string", "description": "MySQL host IP address or hostname." }, "db_database": { "title": "Database Name", "type": "string", "description": "Name of the MySQL database." }, "db_user": { "title": "User", "type": "string", "description": "MySQL username." }, "db_password": { "title": "Password", "type": "string", "description": "MySQL password." }, "supabase_url": { "title": "Project URL", "type": "string", "description": "Your Supabase project URL (e.g. https://xyz.supabase.co)." }, "supabase_api_key": { "title": "API Key", "type": "string", "description": "Supabase service role key (bypasses RLS)." } } }, "runsResponseSchema": { "type": "object", "properties": { "data": { "type": "object", "properties": { "id": { "type": "string" }, "actId": { "type": "string" }, "userId": { "type": "string" }, "startedAt": { "type": "string", "format": "date-time", "example": "2025-01-08T00:00:00.000Z" }, "finishedAt": { "type": "string", "format": "date-time", "example": "2025-01-08T00:00:00.000Z" }, "status": { "type": "string", "example": "READY" }, "meta": { "type": "object", "properties": { "origin": { "type": "string", "example": "API" }, "userAgent": { "type": "string" } } }, "stats": { "type": "object", "properties": { "inputBodyLen": { "type": "integer", "example": 2000 }, "rebootCount": { "type": "integer", "example": 0 }, "restartCount": { "type": "integer", "example": 0 }, "resurrectCount": { "type": "integer", "example": 0 }, "computeUnits": { "type": "integer", "example": 0 } } }, "options": { "type": "object", "properties": { "build": { "type": "string", "example": "latest" }, "timeoutSecs": { "type": "integer", "example": 300 }, "memoryMbytes": { "type": "integer", "example": 1024 }, "diskMbytes": { "type": "integer", "example": 2048 } } }, "buildId": { "type": "string" }, "defaultKeyValueStoreId": { "type": "string" }, "defaultDatasetId": { "type": "string" }, "defaultRequestQueueId": { "type": "string" }, "buildNumber": { "type": "string", "example": "1.0.0" }, "containerUrl": { "type": "string" }, "usage": { "type": "object", "properties": { "ACTOR_COMPUTE_UNITS": { "type": "integer", "example": 0 }, "DATASET_READS": { "type": "integer", "example": 0 }, "DATASET_WRITES": { "type": "integer", "example": 0 }, "KEY_VALUE_STORE_READS": { "type": "integer", "example": 0 }, "KEY_VALUE_STORE_WRITES": { "type": "integer", "example": 1 }, "KEY_VALUE_STORE_LISTS": { "type": "integer", "example": 0 }, "REQUEST_QUEUE_READS": { "type": "integer", "example": 0 }, "REQUEST_QUEUE_WRITES": { "type": "integer", "example": 0 }, "DATA_TRANSFER_INTERNAL_GBYTES": { "type": "integer", "example": 0 }, "DATA_TRANSFER_EXTERNAL_GBYTES": { "type": "integer", "example": 0 }, "PROXY_RESIDENTIAL_TRANSFER_GBYTES": { "type": "integer", "example": 0 }, "PROXY_SERPS": { "type": "integer", "example": 0 } } }, "usageTotalUsd": { "type": "number", "example": 0.00005 }, "usageUsd": { "type": "object", "properties": { "ACTOR_COMPUTE_UNITS": { "type": "integer", "example": 0 }, "DATASET_READS": { "type": "integer", "example": 0 }, "DATASET_WRITES": { "type": "integer", "example": 0 }, "KEY_VALUE_STORE_READS": { "type": "integer", "example": 0 }, "KEY_VALUE_STORE_WRITES": { "type": "number", "example": 0.00005 }, "KEY_VALUE_STORE_LISTS": { "type": "integer", "example": 0 }, "REQUEST_QUEUE_READS": { "type": "integer", "example": 0 }, "REQUEST_QUEUE_WRITES": { "type": "integer", "example": 0 }, "DATA_TRANSFER_INTERNAL_GBYTES": { "type": "integer", "example": 0 }, "DATA_TRANSFER_EXTERNAL_GBYTES": { "type": "integer", "example": 0 }, "PROXY_RESIDENTIAL_TRANSFER_GBYTES": { "type": "integer", "example": 0 }, "PROXY_SERPS": { "type": "integer", "example": 0 } } } } } } } } } } ```