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)

-- 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