225 lines
9.4 KiB
MySQL
225 lines
9.4 KiB
MySQL
|
|
-- CreateTable
|
||
|
|
CREATE TABLE `User` (
|
||
|
|
`id` VARCHAR(191) NOT NULL,
|
||
|
|
`email` VARCHAR(191) NOT NULL,
|
||
|
|
`username` VARCHAR(191) NOT NULL,
|
||
|
|
`fullName` VARCHAR(191) NOT NULL,
|
||
|
|
`passwordHash` VARCHAR(191) NOT NULL,
|
||
|
|
`role` ENUM('SUPER_ADMIN', 'ADMIN', 'USER') NOT NULL DEFAULT 'USER',
|
||
|
|
`status` ENUM('ACTIVE', 'SUSPENDED', 'PENDING') NOT NULL DEFAULT 'ACTIVE',
|
||
|
|
`storageQuotaBytes` BIGINT NOT NULL DEFAULT 5368709120,
|
||
|
|
`storageUsedBytes` BIGINT NOT NULL DEFAULT 0,
|
||
|
|
`createdAt` DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3),
|
||
|
|
`updatedAt` DATETIME(3) NOT NULL,
|
||
|
|
`lastLoginAt` DATETIME(3) NULL,
|
||
|
|
|
||
|
|
UNIQUE INDEX `User_email_key`(`email`),
|
||
|
|
UNIQUE INDEX `User_username_key`(`username`),
|
||
|
|
INDEX `User_role_idx`(`role`),
|
||
|
|
INDEX `User_status_idx`(`status`),
|
||
|
|
PRIMARY KEY (`id`)
|
||
|
|
) DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
|
||
|
|
|
||
|
|
-- CreateTable
|
||
|
|
CREATE TABLE `Folder` (
|
||
|
|
`id` VARCHAR(191) NOT NULL,
|
||
|
|
`ownerId` VARCHAR(191) NOT NULL,
|
||
|
|
`parentId` VARCHAR(191) NULL,
|
||
|
|
`name` VARCHAR(191) NOT NULL,
|
||
|
|
`logicalPath` VARCHAR(191) NOT NULL,
|
||
|
|
`createdAt` DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3),
|
||
|
|
`updatedAt` DATETIME(3) NOT NULL,
|
||
|
|
|
||
|
|
INDEX `Folder_ownerId_idx`(`ownerId`),
|
||
|
|
INDEX `Folder_parentId_idx`(`parentId`),
|
||
|
|
UNIQUE INDEX `Folder_ownerId_parentId_name_key`(`ownerId`, `parentId`, `name`),
|
||
|
|
PRIMARY KEY (`id`)
|
||
|
|
) DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
|
||
|
|
|
||
|
|
-- CreateTable
|
||
|
|
CREATE TABLE `FileAsset` (
|
||
|
|
`id` VARCHAR(191) NOT NULL,
|
||
|
|
`ownerId` VARCHAR(191) NOT NULL,
|
||
|
|
`folderId` VARCHAR(191) NULL,
|
||
|
|
`originalFilename` VARCHAR(191) NOT NULL,
|
||
|
|
`internalStorageKey` VARCHAR(191) NOT NULL,
|
||
|
|
`mimeType` VARCHAR(191) NOT NULL,
|
||
|
|
`fileExtension` VARCHAR(191) NOT NULL,
|
||
|
|
`fileSizeBytes` BIGINT NOT NULL,
|
||
|
|
`fileCategory` ENUM('VIDEO', 'IMAGE', 'DOCUMENT', 'ARCHIVE', 'AUDIO', 'OTHER') NOT NULL,
|
||
|
|
`checksum` VARCHAR(191) NULL,
|
||
|
|
`status` ENUM('PENDING', 'UPLOADING', 'PROCESSING', 'READY', 'FAILED') NOT NULL DEFAULT 'PENDING',
|
||
|
|
`durationSeconds` DOUBLE NULL,
|
||
|
|
`width` INTEGER NULL,
|
||
|
|
`height` INTEGER NULL,
|
||
|
|
`videoCodec` VARCHAR(191) NULL,
|
||
|
|
`audioCodec` VARCHAR(191) NULL,
|
||
|
|
`thumbnailStorageKey` VARCHAR(191) NULL,
|
||
|
|
`hlsMasterPlaylistKey` VARCHAR(191) NULL,
|
||
|
|
`isSoftDeleted` BOOLEAN NOT NULL DEFAULT false,
|
||
|
|
`deletedAt` DATETIME(3) NULL,
|
||
|
|
`createdAt` DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3),
|
||
|
|
`updatedAt` DATETIME(3) NOT NULL,
|
||
|
|
|
||
|
|
UNIQUE INDEX `FileAsset_internalStorageKey_key`(`internalStorageKey`),
|
||
|
|
INDEX `FileAsset_ownerId_idx`(`ownerId`),
|
||
|
|
INDEX `FileAsset_folderId_idx`(`folderId`),
|
||
|
|
INDEX `FileAsset_fileCategory_idx`(`fileCategory`),
|
||
|
|
INDEX `FileAsset_status_idx`(`status`),
|
||
|
|
INDEX `FileAsset_isSoftDeleted_idx`(`isSoftDeleted`),
|
||
|
|
PRIMARY KEY (`id`)
|
||
|
|
) DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
|
||
|
|
|
||
|
|
-- CreateTable
|
||
|
|
CREATE TABLE `FilePermission` (
|
||
|
|
`id` VARCHAR(191) NOT NULL,
|
||
|
|
`resourceType` ENUM('FILE', 'FOLDER', 'USER_FILES') NOT NULL,
|
||
|
|
`resourceId` VARCHAR(191) NULL,
|
||
|
|
`ownerId` VARCHAR(191) NOT NULL,
|
||
|
|
`granteeUserId` VARCHAR(191) NOT NULL,
|
||
|
|
`grantedByUserId` VARCHAR(191) NOT NULL,
|
||
|
|
`canViewMetadata` BOOLEAN NOT NULL DEFAULT true,
|
||
|
|
`canPreview` BOOLEAN NOT NULL DEFAULT false,
|
||
|
|
`canPlay` BOOLEAN NOT NULL DEFAULT false,
|
||
|
|
`canDownload` BOOLEAN NOT NULL DEFAULT false,
|
||
|
|
`canListFolder` BOOLEAN NOT NULL DEFAULT false,
|
||
|
|
`expiresAt` DATETIME(3) NULL,
|
||
|
|
`revokedAt` DATETIME(3) NULL,
|
||
|
|
`createdAt` DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3),
|
||
|
|
`updatedAt` DATETIME(3) NOT NULL,
|
||
|
|
|
||
|
|
INDEX `FilePermission_granteeUserId_idx`(`granteeUserId`),
|
||
|
|
INDEX `FilePermission_ownerId_idx`(`ownerId`),
|
||
|
|
INDEX `FilePermission_resourceType_resourceId_idx`(`resourceType`, `resourceId`),
|
||
|
|
INDEX `FilePermission_revokedAt_expiresAt_idx`(`revokedAt`, `expiresAt`),
|
||
|
|
PRIMARY KEY (`id`)
|
||
|
|
) DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
|
||
|
|
|
||
|
|
-- CreateTable
|
||
|
|
CREATE TABLE `UploadSession` (
|
||
|
|
`id` VARCHAR(191) NOT NULL,
|
||
|
|
`ownerId` VARCHAR(191) NOT NULL,
|
||
|
|
`folderId` VARCHAR(191) NULL,
|
||
|
|
`originalFilename` VARCHAR(191) NOT NULL,
|
||
|
|
`mimeType` VARCHAR(191) NOT NULL,
|
||
|
|
`fileCategory` ENUM('VIDEO', 'IMAGE', 'DOCUMENT', 'ARCHIVE', 'AUDIO', 'OTHER') NOT NULL,
|
||
|
|
`totalSizeBytes` BIGINT NOT NULL,
|
||
|
|
`uploadedSizeBytes` BIGINT NOT NULL DEFAULT 0,
|
||
|
|
`tempStorageKey` VARCHAR(191) NOT NULL,
|
||
|
|
`status` ENUM('INITIALIZED', 'UPLOADING', 'COMPLETED', 'ABORTED', 'EXPIRED') NOT NULL DEFAULT 'INITIALIZED',
|
||
|
|
`expiresAt` DATETIME(3) NOT NULL,
|
||
|
|
`createdAt` DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3),
|
||
|
|
`updatedAt` DATETIME(3) NOT NULL,
|
||
|
|
|
||
|
|
UNIQUE INDEX `UploadSession_tempStorageKey_key`(`tempStorageKey`),
|
||
|
|
INDEX `UploadSession_ownerId_idx`(`ownerId`),
|
||
|
|
INDEX `UploadSession_status_idx`(`status`),
|
||
|
|
INDEX `UploadSession_expiresAt_idx`(`expiresAt`),
|
||
|
|
PRIMARY KEY (`id`)
|
||
|
|
) DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
|
||
|
|
|
||
|
|
-- CreateTable
|
||
|
|
CREATE TABLE `VideoProcessingJob` (
|
||
|
|
`id` VARCHAR(191) NOT NULL,
|
||
|
|
`fileAssetId` VARCHAR(191) NOT NULL,
|
||
|
|
`status` ENUM('QUEUED', 'PROCESSING', 'COMPLETED', 'FAILED') NOT NULL DEFAULT 'QUEUED',
|
||
|
|
`progress` INTEGER NOT NULL DEFAULT 0,
|
||
|
|
`attempts` INTEGER NOT NULL DEFAULT 0,
|
||
|
|
`errorReason` TEXT NULL,
|
||
|
|
`createdAt` DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3),
|
||
|
|
`updatedAt` DATETIME(3) NOT NULL,
|
||
|
|
`completedAt` DATETIME(3) NULL,
|
||
|
|
|
||
|
|
INDEX `VideoProcessingJob_fileAssetId_idx`(`fileAssetId`),
|
||
|
|
INDEX `VideoProcessingJob_status_idx`(`status`),
|
||
|
|
PRIMARY KEY (`id`)
|
||
|
|
) DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
|
||
|
|
|
||
|
|
-- CreateTable
|
||
|
|
CREATE TABLE `RefreshToken` (
|
||
|
|
`id` VARCHAR(191) NOT NULL,
|
||
|
|
`tokenHash` VARCHAR(191) NOT NULL,
|
||
|
|
`userId` VARCHAR(191) NOT NULL,
|
||
|
|
`userAgent` VARCHAR(191) NULL,
|
||
|
|
`ipAddress` VARCHAR(191) NULL,
|
||
|
|
`expiresAt` DATETIME(3) NOT NULL,
|
||
|
|
`revokedAt` DATETIME(3) NULL,
|
||
|
|
`createdAt` DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3),
|
||
|
|
|
||
|
|
UNIQUE INDEX `RefreshToken_tokenHash_key`(`tokenHash`),
|
||
|
|
INDEX `RefreshToken_userId_idx`(`userId`),
|
||
|
|
INDEX `RefreshToken_expiresAt_idx`(`expiresAt`),
|
||
|
|
PRIMARY KEY (`id`)
|
||
|
|
) DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
|
||
|
|
|
||
|
|
-- CreateTable
|
||
|
|
CREATE TABLE `AuditLog` (
|
||
|
|
`id` VARCHAR(191) NOT NULL,
|
||
|
|
`actorUserId` VARCHAR(191) NULL,
|
||
|
|
`action` VARCHAR(191) NOT NULL,
|
||
|
|
`resourceType` VARCHAR(191) NOT NULL,
|
||
|
|
`resourceId` VARCHAR(191) NULL,
|
||
|
|
`targetUserId` VARCHAR(191) NULL,
|
||
|
|
`ipAddress` VARCHAR(191) NULL,
|
||
|
|
`userAgent` VARCHAR(191) NULL,
|
||
|
|
`detailsJson` TEXT NULL,
|
||
|
|
`createdAt` DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3),
|
||
|
|
|
||
|
|
INDEX `AuditLog_actorUserId_idx`(`actorUserId`),
|
||
|
|
INDEX `AuditLog_action_idx`(`action`),
|
||
|
|
INDEX `AuditLog_resourceType_resourceId_idx`(`resourceType`, `resourceId`),
|
||
|
|
INDEX `AuditLog_createdAt_idx`(`createdAt`),
|
||
|
|
PRIMARY KEY (`id`)
|
||
|
|
) DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
|
||
|
|
|
||
|
|
-- CreateTable
|
||
|
|
CREATE TABLE `SystemSetting` (
|
||
|
|
`id` VARCHAR(191) NOT NULL,
|
||
|
|
`key` VARCHAR(191) NOT NULL,
|
||
|
|
`value` TEXT NOT NULL,
|
||
|
|
`description` VARCHAR(191) NULL,
|
||
|
|
`updatedAt` DATETIME(3) NOT NULL,
|
||
|
|
|
||
|
|
UNIQUE INDEX `SystemSetting_key_key`(`key`),
|
||
|
|
PRIMARY KEY (`id`)
|
||
|
|
) DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
|
||
|
|
|
||
|
|
-- AddForeignKey
|
||
|
|
ALTER TABLE `Folder` ADD CONSTRAINT `Folder_ownerId_fkey` FOREIGN KEY (`ownerId`) REFERENCES `User`(`id`) ON DELETE CASCADE ON UPDATE CASCADE;
|
||
|
|
|
||
|
|
-- AddForeignKey
|
||
|
|
ALTER TABLE `Folder` ADD CONSTRAINT `Folder_parentId_fkey` FOREIGN KEY (`parentId`) REFERENCES `Folder`(`id`) ON DELETE CASCADE ON UPDATE CASCADE;
|
||
|
|
|
||
|
|
-- AddForeignKey
|
||
|
|
ALTER TABLE `FileAsset` ADD CONSTRAINT `FileAsset_ownerId_fkey` FOREIGN KEY (`ownerId`) REFERENCES `User`(`id`) ON DELETE CASCADE ON UPDATE CASCADE;
|
||
|
|
|
||
|
|
-- AddForeignKey
|
||
|
|
ALTER TABLE `FileAsset` ADD CONSTRAINT `FileAsset_folderId_fkey` FOREIGN KEY (`folderId`) REFERENCES `Folder`(`id`) ON DELETE SET NULL ON UPDATE CASCADE;
|
||
|
|
|
||
|
|
-- AddForeignKey
|
||
|
|
ALTER TABLE `FilePermission` ADD CONSTRAINT `FilePermission_ownerId_fkey` FOREIGN KEY (`ownerId`) REFERENCES `User`(`id`) ON DELETE CASCADE ON UPDATE CASCADE;
|
||
|
|
|
||
|
|
-- AddForeignKey
|
||
|
|
ALTER TABLE `FilePermission` ADD CONSTRAINT `FilePermission_granteeUserId_fkey` FOREIGN KEY (`granteeUserId`) REFERENCES `User`(`id`) ON DELETE CASCADE ON UPDATE CASCADE;
|
||
|
|
|
||
|
|
-- AddForeignKey
|
||
|
|
ALTER TABLE `FilePermission` ADD CONSTRAINT `FilePermission_grantedByUserId_fkey` FOREIGN KEY (`grantedByUserId`) REFERENCES `User`(`id`) ON DELETE CASCADE ON UPDATE CASCADE;
|
||
|
|
|
||
|
|
-- AddForeignKey
|
||
|
|
ALTER TABLE `UploadSession` ADD CONSTRAINT `UploadSession_ownerId_fkey` FOREIGN KEY (`ownerId`) REFERENCES `User`(`id`) ON DELETE CASCADE ON UPDATE CASCADE;
|
||
|
|
|
||
|
|
-- AddForeignKey
|
||
|
|
ALTER TABLE `UploadSession` ADD CONSTRAINT `UploadSession_folderId_fkey` FOREIGN KEY (`folderId`) REFERENCES `Folder`(`id`) ON DELETE SET NULL ON UPDATE CASCADE;
|
||
|
|
|
||
|
|
-- AddForeignKey
|
||
|
|
ALTER TABLE `VideoProcessingJob` ADD CONSTRAINT `VideoProcessingJob_fileAssetId_fkey` FOREIGN KEY (`fileAssetId`) REFERENCES `FileAsset`(`id`) ON DELETE CASCADE ON UPDATE CASCADE;
|
||
|
|
|
||
|
|
-- AddForeignKey
|
||
|
|
ALTER TABLE `RefreshToken` ADD CONSTRAINT `RefreshToken_userId_fkey` FOREIGN KEY (`userId`) REFERENCES `User`(`id`) ON DELETE CASCADE ON UPDATE CASCADE;
|
||
|
|
|
||
|
|
-- AddForeignKey
|
||
|
|
ALTER TABLE `AuditLog` ADD CONSTRAINT `AuditLog_actorUserId_fkey` FOREIGN KEY (`actorUserId`) REFERENCES `User`(`id`) ON DELETE SET NULL ON UPDATE CASCADE;
|
||
|
|
|
||
|
|
-- AddForeignKey
|
||
|
|
ALTER TABLE `AuditLog` ADD CONSTRAINT `AuditLog_targetUserId_fkey` FOREIGN KEY (`targetUserId`) REFERENCES `User`(`id`) ON DELETE SET NULL ON UPDATE CASCADE;
|