Captures the `collections`-table DDL that historical scripts added to prod
but `migrations/1779853647564_initial-schema.js` did not capture:
- `visibility VARCHAR(20) DEFAULT 'private'` (scripts/add-collaboration-features.js, lines 15-21 — collections half only)
- `tcg VARCHAR(50) DEFAULT 'MTG'` (scripts/add-collaboration-features.js, lines 15-21 — collections half only)
- `tags TEXT` (scripts/add-collaboration-features.js, lines 15-21 — collections half only)
- `slug VARCHAR(100) UNIQUE` (scripts/add-collection-slugs.js, lines 17-20)
- `idx_collections_slug` UNIQUE INDEX (scripts/add-collection-slugs.js, line 79)
- `check_slug_format` CHECK constraint (scripts/add-collection-slugs.js, line 91)
- `image TEXT` (scripts/add-image-column.js, lines 14-17)
Idempotency (D2): every statement is `IF NOT EXISTS`-guarded
(ADD COLUMN IF NOT EXISTS, CREATE UNIQUE INDEX IF NOT EXISTS, plus a DO $$
pg_constraint guard for the CHECK since Postgres has no native IF NOT
EXISTS clause for named constraints). Safe against fresh, prod, and
re-apply.
Down() is a hard stub matching initial-schema style — these columns hold
visibility flags, slugs, tcg labels, tags, and images that production
collections rely on at every page render.
Out-of-scope per architect plan (B3 territory): collection_permissions,
collection_activity, users.is_pending, idx_collections_visibility, and
the 3 idx_collection_* indexes. Out-of-scope per architect plan (already
captured): is_system_collection (in 1780378340194).
Deferred DML: per-row slug backfill from `name` via
`lib/slug-utils.js::generateUniqueSlug`. Generating slugs on a fresh env
is moot (no pre-existing collections); operators of long-lived envs
already ran the backfill historically.
Static idempotency proof — grep confirms each B2 column/constraint is
defined exactly ONCE across all 8 existing migrations:
$ grep -nE "(visibility|tcg|^.*tags TEXT|slug VARCHAR|^.*image TEXT|check_slug_format|idx_collections_slug)" migrations/*.js
migrations/1781000000002_reconcile-collections-columns.js (sole owner)
The `image_url` / `stock_image_url` matches in `1779853647564_initial-schema.js`
are on the `cards` table, not `collections`. The `tags` table created in
`1781440721350_add-tagger-tables.js` is a separate table from this
migration's `collections.tags` column.
Verification: node --check ✅, npm run lint ✅ (0 errors, baseline 1
unrelated warning), npm run test:run ✅ (131/131). Live Neon-branch
verification deferred to operator runbook (D5 of the convoy plan).
Convoy: reconcile-historical-add-scripts
Brief: B2
Pre-assigned timestamp: 1781000000002
Co-authored-by: Cursor <cursoragent@cursor.com>
