# Database Schema vs Model Field Mapping Guide

## Overview
This guide documents the critical database schema mismatches in the IGNY8 project that cause 500 errors. These mismatches occur because the database uses OLD column names while Django models use NEW refactored field names.

---

## Critical Issue: Database Column Name Mismatches

### Problem Statement
During the "Stage 1 Refactor", model field names were changed but database column names were NOT renamed. This causes:
- **500 Internal Server Error** - When Django tries to query non-existent columns
- **AttributeError** - When code references old field names on model instances
- **FieldError** - When filtering/querying with old field names

### Root Cause
The refactor changed the **Python model field names** but the **actual PostgreSQL database columns** still use the old names. Django expects the new field names but the database has the old column names.

---

## Known Database vs Model Mismatches

### Content Model (`igny8_content` table)

| Model Field Name | Database Column Name | Fix Required |
|-----------------|---------------------|--------------|
| `content_html` | `html_content` | Add `db_column='html_content'` |
| `content_type` | `entity_type` | Add `db_column='entity_type'` |
| `content_structure` | `cluster_role` | Add `db_column='cluster_role'` |

**Location:** `backend/igny8_core/business/content/models.py` - Content model

**Additional Fields to Include:**
- `external_type` - exists in database, must be in model
- `sync_status` - exists in database, must be in model

### Tasks Model (`igny8_tasks` table)

| Model Field Name | Database Column Name | Fix Required |
|-----------------|---------------------|--------------|
| `content_type` | `entity_type` | Add `db_column='entity_type'` |
| `content_structure` | `cluster_role` | Add `db_column='cluster_role'` |
| `taxonomy_term` | `taxonomy_id` | Add `db_column='taxonomy_id'` |
| `idea` | `idea_id` | Add `db_column='idea_id'` |
| `keywords` | `keywords` (text) | Change from ManyToManyField to TextField |

**Location:** `backend/igny8_core/business/content/models.py` - Tasks model

### ContentTaxonomy Relations (`igny8_content_taxonomy_relations` table)

| Model Expects | Database Has | Fix Required |
|--------------|-------------|--------------|
| `contenttaxonomy_id` | `taxonomy_id` | Create through model with `db_column` |
| Auto M2M table | Custom table | Use `through='ContentTaxonomyRelation'` |

**Location:** Must create `ContentTaxonomyRelation` through model with explicit `db_column` settings

---

## Common Code Reference Errors

### Issue: Code Still Uses Old Field Names

Even after fixing the model, **view code** might still reference old field names:

**Wrong References to Fix:**
- `content.entity_type` → Should be `content.content_type`
- `content.cluster_role` → Should be `content.content_structure`
- `content.html_content` → Should be `content.content_html`
- `content.taxonomies` → Should be `content.taxonomy_terms`

**Files to Check:**
- `backend/igny8_core/modules/writer/views.py`
- `backend/igny8_core/modules/integration/views.py`
- `backend/igny8_core/business/*/services/*.py`
- `backend/igny8_core/ai/functions/*.py`

**Search Commands:**
```bash
grep -r "\.entity_type" backend/igny8_core/
grep -r "\.cluster_role" backend/igny8_core/
grep -r "\.html_content" backend/igny8_core/
grep -r "\.taxonomies" backend/igny8_core/
```

---

## Diagnostic Checklist for 500 Errors

When encountering 500 Internal Server Errors, follow this checklist:

### Step 1: Check Backend Logs
```bash
docker logs --tail=100 igny8_backend 2>&1 | grep -A 20 "Traceback"
docker logs igny8_backend 2>&1 | grep "UndefinedColumn\|does not exist"
```

### Step 2: Identify Error Type

**A. ProgrammingError: column does not exist**
- Error shows: `column igny8_content.field_name does not exist`
- **Action:** Database has different column name than model expects
- **Fix:** Add `db_column='actual_database_column_name'` to model field

**B. AttributeError: object has no attribute**
- Error shows: `'Content' object has no attribute 'entity_type'`
- **Action:** Code references old field name
- **Fix:** Update code to use new field name from model

**C. FieldError: Cannot resolve keyword**
- Error shows: `Cannot resolve keyword 'entity_type' into field`
- **Action:** Query/filter uses old field name
- **Fix:** Update queryset filters to use new field names

### Step 3: Verify Database Schema
```bash
docker exec igny8_backend python manage.py shell -c "
from django.db import connection
cursor = connection.cursor()
cursor.execute('SELECT column_name FROM information_schema.columns WHERE table_name = %s ORDER BY ordinal_position', ['igny8_content'])
print('\n'.join([row[0] for row in cursor.fetchall()]))
"
```

### Step 4: Compare Against Model
Check if model field names match database column names. If not, add `db_column` attribute.

### Step 5: Search for Code References
After fixing model, search all code for references to old field names and update them.

---

## Prevention: Pre-Refactor Checklist

Before doing ANY database model refactoring:

### 1. Document Current State
- [ ] List all current database column names
- [ ] List all current model field names  
- [ ] Identify any existing `db_column` mappings

### 2. Plan Migration Strategy
- [ ] Decide: Rename database columns OR add `db_column` mappings?
- [ ] If renaming: Create migration to rename columns
- [ ] If mapping: Document which fields need `db_column`

### 3. Update All Code References
- [ ] Search codebase for old field name references
- [ ] Update view code, serializers, services
- [ ] Update filters, querysets, aggregations
- [ ] Update AI functions and background tasks

### 4. Test Before Deploy
- [ ] Run migrations on development database
- [ ] Test all API endpoints with authentication
- [ ] Check logs for database errors
- [ ] Verify no AttributeError or FieldError exceptions

### 5. Maintain Documentation
- [ ] Update this guide with new mappings
- [ ] Document why `db_column` is needed
- [ ] Note any related field name changes

---

## Quick Fix Template

When you find a database mismatch:

```python
# In the model file (e.g., backend/igny8_core/business/content/models.py)

# OLD (causes 500 error):
content_type = models.CharField(max_length=100)

# FIXED (maps to actual database column):
content_type = models.CharField(
    max_length=100,
    db_column='entity_type',  # <- Maps to actual database column name
    blank=True,
    null=True
)
```

Then search and replace code references:
```bash
# Find all references to old field name
grep -rn "\.entity_type" backend/

# Update to new field name
sed -i 's/\.entity_type/.content_type/g' backend/igny8_core/modules/writer/views.py
```

---

## Frontend Common Issues

### Issue: Undefined Variables
**Error:** `ReferenceError: variableName is not defined`

**Common Causes:**
- Variable declared but removed during refactor
- Variable in dependency array but not defined in component
- Import removed but variable still referenced

**Files to Check:**
- `frontend/src/pages/Writer/Tasks.tsx`
- `frontend/src/pages/Writer/Content.tsx`
- Any component showing console loops

**Fix:** Remove from dependency arrays or add proper state declaration

---

## Testing Endpoints After Fixes

Use this command to verify all critical endpoints:

```bash
TOKEN=$(curl -s -X POST "https://api.igny8.com/api/v1/auth/login/" \
  -H "Content-Type: application/json" \
  -d '{"email":"dev@igny8.com","password":"dev123456"}' | \
  python3 -c "import sys, json; print(json.load(sys.stdin)['data']['access'])")

# Test each endpoint
curl -s "https://api.igny8.com/api/v1/writer/content/?site_id=5&page_size=1" \
  -H "Authorization: Bearer $TOKEN" | python3 -m json.tool

curl -s "https://api.igny8.com/api/v1/planner/clusters/?site_id=5&page_size=1" \
  -H "Authorization: Bearer $TOKEN" | python3 -m json.tool

curl -s "https://api.igny8.com/api/v1/writer/tasks/?site_id=5&page_size=1" \
  -H "Authorization: Bearer $TOKEN" | python3 -m json.tool
```

**Success Indicator:** Response should have `"success": true`, not `"success": false` with error.

---

## Status Codes Quick Reference

| Code | Meaning | Common Cause | Fix |
|------|---------|--------------|-----|
| 500 | Internal Server Error | Database column mismatch, code error | Check logs, fix model or code |
| 403 | Forbidden / Auth Required | Normal - means endpoint works but needs auth | Login first, use Bearer token |
| 404 | Not Found | URL wrong or object doesn't exist | Check URL, verify ID exists |
| 400 | Bad Request | Invalid data sent | Check request payload |

**Important:** If you see 403 instead of 500 after a fix, that's SUCCESS - it means the endpoint works and just needs authentication.

---

## Summary

**Key Principle:** The database schema is the source of truth. Models must map to actual database column names, not the other way around (unless you run migrations to rename columns).

**Golden Rule:** After ANY refactor that changes field names:
1. Check if database columns were actually renamed
2. If not, add `db_column` mappings in models
3. Update all code references to use NEW field names
4. Test all endpoints with authentication
5. Check logs for database errors

**Remember:** A 500 error from a database column mismatch will cascade - fixing the model isn't enough, you must also update all code that references the old field names.
