Here’s what a typical application that adopted the client-server architecture looks like:

1. A client-based frontend issues requests to a company backend server
2. The company backend server accepts the request and hits a company DB
3. A company DB fetches or manipulates the data (if necessary) and responds back to the backend server
4. The server responds back to the frontend client that triggered the whole chain of events

But what if clients have their own DBs?

We have what seems to be a pretty unusual way of handing that. The key peculiarity is we don’t have a separate backend layer

1. A frontend client, a medical desktop app, issues a request to a DB with what we call an “app SQL” string. The DB-hosting machine would be typically stored in a server room in the same facility.
2. The DB responds back with an SQL that matches the received “app SQL”. There’s a DB table for “app SQLs” with columns for names and actual SQL statements. Those statements may be simple SELECTs or procedure calls. Say, the frontend would call SELECT * FROM APP_SQL WHERE NAME = 'GET_ACTIVE_SERVICES' and the DB would respond with a “DTO” whose text field would store this string: SELECT * FROM SERVICE WHERE active_status = 1. “App SQL” strings, e.g. GET_ACTIVE_SERVICES, are roughly similar to endpoints, that is they provide a layer of abstraction. A DB may wish to return a different SQL at some point, and the frontend wouldn’t know.
3. The frontend, upon receiving the SQL, uses it to hit the DB again to actually get or modify the data it wants.
4. The DB processes the request (fetches or modifies its data), responds back.

Sometimes, a frontend app skips the “app SQL” phase and hits the DB with an actual DQL/DML statement right away. Typically, it happens when it only needs to perform a trivial SELECT/UPDATE/DELETE

It seems wrong. The DB effectively doubles as a backend server. You should see its procedures: complex walls of procedural code with ifs, cases, and loops. SQL was never designed for that

But how are modern apps with client-based DBs designed instead?

It seems a better solution would be to have a separate backend, even if it’s run on the same localhost (frontend-backend interaction would happen on the same PC of a medical facility employee)