Database ER Diagram

Generated from packages/db/prisma/schema.prisma. Every model carries a tenantId unless noted (the isolation boundary — see Multi-Tenancy); User is the deliberate exception (a global identity).

Tenancy & Identity

Tenant

Field	Type	Notes
id	cuid PK
slug	String unique	path routing key — /t/{slug}
emailDomain	String unique	resolves a self-registering user's tenant
firstAdminEmail	String?	auto-promoted to admin on first sign-up; seeds default projects
entraDirectoryId	String?	customer's Azure AD tid for verified SSO auto-approval
name	String
status	TenantStatus	login/API allowed only when ACTIVE

User (global identity — no tenantId-based isolation)

Field	Type	Notes
id	cuid PK
entraId	String? unique	null for custom-login users
email	String unique	globally unique across all tenants
name, displayName, avatarUrl	String(?)
isActive	Boolean	global account kill-switch
isPlatformAdmin	Boolean	tenant-less super-role
authProvider	String	teams / azure_sso / custom
passwordHash	String?	custom login only
emailVerified, pendingApproval	Boolean
tenantId	String?	home tenant only; nullable (homeless guests)

:::note User.role / User.isAdmin were removed Per-tenant role and admin status now live on Membership. A user can be an admin in one tenant and a member in another. :::

Membership — the user↔tenant link

Field	Type	Notes
userId + tenantId	unique
role	UserAppRole	OWNER / MEMBER / VIEWER (baseline)
isAdmin	Boolean	unlocks the tenant admin panel
status	MembershipStatus	ACTIVE / PENDING / SUSPENDED
isActive	Boolean	per-tenant disable (distinct from status)
isHome	Boolean	true for the email-domain-resolved tenant
invitedById	String?	set for guest memberships

Supporting

• RegistrationAttempt — rate-limit ledger for the public self-serve registration endpoint (not tenant-scoped; it precedes any tenant).
• ReapprovalRequest — a user asking to be re-enabled in a specific tenant (PENDING/APPROVED/REJECTED).
• UserNotificationPreference — per-user level (MENTIONS_ONLY / MINIMAL / REGULAR / HIGH) and in-app/Teams/email toggles.

Projects, Roles & Subjects

Project

Field	Type	Notes
type	ProjectType	EPIC / SUPPORT / DEV / CONSULTING — fixed after create; drives roles, workflow, subjects
ticketPrefix	String	e.g. ASS — max 3 uppercase letters
ticketSeq	Int	last issued ticket number
isPublic	Boolean	public within the tenant
customer	String?
billable / billedBy	Boolean / String?	billing metadata
isArchived	Boolean

ProjectRole (dynamic roles)

Field	Type	Notes
key	String	slug, unique per project type
name, description	String	display
projectType	ProjectType	roles are scoped to a type
permissions	String[]	permission codes (see Roles & Permissions)
isSystem	Boolean	seeded defaults

Unique: [tenantId, projectType, key].

ProjectMember

[projectId, userId] PK, plus roleId → ProjectRole (RESTRICT).

Subject (folder level)

A folder between Project and Ticket. type is constrained by the project's type. Carries startDate/endDate (all subjects are time-based), sortOrder, isDefault (hidden default for Epic projects), and a fields JSON blob for type-specific extras.

ProjectEpicLink

Join table ([projectId, ticketId]) linking a project to the epic ticket(s) it rolls up to.

WorkflowStatus

Per-project-type ticket workflow: which TicketStatus keys a type uses, with label, color, sortOrder, isInitial, isTerminal. Unique [tenantId, projectType, statusKey].

Tickets & Tasks

Ticket

Field	Type	Notes
ticketNumber	Int	sequential per project
title, description	String / Text?
status	TicketStatus	see enum
priority	Priority	support uses Low/Med/High/Critical/Custom; others P1–P3
subjectId	FK	required — every ticket lives in a Subject
parentId, duplicateOfId	String?	self-relations
startDate / endDate	DateTime?	non-support planning window
preApprovalHours, estimateHours, actualHours, billableHours	Float?	Consulting/Dev effort & billing
actualStartDate / actualEndDate	DateTime?
estimateApprovedById / estimateApprovedAt		Lead estimate approval
dueDate, resolvedAt, closedAt	DateTime?
onHoldAt / onHoldMinutes		accumulated hold time (for SLA)
deferredFromStatus	TicketStatus?	restored on resume from Deferred
isEscalated, escalationUrgency, escalatedAt, escalatedById, escalationComment		escalation
commitId	String?	git commit a Dev fix shipped in; captured on Fixed → Review
emailMessageId (unique), emailGraphMessageId, emailMailbox, emailSender, emailCc		email-to-ticket threading
emailInboxId	FK?	which inbox produced it — how notification settings are resolved. SetNull
emailRoutingRuleId	FK?	which rule routed it. SetNull, and indexed so hard-deleting a rule doesn't scan the whole table

Tickets support multiple assignees via TicketAssignee.

Task (sub-task)

taskNumber (per-ticket), status (TaskStatus), single assigneeId, dueDate, completedAt. Supports tags, comments, attachments, time entries, and work logs.

Custom attributes

• ProjectCustomAttribute — fieldName (snake_case), label, type (CustomAttributeType), mandatory, options JSON (string[] or parent::child for cascading), dependsOn (parent field), defaultValue, sortOrder. Unique [projectId, fieldName].
• TicketCustomAttributeValue — polymorphic value JSON. Unique [ticketId, attributeId].

Collaboration

• Comment — on a ticket or task; threaded via parentId; supports attachments and reactions.
• CommentReaction — one row per (comment, user); the compound unique is what enforces one reaction per person, so toggling updates or deletes the row rather than adding a duplicate. emoji is the native unicode character, not an enum. userId/tenantId are plain scalars with no @relation — isolation comes from the tenant extension keying off the column name.
• Follower — a user following exactly one of a ticket or task (the pair is enforced in Zod, mirroring Notification). role records why they have tenant access: VIEWER if they already belonged, EX_VIEWER if a guest membership was created purely to support this follow. Unique per [userId, ticketId] and [userId, taskId].
• Attachment — entityType (TICKET/TASK/COMMENT), Azure Blob blobKey (unique); tenantId is re-checked before issuing a download SAS URL.
• Activity — human-readable event feed per ticket/task/project.
• AuditLog — immutable CREATE/UPDATE/DELETE/RESTORE record with IP/user-agent.
• Notification — tenant-scoped, per-user, with event, isRead/readAt, optional ticket/task link.

Time & Productivity

• TimeEntry — minutes logged against a ticket or sub-task (Consulting), with spentOn.
• WorkLog (+ WorkLogAssignee) — a daily work log: date, summary, free-text ticketRefs[], optional taskId. See Work Log.
• Note (+ NoteAssignee) — title + content; tenantId derived from the creator.
• Reminder (+ ReminderAssignee) — content, scheduledAt, repeat (ReminderRepeat), optional ticket link; fires REMINDER_DUE.
• MeetingLog — title, meetingDate, mom; has MeetingLogAttendee, tags, and MeetingItemLog action items (status, owner, due date), each optionally linked to tickets via MeetingItemLogTicket.

Master Calendar

• Calendar — one isMaster row per tenant; weekStart (display).
• CalendarRegion — timezone (IANA), support hours as minutes-from-midnight (startMinute/endMinute, or is24x7), isDefault.
• CalendarWeekendRule — one clause of a region's weekend set: ordinal (WeekOrdinal) + weekday.
• Holiday — dated per-year row, type (HolidayType).
• HolidayRegion — which regions observe a holiday; empty set = all regions.

See Master Calendar.

Email System

EmailInbox (table still named ProjectEmailConfig)

Owned by the tenant, not a project. It used to be the reverse — projectId was unique, one inbox per project — but a shared support mailbox routinely feeds several projects, so the project became a destination and the old owning column became defaultProjectId. The table name is kept via @@map so the rename cost no data movement.

Field	Notes
defaultProjectId	Required fallback destination. onDelete: Restrict — an inbox is shared, so cascading would let deleting one project destroy a mailbox several others receive through
provider	microsoft, gmail, …
connectionMode	graph_app | imap_oauth | imap_basic. Orthogonal to provider
graphSubscriptionId, subscriptionExpiresAt	Push mode only
oauthRefreshToken (encrypted), oauthAccountEmail	Delegated mode. The signed-in account is stored separately from mailboxAddress because an admin can type one and authenticate as another
imapHost/Port/Username/Password/Tls/Folder, smtp*	IMAP modes; passwords encrypted, and null under imap_oauth where XOAUTH2 replaces them
afterProcessAction, processedFolder	MARK_READ | MOVE | DELETE | NONE
lastPolledAt	The IMAP counterpart of subscriptionExpiresAt. Not a cursor — unread state on the server drives the poll, so a lost write can't lose or replay mail
sendAckEmail, notifyOnComment, notifyOnStatusChange	Outbound notification gates
ignoreNoreplyEmails	Drops no-reply senders and bounces/delivery reports
circuitOpen, consecutiveFailures, lastErrorAt/Msg	Circuit breaker — trips at 5 consecutive failures
autoCreateTicket, allowedFromDomains, blockedFromDomains	Dead — no longer read by ingestion; kept to avoid a no-op migration

Inboxes are soft-deleted (deletedAt), so the tickets they created keep their link.

EmailRoutingRule

Routes one inbox's mail to several projects. Scoped per inbox, not per tenant — two mailboxes in one tenant may route the same domain differently.

Field	Notes
matchType	RECIPIENT_ADDRESS | SENDER_DOMAIN. The enum order is the precedence
pattern	Lowercased; a bare domain or a full address depending on matchType
projectId	Destination. onDelete: Cascade
isEnabled	Park a rule without deleting and retyping it
overridePriority, overrideSendAck, overrideNotifyOnComment, overrideNotifyOnStatusChange	Null means inherit from the inbox, so an inbox-level change still propagates
overrideSubjectId, overrideAssigneeId	Both SetNull — losing the embellishment beats deleting the rule and silently re-routing that customer

Unique on [inboxId, matchType, pattern], so "which rule wins" is unambiguous within a type.

Others

• EmailMessage — inbound message (RFC messageId dedup key), status (EmailProcessingStatus), the inbox it arrived on and the routed ticket.
• OutboundEmail — threaded replies sent from a ticket.
• MicrosoftMailConfig — dead. Per-tenant Azure app registrations were removed; mail now uses the platform SSO app against Tenant.entraDirectoryId. The table remains pending cleanup.

Auth Tables (Better Auth)

Session, Account, Verification — used by the web app (Azure SSO + custom login). The Teams SSO path does not use these.

Config

• StatusConfig — display name/color overrides for ticket/task statuses, per tenant.
• AppConfig — per-tenant settings (e.g. ticketArchiveDays).
• IncomingWebhook — HTTP endpoints that create tickets from external tools.

Enumerations

Enum	Values
TicketStatus	OPEN, ASSIGNED, IN_PROGRESS, ON_HOLD, REVIEW, FIXED, CLOSED, REOPENED, DEFERRED
TaskStatus	OPEN, ASSIGNED, IN_PROGRESS, COMPLETED, REOPENED
Priority	LOW, MEDIUM, HIGH, CRITICAL, CUSTOM, P1, P2, P3
ProjectType	EPIC, SUPPORT, DEV, CONSULTING
FollowerRole	VIEWER, EX_VIEWER
EmailMatchType	RECIPIENT_ADDRESS, SENDER_DOMAIN (also the precedence order)
EmailProcessingStatus	PENDING, PROCESSING, PROCESSED, FAILED, DUPLICATE, IGNORED
NotificationEvent	TICKET_CREATED/ASSIGNED/UPDATED/CLOSED/REOPENED/ON_HOLD/ESCALATED, TASK_CREATED/ASSIGNED/COMPLETED, PROJECT_ASSIGNED, SLA_BREACHED, USER_PENDING, USER_REAPPROVAL_REQUESTED/APPROVED/REJECTED, COMMENT_ADDED, COMMENT_MENTION, COMMENT_REACTION, COMMENT_REPLY, REMINDER_DUE, FOLLOWER_ADDED
NotificationLevel	MENTIONS_ONLY, MINIMAL (default), REGULAR, HIGH
TenantStatus	PENDING_APPROVAL, ACTIVE, SUSPENDED, REJECTED
MembershipStatus	ACTIVE, PENDING, SUSPENDED
UserAppRole	OWNER, MEMBER, VIEWER
EscalationUrgency	Delayed_Resolution, UNRESOLVED_RECURRING, URGENCY_BUSINESS_IMPACT, CRITICAL_SHOWSTOPPER, MISCOMMUNICATION_ERROR
NotificationEvent	TICKET_CREATED, TICKET_ASSIGNED, TICKET_UPDATED, TICKET_CLOSED, TICKET_REOPENED, TICKET_ON_HOLD, TICKET_ESCALATED, TASK_CREATED, TASK_ASSIGNED, TASK_COMPLETED, PROJECT_ASSIGNED, SLA_BREACHED, USER_PENDING, USER_REAPPROVAL_REQUESTED/APPROVED/REJECTED, COMMENT_ADDED, COMMENT_MENTION, REMINDER_DUE
NotificationLevel	MENTIONS_ONLY, MINIMAL, REGULAR, HIGH
ReapprovalStatus	PENDING, APPROVED, REJECTED
CustomAttributeType	TEXT_INPUT, TEXT_AREA, SINGLE_SELECT, MULTI_SELECT, DATE_PICKER, DATE_RANGE_PICKER
ReminderRepeat	NEVER, DAILY, WEEKLY, MONTHLY
MeetingItemStatus	OPEN, IN_PROGRESS, DONE
WeekOrdinal	FIRST, SECOND, THIRD, FOURTH, FIFTH, LAST, ALL
HolidayType	COMPULSORY, REGIONAL, OPTIONAL
AttachmentEntityType	TICKET, TASK, COMMENT
ActivityEntityType	TICKET, TASK, PROJECT
AuditAction	CREATE, UPDATE, DELETE, RESTORE
EmailProcessingStatus	PENDING, PROCESSING, PROCESSED, FAILED, DUPLICATE, IGNORED

:::note Legacy enum ProjectMemberRole (CREATOR / MEMBER / VIEWER) is retained only for the migration that backfilled ProjectMember.roleId. New code resolves roles via ProjectRole. :::