QA Skills Library / third-party-site-scraper-verification

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.

# 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:
  4. 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;
  5. 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:

// 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 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".

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:

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:

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:

Diagnose an incomplete TLS chain before calling the site broken

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

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:

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.

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

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:

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:

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:

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:

⚠️ 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:

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:

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

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:

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:

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:

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:

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

raw Markdown · all skills