pn-new-crm/scripts/backfill-nested-document-folders.ts

#!/usr/bin/env tsx
/**
 * Phase 2 nested-subfolders backfill.
 *
 * Re-files every existing `files` row that has `entity_type='interest'`
 * (or a non-null `interest_id`) under a nested
 * `Clients/<Name>/<Interest folder>/` subfolder. Idempotent — already-
 * filed rows are skipped.
 *
 * Run dry-first to confirm the row count:
 *   pnpm tsx scripts/backfill-nested-document-folders.ts
 *
 * Apply for real:
 *   pnpm tsx scripts/backfill-nested-document-folders.ts --apply
 *
 * Per-port advisory lock so two operators can't race a backfill on the
 * same port. Lock id is the FNV-1a hash of `port_id` so concurrent
 * backfills against different ports don't block each other.
 */

import { sql } from 'drizzle-orm';

import { db } from '../src/lib/db';
import { ensureEntityFolder } from '../src/lib/services/document-folders.service';

const APPLY = process.argv.includes('--apply');

function fnv1a(input: string): number {
  // Simple deterministic 32-bit hash — used as the advisory-lock id so
  // the lock is stable across runs. PostgreSQL accepts a bigint here.
  let hash = 0x811c9dc5;
  for (let i = 0; i < input.length; i++) {
    hash ^= input.charCodeAt(i);
    hash = Math.imul(hash, 0x01000193);
  }
  return hash >>> 0;
}

async function main() {
  console.log(`[backfill-nested-folders] dry-run=${!APPLY}`);

  // 1. Gather every (port_id, interest_id) pair whose files need to be
  //    nested. We only need to ensure the folder exists — the
  //    `files.interest_id` column is populated separately by Phase 1.
  const rows = await db.execute<{ port_id: string; interest_id: string; row_count: number }>(
    sql`
      SELECT f.port_id, f.interest_id, COUNT(*)::int AS row_count
      FROM files f
      WHERE f.interest_id IS NOT NULL
        AND f.archived_at IS NULL
      GROUP BY f.port_id, f.interest_id
      ORDER BY f.port_id, f.interest_id
    `,
  );

  // postgres-js returns the raw result iterable; the `.rows` property is
  // pgnative-only — iterate the result directly.
  const list = Array.isArray(rows) ? rows : ((rows as { rows?: typeof rows }).rows ?? rows);
  console.log(`[backfill-nested-folders] ${list.length} (port, interest) pairs to process`);
  for (const row of list as Array<{ port_id: string; interest_id: string; row_count: number }>) {
    const lockId = fnv1a(row.port_id);
    if (APPLY) {
      await db.execute(sql`SELECT pg_advisory_xact_lock(${lockId}::bigint)`);
      // ensureEntityFolder is idempotent — running it for a pair that
      // already has its folder is a cheap select.
      await ensureEntityFolder(row.port_id, 'interest', row.interest_id, 'system');
    }
    console.log(
      `  ${APPLY ? '✓' : '·'} port=${row.port_id.slice(0, 8)} interest=${row.interest_id.slice(
        0,
        8,
      )} files=${row.row_count}`,
    );
  }

  console.log(`[backfill-nested-folders] done.`);
  process.exit(0);
}

main().catch((err) => {
  console.error('[backfill-nested-folders] failed', err);
  process.exit(1);
});