fourge-portal/scripts/cleanup-orphaned-storage.mjs

/**
 * cleanup-orphaned-storage.mjs
 *
 * Removes files in the `submissions` storage bucket that have no matching
 * row in `submission_files`. These are orphaned uploads from before the
 * silent-failure bug was fixed.
 *
 * Usage:
 *   SUPABASE_SERVICE_ROLE_KEY=<your-key> node scripts/cleanup-orphaned-storage.mjs
 *
 * The service role key is in Supabase dashboard → Project Settings → API.
 * Run once, then you can delete this script if you like.
 */

import { createClient } from '@supabase/supabase-js';

const SUPABASE_URL = 'https://fqflxxqvennhvoeywrdw.supabase.co';
const SERVICE_ROLE_KEY = process.env.SUPABASE_SERVICE_ROLE_KEY;

if (!SERVICE_ROLE_KEY) {
  console.error('Error: SUPABASE_SERVICE_ROLE_KEY env var is required.');
  console.error('  SUPABASE_SERVICE_ROLE_KEY=<key> node scripts/cleanup-orphaned-storage.mjs');
  process.exit(1);
}

const supabase = createClient(SUPABASE_URL, SERVICE_ROLE_KEY);

async function listAllStorageFiles(bucket) {
  // Storage structure: submissions/{taskId}/{timestamp}_{filename}
  // List the root to get task-ID "folders", then list each folder for files.
  const allPaths = [];

  const { data: folders, error: folderError } = await supabase.storage
    .from(bucket)
    .list('', { limit: 1000, sortBy: { column: 'name', order: 'asc' } });

  if (folderError) throw new Error(`Failed to list root: ${folderError.message}`);
  if (!folders?.length) return allPaths;

  for (const folder of folders) {
    // Supabase returns folders as items with `id: null`
    if (folder.id !== null) {
      // It's a top-level file (rare, skip)
      allPaths.push(folder.name);
      continue;
    }

    const { data: files, error: fileError } = await supabase.storage
      .from(bucket)
      .list(folder.name, { limit: 1000, sortBy: { column: 'name', order: 'asc' } });

    if (fileError) {
      console.warn(`  Warning: failed to list ${folder.name}/: ${fileError.message}`);
      continue;
    }

    for (const file of files ?? []) {
      if (file.id !== null) {
        allPaths.push(`${folder.name}/${file.name}`);
      }
    }
  }

  return allPaths;
}

async function main() {
  console.log('Fetching all submission_files records from database...');
  const { data: dbFiles, error: dbError } = await supabase
    .from('submission_files')
    .select('storage_path')
    .not('storage_path', 'is', null);

  if (dbError) throw new Error(`DB query failed: ${dbError.message}`);

  const knownPaths = new Set((dbFiles ?? []).map(r => r.storage_path));
  console.log(`  ${knownPaths.size} file records found in database.`);

  console.log('\nListing all files in submissions storage bucket...');
  const storagePaths = await listAllStorageFiles('submissions');
  console.log(`  ${storagePaths.length} files found in storage.`);

  const orphans = storagePaths.filter(p => !knownPaths.has(p));
  console.log(`\n${orphans.length} orphaned file(s) found (in storage but not in DB).`);

  if (orphans.length === 0) {
    console.log('Nothing to clean up.');
    return;
  }

  console.log('\nOrphaned files:');
  orphans.forEach(p => console.log(`  ${p}`));

  // Delete in batches of 100 (Supabase limit)
  const BATCH = 100;
  let deleted = 0;
  for (let i = 0; i < orphans.length; i += BATCH) {
    const batch = orphans.slice(i, i + BATCH);
    const { error: delError } = await supabase.storage.from('submissions').remove(batch);
    if (delError) {
      console.error(`  Error deleting batch: ${delError.message}`);
    } else {
      deleted += batch.length;
    }
  }

  console.log(`\nDone. ${deleted}/${orphans.length} orphaned file(s) deleted.`);
}

main().catch(err => {
  console.error('Fatal error:', err.message);
  process.exit(1);
});