# Generic Document Management System

## Overview

The TAF ERP includes a **completely generic document management system** that can link documents to any entity type (cases, clients, projects, employees, etc.) using metadata and hierarchical tags. This approach avoids hardcoding entity-specific logic and provides maximum flexibility.

## Architecture

### Core Principles

1. **Entity-Agnostic Design**: All functions accept an `entity_type` parameter instead of hardcoded references
2. **Metadata-Based Linking**: Documents link to entities through flexible key-value metadata
3. **Hierarchical Tags**: Documents organized with system tags, custom tags, and nested categories
4. **No Direct Foreign Keys**: The `documents` table doesn't reference specific entity tables

### Database Schema

#### document_metadata Table
```sql
document_metadata (
  document_id uuid,           -- Links to documents table
  metadata_key text,          -- e.g., "case_id", "client_id", "project_id"
  metadata_value text,        -- The actual value (stored as text)
  value_type text,            -- 'uuid', 'text', 'number', 'date', 'json'
  searchable boolean,         -- Whether to index for search
  indexed boolean             -- Performance optimization flag
)
```

**Example Metadata Entries:**
```sql
-- Linking a document to a case
INSERT INTO document_metadata VALUES 
  (doc_uuid, 'case_id', 'abc-123-uuid', 'uuid', true, true),
  (doc_uuid, 'case_number', 'MC001', 'text', true, false),
  (doc_uuid, 'client_name', 'John Doe', 'text', true, false);
```

#### document_tags Table
```sql
document_tags (
  document_id uuid,
  tag text,                   -- Tag name (e.g., "case", "urgent", "draft")
  parent_tag_id uuid,         -- For hierarchical organization
  tag_type text,              -- 'system', 'category', 'custom', 'auto'
  color text,                 -- UI color for visual organization
  icon text,                  -- FontAwesome icon class
  sort_order int              -- Display order
)
```

**Tag Types:**
- **system**: Auto-generated tags like "case", "case:MC001", "client"
- **category**: Organizational categories (e.g., "legal", "financial")
- **custom**: User-defined tags
- **auto**: Tags generated by rules or AI

## Database Functions

### Generic Functions

#### link_document_to_entity()
```sql
SELECT link_document_to_entity(
  p_document_id := 'doc-uuid',
  p_entity_type := 'case',           -- or 'client', 'project', etc.
  p_entity_id := 'entity-uuid',
  p_entity_metadata := '{
    "case_number": "MC001",
    "case_title": "Smith vs Jones",
    "client_id": "client-uuid",
    "client_name": "John Doe"
  }'::jsonb,
  p_auto_tag := true
);
```

**What it does:**
1. Creates `{entity_type}_id` metadata entry (e.g., `case_id`, `client_id`)
2. Adds all key-value pairs from `p_entity_metadata` to metadata
3. Creates system tag for entity type (e.g., "case")
4. Creates specific entity tag if number/name available (e.g., "case:MC001")

#### get_entity_documents()
```sql
SELECT * FROM get_entity_documents(
  p_entity_type := 'case',
  p_entity_id := 'case-uuid'
);
```

**Returns:** All documents linked to the specified entity with aggregated tags and metadata.

### Legacy Functions

For backward compatibility, legacy case-specific functions exist:
- `link_document_to_case(p_document_id, p_case_id, p_auto_tag)` - Wraps generic function
- `get_case_documents(p_case_id)` - Wraps generic function

## PHP Model (MVC Pattern)

The `Documents` model (`models/Documents.php`) provides object-oriented methods for working with documents:

### Model Methods

```php
use App\Models\Documents;

$documentsModel = new Documents();

// Generic method - works for any entity type
$docs = $documentsModel->getEntityDocuments('case', $caseId);
$docs = $documentsModel->getEntityDocuments('client', $clientId);
$docs = $documentsModel->getEntityDocuments('project', $projectId);

// Convenience wrappers
$caseDocs = $documentsModel->getCaseDocuments($caseId);
$clientDocs = $documentsModel->getClientDocuments($clientId);
$projectDocs = $documentsModel->getProjectDocuments($projectId);

// Link document to entity (generic)
$documentsModel->linkToEntity(
    $documentId,
    'case',
    $caseId,
    [
        'case_number' => 'MC001',
        'case_title' => 'Smith vs Jones',
        'client_id' => $clientId,
        'client_name' => 'John Doe'
    ],
    true  // auto-tag
);

// Convenience wrapper for cases
$documentsModel->linkToCase($documentId, $caseId, true);

// Get case documents with case details (replaces case_documents view)
$caseDocsView = $documentsModel->getCaseDocumentsView($caseId);
```

**Note:** Views are implemented as model methods rather than SQL views for flexibility and maintainability.

## Frontend API

### Generic Helpers (documentHelpers.js)

All document management goes through **generic** helper functions:

#### uploadAndLinkDocument(options)
```javascript
import { uploadAndLinkDocument } from 'helpers/documenthelpers';

uploadAndLinkDocument({
  entityType: 'case',           // or 'client', 'project', etc.
  entityId: caseId,
  entityLabel: caseNumber,
  entityData: {                 // Additional metadata to store
    case_number: caseNumber,
    case_title: caseTitle,
    client_id: clientId,
    client_name: clientName
  },
  onComplete: () => {
    console.log('Document uploaded and linked');
    refreshDocumentList();
  }
});
```

#### Other Functions
```javascript
import { 
  previewDocument,        // Opens document in new window
  downloadDocument,       // Triggers file download
  manageDocumentTags,     // Opens tag management modal
  showDocumentMetadata    // Displays metadata modal
} from 'helpers/documenthelpers';

// Usage with DataLoader context
previewDocument({ record: documentRecord });
downloadDocument({ record: documentRecord });
```

### Backend API (DocumentsController)

Generic endpoint for linking documents via controller:

```javascript
const response = await fetch('/api/documents/link-to-entity', {
  method: 'POST',
  headers: { 'Content-Type': 'application/json' },
  body: JSON.stringify({
    document_id: docId,
    entity_type: 'case',        // Generic!
    entity_id: caseId,
    entity_data: {              // Flexible metadata
      case_number: 'MC001',
      client_id: clientId,
      client_name: 'John Doe'
    },
    source_context: 'manual_upload',
    custom_tags: ['urgent', 'review_needed']
  })
});
```

**Route:** `/api/documents/link-to-entity`  
**Controller:** `DocumentsController::linkToEntity()`  
**Method:** POST

## Module Integration

### Case Management Example

Create a wrapper function in your module-specific helper:

```javascript
// In caseManagementHelpers.js
import { uploadAndLinkDocument } from 'helpers/documenthelpers';

export function uploadCaseDocument(ctx) {
  const selectedCase = ctx.selectedCase || ctx.record;
  
  uploadAndLinkDocument({
    entityType: 'case',
    entityId: selectedCase.case_id,
    entityLabel: selectedCase.case_number,
    entityData: {
      case_number: selectedCase.case_number,
      case_title: selectedCase.title,
      client_id: selectedCase.client_id,
      client_name: selectedCase.client_name,
      matter_type: selectedCase.matter_type
    },
    onComplete: () => {
      // Refresh the DataLoader table
      ctx.dataLoader?.refresh?.();
      Toast.success('Document uploaded successfully');
    }
  });
}
```

### Module Configuration

Configure the documents tab with metadata field mappings in your module config:

```javascript
// In sql/modules.sql or ModuleBuilder
{
  "tabs": [
    {
      "label": "Documents",
      "elements": [
        {
          "type": "documents",
          "options": {
            "filterField": "case_id",
            "metadataFields": {
              "case_number": "case_number",      // metadata_key: record_field
              "case_title": "title",
              "client_id": "client_id",
              "client_name": "client_name",
              "matter_type": "matter_type",
              "status": "status"
            }
          }
        }
      ]
    }
  ]
}
```

**Configuration Options:**
- `filterField` - The field to filter documents by (e.g., "case_id", "client_id")
- `metadataFields` - Object mapping metadata keys to record fields
  - Keys: Metadata field names stored in database
  - Values: Field names from `this.record` in the modal
  - Supports nested fields: `"client_name": "client.name"`

**For Other Entity Types:**

```javascript
// Client documents
{
  "type": "documents",
  "options": {
    "filterField": "client_id",
    "metadataFields": {
      "name": "name",
      "client_code": "code",
      "status": "status",
      "industry": "industry"
    }
  }
}

// Project documents
{
  "type": "documents",
  "options": {
    "filterField": "project_id",
    "metadataFields": {
      "project_name": "name",
      "project_code": "code",
      "manager_id": "manager_id",
      "manager_name": "manager.name",  // Nested field
      "start_date": "start_date"
    }
  }
}
```

## Usage Examples

### For Different Entity Types

#### Client Documents
```javascript
uploadAndLinkDocument({
  entityType: 'client',
  entityId: clientId,
  entityLabel: clientName,
  entityData: {
    client_name: clientName,
    client_code: clientCode,
    status: 'active'
  }
});
```

#### Project Documents
```javascript
uploadAndLinkDocument({
  entityType: 'project',
  entityId: projectId,
  entityLabel: projectName,
  entityData: {
    project_name: projectName,
    project_code: projectCode,
    manager_id: managerId,
    start_date: startDate
  }
});
```

#### Employee Documents
```javascript
uploadAndLinkDocument({
  entityType: 'employee',
  entityId: employeeId,
  entityLabel: employeeName,
  entityData: {
    employee_number: empNumber,
    department: department,
    position: position
  }
});
```

## Migration

Run the migration to set up the enhanced schema:

```bash
docker exec -i taf-postgres psql -U postgres -d tafdb < migrations/20251004_document_metadata_enhancement.sql
```

This migration:
- Adds new columns to `document_metadata` and `document_tags` tables
- Creates generic helper functions
- Creates legacy wrapper functions for backward compatibility
- Migrates existing case document data
- Creates performance indexes

## Benefits

1. **Reusability**: Write document code once, use for any entity
2. **Flexibility**: Add new entity types without modifying core code
3. **Searchability**: Metadata is indexed and searchable
4. **Organization**: Hierarchical tags provide powerful organization
5. **Future-Proof**: Easy to extend with new metadata fields or tag types
6. **No Schema Changes**: Adding new entity types doesn't require schema updates

## Best Practices

1. **Always use generic functions**: Never hardcode entity types in core document code
2. **Create wrapper functions**: Module-specific wrappers make calls cleaner
3. **Store searchable metadata**: Include fields users might search (names, numbers, dates)
4. **Use system tags**: Let the system auto-tag with entity types
5. **Add custom tags**: Supplement with workflow or status tags
6. **Validate entity context**: Ensure entity_id and entity_type are valid before linking
7. **Handle errors gracefully**: Document upload/linking can fail - show clear messages

## Security

- All document operations require JWT authentication
- Document access controlled through `AuthorizationEngine`
- Metadata can include access control fields (e.g., `department_id`, `access_level`)
- Tags can trigger access policies (e.g., "confidential" tag restricts access)

## Testing

Test document system with multiple entity types:

```javascript
// Test case documents
uploadAndLinkDocument({ entityType: 'case', entityId: caseId, ... });

// Test client documents
uploadAndLinkDocument({ entityType: 'client', entityId: clientId, ... });

// Verify metadata stored correctly
const docs = await get_entity_documents('case', caseId);
console.assert(docs[0].metadata.case_number === 'MC001');

// Verify tags created
console.assert(docs[0].tags.includes('case'));
console.assert(docs[0].tags.includes('case:MC001'));
```

## Troubleshooting

**Problem:** Documents not appearing in entity's document list

**Solution:** Check metadata table for `{entity_type}_id` entry:
```sql
SELECT * FROM document_metadata 
WHERE metadata_key = 'case_id' AND metadata_value = 'your-case-uuid';
```

**Problem:** System tags not being created

**Solution:** Ensure `p_auto_tag` is `true` when calling `link_document_to_entity()`

**Problem:** Search not finding documents

**Solution:** Verify `searchable` column is `true` for relevant metadata:
```sql
UPDATE document_metadata SET searchable = true 
WHERE metadata_key IN ('case_number', 'client_name', 'title');
```

## Future Enhancements

- **Full-text search**: Index document content for searching within files
- **Version control**: Track document versions with metadata inheritance
- **Workflow integration**: Link documents to workflow states
- **AI tagging**: Automatically categorize documents based on content
- **Smart folders**: Virtual folders based on metadata queries
- **Access control**: Fine-grained permissions using metadata + tags

## Files Reference

- **Database Schema**: `TAFDB.pgsql` (tables and functions)
- **PHP Model**: `models/Documents.php` (replaces SQL views with methods)
- **Frontend Helpers**: `FrontEnd/js/helpers/documentHelpers.js`
- **Backend Controller**: `api/controllers/DocumentsController.php` (linkToEntity method)
- **Import Map**: `import-map.json` (includes `helpers/documenthelpers`)
- **Dashboard**: `FrontEnd/Dashboard.php` (includes import map entry)
- **Documentation**: `docs/DatabaseChanges_20251004.md` (migration guide)