Sync Ofsted Reports Data with 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.

Built and maintained by Alkausari M.

---

✦ 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
{
    "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": "01-01-2024",
    "latest_report_date_end": "31-12-2024",
    "only_latest_inspection": true,
    "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 date filters (DD-MM-YYYY). Leave blank to fetch all reports.
• only_latest_inspection — true (default) gives one record per provider; useful for dashboards and monitoring.
• max_depth — how deep the underlying scraper crawls linked pages. Default 3 covers most search results.

---

📦 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	—	Filter from this date (DD-MM-YYYY)
latest_report_date_end	String	No	—	Filter up to this date (DD-MM-YYYY)
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_depth	Integer	No	3	Crawl depth for the underlying Ofsted scraper

---

🗄 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 my own Apify API token? This Actor orchestrates the Ofsted Reports Data Scraper via the Apify API — that scraper is what actually extracts data from the PDFs. Your token authenticates that call. It's marked as a secret and never logged.

Do I need to create the Supabase table myself? Yes — use the SQL above. The Actor only writes into an existing table; it does not create one.

Should I use the service_role or anon key? Use service_role if your table has Row Level Security enabled; the anon key may block inserts.

Can I run this on a schedule? Yes — use the Apify Schedule feature. Each run upserts new and updated records, leaving unchanged rows untouched. Ofsted typically publishes inspection batches on Tuesdays and Thursdays.

What if a run fails halfway? Re-run the Actor. The upsert ensures already-processed records are updated, not duplicated — no data is lost.

Is this legal? Ofsted publishes inspection reports as public information under the Open Government Licence v3.0. This Actor accesses only publicly visible report data. Handle any personal data in reports in accordance with UK GDPR.

---

📮 Support

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

---