The Dual-ID Pattern: Stop Exposing Integer Primary Keys in Your API

Most APIs start with a simple convention: the database assigns an integer ID to each record, and the API returns it. A newly created user gets id: 1, the next gets id: 2, and so on.

That's convenient. It's also leaking information most teams don't mean to share, and it's the kind of default worth flagging on every backend built this way.

The Enumeration Attack

When an API exposes sequential integer IDs, anyone can enumerate the data. If GET /api/users/1 returns a user, a script can loop from 1 to 10,000 and scrape every user record the API returns, even without any authentication bypass.

Beyond scraping, sequential IDs reveal business metrics. A competitor can track order volume by placing two orders a week apart and comparing IDs. A new user can tell exactly how many users signed up before them.

Integer IDs are optimized for the database, not for API clients. Fast joins, compact indexes, auto-increment: those are internal concerns. There's no reason a client needs to know the shape of a primary key.

The Fix: Two IDs, One Model

The dual-ID pattern applies to every model that gets exposed via API. It gives each model two identifiers:

• id: integer primary key, used internally for joins and foreign keys
• public_id: UUID, used in all external API surfaces

from sqlalchemy.orm import Mapped, mapped_column
from sqlalchemy import String
import uuid
 
class PublicIdMixin:
    public_id: Mapped[str] = mapped_column(
        String(36),
        unique=True,
        index=True,
        default=lambda: str(uuid.uuid4())
    )

Add this mixin to any model that gets exposed via API:

class User(Base, TimestampMixin, PublicIdMixin):
    __tablename__ = "users"
 
    id: Mapped[int] = mapped_column(primary_key=True, autoincrement=True)
    email: Mapped[str] = mapped_column(String(255), unique=True, nullable=False)
    name: Mapped[str] = mapped_column(String(100), nullable=False)

Internal vs External Query Patterns

The split carries through to how records get queried:

# Internal: use integer id for performance-critical operations
# (joins, foreign key lookups, bulk operations)
user = await session.get(User, user_id)
 
# External: use public_id for anything coming from the API
user = (
    await session.execute(select(User).where(User.public_id == public_id))
).scalar_one_or_none()

Foreign keys between tables still use integer IDs, and that's the whole point. The database's relational performance stays intact. The UUID only surfaces at the API boundary.

The response schema never includes id:

class UserResponse(BaseModel):
    public_id: str      # ← exposed
    email: str
    name: str
    created_at: datetime
 
    # id is intentionally absent

What You Get

An API that returns public_id: "a1b2c3d4-e5f6-..." instead of id: 42:

• Reveals nothing about record count or sequence
• Can't be enumerated without a valid UUID to start from
• Allows the internal schema to change (e.g. switching to a different PK strategy) without breaking API clients
• Makes it obvious at a glance which identifier is safe to share externally

The mixin is two fields and five lines of code. It's one of the lowest-effort security improvements a backend can start with, and one of the harder ones to retrofit once an API is already in use. Worth applying by default on every project, not just the ones where a client asks for it.