Author: Felycia Edelyne & Jovan William

Once, there’s a condition where the donor passes away at Hospital and a kidney is suddenly available. Three other hospitals have patients waiting for one. Two of those hospitals hit “accept” at almost the same second. So… who gets the kidney?

If your answer is “whoever’s request reaches the server first”, it means that you just stumbled into one of the nastiest problems in database design. Organs only stay usable for a few hours, so there’s no “let’s sort it out tomorrow”. Promise the same organ to two patients and one of them loses a chance that might never come back.

The real problem isn’t storage, it’s all about “Coordination”

Information about donors, recipients candidate, hospital facility readiness, and transport is already scattered across different process along hospitals, that changes constantly (Gilbert & Lynch, 2002). Each hospital has different methods to collect, manages, dan updating data. If nobody keeps it in sync, three things could go wrong (Swanson et al., 2010; Joint Task Force, 2020):

• Matching gets slow. Stale data means the system matches an organ to someone who’s no longer on the list.
• Offers get out of sync. Hospital B thinks an offer is still open while Hospital C already took it. That’s the dreaded double allocation (Saito & Shapiro, 2005).
• One bad connection stalls everything. If hospitals depend completely on each other’s network, a single outage can freeze real operations.

On top of that, there’s a privacy twist. Donor and patient identities must stay secret, but every access and every change still has to be traceable and auditable. So we need data that is fresh, consistent, private and accountable, all at once (Gray & Lamport, 2006).

Functional Requirement s— What specific tasks should the user be able to perform?

• Login with roles. System authenticate user and apply access rights according to responsibilities. Admins, hospital staff, and auditors each see only what they’re allowed to see.
• Record donors, organs and recipients. Identity data is split off from clinical data right from the start.
• Hospital transport resources. Store and update locations, contact details, and estimated transport distance.
• Keep a waiting list with a history of every change, ranked only by approved rules.
• Search across hospitals for matching candidates, with the reasons why they match including relevant urgency, hospital details, and also available trasnportation information.
• Sync and detect conflicts. If two hospitals update the same thing differently, nothing gets silently overwritten. A human reviews it.
• Notify the right people when offers are accepted, rejected or about to expire, without leaking identities in the message.

Non-Functional Requirements — Under what conditions does the system need to operate smoothly?

• 95% of searches finish within 3 seconds, with 100 people using it at once across four hospitals.
• Consistency and integration, so the system didn’t overwrite any conflicting critical updates without detection.
• Service availability of at least 90% per month.
• Recovery time (RTO) of 60 minutes max and data loss (RPO) of 3 minutes max for committed data.
• Sensitive data encrypted in transit and never stored as plain text, following Indonesia’s Personal Data Protection Law (UU No. 27/2022).
• Format alignment across hospital data exchanges require aggreed, versioned, and versioned formats.

Entity Relationship Diagram

To make sense of all this data, we first modeled it as 13 entities: hospitals, donors, organs, recipients, waiting list, match offers, transplants, transport and transport routes, user accounts, an audit log, and two separate identity tables (one for donors and one for recipients) so that names never mix with medical data. Here’s the model in two notations. The first is the classic Chen style (Figure 1) and the second is the Crow’s Foot style (Figure 2) that looks more like real tables.

Is one big central database enough?

A single central server sounds simple, but it gives you a single point of failure. If it goes down, or the link to it breaks, every hospital is stuck. It also puts every patient’s sensitive data into one place, which could pose a risk of data leakage.

The opposite extreme, copying everything to every hospital, isn’t great either. Sync gets expensive and every copy is one more place for data to leak. So we aimed for the middle: keep data close to where it’s used and only centralize the tiny bit that must be consistent.

Database Architecture for Optimizing These Problems

Our setup has four hospital nodes. Hospital A is where the donor organ comes from. Hospitals B, C and D are the recipient hospitals. Each hospital keeps its own data locally, in three separate databases:

• Clinic: medical records and test results.
• Identity: names and national IDs, that’s all encrypted.
• Audit: the history of all activities.

Splitting these up is a big deal for privacy. The identity data can be reached only by people with the right role, and it’s linked to the clinical data through an ID, not through names.

Then there’s the coordination cluster: three servers, K1 (primary), K2 and K3 (replicas). It only holds three things: the status of each organ, the match offers, and the hash of the audit trail. It also acts as the coordinator when hospitals need to agree on something and it handles automatic failover if the primary dies.

Everything a user does first goes through a gateway that checks Multi-Factor Authentication (MFA) and Role-Based Access Control (RBAC), then routes the request to the right node (Haerudin, et al., 2025; Sahyudi, M., & Susanto, E. R., 2025).

There’s a data warehouse path too, but it’s kept off to the side, so it never slows down real allocations. Analytics runs on a read-only replica, a routine ETL job moves the data into the warehouse and a dashboard shows things like waiting time and how often allocations succeed.

Here’s the whole picture in one diagram (Figure 3):

So what happens when an organ becomes available?

1. Hospital A records the donor, the organ, and the clinical data.
2. The gateway checks the user’s login and role, then passes the request to the coordination cluster.
3. The cluster matches the organ against candidates across B, C and D, and locks the organ for the best match.
4. The chosen hospital accepts or rejects via a two-phase commit (more on that soon).
5. Every step is written to the audit ledger.

Fragmentation, Replicate, and Allocate Date

This is the part where the “distributed” in a distributed database really shows up.

Fragmentation means cutting the data up. We could cut the data in two ways. First, horizontally: the recipient table is split by hospital_id, so Hospital B keeps only B’s patients, C keeps C’s, and D keeps D’s. Same columns, different rows. Donors live only at Hospital A. Second, vertically: each hospital splits its data into clinic, identity and audit, because they have very different sensitivity levels.

Replication means making copies. Not all data is treated the same:

• Clinic data is copied asynchronously to a standby at a partner hospital (A with B, C with D). It’s big and sensitive, so we don’t make everyone wait for the copy to finish.
• Identity data is not replicated at all. It only has an encrypted backup stored locally.
• Audit logs stay at their hospital and get copied asynchronously, but their hashes are sent synchronously to the coordination cluster. That means anyone can verify the log hasn’t been tampered with, without seeing the sensitive stuff.
• The coordination cluster’s three databases are replicated synchronously, and a write only counts once a majority (2 out of 3) agrees. That’s what stops double allocation.

Allocation part will decide where things live. Three strategies given:

• Partitioning with no copies for the hospital data (it stays where it’s used)
• Partial replication for the standby pairs (so data survives a hospital going down)
• Full replication for the small, frequently read, must-be-consistent coordination data. This avoids stuffing everything into one server, and it avoids copying everything everywhere.

Mechanism to stop double booking

Remember the two hospitals hitting “accept” at the same time? Here’s the toolkit we use to deal with it:

• One coordinator per organ type. Every status change goes through that coordinator, so there’s no parallel decision-making.
• Locks with a lease. While an organ is being offered, it gets a temporary lock so nobody else can offer it. The “lease” part means the lock expires by itself if something crashes, so nothing stays locked forever.
• Optimistic concurrency. Before saving, the system checks whether the organ’s data changed since you last read it. If it did, your update is rejected instead of silently overwriting someone else’s.
• Two-phase commit (2PC). The coordinator first asks everyone involved “can you do this?” (prepare), and only if all say yes does it say “go” (commit). If anyone says no, everything rolls back.
• Transaction isolation, so one transaction can’t peek at another one’s half-finished work.

Recovery system process

For recovery, Write-ahead logging (WAL) writes every change to a log before applying it, so after a crash the server can replay the log and know what was finished and what wasn’t (Narrative Goldmine, 2026). If the main server is gone, the standby takes over. If a 2PC gets cut in the middle, the system checks the transaction’s status with the coordination cluster and cancels it if it was never committed.

For cases like someone marking an organ as “allocated” with no matching record in the audit log, regular backups plus archived WAL let us do point-in-time recovery: roll the database back to right before things went wrong. If Hospital A goes down completely, B, C and D can still see organ status, offers and audit hashes thanks to the replicated cluster.

Security, the most important part because it’s related to people’s private data

• MFA and session timeouts for login, plus RBAC so people only get the access their job needs.
• Pseudonymization. Real identities stay encrypted in separate, non-replicated storage.
• Encryption both at database or media storage (on disk) and in transit (over the network).
• Separation of duties, so the person who changes data isn’t the person who checks it, plus monitoring that flags weird access patterns.
• Detect abnormal access patterns that involve sensitive data if any suspicious activity occurs.

Limitations and Trade Offs

This architecture remains strictly at the conceptual stage without functional prototypes, load testing, or empirical benchmarks, all baseline performance targets that 95% search success rate within 3 seconds, 90% availability, and defined Recovery Time Objective (RTO) and Recovery Point Objective (RPO) metrics are unverified assumptions rather than proven capabilities. Consequently, this design reflects a deliberate trade-off that prioritizes rapid high-level planning and initial resource efficiency over early operational validation, which sacrifices guaranteed system resilience and exposes the architecture to potential structural rework once formal risk analyses and real-world production demands are established.

Conclusion

From the initial problem statement to the recovery and security plan, this proposal treats organ allocation as a coordination challenge across hospitals not simply as a matter of storing more records. Its 13 entity data model separates personal identity from clinical information. The architecture keeps hospital data close to where it is used, partitions records by hospital and sensitivity, and selectively replicates only the information needed for standby recovery or shared allocation decisions.

At the center of the design, a three-node coordination cluster is intended to maintain a common view of organ status and offers. A designated coordinator, temporary locks, optimistic concurrency checks, transaction isolation, and two phase commit are proposed to reduce the risk of conflicting decisions and double allocation. When a connection fails, the system favors local reads and postpones new cross-hospital allocations until synchronization can resume. WAL, backups, point in time recovery, and standby nodes address recovery, while MFA, role-based access, encryption, pseudonymous IDs, and auditable logs address privacy and accountability.

Taken together, these choices form a layered blueprint: keep sensitive data controlled locally, coordinate critical decisions explicitly, and make changes recoverable and traceable. Before the design could guide real operations, its clinical rules, data-sharing responsibilities, failure behavior, security controls, and proposed performance and recovery targets would need to be agreed upon and tested with the participating hospitals.

References

Anwari, A. D., Akbar, R. J., & Ijtihadie, R. M. (2021). Implementasi optimistic concurrency control pada sistem aplikasi e-commerce berdasarkan arsitektur microservices menggunakan Kubernetes. Jurnal Teknik ITS, 10(2). https://doi.org/10.12962/j23373539.v10i2.66255

Gilbert, S., & Lynch, N. (2002). Brewer’s conjecture and the feasibility of consistent, available, partition-tolerant web services. ACM SIGACT News, 33(2), 51–59.

Gray, J., & Lamport, L. (2006). Consensus on transaction commit. ACM Transactions on Database Systems, 31(1), 133–160.

Haeruddin, H., Prasetyo, S. E., & Mindy, A. (2025). Implementasi multi-factor authentication untuk optimalisasi keamanan akses data. Jurnal Manajemen Informatika (JAMIKA), 15(1), 85–97. https://doi.org/10.34010/jamika.v15i1.14190

Sahyudi, M., & Susanto, E. R. (2025). Analisis implementasi sistem keamanan basis data berbasis role-based access control (RBAC) pada aplikasi enterprise resource planning. SATESI (Jurnal Sains Teknologi dan Sistem Informasi), 5(1), 105–116.

Saito, Y., & Shapiro, M. (2005). Optimistic replication. ACM Computing Surveys, 37(1), 42–81.

Swanson, M., Bowen, P., Phillips, A. W., Gallup, D., & Lynes, D. (2010). Contingency planning guide for federal information systems (NIST Special Publication 800–34 Rev. 1). National Institute of Standards and Technology.

Joint Task Force. (2020). Security and privacy controls for information systems and organizations (NIST Special Publication 800–53 Rev. 5). National Institute of Standards and Technology.

Narrative Goldmine. (2026, November 4). Write-ahead logging. https://narrativegoldmine.com/notes/pages/Write-Ahead-Logging

Yasin, M., Sohail, K., Ahmad, I., & Nazar, S. K. A. (2016). Data partitioning and fragments allocation in distributed database. In Proceedings of the 2016 International Conference on Communication System and Network Technologies (CSNT). IEEE.