summaryrefslogtreecommitdiff
path: root/db/schema
diff options
context:
space:
mode:
Diffstat (limited to 'db/schema')
-rw-r--r--db/schema/contract.ts49
-rw-r--r--db/schema/rfq.ts13
-rw-r--r--db/schema/vendorData.ts321
3 files changed, 192 insertions, 191 deletions
diff --git a/db/schema/contract.ts b/db/schema/contract.ts
index 3c29f0d0..10721b4d 100644
--- a/db/schema/contract.ts
+++ b/db/schema/contract.ts
@@ -135,31 +135,31 @@ export const contractEnvelopes = pgTable("contract_envelopes", {
// 하나의 Envelope에 여러 서명자(사인 요청 대상)가 있을 수 있음
export const contractSigners = pgTable("contract_signers", {
id: integer("id").primaryKey().generatedAlwaysAsIdentity(),
-
+
// Envelope와 1:N 관계
envelopeId: integer("envelope_id")
.notNull()
.references(() => contractEnvelopes.id, { onDelete: "cascade" }),
-
+
// Reference to vendor_contacts table (optional - if signer is from vendor contacts)
vendorContactId: integer("vendor_contact_id")
.references(() => vendorContacts.id),
-
+
// Is this signer from the requester (company) side or vendor side
- signerType: varchar("signer_type", {
+ signerType: varchar("signer_type", {
length: 20,
enum: ["REQUESTER", "VENDOR"]
}).notNull().default("VENDOR"),
-
+
// 서명자 정보 (manual entry or populated from vendor contact)
signerEmail: varchar("signer_email", { length: 255 }).notNull(),
signerName: varchar("signer_name", { length: 100 }).notNull(),
signerPosition: varchar("signer_position", { length: 100 }),
-
+
// 서명자별 상태 (sent, delivered, signed, declined, etc.)
signerStatus: varchar("signer_status", { length: 50 }).default("PENDING"),
signedAt: timestamp("signed_at"),
-
+
// 생성/수정 시각
createdAt: timestamp("created_at").defaultNow().notNull(),
updatedAt: timestamp("updated_at").defaultNow().notNull(),
@@ -210,13 +210,46 @@ export const contractsDetailView = pgView("contracts_detail_view").as((qb) => {
// Timestamps
createdAt: contracts.createdAt,
updatedAt: contracts.updatedAt,
-
+
// Electronic signature status - ADDED .as('has_signature') here
hasSignature: sql<boolean>`EXISTS (
SELECT 1
FROM ${contractEnvelopes}
WHERE ${contractEnvelopes.contractId} = ${contracts.id}
)`.as('has_signature'),
+ // --- 전자서명 이력 (Envelope) + 서명자(Signer)를 JSON 으로 중첩한 배열 ---
+ envelopes: sql<string>`COALESCE((
+ SELECT json_agg(
+ json_build_object(
+ 'id', ce.id,
+ 'envelopeId', ce.envelope_id,
+ 'documentId', ce.document_id,
+ 'envelopeStatus', ce.envelope_status,
+ 'fileName', ce.file_name,
+ 'filePath', ce.file_path,
+ 'createdAt', ce.created_at,
+ 'updatedAt', ce.updated_at,
+ 'signers', (
+ SELECT json_agg(
+ json_build_object(
+ 'id', cs.id,
+ 'vendorContactId', cs.vendor_contact_id,
+ 'signerType', cs.signer_type,
+ 'signerEmail', cs.signer_email,
+ 'signerName', cs.signer_name,
+ 'signerPosition', cs.signer_position,
+ 'signerStatus', cs.signer_status,
+ 'signedAt', cs.signed_at
+ )
+ )
+ FROM ${contractSigners} AS cs
+ WHERE cs.envelope_id = ce.id
+ )
+ )
+ )
+ FROM ${contractEnvelopes} AS ce
+ WHERE ce.contract_id = ${contracts.id}
+ ), '[]')`.as("envelopes")
})
.from(contracts)
.leftJoin(projects, eq(contracts.projectId, projects.id))
diff --git a/db/schema/rfq.ts b/db/schema/rfq.ts
index fe5581ce..98c4245c 100644
--- a/db/schema/rfq.ts
+++ b/db/schema/rfq.ts
@@ -81,6 +81,9 @@ export const rfqAttachments = pgTable("rfq_attachments", {
evaluationId: integer("evaluation_id")
.references(() => rfqEvaluations.id)
.$type<number | null>(),
+ cbeId: integer("cbe_id")
+ .references(() => cbeEvaluations.id)
+ .$type<number | null>(),
createdAt: timestamp("created_at").defaultNow().notNull(),
commentId: integer("comment_id")
.references(() => rfqComments.id)
@@ -92,11 +95,15 @@ export const rfqComments = pgTable("rfq_comments", {
rfqId: integer("rfq_id").references(() => rfqs.id),
vendorId: integer("vendor_id").references(() => vendors.id),
commentText: text("comment_text").notNull(),
- commentedBy: integer("commented_by").notNull(),
+ commentedBy: integer("commented_by")
+ .references(() => users.id) // users 테이블의 id 필드 참조 추가
+ .notNull(),
// 아래처럼, 평가(TBE/CBE)에 속한 코멘트인지 여부를 구분할 필드
- evaluationId: integer("evaluation_id").references(() => rfqEvaluations.id)
+ evaluationId: integer("evaluation_id")
+ .references(() => rfqEvaluations.id)
.$type<number | null>(),
- cbeId: integer("evaluation_id").references(() => cbeEvaluations.id)
+ cbeId: integer("cbe_id")
+ .references(() => cbeEvaluations.id)
.$type<number | null>(),
createdAt: timestamp("created_at").defaultNow().notNull(),
updatedAt: timestamp("updated_at").defaultNow().notNull(),
diff --git a/db/schema/vendorData.ts b/db/schema/vendorData.ts
index 048e5ef8..92a92c8e 100644
--- a/db/schema/vendorData.ts
+++ b/db/schema/vendorData.ts
@@ -3,41 +3,32 @@ import {
text,
varchar,
timestamp,
- integer,
- numeric,
- date,
- unique,
- serial,
- jsonb,
- uniqueIndex,
- } from "drizzle-orm/pg-core";
- import { contractItems } from "./contract";
-
- export const forms = pgTable(
- "forms",
- {
- id: integer("id").primaryKey().generatedAlwaysAsIdentity(),
- contractItemId: integer("contract_item_id")
+ integer, numeric, date, unique, serial, jsonb, uniqueIndex
+} from "drizzle-orm/pg-core"
+import { contractItems } from "./contract"
+
+export const forms = pgTable("forms", {
+ id: integer("id").primaryKey().generatedAlwaysAsIdentity(),
+ contractItemId: integer("contract_item_id")
.notNull()
.references(() => contractItems.id, { onDelete: "cascade" }),
- formCode: varchar("form_code", { length: 100 }).notNull(),
- formName: varchar("form_name", { length: 255 }).notNull(),
- // tagType: varchar("tag_type", { length: 50 }).notNull(),
- // class: varchar("class", { length: 100 }).notNull(),
- createdAt: timestamp("created_at").defaultNow().notNull(),
- updatedAt: timestamp("updated_at").defaultNow().notNull(),
- },
- (table) => {
- return {
+ formCode: varchar("form_code", { length: 100 }).notNull(),
+ formName: varchar("form_name", { length: 255 }).notNull(),
+ // tagType: varchar("tag_type", { length: 50 }).notNull(),
+ // class: varchar("class", { length: 100 }).notNull(),
+ createdAt: timestamp("created_at").defaultNow().notNull(),
+ updatedAt: timestamp("updated_at").defaultNow().notNull(),
+}, (table) => {
+ return {
// contractItemId와 formCode의 조합을 유니크하게 설정
- contractItemFormCodeUnique: uniqueIndex(
- "contract_item_form_code_unique"
- ).on(table.contractItemId, table.formCode),
- };
+ contractItemFormCodeUnique: uniqueIndex("contract_item_form_code_unique").on(
+ table.contractItemId,
+ table.formCode
+ ),
}
- );
-
- export const rfqAttachments = pgTable("form_templates", {
+})
+
+export const rfqAttachments = pgTable("form_templates", {
id: serial("id").primaryKey(),
formId: integer("form_id").references(() => forms.id),
fileName: varchar("file_name", { length: 255 }).notNull(),
@@ -45,204 +36,175 @@ import {
createdAt: timestamp("created_at").defaultNow().notNull(),
udpatedAt: timestamp("updated_at").defaultNow().notNull(),
- });
- export const formMetas = pgTable("form_metas", {
+});
+
+
+export const formMetas = pgTable("form_metas", {
id: serial("id").primaryKey(),
formCode: varchar("form_code", { length: 50 }).notNull(),
formName: varchar("form_name", { length: 255 }).notNull(),
columns: jsonb("columns").notNull(),
- createdAt: timestamp("created_at", { withTimezone: true })
- .defaultNow()
- .notNull(),
- updatedAt: timestamp("updated_at", { withTimezone: true })
- .defaultNow()
- .notNull(),
- });
-
- export const formEntries = pgTable("form_entries", {
+ createdAt: timestamp("created_at", { withTimezone: true }).defaultNow().notNull(),
+ updatedAt: timestamp("updated_at", { withTimezone: true }).defaultNow().notNull(),
+})
+
+export const formEntries = pgTable("form_entries", {
id: serial("id").primaryKey(),
formCode: varchar("form_code", { length: 50 }).notNull(),
data: jsonb("data").notNull(),
contractItemId: integer("contract_item_id")
- .notNull()
- .references(() => contractItems.id, { onDelete: "cascade" }),
- createdAt: timestamp("created_at", { withTimezone: true })
- .defaultNow()
- .notNull(),
- updatedAt: timestamp("updated_at", { withTimezone: true })
- .defaultNow()
- .notNull(),
- });
-
- // ============ tags (각 계약 아이템에 대한 Tag) ============
- // "어느 계약의 어느 아이템에 대한 태그"임을 나타내려면 contract_items를 참조
- export const tags = pgTable("tags", {
+ .notNull()
+ .references(() => contractItems.id, { onDelete: "cascade" }),
+ createdAt: timestamp("created_at", { withTimezone: true }).defaultNow().notNull(),
+ updatedAt: timestamp("updated_at", { withTimezone: true }).defaultNow().notNull(),
+})
+
+
+// ============ tags (각 계약 아이템에 대한 Tag) ============
+// "어느 계약의 어느 아이템에 대한 태그"임을 나타내려면 contract_items를 참조
+export const tags = pgTable("tags", {
id: integer("id").primaryKey().generatedAlwaysAsIdentity(),
-
+
// 이 Tag가 속한 "계약 내 아이템" (즉 contract_items.id)
contractItemId: integer("contract_item_id")
- .notNull()
- .references(() => contractItems.id, { onDelete: "cascade" }),
-
- formId: integer("form_id").references(() => forms.id, {
- onDelete: "set null",
- }),
-
+ .notNull()
+ .references(() => contractItems.id, { onDelete: "cascade" }),
+
+ formId: integer("form_id")
+ .references(() => forms.id, { onDelete: "set null" }),
+
tagNo: varchar("tag_no", { length: 100 }).notNull(),
tagType: varchar("tag_type", { length: 50 }).notNull(),
class: varchar("class", { length: 100 }).notNull(),
description: text("description"),
-
+
createdAt: timestamp("created_at").defaultNow().notNull(),
updatedAt: timestamp("updated_at").defaultNow().notNull(),
- });
-
- export type Tag = typeof tags.$inferSelect;
- export type Form = typeof forms.$inferSelect;
- export type NewTag = typeof tags.$inferInsert;
-
- export const tagTypes = pgTable("tag_types", {
+})
+
+export type Tag = typeof tags.$inferSelect
+export type Form = typeof forms.$inferSelect
+export type NewTag = typeof tags.$inferInsert
+
+export const tagTypes = pgTable("tag_types", {
code: varchar("code", { length: 50 }).primaryKey(),
description: text("description").notNull(),
-
- createdAt: timestamp("created_at", { withTimezone: true })
- .defaultNow()
- .notNull(),
- updatedAt: timestamp("updated_at", { withTimezone: true })
- .defaultNow()
- .notNull(),
- });
-
- export const tagSubfields = pgTable(
- "tag_subfields",
- {
- id: serial("id").primaryKey(),
-
- // 외래키: tagTypeCode -> tagTypes.code
- tagTypeCode: varchar("tag_type_code", { length: 50 })
+
+ createdAt: timestamp("created_at", { withTimezone: true }).defaultNow().notNull(),
+ updatedAt: timestamp("updated_at", { withTimezone: true }).defaultNow().notNull(),
+})
+
+export const tagSubfields = pgTable("tag_subfields", {
+ id: serial("id").primaryKey(),
+
+ // 외래키: tagTypeCode -> tagTypes.code
+ tagTypeCode: varchar("tag_type_code", { length: 50 })
.notNull()
.references(() => tagTypes.code, { onDelete: "cascade" }),
-
- /**
- * 나머지 필드
- */
- // tagTypeDescription: -> 이제 불필요. tagTypes.description로 join
- attributesId: varchar("attributes_id", { length: 50 }).notNull(),
- attributesDescription: text("attributes_description").notNull(),
-
- expression: text("expression"),
- delimiter: varchar("delimiter", { length: 10 }),
-
- sortOrder: integer("sort_order").default(0).notNull(),
-
- createdAt: timestamp("created_at", { withTimezone: true })
- .defaultNow()
- .notNull(),
- updatedAt: timestamp("updated_at", { withTimezone: true })
- .defaultNow()
- .notNull(),
- },
- (table) => {
- return {
+
+ /**
+ * 나머지 필드
+ */
+ // tagTypeDescription: -> 이제 불필요. tagTypes.description로 join
+ attributesId: varchar("attributes_id", { length: 50 }).notNull(),
+ attributesDescription: text("attributes_description").notNull(),
+
+ expression: text("expression"),
+ delimiter: varchar("delimiter", { length: 10 }),
+
+ sortOrder: integer("sort_order").default(0).notNull(),
+
+ createdAt: timestamp("created_at", { withTimezone: true }).defaultNow().notNull(),
+ updatedAt: timestamp("updated_at", { withTimezone: true }).defaultNow().notNull(),
+}, (table) => {
+ return {
uniqTagTypeAttribute: unique("uniq_tag_type_attribute").on(
- table.tagTypeCode,
- table.attributesId
+ table.tagTypeCode,
+ table.attributesId
),
- };
- }
- );
-
- export const tagSubfieldOptions = pgTable("tag_subfield_options", {
+ };
+});
+
+export const tagSubfieldOptions = pgTable("tag_subfield_options", {
id: serial("id").primaryKey(),
-
+
// 어떤 subfield에 속하는 옵션인지
attributesId: varchar("attributes_id", { length: 50 })
- .notNull()
- .references(() => tagSubfields.attributesId, { onDelete: "cascade" }),
-
+ .notNull()
+ .references(() => tagSubfields.attributesId, { onDelete: "cascade" }),
+
/**
* 실제 코드 (예: "PM", "AA", "VB", "VAR", "01", "02" ...)
*/
code: varchar("code", { length: 50 }).notNull(),
-
+
/**
* 사용자에게 보여줄 레이블 (예: "Pump", "Pneumatic Motor", "Ball Valve", ...)
*/
label: text("label").notNull(),
-
+
/**
* 생성/수정 시각 (선택)
*/
- createdAt: timestamp("created_at", { withTimezone: true })
- .defaultNow()
- .notNull(),
- updatedAt: timestamp("updated_at", { withTimezone: true })
- .defaultNow()
- .notNull(),
- });
-
- export const tagClasses = pgTable("tag_classes", {
+ createdAt: timestamp("created_at", { withTimezone: true }).defaultNow().notNull(),
+ updatedAt: timestamp("updated_at", { withTimezone: true }).defaultNow().notNull(),
+})
+
+export const tagClasses = pgTable("tag_classes", {
id: integer("id").primaryKey().generatedAlwaysAsIdentity(),
-
+
// 기존 code/label
code: varchar("code", { length: 100 }).notNull(),
label: text("label").notNull(),
-
+
// 새 필드: tagTypeCode -> references tagTypes.code
tagTypeCode: varchar("tag_type_code", { length: 50 })
- .notNull()
- .references(() => tagTypes.code, { onDelete: "cascade" }),
-
+ .notNull()
+ .references(() => tagTypes.code, { onDelete: "cascade" }),
+
createdAt: timestamp("created_at").defaultNow().notNull(),
updatedAt: timestamp("updated_at").defaultNow().notNull(),
- });
-
- export const tagTypeClassFormMappings = pgTable(
- "tag_type_class_form_mappings",
- {
- id: serial("id").primaryKey(),
-
- tagTypeLabel: varchar("tag_type_label", { length: 255 }).notNull(),
- classLabel: varchar("class_label", { length: 255 }).notNull(),
-
- formCode: varchar("form_code", { length: 50 }).notNull(),
- formName: varchar("form_name", { length: 255 }).notNull(),
-
- createdAt: timestamp("created_at", { withTimezone: true })
- .defaultNow()
- .notNull(),
- updatedAt: timestamp("updated_at", { withTimezone: true })
- .defaultNow()
- .notNull(),
- }
- );
-
- export type TagTypeClassFormMappings =
- typeof tagTypeClassFormMappings.$inferSelect;
- export type TagSubfields = typeof tagSubfields.$inferSelect;
- export type TagSubfieldOption = typeof tagSubfieldOptions.$inferSelect;
- export type TagClasses = typeof tagClasses.$inferSelect;
-
- export const viewTagSubfields = pgTable("view_tag_subfields", {
+})
+
+export const tagTypeClassFormMappings = pgTable("tag_type_class_form_mappings", {
+ id: serial("id").primaryKey(),
+
+ tagTypeLabel: varchar("tag_type_label", { length: 255 }).notNull(),
+ classLabel: varchar("class_label", { length: 255 }).notNull(),
+
+ formCode: varchar("form_code", { length: 50 }).notNull(),
+ formName: varchar("form_name", { length: 255 }).notNull(),
+
+ createdAt: timestamp("created_at", { withTimezone: true }).defaultNow().notNull(),
+ updatedAt: timestamp("updated_at", { withTimezone: true }).defaultNow().notNull(),
+})
+
+export type TagTypeClassFormMappings = typeof tagTypeClassFormMappings.$inferSelect
+export type TagSubfields = typeof tagSubfields.$inferSelect
+export type TagSubfieldOption = typeof tagSubfieldOptions.$inferSelect
+export type TagClasses = typeof tagClasses.$inferSelect
+
+
+export const viewTagSubfields = pgTable("view_tag_subfields", {
id: integer("id").primaryKey(),
-
+
tagTypeCode: varchar("tag_type_code", { length: 50 }).notNull(),
tagTypeDescription: text("tag_type_description"),
attributesId: varchar("attributes_id", { length: 50 }).notNull(),
attributesDescription: text("attributes_description").notNull(),
-
+
expression: text("expression"),
delimiter: varchar("delimiter", { length: 10 }),
sortOrder: integer("sort_order").default(0).notNull(),
-
+
createdAt: timestamp("created_at", { withTimezone: true }),
updatedAt: timestamp("updated_at", { withTimezone: true }),
- });
-
- export type ViewTagSubfields = typeof viewTagSubfields.$inferSelect;
-
- export const vendorDataReportTemps = pgTable("vendor_data_report_temps", {
+})
+
+export type ViewTagSubfields = typeof viewTagSubfields.$inferSelect
+
+export const vendorDataReportTemps = pgTable("vendor_data_report_temps", {
id: serial("id").primaryKey(),
contractItemId: integer("contract_item_id")
.notNull()
@@ -253,12 +215,11 @@ import {
fileName: varchar("file_name", { length: 255 }).notNull(),
filePath: varchar("file_path", { length: 1024 }).notNull(),
createdAt: timestamp("created_at", { withTimezone: true })
- .defaultNow()
- .notNull(),
- updatedAt: timestamp("updated_at", { withTimezone: true })
- .defaultNow()
- .notNull(),
+ .defaultNow()
+ .notNull(),
+ updatedAt: timestamp("updated_at", { withTimezone: true })
+ .defaultNow()
+ .notNull(),
});
- export type VendorDataReportTemps = typeof vendorDataReportTemps.$inferSelect;
- \ No newline at end of file
+ export type VendorDataReportTemps = typeof vendorDataReportTemps.$inferSelect; \ No newline at end of file
generated by cgit v1.2.3 (git 2.39.1) at 2025-12-07 10:35:17 +0000