AdvocacyAPI — Data Overview

Current Database

Representatives by Source

Recent Syncs

Data Sources & Coverage

Representatives

Postcode Mapping

Council Spending

Councillor Email Coverage (Modern.gov)

~200 UK councils use the Civica Modern.gov platform for their democracy portals. These expose a mailing list endpoint (/mgCommitteeMailingList.aspx?EM=1&ID=0) that lists all councillor names and emails as CSV-formatted text.

Two URL patterns:

• https://{council}.moderngov.co.uk — hosted by Civica (most common)
• https://democracy.{council}.gov.uk — self-hosted by the council

Verified working

Councils not using Modern.gov

Some councils use other platforms where a different scraping strategy would be needed:

Scaling approach

The enricher at lib/advocacy_api/enrichers/moderngov.ex maps council names (as they appear in the Open Council Data CSV) to their Modern.gov base URL. Adding a council is one line:

@council_urls %{
  "Brighton and Hove" => "https://democracy.brighton-hove.gov.uk",
  "Doncaster" => "https://doncaster.moderngov.co.uk",
  # add more here...
}

Name matching between Open Council Data and Modern.gov is near-perfect — tested with Brighton and Hove where 53/53 names matched exactly (only the “Vacant” seat was unmatched).

To build out the full mapping, the URL slug can often be guessed from the council name, but not always (e.g., “Brighton and Hove” → brighton-hove, “South Cambridgeshire” → scambs). A manual mapping or a discovery script that tries both URL patterns would be the next step.

Councillor Email Coverage (CMIS)

~60 UK councils use CMIS by Astech Consultants for their democracy portals. Unlike Modern.gov (which exposes a single mailing-list endpoint), CMIS requires a two-step scrape: first fetch the councillors list page to discover individual councillor URLs, then visit each detail page to extract the email address.

The enricher at lib/advocacy_api/enrichers/cmis.ex handles this automatically with concurrent detail-page fetching.

Three URL patterns:

• https://{slug}.cmis.uk.com/{slug}/Councillors.aspx — hosted by Astech (most common)
• https://cmis.{council-domain}/cmis5/Councillors.aspx — self-hosted CMIS 5
• https://cmispublic.{council-domain}/cmis/Councillors.aspx — self-hosted public

Scaling approach

Adding a council requires one line in the @council_urls map:

@council_urls %{
  "Birmingham" => "https://birmingham.cmis.uk.com/birmingham/Councillors.aspx",
  "Dudley" => "https://dudley.cmis.uk.com/Councillors.aspx",
  # add more here...
}

The council name must match exactly what appears in the Open Council Data CSV. The scraper visits each councillor’s detail page concurrently (4 at a time) so enrichment takes longer than Modern.gov but is still practical.

Area-Level Datasets

The API attaches statistical datasets to each postcode via its geography codes (LSOA, MSOA, constituency, local authority). Each dataset has typed headline columns for fast queries and a JSONB detail column for full breakdowns.

LSOA-Level Datasets (Lower Layer Super Output Areas)

LSOAs are small statistical areas of roughly 1,500 people / 650 households. England has 32,844 LSOAs. These provide the most granular neighbourhood-level data.

MSOA-Level Datasets (Middle Layer Super Output Areas)

MSOAs are larger areas of roughly 7,500 people. England has 6,791 MSOAs. Used for data that is too volatile at LSOA level.

Constituency-Level Datasets

Local Authority-Level Datasets

NHS integrated care boards

Table: care_boards — ods_code, name, region, postcode_count, papers_collected.

Source: derived from the ONS Postcode Directory, not typed in. AdvocacyApi.CareBoards.refresh/0 reads the postcodes.io icb lookup table when the full dump has been imported (giving both code and name) and otherwise falls back to the distinct values on postcodes.icb.

What we hold: the bodies, and which postcodes and councils fall in each. This is what makes a postcode lookup return a care board.

What we do not hold: board membership, meeting dates, papers, minutes and decisions. papers_collected is false for every row, and every screen says so in words rather than rendering an empty table. Collecting them means scraping 42 separate websites and has not been built.

Caveat: before the directory backfill has run, postcodes.icb carries the ODS code rather than the resolved name, so a board can be stored with its code as its name. CareBoardHTML.title/1 labels that case as “Care board QNX” rather than presenting a code as a name.

Council spend (creditor payments)

Everything below was measured against Brighton and Hove on 8 September 2026. It is one council’s shape, not a national schema.

Source: the council publishes monthly creditor payment CSVs. The index page /payments-over-ps250 links one page per financial year, each of which links that year’s monthly files. 52 monthly files are published, April 2022 to July 2026 — every month in that range, with no gaps. Before that the council published quarterly, and only partly: there are files for April–June 2021, July–September 2021 and January–March 2022, and October–December 2021 is not published in any form. Licence: Open Government Licence v3.0.

data.gov.uk is not a way in. The entry for this dataset is a dead stub — “There are no links to this data” — despite being marked updated 8 July 2026. The council’s own pages are the only route.

The threshold is £250, not the statutory £500. Brighton dropped from £500 in April 2013, so the over-£500 register is a filter over this data rather than a separate publication. 52–55% of rows clear £500.

Shape: eight columns, stable across every month sampled (June 2025, January 2026, April 2026, July 2026 — 37,373 rows):

Creditor Name, Transaction Number, Date, Net Amount, Expenditure Summary, Category, Directorate, Section

8,200–10,700 rows and £43m–£64m per month, across ~1,300–1,400 distinct creditors.

Traps in the file

• Encoding is Windows-1252, not UTF-8. AtkinsRéalis UK Limited arrives as AtkinsR<0xE9>alis. £ also turns up inside text fields (H4U Thank you payment(£350), 246 rows).
• Line endings are CRLF throughout, including a terminator on the final line.
• Fields are quoted when they contain a comma ("Community Transport (Brighton, Hove & Area) Ltd", "Elections, Reg Serv and LLC"). Backslash is a literal character, not an escape — Telephones - Calls \ Rental is the actual value.
• Amounts carry 0, 1 or 2 decimal places. In July 2026: 5,632 rows with none, 1,330 with one, 3,702 with two. 6624 means £6,624.00.
• ~11% of rows are negative (930–1,300 a month) — credit notes and reversals.
• 44–49% of rows are Payment to Individual — redacted, with no attributable supplier. Published in four spellings: capital and lowercase I, each with and without a trailing space (17,208 / 16 / 10 / 1 across the sample).
• Creditor names are truncated at exactly 50 characters, mid-word. About 130 rows a month sit on the cap, e.g. Salutem LD BidCo IV Ltd t/a Ambito Care and Educat. These are prefixes and will not match a contract’s full spelling.
• 82 distinct names carry stray leading or trailing whitespace, and 7 of them collide with the same name written cleanly.
• Transaction Number is not unique — about a third of refs appear on more than one row, one per invoice line. Across the four-month sample the full published row is unique both within and across files, and no transaction ref is reused in a later month, which is what row_hash relies on.

Traps in getting the file

• The site is behind an Imperva WAF. A client with no cookie jar gets 302 Location: <the same URL> with two set-cookie headers, forever — curl stops at 50 redirects. Returning the cookies on a second request gets a 200. The CSVs themselves are static files under /sites/default/files/ and need no handshake.
• File URLs cannot be constructed, only discovered. They sit under /sites/default/files/<upload month>/, which is not the data month: July 2025’s file is under 2025-09. Filename casing drifts (Over vs over), some carry a - CSV suffix, some encode the £ sign (Over %C2%A3250 August 2023), and Drupal appends _0 on re-upload (June 2024_0.csv, December 2022_0.csv) — that underscore defeats a \b word boundary when matching the year.
• Three files cover a quarter, not a month (April-June 2021, July-September 2021, January-March 2022). The loader refuses to date these rather than filing a quarter under its first month; loading them needs a window granularity that can express a quarter. Note that these three do not tile the period either — nothing covers October to December 2021.

Where the code lives

• AdvocacyApi.Scrapers.CouncilSpend — link discovery, WAF handshake, window walking. Source key council_spend, one window per council per month on ingest_windows.
• AdvocacyApi.Payments.CreditorCsv — the Brighton parser.
• AdvocacyApi.Csv — RFC 4180 reader and the CP1252 sniff.
• AdvocacyApi.Payments — storage, idempotent on row_hash.

Council contracts register (Brighton)

Brighton publishes its own contracts register on its In-tend e-tendering portal. It holds 1,734 contracts against Contracts Finder’s 99 for the same council, because most council procurement is a framework call-off or a delegated award and neither generates a national notice. Three suppliers with no Contracts Finder notice at all appear here with a contract: FM Conway, Van Oord and Achieve together.

One public GET, no login, no paging: https://in-tendhost.co.uk/brightonandhove/aspx/ViewContractsRegister.aspx?id=dec104fe-f8cb-4735-90b4-bffa79ed9a82 — 2.1 MB of HTML holding one table of 1,735 rows.

Six columns, and six is all there is: Reference | Title | Suppliers | Extension Date | Expiry Date | Value. The table contains zero hrefs — there are no per-contract detail pages, only an ASP.NET postback into the same page — so there is nothing more to crawl.

Traps in the page

• No Date Provided is a value, not a missing cell — 1,185 rows in the Extension column, 531 in Expiry. The widget marks those cells data-sort="1899-01-01", an Excel epoch sentinel.
• Dates are DD Month YYYY; values are £47,538.00 and are parsed as Decimal, never float.
• An extension date can precede the expiry beside it (BHCC - 009438 expires 09 Jan 2019, extends to 08 Jan 2020). The two columns are not a period; only Expiry becomes ends_on and the extension goes to metadata.
• No start date and no award date are published, whatever the page’s own description claims, so neither is written.
• A reference can appear on more than one row — BHCC - 008176 is published twice, once per supplier, under one internal id. Rows are grouped by reference before writing or the second row’s supplier would replace the first’s.
• £0.00 is 346 of 1,734 rows and often means “not recorded”: Van Oord’s coastal scheme is published at £0.00 and has been paid £4.2m. Any total over this source is a floor.
• 75 supplier cells carry stray whitespace, and all 45 distinct names among them are also published cleanly elsewhere. Trimming merges them.
• A Suppliers cell is sometimes a list and sometimes one name with a comma in it — ApexPrimeCare Ltd, Care Outlook Ltd, … against change, grow, live (CGL) and Knight, Kavanagh & Page. 24 cells contain a comma and it is a separator in exactly half of them. The splitter takes a cell only when it has three or more fragments, each with balanced brackets and either two words or a capitalised single word; a cell it refuses is stored whole and flagged in the contract’s metadata.

Not on the ingest_windows ledger

Unlike council spend. A window is a period, and this source has none — one URL returns the whole register as it stands. Filing it under invented months would fill the coverage matrix with green cells that all mean “we fetched the same page again”. A run is recorded in sync_logs instead.

Where the code lives

• AdvocacyApi.Scrapers.CouncilRegister — fetch, parse, group, upsert. Source key council_register.
• Idempotent on (source, source_ref) — the council’s own reference — via the partial unique index in 20260909111010_add_council_register_contract_key.
• bin/advocacy_api rpc 'AdvocacyApi.Release.load_register("Brighton and Hove")'.

Staged Source Files

Every URL hard-coded in priv/scripts/import_*.exs had rotted (404/403) by September 2026. These are the replacements, re-verified, downloaded and staged in /opt/advocacy-api/priv/data/ on the VPS — a directory the deploy rsync excludes, so it survives deploys.

Almost none of them are CSV any more. They were converted with a stdlib zipfile/openpyxl/xlrd pass, taking the named sheet and skipping the gov.uk preamble rows; the Join column is the measured overlap against the codes actually in postcodes (not an estimate).

Notes on the replacements

• The old hpssadataset09 URL was wrong twice over: dataset 46 lives under its own slug (medianpricepaidbylowerlayersuperoutputareahpssadataset46), and is a zipped legacy .xls, not a CSV. Its 114 columns are quarterly medians from Dec 1995 to Mar 2023 — the last is the headline, the rest belong in house_price_data.
• Child poverty is no longer published at LSOA, so it cannot fill lsoa_data.child_poverty_pct. DWP took over the after-housing-costs method for 2026 and End Child Poverty now publish LA and constituency only. Either point the figures at constituency_data / local_authority_data, or take LSOA numbers from DWP Stat-Xplore’s Children in Low Income Families release.
• Ofcom no longer publishes a single average speed. The OA file gives average download/upload banded by line speed (<10, 10<30, … >=900 Mbit/s), so broadband_avg_download has to be derived; the bands themselves fit broadband_data. It is Output Area, finer than LSOA — aggregate via postcodes.oa21.
• Life expectancy is now 2019–2023, not 2018–2020, and one file covers both tiers: Area type = 'MSOA' feeds msoa_data, 'Local Areas' feeds local_authority_data. Sheets 2 and 3 add healthy and disability-free LE.
• Flood risk measures % of population exposed, not % of properties as the LSOA table above claims.
• GE2024 vote counts do not come from the Commons Library. CBP-10009 sits behind a Cloudflare JS challenge that no scripted client can pass — hence the 403s. electionresults.uk republishes the same results under the Open Parliament Licence with electorate, valid votes, turnout, winner, runner-up and majority, keyed by constituency name (no ONS code).
• EPC still needs a registered key. epc.opendatacommunities.org now redirects to get-energy-performance-data.communities.gov.uk, whose bulk endpoints require registration; there is no anonymous download.
• import_crime.exs keeps only LSOAs starting E, so Welsh crime is discarded even though the source covers Wales.

Data Freshness

Key Limitations & Caveats

1. England-centric: Most LSOA-level datasets cover England only (or England & Wales). Scotland and Northern Ireland publish equivalent data but using different geographies and definitions, which are not yet integrated.

2. Boundary mismatches: IMD 2019 and some other datasets use 2011 LSOA boundaries, while Census 2021 introduced new 2021 LSOA boundaries. Most LSOAs are unchanged, but ~5% were split or merged. We join on LSOA code which handles this for matching LSOAs, but some 2011-only LSOAs may not match 2021 codes.

3. Point-in-time snapshots: Most datasets are annual or less frequent snapshots. The API returns the most recently imported version — it does not track changes over time (except house prices which include historical data in the detail JSONB).

4. Modelled vs measured: Several datasets (fuel poverty, child poverty, air quality) are modelled estimates, not direct measurements. They carry inherent uncertainty that increases at smaller geographies.

5. Small area suppression: DWP benefits data suppresses counts below 10 to prevent identification. This means the most deprived areas (few claimants) may show as null rather than a small number.

6. No real-time data: All datasets are retrospective. Crime data has a ~2 month lag, benefits data ~1 month, house prices ~3 months from transaction to publication.