Ofsted Children's Home Reports to Supabase

Sync Ofsted inspection reports directly into your Supabase table — no Make, no Zapier, no glue code. Powered by the Ofsted Reports Data Scraper, which extracts full inspection data from PDF files on the Ofsted website. This Actor takes those results and upserts them straight into your Supabase database on every run.

---

✦ Highlights

• 🏠 Full PDF extraction — powered by Ofsted Reports Data Scraper, which pulls structured data directly from Ofsted PDF files
• 📅 Date-range filtering — fetch only inspections published in a specific window
• 🔄 Upsert, not overwrite — new records are inserted; existing ones are updated by URN, no duplicates
• 🧹 Auto-deduplication — multiple results for the same provider are collapsed to one row before sync
• 📊 Dual output — data lands in both your Supabase table and the Apify dataset simultaneously
• 🖱 Zero code — fill in credentials, paste a search URL, click Start

---

⚙ How it works

1. Paste an Ofsted search URL — apply your filters on reports.ofsted.gov.uk first (provision type, status, region), then copy the URL.
2. Connect Supabase — provide your project URL and API key. Create the table in advance using the SQL below; the Actor upserts into it.
3. Click Start — the Actor calls the Ofsted Reports Data Scraper, deduplicates by URN, and syncs everything to Supabase.

Example input, syncing every report published in one week:

{
    "start_urls": [
        { "url": "https://reports.ofsted.gov.uk/search?q=&level_1_types=3&level_2_types%5B0%5D=11&status%5B0%5D=1&start=0&rows=10" }
    ],
    "latest_report_date_start": "2026-09-21",
    "latest_report_date_end": "2026-09-25",
    "only_latest_inspection": true,
    "max_reports": 0,
    "apify_api_token": "apify_api_...",
    "ofsted_actor_id": "UNmCLFqfH6zXa3Mfc",
    "supabase_url": "https://xxxx.supabase.co",
    "supabase_api_key": "eyJ...",
    "supabase_table": "ofsted_inspections"
}

• latest_report_date_start / latest_report_date_end — optional publication-date filters (YYYY-MM-DD). Leave blank to fetch only reports published today.
• only_latest_inspection — true (default) gives one record per provider; useful for dashboards and monitoring.
• max_reports — stops after this many reports. The default of 10 keeps test runs short; set it to 0 for scheduled syncs so every report is synced.

---

📦 What you get back

Each record written to Supabase (and the Apify dataset):

{
    "unique_reference_number": "SC012345",
    "registered_provider": "Bright Futures Care Ltd",
    "registered_provider_address": "12 Oak Street, Manchester, M1 1AB",
    "provision_sub_type": "Children's home",
    "home_type": "social and emotional difficulties",
    "home_capacity": "4",
    "responsible_individual": "Jane Smith",
    "registered_manager": "John Doe",
    "inspection_dates": "15 March 2024",
    "inspection_type": "Unannounced",
    "overall_experiences_and_progress": "Good",
    "help_and_protection": "Good",
    "leadership_and_management": "Outstanding",
    "date_of_last_inspection": "10 September 2023",
    "overall_judgement_at_last_inspection": "Good",
    "enforcement_action_since_last_inspection": "None",
    "inspectors": "[{\"name\": \"Sarah Jones\", \"role\": \"Social Care Regulatory Inspector\"}]",
    "pdf_url": "https://files.ofsted.gov.uk/v1/file/..."
}

Also downloadable as JSON, CSV, Excel, or HTML from the run's Storage tab — or live via the Apify API.

---

📋 Input

Field	Type	Required	Default	Description
start_urls	Array	Yes	—	One or more Ofsted search page URLs
apify_api_token	String	Yes	—	Your Apify API token (used to call the Ofsted scraper)
ofsted_actor_id	String	Yes	UNmCLFqfH6zXa3Mfc	Actor ID of the underlying Ofsted scraper
supabase_url	String	Yes	—	Your Supabase project URL (https://xxxx.supabase.co)
supabase_api_key	String	Yes	—	Supabase service role or anon key
supabase_table	String	Yes	ofsted_inspections	Target table name in Supabase
latest_report_date_start	String	No	Today	First publication date to include (YYYY-MM-DD)
latest_report_date_end	String	No	Today	Last publication date to include (YYYY-MM-DD)
only_latest_inspection	Boolean	No	true	Return only the most recent inspection per provider
include_unsupported	Boolean	No	false	Include providers with unsupported inspection types
max_reports	Integer	No	10	Stop after this many reports. 0 means no limit; use it for scheduled syncs.

---

🗄 Supabase table setup

Run this SQL once in your Supabase SQL editor before the first run:

CREATE TABLE ofsted_inspections (
    unique_reference_number                  TEXT NOT NULL PRIMARY KEY,
    pdf_url                                  TEXT,
    registered_provider                      TEXT,
    registered_provider_address              TEXT,
    provision_sub_type                       TEXT,
    responsible_individual                   TEXT,
    registered_manager                       TEXT,
    inspection_dates                         TEXT,
    inspection_type                          TEXT,
    overall_experiences_and_progress         TEXT,
    help_and_protection                      TEXT,
    leadership_and_management                TEXT,
    date_of_last_inspection                  TEXT,
    overall_judgement_at_last_inspection     TEXT,
    enforcement_action_since_last_inspection TEXT,
    inspectors                               JSONB,
    home_capacity                            TEXT,
    home_type                                TEXT
);

The unique_reference_number primary key is what enables upserts — re-running updates existing rows instead of inserting duplicates.

---

💡 Use cases

• Regulatory monitoring — automatically track newly published inspection outcomes for children's homes and social care providers.
• Quality dashboards — sync Ofsted ratings into Supabase and power a live Metabase, Grafana, or Retool dashboard — no manual CSV imports.
• Compliance alerting — run on a schedule; query Supabase to detect rating changes or new enforcement actions since the last sync.
• Research & due diligence — aggregate inspection histories across hundreds of providers for SQL/BI analysis.
• Replace Make/Zapier pipelines — eliminate the middleware layer and per-operation costs by keeping the pipeline on Apify.

---

❓ FAQ

Why do I need an Apify API token? The sync runs the Ofsted scraper in your Apify account, so its usage is billed to you. The token is stored as a secret.

Do I need to create the table? Yes, once, with the SQL above.

Which Supabase key should I use? The service_role key. The anon key usually can't write when Row Level Security is on.

How do I keep the table up to date? Schedule a daily run in the evening, with the dates empty and Max Reports set to 0.

Why did I only get 10 reports? Max Reports defaults to 10. Set it to 0.

Can I load older reports? Yes, a week or two per run. Longer ranges can hit the scraper's 1-hour time limit.

Will re-running create duplicates? No. Rows are updated by reference number, so re-running a failed run is safe.

Is it legal? The reports are public under the Open Government Licence v3.0. Handle any personal data in them under UK GDPR.

---

📮 Support

Bugs, feature requests, or custom database targets (PostgreSQL, BigQuery, Airtable) — open an issue on Apify or email alkausarimujahid@gmail.com.

---