# OnDeck MVP — Policy Renewal Workflow System

> Minimum Viable Product scope for the OnDeck policy renewal management platform.
> This document defines the feature set required for initial production deployment.

---

## Table of Contents

1. [User Authentication](#1-user-authentication)
2. [Logging](#2-logging)
3. [AMS360 Data Sync](#3-ams360-data-sync)
4. [Task Management & Renewal Workflow](#4-task-management--renewal-workflow)
5. [Renewal Grouping & Policy Exclusion](#5-renewal-grouping--policy-exclusion)

---

## 1. User Authentication

### 1.1 Authentication Providers

| Provider | Purpose | Status |
|----------|---------|--------|
| Local credentials | Development and testing (3 built-in accounts: admin, manager, ae) | Existing |
| Microsoft Entra ID (Azure AD) | Production SSO | Existing |

### 1.2 Account Linking

When a user signs in via Microsoft Entra ID and the email matches an existing local account, the accounts are linked automatically by populating the `entraOid` field on the existing user record.

- First lookup: find user by email where `entraOid` is null (pre-created local account)
- If found: update the record with the Entra Object ID to link the accounts
- Fallback: standard upsert by `entraOid` for net-new users

> **Reference:** `src/lib/auth.ts:110-144`

### 1.3 Role-Based Access Control

Four roles with granular permissions:

| Role | Key Permissions |
|------|----------------|
| **Admin** | Full access — users, clients, tasks, designations, templates, sync, reports, audit |
| **Manager** | Read users; read/write clients, tasks, templates; assign tasks; view reports |
| **Account Executive** | Read/write clients and tasks; read designations |
| **Claims** | Read clients; read/write tasks |

Permission categories: User Management, Client Management, Task Management, Designation Management, Template Management, Sync Operations, Reports, Audit.

Role resolution combines two sources:
- **Entra Group mappings** — `EntraGroupRoleMapping` table maps Azure AD groups to application roles
- **Direct assignments** — `UserRole` join table for explicit per-user role grants

> **Reference:** `src/lib/auth/roles.ts`, `src/lib/auth/permissions.ts`

### 1.4 Session Strategy

- JWT-based sessions (`strategy: 'jwt'`)
- Session callback enriches the token with: `id`, `entraOid`, `roles[]`, merged `permissions` object (union of all assigned role permissions)

---

## 2. Logging

### 2.1 Authentication Logging

| Event | Details |
|-------|---------|
| Sign-in success | Provider, user ID, timestamp |
| Sign-in failure | Provider, email attempted, failure reason |
| Account linking | User ID, linked Entra OID |

### 2.2 Action Logging (Audit)

All user-initiated mutations on entities are recorded in the `AuditLog` model:

| Field | Description |
|-------|-------------|
| `userId` | Actor who performed the action |
| `action` | Operation type (create, update, delete) |
| `entityType` | Target entity (Client, Policy, Task, etc.) |
| `entityId` | Target record ID |
| `oldValues` | JSON snapshot of previous state |
| `newValues` | JSON snapshot of new state |
| `ipAddress` | Request origin |
| `userAgent` | Client identifier |
| `createdAt` | Timestamp |

> **Reference:** `AuditLog` model in `prisma/schema.prisma`

### 2.3 Error Logging

- API route errors with request context
- Sync failures with stream identification and row-level detail
- Unhandled exceptions with stack traces

### 2.4 Info Logging

- Sync progress: rows fetched, processed, inserted, updated per stream
- Scheduled job execution: start, completion, next scheduled run
- System events: application startup, configuration changes

### 2.5 Alerts & Notifications

Alerts are delivered through the existing `Notification` model:

| Alert Type | Trigger |
|------------|---------|
| Overdue tasks | Task `dueDate` has passed with status not in COMPLETED/NA/CANCELLED |
| Sync failures | Any sync stream fails after retry exhaustion |
| System health | Scheduler down, database connectivity issues |

User notification preferences are stored in `NotificationPreference` with per-type toggles for: in-app, email, and Teams delivery channels.

> **Reference:** `Notification` and `NotificationPreference` models in `prisma/schema.prisma`

---

## 3. AMS360 Data Sync

### 3.1 Overview

Sync data from the AFW SQL Server database (AMS360 source of truth) into the local PostgreSQL database. The sync engine connects to AFW via a managed connection pool (max 10 connections, 60s request timeout).

> **Reference:** `src/lib/sync/afw-connection.ts`

### 3.2 Department Scope

| Item | Current State | MVP Target |
|------|---------------|------------|
| Departments synced | 5 departments (Dept1, Dept8, Dept9, Dept10, Dept12) | **All 13 departments** |

**MVP change:** Remove the hardcoded `ShortName IN (...)` filter in the policy sync query so all AFW departments are included.

> **Reference:** `src/lib/sync/afw-queries.ts:221` — current 5-department WHERE clause

### 3.3 Sync Streams

Three parallel streams execute concurrently via `Promise.all()`:

| Stream | Source (AFW) | Target (Local) | Key Mapping |
|--------|-------------|-----------------|-------------|
| **Employees → Users** | `AFW_Employee` (Status = 'A') | `User` | EmpCode, Name, Email, DefaultGLDeptCode → department |
| **Customers → Clients** | `AFW_Customer` (Active = 'Y') | `Client` | CustId → amsCustomerId, contact fields, producer code |
| **Policies → Policies** | `AFW_BasicPolInfo` + 20 joined lookup tables | `Policy` | Complex CTE with company, department, personnel lookups; up to 2 additional reps and 2 additional execs per policy |

> **Reference:** `src/lib/sync/sync-engine.ts`, `src/lib/sync/afw-queries.ts`, `src/lib/sync/mappers.ts`

### 3.4 Sync Modes

| Mode | Behavior |
|------|----------|
| **Incremental** (default) | Syncs records changed since last successful sync, using `ChangedDate` filtering where supported |
| **Full** | Re-syncs all records regardless of last sync timestamp |

Signature: `runSync(triggeredBy?: string, isIncremental: boolean = true)`

### 3.5 Scheduling & Triggers

| Mechanism | Details |
|-----------|---------|
| **Scheduled** | Configurable cron expression stored in `SyncConfig` table (default: `0 2 * * *` — daily at 2 AM) |
| **Manual** | `POST /api/sync` with optional date range selection; requires `sync.trigger` permission |

Configuration keys in `SyncConfig`:
- `sync_enabled` — master toggle
- `sync_schedule_cron` — cron expression
- `policy_start_date` / `policy_end_date` — date range for policy sync window

> **Reference:** `src/lib/sync/scheduler.ts`

### 3.6 Retry Logic

Each sync stream independently retries on failure:
- **Max retries:** 3
- **Backoff:** Exponential — 2s, 4s, 8s

### 3.7 Sync Logging

Every sync execution is recorded in the `SyncLog` model:

| Field | Description |
|-------|-------------|
| `syncType` | Which stream or overall sync |
| `startedAt` / `completedAt` | Timing |
| `status` | Success or failure |
| `rowsProcessed` | Total rows fetched from AFW |
| `rowsInserted` | New records created |
| `rowsUpdated` | Existing records updated |
| `errorMessage` | Failure details (if any) |
| `triggeredBy` | User ID or "scheduler" |

### 3.8 API Endpoints

| Method | Path | Purpose | Permission |
|--------|------|---------|------------|
| `POST` | `/api/sync` | Trigger manual sync | `sync.trigger` |
| `GET` | `/api/sync` | List recent sync logs | `sync.trigger` |
| `GET` | `/api/sync/status` | Scheduler status | `sync.trigger` |

---

## 4. Task Management & Renewal Workflow

### 4.1 Task Templates

Templates define repeatable task patterns for renewal workflows:

| Field | Description |
|-------|-------------|
| `name` | Template display name |
| `description` | Detailed instructions |
| `department` | PERSONAL_LINES, COMMERCIAL_LINES, CLAIMS, BENEFITS, OTHER |
| `timing` | PRE_RENEWAL or POST_RENEWAL |
| `daysOffset` | Signed integer — number of days before (negative) or after (positive) policy expiration |
| `defaultPriority` | LOW, MEDIUM, HIGH, or URGENT |
| `designationId` | Optional — restrict template to clients with a specific designation |
| `displayOrder` | Presentation ordering |
| `isActive` | Soft-disable toggle |

Designation-based rules: When a template has a `designationId`, it only generates tasks for clients whose primary or secondary designation matches.

> **Reference:** `TaskTemplate` model in `prisma/schema.prisma`

### 4.2 Task Auto-Generation

Tasks are generated from templates based on policy expiration dates:

1. Identify policies approaching expiration within the configured window
2. Match applicable templates by department and (optionally) client designation
3. Calculate due date: `policy.expirationDate + template.daysOffset`
4. Create task records linked to the client, policy, and source template

### 4.3 Task Statuses

| Status | Description |
|--------|-------------|
| `NOT_STARTED` | Default state on creation |
| `IN_PROGRESS` | Work underway |
| `COMPLETED` | Finished — captures `completedAt` timestamp and `completedBy` user |
| `BLOCKED` | Cannot proceed — requires intervention |
| `NA` | Not applicable — **requires `naReason`** documentation |
| `CANCELLED` | Voided — **requires `cancelledReason`** documentation |

### 4.4 Priority Levels

| Priority | Usage |
|----------|-------|
| `LOW` | Routine, no urgency |
| `MEDIUM` | Standard renewal tasks |
| `HIGH` | Approaching deadline or important client |
| `URGENT` | Immediate attention required |

### 4.5 Timing & Day Offsets

Tasks are classified as either:

- **PRE_RENEWAL** — due before the policy expiration date (negative `daysOffset`)
- **POST_RENEWAL** — due after the policy expiration date (positive `daysOffset`)

The `daysOffset` value is configurable per template, allowing precise scheduling of each step in the renewal workflow.

### 4.6 Task Assignment

- Tasks support multi-user assignment via the `TaskAssignment` join table
- Workload visibility: `GET /api/dashboard/workload` provides manager-facing metrics on task distribution across team members
- Dashboard displays per-user statistics: overdue, due today, upcoming

### 4.7 N/A Marking

When a task is marked as `NA`, the `naReason` field is required. This ensures accountability and auditability for skipped tasks. Similarly, `CANCELLED` status requires `cancelledReason`.

### 4.8 API Endpoints

| Method | Path | Purpose | Permission |
|--------|------|---------|------------|
| `GET` | `/api/tasks` | List tasks with filtering (status, clientId, assignedToMe, pagination) | `tasks.read` |
| `POST` | `/api/tasks` | Create task | `tasks.write` |

Query parameters: `status`, `clientId`, `assignedToMe`, `page`, `limit` (default 50).

---

## 5. Renewal Grouping & Policy Exclusion

> **Status: NEW — not yet implemented.** This section defines net-new functionality for the MVP.

### 5.1 Problem Statement

A single client may have multiple policies with the same expiration date. Currently, there is no mechanism to control how renewal tasks are generated across those policies — whether they share a single task set or need separate ones. Additionally, some policies (being non-renewed, rewritten, or cancelled) should be excluded from task generation entirely.

### 5.2 Default Behavior: Client-Level Task Sets

When no explicit grouping exists, the system generates **one set of renewal tasks per client** based on the client's policies and their expiration dates. This preserves backward compatibility with the existing workflow.

### 5.3 Renewal Groups

A new `RenewalGroup` entity enables manual control over how policies are grouped for task generation:

**RenewalGroup model (new):**

| Field | Type | Description |
|-------|------|-------------|
| `id` | UUID | Primary key |
| `name` | String | User-provided label (e.g., "Main BOP + Auto bundle") |
| `clientId` | FK → Client | Owning client |
| `createdBy` | FK → User | Who created the group |
| `createdAt` | DateTime | Timestamp |
| `updatedAt` | DateTime | Timestamp |

**RenewalGroupPolicy join table (new):**

| Field | Type | Description |
|-------|------|-------------|
| `renewalGroupId` | FK → RenewalGroup | Group membership |
| `policyId` | FK → Policy | Included policy |

### 5.4 Manual Grouping

Users can create explicit renewal groups by selecting which policies should share a single task set:

- Select a client
- Choose one or more policies from that client
- Name the group
- Task generation uses the group rather than the full client policy set

**Use case:** A client has 5 policies but 3 of them are bundled and should be renewed together. Create one group with those 3 policies — they get one shared task set. The remaining 2 policies can be in separate groups or fall through to default behavior.

### 5.5 Splitting

When a client's policies need separate renewal workflows, users can split them into distinct renewal groups:

- Each group gets its own independent set of renewal tasks
- Groups can contain one or many policies
- Ungrouped policies fall back to default client-level behavior

### 5.6 Policy Exclusion

Individual policies can be marked as excluded from task generation:

**Policy model addition (new field):**

| Field | Type | Description |
|-------|------|-------------|
| `isExcludedFromRenewal` | Boolean (default: false) | Excludes policy from task generation |
| `exclusionReason` | String (nullable) | Required when excluded — documents why |

**Use cases for exclusion:**
- Policy is being non-renewed
- Policy is being rewritten under a new policy number
- Policy is being moved to a different carrier outside the normal renewal flow
- Client has requested cancellation

Excluded policies are skipped during task auto-generation but remain visible in the client's policy list with a visual indicator.

### 5.7 Task Generation Logic (Updated)

With renewal grouping and exclusion in place, task auto-generation follows this precedence:

1. **Exclude** any policy where `isExcludedFromRenewal = true`
2. **Grouped policies**: For each `RenewalGroup`, generate one task set using the group's policies
3. **Ungrouped policies**: For remaining policies not in any group, generate one task set per client (default behavior)

---

## Appendix

### A. Technology Stack

| Layer | Technology |
|-------|-----------|
| Frontend | React, TypeScript, Next.js 16 (App Router), Tailwind CSS, shadcn/ui |
| Backend | Next.js API Routes, NextAuth.js 4.x (JWT) |
| Database | PostgreSQL via Prisma ORM 7.x |
| Source DB | SQL Server via mssql (AFW/AMS360) |
| Scheduling | node-cron |
| Deployment | Docker |

### B. Data Model Index

| Model | Purpose | Status |
|-------|---------|--------|
| User | Application users synced from AFW employees | Existing |
| Client | Customers synced from AFW | Existing |
| Policy | Insurance policies synced from AFW | Existing |
| Task | Renewal workflow tasks | Existing |
| TaskTemplate | Reusable task definitions | Existing |
| TaskAssignment | Multi-user task assignment | Existing |
| Designation | Client shape/classification | Existing |
| AuditLog | Action audit trail | Existing |
| Notification | User-facing alerts | Existing |
| NotificationPreference | Per-user delivery prefs | Existing |
| SyncLog | Sync execution history | Existing |
| SyncConfig | Sync configuration key-value store | Existing |
| EntraGroupRoleMapping | Azure AD group → role mapping | Existing |
| UserRole | Direct user → role assignment | Existing |
| **RenewalGroup** | **Policy grouping for shared task sets** | **New** |
| **RenewalGroupPolicy** | **Group ↔ Policy join table** | **New** |

### C. Related Documents

- [PRD — OnDeck Policy Renewal](./prd-ondeck-policy-renewal.md)
- [Tasks — PRD OnDeck Policy Renewal](./tasks-prd-ondeck-policy-renewal.md)