PPPAIMT-Crest/backend/migrations/20260930120000_initial_schema.js

/**
 * @param {import('knex').Knex} knex
 */
export async function up(knex) {
  await knex.schema.createTable('admin_users', (table) => {
    table.charset('utf8mb4');
    table.collate('utf8mb4_unicode_ci');
    table.increments('id').primary();
    table.string('name', 255).notNullable();
    table.string('email', 255).notNullable().unique();
    table.string('password_hash', 255).notNullable();
    table.boolean('is_active').notNullable().defaultTo(true);
    table.datetime('last_login_at').nullable();
    table.integer('failed_login_count').notNullable().defaultTo(0);
    table.datetime('locked_until').nullable();
    table.timestamps(true, true);
  });

  await knex.schema.createTable('applications', (table) => {
    table.charset('utf8mb4');
    table.collate('utf8mb4_unicode_ci');
    table.increments('id').primary();
    table.uuid('public_id').notNullable().unique();
    table.string('email', 255).notNullable().index();
    table
      .enu('status', ['DRAFT', 'SUBMITTED', 'APPROVED', 'REJECTED'])
      .notNullable()
      .defaultTo('DRAFT')
      .index();
    table.date('agreement_date').nullable();
    table.string('full_name', 255).nullable();
    table.integer('age').nullable();
    table.text('address').nullable();
    table.string('registration_no', 64).nullable().unique();
    table.string('medical_council', 255).nullable();
    table.string('pan', 10).nullable();
    table.string('gstin', 15).nullable();
    table.string('bank_name', 255).nullable();
    table.string('account_holder', 255).nullable();
    table.string('account_number', 18).nullable();
    table.string('ifsc', 11).nullable();
    table.boolean('accepted').notNullable().defaultTo(false);
    table.text('rejection_reason').nullable();
    table.datetime('submitted_at').nullable();
    table.datetime('decided_at').nullable();
    table
      .integer('decided_by')
      .unsigned()
      .nullable()
      .references('id')
      .inTable('admin_users')
      .onDelete('SET NULL');
    table.string('draft_pdf_key', 512).nullable();
    table.string('signed_pdf_key', 512).nullable();
    table.string('final_pdf_key', 512).nullable();
    table.string('signed_pdf_sha256', 64).nullable();
    table.integer('version').notNullable().defaultTo(1);
    table.timestamps(true, true);
  });

  await knex.schema.createTable('resume_tokens', (table) => {
    table.charset('utf8mb4');
    table.collate('utf8mb4_unicode_ci');
    table.increments('id').primary();
    table
      .integer('application_id')
      .unsigned()
      .notNullable()
      .references('id')
      .inTable('applications')
      .onDelete('CASCADE');
    table.string('token_hash', 64).notNullable().unique();
    table.datetime('created_at').notNullable().defaultTo(knex.fn.now());
    table.datetime('revoked_at').nullable();
  });

  await knex.schema.createTable('signatures', (table) => {
    table.charset('utf8mb4');
    table.collate('utf8mb4_unicode_ci');
    table.increments('id').primary();
    table
      .integer('application_id')
      .unsigned()
      .notNullable()
      .references('id')
      .inTable('applications')
      .onDelete('CASCADE');
    table.integer('version').notNullable();
    table.enu('type', ['DRAWN', 'TYPED']).notNullable();
    table.string('image_key', 512).nullable();
    table.string('typed_text', 255).nullable();
    table.datetime('signed_at').notNullable();
    table.string('ip', 45).nullable();
    table.text('user_agent').nullable();
    table.datetime('voided_at').nullable();
    table.timestamps(true, true);
  });

  await knex.schema.createTable('settings', (table) => {
    table.charset('utf8mb4');
    table.collate('utf8mb4_unicode_ci');
    table.string('key', 64).primary();
    table.json('value').notNullable();
  });

  await knex.schema.createTable('audit_log', (table) => {
    table.charset('utf8mb4');
    table.collate('utf8mb4_unicode_ci');
    table.increments('id').primary();
    table.enu('actor_type', ['ADMIN', 'PHYSICIAN', 'SYSTEM']).notNullable();
    table.integer('actor_id').unsigned().nullable();
    table.string('action', 64).notNullable();
    table.string('entity_type', 64).notNullable();
    table.integer('entity_id').unsigned().nullable();
    table.json('metadata').nullable();
    table.string('ip', 45).nullable();
    table.datetime('created_at').notNullable().defaultTo(knex.fn.now());
    table.index(['entity_type', 'entity_id']);
  });

  await knex.schema.createTable('email_log', (table) => {
    table.charset('utf8mb4');
    table.collate('utf8mb4_unicode_ci');
    table.increments('id').primary();
    table
      .integer('application_id')
      .unsigned()
      .notNullable()
      .references('id')
      .inTable('applications')
      .onDelete('CASCADE');
    table.string('template', 64).notNullable();
    table.string('to_email', 255).notNullable();
    table.string('sendgrid_message_id', 255).nullable();
    table.string('status', 32).notNullable();
    table.text('error').nullable();
    table.datetime('created_at').notNullable().defaultTo(knex.fn.now());
  });

  await knex.schema.createTable('survey_definitions', (table) => {
    table.charset('utf8mb4');
    table.collate('utf8mb4_unicode_ci');
    table.increments('id').primary();
    table.integer('version').notNullable();
    table.string('title', 255).notNullable();
    table.json('definition').notNullable();
    table.boolean('is_active').notNullable().defaultTo(false);
    table.timestamps(true, true);
  });

  await knex.schema.createTable('survey_tokens', (table) => {
    table.charset('utf8mb4');
    table.collate('utf8mb4_unicode_ci');
    table.increments('id').primary();
    table
      .integer('application_id')
      .unsigned()
      .notNullable()
      .references('id')
      .inTable('applications')
      .onDelete('CASCADE');
    table.string('token_hash', 64).notNullable().unique();
    table.datetime('created_at').notNullable().defaultTo(knex.fn.now());
    table.datetime('used_at').nullable();
    table.datetime('revoked_at').nullable();
  });

  await knex.schema.createTable('survey_responses', (table) => {
    table.charset('utf8mb4');
    table.collate('utf8mb4_unicode_ci');
    table.increments('id').primary();
    table
      .integer('application_id')
      .unsigned()
      .notNullable()
      .references('id')
      .inTable('applications')
      .onDelete('CASCADE');
    table
      .integer('survey_definition_id')
      .unsigned()
      .notNullable()
      .references('id')
      .inTable('survey_definitions')
      .onDelete('RESTRICT');
    table.json('answers').notNullable();
    table.enu('status', ['IN_PROGRESS', 'COMPLETED']).notNullable().defaultTo('IN_PROGRESS');
    table.datetime('started_at').notNullable();
    table.datetime('completed_at').nullable();
    table.string('ip', 45).nullable();
    table.text('user_agent').nullable();
    table.timestamps(true, true);
  });

  await knex('settings').insert([
    { key: 'signatory_name', value: JSON.stringify('Dr Sudeep Salvi') },
    { key: 'signatory_designation', value: JSON.stringify('') },
    { key: 'signatory_signature_key', value: JSON.stringify('') },
    { key: 'mail_from_name', value: JSON.stringify('') },
  ]);
}

/**
 * @param {import('knex').Knex} knex
 */
export async function down(knex) {
  await knex.schema.dropTableIfExists('survey_responses');
  await knex.schema.dropTableIfExists('survey_tokens');
  await knex.schema.dropTableIfExists('survey_definitions');
  await knex.schema.dropTableIfExists('email_log');
  await knex.schema.dropTableIfExists('audit_log');
  await knex.schema.dropTableIfExists('signatures');
  await knex.schema.dropTableIfExists('resume_tokens');
  await knex.schema.dropTableIfExists('applications');
  await knex.schema.dropTableIfExists('settings');
  await knex.schema.dropTableIfExists('admin_users');
}