deckhearth/migrations/1781000000003_reconcile-collaboration-tables.js

/**
 * Reconcile collaboration tables missed by the initial-schema backfill.
 *
 * Captures the DDL half of `scripts/add-collaboration-features.js` (a
 * historical no-go-zone script) that the `initial-schema` backfill missed:
 *
 *   1. `collection_permissions` (collaboration roles + invite tokens)
 *   2. `collection_activity` (audit-trail with JSONB details)
 *   3. `users.is_pending` (column for invited-but-not-yet-accepted users)
 *   4. 3 indexes on the tables this migration creates
 *
 * `idx_collections_visibility` is NOT created here. This file's timestamp
 * runs before `1781442330002_reconcile-collections-columns`, which is the
 * migration that adds `collections.visibility`. Creating the index here
 * fails on a fresh CI database (`column "visibility" does not exist`).
 * The index ships in `1786757312884_add-collections-visibility-index`.
 *
 * `lib/permission-middleware.js` reads `collection_permissions` in
 * `withCollectionPermission` and writes `collection_activity` from
 * `logCollectionActivity` — both are runtime invariants, so a fresh Neon
 * branch onboarded by `npm run setup-db` MUST land these tables. Prod was
 * brought to this shape by `add-collaboration-features.js` running
 * historically; this migration brings fresh envs to parity per
 * `.convoys/reconcile-historical-add-scripts.md` § Brief outline → B3.
 *
 * Idempotent against:
 *   - A brand-new Neon branch (creates everything fresh).
 *   - Any long-lived env where `add-collaboration-features.js` already ran
 *     (every CREATE TABLE / ADD COLUMN / CREATE INDEX is `IF NOT EXISTS`-
 *     guarded → no-op except recording the `pgmigrations` row).
 *
 * The owner-permission DML backfill from the historical script is NOT
 * captured: a fresh env has no pre-existing collections needing
 * back-fill, and prod's backfill is already applied.
 *
 * @type {import('node-pg-migrate').ColumnDefinitions | undefined}
 */
export const shorthands = undefined;

/**
 * @param {import('node-pg-migrate').MigrationBuilder} pgm
 * @returns {void}
 */
export const up = (pgm) => {
  pgm.sql(`
    CREATE TABLE IF NOT EXISTS collection_permissions (
      id SERIAL PRIMARY KEY,
      collection_id INTEGER REFERENCES collections(id) ON DELETE CASCADE,
      user_id INTEGER REFERENCES users(id) ON DELETE CASCADE,
      role VARCHAR(20) NOT NULL CHECK (role IN ('owner', 'editor', 'viewer')),
      status VARCHAR(20) DEFAULT 'active' CHECK (status IN ('active', 'pending', 'declined')),
      invite_token VARCHAR(255) UNIQUE,
      invited_by INTEGER REFERENCES users(id),
      created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
      updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
      UNIQUE(collection_id, user_id)
    )
  `);

  pgm.sql(`
    CREATE TABLE IF NOT EXISTS collection_activity (
      id SERIAL PRIMARY KEY,
      collection_id INTEGER REFERENCES collections(id) ON DELETE CASCADE,
      user_id INTEGER REFERENCES users(id) ON DELETE SET NULL,
      action VARCHAR(50) NOT NULL,
      details JSONB,
      created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
    )
  `);

  pgm.sql(`
    ALTER TABLE users
    ADD COLUMN IF NOT EXISTS is_pending BOOLEAN DEFAULT false
  `);

  pgm.sql(`
    CREATE INDEX IF NOT EXISTS idx_collection_permissions_collection_id
      ON collection_permissions(collection_id);
    CREATE INDEX IF NOT EXISTS idx_collection_permissions_user_id
      ON collection_permissions(user_id);
    CREATE INDEX IF NOT EXISTS idx_collection_activity_collection_id
      ON collection_activity(collection_id);
  `);
};

/**
 * Down-migration is a hard stub. Dropping `collection_permissions` /
 * `collection_activity` would erase every collaboration role assignment
 * and every audit-trail row in the database; dropping `users.is_pending`
 * would discard the invited-user state for every pending invite. If a
 * future schema correction needs to mutate one of these surfaces, write a
 * NEW dated migration with a real `down` — do NOT remove this stub.
 *
 * @returns {void}
 */
export const down = () => {
  throw new Error(
    '[migration:1781000000003_reconcile-collaboration-tables] Refusing to drop ' +
      'collection_permissions / collection_activity / users.is_pending. Rolling back ' +
      'this migration would erase every collaboration role assignment, every audit-trail ' +
      'row, and every invited-user pending state in the database. If you need a clean ' +
      'schema for testing, branch the Neon database and run `npm run migrate up` against ' +
      'the branch instead.'
  );
};