Support Tickets

We are building a support ticket system on top of this template. Create
the database schema and add the permissions we need.

Database (via Supabase MCP):

Create four tables:

1. `tickets` table — id (uuid, default gen_random_uuid(), primary key),
   team_id (uuid, references teams(id) on delete cascade, not null),
   ticket_number (serial, not null),
   subject (text, not null),
   description (text, not null),
   status (text, check status in ('open', 'in_progress', 'waiting', 'resolved',
   'closed'), default 'open', not null),
   priority (text, check priority in ('low', 'normal', 'high', 'urgent'),
   default 'normal', not null),
   category (text, check category in ('bug', 'feature_request', 'question',
   'billing', 'other'), default 'other', not null),
   assigned_to (uuid, references profiles(id) on delete set null, nullable),
   requester_email (text, nullable),
   requester_name (text, nullable),
   resolved_at (timestamptz, nullable),
   first_response_at (timestamptz, nullable),
   created_by (uuid, references profiles(id), not null),
   created_at (timestamptz, default now()),
   updated_at (timestamptz, default now()).
   Add a unique constraint on (team_id, ticket_number).

2. `ticket_replies` table — id (uuid, default gen_random_uuid(), primary key),
   ticket_id (uuid, references tickets(id) on delete cascade, not null),
   user_id (uuid, references profiles(id), not null),
   content (text, not null),
   is_internal (boolean, default false, not null),
   created_at (timestamptz, default now()).

3. `canned_responses` table — id (uuid, default gen_random_uuid(), primary key),
   team_id (uuid, references teams(id) on delete cascade, not null),
   title (text, not null),
   content (text, not null),
   category (text, nullable),
   created_by (uuid, references profiles(id), not null),
   created_at (timestamptz, default now()),
   updated_at (timestamptz, default now()).

4. `ticket_tags` table — id (uuid, default gen_random_uuid(), primary key),
   ticket_id (uuid, references tickets(id) on delete cascade, not null),
   tag (text, not null),
   created_at (timestamptz, default now()).
   Add a unique constraint on (ticket_id, tag).

Enable RLS on all four tables. Create policies that use the existing
`is_team_member()` function to scope access. For `tickets` and
`canned_responses`, the policy should check that the row's `team_id`
matches a team the user belongs to. For `ticket_replies` and `ticket_tags`,
join through the `tickets` table to check team membership.

Only create a SELECT policy for team members — follow the
`announcements_team_member_read` pattern in
`supabase/migrations/00001_initial_schema.sql`. Writes go through
service-role server routes, so no insert/update/delete RLS policies are
needed. Permission checks live in server routes via `authUser(event, "key")`.

After creating the tables, add these permissions to `shared/permissions.ts`:

```
"tickets.view": ["owner", "admin", "member"],
"tickets.create": ["owner", "admin", "member"],
"tickets.update": ["owner", "admin", "member"],
"tickets.delete": ["owner", "admin"],
"tickets.assign": ["owner", "admin"],
"canned_responses.view": ["owner", "admin", "member"],
"canned_responses.manage": ["owner", "admin"],
```

Also wire the new tables into the baked-in AI chat and activity log per
CLAUDE.md conventions:

- Add `COMMENT ON COLUMN` on every non-obvious column — one short technical
  sentence. Mention format or business rules the DB does not enforce.
- `select enable_activity_log('<table>');` for each mutation-bearing table.
- Grant chat read access on each team-scoped table:
  ```
  grant select on <table> to chat_reader;
  create policy "<table>_select_chat" on <table>
    for select to chat_reader using (team_id = current_chat_team());
  ```
  Skip tables without a `team_id` column (scope them through a parent).
- Register each table in `tablePermissions` in `shared/permissions.ts`
  using the permission keys above.
- Add filter entries in `app/components/activity/List.vue` (`tableItems`)
  for each new table.

Regenerate the TypeScript types via Supabase MCP and save them to
`shared/types/database.types.ts`.

Build the ticket management module — server routes and a full UI page.

Server routes (all use `authUser` with the appropriate permission):

- `GET /api/teams/[teamId]/tickets` — uses
  `authUser(event, "tickets.view")`. Returns all tickets for the team with
  the assignee name and reply count joined in. Order by created_at desc.
  Support optional query params: `?status=` to filter by status,
  `?priority=` to filter by priority, `?category=` to filter by category,
  `?assigned_to=` to filter by assignee UUID, `?q=` to search by subject
  or ticket_number (use ILIKE for subject).

- `POST /api/teams/[teamId]/tickets` — uses
  `authUser(event, "tickets.create")`. Reads { subject, description, status,
  priority, category, assigned_to, requester_email, requester_name, tags }
  from the body. Validates that subject and description are required. Sets
  team_id from the auth context and created_by from the authenticated user
  (user.sub). If tags are provided (string array), insert them into
  ticket_tags after creating the ticket.

- `PATCH /api/teams/[teamId]/tickets/[ticketId]` — uses
  `authUser(event, "tickets.update")`. Updates whichever fields are
  provided in the body. If status is being changed to "resolved",
  automatically set resolved_at to now(). If assigned_to is being changed,
  check that the caller has "tickets.assign" permission. Validates that
  the ticket belongs to the team.

- `DELETE /api/teams/[teamId]/tickets/[ticketId]` — uses
  `authUser(event, "tickets.delete")`. Validates that the ticket belongs to
  the team before deleting.

- `GET /api/teams/[teamId]/tickets/[ticketId]/replies` — uses
  `authUser(event, "tickets.view")`. Returns all replies for the ticket with
  the author's name joined in, ordered by created_at asc.

- `POST /api/teams/[teamId]/tickets/[ticketId]/replies` — uses
  `authUser(event, "tickets.view")`. Any team member can reply. Reads
  { content, is_internal } from the body. Sets user_id from the
  authenticated user. If this is the first non-internal reply and the
  ticket's first_response_at is null, set first_response_at to now().

UI:

Create `app/pages/app/tickets/index.vue` — a ticket list page as a top-level
route (under `/app`, sibling of other features), wrapped in a `UDashboardPanel`:

- `UDashboardNavbar` in the header: title "Tickets",
  `UDashboardSidebarCollapse` on the left. On the right, a "New Ticket"
  button wrapped in `CanAccess permission="tickets.create"`.

- `UDashboardToolbar` below the navbar: on the left, show status counts
  (e.g. "5 Open, 3 In Progress, 2 Waiting"). On the right, a search input
  and status filter `USelect` with options: All, Open, In Progress,
  Waiting, Resolved, Closed.

- Ticket list in the body as a table with columns: ticket number (formatted
  as #001, #002, etc.), subject, category badge, requester name, assignee
  name (or "Unassigned"), status badge (color-coded — open = "error",
  in_progress = "warning", waiting = "info", resolved = "success",
  closed = "neutral"), priority badge (low = "neutral", normal = "info",
  high = "warning", urgent = "error"), reply count, and created date
  (formatted as relative time, e.g. "2 hours ago").

- Each row has an inline status dropdown using `UDropdownMenu`. Show
  per-row loading state while the status is being updated.

- "New Ticket" button opens a `UModal` with a form: subject (required),
  description (required, textarea), category (select), priority (select),
  assignee (optional — select from team members fetched on mount, use
  placeholder "Unassigned"), requester name, requester email, tags
  (comma-separated text input). Use Zod for validation. Show loading
  on submit.

- Clicking a ticket row opens a `USlideover` for the ticket detail. Show
  ticket metadata in a header section (subject, status, priority, category,
  assignee, requester, tags, created date, resolved date). Below, show the
  ticket description, then a "Conversation" section: a scrollable list of
  replies (author name, content, timestamp). Internal notes should have a
  distinct yellow background and an "Internal" badge. At the bottom, a
  reply input with a "Reply" button and a toggle for "Internal note".
  Leave a placeholder area for a canned response selector — it will be
  wired up in the next prompt. Include action buttons: assign (opens a member
  select), change priority, and delete (wrapped in
  `CanAccess permission="tickets.delete"` with confirmation modal).

- After creating, editing, or deleting a ticket, refresh the ticket list.

Sidebar navigation:

Add a "Tickets" link to the top navigation group in
`app/components/layout/sidebar/Links.vue`, between "Dashboard" and
"Settings". Use the icon `i-solar-chat-round-dots-bold-duotone`.

The `items` ref is already a `computed<NavigationMenuItem[]>` in this
template — push the new entry into that list. Use `can()` from
`useUserRole()` to gate items by permission if needed.

Build the canned responses module — reusable reply templates.

Server routes:

- `GET /api/teams/[teamId]/canned-responses` — uses
  `authUser(event, "canned_responses.view")`. Returns all canned responses
  for the team, ordered by title asc.

- `POST /api/teams/[teamId]/canned-responses` — uses
  `authUser(event, "canned_responses.manage")`. Reads { title, content,
  category } from the body. Validates that title and content are required.
  Sets team_id from auth context and created_by from the authenticated user.

- `PATCH /api/teams/[teamId]/canned-responses/[responseId]` — uses
  `authUser(event, "canned_responses.manage")`. Updates whichever fields
  are provided. Validates that the response belongs to the team.

- `DELETE /api/teams/[teamId]/canned-responses/[responseId]` — uses
  `authUser(event, "canned_responses.manage")`. Validates that the response
  belongs to the team before deleting.

UI:

Add a "Canned Responses" section to the team settings page (or create a
new settings sub-page at `app/pages/app/settings/canned-responses.vue`):

- Show a list of existing canned responses with title, category, and a
  preview of the content (truncated to 100 characters).

- "New Response" button opens a `UModal` with a form: title (required),
  content (required, textarea with markdown support), category (optional).
  Use Zod for validation.

- Clicking a response opens it for editing in a `UModal`. Include a delete
  button with confirmation.

- All management actions are wrapped in
  `CanAccess permission="canned_responses.manage"`.

Also update the ticket detail slideover (built in the previous prompt) to
wire up the canned response selector: fetch canned responses on mount and
show them in a `USelect` above the reply input. When a canned response is
selected, populate the reply content field with its content.

Replace the placeholder dashboard with real support metrics.

Server route:

Create `GET /api/teams/[teamId]/stats` — uses `authUser(event, "team.view")`.
Returns a JSON object with:

- `open_tickets` — count of tickets with status "open" or "in_progress"
- `waiting_tickets` — count of tickets with status "waiting"
- `resolved_this_month` — count of tickets resolved in the current calendar
  month (where resolved_at is in the current month)
- `avg_first_response_hours` — average hours between created_at and
  first_response_at for tickets resolved this month. Return null if no data.
- `tickets_by_category` — array of { category, count } for open tickets,
  ordered by count desc
- `tickets_by_priority` — array of { priority, count } for open tickets
- `recent_tickets` — the last 10 tickets with assignee name, status,
  priority, and created_at. Include reply count.
- `agent_workload` — array of { agent_name, open_count, in_progress_count }
  for each team member with assigned tickets, ordered by total count desc

All queries are scoped to the team's team_id.

UI:

Update `app/pages/app/index.vue` to show:

- A row of four stat cards at the top using a grid layout. Each card shows
  an icon, a label, and the value. Cards: "Open Tickets" (open_tickets),
  "Waiting" (waiting_tickets), "Resolved This Month" (resolved_this_month),
  "Avg First Response" (avg_first_response_hours formatted as "Xh" or
  "< 1h", or "—" if null). Use `USkeleton` placeholders while loading.

- Below the stats, a two-column layout:
  - Left column: "Recent Tickets" — a list of the last 10 tickets. Each
    item shows ticket number, subject, status badge, priority badge,
    assignee name, and relative timestamp. Clicking a ticket navigates to
    the tickets page. Show an empty state if no tickets.
  - Right column: "Team Workload" — a list of agents and their open/in-progress
    ticket counts, shown as a simple bar or number. Below that, a breakdown
    of tickets by category as small badges with counts.

Remove any placeholder/scaffolding content that was in the dashboard before.
Keep the `UDashboardPanel` wrapper with navbar and sidebar collapse.

Add Supabase Realtime sync for the new tables so changes appear instantly
across browser sessions.

Database migration (via Supabase MCP):

Enable realtime publication and full replica identity for the new tables:

```sql
ALTER PUBLICATION supabase_realtime ADD TABLE tickets, ticket_replies, ticket_tags;
ALTER TABLE tickets REPLICA IDENTITY FULL;
ALTER TABLE ticket_replies REPLICA IDENTITY FULL;
ALTER TABLE ticket_tags REPLICA IDENTITY FULL;
```

Update `app/composables/useRealtime.ts`:

1. Add `"tickets"`, `"ticket_replies"`, and `"ticket_tags"` to the
   `RealtimeTable` union type.
2. In the `setup()` function, add `.on("postgres_changes", ...)` handlers
   for each new table, filtered by `team_id=eq.${teamId}` where the table
   has a team_id column. For `ticket_replies` and `ticket_tags`, filter
   through a join is not possible in Realtime — subscribe unfiltered and
   let the callback decide relevance, or subscribe unfiltered since RLS
   already scopes the events.

Integration — use `onTableDebounced` from `useRealtime()` inline in each
page. Do NOT create separate `useRealtimeX` composable files:

- In `app/pages/app/tickets/index.vue`:
  `const { onTableDebounced } = useRealtime()`
  `onTableDebounced(["tickets", "ticket_tags"], () => refreshTickets())`
- In the ticket detail slideover:
  `const { onTableDebounced } = useRealtime()`
  `onTableDebounced("ticket_replies", () => refreshReplies())`
- In the dashboard stats page:
  `const { onTableDebounced } = useRealtime()`
  `onTableDebounced(["tickets", "ticket_replies"], () => refreshStats())`