# Database Changes - October 4, 2025

## Generic Document Management System

### Summary
Enhanced the document management system to be completely generic and entity-agnostic. Documents can now be linked to any entity type (cases, clients, projects, employees, etc.) using metadata and hierarchical tags.

---

## Changes to TAFDB.pgsql

### 1. Enhanced `document_metadata` Table

**New Columns Added:**
- `document_id` - Direct reference to documents table (uuid)
- `metadata_key` - Standardized key (varchar 100)
- `metadata_value` - Metadata value (text)
- `value_type` - Data type hint: 'text', 'number', 'date', 'uuid', 'json' (varchar 20)
- `searchable` - Whether to index for search (boolean, default true)
- `indexed` - Performance optimization flag (boolean, default false)

**Constraints:**
- Unique constraint on (document_id, metadata_key)

**Indexes Created:**
- `idx_doc_metadata_key` - Index on metadata_key
- `idx_doc_metadata_value` - Index on metadata_value (where searchable = true)
- `idx_doc_metadata_doc` - Index on document_id
- `idx_doc_metadata_doc_key` - Composite index on (document_id, metadata_key)
- `idx_doc_metadata_case_lookup` - Partial index for case_id lookups
- `idx_doc_metadata_client_lookup` - Partial index for client_id lookups

**Comments Added:**
- Table comment explaining enhanced metadata system
- Column comments for document_id, metadata_key, metadata_value, value_type, searchable

### 2. Enhanced `document_tags` Table

**New Columns Added:**
- `parent_tag_id` - For hierarchical organization (uuid, self-reference)
- `tag_type` - Tag origin: 'system', 'category', 'custom', 'auto' (varchar 50)
- `color` - UI display color in hex format (varchar 7)
- `icon` - Icon identifier for UI display (varchar 50)
- `sort_order` - Display order (integer, default 0)

**Constraints:**
- Unique constraint on (document_id, tag)

**Indexes Created:**
- `idx_document_tags_parent` - Index on parent_tag_id
- `idx_document_tags_doc` - Index on document_id
- `idx_document_tags_tag` - Index on tag
- `idx_doc_tags_system` - Partial index for system tags

**Comments Added:**
- Table comment explaining hierarchical tag system
- Column comments for parent_tag_id, tag_type, color, icon

### 3. Views (Handled by Models)

**Note:** Document views are implemented in the `Documents` model class (`models/Documents.php`) rather than as SQL views. This provides more flexibility and follows the MVC pattern.

**Available Model Methods:**
- `getEntityDocuments($entityType, $entityId)` - Generic method for any entity
- `getCaseDocuments($caseId)` - Get case documents
- `getClientDocuments($clientId)` - Get client documents  
- `getProjectDocuments($projectId)` - Get project documents
- `getCaseDocumentsView($caseId)` - Aggregated case view with case details

### 4. New Function: `link_document_to_entity()`

**Purpose:** Generic function to link documents to any entity type

**Parameters:**
- `p_document_id` (uuid) - Document to link
- `p_entity_type` (text) - Entity type (e.g., 'case', 'client', 'project')
- `p_entity_id` (uuid) - Entity ID
- `p_entity_metadata` (jsonb) - Additional metadata as JSON object
- `p_auto_tag` (boolean) - Whether to create system tags (default: true)

**Behavior:**
1. Creates metadata entry: `{entity_type}_id` → `{entity_id}`
2. Processes all key-value pairs from p_entity_metadata
3. Creates system tag for entity type
4. Creates specific entity tag if number/name available

**Example Usage:**
```sql
SELECT link_document_to_entity(
  'doc-uuid'::uuid,
  'case',
  'case-uuid'::uuid,
  '{"case_number": "MC001", "client_name": "John Doe"}'::jsonb,
  true
);
```

### 5. New Function: `link_document_to_case()`

**Purpose:** Legacy wrapper for backward compatibility

**Parameters:**
- `p_document_id` (uuid) - Document to link
- `p_case_id` (uuid) - Case ID
- `p_auto_tag` (boolean) - Whether to create system tags (default: true)

**Behavior:**
1. Retrieves case details (case_number, title, client_id, client_name)
2. Builds metadata JSON object
3. Calls generic `link_document_to_entity()` function

**Example Usage:**
```sql
SELECT link_document_to_case('doc-uuid'::uuid, 'case-uuid'::uuid, true);
```

### 6. New Function: `get_entity_documents()`

**Purpose:** Generic function to retrieve documents for any entity

**Parameters:**
- `p_entity_type` (text) - Entity type (e.g., 'case', 'client', 'project')
- `p_entity_id` (uuid) - Entity ID

**Returns Table:**
- document_id, title, description, type
- source_table, source_context
- tags (JSON array)
- metadata (JSON object)
- updated_at

**Behavior:**
1. Dynamically builds metadata_key as `{entity_type}_id`
2. Joins documents with metadata using computed key
3. Aggregates all tags and metadata
4. Filters out deleted and archived documents

**Example Usage:**
```sql
SELECT * FROM get_entity_documents('case', 'case-uuid'::uuid);
SELECT * FROM get_entity_documents('client', 'client-uuid'::uuid);
SELECT * FROM get_entity_documents('project', 'project-uuid'::uuid);
```

### 7. New Function: `get_case_documents()`

**Purpose:** Legacy wrapper for backward compatibility

**Parameters:**
- `p_case_id` (uuid) - Case ID

**Returns:** Same as `get_entity_documents()`

**Behavior:**
- Calls `get_entity_documents('case', p_case_id)`

**Example Usage:**
```sql
SELECT * FROM get_case_documents('case-uuid'::uuid);
```

---

## Migration Strategy

### For Existing Databases

If updating an existing database, you'll need to migrate data:

```sql
-- Migrate document_version_id references to document_id
UPDATE document_metadata dm
SET document_id = dv.document_id
FROM document_versions dv
WHERE dm.document_version_id = dv.document_version_id
  AND dm.document_id IS NULL;

-- Migrate existing data to new columns
UPDATE document_metadata 
SET 
  metadata_key = meta_key,
  metadata_value = meta_value
WHERE metadata_key IS NULL;

-- Migrate case_instructions document links
INSERT INTO document_metadata (document_id, metadata_key, metadata_value, value_type)
SELECT 
  unnest(string_to_array(ci.document_ids, ','))::uuid as document_id,
  'case_id',
  ci.case_id::text,
  'uuid'
FROM case_instructions ci
WHERE ci.document_ids IS NOT NULL AND ci.document_ids != ''
ON CONFLICT (document_id, metadata_key) DO UPDATE
SET metadata_value = EXCLUDED.metadata_value, updatedAt = now();

-- Add system tags for existing case documents
INSERT INTO document_tags (document_id, tag, tag_type)
SELECT DISTINCT
  unnest(string_to_array(document_ids, ','))::uuid as document_id,
  'case' as tag,
  'system' as tag_type
FROM case_instructions
WHERE document_ids IS NOT NULL AND document_ids != ''
ON CONFLICT (document_id, tag) DO NOTHING;
```

### For New Databases

Simply run the updated `TAFDB.pgsql` schema file. All tables, indexes, views, and functions will be created with the new structure.

---

## Benefits

1. **Reusability** - Write document code once, use for any entity
2. **Flexibility** - Add new entity types without schema changes
3. **Searchability** - Metadata is indexed and searchable
4. **Organization** - Hierarchical tags for powerful categorization
5. **Performance** - Optimized indexes for common queries
6. **Future-Proof** - Easy to extend with new metadata or tags

---

## Testing Queries

### Test Generic Entity Linking
```sql
-- Link document to case
SELECT link_document_to_entity(
  'doc-123'::uuid,
  'case',
  'case-456'::uuid,
  '{"case_number": "MC001", "client_name": "John Doe"}'::jsonb
);

-- Link document to client
SELECT link_document_to_entity(
  'doc-789'::uuid,
  'client',
  'client-101'::uuid,
  '{"name": "Acme Corp", "status": "active"}'::jsonb
);

-- Link document to project
SELECT link_document_to_entity(
  'doc-999'::uuid,
  'project',
  'project-555'::uuid,
  '{"project_name": "Website Redesign", "manager": "Jane Smith"}'::jsonb
);
```

### Test Generic Document Retrieval
```sql
-- Get case documents
SELECT * FROM get_entity_documents('case', 'case-456'::uuid);

-- Get client documents
SELECT * FROM get_entity_documents('client', 'client-101'::uuid);

-- Get project documents
SELECT * FROM get_entity_documents('project', 'project-555'::uuid);
```

### Test Metadata Queries
```sql
-- Find all documents for a specific case
SELECT d.* 
FROM documents d
JOIN document_metadata dm ON d.document_id = dm.document_id
WHERE dm.metadata_key = 'case_id' 
  AND dm.metadata_value = 'case-456';

-- Search documents by case number
SELECT d.* 
FROM documents d
JOIN document_metadata dm ON d.document_id = dm.document_id
WHERE dm.metadata_key = 'case_number' 
  AND dm.metadata_value = 'MC001';
```

### Test Tag Queries
```sql
-- Find all case documents
SELECT d.*
FROM documents d
JOIN document_tags dt ON d.document_id = dt.document_id
WHERE dt.tag = 'case' AND dt.tag_type = 'system';

-- Find documents with specific case tag
SELECT d.*
FROM documents d
JOIN document_tags dt ON d.document_id = dt.document_id
WHERE dt.tag = 'case:MC001' AND dt.tag_type = 'system';
```

---

## Related Files

- **Schema**: `TAFDB.pgsql` (lines 844-915 for tables, end of file for views/functions)
- **Frontend Helpers**: `FrontEnd/js/helpers/documentHelpers.js`
- **Backend API**: `api/documents/link.php`
- **Documentation**: `docs/GenericDocumentSystem.md`
- **Import Map**: `import-map.json`, `FrontEnd/Dashboard.php`

---

## Next Steps

1. ✅ Schema updated in TAFDB.pgsql
2. ✅ Generic helpers created (documentHelpers.js)
3. ✅ Generic API created (link.php)
4. ✅ Import maps updated
5. ⏳ Run updated schema on database
6. ⏳ Create module-specific wrapper functions
7. ⏳ Update module configs to use new document system
8. ⏳ Test with multiple entity types