seubert-claims/tasks/prd-ondeck-policy-renewal.md

# PRD: OnDeck - Policy Renewal Workflow Management System

## 1. Introduction/Overview

### 1.1 Problem Statement

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

- **Fragmented Data:** Client and policy data lives in AMS360 (AFW) with no custom workflow layer
- **Manual Coordination:** Renewal tasks are tracked manually or in disconnected systems
- **No Classification System:** No systematic way to assign internal designations or classifications to clients
- **Limited Visibility:** No insight into workload distribution or deadline compliance across teams
- **Decentralized Personnel Management:** Personnel assignments to clients aren't centrally managed

### 1.2 Solution

**OnDeck** is a unified web application that:
- Syncs client and policy data from the AFW database
- Adds custom metadata layers (Shape designations, personnel assignments)
- Provides renewal-based task management with auto-generation from templates
- Delivers dashboards for workload visibility and deadline compliance

### 1.3 Goal

Create a streamlined, centralized policy renewal workflow system that reduces missed deadlines, improves team coordination, and provides management visibility into renewal operations across all departments.

---

## 2. Goals

### 2.1 Primary Goals

| Goal | Metric | Target |
|------|--------|--------|
| Eliminate missed renewal deadlines | % of tasks completed on time | 95% within 6 months |
| Reduce manual coordination effort | User-reported time savings | 40% reduction |
| Provide workload visibility | Manager dashboard adoption | 100% of managers using weekly |
| Centralize client classification | Clients with Shape assignments | 90% within 90 days |

### 2.2 Secondary Goals

- Enable extensible client designation system (Shape, Shape2, future designations)
- Provide comprehensive audit trails for compliance
- Support 50 users with 10 concurrent without performance degradation
- Integrate seamlessly with existing AFW database (read-only)

---

## 3. User Stories

### 3.1 Account Executive Stories

| ID | Story | Acceptance Criteria |
|----|-------|---------------------|
| AE-1 | As an Account Executive, I want to see all my upcoming renewal tasks in one dashboard so that I can prioritize my work effectively | Dashboard shows tasks sorted by due date with overdue items highlighted |
| AE-2 | As an Account Executive, I want tasks to auto-generate based on renewal dates so that I don't have to manually create routine tasks | Tasks appear automatically when policy enters renewal window |
| AE-3 | As an Account Executive, I want to mark tasks as N/A with required notes so that I can document why certain tasks don't apply | N/A status requires notes field; task is removed from active list |
| AE-4 | As an Account Executive, I want to assign Shape designations to my clients so that I can categorize them for targeted service | Shape dropdown available on client detail; changes logged |
| AE-5 | As an Account Executive, I want to view my clients' policy details and renewal dates so that I can prepare for upcoming renewals | Client detail shows all policies with days-until-renewal |

### 3.2 Manager Stories

| ID | Story | Acceptance Criteria |
|----|-------|---------------------|
| MG-1 | As a Manager, I want to view workload distribution across my team so that I can balance assignments | Dashboard shows task count per team member with visual chart |
| MG-2 | As a Manager, I want to see completion rates and overdue tasks by user so that I can identify issues | Metrics table with drill-down to individual tasks |
| MG-3 | As a Manager, I want to reassign tasks between team members so that I can handle absences | Bulk reassignment UI with audit logging |
| MG-4 | As a Manager, I want to configure department task templates so that our workflows are standardized | Template CRUD interface per department |
| MG-5 | As a Manager, I want to see SLA compliance metrics so that I can report on team performance | % on-time completion displayed prominently |

### 3.3 Admin Stories

| ID | Story | Acceptance Criteria |
|----|-------|---------------------|
| AD-1 | As an Admin, I want to configure sync schedule via UI so that I can adjust timing without code changes | Settings page with time picker; changes take effect next cycle |
| AD-2 | As an Admin, I want to manage Shape definitions with descriptions and rules so that designations are consistent | Shape CRUD with name, description, color, rules, active status |
| AD-3 | As an Admin, I want to map Entra groups to application roles so that access is controlled | Mapping UI showing groups → roles with add/remove |
| AD-4 | As an Admin, I want to monitor sync health and view logs so that I can troubleshoot issues | Sync dashboard with last run, status, row counts, error log |
| AD-5 | As an Admin, I want to configure notification channels (in-app, Teams, Email) so that users receive alerts appropriately | Settings page with channel toggles per notification type |

### 3.4 Claims Department Stories

| ID | Story | Acceptance Criteria |
|----|-------|---------------------|
| CL-1 | As a Claims Specialist, I want to view assigned clients and their renewal dates so that I can proactively address coverage needs | Filtered client list with renewal timeline |
| CL-2 | As a Claims Specialist, I want to create claims-related tasks tied to renewals so that claims considerations are included | Task creation with Claims department assignment |

---

## 4. Functional Requirements

### 4.1 Authentication & Authorization

| ID | Requirement | Priority |
|----|-------------|----------|
| AUTH-1 | The system must authenticate users via Microsoft Entra ID using SSO (MSAL.js) | P0 |
| AUTH-2 | The system must extract group claims from Entra tokens for role mapping | P0 |
| AUTH-3 | The system must support configurable Entra Group → Role mappings via Admin UI | P0 |
| AUTH-4 | The system must enforce role-based access control at the API layer | P0 |
| AUTH-5 | The system must leverage existing Entra MFA policies (no additional MFA implementation) | P0 |
| AUTH-6 | The system must support configurable session timeout (default 8 hours) | P1 |
| AUTH-7 | The system must maintain audit logs of all permission changes | P1 |

**Roles:**
- **Admin:** Full system access, user management, sync configuration
- **Manager:** Department data access, task assignment, team dashboards
- **Account Executive:** Assigned client access, personal tasks, client updates
- **Claims:** Assigned client access, claims-related tasks

### 4.2 AFW Data Sync Engine

| ID | Requirement | Priority |
|----|-------------|----------|
| SYNC-1 | The system must perform automated daily sync at admin-configurable time (default 2 AM) | P0 |
| SYNC-2 | The system must connect to AFW SQL Server database (read-only) | P0 |
| SYNC-3 | The system must sync policies with expiration dates within current calendar year + 24 months | P0 |
| SYNC-4 | The system must perform incremental sync based on ModifiedDate/ChangedDate fields | P0 |
| SYNC-5 | The system must sync: Clients (AFW_Customer), Policies (AFW_BasicPolInfo), Employees (AFW_Employee), Departments (AFW_GeneralLedgerDepartment) | P0 |
| SYNC-6 | The system must log all sync runs with: start time, end time, rows processed, rows inserted, rows updated, errors | P0 |
| SYNC-7 | The system must allow manual sync trigger by Admin users | P1 |
| SYNC-8 | The system must never delete local records (mark inactive if removed from AFW) | P0 |
| SYNC-9 | The system must provide Admin UI for configuring sync schedule | P1 |
| SYNC-10 | The system must implement retry logic (3 attempts with exponential backoff) on sync failure | P1 |

**AFW → Local Data Mapping:**

```
AFW_Customer → clients
  CustId → ams_customer_id
  FirmNameCust → name
  CustNo → customer_number
  Addr1, Addr2, City, State, ZipCode → address fields
  EMail → email
  BusPhone → phone
  Prod1Code → producer_code
  CsrCode → csr_code
  GLDeptCode → department_code

AFW_BasicPolInfo → policies
  PolId → ams_policy_id
  CustId → client_id (FK)
  PolNo → policy_number
  PolEffDate → effective_date
  PolExpDate → expiration_date (RENEWAL DATE)
  PolTypeLOB → policy_type
  CoCode → parent_company_code
  WritingCoCode → writing_company_code
  ExecCode → executive_code
  CsrCode → csr_code
  RenewalRptFlag → status (via AFW_PRCode lookup)
  FullTermPremium → premium_amount
  GLDeptCode → department_code
```

### 4.3 Client Management

| ID | Requirement | Priority |
|----|-------------|----------|
| CLI-1 | The system must display all synced clients in a searchable, filterable list | P0 |
| CLI-2 | The system must support Shape designation assignment (primary) | P0 |
| CLI-3 | The system must support Shape2 designation assignment (secondary) | P0 |
| CLI-4 | The system must support primary personnel assignment from Entra users | P0 |
| CLI-5 | The system must support additional personnel assignments (many-to-many) | P1 |
| CLI-6 | The system must provide client notes field for internal communications | P1 |
| CLI-7 | The system must support extensible custom fields via JSONB storage | P2 |
| CLI-8 | The system must maintain audit trail of all client modifications | P0 |
| CLI-9 | The system must filter client access based on user role and assignments | P0 |

### 4.4 Shape/Designation Management

| ID | Requirement | Priority |
|----|-------------|----------|
| SHP-1 | The system must support Admin-managed Shape definitions | P0 |
| SHP-2 | Each Shape must have: id, name, description, color (hex), display_order, is_active | P0 |
| SHP-3 | The system must support deactivating Shapes (preserve historical data, hide from new assignments) | P0 |
| SHP-4 | The system must support rules/criteria field for Shape definitions (text description) | P1 |
| SHP-5 | The UI must be designed to support additional designation types beyond Shape/Shape2 in future | P1 |
| SHP-6 | The system must display Shape color coding in client lists and details | P0 |

### 4.5 Task Management

| ID | Requirement | Priority |
|----|-------------|----------|
| TSK-1 | The system must support task creation with: title, description, department, timing, days_offset, priority | P0 |
| TSK-2 | The system must calculate due_date as: policy.expiration_date + days_offset | P0 |
| TSK-3 | The system must support task statuses: NOT_STARTED, IN_PROGRESS, COMPLETED, BLOCKED, N/A, CANCELLED | P0 |
| TSK-4 | The system must require notes when setting status to N/A | P0 |
| TSK-5 | The system must support task priorities: LOW, MEDIUM, HIGH, URGENT | P0 |
| TSK-6 | The system must support task assignment to one or more users | P0 |
| TSK-7 | The system must track: created_by, created_at, completed_by, completed_at, updated_at | P0 |
| TSK-8 | The system must support task filtering by: status, assignee, due date range, client, department | P0 |
| TSK-9 | The system must support bulk task operations (status update, reassignment) | P1 |
| TSK-10 | The system must require cancellation_reason when setting status to CANCELLED | P1 |

### 4.6 Task Templates & Auto-Generation

| ID | Requirement | Priority |
|----|-------------|----------|
| TPL-1 | The system must support department-based task templates | P0 |
| TPL-2 | Templates must define: name, description, department, timing (PRE/POST_RENEWAL), days_offset, default_priority | P0 |
| TPL-3 | The system must auto-generate tasks from templates when policy enters renewal window | P0 |
| TPL-4 | The system must assign auto-generated tasks to client's primary personnel by default | P0 |
| TPL-5 | The system must support template activation/deactivation | P1 |
| TPL-6 | The system must prevent duplicate task generation for same policy/template combination | P0 |
| TPL-7 | Admin/Manager must be able to configure renewal window trigger (e.g., 90 days before expiration) | P1 |

**Example Template Structure:**
```
Department: Commercial Lines
Template: "Standard Commercial Renewal"
Tasks:
  1. Review coverage limits (PRE_RENEWAL, -60 days, HIGH)
  2. Contact client for updates (PRE_RENEWAL, -45 days, MEDIUM)
  3. Request renewal quotes (PRE_RENEWAL, -30 days, HIGH)
  4. Present renewal options (PRE_RENEWAL, -21 days, HIGH)
  5. Bind coverage (PRE_RENEWAL, -7 days, URGENT)
  6. Send policy documents (POST_RENEWAL, +5 days, MEDIUM)
```

### 4.7 Dashboards & Reporting

| ID | Requirement | Priority |
|----|-------------|----------|
| DSH-1 | The system must provide Personal Dashboard for Account Executives and Claims users | P0 |
| DSH-2 | Personal Dashboard must show: My Tasks (overdue, due today, due this week, upcoming 30 days) | P0 |
| DSH-3 | Personal Dashboard must show: My Clients quick access list | P0 |
| DSH-4 | Personal Dashboard must show: Quick stats (completed this week, overdue count, upcoming renewals) | P0 |
| DSH-5 | The system must provide Manager Dashboard with team overview | P0 |
| DSH-6 | Manager Dashboard must show: Workload distribution (tasks per team member) | P0 |
| DSH-7 | Manager Dashboard must show: Completion rates by user | P0 |
| DSH-8 | Manager Dashboard must show: Overdue tasks by user with drill-down | P0 |
| DSH-9 | Manager Dashboard must show: Department metrics (tasks by status, renewal timeline, SLA compliance) | P1 |
| DSH-10 | The system must provide Admin Dashboard with system health and cross-department views | P1 |
| DSH-11 | Admin Dashboard must show: Last sync time, status, row counts, error summary | P0 |
| DSH-12 | All dashboards must load within 2 seconds | P0 |

**KPIs to Track:**

| KPI | Calculation | Visualization |
|-----|-------------|---------------|
| Tasks Completed | Count by period | Line chart (trend) |
| Tasks Overdue | due_date < today AND status NOT IN (COMPLETED, N/A, CANCELLED) | Number + list |
| Upcoming Renewals | Policies expiring in next 30/60/90 days | Count + calendar |
| Workload per Person | Active tasks assigned per user | Bar chart |
| On-Time Completion Rate | (Completed on/before due_date) / Total completed | Percentage |
| N/A Rate | Tasks marked N/A / Total tasks | Percentage (monitor for abuse) |

### 4.8 Notifications

| ID | Requirement | Priority |
|----|-------------|----------|
| NOT-1 | The system must support in-app notifications for task assignments and overdue alerts | P0 |
| NOT-2 | The system must provide Admin UI to configure notification channels per notification type | P1 |
| NOT-3 | The system must support future integration with Microsoft Teams notifications | P2 |
| NOT-4 | The system must support future integration with Email notifications | P2 |
| NOT-5 | Users must be able to configure personal notification preferences | P2 |

---

## 5. Non-Goals (Out of Scope)

### 5.1 Phase 1 Exclusions

- **Mobile native app** - Responsive web only; mobile app is future phase
- **Email/Teams notifications** - In-app only for MVP; channels configurable for future
- **Document management** - No file attachments or document storage
- **Policy binding/issuance** - Read-only integration with AFW
- **Carrier portal integration** - No direct carrier connections
- **Commission tracking** - No compensation or commission management
- **Client self-service portal** - Internal users only
- **Multi-tenant support** - Single brokerage deployment
- **Advanced analytics/BI** - Basic KPIs only; advanced reporting future phase
- **Workflow automation beyond task generation** - No complex conditional logic

### 5.2 Explicitly Deferred

- API for third-party integrations
- Custom report builder
- Calendar view for renewals
- Bulk import/export tools
- Integration with other AMS systems

---

## 6. Design Considerations

### 6.1 Technology Stack

| Layer | Technology | Rationale |
|-------|------------|-----------|
| **Frontend** | React + TypeScript + Next.js | Modern, type-safe, SSR capable |
| **UI Framework** | Tailwind CSS + shadcn/ui | Per user requirement; accessible, customizable |
| **State Management** | TanStack Query | Server state caching, optimistic updates |
| **Backend** | Next.js API Routes | Unified codebase, serverless-ready |
| **ORM** | Prisma | Type-safe database access, migrations |
| **Database** | PostgreSQL | Robust, JSONB support, deployed in Docker |
| **Auth** | NextAuth.js + Azure AD provider | Native Entra integration |
| **Sync Engine** | Node.js + mssql package | SQL Server connectivity for AFW |
| **Scheduler** | node-cron | In-process scheduling for sync |
| **Deployment** | Docker containers | Per user requirement; on-premise |

### 6.2 UI/UX Guidelines

- **Design System:** shadcn/ui components with Tailwind CSS
- **Color Scheme:** Professional, accessible color palette with Shape color accents
- **Layout:** Sidebar navigation, responsive breakpoints for tablet/desktop
- **Data Tables:** Sortable, filterable, paginated (default 50 rows)
- **Forms:** Inline validation, clear error messages
- **Loading States:** Skeleton loaders, optimistic updates where appropriate
- **Accessibility:** WCAG 2.1 AA compliance

### 6.3 Key UI Components

1. **Dashboard Cards** - KPI display with trend indicators
2. **Task List** - Filterable table with status badges, priority indicators
3. **Client Detail** - Tabbed interface (Overview, Policies, Tasks, Notes, History)
4. **Shape Selector** - Dropdown with color swatches
5. **Personnel Picker** - Searchable Entra user selector
6. **Sync Status Widget** - Real-time sync health indicator
7. **Notification Center** - Bell icon with unread count, dropdown list

---

## 7. Technical Considerations

### 7.1 Database Schema

```sql
-- Core tables (see full schema in prd-input document Section 4.1)
-- Key additions based on requirements:

-- Task status enum (updated)
CREATE TYPE task_status AS ENUM (
    'NOT_STARTED',
    'IN_PROGRESS', 
    'COMPLETED',
    'BLOCKED',
    'NA',           -- Added: Not Applicable
    'CANCELLED'
);

-- Tasks table (updated)
CREATE TABLE tasks (
    -- ... existing fields ...
    na_reason TEXT,              -- Required when status = 'NA'
    cancelled_reason TEXT,       -- Required when status = 'CANCELLED'
    -- ... 
);

-- Sync configuration table (new)
CREATE TABLE sync_config (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    sync_time TIME NOT NULL DEFAULT '02:00:00',
    is_enabled BOOLEAN DEFAULT true,
    retention_months INTEGER DEFAULT 24,
    updated_by UUID REFERENCES users(id),
    updated_at TIMESTAMP DEFAULT NOW()
);

-- Notification preferences (new)
CREATE TABLE notification_preferences (
    user_id UUID REFERENCES users(id) PRIMARY KEY,
    in_app_enabled BOOLEAN DEFAULT true,
    email_enabled BOOLEAN DEFAULT false,
    teams_enabled BOOLEAN DEFAULT false,
    overdue_alerts BOOLEAN DEFAULT true,
    assignment_alerts BOOLEAN DEFAULT true,
    updated_at TIMESTAMP DEFAULT NOW()
);

-- Shape rules field (added to shapes table)
ALTER TABLE shapes ADD COLUMN rules TEXT;
```

### 7.2 AFW Database Connection

- **Server:** 63.89.3.224:1433
- **Database:** A1100080D1
- **Connection:** Read-only service account
- **Key Tables:** AFW_Customer, AFW_BasicPolInfo, AFW_Employee, AFW_GeneralLedgerDepartment, AFW_Company, AFW_PRCode, AFW_PolicyPersonnel

### 7.3 Performance Requirements

| Metric | Target |
|--------|--------|
| Dashboard load time | < 2 seconds |
| List page load time | < 1 second |
| Sync duration (daily) | < 30 minutes |
| Concurrent users | 10 |
| Total users | 50 |
| Database size (estimated) | < 10 GB |

### 7.4 Docker Deployment

```yaml
# docker-compose.yml structure
services:
  app:
    build: .
    ports:
      - "3000:3000"
    environment:
      - DATABASE_URL=postgresql://...
      - AZURE_AD_CLIENT_ID=...
      - AFW_CONNECTION_STRING=...
    depends_on:
      - db
  
  db:
    image: postgres:17
    volumes:
      - pgdata:/var/lib/postgresql/data
    environment:
      - POSTGRES_DB=ondeck
      - POSTGRES_USER=ondeck_user
      - POSTGRES_PASSWORD=...
```

### 7.5 Security Considerations

- All traffic over HTTPS (TLS 1.2+)
- Database credentials in environment variables
- Read-only AFW database connection
- Parameterized queries (Prisma handles this)
- Input validation on all endpoints
- RBAC enforced at API middleware layer
- Audit logging for sensitive operations
- Session tokens with secure flags

---

## 8. Success Metrics

### 8.1 Adoption Metrics (30/60/90 day targets)

| Metric | 30 Days | 60 Days | 90 Days |
|--------|---------|---------|---------|
| Active users (weekly login) | 60% | 75% | 85% |
| Clients with Shape assigned | 30% | 60% | 90% |
| Tasks created via templates | 50% | 70% | 85% |
| Manager dashboard usage | 50% | 80% | 100% |

### 8.2 Efficiency Metrics

| Metric | Baseline | 6-Month Target |
|--------|----------|----------------|
| On-time task completion | TBD | 95% |
| Missed renewal deadlines | TBD | < 1% |
| Manual coordination time | TBD | -40% |
| Task completion time (avg) | TBD | -25% |

### 8.3 System Health Metrics

| Metric | Target |
|--------|--------|
| Sync success rate | 99% |
| System uptime (business hours) | 99.5% |
| Dashboard response time | < 2 sec (p95) |
| Error rate | < 0.1% |

---

## 9. Open Questions

### 9.1 Resolved (from clarifying questions)

| Question | Resolution |
|----------|------------|
| Priority departments | All departments (phased rollout acceptable) |
| Task auto-generation | Auto-generate; users mark status including N/A with notes |
| Shape extensibility | Start with Shape/Shape2; UI designed for future expansion |
| Sync schedule | 2 AM default; Admin-configurable via UI |
| Historical data | Current calendar year + 24 months |
| Notifications | In-app MVP; Teams/Email configurable for future |
| Mobile | Responsive web; native app future phase |
| Hosting | On-premise Docker deployment |
| User volume | 50 users, 10 concurrent |

### 9.2 Remaining Questions

1. **Entra Configuration:** What are the Entra Group names/IDs for role mapping?
2. **AFW Credentials:** Who provides the read-only service account for AFW database?
3. **Shape Definitions:** What are the initial Shape values to seed? (names, descriptions, colors)
4. **Department Templates:** What are the specific task templates per department?
5. **Renewal Window:** How many days before expiration should tasks auto-generate? (suggest 90)
6. **Backup Strategy:** What is the backup/recovery requirement for the PostgreSQL database?
7. **SSL Certificates:** Who provides/manages SSL certificates for HTTPS?

---

## 10. Implementation Phases

### Phase 1 - MVP (8-10 weeks)

**Sprint 1-2: Foundation**
- [ ] Project setup (Next.js, Prisma, Docker, shadcn/ui)
- [ ] Database schema implementation
- [ ] Entra ID authentication integration
- [ ] Basic role-based access control

**Sprint 3-4: Data Layer**
- [ ] AFW sync engine (clients, policies)
- [ ] Sync scheduling with Admin UI
- [ ] Sync logging and monitoring
- [ ] Client list with search/filter

**Sprint 5-6: Core Features**
- [ ] Client detail page with Shape assignment
- [ ] Personnel assignment (primary)
- [ ] Shape management Admin UI
- [ ] Task CRUD operations

**Sprint 7-8: Task Automation**
- [ ] Task templates per department
- [ ] Auto-generation engine
- [ ] Task status workflow (including N/A with notes)
- [ ] Personal dashboard

**Sprint 9-10: Polish & Deploy**
- [ ] Manager dashboard
- [ ] Admin dashboard (sync health)
- [ ] In-app notifications
- [ ] Testing, bug fixes, deployment

### Phase 2 - Enhancement (6-8 weeks)

- [ ] Additional personnel assignments
- [ ] Bulk task operations
- [ ] Advanced filtering and search
- [ ] Teams/Email notification integration
- [ ] Extended reporting

### Phase 3 - Advanced (8-10 weeks)

- [ ] Calendar view for renewals
- [ ] Custom fields beyond Shape/Shape2
- [ ] API for external integrations
- [ ] Mobile native application
- [ ] Advanced analytics

---

*Document Version: 1.0*  
*Created: January 2026*  
*Last Updated: January 2026*  
*Author: AI Assistant based on stakeholder input*

---

## Appendix A: AFW Database Reference

See `/opt/projects/OnDeck/dev/DATABASE_REFERENCE.md` for complete AFW table schemas, relationships, and query patterns.

## Appendix B: Local Database Connection

```
Host: localhost
Port: 5432
Database: ondeck
User: ondeck_user
Password: ondeck_password_2026!
```

See `/opt/projects/OnDeck/dev/.postgres` for connection strings.