deckhearth/scripts/setup-neon-db.js

#!/usr/bin/env node

/**
 * First-time / re-onboarding setup for the Deck Hearth Neon database.
 *
 * Pipeline (post-`migration-tool` convoy, 2026-05-26):
 *   1. Validate `ADMIN_INITIAL_PASSWORD` is set (fail loud BEFORE touching the DB).
 *   2. Spawn `npm run migrate up` to apply every pending migration under
 *      `migrations/`. The initial backfill migration (1779853647564_initial-schema)
 *      uses `CREATE TABLE IF NOT EXISTS` and is idempotent against fresh or
 *      pre-existing envs.
 *   3. Seed the admin user with `ON CONFLICT (email) DO NOTHING`.
 *
 * Make sure you have `POSTGRES_URL` set in `.env.local`. See README §
 * "First-time admin setup" for the operator runbook.
 */

import dotenv from 'dotenv';
dotenv.config({ path: '.env.local' });

import { spawn } from 'node:child_process';
import { neon } from '@neondatabase/serverless';
import bcrypt from 'bcryptjs';

function runMigrations() {
  return new Promise((resolve, reject) => {
    console.log('✅ Running migrations (npm run migrate up)...');
    const child = spawn('npm', ['run', 'migrate', '--', 'up'], {
      stdio: 'inherit',
      shell: false,
    });
    child.on('error', (err) => reject(err));
    child.on('exit', (code, signal) => {
      if (code === 0) {
        resolve();
      } else {
        reject(
          new Error(
            `npm run migrate up exited with code=${code} signal=${signal}. ` +
              'See output above for the failing migration.'
          )
        );
      }
    });
  });
}

async function setupNeonDatabase() {
  const adminPassword = process.env.ADMIN_INITIAL_PASSWORD;
  if (!adminPassword || !adminPassword.trim()) {
    console.error(
      '❌ ADMIN_INITIAL_PASSWORD environment variable is not set.\n' +
        '\n' +
        '   Set it in .env.local for local dev, or as a CI secret if you run setup from CI.\n' +
        '   Generate a strong password with: openssl rand -base64 24\n' +
        '   See README.md → "First-time admin setup" for the full flow.\n'
    );
    process.exit(1);
  }

  if (!process.env.POSTGRES_URL) {
    console.error(
      '❌ POSTGRES_URL environment variable is not set.\n' +
        '   Set it in .env.local (Neon connection string) before running setup.\n'
    );
    process.exit(1);
  }

  try {
    await runMigrations();

    console.log('✅ Connecting to Neon database to seed admin user...');
    const sql = neon(process.env.POSTGRES_URL);

    const hashedPassword = await bcrypt.hash(adminPassword, 12);

    await sql`
      INSERT INTO users (email, password, role)
      VALUES (${'admin@deckhearth.com'}, ${hashedPassword}, ${'admin'})
      ON CONFLICT (email) DO NOTHING
    `;
    console.log('✅ Admin user ready (email: admin@deckhearth.com)');

    console.log('🎉 Neon database setup completed successfully!');
    console.log('');
    console.log('📋 Database Details:');
    console.log('   Database: Neon PostgreSQL');
    console.log('   Schema: applied via node-pg-migrate (see migrations/)');
    console.log('   Admin user ready (email: admin@deckhearth.com)');
    console.log('');
    console.log('🔧 Next Steps:');
    console.log('   1. Test the API endpoints');
    console.log('   2. Start building the frontend');
  } catch (error) {
    console.error('❌ Database setup failed:', error.message);
    console.log('');
    console.log('🔧 Troubleshooting:');
    console.log('   1. Make sure POSTGRES_URL is set in .env.local');
    console.log('   2. Make sure ADMIN_INITIAL_PASSWORD is set in .env.local');
    console.log('   3. Check your Neon database connection');
    console.log('   4. If the migrate step failed, inspect the SQL above and');
    console.log('      see migrations/ for the failing file. To re-try just the');
    console.log('      migration step run: npm run migrate up');
    process.exit(1);
  }
}

setupNeonDatabase();