I’m using MongoDB with a FastAPI application to manage an unverified_users_collection. I have set a TTL (Time-To-Live) index of one day on the createdAt field to automatically remove documents after 24 hours. Here’s a snippet of the TTL index creation:

async def create_ttl_index(collection):
    indexes = await collection.index_information()
    
    if 'createdAt_1' not in indexes:
         await collection.create_index([("createdAt", 1)], expireAfterSeconds=86_400)

When I insert a document into the collection, I return the MongoDB _id of the newly inserted document. The insertion and retrieval of the _id work perfectly, but when I check MongoDB Atlas for the new document, it is inconsistently visible. Sometimes the document is there, and other times it is not.

Additionally, my timezone is UTC + 1, while the createdAt field in MongoDB shows UTC. Here’s an example of how I insert documents:

async def init_registeration(user_dict, role: str):
    try:
        if role.title() not in [r.value for r in user_enum.RoleEnum]:
            raise RaiseException(status_code=401, message="Role not recognized!")
        
        existing_user = await user_data_logic.find_user(user_dict.get('email'))
        if existing_user:
            raise RaiseException(status_code=409, message="Account with such email already exists!")
        
        existing_unverified_user = await user_data_logic.find_unverified_user(user_dict.get('email'))
        if existing_unverified_user: 
            await user_data_logic.delete_unverified_user(existing_unverified_user.get('_id'))
        
        otp = generate_otp()
        
        # creating an instance of the expected document
        unverified_user_object = user_model.UnverifiedUserAccount(
            first_name=user_dict['first_name'],
            last_name=user_dict['last_name'],
            email=user_dict['email'],
            role=role.title(),
            password=hash(user_dict['password']),
            otp=otp,
            createdAt=get_current_datetime()
        )

        new_unverified_user = await user_data_logic.save_unverified_user(unverified_user_object.dict())
        if not new_unverified_user.inserted_id:
            raise RaiseException(status_code=400, message="User profile not created!")
        
        # Send OTP Notification 
        subject="Verify Your Email Address"
        body=f"Your OTP for account verification is {otp}"
        send_email(subject, body, [user_dict['email']])

        return response(status_code=201, message=f"An OTP has has been sent to {user_dict['email']}")
    except RaiseException as e:
        return response(status_code=e.status_code, message=e.message)
    except Exception as e:
        return response(status_code=500, message=str(e))

The get_current_datetime() function:

def get_current_datetime(offset: timedelta=timedelta(days=0)):
    nigeria_timezone = pytz.timezone("Africa/Lagos")
    current_datetime = datetime.now(nigeria_timezone)
    current_datetime = current_datetime + offset
    return current_datetime

It’s important to note that the TTL functionality works as expected, meaning documents do get deleted after the defined time. However, the problem lies with the inconsistent insertion of documents.

My concerns are:

1. Why are the documents inconsistently visible in MongoDB Atlas?
2. Could the timezone difference be affecting the TTL index or document visibility?
3. Is there a potential issue with the TTL index or the way I’m handling time zones?

Any insights or suggestions to ensure consistent document visibility would be greatly appreciated.

What I tried:

1. Created the TTL index with expireAfterSeconds set to 86400 seconds.
2. Inserted documents using the current time for the createdAt field.
3. Verified the _id of the inserted documents is returned successfully after insertion.
4. Checked MongoDB Atlas for the presence of these documents immediately after insertion.

What I expected:

1. Documents to be consistently visible in MongoDB Atlas immediately after insertion.
2. Documents to be deleted automatically after 24 hours as per the TTL index.

What actually happened:

1. The TTL functionality works correctly, and documents are deleted after the specified time.
2. However, the visibility of newly inserted documents in MongoDB Atlas is inconsistent. Sometimes the documents are visible immediately after insertion, and other times they are not.