---
name: third-party-site-scraper-verification
description: Verify and review a code client that drives a third-party website — an institutional portal scraper, an API-less integration, a login-and-read automation — by reproducing its contract against the LIVE page instead of reading the code and guessing. Covers validating every field name and selector against the real HTML, the ASP.NET WebForms hidden-field dance, the self-redirect that a second GET resolves, diagnosing an incomplete TLS certificate chain (and why the client must not simply enable verification), and the error-branch selector that does not exist. Use when handed a scraper/integration file (Kotlin, Java, Python, Node) and asked to review, check, test, or fix it; when a scraper "stopped working" or "returns nothing"; or before trusting any scraper's output.
---

# Verifying a third-party site client

You will be handed a scraper — a `.kt`, `.py`, `.ts` — for a site you don't control (a university
portal, a government service, a bank, a supplier dashboard), usually with no instruction beyond the
file itself. The valuable answer is not a code read-through. It is **"I ran its contract against the
live target and here is what holds and what doesn't."**

A code review can tell you a selector is *plausible*. Only the live page tells you it is *correct*.

## The rule

> Reproduce every field name, endpoint, and control id the client depends on against the live
> HTML — **with no credentials** — before judging any of the client's logic.

Public pages leak the entire contract. A login form hands you the exact field names, the
hidden-token names, the form action, the submit button's value, and whether a captcha stands in the
way. That is enough to validate or refute most of a scraper without ever logging in — and it means
you never have to ask the user for a student id, a national id, or a password to do your job.

```bash
# the whole validation, two commands
curl -s -o /tmp/page.html "https://<host>/<login-path>"
grep -oE 'name="(__VIEWSTATE|__VIEWSTATEGENERATOR|__EVENTVALIDATION|txtUsername|txtPassword|btnEnter)"' /tmp/page.html | sort -u
grep -ciE 'captcha|recaptcha' /tmp/page.html     # 0 ⇒ automation is viable at all; report it early
```

Then check each name the client sends against that list, and read the **submit button's value** —
ASP.NET distinguishes a postback by it, and the client must send it byte-identical (including the
Arabic label, if the button is labelled in Arabic).

Run `scripts/probe_site_contract.sh <url> [client-file]` to get steps 1–7 of this skill in one pass:
layer-by-layer reachability (so a `000` is never mistaken for "down"), the double-GET status pair,
every hidden input, the form action, field names, submit-button values, captcha presence — and, when
you pass the client file, the list of names it references that **are not on the live page**, which is
usually the whole finding in one line.

`scripts/webforms_login_probe.py <login-url> [--user U --password P] [--client src/portal.ts]`
is the second half of the same pass, aimed at the **login flow**: it prints how many GETs the page
really needs and what each returns, every hidden input on the live page, the **real** submit-button
name and value, the captcha fields, and — with `--client` — the control names the client references
that do not exist live. With credentials it also prints which success signal this portal actually
uses, plus the portal's own error text. Run it before reading any login code.

`references/alazher-portal.md` is a worked example of everything below — a fully reconstructed
contract for one live WebForms portal (endpoints, hidden-token names, the two success-signal
readings that contradicted each other, dropdown label≠value mappings, and a verified result table to
regress against). Read it before re-deriving that portal, and as a template for what a *finished*
verification looks like.

`references/account-lockout-recovery.md` is the companion playbook for the case where **your own
failed logins locked real user accounts** — how to classify the cohort from the portal's own text,
what to escalate to the owner, and the exact safety checks (one attempt per record, pre-skip any
record whose stored credential is still the bad value, pause sibling portal workers, long gaps) for
the re-verification run after the institution re-activates them.

## Legacy ASP.NET WebForms: the mechanics that decide success

Most institutional portals are WebForms. Four consequences, all of which a client must respect:

1. **Hidden tokens are per-render and must be echoed back.** `__VIEWSTATE`,
   `__VIEWSTATEGENERATOR` and `__EVENTVALIDATION` are re-read from *each* fresh page and posted
   verbatim. The client should harvest `input[type=hidden]` generically rather than naming a fixed
   set — a missing one produces a generic 500, not a useful error.
2. **Control names are name-mangled.** Your clean `ACadYearDropDownList` is posted as
   `ctl00$cntphmaster$ACadYearDropDownList`. Grep the live page for the real one; don't trust the
   `id`.
3. **Success is a *content* fact, not a status code.** The tempting rule — gate the client on
   `302` or the arrival of `.ASPXAUTH` — is **wrong on real portals**, and wrong in both
   directions. Measured on one live portal in a single session:
   - the login POST returned **`302` with no `.ASPXAUTH` cookie at all** (`auth=False ok=True`), so
     a check of `302 AND cookie` would have rejected a *good* login;
   - the implementer of the Python port separately observed **no 302 and no cookie** (the portal
     used `Server.Transfer` straight to `Home.aspx`, a **200**), so a check of `302` alone would also
     have rejected a good login.

   The robust check is **whether the request is still the login form**: after a successful post, the
   credential fields are gone (`txtUsername` absent, or success is keyed on landing inside the app).
   Prefer that over any status/instrument signal. Ask the implementer to state which signal they
   gated on, and treat "200 is an error page" and "302 is success" as hypotheses to test, not rules.
   When two clients disagree about the success signal for the same portal, that disagreement *is* the
   finding — it usually means one of them is silently reporting every good login as a failure.
   **And take the marker SET from the reference implementation, not from one field you guessed.**
   Measured: a client gated on `Home.aspx` in `Location` **or** `ctl00_lblUser` in the body, while the
   portal's home page for most records carried `student_sem_work` and *no* `ctl00_lblUser`. Every good
   login came back as a bare `200 with no success marker` → classified `portal_down` → **78 queued
   pulls failed as an "outage" while the proven Python scraper logged into the same records seconds
   later**, because that implementation accepted all three markers. A good login reported as
   `portal_down` is worse than a crash: the queue keeps retrying it and the owner reads it as the
   portal being down. Diff the two implementations' marker lists before touching anything else — and
   when you inherit a queue full of `portal_down`, re-run **one** record by hand before believing the
   portal is broken.
4. **A wrong-credentials page is still a login page.** Which is why the error branch needs its own
   selector check — see below.

## Count the requests the code actually makes — the comment lies

The prose is not the contract; the call count is. A TypeScript port of a **working** Python scraper
carried this comment and this body:

```ts
// Login is a two-request ASP.NET WebForms dance: the first GET returns a 302,
// the second GET with the same cookie returns 200 and a usable __VIEWSTATE.
async login(...) {
  const r1 = await this.rawGet(LOGIN_PATH);   // ← the only GET
  this.extractHidden(await r1.text());        // 302 body is EMPTY → no viewstate
  ...POST...                                  // no __VIEWSTATE → server re-renders the form
}
```

The comment describes two GETs; the code performs one. `redirect: "manual"` (correct, needed to read
`Location`) means that first GET's body is the empty 302 stub, so **no hidden token is ever
harvested**. The portal then ignores the postback and re-renders the login form as a `200` with no
error element — and the client's own fallback maps that to `bad_credentials`.

Two lessons, both general:

- **The symptom of a broken dance is a credential failure for *every* record.** Not "some logins
  fail" — 100% of the cohort, including records whose credentials you can prove by hand. When a
  credential failure is uniform, the instrument is broken; verify one record against the live site
  with your own two-command client before touching the data. (Same rule as the all-empty-scenario
  discriminator below — uniformity is the signature of a bad request.)
- **Assert the token is non-empty and fail loudly, in the instrument's own terms.** After harvesting,
  `if (!this.viewState) throw new PortalError("portal_down", "login form had no __VIEWSTATE")`.
  Anything else reports a plumbing bug as a data bug, and the owner goes and "fixes" correct records.

### The submit button is often NOT name-mangled — and a guessed name is silently ignored

The rule above ("control names are name-mangled") is true for dropdowns and holds for the *fields* —
but the submit button on a live WebForms login was literally `name="btnEnter"`, while the client
posted `ctl00$ContentPlaceHolder1$btnEnter`. ASP.NET does not error on an unknown control name; it
just does not register a postback. So the diagnosis is not "wrong credentials" but "the click never
happened".

```bash
grep -oE 'name="[^"]*btn[A-Za-z]*"' page.html   # the REAL button name, from the live page
```

Harvest **every** `input[type=hidden]` generically (`__EVENTTARGET`, `popAccountSelectWS`,
`ASPxPopupControl1WS`, … all of it — as the Python client did) rather than a hardcoded three-token
list, and take the button name from the page too. A browser `User-Agent` also belongs on these
requests; the working client sent one and the port did not.

## The self-redirect: a first GET that 302s to itself

A real portal returned `302 Found` → `Location: /Default.aspx` — i.e. **to the path that was just
requested** — for every request. This is not a bug and not a loop to "fix"; it is the portal
planting a session:

```bash
rm -f /tmp/jar; echo "=== req 1 (fresh) ==="
curl -sk -c /tmp/jar -o /dev/null -w 'http=%{http_code}\n' "https://<host>/Default.aspx"        # 302
echo "=== req 2 (same jar) ==="
curl -sk -b /tmp/jar -c /tmp/jar -o /tmp/p2.html -w 'http=%{http_code} size=%{size_download}\n' \
     "https://<host>/Default.aspx"                                                             # 200 + real HTML
```

Request 1 sets `ASP.NET_SessionId`; request 2 is served the actual page. So when you see a client
built with `followRedirects(false)` deliberately fetching **twice** — with a comment about matching
some other implementation — that is the author having hit this and solved it. **Do not "simplify"
it.** Say so explicitly in the report, because the natural instinct on review is to call the double
fetch redundant.

Two curl traps while doing this:

- `-L` on a self-redirect exits **47 (too many redirects)** and hands you a 130-byte stub. Use `-I`
  or drop `-L` and read the `Location` header instead.
- `-k` (skip certificate verification) is *required* here — see the next section — and it is a
  deliberate diagnostic step, not a suggestion for the client's code.

**Node/undici: `redirect: "follow"` cannot resolve a self-redirect — it loops and reports a transport
error.** This is the trap inside the trap. `curl -L` exits 47 on the self-redirect (above), and
Node's `fetch` fails the same way but *says something else*:

```
manual → 302 /Default.aspx
follow → فشل: fetch failed | cause: redirect count exceeded
```

The reason is that `fetch` has **no cookie jar**: hop 2 to the same URL arrives without the
`ASP.NET_SessionId` that hop 1 just planted, so the server 302s again, and undici gives up after ~20
hops. Two consequences:

- **Never "fix" a manual-redirect client by switching it to `redirect: "follow"`.** A client that
  carries its own cookie jar *must* follow the hops itself, one request at a time, re-absorbing
  `Set-Cookie` on each hop — `for (let i = 0; i < 4 && isRedirect(res); i++) { … fetch(location,
  {redirect: "manual"}) }`. That, plus a two-GET login, is the whole contract.
- **`TypeError: fetch failed` is not evidence the site is down.** Print `err.cause` — `redirect count
  exceeded`, `ENOTFOUND`, `CERT_*` and a genuine timeout all collapse into the same `fetch failed`
  string and need three different responses. A one-line probe (`fetch(url, {redirect:'follow'})` vs
  `{redirect:'manual'}`, printing `r.status`, `r.headers.get('location')` and `e.cause`) separates
  them in seconds and is worth keeping as a throwaway script.

## Diagnose an incomplete TLS chain before calling the site broken

A `000` from curl is not "the site is down". Separate the layers:

```bash
getent hosts <host>                       # DNS
curl -sv --max-time 10 https://<host>/ 2>&1 | grep -iE 'tls|ssl|verify|certificate|alert'
openssl s_client -servername <host> -connect <host>:443 </dev/null 2>&1 | grep -E 'subject=|issuer=|Verify'
```

A real finding, and the reason an integration had seemingly paranoid security code:

```
TLSv1.2 (OUT), TLS alert, unknown CA (560):
SSL certificate problem: unable to get local issuer certificate
Verify return code: 21 (unable to verify the first certificate)
```

The server was **not sending its intermediate certificate**. The client's
`trustAllCerts` + `hostnameVerifier { _, _ -> true }` was not laziness — it was the only way to
connect at all. Two things to say, in this order:

- **The workaround is justified.** A reviewer who "fixes" it by turning verification back on has
  broken the integration. Diagnose before judging.
- **It is also too broad.** Accept-all-trust plus accept-all-hostname disables verification for
  *every* request the client ever makes, including ones that don't need it. The precise fix is to
  **pin the missing intermediate** (ship it, or install it in the platform trust store) and keep
  verification and hostname checking on. Recommend that, don't just demand it.

## Check the error branch too — a selector that doesn't exist

The happy path is what gets tested; the failure path is what gets shipped broken. A real client
parsed `#lblError` to surface the server's message on bad credentials. That element **was not in the
login page** — so the branch could never fire, and every failure collapsed into the client's own
generic fallback string. The user would never see the real reason.

> For every selector in the client, ask *which page is this evaluated against?* Grep the live HTML
> for it. `grep -c 'lblError' page.html` returning 0 is the whole finding.

Same pass, cheap: list the anti-automation fields you noticed and say plainly which ones **do not
matter** for a POST-based client — `onpaste="return false"` and a `maxlength` are irrelevant to a
programmatic form post. A report that lists them as risks is noise; a report that says "irrelevant
here, and why" is signal.

## Dropdowns: enumerate before you guess, and expect labels ≠ values

When the client POSTs a `<select>`, never assume the option *label* is the value. Print both, live:

```python
for o in sel.find_all("option"):
    print(repr(o.get_text(strip=True)), "=>", repr(o.get("value")))
```

Real findings from one portal, all of which a reasonable guess would have gotten wrong:

- The year list held **academic years** (`"2025/2026" => "16"`), not levels. "First year" had to be
  mapped to a year value — the level is not a field at all.
- Semester labels were `أول` / `ثانى` / `صيفى` / `معادلة` — note `ثانى` (no alef maqsura, no `ال`),
  while a guess would be `الثاني`. Arabic option labels are frequently un-vocalised and
  inconsistently spelled; match by substring, then report the exact list you saw.
- The round list was `دور أول` / `دور ثاني` — and the two entries spell "second" *differently*
  (`ثاني` vs `ثانى`) within the same list.

So the first deliverable of a port is always **the enumerated option lists, verbatim**, before any
scrape logic. It converts "the client returns nothing for level 1 semester 1" into "the label you
searched for isn't the label the portal uses."

## Credential hygiene, and the check that proves it

A client built to log in carries a real password. Four things, every time:

- Credentials go in a **`.env`** (or CLI flags / env vars), **never** in the source. Put `.env`,
  `output/` and `__pycache__/` in `.gitignore` **before** the first commit.
- The script must run end-to-end from env with no hand-editing:
  `python3 scraper.py --student … --national-id …`.
- **Prove the secret is absent rather than asserting it:**
  `grep -r "<the-national-id>" . --exclude-dir=.git` → must return nothing, including from logs and
  saved HTML. If the raw response bodies are stored, check those too.
- Do not echo the credential in diagnostics. Print the login *status*, the student **name**, and the
  cookie presence — never the password, and prefer not the full id.

## Designing for the bulk run (one student → the whole cohort)

The single-record run is a smoke test; the real job is hundreds of records. Build these in from the
start, because retrofitting them is what turns a 10-minute job into a lost night:

- **Find the credential column before you ask the owner for a file.** The product you are updating
  usually had to authenticate these same people, so its own database often already stores each
  record's portal login. One batch portal's `registrations` table held `student_code` +
  `identity_number` for all 1,138 members — the exact username/password pair the third-party portal
  wanted. Confirm the mapping on a **varied sample of two or three** (one local-format id, one
  foreign/`wafid`-style id) before designing the run around it, and report the coverage count
  (`1,138/1,138 have it; two log in successfully`) so the owner has nothing left to supply. See
  `multi-host-project-onboarding`.
- `--students-file students.csv` (columns: id, credential) — **a fresh session per record**; cookies
  bleed between records and produce silently wrong data (someone else's rows) if you reuse one.
- `--resume` — skip records that already have an output file. Long scrapes get interrupted; without
  this you re-fetch everything or hand-prune directories.
- **Start serial and gentle (1–2s between requests) — then measure the ceiling instead of assuming
  it.** These portals are small IIS boxes and hammering one gets the account or the IP blocked
  mid-run, so the conservative default is right *until you have data*. But "no parallelism" is a
  hypothesis, and on a 1,138-record job it is the difference between a night and half an hour:

  | configuration | measured | errors |
  |---|---|---|
  | 1 worker, 1.4–2.6s delay | ~7s/record → **~2h** projected | 6 / 204 (login, not load) |
  | 3 workers, 0.6s delay | **~1.4s/record → 26.5 min** for 892 | **0** portal errors |

  The probe that earns the speed-up is 8–10 records at a deliberately low delay, reading the
  **error bucket**, not the clock:

  ```bash
  # tolerant: 8 records, 0.3s delay, 1 try only — a portal that throttles will say so here
  # → 28.3s, 0 failures ⇒ there is headroom.  Any 429/5xx/timeout ⇒ stay serial.
  ```

  Then run at roughly 3–4× the rate the probe survived, and **keep a tripwire**: if the
  `system/transport` bucket starts filling, drop the worker count or pause — the whole run depends on
  not being blocked, so an aggressive rate is a bet with the entire job as the stake. Record the
  bucket counts in the final summary so the speed and the safety are both auditable.

**The inverse failure — back-to-back logins rejected as 5xx — looks exactly like an outage.** Rate
limiting does not always arrive as `429`. On one portal the login POST answered **`500` with no
success marker** for every record *except the first of a run*: the first login after a pause succeeded
and every request behind it got a bare 500. Two different operators (one on a foreign VPS, one on the
Hermes host) reproduced the identical shape — first job `ok`, everything after it `portal_down` — and
the same records logged in fine from an interactive probe minutes later. So:

- **"First record ok, rest 5xx, same credentials work by hand" is a pacing signature, not an outage.**
  Before reporting the portal as down, re-run **one** record on its own after a pause; if it succeeds,
  you are the problem and the fix is a gap between logins, not a retry policy.
- Sequence the work as **one login at a time with a real interval** (tens of seconds per record), not
  a worker pool. The existing `system/transport → retry with backoff` bucket is still the right
  classification for the 5xx; what changes is the *cause*, so log the interval you used alongside the
  bucket counts.
- When a scheduled worker owns this loop, make the gap an env knob
  (`PULL_JOB_DELAY_MS`, default ~45s in the grade-pull worker) rather than a constant in the loop —
  the right gap is portal-specific and gets tuned after the first run.

### ⚠️ Concurrency can corrupt a WebForms scrape *silently* — and the damage looks like data

The table above was written from a probe that read the **error bucket**. That is not sufficient. On a
second run of the same cohort, a 3-worker sweep returned `status: ok` for every record while the
**third scenario returned zero rows for 1,138 of 1,138 records (100%)** — and the first two scenarios
returned data for 94% of those same records. Nothing errored. Nothing was logged. The records were
persisted as "login ok, no results" and every re-sit pass in the cohort vanished from the dataset.

Re-running the *exact same request* serially, one worker, returned the real rows immediately — no
wait, no publish window. So the emptiness was **produced by concurrency**, not by the portal having
nothing to say. The mechanism is the hidden-token dance above: `__VIEWSTATE` / `__EVENTVALIDATION`
are per-render, and under parallel load the last request in a record's sequence is the one that
receives a stale or cross-contaminated pair. ASP.NET rejects the postback **without an exception**
and re-renders the form — a `200`, a perfectly valid page, an empty result grid.

So a concurrency ceiling you measured is **only a ceiling on the errors you can see**. Two rules:

- **A scenario that comes back empty for ~100% of records is a broken request, not a data fact.**
  Real per-record data is *ragged* — some records have rows, some do not. An all-records-empty column
  is the signature of a bad request, a bad token, or a stale session; it is never "nobody has
  results". Treat any value that is uniform across the whole cohort as an instrument failure until
  proven otherwise.
- **Never record an empty result on one attempt.** Retry the request (2–3 times), and persist `empty`
  only once the retries agree *and* the page passes the discriminator below — and re-probe before
  drawing any conclusion from an empty cell. Otherwise the sweep's worst bug is frozen into your
  dataset as a fact, and every downstream number built on it is wrong.

**The discriminator — prove an empty is real, don't assume it.** Compare a known-empty page against a
known-has-rows page and check all four:

| check | genuinely empty | silent postback failure |
|---|---|---|
| the result-grid element exists | **yes** | often absent — the page is still the blank form |
| `EmptyDataText` / "لا توجد بيانات" **inside** the grid | **yes** | no |
| `__VIEWSTATE` length, pre-POST → post-POST | **grows** (a re-rendered page) | **identical** to the value you sent — the postback was rejected |
| body size / md5 vs the other empties | varies per record | often near-identical across records |

Worked numbers from the real run — the two columns are unmistakable:

```
empty:     grid=yes  "لا توجد بيانات"=yes  rows=0  viewstate  5760 →  9324   size ~64.3k
has data:  grid=yes  "لا توجد بيانات"=no   rows=6  viewstate  5760 → 12140   size ~72.4k
```

The `__VIEWSTATE`-unchanged test is the decisive one: if the value you posted comes back unmodified,
the server never processed your request and the "empty" grid is a rendering of the *form*, not an
answer about the record. Persist the raw body plus these fields per empty record so the claim stays
auditable — and so a later reviewer can re-check it without re-running the scrape.
- One output file per record (`<id>__<scenario>.json` + `.csv`), never one giant file. Partial
  results stay usable, and `--resume` has something to test against.
- Print a summary table at the end and save it, so the run is auditable without re-reading N files.

## Verify the numbers with your own client, not the implementer's

When a delegated agent both **builds** the scraper and **reports** its results, the report is a
self-report. Re-derive the numbers with a throwaway client of your own — a separate code path, a
different HTTP surface, hardcoded to one record — and compare field by field.

This caught nothing wrong in one case (the implementer's counts and sums matched exactly) and that
is the point: the match is what converts "the agent says 9 subjects / 419.5" into a fact you can
state. It also catches the *inverse* failure — where the implementer's success signal is wrong and
your independent client succeeds where theirs "fails", which is how the status-code issue above
surfaced.

Two traps in writing that throwaway verifier:

- Your own selection logic is the likely bug, not theirs. Mine picked the wrong year because a
  substring match hit `"1"` inside `"2011/2012"` and silently returned an empty result set. If your
  independent run returns **zero rows** while theirs returns data, suspect your picker first.
- A second client that *logs in successfully* is itself useful evidence: printing the returned
  student name proves the credentials and the login contract are both good, independent of scraped
  rows.

## Writing the harvested rows back into a live product's database

A scrape that only emits JSON is a report. The moment the rows are meant to **replace** what a live
service already serves — a ranking site, a bot's allowlist, a dashboard — the failure modes turn
destructive and the job needs its own gates. Sequence it like this, and do not collapse steps.

1. **Back up the destination before the first write, and keep the restore path.** Snapshot the
   affected tables (a repo usually already has a `scripts/snapshot-tables.*`) plus a plain
   export. A backup you have not read back is a hope, not a backup.
2. **Dry-run that computes the diff and writes nothing.** The dry run is the deliverable the owner
   actually wants first: *these N records change, these M cross the threshold, these K are
   untouched.* It is also the only cheap moment to catch a mis-mapped field.
3. **Classify every record's outcome into four buckets — and never merge them.**

   | Bucket | Looks like | Correct handling |
   |---|---|---|
   | `ok` | rows returned, parsed | write |
   | `bad record` | the target rejected the **credential** | mark the record as needing correction; do **not** retry |
   | `empty` | login succeeded, zero rows for that scenario/round | usually legitimate (no results in that round) — record it, don't retry |
   | `system/transport` | timeout, 5xx, rate-limit, challenge page | **retry with backoff** — this says nothing about the record |

   The owner will state this requirement in exactly these terms — *"لازم تفرق بين بيانات غلط وان
   السيستم نفسه فيه مشكلة زي ال rate limiting"*. Merging the buckets produces one of two silent
   disasters: hundreds of valid students flagged as "wrong credentials" because the portal throttled
   you, or an infinite retry loop against records that will never succeed. Persist the raw response
   per record so any disagreement is auditable later.

   **`bad record` is itself several causes — read the source's own message, not your boolean.**
   Your client collapses every rejection into "login failed"; the portal's page text is the actual
   diagnosis, and the three causes need three different actions:

   | portal text (measured) | real meaning | action |
   |---|---|---|
   | wrong-password / generic rejection | the stored credential is wrong | correct the data |
   | `عفوا هذا المستخدم غير نشط برجاء العودة الى مدير النظام` | the account was **never activated** | the *owner* must get it activated — no data fix will help |
   | `لقد استنفذت عدد محاولات الدخول, تم غلق الحساب` | **locked by your own failed attempts** | stop; it may self-release, otherwise the operator unlocks it |

   Getting this wrong cost a whole correction cycle: 16 identifiers were repaired from the
   documents (real typos, worth fixing) and **not one of the 53 logins started working**, because the
   cohort's actual blocker was unactivated accounts. Report the two facts separately — *what you
   repaired* and *what is still blocked, and by what* — or the owner hears "fixed 16" and assumes the
   batch now works.

   **Cap failed authentication attempts.** N variants × the whole failing set is not a probe, it is a
   targeted lockout: ~238 attempts across 53 accounts locked real student accounts at a partner
   institution. Two or three variants on **one** record, then stop and go read the portal's message.
   If you did lock accounts, say so plainly — they are the owner's users, and it is usually
   recoverable without hiding it.
4. **Re-run the derived machinery and diff *its* output too.** If eligibility, access lists, or
   channel membership are computed from the harvested fields, the job is not finished when the
   columns land — it is finished when the derived list shows exactly the intended set changed. That
   is the difference between "the data updated" and "the system updated".
5. **Verify on the user-facing surface with a real affected record**, and let the implementer verify
   before you do. Multi-layer review is cheap here and the blast radius is production.

### Working the failure cohort before you escalate it

A dry run that reports *"N records failed"* is not a diagnosis, and the analysis you build on top of
those failures can manufacture findings of its own. Four techniques, in the order they pay off:

1. **Exclude unfetched records from every derived-value comparison.** A record whose fetch failed has
   **unchanged** data — so its derived numbers (carried hours, totals, eligibility) must compare as
   *equal*, never recomputed from an empty scrape. Treating "no rows returned" as "carried hours = 0"
   reported **44 records as having lost eligibility**; after excluding the 53 login-failures from the
   comparison the real number was **4**. The owner's reaction (*«استحالة طالب يشيل بعد ما يبقى ناجح»*)
   was the correct signal, and the *instrument* was what was wrong. Guard it inline — compute an
   `any_ok` flag over the record's scenarios and, when it is false, carry the old values through
   untouched.
2. **Slice the failures by a dimension you already have, before reaching for anything expensive.**
   Grouping by the record's own `nationality_track` immediately exposed a systematic cause —
   **38% failure for one track vs 4% for another** — which points at that track's identifier format
   rather than at 53 individual typos. One `Counter()`; no document review needed.
3. **Exhaust the cheap retries before asking the owner for evidence.** For a credential rejection,
   try the plausible format variants first — case folding, whitespace/dash stripping, zero-padding to
   the document's canonical length, and the record's *other* id-like fields (seat number, code,
   phone) — one login attempt each. Zero successes is still a useful result: it rules out "we stored
   it with the wrong case" and justifies the expensive ask.
4. **Validate a national-identifier field against its own encoded structure.** Many national IDs embed
   a birth date or a check digit; decoding it turns "53 suspicious ids" into "*exactly one* whose
   birth year is 1907, and *zero* of the ~500 working records have an implausible year" — a single
   named typo instead of a vague data-quality worry. Watch the century digit: the same two digits mean
   `1907` or `2007` depending on it, and a sloppy substring filter will flag all the valid ones.

**And the rule underneath all four:** when your analysis contradicts a stated domain invariant,
**audit your instrument before reporting the finding.** Publish the corrected number, name your own
error, and say why the artifact appeared — the owner will otherwise spend the next round arguing you
out of a phantom regression, which costs more trust than the original failure did.

### Re-checking a cohort after the operator re-activates it (the lockout-recovery loop)

Locked accounts are the *owner's users*, and this is a loop you will run more than once, not a
one-shot. Sequence it like this.

1. **Keep the pre-lock classification as a file.** Before escalating, save one row per record with the
   portal's **own text** (`never activated` / `locked` / `wrong password`) — the three-way table above.
   That file is the baseline for the re-check, and it records which failures were never the
   scraper's fault.
2. **Escalate two separate facts:** *what you repaired* and *what is still blocked, and by what*. The
   second is what the owner takes to the institution's engineer.
3. **When the owner reports «the engineer re-activated them all», treat it as a claim and re-test
   against the primary source yourself — one attempt per record, corrected credential only.**
4. **Pre-skip every record whose stored credential still equals the value that previously failed.**
   Retrying a known-bad credential is exactly what caused the lockout; such a record gets reported as
   *"blocked by our own data"*, never re-attempted. This is the single most important safety rule in
   the loop — it is what stops the recovery run from re-locking the cohort.
5. **Serialize with a real gap (tens of seconds) and pause every other portal worker while the check
   runs.** Two interleaved login streams reproduce the `500`-on-back-to-back pattern above and make
   the results unattributable.
6. **Classify per record from the portal's message text**, then ship a **before/after table**
   (previous status → result now) plus the coverage arithmetic: *N records → M testable (K pre-skipped,
   with the reason) → X ok / Y still failing, each with the portal's exact message.* Publish that as a
   file, not prose — it is what settles the claim.

**Measured on the real recovery:** 53 records (31 locked, 19 never-activated, 3 wrong-password) → after
the engineer re-activated them, 51 were testable and **2 were pre-skipped** because their stored
credential still held the wrong identifier that had caused the lock. The never-activated records then
logged in successfully — the escalation worked — while a separate cohort of already-linked accounts
tested the same way came back **44 ok / 2 failing, both on one identifier format** (foreign/`wafid`
passports). That is the `nationality_track` slicing signal above pointing at the passport format
rather than at individual typos: fix the format, not the records.

Worked record, with the exact safety checks and the script paths used:
`references/account-lockout-recovery.md`.

### The write-back is ADDITIVE, not a mirror

The instinct — *"the destination should equal what the portal says"* — is the single most dangerous
assumption in this whole class of work. A live destination usually holds **more** than the scraper
can see, and treating the scrape as authoritative silently deletes real data.

Measured case: 22 modules existed in the destination DB but were absent from the portal, and
**`IMP07-10212_01` («جهاز مكونات الدم») was missing for 16 of 16 students** — not a parsing bug, not
a bad selector. The portal simply does not expose it: probing one record across **all twelve**
semester × round combinations returned zero rows for it. Deleting "rows the portal didn't mention"
would have rewritten which modules each student is carrying, and therefore flipped carried-hours and
channel eligibility for the whole cohort.

**Ask the owner *why* the source is incomplete — it is usually by design, and knowing the rule makes
it predictable.** The batch owner's answer, unprompted: *«اللى موجود فى البوابة مش شرط يكون كل
الموديولات، وده الصح فعلا — مش بيكون موجود غير اللى حصل فيهم تغيير، يعنى ده لو على الدور التانى أو
حتى فى الدور الأول لو الطالب باقى للإعادة»*. So the portal is a **change feed, not a snapshot**: it
returns the modules whose grade moved this round (a second-round re-sit result, or a first-round
module the student is re-sitting), and everything else is simply not mentioned because it did not
change. That reframes the whole job — you are merging a delta into a record, never mirroring a view.
Say that rule in the report so the next reader does not re-open the "missing rows" investigation.

So classify every field/row by **direction**, and only two directions are writes:

| Direction | Meaning | Action |
|---|---|---|
| source-only | the scrape has it, the destination doesn't | **insert** |
| both | present on both sides, values differ | **update** |
| **destination-only** | the destination has it, the scrape never saw it | **keep — never delete.** Report the count and the specific keys |

- Say it out loud in the report: *"`N` rows exist in the destination that the portal does not
  expose — these are retained, not deleted."* The owner needs to know the sync is not a mirror, and
  a reviewer who assumes it is will read your diff as data loss.
- Before accepting "destination-only" as normal, **probe the source exhaustively for one record**
  (every scenario / round / term / filter combination) to prove the field is genuinely absent and
  not merely uncovered by your scenario list. That probe is what separates a safe retention from a
  missed selector.
- Corollary for safety: never let a scrape drive a `DELETE`, a truncate, or a replace-the-set
  operation against a live table. Only inserts and updates.

**You can build and review the whole plan — backup, dry-run harness, classifier — before any
credential exists.** When the secret is genuinely unavailable, ship the plan and the dry-run
instrument rather than idling; the only thing blocked is the write.

### Executing the write: parent rows first, and post-state aggregates from the FULL state

> **Prerequisite before any write:** Run a dry-run first, present the backup and predicted diff to Sohail, and get his explicit approval before writing to production data.

Three things that only surface the moment you stop dry-running.

**1. Foreign keys force an order.** A child table often references the parent on the *natural* key
you are also writing. Here `module_grades (student_code, term)` → `grades_db (student_code, term)`,
`ON DELETE CASCADE` — so inserting children for a record with no parent row fails outright:

```
psycopg2.errors.ForeignKeyViolation: insert or update on table "module_grades" violates
foreign key constraint "module_grades_student_code_term_fkey"
DETAIL: Key (student_code, term)=(120240000569, term1) is not present in table "grades_db".
```

Sequence it as **parent first, children second**: upsert one parent row per (record × term) with
`ON CONFLICT (<natural key>) DO UPDATE`, computing the parent's aggregate columns (totals,
percentage) from the child set you are about to write — then upsert the children. Do not assume the
parent exists; of the 2,153 parent rows this write needed, many had to be created.

**2. Never compute a post-state aggregate from the source's delta.** The source returns only what
*changed* (see the ADDITIVE section above). If you compute the record's *new* derived total from
those rows alone, you undercount and publish a phantom improvement. Measured on the real run:

| "gained eligibility" computed from | result |
|---|---|
| the source delta only | **28** ❌ |
| full post-state vs pre-write snapshot | **2** ✅ |

The rule: **a metric describing the destination *after* the write must be computed from the
destination, joined over all of its rows — never from the partial feed that happened to arrive.**
Treat the incoming feed as a write *instruction*, not as the new value of a derived field. The
pre-write export is what makes the correct "after" computable and the "before" recoverable at all.

If you already published the inflated number, retract it in the same message as the correction and
name the cause in one line (*"حسبت الساعات من الموديولات المسحوبة بس — والبوابة بترجّع المتغيّر بس"*).
The owner's stated invariant (*"كله بيزيد او يفضل زى ما هوا"*) is a good tripwire: when your numbers
violate it, audit the instrument before you report the finding.

**3. Then verify the write with numbers that could not be right by accident.** The dry run's
predicted delta is a falsifiable claim — check it, and check the negative too:

- predicted **+622** rows inserted → post-write count moved `19,538 → 20,160` = **+622 exactly**;
- `result_pulled_at` set for **1,080** records, while the deliberately-skipped failure cohort had it
  **still null** (the untouched-cohort check — the cheapest proof you did not clobber what you
  excluded);
- the authoritative column you chose *not* to touch confirmed unchanged (`cumulative` still had 1,253
  distinct ranks over 1,253 rows).

Three checks, all predicted before the write. That is the standard: a write is "done" when the
outcomes it committed to in the dry run are the ones that landed.

### Derived columns have provenance — find out who computed them before you recompute

A destination table usually carries values that were **computed from** the rows you are about to
change: a rank, a total, a percentage, an eligibility flag. Recomputing them looks like obvious
hygiene and is a trap, because the column may not have been computed by your app at all.

Measured case: an app recomputed rank with **standard competition ranking** (1, 2, 2, 4 — ties share
a rank), and its own import script said so in a comment. Two of the three terms matched that rule
perfectly. The third — `cumulative` — did not:

| term | rows | **distinct rank values** | range |
|---|---|---|---|
| `term1` | 1197 | 572 | 1…1197 |
| `term2` | 1202 | 527 | 1…1201 |
| **`cumulative`** | **1253** | **1253** | 1…1253 |

1253 rows with 1253 distinct ranks means *every* record has its own position — that column was
**imported from the official source**, which breaks ties by its own unseen rule, not computed by the
app. Recomputing it would have given the 52 students stuck at 0% the *same* rank instead of their own,
and re-numbered **486 records**. The fix was to recompute the two derived terms and **leave the
authoritative one alone** — which the owner had to agree to, because "recompute the ranks" was his
own instruction.

So, per derived column:

- **Compare it against a fresh computation of your own rule** (in a throwaway script, not in the DB).
  All-match ⇒ it is yours, safe to recompute. Any mismatch ⇒ stop and find its origin before touching
  it.
- A tell-tale signature: **distinct-count == row-count** in a column whose rule says ties should
  merge. That is an authoritative source leaking into your schema.
- Report the blast radius before the write: *"recomputing this would re-number 486 records that
  nobody asked to change."*

**Recompute inside the database via one RPC, not row-by-row from the client.** A ranking pass over a
few thousand rows is trivial for Postgres and expensive as a round trip, and the free tier's egress
allowance is a real constraint the owner will raise (*«سوبا بيز فيه حدود مجانية»*). One call, zero
rows transferred:

```sql
create or replace function public.recompute_term_ranks(p_term text)
returns void language sql security definer set search_path = public as $$
  update public.grades_db g set rank = r.rk
    from (select id, rank() over (order by percentage desc) as rk
            from public.grades_db where term = p_term and percentage is not null) r
   where g.id = r.id;
$$;
-- POST /rest/v1/rpc/recompute_term_ranks  {"p_term":"term1"}   ← one request, no data back
```

`rank()` in SQL *is* standard competition ranking, so the RPC reproduces the app's own function —
but **prove that, don't assume it**.

### Proving a SQL rewrite without writing: the transaction + ROLLBACK

When you are handed an agent's "equivalence test" that compares its JS helper to its own JS reference,
it has proven nothing about the SQL that will actually run. Close the gap against real data with a
connection you already have (the DB password is often in the last place you looked — a bot container's
env), and **roll back**:

```python
con = psycopg2.connect(CONN); con.autocommit = False       # nothing persists without commit
for term in ("term1", "term2", "cumulative"):
    cur.execute("select id, percentage, rank from grades_db where term=%s and percentage is not null", (term,))
    before = cur.fetchall()
    js = js_rank([(r[0], float(r[1])) for r in before])     # your reference implementation
    cur.execute(SQL_RPC, (term,))                            # the exact statement the RPC will run
    cur.execute("select id, rank from grades_db where term=%s and percentage is not null", (term,))
    sql = dict(cur.fetchall())
    js_vs_sql = sum(1 for i in js if js[i] != sql.get(i))    # → 0 means the RPC is equivalent
    old_vs_sql = sum(1 for r in before if r[2] != sql.get(r[0]))  # >0 exposes a stale/imported column
    print(term, len(before), "js_vs_sql:", js_vs_sql, "old_vs_sql:", old_vs_sql)
    con.rollback()                                            # ← undo, every iteration
```

This is the strongest available evidence for a destructive migration, and it is **free**: it produced
`js_vs_sql_mismatch: 0` for all three terms — the equivalence the agent claimed but had not
demonstrated — *and* surfaced the stale-column finding above as a side effect, which a passing unit
test never would have. Re-`select` a row count afterwards to show the data is untouched.

### Escalating to the records' own documents

When credential rejections survive the cheap variants and the structural checks, the record's **uploaded
identity documents** are usually already in an object store the app uses. This is the escalation that
turns *"50 records I cannot log in as"* into a per-record correction list — and it is a verification
job, not a scrape:

1. **Find the bucket and the metadata table.** The app's own table (`student_documents`) holds
   `student_code`, `document_type`, and an object key of the form
   `<code>/<document_type>/<uuid>.jpg`; the bucket name is discoverable from the storage CLI's own
   `bucket list`. Filenames are generic (`document.jpg`) — do not expect the id in the name.
2. **Prove the pipeline on a control record first.** Download one document for a record whose
   credential **works**, read it, and compare. A match there is what licenses you to apply it to the
   failures; without it you cannot tell a genuine mismatch from your own extraction error.
3. **Read the image with a multimodal CLI** (`ask-gemini --tools "…"` on the downloaded path) and ask
   for the digits only. It returned `<الرقم القومي — محجوب>` for a real national-ID card, byte-identical to
   the value in the DB.
4. Report per-record as **match / mismatch / no document**, with the count of records that have no
   document at all, and feed mismatches back to the owner as a correction list rather than writing
   them yourself.

Keep the document reading **out of the write path** — it tells you which records are wrong, it does not
authorise changing them.

## Reporting shape

Split the report into what holds and what doesn't, and lead with the verification evidence:

| Verdict | Evidence required |
|---|---|
| Approach is correct | a live reproduction (the 302 → 200 pair, the matching field list) |
| Bug | the selector/field, and its absence from the live page |
| Security note | the chain diagnosis, plus the targeted fix |
| Cannot confirm without credentials | say so — never imply you tested a logged-in flow you didn't |

**Report what the client gets right, not only faults.** A review that finds only bugs is
uncalibrated, and here the author had correctly solved a non-obvious redirect behaviour that a
casual reader would flag as wrong. Naming that explicitly is what makes the rest of the report
credible.

## Pitfalls

- **Count the requests the code makes, not the ones its comment describes.** A client whose comment
  says "two-request login" while calling `GET` once is not a style nit — with `redirect: "manual"` it
  harvests tokens from an empty 302 body, so *every* login is rejected and reported as
  `bad_credentials`. A credential failure that is **uniform across the whole cohort** (including
  records you can log in as by hand) is an instrument failure; verify one record with your own client
  before touching the data.
- **Fail loudly when a token is missing.** `if (!viewState) throw new PortalError("portal_down", …)` —
  never let an absent `__VIEWSTATE` fall through to a credentials verdict, or the owner spends a round
  "fixing" correct identifiers.
- **The submit button's name may not be name-mangled.** A live login button was literally `btnEnter`
  while the client posted `ctl00$ContentPlaceHolder1$btnEnter`; ASP.NET silently ignores an unknown
  control name, so the click never registers. Grep the page for `name="[^"]*btn…"` and POST that
  name+value verbatim (Arabic label included). Harvest **all** hidden inputs generically, and send a
  browser `User-Agent`.
- **Never ask for the user's real credentials to verify shape.** The public page validates the
  contract; the only thing credentials add is the authenticated fetch, which is a separate,
  user-authorised step.
- **`000` is a diagnosis prompt, not a conclusion.** DNS → TCP → TLS, in that order, each with its
  own command, before you say anything about reachability.
- **Don't rename or "tidy" name-mangled control ids.** `ctl00$cntphmaster$…` is the wire format.
- **A missing hidden field fails as a generic server error.** If the client names a fixed set of
  tokens instead of harvesting `input[type=hidden]`, flag the fragility, not just the instance.
- **A scrape is a partial view of the destination, never the truth about it.** One portal omitted a
  module that existed for 16/16 students across every one of twelve scenario combinations. Any
  write-back that deletes what the source did not return destroys live data and quietly changes every
  derived number (carried hours, eligibility, access lists). Insert and update only; retain and
  *report* destination-only rows. See "The write-back is ADDITIVE, not a mirror" above.
- **A record you failed to fetch has NOT changed — do not let it enter a derived-value comparison.**
  Counting an empty scrape as zero turns every transport/credential failure into a phantom
  "regression", and the phantom is reported with the same confidence as a real one. Exclude unfetched
  records, and when your diff contradicts the owner's stated invariant, suspect your own comparison
  logic before you report the finding. See "Working the failure cohort before you escalate it".
- **The geo-block hypothesis is usually wrong and is easy to check.** A portal that "must be
  blocking foreign IPs" was reachable from this server the whole time; the failure was the
  certificate chain. Resolve the layer before theorising.
- **A derived column may be an import, not a computation.** One rank column had
  *distinct-values == row-count* — someone else's tie-breaking, not the app's. Recomputing it would
  have re-numbered 486 records to fix nothing. Fresh-compute it in a throwaway script and compare
  before you touch it; `count(*)` vs `count(distinct col)` is the one-line tell.
- **An agent's "equivalence test" that compares its own helper to its own reference proves nothing
  about the SQL that ships.** Re-run the real statement against real data inside a transaction and
  `ROLLBACK`. It is free, it is the strongest evidence available, and it catches the stale-column
  class of bug that a passing unit test never will.
- **Reading a record's documents tells you which records are wrong — it does not authorise fixing
  them.** Keep it on the report side of the line, and prove the reader on a known-good control record
  before pointing it at the failures.
- **Check the FK graph before writing two tables in one pass.** A composite foreign key on the natural
  key (`module_grades (student_code, term)` → `grades_db (student_code, term)`) makes the child upsert
  fail until the parent row exists. Parent-first, children-second — and derive the parent's aggregate
  columns from the child rows rather than leaving them null.
- **An "after" metric computed from the source's delta is wrong by construction.** The feed only
  carries what changed, so summing it undercounts the post-state. This produced a published "28
  records gained channel access" when the truth was **2**, and it is the same family of bug as
  counting an unfetched record as zero. Recompute from the destination joined over all rows, against
  the pre-write export — and if the source is a delta, say so in the report so the next reader does
  not re-derive the same phantom.
