ddtracker/prd.md

# Due Diligence Tracking System
## Product Requirements Document (PRD)

### 1. Executive Summary

A web-based due diligence tracking system that enables systematic evaluation of corporate dimensions through KPIs and checks. The system provides a dashboard overview of progress across multiple dimensions with drill-down capabilities to individual checks.

### 2. System Architecture

#### 2.1 Technology Stack
- **Frontend**: React with TypeScript
- **Styling**: Tailwind CSS
- **Database**: SQLite (via sql.js for browser compatibility)
- **File Storage**: Local file system (server-side)
- **Build Tool**: Vite
- **Deployment**: Self-hosted on dedicated server

#### 2.2 Data Architecture
```
Database: SQLite
├── dimensions (id, name, order, created_at)
├── kpis (id, dimension_id, name, order, created_at)
├── checks (id, kpi_id, question, check_type, current_value, expected_value, 
│          reference_url, reference_file_id, comment, is_completed, created_at, updated_at)
├── check_types (id, name, data_type, options_json)
└── files (id, original_name, stored_path, mime_type, size_bytes, uploaded_at)
```

### 3. Data Model

#### 3.1 Hierarchy
- **Dimension** → **KPIs** → **Checks** (strict 3-level hierarchy)
- One-to-many relationships throughout
- No cross-references between checks and KPIs

#### 3.2 Check Structure
Each check contains:
1. **Question/Statement** (text): The evaluation criteria
2. **Current Value** (dynamic type based on check_type)
3. **Expected Value** (dynamic type based on check_type)
4. **Reference** (URL or file upload)
5. **Comment** (text): Additional notes
6. **Is Completed** (boolean): Manual completion flag

#### 3.3 Check Types
- **Text**: Free-form text input
- **Dropdown**: Predefined options stored in JSON
- **Number**: Numeric input with optional min/max
- **Percentage**: 0-100 with % display

Example check_types configuration:
```json
{
  "development_method": {
    "type": "dropdown",
    "options": ["Traditional", "Agile", "Waterfall", "Hybrid"]
  },
  "team_size": {
    "type": "number",
    "min": 1,
    "max": 1000
  },
  "completion_rate": {
    "type": "percentage"
  }
}
```

### 4. Core Features

#### 4.1 Dashboard View
- **Overview Cards**: Display each dimension with:
  - Icon and name
  - KPI count
  - Total items (open + completed)
  - Progress percentage
  - Visual progress bar
- **Expandable Sections**: Click to reveal KPI details
- **Progress Calculation**: (Completed checks / Total checks) × 100%

#### 4.2 Check Management
- **Add Check**: Form with dynamic fields based on check type
- **Edit Check**: In-place editing with validation
- **Complete Check**: Checkbox to mark as done
- **Bulk Actions**: Select multiple checks for completion/deletion

#### 4.3 File Handling
- **Upload**: Drag-and-drop or click to upload
- **File Types**: All types accepted (max 100MB)
- **Storage**: Server file system with database reference
- **Preview**: Quick preview for common formats (PDF, images)

### 5. User Interface

#### 5.1 Design System Options

**Option A: Professional Blue**
- Primary: #3B82F6 (Blue)
- Success: #10B981 (Green)
- Warning: #F59E0B (Amber)
- Error: #EF4444 (Red)
- Neutral: Gray scale

**Option B: Modern Purple**
- Primary: #8B5CF6 (Purple)
- Success: #10B981 (Green)
- Warning: #F59E0B (Amber)
- Error: #EF4444 (Red)
- Neutral: Slate scale

**Option C: Corporate Dark**
- Primary: #1F2937 (Dark Gray)
- Success: #059669 (Emerald)
- Warning: #D97706 (Amber)
- Error: #DC2626 (Red)
- Neutral: Gray scale

#### 5.2 Layout Structure
```
┌─────────────────────────────────────┐
│ Header (Logo, Title, Actions)       │
├─────────────────────────────────────┤
│ ┌─────────┐ ┌─────────┐ ┌─────────┐│
│ │Strategy │ │ Sales   │ │Product  ││ <- Dimension Cards
│ └─────────┘ └─────────┘ └─────────┘│
│ ┌─────────┐ ┌─────────┐ ┌─────────┐│
│ │Engineer │ │ DevOps  │ │ Cyber   ││
│ └─────────┘ └─────────┘ └─────────┘│
│ ┌─────────┐ ┌─────────┐            │
│ │ People  │ │Culture  │            │
│ └─────────┘ └─────────┘            │
└─────────────────────────────────────┘

Expanded View:
┌─────────────────────────────────────┐
│ ▼ Engineering                       │
├─────────────────────────────────────┤
│   KPI: Development Efficiency       │
│   ├─ Check 1: Dev methodology      │
│   ├─ Check 2: Sprint velocity      │
│   └─ Check 3: Code coverage        │
└─────────────────────────────────────┘
```

### 6. Functional Requirements

#### 6.1 Phase 1 (MVP)
- [ ] Dashboard with all dimensions
- [ ] Expandable dimension views
- [ ] Add/Edit/Complete checks
- [ ] File upload and reference
- [ ] Progress tracking
- [ ] Data persistence in SQLite
- [ ] Export data to JSON/CSV

#### 6.2 Phase 2 (Future)
- [ ] Configurable dimensions
- [ ] Advanced queries and filters
- [ ] PDF report generation
- [ ] Historical progress tracking
- [ ] Multi-user support
- [ ] Check templates
- [ ] Automated reminders

### 7. Technical Implementation

#### 7.1 Database Schema
```sql
-- Dimensions table
CREATE TABLE dimensions (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    name TEXT NOT NULL,
    icon TEXT,
    color TEXT,
    order_index INTEGER,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- KPIs table
CREATE TABLE kpis (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    dimension_id INTEGER NOT NULL,
    name TEXT NOT NULL,
    description TEXT,
    order_index INTEGER,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (dimension_id) REFERENCES dimensions(id)
);

-- Checks table
CREATE TABLE checks (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    kpi_id INTEGER NOT NULL,
    question TEXT NOT NULL,
    check_type TEXT NOT NULL,
    current_value TEXT,
    expected_value TEXT,
    reference_url TEXT,
    reference_file_id INTEGER,
    comment TEXT,
    is_completed BOOLEAN DEFAULT 0,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (kpi_id) REFERENCES kpis(id),
    FOREIGN KEY (reference_file_id) REFERENCES files(id)
);

-- Files table
CREATE TABLE files (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    original_name TEXT NOT NULL,
    stored_path TEXT NOT NULL,
    mime_type TEXT,
    size_bytes INTEGER,
    uploaded_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- Check types configuration
CREATE TABLE check_types (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    name TEXT NOT NULL UNIQUE,
    data_type TEXT NOT NULL,
    options_json TEXT,
    validation_json TEXT
);
```

#### 7.2 API Endpoints
```
GET    /api/dimensions
GET    /api/dimensions/:id/kpis
GET    /api/kpis/:id/checks
POST   /api/checks
PUT    /api/checks/:id
DELETE /api/checks/:id
POST   /api/files/upload
GET    /api/files/:id
GET    /api/export/:format (json|csv)
```

### 8. Non-Functional Requirements

#### 8.1 Performance
- Dashboard load time < 2 seconds
- Check updates < 500ms
- Support 10,000+ checks without degradation

#### 8.2 Security
- File upload validation
- SQL injection prevention
- XSS protection
- File size limits (100MB)

#### 8.3 Usability
- Responsive design (mobile, tablet, desktop)
- Keyboard navigation support
- Print-friendly views
- Intuitive drag-and-drop

### 9. Initial Data Structure

```json
{
  "dimensions": [
    {
      "name": "Strategy",
      "icon": "Briefcase",
      "kpis": [
        {
          "name": "Strategic Alignment",
          "checks": [
            {
              "question": "Is the company vision clearly defined?",
              "check_type": "dropdown",
              "options": ["Yes", "Partially", "No"]
            }
          ]
        }
      ]
    },
    {
      "name": "Sales",
      "icon": "TrendingUp",
      "kpis": [
        {
          "name": "Sales Performance",
          "checks": [
            {
              "question": "Monthly revenue growth rate",
              "check_type": "percentage"
            }
          ]
        }
      ]
    },
    {
      "name": "Engineering",
      "icon": "Code",
      "kpis": [
        {
          "name": "Development Practices",
          "checks": [
            {
              "question": "Development methodology",
              "check_type": "dropdown",
              "options": ["Traditional", "Agile", "Waterfall", "Hybrid"]
            },
            {
              "question": "Average sprint velocity",
              "check_type": "number"
            }
          ]
        }
      ]
    }
  ]
}
```

### 10. Development Roadmap

#### Week 1-2: Foundation
- Set up project structure
- Implement SQLite integration
- Create basic UI components
- Dashboard layout

#### Week 3-4: Core Features
- Check CRUD operations
- File upload system
- Progress calculations
- Expand/collapse functionality

#### Week 5-6: Polish & Testing
- Validation rules
- Export functionality
- Responsive design
- Performance optimization

### 11. Success Metrics

- **Efficiency**: 50% reduction in due diligence tracking time
- **Completeness**: 100% of checks documented
- **Visibility**: Real-time progress tracking
- **Usability**: < 5 minute onboarding time

### 12. Appendix

#### Sample Check Types Configuration
```javascript
const checkTypes = {
  text: {
    component: 'TextInput',
    validation: null
  },
  dropdown: {
    component: 'SelectInput',
    validation: 'requiredOption'
  },
  number: {
    component: 'NumberInput',
    validation: 'numeric'
  },
  percentage: {
    component: 'PercentageInput',
    validation: 'range:0:100'
  }
};
```

#### File Structure
```
due-diligence-tracker/
├── client/
│   ├── src/
│   │   ├── components/
│   │   ├── pages/
│   │   ├── hooks/
│   │   ├── utils/
│   │   └── types/
│   └── public/
├── server/
│   ├── api/
│   ├── db/
│   ├── uploads/
│   └── utils/
└── shared/
    └── types/
```