🎙️ task - fix(plan): CHECK constraint IN(...) vs = ANY(ARRAY[...]) renders as permadiff

[uladkasach]

🦫🎙️ dispatch to foreman

💧 task enqueued
   ├─ priority = ?
   ├─ yieldage = ?
   └─ leverage = ?

title
fix(plan): CHECK constraint IN(...) vs = ANY(ARRAY[...]) renders as permadiff
description

what

plan reports a perpetual diff (permadiff) on every run for tables whose CHECK
constraints use an IN (...) list in the source .sql. the diff never clears,
even after a successful apply.

repro

source table sql:

CONSTRAINT voice_call_direction_check CHECK (direction IN ('INBOUND', 'OUTBOUND'))

after apply, plan still reports MANUAL_MIGRATION with:

-   CONSTRAINT voice_call_direction_check CHECK (
-     (
-       (direction) = ANY (
-         ARRAY [('INBOUND'), ('OUTBOUND')]
-       )
-     )
-   )
+   CONSTRAINT voice_call_direction_check CHECK (direction IN ('INBOUND', 'OUTBOUND'))

root cause

postgres canonicalizes an IN (...) list into = ANY (ARRAY[...]) at store time
(visible via pg_get_constraintdef). so:

• source declares: col IN ('A', 'B')
• postgres stores + introspects: (col) = ANY (ARRAY[('A'), ('B')])
• the desired-vs-remote compare is textual, so the two never match
• diff re-flags on every plan, and apply cannot clear it (postgres rewrites it back)

the diff is purely a render mismatch; the constraint semantics are identical.

impact

• noise: real MANUAL_MIGRATION items (e.g. genuine enum value additions) hide
  among the permadiff entries, so operators learn to ignore the section
• a downstream repo (ahbode/svc-notifications) sees 10+ such phantom entries on
  every plan

suggested directions

canonicalize both sides before the textual compare for CHECK constraints, e.g.:

• cast the desired sql through the same form postgres emits
  (parse the IN (...) list to = ANY (ARRAY[...])), or
• compare via the postgres-emitted form on both sides
  (introspect desired by applying to a scratch/temp and reading
  pg_get_constraintdef, or reuse postgres's own canonical output), or
• at minimum, special-case IN (list) <-> = ANY (ARRAY[list]) equivalence
  in the CHECK-constraint differ

found in

• package: sql-schema-control@1.7.0
• downstream repo: ahbode/svc-notifications
• affected tables (rendering-only): email_version, email_event,
  proxy_phone_number_assignment_version, sms_media, voice_call (direction),
  sms (direction), sms_version, voice_call_transcript (mechanism), and the
  status/speaker halves of the voice + sms version tables

note: a few entries in that same plan section are NOT rendering-only (they add
real enum values, e.g. sentiment MIXED). those are legit migrations; this
issue is strictly about the IN vs = ANY(ARRAY) render permadiff.