User login/activity auditing: UserActivityLogger utility, Audit Log integration, and User rollup fields

## Summary

Add comprehensive user login and activity tracking to MemberJunction. Rather than just adding two timestamp columns, this proposal creates a reusable `UserActivityLogger` utility that any backend service can call, logs events to the existing **MJ: Audit Logs** entity, and rolls up `LastLoginAt`/`LastActivityAt` to the User record for easy querying.

## Problem

MJ currently has **no login event tracking** and **no general activity timeline** for users. The User entity only has basic identity fields and `__mj_CreatedAt`/`__mj_UpdatedAt` timestamps. There's no way to answer:

- When did a user last log in?
- How many unique users were active this week?
- Which users haven't logged in for 30+ days?
- What authentication method did a user use?

MJ has rich audit infrastructure (Record Changes, API Key Usage Logs, MCP Tool Execution Logs) but a gap at the authentication/session layer.

## Existing Infrastructure We'll Leverage

| Entity | What It Tracks | Auto-Populated? |
|---|---|---|
| **MJ: Audit Logs** | Categorized events with UserID, Status, Description, Details (JSON) | Manual — no auto-logger exists |
| **MJ: Audit Log Types** | Categorization of audit events | Seed data |
| **MJ: Record Changes** | Every Save/Update/Delete on tracked entities | Automatic via DB trigger |
| **MJ: API Key Usage Logs** | API key auth events with scope evaluation | Automatic via APIKeyEngine |
| **MJ: MCP Tool Execution Logs** | MCP tool invocations | Automatic via ExecutionLogger |
| **MJ: User Record Logs** | Per-record access tracking (Recent Items in UI) | Client-side via RecentAccessService |

**Key insight:** The Audit Logs entity already has the right schema (UserID, AuditLogTypeID, Status, Description, Details JSON). We just need to populate it.

## Design

### 1. UserActivityLogger — extends BaseSingleton

**Package:** `@memberjunction/core-entities` (alongside existing engine classes like `UserInfoEngine`)

**Base class:** `BaseSingleton` from `@memberjunction/global`

**Why BaseSingleton instead of static `_instance` pattern:**

MJ's `BaseSingleton` (defined in `@memberjunction/global`) stores the singleton instance in the **Global Object Store** (`GetGlobalObjectStore()`) rather than as a static class member. This solves a real problem in MJ's architecture:

- In bundled applications (ESBuild, Vite), the same class code can end up duplicated across multiple execution paths (e.g., a library imported by both MJAPI and a dynamically loaded plugin). Each copy gets its own static members, so a naive `static _instance` pattern creates **multiple "singletons"** — breaking deduplication, throttling, and session tracking.
- `BaseSingleton` uses a global key (`___SINGLETON__UserActivityLogger`) in the environment's global object (`globalThis`/`window`/`global`), guaranteeing exactly one instance regardless of how many code copies exist.
- This is the established MJ pattern used by `BaseEngine`, `BaseEngineRegistry`, `TelemetryManager`, `LocalCacheManager`, and others.

**Why `@memberjunction/core-entities`:**
- Server-only concern but `core-entities` is already a dependency of every backend service (MJServer, MCPServer, MJCLI, etc.)
- Has access to all entity types needed (`MJAuditLogEntity`, `MJUserEntity`)
- Uses standard MJ patterns (`Metadata.GetEntityObject`, entity `.Save()`) — no direct SQL needed
- Follows the established engine class pattern (singleton, initialized via provider)

```typescript
import { BaseSingleton } from '@memberjunction/global';
import { UserInfo, Metadata, LogError } from '@memberjunction/core';
import { MJAuditLogEntity, MJUserEntity } from './generated/entity_subclasses';

export interface LoginEventDetails {
AuthMethod: 'JWT' | 'APIKey' | 'MCPOAuth' | 'SystemKey';
AuthProvider?: string; // 'MSAL', 'Google', 'Okta', 'Auth0', 'Cognito'
SessionId?: string;
IPAddress?: string;
UserAgent?: string;
Source: string; // 'MJServer' | 'MCPServer' | 'MJCLI' | custom
}

export class UserActivityLogger extends BaseSingleton {
// BaseSingleton pattern — single instance via Global Object Store
public static get Instance(): UserActivityLogger {
return super.getInstance();
}

// In-memory session deduplication (TTL-based)
private recentSessions = new Map();
private static readonly SESSION_TTL_MS = 30 * 60 * 1000; // 30 minutes

// Activity update throttle
private lastActivityUpdate = new Map();
private static readonly ACTIVITY_THROTTLE_MS = 5 * 60 * 1000; // 5 minutes

// Cached Audit Log Type IDs (loaded once on first use)
private auditLogTypeIds: Map | null = null;

protected constructor() {
super();
}

/**
* Log a user login event. Deduplicates by session — if the same
* user+session combination is seen within 30 minutes, it's not
* re-logged. This prevents every GraphQL request from creating
* a login event.
*
* Creates an MJ: Audit Logs record AND updates User.LastLoginAt.
* Both operations are fire-and-forget (non-blocking).
*/
public async LogLogin(
userId: string,
details: LoginEventDetails,
contextUser: UserInfo
): Promise {
const sessionKey = `${userId}:${details.SessionId ?? 'no-session'}`;
const now = Date.now();
const lastSeen = this.recentSessions.get(sessionKey);

// Deduplicate: skip if same session seen within TTL
if (lastSeen && (now - lastSeen) < UserActivityLogger.SESSION_TTL_MS) {
// Still track activity even if we skip the login event
this.TrackActivity(userId, contextUser);
return;
}

this.recentSessions.set(sessionKey, now);
this.cleanExpiredSessions();

// Fire-and-forget: create audit log + update user
this.createLoginAuditLog(userId, details, contextUser).catch(LogError);
this.updateUserTimestamp(userId, 'LastLoginAt', contextUser).catch(LogError);
}

/**
* Log a failed login attempt. Always logged (no deduplication).
*/
public async LogLoginFailure(
email: string,
details: LoginEventDetails,
contextUser: UserInfo
): Promise { ... }

/**
* Update User.LastActivityAt, throttled to once per 5 minutes
* per user. Fire-and-forget, non-blocking.
*
* Call this from request middleware — safe to call on every
* request since it self-throttles.
*/
public TrackActivity(userId: string, contextUser: UserInfo): void {
const now = Date.now();
const last = this.lastActivityUpdate.get(userId) ?? 0;

if ((now - last) > UserActivityLogger.ACTIVITY_THROTTLE_MS) {
this.lastActivityUpdate.set(userId, now);
this.updateUserTimestamp(userId, 'LastActivityAt', contextUser).catch(LogError);
}
}

// --- Private helpers ---

private async createLoginAuditLog(
userId: string,
details: LoginEventDetails,
contextUser: UserInfo
): Promise {
const md = new Metadata();
const auditLog = await md.GetEntityObject(
'MJ: Audit Logs', contextUser
);
auditLog.UserID = userId;
auditLog.AuditLogTypeID = await this.getAuditLogTypeId(
`User Login - ${details.AuthMethod}`
);
auditLog.Status = 'Success';
auditLog.Description = `User login via ${details.AuthMethod}` +
(details.AuthProvider ? ` (${details.AuthProvider})` : '') +
` from ${details.Source}`;
auditLog.Details = JSON.stringify({
authMethod: details.AuthMethod,
authProvider: details.AuthProvider,
sessionId: details.SessionId,
ipAddress: details.IPAddress,
userAgent: details.UserAgent,
source: details.Source,
timestamp: new Date().toISOString()
});
await auditLog.Save();
}

private async updateUserTimestamp(
userId: string,
field: 'LastLoginAt' | 'LastActivityAt',
contextUser: UserInfo
): Promise {
const md = new Metadata();
const user = await md.GetEntityObject('MJ: Users', contextUser);
await user.Load(userId);
user[field] = new Date();
await user.Save();
}

private cleanExpiredSessions(): void {
const now = Date.now();
for (const [key, timestamp] of this.recentSessions) {
if ((now - timestamp) > UserActivityLogger.SESSION_TTL_MS) {
this.recentSessions.delete(key);
}
}
}

private async getAuditLogTypeId(typeName: string): Promise {
if (!this.auditLogTypeIds) {
// Load all audit log types once and cache
const rv = new RunView();
const result = await rv.RunView<{ID: string; Name: string}>({
EntityName: 'MJ: Audit Log Types',
ResultType: 'simple',
Fields: ['ID', 'Name']
});
this.auditLogTypeIds = new Map(
result.Results.map(r => [r.Name, r.ID])
);
}
return this.auditLogTypeIds.get(typeName) ?? '';
}
}
```

### 2. Database Migration

#### Add fields to User entity

```sql
ALTER TABLE [${flyway:defaultSchema}].[User]
ADD LastLoginAt DATETIMEOFFSET NULL,
LastActivityAt DATETIMEOFFSET NULL;
```

No default values — NULL means "never logged in" / "no activity tracked yet." CodeGen will add `__mj_UpdatedAt` triggers and FK indexes automatically.

#### Seed Audit Log Types (via mj-sync metadata)

Use mj-sync metadata files instead of SQL migration for audit log type seed data. This is more flexible for ongoing changes and follows the established MJ metadata management pattern.

**File:** `metadata/audit-log-types/.user-login-audit-types.json`

```json
[
{
"entity": "MJ: Audit Log Types",
"fields": {
"Name": "User Login - JWT",
"Description": "User authenticated via JWT bearer token (OAuth/OIDC)"
}
},
{
"entity": "MJ: Audit Log Types",
"fields": {
"Name": "User Login - APIKey",
"Description": "User authenticated via MJ API key (mj_sk_*)"
}
},
{
"entity": "MJ: Audit Log Types",
"fields": {
"Name": "User Login - MCPOAuth",
"Description": "User authenticated via MCP OAuth 2.1 flow"
}
},
{
"entity": "MJ: Audit Log Types",
"fields": {
"Name": "User Login - SystemKey",
"Description": "Backend service authenticated via system API key"
}
},
{
"entity": "MJ: Audit Log Types",
"fields": {
"Name": "User Login Failed",
"Description": "Authentication attempt failed"
}
}
]
```

Push with: `npx mj-sync push --dir metadata/audit-log-types`

### 3. Integration Points

#### A. MJServer — GraphQL Requests (primary)

**File:** `packages/MJServer/src/context.ts` — `contextFunction()`

After `getUserPayload()` succeeds (line ~233), add:

```typescript
// After successful authentication, log login + track activity
UserActivityLogger.Instance.LogLogin(userPayload.userRecord.ID, {
AuthMethod: userPayload.apiKeyId ? 'APIKey' : 'JWT',
AuthProvider: tokenIssuerProvider?.Name,
SessionId: userPayload.sessionId,
IPAddress: req.ip,
UserAgent: req.headers['user-agent'],
Source: 'MJServer'
}, userPayload.userRecord);
```

This is the chokepoint for all authenticated GraphQL requests. The `LogLogin()` method self-deduplicates by session, so calling it on every request is safe — only the first request per 30-minute window creates an audit log entry. `TrackActivity()` is called internally and throttles to once per 5 minutes per user.

#### B. MCP Server — OAuth/Token Auth

**File:** `packages/AI/MCPServer/src/auth/` — after token validation

```typescript
UserActivityLogger.Instance.LogLogin(contextUser.ID, {
AuthMethod: 'MCPOAuth',
SessionId: connectionId,
IPAddress: req.ip,
UserAgent: req.headers['user-agent'],
Source: 'MCPServer'
}, contextUser);
```

#### C. MJCLI — Command-Line Tools

**File:** `packages/MJCLI/src/` — after user context is established

CLI tools typically authenticate once at startup. Log a single login event:

```typescript
UserActivityLogger.Instance.LogLogin(contextUser.ID, {
AuthMethod: 'JWT', // or 'SystemKey' depending on auth mode
Source: 'MJCLI'
}, contextUser);
```

#### D. Any Future Backend Service

Any code that establishes a user context can call:

```typescript
import { UserActivityLogger } from '@memberjunction/core-entities';

// After authenticating the user:
UserActivityLogger.Instance.LogLogin(userId, {
AuthMethod: 'JWT',
Source: 'MyService'
}, contextUser);

// During operations (safe to call frequently — self-throttles):
UserActivityLogger.Instance.TrackActivity(userId, contextUser);
```

Because `UserActivityLogger` extends `BaseSingleton`, even if `@memberjunction/core-entities` is imported by multiple code paths in a bundled application, `UserActivityLogger.Instance` always returns the same object — session deduplication and activity throttling work correctly across the entire process.

### 4. What This Enables

#### Direct Queries on User Entity

```sql
-- Users who haven't logged in for 30 days
SELECT Name, Email, LastLoginAt
FROM [__mj].[User]
WHERE LastLoginAt < DATEADD(day, -30, GETDATE()) AND IsActive = 1;

-- Most recently active users
SELECT Name, Email, LastActivityAt
FROM [__mj].[User]
ORDER BY LastActivityAt DESC;
```

#### Audit Log Analytics

```sql
-- Login events by auth method this week
SELECT
alt.Name AS AuthMethod,
COUNT(*) AS LoginCount,
COUNT(DISTINCT al.UserID) AS UniqueUsers
FROM [__mj].[AuditLog] al
JOIN [__mj].[AuditLogType] alt ON al.AuditLogTypeID = alt.ID
WHERE alt.Name LIKE 'User Login%'
AND al.__mj_CreatedAt > DATEADD(day, -7, GETDATE())
GROUP BY alt.Name;

-- Failed login attempts
SELECT al.Description, al.Details, al.__mj_CreatedAt
FROM [__mj].[AuditLog] al
JOIN [__mj].[AuditLogType] alt ON al.AuditLogTypeID = alt.ID
WHERE alt.Name = 'User Login Failed'
ORDER BY al.__mj_CreatedAt DESC;
```

#### Full User Activity Timeline (Union of All Log Sources)

```sql
SELECT Timestamp, EventType, Description FROM (
-- Login events
SELECT al.__mj_CreatedAt AS Timestamp, 'Login' AS EventType, al.Description
FROM [__mj].[AuditLog] al
JOIN [__mj].[AuditLogType] alt ON al.AuditLogTypeID = alt.ID
WHERE al.UserID = @UserID AND alt.Name LIKE 'User Login%'

UNION ALL

-- Data changes
SELECT rc.ChangedAt, rc.Type + ' ' + e.Name, rc.ChangesDescription
FROM [__mj].[RecordChange] rc
JOIN [__mj].[Entity] e ON rc.EntityID = e.ID
WHERE rc.UserID = @UserID

UNION ALL

-- API key usage
SELECT aku.__mj_CreatedAt, 'API Call', aku.Endpoint + ' ' + ISNULL(aku.Operation, '')
FROM [__mj].[APIKeyUsageLog] aku
JOIN [__mj].[APIKey] ak ON aku.APIKeyID = ak.ID
WHERE ak.UserID = @UserID
) timeline
ORDER BY Timestamp DESC;
```

### 5. Performance Characteristics

| Operation | Frequency | Cost | Notes |
|---|---|---|---|
| Login audit log write | Once per 30-min session window | 1 entity Save (fire-and-forget) | Deduped by session ID |
| User.LastLoginAt update | Once per 30-min session window | 1 entity Load + Save (fire-and-forget) | Same dedup as login |
| User.LastActivityAt update | Once per 5-min per user | 1 entity Load + Save (fire-and-forget) | Throttled in-memory |
| Session map cleanup | On each LogLogin call | O(n) scan of expired entries | Bounded by active user count |

**Write amplification is minimal:** For a user making 100 GraphQL requests per hour, this generates ~2 audit log entries and ~12 activity updates per hour, instead of 100.

### 6. Files Affected

| File | Change | Package |
|---|---|---|
| New: `UserActivityLogger.ts` | Utility class extending BaseSingleton | `@memberjunction/core-entities` |
| `packages/MJCoreEntities/src/index.ts` | Export UserActivityLogger | `@memberjunction/core-entities` |
| `packages/MJServer/src/context.ts` | Call LogLogin + TrackActivity | `@memberjunction/server` |
| `packages/AI/MCPServer/src/auth/` | Call LogLogin | `@memberjunction/ai-mcp-server` |
| `packages/MJCLI/src/` | Call LogLogin on startup | `@memberjunction/cli` |
| `migrations/` | Add User columns (LastLoginAt, LastActivityAt) | Database |
| `metadata/audit-log-types/` | Audit Log Type seed data via mj-sync | Metadata |
| CodeGen run | Regenerate UserEntity with new fields | Automatic |

### 7. What We Explicitly Don't Build

- **No per-CRUD activity configuration** — Record Changes already tracks every entity operation automatically
- **No per-request logging** — Too noisy; login deduplication + activity throttling gives the right granularity
- **No new entities** — Reuse existing MJ: Audit Logs with new Audit Log Type seed data
- **No client-side tracking** — This is server-only; MJ: User Record Logs already handles client-side "recently accessed" tracking
- **No session entity** — Session deduplication is in-memory only; if needed later, a Session entity can be added without changing the logger interface