If this problem has been created by using ad-hoc development practices, it is unlikely that the problem can be solved without some fundamental changes.

Writing a "checker script" as you mentions might help, but it will likely not solve the problem. Any warnings it generates may be ignored, and the script itself may become outdated unless rigorously maintained.

My guess is that this is a case of Conways law, i.e. that if your architecture is a mess it is likely because your organization is a mess. So I would suggest the "Inverse Conway maneuver" - I.e. you should change your organization to mirror how you want your architecture to be. If you want consistent data, create a group that has the responsibility to manage data and data consistency, and the power to actually do that. My guess is that the first step such a group would take would be to make all data access go thru a single API, and once that is done, start moving towards using a single database.

Or you could embrace the micro-services philosophy of independent groups developing independent services/applications with separate databases, where interaction between applications are governed by APIs with some type of service and backward compatibility guarantees. This would mean that cross database dependencies are minimized, and that there should be a well defined behavior for any inconsistencies that do occur.

In any case, I would not expect any "easy fix". And suggesting to completely change the way the company works is easier said than done.

There are no easy solutions.

Distributed consistency is fundamentally difficult, with some hard limits to what is possible. See the CAP theorem and Fallacies of distributed computing. And doing anything in a large and complex system is inherently costly and risky, so your desire of "a few weeks development time" seem incredible optimistic to me. I have worked at companies spending years to slowly move from one database architecture to a newer architecture.

If this problem has been created by using ad-hoc development practices, it is unlikely that the problem can be solved without some fundamental changes.

Writing a "checker script" as you mentions might help, but it will likely not solve the problem. Any warnings it generates may be ignored, and the script itself may become outdated unless rigorously maintained.

Conways Law

My guess is that this is a case of Conways law, i.e. that if your architecture is a mess it is likely because your organization is a mess. A possible approach is the "Inverse Conway maneuver" - I.e. you should change your organization to mirror how you want your architecture to be. If you want consistent data, create a group that has the responsibility to manage data and data consistency, and the power to actually do that. My guess is that the first step such a group would take would be to make all data access go thru a single service/application layer/API, and once that is done, start moving towards using a single database.

Or you could embrace the micro-services philosophy of independent groups developing independent services/applications with separate databases, where interaction between applications are governed by APIs with some type of service and backward compatibility guarantees. This would mean that cross database dependencies are minimized, and that there should be a well defined behavior for any inconsistencies that do occur. But this require some serious thought about where the boundaries should be, and how to handle all the various failure modes.

Of course, suggesting to completely change the way the company works is easier said than done.