Build a Production-Style SQL Server Backend: A Capstone Ticket System Project

This combines every chapter of the advanced track into one realistic deliverable: the backend for TicketDesk, a small support-ticket system, built the way a real backend actually gets built — ambiguous edges, several defensible designs, and a requirement to justify your choices, not just produce code that runs.

Schema Requirements

• Agent: agent_id, name, email (unique), is_active
• Customer: customer_id, name, email (unique)
• Ticket: ticket_id, customer_id (FK), assigned_agent_id (FK, nullable — unassigned tickets are a valid state), status, priority, created_at, resolved_at
• TicketComment: comment_id, ticket_id (FK), author_type, body, created_at
• TicketAudit: populated automatically by a trigger — old_status, new_status, changed_at

The Architecture, Visualized

Business Logic Requirements — Mapped to Where You Learned Each One

-- A skeleton for one requirement, deliberately incomplete — you decide the JOIN type
CREATE VIEW dbo.vw_AgentWorkload AS
SELECT a.agent_id, a.name, COUNT(t.ticket_id) AS open_ticket_count
FROM dbo.Agent a
-- ??? JOIN dbo.Ticket t ON t.assigned_agent_id = a.agent_id AND t.status IN ('open','in_progress')
WHERE a.is_active = 1
GROUP BY a.agent_id, a.name;

The blank above is deliberate: pick the wrong JOIN type here and agents with zero open tickets silently vanish from the report — the exact LEFT JOIN + WHERE-vs-ON distinction from the Fundamentals course, now applied inside a view that a real dashboard would depend on.

Performance & Security Requirements

• Populate Ticket with 5,000+ rows and design a covering/filtered index for “open tickets by agent, ordered by priority” — prove it with before/after STATISTICS IO (Ch.8)
• Create a least-privilege service account for the application — not db_owner, with explicit GRANTs you can justify one by one (Ch.10)

Self-Check Before You Consider It Done

Why This Is the Right Capstone

Every chapter of this track shows up here: functions, procedures with proper error handling, a correctly set-based trigger, a view with the right JOIN type, indexing backed by real measurement, and least-privilege security. It mirrors how a real backend ticket actually gets built — ambiguous edges, multiple valid designs, and a requirement to justify your decisions, not just produce working code.