docs/agent/implementation-notes/DATABASE_ENUM_REFERENCE.md

# Database Enum Types Reference

## Enum Naming Convention

The database uses **PascalCase** for enum type names (defined in `01_foundation.sql`):

| Enum Type | Values |
|-----------|--------|
| `AppRole` | `platform_admin`, `client_admin`, `contractor_admin`, `sales_manager`, `project_manager`, `dispatcher`, `field_agent`, `sales_agent` |
| `UserStatus` | `invited`, `pending_setup`, `active`, `suspended` |
| `PayoutMethod` | `mobile_money`, `bank_transfer`, `cash`, `other` |
| `OrganizationType` | `client`, `contractor` |

## New Enums (User Invitations)

Added in `11_user_invitations.sql`:

| Enum Type | Values |
|-----------|--------|
| `invitation_status` | `pending`, `accepted`, `expired`, `cancelled` |
| `invitation_method` | `whatsapp`, `email`, `both` |

## Usage in Migrations

When creating tables, reference enums with PascalCase:

```sql
-- ✅ CORRECT
invited_role AppRole NOT NULL

-- ❌ WRONG
invited_role app_role NOT NULL
```

## Usage in SQLAlchemy Models

In Python models, use String columns and let the database handle enum constraints:

```python
# ✅ CORRECT
role = Column(String(50), nullable=False)  # AppRole ENUM in DB
status = Column(String(50), default='invited')  # UserStatus ENUM in DB

# The database enforces the enum constraint
# Python just sees it as a string
```

## Usage in Constraints

When referencing enum values in constraints, cast explicitly:

```sql
-- ✅ CORRECT
invited_role = 'platform_admin'::AppRole

-- Also works (implicit cast)
invited_role = 'platform_admin'
```

## Migration File Fixed

The `11_user_invitations.sql` migration has been corrected to use:
- `AppRole` instead of `app_role`
- Proper enum casting in constraints

You can now run the migration successfully!