starr-philproperties/apps/api/database/migrations/20270101000040-create-task-progress.js

'use strict';

// `type` is STRING + an explicit CHECK constraint (check_type) — this
// column isn't a native enum on this DB.
module.exports = {
  async up(queryInterface, Sequelize) {
    await queryInterface.createTable('task_progress', {
      progress_id:    { type: Sequelize.UUID, defaultValue: Sequelize.UUIDV4, primaryKey: true },
      task_id:        { type: Sequelize.UUID, allowNull: false, references: { model: 'tasks',            key: 'task_id'        }, onDelete: 'CASCADE' },
      requirement_id: { type: Sequelize.UUID, allowNull: false, references: { model: 'task_requirements', key: 'requirement_id' }, onDelete: 'CASCADE' },
      user_id:        { type: Sequelize.BIGINT, allowNull: false, references: { model: 'users',           key: 'user_id'        }, onDelete: 'CASCADE' },
      reference_id:   { type: Sequelize.UUID, allowNull: false },
      type:           { type: Sequelize.STRING, allowNull: false },
      completed:      { type: Sequelize.BOOLEAN, defaultValue: false, allowNull: false },
      completed_at:   { type: Sequelize.DATE, allowNull: true },
      createdBy:      { type: Sequelize.BIGINT, allowNull: true },
      updatedBy:      { type: Sequelize.BIGINT, allowNull: true },
      createdAt:      { type: Sequelize.DATE, allowNull: false },
      updatedAt:      { type: Sequelize.DATE, allowNull: false },
    });

    await queryInterface.sequelize.query(
      `ALTER TABLE task_progress ADD CONSTRAINT check_type
       CHECK (type IN ('read_course', 'read_unit', 'read_lesson'))`
    );

    await queryInterface.addIndex('task_progress', { fields: ['requirement_id', 'user_id', 'reference_id'], unique: true, name: 'uq_tp_requirement_user_reference' });
    await queryInterface.addIndex('task_progress', { fields: ['task_id'],        name: 'idx_tp_task_id'        });
    await queryInterface.addIndex('task_progress', { fields: ['user_id'],        name: 'idx_tp_user_id'        });
    await queryInterface.addIndex('task_progress', { fields: ['requirement_id'], name: 'idx_tp_requirement_id' });
    await queryInterface.addIndex('task_progress', { fields: ['type'],           name: 'idx_tp_type'           });
  },

  async down(queryInterface) {
    await queryInterface.dropTable('task_progress');
  },
};