starr-philproperties/apps/api/database/migrations/20270101000002-create-assets.js

'use strict';

module.exports = {
  async up(queryInterface, Sequelize) {
    await queryInterface.createTable('assets', {
      asset_id:         { type: Sequelize.BIGINT, primaryKey: true, autoIncrement: true },
      uuid:             { type: Sequelize.UUID, defaultValue: Sequelize.UUIDV4, allowNull: false, unique: true },
      original_name:    { type: Sequelize.STRING(255), allowNull: false },
      display_name:     { type: Sequelize.STRING(255), allowNull: false },
      file_url:         { type: Sequelize.STRING(512), allowNull: false },
      file_size:        { type: Sequelize.BIGINT, allowNull: false },
      mime_type:        { type: Sequelize.STRING(100), allowNull: false },
      extension:        { type: Sequelize.STRING(20) },
      checksum:         { type: Sequelize.STRING(64) },
      file_type:        { type: Sequelize.ENUM('avatar', 'document', 'video', 'image', 'audio'), allowNull: false, defaultValue: 'image' },
      width:            { type: Sequelize.INTEGER },
      height:           { type: Sequelize.INTEGER },
      duration:         { type: Sequelize.FLOAT },
      resolution:       { type: Sequelize.STRING(20) },
      frame_rate:       { type: Sequelize.FLOAT },
      bitrate:          { type: Sequelize.BIGINT },
      video_codec:      { type: Sequelize.STRING(50) },
      audio_codec:      { type: Sequelize.STRING(50) },
      thumbnail_url:    { type: Sequelize.STRING(512) },
      thumbnail_storage_key: { type: Sequelize.STRING(512), allowNull: true },
      description:      { type: Sequelize.TEXT },
      storage_provider: { type: Sequelize.ENUM('local', 's3', 'gcs', 'cloudinary', 'chibisafe', 'other'), defaultValue: 'local' },
      storage_bucket:   { type: Sequelize.STRING(255) },
      storage_key:      { type: Sequelize.STRING(512) },
      is_public:        { type: Sequelize.BOOLEAN, defaultValue: false },
      createdBy:        { type: Sequelize.BIGINT, allowNull: true },
      updatedBy:        { type: Sequelize.BIGINT, allowNull: true },
      deletedBy:        { type: Sequelize.BIGINT, allowNull: true },
      createdAt:        { type: Sequelize.DATE, allowNull: false },
      updatedAt:        { type: Sequelize.DATE, allowNull: false },
      deletedAt:        { type: Sequelize.DATE, allowNull: true },
    });

    await queryInterface.addIndex('assets', ['uuid']);
    await queryInterface.addIndex('assets', ['createdBy']);
    await queryInterface.addIndex('assets', ['file_type']);
    await queryInterface.addIndex('assets', ['deletedAt']);
  },

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