The second source.
where my scraper design held up, and where it didn’t.

The first course worked. The second one showed me which parts of my design were real and which parts were just how FSI happened to be laid out.

How this started

Last post ended with the FSI course in Postgres. 30 lessons, 351 lines of dialog, a pipeline I could run twice without breaking anything.

It worked for one course. That proves less than it sounds like. Every decision I made was tested against exactly one set of books. Some of those decisions were real design, and some of them were just how FSI happens to be laid out. With one source there is no way to tell which is which.

A second source tells you. Whatever I built for FSI by accident breaks the moment it meets something shaped differently. So this post is about the second source, and specifically about the changes it forced. That list of changes is the honest review of the first design.

Picking the harder source on purpose

COERLL, the open language center at UT Austin, has two Portuguese courses I could use:

• Tá Falado. 46 podcast lessons with PDF transcripts and mp3s. The same shape as FSI.
• Conversa Brasileira. 35 short videos of real people talking. Two friends in a park, a couple stuck in traffic, a band in rehearsal. Every scene has a transcript with an English translation.

Tá Falado would have been faster. It would also have proved almost nothing, because a source shaped exactly like the first one cannot break the parts that were built around the first one. I went with Conversa Brasileira. It is also much closer to how people in Rio actually talk than a 1970s government course.

One difference before any code. FSI is public domain. COERLL is CC BY, Creative Commons Attribution. Free to use, but the credit has to appear wherever the text appears. Nothing in my database had anywhere to store that. More on that below.

The site said no

First step on any new site is checking whether it has rules for scrapers:

curl -s https://www.coerll.utexas.edu/robots.txt
#    -> <title>404 Not Found</title>

No robots.txt. No posted rules. That is not permission to hammer them, so the one second delay between requests and the page cache stay on.

Then I saved the course page and pulled out every link:

curl -s https://www.coerll.utexas.edu/brazilpod/cob/ -o .cache/cob/index.html
grep -o 'href="[^"]*"' .cache/cob/index.html | sort -u | head -60
#    -> (nothing)

Nothing. A real web page always has links, so the problem was what I saved, not the page. I had two guesses: a redirect that curl did not follow, or links written with single quotes that my pattern skipped. I checked instead of picking one:

ls -l .cache/cob/index.html
#    -> 199 bytes

curl -sI https://www.coerll.utexas.edu/brazilpod/cob/ | head -1
#    -> HTTP/2 403

Neither guess. 403 means the server got the request and refused it. The 199 byte file was the refusal page. In a browser the same URL loads fine, so the server was turning away anything that does not look like a browser.

The well known fix is to make curl pretend to be Chrome. I did not do that. If a site blocks tools, getting around the block means ignoring what the owner asked for, and that is the difference between a polite scraper and a rude one. Before going anywhere near that line I tried the other address the project lives at:

curl -sI https://cob.coerll.utexas.edu/brazilpod/cob/ | head -1
#    -> HTTP/2 200

200. The block was only on the old www path. The course actually lives on the cob. address. So the scraper uses that one, with a comment in the code saying why, and it still sends its own honest User-Agent with a link back to my repo.

Index, scene pages, then a PDF

With the right address, the links came out clean:

grep -o 'href=["'"'"'][^"'"'"']*' .cache/cob/index.html | sort -u
#    -> href="/brazilpod/cob/animals-1/
#    -> href="/brazilpod/cob/animals-2/
#    -> href="/brazilpod/cob/directions-1/
#    -> ...
#    -> href="/brazilpod/cob/working-out-2/

17 topics with a part 1 and a part 2, plus a shopping-3. 35 scene pages, which matched the 35 transcripts the site advertises. Each scene page had one link I cared about:

wp-content/uploads/cob_01.pdf">PDF: Notes, Transcripts, Translation</a>

So the path to the text is three steps: the index, then 35 scene pages, then one PDF on each. FSI FAST was one page with every file on it.

This is where the first design either held or did not. Discover has a small contract every course has to fill in. parse finds the files on a page. follow finds more pages worth visiting. FAST only ever needed parse. My other FSI course, Programmatic, already used follow to walk its 48 unit pages. So the shape Conversa Brasileira needed already existed:

def follow(self, html: str, page_url: str = "") -> Iterable[str]:
    """Yield the scene pages (animals-1, food-2, ...) linked from the index."""
    soup = BeautifulSoup(html, "html.parser")
    found: dict[str, str] = {}
    for anchor in soup.find_all("a", href=True):
        url = urljoin(page_url or self.page_url, anchor["href"].strip())
        parsed = urlparse(url)
        if parsed.netloc == SITE_HOST and SCENE_PAGE.match(parsed.path):
            found.setdefault(parsed.path, f"https://{SITE_HOST}{parsed.path}")
    return [found[path] for path in sorted(found)]

SCENE_PAGE only matches /brazilpod/cob/<topic>-<number>/, so the about page, the CSS files, /video and /podcast never get visited. parse then reads each scene page and returns the one transcript PDF it links. The crawl loop, the delay, the cache and the download code did not change at all.

34 files for 35 scenes

fsi-scraper discover --course conversa-brasileira --cache .cache
#    -> ...
#    -> 34 assets: 34 pdf; units 1-35

Units 1 to 35, but only 34 files. Those two numbers should agree, and they did not. When two numbers that should match do not, that is the moment to stop.

Discover had cached every page, so I could check all 36 without touching their server again. Two different scene pages linked the same PDF:

And cob_31.pdf was not linked from anywhere. Discover keeps one entry per URL, the same rule as the UNIQUE on url in the database, so the duplicate quietly folded into one. 35 pages, 34 files.

It looked like the Soccer 2 page was supposed to link cob_31.pdf. Looking like it is not proof. So I checked that the file exists and read its cover:

curl -sI https://cob.coerll.utexas.edu/brazilpod/cob/wp-content/uploads/cob_31.pdf | head -1
#    -> HTTP/2 200

python -c "from pypdf import PdfReader; print(PdfReader('.cache/cob/cob_31.pdf').pages[0].extract_text()[:60])"
#    -> Soccer 2: Ninguém tira o título da gente

Proven. Then the fix, and the proof written next to it, so nobody six months from now has to wonder why that line exists:

# One known error on the site: the Soccer 2 page links cob_32.pdf, which is
# Jam Session 1's file. The real Soccer 2 PDF, cob_31.pdf, exists but is linked
# from nowhere. Checked 2026-10-01: cob_31.pdf returns 200 and its cover reads
# "Soccer 2: Ninguém tira o título da gente". LINK_FIXES corrects that one link.
LINK_FIXES = {"soccer-2": "cob_31.pdf"}

After that, 35 assets, and fetch pulled down all 35 PDFs, 41.6 MB.

Inside the PDF

FSI was scanned books with an OCR layer full of guessed letters. These are 2013 PDFs made on a computer, so the text is real. Accents clean, no 81anks. The hard parts were somewhere else entirely. Here is a piece of the dog lovers scene:

MICHELLE:  E cadê # o Júnior? $
Where is Junior?
DENISE:  Pois é, eu tenho duas meninas em casa, aí elas que escolheram o nome,
né? %
Well, I have two girls at home, so they chose her name...

• Speakers are real people, in capitals with a colon. Denise, Michelle, Valdo, Simone. That part is easier than FSI’s A. and B..
• Every Portuguese line is followed by its English translation. FSI had no translations at all, and my dialog_line table had nowhere to put one.
• Marker symbols sit in the Portuguese. #, $, %. Each points to a note at the back of the PDF about that phrase.
• The back pages are the notes. Numbered, 1. Ai, que amor!, explaining grammar and culture. The dialog ends where the notes begin.

Which line is the translation

This was the actual problem with this source. Look at Denise’s turn above. Her Portuguese runs onto a second line, né? %, and then the English starts. So “the line after the speaker is the translation” is wrong. Nothing in the text says where the Portuguese stops.

First, look for a fact

My first thought was the printed page. In books like this the translation is often in italics or a lighter color. If the PDF stored that, I would not have to guess at all. I would be reading the label the author put there. pypdf can report the font of every piece of text:

/UWYLGY+GillSansMT | DENISE:
/UWYLGY+GillSansMT | É...
/UWYLGY+GillSansMT | Yeah...
/UWYLGY+GillSansMT | MICHELLE:
/UWYLGY+GillSansMT | E cadê
/LHPKTE+ZapfDingbatsITC | #
/UWYLGY+GillSansMT | o Júnior?

Same font for both languages. I checked the color next: it tracks the speaker, not the language. Michelle’s Portuguese and every English line are grey, Denise’s Portuguese is black. Then the position on the page: every Portuguese and English line starts at the same left edge with the same spacing.

So the author never marked the language anywhere in the file. That is a real finding, and it is worth checking for before writing a single guess. When the data carries the answer, use it. When it does not, then you guess, carefully.

The font check did turn up one fact I could use. The marker symbols are drawn in ZapfDingbats, a symbol font. So the parser drops anything in a symbol font. That is reading the file, not guessing which % is a marker and which is a percent sign.

Then, clues

With no fact to read, each line gets judged on what it contains. A line collects Portuguese clues and English clues:

PT_LETTERS = re.compile(r"[ãõçáéíóúâêô]", re.IGNORECASE)
EN_SPELLING = re.compile(r"th|\w+ing\b|\w'(s|t|re|ll|ve|m|d)\b", re.IGNORECASE)

def evidence(line: str) -> tuple[int, int]:
    """(Portuguese clues, English clues) found in a line."""
    words = [w.lower() for w in WORD.findall(line)]
    pt = sum(w in PT_WORDS for w in words) + len(PT_LETTERS.findall(line))
    en = sum(w in EN_WORDS for w in words) + len(EN_SPELLING.findall(line))
    return pt, en

• Portuguese clues: common words like né, você, que, tava, plus accented letters.
• English clues: common words like the, you, well, plus spelling Portuguese never uses. th, words ending in -ing, and contractions like it’s and don’t.

The words decide when they clearly can. When they cannot, the line before breaks the tie. If the Portuguese line before it was cut off mid-sentence, this line continues it. If it ended a sentence, this is where the English starts:

pt_clues, en_clues = evidence(line)
s = pt_clues - en_clues
unfinished = not pt[-1] or not TERMINAL.search(pt[-1])
if s >= 2 or (pt_clues and not en_clues) or (s >= 0 and unfinished):
    pt.append(line)
else:
    en.append(line)

Denise’s turn: …escolheram o nome, ends in a comma, and né? has a Portuguese word in it. Portuguese. Then Well, I have two girls… has well, I, have. English. Every line after that is English until the next speaker.

Every part of that one if came from a line that broke an earlier version. That is the next section.

Everything else that broke

I ran each version against all 35 PDFs and read what came out. This is the list, in the order it happened.

Markers in three fonts, not one

ZapfDingbats covered most files. Some used AppleGothic and came out as �. Two used HiraKaku and came out as ⤓ and ⤚. Same job, three fonts. The symbol font list has all three now.

Note numbers that look like real numbers

Some notes are pointed to with a plain number in the normal font, right after the phrase: Ai, que amor! 1 Que coisa mais linda!. Deleting every lone number would also delete fechava às 5 horas and continua na 24, which are real. So a number only gets removed when note N at the back of that same PDF starts or ends with the words right before it:

def strip_note_numbers(text: str, notes: dict[int, str]) -> str:
    """Remove "1" in "Ai, que amor! 1 Que..." when note 1 is "Ai, que amor!"."""
    for n, phrase in notes.items():
        words = WORD.findall(phrase.split("/")[0])
        if not words:
            continue
        # the number sits after the end of the phrase, or after its first word
        for anchor in (words[-2:], words[:1]):
            pattern = r"\W+".join(re.escape(w) for w in anchor)
            text = re.sub(rf"(\b{pattern}\W{{0,4}})\s*\b{n}\b\s*", r"\1 ", text)
    return re.sub(r"\s+", " ", text).strip()

A few stray note numbers still get through, where the note is worded differently from the dialog. I left those. A stray number is a much smaller problem than deleting a real one.

A misspelled name for a whole file

Lesson 4 spells Denise as DESINE, 53 times. Same person, same voice, every other file spells it right. One line fixes it:

SPEAKER_FIXES = {"DESINE": "DENISE"}

A bug I wrote myself

Every page repeats the scene title at the top. My first version dropped any line containing the title. Lesson 18 is titled They are getting along, and one of its translations is They are getting along really well, right?. My filter deleted a real line of dialog because it matched the title.

Now the header and footer are dropped by where they sit on the page, the top and bottom bands, not by what they say. Lines of dialog never sit there.

Words that are both languages

as and no are common in Portuguese and English. Counting them as Portuguese turned … as big as Rio… into Portuguese with no translation. They are out of both lists now.

A translation that keeps a Portuguese word

Or pão de queijo … is English. The translation just keeps the name of the food. pão and de count as Portuguese, so I added or to the English words. Now the clues tie, and the finished sentence before it breaks the tie toward English.

Two Portuguese sentences in a row

In lesson 8, Alexandre finishes a sentence with não? and keeps talking: Em primeiro lugar… Então, se ela tem uma vida estável. The finished sentence rule said that line had to be English. That is why clearly Portuguese words now beat the finished sentence rule.

Names printed twice

Sílvia! then Sílvia!. A name needs no translation, so the PDF just prints it again. Same with Guaraná!. A turn of two lines with the same words now treats the second copy as the translation.

Finding bad lines without reading 1,790 of them

1,790 lines is too many to read every time something changes. So I made the data point at its own problems. A Portuguese line and its English are usually close in length, so I flagged any pair where one side was more than three times the other:

fsi-scraper parse --course conversa-brasileira --format json | python -c "
import json, sys
for lesson in json.load(sys.stdin):
    for l in lesson['lines']:
        pt, en = l['text'], l['translation'] or ''
        if en and (len(pt) > 3 * len(en) or len(en) > 3 * len(pt)) and max(len(pt), len(en)) > 40:
            print(lesson['number'], l['speaker'], '|', pt[:70], '||', en[:70])
"

Three lines out of 1,790. All three in lessons 34 and 35:

34 Orlando | It’d be nice to have different words, one for ‘ safado ’ and one for… || ‘ desgraçado …’
35 Orlando | Ok. Let’s get back on track here. So the next, the number 12, Simone || says, ‘ Ah, eu também acho .’ ...

Those two scenes are “Behind the Scenes.” The people who made the course, talking about the course, in English, quoting Portuguese as they go. There is no Portuguese then English pattern to find, so the parser was cutting English sentences in half and calling the second half a translation.

That is not a word list problem. No amount of tuning fixes it. It is a different kind of content. So turns in those two lessons are kept whole, with no translation:

COMMENTARY = re.compile(r"^Behind the Scenes", re.IGNORECASE)

IGNORECASE matters. Lesson 34 says Behind the Scenes and lesson 35 says Behind The Scenes. Without it the rule catches one of them.

The load that failed, and the bug it was hiding

With the parser in shape, I loaded both courses into a fresh database:

fsi-scraper load --course brazilian-portuguese-fast --cache .cache
#    -> brazilian-portuguese-fast: loaded 36 resources, 30 lessons, 351 dialog lines
fsi-scraper load --course conversa-brasileira --cache .cache
#    -> psycopg.DataError: PostgreSQL text fields cannot contain NUL (0x00) bytes

Lesson 30 had an invisible character, code 0, where a symbol used to be. Postgres will not store that in text at all.

The useful part was what the database looked like after the crash:

select course, count(*) from lesson group by course;
#    -> brazilian-portuguese-fast | 30

FSI fully loaded. Conversa Brasileira, zero. Not 29 lessons and half of the 30th. The whole load runs in one transaction, so when it failed partway, Postgres rolled back every row of it. I designed it that way in the last post. This was the first time it actually had to work.

The fix was small. No line of dialog ever needs a control character, so the parser drops them:

# Lesson 30 has a NUL byte where a symbol was. Postgres refuses NUL in
# text, and no control character belongs in dialog, so drop them all.
text = CONTROL.sub("", text)

Then I reran every check, not just the one that failed, and lesson 30 was wrong in a new way:

Alexandre | Pô, pessoal! Eu fiz o único gol da partida, carrego o time nas costas...
          = Tava ali no meio do campo tentando fazer alguma jogada... Hey, guys! I scored...

Portuguese inside the translation. That NUL byte had been sitting at the end of a line and gluing the next Portuguese line onto it. With it gone, Tava ali no meio do campo tentando stood on its own line, after a sentence that ended in …. It has only one Portuguese word I was counting, so the tie went to English.

That bug was there the whole time. A different bug was covering it up. The fix was the rule in the if above: a line with some Portuguese clues and no English clues at all stays Portuguese, even after a finished sentence. That is also when the English spelling clues went in, so English lines can never slip through on zero.

What held

Most of the first design took the second source without changes:

• Discover’s parse and follow contract. The new course is one new file. The crawl, the delay, the cache did not change.
• Fetch. Not one line. It downloaded 35 PDFs into data/raw/conversa-brasileira/pdf/ because it was already built around course and kind, not around FSI.
• The Resource record and the resource table. A COERLL PDF is a file with a URL like any other.
• Upserts and the one transaction load. Loading twice still changes nothing. A failed load still saves nothing.
• One DATABASE_URL. Nothing about where the database lives changed.
• The export staying the same every run. The diff still only shows real changes.

What I had to change

This is the honest part. Each of these was something I built for FSI without realizing it.

The text parser was never shared

Discover has one contract every source fills in. The text parser never did. parse_fast.py was written for FSI’s OCR books, top to bottom. Conversa Brasileira needed its own parser from scratch. The only thing the two share is what they hand back: lessons, and lines of dialog. The command line now picks a parser by course:

# course -> function that turns that course's PDFs into lessons
TEXT_PARSERS = {
    "brazilian-portuguese-fast": parse_fast.parse_books,
    "conversa-brasileira": parse_cob.parse_books,
}

I am fine with that. FSI and COERLL PDFs have nothing in common past the file extension. A shared parser would have been one function full of if this course checks. Sharing the output and keeping the parsers separate is the right split. But it means the claim “add a source by adding one file” is only true for discover. The full truth is one file for discover, one parser, and a set of tests.

Translations

FSI has none. COERLL has one for every line. Zero or one per line is a column, not a separate table:

CREATE TABLE IF NOT EXISTS dialog_line (
    id          bigint  GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    lesson_id   bigint  NOT NULL REFERENCES lesson(id) ON DELETE CASCADE,
    seq         integer NOT NULL,
    speaker     text,
    text        text    NOT NULL,
    translation text,             -- English, when the source gives one
    page        integer NOT NULL,
    UNIQUE (lesson_id, seq)
);

If I ever add a second translation for the same line, Spanish say, that is when it becomes its own table. Moving a column into a table later is a small, well known change. Building the table now would cost a join on every query for a case that does not exist yet.

License and credit

FSI is public domain, so I never needed to say where the text came from. COERLL is CC BY. The license belongs to the course, not to each lesson, and one course has many lessons. That makes it a table:

CREATE TABLE IF NOT EXISTS course (
    slug     text PRIMARY KEY,
    title    text NOT NULL,
    provider text NOT NULL,
    url      text NOT NULL,
    license  text NOT NULL,
    credit   text NOT NULL
);

lesson.course now has to point at a row in that table. The credit line itself lives in the code, next to the scraper for that source, so the facts about a course are in one place:

class CoerllCobParser(CourseParser):
    source = "coerll"
    provider = "coerll"
    course = "conversa-brasileira"
    title = "Conversa Brasileira"
    license = "CC BY"
    credit = ("Conversa Brasileira, COERLL, The University of Texas at Austin "
              "(CC BY)")

Load writes the course row first, because lessons point at it. Export writes every course with its credit. And the demo page, which shows one random lesson from the sample, shows the credit for that lesson’s own course. If a COERLL scene comes up, the COERLL credit comes with it. That is the license being handled by the code instead of by me remembering.

The schema file could not change the tables

CREATE TABLE IF NOT EXISTS creates a table that is missing. It never changes a table that is already there. So rerunning the schema file did not add the new column or the new rule to my existing tables.

Locally that was easy, and it is exactly why my local Postgres has no volume. Throw the database away and build it from the file:

docker compose down && docker compose up -d && sleep 3
docker compose exec -T db psql -U postgres -d corpus < db/schema.sql

On the cluster the data has to survive, so that does not work there. The cluster will need migrations: small numbered scripts that each change the database one step, applied in order, and recorded so none runs twice. That is block 8’s problem, and now I know it is coming.

Where it landed

pytest -q
#    -> 23 passed in 0.03s

fsi-scraper load --course brazilian-portuguese-fast --cache .cache
#    -> brazilian-portuguese-fast: loaded 36 resources, 30 lessons, 351 dialog lines
fsi-scraper load --course conversa-brasileira --cache .cache
#    -> conversa-brasileira: loaded 35 resources, 35 lessons, 1790 dialog lines

select course, count(*) from lesson group by course;
#    -> conversa-brasileira       | 35
#    -> brazilian-portuguese-fast | 30

fsi-scraper export --out corpus-sample.json
#    -> 6 sample lessons from 2 courses (of 65 lessons, 2141 lines, 71 files)

30 plus 35 is 65. 351 plus 1,790 is 2,141. The database agrees with the arithmetic, which is how I know nothing got lost or doubled on the way in.

The 12 new tests are all real lines from these PDFs: Denise’s wrapped né?, Or pão de queijo, Alexandre’s second sentence, às 5 horas, the Tava ali line, Guaraná! printed twice, Orlando’s commentary, and the misspelled DESINE. Each one broke a version of the parser once.

What is not done

• Tá Falado, the third source. It is shaped like FSI, so it should mostly need its own parser and tests.
• The notes pages. Every scene has pages of grammar and culture notes. I skip them for now. They are probably the best study material in the whole PDF.
• A few stray note numbers where the note is worded differently from the dialog.
• Tests for load and export against a real database. Right now they are proven by running them twice and counting, which is real evidence but not an automated test.
• The cluster. A CronJob, a volume for the files, CloudNativePG, and migrations instead of a rebuild.

Final checklist: adding a source

# 1. Rules and the right address
curl -s https://<site>/robots.txt
curl -sI https://<site>/<course>/ | head -1
#    -> 200, not 403. Try the project's other address before anything else.

# 2. Map the site from one saved page
curl -s https://<site>/<course>/ -o .cache/index.html
grep -o 'href=["'"'"'][^"'"'"']*' .cache/index.html | sort -u

# 3. Discover. The count of files should match the count of units.
fsi-scraper discover --course <course> --cache .cache

# 4. Before writing a parser, look for facts in the file: fonts, colors, position
# 5. Parse, then let the data point at problems (length check)
fsi-scraper parse --course <course> > /dev/null

# 6. Tests from real lines that broke something
pytest -q

# 7. Load into a fresh database, then count by course
fsi-scraper load --course <course> --cache .cache

Next is block 8. Everything here moves onto the cluster, and the schema file stops being enough.