exports.up = async function(knex) { // Create password_reset_tokens table await knex.schema.createTable('password_reset_tokens', (table) => { table.uuid('tenant').notNullable(); table.uuid('token_id').defaultTo(knex.raw('gen_random_uuid()')).notNullable(); table.uuid('user_id').notNullable(); table.text('token').notNullable(); table.text('email').notNullable(); table.enu('user_type', ['internal', 'client']).notNullable().defaultTo('internal'); table.timestamp('expires_at', { useTz: true }).notNullable(); table.timestamp('created_at', { useTz: true }).defaultTo(knex.fn.now()); table.timestamp('used_at', { useTz: true }); table.jsonb('metadata').defaultTo('{}'); // Primary key table.primary(['tenant', 'token_id']); // Foreign key constraints table.foreign('tenant').references('tenants.tenant'); table.foreign(['tenant', 'user_id']).references(['tenant', 'user_id']).inTable('users'); // Indexes for performance table.index(['tenant', 'token'], 'idx_password_reset_tokens_token'); table.index(['tenant', 'user_id'], 'idx_password_reset_tokens_user'); table.index(['tenant', 'expires_at'], 'idx_password_reset_tokens_expires'); table.index(['tenant', 'email'], 'idx_password_reset_tokens_email'); // Unique constraint on token per tenant for CitusDB compatibility table.unique(['tenant', 'token'], 'unique_password_reset_tenant_token'); }); // Check if password reset notification subtype exists let passwordResetSubtype = await knex('notification_subtypes') .where({ name: 'password-reset' }) .first(); if (!passwordResetSubtype) { // Get or create User Account category let userAccountCategory = await knex('notification_categories') .where({ name: 'User Account' }) .first(); if (!userAccountCategory) { [userAccountCategory] = await knex('notification_categories') .insert({ name: 'User Account', description: 'User account related notifications', is_enabled: true, is_default_enabled: true, created_at: new Date(), updated_at: new Date() }) .returning('*'); } // Create password reset subtype [passwordResetSubtype] = await knex('notification_subtypes') .insert({ category_id: userAccountCategory.id, name: 'password-reset', description: 'Password reset request notifications', is_enabled: true, is_default_enabled: true, created_at: new Date(), updated_at: new Date() }) .returning('*'); } // Check if email template exists const existingTemplate = await knex('system_email_templates') .where({ name: 'password-reset' }) .first(); if (!existingTemplate) { // Create email template for password reset await knex('system_email_templates').insert({ name: 'password-reset', notification_subtype_id: passwordResetSubtype.id, subject: 'Password Reset Request', html_content: ` Password Reset Request

Password Reset Request

Hello {{userName}},

We received a request to reset your password for your account associated with {{email}}.

To reset your password, please click the button below:

Reset Your Password

Or copy and paste this link into your browser:

{{resetLink}}

Important: This password reset link will expire in {{expirationTime}}. If you did not request a password reset, please ignore this email or contact support if you have concerns.

For security reasons, this link can only be used once.

`, text_content: `Hello {{userName}}, We received a request to reset your password for your account associated with {{email}}. To reset your password, please visit the following link: {{resetLink}} Important: This password reset link will expire in {{expirationTime}}. If you did not request a password reset, please ignore this email or contact support if you have concerns. For security reasons, this link can only be used once. If you're having trouble, please contact support at {{supportEmail}} © {{currentYear}} {{clientName}}. All rights reserved.`, created_at: new Date(), updated_at: new Date() }); } }; exports.down = async function(knex) { // Remove the email template await knex('system_email_templates') .where({ name: 'password-reset' }) .del(); // Remove the notification subtype await knex('notification_subtypes') .where({ name: 'password-reset' }) .del(); // Check if User Account category has other subtypes const userAccountCategory = await knex('notification_categories') .where({ name: 'User Account' }) .first(); if (userAccountCategory) { const subtypeCount = await knex('notification_subtypes') .where({ category_id: userAccountCategory.id }) .count('id as count') .first(); // If no other subtypes, delete the category if (subtypeCount && Number(subtypeCount.count) === 0) { await knex('notification_categories') .where({ name: 'User Account' }) .del(); } } // Drop the table await knex.schema.dropTableIfExists('password_reset_tokens'); };