Serving “users” and a few other tables independently from the main PW MariaDb database

[ErikMH]

Hello, friends!

Thus far in my Processwire journey, I have created a half-dozen lightweight sites that all run on an inexpensive Vultr cloud server (also, a really exciting web app that isn’t quite ready for unveiling, but I can’t wait to show you!).

I clone this Vultr server, and “restore” it to a few other Vultr servers around the globe, and have set up Cloudflare to “load-balance” (really, “proximity-balance”) access — so rather than upgrading my single server to handle more traffic, I have cheaper, geographically-distributed servers with quite sufficient speed and lower latency. This has worked really well for my read-only sites.

I now find I need to add user accounts and lightweight user-stored data. Since I would prefer to use Processwire’s built-in user structure, I seem to have a few options:

1. Host everything on one server.
   • Pros: Simple solution.
   • Cons: I lose the low-latency geographic distribution; this would be really tragic for colleagues in Australia and New Zealand, in particular.
2. Roll my own user subsystem using Cloudflare or other cloud database and workers.
   • Pros: It would probably work? And I could keep my low-latency architecture.
   • Cons: I lose all Processwire integration; I have to learn how to create such a thing.
3. Set up all my servers’ MariaDb databases as replica masters using Galera.
   • Pros: 100% of my data would be synchronized among all my servers.
   • Cons: It sounds complicated, and it’s probably overkill. And I have no idea how I’d proceed when I update the system to include a new template or fields; I don’t think there’d be a way for me to simply “push” from a staging server the way I do now....
4. Host a few specific Processwire tables in one database instance, access it from PW sites around the globe.
   • Pros: I keep the low-latency distribution and I work primarily with Processwire.
   • Cons: I don’t know how to accomplish it.

So, obviously, I want to go with #4 if I can. But I have some questions:

1. Am I right? Or would one of the other options be preferable? Or am I missing a better solution?
2. Is #4 possible?
3. I would use hooks, somehow, to divert user-table and related traffic?
4. And a secure Cloudflare “tunnel,” for example, between the local PW host and the user-table host?
5. To save and read user-table (and related) data, I would somehow need to redefine the $user variable to use some kind of a backchannel API inquiry?

I feel like I’m just a little bit out of my depth here, and if anyone has any suggestions, I’d really appreciate your insight!
 

Edited March 7 by ErikMH
corrected text and added link for Cloudflare proximity steering

[wbmnfktr]

To dump my thoughts here:

• What's the main goal? Or ...
• What's the reason you need an off-site user management system?
• How real-time does it need to be?

 

Putting everything on one server (#1) would be perfect as you then could bootstrap ProcessWire into other instances quite easily and go that route - which actually would be #4 - but you would lose those juice ms on each request you already mentioned.

Ideas #2 and #3 sound fun but yes, probably total overkill. Never used or tried those, so no idea how well that would work out at the end here.

#4 as mentioned would probably need all sites/instances on one server as well. Not sure if and how you wouild do this across multiple serveres.

But here comes idea #5:

Get a server that's somewhere in the middle and build a your user management there. You then could put an API on top with either your custom code or something like the AppApi module and roll your own auth for some or all users. Probably a bit overkill as well, yet another approach could be some kind of API to sync accounts from and to that server. Trigger actions via webhooks in case new users were added or removed somewhere.

Could be fun. Could be pain. Not sure for now.

 

[ErikMH]

Thanks for your thoughts, @wbmnfktr! I apologize for omitting the purpose of my question....

Basically, my sites have all been “read-only,” if you will. A very limited number of people have added content to a staging site (in New York, say), and that’s been cloned to the various servers. I haven’t needed to worry about other data getting added in Sydney or Seoul or Amsterdam.

(I have the idea that most ProcessWire sites are actually fairly local: museums, schools, shops, local councils. Mine are certainly niche, but they are not in any way geographically limited.)

But as soon as I allow user logins (and user profiles and preferences and user-stored private notes, etc.), then I have data getting added in Santiago and Bangalore, etc. It will still be a small percentage of the total site data, but it has to be handled somehow.

The easiest thing would certainly be to change my topology altogether and have exactly one speedy server somewhere, but it seems unfair to add 400ms latency (x2) to every server request for visitors half-way across the planet, just to accommodate this need.

OTOH, I was thinking that would seem like a fairly reasonable price to pay for the small subset of information that was actually user-related — if only there were some way of siloing that data away on a single server. That’s where I was coming from with my original question. As I write this, though, it occurs to me that there will always be small but non-zero bits of user-related information that are being called for constantly (“does the user have permission to see this particular data?” “Display the user’s full name in the corner.” etc.) — so I’m realizing that dividing the data up really isn’t a great idea.

Your option #5 sounds quite interesting; I hadn’t been aware of AppApi. I’ve read through the documentation and will think about it.

In fact, your sixth (unnumbered) option might be the best yet: allow local addition/modification of user-related data, but set up my own synching protocol. The usual problems of conflict-resolution wouldn’t be much of a factor, since the likelihood of a single user’s data being modified on two geographically distinct servers is vanishingly small. (It would be low anyway, but Cloudflare is set to continue using a given server for the duration of a session.) I could actually use a log-synchronization strategy similar to what Galera provides for MariaDb....

I won’t be free to begin this for another four weeks, but I’ll keep digging into it when I get free moments in the meantime. Thanks again for your suggestions!

Anyone else have any thoughts?

[wbmnfktr]

18 hours ago, ErikMH said:

but it seems unfair to add 400ms latency (x2) to every server request for visitors half-way across the planet, just to accommodate this need.

The user here is only on the frontend aka consuming content from those websites?

Well, than those 400ms can be squashed by using Cloudflare as CDN. Cloudflare is close to everyone, especially regular users.
Your backend users/admins/whoever might probably be just fine with the additional 400ms when adding content, notes, and other data.

 

[ErikMH]

Wow. No, I’m sorry that I seem to be writing so unclearly. When you write:

Quote

The user here is only on the frontend aka consuming content from those websites?

you are correctly describing the current situation.

---

The whole point is that I want to open the system up for users around the world — regular people — who will be setting their preferences, creating public user profiles, tagging things privately for their own use, storing private notes. All using front-end forms and (probably) htmx.

Because servers occasionally go down and people sometimes travel, I need this data to be on all the servers.

[wbmnfktr]

On 3/8/2025 at 7:17 PM, ErikMH said:

sorry that I seem to be writing so unclearly

It's not you. That's me. I need to narrow down things first before I start thinking more deeply about problems and solutions.
it happens so often that those exact tiny details can make a huge difference.