[Django] #36319: facing issue where when i change a model field from PENDING to VERIFIED for some reason on next set of code some VERIFIED are changing back to PENDING

[Django]

#36319: facing issue where when i change a model field from PENDING to VERIFIED
for some reason on next set of code some VERIFIED are changing back to
PENDING
-------------------------------------+-------------------------------------
Reporter: irfan-hyperion | Type: Bug
Status: new | Component: Database
| layer (models, ORM)
Version: 3.2 | Severity: Normal
Keywords: orm, django, sql, | Triage Stage:
database | Unreviewed
Has patch: 0 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------
# Issue: Unstructured Facts Reverting from VERIFIED to PENDING Status
Unexpectedly

## Problem Description
In our Django application, we're experiencing an issue where
`UnstructuredFact` objects that are set to `STATUS_VERIFIED` are
unexpectedly reverting back to `STATUS_PENDING`. This occurs in the
`SubmitOverviewPageApiView` class where we handle the verification of
meeting notes and their associated facts.

## Environment
- Django version: 3.2.25
- Database: PostgreSQL
- Python version: 3.11.0

## Code Context
The issue occurs in the `SubmitOverviewPageApiView.post()` method where
we:
1. Update all pending facts to verified status
2. Resequence facts
3. Update meeting review flow progress

## Current Implementation
```python
with transaction.atomic():
# Update all visible facts to VERIFIED status
pending_facts = list(UnstructuredFact.objects.filter(
meeting_note=meeting_note,
status=UnstructuredFact.STATUS_PENDING,
deleted=False,
).select_for_update())

# Update status in memory
for fact in pending_facts:
fact.status = UnstructuredFact.STATUS_VERIFIED

# Bulk save all updated facts
if pending_facts:
UnstructuredFact.objects.bulk_update(pending_facts, ['status'])
```

## Expected Behavior
1. When facts are marked as VERIFIED, they should remain in that state
2. The status change should be atomic and consistent
3. No facts should revert back to PENDING status without explicit updates

## Actual Behavior
1. Facts that are set to VERIFIED status sometimes revert back to PENDING
2. This happens without any explicit update queries being triggered
3. The issue appears to be intermittent and not consistently reproducible

## Attempted Solutions
1. Added transaction.atomic() to ensure atomicity
2. Used select_for_update() to prevent race conditions
3. Added detailed logging to track status changes
4. Verified the update was successful by checking the count of verified
facts after the update

## Questions
1. Could this be related to Django's transaction management or database
isolation levels?
2. Are there any known issues with bulk_update() and status fields?
3. Could this be caused by concurrent requests or race conditions despite
our locking?
4. Are there any best practices for handling status transitions in Django
that we might be missing?

## Additional Context
- The application uses multiple databases (default and legacy)
- We're using Django's ORM for all database operations
- The issue occurs in a production environment with concurrent users
- We've verified that no other parts of the code are explicitly setting
the status back to PENDING

## Relevant Models
```python
class UnstructuredFact(models.Model):
STATUS_PENDING = 'PENDING'
STATUS_VERIFIED = 'VERIFIED'
STATUS_DELETED = 'DELETED'

status = models.CharField(
max_length=20,
choices=[
(STATUS_PENDING, 'Pending'),
(STATUS_VERIFIED, 'Verified'),
(STATUS_DELETED, 'Deleted'),
],
default=STATUS_PENDING
)
# ... other fields
```

## Logging Output
We've added detailed logging that shows:
1. The number of facts being updated
2. The status before and after updates
3. Verification of the update success

## Request for Help
We would appreciate any insights into:
1. Potential causes for this behavior
2. Best practices for handling status transitions in Django
3. Debugging strategies to identify the root cause
4. Any known issues or edge cases with bulk updates and status fields

Thank you for your time and assistance!
--
Ticket URL: <https://code.djangoproject.com/ticket/36319>
Django <https://code.djangoproject.com/>
The Web framework for perfectionists with deadlines.

[Django]

#36319: facing issue where when i change a model field from PENDING to VERIFIED
for some reason on next set of code some VERIFIED are changing back to
PENDING
-------------------------------------+-------------------------------------

Reporter: irfan-hyperion | Owner: (none)
Type: Bug | Status: closed
Component: Database layer | Version: 3.2
(models, ORM) |
Severity: Normal | Resolution: invalid

Keywords: orm, django, sql, | Triage Stage:
database | Unreviewed
Has patch: 0 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------

Changes (by Sarah Boyce):

* resolution: => invalid
* status: new => closed

Comment:

Hello,

This report seems better suited to be a support request. The best place to
get answers to your issue is using any of the user support channels from
[https://docs.djangoproject.com/en/dev/faq/help/#how-do-i-do-x-why-
doesn-t-y-work-where-can-i-go-to-get-help this link].

Since the goal of this issue tracker is to track issues about Django
itself, and your issue seems, at first, to be located in your custom code,
I'll be closing this ticket as invalid following the
[https://docs.djangoproject.com/en/dev/internals/contributing/triaging-
tickets/#closing-tickets ticket triaging process]. If, after debugging,
you find out that this is indeed a bug in Django, please re-open with the
specific details and please be sure to include a small Django project to
reproduce or a failing test case.

Thank you!
--
Ticket URL: <https://code.djangoproject.com/ticket/36319#comment:1>