DEV Community

Haelion14
Haelion14

Posted on

Postgres Account Merge Preflight: Resolving Identities Before Irreversible Writes

Short answer: make account merge a two-phase decision in Node.js: resolve identities and calculate conflicts first, then require an explicit, expiring approval before any destructive write. In healthtech, that pause protects session security without turning every duplicate-account report into a support marathon.

The dangerous request is usually phrased as “these are the same person.” A patient signs in with an email address, later uses an enterprise SSO identity, and support asks for one account. The records may share a name and date of birth while pointing at different refresh-token families, consent records, or clinician relationships. A merge that looks tidy in a spreadsheet can revoke the wrong session or join data the patient never authorized.

I treat the preflight as a read-only transaction boundary. It loads candidate identities, applies deterministic checks, and returns a decision document with a hash. It does not delete, re-parent, or revoke anything. That invariant matters during an on-call handoff: the person approving the operation can inspect exactly what the write phase will consume.

One rule is easy to remember: no preflight token, no merge.

What should an identity preflight prove before a merge?

The check should prove identity continuity, data ownership, and session consequences separately. Matching an email is evidence, not proof; OWASP recommends treating authentication factors and recovery paths as security-sensitive, and the same caution belongs here. Require a verified factor on both sides, or route the case to a staffed review when the evidence is weaker.

For each candidate pair, produce a stable result such as compatible, needs_review, or blocked. Include the evidence class, the records that would move, the refresh-token families that would be revoked, and an approval expiry. Avoid returning raw health data to an operator who does not need it; identifiers and field-level reasons are enough for the decision screen.

The conflict matrix is more useful than a single score:

Check Preflight result Write-phase action
Verified subject identifiers disagree blocked Stop; require a new identity proof
One account owns active clinical relationships needs_review Keep the source record and obtain explicit consent
Refresh-token families overlap compatible Revoke the selected family, then issue a fresh session
Recovery email or phone is unverified needs_review Ask for step-up verification

The matrix is deliberately conservative. A false negative creates a support ticket; a false positive can expose records.

A read-only preflight path in Go

The implementation can sit behind any HTTP service. The important part is that the function returns a plan rather than mutating storage. This example uses generic interfaces so the policy remains testable outside a particular database driver.

package merge

import (
    "context"
    "crypto/sha256"
    "encoding/hex"
    "encoding/json"
    "errors"
    "time"
)

type Identity struct {
    ID             string
    VerifiedEmail  bool
    Subject        string
    TokenFamilies  []string
    ClinicalLinks  int
}

type Plan struct {
    SourceID       string   `json:"source_id"`
    TargetID       string   `json:"target_id"`
    Decision       string   `json:"decision"`
    RevokeFamilies []string `json:"revoke_families"`
    ExpiresAt      time.Time `json:"expires_at"`
    Hash           string   `json:"hash"`
}

type Store interface {
    LoadIdentity(context.Context, string) (Identity, error)
}

func Preflight(ctx context.Context, s Store, sourceID, targetID string, now time.Time) (Plan, error) {
    if sourceID == targetID {
        return Plan{}, errors.New("source and target must differ")
    }
    source, err := s.LoadIdentity(ctx, sourceID)
    if err != nil { return Plan{}, err }
    target, err := s.LoadIdentity(ctx, targetID)
    if err != nil { return Plan{}, err }

    decision := "compatible"
    if source.Subject != target.Subject || !source.VerifiedEmail || !target.VerifiedEmail {
        decision = "needs_review"
    }
    if source.ClinicalLinks > 0 && target.ClinicalLinks > 0 {
        decision = "blocked"
    }

    families := append([]string{}, source.TokenFamilies...)
    plan := Plan{sourceID, targetID, decision, families, now.Add(15 * time.Minute), ""}
    raw, _ := json.Marshal(plan)
    sum := sha256.Sum256(raw)
    plan.Hash = hex.EncodeToString(sum[:])
    return plan, nil
}
Enter fullscreen mode Exit fullscreen mode

The code has no delete call, no update call, and no hidden retry that could turn a read into a write. In production I would persist the hash and the policy version, then make the merge endpoint accept only that exact pair. A stale plan should return a normal conflict response and force a new preflight; it should not silently recalculate under a different policy.

Operating the approval boundary

The write phase should be idempotent and observable. Accept an idempotency key, verify the plan hash and expiry, lock both account rows in a consistent order, and record an audit event containing actor, reason, policy version, and affected token families. Revoke refresh tokens before issuing the replacement session, so a stolen session cannot race the consolidation. Emit counters for needs_review, blocked, expired plans, and replayed approvals; alert on a sudden change in their ratios, not only on request failures.

Capacity planning belongs here too. If support creates 40 preflights per minute and each review takes 90 seconds, a queue of roughly 60 active reviews is already one hour of human work. Set an SLO for review latency separately from the API latency SLO. The former is a staffing signal; the latter is a service signal.

I’m not sure a single global expiry is right for every organization. Fifteen minutes is a reasonable starting bound for a high-risk action, but your mileage may vary when legal review or patient notification is part of the workflow. Measure the age of approved plans and adjust from evidence.

When this pattern is the wrong trade

Do not use an automatic merge for shared household accounts, delegated caregiver access, or records under an active legal hold. Those cases need a domain-specific relationship model, not stronger matching rules. Keep separate accounts when the identity proof is incomplete, when consent scopes differ, or when merging would force a user to re-authenticate during a critical care workflow; a temporary link between accounts can reduce friction while preserving boundaries.

The catch is operational cost: two phases mean more state, an approval UI, and a queue that someone owns. A small consumer app with no sensitive records may reasonably choose a reversible alias operation instead. For a healthtech platform, the extra friction is usually the cheaper failure mode than an irreversible data join.

References

Top comments (0)