Lorentz
a67558de23
Implements authentication middleware with rate limiting, comprehensive permission system, full portal navigation with sidebar and header, company selection flow, and data migration tooling from SQL Server to PostgreSQL. Key additions: - Middleware: auth guards, rate limiting, session management - Permissions: role-based access control with hierarchical permission rules - Navigation: responsive sidebar with expandable sections, header with notifications - Company selector: multi-company user support with session-based active company - Data migration: comprehensive script for migrating auth and quest domain tables - Schema: added auth_user_type_permission_group junction table Co-Authored-By: Claude Sonnet 4.5 <noreply@anthropic.com>
1018 lines
33 KiB
Text
1018 lines
33 KiB
Text
// Prisma schema for Vorteq Quest Portal
|
|
// PostgreSQL 16 database
|
|
|
|
generator client {
|
|
provider = "prisma-client-js"
|
|
}
|
|
|
|
datasource db {
|
|
provider = "postgresql"
|
|
url = env("DATABASE_URL")
|
|
}
|
|
|
|
// =============================================================================
|
|
// AUTH DOMAIN - User Authentication & Authorization
|
|
// =============================================================================
|
|
|
|
// Core user authentication table (Better Auth compatible)
|
|
model auth_user {
|
|
id String @id @default(cuid())
|
|
email String @unique
|
|
email_verified Boolean @default(false)
|
|
name String?
|
|
image String?
|
|
created_at DateTime @default(now())
|
|
updated_at DateTime @updatedAt
|
|
|
|
// Password authentication
|
|
password_hash String?
|
|
|
|
// Account security
|
|
deactivated Boolean @default(false)
|
|
deactivated_at DateTime?
|
|
deactivation_reason String?
|
|
|
|
// Login tracking
|
|
login_count Int @default(0)
|
|
failed_login_count Int @default(0)
|
|
last_login_at DateTime?
|
|
last_login_ip String?
|
|
last_failed_login_at DateTime?
|
|
|
|
// Two-factor authentication
|
|
two_factor_enabled Boolean @default(false)
|
|
two_factor_secret String?
|
|
two_factor_backup_codes String?
|
|
|
|
// SSO fields
|
|
sso_provider String?
|
|
sso_id String?
|
|
|
|
// Relationships
|
|
auth_user_type_id String
|
|
auth_user_type auth_user_type @relation(fields: [auth_user_type_id], references: [id])
|
|
auth_domain_id String?
|
|
auth_domain auth_domain? @relation(fields: [auth_domain_id], references: [id])
|
|
|
|
sessions auth_session[]
|
|
accounts auth_account[]
|
|
password_history auth_password_history[]
|
|
password_resets auth_password_reset[]
|
|
security_answers auth_security_answer[]
|
|
quest_user quest_user?
|
|
|
|
@@index([email])
|
|
@@index([auth_user_type_id])
|
|
@@index([auth_domain_id])
|
|
@@map("auth_user")
|
|
}
|
|
|
|
// User session table (Better Auth compatible)
|
|
model auth_session {
|
|
id String @id @default(cuid())
|
|
user_id String
|
|
token String @unique
|
|
expires_at DateTime
|
|
ip_address String?
|
|
user_agent String?
|
|
created_at DateTime @default(now())
|
|
updated_at DateTime @updatedAt
|
|
|
|
user auth_user @relation(fields: [user_id], references: [id], onDelete: Cascade)
|
|
|
|
@@index([user_id])
|
|
@@index([token])
|
|
@@index([expires_at])
|
|
@@map("auth_session")
|
|
}
|
|
|
|
// OAuth/Social provider accounts (Better Auth compatible)
|
|
model auth_account {
|
|
id String @id @default(cuid())
|
|
user_id String
|
|
account_id String
|
|
provider_id String
|
|
access_token String?
|
|
refresh_token String?
|
|
id_token String?
|
|
access_token_expires_at DateTime?
|
|
refresh_token_expires_at DateTime?
|
|
scope String?
|
|
password String?
|
|
created_at DateTime @default(now())
|
|
updated_at DateTime @updatedAt
|
|
|
|
user auth_user @relation(fields: [user_id], references: [id], onDelete: Cascade)
|
|
|
|
@@unique([provider_id, account_id])
|
|
@@index([user_id])
|
|
@@map("auth_account")
|
|
}
|
|
|
|
// OAuth client configuration
|
|
model auth_oauth_client {
|
|
id String @id @default(cuid())
|
|
client_id String @unique
|
|
client_secret String
|
|
redirect_uris String[] // Array of allowed redirect URIs
|
|
grant_types String[] // Array of allowed grant types
|
|
name String
|
|
created_at DateTime @default(now())
|
|
updated_at DateTime @updatedAt
|
|
revoked Boolean @default(false)
|
|
|
|
access_tokens auth_oauth_access_token[]
|
|
refresh_tokens auth_oauth_refresh_token[]
|
|
|
|
@@map("auth_oauth_client")
|
|
}
|
|
|
|
// OAuth access tokens
|
|
model auth_oauth_access_token {
|
|
id String @id @default(cuid())
|
|
access_token String @unique
|
|
client_id String
|
|
user_id String?
|
|
expires_at DateTime
|
|
scopes String[] // Array of scopes
|
|
created_at DateTime @default(now())
|
|
|
|
client auth_oauth_client @relation(fields: [client_id], references: [client_id], onDelete: Cascade)
|
|
|
|
@@index([access_token])
|
|
@@index([expires_at])
|
|
@@map("auth_oauth_access_token")
|
|
}
|
|
|
|
// OAuth refresh tokens
|
|
model auth_oauth_refresh_token {
|
|
id String @id @default(cuid())
|
|
refresh_token String @unique
|
|
access_token_id String?
|
|
client_id String
|
|
user_id String?
|
|
expires_at DateTime
|
|
scopes String[] // Array of scopes
|
|
created_at DateTime @default(now())
|
|
revoked Boolean @default(false)
|
|
|
|
client auth_oauth_client @relation(fields: [client_id], references: [client_id], onDelete: Cascade)
|
|
|
|
@@index([refresh_token])
|
|
@@index([expires_at])
|
|
@@map("auth_oauth_refresh_token")
|
|
}
|
|
|
|
// User type/role definitions
|
|
model auth_user_type {
|
|
id String @id @default(cuid())
|
|
name String @unique
|
|
description String?
|
|
is_admin Boolean @default(false)
|
|
is_customer Boolean @default(false)
|
|
is_internal Boolean @default(false)
|
|
created_at DateTime @default(now())
|
|
updated_at DateTime @updatedAt
|
|
|
|
users auth_user[]
|
|
permission_groups auth_user_type_permission_group[]
|
|
|
|
@@map("auth_user_type")
|
|
}
|
|
|
|
// Domain/company grouping for multi-tenant scenarios
|
|
model auth_domain {
|
|
id String @id @default(cuid())
|
|
name String @unique
|
|
description String?
|
|
is_active Boolean @default(true)
|
|
created_at DateTime @default(now())
|
|
updated_at DateTime @updatedAt
|
|
|
|
users auth_user[]
|
|
|
|
@@map("auth_domain")
|
|
}
|
|
|
|
// Password history for preventing reuse
|
|
model auth_password_history {
|
|
id String @id @default(cuid())
|
|
user_id String
|
|
password_hash String
|
|
created_at DateTime @default(now())
|
|
|
|
user auth_user @relation(fields: [user_id], references: [id], onDelete: Cascade)
|
|
|
|
@@index([user_id])
|
|
@@map("auth_password_history")
|
|
}
|
|
|
|
// Password reset tokens
|
|
model auth_password_reset {
|
|
id String @id @default(cuid())
|
|
user_id String
|
|
token String @unique
|
|
expires_at DateTime
|
|
used_at DateTime?
|
|
ip_address String?
|
|
min_time_bypass Boolean @default(false)
|
|
created_at DateTime @default(now())
|
|
|
|
user auth_user @relation(fields: [user_id], references: [id], onDelete: Cascade)
|
|
|
|
@@index([token])
|
|
@@index([user_id])
|
|
@@index([expires_at])
|
|
@@map("auth_password_reset")
|
|
}
|
|
|
|
// Permission groups (roles)
|
|
model auth_permission_group {
|
|
id String @id @default(cuid())
|
|
name String @unique
|
|
description String?
|
|
is_active Boolean @default(true)
|
|
created_at DateTime @default(now())
|
|
updated_at DateTime @updatedAt
|
|
|
|
rules auth_permission_group_rule[]
|
|
user_types auth_user_type_permission_group[]
|
|
|
|
@@map("auth_permission_group")
|
|
}
|
|
|
|
// Permission rules (individual permissions)
|
|
model auth_permission_rule {
|
|
id String @id @default(cuid())
|
|
name String @unique
|
|
description String?
|
|
auth_permission_rule_category_id String?
|
|
category auth_permission_rule_category? @relation(fields: [auth_permission_rule_category_id], references: [id])
|
|
created_at DateTime @default(now())
|
|
updated_at DateTime @updatedAt
|
|
|
|
groups auth_permission_group_rule[]
|
|
|
|
@@index([auth_permission_rule_category_id])
|
|
@@map("auth_permission_rule")
|
|
}
|
|
|
|
// Permission rule categories for organization
|
|
model auth_permission_rule_category {
|
|
id String @id @default(cuid())
|
|
name String @unique
|
|
description String?
|
|
created_at DateTime @default(now())
|
|
updated_at DateTime @updatedAt
|
|
|
|
rules auth_permission_rule[]
|
|
|
|
@@map("auth_permission_rule_category")
|
|
}
|
|
|
|
// Junction table: permission groups to rules (many-to-many)
|
|
model auth_permission_group_rule {
|
|
id String @id @default(cuid())
|
|
auth_permission_group_id String
|
|
auth_permission_rule_id String
|
|
created_at DateTime @default(now())
|
|
|
|
group auth_permission_group @relation(fields: [auth_permission_group_id], references: [id], onDelete: Cascade)
|
|
rule auth_permission_rule @relation(fields: [auth_permission_rule_id], references: [id], onDelete: Cascade)
|
|
|
|
@@unique([auth_permission_group_id, auth_permission_rule_id])
|
|
@@index([auth_permission_group_id])
|
|
@@index([auth_permission_rule_id])
|
|
@@map("auth_permission_group_rule")
|
|
}
|
|
|
|
// Junction table: user types to permission groups (many-to-many)
|
|
model auth_user_type_permission_group {
|
|
id String @id @default(cuid())
|
|
auth_user_type_id String
|
|
auth_permission_group_id String
|
|
created_at DateTime @default(now())
|
|
|
|
user_type auth_user_type @relation(fields: [auth_user_type_id], references: [id], onDelete: Cascade)
|
|
group auth_permission_group @relation(fields: [auth_permission_group_id], references: [id], onDelete: Cascade)
|
|
|
|
@@unique([auth_user_type_id, auth_permission_group_id])
|
|
@@index([auth_user_type_id])
|
|
@@index([auth_permission_group_id])
|
|
@@map("auth_user_type_permission_group")
|
|
}
|
|
|
|
// Security questions for account recovery
|
|
model auth_security_question {
|
|
id String @id @default(cuid())
|
|
question String @unique
|
|
is_active Boolean @default(true)
|
|
sort_order Int @default(0)
|
|
created_at DateTime @default(now())
|
|
updated_at DateTime @updatedAt
|
|
|
|
answers auth_security_answer[]
|
|
|
|
@@map("auth_security_question")
|
|
}
|
|
|
|
// User security question answers
|
|
model auth_security_answer {
|
|
id String @id @default(cuid())
|
|
user_id String
|
|
auth_security_question_id String
|
|
answer_hash String
|
|
created_at DateTime @default(now())
|
|
updated_at DateTime @updatedAt
|
|
|
|
user auth_user @relation(fields: [user_id], references: [id], onDelete: Cascade)
|
|
question auth_security_question @relation(fields: [auth_security_question_id], references: [id])
|
|
|
|
@@unique([user_id, auth_security_question_id])
|
|
@@index([user_id])
|
|
@@map("auth_security_answer")
|
|
}
|
|
|
|
// =============================================================================
|
|
// QUEST DOMAIN - Portal-specific user and company data
|
|
// =============================================================================
|
|
|
|
// Quest-specific user data (extends auth_user)
|
|
model quest_user {
|
|
id String @id @default(cuid())
|
|
auth_user_id String @unique
|
|
is_sub_user Boolean @default(false)
|
|
api_token String? @unique
|
|
api_token_expires_at DateTime?
|
|
created_at DateTime @default(now())
|
|
updated_at DateTime @updatedAt
|
|
|
|
auth_user auth_user @relation(fields: [auth_user_id], references: [id], onDelete: Cascade)
|
|
companies quest_user_company[]
|
|
|
|
@@index([auth_user_id])
|
|
@@map("quest_user")
|
|
}
|
|
|
|
// Company/customer information
|
|
model quest_company {
|
|
id String @id @default(cuid())
|
|
epicor_cust_id String @unique
|
|
display_name String
|
|
can_access_invoices Boolean @default(false)
|
|
invoicing_email String?
|
|
order_ack_email String?
|
|
receives_so_emails Boolean @default(false)
|
|
is_active Boolean @default(true)
|
|
created_at DateTime @default(now())
|
|
updated_at DateTime @updatedAt
|
|
|
|
users quest_user_company[]
|
|
|
|
@@index([epicor_cust_id])
|
|
@@map("quest_company")
|
|
}
|
|
|
|
// Junction table: users to companies (many-to-many with active company tracking)
|
|
model quest_user_company {
|
|
id String @id @default(cuid())
|
|
quest_user_id String
|
|
quest_company_id String
|
|
is_active_company Boolean @default(false)
|
|
created_at DateTime @default(now())
|
|
|
|
user quest_user @relation(fields: [quest_user_id], references: [id], onDelete: Cascade)
|
|
company quest_company @relation(fields: [quest_company_id], references: [id], onDelete: Cascade)
|
|
|
|
@@unique([quest_user_id, quest_company_id])
|
|
@@index([quest_user_id])
|
|
@@index([quest_company_id])
|
|
@@map("quest_user_company")
|
|
}
|
|
|
|
// Account request for new user registration
|
|
model quest_account_request {
|
|
id String @id @default(cuid())
|
|
first_name String
|
|
last_name String
|
|
email String
|
|
phone String?
|
|
company_name String
|
|
epicor_cust_id String?
|
|
message String?
|
|
request_processed Boolean @default(false)
|
|
processed_at DateTime?
|
|
processed_by String?
|
|
approved Boolean @default(false)
|
|
rejection_reason String?
|
|
created_at DateTime @default(now())
|
|
updated_at DateTime @updatedAt
|
|
|
|
@@index([email])
|
|
@@index([request_processed])
|
|
@@map("quest_account_request")
|
|
}
|
|
|
|
// Notification/alert system
|
|
model quest_notification {
|
|
id String @id @default(cuid())
|
|
title String
|
|
text String
|
|
is_alert Boolean @default(false)
|
|
display_date DateTime
|
|
display_time String?
|
|
is_active Boolean @default(true)
|
|
created_at DateTime @default(now())
|
|
updated_at DateTime @updatedAt
|
|
|
|
alert_reads quest_user_notification_alert_read[]
|
|
|
|
@@index([is_alert])
|
|
@@index([display_date])
|
|
@@index([is_active])
|
|
@@map("quest_notification")
|
|
}
|
|
|
|
// Tracking which users have read alert notifications
|
|
model quest_user_notification_alert_read {
|
|
id String @id @default(cuid())
|
|
quest_notification_id String
|
|
auth_user_id String
|
|
read_at DateTime @default(now())
|
|
|
|
notification quest_notification @relation(fields: [quest_notification_id], references: [id], onDelete: Cascade)
|
|
|
|
@@unique([quest_notification_id, auth_user_id])
|
|
@@index([auth_user_id])
|
|
@@map("quest_user_notification_alert_read")
|
|
}
|
|
|
|
// Email event definitions (types of emails that can be sent)
|
|
model quest_email_event {
|
|
id String @id @default(cuid())
|
|
name String @unique
|
|
description String?
|
|
is_active Boolean @default(true)
|
|
created_at DateTime @default(now())
|
|
updated_at DateTime @updatedAt
|
|
|
|
user_subscriptions quest_user_email_event[]
|
|
|
|
@@map("quest_email_event")
|
|
}
|
|
|
|
// User subscriptions to email events (many-to-many)
|
|
model quest_user_email_event {
|
|
id String @id @default(cuid())
|
|
auth_user_id String
|
|
quest_email_event_id String
|
|
subscribed Boolean @default(true)
|
|
created_at DateTime @default(now())
|
|
|
|
email_event quest_email_event @relation(fields: [quest_email_event_id], references: [id], onDelete: Cascade)
|
|
|
|
@@unique([auth_user_id, quest_email_event_id])
|
|
@@index([auth_user_id])
|
|
@@map("quest_user_email_event")
|
|
}
|
|
|
|
// Email log for tracking sent emails
|
|
model quest_email_log {
|
|
id String @id @default(cuid())
|
|
to_addresses String[] // Array of recipient emails
|
|
from_address String
|
|
subject String
|
|
body String
|
|
sent_at DateTime @default(now())
|
|
status String @default("sent") // sent, failed, pending
|
|
error_message String?
|
|
quest_email_event_id String?
|
|
auth_user_id String?
|
|
|
|
@@index([sent_at])
|
|
@@index([status])
|
|
@@index([auth_user_id])
|
|
@@map("quest_email_log")
|
|
}
|
|
|
|
// Plant/facility definitions
|
|
model quest_plant {
|
|
id String @id @default(cuid())
|
|
name String @unique
|
|
code String @unique
|
|
is_active Boolean @default(true)
|
|
created_at DateTime @default(now())
|
|
updated_at DateTime @updatedAt
|
|
|
|
inventory_assignments quest_inventory_plant[]
|
|
|
|
@@map("quest_plant")
|
|
}
|
|
|
|
// Inventory type definitions (WIP, Finished Goods, etc.)
|
|
model quest_inventory_type {
|
|
id String @id @default(cuid())
|
|
name String @unique
|
|
code String @unique
|
|
description String?
|
|
is_active Boolean @default(true)
|
|
sort_order Int @default(0)
|
|
created_at DateTime @default(now())
|
|
updated_at DateTime @updatedAt
|
|
|
|
plant_assignments quest_inventory_plant[]
|
|
|
|
@@map("quest_inventory_type")
|
|
}
|
|
|
|
// Junction: which inventory types are available at which plants
|
|
model quest_inventory_plant {
|
|
id String @id @default(cuid())
|
|
quest_plant_id String
|
|
quest_inventory_type_id String
|
|
is_active Boolean @default(true)
|
|
created_at DateTime @default(now())
|
|
|
|
plant quest_plant @relation(fields: [quest_plant_id], references: [id], onDelete: Cascade)
|
|
inventory_type quest_inventory_type @relation(fields: [quest_inventory_type_id], references: [id], onDelete: Cascade)
|
|
|
|
@@unique([quest_plant_id, quest_inventory_type_id])
|
|
@@index([quest_plant_id])
|
|
@@index([quest_inventory_type_id])
|
|
@@map("quest_inventory_plant")
|
|
}
|
|
|
|
// Tracking processed order acknowledgement emails to prevent duplicates
|
|
model quest_processed_order_acknowledgement_email {
|
|
id String @id @default(cuid())
|
|
epicor_order_num Int
|
|
epicor_cust_id String
|
|
epicor_po_num String?
|
|
sent_at DateTime @default(now())
|
|
quest_company_id String?
|
|
|
|
@@unique([epicor_order_num, epicor_cust_id])
|
|
@@index([epicor_cust_id])
|
|
@@index([sent_at])
|
|
@@map("quest_processed_order_acknowledgement_email")
|
|
}
|
|
|
|
// =============================================================================
|
|
// SHIPMENT REQUEST DOMAIN
|
|
// =============================================================================
|
|
|
|
// Shipment request header
|
|
model ship_request {
|
|
id String @id @default(cuid())
|
|
auth_user_id String
|
|
quest_company_id String
|
|
ship_to_address String
|
|
order_number String?
|
|
po_number String?
|
|
release_number String?
|
|
pickup_date DateTime?
|
|
instructions String?
|
|
email_recipients String[] // Array of email addresses
|
|
is_submitted Boolean @default(false)
|
|
submitted_at DateTime?
|
|
is_cancelled Boolean @default(false)
|
|
cancelled_at DateTime?
|
|
cancelled_by String?
|
|
last_cart_activity DateTime @default(now())
|
|
created_at DateTime @default(now())
|
|
updated_at DateTime @updatedAt
|
|
|
|
details ship_request_detail[]
|
|
|
|
@@index([auth_user_id])
|
|
@@index([quest_company_id])
|
|
@@index([is_submitted])
|
|
@@index([is_cancelled])
|
|
@@map("ship_request")
|
|
}
|
|
|
|
// Shipment request line items
|
|
model ship_request_detail {
|
|
id String @id @default(cuid())
|
|
ship_request_id String
|
|
part_num String
|
|
lot_num String?
|
|
plant String
|
|
warehouse String?
|
|
quantity Float
|
|
notes String?
|
|
created_at DateTime @default(now())
|
|
|
|
request ship_request @relation(fields: [ship_request_id], references: [id], onDelete: Cascade)
|
|
|
|
@@index([ship_request_id])
|
|
@@map("ship_request_detail")
|
|
}
|
|
|
|
// =============================================================================
|
|
// ALLOCATION REQUEST DOMAIN
|
|
// =============================================================================
|
|
|
|
// Coil allocation request header
|
|
model alloc_request {
|
|
id String @id @default(cuid())
|
|
auth_user_id String
|
|
quest_company_id String
|
|
job_number String
|
|
ship_to_address String
|
|
order_number String?
|
|
po_number String?
|
|
release_number String?
|
|
pickup_date DateTime?
|
|
instructions String?
|
|
email_recipients String[] // Array of email addresses
|
|
is_submitted Boolean @default(false)
|
|
submitted_at DateTime?
|
|
is_cancelled Boolean @default(false)
|
|
cancelled_at DateTime?
|
|
cancelled_by String?
|
|
last_cart_activity DateTime @default(now())
|
|
created_at DateTime @default(now())
|
|
updated_at DateTime @updatedAt
|
|
|
|
details alloc_request_detail[]
|
|
|
|
@@index([auth_user_id])
|
|
@@index([quest_company_id])
|
|
@@index([job_number])
|
|
@@index([is_submitted])
|
|
@@map("alloc_request")
|
|
}
|
|
|
|
// Allocation request line items
|
|
model alloc_request_detail {
|
|
id String @id @default(cuid())
|
|
alloc_request_id String
|
|
part_num String
|
|
lot_num String?
|
|
coil_number String?
|
|
quantity Float
|
|
notes String?
|
|
created_at DateTime @default(now())
|
|
|
|
request alloc_request @relation(fields: [alloc_request_id], references: [id], onDelete: Cascade)
|
|
|
|
@@index([alloc_request_id])
|
|
@@map("alloc_request_detail")
|
|
}
|
|
|
|
// Allocation plant assignments (which plants can allocate)
|
|
model alloc_plant {
|
|
id String @id @default(cuid())
|
|
plant_code String @unique
|
|
plant_name String
|
|
is_active Boolean @default(true)
|
|
created_at DateTime @default(now())
|
|
updated_at DateTime @updatedAt
|
|
|
|
@@map("alloc_plant")
|
|
}
|
|
|
|
// =============================================================================
|
|
// INVOICE UPLOAD DOMAIN
|
|
// =============================================================================
|
|
|
|
// Invoice upload batch status
|
|
model inv_upload_status {
|
|
id String @id @default(cuid())
|
|
name String @unique
|
|
description String?
|
|
sort_order Int @default(0)
|
|
|
|
uploads inv_upload[]
|
|
|
|
@@map("inv_upload_status")
|
|
}
|
|
|
|
// Invoice upload batch
|
|
model inv_upload {
|
|
id String @id @default(cuid())
|
|
auth_user_id String
|
|
original_filename String
|
|
storage_filename String
|
|
file_path String
|
|
file_size Int
|
|
total_pages Int?
|
|
inv_upload_status_id String
|
|
upload_notes String?
|
|
created_at DateTime @default(now())
|
|
processed_at DateTime?
|
|
|
|
status inv_upload_status @relation(fields: [inv_upload_status_id], references: [id])
|
|
entries inv_upload_entry[]
|
|
|
|
@@index([auth_user_id])
|
|
@@index([inv_upload_status_id])
|
|
@@index([created_at])
|
|
@@map("inv_upload")
|
|
}
|
|
|
|
// Individual invoice entries from upload
|
|
model inv_upload_entry {
|
|
id String @id @default(cuid())
|
|
inv_upload_id String
|
|
invoice_number String
|
|
epicor_cust_id String?
|
|
quest_company_id String?
|
|
job_number String?
|
|
page_number Int?
|
|
storage_filename String?
|
|
file_path String?
|
|
is_ignored Boolean @default(false)
|
|
is_replaced Boolean @default(false)
|
|
send_error String?
|
|
created_at DateTime @default(now())
|
|
sent_at DateTime?
|
|
|
|
upload inv_upload @relation(fields: [inv_upload_id], references: [id], onDelete: Cascade)
|
|
|
|
@@index([inv_upload_id])
|
|
@@index([invoice_number])
|
|
@@index([epicor_cust_id])
|
|
@@index([quest_company_id])
|
|
@@map("inv_upload_entry")
|
|
}
|
|
|
|
// =============================================================================
|
|
// FINANCE/AP CHECK PROCESSING DOMAIN
|
|
// =============================================================================
|
|
|
|
// AP check processing status definitions
|
|
model finance_ap_check_processing_status {
|
|
id String @id @default(cuid())
|
|
name String @unique
|
|
description String?
|
|
sort_order Int @default(0)
|
|
|
|
batches finance_ap_check_processing_batch[]
|
|
|
|
@@map("finance_ap_check_processing_status")
|
|
}
|
|
|
|
// AP check processing batch tracking
|
|
model finance_ap_check_processing_batch {
|
|
id String @id @default(cuid())
|
|
auth_user_id String
|
|
group_id String
|
|
original_filename String
|
|
generated_csv_filename String?
|
|
generated_csv_path String?
|
|
finance_ap_check_processing_status_id String
|
|
error_message String?
|
|
record_count Int @default(0)
|
|
created_at DateTime @default(now())
|
|
processed_at DateTime?
|
|
|
|
status finance_ap_check_processing_status @relation(fields: [finance_ap_check_processing_status_id], references: [id])
|
|
|
|
@@index([auth_user_id])
|
|
@@index([group_id])
|
|
@@index([finance_ap_check_processing_status_id])
|
|
@@map("finance_ap_check_processing_batch")
|
|
}
|
|
|
|
// =============================================================================
|
|
// PAINT SCHEDULE DOMAIN
|
|
// =============================================================================
|
|
|
|
// Paint line types
|
|
model paint_line_type {
|
|
id String @id @default(cuid())
|
|
name String @unique
|
|
description String?
|
|
|
|
lines paint_line[]
|
|
|
|
@@map("paint_line_type")
|
|
}
|
|
|
|
// Paint plants
|
|
model paint_plant {
|
|
id String @id @default(cuid())
|
|
name String @unique
|
|
code String @unique
|
|
is_active Boolean @default(true)
|
|
|
|
plant_lines paint_plant_line[]
|
|
|
|
@@map("paint_plant")
|
|
}
|
|
|
|
// Paint lines
|
|
model paint_line {
|
|
id String @id @default(cuid())
|
|
name String @unique
|
|
paint_line_type_id String
|
|
is_active Boolean @default(true)
|
|
|
|
line_type paint_line_type @relation(fields: [paint_line_type_id], references: [id])
|
|
plant_lines paint_plant_line[]
|
|
schedules paint_schedule[]
|
|
|
|
@@map("paint_line")
|
|
}
|
|
|
|
// Junction: which lines are at which plants
|
|
model paint_plant_line {
|
|
id String @id @default(cuid())
|
|
paint_plant_id String
|
|
paint_line_id String
|
|
is_active Boolean @default(true)
|
|
|
|
plant paint_plant @relation(fields: [paint_plant_id], references: [id], onDelete: Cascade)
|
|
line paint_line @relation(fields: [paint_line_id], references: [id], onDelete: Cascade)
|
|
|
|
@@unique([paint_plant_id, paint_line_id])
|
|
@@map("paint_plant_line")
|
|
}
|
|
|
|
// Paint schedule entries
|
|
model paint_schedule {
|
|
id String @id @default(cuid())
|
|
paint_line_id String
|
|
job_id String?
|
|
part_description String?
|
|
customer String?
|
|
quantity Int?
|
|
estimated_run_time Float?
|
|
start_time DateTime?
|
|
notes String?
|
|
is_complete Boolean @default(false)
|
|
line_speed Float?
|
|
created_at DateTime @default(now())
|
|
updated_at DateTime @updatedAt
|
|
|
|
line paint_line @relation(fields: [paint_line_id], references: [id])
|
|
|
|
@@index([paint_line_id])
|
|
@@index([start_time])
|
|
@@index([is_complete])
|
|
@@map("paint_schedule")
|
|
}
|
|
|
|
// =============================================================================
|
|
// DOCUMENTATION/KNOWLEDGE BASE DOMAIN
|
|
// =============================================================================
|
|
|
|
// Documentation categories
|
|
model doc_category {
|
|
id String @id @default(cuid())
|
|
name String @unique
|
|
description String?
|
|
icon String?
|
|
sort_order Int @default(0)
|
|
is_active Boolean @default(true)
|
|
created_at DateTime @default(now())
|
|
updated_at DateTime @updatedAt
|
|
|
|
articles doc_article[]
|
|
|
|
@@map("doc_category")
|
|
}
|
|
|
|
// Documentation articles
|
|
model doc_article {
|
|
id String @id @default(cuid())
|
|
title String
|
|
content String
|
|
doc_category_id String
|
|
is_published Boolean @default(false)
|
|
sort_order Int @default(0)
|
|
created_at DateTime @default(now())
|
|
updated_at DateTime @updatedAt
|
|
created_by String?
|
|
updated_by String?
|
|
|
|
category doc_category @relation(fields: [doc_category_id], references: [id])
|
|
permission_groups doc_article_permission_group[]
|
|
|
|
@@index([doc_category_id])
|
|
@@index([is_published])
|
|
@@map("doc_article")
|
|
}
|
|
|
|
// Junction: which permission groups can see which articles
|
|
model doc_article_permission_group {
|
|
id String @id @default(cuid())
|
|
doc_article_id String
|
|
auth_permission_group_id String
|
|
created_at DateTime @default(now())
|
|
|
|
article doc_article @relation(fields: [doc_article_id], references: [id], onDelete: Cascade)
|
|
|
|
@@unique([doc_article_id, auth_permission_group_id])
|
|
@@map("doc_article_permission_group")
|
|
}
|
|
|
|
// =============================================================================
|
|
// WAVE/EDI PROCESSING DOMAIN
|
|
// =============================================================================
|
|
|
|
// Wave process definitions
|
|
model wave_process {
|
|
id String @id @default(cuid())
|
|
name String @unique
|
|
description String?
|
|
is_enabled Boolean @default(true)
|
|
schedule_cron String?
|
|
created_at DateTime @default(now())
|
|
updated_at DateTime @updatedAt
|
|
|
|
history wave_process_history[]
|
|
|
|
@@map("wave_process")
|
|
}
|
|
|
|
// Wave process execution history
|
|
model wave_process_history {
|
|
id String @id @default(cuid())
|
|
wave_process_id String
|
|
started_at DateTime @default(now())
|
|
completed_at DateTime?
|
|
status String @default("running") // running, success, failed
|
|
result_message String?
|
|
error_message String?
|
|
|
|
process wave_process @relation(fields: [wave_process_id], references: [id])
|
|
|
|
@@index([wave_process_id])
|
|
@@index([started_at])
|
|
@@index([status])
|
|
@@map("wave_process_history")
|
|
}
|
|
|
|
// =============================================================================
|
|
// LTS (LONG-TERM SCHEDULER) TASK DOMAIN
|
|
// =============================================================================
|
|
|
|
// Task types
|
|
model lts_task_type {
|
|
id String @id @default(cuid())
|
|
name String @unique
|
|
description String?
|
|
|
|
tasks lts_task[]
|
|
|
|
@@map("lts_task_type")
|
|
}
|
|
|
|
// Scheduled tasks
|
|
model lts_task {
|
|
id String @id @default(cuid())
|
|
lts_task_type_id String
|
|
name String
|
|
description String?
|
|
command String
|
|
is_enabled Boolean @default(true)
|
|
created_at DateTime @default(now())
|
|
updated_at DateTime @updatedAt
|
|
|
|
task_type lts_task_type @relation(fields: [lts_task_type_id], references: [id])
|
|
schedules lts_task_schedule[]
|
|
executions lts_task_execution[]
|
|
|
|
@@map("lts_task")
|
|
}
|
|
|
|
// Task schedule definitions
|
|
model lts_task_schedule {
|
|
id String @id @default(cuid())
|
|
lts_task_id String
|
|
schedule_type String // cron, interval, daily, weekly, monthly
|
|
schedule_value String // cron expression or interval value
|
|
timezone String @default("UTC")
|
|
is_active Boolean @default(true)
|
|
next_run_at DateTime?
|
|
created_at DateTime @default(now())
|
|
updated_at DateTime @updatedAt
|
|
|
|
task lts_task @relation(fields: [lts_task_id], references: [id], onDelete: Cascade)
|
|
|
|
@@index([lts_task_id])
|
|
@@index([next_run_at])
|
|
@@map("lts_task_schedule")
|
|
}
|
|
|
|
// Task execution log
|
|
model lts_task_execution {
|
|
id String @id @default(cuid())
|
|
lts_task_id String
|
|
started_at DateTime @default(now())
|
|
completed_at DateTime?
|
|
status String @default("running") // running, success, failed
|
|
output String?
|
|
error_message String?
|
|
exit_code Int?
|
|
|
|
task lts_task @relation(fields: [lts_task_id], references: [id])
|
|
|
|
@@index([lts_task_id])
|
|
@@index([started_at])
|
|
@@index([status])
|
|
@@map("lts_task_execution")
|
|
}
|