# Event reporting research and implementation basis

## Decision summary

Membersync should treat a check-in as an immutable event, not a single boolean on a ticket. Reports therefore use `event_checkin_logs` for overall attendance, repeat daily attendance, hourly throughput, and per-session attendance. Merchandise distributions remain separate operational events and retain their optional session and check-in links. Ticket sales, payment state, meals, elections, awards, training and attendee participation remain separate report sections so that unrelated joins cannot inflate totals.

## External findings

- Eventbrite exposes real-time check-in counts and a clear checked-in state during event operations. Its reporting separates attendee/order status from attendance status, which supports keeping payment, ticket and entry facts separate in Membersync.
- Eventbrite attendance reports preserve check-in time and provide exports. Its attendee export guidance recommends CSV for datasets above one million rows. Membersync should therefore paginate on screen and stream/chunk large exports rather than construct one enormous workbook or browser object.
- Mature event reporting supports event/date selection, grouping and filtering by ticket type. Membersync should expose the same dimensions while retaining session, meal and merchandise detail specific to its conference workflow.
- MySQL documents that composite indexes accelerate selective filtering and can assist grouping when the grouped fields match an ordered index prefix. Membersync's event/time and ticket/time check-in indexes follow this pattern.
- MySQL recommends validating report queries with `EXPLAIN`/`EXPLAIN ANALYZE`; an index being present does not prove that it is useful. Production-sized data should be used before further index changes.

## Implemented event-report model

1. Registration and revenue: verified tickets, pending/verified status, ticket types, daily sales and balances.
2. Overall attendance: unique tickets with at least one immutable check-in log.
3. Daily attendance: day, unique attendees, total scans, first scan and last scan.
4. Session attendance: schedule, type, unique attendees, scans, first scan and last scan; null schedules are reported as general event entry.
5. Attendee ledger: ticket status, first/last check-in, scan count and merchandise received.
6. Distribution ledger: attendee, ticket, item, related session and distribution time.
7. Meal fulfilment: cards issued/activated/collected by schedule and attendee.
8. Program engagement: elections, nominations, awards, training resources and general schedules.

## Scaling and controls

- Keep UI summaries aggregated; load row-level ledgers only for the selected event.
- Paginate row-level APIs before production datasets become large.
- Export large data as streamed CSV in bounded chunks; queue complex PDF/Excel packages and notify the admin when ready.
- Authorize reports with the existing report permission and avoid exposing attendee contact details to roles that only need summary analytics.
- Validate the principal queries with `EXPLAIN ANALYZE` against a production-like copy before adding or dropping indexes.

## Sources

- Eventbrite, "Check in attendees from your computer": https://www.eventbrite.com/help/en-us/articles/126937/
- Eventbrite, "Glossary of order and attendee statuses in event reports": https://www.eventbrite.com/help/en-us/articles/291888/
- Eventbrite, "Download an Attendees report": https://www.eventbrite.com/help/en-us/articles/496608/download-an-attendees-report/
- Eventbrite, "How to use the Analytics tool": https://www.eventbrite.com/help/en-gb/articles/237711/how-to-use-the-analytics-tool/
- MySQL 8.0 Reference Manual, "How MySQL Uses Indexes": https://dev.mysql.com/doc/refman/8.0/en/mysql-indexes.html
- MySQL 8.0 Reference Manual, "EXPLAIN Statement": https://dev.mysql.com/doc/refman/8.0/en/explain.html
- MySQL 8.0 Reference Manual, "GROUP BY Optimization": https://dev.mysql.com/doc/refman/8.0/en/group-by-optimization.html