Skip to content

A self-hosted UPI & COD payment gateway with zero per-transaction platform fees, universal JavaScript checkout integration, dynamic QR payments, WhatsApp OTP authentication, and automated payment verification.

JavaScript
7.5 KB
Raw Blame
import fs from 'node:fs';
import path from 'node:path';
import assert from 'node:assert/strict';
import { Database } from '../packages/db/src/database.js';

async function resetProductionDatabase() {
  console.log('============================================================');
  console.log('🚀 TARGETED PRODUCTION DATABASE RESET');
  console.log('============================================================');

  const isConfirmed = process.argv.includes('--confirm-reset-production-db') || process.env.ALLOW_PRODUCTION_RESET === 'true';
  if (!isConfirmed) {
    console.error('\n❌ [SAFETY ABORT] Production database reset requires explicit confirmation.');
    console.error('To proceed, re-run with `--confirm-reset-production-db` or set `ALLOW_PRODUCTION_RESET=true`.\n');
    process.exit(1);
  }

  const prodDbPath = path.resolve(process.cwd(), 'data/youpi_pg.sqlite');
  console.log('Target Database Path:', prodDbPath);

  if (!fs.existsSync(prodDbPath)) {
    const parentDir = path.dirname(prodDbPath);
    if (!fs.existsSync(parentDir)) {
      fs.mkdirSync(parentDir, { recursive: true });
    }
    const initDb = new Database({ dbPath: prodDbPath });
    initDb.close();
  }

  const db = new Database({ dbPath: prodDbPath });

  // Backup before reset using WAL-safe atomic VACUUM INTO
  const backupDir = path.resolve(process.cwd(), 'data/backups');
  if (!fs.existsSync(backupDir)) {
    fs.mkdirSync(backupDir, { recursive: true });
  }
  const backupPath = path.join(backupDir, `prod_pre_reset_${Date.now()}.sqlite`);
  try {
    db.execute('VACUUM INTO ?', [backupPath]);
    console.log('[✓] Confirmed WAL-consistent atomic backup created via VACUUM INTO:', backupPath);
  } catch (backupErr) {
    console.warn('[!] VACUUM INTO failed, falling back to copyFileSync:', backupErr.message);
    if (fs.existsSync(prodDbPath)) {
      fs.copyFileSync(prodDbPath, backupPath);
    }
  }

  // 1. Audit BEFORE state
  console.log('\n--- BEFORE RESET COUNTS ---');
  const countsBefore = {
    merchants: db.queryOne('SELECT count(*) as c FROM merchants').c,
    receivingAccounts: db.queryOne('SELECT count(*) as c FROM payment_receiving_accounts').c,
    customers: db.queryOne('SELECT count(*) as c FROM customers').c,
    paymentSessions: db.queryOne('SELECT count(*) as c FROM payment_sessions').c,
    observations: db.queryOne('SELECT count(*) as c FROM payment_observations').c,
    supportTickets: db.queryOne('SELECT count(*) as c FROM support_tickets').c,
    ticketMessages: db.queryOne('SELECT count(*) as c FROM ticket_messages').c,
    webhookDeliveries: db.queryOne('SELECT count(*) as c FROM webhook_deliveries').c,
    auditLogs: db.queryOne('SELECT count(*) as c FROM audit_logs').c
  };
  console.table(countsBefore);

  const merchantBefore = db.queryOne('SELECT id, business_name, slug, webhook_url, branding FROM merchants');
  console.log('Merchant before reset:', merchantBefore);
  const accountBefore = db.queryOne('SELECT id, merchant_id, upi_id, payee_name, is_active FROM payment_receiving_accounts');
  console.log('Receiving account before reset:', accountBefore);

  assert.ok(merchantBefore, 'Merchant record must exist before reset');
  assert.ok(accountBefore, 'Receiving account record must exist before reset');

  // 2. Perform ACID Transactional Reset
  console.log('\n[→] Executing transactional customer & transaction data purge...');
  db.transaction(() => {
    // A. Support tickets and messages
    db.execute('DELETE FROM ticket_messages;');
    db.execute('DELETE FROM support_tickets;');

    // B. Webhook deliveries
    db.execute('DELETE FROM webhook_deliveries;');

    // C. Payment observations (unlink attributed payments then delete)
    db.execute('UPDATE payment_observations SET attributed_payment_id = NULL;');
    db.execute('DELETE FROM payment_observations;');

    // D. Payment sessions
    db.execute('DELETE FROM payment_sessions;');

    // E. Customers
    db.execute('DELETE FROM customers;');

    // F. Audit logs (remove customer/transaction events, keep merchant & account configuration history)
    db.execute("DELETE FROM audit_logs WHERE entity_type IN ('PAYMENT_SESSION', 'PAYMENT_OBSERVATION');");

    // G. Reset slot state on receiving accounts
    db.execute('UPDATE payment_receiving_accounts SET is_slot_busy = 0, current_active_session_id = NULL;');
  });

  console.log('[✓] Transaction committed successfully.');

  // 3. Audit AFTER state
  console.log('\n--- AFTER RESET COUNTS ---');
  const countsAfter = {
    merchants: db.queryOne('SELECT count(*) as c FROM merchants').c,
    receivingAccounts: db.queryOne('SELECT count(*) as c FROM payment_receiving_accounts').c,
    customers: db.queryOne('SELECT count(*) as c FROM customers').c,
    paymentSessions: db.queryOne('SELECT count(*) as c FROM payment_sessions').c,
    observations: db.queryOne('SELECT count(*) as c FROM payment_observations').c,
    supportTickets: db.queryOne('SELECT count(*) as c FROM support_tickets').c,
    ticketMessages: db.queryOne('SELECT count(*) as c FROM ticket_messages').c,
    webhookDeliveries: db.queryOne('SELECT count(*) as c FROM webhook_deliveries').c,
    auditLogs: db.queryOne('SELECT count(*) as c FROM audit_logs').c
  };
  console.table(countsAfter);

  // 4. Verify Merchant & Account Integrity
  const merchantAfter = db.queryOne('SELECT id, business_name, slug, webhook_url, branding FROM merchants');
  const accountAfter = db.queryOne('SELECT id, merchant_id, upi_id, payee_name, is_active, is_slot_busy, current_active_session_id FROM payment_receiving_accounts');

  assert.equal(countsAfter.merchants, 1, 'Merchant must remain');
  assert.equal(countsAfter.receivingAccounts, 1, 'Receiving account must remain');
  assert.equal(merchantAfter.id, merchantBefore.id, 'Merchant ID must match');
  assert.equal(merchantAfter.business_name, merchantBefore.business_name, 'Merchant business name must match');
  assert.equal(merchantAfter.webhook_url, merchantBefore.webhook_url, 'Merchant webhook must match');
  assert.equal(accountAfter.id, accountBefore.id, 'Receiving account ID must match');
  assert.equal(accountAfter.upi_id, accountBefore.upi_id, 'Receiving account upi_id must match');
  assert.equal(accountAfter.is_slot_busy, 0, 'Receiving slot must be free');
  assert.equal(accountAfter.current_active_session_id, null, 'Active session ID must be null');

  // Verify Acceptance Criteria
  assert.equal(countsAfter.customers, 0, 'Customers count must be 0');
  assert.equal(countsAfter.paymentSessions, 0, 'Payment sessions count must be 0');
  assert.equal(countsAfter.observations, 0, 'Observations count must be 0');
  assert.equal(countsAfter.supportTickets, 0, 'Support tickets count must be 0');
  assert.equal(countsAfter.ticketMessages, 0, 'Ticket messages count must be 0');
  assert.equal(countsAfter.webhookDeliveries, 0, 'Webhook deliveries count must be 0');

  // 5. Vacuum database to reclaim disk space
  console.log('\n[→] Vacuuming database to reclaim storage space...');
  db.exec('VACUUM;');
  console.log('[✓] Database vacuumed.');

  db.close();

  const finalStats = fs.statSync(prodDbPath);
  console.log(`[✓] Final Production Database Size: ${(finalStats.size / (1024 * 1024)).toFixed(2)} MB (reclaimed from 326 MB)`);

  console.log('============================================================');
  console.log('🎉 TARGETED PRODUCTION DATABASE RESET COMPLETED SUCCESSFULLY');
  console.log('============================================================');
}

resetProductionDatabase().catch(err => {
  console.error('CRITICAL RESET FAILURE:', err);
  process.exit(1);
});