skip to content

Two booking-tool accounts turn out to be one researcher and must be merged — what do you have to decide, and what can you not undo?

level: seniorimportance: should knowfreq 35%

answer

  1. pick the survivor deliberately
  2. enumerate every table referencing an account
  3. per-account unique indexes collide
  4. history stays written, add a merge event
  5. reversible only until it commits

basics

~20 s

Decide which account survives, what happens to rows a per-user unique index will not let you move, and what the history now says. The merge is irreversible in practice once it commits, and it does not end anything already issued to the losing account.

solid answer

~60 s

A merge is three decisions and one consequence. First, which record survives — usually the one more rows already reference, and the one bound to the tenant you are keeping. Second, what to do with collisions: identity rows re-point cleanly, but any table with a per-account unique index will reject a re-pointed row when both accounts hold one, so each such table needs a stated rule rather than a blanket update. Third, the history — re-pointing audit rows makes the survivor appear to have done things it did not, so the defensible answer is to leave them written as they were, keep the losing account as a tombstone that points at the survivor, and append a merge event carrying both ids, the actor, the time and the reason. The consequence is that once it commits, later writes land on the survivor and a reversal would need a full before-image of every moved row — and anything already issued against the losing account keeps working until it is separately ended.

code

pseudocode · 23 lines
pseudocode
merge(loser_id, winner_id, actor, reason):
    transaction:
        lock accounts [loser_id, winner_id] in ascending id order   # two merges racing would deadlock

        for row in identities where user_id = loser_id:
            if identities.exists(issuer = row.issuer, subject = row.subject, user_id = winner_id):
                delete row                     # already linked to the survivor
            else:
                row.user_id = winner_id

        for table in tables_referencing_account:
            for row in table where user_id = loser_id:
                if table.has_per_account_unique_index and collides_with(winner_id, row):
                    apply table.collision_rule(row)     # keep survivor / keep newest / merge fields
                else:
                    row.user_id = winner_id

        # history is not rewritten: audit rows keep pointing at loser_id
        accounts[loser_id].status = MERGED
        accounts[loser_id].merged_into = winner_id
        append merge_event(loser_id, winner_id, actor, reason, now)

    # note: sessions already issued against loser_id keep working until ended separately

go deeper

for a junior

Recall that a merge is not a delete: two accounts become one, the data has to go somewhere, and something must still explain what the second account did before it disappeared.

for a middle

Explain the mechanics — moving identity rows, re-pointing foreign keys, and why a table with a unique index per account cannot simply be updated when both accounts hold a row.

for a senior

Demonstrate judgment about history and blast radius: which record survives and why, why audit rows stay written as they were, and that anything already issued to the losing account outlives the merge.

for a principal

Own the policy across years: whether merges are reversible at all, what before-image you keep to make that claim honest, and how the merge rule stays consistent so the record corpus can still be reasoned about later.

## Why you are here at all The researcher booked the confocal microscope under a personal address in year one, the institute's provider later asserted a work address, nobody linked the two, and now there are two accounts with bookings, approvals and quota against each. The join has already failed; the merge is the repair. It is worth saying out loud that the best merge is the one you avoided by refusing to auto-link on an address in the first place, because merging is the operation in this whole area with the least reversibility. ## Choosing the survivor | Criterion | Why it points where it does | |---|---| | Number of referencing rows | Moving fewer rows means fewer collisions and a shorter transaction | | Correct tenant binding | The survivor must sit in the organisation you are keeping; a merge is not a tenant move | | Live identity rows | Prefer the account already reachable by the route people will use from now on | | Age of history | The older account usually anchors more audit and reporting already published | Note that these can disagree. When they do, say which one you let win and why — that sentence is the answer an interviewer is listening for. ## Moving the rows 1. **Identity rows re-point first.** They are the cheapest and the ones that make the survivor reachable by both routes. `unique (issuer, subject)` still applies, so a row already linked to the survivor is a no-op and not a conflict. 2. **Enumerate every table that references an account.** Bookings, waiting lists, approvals granted and received, saved searches, notification preferences, per-person quota, delegate assignments, API credentials, consent records. The enumeration is the real work; a merge that misses a table leaves rows nobody can reach through the surviving account, and they surface months later as a support ticket that makes no sense. 3. **Collisions need a rule per table, not a global one.** Where a table has a unique index per account — one notification-preference row, one quota row, one delegate grant per instrument — re-pointing the losing row violates it. The rule might be *keep the survivor's*, *keep the most recently updated*, or *merge field by field*, and it differs per table. What it must never be is an update that fails halfway. 4. **One transaction, locks taken in a fixed id order.** Two merges running at once in opposite directions deadlock. Make the operation idempotent so a client retry after a timeout does not move things twice. ## History is the part you cannot fix This is where the merge stops being a data-plumbing exercise: - **Re-point the audit rows** and the surviving account now appears to have cancelled bookings it never touched. You have rewritten history to make a report tidy, and any argument that rests on those records is gone. - **Leave them** and the survivor's history has a hole, with rows referencing an account that no longer appears in any listing. - **The usual, defensible answer** is the second with a repair: leave every historical row written as it was, keep the losing account row as a tombstone carrying a status of merged and a pointer to the survivor, and append a merge event with both ids, the actor, the reason and the time. A reader following the trail can then reconstruct the person; nobody has to pretend a different account did the work. Whichever you choose, write it down and apply it consistently, because the one genuinely bad outcome is a corpus where some merges rewrote history and some did not and nothing records which. ## Why it is irreversible in practice The rows are all still there, so people assume a merge can be undone. Inside the transaction, yes. After it commits: - new writes land on the survivor and interleave with the moved rows, so a reversal must also decide where each *subsequent* write belongs - restoring the split needs a complete before-image of every moved row and of every collision the rule resolved, which you only have if you captured it deliberately - side effects have already left the system — notifications sent, exports taken, downstream records updated by whoever consumes your data And one that is often missed: **the merge does not end anything already issued to the losing account.** A browser session opened against the losing account before the merge keeps working until it is separately ended, which is a different piece of work with its own lag. If the merge was prompted by a suspicion that the losing account was not the person you thought, the merge alone has not closed anything. ## Operational shape Make it an explicit administrative action with a recorded reason, never an automatic response to an address collision. Require the merge to be requested by someone who can see both accounts, capture the before-image if you want any chance of reversal, and show the operator the collision decisions the rules are about to make rather than applying them silently.

  • Why keep the losing account row at all instead of deleting it?
    Because historical rows still reference it. A tombstone carrying a merged status and a pointer to the survivor keeps every foreign key valid, keeps old reports readable, and gives support a place to land when someone asks where an account went. Deleting it either breaks those references or forces you to rewrite the history you were trying to preserve.
  • The merge was triggered because someone suspects a wrong link. Does merging close the exposure?
    No. Merging moves rows; it does not end anything already issued against the losing account, so a session opened before the merge keeps working until it is separately revoked, and any credential that account held is still live. Treat the merge as the data repair and the revocation as a distinct step with its own lag.
  • How would you make a merge reversible if the business insists?
    Capture a complete before-image of every row you move and every collision the rules resolved, store it with the merge event, and accept that reversal is still only honest for a bounded window — because writes made after the commit landed on the survivor and have to be assigned somewhere. Say what that window is rather than implying a general undo.

saying these in an interview costs you the question

  • Re-points audit rows so the survivor's history reads continuous.
  • Assumes a foreign-key update handles per-account unique index conflicts.
  • Deletes the losing account row and breaks historical references.
  • Believes the merge itself ends the losing account's live sessions.
  • Triggers a merge automatically whenever two accounts share an address.
  • Calls a merge reversible because every row is technically still present.