neo/backend/migrations/tenant/20250305000001_create_contacts_and_contact_details.js

exports.up = async function (knex) {
  await knex.schema.createTable('contacts', (table) => {
    table.uuid('id').primary().defaultTo(knex.raw('(UUID())'));
    table.string('firstName', 100).notNullable();
    table.string('lastName', 100).notNullable();
    table.uuid('accountId').notNullable();
    table.timestamps(true, true);

    table
      .foreign('accountId')
      .references('id')
      .inTable('accounts')
      .onDelete('CASCADE');
    table.index(['accountId']);
    table.index(['lastName', 'firstName']);
  });

  await knex.schema.createTable('contact_details', (table) => {
    table.uuid('id').primary().defaultTo(knex.raw('(UUID())'));
    table.string('relatedObjectType', 100).notNullable();
    table.uuid('relatedObjectId').notNullable();
    table.string('detailType', 50).notNullable();
    table.string('label', 100);
    table.text('value').notNullable();
    table.boolean('isPrimary').defaultTo(false);
    table.timestamps(true, true);

    table.index(['relatedObjectType', 'relatedObjectId']);
    table.index(['detailType']);
  });

  const [contactObjectId] = await knex('object_definitions').insert({
    id: knex.raw('(UUID())'),
    apiName: 'Contact',
    label: 'Contact',
    pluralLabel: 'Contacts',
    description: 'Standard Contact object',
    isSystem: true,
    isCustom: false,
    created_at: knex.fn.now(),
    updated_at: knex.fn.now(),
  });

  const contactObjectDefId =
    contactObjectId ||
    (await knex('object_definitions').where('apiName', 'Contact').first()).id;

  await knex('field_definitions').insert([
    {
      id: knex.raw('(UUID())'),
      objectDefinitionId: contactObjectDefId,
      apiName: 'firstName',
      label: 'First Name',
      type: 'String',
      length: 100,
      isRequired: true,
      isSystem: true,
      isCustom: false,
      displayOrder: 1,
      created_at: knex.fn.now(),
      updated_at: knex.fn.now(),
    },
    {
      id: knex.raw('(UUID())'),
      objectDefinitionId: contactObjectDefId,
      apiName: 'lastName',
      label: 'Last Name',
      type: 'String',
      length: 100,
      isRequired: true,
      isSystem: true,
      isCustom: false,
      displayOrder: 2,
      created_at: knex.fn.now(),
      updated_at: knex.fn.now(),
    },
    {
      id: knex.raw('(UUID())'),
      objectDefinitionId: contactObjectDefId,
      apiName: 'accountId',
      label: 'Account',
      type: 'Reference',
      referenceObject: 'Account',
      isRequired: true,
      isSystem: true,
      isCustom: false,
      displayOrder: 3,
      created_at: knex.fn.now(),
      updated_at: knex.fn.now(),
    },
  ]);

  const [contactDetailObjectId] = await knex('object_definitions').insert({
    id: knex.raw('(UUID())'),
    apiName: 'ContactDetail',
    label: 'Contact Detail',
    pluralLabel: 'Contact Details',
    description: 'Polymorphic contact detail object',
    isSystem: true,
    isCustom: false,
    created_at: knex.fn.now(),
    updated_at: knex.fn.now(),
  });

  const contactDetailObjectDefId =
    contactDetailObjectId ||
    (await knex('object_definitions').where('apiName', 'ContactDetail').first())
      .id;

  const contactDetailRelationObjects = ['Account', 'Contact']

  await knex('field_definitions').insert([
    {
      id: knex.raw('(UUID())'),
      objectDefinitionId: contactDetailObjectDefId,
      apiName: 'relatedObjectType',
      label: 'Related Object Type',
      type: 'PICKLIST',
      length: 100,
      isRequired: true,
      isSystem: false,
      isCustom: false,
      displayOrder: 1,
      ui_metadata: JSON.stringify({
        options: contactDetailRelationObjects.map((value) => ({ label: value, value })),
      }),
      created_at: knex.fn.now(),
      updated_at: knex.fn.now(),
    },
    {
      id: knex.raw('(UUID())'),
      objectDefinitionId: contactDetailObjectDefId,
      apiName: 'relatedObjectId',
      label: 'Related Object ID',
      type: 'LOOKUP',
      length: 36,
      isRequired: true,
      isSystem: false,
      isCustom: false,
      displayOrder: 2,
      ui_metadata: JSON.stringify({
        relationObjects: contactDetailRelationObjects,
        relationTypeField: 'relatedObjectType',
        relationDisplayField: 'name',
      }),
      created_at: knex.fn.now(),
      updated_at: knex.fn.now(),
    },
    {
      id: knex.raw('(UUID())'),
      objectDefinitionId: contactDetailObjectDefId,
      apiName: 'detailType',
      label: 'Detail Type',
      type: 'String',
      length: 50,
      isRequired: true,
      isSystem: false,
      isCustom: false,
      displayOrder: 3,
      created_at: knex.fn.now(),
      updated_at: knex.fn.now(),
    },
    {
      id: knex.raw('(UUID())'),
      objectDefinitionId: contactDetailObjectDefId,
      apiName: 'label',
      label: 'Label',
      type: 'String',
      length: 100,
      isSystem: false,
      isCustom: false,
      displayOrder: 4,
      created_at: knex.fn.now(),
      updated_at: knex.fn.now(),
    },
    {
      id: knex.raw('(UUID())'),
      objectDefinitionId: contactDetailObjectDefId,
      apiName: 'value',
      label: 'Value',
      type: 'Text',
      isRequired: true,
      isSystem: false,
      isCustom: false,
      displayOrder: 5,
      created_at: knex.fn.now(),
      updated_at: knex.fn.now(),
    },
    {
      id: knex.raw('(UUID())'),
      objectDefinitionId: contactDetailObjectDefId,
      apiName: 'isPrimary',
      label: 'Primary',
      type: 'Boolean',
      isSystem: false,
      isCustom: false,
      displayOrder: 6,
      created_at: knex.fn.now(),
      updated_at: knex.fn.now(),
    },
  ]);
};

exports.down = async function (knex) {
  await knex.schema.dropTableIfExists('contact_details');
  await knex.schema.dropTableIfExists('contacts');
};