Skip to main content

[Employer Sync 4.1] Implement select_employers_in_scope

TL;DR: Implement the locked selection predicate, fix the WHERE NOT IN three-valued-logic bug that drops company_id IS NULL rows, and move OBSOLETE_REMOTE_COMPANIES_IDS to JodGig::Configuration so the company sync and this sync read the same list.

Context​

The sync needs to pull the right set of jodgig employer users — and only that set — on each run. The exact predicate is locked in issue 1.1.

Problem​

PR #1634's scope has a real bug. Its filter ends with:

.where.not(company_id: JodGig::Companies::SyncService::OBSOLETE_REMOTE_COMPANIES_IDS)

That generates WHERE company_id NOT IN (...). Under SQL three-valued logic, NULL NOT IN (...) is NULL, not TRUE, so every row where company_id IS NULL is dropped. Super-HQ users legitimately have company_id = NULL — they link to companies through the user_company pivot. ~32 valid super-HQ users disappear from the scope, silently.

PR #1634 also reads the obsolete-company list from JodGig::Companies::SyncService — two syncs should not depend on each other's constants.

Direction​

Implement select_employers_in_scope on EmployersSyncService per the predicate in 1.1:

user_types = [
  JodGig::User.user_types[:SUPER_HQ_EXTERNAL],
  JodGig::User.user_types[:HQ],
  JodGig::User.user_types[:AREA],
  JodGig::User.user_types[:LOCATION]
]

JodGig::User
  .where(user_type: user_types, is_deleted: false, status: 1)
  .where(
    'company_id IS NULL OR company_id NOT IN (?)',
    JodGig::Configuration::OBSOLETE_REMOTE_COMPANIES_IDS
  )
  .where(/* incremental watermark via JodGig::TimezoneService */)

Move OBSOLETE_REMOTE_COMPANIES_IDS from JodGig::Companies::SyncService to JodGig::Configuration. Both the company sync and the employer sync read it from there.

Add a post-selection filter for the company state — drop any employer whose resolved Org::Company is archived / disabled. For SUPER_HQ_EXTERNAL, drop any user whose every user_company pivot row points to an obsolete or missing company. This is the "skip-on-create" half of the data-cleaning decision; the "state-transition" half lives in 4.3.

The watermark uses JodGig::TimezoneService.format_to_naive_sgt_string(datetime: last_sync_log.started_at). Do not use + 8.hours. Coordinate with branch 1642.

Acceptance​

  • OBSOLETE_REMOTE_COMPANIES_IDS lives on JodGig::Configuration; the company sync reads it from there.
  • Tests cover: null company_id (kept for super-HQ, dropped for others), obsolete company_id (dropped), employer at a status = 0 company (dropped), enabled super-HQ with valid pivot (kept), enabled super-HQ with only obsolete pivot rows (dropped).
  • On the QA snapshot, the scope size matches the audit target (~1,682, within drift).
  • No + 8.hours literal anywhere in the file.

Depends on​

  • 3.1 (the skeleton this method plugs into)