Waypoint — Database Design

Version: 1.0
Date: July 2026
Author: Pete Carroll, Founder — Traxs Group LLC
Status: Draft

---

1. Overview

The Waypoint database (WaypointDb) is a dedicated Azure SQL database completely separate from the RoundTrip database. No shared tables, no cross-database queries, no shared schema.

All tables include TenantId as a required column. EF Core global query filters ensure every query is automatically scoped to the current tenant — no manual WHERE clause needed for tenant isolation.

---

2. Conventions

Convention	Rule
Primary keys	UNIQUEIDENTIFIER (Guid), default NEWID()
Tenant isolation	Every table has TenantId UNIQUEIDENTIFIER NOT NULL
Timestamps	CreatedAt DATETIME2 NOT NULL DEFAULT GETUTCDATE()
Soft deletes	IsDeleted BIT NOT NULL DEFAULT 0, DeletedAt DATETIME2 NULL
Enum storage	Stored as NVARCHAR(50) — human readable, not integer
Money	Stored as DECIMAL(18,2) with a separate Currency NCHAR(3) column
String lengths	Names: 200, Descriptions: 1000, Content: MAX
Indexes	All foreign keys indexed, TenantId always included in composite indexes

---

3. Schema

3.1 Workspace Context

Workspaces

CREATE TABLE Workspaces (
    WorkspaceId         UNIQUEIDENTIFIER    NOT NULL DEFAULT NEWID(),
    TenantId            UNIQUEIDENTIFIER    NOT NULL,
    CompanyName         NVARCHAR(200)       NOT NULL,
    Industry            NVARCHAR(100)       NULL,
    LogoBlobPath        NVARCHAR(500)       NULL,
    FinancialYearStart  NVARCHAR(20)        NOT NULL DEFAULT 'January',
    TimeZone            NVARCHAR(100)       NOT NULL DEFAULT 'America/New_York',
    Currency            NCHAR(3)            NOT NULL DEFAULT 'USD',
    EmployeeCount       INT                 NULL,
    CreatedAt           DATETIME2           NOT NULL DEFAULT GETUTCDATE(),
    UpdatedAt           DATETIME2           NOT NULL DEFAULT GETUTCDATE(),

    CONSTRAINT PK_Workspaces PRIMARY KEY (WorkspaceId),
    CONSTRAINT UQ_Workspaces_TenantId UNIQUE (TenantId)
);

CREATE INDEX IX_Workspaces_TenantId ON Workspaces (TenantId);

Integrations

CREATE TABLE Integrations (
    IntegrationId       UNIQUEIDENTIFIER    NOT NULL DEFAULT NEWID(),
    TenantId            UNIQUEIDENTIFIER    NOT NULL,
    WorkspaceId         UNIQUEIDENTIFIER    NOT NULL,
    IntegrationType     NVARCHAR(50)        NOT NULL,  -- 'RoundTrip'
    ExternalTenantId    UNIQUEIDENTIFIER    NOT NULL,
    IsActive            BIT                 NOT NULL DEFAULT 1,
    ConnectedAt         DATETIME2           NOT NULL DEFAULT GETUTCDATE(),
    LastSyncAt          DATETIME2           NULL,

    CONSTRAINT PK_Integrations PRIMARY KEY (IntegrationId),
    CONSTRAINT FK_Integrations_Workspaces 
        FOREIGN KEY (WorkspaceId) REFERENCES Workspaces(WorkspaceId)
);

CREATE INDEX IX_Integrations_TenantId ON Integrations (TenantId);
CREATE INDEX IX_Integrations_WorkspaceId ON Integrations (WorkspaceId);

---

3.2 Financial Context

RevenueEntries

CREATE TABLE RevenueEntries (
    RevenueEntryId      UNIQUEIDENTIFIER    NOT NULL DEFAULT NEWID(),
    TenantId            UNIQUEIDENTIFIER    NOT NULL,
    Amount              DECIMAL(18,2)       NOT NULL,
    Currency            NCHAR(3)            NOT NULL DEFAULT 'USD',
    Date                DATE                NOT NULL,
    Description         NVARCHAR(500)       NOT NULL,
    ClientName          NVARCHAR(200)       NULL,
    Source              NVARCHAR(50)        NOT NULL DEFAULT 'Manual',
    RoundTripReference  NVARCHAR(100)       NULL,
    Category            NVARCHAR(50)        NOT NULL DEFAULT 'Service',
    IsRecurring         BIT                 NOT NULL DEFAULT 0,
    RecurringInterval   NVARCHAR(20)        NULL,
    CreatedAt           DATETIME2           NOT NULL DEFAULT GETUTCDATE(),

    CONSTRAINT PK_RevenueEntries PRIMARY KEY (RevenueEntryId),
    CONSTRAINT CK_RevenueEntries_Amount CHECK (Amount > 0)
);

CREATE INDEX IX_RevenueEntries_TenantId_Date 
    ON RevenueEntries (TenantId, Date DESC);
CREATE INDEX IX_RevenueEntries_TenantId_Source 
    ON RevenueEntries (TenantId, Source);

Expenses

CREATE TABLE Expenses (
    ExpenseId           UNIQUEIDENTIFIER    NOT NULL DEFAULT NEWID(),
    TenantId            UNIQUEIDENTIFIER    NOT NULL,
    Amount              DECIMAL(18,2)       NOT NULL,
    Currency            NCHAR(3)            NOT NULL DEFAULT 'USD',
    Date                DATE                NOT NULL,
    Description         NVARCHAR(500)       NOT NULL,
    Category            NVARCHAR(50)        NOT NULL,
    ReceiptBlobPath     NVARCHAR(500)       NULL,
    IsRecurring         BIT                 NOT NULL DEFAULT 0,
    RecurringInterval   NVARCHAR(20)        NULL,
    RecurringEndDate    DATE                NULL,
    CreatedAt           DATETIME2           NOT NULL DEFAULT GETUTCDATE(),
    UpdatedAt           DATETIME2           NOT NULL DEFAULT GETUTCDATE(),

    CONSTRAINT PK_Expenses PRIMARY KEY (ExpenseId),
    CONSTRAINT CK_Expenses_Amount CHECK (Amount > 0)
);

CREATE INDEX IX_Expenses_TenantId_Date 
    ON Expenses (TenantId, Date DESC);
CREATE INDEX IX_Expenses_TenantId_Category 
    ON Expenses (TenantId, Category);

---

3.3 HR Context

Employees

CREATE TABLE Employees (
    EmployeeId          UNIQUEIDENTIFIER    NOT NULL DEFAULT NEWID(),
    TenantId            UNIQUEIDENTIFIER    NOT NULL,
    FirstName           NVARCHAR(100)       NOT NULL,
    LastName            NVARCHAR(100)       NOT NULL,
    Email               NVARCHAR(255)       NOT NULL,
    Phone               NVARCHAR(20)        NULL,
    AddressLine1        NVARCHAR(200)       NULL,
    AddressLine2        NVARCHAR(200)       NULL,
    City                NVARCHAR(100)       NULL,
    State               NCHAR(2)            NULL,
    PostalCode          NVARCHAR(10)        NULL,
    EmergencyName       NVARCHAR(200)       NULL,
    EmergencyPhone      NVARCHAR(20)        NULL,
    EmergencyRelation   NVARCHAR(100)       NULL,
    Role                NVARCHAR(200)       NOT NULL,
    EmploymentType      NVARCHAR(50)        NOT NULL DEFAULT 'FullTime',
    StartDate           DATE                NOT NULL,
    WorkDays            NVARCHAR(100)       NOT NULL DEFAULT 'Mon,Tue,Wed,Thu,Fri',
    HoursPerDay         DECIMAL(4,1)        NOT NULL DEFAULT 8.0,
    PtoAccrued          DECIMAL(6,2)        NOT NULL DEFAULT 0,
    PtoUsed             DECIMAL(6,2)        NOT NULL DEFAULT 0,
    Skills              NVARCHAR(MAX)       NULL,  -- JSON array of strings
    IsActive            BIT                 NOT NULL DEFAULT 1,
    CreatedAt           DATETIME2           NOT NULL DEFAULT GETUTCDATE(),
    UpdatedAt           DATETIME2           NOT NULL DEFAULT GETUTCDATE(),

    CONSTRAINT PK_Employees PRIMARY KEY (EmployeeId),
    CONSTRAINT UQ_Employees_TenantId_Email UNIQUE (TenantId, Email)
);

CREATE INDEX IX_Employees_TenantId_IsActive 
    ON Employees (TenantId, IsActive);

Certifications

CREATE TABLE Certifications (
    CertificationId     UNIQUEIDENTIFIER    NOT NULL DEFAULT NEWID(),
    TenantId            UNIQUEIDENTIFIER    NOT NULL,
    EmployeeId          UNIQUEIDENTIFIER    NOT NULL,
    Name                NVARCHAR(200)       NOT NULL,
    IssuingBody         NVARCHAR(200)       NOT NULL,
    IssueDate           DATE                NOT NULL,
    ExpiryDate          DATE                NULL,
    BlobPath            NVARCHAR(500)       NULL,
    CreatedAt           DATETIME2           NOT NULL DEFAULT GETUTCDATE(),

    CONSTRAINT PK_Certifications PRIMARY KEY (CertificationId),
    CONSTRAINT FK_Certifications_Employees 
        FOREIGN KEY (EmployeeId) REFERENCES Employees(EmployeeId)
);

CREATE INDEX IX_Certifications_TenantId_ExpiryDate 
    ON Certifications (TenantId, ExpiryDate);
CREATE INDEX IX_Certifications_EmployeeId 
    ON Certifications (EmployeeId);

LeaveRequests

CREATE TABLE LeaveRequests (
    LeaveRequestId      UNIQUEIDENTIFIER    NOT NULL DEFAULT NEWID(),
    TenantId            UNIQUEIDENTIFIER    NOT NULL,
    EmployeeId          UNIQUEIDENTIFIER    NOT NULL,
    LeaveType           NVARCHAR(50)        NOT NULL,
    StartDate           DATE                NOT NULL,
    EndDate             DATE                NOT NULL,
    WorkingDaysCount    INT                 NOT NULL,
    Notes               NVARCHAR(500)       NULL,
    Status              NVARCHAR(20)        NOT NULL DEFAULT 'Approved',
    StatusReason        NVARCHAR(500)       NULL,
    CreatedAt           DATETIME2           NOT NULL DEFAULT GETUTCDATE(),
    UpdatedAt           DATETIME2           NOT NULL DEFAULT GETUTCDATE(),

    CONSTRAINT PK_LeaveRequests PRIMARY KEY (LeaveRequestId),
    CONSTRAINT FK_LeaveRequests_Employees 
        FOREIGN KEY (EmployeeId) REFERENCES Employees(EmployeeId),
    CONSTRAINT CK_LeaveRequests_Dates 
        CHECK (EndDate >= StartDate)
);

CREATE INDEX IX_LeaveRequests_TenantId_StartDate 
    ON LeaveRequests (TenantId, StartDate);
CREATE INDEX IX_LeaveRequests_EmployeeId 
    ON LeaveRequests (EmployeeId);

PerformanceReviews

CREATE TABLE PerformanceReviews (
    ReviewId            UNIQUEIDENTIFIER    NOT NULL DEFAULT NEWID(),
    TenantId            UNIQUEIDENTIFIER    NOT NULL,
    EmployeeId          UNIQUEIDENTIFIER    NOT NULL,
    ReviewDate          DATE                NOT NULL,
    ReviewedBy          NVARCHAR(200)       NOT NULL,
    OverallRating       INT                 NOT NULL,
    Comments            NVARCHAR(MAX)       NULL,
    Goals               NVARCHAR(MAX)       NULL,  -- JSON array
    Status              NVARCHAR(20)        NOT NULL DEFAULT 'Draft',
    CreatedAt           DATETIME2           NOT NULL DEFAULT GETUTCDATE(),

    CONSTRAINT PK_PerformanceReviews PRIMARY KEY (ReviewId),
    CONSTRAINT FK_PerformanceReviews_Employees 
        FOREIGN KEY (EmployeeId) REFERENCES Employees(EmployeeId),
    CONSTRAINT CK_PerformanceReviews_Rating 
        CHECK (OverallRating BETWEEN 1 AND 5)
);

CREATE INDEX IX_PerformanceReviews_EmployeeId 
    ON PerformanceReviews (EmployeeId);

EmployeeDocuments

CREATE TABLE EmployeeDocuments (
    EmployeeDocumentId  UNIQUEIDENTIFIER    NOT NULL DEFAULT NEWID(),
    TenantId            UNIQUEIDENTIFIER    NOT NULL,
    EmployeeId          UNIQUEIDENTIFIER    NOT NULL,
    FileName            NVARCHAR(255)       NOT NULL,
    BlobPath            NVARCHAR(500)       NOT NULL,
    FileSizeBytes       BIGINT              NOT NULL,
    MimeType            NVARCHAR(100)       NOT NULL,
    UploadedBy          NVARCHAR(200)       NOT NULL,
    CreatedAt           DATETIME2           NOT NULL DEFAULT GETUTCDATE(),

    CONSTRAINT PK_EmployeeDocuments PRIMARY KEY (EmployeeDocumentId),
    CONSTRAINT FK_EmployeeDocuments_Employees 
        FOREIGN KEY (EmployeeId) REFERENCES Employees(EmployeeId)
);

CREATE INDEX IX_EmployeeDocuments_EmployeeId 
    ON EmployeeDocuments (EmployeeId);

---

3.4 Team Context

TeamMembers

CREATE TABLE TeamMembers (
    TeamMemberId        UNIQUEIDENTIFIER    NOT NULL DEFAULT NEWID(),
    TenantId            UNIQUEIDENTIFIER    NOT NULL,
    EmployeeId          UNIQUEIDENTIFIER    NULL,
    RoundTripUserId     UNIQUEIDENTIFIER    NULL,
    FirstName           NVARCHAR(100)       NOT NULL,
    LastName            NVARCHAR(100)       NOT NULL,
    Role                NVARCHAR(200)       NOT NULL,
    Source              NVARCHAR(20)        NOT NULL DEFAULT 'Waypoint',
    IsActive            BIT                 NOT NULL DEFAULT 1,
    WorkDays            NVARCHAR(100)       NOT NULL DEFAULT 'Mon,Tue,Wed,Thu,Fri',
    HoursPerDay         DECIMAL(4,1)        NOT NULL DEFAULT 8.0,
    CreatedAt           DATETIME2           NOT NULL DEFAULT GETUTCDATE(),
    UpdatedAt           DATETIME2           NOT NULL DEFAULT GETUTCDATE(),

    CONSTRAINT PK_TeamMembers PRIMARY KEY (TeamMemberId)
);

CREATE INDEX IX_TeamMembers_TenantId_IsActive 
    ON TeamMembers (TenantId, IsActive);
CREATE INDEX IX_TeamMembers_EmployeeId 
    ON TeamMembers (EmployeeId);

---

3.5 Documents Context

Folders

CREATE TABLE Folders (
    FolderId            UNIQUEIDENTIFIER    NOT NULL DEFAULT NEWID(),
    TenantId            UNIQUEIDENTIFIER    NOT NULL,
    Name                NVARCHAR(200)       NOT NULL,
    ParentFolderId      UNIQUEIDENTIFIER    NULL,
    Category            NVARCHAR(50)        NOT NULL DEFAULT 'Other',
    CreatedBy           NVARCHAR(200)       NOT NULL,
    CreatedAt           DATETIME2           NOT NULL DEFAULT GETUTCDATE(),

    CONSTRAINT PK_Folders PRIMARY KEY (FolderId),
    CONSTRAINT FK_Folders_ParentFolder 
        FOREIGN KEY (ParentFolderId) REFERENCES Folders(FolderId)
);

CREATE INDEX IX_Folders_TenantId ON Folders (TenantId);
CREATE INDEX IX_Folders_ParentFolderId ON Folders (ParentFolderId);

DocumentRecords

CREATE TABLE DocumentRecords (
    DocumentId          UNIQUEIDENTIFIER    NOT NULL DEFAULT NEWID(),
    TenantId            UNIQUEIDENTIFIER    NOT NULL,
    FileName            NVARCHAR(255)       NOT NULL,
    FileExtension       NVARCHAR(10)        NOT NULL,
    FileSizeBytes       BIGINT              NOT NULL,
    MimeType            NVARCHAR(100)       NOT NULL,
    CurrentBlobPath     NVARCHAR(500)       NOT NULL,
    FolderId            UNIQUEIDENTIFIER    NULL,
    Category            NVARCHAR(50)        NOT NULL DEFAULT 'Other',
    UploadedBy          NVARCHAR(200)       NOT NULL,
    ReviewDate          DATE                NULL,
    IsDeleted           BIT                 NOT NULL DEFAULT 0,
    DeletedAt           DATETIME2           NULL,
    CreatedAt           DATETIME2           NOT NULL DEFAULT GETUTCDATE(),
    UpdatedAt           DATETIME2           NOT NULL DEFAULT GETUTCDATE(),

    CONSTRAINT PK_DocumentRecords PRIMARY KEY (DocumentId),
    CONSTRAINT FK_DocumentRecords_Folders 
        FOREIGN KEY (FolderId) REFERENCES Folders(FolderId)
);

CREATE INDEX IX_DocumentRecords_TenantId_IsDeleted 
    ON DocumentRecords (TenantId, IsDeleted);
CREATE INDEX IX_DocumentRecords_FolderId 
    ON DocumentRecords (FolderId);
CREATE INDEX IX_DocumentRecords_ReviewDate 
    ON DocumentRecords (TenantId, ReviewDate) 
    WHERE ReviewDate IS NOT NULL;

DocumentVersions

CREATE TABLE DocumentVersions (
    VersionId           UNIQUEIDENTIFIER    NOT NULL DEFAULT NEWID(),
    TenantId            UNIQUEIDENTIFIER    NOT NULL,
    DocumentId          UNIQUEIDENTIFIER    NOT NULL,
    VersionNumber       INT                 NOT NULL,
    BlobPath            NVARCHAR(500)       NOT NULL,
    FileSizeBytes       BIGINT              NOT NULL,
    UploadedBy          NVARCHAR(200)       NOT NULL,
    UploadedAt          DATETIME2           NOT NULL DEFAULT GETUTCDATE(),

    CONSTRAINT PK_DocumentVersions PRIMARY KEY (VersionId),
    CONSTRAINT FK_DocumentVersions_DocumentRecords 
        FOREIGN KEY (DocumentId) REFERENCES DocumentRecords(DocumentId),
    CONSTRAINT UQ_DocumentVersions_DocVersion 
        UNIQUE (DocumentId, VersionNumber)
);

CREATE INDEX IX_DocumentVersions_DocumentId 
    ON DocumentVersions (DocumentId);

KnowledgeArticles

CREATE TABLE KnowledgeArticles (
    ArticleId           UNIQUEIDENTIFIER    NOT NULL DEFAULT NEWID(),
    TenantId            UNIQUEIDENTIFIER    NOT NULL,
    Title               NVARCHAR(500)       NOT NULL,
    Content             NVARCHAR(MAX)       NOT NULL,  -- TipTap JSON
    Category            NVARCHAR(100)       NULL,
    Status              NVARCHAR(20)        NOT NULL DEFAULT 'Draft',
    AuthorName          NVARCHAR(200)       NOT NULL,
    ReviewDate          DATE                NULL,
    RelatedArticleIds   NVARCHAR(MAX)       NULL,  -- JSON array of GUIDs
    CreatedAt           DATETIME2           NOT NULL DEFAULT GETUTCDATE(),
    UpdatedAt           DATETIME2           NOT NULL DEFAULT GETUTCDATE(),

    CONSTRAINT PK_KnowledgeArticles PRIMARY KEY (ArticleId)
);

CREATE INDEX IX_KnowledgeArticles_TenantId_Status 
    ON KnowledgeArticles (TenantId, Status);

-- Full-text search index on Title and Content
CREATE FULLTEXT CATALOG WaypointCatalog AS DEFAULT;
CREATE FULLTEXT INDEX ON KnowledgeArticles (Title, Content)
    KEY INDEX PK_KnowledgeArticles;

---

4. EF Core Migration Strategy

Same pattern as RoundTrip:

1. Create migration: dotnet ef migrations add MigrationName
2. Never run migrations at startup — always manual
3. Apply via script against target database
4. Track applied migrations in __EFMigrationsHistory table
5. DDL statements (sequences, full-text indexes) run on separate ADO.NET connection outside EF Core transaction

---

5. Indexes Summary

Table	Index	Purpose
RevenueEntries	(TenantId, Date DESC)	Financial overview date range queries
Expenses	(TenantId, Date DESC)	Financial overview date range queries
Expenses	(TenantId, Category)	Expense breakdown by category
Employees	(TenantId, IsActive)	Active employee list
Certifications	(TenantId, ExpiryDate)	Expiry alert queries
LeaveRequests	(TenantId, StartDate)	Leave calendar queries
DocumentRecords	(TenantId, IsDeleted)	Document library queries
DocumentRecords	(TenantId, ReviewDate)	Review date alerts
KnowledgeArticles	(TenantId, Status)	Published articles list
KnowledgeArticles	Full-text on Title, Content	Search

---

6. Document History

Version	Date	Author	Changes
1.0	July 2026	Pete Carroll	Initial draft