Taped-Up DBs, But It Works

We did not integrate two databases. We copied tickets one way, on a timer, through an API that is allowed to be wrong for five minutes.

That is tape. The tape holds because we were strict about the direction.

Two products, one guest login

The new app is a field tool: photos, on-box vision models, background jobs, GPS, a workflow the legacy schema never heard of. The old app is still the system of record for work requests. Crews log into us. Closeout still has to go back through their HTTP API. Their MySQL is not our house.

So we did not share a database. We did not put a foreign data wrapper over work_requests. We did not replicate their tables into ours. Browsers talk to our web app. The web app talks to our API. Only that API may touch MySQL, and only with SELECT. The worker never sees the source database. It drains jobs that already sit in Postgres.

The copy

Sync is not a conversation. HTTP returns ticket ids. We SELECT those ids from MySQL. Non-empty MySQL fields overlay the HTTP payload: site, GPS, contact, company. The merge is upserted into Postgres tickets.raw_json. After that we work from Postgres until the next copy.

Dashboard load does it for the signed-in user. A worker repeats it every 300 seconds. A ticket-link call loads one MySQL row by owner id and request id, then writes the same Postgres row. Inspections, scores, and local status never travel the other way.

sequenceDiagram
    actor Crew
    participant HTTP as Legacy HTTP API
    participant API as Our API
    participant MySQL as Source MySQL
    participant PG as Our Postgres

    Note over Crew,PG: Copy happens on dashboard open
    Crew->>API: open dashboard
    API->>HTTP: list tickets
    HTTP-->>API: ids + payload
    API->>MySQL: SELECT work_requests WHERE id IN (...)
    alt answers within 5s connect / 10s read
        MySQL-->>API: site, GPS, contacts
    else timeout
        MySQL--xAPI: no row
        Note over API: warning only, keep HTTP payload
    end
    API->>PG: UPSERT tickets.raw_json
    Note over PG: inspections and scores stay here

    Note over Crew,PG: Closeout is a later call, not this copy
    Crew->>API: submit closeout
    API->>HTTP: closeout
    HTTP-->>API: accepted
    Note over MySQL: still no writes from us

Tuesday, 06:40. A crew opened the dashboard. Source MySQL did not answer in 5 seconds. We logged a warning, upserted the HTTP row, and the ticket rendered with a title and no pin. They started work. The pin arrived on the next 300-second poll. Dispatch asked why the map was empty for four minutes.

That is the scar. Blocking on their MySQL would have been the clean dashboard. It would also have made their outage our outage. Missing GPS for one poll is worse than a spinner. A white screen in the field is worse than both.

Why not one database

Putting our tables into their MySQL couples a new product to a schema we do not own. Using their database as our operational store mixes inspections into their domain. Joining live at query time makes every dashboard depend on a hop we cannot page.

We borrow ticket facts. We leave them there. Guest credentials use a different env prefix than their app's DB_HOST, on purpose: same server, same table, not our house.

Best practice, with the ugly bit named

The bounded part is textbook. Read-only access to a system of record, plus a local working copy, is an anti-corruption layer. Tickets we already copied keep working if source MySQL is slow. They stay unaware that inspections exist.

The taped-up part is the dual inbound path. Ticket identity still comes from HTTP. Extra columns come from MySQL. Merge rule: database wins when the value is not empty. That hides disagreement. If HTTP says approved and MySQL still says pending, we show the MySQL value and neither team would fully claim the screen. A cleaner design would pick one canonical read for details and keep HTTP only for login and closeout. We had a live API, a live table, and a field app that needed both.

Polling is tape too. Five minutes is not event-driven. For a crew already on site, it is soon enough. For a dispatcher staring at a status that changed 90 seconds ago, it is a lie we accepted.

The rule we will not break

Do not write to the source database.

The moment we UPDATE work_requests, the tape becomes a two-way cable. Then we own their schema, their transactions, and the night someone files a ticket because our inspection closed a request their finance screen still thought was open. Closeout goes through their HTTP API, which is their write path. Ours is copy-in.

Copy in. Never copy out. Live in Postgres.

What "it works" means

It works if a crew can open a ticket, see the site when MySQL answered, run the checks, and submit a closeout without us becoming a second ERP.

It works if they add a column we do not read and nothing here restarts.

It works if 06:40 looks like a ticket with no pin, not a 502 on the briefing.

Two databases. One direction. A 300-second lie we can live with.

Taped-up DBs. But it works.