seubert-claims/dev/prd-input-policy-renewal-system.md

# OnDeck - PRD Input Document

## Project Overview

**Project Name:** OnDeck - Policy Renewal Workflow Management System

**Client Type:** Insurance Brokerage

**Purpose:** Streamline policy renewal workflows by centralizing client and policy data from AMS360, enabling custom client classifications, personnel assignments, and department-specific task management tied to policy renewal dates.

---

## 1. Problem Statement

Insurance brokerages managing large policy portfolios face challenges coordinating renewal activities across departments. Currently:

- Client and policy data lives in AMS360 with no custom workflow layer
- No systematic way to assign internal designations or classifications to clients
- Renewal tasks are tracked manually or in disconnected systems
- No visibility into workload distribution or deadline compliance across teams
- Personnel assignments to clients aren't centrally managed

**Goal:** Create OnDeck - a unified web application that syncs AMS360 data, adds custom metadata layers, and provides renewal-based task management with dashboards for workload visibility.

---

## 2. User Personas & Roles

### 2.1 Role Definitions

| Role | Description | Key Permissions |
|------|-------------|-----------------|
| **Admin** | System administrators, IT staff | Full access: user management, sync configuration, all data, system settings |
| **Manager** | Department leads, supervisors | View all department data, assign tasks, manage personnel assignments, view all dashboards |
| **Account Executive** | Handles client relationships, renewals | View assigned clients, manage own tasks, update client info, view personal dashboard |
| **Claims** | Claims department staff | View assigned clients, manage claims-related tasks, limited client edit |

### 2.2 Authorization Model

**Dual-layer approach:**

1. **Entra ID Groups → Base Role Mapping**
   - Entra Group membership determines initial role assignment
   - Example: `SG-OnDeck-Admins` → Admin role
   - MFA enforced via Entra Conditional Access policies

2. **In-App Granular Permissions**
   - Fine-grained permissions within roles
   - Client-level access assignments (primary/additional personnel)
   - Department-based data filtering
   - Custom permission overrides when needed

---

## 3. Functional Requirements

### 3.1 Authentication & Authorization

#### 3.1.1 Entra ID Integration
- **SSO via Entra ID** using MSAL.js or NextAuth.js Azure AD provider
- **Token claims must include:** `groups`, `preferred_username`, `name`, `oid`
- **Group-to-role mapping** configurable by Admin
- **Leverage existing MFA** - no additional MFA implementation needed
- **Session management:** Configurable timeout, refresh token handling

#### 3.1.2 In-App Permission System
- Permission sets per role (CRUD matrix)
- Ability to assign individuals to specific clients (overrides department-level access)
- Audit log of permission changes
- Admin UI for managing Entra Group → Role mappings

### 3.2 AMS360 Data Sync Engine

#### 3.2.1 Sync Configuration
- **Frequency:** Daily automated sync (configurable time window)
- **Method:** Direct SQL connection to AMS360 database
- **Direction:** One-way (AMS360 → Local Postgres)
- **Entities to sync:**
  - Clients (customer master data)
  - Policies (policy details, renewal dates, coverage info)

#### 3.2.2 Sync Behavior
- **Initial load:** Full sync of all active clients and policies
- **Incremental sync:** Based on `ModifiedDate` or equivalent timestamp fields
- **Conflict handling:** AMS360 is source of truth for synced fields
- **Sync logging:** Record sync runs, row counts, errors
- **Manual trigger:** Admin can initiate on-demand sync

#### 3.2.3 Data Mapping (AMS360 → Local)

```
AMS360 Client → local.clients
  - CustomerID → ams_customer_id (unique key for sync)
  - CustomerName → name
  - Address fields → address_*
  - Phone/Email → contact_*
  - Producer → producer_code
  - (other relevant fields TBD based on AMS360 schema)

AMS360 Policy → local.policies
  - PolicyID → ams_policy_id (unique key for sync)
  - CustomerID → client_id (FK to local.clients)
  - PolicyNumber → policy_number
  - EffectiveDate → effective_date
  - ExpirationDate → expiration_date (THIS IS THE RENEWAL DATE)
  - PolicyType → policy_type
  - Carrier → carrier_name
  - Premium → premium_amount
  - Status → status
```

### 3.3 Client Management (Custom Data Layer)

#### 3.3.1 Custom Client Fields (Local Only)
These fields are NOT synced from AMS360 - they are managed entirely in the app:

| Field | Type | Description |
|-------|------|-------------|
| `shape` | FK to shapes table | Primary designation (user-defined) |
| `shape2` | FK to shapes table | Secondary designation (user-defined) |
| `primary_personnel_id` | FK to users | Primary account owner (Entra user) |
| `notes` | Text | Internal notes |
| `custom_fields` | JSONB | Extensible custom field storage |

#### 3.3.2 Shape/Designation Management
- Admin-configurable list of Shape values
- Each shape has: `id`, `name`, `description`, `color` (for UI), `active`
- Shapes can be deactivated but not deleted (preserve historical data)
- Clients can have one Shape and one Shape2 (both optional)

#### 3.3.3 Personnel Assignment
- **Primary Personnel:** One Entra user designated as primary owner
- **Additional Personnel:** Zero or more Entra users with access
- Personnel dropdown populated from Entra ID users (synced or on-demand lookup)
- Assignment changes logged for audit

### 3.4 Task/Action Management

#### 3.4.1 Task Definition
Tasks represent actions to be taken relative to policy renewal dates.

**Task Properties:**
| Field | Type | Description |
|-------|------|-------------|
| `id` | UUID | Primary key |
| `title` | String | Task name |
| `description` | Text | Detailed instructions |
| `department` | Enum | Personal Lines, Commercial Lines, Claims, etc. |
| `timing` | Enum | `PRE_RENEWAL` or `POST_RENEWAL` |
| `days_offset` | Integer | Days before (negative) or after (positive) renewal |
| `due_date` | Date | Calculated: policy.expiration_date + days_offset |
| `status` | Enum | `NOT_STARTED`, `IN_PROGRESS`, `COMPLETED`, `BLOCKED`, `CANCELLED` |
| `priority` | Enum | `LOW`, `MEDIUM`, `HIGH`, `URGENT` |
| `client_id` | FK | Associated client |
| `policy_id` | FK | Associated policy (optional - can be client-level) |
| `assigned_users` | Many-to-many | One or more Entra users |
| `created_by` | FK | User who created |
| `created_at` | Timestamp | |
| `updated_at` | Timestamp | |
| `completed_at` | Timestamp | When status changed to COMPLETED |
| `completed_by` | FK | User who completed |

#### 3.4.2 Task Templates
- **Department-based templates:** Predefined task sets per department
- Templates define: title, description, timing, days_offset, default priority
- When a policy approaches renewal, tasks can be auto-generated from templates
- Admin can manage templates per department

**Example Template:**
```
Department: Personal Lines
Template Name: "Standard Auto Renewal"
Tasks:
  1. Review coverage limits (PRE_RENEWAL, -45 days)
  2. Contact client for updates (PRE_RENEWAL, -30 days)
  3. Submit renewal to carrier (PRE_RENEWAL, -21 days)
  4. Send renewal docs to client (POST_RENEWAL, +3 days)
```

#### 3.4.3 Task Assignment
- Tasks can be assigned to one or more users
- Assignment can be manual or auto-assigned to client's primary personnel
- Bulk task assignment for efficiency
- Reassignment with audit trail

#### 3.4.4 Task Lifecycle
```
NOT_STARTED → IN_PROGRESS → COMPLETED
                    ↓
                 BLOCKED → IN_PROGRESS → COMPLETED
                    
Any status → CANCELLED (with reason)
```

### 3.5 Dashboards & Reporting

#### 3.5.1 Personal Dashboard (Account Executive, Claims)
- **My Tasks:** Filtered to assigned tasks
  - Overdue (red highlight)
  - Due today
  - Due this week
  - Upcoming (next 30 days)
- **My Clients:** Quick access to assigned clients
- **Quick Stats:**
  - Tasks completed this week/month
  - Overdue task count
  - Upcoming renewals count

#### 3.5.2 Manager Dashboard
- **Team Overview:**
  - Workload distribution (tasks per team member)
  - Completion rates by user
  - Overdue tasks by user
- **Department Metrics:**
  - Tasks by status (pie/bar chart)
  - Renewal timeline (upcoming 90 days)
  - SLA compliance (% tasks completed on time)
- **Drill-down capability:** Click to see individual user details

#### 3.5.3 Admin Dashboard
- All Manager dashboard features, plus:
- **System Health:**
  - Last sync time, status, row counts
  - Error logs
  - User activity summary
- **Cross-department views**

#### 3.5.4 KPIs to Track
| KPI | Calculation | Visualization |
|-----|-------------|---------------|
| Tasks Completed | Count by period | Line chart (trend) |
| Tasks Overdue | Count where due_date < today AND status not COMPLETED | Number + list |
| Upcoming Renewals | Policies expiring in next 30/60/90 days | Count + calendar view |
| Workload per Person | Active tasks assigned per user | Bar chart |
| On-Time Completion Rate | (Completed on/before due_date) / Total completed | Percentage |
| Avg Days to Complete | Mean(completed_at - created_at) | Number |

---

## 4. Data Architecture

### 4.1 Database Schema (PostgreSQL)

```sql
-- Entra user cache (synced from Entra or populated on first login)
CREATE TABLE users (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    entra_oid VARCHAR(36) UNIQUE NOT NULL,  -- Entra Object ID
    email VARCHAR(255) UNIQUE NOT NULL,
    display_name VARCHAR(255),
    department VARCHAR(100),
    is_active BOOLEAN DEFAULT true,
    last_login_at TIMESTAMP,
    created_at TIMESTAMP DEFAULT NOW(),
    updated_at TIMESTAMP DEFAULT NOW()
);

-- Role definitions
CREATE TABLE roles (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    name VARCHAR(50) UNIQUE NOT NULL,  -- Admin, Manager, Account Executive, Claims
    description TEXT,
    permissions JSONB NOT NULL DEFAULT '{}',
    created_at TIMESTAMP DEFAULT NOW()
);

-- Entra Group to Role mapping
CREATE TABLE entra_group_role_mappings (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    entra_group_id VARCHAR(36) NOT NULL,  -- Entra Group Object ID
    entra_group_name VARCHAR(255),
    role_id UUID REFERENCES roles(id),
    created_at TIMESTAMP DEFAULT NOW(),
    UNIQUE(entra_group_id)
);

-- User role assignments (can override group-based roles)
CREATE TABLE user_roles (
    user_id UUID REFERENCES users(id),
    role_id UUID REFERENCES roles(id),
    assigned_by UUID REFERENCES users(id),
    assigned_at TIMESTAMP DEFAULT NOW(),
    PRIMARY KEY (user_id, role_id)
);

-- Shape/Designation definitions
CREATE TABLE shapes (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    name VARCHAR(100) NOT NULL,
    description TEXT,
    color VARCHAR(7),  -- Hex color for UI
    is_active BOOLEAN DEFAULT true,
    display_order INTEGER,
    created_at TIMESTAMP DEFAULT NOW(),
    updated_at TIMESTAMP DEFAULT NOW()
);

-- Clients (synced from AMS360 + custom fields)
CREATE TABLE clients (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    -- AMS360 synced fields
    ams_customer_id VARCHAR(50) UNIQUE NOT NULL,
    name VARCHAR(255) NOT NULL,
    address_line1 VARCHAR(255),
    address_line2 VARCHAR(255),
    city VARCHAR(100),
    state VARCHAR(50),
    zip_code VARCHAR(20),
    phone VARCHAR(50),
    email VARCHAR(255),
    producer_code VARCHAR(50),
    ams_created_at TIMESTAMP,
    ams_modified_at TIMESTAMP,
    -- Custom fields (local only)
    shape_id UUID REFERENCES shapes(id),
    shape2_id UUID REFERENCES shapes(id),
    primary_personnel_id UUID REFERENCES users(id),
    notes TEXT,
    custom_fields JSONB DEFAULT '{}',
    -- Metadata
    last_synced_at TIMESTAMP,
    created_at TIMESTAMP DEFAULT NOW(),
    updated_at TIMESTAMP DEFAULT NOW()
);

-- Additional personnel assignments for clients
CREATE TABLE client_personnel (
    client_id UUID REFERENCES clients(id) ON DELETE CASCADE,
    user_id UUID REFERENCES users(id) ON DELETE CASCADE,
    assigned_at TIMESTAMP DEFAULT NOW(),
    assigned_by UUID REFERENCES users(id),
    PRIMARY KEY (client_id, user_id)
);

-- Policies (synced from AMS360)
CREATE TABLE policies (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    -- AMS360 synced fields
    ams_policy_id VARCHAR(50) UNIQUE NOT NULL,
    client_id UUID REFERENCES clients(id) ON DELETE CASCADE,
    policy_number VARCHAR(100),
    policy_type VARCHAR(100),
    effective_date DATE,
    expiration_date DATE NOT NULL,  -- This is the RENEWAL DATE
    carrier_name VARCHAR(255),
    premium_amount DECIMAL(12, 2),
    status VARCHAR(50),
    ams_created_at TIMESTAMP,
    ams_modified_at TIMESTAMP,
    -- Metadata
    last_synced_at TIMESTAMP,
    created_at TIMESTAMP DEFAULT NOW(),
    updated_at TIMESTAMP DEFAULT NOW()
);

-- Index for renewal date queries
CREATE INDEX idx_policies_expiration ON policies(expiration_date);
CREATE INDEX idx_policies_client ON policies(client_id);

-- Department enum
CREATE TYPE department_type AS ENUM (
    'PERSONAL_LINES',
    'COMMERCIAL_LINES', 
    'CLAIMS',
    'BENEFITS',
    'OTHER'
);

-- Task timing enum
CREATE TYPE task_timing AS ENUM ('PRE_RENEWAL', 'POST_RENEWAL');

-- Task status enum
CREATE TYPE task_status AS ENUM (
    'NOT_STARTED',
    'IN_PROGRESS', 
    'COMPLETED',
    'BLOCKED',
    'CANCELLED'
);

-- Task priority enum
CREATE TYPE task_priority AS ENUM ('LOW', 'MEDIUM', 'HIGH', 'URGENT');

-- Task templates (department-based)
CREATE TABLE task_templates (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    name VARCHAR(255) NOT NULL,
    description TEXT,
    department department_type NOT NULL,
    timing task_timing NOT NULL,
    days_offset INTEGER NOT NULL,  -- Negative = before renewal, Positive = after
    default_priority task_priority DEFAULT 'MEDIUM',
    is_active BOOLEAN DEFAULT true,
    display_order INTEGER,
    created_by UUID REFERENCES users(id),
    created_at TIMESTAMP DEFAULT NOW(),
    updated_at TIMESTAMP DEFAULT NOW()
);

-- Tasks (actual task instances)
CREATE TABLE tasks (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    title VARCHAR(255) NOT NULL,
    description TEXT,
    department department_type NOT NULL,
    timing task_timing NOT NULL,
    days_offset INTEGER NOT NULL,
    due_date DATE NOT NULL,
    status task_status DEFAULT 'NOT_STARTED',
    priority task_priority DEFAULT 'MEDIUM',
    client_id UUID REFERENCES clients(id) ON DELETE CASCADE,
    policy_id UUID REFERENCES policies(id) ON DELETE SET NULL,
    template_id UUID REFERENCES task_templates(id),  -- If generated from template
    created_by UUID REFERENCES users(id),
    completed_at TIMESTAMP,
    completed_by UUID REFERENCES users(id),
    cancelled_reason TEXT,
    created_at TIMESTAMP DEFAULT NOW(),
    updated_at TIMESTAMP DEFAULT NOW()
);

-- Task assignments (many-to-many)
CREATE TABLE task_assignments (
    task_id UUID REFERENCES tasks(id) ON DELETE CASCADE,
    user_id UUID REFERENCES users(id) ON DELETE CASCADE,
    assigned_at TIMESTAMP DEFAULT NOW(),
    assigned_by UUID REFERENCES users(id),
    PRIMARY KEY (task_id, user_id)
);

-- Indexes for task queries
CREATE INDEX idx_tasks_due_date ON tasks(due_date);
CREATE INDEX idx_tasks_status ON tasks(status);
CREATE INDEX idx_tasks_client ON tasks(client_id);
CREATE INDEX idx_tasks_department ON tasks(department);

-- Sync log
CREATE TABLE sync_logs (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    sync_type VARCHAR(50) NOT NULL,  -- 'clients', 'policies', 'full'
    started_at TIMESTAMP NOT NULL,
    completed_at TIMESTAMP,
    status VARCHAR(50) NOT NULL,  -- 'running', 'completed', 'failed'
    rows_processed INTEGER DEFAULT 0,
    rows_inserted INTEGER DEFAULT 0,
    rows_updated INTEGER DEFAULT 0,
    error_message TEXT,
    triggered_by UUID REFERENCES users(id)  -- NULL if scheduled
);

-- Audit log
CREATE TABLE audit_logs (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    user_id UUID REFERENCES users(id),
    action VARCHAR(100) NOT NULL,
    entity_type VARCHAR(50) NOT NULL,
    entity_id UUID,
    old_values JSONB,
    new_values JSONB,
    ip_address INET,
    user_agent TEXT,
    created_at TIMESTAMP DEFAULT NOW()
);

CREATE INDEX idx_audit_logs_user ON audit_logs(user_id);
CREATE INDEX idx_audit_logs_entity ON audit_logs(entity_type, entity_id);
CREATE INDEX idx_audit_logs_created ON audit_logs(created_at);
```

### 4.2 AMS360 Connection Requirements

- **Connection type:** SQL Server (typically)
- **Credentials:** Read-only service account recommended
- **Network:** May require VPN or firewall rules
- **Tables to query:** (Exact names depend on AMS360 version)
  - Customer table
  - Policy table
  - (Reference AMS360 data dictionary for exact schema)

---

## 5. Technical Architecture

### 5.1 Recommended Stack

| Layer | Technology | Rationale |
|-------|------------|-----------|
| **Frontend** | React + TypeScript | Familiarity, strong ecosystem |
| **UI Framework** | Tailwind CSS + shadcn/ui | Modern, accessible components |
| **State Management** | TanStack Query | Server state caching, mutations |
| **Backend** | Node.js + Express or Next.js API Routes | JavaScript consistency |
| **ORM** | Prisma or Drizzle | Type-safe database access |
| **Database** | PostgreSQL | Robust, JSONB support |
| **Auth** | MSAL.js + NextAuth.js (Azure AD provider) | Native Entra integration |
| **Sync Engine** | Node.js service + node-mssql | AMS360 SQL connection |
| **Scheduler** | node-cron or external (systemd timer) | Daily sync trigger |
| **Hosting** | Docker on Linux server | Your standard deployment |

### 5.2 Authentication Flow

```
1. User navigates to app
2. App redirects to Entra ID login (MSAL.js)
3. User authenticates + MFA (per Entra CA policies)
4. Entra returns ID token + access token
5. Token includes group claims
6. App backend:
   a. Validates token
   b. Looks up entra_oid in users table (create if first login)
   c. Resolves roles from group claims + user_roles table
   d. Creates session
7. Frontend receives user context with permissions
```

### 5.3 Sync Engine Architecture

```
┌─────────────────┐         ┌──────────────────┐
│   AMS360 DB     │ ──SQL── │  Sync Service    │
│   (SQL Server)  │         │  (Node.js)       │
└─────────────────┘         └────────┬─────────┘
                                     │
                            ┌────────▼─────────┐
                            │   PostgreSQL     │
                            │   (Local DB)     │
                            └──────────────────┘

Sync Process:
1. Cron triggers sync at configured time
2. Sync service connects to AMS360 (read-only)
3. Queries records modified since last sync
4. Upserts into local Postgres (match on ams_*_id)
5. Logs sync results
6. Closes connections
```

---

## 6. Non-Functional Requirements

### 6.1 Security
- All traffic over HTTPS
- Entra ID MFA enforced via Conditional Access
- Database credentials in environment variables / secrets manager
- Read-only AMS360 connection
- RBAC enforced at API layer
- Audit logging for sensitive operations
- Session timeout configurable (default: 8 hours)

### 6.2 Performance
- Dashboard queries < 2 seconds
- Sync should complete within 1 hour for typical data volumes
- Pagination on all list endpoints (default 50, max 100)
- Database indexes on frequently queried fields

### 6.3 Availability
- Daily sync window during off-hours (e.g., 2 AM)
- Sync failures should not affect app availability
- Sync retry logic (3 attempts with backoff)

### 6.4 Data Integrity
- Foreign key constraints enforced
- Soft deletes where appropriate
- Sync never deletes local records (mark inactive if removed from AMS360)

---

## 7. MVP Scope vs. Future Phases

### Phase 1 - MVP
- [ ] Entra ID authentication with group-based roles
- [ ] AMS360 daily sync (Clients + Policies)
- [ ] Client list with custom fields (Shape, Shape2, Primary Personnel)
- [ ] Basic task management (CRUD, assignment, status)
- [ ] Personal dashboard (my tasks, overdue, upcoming)
- [ ] Admin: Shape management, sync trigger

### Phase 2
- [ ] Task templates per department
- [ ] Auto-generation of tasks from templates based on renewal dates
- [ ] Manager dashboard with team metrics
- [ ] Bulk operations (assign, update status)
- [ ] Email notifications for overdue tasks

### Phase 3
- [ ] Advanced reporting / export
- [ ] Calendar view for renewals
- [ ] Additional personnel assignments
- [ ] Custom fields beyond Shape/Shape2
- [ ] API for external integrations

---

## 8. Open Questions / Decisions Needed

1. **AMS360 access:** Who provides credentials? Any data restrictions?
2. **Departments:** Are the listed departments (Personal Lines, Commercial, Claims, Benefits) complete?
3. **Task auto-generation:** Should tasks auto-generate when policy enters renewal window, or manual trigger?
4. **Notifications:** Email? In-app only? Teams integration?
5. **Mobile:** Responsive web sufficient, or native app needed?
6. **Hosting:** On-prem server or cloud (Azure)?

---

## 9. Glossary

| Term | Definition |
|------|------------|
| **AMS360** | Agency Management System by Vertafore - source of client/policy data |
| **Shape/Shape2** | Custom client designations defined by the brokerage |
| **Renewal Date** | Policy expiration date - triggers workflow tasks |
| **Pre-Renewal Task** | Action to be completed before policy expires |
| **Post-Renewal Task** | Action to be completed after policy renews |
| **Primary Personnel** | Main user responsible for a client |
| **Entra ID** | Microsoft's identity platform (formerly Azure AD) |

---

*Document Version: 1.0*
*Created: January 2025*
*Last Updated: January 2025*