deckhearth/migrations/1781000000006_reconcile-user-cards-unique.js

/**
 * Reconcile `user_cards` UNIQUE constraint to the canonical 3-column shape
 * declared by `migrations/1779853647564_initial-schema.js`
 * (`UNIQUE(user_id, card_id, is_foil)`).
 *
 * Convoy: `reconcile-historical-add-scripts` Brief 6.
 * Operator decision (Finding 1, Option A, ratified 2026-06-14):
 * keep the 3-column constraint that distinguishes foil and non-foil
 * copies of the same card as separate rows. Drop / treat-as-no-op
 * the stricter 2-column constraint (`UNIQUE(user_id, card_id)`)
 * that the historical `scripts/fix-user-cards-constraints.js`
 * job would have installed.
 *
 * This migration is defensive idempotent and achieves Option A
 * regardless of the prod env's current state:
 *
 *   1. Fresh Neon branches: `initial-schema` already created the
 *      auto-named 3-col constraint (`user_cards_user_id_card_id_is_foil_key`).
 *      The 2-col constraint never existed. Both blocks no-op.
 *
 *   2. Long-lived envs that never ran `fix-user-cards-constraints.js`:
 *      same as fresh — only the auto-named 3-col constraint exists.
 *      Both blocks no-op.
 *
 *   3. Long-lived envs that DID run `fix-user-cards-constraints.js`
 *      at some point (the script ADDs `user_cards_user_card_unique`
 *      as a 2-col `UNIQUE(user_id, card_id)`, on top of the
 *      pre-existing 3-col): the DROP removes the stricter 2-col
 *      constraint; the ADD's EXCEPTION block swallows the
 *      `duplicate_object` because the 3-col is still in place from
 *      `initial-schema`.
 *
 * Constraint names:
 * - `user_cards_user_id_card_id_is_foil_key` is the Postgres
 *   auto-generated name for the inline `UNIQUE(user_id, card_id, is_foil)`
 *   on `user_cards` declared at line 82 of `initial-schema.js`.
 *   Convention: `<table>_<col1>_<col2>_..._key`.
 * - `user_cards_user_card_unique` is the explicit name used by
 *   `scripts/fix-user-cards-constraints.js` (line 52) when ADDing
 *   the would-be 2-col constraint.
 *
 * `down()` is a hard-stub throw — re-installing the 2-col constraint
 * would forbid the foil/non-foil row distinction that runtime code
 * (`pages/api/cards/[id]/ownership.js`, `user_cards` ownership flows)
 * relies on.
 *
 * @type {import('node-pg-migrate').ColumnDefinitions | undefined}
 */
export const shorthands = undefined;

/**
 * @param {import('node-pg-migrate').MigrationBuilder} pgm
 * @returns {void}
 */
export const up = (pgm) => {
  // Defensive: if some env happened to apply
  // `scripts/fix-user-cards-constraints.js` (the 2-col UNIQUE), drop it.
  // No-op against fresh envs / envs that never applied the script.
  pgm.sql(`
    ALTER TABLE user_cards
    DROP CONSTRAINT IF EXISTS user_cards_user_card_unique;
  `);

  // Re-assert the canonical 3-col UNIQUE from `initial-schema.js`.
  // No-op against initial-schema-applied envs (the constraint already
  // exists under its auto-generated name); constructive against any
  // env where the constraint was somehow dropped. Postgres does not
  // support `ADD CONSTRAINT ... IF NOT EXISTS`, so we pre-check
  // `pg_constraint`. (An earlier draft used `EXCEPTION WHEN
  // duplicate_object`, but `ADD CONSTRAINT UNIQUE` raises
  // SQLSTATE 42P07 `duplicate_table` from the auto-created
  // supporting index, not 42710 `duplicate_object` — the pre-check
  // sidesteps both.)
  pgm.sql(`
    DO $$
    BEGIN
      IF NOT EXISTS (
        SELECT 1 FROM pg_constraint
        WHERE conname = 'user_cards_user_id_card_id_is_foil_key'
          AND conrelid = 'user_cards'::regclass
      ) THEN
        ALTER TABLE user_cards
        ADD CONSTRAINT user_cards_user_id_card_id_is_foil_key
        UNIQUE (user_id, card_id, is_foil);
      END IF;
    END $$;
  `);
};

/**
 * Hard-stub down. Reverting this migration would re-install the
 * stricter 2-col UNIQUE that forbids foil and non-foil copies of the
 * same card from coexisting as separate rows — the exact semantic
 * regression Option A rejected. If a future schema correction
 * legitimately needs to reshape `user_cards`'s uniqueness, ship a
 * NEW dated migration with a real `down()`.
 *
 * @returns {void}
 */
export const down = () => {
  throw new Error(
    '[migration:1781000000006_reconcile-user-cards-unique] Refusing to roll back. ' +
      'Reverting would re-install the 2-col UNIQUE(user_id, card_id) constraint that forbids ' +
      'foil and non-foil copies of the same card as separate rows — the regression Option A ' +
      'rejected. If you need to reshape user_cards uniqueness, ship a new dated migration ' +
      'with an explicit replacement constraint.'
  );
};