Running a 13-service hospital system locally on PostgreSQL, without touching patient data
A case study in porting production code from MySQL to PostgreSQL, rebuilding its data safely, and proving it with 43 working report PDFs
At a glance
| Situation | A city government's hospital information system ran as separate Laravel services on MySQL. Only old backups, a pasted schema and a shared package were available locally, and the goal was to run and test it on PostgreSQL without production data |
| Approach | Document the services, convert the schema, bring in the production code, adapt it to PostgreSQL, rebuild the schemas from the services' own migrations, seed fictional data, and prove the system by rendering every report |
| Window | 24 to 29 October 2026, with most of the build on 26 and 27 October |
| Output | 13 Laravel 12 services running in Docker against one PostgreSQL database (691 tables), seeded with fictional data, 43 report PDFs from the demo patients, scripts to rebuild everything, and a developer guide |
| Method | The consultant supplied files, made decisions and checked results. Claude Code read, ported, tested and documented, and ran four agents in parallel for the report work |
| Status | Working local environment. One service is not ported, routes that write data are untested, and a number of routes still fail because of defects in the original code |
The situation
The hospital information system is a set of Laravel APIs that share a database layer, a token service and a common package of 208 models. In production it runs on MySQL. The consultant wanted to recreate it on PostgreSQL locally, seed it, and run it, for study and for report testing.
What was on hand at the start was thin.
- Six service backups, all Laravel 8, in a workspace that was not a git repository.
- No shared package. The folder where it should have been vendored was empty in every service.
- A schema in a notes file, as MySQL definitions for the production databases, dated earlier than the code.
- No PHP, no Composer and no
psqlon the machine, and Docker not yet running.
The data was the real constraint. The production databases hold patient records. A local copy would have put them on a laptop, so the rule from the start was: structure and fictional data only.
How the work was run
- Read before changing. Claude read the six services and wrote a root context file plus one per service. The root file recorded what they share: routes loaded by scanning feature folders, models from the shared package, and each service connecting directly to others' databases. It also warned that the
.envfiles held real credentials, which Claude never opened. A search of the two cloud VMs reachable from the laptop found nothing, though that was not conclusive, so the question was left open. - Choose the data policy. Asked whether the local copy should hold production data, the options were schema only, seeded data, or an export. The consultant chose schema and seeders. That decision shaped every later step.
- Convert the schema. A converter script turned the MySQL definitions into PostgreSQL: auto-increment to identity columns, JSON to
jsonb, datetimes to timestamps, long text totext, unsigned integers tobigint. One database became one PostgreSQL database with one schema per former MySQL database. Booleans (tinyint(1)) becamesmallint, notboolean, because the applications compare against raw 0 and 1. The result: 490 tables and 1,925 indexes in 14 schemas, loaded into a container with nothing rejected, and every schema's table count matched the source. - Pilot one service. The consultant supplied the shared package, and Claude checked it before extracting: 279 commits and tags up to 2.3.0, models only, no migrations, no secrets. The package needed Laravel 12 while the backups were Laravel 8, and the consultant decided to run Laravel 12. Claude ported the authentication service first because every other service depends on its tokens. It built a fresh Laravel 12 skeleton and copied code over. Booting it exposed the changes: a new application bootstrap in place of the old kernel and providers, session support restored for an API that stores CSRF tokens, connection names changed from MySQL to PostgreSQL schemas through
search_path, and new Passport keys. Login returned a token, and the seeded roles and permissions (66 roles, 74 permissions) answered over the API. - Switch to the real code. While Claude was porting the other five backups with a script, the consultant downloaded an archive of all 14 production services. Thirteen were already on Laravel 12. The backups were stale and the port was stopped. Claude wrote a second script that copies each production service into a working folder, rewrites its connections for PostgreSQL, writes a local environment file, installs dependencies, and links one shared Passport key so a token from the authentication service works everywhere. Dependencies now live in Docker volumes because installs on the Windows drive timed out.
- Rebuild the schemas from migrations. The pasted schema was older than the code, so the final schemas were built from each service's own migrations, after marking pre-snapshot migrations as run. Three hundred and twenty-one migrations had MySQL-only lines commented out, migrations that change and rename a column in one step were split, index-only migrations were skipped, and compatibility casts and 14 tables that existed only in the old snapshot were added. The database grew to 691 tables and covered two schemas that were absent from the notes.
- Smoke-test every service. Using a token, Claude called every route that takes no parameters. Many returned 200, such as billing 41 of 47 and utilities 62 of 65. The remaining 500s were sorted into PostgreSQL problems that could be fixed and defects in the original code, such as undefined controller methods, missing columns and an unregistered middleware.
- Get the report templates. Report generation needs the Jasper templates. The first sizing command hung on the server, and the pasted listing showed why: a data folder whose directory entry alone was about 205 MB, meaning millions of files. That folder holds the JSON for each generated report and is patient data, in use by the live system. Claude excluded it, and a harmless tar warning (a changing folder timestamp) was explained. The archive held 449 entries and 5.6 MB, with none of the data folder. The same step later retrieved the clinical form templates (148 files, about 15 MB) and swapped them in for temporary stand-ins.
- Make the reports run. Report rendering needed a Java runtime. The bundled report engine crashed on any Java after 8, so Java 8 was installed in the image (the other option, a newer engine, was rejected as risky for the production layouts). Then came fonts, a font fallback file, an HTML-to-PDF binary that the image lacked, and a setting that lets that binary read local assets.
- Seed the data. The services' own seeders ran in a fixed order, and failures were rerun from the point where they stopped. A shared demo scenario of two fictional patients, one inpatient and one outpatient, gave every service rows to attach to. Four agents worked in parallel, one each for billing, orders, insurance claims and clinical forms, seeding their service and rendering its reports. A last script reran the whole demo seed from scratch to prove it was repeatable.
- Package the proof. Every renderer was run fresh and the newest PDF for each report was collected into a zip of 43 PDFs plus a results file, named with the date and time. The consultant asked for a standing rule that every file prepared for download carries a timestamp, and Claude saved it as a memory.
What the port exposed
PostgreSQL is stricter than the MySQL the code was written for
The most useful part of the work was a catalogue of behaviours the code silently depended on.
- Over-long values. MySQL truncates them silently. PostgreSQL rejects them. A seed failed on an inventory text value, and the fix widened 3,331 columns of 255 or more characters to
textin the local copy. - Not-null drift. A Laravel 12 column change drops the nullable setting, so five columns drifted to NOT NULL and a seeder failed on a null. They were relaxed to match the production definitions.
- Empty strings and loose typing. MySQL turns an empty string into 0 for numbers, takes a numeric prefix from text ("120px" becomes 120), and accepts empty strings for dates. A small provider added to all 13 apps reproduces those behaviours on save, including matching column names without regard to case.
- Sequences. The seeders insert explicit ids, so identity sequences stayed at 1 and the first normal insert would collide. All 591 were reset.
- Booleans in report data. PostgreSQL booleans serialise to
true, while the report templates expect numbers. Several forms failed until the same cast was added to each service. - Case-sensitive search and mixed types remain on the open list:
LIKEis case-sensitive, and comparing an integer to a text column is an error.
The reports
All report routes that were attempted rendered with the demo data. The 43 PDFs covered patient data sheets and stickers, billing receipts and statements, order documents and labels, insurance claim forms and clinical documents. Four order reports needed an HTML-to-PDF binary, which was downloaded into the image (the official build for one Debian release ran on a newer one). Rendering the output to images and looking at it caught problems the route status alone would not: stand-in templates with a banner, header-only monitoring sheets because the real template reads its rows from a different field, and a date format the template parsed differently from what the seeder wrote.
Where it went wrong, and how it was handled
- Porting stale code. Hand-porting the Laravel 8 backups for two services took time that the production archive made unnecessary. The lesson recorded in the notes was that the server copy is the base, and older backups only showed which changes PostgreSQL needs.
- A sensitive archive. The consultant's archive of the production services included the production token-signing private keys, because the exclude list missed files placed directly under
storage/. Claude had listed the archive before extracting, extracted without any.keyfiles, generated separate local keys, and asked the consultant to delete the archive from the laptop and the server. - Shell quoting. Backslashes and escapes were mangled more than once in shell heredocs, and one
sededit broke a seeder halfway, creating duplicate document rows on rerun. The scripts moved into files written with the file tool, and the duplicate rows were removed. - Overlapping background runs. Early port attempts overlapped and could not be killed cleanly. The strays were stopped, half-built folders deleted, and only one run kept.
- A hang on production. The template command hung because it walked the live data folder, and the consultant noted it was actively used. The answer was to avoid sizing commands inside that folder at all, since walking millions of files costs real I/O on a live server.
- Claims with limits. Several statuses carried explicit limits: the Excel import, uploads and write routes were not exercised, a Java download size was from memory and flagged, and the chargeslip prices showed 0.00 because pricing lives in another service.
What was left open
- One service is not ported. The reports service is still Laravel 8, and 77 of its files use raw MySQL SQL.
- Write paths. POST and PUT routes, queued jobs and file handling were not tested.
- Code defects. The remaining failing routes are mostly problems in the original code and need a decision on who fixes them.
- Blank fields. Some report bodies are empty where the demo data does not match the real templates' field names, including the discharge summary and clinical notes.
- Search and types. Case-insensitive search and integer-versus-text comparisons still need a systematic pass.
- Cleanup. The temporary archives on the server and in the consultant's downloads folder needed deleting.
Takeaways
- Decide the data policy first. Choosing schema plus fictional data at the start meant every later step was safe by default.
- Prefer the real code over a faithful port of old code. The server copy removed days of guesswork.
- Rebuild structure from migrations, not from a snapshot. The snapshot was older than the code and missed two schemas.
- Write down what the old database tolerated. The leniency list is now a checklist for anyone moving a MySQL application to PostgreSQL.
- Look at the output. Status codes said the reports worked. Looking at them found stand-ins, blank bodies and the wrong date formats.
- Parallelise by service, serialise by shared files. Four agents each owned one service, and the shared files were consolidated once they finished.
- Stay out of live data. Excluding the live data folder, listing archives before extracting, and keeping keys out were small habits that avoided the real risks.
About these numbers
Table, index, route, migration, sequence and file counts come from the session's command output: the schema load, the migration runs, the smoke test and the packaged results. The 3,331 widened columns and 591 reset sequences are counts reported by the scripts that did the work. Smoke-test results are for routes that take no parameters, called with one local development account, and do not cover writes. All data in the environment is fictional or lookup data from the services' own seeders. Cloud project, server, account and organisation names are deliberately left out.