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:

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

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

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

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/