docs/agent/implementation-notes/TICKET_DEDUPLICATION_CORRECTION.md

# Ticket Management - Critical Design Corrections

## Overview
This document explains the critical corrections made to the ticket deduplication strategy and subcontractor assignment model based on real-world business requirements.

---

## 1. Deduplication Strategy - CORRECTED ✅

### Previous (WRONG) Design
```python
# dedup_key = MD5(source + source_id + ticket_type)
# Cleared on completion → allows re-creation
```

**Problems:**
- Allowed same sales order to create multiple tickets
- Dedup key was cleared on completion/cancellation
- Not real-world compliant

### Current (CORRECT) Design - WITH REACTIVATION SUPPORT
```python
# dedup_key = MD5(source + source_id + ticket_type + created_at)
# PERMANENT - never cleared
# Tracks creation instance, not lifecycle state
```

**Business Rules:**
1. **One source = One ticket per creation instance**
   - A sales order creates ONE ticket when first promoted
   - Dedup_key is permanent (tracks creation timestamp)
   - Ticket can be reactivated without creating new row

2. **Reactivation (Same Service):**
   - Customer cancels → Ticket status = cancelled (dedup_key remains)
   - Customer wants service again → REACTIVATE existing ticket
   - Update: status cancelled→open, new dates, add reactivation note
   - **Benefits**: Space efficient, payment history preserved, chronic canceller detection

3. **New Service (Different Package):**
   - Customer had 10Mbps (cancelled) → Now wants 50Mbps
   - Create NEW sales order → Creates NEW ticket
   - New dedup_key (different creation timestamp)
   - History shows multiple tickets for customer

4. **Why This Design?**
   - **Space efficient**: No duplicate rows for reactivations
   - **Payment tracking**: If customer paid before cancelling, we know
   - **Customer behavior**: Can identify chronic cancellers
   - **Audit trail**: Complete history per customer
   - **Flexibility**: New package = new ticket, same service = reactivate

### Code Changes

**Model (`src/app/models/ticket.py`):**
```python
# Before
dedup_key = Column(Text, nullable=True, unique=False)

def generate_dedup_key(source, source_id, ticket_type):
    dedup_string = f"{source}::{source_id}::{ticket_type}"
    return hashlib.md5(dedup_string.encode()).hexdigest()

def mark_as_completed(self):
    self.dedup_key = None  # WRONG - cleared

# After
dedup_key = Column(Text, nullable=True, unique=True)

def generate_dedup_key(source, source_id, ticket_type, created_at):
    timestamp = created_at.isoformat()
    dedup_string = f"{source}::{source_id}::{ticket_type}::{timestamp}"
    return hashlib.md5(dedup_string.encode()).hexdigest()

def mark_as_completed(self):
    # dedup_key NEVER cleared - permanent
```

**Service (`src/app/services/ticket_service.py`):**
```python
# Check for existing ticket (one source = one ticket)
existing_ticket = db.query(Ticket).filter(
    Ticket.source == TicketSource.SALES_ORDER.value,
    Ticket.source_id == sales_order.id,
    Ticket.ticket_type == TicketType.INSTALLATION.value,
    Ticket.deleted_at.is_(None)
).first()

if existing_ticket:
    raise HTTPException(
        status_code=409,
        detail="Ticket already exists. One sales order can only create one ticket."
    )

# Generate permanent dedup key
created_at = datetime.utcnow()
dedup_key = Ticket.generate_dedup_key(
    source=TicketSource.SALES_ORDER.value,
    source_id=sales_order.id,
    ticket_type=TicketType.INSTALLATION.value,
    created_at=created_at
)
```

---

## 2. Subcontractor Assignment - REMOVED ✅

### Previous Design
- `project_subcontractor_id` column in tickets table
- Direct link to project_subcontractors

### Current (CORRECT) Design
- **REMOVED** `project_subcontractor_id` from tickets
- Subcontractor relationship via: `ticket_assignments → users → project_team → project_subcontractors`

**Why?**
- Tickets use assignments for field agent allocation
- Assignments link to users
- Users have subcontractor relationship via project_team
- No need for direct subcontractor link on ticket

### Migration
```sql
-- File: supabase/migrations/20241116000001_remove_subcontractor_from_tickets.sql

ALTER TABLE tickets 
DROP CONSTRAINT IF EXISTS tickets_project_subcontractor_id_fkey;

DROP INDEX IF EXISTS idx_tickets_subcontractor;

ALTER TABLE tickets 
DROP COLUMN IF EXISTS project_subcontractor_id;
```

### Model Changes
```python
# Before
project_id = Column(UUID, ForeignKey("projects.id"), nullable=False)
project_subcontractor_id = Column(UUID, ForeignKey("project_subcontractors.id"), nullable=True)

# After
project_id = Column(UUID, ForeignKey("projects.id"), nullable=False)
# Note: Subcontractor via ticket_assignments → users → project_team
```

---

## 3. Task → Ticket Creation - IMPLEMENTED ✅

### Added Support for Infrastructure Tasks

**Service Method:**
```python
@staticmethod
def create_ticket_from_task(
    db: Session,
    data: TicketCreateFromTask,
    current_user: User
) -> Ticket:
    """
    Create infrastructure ticket from task.
    
    Business Rule: One task = One ticket ever
    """
```

**API Endpoint:**
```
POST /api/v1/tickets/from-task
```

**Example:**
```json
{
  "task_id": "uuid-here",
  "priority": "normal",
  "scheduled_date": "2024-03-20",
  "scheduled_time_slot": "morning",
  "work_description": "Install fiber cable from pole A to B",
  "notes": "Requires cherry picker"
}
```

---

## 4. Real-World Business Flow

### Sales Order Scenario (With Reactivation)
```
SCENARIO A: SAME SERVICE REACTIVATION (Space Efficient)
1. Customer orders 10Mbps WiFi
   → Sales order created (status=pending)

2. Sales order promoted to ticket
   → Ticket created (dedup_key = MD5(...timestamp1...))
   → Sales order status = processed

3. Customer cancels before installation
   → Ticket status = cancelled
   → Dedup key REMAINS (permanent)
   → Payment record preserved (if they paid)

4. Customer wants WiFi again (SAME 10Mbps)
   → Find existing sales order & cancelled ticket
   → REACTIVATE ticket: status cancelled→open
   → Update dates, add reactivation note
   → REUSE same dedup_key
   → Space efficient! No new row created

5. If customer cancels AGAIN
   → Ticket status = cancelled again
   → System knows: This customer is a chronic canceller!

SCENARIO B: DIFFERENT SERVICE (New Ticket)
1. Customer had 10Mbps (cancelled)
2. Customer now wants 50Mbps (different package)
   → Create NEW sales order (different package)
   → Creates NEW ticket (dedup_key = MD5(...timestamp2...))
3. History shows:
   - Sales Order 1: 10Mbps (cancelled)
   - Sales Order 2: 50Mbps (active)
   - Customer has 2 tickets in system
```

### Task Scenario
```
1. Infrastructure project: Install fiber backbone
   → Task created: "Install cable segment A-B"

2. Task promoted to ticket
   → Ticket created (dedup_key = MD5(...timestamp...))
   → Assigned to field agent

3. Work completed
   → Ticket status = completed
   → Task status = completed
   → Dedup key REMAINS

4. Cable damaged, needs replacement
   → NEW task created: "Replace cable segment A-B"
   → NEW ticket created (different dedup_key)
```

---

## 5. Database Schema Impact

### Unique Constraint
```sql
-- Previous (WRONG)
CREATE UNIQUE INDEX idx_tickets_source_unique 
ON tickets (source, source_id, ticket_type)
WHERE deleted_at IS NULL AND source_id IS NOT NULL;

-- This allowed re-creation after completion
```

**Current (CORRECT):**
- Dedup_key column has `unique=True` in model
- Timestamp in dedup_key ensures permanent uniqueness
- One source can only create one ticket ever

---

## 6. Testing Scenarios

### Scenario 1: Duplicate Prevention
```python
# Create ticket from sales order
ticket1 = create_ticket_from_sales_order(sales_order_id="uuid1")
# ✅ Success

# Try to create again
ticket2 = create_ticket_from_sales_order(sales_order_id="uuid1")
# ❌ 409 Conflict: "Ticket already exists"
```

### Scenario 2: Reactivation (Space Efficient)
```python
# Complete/cancel ticket
ticket.mark_as_cancelled(reason="Customer changed mind")
# dedup_key remains (permanent)

# Customer wants service again
ticket2 = create_ticket_from_sales_order(sales_order_id="uuid1")
# ✅ Success - REACTIVATES existing ticket
# - Status: cancelled → open
# - Updates dates and notes
# - Reuses same dedup_key
# - No new row created (space efficient!)
# - Payment history preserved
```

### Scenario 3: New Source = New Ticket
```python
# Complete first ticket
ticket1.mark_as_completed()

# Create NEW sales order (new business transaction)
sales_order2 = create_sales_order(customer_id=same_customer)

# Create ticket from new sales order
ticket2 = create_ticket_from_sales_order(sales_order_id=sales_order2.id)
# ✅ Success (different source_id → different dedup_key)
```

---

## 7. Migration Checklist

- [x] Update Ticket model - permanent dedup_key with timestamp
- [x] Remove project_subcontractor_id from Ticket model
- [x] Update generate_dedup_key() to include timestamp
- [x] Remove dedup_key clearing in mark_as_completed()
- [x] Remove dedup_key clearing in mark_as_cancelled()
- [x] Update ticket service to check source existence (not just dedup_key)
- [x] Add create_ticket_from_task() method
- [x] Add POST /tickets/from-task endpoint
- [x] Create Supabase migration to remove subcontractor column
- [x] Update documentation

---

## 8. Summary

### Key Changes
1. **Dedup key is now PERMANENT** (includes timestamp, never cleared)
2. **One source = One ticket FOREVER** (real-world compliant)
3. **Removed subcontractor_id** (use assignments instead)
4. **Added task → ticket creation** (infrastructure support)

### Business Impact
- ✅ Prevents accidental duplicate tickets
- ✅ Clear audit trail (one source = one ticket)
- ✅ Forces proper business flow (new order = new ticket)
- ✅ Simplified assignment model (via project_team)
- ✅ Infrastructure project support (tasks → tickets)

### Files Changed
- `src/app/models/ticket.py` - Model updates
- `src/app/services/ticket_service.py` - Service logic
- `src/app/api/v1/tickets.py` - API endpoint
- `supabase/migrations/20241116000001_remove_subcontractor_from_tickets.sql` - Migration
- `docs/agent/TICKET_MANAGEMENT_CHECKLIST.md` - Documentation