Empty string in external_user_id causes uniqueness constraint violation

[Ahmath-Gadji]

Description

Origin of the Error

When creating 2 users without supplying the external_user_id (so it's supposed to be None), it leads to error. Here is the definition of the field external_user_id.

openrag/openrag/components/indexer/vectordb/utils.py

Line 102 in 4716004

external_user_id = Column(String, unique=True, nullable=True, index=True)

Although PostgreSQL allows multiple NULL values in a UNIQUE column, new users are being created with an empty string ('') instead of None.
Since PostgreSQL treats '' as a valid (non-null) value, the UNIQUE constraint is enforced, leading to:

psycopg2.errors.UniqueViolation: duplicate key value violates unique constraint "ix_users_external_user_id"
DETAIL: Key (external_user_id)=() already exists.

Cause:
FastAPI’s Swagger UI sends an empty string ('') when the field is left blank.
Only when “Send empty value” is unchecked does it send None, allowing user creation.

Reference:
[openrag/routers/users.py#L28-L43](

openrag/openrag/routers/users.py

Lines 28 to 43 in 4716004

	@router.post("/")
	async def create_user(
	display_name: Optional[str] = Form(None),
	external_user_id: Optional[str] = Form(None),
	is_admin: bool = Form(False),
	vectordb=Depends(get_vectordb),
	admin_user=Depends(require_admin),
	):
	"""
	Create a new user and generate a token.
	"""
	user = await vectordb.create_user.remote(
	display_name=display_name,
	external_user_id=external_user_id,
	is_admin=is_admin,
	)

)

---

Solution

Simple solution

Ensure that empty strings are converted to None before database insertion:

@router.post("/")
async def create_user(
    display_name: Optional[str] = Form(None),
    external_user_id: Optional[str] = Form(None),
    is_admin: bool = Form(False),
    vectordb=Depends(get_vectordb),
    admin_user=Depends(require_admin),
):
    """
    Create a new user and generate a token.
    """
    user = await vectordb.create_user.remote(
        display_name=display_name,
        external_user_id=external_user_id or None,
        is_admin=is_admin,
    )
    logger.info("Created new user", user_id=user["id"])
    return JSONResponse(status_code=status.HTTP_201_CREATED, content=user)

Note

This issue is related to this commit when we replaced Query to Form: dcfd643