docs/agent/implementation-notes/TICKET_REACTIVATION_STRATEGY.md

Ticket Reactivation Strategy - Final Design

Overview

The ticket system now supports space-efficient reactivation of cancelled/completed tickets, while still maintaining unique deduplication keys for audit trails.

---

Business Problem Solved

Scenario: Customer Service Reactivation

Customer orders WiFi → Cancels → Wants WiFi again

Bad Design (Create New Ticket):

• ❌ Duplicate rows in database
• ❌ Lose payment history if customer paid before cancelling
• ❌ Can't track chronic cancellers
• ❌ Wastes database space

Good Design (Reactivate Ticket):

• ✅ Reuse existing ticket row (space efficient)
• ✅ Preserve payment history
• ✅ Track customer behavior (chronic cancellers)
• ✅ Complete audit trail

---

Implementation Logic

Create Ticket Flow (With Reactivation)

def create_ticket_from_sales_order(sales_order_id):
    """
    Creates new ticket OR reactivates cancelled/completed ticket.
    """
    
    # 1. Check for ACTIVE ticket (prevent duplicates)
    active_ticket = find_active_ticket(sales_order_id)
    if active_ticket:
        raise HTTPException(409, "Active ticket already exists")
    
    # 2. Check for CANCELLED/COMPLETED ticket (reactivation opportunity)
    cancelled_ticket = find_cancelled_ticket(sales_order_id)
    if cancelled_ticket:
        # REACTIVATE: Update existing ticket
        cancelled_ticket.status = "open"
        cancelled_ticket.scheduled_date = new_date
        cancelled_ticket.notes = f"[REACTIVATED {now}] {notes}\n{old_notes}"
        # dedup_key remains unchanged (permanent)
        return cancelled_ticket  # ✅ Reused row
    
    # 3. No existing ticket - Create new one
    dedup_key = MD5(source + source_id + ticket_type + created_at)
    new_ticket = Ticket(
        source_id=sales_order_id,
        status="open",
        dedup_key=dedup_key  # Permanent unique key
    )
    return new_ticket  # ✅ New row

Key Points

1. Active Ticket Check: Prevents duplicate active tickets
2. Cancelled Ticket Check: Enables reactivation (space efficient)
3. Dedup Key: Permanent (tracks creation instance, not lifecycle)
4. Reactivation: Updates status, dates, adds note, reuses row

---

Business Rules

Rule 1: One Source = One Active Ticket

• Sales order can have ONE active ticket at a time
• Prevents duplicate work orders
• Check: status NOT IN ('completed', 'cancelled')

Rule 2: Reactivation for Same Service

Customer cancels 10Mbps WiFi → Wants 10Mbps WiFi again
→ REACTIVATE cancelled ticket (reuse row)

Implementation:

• Find cancelled ticket for same sales order
• Update status: cancelled → open
• Update dates and schedule
• Add reactivation note with timestamp
• Dedup key remains unchanged

Rule 3: New Ticket for Different Service

Customer had 10Mbps (cancelled) → Now wants 50Mbps
→ Create NEW sales order → NEW ticket (new row)

Implementation:

• Create new sales order (different package)
• Create new ticket with new dedup_key
• History shows multiple tickets for customer

---

Database Space Efficiency

Scenario: Chronic Canceller

Without Reactivation (Wasteful):

-- Customer orders/cancels 5 times
INSERT INTO tickets (...) -- Ticket 1
INSERT INTO tickets (...) -- Ticket 2
INSERT INTO tickets (...) -- Ticket 3
INSERT INTO tickets (...) -- Ticket 4
INSERT INTO tickets (...) -- Ticket 5
-- Result: 5 rows for same sales order

With Reactivation (Efficient):

-- Customer orders/cancels 5 times
INSERT INTO tickets (...) -- Ticket 1 created
UPDATE tickets SET status='cancelled' -- Cancelled
UPDATE tickets SET status='open' -- Reactivated
UPDATE tickets SET status='cancelled' -- Cancelled again
UPDATE tickets SET status='open' -- Reactivated again
-- Result: 1 row for sales order
-- Notes field tracks all state changes

Savings:

• 1 row instead of 5
• 80% space reduction
• Payment history preserved
• Chronic canceller easily identified

---

Dedup Key Strategy

Format

dedup_key = MD5(source + '::' + source_id + '::' + ticket_type + '::' + created_at)

Properties

• Permanent: Never cleared (even on completion/cancellation)
• Unique: Timestamp ensures uniqueness
• Tracks Creation: Identifies when ticket was first created
• Not Lifecycle: Doesn't change with status updates

Why Timestamp?

1. Prevents Collisions: If same source tries multiple times
2. Audit Trail: Know when ticket was created
3. Allows Multiple Tickets: New creation = new timestamp = new dedup_key
4. Reactivation Support: Same dedup_key when reactivating

---

API Behavior

POST /api/v1/tickets/from-sales-order

Request:

{
  "sales_order_id": "uuid-123",
  "priority": "normal",
  "scheduled_date": "2024-03-20",
  "notes": "Customer wants morning installation"
}

Response (New Ticket):

{
  "id": "ticket-uuid",
  "source": "sales_order",
  "source_id": "uuid-123",
  "status": "open",
  "dedup_key": "abc123...",
  "created_at": "2024-03-15T10:00:00Z"
}

Response (Reactivated Ticket):

{
  "id": "ticket-uuid",
  "source": "sales_order",
  "source_id": "uuid-123",
  "status": "open",  // Changed from 'cancelled'
  "dedup_key": "abc123...",  // Same as before
  "created_at": "2024-03-10T08:00:00Z",  // Original creation date
  "updated_at": "2024-03-15T10:00:00Z",  // Now
  "notes": "[REACTIVATED 2024-03-15T10:00:00Z] Customer wants morning installation\n[CANCELLED 2024-03-12] Customer changed mind\n..."
}

---

Customer Behavior Tracking

Identifying Chronic Cancellers

Query:

-- Find tickets that were reactivated multiple times
SELECT 
    ticket_name,
    status,
    notes,
    (LENGTH(notes) - LENGTH(REPLACE(notes, '[REACTIVATED', ''))) / LENGTH('[REACTIVATED') as reactivation_count
FROM tickets
WHERE notes LIKE '%[REACTIVATED%'
ORDER BY reactivation_count DESC;

Business Value:

• Identify customers who frequently cancel
• Adjust payment terms (require upfront payment)
• Risk assessment for resource allocation
• Customer support insights

---

Payment History Preservation

Scenario: Customer Paid Before Cancelling

Without Reactivation:

Ticket 1 (cancelled) → Has payment record
Ticket 2 (new) → No link to previous payment
→ Lost payment history! ❌

With Reactivation:

Ticket 1 (cancelled) → Has payment record
Ticket 1 (reactivated) → Still has payment record
→ Payment history preserved! ✅

Additional Metadata Example:

{
  "payments": [
    {
      "amount": 5000,
      "date": "2024-03-01",
      "status": "refunded",
      "reason": "Customer cancelled before installation"
    }
  ],
  "cancellations": [
    {
      "date": "2024-03-05",
      "reason": "Customer changed mind"
    }
  ],
  "reactivations": [
    {
      "date": "2024-03-15",
      "reason": "Customer wants service again"
    }
  ]
}

---

Summary

Key Features

1. ✅ Space Efficient: Reactivates existing tickets instead of creating duplicates
2. ✅ Payment History: Preserves financial records when reactivating
3. ✅ Customer Behavior: Tracks chronic cancellers via reactivation notes
4. ✅ Audit Trail: Dedup key is permanent (tracks creation instance)
5. ✅ Flexibility: New service = new ticket, same service = reactivate

Database Impact

• Reduces rows: 1 ticket per sales order (not 1 per order attempt)
• Preserves data: Payment history, notes, metadata
• Clear history: Notes field shows all state changes with timestamps

Business Value

• Cost Savings: Less database storage
• Better Analytics: True customer behavior tracking
• Financial Accuracy: Payment history never lost
• Operational Efficiency: One ticket to manage per order

---

Migration Notes

No database migration needed!

The existing schema already supports this:

• dedup_key is already unique and permanent
• status column supports state transitions
• notes field stores reactivation history
• Logic change only (service layer, not schema)

Code Changes:

• ✅ Service: Check for cancelled tickets before creating new
• ✅ Service: Reactivate logic (update status, dates, notes)
• ✅ API: Document reactivation behavior
• ✅ Docs: Update business rules