TELS Auth Service - Entity-Relationship Diagrams

Overview

The Auth Service owns a single schema (TELS.Auth) with one table, 2 views, and 14 stored procedures. However, its identity resolution logic performs extensive cross-database reads across 5 databases and 30+ tables to build comprehensive user identity claims.

Auth Schema (Owned)

erDiagram
    %% ==========================================
    %% TELS.Auth Schema - Token Revocation
    %% ==========================================

    Auth_Revocations {
        VARCHAR_25 Domain PK "tels-personid | ds-mtolympus | jti"
        NVARCHAR_100 Identifier PK "Composite PK with Domain"
        DATETIMEOFFSET RevocationDate "UTC timestamp of revocation"
    }

Auth.Revocations

Purpose: Tracks revoked tokens and user sessions
Domains:
tels-personid — Revoke all tokens for a TELS person
ds-mtolympus — Revoke all tokens for a DS user
jti — Revoke a specific token by JWT ID
Operations: MERGE upsert (insert or update RevocationDate)

Cross-Database Identity Model

The Auth service reads from multiple databases to resolve user identity. The two primary views (Auth.v_Identity_ByUserAccount and Auth.v_Identity_ByDsUser) join across these databases.

erDiagram
    %% ==========================================
    %% CONTACT.dbo - User Account & Person Data
    %% ==========================================

    cUserAccount {
        INT UserAccountID PK
        INT UserGroupID "User group classification"
        NVARCHAR Username "Login username"
        INT Status "Account status"
        INT PersonID FK "Links to cPerson"
    }

    cPasswordLockout {
        INT UserAccountID PK "FK to cUserAccount"
        INT ConsecutiveIncorrect "Failed login counter"
    }

    cPerson {
        INT PersonID PK
        NVARCHAR FirstName
        NVARCHAR LastName
    }

    ctPersonUID {
        UNIQUEIDENTIFIER PersonUID PK "Composite PK"
        INT PersonID PK "FK to cPerson"
    }

    cRouting {
        INT PersonID FK "FK to cPerson"
        INT RouteType "1 = contact email"
        NVARCHAR RoutingAddress "Email address"
    }

    telsContact {
        INT PersonID FK "FK to cPerson"
        INT Status "TELS contact status"
    }

    cFlag {
        INT cFlagID PK
        NVARCHAR Name "TELS Access | Administrator | Power User | Resident | Integration"
    }

    cFlagValue {
        INT cFlagID PK "FK to cFlag"
        INT UserAccountID PK "FK to cUserAccount"
    }

    cUserReln {
        INT UserID FK "FK to DsUser"
    }

    luSupplierContactKey {
        INT PersonID PK "FK to cPerson"
        INT ContactKey PK "FK to ClCont"
    }

    %% CONTACT Relationships
    cUserAccount ||--o| cPasswordLockout : "UserAccountID"
    cUserAccount }o--|| cPerson : "PersonID"
    cPerson ||--o{ ctPersonUID : "PersonID"
    cPerson ||--o{ cRouting : "PersonID"
    cPerson ||--o| telsContact : "PersonID"
    cUserAccount ||--o{ cFlagValue : "UserAccountID"
    cFlag ||--o{ cFlagValue : "cFlagID"
    cPerson ||--o{ luSupplierContactKey : "PersonID"

    %% ==========================================
    %% MDID_BTRIEVE.dbo - Customer/Supplier Contacts
    %% ==========================================

    ClCont {
        INT ContactKey PK
        UNIQUEIDENTIFIER PersonGUID "Links to cUserAccount.PersonID"
        INT PersonID
        NVARCHAR FirstName
        NVARCHAR LastName
    }

    ClEmail {
        INT ContactKey PK "FK to ClCont"
        VARCHAR EmailCode PK "CE = customer email"
        NVARCHAR Location
        NVARCHAR EmailAddress
    }

    ApCont {
        INT ContactKey PK
        NVARCHAR BaseLocation
        NVARCHAR FirstName
        NVARCHAR LastName
    }

    %% MDID_BTRIEVE Relationships
    ClCont ||--o{ ClEmail : "ContactKey"
    luSupplierContactKey }o--|| ClCont : "ContactKey"

    %% ==========================================
    %% DSI_APP.dbo - Directory Services Users
    %% ==========================================

    DsUser {
        INT UserID PK
        NVARCHAR Username
        NVARCHAR FirstName
        NVARCHAR LastName
        NVARCHAR Signature "Display name"
        INT TeamID FK "FK to DsTeam"
        BIT Active
    }

    DsUserADInfo {
        NVARCHAR UserName PK
        NVARCHAR Email "Active Directory email"
    }

    DsUserContactReln {
        INT UserID PK "FK to DsUser"
        INT ContactKey PK "FK to ClCont"
    }

    %% DSI_APP Relationships
    DsUser ||--o| DsUserADInfo : "UserName"
    DsUser ||--o{ DsUserContactReln : "UserID"
    DsUserContactReln }o--|| ClCont : "ContactKey"
    cUserReln }o--|| DsUser : "UserID"

    %% ==========================================
    %% DSHE_APP.dbo - Security Group Hierarchy
    %% ==========================================

    DsSecurityGroup {
        INT GroupID PK
        NVARCHAR GroupName
        BIT Active
    }

    DsGroupReln {
        INT GroupID PK "FK to DsSecurityGroup"
        INT SubGroupID PK "FK to DsSecurityGroup"
    }

    DsTeam {
        INT TeamID PK
        INT DeptID FK "FK to DsDepartment"
        BIT Active
    }

    DsDepartment {
        INT DeptID PK
        BIT Active
    }

    DsCompany {
        INT CompanyID PK
        BIT Active
    }

    DsCompanyGroupReln {
        INT CompanyID PK "FK to DsCompany"
        INT GroupID PK "FK to DsSecurityGroup"
    }

    DsTeamGroupReln {
        INT TeamID PK "FK to DsTeam"
        INT GroupID PK "FK to DsSecurityGroup"
    }

    DsDeptGroupReln {
        INT DeptID PK "FK to DsDepartment"
        INT GroupID PK "FK to DsSecurityGroup"
    }

    DsCompanyReln {
        INT CompanyID PK "FK to DsCompany"
        INT TeamID PK "FK to DsTeam"
    }

    DsTeamReln {
        INT TeamID PK
        INT SubTeamID PK "FK to DsTeam"
    }

    %% DSHE_APP Relationships
    DsSecurityGroup ||--o{ DsGroupReln : "GroupID"
    DsUser }o--|| DsTeam : "TeamID"
    DsTeam }o--|| DsDepartment : "DeptID"
    DsCompany ||--o{ DsCompanyGroupReln : "CompanyID"
    DsSecurityGroup ||--o{ DsCompanyGroupReln : "GroupID"
    DsTeam ||--o{ DsTeamGroupReln : "TeamID"
    DsSecurityGroup ||--o{ DsTeamGroupReln : "GroupID"
    DsDepartment ||--o{ DsDeptGroupReln : "DeptID"
    DsSecurityGroup ||--o{ DsDeptGroupReln : "GroupID"
    DsCompany ||--o{ DsCompanyReln : "CompanyID"
    DsTeam ||--o{ DsCompanyReln : "TeamID"

    %% ==========================================
    %% TELS Schemas - Facility & Service Provider
    %% ==========================================

    ServiceProvider {
        INT ServiceProviderID PK
        NVARCHAR ProviderCode
        BIT IsActive
    }

    OwnerContactAccess {
        INT OwnerID PK
        INT PersonId PK
    }

    OwnerFacilities {
        INT OwnerId PK
        INT FacilityId PK
        INT OwnerFacilityStatus "2 = Active"
    }

Domain Groupings

User Identity (CONTACT.dbo)

Core user account and person data — the entry point for most authentication flows. - cUserAccount — Login credentials and account status - cPasswordLockout — Failed login tracking - cPerson — Person biographical data - ctPersonUID — GUID-based person identification - cRouting — Email/contact routing - telsContact — TELS-specific contact status - cFlag / cFlagValue — Role/permission flags (TELS Access, Administrator, Power User, Resident, Integration)

Customer/Supplier Contacts (MDID_BTRIEVE.dbo)

Legacy customer relationship management data. - ClCont — Customer contact records - ClEmail — Customer email addresses - ApCont — Supplier/applicant contact records - luSupplierContactKey — Maps TELS persons to customer contacts

Directory Services (DSI_APP.dbo)

Internal DS employee/partner user records. - DsUser — DS user accounts with team assignment - DsUserADInfo — Active Directory integration - DsUserContactReln — Links DS users to customer contacts

Security Hierarchy (DSHE_APP.dbo)

Organizational security group structure with recursive hierarchies. - DsSecurityGroup — Security group definitions - DsGroupReln — Group-to-subgroup hierarchy (recursive) - DsTeam / DsTeamReln — Team hierarchy - DsDepartment — Departments - DsCompany / DsCompanyReln — Company-team mapping - Ds*GroupReln — Links company/team/department to security groups

Facility Access (TELS schemas)

ServiceProvider (TELS.LocalServices) — Service provider records
OwnerContactAccess (TELS.Customers) — Owner-person access mapping
OwnerFacilities (TELS.Customers) — Owner-facility relationships
v_telsFacilitySelectPerson_v2 (TELS.dbo) — Facility-person access view

Views (Auth Schema)

Auth.v_Identity_ByUserAccount

Comprehensive identity lookup starting from cUserAccount. Joins 15+ tables across CONTACT, MDID_BTRIEVE, DSI_APP, and TELS databases. Returns: person details, email, personas, facility access, contact keys, DsUser mapping.

Auth.v_Identity_ByDsUser

Comprehensive identity lookup starting from DsUser. Joins 15+ tables across DSI_APP, MDID_BTRIEVE, CONTACT, and TELS databases. Returns: same identity fields from the DsUser entry point.

Stored Procedures

Identity Lookups

Procedure	Parameters	Purpose
`Auth.Identity_ByUserAccountId_S`	@userAccountId INT	Lookup by UserAccount ID
`Auth.Identity_ByUserAccountUserName_S`	@userGroupId INT, @userName	Lookup by username within group
`Auth.Identity_CheckPassword_M`	@userGroupId, @userName, @passwordHash	Validate password + update lockout (writes to CONTACT.dbo)
`Auth.Identity_ByDsUserId_S`	@dsUserId INT	Lookup by DsUser ID
`Auth.Identity_ByDsUserUserName_S`	@userName	Lookup by DsUser username
`Auth.Identity_ByDsUserPersonId_S`	@personId INT	Lookup by DsUser PersonId
`Auth.Identity_DsUserByUserName_S`	@userName	Direct DsUser lookup
`Auth.Identity_DsUserByPersonId_S`	@personId INT	Direct DsUser by PersonId
`Auth.Identity_ServiceProviderByUserName_S`	@userName	Service provider lookup

Revocation Operations

Procedure	Parameters	Purpose
`Auth.Revocation_S`	@Domain, @Identifier	Get single revocation
`Auth.Revocation_M`	@Domain, @Identifier	Upsert revocation (MERGE)
`Auth.Revocations_ByDate_S`	@MinDate DATETIMEOFFSET	Bulk retrieval after date

Security Editor

Procedure	Parameters	Purpose
`Auth.SecurityEditor_Group_Users_S`	@GroupName VARCHAR(50)	Recursive group hierarchy user lookup
`Auth.SecurityEditor_MaxAccessLevel_S`	@userId INT, @accessPointId INT	Max access level for user at access point

Cross-Database References

Source	Target Database	Tables Accessed	Access Type
Auth views/SPs	CONTACT.dbo	cUserAccount, cPasswordLockout, cPerson, ctPersonUID, cRouting, telsContact, cFlag, cFlagValue, cUserReln, luSupplierContactKey	Read + Write (lockout)
Auth views/SPs	MDID_BTRIEVE.dbo	ClCont, ClEmail, ApCont	Read only
Auth views/SPs	DSI_APP.dbo	DsUser, DsUserADInfo, DsUserContactReln	Read only
Auth views/SPs	DSHE_APP.dbo	DsSecurityGroup, DsGroupReln, DsTeam, DsDepartment, DsCompany, DsGroupReln, DsReln	Read only
Auth views/SPs	TELS.dbo	v_telsFacilitySelectPerson_v2	Read only
Auth views/SPs	TELS.Customers	OwnerContactAccess, OwnerFacilities	Read only
Auth views/SPs	TELS.LocalServices	ServiceProvider	Read only

Write Operations: Only Auth.Identity_CheckPassword_M writes to CONTACT.dbo.cPasswordLockout (MERGE on consecutive failed login counter).

Migration Timeline

Date	Migration	Description
2025-12-12	Initial EXTERNAL	Created Auth schema, TELSSRVC_Auth user, role indirection
2025-12-12	Initial LOCAL	Created local login TELSSRVCD_Auth, mapped user
2025-12-12+	100-tracked	Standard DbUp migration framework
2025-12-12+	150-untracked	Created identity views (v_Identity_ByUserAccount, v_Identity_ByDsUser)
2025-12-12+	200-untracked	Created 14 stored procedures (identity, revocation, security editor)

Key Schema Patterns

Single Owned Table: Auth only owns Auth.Revocations — all other data is read from existing databases
View-Based Identity: Two comprehensive views abstract the complexity of 15+ table joins across 5 databases
Composite Primary Keys: Revocations uses Domain + Identifier; many cross-DB tables use composite keys
Password Lockout: Separate cPasswordLockout table tracks consecutive failures; reset on success via MERGE
Recursive Hierarchies: Security groups (DsGroupReln) and teams (DsTeamReln) use self-referential relationships
Dynamic Facility Access: Computed via STRING_AGG in views, supporting both Operator and Owner persona hierarchies