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

# 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

# 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):

# 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):

# 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

-- 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

# 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:

@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:

{
  "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

-- 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

# 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)

# 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

# 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

• Update Ticket model - permanent dedup_key with timestamp
• Remove project_subcontractor_id from Ticket model
• Update generate_dedup_key() to include timestamp
• Remove dedup_key clearing in mark_as_completed()
• Remove dedup_key clearing in mark_as_cancelled()
• Update ticket service to check source existence (not just dedup_key)
• Add create_ticket_from_task() method
• Add POST /tickets/from-task endpoint
• Create Supabase migration to remove subcontractor column
• 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