about mecontact
⌕search⌘K
sysadmin cybersecurity devops brasil homelab kubecraft homelab thoughts
⌕search the log…
writing
$sysadmin$cybersecurity$devops$thoughts
homelabs
▣brasil homelab⎈kubecraft homelab
site
◈about me✉contact
rss feedgithub
devops · October 2, 2026 · 18 min read

fsi-scraper.
a 1970s course, an OCR layer, and 351 lines of dialog.

A free FSI course, an OCR text layer that is mostly right, and a five stage pipeline you can run twice without breaking anything.

pythonpostgresocrfsi-scraperhomelabbrasil
// what we’re getting into
  1. How this started
  2. First, where was the code
  3. Getting the Mac ready
  4. Discover: how the first stage already worked
  5. Fetch: downloading without trusting the network
  6. The text was already there, mostly
  7. Parse: every lesson broke a different rule
  8. Why Postgres and not SQLite
  9. The schema, one decision at a time
  10. Load: run it twice, get the same database
  11. Export: the website never touches the database
  12. Where the audio goes
  13. What is not done yet
  14. Final checklist: is the pipeline actually working

How this started

fsi-scraper is the project on my resume. It takes a free 1970s Portuguese course from the US Foreign Service Institute and turns it into data the rest of the Brasil homelab can use. Flashcards, the demo page, and later the slang database.

Until this week it had one stage. It could find the files. It could not download them, read them, or store anything. On a resume that is a list of URLs.

This post is the rest of the pipeline. Five stages, each one small, each one with one job:

text
discover   read the course page        ->  list of 36 files
fetch      download the files          ->  data/raw/  (2 PDFs, 34 mp3s)
parse      read the PDFs               ->  30 lessons, 351 dialog lines
load       write it all to Postgres    ->  3 tables
export     read a sample back out      ->  corpus-sample.json for the website

Every stage can be run again without breaking anything. That turned out to be the theme of the whole afternoon, so it comes up a lot below.

First, where was the code

My checklist said to create the project on my Mac. Before running git init I checked if it already existed. Not on the Mac.

bash
ls ~/Developer/fsi-scraper
#    -> No such file or directory

It was on the control plane, and it was already on GitHub:

bash
find ~ -type d -name "fsi-scraper" 2>/dev/null
#    -> /home/op/fsi-scraper

git remote -v
#    -> origin  git@github.com:CravenCraven/fsi-scraper.git (push)

git log --oneline
#    -> 04957c1 (HEAD -> main, origin/main) Add discover for FSI Brazilian Portuguese FAST and Programmatic

HEAD -> main, origin/main on the same line means the server and GitHub were at the same commit. Nothing was waiting to be pushed, so the code was safe even if the server died.

So I cloned it instead of starting over. A fresh git init would have made a second repo with no history, and the first push would have fought the real one.

The clone came down without a tests folder. On the server there was one, and it was empty. Git tracks files, not folders. An empty folder has nothing in it to save, so git never recorded it. That answered a question I had been avoiding. The tests in my notes were never committed. They were not lost, they never existed in this repo.

worth knowing: GitHub is the copy that matters. The Mac and the server are both clones of it. I clone from GitHub, not from the server, so neither machine is special.

Getting the Mac ready

The Python version was wrong

bash
python3 --version
#    -> Python 3.9.10

pyproject.toml says requires-python = ”>=3.11”. That is not a suggestion. The code uses str | None in type hints and datetime.UTC, and neither exists in 3.9. It would not even import.

My Mac has two Homebrews. The old Intel one in /usr/local is broken on Apple Silicon, which I wrote about in the wrong cluster post. So I called the working one by its full path and built the virtual environment from that exact Python:

bash
/opt/homebrew/bin/brew install python@3.12
/opt/homebrew/bin/python3.12 -m venv .venv
source .venv/bin/activate
python --version
#    -> Python 3.12.15

A venv is a private Python for one project. Packages installed in it stay in the .venv folder and cannot break anything else on the machine. Building it from a full path means I know exactly which Python it is, instead of trusting whatever python3 happens to point at today.

Lint rules belong in the repo

When I ran ruff on the server it flagged 8 warnings, but the rules were not written down anywhere in the repo. That means another machine could check different things. So the rules went into pyproject.toml:

toml
[project.optional-dependencies]
dev = ["pytest>=8.0", "ruff>=0.6"]

[tool.ruff]
target-version = "py311"

[tool.ruff.lint]
select = ["E", "F", "I", "UP"]
bash
pip install -e '.[dev]'
ruff check . --fix
#    -> Found 11 errors (11 fixed, 0 remaining).

Eleven, not eight, because turning on I caught three import blocks that were out of order. None of the fixes changed what the code does. They change how it is written. -e installs the project in editable mode, so Python runs the code straight from src/ and every edit is live.

Discover: how the first stage already worked

I wrote discover weeks ago. Before adding stages on top of it, I went back and read it properly.

One interface for every source

Every course gets a parser that implements two methods:

python
class CourseParser(ABC):
    @abstractmethod
    def parse(self, html: str, page_url: str) -> Iterator[Resource]:
        """Yield one Resource per downloadable asset found in `html`."""

    def follow(self, html: str, page_url: str) -> Iterable[str]:
        """Yield further page URLs to crawl. Default: single-page course."""
        return ()

parse finds files on a page. follow finds more pages worth visiting. The FAST course is a single page, so it only needs parse. Another course might be an index page plus a page per unit, so it would also implement follow. The crawl loop does not care which. It keeps a queue of pages and a set of pages it has already visited, so nothing gets fetched twice even if two pages link to each other.

Polite by default

Read the URL, not the page

The obvious way to scrape a course page is to read its HTML table: this row is lesson 1, this cell is the download link. The problem is the table is the part of the page most likely to change when the site gets redesigned.

So discover ignores the layout and reads the download URLs themselves. The files live on a CDN, and the lesson number and volume are right there in the path:

python
VOLUME_IN_PATH = re.compile(r"/FAST/Volume\s+(\d+)/", re.IGNORECASE)
AUDIO_FILE = re.compile(r"Lesson\s+(\d{1,2})\s*([AB])?\.mp3$", re.IGNORECASE)
PDF_FILE = re.compile(r"Volume\s+(\d+)\.pdf$", re.IGNORECASE)

One catch. A URL cannot contain a space, so Volume 1 travels as Volume%201. A pattern looking for a space will never match that. Discover decodes the path with unquote first, so both forms come out as Volume 1 before any pattern sees them.

bash
fsi-scraper discover --course brazilian-portuguese-fast --cache .cache
#    -> pdf    Volume 1   Student Text, Volume 1  FSI - Portuguese FAST - Volume 1.pdf
#    -> audio  Volume 1   Lesson 01 (Tape A)      FSI - Brazilian Portuguese FAST - Lesson 01A.mp3
#    -> audio  Volume 1   Lesson 01 (Tape B)      FSI - Brazilian Portuguese FAST - Lesson 01B.mp3
#    -> ...
#    -> 36 assets: 34 audio, 2 pdf; units 1-30

30 lessons. 34 mp3s, because lessons 1, 6, 15 and 19 are split across two tapes. And two PDFs, the student books. All the text is in those two PDFs. The audio is the same lessons, spoken.

Fetch: downloading without trusting the network

Downloading a file sounds like one line. It is, until the connection drops halfway through a 16 MB PDF and you are left with a file that has the right name, the right extension, and half the pages. Fetch is built around not letting that happen.

python
def download(fetcher: Fetcher, resource: Resource, root: Path) -> Fetched:
    dest = target_path(root, resource)
    if dest.exists():
        return Fetched(resource, dest, dest.stat().st_size, sha256_of(dest),
                       downloaded=False)

    dest.parent.mkdir(parents=True, exist_ok=True)
    part = dest.with_name(dest.name + ".part")
    digest = hashlib.sha256()
    size = 0

    with fetcher.stream(resource.url) as response:
        expected = response.headers.get("Content-Length")
        if response.headers.get("Content-Encoding"):
            expected = None  # compressed on the wire; length won't match
        with part.open("wb") as out:
            for chunk in response.iter_content(CHUNK):
                out.write(chunk)
                digest.update(chunk)
                size += len(chunk)

    if expected is not None and int(expected) != size:
        part.unlink()
        raise OSError(f"incomplete download: got {size} of {expected} bytes")

    part.replace(dest)
    return Fetched(resource, dest, size, digest.hexdigest(), downloaded=True)

Stream it, 64 KB at a time

stream=True tells requests not to pull the whole file into memory. The loop reads 64 KB, writes it, reads the next 64 KB. A 16 MB PDF would fit in memory fine. 330 MB of audio on a laptop that is also a Kubernetes node is a worse idea. Same code either way.

Write to .part, then rename

The download goes to Volume 1.pdf.part. Only when every byte has arrived does it get renamed to Volume 1.pdf. A rename within one disk is a single step. There is no moment where the real filename points at half a file.

If the connection dies, what is left is a .part file. That name says exactly what it is. And because the check at the top looks for the real filename, the next run starts that file over instead of skipping it.

Check the size

The server sends a Content-Length header: how many bytes it is about to send. Fetch counts what actually arrived. If they do not match, it deletes the .part and reports a failure.

There is one exception in the code. If the server compresses the file on the way (Content-Encoding), Content-Length is the compressed size, and requests hands back the uncompressed bytes. They would never match. So in that case the check is skipped instead of failing every time.

Fingerprint it while it downloads

Each chunk also goes into a SHA-256 hash. By the time the download finishes, the fingerprint is done too, without reading the file a second time. That fingerprint is how I will tell, months from now, whether the file on the site changed or whether my copy got damaged.

Run it twice

bash
fsi-scraper fetch --kind pdf --cache .cache
#    -> [1/2] got     16.3 MB  data/raw/brazilian-portuguese-fast/pdf/FSI - Portuguese FAST - Volume 1.pdf
#    -> [2/2] got     10.1 MB  data/raw/brazilian-portuguese-fast/pdf/FSI - Portuguese FAST - Volume 2.pdf
#    -> 2 downloaded, 0 already on disk, 0 failed; 26.4 MB in data/raw

fsi-scraper fetch --kind pdf --cache .cache
#    -> [1/2] have    16.3 MB  ...
#    -> [2/2] have    10.1 MB  ...
#    -> 0 downloaded, 2 already on disk, 0 failed; 26.4 MB in data/raw

got the first time, have the second. —kind pdf because the parser only needs the books. Everything lands under data/raw/<course>/<kind>/, and data/ is in .gitignore, so 300 MB of course files can never end up in git by accident.

The text was already there, mostly

These are books from the 1970s. Before writing a parser, I needed to know if there was actually text in the PDF, or just pictures of typed pages. If it was only pictures, the parser would need OCR first, which is a much bigger project.

bash
python -c "from pypdf import PdfReader; r = PdfReader('Volume 1.pdf'); print(len(r.pages)); print(r.pages[20].extract_text()[:400])"
#    -> 329
#    -> I. Setting the Scene
#    -> BRAZILlAN PORTUGUESE FAST
#    -> LESSON 1
#    -> AT THE HOTEL
#    -> Checkingln
#    -> ...The driver moves quickly ta open the trunk...

There was text. Somebody already scanned the books and ran OCR on them. A PDF like this is two layers: the scanned picture you see, and an invisible text layer underneath it, lined up with the picture so you can select and search. extract_text reads the invisible layer. It never looks at the picture.

That text layer is software’s best guess at each letter, and the guesses go wrong in a pattern. Letters that look alike get swapped:

In the PDFShould beWhat it confused
BRAZILlAN, CheckinglnBRAZILIAN, Checking Incapital I and lowercase l
ta open, ontato open, ontoo and a
111. Seeing ItIII. Seeing ItRoman numeral I and the digit 1
Filling in the 81anksFilling in the BlanksB and 8
wai t, A mericanwait, Americana space that is not there

The part I actually cared about was the Portuguese. OCR software trained on English loves to throw away accents. If não came out as nao, the dialogs would be useless for flashcards. So I checked a dialog page:

text
B. Bom dia, às suas ordens!
A. Bom dia, o senhor tem uma reserva no nome de Anne Covington?
B. Nós temos um ótimo apartamento de frente no quinto andar e temos
outro com vista para o mar no sétimo andar.

às, ótimo, sétimo. All there. The English instructions are messy and the Portuguese dialogs are clean. That is the right way around.

worth knowing: look at the data before you design anything. Ten minutes with two pages told me the parser was possible, what it should anchor on, and what it could ignore.

Parse: every lesson broke a different rule

The shape of a lesson

Every lesson in the book follows the same layout:

The patterns

Here is every pattern the parser uses, and the OCR mistake each one was written for:

python
LESSON = re.compile(r"\bLESSON\s*(\d{1,2})\b")
HEADER = re.compile(r"^BRAZI\w*\s+PORTUGUESE\s+FAST$")
SEEING_IT = re.compile(r"Seeing\s+It\b")
NEXT_SECTION = re.compile(r"^[IVX1l]+\s*\.\s+[A-Z][a-z]")  # "IV. Taking It Apart"
FOOTER = re.compile(r"^\d{1,2}\s*[.\-]\s*\S{1,3}$")  # "2.2", "5.t", "13 -4"
SPEAKER = re.compile(r"^(A|B|[B8]\s?[12])\s?\.\s+(.*)$")
DIRECTION = re.compile(r"^\(.*\)$")  # "(A few minutes later)"

What broke when I ran it against all 681 pages

The first version worked on lesson 1. Then I ran it against both books and every assumption broke at least once.

bash
fsi-scraper parse > /dev/null
#    -> 30 lessons, 351 dialog lines

The command also exits with an error if any lesson comes back with zero lines. If a future change silently drops lesson 30 again, I find out right away instead of finding out from an empty flashcard deck.

Keeping the PDF out of the parser

The parser never opens a PDF. One small function turns a PDF into a list of page strings, and everything else works on that list:

python
def pdf_pages(path: Path) -> list[str]:
    """Text of every page. Kept separate so the parser itself needs no PDF."""
    from pypdf import PdfReader

    return [page.extract_text() or "" for page in PdfReader(str(path)).pages]

That split is what makes the tests possible. A test can hand the parser three pages of text typed into the test file. No 16 MB PDF in the repo, no download, no waiting.

Tests built from the real mistakes

Every test uses text copied from what the OCR really produced, typos and all:

python
def test_ocr_8_for_b_gives_b1_and_b2():
    page = """111. Seeing It
Sample Dialog
B 1. Oi Paul, como vai?
A. Eu acabei de chegar.
81. Ela é prima do Carlinhos.
82. Tudo ótimo, Fernando, e você?
13 -4"""
    lines = parse_dialog(page, lesson=13, page_no=9)
    assert [line.speaker for line in lines] == ["B1", "A", "B1", "B2"]

There is one for LESSON3, one for the missing heading in lesson 30, one that checks the accents survive, one for wrapped lines, one that makes sure the instructions are not mistaken for dialog. Each one is a case that broke once. If I ever change a pattern and break it again, a test fails before the bad data gets anywhere.

bash
pytest -q
#    -> ...........
#    -> 11 passed in 0.02s

Two hundredths of a second, because none of them touch the network or a real PDF.

Titles: a table, not rules

The lesson titles had the same OCR noise. 14 of the 30 were wrong: Checkingln, o rdering B reakfast, Ordenng Lunch, HANOLING EMERGENCIES, AT A PARTV.

The clever fix is rules. A lowercase l in front of a capital letter becomes an I. A single letter followed by a space gets joined to the next word. The problem is rules are blind. The rule that fixes Checkingln would also turn a real word like kiln into kiIn.

This is 30 titles from a book that was printed once and will never change. So I typed the 14 broken ones into a table, keyed by lesson number:

python
TITLE_FIXES: dict[int, tuple[str, str]] = {
    1: ("AT THE HOTEL", "Checking In"),
    2: ("AT THE HOTEL", "Ordering Breakfast"),
    13: ("AT A PARTY", "Being Introduced to Someone"),
    29: ("HANDLING EMERGENCIES", "Reporting an Assault to the Police"),
    # ...
}

Rules are for data that keeps changing. A table is for data that does not. The dialog text stays exactly as the OCR gave it, because it was already clean.

Why Postgres and not SQLite

For one person and 351 lines, SQLite would be fine. It is one file and needs nothing running. I went with Postgres for two reasons.

One setting switches where it runs. The code reads a single environment variable, DATABASE_URL. On my Mac it falls back to a local Postgres in Docker. On the cluster it will point at CloudNativePG. Same code, one variable, nothing else changes.

python
DEFAULT_URL = "postgresql://postgres:postgres@localhost:5432/corpus"

def database_url() -> str:
    return os.environ.get("DATABASE_URL", DEFAULT_URL)

More than one thing will read it. Flashcards, the export for the website, eventually the slang database. With SQLite each of those needs its own copy of the file, and the copies drift. With Postgres they all connect to the same one.

Locally, Postgres is one Docker Compose file:

yaml
# Local Postgres for development and tests only.
# No volume on purpose: data is thrown away on `docker compose down`.
services:
  db:
    image: postgres:16
    environment:
      POSTGRES_USER: postgres
      POSTGRES_PASSWORD: postgres
      POSTGRES_DB: corpus
    ports:
      - "5432:5432"
bash
docker compose up -d
docker compose exec db psql -U postgres -d corpus -c "select version();"
#    -> PostgreSQL 16.15 (Debian 16.15-1.pgdg13+2) on aarch64-unknown-linux-gnu ...

aarch64 is ARM. Docker pulled the Apple Silicon build by itself. exec db psql runs psql inside the container, so I never had to install Postgres on the Mac.

The schema, one decision at a time

I did not design the first table from scratch. The scraper already had a Resource record, one per downloadable file. The table is that record, field for field, so one Resource is one row and there is nothing to translate.

sql
CREATE TABLE IF NOT EXISTS resource (
    id             bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    source         text        NOT NULL,
    provider       text        NOT NULL,
    course         text        NOT NULL,
    kind           text        NOT NULL CHECK (kind IN ('pdf', 'audio', 'zip')),
    url            text        NOT NULL UNIQUE,
    filename       text        NOT NULL,
    title          text        NOT NULL,
    section        text,
    ordinal        integer,
    part           text,
    declared_bytes bigint,
    discovered_at  timestamptz NOT NULL DEFAULT now()
);

Then the two tables for the parsed text:

sql
CREATE TABLE IF NOT EXISTS lesson (
    id        bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    course    text    NOT NULL,
    number    integer NOT NULL,
    location  text    NOT NULL,
    title     text    NOT NULL,
    UNIQUE (course, number)
);

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,
    page      integer NOT NULL,
    UNIQUE (lesson_id, seq)
);

IF NOT EXISTS makes the whole file safe to run again. When I added the two new tables I applied the whole file, including the table that was already there:

bash
docker compose exec -T db psql -U postgres -d corpus < db/schema.sql
#    -> NOTICE:  relation "resource" already exists, skipping
#    -> CREATE TABLE
#    -> CREATE TABLE
#    -> CREATE TABLE

Small trap there. Postgres printed CREATE TABLE three times, even for the table it skipped. The NOTICE line is the one that tells you what actually happened.

Load: run it twice, get the same database

The first time load runs, lesson 13 goes in. The second time, it tries to put lesson 13 in again, and UNIQUE (course, number) says no. Postgres does not guess what you meant. It stops with an error:

text
ERROR:  duplicate key value violates unique constraint "lesson_course_number_key"

So every insert says what to do instead:

sql
INSERT INTO lesson (course, number, location, title)
VALUES (%s, %s, %s, %s)
ON CONFLICT (course, number) DO UPDATE SET
    location = EXCLUDED.location,
    title = EXCLUDED.title
RETURNING id

That pattern is called an upsert. The resource table gets the same thing, on url, with one column left out on purpose:

python
# discovered_at is left out of the update on purpose: it keeps the date the
# file was FIRST seen, which is the useful one.

Dialog lines get replaced, not updated

Lines are different. If the parser gets better, lesson 4 might go from 11 lines to 12. There is no clean way to match old line 7 to new line 7 when a line has been added in the middle. So for each lesson, load deletes its lines and inserts the fresh ones:

python
cur.execute(UPSERT_LESSON, (course, lesson.number, lesson.location, lesson.title))
(lesson_id,) = cur.fetchone()

cur.execute("DELETE FROM dialog_line WHERE lesson_id = %s", (lesson_id,))
cur.executemany(INSERT_LINE, [
    (lesson_id, line.seq, line.speaker, line.text, line.page)
    for line in lesson.lines
])

Delete then insert sounds dangerous. What if it crashes in between, and the lesson ends up with no lines? It cannot, because all of it runs inside one transaction. The whole load sits inside with psycopg.connect(…) as conn:. If anything fails, Postgres rolls the entire load back. Either every lesson is saved or none of them are. There is no half state.

The proof

bash
fsi-scraper load --cache .cache
#    -> loaded 36 resources, 30 lessons, 351 dialog lines
fsi-scraper load --cache .cache
#    -> loaded 36 resources, 30 lessons, 351 dialog lines

select (select count(*) from resource),
       (select count(*) from lesson),
       (select count(*) from dialog_line);
#    -> 36 | 30 | 351

Two runs. Still 351, not 702. This is what idempotent means. Running it once or ten times leaves the database in the same state. It matters because I am going to rerun load after every parser fix, and later the cluster will run it on a schedule. Neither of those can be allowed to pile up duplicates.

Export: the website never touches the database

The demo page is going to show real lessons. The obvious way to do that is to have the website read the database. There are three reasons it does not.

So export reads three lessons back out of Postgres and writes a JSON file into the website repo. The site will read the file at build time.

Read it back from the database

Export could have taken the lessons straight from the parser. It reads from Postgres instead, on purpose. The file is proof that the whole chain worked: the files came down, the PDFs parsed, the rows landed, and they come back out the way they went in.

Same data in, same file out

The export has no timestamp, and every query has an ORDER BY. Export the same data twice and you get the same file, byte for byte. It also writes with ensure_ascii=False, so às stays às instead of turning into \u00e0s, which keeps the file readable in a diff.

That paid off right away. After I added the title table, I loaded and exported again:

bash
git diff --stat src/data/corpus-sample.json
#    -> 1 file changed, 2 insertions(+), 2 deletions(-)

Two lines. Lesson 1 and lesson 2, the two broken titles in the sample. Lesson 3 was already right, so it did not move. If the export stamped the time, every run would change the file, and I would never be able to see what actually changed.

worth knowing: if a generated file is going into git, make it deterministic. Then a diff only shows up when the data really changed.

Where the audio goes

34 mp3s, about 330 MB. Three options:

Files live on disk and facts about files live in the database. That is the same split Navidrome already uses for my music. Locally they sit in data/raw/…/audio/ for now. The volume gets created when fsi-scraper moves onto the cluster, and since local-path volumes cannot grow after they are created, it gets sized with room to spare.

What is not done yet

The pipeline works end to end. That is different from finished. What is still open:

Final checklist: is the pipeline actually working

bash
# 1. The right Python, in the venv
python --version
#    -> 3.11 or newer

# 2. Lint and tests
ruff check . && pytest -q

# 3. Local Postgres is up and has the tables
docker compose up -d
docker compose exec -T db psql -U postgres -d corpus < db/schema.sql

# 4. Download, twice. The second run should say "have", not "got".
fsi-scraper fetch --kind pdf --cache .cache

# 5. Parse. Every lesson should have dialog.
fsi-scraper parse > /dev/null
#    -> 30 lessons, 351 dialog lines

# 6. Load, twice. The counts should not move.
fsi-scraper load --cache .cache

# 7. Export, twice. git diff should only show real data changes.
fsi-scraper export --out corpus-sample.json