Technical Design Document

Orion Portal — Existing Data Model

Complete reference for the current PostgreSQL schema as of July 2026. Source of truth: database/prisma/schema.prisma in the Metiss-AI/orion repo.

Status

AS-IS — current state

Models

27 models across 6 domains

Database

PostgreSQL 16 · Cloud SQL

Version

v1.0 — 2026-07-08

AS-ISData ModelPostgreSQLPrisma

Schema file: database/prisma/schema.prisma in Metiss-AI/orion. Issues in this model are tracked in the Data Model Execution Plan. Background and rationale in the Architecture Review.

01Domain map

27 models across 6 domains. Dashed arrows indicate foreign key direction (child → parent).

02Auth domain

User

Central identity record. All audit trails and assignments reference this model.

Field	Type	Notes
id	UUID (PK)	Primary key
firstName, lastName	VarChar(80)
name	VarChar(120)	Computed full name — redundant with first+last
email	VarChar(255) UNIQUE
role	`String` → enum DM-02	"Executive" \| "Admin" \| "Project Coordinator"
status	`String` → enum DM-02	"Active" \| "Inactive" \| "Suspended"
communicationPreference	`String` → enum DM-02	"Email" \| "Text Message"
passwordHash	VarChar(255)	bcrypt hash
invalidLoginAttempts	Int default 0	Incremented on failed login
jobTitle, phoneNumber, avatarUrl	VarChar / nullable
createdAt, updatedAt	Timestamptz

Session

Field	Type	Notes
id	UUID (PK)
tokenHash	VarChar(255) UNIQUE	HTTP-only cookie value, hashed
userId	UUID → User	Cascade delete on user removal
expiresAt	Timestamptz	Indexed
revokedAt	Timestamptz?	Null = active session

ForgotPasswordToken

Field	Type	Notes
id	BigInt (PK autoincrement)
uuid	UUID UNIQUE	Exposed in reset URL
tokenHash	VarChar(255) UNIQUE	One-time reset token, hashed
userId	UUID → User	Cascade delete
expiresAt, usedAt	Timestamptz	usedAt set when consumed

03Jobs domain

Job

The central operational record. Every field service assignment starts here.

Field	Type	Notes
id	UUID (PK)
jobId	VarChar(80) UNIQUE	Human-readable identifier (e.g. ORN-00123)
partnerId	UUID → Partner	Required — which partner owns this job
userId	UUID → User?	Assigned Metiss team member
organizationId	`BigInt → Organization?` See DM-07	Subcontractor FK
subContractor	`String?` DM-07: deprecate	Legacy free-text subcontractor name; coalesced with org.name
jobStatus	`String` → enum DM-03	"New" \| "Lead" \| "Assigned" \| "Scheduled" \| "Rescheduled" \| "In Partner Review" \| "Completed" \| "Cancelled" \| "On Hold" \| "Rejected" \| "Revisit Needed" \| "Closed"
serviceType	String?	"Pre-Install Services" \| "Installation Services" \| "Post Install Services" \| "Diagnostics & Service" \| "Roofing" \| "Removal and Reinstall" \| "Panel Cleaning" \| "Critter Guard Install" \| "Other"
serviceSubType	String?	Sub-type per service type; values defined in shared-types
customerName, customerPhone, customerEmail, customerAddress	String?	Flat customer fields — no Customer entity yet. See Architecture Review §3.2
city, state, zipCode	String?
latitude, longitude	Float?
dateAssigned, completedDate, cancelledDate, holdDate, partnerAssignedDate, createdDate	Date?	Status-transition dates
storageProvider	StorageProvider enum	GOOGLE_DRIVE \| ONE_DRIVE (ONE_DRIVE not yet implemented)
storageUrl	Text?	Root folder URL in storage provider
fileNames	String[]	File list; denormalized — no FileAttachment entity
cancellationReason	Text?
notes	String?	Legacy single-field notes — prefer Notes model
externalId	VarChar?	ID in upstream partner system
createdByUserId, updatedByUserId	UUID → User?

Notes

Job-scoped notes. Note: a separate UserNote model exists for org/task/ invoice notes — see Architecture Review §3.4.

Field	Type	Notes
id	UUID (PK)
jobId	UUID → Job?	Nullable — note may be user-level
userId	UUID → User?
type	NoteType enum	CUSTOMER \| SUB_CONTRACTOR \| METISS \| PARTNER \| SOW
notes	String?	Note body
fileNames	String[]	Attached file names
createdBy, updatedBy	VarChar(120)?	Name string — not a FK to User
isDeleted	Boolean default false	Soft delete

Task

Field	Type	Notes
id	BigInt (PK autoincrement)
uuid	UUID UNIQUE
assignedTo	UUID → User
jobId	UUID → Job?	Optional — tasks can exist without a job
description	Text
dueDate	Timestamptz
status	VarChar(50)	"Pending" \| "Completed" \| …
isDeleted	Boolean default false

Notifications

Field	Type	Notes
id	BigInt (PK autoincrement)
userId	UUID → User	Recipient
jobId	UUID → Job?
threadId	BigInt → EmailThread?
organizationId	BigInt → Organization?
invoiceDetailsId	BigInt → InvoiceDetails?
content	Text
isRead	Boolean default false
receivedAt	Timestamptz

04Partners domain

Partner

Field	Type	Notes
id	UUID (PK)
name	VarChar(120)
status	`String` → enum DM-04	"Active" \| "Inactive"
isDeleted	Boolean default false

UserPartner

Many-to-many join between User and Partner — controls which partner(s) a user can see.

Field	Type	Notes
id	UUID (PK)
userId	UUID → User	Indexed
partnerId	UUID → Partner	Indexed
isDeleted	Boolean default false

05Subcontractors domain

Organization

Subcontractor company. The authoritative entity for subcontractor identity.

Field	Type	Notes
id	BigInt (PK autoincrement)	See Architecture Review §3.1
uuid	UUID UNIQUE
name	VarChar(120)
status	`String?` → enum DM-04
businessType	VarChar(120)?
website, dba, notes	Text?
yearFormed	Date?
crewCapacity	`String?` DM-01: fix default	Stored as string, coerced to Number at read time. Bad default: 'crew_capacity'
mailingAddressId	BigInt → Address? (UNIQUE)
warehouseAddressId	BigInt → Address? (UNIQUE)
isDeleted	Boolean default false

Address

Field	Type	Notes
id	BigInt (PK autoincrement)
streetAddress, secondAddress	VarChar(255)?
city, state	VarChar(120)?
postalCode	VarChar(20)?
isDeleted	Boolean default false

Contact

Field	Type	Notes
id	BigInt (PK autoincrement)
firstName, lastName	String?
role	VarChar(120)?
email	VarChar(120)?
mobilePhone	String?
isDeleted	Boolean default false

Billing DM-08: encrypt credentials

Field	Type	Notes
id	BigInt (PK autoincrement)
organizationId	BigInt → Organization	Cascade delete
addressId	BigInt → Address?
routingNumber	Text?	PLAINTEXT — see DM-08
accountNumber	Text?	PLAINTEXT — see DM-08
bankName, accountType	VarChar(120)?
remittanceEmail	VarChar(120)?
paymentTerm	String?
isDeleted	Boolean default false

Market

Field	Type	Notes
id	BigInt (PK autoincrement)
organizationId	BigInt → Organization	Cascade delete
state	VarChar(120)?
zipCode	VarChar(20)?
serviceType, serviceSubType	VarChar(120)?	Mirror of Job service fields
isDeleted	Boolean default false

Pricing

Field	Type	Notes
id	BigInt (PK autoincrement)
organizationId	BigInt → Organization
marketId	BigInt → Market? (Cascade)
subContractorPrice	String?	Stored as string, not Decimal
adders	Text?
notes	Text?

License

Field	Type	Notes
id	BigInt (PK autoincrement)
organizationId	BigInt → Organization	Cascade delete
licenseType	String?
market	VarChar(120)?	Free-text string — not FK to Market model
receiveDate, expirationDate	Timestamptz?
notes	Text?

Certification

Field	Type	Notes
id	BigInt (PK autoincrement)
organizationId	BigInt → Organization	Cascade delete
certificationType	VarChar(120)?
market	VarChar(120)?	Free-text string — not FK to Market model
receiveDate, expirationDate	Timestamptz?

Document

Field	Type	Notes
id	BigInt (PK autoincrement)
organizationId	BigInt → Organization	Cascade delete
documentType, documentName	VarChar(120)?
status	VarChar(120)?
executionDate, expirationDate	Timestamptz?
isDeleted	Boolean default false

06Invoicing domain

InvoiceDetails

Field	Type	Notes
id	BigInt (PK autoincrement)
jobId	UUID → Job
emailThreadIds	`BigInt[]` DM-06: migrate to FK	Array of EmailThread IDs — breaks normalization; redundant with EmailThread.invoiceDetailsId FK
invoiced	Boolean? default true
invoiceDate, paymentDate	Date?
invoiceStatus	VarChar(120)?
invoiceAmount, customInvoiceNumber	VarChar(120)?	Amount stored as string, not Decimal
type	VarChar(120)
splitInvoice	Boolean?
customerInvoiceAmount, customerInvoiceStatus, customerInvoiceDate, customerInvoiced	Various?	Split-invoice customer side
isDeleted	Boolean default false

EmailThread

Field	Type	Notes
id	BigInt (PK autoincrement)
uuid	`UUID` DM-09: add @unique	@default(uuid()) present but @unique missing
userId	BigInt → User	Thread owner
jobId	UUID → Job?
organizationId	BigInt → Organization?
invoiceDetailsId	BigInt → InvoiceDetails?	Proper FK — counterpart to the emailThreadIds array
isRead	Boolean default false
lastActivityAt	Timestamptz

EmailMessage

Field	Type	Notes
id	BigInt (PK autoincrement)
uuid	`UUID` DM-09: add @unique	@default(uuid()) present but @unique missing
threadId	BigInt → EmailThread	Cascade delete
messageId	String? UNIQUE	SMTP Message-ID header
inReplyTo	String?	SMTP In-Reply-To header
references	String[]	SMTP References header
direction	Direction enum	SENT \| RECEIVED
fromEmail	String
toEmails, ccEmails, bccEmails	String[]
subject	String
bodyHtml	String?
attachmentsGroupId	UUID?	Groups attachments across messages
receivedAt	Timestamptz	Indexed with threadId

07System domain

EventLog

Local event store. Used when EVENT_PUBLISHER_DRIVER=local. In production, domain mutations should publish to GCP Pub/Sub instead — see Architecture Review §4.4.

Field	Type	Notes
id	UUID (PK)
eventType, aggregateType	VarChar(80)
aggregateId	UUID	ID of the entity that changed
provider	VarChar(60)	"local" \| "gcp-pubsub"
status	VarChar(60)
payloadJson	String	Full event payload as JSON string

AuditLog

Field	Type	Notes
id	BigInt (PK autoincrement)
schemaName, tableName	VarChar(120)
entityId, entityUuid	BigInt? / UUID?	Indexed with tableName
operation	VarChar(120)	"INSERT" \| "UPDATE" \| "DELETE"
version	Int	Monotonic version counter per entity
after	Json?	Snapshot of row after mutation

SavedSearch

Field	Type	Notes
id	BigInt (PK autoincrement)
uuid	UUID UNIQUE
view	VarChar(32) default 'active'	Which job board tab this search applies to
name	VarChar(120)
searchCriteria	Text	JSON-serialised filter state
shareSettings	VarChar(255)?
selectedUserIds	String[]	Users the search is shared with
createdByUserId, updatedByUserId	UUID → User
isDeleted	Boolean default false

SavedSearchHistory mirrors SavedSearch in the history schema with a composite PK of (id, version) — populated by a PostgreSQL trigger on every update to track change history. Its uuid field carries the entity's UUID repeated across all version rows and has no @default(uuid()). The absence of @unique here is intentional — multiple rows share the same UUID by design.

08Note join tables

UserNote is a shared note body used by five entity-specific join tables. This is a parallel note system to Notes — see Architecture Review §3.4.

Join table	Parent entity	FK fields
OrganizationNote	Organization	organizationId → Organization, noteId → UserNote
PricingNote	Pricing	pricingId → Pricing, noteId → UserNote
LicenseNote	License	licenseId → License, noteId → UserNote
CertificationNote	Certification	certificationId → Certification, noteId → UserNote
TaskNotes	Task	taskId → Task, noteId → UserNote
InvoiceNote	InvoiceDetails	invoiceDetailsId → InvoiceDetails, userNotesId → UserNote

UserNote (shared note body)

Field	Type	Notes
id	BigInt (PK autoincrement)
userId	UUID → User?	Author
notes	Text?	Note body
createdBy, updatedBy	VarChar(120)?	Name string — not FK
isDeleted	Boolean default false

09Enum reference

Enum	Values	Status
Direction	SENT \| RECEIVED	In schema
NoteType	CUSTOMER \| SUB_CONTRACTOR \| METISS \| PARTNER \| SOW	In schema
StorageProvider	GOOGLE_DRIVE \| ONE_DRIVE	In schema
UserRole	"Executive" \| "Admin" \| "Project Coordinator"	DM-02 — add to schema
UserStatus	"Active" \| "Inactive" \| "Suspended"	DM-02 — add to schema
CommunicationPreference	"Email" \| "Text Message"	DM-02 — add to schema
JobStatus	"New" \| "Lead" \| "Assigned" \| "Scheduled" \| "Rescheduled" \| "In Partner Review" \| "Completed" \| "Cancelled" \| "On Hold" \| "Rejected" \| "Revisit Needed" \| "Closed"	DM-03 — add to schema
EntityStatus (Partner, Org)	"Active" \| "Inactive"	DM-04 — add to schema

Technical Design Document · v1.0 · 2026-07-08 · AS-IS stateSource: database/prisma/schema.prisma