Migration 20260615120000 can abort the upgrade or silently drop static-segment memberships

[hanamizuki]

Summary

The normalize_contact_emails migration in v0.12.0 has two bugs in the step that reassigns segment memberships from merged-away duplicates to the survivor. Both only fire when a project has case/whitespace-variant duplicate contacts and uses static segments, but in that case one of them blocks the upgrade and the other loses data silently.

• Bug 1 — the migration aborts with a primary-key violation, so the container cannot start and the deploy is blocked.
• Bug 2 — the migration commits successfully but drops the merged contact out of a static segment, so it silently stops receiving that segment's campaigns.

Both reproduced locally on PostgreSQL 16.14. A single change fixes both; tested SQL below.

File: packages/db/prisma/migrations/20260615120000_normalize_contact_emails/migration.sql

Bug 1 — deploy-blocking primary-key violation

The dedupe DELETE only removes a duplicate's membership when the survivor already holds one for that segment:

DELETE FROM segment_memberships dsm
USING _contact_merge_map m
WHERE dsm."contactId" = m.dup_id
  AND EXISTS (
    SELECT 1 FROM segment_memberships ssm
    WHERE ssm."contactId" = m.survivor_id
      AND ssm."segmentId" = dsm."segmentId"
  );

When a merge group has three or more contacts and two duplicates share a segment the survivor is not in, neither row is deleted. The following UPDATE then rewrites both to the same ("contactId", "segmentId") and hits the composite primary key:

ERROR:  duplicate key value violates unique constraint "segment_memberships_pkey"
DETAIL:  Key ("contactId", "segmentId")=(surv, segB) already exists.

Prisma wraps each migration in a transaction, so this rolls back cleanly — no partial state — but prisma migrate deploy fails, and the Docker entrypoint exits 1 on migration failure, so the application will not start until the data is fixed by hand.

Bug 2 — silent loss of an active membership

The same DELETE keeps the survivor's row unconditionally, without looking at exitedAt. Since exitedAt IS NULL means "currently in the segment" (and SegmentService filters on exactly that), a survivor whose membership is exited wins over a duplicate whose membership is active — and the active rows are deleted.

The merged contact ends up outside the segment. Nothing errors, the migration commits, and because static segments drive campaign audiences, that contact quietly stops receiving mail it should receive.

This also contradicts the migration's own stated conservatism elsewhere — step 2 already applies "a single unsubscribe wins" for subscribed. Membership should follow the same principle: a single active source keeps the merged contact active.

Reproduction

Self-contained; only the two tables involved. Verified on PostgreSQL 16.14.

CREATE TABLE contacts (
  id text PRIMARY KEY, email text NOT NULL, subscribed boolean DEFAULT true,
  data jsonb, "projectId" text NOT NULL, "createdAt" timestamptz NOT NULL,
  UNIQUE ("projectId", email)
);
CREATE TABLE segment_memberships (
  "contactId" text NOT NULL REFERENCES contacts(id) ON DELETE CASCADE,
  "segmentId" text NOT NULL,
  "enteredAt" timestamptz NOT NULL, "exitedAt" timestamptz,
  PRIMARY KEY ("contactId", "segmentId")
);

-- One merge group of three: surv is the oldest and therefore the survivor.
INSERT INTO contacts VALUES
 ('surv', 'Dup@x.com',     true,  '{"k":"s"}',  'p1', now() - interval '3 day'),
 ('d1',   'dup@x.com',     false, 'null',       'p1', now() - interval '2 day'),
 ('d2',   '  DUP@X.COM  ', true,  '{"k2":"v"}', 'p1', now() - interval '1 day');

-- segA reproduces Bug 2: survivor exited, both duplicates still active.
-- segB reproduces Bug 1: survivor absent, both duplicates present.
INSERT INTO segment_memberships VALUES
 ('surv', 'segA', now() - interval '3 day', now() - interval '1 hour'),
 ('d1',   'segA', now() - interval '2 day', NULL),
 ('d2',   'segA', now() - interval '1 day', NULL),
 ('d1',   'segB', now() - interval '2 day', NULL),
 ('d2',   'segB', now() - interval '1 day', NULL);

Running the migration's membership steps against this aborts on segB (Bug 1). Removing the segB rows and re-running lets it commit, and the survivor is left with exitedAt still set on segA while both active rows are gone (Bug 2).

Suggested fix

One change covers both: pick a single winning membership per (survivor, segment) across the whole merge group — the survivor's own row included — preferring an active one, then delete the losers. The existing reassignment UPDATE afterwards is then always collision-free.

Replace the current dedupe DELETE with:

-- Collapse memberships across the entire merge group (survivor included) down to
-- one row per (survivor, segment), so the reassignment below cannot collide on the
-- composite primary key. An active membership (exitedAt IS NULL) wins, mirroring the
-- "a single unsubscribe wins" rule applied to `subscribed` above: if any source
-- contact is still in the segment, the merged contact stays in it.
WITH grp AS (
  SELECT sm.ctid AS row_id,
         ROW_NUMBER() OVER (
           PARTITION BY m.survivor_id, sm."segmentId"
           ORDER BY (sm."exitedAt" IS NOT NULL), sm."enteredAt", sm."contactId"
         ) AS rn
  FROM segment_memberships sm
  JOIN (
    SELECT dup_id AS cid, survivor_id FROM _contact_merge_map
    UNION
    SELECT survivor_id, survivor_id FROM _contact_merge_map
  ) m ON m.cid = sm."contactId"
)
DELETE FROM segment_memberships sm
USING grp
WHERE sm.ctid = grp.row_id AND grp.rn > 1;

Verified against the reproduction above: the migration now commits, and the survivor keeps one active membership in both segA and segB.

Also verified as a no-op when there are no duplicates — with an empty _contact_merge_map the join matches nothing, so on a database without variant duplicates (the common case) memberships and exitedAt values are left untouched. Confirmed on a three-contact fixture with no duplicates, including one contact with exitedAt set: all rows unchanged.

Pre-upgrade detection

For operators who want to check before upgrading, these detect the two conditions. Both return 0 when unaffected:

WITH map AS (
  SELECT id AS dup_id, survivor_id FROM (
    SELECT id, FIRST_VALUE(id) OVER (
      PARTITION BY "projectId", lower(regexp_replace(email, '^\s+|\s+$', '', 'g'))
      ORDER BY "createdAt" ASC, id ASC) AS survivor_id
    FROM contacts) r WHERE id <> survivor_id)
-- Bug 1: would abort the migration
SELECT 'pk_collisions' AS check, count(*) FROM (
  SELECT m.survivor_id, sm."segmentId" FROM map m
  JOIN segment_memberships sm ON sm."contactId" = m.dup_id
  WHERE NOT EXISTS (SELECT 1 FROM segment_memberships s2
    WHERE s2."contactId" = m.survivor_id AND s2."segmentId" = sm."segmentId")
  GROUP BY m.survivor_id, sm."segmentId" HAVING count(*) > 1) c
UNION ALL
-- Bug 2: would silently drop the contact from a segment
SELECT 'active_membership_loss', count(*) FROM map m
JOIN segment_memberships dsm ON dsm."contactId" = m.dup_id AND dsm."exitedAt" IS NULL
JOIN segment_memberships ssm ON ssm."contactId" = m.survivor_id
  AND ssm."segmentId" = dsm."segmentId" AND ssm."exitedAt" IS NOT NULL;

Note regexp_replace rather than btrim here: btrim(text) strips only U+0020, while ContactService.normalizeEmail uses JavaScript .trim(), which also strips tabs, newlines and NBSP. A btrim-based check therefore has a blind spot for rows whose email carries that whitespace — worth considering for the migration's own grouping expression too, which currently uses btrim and so may leave such rows un-normalized and un-grouped.

Notes

Found while merging v0.12.0 into a fork. Our own deployment is unaffected (no variant duplicates, and all segments are DYNAMIC, so segment_memberships is empty), so this is reported for other self-hosters rather than out of need — happy to open a PR with the fix above if useful.

A handful of smaller findings turned up in the same review (jsonb_object_agg ordering being non-deterministic when duplicates define the same custom-data key; no ANALYZE on _contact_merge_map before six joins against it, which matters at the 1M+ contact scale the docs target). Glad to file those separately if wanted.