deckhearth/.convoys/reconcile-historical-add-scripts/brief-5-reconcile-user-profile.md

---
convoy: reconcile-historical-add-scripts
brief_number: 5
depends_on: []
files:
  - migrations/1781000000005_reconcile-user-profile.js
---

# Brief 5: Reconcile user profile fields into migration history

## Goal (1 sentence)

Capture the deduplicated union of `scripts/add-user-profile-columns.js` and `scripts/add-user-profile-fields.js` (15 new `users` columns, 2 new tables `user_settings` + `user_avatars`, 6 CHECK constraints, 6 indexes) into a single new `node-pg-migrate` migration so a brand-new Neon branch ends up with the user-profile surface after `npm run setup-db`.

## Scope (files in scope — do not edit anything else)

- `migrations/1781000000005_reconcile-user-profile.js` — **new**

## Source scripts (read-only audit reference; DO NOT EDIT — both are no-go-zones)

### `scripts/add-user-profile-columns.js` lines 12-18 (the earlier, narrower script):

```js
await sql`
  ALTER TABLE users 
  ADD COLUMN IF NOT EXISTS first_name VARCHAR(255),
  ADD COLUMN IF NOT EXISTS last_name VARCHAR(255),
  ADD COLUMN IF NOT EXISTS username VARCHAR(255) UNIQUE,
  ADD COLUMN IF NOT EXISTS profile_image_url TEXT
`;
```

Plus per-row UPDATE backfill of defaults (lines 25-42). **Do NOT fold the backfill DML into this migration** — fresh envs have no rows to backfill; column DEFAULTs handle new rows.

### `scripts/add-user-profile-fields.js` lines 27-115 (the later, broader script — superset of #8 plus additional columns + 2 new tables + 6 CHECK constraints + 6 indexes):

```js
// Basic profile fields (overlaps add-user-profile-columns.js for first_name/last_name/username,
// but ADDS bio + avatar_url; idempotent overlap because of IF NOT EXISTS)
await sql`
  ALTER TABLE users 
  ADD COLUMN IF NOT EXISTS first_name VARCHAR(255),
  ADD COLUMN IF NOT EXISTS last_name VARCHAR(255),
  ADD COLUMN IF NOT EXISTS username VARCHAR(255) UNIQUE,
  ADD COLUMN IF NOT EXISTS bio TEXT,
  ADD COLUMN IF NOT EXISTS avatar_url TEXT
`;

// Preference fields
await sql`
  ALTER TABLE users 
  ADD COLUMN IF NOT EXISTS favorite_games JSONB DEFAULT '["MTG"]',
  ADD COLUMN IF NOT EXISTS collection_visibility VARCHAR(20) DEFAULT 'private',
  ADD COLUMN IF NOT EXISTS preferred_currency VARCHAR(3) DEFAULT 'USD',
  ADD COLUMN IF NOT EXISTS cards_per_page INTEGER DEFAULT 50,
  ADD COLUMN IF NOT EXISTS default_view VARCHAR(10) DEFAULT 'grid'
`;

// Notification + 2FA settings
await sql`
  ALTER TABLE users 
  ADD COLUMN IF NOT EXISTS notifications_email BOOLEAN DEFAULT true,
  ADD COLUMN IF NOT EXISTS notifications_marketing BOOLEAN DEFAULT false,
  ADD COLUMN IF NOT EXISTS two_factor_enabled BOOLEAN DEFAULT false
`;

// Display settings
await sql`
  ALTER TABLE users 
  ADD COLUMN IF NOT EXISTS theme VARCHAR(10) DEFAULT 'system',
  ADD COLUMN IF NOT EXISTS language VARCHAR(5) DEFAULT 'en'
`;

// user_settings table
await sql`
  CREATE TABLE IF NOT EXISTS user_settings (
    id SERIAL PRIMARY KEY,
    user_id INTEGER REFERENCES users(id) ON DELETE CASCADE,
    setting_key VARCHAR(100) NOT NULL,
    setting_value JSONB NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    UNIQUE(user_id, setting_key)
  )
`;

// user_avatars table
await sql`
  CREATE TABLE IF NOT EXISTS user_avatars (
    id SERIAL PRIMARY KEY,
    user_id INTEGER REFERENCES users(id) ON DELETE CASCADE,
    filename VARCHAR(255) NOT NULL,
    original_name VARCHAR(255),
    mime_type VARCHAR(100),
    file_size INTEGER,
    file_path TEXT NOT NULL,
    is_active BOOLEAN DEFAULT true,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
  )
`;

// 6 indexes
await sql`CREATE INDEX IF NOT EXISTS idx_users_username ON users(username)`;
await sql`CREATE INDEX IF NOT EXISTS idx_users_email ON users(email)`;
await sql`CREATE INDEX IF NOT EXISTS idx_user_settings_user_id ON user_settings(user_id)`;
await sql`CREATE INDEX IF NOT EXISTS idx_user_settings_key ON user_settings(setting_key)`;
await sql`CREATE INDEX IF NOT EXISTS idx_user_avatars_user_id ON user_avatars(user_id)`;
await sql`CREATE INDEX IF NOT EXISTS idx_user_avatars_active ON user_avatars(user_id, is_active)`;

// 6 CHECK constraints (each wrapped in JS try/catch to swallow 'already exists')
await sql`ALTER TABLE users ADD CONSTRAINT check_collection_visibility CHECK (collection_visibility IN ('private', 'public', 'unlisted'))`;
await sql`ALTER TABLE users ADD CONSTRAINT check_preferred_currency   CHECK (preferred_currency IN ('USD', 'EUR', 'GBP', 'CAD', 'JPY'))`;
await sql`ALTER TABLE users ADD CONSTRAINT check_cards_per_page       CHECK (cards_per_page IN (25, 50, 100))`;
await sql`ALTER TABLE users ADD CONSTRAINT check_default_view         CHECK (default_view IN ('grid', 'list'))`;
await sql`ALTER TABLE users ADD CONSTRAINT check_theme                CHECK (theme IN ('light', 'dark', 'system'))`;
await sql`ALTER TABLE users ADD CONSTRAINT check_language             CHECK (language IN ('en', 'es', 'fr', 'de', 'ja'))`;
```

Plus a per-row UPDATE backfill of defaults (lines 198-222). **Do NOT fold the backfill DML in** — column DEFAULTs handle new rows; fresh envs have no rows to backfill.

### Important note on the column dedup

Scripts #8 and #9 overlap on `first_name`, `last_name`, `username` — both use `ADD COLUMN IF NOT EXISTS` so on prod the second-running script no-ops those three columns. Both scripts have run on prod, so the union of their columns is what's actually present:

- From #8 only: `profile_image_url` (TEXT) — a column that ONLY #8 added.
- From #9 only: `bio`, `avatar_url`, `favorite_games`, `collection_visibility`, `preferred_currency`, `cards_per_page`, `default_view`, `notifications_email`, `notifications_marketing`, `two_factor_enabled`, `theme`, `language` (12 columns) + the 2 new tables + 6 CHECK constraints + 6 indexes.
- From both (idempotent overlap): `first_name`, `last_name`, `username`.

The migration must add **all 15 columns** (4 from #8 ∪ 12 from #9 with 3 in the intersection = 4 + 12 - 3 = 13 unique users columns; wait, let me recount: #8 adds 4 (first_name, last_name, username, profile_image_url); #9 adds 5 basic (first_name, last_name, username, bio, avatar_url) + 5 prefs + 3 notif + 2 display = 15. Union: first_name, last_name, username (shared) + profile_image_url (#8) + bio, avatar_url, favorite_games, collection_visibility, preferred_currency, cards_per_page, default_view, notifications_email, notifications_marketing, two_factor_enabled, theme, language (#9) = **3 + 1 + 12 = 16 columns**). So the migration adds 16 columns to `users`.

The redundant `profile_image_url` vs `avatar_url` pair is documented in `docs/SCHEMA_MAP.md` § "Known schema smells" #1 and surfaced as the `unify-user-avatar-column` follow-up. Both must be in fresh envs for parity.

## Target migration file

Path: `migrations/1781000000005_reconcile-user-profile.js`

Contents:

```js
/**
 * Reconcile two overlapping historical user-profile scripts into the
 * migration history, taking the union of their effects:
 *
 *   - scripts/add-user-profile-columns.js (the earlier, narrower
 *     script): first_name, last_name, username UNIQUE, profile_image_url
 *
 *   - scripts/add-user-profile-fields.js (the later, broader script;
 *     overlaps the earlier script on first_name / last_name / username
 *     and additionally adds): bio, avatar_url, favorite_games (JSONB
 *     DEFAULT '["MTG"]'), collection_visibility, preferred_currency,
 *     cards_per_page, default_view, notifications_email,
 *     notifications_marketing, two_factor_enabled, theme, language,
 *     + the new user_settings + user_avatars tables, + 6 CHECK
 *     constraints, + 6 indexes.
 *
 * Result on a fresh env: 16 new columns on `users`, 2 new tables,
 * 6 CHECK constraints, 6 indexes. On any long-lived env: every
 * statement is a no-op (IF NOT EXISTS / DO $$ EXCEPTION).
 *
 * Per-row UPDATE backfills from both scripts are intentionally NOT
 * folded in — column DEFAULTs handle new rows; fresh envs have no
 * rows to backfill.
 *
 * The profile_image_url / avatar_url redundancy is intentional for
 * parity with prod and is flagged in docs/SCHEMA_MAP.md § "Known
 * schema smells" #1; future cleanup is the queued
 * `unify-user-avatar-column` follow-up.
 *
 * CHECK constraint adds wrap in DO $$ ... EXCEPTION WHEN
 * duplicate_object THEN NULL END $$ because Postgres doesn't accept
 * ADD CONSTRAINT ... IF NOT EXISTS for CHECK. Each constraint gets
 * its own DO block so a failure in one doesn't block the rest.
 *
 * @type {import('node-pg-migrate').ColumnDefinitions | undefined}
 */
export const shorthands = undefined;

/**
 * @param {import('node-pg-migrate').MigrationBuilder} pgm
 */
export const up = (pgm) => {
  pgm.sql(`
    -- 16 columns on users (union of add-user-profile-columns.js + add-user-profile-fields.js)
    ALTER TABLE users
      ADD COLUMN IF NOT EXISTS first_name VARCHAR(255),
      ADD COLUMN IF NOT EXISTS last_name VARCHAR(255),
      ADD COLUMN IF NOT EXISTS username VARCHAR(255) UNIQUE,
      ADD COLUMN IF NOT EXISTS profile_image_url TEXT,
      ADD COLUMN IF NOT EXISTS bio TEXT,
      ADD COLUMN IF NOT EXISTS avatar_url TEXT,
      ADD COLUMN IF NOT EXISTS favorite_games JSONB DEFAULT '["MTG"]',
      ADD COLUMN IF NOT EXISTS collection_visibility VARCHAR(20) DEFAULT 'private',
      ADD COLUMN IF NOT EXISTS preferred_currency VARCHAR(3) DEFAULT 'USD',
      ADD COLUMN IF NOT EXISTS cards_per_page INTEGER DEFAULT 50,
      ADD COLUMN IF NOT EXISTS default_view VARCHAR(10) DEFAULT 'grid',
      ADD COLUMN IF NOT EXISTS notifications_email BOOLEAN DEFAULT true,
      ADD COLUMN IF NOT EXISTS notifications_marketing BOOLEAN DEFAULT false,
      ADD COLUMN IF NOT EXISTS two_factor_enabled BOOLEAN DEFAULT false,
      ADD COLUMN IF NOT EXISTS theme VARCHAR(10) DEFAULT 'system',
      ADD COLUMN IF NOT EXISTS language VARCHAR(5) DEFAULT 'en';

    -- user_settings table
    CREATE TABLE IF NOT EXISTS user_settings (
      id SERIAL PRIMARY KEY,
      user_id INTEGER REFERENCES users(id) ON DELETE CASCADE,
      setting_key VARCHAR(100) NOT NULL,
      setting_value JSONB NOT NULL,
      created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
      updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
      UNIQUE(user_id, setting_key)
    );

    -- user_avatars table
    CREATE TABLE IF NOT EXISTS user_avatars (
      id SERIAL PRIMARY KEY,
      user_id INTEGER REFERENCES users(id) ON DELETE CASCADE,
      filename VARCHAR(255) NOT NULL,
      original_name VARCHAR(255),
      mime_type VARCHAR(100),
      file_size INTEGER,
      file_path TEXT NOT NULL,
      is_active BOOLEAN DEFAULT true,
      created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
      updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
    );

    -- 6 indexes
    CREATE INDEX IF NOT EXISTS idx_users_username       ON users (username);
    CREATE INDEX IF NOT EXISTS idx_users_email          ON users (email);
    CREATE INDEX IF NOT EXISTS idx_user_settings_user_id ON user_settings (user_id);
    CREATE INDEX IF NOT EXISTS idx_user_settings_key    ON user_settings (setting_key);
    CREATE INDEX IF NOT EXISTS idx_user_avatars_user_id ON user_avatars (user_id);
    CREATE INDEX IF NOT EXISTS idx_user_avatars_active  ON user_avatars (user_id, is_active);

    -- 6 CHECK constraints (each in its own DO block so one failure doesn't block the rest)
    DO $$ BEGIN
      ALTER TABLE users ADD CONSTRAINT check_collection_visibility
        CHECK (collection_visibility IN ('private', 'public', 'unlisted'));
    EXCEPTION WHEN duplicate_object THEN NULL; END $$;

    DO $$ BEGIN
      ALTER TABLE users ADD CONSTRAINT check_preferred_currency
        CHECK (preferred_currency IN ('USD', 'EUR', 'GBP', 'CAD', 'JPY'));
    EXCEPTION WHEN duplicate_object THEN NULL; END $$;

    DO $$ BEGIN
      ALTER TABLE users ADD CONSTRAINT check_cards_per_page
        CHECK (cards_per_page IN (25, 50, 100));
    EXCEPTION WHEN duplicate_object THEN NULL; END $$;

    DO $$ BEGIN
      ALTER TABLE users ADD CONSTRAINT check_default_view
        CHECK (default_view IN ('grid', 'list'));
    EXCEPTION WHEN duplicate_object THEN NULL; END $$;

    DO $$ BEGIN
      ALTER TABLE users ADD CONSTRAINT check_theme
        CHECK (theme IN ('light', 'dark', 'system'));
    EXCEPTION WHEN duplicate_object THEN NULL; END $$;

    DO $$ BEGIN
      ALTER TABLE users ADD CONSTRAINT check_language
        CHECK (language IN ('en', 'es', 'fr', 'de', 'ja'));
    EXCEPTION WHEN duplicate_object THEN NULL; END $$;
  `);
};

/**
 * Down-migration intentionally throws. Dropping 16 user-profile columns
 * + user_settings + user_avatars on a long-lived env would erase every
 * user's profile data, preferences, avatar history, and settings, and
 * break runtime reads in pages/api/user/{settings,profile,avatar}.js.
 *
 * @returns {void}
 */
export const down = () => {
  throw new Error(
    '[migration:1781000000005_reconcile-user-profile] Down not supported. ' +
      'Dropping these columns + tables would erase every user profile, preference, ' +
      'avatar history, and settings row, and break runtime reads in ' +
      'pages/api/user/{settings,profile,avatar}.js. Write a new dated migration ' +
      'for any future schema correction.'
  );
};
```

## Conventions to follow

- `.cursor/rules/db-and-schema.mdc` § "Schema source of truth" — `migrations/` is canonical.
- `.cursor/rules/no-go-zones.mdc` — both source scripts are append-only history.
- Style: raw `pgm.sql(...)` template literals. Per D2.
- ESM exports; `"type": "module"`.
- `IF NOT EXISTS` on every ALTER + CREATE INDEX + CREATE TABLE.
- CHECK constraints in `DO $$ ... EXCEPTION WHEN duplicate_object THEN NULL END $$;` blocks, one block per constraint.
- Match the source scripts' column types, defaults, FK rules, and CHECK values verbatim.

## Acceptance criteria

- [ ] `migrations/1781000000005_reconcile-user-profile.js` exists with the exact filename above.
- [ ] The file's `up()` adds exactly **16 columns** to `users` (per the dedup count in the source-scripts note above), creates **2 tables** (`user_settings`, `user_avatars`), creates **6 indexes**, and adds **6 CHECK constraints** each in its own `DO $$ EXCEPTION` block.
- [ ] BOTH `profile_image_url` AND `avatar_url` are present (parity with prod; redundancy is documented).
- [ ] The file's `down()` throws with a clear message.
- [ ] The file's docstring cites both source scripts + the convoy file + the `unify-user-avatar-column` follow-up.
- [ ] Per-row UPDATE backfill DML is NOT in the migration.
- [ ] `node --check migrations/1781000000005_reconcile-user-profile.js` passes.
- [ ] `node -e "import('./migrations/1781000000005_reconcile-user-profile.js').then(m => console.log(typeof m.up, typeof m.down, typeof m.shorthands))"` prints `function function undefined`.
- [ ] `npm run lint` matches baseline.
- [ ] `npm run test:run` reports 21/21 passing.

## Verification

```bash
node --check migrations/1781000000005_reconcile-user-profile.js
node -e "import('./migrations/1781000000005_reconcile-user-profile.js').then(m => console.log(typeof m.up, typeof m.down, typeof m.shorthands))"
npm run lint
npm run test:run
```

Expected: `node --check` silent, `node -e` prints `function function undefined`, lint baseline, 21/21 tests pass.

**Do NOT** run `npm run migrate up` against any environment.

## Commit message

```
feat(migrations): reconcile user profile fields into migration history (brief 5/7)

Captures the deduplicated union of scripts/add-user-profile-columns.js
and scripts/add-user-profile-fields.js into one new node-pg-migrate
migration:

  - 16 columns on users (basic profile + preferences + notifications + display)
  - 2 new tables (user_settings, user_avatars)
  - 6 CHECK constraints (each in its own DO $$ EXCEPTION block)
  - 6 indexes

Both profile_image_url (from script #1) and avatar_url (from script #2)
are added for parity with prod. The redundancy is flagged in
docs/SCHEMA_MAP.md § "Known schema smells" #1 and queued for cleanup as
the `unify-user-avatar-column` follow-up.

Per-row UPDATE backfill DML from both scripts is intentionally NOT
folded in — column DEFAULTs handle new rows; fresh envs have no rows
to backfill.

Per .convoys/reconcile-historical-add-scripts.md § Brief outline → B5.
Idempotent re-apply (IF NOT EXISTS guards + per-CHECK DO blocks).
```

## PR shape

**Title:** `feat(migrations): reconcile user profile fields into migration history (brief 5/7)`

**Body template:**

```markdown
Brief 5 of the `reconcile-historical-add-scripts` convoy. See
[`.convoys/reconcile-historical-add-scripts.md`](../.convoys/reconcile-historical-add-scripts.md)
for the full plan and rationale.

## What this PR does

Adds `migrations/1781000000005_reconcile-user-profile.js` — captures
the deduplicated union of `scripts/add-user-profile-columns.js`
(earlier, narrower) and `scripts/add-user-profile-fields.js` (later,
broader) into a single new `node-pg-migrate` migration.

- 16 new columns on `users` (first_name, last_name, username UNIQUE,
  profile_image_url, bio, avatar_url, favorite_games JSONB, …, theme,
  language) — all `ADD COLUMN IF NOT EXISTS`
- 2 new tables: `user_settings`, `user_avatars` — both
  `CREATE TABLE IF NOT EXISTS`
- 6 indexes — `CREATE INDEX IF NOT EXISTS`
- 6 CHECK constraints (each in its own
  `DO $$ ... EXCEPTION WHEN duplicate_object THEN NULL END $$;` block)

## Why both `profile_image_url` and `avatar_url`?

Script #1 added `profile_image_url`, script #2 added `avatar_url` —
both exist on prod and both are in this migration for fresh-env
parity. The redundancy is flagged in
`docs/SCHEMA_MAP.md` § "Known schema smells" #1 and queued for cleanup
as the `unify-user-avatar-column` follow-up.

## What this PR does NOT do

- Does **NOT** edit either source script (no-go-zones).
- Does **NOT** fold in the per-row UPDATE backfill DML from either
  source script — column DEFAULTs handle new rows.
- Does **NOT** unify `profile_image_url` / `avatar_url` — that's the
  `unify-user-avatar-column` follow-up.
- Does **NOT** edit `scripts/setup-neon-db.js`, `package.json`, README,
  `AGENTS.md`, or `docs/SCHEMA_MAP.md` (B7 owns SCHEMA_MAP).
- Does **NOT** run `npm run migrate up` against any environment.

## Verification checklist

- [ ] `node --check migrations/1781000000005_reconcile-user-profile.js` exits 0
- [ ] `node -e "import('./migrations/1781000000005_reconcile-user-profile.js').then(m => console.log(typeof m.up, typeof m.down, typeof m.shorthands))"` prints `function function undefined`
- [ ] `npm run lint` matches baseline
- [ ] `npm run test:run` reports 21/21 passing
- [ ] Did not run `npm run migrate up` against any environment in this PR

## Cross-references

- Convoy file: `.convoys/reconcile-historical-add-scripts.md`
- Source scripts (no-go-zones, audit reference only):
  `scripts/add-user-profile-columns.js`, `scripts/add-user-profile-fields.js`
```

## DO NOT

- DO NOT edit either source script or any file under `scripts/`.
- DO NOT edit any other file under `migrations/`.
- DO NOT edit `scripts/setup-neon-db.js`, `package.json`, README, `AGENTS.md`, or `docs/SCHEMA_MAP.md`.
- DO NOT run `npm run migrate up` against any environment.
- DO NOT pick one of `profile_image_url` / `avatar_url` to omit — both must be present for parity.
- DO NOT collapse the 6 CHECK constraints into a single `DO $$ EXCEPTION` block — one block per constraint so one duplicate doesn't swallow the others.
- DO NOT include the per-row UPDATE backfill DML.
- DO NOT call `npm run migrate create`.

## Rationale (≤3 sentences)

This is the largest single migration in the convoy because both historical scripts are tightly coupled to the `users` table surface and splitting them would create artificial boundaries (e.g., separating "users columns" from "CHECK constraints on users columns" makes no sense). Per-CHECK `DO $$ EXCEPTION` blocks mirror the historical scripts' per-statement try/catch pattern and ensure one duplicate-constraint failure doesn't block the rest. Keeping both avatar-style columns matches prod-as-is and explicitly defers the cleanup to a scoped follow-up convoy.