async def _attach_latest_sync_job_info(
        self, db: AsyncSession, source_connections: List[SourceConnection]
    ) -> List[SourceConnection]:
        """Attach latest sync job information to source connections.

        Args:
            db: The database session
            source_connections: List of source connections to augment

        Returns:
            The source connections with attached sync job info and status
        """
        if not source_connections:
            return []

        # Get all sync IDs
        sync_ids = [sc.sync_id for sc in source_connections if sc.sync_id]
        if not sync_ids:
            return source_connections

        # Get all sync jobs for the sync_ids to log before filtering
        all_jobs_query = select(
            SyncJob.sync_id, SyncJob.id, SyncJob.status, SyncJob.created_at
        ).where(SyncJob.sync_id.in_(sync_ids))

        all_jobs_result = await db.execute(all_jobs_query)
        all_jobs = list(all_jobs_result.fetchall())

        job_info = [
            {
                "id": str(job.id),
                "sync_id": str(job.sync_id),
                "status": job.status,
                "created_at": job.created_at,
            }
            for job in all_jobs
        ]

        # Get the latest sync job for each sync ID in a single query
        subq = (
            select(
                SyncJob.sync_id,
                SyncJob.id,
                SyncJob.status,
                SyncJob.created_at,
                SyncJob.started_at,
                SyncJob.completed_at,
                func.row_number()
                .over(partition_by=SyncJob.sync_id, order_by=SyncJob.created_at.desc())
                .label("rn"),
            )
            .where(SyncJob.sync_id.in_(sync_ids))
            .subquery()
        )

        query = select(subq).where(subq.c.rn == 1)
        result = await db.execute(query)

        # Log the query results
        latest_jobs = list(result.fetchall())

        # Create a dictionary of sync_id -> latest sync job info
        sync_job_info = {
            row.sync_id: {
                "id": row.id,
                "status": row.status,
                "started_at": row.started_at,
                "completed_at": row.completed_at,
                "created_at": row.created_at,  # Log created_at as well
            }
            for row in latest_jobs
        }

        # Attach the latest sync job info to each source connection
        for sc in source_connections:
            sc_id = str(sc.id) if hasattr(sc, "id") else "unknown"
            if sc.sync_id and sc.sync_id in sync_job_info:
                job_info = sync_job_info[sc.sync_id]
                sc.latest_sync_job_id = job_info["id"]
                sc.latest_sync_job_status = job_info["status"]
                sc.latest_sync_job_started_at = job_info["started_at"]
                sc.latest_sync_job_completed_at = job_info["completed_at"]

                # Set the ephemeral status based on the latest sync job
                job = SyncJob(
                    id=job_info["id"],
                    status=job_info["status"],
                    started_at=job_info["started_at"],
                    completed_at=job_info["completed_at"],
                    sync_id=sc.sync_id,
                )
                sc.status = await self.get_status(sc, job)
            else:
                logger.info(
                    f"Source connection {sc_id} with sync_id {sc.sync_id}: "
                    f"No matching latest job found"
                )
                # No sync job found, set default status
                sc.status = await self.get_status(sc)

        return source_connections
Attach latest sync job information to source connections.

        Args:
            db: The database session
            source_connections: List of source connections to augment

        Returns:
            The source connections with attached sync job info and status
        
backend/airweave/crud/crud_source_connection.py