Skip to content

:hedgehog: PostHog is the leading platform for building self-driving products. Our developer tools – AI observability, analytics, session replay, flags, experiments, error tracking, logs, and more – capture all the context agents need to diagnose problems, uncover opportunities, and ship fixes. Steer it all from Slack, web, desktop, or the MCP.

15.9 KB
Raw Blame
from datetime import timedelta
from typing import TYPE_CHECKING, Any

from django.db import models, transaction

from posthog.models.tagged_items_relation import Taggable
from posthog.models.utils import UUIDTModel

from .constants import Channel, ChannelDetail, Priority, Status
from .purged_ticket_thread import PurgedTicketThread, ticket_thread_key
from .team_conversations_ticket_config import TeamConversationsTicketConfig

# Soft-deleted tickets stay this long so a mistaken delete can still be recovered
# operationally, and so the hard purge finishes inside a one-month erasure window.
TICKET_HARD_DELETE_AFTER = timedelta(days=14)

# Two-arg lock namespace for ticket_number allocation. Keep this value stable:
# every allocator (create_with_number and bulk import) must use the same pair.
_TICKET_NUMBER_LOCK_NAMESPACE = 0x0C0F_5E71

if TYPE_CHECKING:
    from posthog.models import Person


class TicketManager(models.Manager):
    """Every ticket, including soft-deleted rows.

    ``objects`` hides those rows. This manager stays unfiltered so number allocation
    and inbound thread-key checks can still see them.
    """

    def lock_ticket_number_allocation(self, team_id: int) -> None:
        """Serialize ticket_number assignment for this team.

        Uses a transaction-scoped advisory lock instead of locking the Team row,
        so unrelated writers of Team children are not blocked. Callers must be
        inside ``transaction.atomic()``.
        """
        db_connection = transaction.get_connection(self.db)
        if not db_connection.in_atomic_block:
            raise RuntimeError("lock_ticket_number_allocation requires an open transaction")
        with db_connection.cursor() as cursor:
            cursor.execute(
                "SELECT pg_advisory_xact_lock(%s, %s)",
                [_TICKET_NUMBER_LOCK_NAMESPACE, team_id],
            )

    def highest_used_ticket_number(self, team_id: int) -> int:
        """Highest number any ticket of this team holds or held. Call under the allocation lock.

        Soft-deleted rows count, because their number is still in unique_ticket_number_per_team.
        Purged rows count through the retired number, so an old link never opens a new ticket.
        """
        max_num = (
            Ticket.all_objects.filter(team_id=team_id).aggregate(models.Max("ticket_number"))["ticket_number__max"] or 0
        )
        retired = (
            TeamConversationsTicketConfig.objects.filter(team_id=team_id)
            .values_list("retired_ticket_number", flat=True)
            .first()
            or 0
        )
        return max(max_num, retired)

    def create_with_number(self, **kwargs):
        """Create a ticket with the next ticket_number for its team."""
        team = kwargs.get("team")
        if not team:
            raise ValueError("team is required")

        allocation = Ticket.all_objects
        with transaction.atomic(using=allocation.db):
            allocation.lock_ticket_number_allocation(team.id)
            kwargs["ticket_number"] = allocation.highest_used_ticket_number(team.id) + 1
            return allocation.create(**kwargs)


class LiveTicketManager(TicketManager):
    """Tickets a person can still open. Soft-deleted rows stay on ``all_objects``."""

    def get_queryset(self) -> models.QuerySet:
        return super().get_queryset().filter(deleted_at__isnull=True)


def deleted_ticket_holds_thread(*, team_id: int, **lookup: Any) -> bool:
    """Whether a soft-deleted or purged ticket still owns this inbound thread key.

    Callers that create a ticket when no live row matches must check this first.
    The unique keys stay on the deleted row until the sweeper, so a new ticket
    would either violate that constraint or open a second copy of a deleted thread.
    After the purge, a PurgedTicketThread row keeps the thread closed.
    """
    if not lookup:
        return False
    if Ticket.all_objects.filter(team_id=team_id, deleted_at__isnull=False, **lookup).exists():
        return True
    return PurgedTicketThread.objects.for_team(team_id).filter(thread_key=ticket_thread_key(**lookup)).exists()


class Ticket(Taggable, UUIDTModel):
    # Live rows are the default so a forgotten query cannot return a deleted ticket.
    # ``all_objects`` is the unfiltered manager historical migrations already import.
    objects = LiveTicketManager()
    all_objects = TicketManager()

    # Dynamic attribute set by TicketViewSet._attach_persons_to_tickets for serialization
    person: "Person | None"

    team = models.ForeignKey("posthog.Team", on_delete=models.CASCADE, related_name="+")
    ticket_number = models.PositiveIntegerField()
    channel_source = models.CharField(max_length=20, choices=Channel, default=Channel.WIDGET)
    channel_detail = models.CharField(max_length=30, choices=ChannelDetail, null=True, blank=True)
    widget_session_id = models.CharField(max_length=64, db_index=True)  # Random UUID for access control
    distinct_id = models.CharField(max_length=400)  # PostHog distinct_id for Person linking only
    status = models.CharField(max_length=20, choices=Status, default=Status.NEW)
    priority = models.CharField(max_length=20, choices=Priority, null=True, blank=True)
    anonymous_traits = models.JSONField(default=dict, blank=True)
    # Trust signal (tri-state):
    #   True  — the claimed identity was attested by the server (widget HMAC,
    #           SPF-authenticated email, or a signature-validated platform webhook).
    #   False — assessed but not attested (anonymous claim we couldn't verify).
    #   None  — unknown; we never assessed it (e.g. predates this signal and the
    #           channel doesn't structurally guarantee verification).
    identity_verified = models.BooleanField(null=True)
    ai_resolved = models.BooleanField(default=False)
    escalation_reason = models.TextField(null=True, blank=True)
    ai_triage = models.JSONField(default=dict, blank=True)

    # Unread message counters
    unread_customer_count = models.IntegerField(default=0)  # Messages customer hasn't seen (from team/AI)
    unread_team_count = models.IntegerField(default=0)  # Messages team hasn't seen (from customer)

    # Denormalized message stats (updated via signal on Comment save)
    message_count = models.IntegerField(default=0)
    last_message_at = models.DateTimeField(null=True, blank=True)
    last_message_text = models.CharField(max_length=500, null=True, blank=True)  # Truncated preview

    # Slack channel fields (only set for channel_source="slack")
    slack_channel_id = models.CharField(max_length=64, null=True, blank=True)  # Slack channel ID
    slack_thread_ts = models.CharField(max_length=64, null=True, blank=True)  # Slack thread timestamp (thread ID)
    slack_team_id = models.CharField(max_length=64, null=True, blank=True)  # Slack workspace/team ID

    # Teams channel fields (only set for channel_source="teams")
    teams_channel_id = models.CharField(max_length=128, null=True, blank=True)
    teams_conversation_id = models.CharField(max_length=256, null=True, blank=True)  # Reply chain / thread ID
    teams_service_url = models.URLField(max_length=512, null=True, blank=True)  # Bot Connector endpoint for replies
    teams_tenant_id = models.CharField(max_length=64, null=True, blank=True)
    # Shared-channel thread reply poller watermark (Graph createdDateTime of last ingested reply).
    teams_thread_replies_synced_at = models.DateTimeField(null=True, blank=True)

    # GitHub channel fields (only set for channel_source="github")
    github_repo = models.CharField(max_length=256, null=True, blank=True)  # owner/repo
    github_issue_number = models.PositiveIntegerField(null=True, blank=True)

    # Email channel fields (only set for channel_source="email")
    email_config = models.ForeignKey(
        "conversations.EmailChannel",
        null=True,
        blank=True,
        on_delete=models.SET_NULL,
        related_name="tickets",
    )
    email_subject = models.CharField(max_length=500, null=True, blank=True)
    email_from = models.EmailField(null=True, blank=True)
    cc_participants = models.JSONField(default=list, blank=True)

    # Session context (captured when ticket is created)
    session_id = models.CharField(max_length=64, null=True, blank=True)  # PostHog session ID
    session_context = models.JSONField(default=dict, blank=True)  # session_replay_url, current_url, etc.

    # SLA deadline — set via workflows, null means no SLA
    sla_due_at = models.DateTimeField(null=True, blank=True)

    # Snooze — when set, ticket is "on hold" until this time, then auto-reopened by wake task
    snoozed_until = models.DateTimeField(null=True, blank=True)

    # Customer's PostHog org group key, resolved once at creation or on a later message
    # (local org pk, cross-region analytics key, or the person's organization_id property).
    organization_id = models.CharField(max_length=400, null=True, blank=True)
    # How organization_id was resolved: "person" (the requester's identity) or
    # "slack_channel_account" (inferred from the customer analytics account linked to the Slack channel).
    organization_id_source = models.CharField(max_length=32, null=True, blank=True)

    # Zendesk import dedup — set when a ticket is imported from Zendesk Support.
    # No standalone index: the partial unique constraint below covers the dedup lookup
    # (team + zendesk_ticket_id), mirroring the GitHub issue-number pattern.
    zendesk_ticket_id = models.BigIntegerField(null=True, blank=True)

    created_at = models.DateTimeField(auto_now_add=True)
    updated_at = models.DateTimeField(auto_now=True)
    # Set together when a manager deletes the ticket. The sweeper hard-deletes
    # once deleted_at is older than TICKET_HARD_DELETE_AFTER.
    deleted_at = models.DateTimeField(null=True, blank=True)
    deleted_by = models.ForeignKey(
        "posthog.User",
        null=True,
        blank=True,
        on_delete=models.SET_NULL,
        db_constraint=False,
        related_name="+",
    )

    class Meta:
        db_table = "posthog_conversations_ticket"
        indexes = [
            models.Index(fields=["team", "widget_session_id"]),  # Access control queries
            models.Index(fields=["team", "distinct_id"]),  # Person linking queries
            models.Index(fields=["team", "status"]),
            models.Index(fields=["team", "-ticket_number"], name="posthog_con_team_id_ticket_idx"),  # MAX() lookups
            models.Index(fields=["team", "session_id"]),  # Session context queries
            # Slack thread lookup: find ticket by (team, slack_channel_id, slack_thread_ts)
            models.Index(
                fields=["team", "slack_channel_id", "slack_thread_ts"],
                name="posthog_con_slack_thread_idx",
            ),
            # Teams thread lookup: find ticket by (team, teams_channel_id, teams_conversation_id)
            models.Index(
                fields=["team", "teams_channel_id", "teams_conversation_id"],
                name="posthog_con_teams_thread_idx",
            ),
            # Dashboard ordering optimization
            models.Index(fields=["team", "-updated_at"], name="posthog_con_team_updated_idx"),
            # Dashboard filtered + ordered queries
            models.Index(fields=["team", "status", "-updated_at"], name="posthog_con_status_upd_idx"),
            # SLA sort + filter. The dashboard sorts by "sla_due_at <dir> NULLS LAST, ticket_number
            # DESC"; one expression index per direction makes each page a top-N index scan instead
            # of a full sort of the mostly-NULL table (a plain ascending index can't serve DESC
            # NULLS LAST, and lacks the ticket_number tiebreaker). The leading (team_id, sla_due_at)
            # prefix also serves the SLA state filter, so these supersede a plain (team, sla_due_at).
            models.Index(
                models.F("team_id"),
                models.F("sla_due_at").asc(nulls_last=True),
                models.F("ticket_number").desc(),
                name="posthog_con_sla_asc_idx",
            ),
            models.Index(
                models.F("team_id"),
                models.F("sla_due_at").desc(nulls_last=True),
                models.F("ticket_number").desc(),
                name="posthog_con_sla_desc_idx",
            ),
            # Snooze sort + filter: same asc/desc expression-index pair. The leading prefix serves
            # the snoozed isnull filter, so these supersede a plain (team, snoozed_until) index.
            models.Index(
                models.F("team_id"),
                models.F("snoozed_until").asc(nulls_last=True),
                models.F("ticket_number").desc(),
                name="posthog_con_snooze_asc_idx",
            ),
            models.Index(
                models.F("team_id"),
                models.F("snoozed_until").desc(nulls_last=True),
                models.F("ticket_number").desc(),
                name="posthog_con_snooze_desc_idx",
            ),
            # Snooze: wake task (cross-team, only non-null rows)
            models.Index(
                fields=["snoozed_until"],
                name="posthog_con_snooze_wake_idx",
                condition=models.Q(snoozed_until__isnull=False),
            ),
            models.Index(fields=["organization_id"], name="posthog_org_id_idx"),
            models.Index(
                fields=["organization_id", "slack_channel_id"],
                name="posthog_org_slack_ch_idx",
                condition=models.Q(channel_source="slack"),
            ),
            # Compose dedupe fallback: find recent outbound email tickets by (team, email channel,
            # sender) within a short window, newest first. Runs on every new compose, so without
            # this it scans the channel's whole ticket history. Partial to email keeps it small.
            models.Index(
                models.F("team_id"),
                models.F("email_config_id"),
                models.F("email_from"),
                models.F("created_at").desc(),
                name="posthog_con_compose_dedupe_idx",
                condition=models.Q(channel_source="email"),
            ),
            # Sweeper scans only soft-deleted rows, which are rare compared to the table.
            models.Index(
                fields=["deleted_at"],
                name="posthog_con_ticket_deleted_idx",
                condition=models.Q(deleted_at__isnull=False),
            ),
        ]
        constraints = [
            models.UniqueConstraint(fields=["team", "ticket_number"], name="unique_ticket_number_per_team"),
            models.UniqueConstraint(
                fields=["team", "github_repo", "github_issue_number"],
                condition=models.Q(github_repo__isnull=False, github_issue_number__isnull=False),
                name="posthog_con_github_issue_uniq",
            ),
            models.UniqueConstraint(
                fields=["team", "zendesk_ticket_id"],
                condition=models.Q(zendesk_ticket_id__isnull=False),
                name="posthog_con_zendesk_ticket_uniq",
            ),
        ]

    def __str__(self):
        return f"Ticket {self.id} - {self.widget_session_id[:8]}..."

    def inbound_thread_keys(self) -> list[str]:
        """Keys of the external threads that route inbound messages to this ticket."""
        lookups: list[dict[str, Any]] = []
        if self.slack_channel_id and self.slack_thread_ts:
            lookups.append({"slack_channel_id": self.slack_channel_id, "slack_thread_ts": self.slack_thread_ts})
        if self.teams_channel_id and self.teams_conversation_id:
            lookups.append(
                {"teams_channel_id": self.teams_channel_id, "teams_conversation_id": self.teams_conversation_id}
            )
        if self.github_repo and self.github_issue_number is not None:
            lookups.append({"github_repo": self.github_repo, "github_issue_number": self.github_issue_number})
        if self.zendesk_ticket_id is not None:
            lookups.append({"zendesk_ticket_id": self.zendesk_ticket_id})
        return [ticket_thread_key(**lookup) for lookup in lookups]