Akihira77 / schema.prisma

// This is your Prisma schema file, // learn more about it in the docs: https://pris.ly/d/prisma-schema // Looking for ways to speed up your queries, or scale easily with your serverless or edge functions? // Try Prisma Accelerate: https://pris.ly/cli/accelerate-init generator client { provider = "prisma-client-js" previewFeatures = [] } datasource db { provider = "mysql" url = env("DATABASE_URL") } /** * Enums */ // Enums for Document enum DocumentType { Design_Development Detail_Engineering_Design For_Construction Shop_Drawing Sitemap As_Build_Drawing Work_Permit Method_Of_Work Material_Testing Site_Instruction } enum VersionStatus { IN_REVIEW APPROVED APPROVED_WITH_NOTES NEED_IMPROVEMENT REJECTED REJECTED_WITH_NOTES REJECTED_RESUBMISSION SCHEDULED // version reserved on document-service for a not-yet-sent scheduled transmittal } enum RevisionStatus { IN_REVIEW APPROVED APPROVED_WITH_NOTES NEED_IMPROVEMENT REJECTED REJECTED_WITH_NOTES REJECTED_RESUBMISSION } enum DocumentPageStatus { queue // Initial state extracting_preview // Phase 1: Extracting preview (150 DPI) preview_ready // Phase 1 complete: Preview available extracting_quality // Phase 2: Extracting full quality compressing // Optional: WebP compression ready // Final state: Full quality available } // Enums for Queue enum QueueDownloadStatus { PENDING IN_PROGRESS COMPLETED FAILED EXPIRED } enum QueueDownloadType { DIRECTORY DOCUMENT } enum QueueExtractorStatus { PENDING IN_PROGRESS COMPLETED FAILED EXPIRED } // Enums for Change Log (CDC) enum Operation { CREATE UPDATE DELETE } enum AutodeskTranslationStatus { PENDING IN_PROGRESS SUCCESS FAILED } /** * Models */ model Folder { id String @id @unique @db.VarChar(50) projectId String @db.VarChar(100) parentId String? @db.VarChar(50) modelType DocumentType? name String @db.VarChar(255) description String? @db.VarChar(500) companyId String? @db.VarChar(50) createdBy String? @db.VarChar(150) createdAt DateTime @default(now()) updatedBy String? @db.VarChar(150) updatedAt DateTime? inTrash Boolean @default(false) parent Folder? @relation("ParentChildren", fields: [parentId], references: [id], onDelete: Cascade) children Folder[] @relation("ParentChildren") documentVersions Version[] documentRevisions Revision[] @@index([projectId, parentId, modelType, companyId]) @@map("folders") } model Document { id String @id @unique @db.VarChar(50) projectId String @db.VarChar(100) companyId String? @db.VarChar(50) modelType DocumentType? filename String @db.VarChar(255) inTrash Boolean @default(false) activeVersionId String? @unique @db.VarChar(50) activeRevisionId String? @unique @db.VarChar(50) activeTransmittalId String? @db.VarChar(255) uploadedBy String? @db.VarChar(150) uploadedAt DateTime? @default(now()) updatedBy String? @db.VarChar(150) updatedAt DateTime? activeVersion Version? @relation("DocumentActiveVersion", fields: [activeVersionId], references: [id]) activeRevision Revision? @relation("DocumentActiveRevision", fields: [activeRevisionId], references: [id]) versions Version[] @relation("DocumentVersions") revisions Revision[] @relation("DocumentRevisions") relations Document[] @relation("DocumentRelation") related Document[] @relation("DocumentRelation") comments DocumentComments[] pages DocumentPages[] document3DMetadata Document3DMetadata[] @relation("Document3DMetadatas") @@index([projectId, modelType, activeTransmittalId]) @@map("documents") } model DocumentPages { id String @id @unique @db.VarChar(50) documentId String @db.VarChar(50) versionId String @db.VarChar(50) revisionId String @db.VarChar(50) queueId String? @db.VarChar(50) // Optional: tracks which queue job created this page status DocumentPageStatus @default(queue) pageOrder Int @default(0) pageName String? @db.VarChar(255) pageType String? @db.VarChar(255) pageContent String? @db.Text pageWidth Float @default(0) pageHeight Float @default(0) filePath String? @db.VarChar(512) // Increased from 255 for SHA256 hash-based paths fileSize Float @default(0) fileExtension String? @db.VarChar(10) fileMimeType String? @db.VarChar(255) // DPI tracking for two-phase extraction dpi Int @default(150) // Current DPI (150 for preview, 300-600 for quality) targetDpi Int? // Target DPI detected from PDF analysis extractionPreset String? @db.VarChar(50) // 'preview' | 'technical' | 'technical_archival' | 'graphics' // Dual file paths for preview/quality images previewPath String? @db.VarChar(512) // Increased from 255 for SHA256 hash-based paths qualityPath String? @db.VarChar(512) // Increased from 255 for SHA256 hash-based paths compressed Boolean @default(false) createdBy String? @db.VarChar(150) createdAt DateTime @default(now()) updatedBy String? @db.VarChar(150) updatedAt DateTime? @updatedAt // Relations document Document @relation(fields: [documentId], references: [id], onDelete: Cascade) version Version @relation(fields: [versionId], references: [id], onDelete: Cascade) revision Revision @relation(fields: [revisionId], references: [id], onDelete: Cascade) queue QueueExtractor? @relation(fields: [queueId], references: [id], onDelete: SetNull) @@index([documentId]) @@index([versionId]) @@index([revisionId]) @@index([queueId]) @@index([documentId, pageOrder]) @@index([status]) @@index([documentId, versionId, revisionId]) @@map("document_pages") } model DocumentComments { id String @id @unique @db.VarChar(50) parentId String? @db.VarChar(50) // For nested comments/replies documentId String @db.VarChar(50) comments String @db.Text editorData String? @db.Text // JSON data from Editor.js editorConfig String? @db.Text // Editor.js configuration createdBy String? @db.VarChar(150) createdAt DateTime @default(now()) updatedBy String? @db.VarChar(150) updatedAt DateTime? deletedBy String? @db.VarChar(150) deletedAt DateTime? // Relations document Document @relation(fields: [documentId], references: [id], onDelete: Cascade) parent DocumentComments? @relation("DocumentCommentReplies", fields: [parentId], references: [id], onDelete: Restrict) replies DocumentComments[] @relation("DocumentCommentReplies") attachments DocumentCommentAttachments[] @@index([documentId]) @@index([parentId]) @@index([createdBy]) @@index([deletedAt]) // For soft delete queries @@map("document_comments") } model DocumentCommentAttachments { id String @id @unique @db.VarChar(50) commentId String @db.VarChar(50) fileName String @db.VarChar(255) path String @db.Text extension String @db.VarChar(10) createdBy String? @db.VarChar(150) createdAt DateTime @default(now()) updatedBy String? @db.VarChar(150) updatedAt DateTime? comment DocumentComments @relation(fields: [commentId], references: [id], onDelete: Cascade) @@index([commentId]) @@map("document_comment_attachments") } // ------------------------------------- // 3D Model Metadata (Autodesk APS) // ------------------------------------- model Document3DMetadata { id String @id @unique @db.VarChar(50) documentId String @db.VarChar(50) versionId String @db.VarChar(50) revisionId String @unique @db.VarChar(50) s3Key String @db.VarChar(512) autodeskObjectId String? @db.Text autodeskUrn String? @db.Text // autodeskObjectId Base64 encoded URN untuk Viewer bucketKey String @db.VarChar(100) // Translation / Conversion Status translationStatus AutodeskTranslationStatus @default(PENDING) translationProgress String? @db.VarChar(50) translationErrorMessage String? @db.Text createdBy String? @db.VarChar(150) // uuid createdAt DateTime @default(now()) updatedBy String? @db.VarChar(150) // uuid updatedAt DateTime? @updatedAt document Document @relation(name: "Document3DMetadatas", fields: [documentId], references: [id], onDelete: Cascade) version Version @relation(name: "Version3DMetadatas", fields: [versionId], references: [id], onDelete: Cascade) revision Revision @relation(name: "Revision3DMetadata", fields: [revisionId], references: [id], onDelete: Cascade) @@index([documentId]) @@index([translationStatus]) @@map("document_3d_metadatas") } model Version { id String @id @unique @db.VarChar(50) key String @unique documentId String @db.VarChar(50) folderId String? @db.VarChar(50) version Int @default(0) filePath String @db.VarChar(512) // Increased from 255 for SHA256 hash-based paths fileSize Float fileHash Json? // { value: string, type: 'sha256', calculatedAt: ISO datetime } status VersionStatus? remark String? @db.Text description String? @db.Text companyId String? @db.VarChar(50) inTrash Boolean @default(false) transmittalId String? @db.VarChar(255) transmittalStatus String? @db.VarChar(255) transmittalTimestamp DateTime? publishedRevisionId String? @unique @db.VarChar(50) // Track which revision was published for this version archivedBy String? @db.VarChar(150) archivedAt Int? @db.UnsignedInt approvedBy String? @db.VarChar(150) approvedAt Int? @db.UnsignedInt uploadedBy String? @db.VarChar(150) uploadedAt DateTime @default(now()) updatedBy String? @db.VarChar(150) updatedAt DateTime? folder Folder? @relation(fields: [folderId], references: [id]) document Document @relation("DocumentVersions", fields: [documentId], references: [id], onDelete: Cascade) documentActive Document? @relation("DocumentActiveVersion") publishedRevision Revision? @relation("VersionPublishedRevision", fields: [publishedRevisionId], references: [id]) revisions Revision[] @relation("VersionRevisions") pages DocumentPages[] document3DMetadatas Document3DMetadata[] @relation("Version3DMetadatas") @@index([documentId, folderId, version, companyId, transmittalId]) @@map("versions") } model Revision { id String @id @unique @db.VarChar(50) key String @unique documentId String @db.VarChar(50) folderId String? @db.VarChar(50) versionId String @db.VarChar(50) // Reference to the version this revision belongs to revision Int @default(0) filePath String @db.VarChar(512) // Increased from 255 for SHA256 hash-based paths fileSize Float fileHash Json? // { value: string, type: 'sha256', calculatedAt: ISO datetime } status RevisionStatus @default(IN_REVIEW) remark String? @db.Text description String? @db.Text companyId String? @db.VarChar(50) inTrash Boolean @default(false) transmittalId String? @db.VarChar(255) transmittalStatus String? @db.VarChar(255) transmittalTimestamp DateTime? archivedBy String? @db.VarChar(150) archivedAt Int? @db.UnsignedInt approvedBy String? @db.VarChar(150) approvedAt Int? @db.UnsignedInt uploadedBy String? @db.VarChar(150) uploadedAt DateTime @default(now()) updatedBy String? @db.VarChar(150) updatedAt DateTime? folder Folder? @relation(fields: [folderId], references: [id]) version Version @relation("VersionRevisions", fields: [versionId], references: [id], onDelete: Cascade) document Document @relation("DocumentRevisions", fields: [documentId], references: [id], onDelete: Cascade) documentActive Document? @relation("DocumentActiveRevision") versionPublished Version? @relation("VersionPublishedRevision") pages DocumentPages[] document3DMetadata Document3DMetadata? @relation("Revision3DMetadata") @@index([documentId, folderId, versionId, revision, companyId, transmittalId]) @@map("revisions") } model QueueDownload { id String @id @unique @db.VarChar(50) projectId String @db.VarChar(100) companyId String? @db.VarChar(50) directoryId String? @db.VarChar(50) // Made optional for document type modelType DocumentType? // Queue type and data for flexible downloads queueType QueueDownloadType @default(DIRECTORY) queueData Json? // Store the flexible data array as JSON archiveName String? @db.VarChar(255) // Store the generated archive name status QueueDownloadStatus @default(PENDING) progress Float @default(0) // 0-100 percentage // Metadata totalSize BigInt? // Total size in bytes compressedSize BigInt? // Final compressed size in bytes totalDirectories Int? // Total directories count totalDocuments Int? // Total documents count // File paths workingPath String? @db.VarChar(500) // Temporary working directory path finalPath String? @db.VarChar(500) // Final compressed file path downloadUrl String? @db.VarChar(500) // Download URL/path // Timestamps requestedBy String? @db.VarChar(150) requestedAt DateTime @default(now()) startedAt DateTime? completedAt DateTime? expiresAt DateTime? // Auto-expire after 1 day // Error handling errorMessage String? @db.Text retryCount Int @default(0) @@index([projectId, companyId, status]) @@index([status, expiresAt]) @@index([queueType]) @@map("queue_downloads") } model QueueExtractor { id String @id @unique @db.VarChar(50) projectId String @db.VarChar(100) companyId String? @db.VarChar(50) queueType String? // Null for now as per requirement queueData Json // Store document info: { documentId, versionId, revisionId?, modelType? } status QueueExtractorStatus @default(PENDING) progress Float @default(0) // 0-100 percentage // Metadata totalSize BigInt? // Total size in bytes compressedSize BigInt? // Final compressed size in bytes (after WebP conversion) totalPages Int @default(0) // Total pages extracted // File paths workingPath String? @db.VarChar(500) // Temporary working directory path finalPath String? @db.VarChar(500) // Final output directory path // Timestamps requestedBy String? @db.VarChar(150) requestedAt DateTime @default(now()) startedAt DateTime? completedAt DateTime? // Error handling errorMessage String? @db.Text retryCount Int @default(0) // Phase tracking for two-phase extraction queuePhase String? @db.VarChar(20) // 'preview' | 'quality' detectedDpi Int? // Max DPI detected from pdfimages -list detectedType String? @db.VarChar(50) // 'technical' | 'graphics' | 'mixed' selectedPreset String? @db.VarChar(50) // Final preset used for extraction // Relations pages DocumentPages[] @@index([projectId, companyId, status]) @@index([status]) @@map("queue_extractors") } model ChangeLog { id String @id @default(cuid()) tableName String recordId String operation Operation timestamp DateTime @default(now()) processed Boolean @default(false) @@map("change_logs") } // ------------------------------------- // Metrics scope-related models // ------------------------------------- // Metrics for tracking the usage of the system model Metrics { id String @id @default(uuid()) projectId String @db.VarChar(100) companyId String? @db.VarChar(50) totalUsers Int @default(0) @map("total_users") totalDocuments Int @default(0) @map("total_documents") totalDocumentsPublish Int @default(0) @map("total_documents_publish") totalDocumentsType Json @default("{}") @map("total_documents_type") totalDocumentsPublishType Json @default("{}") @map("total_documents_publish_type") totalDocumentsTrashed Int @default(0) @map("total_documents_trashed") totalDocumentsTrashedType Json @default("{}") @map("total_documents_trashed_type") totalDocumentsExtracted Int @default(0) @map("total_documents_extracted") totalStatus Json @default("{}") @map("total_status") totalStatusType Json @default("{}") @map("total_status_type") totalStatusPublish Json @default("{}") @map("total_status_publish") totalStatusPublishType Json @default("{}") @map("total_status_publish_type") totalDirectories Int @default(0) @map("total_directories") totalDirectoriesTrashed Int @default(0) @map("total_directories_trashed") totalVersions Int @default(0) @map("total_versions") totalRevisions Int @default(0) @map("total_revisions") totalDocumentComments Int @default(0) @map("total_doc_comments") totalDocumentCommentReplies Int @default(0) @map("total_doc_comment_replies") totalDocumentCommentAttachments Int @default(0) @map("total_doc_comment_attachments") usedStorage Float @default(0.0) @map("used_storage") usedStorageFormatted String @default("0 B") @map("used_storage_formatted") @db.VarChar(100) usedStorageDetailed Json @default("{}") @map("used_storage_detailed") createdAt DateTime @default(now()) updatedAt DateTime? deletedAt DateTime? @@index([projectId, companyId]) @@index([projectId, createdAt]) @@index([projectId, updatedAt]) @@index([projectId, companyId, deletedAt]) @@map("metrics") } // ------------------------------------- // Audit log scope-related models // ------------------------------------- // Audit logs for security tracking model AuditLog { id String @id @default(uuid()) user_id String? action String // 'login', 'logout', 'create', 'update', 'delete', 'view' resource String? // table name or resource type resource_id String? old_values Json? new_values Json? ip_address String? user_agent String? location String? // geo location status String @default("success") // 'success', 'failed', 'blocked' created_at DateTime @default(now()) @@index([user_id]) @@index([action]) @@index([resource]) @@index([resource_id]) @@index([status]) @@index([created_at]) @@map("audit_logs") } // ============================================================================ // MPP Gantt Cache // Menyimpan hasil raw ekstraksi Aspose Tasks Cloud agar tidak perlu // memanggil API Aspose setiap kali view Gantt dibuka. // // Hierarki relasi: // MppSnapshot (1) ──< MppTaskItem (tasks.taskItem[]) // ──< MppTaskDetail (taskDetails[]) // ──< MppTaskLink (taskLinks[]) // ──< MppResource (resources.resourceItem[]) // ──< MppTaskAssignment (taskAssignments[].list[] — di-flatten) // ============================================================================ model MppSnapshot { id String @id @default(uuid()) @db.VarChar(50) projectId String @db.VarChar(100) fileName String @db.VarChar(255) /// Path folder di Aspose Cloud Storage (opsional) folder String? @db.VarChar(500) extractedAt DateTime @default(now()) taskItems MppTaskItem[] taskDetails MppTaskDetail[] taskLinks MppTaskLink[] resources MppResource[] taskAssignments MppTaskAssignment[] /// Satu entri cache per kombinasi projectId + fileName @@unique([projectId, fileName], name: "projectId_fileName") @@index([projectId]) @@map("mpp_snapshots") } model MppTaskItemLink { id String @id @default(uuid()) @db.VarChar(50) href String rel String type String? title String? mppTaskItems MppTaskItem[] @@map("mpp_task_item_links") } /// tasks.taskItem[] — daftar ringkas task (uid, nama, tanggal, durasi) model MppTaskItem { id String @id @default(uuid()) @db.VarChar(50) snapshotId String @db.VarChar(50) uid Int /// id (bukan uid) dari Aspose — urutan task di dalam file asposeId Int @map("aspose_id") name String @db.VarChar(500) start DateTime finish DateTime duration String @db.VarChar(50) link MppTaskItemLink @relation(fields: [mppTaskItemLinkId], references: [id]) snapshot MppSnapshot @relation(fields: [snapshotId], references: [id], onDelete: Cascade) mppTaskItemLinkId String @db.VarChar(50) @@unique([snapshotId, uid]) @@index([snapshotId]) @@map("mpp_task_items") } /// taskDetails[] — detail lengkap per task termasuk hierarki dan prioritas model MppTaskDetail { id String @id @default(uuid()) @db.VarChar(50) snapshotId String @db.VarChar(50) uid Int asposeId Int @map("aspose_id") name String @db.VarChar(500) start DateTime finish DateTime duration String @db.VarChar(50) /// 0–100 percentComplete Int @default(0) @map("percent_complete") /// Numerik Aspose: 500=Normal, 700=High, 300=Low, dst. priority Int @default(500) isSummary Boolean @default(false) @map("is_summary") isMilestone Boolean @default(false) @map("is_milestone") outlineLevel Int @default(0) @map("outline_level") outlineNumber String? @map("outline_number") @db.VarChar(50) wbs String? @db.VarChar(50) /// Array uid child task, disimpan sebagai JSON int[]. /// Kunci untuk invert parent-child di aggregator. subtasksUids Json @map("subtasks_uids") /// Objek Task lengkap dari Aspose — untuk keperluan di masa depan /// tanpa harus memanggil API lagi. rawData Json @map("raw_data") snapshot MppSnapshot @relation(fields: [snapshotId], references: [id], onDelete: Cascade) @@unique([snapshotId, uid]) @@index([snapshotId]) @@map("mpp_task_details") } /// taskLinks[] — dependency antar task (FinishToStart, dll.) model MppTaskLink { id String @id @default(uuid()) @db.VarChar(50) snapshotId String @db.VarChar(50) /// Index 1-based dari Aspose (dipakai untuk update/delete link) asposeIndex Int @map("aspose_index") predecessorUid Int @map("predecessor_uid") successorUid Int @map("successor_uid") /// "FinishToStart" | "StartToStart" | "FinishToFinish" | "StartToFinish" linkType String @map("link_type") @db.VarChar(50) lag Int @default(0) lagFormat String? @map("lag_format") @db.VarChar(50) snapshot MppSnapshot @relation(fields: [snapshotId], references: [id], onDelete: Cascade) @@unique([snapshotId, asposeIndex]) @@index([snapshotId]) @@map("mpp_task_links") } /// resources.resourceItem[] — daftar resource/orang dalam proyek model MppResource { id String @id @default(uuid()) @db.VarChar(50) snapshotId String @db.VarChar(50) uid Int asposeId Int @map("aspose_id") /// null untuk resource uid=0 (placeholder "Unassigned" bawaan Aspose) name String? @db.VarChar(500) snapshot MppSnapshot @relation(fields: [snapshotId], references: [id], onDelete: Cascade) @@unique([snapshotId, uid]) @@index([snapshotId]) @@map("mpp_resources") } /// taskAssignments[].list[] — flatten dari semua container per task. /// Menyimpan pasangan taskUid↔resourceUid untuk resolve PIC di aggregator. model MppTaskAssignment { id String @id @default(uuid()) @db.VarChar(50) snapshotId String @db.VarChar(50) /// uid assignment dari Aspose uid Int taskUid Int @map("task_uid") resourceUid Int @map("resource_uid") snapshot MppSnapshot @relation(fields: [snapshotId], references: [id], onDelete: Cascade) @@unique([snapshotId, uid]) @@index([snapshotId]) @@index([snapshotId, taskUid]) @@map("mpp_task_assignments") }