Designing Composite Indexes for Competing Query Patterns

Your support_tickets table (12 million rows) serves three query patterns from different parts of the product, all currently slow (full seq scans):

-- (A) Agent dashboard: tickets assigned to an agent, open ones first,
-- newest first
SELECT * FROM support_tickets
WHERE assigned_agent_id = 481 AND status = 'open'
ORDER BY created_at DESC LIMIT 50;

-- (B) Team lead report: count of tickets per status for a team,
-- over a date range
SELECT status, COUNT(*) FROM support_tickets
WHERE team_id = 12 AND created_at BETWEEN '2026-07-01' AND '2026-08-01'
GROUP BY status;

-- (C) Customer-facing status check: a single ticket by its public
-- reference code
SELECT * FROM support_tickets WHERE reference_code = 'TCK-88213';

1. Propose a specific set of indexes (as few as you can justify) to serve all three patterns well, giving exact column lists and order, and explain which query each index serves and why.
2. Explain why a single composite index across all involved columns would not serve all three queries well, even though it "covers" every column mentioned.
3. Your DBA is worried about total index count on a 12-million-row, moderately write-heavy table (a few hundred writes/second). Which, if any, of your proposed indexes would you argue against adding, and what would you check before deciding?