(대표님) archiver 추가, 데이터룸구현

1 files changed, 377 insertions, 0 deletions

diff --git a/db/schema/fileSystem.ts b/db/schema/fileSystem.ts
new file mode 100644
index 00000000..a66e3180
--- /dev/null
+++ b/db/schema/fileSystem.ts
@@ -0,0 +1,377 @@
+// db/schema/fileSystem.ts
+import {
+  pgTable,
+  varchar,
+  integer,
+  timestamp,
+  boolean,
+  text,
+  jsonb,
+  uuid,
+  bigint,
+  uniqueIndex,
+  index,
+  pgEnum,
+  primaryKey,
+} from "drizzle-orm/pg-core";
+import { relations } from "drizzle-orm";
+import { users } from "./users"; // 기존 users 테이블
+
+// 파일 접근 레벨 Enum
+export const fileAccessLevelEnum = pgEnum("file_access_level", [
+  "view_only",        // 열람만 가능
+  "view_download",    // 열람 + 다운로드
+  "full_access",      // 모든 권한 (내부 사용자 기본)
+]);
+
+// 파일 타입 Enum  
+export const fileTypeEnum = pgEnum("file_type", ["file", "folder"]);
+
+// 파일 카테고리 Enum (외부 사용자 접근 권한 분류)
+export const fileCategoryEnum = pgEnum("file_category", [
+  "public",          // 외부 사용자 열람 + 다운로드 가능
+  "restricted",      // 외부 사용자 열람만 가능
+  "confidential",    // 외부 사용자 접근 불가
+  "internal",        // 내부 전용
+]);
+
+// 프로젝트 테이블
+export const fileSystemProjects = pgTable("file_system_projects", {
+  id: uuid("id").primaryKey().defaultRandom(),
+  code: varchar("code", { length: 50 }).notNull(), 
+
+  name: varchar("name", { length: 255 }).notNull(),
+  description: text("description"),
+  ownerId: integer("owner_id")
+    .references(() => users.id, { onDelete: "set null" }),
+  isPublic: boolean("is_public").default(false).notNull(), // 외부 공개 여부
+  externalAccessEnabled: boolean("external_access_enabled").default(false).notNull(),
+  metadata: jsonb("metadata").default({}).notNull(),
+  createdAt: timestamp("created_at", { withTimezone: true }).defaultNow().notNull(),
+  updatedAt: timestamp("updated_at", { withTimezone: true }).defaultNow().notNull(),
+}, (table) => ({
+  ownerIdx: index("projects_owner_idx").on(table.ownerId),
+}));
+
+// 파일/폴더 테이블
+export const fileItems = pgTable("file_items", {
+  id: uuid("id").primaryKey().defaultRandom(),
+  projectId: uuid("project_id")
+    .references(() => fileSystemProjects.id, { onDelete: "cascade" })
+    .notNull(),
+  parentId: uuid("parent_id")
+    .references(() => fileItems.id, { onDelete: "cascade" }),
+  name: varchar("name", { length: 255 }).notNull(),
+  type: fileTypeEnum("type").notNull(),
+
+  // 파일 정보
+  mimeType: varchar("mime_type", { length: 255 }),
+  size: bigint("size", { mode: "number" }).default(0).notNull(),
+  filePath: text("file_path"), // S3 키 또는 로컬 경로
+  fileUrl: text("file_url"), // 직접 접근 URL (CDN 등)
+
+  // 권한 카테고리 (외부 사용자용)
+  category: fileCategoryEnum("category").default("confidential").notNull(),
+
+  // 외부 접근 설정
+  externalAccessLevel: fileAccessLevelEnum("external_access_level").default("view_only"),
+  externalAccessExpiry: timestamp("external_access_expiry", { withTimezone: true }),
+  downloadCount: integer("download_count").default(0).notNull(),
+  viewCount: integer("view_count").default(0).notNull(),
+
+  // 메타데이터
+  metadata: jsonb("metadata").default({}).notNull(),
+  tags: text("tags").array(), // 태그 배열
+
+  // 버전 관리
+  version: integer("version").default(1).notNull(),
+  previousVersionId: uuid("previous_version_id"),
+
+  // 감사 로그
+  createdBy: integer("created_by")
+    .references(() => users.id, { onDelete: "set null" }),
+  updatedBy: integer("updated_by")
+    .references(() => users.id, { onDelete: "set null" }),
+  createdAt: timestamp("created_at", { withTimezone: true }).defaultNow().notNull(),
+  updatedAt: timestamp("updated_at", { withTimezone: true }).defaultNow().notNull(),
+
+  // 경로 최적화
+  path: text("path").notNull().default("/"),
+  depth: integer("depth").notNull().default(0),
+}, (table) => ({
+  projectPathIdx: uniqueIndex("file_items_project_path_idx").on(
+    table.projectId,
+    table.path,
+    table.name
+  ),
+  parentIdx: index("file_items_parent_idx").on(table.parentId),
+  categoryIdx: index("file_items_category_idx").on(table.category),
+  createdByIdx: index("file_items_created_by_idx").on(table.createdBy),
+  tagsIdx: index("file_items_tags_idx").on(table.tags),
+}));
+
+// 파일 공유 링크 테이블
+export const fileShares = pgTable("file_shares", {
+  id: uuid("id").primaryKey().defaultRandom(),
+  fileItemId: uuid("file_item_id")
+    .references(() => fileItems.id, { onDelete: "cascade" })
+    .notNull(),
+  shareToken: varchar("share_token", { length: 64 }).notNull().unique(),
+
+  // 공유 설정
+  accessLevel: fileAccessLevelEnum("access_level").default("view_only").notNull(),
+  password: varchar("password", { length: 255 }), // 선택적 비밀번호
+  maxDownloads: integer("max_downloads"), // 최대 다운로드 횟수
+  currentDownloads: integer("current_downloads").default(0).notNull(),
+
+  // 유효기간
+  expiresAt: timestamp("expires_at", { withTimezone: true }),
+
+  // 공유 대상 (선택적)
+  sharedWithEmail: varchar("shared_with_email", { length: 255 }),
+  sharedWithUserId: integer("shared_with_user_id")
+    .references(() => users.id, { onDelete: "set null" }),
+
+  // 감사 로그
+  createdBy: integer("created_by")
+    .references(() => users.id, { onDelete: "set null" }),
+  createdAt: timestamp("created_at", { withTimezone: true }).defaultNow().notNull(),
+  lastAccessedAt: timestamp("last_accessed_at", { withTimezone: true }),
+}, (table) => ({
+  tokenIdx: uniqueIndex("file_shares_token_idx").on(table.shareToken),
+  fileIdx: index("file_shares_file_idx").on(table.fileItemId),
+  expiryIdx: index("file_shares_expiry_idx").on(table.expiresAt),
+}));
+
+// 세밀한 파일 권한 테이블 (특정 사용자/그룹에 대한 예외 권한)
+export const filePermissions = pgTable("file_permissions", {
+  id: uuid("id").primaryKey().defaultRandom(),
+  fileItemId: uuid("file_item_id")
+    .references(() => fileItems.id, { onDelete: "cascade" })
+    .notNull(),
+
+  // 대상 (사용자 또는 도메인)
+  userId: integer("user_id")
+    .references(() => users.id, { onDelete: "cascade" }),
+  userDomain: varchar("user_domain", { length: 50 }), // 'partners', 'internal' 등
+
+  // 권한
+  canView: boolean("can_view").default(true).notNull(),
+  canDownload: boolean("can_download").default(false).notNull(),
+  canEdit: boolean("can_edit").default(false).notNull(),
+  canDelete: boolean("can_delete").default(false).notNull(),
+  canShare: boolean("can_share").default(false).notNull(),
+
+  // 유효기간
+  validFrom: timestamp("valid_from", { withTimezone: true }),
+  validUntil: timestamp("valid_until", { withTimezone: true }),
+
+  // 감사 로그
+  grantedBy: integer("granted_by")
+    .references(() => users.id, { onDelete: "set null" }),
+  createdAt: timestamp("created_at", { withTimezone: true }).defaultNow().notNull(),
+  updatedAt: timestamp("updated_at", { withTimezone: true }).defaultNow().notNull(),
+}, (table) => ({
+  fileUserIdx: uniqueIndex("file_permissions_file_user_idx").on(
+    table.fileItemId,
+    table.userId
+  ),
+  fileIdx: index("file_permissions_file_idx").on(table.fileItemId),
+  userIdx: index("file_permissions_user_idx").on(table.userId),
+  domainIdx: index("file_permissions_domain_idx").on(table.userDomain),
+}));
+
+// 파일 활동 로그 테이블
+export const fileActivityLogs = pgTable("file_activity_logs", {
+  id: uuid("id").primaryKey().defaultRandom(),
+  fileItemId: uuid("file_item_id")
+    .references(() => fileItems.id, { onDelete: "cascade" })
+    .notNull(),
+  projectId: uuid("project_id")
+    .references(() => fileSystemProjects.id, { onDelete: "cascade" })
+    .notNull(),
+
+  // 활동 정보
+  action: varchar("action", { length: 50 }).notNull(), // 'view', 'download', 'upload', 'edit', 'delete', 'share'
+  actionDetails: jsonb("action_details").default({}).notNull(),
+
+  // 사용자 정보
+  userId: integer("user_id")
+    .references(() => users.id, { onDelete: "set null" }),
+  userEmail: varchar("user_email", { length: 255 }),
+  userDomain: varchar("user_domain", { length: 50 }),
+  ipAddress: varchar("ip_address", { length: 45 }),
+  userAgent: text("user_agent"),
+
+  // 공유 링크를 통한 접근인 경우
+  shareId: uuid("share_id")
+    .references(() => fileShares.id, { onDelete: "set null" }),
+
+  createdAt: timestamp("created_at", { withTimezone: true }).defaultNow().notNull(),
+}, (table) => ({
+  fileIdx: index("file_activity_logs_file_idx").on(table.fileItemId),
+  projectIdx: index("file_activity_logs_project_idx").on(table.projectId),
+  userIdx: index("file_activity_logs_user_idx").on(table.userId),
+  actionIdx: index("file_activity_logs_action_idx").on(table.action),
+  createdAtIdx: index("file_activity_logs_created_at_idx").on(table.createdAt),
+}));
+
+// Relations
+export const projectsFilesRelations = relations(fileSystemProjects, ({ one, many }) => ({
+  owner: one(users, {
+    fields: [fileSystemProjects.ownerId],
+    references: [users.id],
+  }),
+  fileItems: many(fileItems),
+}));
+
+
+export const fileItemsRelations = relations(fileItems, ({ one, many }) => ({
+  project: one(fileSystemProjects, {
+    fields: [fileItems.projectId],
+    references: [fileSystemProjects.id],
+  }),
+  parent: one(fileItems, {
+    fields: [fileItems.parentId],
+    references: [fileItems.id],
+    relationName: "parentChild",
+  }),
+  children: many(fileItems, {
+    relationName: "parentChild",
+  }),
+  createdByUser: one(users, {
+    fields: [fileItems.createdBy],
+    references: [users.id],
+    relationName: "createdFiles",
+  }),
+  updatedByUser: one(users, {
+    fields: [fileItems.updatedBy],
+    references: [users.id],
+    relationName: "updatedFiles",
+  }),
+  permissions: many(filePermissions),
+  shares: many(fileShares),
+  activityLogs: many(fileActivityLogs),
+}));
+
+export const filePermissionsRelations = relations(filePermissions, ({ one }) => ({
+  fileItem: one(fileItems, {
+    fields: [filePermissions.fileItemId],
+    references: [fileItems.id],
+  }),
+  user: one(users, {
+    fields: [filePermissions.userId],
+    references: [users.id],
+  }),
+  grantedByUser: one(users, {
+    fields: [filePermissions.grantedBy],
+    references: [users.id],
+    relationName: "grantedPermissions",
+  }),
+}));
+
+export const fileSharesRelations = relations(fileShares, ({ one }) => ({
+  fileItem: one(fileItems, {
+    fields: [fileShares.fileItemId],
+    references: [fileItems.id],
+  }),
+  createdByUser: one(users, {
+    fields: [fileShares.createdBy],
+    references: [users.id],
+  }),
+  sharedWithUser: one(users, {
+    fields: [fileShares.sharedWithUserId],
+    references: [users.id],
+    relationName: "receivedShares",
+  }),
+}));
+
+export const fileActivityLogsRelations = relations(fileActivityLogs, ({ one }) => ({
+  fileItem: one(fileItems, {
+    fields: [fileActivityLogs.fileItemId],
+    references: [fileItems.id],
+  }),
+  project: one(fileSystemProjects, {
+    fields: [fileActivityLogs.projectId],
+    references: [fileSystemProjects.id],
+  }),
+  user: one(users, {
+    fields: [fileActivityLogs.userId],
+    references: [users.id],
+  }),
+  share: one(fileShares, {
+    fields: [fileActivityLogs.shareId],
+    references: [fileShares.id],
+  }),
+}));
+
+// Type exports
+export type FileItem = typeof fileItems.$inferSelect;
+export type NewFileItem = typeof fileItems.$inferInsert;
+export type FilePermission = typeof filePermissions.$inferSelect;
+export type NewFilePermission = typeof filePermissions.$inferInsert;
+export type FileShare = typeof fileShares.$inferSelect;
+export type NewFileShare = typeof fileShares.$inferInsert;
+export type FileActivityLog = typeof fileActivityLogs.$inferSelect;
+export type NewFileActivityLog = typeof fileActivityLogs.$inferInsert;
+
+
+
+
+export type FileSystemProject = typeof fileSystemProjects.$inferSelect;
+export type NewFileSystemProject = typeof fileSystemProjects.$inferInsert;
+
+// db/schema/fileSystem.ts에 추가할 테이블
+export const projectMemberRoleEnum = pgEnum("project_member_role", [
+  "owner",
+  "admin",
+  "editor",
+  "viewer",
+]);
+
+export const projectMembers = pgTable("project_members", {
+  id: uuid("id").primaryKey().defaultRandom(),
+  projectId: uuid("project_id")
+    .references(() => fileSystemProjects.id, { onDelete: "cascade" })
+    .notNull(),
+  userId: integer("user_id")
+    .references(() => users.id, { onDelete: "cascade" })
+    .notNull(),
+  role: projectMemberRoleEnum("role").notNull().default("viewer"),
+  
+  // 권한 세부 설정 (역할 외 추가 권한)
+  canInvite: boolean("can_invite").default(false).notNull(),
+  canManageFiles: boolean("can_manage_files").default(false).notNull(),
+  canManageMembers: boolean("can_manage_members").default(false).notNull(),
+  
+  // 감사 로그
+  addedBy: integer("added_by")
+    .references(() => users.id, { onDelete: "set null" }),
+  createdAt: timestamp("created_at", { withTimezone: true }).defaultNow().notNull(),
+  updatedAt: timestamp("updated_at", { withTimezone: true }).defaultNow().notNull(),
+}, (table) => ({
+  // 한 프로젝트에 한 사용자는 하나의 역할만
+  projectUserUnique: uniqueIndex("project_members_project_user_idx").on(
+    table.projectId,
+    table.userId
+  ),
+  userIdx: index("project_members_user_idx").on(table.userId),
+  roleIdx: index("project_members_role_idx").on(table.role),
+}));
+
+// Relations 추가
+export const projectMembersRelations = relations(projectMembers, ({ one }) => ({
+  project: one(fileSystemProjects, {
+    fields: [projectMembers.projectId],
+    references: [fileSystemProjects.id],
+  }),
+  user: one(users, {
+    fields: [projectMembers.userId],
+    references: [users.id],
+  }),
+  addedByUser: one(users, {
+    fields: [projectMembers.addedBy],
+    references: [users.id],
+    relationName: "addedMembers",
+  }),
+}));
\ No newline at end of file