Waste AI Search

AI-assisted waste site activity data discovery using Azure Foundry.

This repository is designed to take a post-consolidated facility waste activity dataset, enrich the search context with cached reverse-geocode fields when available, call a Foundry-compatible search client, and write a review-ready Excel workbook.

uv --cache-dir .uv-cache sync

Run tests:

PYTHONPATH=tests/stubs:src uv --cache-dir .uv-cache run pytest -q

For live Foundry mode, create an Azure AI Foundry Agent with web search enabled, then copy .env.example to .env or export the same variables:

cp .env.example .env

Required values:

AZURE_AI_PROJECT_ENDPOINT=
AZURE_AI_AGENT_NAME=

AZURE_AI_AGENT_ID is optional, but useful for older Azure AI Projects agent APIs or if multiple agents share a name.

Authentication uses DefaultAzureCredential, so local development can use:

az login

For service principal or future Azure Container Apps Jobs, set:

AZURE_TENANT_ID=
AZURE_CLIENT_ID=
AZURE_CLIENT_SECRET=

Check the local environment without making a live Foundry call:

uv --cache-dir .uv-cache run scripts/check_environment.py

Default outputs:

outputs/runs/<run_id>/seed.csv
outputs/runs/<run_id>/consolidation_snapshot/
outputs/runs/<run_id>/<run_id>_candidate.xlsx
outputs/runs/<run_id>/raw_foundry_responses/site_<site_id>.json
outputs/runs/<run_id>/status/site_<site_id>.json

search and run read consolidation.consolidated_facility directly. There is no seed file to generate or keep current, and no inputs/ folder — the database is the source of truth, and a checked-in export is stale the moment consolidation moves.

uv run python -m waste_ai_search.cli run --iso3 MEX      # seeds from the database
uv run python -m waste_ai_search.cli run --input-csv seed.csv   # pinned corpus instead

--iso3 is pushed into SQL, so a single-country run reads only that country rather than pulling all 19,492 facilities to keep 83.

A run reads the database once. The first read is written to outputs/runs/<run_id>/seed.csv, and every later phase of that run reads the snapshot instead of querying again — both arbitrations, the refresh between passes, a resumed search, and a standalone arbitrate. So every phase sees the corpus the search ran against, arbitrate never needs the database (it makes no agent calls; with blob storage configured it pulls from blob, so pass --local to stay fully offline), and seed_source in the workbook's Run_Config records where the run is pinned.

A run id is therefore pinned to its corpus. To seed afresh, use a new --run-id. Only the search seeds a run: arbitrate and refresh-seed on a run with no seed.csv (a mistyped --run-id, or a run from before seeds were pinned) stop and say so rather than read today's database, which may no longer match what was searched.

A run seeded with --input-csv is pinned the same way: the supplied file is copied, byte for byte, into the run's seed.csv on first use, and every later phase reads that copy — so the run can still be re-arbitrated after the file is moved or deleted. Passing a different file to an existing run id stops with an error rather than searching one corpus while the snapshot records another; use a new --run-id. The one exception is the pass-2 follow-up's refreshed_seed.csv, which is read as given once the original snapshot exists and never replaces it. Any other CSV is pinned and checked against the snapshot, even one stored inside the run directory.

--input-csv remains for two cases: pinning an exact corpus, and the gas-collection follow-up, which rewrites the seed between passes into the run directory.

A run also keeps a copy of the consolidation tables it searched (F32). Every consolidation rebuild reassigns internal_facility_id, which is every output's site_id, so once the database moves on a run's results no longer point at their facilities. Before seeding from the database, a run saves outputs/runs/<run_id>/consolidation_snapshot/, which goes to blob with the rest of the run:

data_source + facility_id in the crosswalk is the key that survives a rebuild: it maps a run's site_id to its raw records now, and onto the new internal_facility_id later. That is also how a later run can search only what changed. Tables are read whole (every country, not just those searched) in one read-only transaction, so they describe one consolidation state; the run stops if consolidation is rebuilt while it is seeding. Blob transfers go in stages - data, then manifest.json, then seed.csv last - so a resumed run whose copy lacks the manifest, or a file it lists, stops rather than carry on without its consolidation state, and a seed with no snapshot folder at all can only be a run that never had one. A run seeded with --input-csv reads no database, so it takes no snapshot.

A run therefore needs database access. Without the VPN, seed a file first with scripts/seed_metadata_search.py and pass it with --input-csv.

At the end of the search phase, every cited URL is fetched once and given a verdict, saved to link_check.json in the run folder. Ported from refining-ai-search (RDP-63):

A broken URL is retried with a literal comma for %2C and with the trailing slash toggled; a respelling that works replaces it. Existing escapes are never decoded otherwise.

Only public addresses are ever contacted. Cited URLs are untrusted and are fetched from a machine that is often on the VPN, so every hop — redirects included — is resolved first and refused if any address is private, loopback, link-local (including the cloud metadata address) or otherwise non-public. The connection then goes to the address that was checked, so a second DNS answer cannot redirect it. A refused URL is Unverified and never fetched; a malformed one is Unverified too, rather than stopping the check.

The fetching happens during search, which needs the network anyway; arbitrate only reads link_check.json, so it stays offline. A value with one dead source and one working source is still promoted from the working one.

uv run waste-ai-search check-links --run-id <run_id>   # backfill an older run, then arbitrate
uv run waste-ai-search run --no-link-check ...          # skip checking
uv run waste-ai-search arbitrate --keep-broken-link-evidence ...  # let dead sources stay promotable

Run_Config records links_checked, links_broken, links_repaired and broken_link_route, so every workbook states which policy produced it.

Each run folder is mirrored to blob storage at outputs/runs/<run_id>/ (adapted from refining-ai-search RDP-52):

• Before searching, any earlier attempt at the same --run-id is pulled down, so responses already in storage are reused instead of paid for again. A local file is replaced only by a newer blob copy - another machine's later work, such as fresh link verdicts - and the run's seed.csv and consolidation_snapshot/ never are, since they pin the run's corpus.
• A search that fails or is interrupted still pushes what it finished.
• Raw responses go up as soon as a search pass finishes, so paid-for results are safe even if arbitration fails; the whole folder goes up again after arbitration.
• arbitrate pulls first, so a run searched on another machine can be arbitrated here.

Blob is used once it is configured — set AZURE_STORAGE_ACCOUNT (signed in with az login) or AZURE_STORAGE_CONNECTION_STRING. Until then every run says "results stay local only". --local or WASTE_AI_SEARCH_LOCAL=1 keeps a run local regardless.

The account sits behind a firewall: off the RMI-SP-FLEX-VNET VPN, Azure answers AuthorizationFailure, which looks like a credential problem but is not. The CLI says so and exits.

After arbitration, the review workbook is copied to WASTE_AI_SEARCH_REVIEW_DIR/<run_id>/, a SharePoint folder synced locally by OneDrive, which uploads it. --no-publish skips this.

An SME's work is never overwritten. Every published file is fingerprinted in the run's published.json. Republishing replaces a SharePoint copy only if nobody has saved it since; a copy that has been edited, or is open in Excel, is left alone, and the new version is written beside it with a timestamp.

One command does everything:

uv run waste-ai-search run --run-id pilot10 --iso3 NGA PHL --limit 10

That searches, arbitrates, and — if any facility turns out to have a gas collection system the seed did not know about — automatically runs a second, narrow pass for that facility's gas-capture attributes and re-arbitrates. Same run id, same output directory, no reseeding by hand. Pass --no-followup to stop after the first pass.

Why a second pass exists at all: gas-capture attributes are only asked of facilities known to have a collection system, and has_landfill_gas_collection is itself one of the searched attributes. A facility whose system is discovered mid-run therefore needs one more, cheap request. The follow-up is cached separately (site_<id>__gccs.json) so it never overwrites the first pass, and arbitration merges both.

Or drive the phases yourself — useful for long runs, because arbitrate is free and you can run it as often as you like against a search that is still in progress:

# Phase 1 - query Foundry, cache one raw JSON response per site. Resumable.
uv run waste-ai-search search --run-id pilot10 --iso3 NGA PHL --limit 10

# ...or sample a spread instead of the first N. --limit follows seed order, which is
# facility-id order and clusters by country; --pilot-size draws across countries and
# deliberately includes generic-named hard cases.
uv run waste-ai-search search --run-id pilot10 --pilot-size 10

# Phase 2 - rebuild every output from the cached responses. No agent calls, re-runnable.
# With blob storage configured this pulls from blob first (VPN); add --local to stay fully offline.
uv run waste-ai-search arbitrate --run-id pilot10

# Phase 3 - `run` does this for you. By hand: fold promoted values back into the seed, then
# search ONLY the sites where gas collection was discovered - refresh-seed prints the command.
uv run waste-ai-search refresh-seed --run-id pilot10
uv run waste-ai-search search --run-id pilot10_pass2 \
  --input-csv outputs/runs/pilot10/refreshed_seed.csv --site-ids <ids printed above>

Never search refreshed_seed.csv without --site-ids. It holds the whole seeded corpus (19,492 facilities), not just the sites the run searched, so it would search all ~12,000 searchable sites again (about $3,300). By hand, the narrowed search also re-asks those sites every attribute still missing; run asks only their gas-capture attributes.

Re-running search skips sites that already have a cached response unless --force is passed, so an interrupted long run resumes where it stopped. Because arbitrate reads the cache rather than the network, a change to the credibility rubric can be re-applied to a completed run at no cost.

Sites are searched 4 at a time (--workers, on search and run; 1 = one at a time). A search mostly waits on Foundry, so the waits overlap: 10 sites took 1 min 36 s with 4 workers, against about 4 minutes one at a time, with no rate limiting. Progress prints as each site finishes, so the order is not seed order. Ctrl-C cancels the sites not yet started and waits for those in flight, which are still logged.

Each attempt at a site is cut off after --hard-site-timeout-seconds (240), retries included: a call still running then is abandoned and not retried. The HTTP timeout (AZURE_FOUNDRY_TIMEOUT_SECONDS) cannot do this - one site waited 15 minutes past it.

Run the batches in order, one run id each, so each gets its own review workbook and its own Bing total. Batches 1-2 are English-speaking, so SMEs learn the review process on familiar sources; 3-5 follow the most blank core fields; 6 is every other country. The lists are COUNTRY_BATCHES in schema.py. Sizes and costs are from the 3 Oct 2026 seed at about 19 Bing queries per site.

uv run waste-ai-search run --run-id batch1 --batch 1 --workers 16

The six cover all 12,122 searchable sites once each. USA and Brazil stay excluded, as everywhere.

Three rules narrow the search. Together they mean a full run queries 12,392 of 15,537 facilities and makes 167,410 attribute-requests instead of 22 x 15,537 = 341,814.

1. United States facilities are excluded by default — 3,145 sites, 20% of the corpus.

US landfills are already covered by usa_ghgrp_2026 and lmop_2024, both Tier 1. An AI search there mostly produces equal-tier conflicts for a human to adjudicate rather than new data. Override in any of three ways — an explicit request always beats the default:

uv run waste-ai-search search --run-id r --include-excluded-countries   # search everything
uv run waste-ai-search search --run-id r --iso3 USA                     # naming it wins
uv run waste-ai-search search --run-id r --site-ids 499                 # listing ids wins

The run prints how many sites it dropped, so this is never silent.

2. Brazil is excluded by default. When searched, it is coordinates only, and only where the location is flagged inexact — 3,913 of 4,225 sites, all from SINIR.

For a Brazilian facility, the search asks for found_latitude and found_longitude and nothing else — and only when is_location_exact is FALSE. Every other Brazilian attribute comes from a government source, so searching it risks overwriting better data than the search can find.

SINIR's coordinates are the centre of a municipality, not the dumpsite, and most of its names are generic ("Lixão", "Aterro Controlado"). So the seed carries the municipality and state SINIR recorded (facility_city_location, an IBGE code, named from raw_sinir_general_city_data) into municipality and admin1, and the prompt passes them on. Without them the agent guessed the town from the city-centre point and, on the WP-543 test, returned dumps 140 and 237 km away.

With the municipality the agent found the right facilities, but in reports that publish no readable coordinates: 0 of 20 sites got usable coordinates either way. So Brazil is excluded by default like the US, and searching it is opt-in with the same three overrides (--iso3 BRA, --site-ids, --include-excluded-countries). Matching against OSM/GPW landfills within the municipality is the likelier fix.

Unknown is not treated as inexact: only an explicit FALSE triggers a coordinate search.

3. Everywhere else: gap-fill, plus the gas-capture gate.

• Identity (found_facility_name, found_latitude, found_longitude) is requested except where the seed's value came from a Tier 1–2 source: the name for about 10,600 facilities, and both coordinates for 4,063 of the 12,122 searchable sites (Mexico INEGI, E-PRTR, Canada GHGRP). This needs per-attribute provenance; with none, the attribute is still asked. Coordinates flagged inexact are still searched.
• Where both are trusted, the wrong-facility check does not run. That check compares the found coordinates with the seed and withdraws auto-validation beyond 5 km; with no found coordinates there is nothing to compare. Accepted to cut cost (design doc F29): skipping coordinates saves 8,126 of 175,293 attribute-requests (4.6%).
• Metadata attributes are requested only where that facility's baseline is empty.
• The seven gccs_* attributes are requested only where gas collection is known present — 212 to 603 facilities each, not 13,000. See the two-pass note above for why this loses nothing.

Both country rules live in schema.py as DEFAULT_EXCLUDED_ISO3 and COORDINATES_ONLY_ISO3, so changing policy is a one-line edit.

Why these rules exist, in one line each: the US is already Tier 1 covered by GHGRP and LMOP, so searching it manufactures equal-tier conflicts rather than data; Brazil's non-coordinate attributes are government-sourced and should not be overwritten by a web search, and its coordinates were not found by web search on the WP-543 test.

A site left with no attributes to search is dropped from selection entirely — querying it would spend a request and a prompt to receive nothing.

At the measured 42s/site that is roughly 6.3 days serial, down from 8.0.

23 attributes are searched. No site is asked for all 23 — the scoping rules above decide which ones a given facility gets. Three more gas-capture volumes are no longer searched but are still read from the seed and from older cached responses (see below).

Identity — requested unless a Tier 1–2 source already supplied it. These are the evidence that the agent researched the right facility. Found coordinates drive the distance check and the 5 km identity gate; where they are not searched (F29), that check does not run.

Newer sources win. The agent is told to prefer the most recent source when sources disagree, and to base facility_status on the latest evidence it can find (a closure notice, current opening hours, recent news) rather than an older permit or directory listing. Arbitration follows the same rule: within a tier the newer value_date wins, and for facility_status only, a lower-tier source down to Tier 3 may win when it is at least 3 years newer than the source or baseline it replaces. Such a win always goes to review, and resolution_rule states the dates.

found_facility_name must be specific: a name that identifies the site on its own ("Wonthaggi Transfer Station"), not a generic label ("Waste Management Facility"). If a source gives only a generic name, the agent adds the municipality, town or operator that the same source names ("Bass Coast Shire Waste Management Facility"), never a place the source doesn't mention.

Gap-fill — requested only where that facility's baseline value is empty.

‡ Review-layer only: operator, bulk_waste_type, has_flare, flare_efficiency. The upstream standardized spec has no column for them yet, so a found value reaches resolved.csv but never the standardized table. Like every attribute, a Tier 1–2 value auto-validates into resolved.csv; Tier 3 and below go to the review queue instead. Promoting them is a follow-up once upstream adds the columns. consolidated_facility has none of them either, so each is asked of every facility its scoping rules allow.

bulk_waste_type is the waste that makes up most of what a site receives. It separates municipal solid waste (household and similar waste, which decomposes and generates methane) from inert waste (construction and demolition rubble, soil, stones); others is mainly industrial, hazardous or mining waste or sludge. A site taking mostly municipal waste plus some rubble is municipal solid waste. There is no unknown: with no source on the waste received, or no one main type, the value stays NULL, so the site is asked again on a later pass.

waste_depth is the only derived categorical. The agent still reports a number and its unit; the pipeline converts to metres, then bins on the spec's 5 m boundary. The metre value is kept in normalized_value as evidence and the category lands in the column. A depth of zero or less is not a measurement, so it is stored NULL rather than binned to <=5m.

has_biocover is not derived from cover_types — a plain soil, clay or sand cover is not a biocover, and the prompt says so explicitly. consolidated_facility now carries the column but it is entirely NULL today, so the gap-fill rule still fires for every facility.

Gas capture — additionally gated on has_landfill_gas_collection being known TRUE. Asking a facility with no capture system about methane flaring buys a certain "nothing". Nothing is lost by the gate: has_landfill_gas_collection is itself gap-filled, so refresh-seed promotes a discovery into the baseline and the next pass picks up that facility's GCCS attributes.

No longer searched (F31): gccs_ch4_generated_metric_tonnes, gccs_ch4_collected_metric_tonnes and gccs_ch4_flow_to_project_metric_tonnes. They are still read from the seed, and a run cached before the change still re-arbitrates them into their standardized columns.

Computed locally, never searched.

The agent is asked for the source's original unit and the pipeline converts to the spec's canonical unit. A unit it cannot convert produces a warning and the value is not promoted, so an unconverted number can never land in a column whose name asserts a unit.

Bold entries are the spec's own conversion table. Three deliberate refusals: an ambiguous "tons" is never converted (metric vs short is a 10% error); a landfill-gas volume is refused because the spec's formula needs a methane fraction the source did not give; and a value whose unit cannot be identified is refused rather than assumed.

Allowed categorical values come from the live Postgres enums — regenerate with scripts/generate_enums.py. Anything outside them maps to NULL with a parse warning rather than being forced into a wrong category.

8,564 facilities (44% of the corpus) come only from OSM (6,848) or Global Plastic Watch (1,716), with nothing but a name and a coordinate. A crowd-mapped landuse=landfill polygon or a satellite detection says nothing about what a site is used for — it can be a quarry or a construction yard.

For those facilities only, facility_type has a fifth allowed value, Not a Waste Facility. The agent returns it only when a source positively says the site is, and always was, something else. It is a contradiction, not a verification: finding nothing leaves facility_type empty, because small informal dumps routinely have no web presence at all.

A closed, capped or redeveloped landfill is not a contradiction. It keeps emitting methane for decades, which is why WasteMAP tracks it. A landfill that is now a park keeps its landfill type, with closure in facility_status = Inactive and closing_year. The question is was this ever a waste disposal site?, not is it operating today?

The value is promoted like any facility_type, so it reaches the standardized table and the next pass's seed. Every such verdict is also routed to review whatever its tier, on the site's own facility_type row in Review_Queue — there is no separate tab. Where the same run also found the site closed, the likeliest misreading, the row's evidence_summary says so.

Pending upstream. The database's facility_type enum and chk_facility_type do not carry Not a Waste Facility yet, so a load containing it will be rejected — and COPY is all-or-nothing, so one such row blocks the whole run's load. check_db_schema.py warns about it. The value needs adding upstream in waste_data_ingestion_pipeline, ideally alongside creating transformed.transformed_ai_search, which does not exist yet either.

Facilities with any other source, or any corroborating attribute, are never offered the value, so the other 10,928 prompts carry the ordinary values and none of this guidance.

facility_type also offers Transfer Station and Recycling Center, both pending upstream like Not a Waste Facility. Neither disposes of waste, so a site that is only one of these is out of scope for methane. A name scan of OSM sites found about 100 in the corpus, 52 of them in batch 1.

A site that is both (e.g. a "Waste Transfer & Recycling Centre") is a Transfer Station if it takes mixed waste, and a Recycling Center only if it takes nothing but separated recyclables.

A transfer station or recycling centre built on a closed landfill is still the landfill. The agent is told to classify the disposal site (Sanitary Landfill, Controlled Dumpsite or Dumpsite) whenever waste was ever landfilled or dumped there, because the buried waste still emits.

Pending upstream. Transfer Station is in chk_facility_type on every transformed.* table, but not yet in the facility_type enum type or consolidation.consolidated_facility's chk_facility_type; Recycling Center is in none of them. Until they are, consolidating a row with either is rejected, as with Not a Waste Facility. Taking a value off PENDING_UPSTREAM_FACILITY_TYPES is a manual step: do it only once check_db_schema.py reports it accepted by both the enum and consolidated_facility's chk_facility_type, which roll out separately. Then regenerate db_enums.py; generate_enums.py does not edit the pending list.

Tier is assigned locally from source_type and publisher, never taken from the agent — measured against the pilot responses, the agent's self-assigned tier put news reports at Tier 2, 3 and 4 simultaneously. Its number is kept as agent_proposed_tier for drift monitoring only.

Baseline credibility is judged per attribute, from consolidation.value_resolution_ledger, not from the facility's composite contributing_data_sources — so an osm_2022 site name is tiered as OSM even when the same facility also has a GHGRP filing. The dataset is shown as baseline_source.

A source with no publication date is capped at Tier 3 whatever its publisher. An empty baseline is filled at any tier — a sourced value beats no value — but only Tier 1-2 fills auto-validate. Once a baseline value exists, only Tier 1-2 may override it.

Allowed values come from the live database, not a hand-kept list — from Postgres enum types where one exists, and from CHECK constraints on consolidated_facility where one does not (waste_depth is the latter):

uv run python scripts/generate_enums.py    # rewrites src/waste_ai_search/db_enums.py

Re-run it whenever the database vocabularies change. See Standardized Facility Schema for how this fits with the vendored spec, and docs/metadata_search_redesign.md for the full design and the accepted risks.

The reviewer opens one file: outputs/runs/<run_id>/<run_id>_review.xlsx. Everything else in the run directory is audit material you only need if a row raises a question.

This is the worklist. It contains only rows the pipeline would not sign off itself — nothing else. An empty queue means nothing needs you.

Rows arrive here for one of two reasons:

• A value came from a weak or unclassifiable source (Tier 3, 4 or 5). It filled a field that was empty, but no one has confirmed it.
• A value disagrees with what the database already holds, at equal or better credibility.

Everything the pipeline was confident about — Tier 1-2 fills, confirmations, and every search that found nothing — is already marked Auto-validated with reviewer = AI Agent, and is not shown here. In the 10-site pilot that was 122 of 136 rows, leaving 12 for a human.

The five columns that decide your judgement:

Rows are colour-coded by resolution, and the tier scale is on the Definitions tab.

Find the site. baseline_coordinates is where the database puts the facility, as lat, lon. Paste it into Google Maps' search box to see the site, and compare it with what the source describes.

Check the name first. site_name is machine-translated; original_site_name beside it is the source's own spelling, in source_language. The search uses the original, so a source about EL HONGO is the right facility even though the row says THE FUNGUS. If the translation is wrong or misleading, say so in translation_note, which sits next to the names, rather than in researcher_notes. The same three name columns appear in every tab and in resolved.csv and evidence.csv; they are blank where the source was never translated.

Source links. winning_link_status beside the URL says whether it was checked and found working (OK) or could not be verified (Unverified — usually a site refusing automated checks, so it may still open for you). A source whose link is dead never wins: it is routed to Leads instead. winning_source_url is clickable, and shows the URL exactly as the source gave it. The link behind it is made safe (spaces and accented characters encoded), so it opens even when the text has them. A URL that is not http/https, or longer than Excel can follow, is shown as plain text rather than linked.

The single highest-value check: identity. Tier cannot detect a perfectly credible document that describes the wrong landfill. If researcher_notes says "Identity not confirmed: source-backed coordinates are N km from the seed location", treat every row for that site as suspect regardless of tier — the pipeline has already withdrawn its own sign-off. In the pilot this caught a site whose findings were 238 km from the facility.

Edit only these columns; everything else is read-only:

Validated means the value is correct and may be merged. Rejected means it must not be.

• Leads — findings that lost: below the credibility floor, weaker than the baseline, or unconvertible units. Read-only. If you judge one good enough, promote it by hand.
• Parse_Warnings — values the pipeline dropped or could not map, each attributed to its site_id and site_name. Worth scanning for systematic problems (a recurring unit or enum failure usually means an upstream fix, not 50 individual reviews).
• Definitions — the allowed values, mirrored from the live database enums.

Bing grounding bills per query, so the run log records the queries each site made in web_searches (every retried attempt, resumed run and follow-up pass included, since each is billed), and the search prints the run's total. Each site's row is written as it finishes, so an interrupted run keeps its counts. One exception: a call abandoned at the time limit is billed for any queries it made, but returns no response to count them from. One live site with 12 attributes made 39 queries. Retries are capped at 2 for both Foundry errors (AZURE_FOUNDRY_MAX_RETRIES) and reported search-tool failures (--search-tool-retries), so a site runs the agent at most 4 times, down from 9. A local .env that still sets AZURE_FOUNDRY_MAX_RETRIES=3 overrides the new default.

Searches per attempt are capped at 6 (--max-web-searches, 0 = no cap). The cap is sent as the Responses API's max_tool_calls, which Foundry enforces; asking in the prompt alone was ignored. It caps searches, not queries — each search sends about 4 queries. Each retry gets a fresh cap, so a site that uses all 4 attempts can make up to 24 searches. On the same 10 sites:

Repeat runs of one site vary widely (one used 12, 30 and 39 queries), so treat the last two rows as noise-level.

sites_to_retry.csv lists sites where the agent's own web-search tool failed. These are not "no data found" — nothing was searched. They need a re-run, not review:

uv run waste-ai-search search --run-id <run_id> --force --site-ids <ids from that file>
uv run waste-ai-search arbitrate --run-id <run_id>

resolved.csv holds only values the agent found and the rules auto-validated. Values awaiting a decision are in the review queue instead, until an SME promotes them; attributes not found are not written anywhere. evidence.csv holds one row per source-backed claim with the tier rule applied to each source, so you can see exactly why a value won or lost. sources.csv is the deduped source registry.

Reverse geocoding is optional but strongly recommended for live pilot runs. The pipeline supports cache-only geocoding through JSONL records with either a site_id or a rounded coordinate key.

Example cache row:

{"site_id":"11489","municipality":"Lagos","admin1":"Lagos","admin2":"","formatted_address":"Olushosun, Lagos, Nigeria","geocoder_provider":"manual","geocoder_confidence":"High"}

Pass the cache to the search phase so the prompt can use municipality and admin names, which matters most for local-language searching:

uv run waste-ai-search search --run-id pilot10 --use-geocode-cache \
  --geocode-cache data/geocode_cache.jsonl

Without --use-geocode-cache those fields stay empty and every site records geocode_status = disabled.

Live Azure Maps batch reverse geocoding should be added later as a separate pre-processing command that writes this cache.

The schema has two sources of truth, and they cover different things. Neither one is enough on its own.

The database wins on structure, and a rename there breaks this repo immediately — which is exactly what happened when area became area_square_meters: refresh-seed started failing with column does not exist before anyone had touched the spec.

But the database carries no column comments and no range constraints on consolidated_facility, so it cannot tell you what a column means or what a valid number is. The clearest case is waste_depth. The database says:

CHECK (waste_depth IS NULL OR waste_depth = ANY (ARRAY['<=5m', '>5m']))

That gives the two permitted strings. It does not say to convert the source's depth to metres first, bin at 5 m, or store NULL for a reported zero. Those rules exist only in the spec's prose, and getting them from the constraint alone would write a bogus <=5m for every source reporting a depth of 0.

uv run python scripts/check_db_schema.py       # do the code's columns and vocabularies match?
uv run python scripts/generate_enums.py        # regenerate src/waste_ai_search/db_enums.py
uv run python scripts/generate_enums.py --check  # is the committed copy stale?

Both need database access (so, the VPN). Exit 1 means real drift; exit 2 means the database could not be reached, so CI can tell a network blip from a genuine mismatch.

generate_enums.py reads allowed values from both places they live: Postgres enum types, and CHECK constraints on consolidated_facility. waste_depth is only in the second — it is a text column with a constraint and no enum type, which is why a generator reading pg_enum alone never saw the depth vocabulary appear.

schema/StandardizedFacilityTableSpecification.md is owned by RMI/waste_data_ingestion_pipeline (facility_etl/), not by this repository. It is vendored here and pinned to an exact upstream commit recorded in schema/SCHEMA_SOURCE.json, so a rename upstream lands as a reviewable diff rather than silently invalidating a run in flight.

uv run python scripts/sync_schema.py            # pull upstream main, rewrite the vendored copy
uv run python scripts/sync_schema.py --check    # network: are we behind upstream?
uv run python scripts/sync_schema.py --verify   # offline: has the vendored copy been edited?

Do not edit the vendored spec by hand — change it upstream and re-sync. --verify and a spec-vs-code column check run offline in the test suite; --check runs weekly in CI (.github/workflows/schema-drift.yml), which needs a token that can read the private upstream repo, stored as the UPSTREAM_SCHEMA_TOKEN secret.

When either source renames or retypes a column, the code bound to it must move in the same commit: STANDARDIZED_FACILITY_COLUMNS, ATTRIBUTE_TO_STANDARD_COLUMN and the attribute sets in schema.py, the units and guidance in prompt_builder.py, the target sets in unit_converter.py, and the DB column lists in seed_source.py. The column test fails until they agree.

Connection settings live in .env (see .env.example for the keys):

WASTEMAP_DB_HOST=wastemap-database-dev.postgres.database.azure.com
WASTEMAP_DB_PORT=5432
WASTEMAP_DB_NAME=postgres
WASTEMAP_DB_USER=<your-user>
WASTEMAP_DB_PASSWORD='<your-password>'
WASTEMAP_DB_SSLMODE=require

Quote the password in .env if it contains special characters. Azure requires TLS, so keep sslmode=require.

Verify connectivity:

uv run python scripts/check_database.py

Query from code (src/waste_ai_search/db.py):

from waste_ai_search.db import fetch_all, fetch_one, execute, connect

rows = fetch_all("SELECT * FROM facility WHERE country_id = %s LIMIT 10", (1,))
count = fetch_one("SELECT count(*) AS n FROM facility")["n"]

# Multi-statement work in one transaction (commits on clean exit, rolls back on error)
with connect() as conn:
    with conn.cursor() as cur:
        cur.execute("UPDATE facility SET name = %s WHERE id = %s", ("New name", 42))

Always pass values as parameters (%s placeholders), never f-strings, to avoid SQL injection.

consolidation.consolidated_facility holds one row per facility-year (44,260 rows covering 15,537 physical facilities). The seeder collapses it to one row per physical facility, keyed on internal_facility_id:

uv run python scripts/seed_metadata_search.py                   # all facilities
uv run python scripts/seed_metadata_search.py --iso3 NGA PHL    # priority countries only
uv run python scripts/seed_metadata_search.py --require-coordinates
uv run python scripts/seed_metadata_search.py --no-backfill     # latest year verbatim

has_biocover is defined by the spec as of upstream 8c0bb3fe and sits next to has_cover / cover_types in STANDARDIZED_FACILITY_COLUMNS, matching the spec's own column order.

Regenerate it whenever the schema or the consolidation changes — the checked-in copy is the CLI default, so a search runs against whatever was last committed. The regeneration for the name-provenance fields also grew the corpus from 15,559 to 19,492 facilities, and brought Brazil from 303 sites (all with exact locations) to 4,225, of which 3,913 are flagged inexact. The COORDINATES_ONLY_ISO3 rule that had never matched anything now fires for all 3,913, scoping each to a coordinate-only search.

This is now optional. search and run read consolidation.consolidated_facility directly, so no file is needed to start a run. Use this script only to pin a corpus you want to re-run later, or to work offline; it writes to outputs/consolidated_sites.csv and is fed back with --input-csv.

consolidated_facility.facility_name has already been machine-translated, so a search on it alone looks for a string no local source ever wrote. The clearest case in the corpus: the Mexican site DELICIAS — a city in Chihuahua — reaches the pipeline as DELICACIES.

The seed recovers the source's own spelling via consolidation.value_resolution_ledger, which records which dataset supplied each facility's name and under what data_source_facility_id, joined back to that dataset's raw_data.raw_<source>_translated table:

Only these four went through translation. The other seven sources are English-language, so their stored name is the original. 14,692 of 19,492 facilities match a raw row, and they split three ways: 7,992 carry a genuinely different second name, 4,458 translated to themselves but keep a known non-English source language, and 2,242 came from a source already in English.

OSM is the awkward one: it stores a blob of tags rather than a bare name, in two spellings (JSON in name_left, a Python dict repr in fixed_name and translated), so _osm_tag_name parses both and pulls out name.

The seed gains original_site_name, source_language and name_data_source. Those are two independent facts, and the prompt branches on both:

A facility whose original matches its translation is left blank rather than repeating the string — but its source_language is still recorded, because a Portuguese source that translated to itself still needs searching in Portuguese.

Identity columns (facility_name, iso3c_plus, area_square_meters, latitude, longitude) are constant within an internal_facility_id, so deduping is lossless for identity. The year-varying measurements are not, so the fold:

1. takes the latest year as the reference row;
2. backfills only the baseline fields that are still empty, from the most recent year that has a value (disable with --no-backfill);
3. records provenance in reference_year, source_year_min, source_year_max, source_row_count, and backfilled_fields (e.g. facility_status@2013).

False and 0 are real values and are never treated as empty, so they are never overwritten by an older year.

Backfilling matters for baseline coverage — latest-year-only would drop waste_in_place_metric_tonnes from 2,499 facilities to 1,137, and annual_incoming_waste_metric_tonnes from 4,607 to 3,636.

has_biocover is read straight from consolidated_facility, which has carried the column since upstream 8c0bb3fe. It is not derived from cover_types.

To-dos:

1. Use source_id in ledger table to find original language site name so that the search can result better data — done: see Original (untranslated) facility names;
2. Update the standardized facility schema — done: realigned to upstream 8c0bb3fe and pinned via scripts/sync_schema.py, which reports drift against the ETL repo;
3. Parallel processing — done: sites are searched 4 at a time (--workers);
4. Saving search result json files to blob storage;
5. Process to promote SME validated data into a raw AI_discovery data source;
6. Process to shape AI_discovery data source into standardized facility schema, similar to other raw data sources.
7. Rerun consolidation;
8. Run through modeling pipeline with AI discovered activity data folded in;