Living systems
Writing to FoxPro tables that are still open
Getting 500,000+ records out of a 1990s system without closing the business for a single day.
- Period
- 2024 —
- Role
- Design and implementation
- Stack
- Python · FastAPI · PostgreSQL · FoxPro · DBF · Alembic · Docker · Linux
- Proof
- py-foxpro-engine ↗
01 Context
FoxPro still runs the daily operation of a private clinic, and the application sits open on every desk from eight in the morning. There had never been a migration: the data lived on the local network, tied to the application that wrote it, and the only way anything got out was an Excel report someone exported by hand.
02 Problem
Every normal route — ODBC, exporting by hand, a third-party Windows tool — needs nobody to be using the table. Some of them write in a way FoxPro no longer recognises afterwards. And a weekend export would not solve the actual need anyway: admissions has to resolve a patient by their ID document while every cash desk is writing to that same file.
03 Constraints
- The operation cannot stop. Not for one shift.
- The 1990s application is untouchable: no source, no vendor, no support contract.
- Health data is a sensitive category under Peruvian law. Nothing leaves the clinic network uncontrolled.
- It has to run without depending on a particular Windows runtime or a 32-bit driver.
04 Decisions
01
Write the DBF engine, do not adopt one
A parser that understands the table header, the null flags and the 0x1A end-of-file marker, and that reads and writes records at their byte offsets. No dependencies at all.
DiscardedThe VFP ODBC driver needs a 32-bit Microsoft runtime and locks the whole table. The read-only Python libraries solve half the problem: they read, and the job here is to write.
02
Lock byte ranges, not the file
The engine holds only the bytes of the record it is touching. The legacy application keeps reading and writing the same file at the same time and never notices. This is the decision the whole thing hangs on.
DiscardedAn exclusive file lock is what off-the-shelf tools take, and it is exactly what forces the maintenance window this project exists to avoid.
03
Reserve the native auto-increments
FoxPro keeps its own counters. Inserting without reserving them breaks referential integrity with the old system — silently, and days later.
04
Write a whole record in one pass
A record is serialised complete in memory and written in a single operation, never field by field. There is no instant in which a concurrent reader can see a row that is half old and half new. Deleting marks the row, as FoxPro does; it never compacts, because moving records would invalidate every index and every record number another process is holding in memory.
05
Do not trust the header
A table FoxPro did not close cleanly declares one record count and contains another. The engine reports both — the declared one and the one measured from the file size — and flags whether they agree. That flag is what makes the migration decision below possible in the first place.
06
Migrate by data quality, not by volume
The lab results table alone holds over two million rows going back to 2019. Only 2025 and 2026 were migrated: everything older is inconsistent, and it now comes across on demand, by id, when someone actually needs it. Moving it all would have been easy to announce and would have poisoned the reporting.
DiscardedA full historical backfill, which is the default answer and the wrong one here.
07
Ship it as a service, not a script
A FastAPI service where a migration is launched by date range and followed by its id, next to a scheduler that starts and stops while the system is up, and endpoints that serve the aggregates once the data has landed.
DiscardedA cron job someone remembers to check is a job that stops being checked.
08
Extract the engine and publish it
The technical value is in the engine; the commercial value and the data belong to the client. So the engine came out stripped of every business rule and went out under MIT, and the pipeline that knows about the clinic stayed private.
- Windows ServerFoxPro · .dbf open all day
- Shared foldersame LAN, two OSes
- DBF engineFoxPro’s own lock protocol
- FastAPIqueries live or migrates
- PostgreSQLadmissions · reception · reports
↩ And back: the legacy system calls these endpoints to build reports it could not produce on its own.
05 Result
- Over 500,000 historical records in PostgreSQL, with zero days of downtime.
- Around 2,000 records a day through the automated pipeline.
- The point was never the downtime avoided: it is that the data stopped being local. Once it was reachable from anything, things that could not even be proposed before became ordinary — dashboards that refresh themselves, one device reacting to what happened on another, and analysis over history rather than over last month’s spreadsheet.
- The engine published as a standalone, dependency-free package.
- The traffic goes both ways, and that is the part I did not expect. My service reads from the legacy system — and the legacy system now calls my endpoints back, because a colleague uses them to build reports his own stack could not produce. An admission recorded on one side shows up on the other without anyone retyping it.
- The day-to-day proof: admissions pulls the day’s schedule from a tablet, reception resolves a patient’s whole history from their ID document in real time — insured, private or inpatient, and whether they have an appointment — and management reads live reports from a phone. All of it while every cash desk is still working inside the 1990s application.
- The published engine carries tests for the writer and the introspector — the part that would hurt if it were wrong. The private pipeline that orchestrates it does not: it has health checks, not a suite. Saying both is the honest version.
- Extracting it surfaced two correctness bugs that had been hiding inside the pipeline: opening the file without binary mode, which corrupts the EOF marker and throws off the record count, and a header lock that was never released for the whole session. Pulling a piece out into the open is itself a review.
06 Proof
Everything above is visible here: py-foxpro-engine ↗