Build a Support Ticket System in Lovable

Audit requester isolation to confirm customers cannot see each other's tickets.

Open two browser windows in incognito mode. Create Customer A account and submit a ticket. Create Customer B account and submit a different ticket.

Sign in as Customer A: the /support page should show exactly 1 ticket (their own). Navigate to /support/${Customer B's ticket number} — should return a not-found or access-denied error (the ticket query returns 0 rows via RLS).

If Customer A can see Customer B's ticket, fix the tickets SELECT policy:
DROP POLICY IF EXISTS tickets_requester_or_agent ON tickets;
CREATE POLICY tickets_requester_or_agent ON tickets
  FOR SELECT TO authenticated
  USING (requester_id = auth.uid() OR is_agent());

Also confirm the ticket_messages SELECT policy filters internal notes for customers:
DROP POLICY IF EXISTS messages_requester_or_agent ON ticket_messages;
CREATE POLICY messages_requester_or_agent ON ticket_messages
  FOR SELECT USING (
    (EXISTS (SELECT 1 FROM tickets t WHERE t.id = ticket_id AND t.requester_id = auth.uid()) AND is_internal_note = false)
    OR is_agent()
  );

Run the two-account test again after every policy change.

Wire Resend email notifications for ticket lifecycle events.

Add a Postgres trigger on tickets INSERT that fires notify-on-update with event_type='new_ticket'. Add a trigger on ticket_messages INSERT that fires notify-on-update with event_type='new_reply'. Add a trigger on tickets UPDATE (when status changes) that fires notify-on-update with event_type='status_changed'.

Use pg_net.http_post to call the Edge Function from the trigger. Pass ticket_id and sender_id in the JSON body.

In notify-on-update/index.ts:
- Fetch the ticket with requester's email and assignee's email
- On new_ticket: send Resend email to assignee (or a support alias if unassigned) with ticket subject + link to /agent/tickets/${ticket_number}; send ack to requester with 'We received your request [#${ticket_number}]' and a link to /support/${ticket_number}
- On new_reply (from agent): send to requester with agent's message preview
- On status_changed to 'resolved': send to requester with resolution message

Add RESEND_API_KEY to Cloud tab → Secrets before testing.

Wire inbound email so customers can reply by replying to the notification email.

In Postmark Dashboard: Servers → your server → Inbound → set the Inbound Hook URL to https://[your-deployed-url]/api/inbound-email (or the Edge Function URL). Enable the inbound domain (e.g., reply.yourdomain.com).

Update notify-on-update to prefix the email subject with [#${ticket_number}]: e.g., 'Re: Login issue [#1042]'. This is how inbound-email identifies which ticket a reply belongs to.

In inbound-email/index.ts, complete the Postmark webhook handler:
- Parse the JSON body: From, Subject, TextBody (strip signatures), Attachments
- Try to match ticket: const match = subject.match(/\[#(\d+)\]/); if match, look up ticket by ticket_number
- If found: INSERT ticket_messages with sender_id looked up by From email, body=TextBody (truncated to 5000 chars), source='email'
- If no match: INSERT new ticket with subject=Subject, requester_id looked up or created by From email, first ticket_messages row with body=TextBody
- Set first_response_at on tickets if the message sender is_agent() and first_response_at IS NULL
- Return 200 (Postmark retries on anything else)

Add SLA breach detection and proactive alerts for agents.

Create a Postgres view v_tickets_with_sla that joins tickets with sla_policies on priority and computes:
- minutes_to_first_response = EXTRACT(EPOCH FROM (first_response_at - created_at)) / 60 (null if not yet responded)
- first_response_due_at = created_at + first_response_minutes * interval '1 minute'
- first_response_breached = first_response_at IS NULL AND now() > first_response_due_at
- resolution_due_at = created_at + resolution_minutes * interval '1 minute'
- resolution_breached = status NOT IN ('resolved','closed') AND now() > resolution_due_at

Update SLAPill to read from this view — show 'Respond by HH:MM' in blue, 'Overdue by X min' in rose.

Create a check-sla-breach Edge Function. Schedule it via pg_cron every 15 minutes:
SELECT cron.schedule('sla_breach_check', '*/15 * * * *', $$SELECT net.http_post(url:='YOUR_FUNCTION_URL', body:='{}')$$);

The function queries v_tickets_with_sla WHERE first_response_breached=true AND status='open' and sends a Resend digest to the assignee (or unassigned-pool alias) listing each overdue ticket.

Add a canned-response library for common agent replies.

Create a canned_responses table: id uuid PK, title text not null, body text not null (supports {{ticket.subject}} and {{requester.name}} placeholders), created_by uuid references auth.users.id, is_global bool default true, created_at timestamptz. RLS: agents read-all; creator updates own.

In MessageComposer, add a Combobox below the reply textarea labeled 'Insert canned response'. When an agent selects an item, insert the canned response body into the textarea and replace {{ticket.subject}} with the current ticket's subject and {{requester.name}} with the requester's full_name from profiles.

Add a canned_responses management page at /agent/settings/canned-responses — table list of all global responses + the agent's own, with Add / Edit / Delete. Global responses managed by admins only (has_role('admin') check on INSERT/UPDATE/DELETE).

Wire AI triage on new ticket creation.

Create an ai-triage Edge Function. Add ANTHROPIC_API_KEY to Cloud tab → Secrets.

The function accepts {ticket_id}. It fetches the ticket subject and first message body, then calls Claude Haiku 4.5 via the Anthropic API:
- Model: claude-haiku-4-5
- System prompt: 'You are a support ticket classifier for [your product]. Given a ticket, return JSON with: category (one of Billing/Technical/Account/General), priority (low/normal/high/urgent), and draft_reply (max 150 words, friendly and specific to the issue).'
- User message: Subject: {subject}\nBody: {body}

Parse the JSON response. Update the ticket's category_id and priority in the DB (status stays 'open'). Store draft_reply in a ticket_metadata jsonb column on tickets.

In AgentTicketView, show the AI-suggested priority and draft reply in a dismissible banner at the top: 'AI suggests: Priority=High, Category=Technical. Draft reply below.' Agent clicks 'Use this reply' to copy it into MessageComposer, or dismisses the banner.

Add a customer satisfaction survey sent on ticket resolution.

Create a csat_responses table: id uuid PK, ticket_id uuid references tickets(id), requester_id uuid references auth.users.id, rating int check in (1,2,3,4,5), comment text, submitted_at timestamptz. RLS: requester writes own (once); agents read all.

Add a Postgres trigger on tickets UPDATE when status changes to 'resolved' (NEW.status = 'resolved' AND OLD.status != 'resolved'). Trigger fires notify-on-update with event_type='csat_request'.

In notify-on-update, on csat_request: send Resend email with subject 'How did we do? [#${ticket_number}]' and a link to /support/${ticket_number}/feedback.

Create /support/:ticketNumber/feedback page (no auth required — use the ticket_number as a simple token). Show 5-star rating input + optional comment. Submit inserts csat_responses. After submit: 'Thank you for your feedback.'

In AgentReports, add a rolling 30-day CSAT score card: AVG(rating) across all resolved tickets in the period, with trend.