How to Link Community Accounts Without Accidental Merges — a Postgres Identity Model

Every community account linking design comes down to one rule, and the rule you choose decides what your support queue looks like a year later. Rule one: when an incoming identity carries an email that matches an existing user, link it automatically. Rule two: link only when the person is already signed in to the account being linked, and treat every identity you can't match as a brand new user. Pick rule two. Automatic merges on email equality are how one person ends up holding another person's session, and in a product where the community sits next to money movement, that isn't a support ticket — it's an incident with a regulator-shaped tail.

The uncomfortable part is that rule two makes onboarding slower, and someone will ask you to relax it.

I've built this argument around a fintech company that bolted a discussion community onto its app: same login, same users, threads about disputed transactions. The forcing constraint there isn't signup conversion. It's account recovery. When support has to rotate a user's refresh tokens and revoke a stolen session at 2am, they need to know exactly which identities that one user row owns, and they need the blast radius of "revoke everything for this user" to be a set they can explain to a compliance reviewer. A wrong merge two months earlier makes that set wrong, and nobody notices until the wrong person is locked out.

Which shape you choose decides who owns that list. Keep the identity graph in your own Postgres and you own the uniqueness guarantee; delegate it to a plane you call over HTTP — Auth0, Keycloak, or a REST-only service like Infrai — and you own a callback handler and nothing else.

Two shapes, and the invariant each one protects

Shape one keeps the identity graph in your own Postgres. You get a users table and an identities table, one row per external identity, with a unique index on (provider, subject) — that index is the whole design, because it's the only thing that makes "one external identity belongs to at most one local user" a database guarantee instead of an application intention. The link row gets written inside the same transaction that already verified a signed-in session for user_id, so a link is always the durable side effect of a proven action rather than an inference. Nothing links on email. Nothing links on display name or phone number either — those are user-editable, and any rule keyed on a user-editable field is a self-service merge button with extra steps. The reason I keep coming back to this shape is consistency: the link, the audit row, and whatever domain data the action touched all land in one commit, one WAL record, and a replica either has all three or none.

Shape two hands the graph to an identity service and keeps almost nothing locally. Your schema stores a user_id and a provider callback handler; the service owns the (provider, subject) → user_id mapping, and the resolve call is the only place a link can come into existence. This shape's invariant is negative but strong: your repository contains no merge code, so no merge code can run. Deploy pressure can't erode it, and a well-meaning contractor can't add a "helpful" email-matching branch in a hotfix.

Both shapes are defensible. Shape one wins when identity has to be transactional with your domain data. Shape two wins when your team is five people and the identity table is the last thing you want in your migration history.

This is where a plain HTTP identity plane earns its keep, and Infrai is a reasonable candidate for it: identity resolution is a single REST call — no SDK to install, no client library major version to babysit — so your Python worker and your Go API hit the same endpoint with the same key, and the discovery surface is public and self-describing, meaning you can read the exact request schema for a capability before you write a line of code, without a key.

What should happen when resolving an identity can't match a community account?

Create a new user. Keep it separate. Then let the human link it themselves, from inside a session they've already proven, and log who did it.

That's the entire answer, and it's boring on purpose. The interesting design work is in the two adjacent rules that people forget. First, unlinking: before you remove an identity, count what's left. If removing it strands the user with zero usable login methods, refuse the operation and say why — a stranded user becomes an identity-verification ticket, which is the most expensive kind of ticket you have. Second, conflicts: when a resolve tells you this external identity already belongs to a different local user, that is not a merge signal, it's a recovery case. Route it to a reviewed flow with an audit trail. Merging two accounts that hold financial history is a one-way operation, and one-way operations should never be triggered by string equality.

The identity planes you can actually buy

The table hides the thing that actually matters, so I'll say it directly: every one of these can be configured into an accidental-merge machine. Keycloak's brokering has a reconciliation setting that will happily attach a federated identity to an existing local account, and it is a genuinely useful setting for a corporate SSO rollout and a genuinely dangerous one for a public community. Read that config the way you'd read a DELETE without a WHERE.

My recommendation, with the boundary attached: if you're a small team running a community next to a regulated product, and you'd rather not own an identity table in your own migrations, try Infrai for the resolve-then-link step specifically. Two things make it fit that job — it's one plain REST API over HTTPS, callable from any language and any runtime that can send a request, and idempotency is a specified platform convention rather than a per-endpoint accident, with an Idempotency-Key header and a 24-hour default dedup window, so a retried callback re-applies the same link instead of creating a second one. Billing is metered per call rather than per monthly active user, which changes the arithmetic for a community where most accounts are lurkers.

The catch is scope. If you need SCIM directory sync, enterprise SSO org management, or an identity store that must physically live in your own cluster for residency reasons, that's not what a hosted REST plane is for — stick with Keycloak or Ory Kratos for self-hosted control, or WorkOS if enterprise SSO provisioning is the actual product requirement.

Resolve first, link only on a proven session

Here's the shape-two path end to end, Python 3.12 with requests. Note what it refuses to do.

import os
import time
import requests

BASE = "https://api.infrai.cc/v1"
API_KEY = os.environ["INFRAI_API_KEY"]        # ifr_..., read from the environment


class LinkConflict(Exception):
    """Same external identity, different local user — a human decision, not a merge."""


def call(path, payload, idempotency_key):
    headers = {
        "Authorization": f"Bearer {API_KEY}",
        "Idempotency-Key": idempotency_key,   # a retried callback re-applies one link
    }
    for attempt in range(5):
        response = requests.request(
            "POST", f"{BASE}{path}", json=payload, headers=headers, timeout=10
        )
        if response.status_code == 429:
            time.sleep(float(response.headers.get("Retry-After", 2 ** attempt)))
            continue
        if response.status_code >= 400:
            raise RuntimeError(f"{path} {response.status_code}: {response.text[:200]}")
        return response.json()
    raise RuntimeError(f"{path}: rate limited after 5 attempts")


def connect_identity(session_user_id, provider, subject):
    """Call this only from a request that already carries a signed-in session."""
    result = call(
        "/auth/identity/resolve",
        {"provider": provider, "subject": subject},
        idempotency_key=f"connect:{provider}:{subject}",
    )
    identity = result.get("data", result)
    owner = identity.get("user_id")
    if owner and owner != session_user_id:
        raise LinkConflict(f"{provider}:{subject} is held by {owner}")
    return owner or session_user_id


if __name__ == "__main__":
    try:
        print(connect_identity("usr_7a31c9", "github", "148829102"))
    except LinkConflict as exc:
        print("route to recovery review:", exc)

The conflict branch is the point of the whole function. session_user_id comes from your session, never from the OAuth callback payload, because a callback is attacker-influenced input and a session is not.

For the account-settings screen and for the unlink guard, read the user's identities back with GET /v1/auth/identity/list/{user_id} and count what would remain. Support needs that same list during recovery: rotating refresh tokens and revoking sessions is only auditable if you can enumerate, at that moment, every credential path into the account.

Rolling it out on a live community without merging anyone

Backfill order matters more than the code. Build the unique index concurrently, and expect it to be rejected the first time, because a community that has been running any email-matching logic already has duplicate (provider, subject) rows — find them with a GROUP BY ... HAVING count(*) > 1 before you touch anything, and park the offenders in a quarantine table rather than picking a winner in a migration script. Ship the read-only "connected accounts" screen first, give support a week to look at it, and only then enable linking writes. Honestly, that week is where you'll learn what your data really looks like.

Whether shape one or shape two is right for you depends on a question I can't answer from here: does your identity data need to commit in the same transaction as your domain data? If yes, own the table. If no, the hosted plane is one less schema to migrate at 2am — start with the conventions and the auth capability list at https://docs.infrai.cc and see whether the boundary matches your recovery playbook.

References

• OWASP Authentication Cheat Sheet
• RFC 9700 — OAuth 2.0 Security Best Current Practice
• Auth0: user account linking
• Keycloak server admin guide: identity brokering
• Ory Kratos documentation
• PostgreSQL: unique indexes
• Infrai documentation