Skip to main content

[Employer Sync 2.1] Schema changes — uniqueness for upsert keys

TL;DR: Three index changes so the sync's upsert_all calls have the unique indexes they require, and the JodGig→JodApp outlet lookup is unique. Each is preceded by a duplicate-row pre-flight against production.

Context​

The employer sync writes Org::Membership and Org::OutletAssignment rows. Both use upsert_all(unique_by: ...), which only works if a real unique index exists on the chosen columns. The sync also reads org_outlets.remote_id to map a JodGig outlet id to a JodApp outlet — that read assumes one outlet per remote id.

The current production schema (verified against db/structure.sql) does not have any of those three indexes. The sync cannot run until they exist.

Problem​

Three concrete gaps in the existing schema:

  • org_memberships has only single-column indexes on user_id and company_id. No unique composite. upsert_all(unique_by: [:user_id, :company_id]) raises at runtime.
  • org_outlet_assignments has only single-column indexes on membership_id and outlet_id. No unique composite. upsert_all(unique_by: [:membership_id, :outlet_id]) raises at runtime.
  • index_org_outlets_on_remote_id exists but is not unique. A WHERE remote_id = ? lookup can silently return more than one row, so 4.4's outlet-mapping is unsound until this is fixed.

Direction​

Three additive migrations. Each migration is preceded by a pre-flight check in this issue's comments — duplicates today mean the migration cannot land cleanly, and the data is cleaned up first.

  1. org_memberships — add UNIQUE INDEX (user_id, company_id). Pre-flight (paste result in this issue):

    SELECT user_id, company_id, count(*)
    FROM org_memberships
    GROUP BY user_id, company_id
    HAVING count(*) > 1;

    If non-empty, decide policy per duplicate pair (keep oldest, merge, archive) before the migration.

  2. org_outlet_assignments — add UNIQUE INDEX (membership_id, outlet_id). Pre-flight:

    SELECT membership_id, outlet_id, count(*)
    FROM org_outlet_assignments
    GROUP BY membership_id, outlet_id
    HAVING count(*) > 1;

    Same policy decision rule applies.

  3. org_outlets — replace index_org_outlets_on_remote_id with a UNIQUE version. Pre-flight:

    SELECT remote_id, count(*)
    FROM org_outlets
    WHERE remote_id IS NOT NULL
    GROUP BY remote_id
    HAVING count(*) > 1;

    remote_id IS NULL is allowed (JodApp-native outlets have no source row). Drop the non-unique index and add a unique one in the same migration.

Regenerate db/structure.sql cleanly against main.

Out of scope​

  • Notification-preference columns. PR #1634 adds allow_email_notification, allow_whatsapp_notification, allow_sms_notification. These are not added. JodApp will own notification preferences separately. The corresponding mapping is removed from 4.3, and the F1 swap bug disappears with the columns.
  • org_memberships.remote_gig_user_id. Not added. identities_users.remote_gig_user_id is the single source of truth (verified UNIQUE in production). Queries like "memberships for jodgig user X" join through identities_users.
  • Boolean conversion of is_default and is_owner. These are currently varchar storing 't'/'f'. The conversion is its own sub-issue (2.2) because it requires app-code coordination across multiple deploys, not a single migration.

Acceptance​

  • Pre-flight result for each of the three indexes attached to this issue.
  • Three migrations land cleanly on QA, then production.
  • db/structure.sql regenerated; no unrelated diffs.
  • No notification-preference columns added.
  • No remote_gig_user_id column added to org_memberships.