Two IDs, One Row: Keeping Public UUIDs and Internal Keys From Leaking Into Each Other

TL;DR

• I use two identifiers per row: a UUID as the public ID (URLs, APIs, forms) and an auto-increment integer as the internal key (foreign keys, joins).
• The bug class: those two leak into each other — a foreign key stores a UUID, a <select> posts a UUID where a key is expected, an action writes a raw key it never resolved.
• The fix is a discipline: resolve UUID → key at the write boundary, and reject unknown IDs instead of inventing rows or writing nulls.

Why two IDs at all

A quick analogy. Your internal key is your staff number — compact, sequential, great for the payroll system to join on. Your UUID is your business card — safe to hand out, reveals nothing, and doesn't let anyone guess the next person's number.

Concern	Public UUID	Internal key (auto-inc)
Where it appears	URLs, API payloads, form fields	Foreign keys, joins, indexes
Guessable	No	Yes (sequential)
Enumeration risk	None	High if exposed
Join performance	Poor (128-bit, random)	Excellent (narrow int)

Adopt this pattern and you adopt a contract: the boundary between "outside" and "inside" is where the translation happens. Break it and you get subtle, ugly bugs.

The four ways it leaks

Closed every one of these today. They rhyme.

1. A foreign key holding a UUID. The FK should point at the internal key. Store the UUID and joins either silently miss or need a second lookup. Fix: a migration that retypes the column, backfills the real key, and drops cross-boundary FK constraints that never should have existed.

2. A <select> emitting UUIDs. The dropdown is populated from public IDs (correct — you don't render internal keys), but the submitted value must be the key the column stores. UUID option value + key column = a wrong insert before validation even runs.

3. An action writing an unresolved ID. The important one. An invokable action takes a public UUID and writes it straight into a key column, no resolution step. It compiles, passes the happy path, and quietly corrupts referential integrity.

4. Mixed intent on a column that should stay a UUID. Not every *_uuid column is a mistake — a users.member_uuid link is meant to be a UUID. Fix there is the opposite: stop comparing it against a bare key, resolve through it, keep the rule typed uuid.

  outside (public)                  boundary                 inside
  ┌──────────────┐         ┌────────────────────┐       ┌───────────┐
  │ UUID in URL, │  --->   │ resolve UUID -> key │ --->  │ FK = key  │
  │ API, <select>│         │  or REJECT if none  │       │ joins ok  │
  └──────────────┘         └────────────────────┘       └───────────┘

Resolve at the write boundary, and reject

The rule that kills the whole bug class: an action never trusts an incoming public ID as a key. It resolves it, and if resolution fails, it rejects — it does not write a null and it does not invent a row.

final class RecordVerificationAction
{
    public function __invoke(string $memberUuid, string $token): Verification
    {
        // Resolve at the boundary. Unknown public ID is a hard stop.
        $memberKey = Member::query()
            ->where('uuid', $memberUuid)
            ->value('id');

        if ($memberKey === null) {
            // Record the event WITHOUT inventing a member.
            return Verification::createUnattributed($token);
            // ... no null FK, no phantom row
        }

        return Verification::create([
            'member_id' => $memberKey, // key, never the uuid
            'token'     => $token,
        ]);
    }
}

Resolution happens once, inside the action that writes. And an unknown token is recorded as unattributed rather than forcing a fake member into existence — the audit trail stays honest instead of polluted.

Make the boundary testable

These failures are invisible in the happy path, so assert on the boundary itself: an unknown UUID never writes a null or a phantom row.

it('records an unknown token without inventing a member', function () {
    $verification = (new RecordVerificationAction())('non-existent-uuid', 'tok_123');

    expect($verification->member_id)->toBeNull()
        ->and(Member::count())->toBe(0); // nothing invented
});

Takeaway

The two-ID pattern is worth it — clean public surface, fast internal joins — but only if the translation is disciplined. Keep the rule small and everywhere: resolve UUID → key at the write boundary, reject unknown IDs, and never let a *_uuid value land in a key column (or the reverse). These bugs pass every happy-path test; only boundary assertions catch them.

Comments

[Mads Hansen]

The translation boundary is a useful discipline, but I’d add two invariants. First, a UUID is an identifier, not authorization: resolve it inside the caller’s tenant/ownership scope and return the same not-found result for unknown and unauthorized values. “Enumeration risk: none” is too strong—UUIDs reduce guessing, but leaked IDs can still become IDOR handles. Second, encode the distinction in types and constraints, not naming alone: unique public_uuid, typed request DTOs/value objects, integer FKs with real constraints, and repository methods that accept only the public-ID type at external boundaries. One nuance in the example: createUnattributed() is not rejecting the event; it rejects the association while preserving evidence. Calling that out explicitly would keep the audit-trail exception from becoming a general fallback that masks bad IDs.