`GET /api/pivot_map` pagination drops/duplicates rows without `ORDER BY`; nightly job-retention cron is also guaranteed to trigger.

[Aditya30ag]

Summary

PivotMap has no Meta.ordering, and PivotMapViewSet (which backs the paginated GET /api/pivot_map endpoint) queries it with a bare PivotMap.objects.all() no .order_by(...) anywhere in the chain. Confirmed directly in a Django shell:

>>> PivotMap.objects.all().ordered
False

Because the API's global pagination (PAGE_SIZE = 10) pages with LIMIT/OFFSET, and Postgres gives no row-order guarantee for a query without an explicit ORDER BY, a client paging through results (?page=1, ?page=2, ...) can see the same row twice, or more importantly never see a row at all, if any row is
deleted between page fetches.

This isn't a hypothetical edge case: PivotMap.starting_job and PivotMap.ending_job are both on_delete=CASCADE, and the project runs a daily cron job (intel_owl.tasks.remove_old_jobs, crontab(minute="10", hour="2")) that deletes Job rows older than OLD_JOBS_RETENTION_DAYS. Any such deletion cascades to PivotMap rows, which is exactly the condition that breaks unordered OFFSET
pagination.

Where

api_app/pivots_manager/models.py no ordering in PivotMap.Meta:

class PivotMap(models.Model):
    starting_job = models.ForeignKey(Job, on_delete=models.CASCADE, ...)
    pivot_config = models.ForeignKey("PivotConfig", ...)
    ending_job = models.OneToOneField(Job, on_delete=models.CASCADE, ...)

    class Meta:
        unique_together = [
            ("starting_job", "pivot_config", "ending_job"),
        ]
        # no `ordering` — this is the gap

api_app/pivots_manager/views.py the viewset doesn't add ordering either:

class PivotMapViewSet(viewsets.ReadOnlyModelViewSet):
    permission_classes = [IsAuthenticated, PivotOwnerPermission]
    serializer_class = PivotMapSerializer
    lookup_field = "pk"
    queryset = PivotMap.objects.all()   # <-- no .order_by()

api_app/pivots_manager/urls.py exposed at the paginated list endpoint:

router.register(r"pivot_map", PivotMapViewSet, basename="pivot_map")

intel_owl/settings/rest.py pagination is on globally, so this affects any user
with more than 10 pivot maps:

"DEFAULT_PAGINATION_CLASS": "certego_saas.ext.pagination.CustomPageNumberPagination",
"PAGE_SIZE": 10,

intel_owl/tasks.py / intel_owl/celery.py the daily cron that guarantees
concurrent deletes will happen against this table:

@shared_task(base=FailureLoggedTask, soft_time_limit=10000)
def remove_old_jobs():
    ...
    retention_days = int(secrets.get_secret("OLD_JOBS_RETENTION_DAYS", 14))
    date_to_check = now() - datetime.timedelta(days=retention_days)
    old_jobs = Job.objects.filter(finished_analysis_time__lt=date_to_check)
    for old_job in old_jobs.iterator():
        ...  # deletion cascades to PivotMap via on_delete=CASCADE

# intel_owl/celery.py
"remove_old_jobs": {
    "task": "intel_owl.tasks.remove_old_jobs",
    "schedule": crontab(minute="10", hour="2"),
    ...
},

Impact

• Real correctness/data-loss bug, not a theoretical warning: clients paging through
  /api/pivot_map (e.g. the frontend's pivot history view) can permanently miss
  entries or see duplicates, and the project's own nightly retention cron
  (remove_old_jobs, crontab(minute="10", hour="2")) is a standing, guaranteed
  trigger for the delete-during-pagination case.
• Only surfaces once results exceed PAGE_SIZE = 10, so it's easy to miss in casual
  manual testing with a handful of test pivots likely why it hasn't been caught.

[mlodic]

I don't accept issues generated by AI without any POC with a local installation