Hey everyone, Alex here. Welcome back to another edition of Coding with Alex on sysseder.com.
If you’ve been scrolling through Hacker News today, you might have seen a bizarre and fascinating story trending under the headline "The other Sean Byrne doesn't exist." It’s an eye-opening tale of a developer who discovered a ghost in his own machine: a completely separate, non-existent person with his exact name was somehow generated in various public databases and credit bureaus, creating a digital doppelgänger that wreaked havoc on his real-world identity.
While the story reads like a techno-thriller, it immediately triggered my software engineering spidey-senses. Why? Because as developers, we are tasked with modeling the chaotic, messy reality of human existence into rigid, binary database schemas. We assume "uniqueness" is a solved problem. We slap a UNIQUE constraint on a column, or auto-generate a UUID, and dust our hands off.
But real-world identity is incredibly messy. Today, we're going to dive deep into why representing unique human identities in software is one of the hardest engineering problems you'll face, how databases fail when we get it wrong, and the architectural patterns you can use to build robust, collision-resistant identity systems.
The Fallacy of "Unique" Human Attributes
When we design a user table, our first instinct is to find a natural key. We look at the real world and think, "What makes a person unique?"
- Full Name? Absolutely not. There are thousands of people named John Smith, and as our friend Sean Byrne discovered, even less common names collide constantly.
- Email Address? Close, but emails can be recycled, shared by couples, or typed incorrectly, linking the wrong accounts.
- Social Security Number / National ID? Government systems make mistakes. SSNs are recycled, typed in wrong by data entry clerks, and are notoriously insecure as identifiers.
- Biometrics? Fingerprints and facial recognition are probabilistic, not deterministic. They are great for authentication, but terrible as primary keys.
When systems try to merge records from different sources—say, a banking app merging credit bureau data—they use fuzzy logic algorithms. If "Sean Byrne" of City A matches "Sean Byrne" of City B on enough vague data points, a naive algorithm merges them. Suddenly, you have a digital ghost: one person's credit history or criminal record is merged into another’s. This is precisely how "the other Sean Byrne" was born.
The Developer's Solution: Surrogate Keys and UUIDs
Because natural keys fail, we rely on surrogate keys—synthetic identifiers created solely for the database. Today, the industry standard for distributed systems is the Universally Unique Identifier (UUID).
However, not all UUIDs are created equal. Let's look at how choosing the wrong ID generation strategy can impact your database performance and your ability to track unique identities.
The Problem with UUIDv4
For years, developers have defaulted to UUIDv4. It’s entirely random, which is great for security because it’s unpredictable. Here is how you might generate one in Node.js:
const crypto = require('crypto');
const userId = crypto.randomUUID();
console.log(userId); // e.g., "3b293d13-53e2-45e0-9e9d-eb328a64cf9f"
But UUIDv4 has a massive drawback when used as a primary key in relational databases like PostgreSQL or MySQL (InnoDB). Because they are completely random, they destroy locality of reference.
When you insert rows with random UUIDs into a B-Tree index, the database engine has to write to random pages on your disk. This causes frequent page splits, high disk I/O, and rapidly degrades write performance as your database grows.
The Rise of UUIDv7
To solve this, the IETF recently standardized UUIDv7. UUIDv7 is time-ordered (lexicographically sortable) while retaining enough randomness to guarantee uniqueness across distributed systems.
A UUIDv7 consists of:
- A 48-bit Unix timestamp (millisecond precision)
- A 4-bit sub-version
- 74 bits of pseudo-random data
Because the first 48 bits are timestamp-based, newly created UUIDv7s are always greater than older ones. This means they can be appended to the end of a B-Tree index, drastically reducing page splits and boosting write performance.
Here is a conceptual implementation of how UUIDv7 is structured:
function generateUUIDv7() {
const epochMilli = Date.now();
const hexTime = epochMilli.toString(16).padStart(12, '0'); // 48 bits
// Generate random bits for the rest of the UUID
const randomHex = () => Math.floor(Math.random() * 16).toString(16);
let rest = '7'; // Version 7
for (let i = 0; i < 3; i++) rest += randomHex();
rest += '8'; // Variant 1 (typically '8', '9', 'a', or 'b')
for (let i = 0; i < 18; i++) rest += randomHex();
return `${hexTime.slice(0, 8)}-${hexTime.slice(8, 12)}-${rest.slice(0, 4)}-${rest.slice(4, 8)}-${rest.slice(8)}`;
}
console.log(generateUUIDv7()); // e.g., "018d96cb-2480-7000-8b29-3d1353e245e0"
Designing a Collision-Proof Identity Architecture
Generating unique database IDs is only half the battle. How do we prevent our application logic from merging two real-world "Sean Byrnes" into a single profile, or creating duplicate profiles for the same person?
We need an architecture that cleanly separates Authentication (who is logging in), Authorization (what they can do), and Identity Profiles (who they actually are in the real world).
1. Implement Strict Entity Resolution Rules
If your system aggregates data from multiple external APIs (like Stripe, Plaid, or third-party OAuth providers), you must design a strict, multi-stage Entity Resolution pipeline. Never trust a single matching attribute to merge two accounts.
Instead, use a probabilistic scoring system, and escalate low-confidence matches to manual human review:
interface UserProfile {
firstName: string;
lastName: string;
dob: string; // YYYY-MM-DD
postalCode: string;
}
function calculateMatchScore(userA: UserProfile, userB: UserProfile): number {
let score = 0;
if (userA.lastName.toLowerCase() === userB.lastName.toLowerCase()) score += 20;
if (userA.firstName.toLowerCase() === userB.firstName.toLowerCase()) score += 20;
if (userA.dob === userB.dob) score += 40;
if (userA.postalCode === userB.postalCode) score += 20;
return score;
}
// Usage
const score = calculateMatchScore(existingUser, incomingData);
if (score >= 90) {
// High confidence: Auto-merge records
mergeProfiles(existingUser.id, incomingData);
} else if (score >= 50) {
// Medium confidence: Flag for manual review. Prevent "Sean Byrne" collisions!
flagForManualReview(existingUser.id, incomingData);
} else {
// Low confidence: Create a brand new, distinct user record
createNewUser(incomingData);
}
2. Levering PostgreSQL's Partial Unique Indexes
Sometimes, identity attributes are only unique under certain conditions. For example, you might allow users to register with an email address, but if they delete their account, you want to free up that email while keeping the old record in your database for compliance audit logs (soft deletion).
If you use a standard UNIQUE constraint on the email column, your database will throw an error if a new user tries to register with a soft-deleted user's email.
To solve this, use a Partial Unique Index in PostgreSQL:
CREATE TABLE users (
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
email VARCHAR(255) NOT NULL,
deleted_at TIMESTAMP WITH TIME ZONE DEFAULT NULL,
created_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP
);
-- This index ensures email uniqueness ONLY for active accounts
CREATE UNIQUE INDEX active_users_unique_email
ON users (email)
WHERE deleted_at IS NULL;
With this schema, you can have ten deleted records with the email sean@example.com, but only one active record. This keeps your system clean without sacrificing historical data.
Conclusion: Treat Identity as a First-Class Engineering Problem
The story of "the other Sean Byrne" isn't just an anomaly; it's a stark warning of what happens when we design systems that treat human identity too casually. As software engineers, it is our responsibility to build safeguards into our databases and application logic.
By moving away from unpredictable natural keys, adopting time-sorted surrogate keys like UUIDv7, implementing cautious entity resolution pipelines, and utilizing advanced database indexing features like partial unique indexes, we can ensure our software accurately represents the complex humans using it.
What about you? How does your team handle identity verification and avoid user record collisions in your databases? Have you made the switch to UUIDv7 yet? Let's talk about it in the comments below!
Until next time, happy coding!