🗄️ Enterprise Database Architecture

SaaS Database Architecture Strategy ═══════════════════════════════════════════════════════════════════════════════ ┌─────────────────────────────────────────────────────────────────────────────┐ │ SHARED-SCHEMA MULTI-TENANT MODEL │ ├─────────────────────────────────────────────────────────────────────────────┤ │ │ │ ┌─────────────────────────────────────────────────────────────────────┐ │ │ │ SQL SERVER DATABASE │ │ │ │ │ │ │ │ ┌─────────────────────────────────────────────────────────────┐ │ │ │ │ │ TENANT-SCOPED TABLES (OrganizationId FK required) │ │ │ │ │ │ ───────────────────────────────────────────────────────── │ │ │ │ │ │ │ │ │ │ │ │ Organizations ──► Users ──► Sessions ──► AIUsageLogs │ │ │ │ │ │ │ │ │ │ │ │ │ │ │ │ │ ▼ ▼ ▼ │ │ │ │ │ │ └─────────► AuditLogs ──► ExportJobs ──► Analytics │ │ │ │ │ │ │ │ │ │ │ │ KEY RULE: EVERY query MUST include OrganizationId filter │ │ │ │ │ │ │ │ │ │ │ └─────────────────────────────────────────────────────────────┘ │ │ │ │ │ │ │ │ ┌─────────────────────────────────────────────────────────────┐ │ │ │ │ │ GLOBAL TABLES (No OrganizationId - Shared definitions) │ │ │ │ │ │ ───────────────────────────────────────────────────────── │ │ │ │ │ │ │ │ │ │ │ │ • Plans (Subscription tiers) │ │ │ │ │ │ • SystemRoles (Admin, Manager, User definitions) │ │ │ │ │ │ • ApplicationCatalog (Known AI applications) │ │ │ │ │ │ │ │ │ │ │ └─────────────────────────────────────────────────────────────┘ │ │ │ │ │ │ │ └─────────────────────────────────────────────────────────────────────┘ │ │ │ │ TENANT ISOLATION STRATEGY: │ │ ───────────────────────── │ │ │ │ Organization A (org-a-123) Organization B (org-b-456) │ │ ┌────────────────────────┐ ┌────────────────────────┐ │ │ │ WHERE OrganizationId = │ │ WHERE OrganizationId = │ │ │ │ 'org-a-123' │ │ 'org-b-456' │ │ │ │ │ │ │ │ │ │ Users: Alice, Bob │ │ Users: Carol, Dave │ │ │ │ Sessions: 1,245 │ │ Sessions: 892 │ │ │ │ AI Usage: 45h │ │ AI Usage: 23h │ │ │ │ │ │ │ │ │ │ NEVER sees Org B data │ │ NEVER sees Org A data │ │ │ └────────────────────────┘ └────────────────────────┘ │ │ │ └─────────────────────────────────────────────────────────────────────────────┘

Complete Entity Relationship Map ═══════════════════════════════════════════════════════════════════════════════ ┌─────────────────────────────────────────────────────────────────────────────┐ │ ENTITY RELATIONSHIP HIERARCHY │ ├─────────────────────────────────────────────────────────────────────────────┤ │ │ │ ORGANIZATION (Root Entity) │ │ ┌─────────────────────────────────────────────────────────────────────────┐│ │ │ PK: Id, Name, ApiKey, PlanType, Status, CreatedAt ││ │ │ FK: PlanId ──► Plans ││ │ │ TENANT SCOPE: Global table (no org filter needed for lookup) ││ │ └─────────────────────────────────────────────────────────────────────────┘│ │ │ │ │ │ 1:N │ │ ▼ │ │ USERS │ │ ┌─────────────────────────────────────────────────────────────────────────┐│ │ │ PK: Id, Email, Name, PasswordHash, Role, Status ││ │ │ FK: OrganizationId (REQUIRED) ││ │ │ TENANT SCOPE: WHERE OrganizationId = @orgId ││ │ │ RELATIONSHIPS: ││ │ │ • 1:N ──► Sessions ││ │ │ • 1:N ──► AuditLogs ││ │ │ • 1:N ──► ExportJobs ││ │ │ • M:N ──► Roles (via UserRoles junction) ││ │ │ • 1:N ──► Devices (optional) ││ │ └─────────────────────────────────────────────────────────────────────────┘│ │ │ │ │ │ 1:N │ │ ▼ │ │ SESSIONS (High-Volume Table) │ │ ┌─────────────────────────────────────────────────────────────────────────┐│ │ │ PK: Id, UserId, OrganizationId, StartTime, EndTime, Duration ││ │ │ FK: UserId, OrganizationId (both REQUIRED) ││ │ │ TENANT SCOPE: WHERE OrganizationId = @orgId ││ │ │ RELATIONSHIPS: ││ │ │ • 1:N ──► AIUsageLogs ││ │ │ • 1:N ──► ApplicationUsage ││ │ │ • 1:N ──► WebsiteUsage ││ │ │ • 1:N ──► ProductivityIntervals ││ │ │ • N:1 ──► Devices (optional) ││ │ │ VOLUME: 10M+ rows, fastest growing ││ │ └─────────────────────────────────────────────────────────────────────────┘│ │ │ │ │ │ 1:N │ │ ▼ │ │ AI_USAGE_LOGS (High-Volume Child Table) │ │ ┌─────────────────────────────────────────────────────────────────────────┐│ │ │ PK: Id, SessionId, OrganizationId, Application, Domain, Duration ││ │ │ FK: SessionId, OrganizationId (both REQUIRED) ││ │ │ TENANT SCOPE: WHERE OrganizationId = @orgId ││ │ │ RELATIONSHIPS: ││ │ │ • N:1 ──► Sessions ││ │ │ • Can aggregate to SessionDailySummary ││ │ │ VOLUME: 20M+ rows (2x sessions) ││ │ └─────────────────────────────────────────────────────────────────────────┘│ │ │ │ PARALLEL ENTITIES (Same hierarchy level): │ │ │ │ DEVICES │ │ ┌─────────────────────────────────────────────────────────────────────────┐│ │ │ PK: Id, OrganizationId, UserId, Name, Type, AgentVersion, LastSeen ││ │ │ FK: OrganizationId, UserId (optional) ││ │ │ TENANT SCOPE: WHERE OrganizationId = @orgId ││ │ │ RELATIONSHIPS: 1:N ──► Sessions (optional) ││ │ │ ⚠️ ASSUMPTION: Device table structure inferred from dashboard usage ││ │ └─────────────────────────────────────────────────────────────────────────┘│ │ │ │ ROLES & PERMISSIONS (RBAC System) │ │ ┌─────────────────────────────────────────────────────────────────────────┐│ │ │ ROLES Table: ││ │ │ PK: Id, Name, OrganizationId (nullable for system roles), Level ││ │ │ FK: OrganizationId (null = system role, set = custom role) ││ │ │ ││ │ │ USERROLES Junction Table: ││ │ │ PK: (UserId, RoleId) composite ││ │ │ FK: UserId ──► Users, RoleId ──► Roles ││ │ │ TENANT SCOPE: Implicit via User.OrganizationId ││ │ └─────────────────────────────────────────────────────────────────────────┘│ │ │ │ AUDIT & REPORTING ENTITIES: │ │ │ │ AUDIT_LOGS │ │ ┌─────────────────────────────────────────────────────────────────────────┐│ │ │ PK: Id, OrganizationId, UserId, Action, EntityType, Timestamp ││ │ │ FK: OrganizationId, UserId (optional) ││ │ │ TENANT SCOPE: WHERE OrganizationId = @orgId ││ │ │ VOLUME: 5M+ rows, compliance-critical ││ │ └─────────────────────────────────────────────────────────────────────────┘│ │ │ │ EXPORT_JOBS │ │ ┌─────────────────────────────────────────────────────────────────────────┐│ │ │ PK: Id, OrganizationId, UserId, Status, Format, FileUrl, CreatedAt ││ │ │ FK: OrganizationId, UserId ││ │ │ TENANT SCOPE: WHERE OrganizationId = @orgId ││ │ │ RELATIONSHIPS: Triggered by Reporting queries ││ │ └─────────────────────────────────────────────────────────────────────────┘│ │ │ │ SESSION_DAILY_SUMMARY (Aggregation Table) │ │ ┌─────────────────────────────────────────────────────────────────────────┐│ │ │ PK: Id, OrganizationId, Date, TotalSessions, TotalDuration ││ │ │ FK: OrganizationId ││ │ │ TENANT SCOPE: WHERE OrganizationId = @orgId ││ │ │ PURPOSE: Pre-aggregated analytics for dashboard performance ││ │ │ UPDATED BY: Background job or trigger ││ │ └─────────────────────────────────────────────────────────────────────────┘│ │ │ └─────────────────────────────────────────────────────────────────────────────┘

Agent Onboarding Data Flow ═══════════════════════════════════════════════════════════════════════════════ ┌─────────────────────────────────────────────────────────────────────────────┐ │ PHASE 1: ORGANIZATION SETUP │ ├─────────────────────────────────────────────────────────────────────────────┤ │ │ │ Step 1: Organization Registration │ │ ───────────────────────────────────────────────────────────────────────── │ │ │ │ Actor: SuperAdmin or Self-Registration │ │ │ │ ┌─────────────────────────────────────────────────────────────────────────┐│ │ │ INSERT INTO Organizations ││ │ │ (Id, Name, PlanId, ApiKey, Status, CreatedAt) ││ │ │ VALUES ││ │ │ (NEWID(), 'Acme Corp', 'plan-pro', NEWID(), 'Active', GETUTCDATE())││ │ │ ││ │ │ OUTPUT: OrganizationId = 'org-abc-123' (propagates to all child rows) ││ │ └─────────────────────────────────────────────────────────────────────────┘│ │ │ │ │ ▼ │ │ ┌─────────────────────────────────────────────────────────────────────────┐│ │ │ INSERT INTO Subscriptions ││ │ │ (Id, OrganizationId, PlanId, StartDate, EndDate, Status) ││ │ │ VALUES ││ │ │ (NEWID(), 'org-abc-123', 'plan-pro', GETDATE(), DATEADD(year,1,...),││ │ │ 'Active') ││ │ │ ││ │ │ TENANT ISOLATION: OrganizationId = 'org-abc-123' established ││ │ └─────────────────────────────────────────────────────────────────────────┘│ │ │ └─────────────────────────────────────────────────────────────────────────────┘ ┌─────────────────────────────────────────────────────────────────────────────┐ │ PHASE 2: ADMIN USER CREATION │ ├─────────────────────────────────────────────────────────────────────────────┤ │ │ │ Step 2: Admin User Registration │ │ ───────────────────────────────────────────────────────────────────────── │ │ │ │ Actor: Registration Form → AuthController │ │ │ │ ┌─────────────────────────────────────────────────────────────────────────┐│ │ │ INSERT INTO Users ││ │ │ (Id, OrganizationId, Email, Name, PasswordHash, Role, Status) ││ │ │ VALUES ││ │ │ (NEWID(), 'org-abc-123', 'admin@acme.com', 'Admin User', ││ │ │ HASH('password'), 'Admin', 'Active') ││ │ │ ││ │ │ CRITICAL: OrganizationId FK links user to org (tenant isolation) ││ │ │ OUTPUT: UserId = 'usr-def-456' ││ │ └─────────────────────────────────────────────────────────────────────────┘│ │ │ │ │ ▼ │ │ ┌─────────────────────────────────────────────────────────────────────────┐│ │ │ INSERT INTO UserRoles (Junction Table) ││ │ │ SELECT NEWID(), 'usr-def-456', Id, GETUTCDATE() ││ │ │ FROM Roles WHERE Name = 'Admin' AND OrganizationId IS NULL ││ │ │ ││ │ │ RBAC: User now has Admin role (level 50) ││ │ └─────────────────────────────────────────────────────────────────────────┘│ │ │ │ │ ▼ │ │ ┌─────────────────────────────────────────────────────────────────────────┐│ │ │ JWT GENERATION (AuthController) ││ │ │ ───────────────────────────────────────────────────────────────────── ││ │ │ ││ │ │ Claims: ││ │ │ { ││ │ │ "sub": "usr-def-456", ← UserId for self-scoped queries ││ │ │ "org": "org-abc-123", ← CRITICAL: OrganizationId for tenant ││ │ │ "role": "Admin", ││ │ │ "email": "admin@acme.com" ││ │ │ } ││ │ │ ││ │ │ TOKEN USAGE: Every API call includes org claim for tenant isolation ││ │ └─────────────────────────────────────────────────────────────────────────┘│ │ │ └─────────────────────────────────────────────────────────────────────────────┘ ┌─────────────────────────────────────────────────────────────────────────────┐ │ PHASE 3: DEVICE & AGENT SETUP │ ├─────────────────────────────────────────────────────────────────────────────┤ │ │ │ Step 3: Device Registration │ │ ───────────────────────────────────────────────────────────────────────── │ │ │ │ Actor: Admin Portal → Device Installation │ │ ⚠️ ASSUMPTION: Device table exists (inferred from dashboard top-devices) │ │ │ │ ┌─────────────────────────────────────────────────────────────────────────┐│ │ │ INSERT INTO Devices (⚠️ ASSUMED STRUCTURE) ││ │ │ (Id, OrganizationId, UserId, Name, Type, AgentVersion, LastSeen) ││ │ │ VALUES ││ │ │ (NEWID(), 'org-abc-123', 'usr-def-456', 'Alice-PC', 'Windows', ││ │ │ '1.2.3', GETUTCDATE()) ││ │ │ ││ │ │ OUTPUT: DeviceId = 'dev-ghi-789' ││ │ │ TENANT ISOLATION: OrganizationId = 'org-abc-123' ││ │ └─────────────────────────────────────────────────────────────────────────┘│ │ │ │ │ ▼ │ │ Step 4: Agent Installation & Authentication │ │ ───────────────────────────────────────────────────────────────────────── │ │ │ │ Actor: Agent Installer → Agent Onboarding API │ │ │ │ ┌─────────────────────────────────────────────────────────────────────────┐│ │ │ AGENT INSTALLATION FLOW: ││ │ │ ││ │ │ 1. Agent installer prompts for credentials ││ │ │ 2. User enters: admin@acme.com / password ││ │ │ 3. Agent calls: POST /api/auth/login ││ │ │ 4. Receives JWT with org claim: "org-abc-123" ││ │ │ 5. Agent stores: OrgId + ApiKey locally (encrypted) ││ │ │ 6. Agent registers device: POST /api/devices/register ││ │ │ 7. Database: Devices row created with OrgId = 'org-abc-123' ││ │ │ ││ │ │ DEVICE LINKAGE: ││ │ │ Device.OrganizationId = 'org-abc-123' ││ │ │ Device.UserId = 'usr-def-456' (optional, can be null) ││ │ │ Device.ApiKey = Org.ApiKey (for future auth) ││ │ └─────────────────────────────────────────────────────────────────────────┘│ │ │ └─────────────────────────────────────────────────────────────────────────────┘ ┌─────────────────────────────────────────────────────────────────────────────┐ │ PHASE 4: RUNTIME ACTIVATION │ ├─────────────────────────────────────────────────────────────────────────────┤ │ │ │ Step 5: Agent Runtime Data Ingestion Begins │ │ ───────────────────────────────────────────────────────────────────────── │ │ │ │ ┌─────────────────────────────────────────────────────────────────────────┐│ │ │ ONGOING: Session Creation ││ │ │ ───────────────────────────────────────────────────────────────────── ││ │ │ ││ │ │ TRIGGER: Agent detects user activity ││ │ │ ││ │ │ INSERT INTO Sessions ││ │ │ (Id, UserId, OrganizationId, DeviceId, StartTime, Status) ││ │ │ VALUES ││ │ │ (NEWID(), 'usr-def-456', 'org-abc-123', 'dev-ghi-789', ││ │ │ GETUTCDATE(), 'Active') ││ │ │ ││ │ │ CRITICAL: OrganizationId = 'org-abc-123' on EVERY session row ││ │ │ This enables tenant isolation for all child entities ││ │ │ OUTPUT: SessionId = 'ses-jkl-012' ││ │ └─────────────────────────────────────────────────────────────────────────┘│ │ │ │ │ ▼ │ │ ┌─────────────────────────────────────────────────────────────────────────┐│ │ │ ONGOING: AI Usage Detection ││ │ │ ───────────────────────────────────────────────────────────────────── ││ │ │ ││ │ │ TRIGGER: Agent detects AI application (ChatGPT, Copilot, etc.) ││ │ │ ││ │ │ INSERT INTO AIUsageLogs ││ │ │ (Id, SessionId, OrganizationId, Application, Domain, StartTime) ││ │ │ VALUES ││ │ │ (NEWID(), 'ses-jkl-012', 'org-abc-123', 'ChatGPT', ││ │ │ 'chat.openai.com', GETUTCDATE()) ││ │ │ ││ │ │ TENANT PROPAGATION: Session.OrganizationId ──► AIUsageLogs.OrgId ││ │ └─────────────────────────────────────────────────────────────────────────┘│ │ │ │ │ ▼ │ │ ┌─────────────────────────────────────────────────────────────────────────┐│ │ │ AGGREGATION: Dashboard Data ││ │ │ ───────────────────────────────────────────────────────────────────── ││ │ │ ││ │ │ BACKGROUND JOB (nightly): ││ │ │ UPDATE SessionDailySummary ││ │ │ SET TotalSessions = (SELECT COUNT(*) FROM Sessions ││ │ │ WHERE OrganizationId = 'org-abc-123' ││ │ │ AND CAST(StartTime AS DATE) = @date) ││ │ │ WHERE OrganizationId = 'org-abc-123' AND Date = @date ││ │ │ ││ │ │ DASHBOARD QUERY: ││ │ │ SELECT * FROM SessionDailySummary ││ │ │ WHERE OrganizationId = 'org-abc-123' ││ │ │ ORDER BY Date DESC ││ │ └─────────────────────────────────────────────────────────────────────────┘│ │ │ └─────────────────────────────────────────────────────────────────────────────┘

Agent Runtime Data Pipeline ═══════════════════════════════════════════════════════════════════════════════ ┌─────────────────────────────────────────────────────────────────────────────┐ │ AGENT RUNTIME LAYER │ ├─────────────────────────────────────────────────────────────────────────────┤ │ │ │ AGENT (Desktop Application) │ │ ┌─────────────────────────────────────────────────────────────────────────┐│ │ │ MONITORING MODULES: ││ │ │ ───────────────────────────────────────────────────────────────────── ││ │ │ ││ │ │ 1. Activity Tracker ││ │ │ • Detects active window ││ │ │ • Tracks idle time ││ │ │ • Records application usage ││ │ │ ││ │ │ 2. AI Detection Engine ││ │ │ • Monitors browser URLs (ChatGPT, Claude, Copilot) ││ │ │ • Detects AI desktop apps ││ │ │ • Captures AI usage duration ││ │ │ ││ │ │ 3. Sync Engine ││ │ │ • Batches events locally │ │ │ • Uploads to API every 30 seconds │ │ │ • Handles offline buffering │ │ │ ││ │ │ AUTHENTICATION: ││ │ │ • Stores JWT (refresh periodically) │ │ │ • Includes OrgId in API calls │ │ │ • Device fingerprint for verification │ │ │ ││ │ └─────────────────────────────────────────────────────────────────────────┘│ │ │ │ │ │ HTTP/HTTPS │ │ ▼ │ └─────────────────────────────────────────────────────────────────────────────┘ ┌─────────────────────────────────────────────────────────────────────────────┐ │ API INGESTION LAYER │ ├─────────────────────────────────────────────────────────────────────────────┤ │ │ │ AGENT API ENDPOINTS (⚠️ ASSUMED - Inferred from data flow) │ │ ┌─────────────────────────────────────────────────────────────────────────┐│ │ │ ││ │ │ POST /api/agent/session/start ││ │ │ Body: { deviceId, userId, startTime, organizationId } ││ │ │ Response: { sessionId } ││ │ │ Action: INSERT INTO Sessions ││ │ │ ││ │ │ POST /api/agent/activity ││ │ │ Body: { sessionId, application, windowTitle, timestamp } ││ │ │ Action: INSERT INTO ApplicationUsage (⚠️ assumed table) ││ │ │ ││ │ │ POST /api/agent/ai-usage ││ │ │ Body: { sessionId, aiApplication, domain, duration } ││ │ │ Action: INSERT INTO AIUsageLogs ││ │ │ TENANT CHECK: JWT org claim must match session.OrganizationId ││ │ │ ││ │ │ POST /api/agent/heartbeat ││ │ │ Body: { deviceId, timestamp, status } ││ │ │ Action: UPDATE Devices SET LastSeen = GETUTCDATE() ││ │ │ ││ │ │ POST /api/agent/session/end ││ │ │ Body: { sessionId, endTime, totalDuration, aiUsageTime } ││ │ │ Action: UPDATE Sessions SET EndTime = @endTime, Status = 'Completed' ││ │ │ ││ │ │ ⚠️ NOTE: These agent endpoints are INFERRED from data structures. ││ │ │ Actual endpoint names may differ. ││ │ │ ││ │ └─────────────────────────────────────────────────────────────────────────┘│ │ │ │ │ ▼ │ └─────────────────────────────────────────────────────────────────────────────┘ ┌─────────────────────────────────────────────────────────────────────────────┐ │ DATABASE STORAGE LAYER │ ├─────────────────────────────────────────────────────────────────────────────┤ │ │ │ RAW DATA TABLES (High Write Volume) │ │ ┌─────────────────────────────────────────────────────────────────────────┐│ │ │ ││ │ │ Sessions (10M+ rows, growing) ││ │ │ ┌─────────────────────────────────────────────────────────────────┐ ││ │ │ │ PK: Id, UserId, OrganizationId, DeviceId, StartTime, EndTime │ ││ │ │ │ Status: 'Active' | 'Completed' | 'Idle' │ ││ │ │ │ │ ││ │ │ │ INDEX: IX_Sessions_OrgId_StartTime (⚠️ MISSING - HIGH PRIORITY)│ ││ │ │ │ │ ││ │ │ │ QUERY PATTERN: │ ││ │ │ │ SELECT * FROM Sessions │ ││ │ │ │ WHERE OrganizationId = @orgId │ ││ │ │ │ AND StartTime BETWEEN @from AND @to │ ││ │ │ └─────────────────────────────────────────────────────────────────┘ ││ │ │ ││ │ │ AIUsageLogs (20M+ rows, 2x sessions) ││ │ │ ┌─────────────────────────────────────────────────────────────────┐ ││ │ │ │ PK: Id, SessionId, OrganizationId, Application, Domain │ ││ │ │ │ Duration: AI usage time in seconds │ ││ │ │ │ │ ││ │ │ │ TENANT ISOLATION: │ ││ │ │ │ WHERE OrganizationId = @orgId ← ALWAYS required │ ││ │ │ └─────────────────────────────────────────────────────────────────┘ ││ │ │ ││ │ └─────────────────────────────────────────────────────────────────────────┘│ │ │ │ │ ▼ │ │ AGGREGATION TABLES (Background Process) │ │ ┌─────────────────────────────────────────────────────────────────────────┐│ │ │ ││ │ │ SessionDailySummary (500K rows) ││ │ │ ┌─────────────────────────────────────────────────────────────────┐ ││ │ │ │ PK: Id, OrganizationId, Date │ ││ │ │ │ TotalSessions: COUNT from Sessions │ ││ │ │ │ TotalDuration: SUM(Duration) from Sessions │ ││ │ │ │ TotalAIUsage: SUM(AIUsageTime) from Sessions │ ││ │ │ │ │ ││ │ │ │ UPDATED BY: Nightly background job or SQL trigger │ ││ │ │ │ │ ││ │ │ │ DASHBOARD QUERY (Fast): │ ││ │ │ │ SELECT Date, TotalSessions, TotalAIUsage │ ││ │ │ │ FROM SessionDailySummary │ ││ │ │ │ WHERE OrganizationId = @orgId │ ││ │ │ │ ORDER BY Date DESC │ ││ │ │ └─────────────────────────────────────────────────────────────────┘ ││ │ │ ││ │ └─────────────────────────────────────────────────────────────────────────┘│ │ │ │ │ ▼ │ └─────────────────────────────────────────────────────────────────────────────┘ ┌─────────────────────────────────────────────────────────────────────────────┐ │ DASHBOARD & REPORTING LAYER │ ├─────────────────────────────────────────────────────────────────────────────┤ │ │ │ ADMIN DASHBOARD QUERY FLOW: │ │ ┌─────────────────────────────────────────────────────────────────────────┐│ │ │ ││ │ │ 1. ORGANIZATION SCOPED (Fast - Pre-aggregated): ││ │ │ GET /api/admindashboard ││ │ │ → SELECT * FROM SessionDailySummary ││ │ │ WHERE OrganizationId = @orgId (from JWT) ││ │ │ → Returns: Daily totals for last 30 days ││ │ │ ││ │ │ 2. USER ANALYTICS (Slower - Raw data): ││ │ │ GET /api/analytics/user/{id} ││ │ │ → SELECT * FROM Sessions ││ │ │ WHERE OrganizationId = @orgId AND UserId = @userId ││ │ │ → JOIN AIUsageLogs ON SessionId ││ │ │ → ⚠️ Can be slow without IX_OrgId_StartTime ││ │ │ ││ │ │ 3. TOP USERS (Aggregated): ││ │ │ GET /api/admindashboard/top-users ││ │ │ → SELECT UserId, SUM(Duration) as TotalTime ││ │ │ FROM Sessions ││ │ │ WHERE OrganizationId = @orgId ││ │ │ GROUP BY UserId ││ │ │ ORDER BY TotalTime DESC ││ │ │ ││ │ └─────────────────────────────────────────────────────────────────────────┘│ │ │ └─────────────────────────────────────────────────────────────────────────────┘

Analytics & Reporting Database Dependencies ═══════════════════════════════════════════════════════════════════════════════ ┌─────────────────────────────────────────────────────────────────────────────┐ │ RAW DATA SOURCES (Real-time Write) │ ├─────────────────────────────────────────────────────────────────────────────┤ │ │ │ Agent ──► Sessions ──► AIUsageLogs ──► ApplicationUsage ──► WebsiteUsage │ │ (10M rows) (20M rows) (⚠️ assumed) (⚠️ assumed) │ │ │ │ Characteristics: │ │ • High write frequency (every 30 seconds per active device) │ │ • INSERT-heavy │ │ • OrganizationId required on every row │ │ • No UPDATE (immutable log pattern) │ │ │ └─────────────────────────────────────────────────────────────────────────────┘ │ │ Background Aggregation ▼ ┌─────────────────────────────────────────────────────────────────────────────┐ │ SUMMARY TABLES (Pre-aggregated) │ ├─────────────────────────────────────────────────────────────────────────────┤ │ │ │ SessionDailySummary │ │ ┌─────────────────────────────────────────────────────────────────────────┐│ │ │ ││ │ │ AGGREGATION LOGIC (Nightly Job): ││ │ │ ───────────────────────────────────────────────────────────────────── ││ │ │ ││ │ │ INSERT INTO SessionDailySummary ││ │ │ (Id, OrganizationId, Date, TotalSessions, TotalDuration, ││ │ │ TotalAIUsage, ActiveUsers) ││ │ │ SELECT ││ │ │ NEWID(), ││ │ │ OrganizationId, ││ │ │ CAST(StartTime AS DATE), ││ │ │ COUNT(*), ││ │ │ SUM(Duration), ││ │ │ SUM(AIUsageTime), ││ │ │ COUNT(DISTINCT UserId) ││ │ │ FROM Sessions ││ │ │ WHERE StartTime >= @yesterday AND StartTime < @today ││ │ │ GROUP BY OrganizationId, CAST(StartTime AS DATE) ││ │ │ ││ │ │ ⚠️ PERFORMANCE RISK: Without IX_Sessions_OrgId_StartTime, ││ │ │ this query scans millions of rows. ││ │ │ ││ │ └─────────────────────────────────────────────────────────────────────────┘│ │ │ │ Benefits of Summary Tables: │ │ • Dashboard queries: O(30) rows vs O(1M) raw rows │ │ • Report generation: Seconds vs minutes │ │ • Cross-tenant analytics: Fast aggregation across orgs │ │ │ └─────────────────────────────────────────────────────────────────────────────┘ │ │ Query ▼ ┌─────────────────────────────────────────────────────────────────────────────┐ │ DASHBOARD & REPORTING CONSUMERS │ ├─────────────────────────────────────────────────────────────────────────────┤ │ │ │ Dashboard ──► SessionDailySummary (Fast) │ │ │ │ │ ├── Top Users ──► Sessions (Slower) │ │ ├── AI Analytics ──► AIUsageLogs (Medium) │ │ └── Trends ──► TimeAggregationController (Pre-aggregated) │ │ │ │ Reports ──► ExportJobs ──► Background Processing │ │ │ │ │ ├── AI Usage Report ──► AIUsageLogs + Sessions │ │ ├── Organization Report ──► SessionDailySummary + Users │ │ └── Comprehensive Report ──► All tables JOINed │ │ │ │ ⚠️ REPORTING BOTTLENECK: Large date ranges hit raw tables │ │ Solution: Use SessionDailySummary for >30 day ranges │ │ │ └─────────────────────────────────────────────────────────────────────────────┘

OrganizationId Propagation & Tenant Isolation ═══════════════════════════════════════════════════════════════════════════════ ┌─────────────────────────────────────────────────────────────────────────────┐ │ ORGANIZATIONID PROPAGATION CHAIN │ ├─────────────────────────────────────────────────────────────────────────────┤ │ │ │ ROOT: Organizations.Id = 'org-abc-123' │ │ │ │ │ ├──► Users.OrganizationId = 'org-abc-123' │ │ │ ├──► Sessions.OrganizationId = 'org-abc-123' │ │ │ │ ├──► AIUsageLogs.OrganizationId = 'org-abc-123' │ │ │ │ ├──► ApplicationUsage.OrganizationId = 'org-abc-123'│ │ │ │ └──► WebsiteUsage.OrganizationId = 'org-abc-123' │ │ │ │ │ │ │ ├──► AuditLogs.OrganizationId = 'org-abc-123' │ │ │ ├──► ExportJobs.OrganizationId = 'org-abc-123' │ │ │ └──► Devices.OrganizationId = 'org-abc-123' │ │ │ │ │ └──► SessionDailySummary.OrganizationId = 'org-abc-123' │ │ │ │ RULE: Every query MUST include: WHERE OrganizationId = @orgId │ │ │ └─────────────────────────────────────────────────────────────────────────────┘ ┌─────────────────────────────────────────────────────────────────────────────┐ │ TENANT ISOLATION ENFORCEMENT LAYERS │ ├─────────────────────────────────────────────────────────────────────────────┤ │ │ │ LAYER 1: JWT Claims (Trusted Source) │ │ ───────────────────────────────────────────────────────────────────────── │ │ JWT: { "org": "org-abc-123", "role": "Admin" } │ │ ↓ │ │ │ │ LAYER 2: Middleware Extraction │ │ ───────────────────────────────────────────────────────────────────────── │ │ MultiTenantIsolationMiddleware: │ │ • Extracts OrganizationId from JWT │ │ • Attaches to HttpContext │ │ • Validates organization exists & active │ │ ↓ │ │ │ │ LAYER 3: Controller Enforcement │ │ ───────────────────────────────────────────────────────────────────────── │ │ BaseApiController.GetCurrentOrganizationId(): │ │ • Returns OrganizationId from JWT claim │ │ • All controllers inherit this method │ │ ↓ │ │ │ │ LAYER 4: Repository Filtering │ │ ───────────────────────────────────────────────────────────────────────── │ │ Repository Pattern: │ │ _dbContext.Sessions │ │ .Where(s => s.OrganizationId == orgId) ← CRITICAL FILTER │ │ .Where(predicate) │ │ .ToListAsync(); │ │ ↓ │ │ │ │ LAYER 5: Database Query (Final Enforcement) │ │ ───────────────────────────────────────────────────────────────────────── │ │ Generated SQL: │ │ SELECT * FROM Sessions │ │ WHERE OrganizationId = 'org-abc-123' ← Database-level filter │ │ AND StartTime > @date │ │ │ │ DEFENSE IN DEPTH: 5 layers of OrganizationId enforcement │ │ │ └─────────────────────────────────────────────────────────────────────────────┘

Database Scaling Strategy ═══════════════════════════════════════════════════════════════════════════════ ┌─────────────────────────────────────────────────────────────────────────────┐ │ IMMEDIATE (0-3 months) │ ├─────────────────────────────────────────────────────────────────────────────┤ │ │ │ 1. ADD COMPOSITE INDEXES (Critical) │ │ ───────────────────────────────────────────────────────────────────── │ │ │ │ CREATE INDEX IX_Sessions_OrgId_StartTime │ │ ON Sessions (OrganizationId, StartTime) │ │ INCLUDE (Duration, AIUsageTime, UserId); │ │ │ │ Impact: 80% reduction in analytics query time │ │ Effort: 2 hours (online index creation) │ │ │ │ 2. ADD AUDIT LOG INDEX │ │ ───────────────────────────────────────────────────────────────────── │ │ │ │ CREATE INDEX IX_AuditLogs_OrgId_Timestamp │ │ ON AuditLogs (OrganizationId, Timestamp); │ │ │ │ Impact: Audit log queries < 100ms │ │ Effort: 1 hour │ │ │ └─────────────────────────────────────────────────────────────────────────────┘ ┌─────────────────────────────────────────────────────────────────────────────┐ │ SHORT-TERM (3-6 months) │ ├─────────────────────────────────────────────────────────────────────────────┤ │ │ │ 3. TABLE PARTITIONING (High Volume Tables) │ │ ───────────────────────────────────────────────────────────────────── │ │ │ │ Sessions Table: │ │ • Partition by StartTime (monthly) │ │ • Current: 10M rows → Future: 100M+ rows │ │ • Query pruning: Only scan relevant partitions │ │ │ │ Implementation: │ │ CREATE PARTITION FUNCTION pfn_Sessions (DATETIME2) │ │ AS RANGE RIGHT FOR VALUES ('2026-01-01', '2026-02-01', ...); │ │ │ │ Impact: Maintains performance at 100M+ rows │ │ Effort: 2 days (requires maintenance window) │ │ │ │ 4. READ REPLICAS FOR ANALYTICS │ │ ───────────────────────────────────────────────────────────────────── │ │ │ │ • Configure SQL Server Always On read replica │ │ • Route dashboard/reporting queries to replica │ │ • Keep primary for writes (Sessions, AIUsageLogs) │ │ │ │ Impact: Offload 70% of read traffic from primary │ │ Effort: 3 days (infrastructure setup) │ │ │ └─────────────────────────────────────────────────────────────────────────────┘ ┌─────────────────────────────────────────────────────────────────────────────┐ │ LONG-TERM (6-12 months) │ ├─────────────────────────────────────────────────────────────────────────────┤ │ │ │ 5. REDIS CACHING LAYER │ │ ───────────────────────────────────────────────────────────────────── │ │ │ │ • Cache dashboard aggregates (SessionDailySummary) │ │ • Cache user lists for organization │ │ • TTL: 5 minutes for dashboard data │ │ │ │ 6. ANALYTICS WAREHOUSE (OLAP) │ │ ───────────────────────────────────────────────────────────────────── │ │ │ │ • ETL raw data to columnar store (Azure Synapse / Snowflake) │ │ • Complex analytics queries run on warehouse │ │ • Keep 90 days hot data in SQL Server │ │ • Archive older data to warehouse │ │ │ │ 7. DATA ARCHIVAL STRATEGY │ │ ───────────────────────────────────────────────────────────────────── │ │ │ │ Sessions: │ │ • > 2 years: Move to cold storage (Azure Blob) │ │ • > 5 years: Delete (compliance dependent) │ │ │ │ AuditLogs: │ │ • Keep 7 years (compliance requirement) │ │ • Partition by year for efficient queries │ │ │ └─────────────────────────────────────────────────────────────────────────────┘

Future Enterprise Database Architecture ═══════════════════════════════════════════════════════════════════════════════ ┌─────────────────────────────────────────────────────────────────────────────┐ │ CURRENT: Direct Write Architecture │ ├─────────────────────────────────────────────────────────────────────────────┤ │ │ │ Agent ──► API ──► SQL Server (Sessions, AIUsageLogs) │ │ │ │ │ └──► Background Job ──► SessionDailySummary │ │ │ │ Limitations: │ │ • High write load on SQL Server │ │ • Analytics queries impact transactional performance │ │ • No real-time streaming │ │ • Scaling bottleneck at 100M+ rows │ │ │ └─────────────────────────────────────────────────────────────────────────────┘ ↓ │ Future Evolution ↓ ┌─────────────────────────────────────────────────────────────────────────────┐ │ FUTURE: Event-Driven Analytics Architecture │ ├─────────────────────────────────────────────────────────────────────────────┤ │ │ │ AGENT LAYER │ │ ┌─────────────────────────────────────────────────────────────────────────┐│ │ │ Agent ──► API Gateway ││ │ │ │ ││ │ │ ├──► [FAST PATH] ──► SQL Server (Hot Data 90 days) ││ │ │ │ ││ │ │ └──► [EVENT STREAM] ──► Redis Streams / Kafka ││ │ │ │ ││ │ └─────────────────────────────────────────┼───────────────────────────────┘│ │ │ │ │ ▼ │ │ EVENT PROCESSING LAYER │ │ ┌─────────────────────────────────────────────────────────────────────────┐│ │ │ Stream Consumers: ││ │ │ ───────────────────────────────────────────────────────────────────── ││ │ │ ││ │ │ 1. Real-time Aggregator ──► Redis (Dashboard cache) ││ │ │ 2. Analytics Processor ──► ClickHouse / Druid (OLAP) ││ │ │ 3. Audit Archiver ──► Long-term storage (Blob) ││ │ │ 4. ML Feature Store ──► Feature extraction for ML ││ │ │ ││ │ └─────────────────────────────────────────────────────────────────────────┘│ │ │ │ │ ▼ │ │ QUERY LAYER │ │ ┌─────────────────────────────────────────────────────────────────────────┐│ │ │ ││ │ │ Dashboard Queries: ││ │ │ • Real-time: Redis cache (< 10ms) ││ │ │ • Historical: ClickHouse OLAP (< 100ms) ││ │ │ ││ │ │ Ad-hoc Analytics: ││ │ │ • ClickHouse SQL interface ││ │ │ • Columnar compression (10x storage efficiency) ││ │ │ ││ │ │ Audit & Compliance: ││ │ │ • Azure Blob / S3 (cheap long-term storage) ││ │ │ • 7-year retention ││ │ │ ││ │ └─────────────────────────────────────────────────────────────────────────┘│ │ │ │ BENEFITS: │ │ • SQL Server write load reduced 80% │ │ • Analytics queries don't impact transactions │ │ • Real-time dashboard updates via Redis │ │ • OLAP queries 100x faster for large datasets │ │ • 10x storage cost reduction for historical data │ │ │ └─────────────────────────────────────────────────────────────────────────────┘