Data model foundation: communities, sessions, ratings and operations

[elgorro]

The schema is a thin slice — players, teams, matches, tournaments — and does not yet
carry the things the product needs: communities, scheduling, formats, money, privacy.

This issue tracks laying down the model, so the feature issues that follow have
something to build on rather than each inventing their own tables.

Scope

Eleven bounded contexts, five migrations that each apply and revert on their own:

• Identity — Player (global), PlayerContact with per-channel visibility, PlayerProfile
• Communities — Community, CommunityMember, Season, invites, join requests, CommunityLink
• Venues — Venue, Court, CourtReservation
• Play — Format (2v2–6v6, seeded), RuleSet, SessionTemplate, Session, SessionParticipant
• Matches — MatchAppearance, MatchDispute, PlayerFormatStat, tournament entries and brackets
• Polls — Poll, PollOption, Vote
• Money — double-entry Account / LedgerEntry, dues, funding sources
• Equipment — items, loans, service requests
• Weather — an observation per session
• Privacy — consent records, data requests, media and who appears in it, audit events
• Notifications — per-member preferences and a transactional outbox

Decisions

Recorded here because they are expensive to reverse:

1. Rating lives on CommunityMember, not Player. Standing is relative to the people
   you play with.
2. MatchAppearance is the rating source of truth, not TeamMember. Teams are lineups
   and change; who played a given match does not. It also handles substitutes and uneven sides.
3. Matches snapshot their scoring rules, so amending a rule set cannot rewrite the past.
4. Money is long minor units plus a currency code. No decimal, no floats.
5. Double-entry ledger from the start, making dues, fees, expenses and funding one mechanism.
6. CommunityId is denormalised onto every owned entity; queries filter on it explicitly,
   with no global query filter.
7. Formats carry a rating weight — a 6v6 result says less about one player than a 2v2 does.
8. Federation is identifiers and a link table only. No protocol yet; the point is that the
   schema will not need rewriting when it arrives.

Enforced by the database, not the application

• Court reservations cannot overlap (EXCLUDE USING gist, half-open ranges)
• Ledger amounts are positive and move between two different accounts
• A member-balance account has a member; nothing else does
• An item is on loan to at most one person at a time
• A community has at most one current season; a format has at most one default rule set

Not in scope here

Left deliberately unbuilt, each wanting its own issue:

• Generating Session rows from SessionTemplate (background job)
• Draining NotificationOutbox
• Writing AuditEvent from a SaveChangesInterceptor
• Match confirmation and dispute flow in the UI
• Court picker on match entry (free text still lands in LocationNote)

Dashboards are not modelled: they are queries over this schema, not tables. Weather forecasts
are fetched live; only the observation taken at session time is kept.