JodGig Employer Sync

Goal

Migrate employer users from the legacy JodGig MySQL database into JodApp, so that:

• An existing JodGig employer logs into JodApp with the same email and password they already use.
• They land on an Org::Membership that ties them to their company.
• They can post Ads::Campaign and Careers::Job — paid products that generate revenue from clients we already have.

Mobile-number accuracy matters because transactional notifications go through WhatsApp (with SMS as backup). For example, employers currently ignore many talent applications because the dashboard is friction; a WhatsApp flow that lets them accept or reject candidates from their phone would close that gap, but only if we have a real personal mobile.

Who is an employer

A JodGig employer user is users.user_type IN ('HQ', 'AREA', 'LOCATION', 'SUPER_HQ_EXTERNAL'). Every other user_type is excluded:

• APP — talent / gig workers. Handled by the separate talent sync.
• SUPER_HQ_INTERNAL, INTERNAL, SUPER_ADMIN, USER_ADMIN, and the one row with a blank user_type — jod-internal staff or junk records.

Universe and partition

Audited on JodGig production in May 2026. The universe is 3,252 employer users. Every one lands in exactly one of seven mutually exclusive sets:

Set	Definition	Count	Action
G	live — user enabled, company status = 1	1,616	Migrate
S	SUPER_HQ_EXTERNAL (company link via user_company pivot)	72	Migrate ~66 valid
E	company disabled (companies.status = 0)	1,157	Skip — 94% have not posted a gig job in 2+ years
F	user disabled (users.status = 0), company live	348	Skip
C	obsolete company (the 11 in OBSOLETE_REMOTE_COMPANIES_IDS)	57	Skip
A	user is_deleted = 1	1	Skip
B	no company (users.company_id IS NULL, not super-HQ)	1	Skip
D	company deleted_at set	0	Empty set — JodGig does not use companies.deleted_at

Migration target ≈ 1,682 employers. About 862 are active (have logged into JodGig within the last 2 years); about 820 are dormant — re-engagement targets for sales.

Other audit numbers

• Non-bcrypt password (legacy MD5 / unknown format) — 1,363 employers (42% of the universe; ~480 of those land in the migrate target G). The cohort has not authenticated since JodGig's bcrypt cutover in June–July 2023; for practical purposes, the password format is a "dormant since 2023" flag.
• Email with uppercase characters — 214. JodApp's email unique index is case-sensitive; login lowercases the typed identifier. Emails must be lowercased on migration or those rows are unfindable.
• Office numbers in contact_number — 1,047 employers (32%) share their contact_number with at least one other employer; one office number is used by 107 sub-accounts. contact_number is not a personal mobile.
• Null/empty email, contact_number, password — 0 / 0 / 0. JodGig enforces non-empty for all three.
• Duplicate email across the employer universe — 0 case-sensitive groups, 0 case-insensitive collisions. JodGig enforces a unique index on email.
• Dual-role users detectable across APP and employer types — 0 by email, 0 by unique_id (NRIC). JodGig enforces both email and unique_id uniqueness across all users, so a person who is both a gig worker and an employer used two different emails and cannot carry unique_id on both rows. Dual-role accounts are structurally undetectable in the data.
• Companies with more than one HQ user — 0. Every company has exactly 0 or 1 HQ user.
• AREA users managing locations across more than one company — 0.
• Super-HQ users with no user_company pivot rows — 1.

CSV exports of every audit query live in employer-migration-notes/.

Selection predicate

The exact rule the sync code implements:

user_type IN ('HQ', 'AREA', 'LOCATION', 'SUPER_HQ_EXTERNAL')
AND is_deleted = 0
AND status = 1
AND (company_id IS NULL OR company_id NOT IN <OBSOLETE_REMOTE_COMPANIES_IDS>)
AND resolved Org::Company is not archived / disabled
AND for SUPER_HQ_EXTERNAL: at least 1 live user_company pivot row

OBSOLETE_REMOTE_COMPANIES_IDS lives on JodGig::Configuration so the company sync and the employer sync read it from the same place.

The explicit company_id IS NULL OR … is load-bearing. WHERE company_id NOT IN (…) alone evaluates to NULL (not TRUE) for null company_id rows under SQL three-valued logic, silently dropping the ~32 valid super-HQ users whose company link lives in the user_company pivot rather than the parent users.company_id column.

Inclusion vs state transition

The selection predicate decides inclusion — who enters JodApp for the first time. It does not decide state transition — what happens to a record that is already in JodApp when JodGig changes.

• Never-synced employer. If they fail the predicate (disabled, deleted, orphan, closed company), they are not created. JodApp never sees them.
• Already-synced employer who later fails the predicate. Propagate to Org::Membership.status = :revoked. Never silently drop them from the result set, or they keep a working JodApp login after JodGig offboarding.

The sync therefore selects records by updated_at regardless of status (the watermark drives selection), and decides skip-vs-revoke per record by checking whether a JodApp row already exists.

What the sync does, per record

For each in-scope JodGig employer the sync produces one Identities::User, one or more Org::Membership, and zero or more Org::OutletAssignment.

Identities::User

Upsert key: unique_by: :remote_gig_user_id. The stable identity link — not email, which collapses two distinct JodGig users that happen to share a JodApp email collision.

Mapped from the JodGig row:

• email — jodgig_user.email.downcase.strip.
• mobile — a unique placeholder produced by JodGig::ContactNumberService.generate_invalid_format. The JodGig contact_number is an office number for many employers, so it cannot be used directly under JodApp's UNIQUE mobile constraint. The real personal mobile is collected via a non-blocking prompt on first login.
• phone_code — jodgig_user.country_code.
• password_digest — bcrypt prefix-swapped ($2y$ → $2a$). Non-bcrypt hashes pass through unchanged; the login flow re-hashes them on the next successful authentication (see Login flow below).
• is_email_verified: true, email_verified_at: Time.current, is_phone_verified: true, phone_verified_at: Time.current. JodGig already trusted the account; the JodApp login verification gate must not block migration.
• remote_gig_user_id — the JodGig user id.
• gender, date_of_birth, gov_identity_number, identity_verified, deactivated_at, deactivation_reason — mapped from the JodGig row.
• uuid — SecureRandom.uuid on insert.

update_only excludes email, mobile, first_name, last_name, password_digest. Those are insert-only; we never overwrite a JodApp-side change to a user's identity. Other fields (verification flags + timestamps, gender, dates, gov id, deactivation) are refreshed on every run.

Org::Membership

For HQ / AREA / LOCATION — one membership per users.company_id.

For SUPER_HQ_EXTERNAL — one membership per row in the JodGig user_company pivot. The pivot is read via a sanitised raw SQL query filtered to deleted_at IS NULL. Pivot rows pointing to obsolete or disabled companies are skipped.

Upsert key: unique_by: [:user_id, :company_id]. update_only includes role, status, title, is_owner, is_default.

• Role mapping — HQ and SUPER_HQ_EXTERNAL → hq_manager; AREA → area_manager; LOCATION → location_manager.
• Status mapping — is_deleted = 1 → revoked; users.status = 0 → revoked (the state-transition propagation path); suspended_at set → suspended; otherwise → active.
• One owner per company. The single HQ user wins. For super-HQ-only companies, the user matching companies.created_by wins; else the earliest-created super-HQ membership. All other candidates for the same company are is_owner = false.
• is_default — true for one membership per user (the only one for non-super-HQ; for super-HQ, the one matching users.company_id if present, else the earliest by companies.created_at).
• remote_gig_user_id — set on every membership. For a super-HQ user with N pivot rows, all N memberships carry the same value — the link back to the source JodGig user.

JodGig notification settings (user_setting_email_notification_status, user_setting_sms_notification_status, user_setting_whatsapp_notification_status) are not migrated. JodApp owns notification preferences as its own concern; JodGig's notification data is unreliable.

Org::OutletAssignment

For LOCATION users — one assignment, resolved by users.location_id against Org::Outlet.remote_id.

For AREA users — one assignment per outlet whose org_outlets.area_user_id equals the JodGig user id. The location sync writes the JodGig user id directly into org_outlets.area_user_id (the column is conceptually a remote_area_user_id but the name predates this design).

For HQ and SUPER_HQ_EXTERNAL — no outlet assignments. Company-level access only.

Upsert key: unique_by: [:membership_id, :outlet_id]. The upsert is skipped when the assignment array is empty — a batch of only HQ users produces no outlet rows, and calling upsert_all([]) raises.

Assignments are removed when they disappear from JodGig. An AREA user moved off an outlet in JodGig has the old assignment deleted on the next re-sync, so JodApp converges to the JodGig truth.

The sync as a whole

JodGig::Users::EmployersSyncService is a Sidekiq job (queue: :low, retry: 0, dead: true) scheduled hourly. The sync:

1. Reads the last successful Gig::SyncLog for this origin/destination pair.
2. Computes the incremental watermark — the lower bound on users.updated_at — through JodGig::TimezoneService, which translates the JodApp UTC timestamp into the naive SGT format the JodGig MySQL column stores.
3. Iterates find_in_batches(batch_size: 1000) over the in-scope users.
4. For each record, runs its own begin / rescue. One bad record fails one record, not the batch.
5. Inside the per-record block: builds the Identities::User upsert hash; runs the single-row upsert (via a shared service that both the talent and employer paths call); builds and upserts the Org::Membership(s); builds and upserts the Org::OutletAssignment(s). Errors are appended to fail_log and sent to Sentry with the JodGig user id as context.
6. Writes a Gig::SyncLog row at the end with origin_count, destination_count, fail_log, and is_successful = fail_log.blank?.

Cron order

Daily anchor (UTC):

Time	Job
15:00	JodGig::Companies::SyncJob
15:10	JodGig::Locations::SyncJob
15:20, then hourly at :20 past	JodGig::Users::EmployersSyncJob
16:00	JodGig::Users::SyncJob (talent)

Employers depend on companies and locations being in JodApp first (a membership needs an Org::Company; an outlet assignment needs an Org::Outlet). The cadence is hourly because new employers keep being created in JodGig until the sunset finishes, and an hour of "I cannot log in" is the maximum delay we want to expose to sales-driven first-touch traffic. The login-time JIT path (below) handles the in-the-moment case.

Login flow

Login is handled by Identities::Sessions::CreateManager. The flow:

1. Find an Identities::User by the lowercased identifier (email or normalised phone).
2. If not found, try the talent JIT (JodGig::Users::JitMigrationService) — looks up JodGig user_type = 'APP' users.
3. If still not found, try the employer JIT — detect a real JodGig employer matching the identifier with a valid password, enqueue a single-user employer sync job, and raise Errors::MigrationInProgress (HTTP 409). The frontend renders "Setting up your account — please try again in a moment", not the generic credential error. The migration itself runs in the background job, never the request thread, because it depends on companies and outlets already being resolvable.
4. If a user was found, run identities_user.authenticate(password).
5. If authenticate returns false and the stored password_digest is not a bcrypt hash ($2a$ / $2b$ / $2y$), call JodGig::PasswordService.valid? — it understands the legacy formats. If it matches, save the password (which has_secure_password rewrites as bcrypt) and continue as if authenticate had returned true. This is how dormant employers with legacy hashes recover on their first successful login.
6. Check the verification gate (is_email_verified || is_phone_verified). Migrated employers always pass — both flags are forced true at migration time.
7. Issue a JWT session and stamp last_login.

Schema reference

JodGig (legacy, MySQL)

• users — id, user_type, company_id, location_id, status, is_deleted, suspended_at, email (UNIQUE), contact_number, password, unique_id (UNIQUE), first_name, last_name, phone_verified_at, email_verified_at, country_code, last_login_at, timestamps. PII columns (email, contact_number, password, unique_id, names) are dropped from the local schema-only copy at jodgig_2026_clean and live only in production / Cloudbeaver.
• companies — id, name, status (smallint; 1 = enabled, 0 = disabled), deleted_at (timestamp, never set in production), created_by (the JodGig user id who created the company; used by the one-owner-per-company tie-break), timestamps.
• locations — id, company_id, area_user_id (the JodGig user id of the AREA manager), status, deleted_at, timestamps.
• user_company — pivot for SUPER_HQ_EXTERNAL; columns user_id, company_id, deleted_at.

JodApp (PostgreSQL)

• identities_users — unique indexes on email, mobile, remote_gig_user_id, uuid. email and mobile are case-sensitive at the DB level; is_email_verified and is_phone_verified are booleans; password_digest uses has_secure_password (bcrypt).
• org_companies — remote_id (unique), status enum (active / disabled / deleted), created_by (polymorphic; points at JodGig::User for migrated companies). The company sync upserts on slug (not remote_id).
• org_outlets — remote_id (non-unique — the location sync upserts on (slug, company_id)), area_user_id (stores the JodGig user id of the AREA manager, written by the location sync).
• org_memberships — needs a (user_id, company_id) unique index (the membership upsert key) and a nullable remote_gig_user_id audit column for the redo. Retains a plain (non-unique) index on company_id for company-scoped lookups.
• org_outlet_assignments — needs a (membership_id, outlet_id) unique index for the redo.

Open work

The redo is broken into one epic plus 18 sub-issues in employer-migration-notes/. The Epic Overview has the phase map, the per-record decision flow, and the login flow with the new JIT path. Sub-issues are grouped into four phases:

• Phase A (parallel, no dependencies) — 1.2, 2.1, 5.4, 6.1, 6.2, 6.4.
• Phase B — 2.2 → 3.1 (schema then skeleton).
• Phase C — 4.1–4.5, 5.3, 6.3, 6.5 (wished functions plus frontend).
• Phase D — 5.1 → 5.2 (shared upsert service, then JIT detect-and-enqueue).

PR #1634 in jod-app/jodapp-api is the previous attempt at this sync; it is closed once the sub-issues here land. Its migrations and a few helper services are reused; the central upsert logic, the error-handling shape, the login-flow additions, and the frontend changes are rebuilt.