Files

743 lines
28 KiB
Plaintext

enum PortalUserStatus {
invited
active
disabled
}
enum PortalUserKind {
customer
subcontractor
}
enum PortalUploadCategory {
complaint
photo
attachment
audio
other
}
enum PortalUploadStatus {
reserved
uploading
scanning
clean
failed
cancelled
}
enum PortalComplaintStatus {
open
in_review
resolved
rejected
}
enum PortalLifecycleMode {
customer
project
}
enum PortalNotificationEvent {
stage_changed
document_released
upload_confirmation
status_mail
lifecycle
}
enum PortalServiceInboxKind {
complaint
upload
login
download
}
enum PortalServiceInboxStatus {
open
in_progress
resolved
dismissed
}
enum PortalRoutingTargetKind {
user
role
}
enum PortalDeletionRequestStatus {
pending
cancelled
processing
completed
failed
}
enum PortalGdprExportStatus {
queued
processing
ready
expired
failed
}
enum PortalDomainDnsStatus {
pending
txt_verified
cname_verified
verified
failed
}
enum PortalCustomDomainStatus {
awaiting_payment
awaiting_dns
dns_verified
provisioning
active
degraded
failed
disabled
deprovisioning
cooldown
released
}
enum PortalTokenPurpose {
magic_link
setup
verify_email
reset_password
}
model Portal {
id String @id @default(cuid())
createdAt DateTime @default(now())
updatedAt DateTime @updatedAt
tenantId String
tenant Tenant @relation(fields: [tenantId], references: [id], onDelete: Cascade)
createdByUserId String?
createdByUser User? @relation(fields: [createdByUserId], references: [id], onDelete: Cascade)
subdomain String @unique
title String
isPublished Boolean @default(false)
paymentProviderAccountId String?
themeColor String?
themeScheme String?
seoTitle String?
seoDescription String?
seoImage String?
seoThumbnail String?
seoTwitterCreator String?
seoTwitterSite String?
seoKeywords String?
authRequireEmailVerification Boolean @default(false)
authRequireOrganization Boolean @default(true)
authRequireName Boolean @default(true)
analyticsSimpleAnalytics Boolean @default(false)
analyticsPlausibleAnalytics Boolean @default(false)
analyticsGoogleAnalyticsTrackingId String?
brandingLogo String?
brandingLogoDarkMode String?
brandingIcon String?
brandingIconDarkMode String?
brandingFavicon String?
metadata Json?
users PortalUser[]
projectAccesses PortalProjectAccess[]
stageVisibilities PortalStageVisibility[]
projectDocuments PortalProjectDocument[]
emailTemplates PortalEmailTemplate[]
domainHostnameClaims PortalDomainHostnameClaim[]
customDomain PortalCustomDomain?
configuration PortalConfiguration?
uploads PortalUpload[]
complaints PortalComplaint[]
subcontractorAccesses PortalSubcontractorAccess[]
documentAutoReleaseRules PortalDocumentAutoReleaseRule[]
serviceInboxItems PortalServiceInboxItem[]
activityRoutingRules PortalActivityRoutingRule[]
deletionRequests PortalDeletionRequest[]
gdprExportJobs PortalGdprExportJob[]
pages PortalPage[]
analyticsUniqueVisitors AnalyticsUniqueVisitor[]
analyticsPageViews AnalyticsPageView[]
analyticsEvents AnalyticsEvent[]
@@unique([tenantId])
@@unique([id, tenantId])
}
model PortalUser {
id String @id @default(cuid())
createdAt DateTime @default(now())
updatedAt DateTime @updatedAt
tenantId String
tenant Tenant @relation(fields: [tenantId], references: [id], onDelete: Cascade)
portalId String
portal Portal @relation(fields: [portalId, tenantId], references: [id, tenantId], onDelete: Cascade)
email String
emailNormalized String
passwordHash String?
firstName String
lastName String
avatar String?
phone String?
kind PortalUserKind @default(customer)
status PortalUserStatus @default(invited)
locale String @default("de")
emailVerifiedAt DateTime?
/// Server-canonical legal notice acknowledged during first-access setup.
/// PortalUser_privacyAcknowledgement_pair_check requires both fields to be
/// NULL or populated together; existing identities are not backfilled.
privacyNoticeAcknowledgedAt DateTime?
privacyNoticeAcknowledgedUrl String?
archivedAt DateTime?
lastLoginAt DateTime?
lastActivityAt DateTime?
isDemoData Boolean @default(false)
sessions PortalSession[]
tokens PortalToken[]
projectAccesses PortalProjectAccess[]
uploadedFiles File[] @relation("PortalFileActor")
fileUploads FileUpload[] @relation("PortalFileUploadActor")
fileEntityLinks FileEntityLink[] @relation("PortalFileEntityLinkActor")
formSubmissions FormSubmission[] @relation("PortalFormSubmissionActor")
uploads PortalUpload[]
complaints PortalComplaint[]
subcontractorAccesses PortalSubcontractorAccess[]
notificationPreferences PortalNotificationPreference[]
serviceInboxItems PortalServiceInboxItem[]
deletionRequests PortalDeletionRequest[]
gdprExportJobs PortalGdprExportJob[]
analyticsUniqueVisitors AnalyticsUniqueVisitor[]
analyticsPageViews AnalyticsPageView[]
analyticsEvents AnalyticsEvent[]
@@unique([portalId, email])
@@unique([portalId, emailNormalized])
@@unique([id, tenantId])
@@index([portalId])
@@index([tenantId, status, archivedAt])
}
/// Server-side session for the separate customer-portal identity class. The
/// raw cookie value is never stored; tenantId and portalUserId are immutable
/// under the dedicated portal RLS policy.
model PortalSession {
id String @id @default(cuid())
createdAt DateTime @default(now())
updatedAt DateTime @updatedAt
tenantId String
portalUserId String
tokenHash String @unique
expiresAt DateTime
lastSeenAt DateTime?
revokedAt DateTime?
ipAddressHash String?
userAgent String?
/// Canonical origin selected by the trusted host resolver when the session
/// was issued. Legacy null rows are rejected and are backfilled/revoked by
/// the merge migration; every newly issued session is host-bound.
origin String?
portalUser PortalUser @relation(fields: [portalUserId, tenantId], references: [id, tenantId], onDelete: Cascade)
@@index([tenantId, portalUserId, expiresAt])
@@index([expiresAt, revokedAt])
}
/// Durable global hostname reservation. The row survives deprovisioning and
/// carries the 30-day reclaim cooldown so a dangling CNAME cannot be claimed
/// by another workspace. Only the SHA-256 TXT proof is persisted.
model PortalDomainHostnameClaim {
id String @id @default(cuid())
createdAt DateTime @default(now())
updatedAt DateTime @updatedAt
tenantId String
portalId String
hostname String @unique
verificationTokenHash String @unique
verificationTokenExpiresAt DateTime
dnsStatus PortalDomainDnsStatus @default(pending)
txtVerifiedAt DateTime?
cnameVerifiedAt DateTime?
verifiedAt DateTime?
lastDnsCheckedAt DateTime?
verificationAttemptCount Int @default(0)
lastDnsObservation Json?
generation Int @default(1)
billingScopeKey String?
idempotencyKey String
claimedAt DateTime @default(now())
releasedAt DateTime?
cooldownUntil DateTime?
/// Signed controller deprovision proof copied before the portal's single
/// current-domain row can be retargeted to another hostname.
deprovisionedAt DateTime?
deprovisionCommandHash String?
deprovisionResultHash String?
isDemoData Boolean @default(false)
portal Portal @relation(fields: [portalId, tenantId], references: [id, tenantId], onDelete: Restrict)
customDomain PortalCustomDomain?
@@unique([id, tenantId])
@@unique([tenantId, idempotencyKey])
@@index([tenantId, portalId, releasedAt])
@@index([dnsStatus, lastDnsCheckedAt])
@@index([cooldownUntil])
}
/// Exactly one current custom-domain generation per portal. Host resolution
/// reads only `active` rows with a matching claim generation and non-null
/// origin through a SECURITY DEFINER function; the portal login has no direct
/// table grant.
model PortalCustomDomain {
id String @id @default(cuid())
createdAt DateTime @default(now())
updatedAt DateTime @updatedAt
tenantId String
portalId String @unique
claimId String @unique
generation Int
status PortalCustomDomainStatus @default(awaiting_payment)
activeOrigin String?
entitlementValidatedAt DateTime?
paymentAcquisitionId String?
controllerCommandId String?
controllerContentHash String?
controllerResultContentHash String?
controllerResultAttempt Int @default(0)
controllerResultStatus String?
lastHealthAckAt DateTime?
provisionedAt DateTime?
nextRevalidationAt DateTime?
firstFailureAt DateTime?
lastFailureAt DateTime?
failureCount Int @default(0)
lastFailureCode String?
nextRetryAt DateTime?
tlsObservedAt DateTime?
certificateExpiresAt DateTime?
disabledAt DateTime?
disabledReason String?
deprovisioningStartedAt DateTime?
deprovisionedAt DateTime?
killSwitchEnabled Boolean @default(false)
killSwitchReason String?
cooldownUntil DateTime?
releasedAt DateTime?
isDemoData Boolean @default(false)
portal Portal @relation(fields: [portalId, tenantId], references: [id, tenantId], onDelete: Restrict)
claim PortalDomainHostnameClaim @relation(fields: [claimId, tenantId], references: [id, tenantId], onDelete: Restrict)
@@unique([id, tenantId])
@@unique([portalId, tenantId])
@@unique([claimId, tenantId])
@@index([tenantId, status, updatedAt])
@@index([status, nextRetryAt])
@@index([status, nextRevalidationAt])
@@index([certificateExpiresAt])
}
/// Hashed, single-purpose portal authentication capability. Magic-link,
/// first-login setup, email verification and password reset share this table
/// but can never be exchanged across purposes.
model PortalToken {
id String @id @default(cuid())
createdAt DateTime @default(now())
tenantId String
portalUserId String
purpose PortalTokenPurpose
tokenHash String @unique
expiresAt DateTime
consumedAt DateTime?
revokedAt DateTime?
/// Lowercase SHA-256 client-IP binding. System-issued setup capabilities may
/// start unbound; consumption claims the IP atomically. The migration-only
/// PortalToken_requestedIpHash_binding_check enforces every other value.
requestedIpHash String?
portalUser PortalUser @relation(fields: [portalUserId, tenantId], references: [id, tenantId], onDelete: Cascade)
// PortalToken_one_live_setup_per_user_key is a migration-only partial unique
// index over live, unconsumed setup capabilities.
@@index([tenantId, portalUserId, purpose, expiresAt])
@@index([expiresAt, consumedAt, revokedAt])
}
/// Explicit project allow-list for one portal identity. Project visibility is
/// never inferred from tenant membership alone.
model PortalProjectAccess {
id String @id @default(cuid())
createdAt DateTime @default(now())
updatedAt DateTime @updatedAt
tenantId String
portalId String
portalUserId String
projectId String
active Boolean @default(true)
statusEmailsEnabled Boolean @default(true)
revokedAt DateTime?
isDemoData Boolean @default(false)
portal Portal @relation(fields: [portalId, tenantId], references: [id, tenantId], onDelete: Cascade)
portalUser PortalUser @relation(fields: [portalUserId, tenantId], references: [id, tenantId], onDelete: Cascade)
project Projekt @relation(fields: [projectId, tenantId], references: [id, tenantId], onDelete: Cascade)
subcontractorAccess PortalSubcontractorAccess?
@@unique([portalId, portalUserId, projectId])
@@unique([id, tenantId])
@@index([tenantId, portalUserId, active])
@@index([tenantId, projectId, active])
}
/// Tenant-admin configuration for the stages that an end customer may see.
model PortalStageVisibility {
id String @id @default(cuid())
createdAt DateTime @default(now())
updatedAt DateTime @updatedAt
tenantId String
portalId String
stageId String
visible Boolean @default(false)
customerLabel String?
isDemoData Boolean @default(false)
portal Portal @relation(fields: [portalId, tenantId], references: [id, tenantId], onDelete: Cascade)
stage PipelineStage @relation(fields: [stageId, tenantId], references: [id, tenantId], onDelete: Cascade)
@@unique([portalId, stageId])
@@index([tenantId, stageId, visible])
}
/// Explicit document release. A File or FileEntityLink by itself never grants
/// customer-portal read access.
model PortalProjectDocument {
id String @id @default(cuid())
createdAt DateTime @default(now())
updatedAt DateTime @updatedAt
tenantId String
portalId String
projectId String
fileId String
releasedAt DateTime @default(now())
releasedByUserId String?
revokedAt DateTime?
isDemoData Boolean @default(false)
portal Portal @relation(fields: [portalId, tenantId], references: [id, tenantId], onDelete: Cascade)
project Projekt @relation(fields: [projectId, tenantId], references: [id, tenantId], onDelete: Cascade)
file File @relation(fields: [fileId, tenantId], references: [id, tenantId], onDelete: Cascade)
@@unique([portalId, projectId, fileId])
@@index([tenantId, projectId, revokedAt])
@@index([tenantId, fileId])
}
/// Stage-change mail template managed by the tenant-facing core application.
/// The portal runtime only reads rendered project/status data, never edits it.
model PortalEmailTemplate {
id String @id @default(cuid())
createdAt DateTime @default(now())
updatedAt DateTime @updatedAt
tenantId String
portalId String
stageId String
locale String @default("de")
subject String
body String
active Boolean @default(false)
isDemoData Boolean @default(false)
portal Portal @relation(fields: [portalId, tenantId], references: [id, tenantId], onDelete: Cascade)
stage PipelineStage @relation(fields: [stageId, tenantId], references: [id, tenantId], onDelete: Cascade)
@@unique([portalId, stageId, locale])
@@index([tenantId, stageId, active])
}
/// Persisted contract for one portal upload. File/FileUpload/FileEntityLink
/// identifiers remain bare cross-module scalars per AD-21; the file tables
/// independently carry the same PortalUser actor and tenant scope.
model PortalUpload {
id String @id @default(cuid())
createdAt DateTime @default(now())
updatedAt DateTime @updatedAt
tenantId String
portalId String
portalUserId String
projectId String
fileUploadId String?
fileId String?
fileEntityLinkId String?
category PortalUploadCategory @default(attachment)
description String?
status PortalUploadStatus @default(reserved)
quotaBytes Int
requestChecksumSha256 String
scanCompletedAt DateTime?
completedAt DateTime?
effectsCompletedAt DateTime?
failedAt DateTime?
failureCode String?
idempotencyKey String
isDemoData Boolean @default(false)
portal Portal @relation(fields: [portalId, tenantId], references: [id, tenantId], onDelete: Cascade)
portalUser PortalUser @relation(fields: [portalUserId, tenantId], references: [id, tenantId], onDelete: Cascade)
@@unique([tenantId, idempotencyKey])
@@unique([fileUploadId])
@@unique([fileId])
@@index([tenantId, portalUserId, status, createdAt(sort: Desc)])
@@index([tenantId, projectId, createdAt(sort: Desc)])
}
/// Authenticated portal-form complaint. The validated form payload remains in
/// FormSubmission; this row supplies the portal/project lifecycle contract.
model PortalComplaint {
id String @id @default(cuid())
createdAt DateTime @default(now())
updatedAt DateTime @updatedAt
tenantId String
portalId String
portalUserId String
projectId String
formSubmissionId String @unique
subject String?
status PortalComplaintStatus @default(open)
effectsCompletedAt DateTime?
resolvedAt DateTime?
isDemoData Boolean @default(false)
portal Portal @relation(fields: [portalId, tenantId], references: [id, tenantId], onDelete: Cascade)
portalUser PortalUser @relation(fields: [portalUserId, tenantId], references: [id, tenantId], onDelete: Cascade)
@@index([tenantId, projectId, status, createdAt(sort: Desc)])
@@index([tenantId, portalUserId, createdAt(sort: Desc)])
}
/// One-to-one visibility extension for a subcontractor project grant. Customer
/// access continues to use PortalProjectAccess without this restricted matrix.
model PortalSubcontractorAccess {
id String @id @default(cuid())
createdAt DateTime @default(now())
updatedAt DateTime @updatedAt
tenantId String
portalId String
portalUserId String
projectId String
projectAccessId String
companyName String
roleLabel String?
validFrom DateTime?
validUntil DateTime?
visibleStageIds String[] @default([])
visibleDocumentTypes String[] @default([])
canSeeOtherSubcontractors Boolean @default(false)
canUpload Boolean @default(true)
active Boolean @default(true)
revokedAt DateTime?
isDemoData Boolean @default(false)
portal Portal @relation(fields: [portalId, tenantId], references: [id, tenantId], onDelete: Cascade)
portalUser PortalUser @relation(fields: [portalUserId, tenantId], references: [id, tenantId], onDelete: Cascade)
projectAccess PortalProjectAccess @relation(fields: [projectAccessId, tenantId], references: [id, tenantId], onDelete: Cascade)
@@unique([portalId, portalUserId, projectId])
@@unique([projectAccessId, tenantId])
@@index([tenantId, portalUserId, active, revokedAt])
@@index([tenantId, projectId, active, revokedAt])
}
model PortalConfiguration {
id String @id @default(cuid())
createdAt DateTime @default(now())
updatedAt DateTime @updatedAt
tenantId String
portalId String
lifecycleMode PortalLifecycleMode @default(customer)
projectAutoCloseMonths Int @default(12)
inactivityDisableMonths Int @default(24)
enabledProjectTypes String[] @default([])
uploadMaxFiles Int @default(10)
uploadMaxFileSizeBytes Int @default(52428800)
statusEmailsEnabled Boolean @default(true)
welcomeMessage String?
supportEmail String?
supportPhone String?
imprintUrl String?
privacyUrl String?
termsUrl String?
isDemoData Boolean @default(false)
portal Portal @relation(fields: [portalId, tenantId], references: [id, tenantId], onDelete: Cascade)
@@unique([id, tenantId])
@@unique([portalId, tenantId])
@@index([tenantId])
}
model PortalDocumentAutoReleaseRule {
id String @id @default(cuid())
createdAt DateTime @default(now())
updatedAt DateTime @updatedAt
tenantId String
portalId String
projectType String?
documentType String
enabled Boolean @default(true)
isDemoData Boolean @default(false)
portal Portal @relation(fields: [portalId, tenantId], references: [id, tenantId], onDelete: Cascade)
@@unique([portalId, projectType, documentType])
@@index([tenantId, enabled])
}
model PortalNotificationPreference {
id String @id @default(cuid())
createdAt DateTime @default(now())
updatedAt DateTime @updatedAt
tenantId String
portalUserId String
eventType PortalNotificationEvent
emailEnabled Boolean @default(true)
mutedUntil DateTime?
portalUser PortalUser @relation(fields: [portalUserId, tenantId], references: [id, tenantId], onDelete: Cascade)
@@unique([portalUserId, eventType])
@@index([tenantId, portalUserId])
}
/// Persistent, seven-day internal service queue. It is written by trusted
/// app/worker consumers and deliberately never exposed to hivegrid_portal.
model PortalServiceInboxItem {
id String @id @default(cuid())
createdAt DateTime @default(now())
updatedAt DateTime @updatedAt
tenantId String
portalId String
portalUserId String
projectId String?
kind PortalServiceInboxKind
status PortalServiceInboxStatus @default(open)
priority Int
sourceKey String
sourceId String?
titleKey String
metadata Json @default("{}")
occurredAt DateTime
lastOccurredAt DateTime
bundledCount Int @default(1)
expiresAt DateTime
ownerUserId String?
routingRuleId String?
resolvedAt DateTime?
isDemoData Boolean @default(false)
portal Portal @relation(fields: [portalId, tenantId], references: [id, tenantId], onDelete: Cascade)
portalUser PortalUser @relation(fields: [portalUserId, tenantId], references: [id, tenantId], onDelete: Cascade)
routingRule PortalActivityRoutingRule? @relation(fields: [routingRuleId], references: [id], onDelete: SetNull)
@@unique([tenantId, sourceKey])
@@index([tenantId, status, priority(sort: Desc), occurredAt(sort: Desc)])
@@index([tenantId, portalUserId, occurredAt(sort: Desc)])
@@index([expiresAt])
}
model PortalActivityRoutingRule {
id String @id @default(cuid())
createdAt DateTime @default(now())
updatedAt DateTime @updatedAt
tenantId String
portalId String
activityType PortalServiceInboxKind
targetKind PortalRoutingTargetKind
targetUserId String?
targetRoleId String?
priority Int @default(100)
enabled Boolean @default(true)
notifyEnabled Boolean @default(true)
matchConfig Json @default("{}")
isDemoData Boolean @default(false)
portal Portal @relation(fields: [portalId, tenantId], references: [id, tenantId], onDelete: Cascade)
inboxItems PortalServiceInboxItem[]
@@unique([portalId, activityType, priority])
@@index([tenantId, activityType, enabled, priority])
}
model PortalDeletionRequest {
id String @id @default(cuid())
createdAt DateTime @default(now())
updatedAt DateTime @updatedAt
tenantId String
portalId String
portalUserId String
status PortalDeletionRequestStatus @default(pending)
requestedAt DateTime @default(now())
confirmedAt DateTime?
confirmationEmailSentAt DateTime?
cancellableUntil DateTime
cancelledAt DateTime?
processingStartedAt DateTime?
completedAt DateTime?
failureCode String?
idempotencyKey String
portal Portal @relation(fields: [portalId, tenantId], references: [id, tenantId], onDelete: Cascade)
portalUser PortalUser @relation(fields: [portalUserId, tenantId], references: [id, tenantId], onDelete: Restrict)
@@unique([tenantId, idempotencyKey])
@@index([tenantId, portalUserId, status])
@@index([status, cancellableUntil])
}
model PortalGdprExportJob {
id String @id @default(cuid())
createdAt DateTime @default(now())
updatedAt DateTime @updatedAt
tenantId String
portalId String
portalUserId String
requestedByUserId String?
status PortalGdprExportStatus @default(queued)
progressPercent Int @default(0)
resultFileId String?
resultBucket String?
resultKey String?
resultSizeBytes Int?
resultExpiresAt DateTime?
downloadCount Int @default(0)
lastDownloadedAt DateTime?
processingStartedAt DateTime?
processingFinishedAt DateTime?
failureCode String?
idempotencyKey String
portal Portal @relation(fields: [portalId, tenantId], references: [id, tenantId], onDelete: Cascade)
portalUser PortalUser @relation(fields: [portalUserId, tenantId], references: [id, tenantId], onDelete: Restrict)
@@unique([tenantId, idempotencyKey])
@@index([tenantId, portalUserId, status, createdAt(sort: Desc)])
@@index([status, resultExpiresAt])
}
model PortalPage {
id String @id @default(cuid())
createdAt DateTime @default(now())
updatedAt DateTime @updatedAt
portalId String
portal Portal @relation(fields: [portalId], references: [id], onDelete: Cascade)
name String
attributes Json?
@@unique([portalId, name])
}