ARFC-1004: Teams, Leagues, Seasons, and Competitive Gaming Schema

Status

Implemented

Draft Date: 2026-02-26

Last Call Date: 2026-03-05

Publication Date: 2026-03-12

Version: 0.20

Abstract

This RFC proposes a comprehensive database schema for managing competitive teams within the ĀYŌDÈ platform. The schema introduces Leagues (owned by Realms), Teams (belonging to Leagues), Seasons (competitive periods within Leagues), Games (events within Seasons), and Plays (atomic scoring events within Games). The design supports team management workflows including membership, invitations, bans, and hierarchical scoring aggregation.

Introduction
Motivation
Specification
Security Considerations
Backward Compatibility
References

1. Introduction

The ĀYŌDÈ platform requires support for competitive team-based activities such as the Flight Path competition. This RFC defines the database schema necessary to support:

Leagues: Competitive containers owned by Realms that define team size constraints
Teams: Groups of users competing within a League
Seasons: Time-bounded competitive periods within a League
Games: Individual matches or events within a Season
Plays: Atomic scoring events within a Game

The schema follows existing ĀYŌDÈ patterns including the triple-UUID pattern (id, externalID, internalUUID), closure tables for hierarchy traversal, and standard audit columns.

2. Motivation

Current State

The existing schema supports:

Users with profiles
Realms (hierarchical organizational units) with RealmMembership
Roles and privilege-based access control

Missing Capabilities

To support competitions like Flight Path, the platform needs:

Team formation and management - Users forming teams with managers and captains
League-level constraints - Minimum/maximum team sizes enforced at the league level
Seasonal competition structure - Multiple seasons per league, with registration windows
Game tracking - Individual competitive events with participating teams
Point-based scoring - Granular play-by-play scoring that aggregates upward

Design Goals

Realm integration: All team members must be members of the League’s owning realm (or descendants)
Flexible team roles: Separate Manager (administrative) and Captain (gameplay leadership) roles
Invitation workflow: Support both manager-initiated invites and user-initiated applications
Ban management: Temporary or permanent bans from specific teams
Hierarchical scoring: Points flow from Plays → Games → Seasons

3. Specification

3.1 Schema Overview

flowchart TB
    subgraph Existing["Existing Schema"]
        Realms[(Realms)]
    end

    subgraph LeagueLayer["League Layer"]
        Leagues[(Leagues)]
    end

    subgraph TeamLayer["Team Layer"]
        Teams[(Teams)]
        TeamMembership[(TeamMembership)]
        TeamBans[(TeamBans)]
        TeamMembershipRequests[(TeamMembershipRequests)]
    end

    subgraph SeasonLayer["Season Layer"]
        Seasons[(Seasons)]
        SeasonParticipation[(SeasonParticipation)]
    end

    subgraph GameLayer["Game Layer"]
        Games[(Games)]
        GameParticipation[(GameParticipation)]
        Plays[(Plays)]
    end

    Realms -->|"1:N"| Leagues
    Leagues -->|"1:N"| Teams
    Leagues -->|"1:N"| Seasons
    Leagues -.->|"dictates min/max members"| Teams

    Teams -->|"1:N"| TeamMembership
    Teams -->|"1:N"| TeamBans
    Teams -->|"1:N"| TeamMembershipRequests

    Seasons -->|"1:N"| SeasonParticipation
    Seasons -->|"1:N"| Games

    SeasonParticipation -->|"N:1"| Teams

    Games -->|"1:N"| GameParticipation
    Games -->|"1:N"| Plays

    GameParticipation -->|"N:1"| Teams
    Plays -->|"N:1"| Teams

3.2 Table Definitions

Leagues

Leagues are competitive containers owned by Realms. They define team size constraints that apply to all teams within the league.

Column	Type	Nullable	Description
id	INT	NOT NULL	Auto-increment primary key
externalID	BINARY(16)	NOT NULL	Public API identifier (unique)
internalUUID	BINARY(16)	NOT NULL	Service-to-service identifier (unique)
realmID	INT	NOT NULL	FK to Realms — owning realm
name	VARCHAR(255)	NOT NULL	League name (unique within realm)
description	TEXT	NULL	Optional description
minimumMembers	INT	NOT NULL	Minimum team size (≥ 1)
maximumMembers	INT	NULL	Maximum team size (≥ minimumMembers if set)
status	ENUM	NOT NULL	`active`, `archived`, `suspended`
audit columns			Standard createdBy/updatedBy/timestamps

Constraints:

Unique: (realmID, name)
Check: minimumMembers ≥ 1
Check: maximumMembers is null OR maximumMembers ≥ minimumMembers

Teams

Teams belong to exactly one League and consist of zero or more members. Each team has a Manager (administrative control) and optionally a Captain (must be a member).

Column	Type	Nullable	Description
id	INT	NOT NULL	Auto-increment primary key
externalID	BINARY(16)	NOT NULL	Public API identifier (unique)
internalUUID	BINARY(16)	NOT NULL	Service-to-service identifier (unique)
leagueID	INT	NOT NULL	FK to Leagues
name	VARCHAR(255)	NOT NULL	Team name (unique within league)
description	TEXT	NULL	Optional description
managerUserID	INT	NOT NULL	FK to Users — administrative owner
captainUserID	INT	NULL	FK to Users — gameplay leader (must be a member)
status	ENUM	NOT NULL	`forming`, `active`, `disbanded`
audit columns			Standard createdBy/updatedBy/timestamps

Constraints:

Unique: (leagueID, name)
captainUserID must be a current member (enforced by stored procedure)

TeamMembership

Links users to teams with full join/leave history. Each user can be a member of multiple teams (across different leagues), and may join/leave the same team multiple times.

History Pattern: Following the Sessions table pattern, each membership period creates a new record. The isMostRecent flag marks the current record for each (teamID, userID) pair. To find active members: isMostRecent = true AND leftAt is null.

Column	Type	Nullable	Description
id	INT	NOT NULL	Auto-increment primary key
externalID	BINARY(16)	NOT NULL	Public API identifier (unique)
internalUUID	BINARY(16)	NOT NULL	Service-to-service identifier (unique)
teamID	INT	NOT NULL	FK to Teams
userID	INT	NOT NULL	FK to Users
membershipIndex	INT	NOT NULL	Zero-based index for this (team, user) pair
role	ENUM	NOT NULL	`member`, `alternate`
joinedAt	TIMESTAMP(6)	NOT NULL	When user joined
leftAt	TIMESTAMP(6)	NULL	When user left (null if still active)
isMostRecent	BOOLEAN	NOT NULL	True for current record per (team, user)
audit columns			Standard createdBy/updatedBy/timestamps

Constraints:

Unique: (teamID, userID, membershipIndex)
Index: (teamID, userID) for lookups
Index: (teamID, userID, isMostRecent) for current-record queries

Stored Procedure Workflow:

Action	Procedure Logic
User joins (first time)	Insert with `membershipIndex = 0`, `isMostRecent = TRUE`, `leftAt = NULL`
User leaves	Update current record: set `leftAt = NOW()`
User rejoins	Set `isMostRecent = FALSE` on prior record, insert new record with `membershipIndex = MAX + 1`, `isMostRecent = TRUE`, `leftAt = NULL`

TeamMembershipRequests

Tracks both invitations (manager-initiated) and applications (user-initiated) to join teams, with full request history.

History Pattern: Each request creates a new record. The isMostRecent flag marks the current record for each (teamID, userID) pair.

Invariant: At most one pending request per (teamID, userID). Stored procedures enforce this by rejecting new requests if a pending request exists for that pair.

Column	Type	Nullable	Description
id	INT	NOT NULL	Auto-increment primary key
externalID	BINARY(16)	NOT NULL	Public API identifier (unique)
internalUUID	BINARY(16)	NOT NULL	Service-to-service identifier (unique)
teamID	INT	NOT NULL	FK to Teams
userID	INT	NOT NULL	FK to Users — candidate member
requestIndex	INT	NOT NULL	Zero-based index for this (team, user) pair
requestType	ENUM	NOT NULL	`invite` (manager-initiated), `apply` (user-initiated)
status	ENUM	NOT NULL	`pending`, `accepted`, `declined`, `expired`
message	TEXT	NULL	Optional message with request
requestedAt	TIMESTAMP(6)	NOT NULL	When request was created
expiresAt	TIMESTAMP(6)	NULL	When request auto-expires
respondedAt	TIMESTAMP(6)	NULL	When request was accepted/declined
respondedByUserID	INT	NULL	FK to Users — who responded
isMostRecent	BOOLEAN	NOT NULL	True for current record per (team, user)
audit columns			Standard createdBy/updatedBy/timestamps

Constraints:

Unique: (teamID, userID, requestIndex)

Stored Procedure Workflow:

Action	Procedure Logic
Create request	Reject if pending exists (`isMostRecent = TRUE AND status = 'pending'`). Set `isMostRecent = FALSE` on prior record (if any), insert new with `requestIndex = MAX + 1`, `isMostRecent = TRUE`, `status = 'pending'`
Accept/Decline	Update current record: set `status`, `respondedAt = NOW()`, `respondedByUserID`
Expire	Background job updates: set `status = 'expired'` where `expiresAt < NOW() AND status = 'pending'`

TeamBans

Tracks users banned from specific teams with full ban history. Users can be banned multiple times from the same team.

History Pattern: Each ban creates a new record. The isMostRecent flag marks the current record for each (teamID, userID) pair. Active ban: isMostRecent = true AND liftedAt is null AND (expiresAt is null OR expiresAt > now).

Column	Type	Nullable	Description
id	INT	NOT NULL	Auto-increment primary key
externalID	BINARY(16)	NOT NULL	Public API identifier (unique)
internalUUID	BINARY(16)	NOT NULL	Service-to-service identifier (unique)
teamID	INT	NOT NULL	FK to Teams
userID	INT	NOT NULL	FK to Users — banned user
banIndex	INT	NOT NULL	Zero-based index for this (team, user) pair
reason	TEXT	NULL	Optional ban reason
bannedByUserID	INT	NOT NULL	FK to Users — who issued the ban
bannedAt	TIMESTAMP(6)	NOT NULL	When ban was issued
expiresAt	TIMESTAMP(6)	NULL	When ban auto-expires (null = permanent)
liftedAt	TIMESTAMP(6)	NULL	When ban was manually lifted
liftedByUserID	INT	NULL	FK to Users — who lifted the ban
isMostRecent	BOOLEAN	NOT NULL	True for current record per (team, user)
audit columns			Standard createdBy/updatedBy/timestamps

Constraints:

Unique: (teamID, userID, banIndex)

Stored Procedure Workflow:

Action	Procedure Logic
Ban user (first time)	Insert with `banIndex = 0`, `isMostRecent = TRUE`, `liftedAt = NULL`
Lift ban	Update current record: set `liftedAt = NOW()`, `liftedByUserID`
Ban again	Set `isMostRecent = FALSE` on prior record, insert new with `banIndex = MAX + 1`, `isMostRecent = TRUE`

Seasons

Seasons are time-bounded competitive periods within a League. Teams register for seasons during the registration window.

Column	Type	Nullable	Description
id	INT	NOT NULL	Auto-increment primary key
externalID	BINARY(16)	NOT NULL	Public API identifier (unique)
internalUUID	BINARY(16)	NOT NULL	Service-to-service identifier (unique)
leagueID	INT	NOT NULL	FK to Leagues
name	VARCHAR(255)	NOT NULL	Season name (unique within league)
description	TEXT	NULL	Optional description
seasonIndex	INT	NOT NULL	Zero-based index within league
registrationOpensDate	DATE	NULL	When registration opens
registrationClosesDate	DATE	NULL	When registration closes
startDate	DATE	NULL	Competition start date
endDate	DATE	NULL	Competition end date
status	ENUM	NOT NULL	`upcoming`, `active`, `completed`, `cancelled`
metadata	JSON	NULL	Extensible metadata
audit columns			Standard createdBy/updatedBy/timestamps

Constraints:

Unique: (leagueID, name)
Check: registrationOpensDate ≤ registrationClosesDate (if both set)
Check: startDate ≤ endDate (if both set)

SeasonParticipation

Links teams to seasons they are participating in. Tracks registration status and aggregated scores.

Column	Type	Nullable	Description
id	INT	NOT NULL	Auto-increment primary key
externalID	BINARY(16)	NOT NULL	Public API identifier (unique)
internalUUID	BINARY(16)	NOT NULL	Service-to-service identifier (unique)
seasonID	INT	NOT NULL	FK to Seasons
teamID	INT	NOT NULL	FK to Teams
registrationStatus	ENUM	NOT NULL	`registered`, `confirmed`, `withdrawn`, `disqualified`
seedRank	INT	NULL	Initial seeding position
finalRank	INT	NULL	Final standing
totalPoints	INT	NOT NULL	Aggregated points (derived from games)
audit columns			Standard createdBy/updatedBy/timestamps

Constraints:

Unique: (seasonID, teamID)

Games

Games are individual competitive events within a Season.

Column	Type	Nullable	Description
id	INT	NOT NULL	Auto-increment primary key
externalID	BINARY(16)	NOT NULL	Public API identifier (unique)
internalUUID	BINARY(16)	NOT NULL	Service-to-service identifier (unique)
seasonID	INT	NOT NULL	FK to Seasons
name	VARCHAR(255)	NULL	Optional game name
gameIndex	INT	NOT NULL	Zero-based index within season
gameType	VARCHAR(63)	NULL	Game type/category
scheduledAt	TIMESTAMP(6)	NULL	Scheduled start time
startedAt	TIMESTAMP(6)	NULL	Actual start time
endedAt	TIMESTAMP(6)	NULL	End time
status	ENUM	NOT NULL	`scheduled`, `in_progress`, `completed`, `cancelled`, `forfeited`
metadata	JSON	NULL	Extensible metadata
audit columns			Standard createdBy/updatedBy/timestamps

Indexes:

(seasonID, gameIndex) for ordered game lookups

GameParticipation

Links teams to specific games. Tracks per-game scores and outcomes.

Column	Type	Nullable	Description
id	INT	NOT NULL	Auto-increment primary key
externalID	BINARY(16)	NOT NULL	Public API identifier (unique)
internalUUID	BINARY(16)	NOT NULL	Service-to-service identifier (unique)
gameID	INT	NOT NULL	FK to Games
teamID	INT	NOT NULL	FK to Teams
participationRole	VARCHAR(63)	NULL	Role in game (e.g., “home”, “away”)
gameScore	INT	NOT NULL	Aggregated score (derived from plays)
placement	INT	NULL	Final placement in game
outcome	ENUM	NOT NULL	`pending`, `win`, `loss`, `draw`, `forfeit`
audit columns			Standard createdBy/updatedBy/timestamps

Constraints:

Unique: (gameID, teamID)

Plays

Plays are atomic scoring events within a Game. Points can be positive or negative (penalties).

Play Types:

Type	Description	Typical Points
`solve`	Team solved a challenge/puzzle	Positive
`partial`	Partial credit for progress	Positive
`bonus`	Bonus points (speed, elegance, etc.)	Positive
`hint`	Penalty for requesting a hint	Negative
`penalty`	General penalty (rule violation, etc.)	Negative
`first_blood`	First team to solve a challenge	Positive
`adjustment`	Manual admin adjustment	Either

Invalidation Invariants:

Validity is derived from invalidatedAt IS NULL. The following consistency rules are enforced via CHECK constraint:

State	`invalidatedAt`	`invalidatedByUserID`	`invalidationReason`
Valid	NULL	NULL	NULL
Invalidated	NOT NULL	NOT NULL (required)	Optional

A valid play has no invalidation metadata
An invalidated play must record who invalidated it and when
invalidationReason is optional (may be omitted for simple voids)

Column	Type	Nullable	Description
id	INT	NOT NULL	Auto-increment primary key
externalID	BINARY(16)	NOT NULL	Public API identifier (unique)
internalUUID	BINARY(16)	NOT NULL	Service-to-service identifier (unique)
gameID	INT	NOT NULL	FK to Games
teamID	INT	NOT NULL	FK to Teams — must be a game participant
userID	INT	NULL	FK to Users — individual attribution (optional)
playIndex	INT	NOT NULL	Zero-based index within game
playType	ENUM	NOT NULL	`solve`, `partial`, `bonus`, `hint`, `penalty`, `first_blood`, `adjustment`
points	INT	NOT NULL	Point value (positive or negative)
occurredAt	TIMESTAMP(6)	NOT NULL	When play occurred
description	TEXT	NULL	Optional description
metadata	JSON	NULL	Extensible metadata
invalidatedAt	TIMESTAMP(6)	NULL	When play was voided
invalidatedByUserID	INT	NULL	FK to Users — who invalidated
invalidationReason	TEXT	NULL	Optional reason for invalidation
audit columns			Standard createdBy/updatedBy/timestamps

Constraints:

Check: (invalidatedAt is null AND invalidatedByUserID is null) OR (invalidatedAt is not null AND invalidatedByUserID is not null)

Critical Invariant — Scoring Integrity:

A Play’s teamID MUST exist in GameParticipation for the Play’s gameID.

This invariant prevents points from being credited to teams that are not participating in the game. Without enforcement, a play could be recorded for an arbitrary team, corrupting leaderboards and scoring aggregates.

Enforcement: api_playRecord_v1 MUST reject the insert if no matching GameParticipation record exists:

if not exists(GameParticipation where gameID = play.gameID AND teamID = play.teamID):
    reject "TEAM_NOT_IN_GAME: Cannot record play for non-participating team"

Additional constraint:

If userID is set, user must be current member of teamID (enforced by stored procedure)

3.3 Relationship Diagrams

Entity Relationship Diagram

erDiagram
    Realms ||--o{ Leagues : "owns"
    Leagues ||--o{ Teams : "contains"
    Leagues ||--o{ Seasons : "has"

    Teams ||--o{ TeamMembership : "has"
    Teams ||--o{ TeamMembershipRequests : "has"
    Teams ||--o{ TeamBans : "has"
    Teams }o--|| Users : "managedBy"
    Teams }o--o| Users : "captainedBy"

    TeamMembership }o--|| Users : "member"
    TeamMembershipRequests }o--|| Users : "candidate"
    TeamBans }o--|| Users : "banned"

    Seasons ||--o{ SeasonParticipation : "has"
    Seasons ||--o{ Games : "contains"
    SeasonParticipation }o--|| Teams : "participant"

    Games ||--o{ GameParticipation : "has"
    Games ||--o{ Plays : "contains"
    GameParticipation }o--|| Teams : "participant"

    Plays }o--|| Teams : "scoredBy"
    Plays }o--o| Users : "playedBy"

    Leagues {
        int id PK
        binary externalID UK
        binary internalUUID UK
        int realmID FK
        string name
        int minimumMembers
        int maximumMembers
        enum status
    }

    Teams {
        int id PK
        binary externalID UK
        binary internalUUID UK
        int leagueID FK
        string name
        int managerUserID FK
        int captainUserID FK
        enum status
    }

    Seasons {
        int id PK
        binary externalID UK
        binary internalUUID UK
        int leagueID FK
        string name
        date registrationOpensDate
        date registrationClosesDate
        date startDate
        date endDate
        enum status
    }

    Games {
        int id PK
        binary externalID UK
        binary internalUUID UK
        int seasonID FK
        string name
        int gameIndex
        enum status
    }

    Plays {
        int id PK
        binary externalID UK
        binary internalUUID UK
        int gameID FK
        int teamID FK
        int userID FK
        enum playType
        int points
        timestamp invalidatedAt
    }

Scoring Flow

flowchart TB
    subgraph Atomic["Atomic Events"]
        P[/"Plays<br/>(points per event)"/]
    end

    subgraph GameLevel["Game Aggregation"]
        GP[("GameParticipation.gameScore")]
    end

    subgraph SeasonLevel["Season Aggregation"]
        SP[("SeasonParticipation.totalPoints")]
    end

    P -->|"SUM(points)<br/>WHERE invalidatedAt IS NULL"| GP
    GP -->|"SUM(gameScore)<br/>per team per season"| SP

    style P fill:#e1f5fe,color:#1b1a17
    style GP fill:#fff3e0,color:#1b1a17
    style SP fill:#e8f5e9,color:#1b1a17

Team Lifecycle

stateDiagram-v2
    [*] --> forming: Team Created
    forming --> active: Minimum Members Met
    active --> disbanded: Manager Disbands
    forming --> disbanded: Manager Disbands
    disbanded --> [*]

    note right of forming
        Team is recruiting members
        Cannot participate in seasons
    end note

    note right of active
        Team meets league requirements
        Can register for seasons
    end note

Season Lifecycle

stateDiagram-v2
    [*] --> upcoming: Season Created
    upcoming --> active: Start Date Reached
    active --> completed: End Date Reached
    upcoming --> cancelled: Admin Cancels
    active --> cancelled: Admin Cancels
    completed --> [*]
    cancelled --> [*]

    note right of upcoming
        Registration window may be open
        registrationOpensDate <= NOW <= registrationClosesDate
    end note

Membership Request Workflow

flowchart LR
    subgraph Invite["Manager Invites User"]
        A1[Manager creates invite] --> A2{User responds}
        A2 -->|Accept| A3[Add to TeamMembership]
        A2 -->|Decline| A4[Request declined]
        A2 -->|No response| A5[Request expires]
    end

    subgraph Apply["User Applies to Team"]
        B1[User submits application] --> B2{Manager responds}
        B2 -->|Accept| B3[Add to TeamMembership]
        B2 -->|Decline| B4[Request declined]
        B2 -->|No response| B5[Request expires]
    end

Realm Membership Validation

flowchart TB
    Start([User wants to join Team]) --> GetLeague[Get Team's League]
    GetLeague --> GetRealm[Get League's Realm]
    GetRealm --> CheckMembership{Is User member of<br/>Realm or descendant?}

    CheckMembership -->|Yes| CheckBan{Is User banned<br/>from Team?}
    CheckMembership -->|No| Reject([Reject: Not in Realm])

    CheckBan -->|No| CheckSize{Would adding User<br/>exceed maximumMembers?}
    CheckBan -->|Yes| Reject2([Reject: User is Banned])

    CheckSize -->|No| Accept([Add to TeamMembership])
    CheckSize -->|Yes| Reject3([Reject: Team Full])

    style Accept fill:#c8e6c9,color:#1b1a17
    style Reject fill:#ffcdd2,color:#1b1a17
    style Reject2 fill:#ffcdd2,color:#1b1a17
    style Reject3 fill:#ffcdd2,color:#1b1a17

3.4 Validation Rules

Team Size Validation

Teams may be created in forming status with zero members (below the league’s minimumMembers threshold). The minimum size constraint is enforced only at these transition points:

Activating a team: Transition from forming → active requires memberCount >= minimumMembers
Registering for a season: Only active teams meeting size requirements can register

This allows teams to recruit members before becoming competition-eligible.

When activating a Team or registering for a Season, the team must meet the League’s size requirements:

memberCount := count(TeamMembership where teamID = team
                     AND isMostRecent = true AND leftAt is null)
league := team.league

isValid := memberCount >= league.minimumMembers
       AND (league.maximumMembers is null OR memberCount <= league.maximumMembers)

Realm Membership Constraint

For a user to join a Team, they must be a member of the League’s owning realm OR any descendant realm:

leagueRealmID := team.league.realmID

isEligible := exists(RealmMembership rm, RealmClosure rc
                     where rm.userID = user
                       AND rc.ancestorID = leagueRealmID
                       AND rc.descendantID = rm.realmID)

Captain Must Be Member

The captain must be a current (active) member of the team:

captainIsCurrentMember := exists(TeamMembership
                                 where teamID = team
                                   AND userID = candidateCaptain
                                   AND isMostRecent = true
                                   AND leftAt is null)

if not captainIsCurrentMember:
    reject "Captain must be a current team member"

Season Registration Window

Teams can only register for seasons during the registration window:

registrationOpen := season.registrationOpensDate is null
                 OR season.registrationClosesDate is null
                 OR (today >= season.registrationOpensDate
                     AND today <= season.registrationClosesDate)

if not registrationOpen:
    reject "Registration window is closed"

Play Team Participation (Critical Scoring Integrity)

Invariant: A Play can only be recorded for a team that is participating in the game.

The Plays table has independent FKs to Games and Teams, but scoring integrity requires that teamID exists in GameParticipation for the given gameID. Without this check, points could be credited to non-participating teams.

teamInGame := exists(GameParticipation where gameID = game AND teamID = team)

if not teamInGame:
    reject "TEAM_NOT_IN_GAME: Team is not a participant in this game"

This validation must also be enforced in:

api_playRecord_v1 — before inserting a new play
internal_recomputeGameScore — when recomputing, only sum plays for participating teams
api_playInvalidate_v1 — verify play belongs to a valid game/team combination

Play User Attribution

Invariant: If Plays.userID is set, the user must be a current member of the team.

The userID field is nullable to support different attribution scenarios:

Scenario	`userID`	Validation
Individual solve	Set	User must be current member of `teamID` (isMostRecent = true AND leftAt is null)
Team-based solve	NULL	No user validation needed
Admin adjustment	NULL or Set	If set, user must be current member; playType = ‘adjustment’

if userID is not null:
    isMember := exists(TeamMembership
                       where teamID = team
                         AND userID = user
                         AND isMostRecent = true
                         AND leftAt is null)

    if not isMember:
        reject "USER_NOT_TEAM_MEMBER: User is not a current member of the team"

Rationale: This ensures auditability for dispute resolution — every attributed play links to a verified team member at the time of the play. Historical membership records (via isMostRecent pattern) preserve the audit trail even if the user later leaves the team.

3.5 Enforcement Policy

Per ĀYŌDÈ platform standards, all business rules and constraints are enforced via stored procedures. Direct table access is prohibited; all writes go through api_* or proxied_* stored procedures.

Why Stored Procedure Enforcement?

Centralized authorization - Security checks happen in one place, not scattered across application code
Atomic transactions - Complex operations (e.g., accepting an invite) can validate constraints and update multiple tables atomically
Audit trail - All modifications pass through controlled entry points
No schema-level triggers - Avoids hidden side effects and simplifies debugging

Constraints Enforced by Stored Procedures

Constraint	Enforcement Point
Captain must be a team member	`api_teamSetCaptain_v1` validates membership before setting `captainUserID`
Team size within League bounds	`api_teamMemberAdd_v1` checks `minimumMembers`/`maximumMembers` before insert
User must be in Realm hierarchy	`api_teamMemberAdd_v1` validates via `RealmClosure` join
No active ban exists	`api_teamMemberAdd_v1` checks `TeamBans` where `isMostRecent = TRUE AND liftedAt IS NULL AND (expiresAt IS NULL OR expiresAt > NOW())`
Registration window is open	`api_seasonRegisterTeam_v1` validates dates before insert
At most one pending request per (teamID, userID)	`api_teamMembershipRequestCreate_v1` checks `isMostRecent = TRUE AND status = 'pending'`
Play team must be game participant	`api_playRecord_v1` validates `teamID` exists in `GameParticipation` for `gameID`
Play user must be team member (if set)	`api_playRecord_v1` validates `userID` is current member of `teamID` when not NULL

Membership Request Deduplication

To prevent duplicate/ambiguous membership requests, api_teamMembershipRequestCreate_v1 enforces:

Reject if pending exists: Check isMostRecent = TRUE AND status = 'pending' for (teamID, userID)
Mark prior as historical: Set isMostRecent = FALSE on existing record
Insert new request: Assign requestIndex = MAX(existing) + 1

3.6 Aggregation Maintenance

Scoring aggregates (GameParticipation.gameScore and SeasonParticipation.totalPoints) are derived from the authoritative source: the Plays table.

Source of Truth

flowchart LR
    Plays[(Plays)] -->|"authoritative"| GP[(GameParticipation.gameScore)]
    GP -->|"derived"| SP[(SeasonParticipation.totalPoints)]

Plays: The authoritative record of all scoring events
gameScore: Cached aggregate, updated transactionally
totalPoints: Cached aggregate, updated transactionally

Update Strategy: Transactional Stored Procedures

Aggregates are updated synchronously within the same transaction as the underlying change:

Operation	Procedure	Aggregate Update
Record a play	`api_playRecord_v1`	Recomputes `gameScore` for affected `GameParticipation`
Invalidate a play	`api_playInvalidate_v1`	Recomputes `gameScore` for affected `GameParticipation`
Complete a game	`api_gameComplete_v1`	Recomputes `totalPoints` for all teams in that game’s season

Recompute Logic (Idempotent)

For repairs, backfills, or after bulk imports, internal procedures recompute aggregates:

gameScore recomputation:

gameScore := sum(Plays.points
                 where gameID = game
                   AND teamID = team
                   AND invalidatedAt is null) ?? 0

totalPoints recomputation:

totalPoints := sum(GameParticipation.gameScore
                   where game.seasonID = season
                     AND teamID = team) ?? 0

Why Not Triggers?

ĀYŌDÈ platform standard prohibits triggers for business logic
Stored procedures provide explicit, auditable control flow
Easier to debug and test than implicit trigger chains
Recompute procedures can be called independently for repairs

3.7 API Endpoints

All endpoints follow existing ĀYŌDÈ API conventions:

Authentication via cognitoAuthentication
Realm assertion via X-Ayode-Asserted-Realm-EID header (or X-Ayode-Asserted-Realm-URN, X-Ayode-Asserted-Realm-Path)
Response envelope: { envelope: ResponseEnvelope, data: ... }
All entity identifiers use EID URNs (e.g., urn:ayode:league-eid:uuid)

Endpoint Summary

flowchart LR
    subgraph LeagueEndpoints["League Endpoints"]
        L1["/v1/leagues/{realm-eid}"]
        L2["/v1/leagues/{realm-eid}/{league-eid}"]
    end

    subgraph TeamEndpoints["Team Endpoints"]
        T1["/v1/leagues/{realm-eid}/{league-eid}/teams"]
        T2["/v1/teams/{team-eid}"]
        T3["/v1/teams/{team-eid}/members"]
        T4["/v1/teams/{team-eid}/requests"]
        T5["/v1/teams/{team-eid}/bans"]
    end

    subgraph SeasonEndpoints["Season Endpoints"]
        S1["/v1/leagues/{realm-eid}/{league-eid}/seasons"]
        S2["/v1/seasons/{season-eid}"]
        S3["/v1/seasons/{season-eid}/participants"]
    end

    subgraph GameEndpoints["Game Endpoints"]
        G1["/v1/seasons/{season-eid}/games"]
        G2["/v1/games/{game-eid}"]
        G3["/v1/games/{game-eid}/participants"]
        G4["/v1/games/{game-eid}/plays"]
    end

    L1 --> L2
    L2 --> T1
    T1 --> T2
    T2 --> T3 & T4 & T5
    L2 --> S1
    S1 --> S2
    S2 --> S3 & G1
    G1 --> G2
    G2 --> G3 & G4

League Endpoints

Method	Path	operationId	Description
GET	`/v1/leagues/{realm-eid}`	`listLeaguesByRealmV1`	List all leagues owned by the realm
POST	`/v1/leagues/{realm-eid}`	`createLeagueV1`	Create a new league in the realm
GET	`/v1/leagues/{realm-eid}/{league-eid}`	`getLeagueV1`	Get league details
PATCH	`/v1/leagues/{realm-eid}/{league-eid}`	`updateLeagueV1`	Update league settings
DELETE	`/v1/leagues/{realm-eid}/{league-eid}`	`deleteLeagueV1`	Archive/delete a league

Required Privileges:

Operation	Privilege URN
List leagues	`urn:ayode:privilege-action:/league/list`
Create league	`urn:ayode:privilege-action:/league/create`
Get league	`urn:ayode:privilege-action:/league/read`
Update league	`urn:ayode:privilege-action:/league/update`
Delete league	`urn:ayode:privilege-action:/league/delete`

Team Endpoints

Method	Path	operationId	Description
GET	`/v1/leagues/{realm-eid}/{league-eid}/teams`	`listTeamsByLeagueV1`	List all teams in a league
POST	`/v1/leagues/{realm-eid}/{league-eid}/teams`	`createTeamV1`	Create a new team
GET	`/v1/teams/{team-eid}`	`getTeamV1`	Get team details
PATCH	`/v1/teams/{team-eid}`	`updateTeamV1`	Update team settings
DELETE	`/v1/teams/{team-eid}`	`disbandTeamV1`	Disband a team

Team Membership:

Method	Path	operationId	Description
GET	`/v1/teams/{team-eid}/members`	`listTeamMembersV1`	List current members (or full history with `?history=true`)
POST	`/v1/teams/{team-eid}/members/{user-eid}`	`addTeamMemberV1`	Add a member directly (manager action)
DELETE	`/v1/teams/{team-eid}/members/{user-eid}`	`removeTeamMemberV1`	Remove a member (manager action, sets `leftAt`)
POST	`/v1/teams/{team-eid}/leave`	`leaveTeamV1`	Current user leaves the team (sets `leftAt`)
PATCH	`/v1/teams/{team-eid}/captain`	`setTeamCaptainV1`	Set team captain

Membership Requests (Invites/Applications):

Method	Path	operationId	Description
GET	`/v1/teams/{team-eid}/requests`	`listTeamMembershipRequestsV1`	List pending requests (or full history with `?history=true`)
POST	`/v1/teams/{team-eid}/requests`	`createTeamMembershipRequestV1`	Create invite or application
GET	`/v1/teams/{team-eid}/requests/{request-eid}`	`getTeamMembershipRequestV1`	Get request details
POST	`/v1/teams/{team-eid}/requests/{request-eid}/accept`	`acceptTeamMembershipRequestV1`	Accept the request
POST	`/v1/teams/{team-eid}/requests/{request-eid}/decline`	`declineTeamMembershipRequestV1`	Decline the request

Team Bans:

Method	Path	operationId	Description
GET	`/v1/teams/{team-eid}/bans`	`listTeamBansV1`	List active bans (or full history with `?history=true`)
POST	`/v1/teams/{team-eid}/bans`	`createTeamBanV1`	Ban a user from the team
POST	`/v1/teams/{team-eid}/bans/{user-eid}/lift`	`liftTeamBanV1`	Lift an active ban (sets `liftedAt`)

Required Privileges:

Operation	Privilege URN
List teams	`urn:ayode:privilege-action:/team/list`
Create team	`urn:ayode:privilege-action:/team/create`
Read team	`urn:ayode:privilege-action:/team/read`
Update team	`urn:ayode:privilege-action:/team/update`
Manage members	`urn:ayode:privilege-action:/team/manage`
Join team (apply)	`urn:ayode:privilege-action:/team/join`

Season Endpoints

Method	Path	operationId	Description
GET	`/v1/leagues/{realm-eid}/{league-eid}/seasons`	`listSeasonsByLeagueV1`	List all seasons in a league
POST	`/v1/leagues/{realm-eid}/{league-eid}/seasons`	`createSeasonV1`	Create a new season
GET	`/v1/seasons/{season-eid}`	`getSeasonV1`	Get season details
PATCH	`/v1/seasons/{season-eid}`	`updateSeasonV1`	Update season settings
DELETE	`/v1/seasons/{season-eid}`	`cancelSeasonV1`	Cancel a season

Season Participation (Registration):

Method	Path	operationId	Description
GET	`/v1/seasons/{season-eid}/participants`	`listSeasonParticipantsV1`	List registered teams
POST	`/v1/seasons/{season-eid}/participants`	`registerTeamForSeasonV1`	Register a team
GET	`/v1/seasons/{season-eid}/participants/{team-eid}`	`getSeasonParticipationV1`	Get participation details
DELETE	`/v1/seasons/{season-eid}/participants/{team-eid}`	`withdrawTeamFromSeasonV1`	Withdraw a team
GET	`/v1/seasons/{season-eid}/leaderboard`	`getSeasonLeaderboardV1`	Get ranked standings

Required Privileges:

Operation	Privilege URN
List seasons	`urn:ayode:privilege-action:/season/list`
Create season	`urn:ayode:privilege-action:/season/create`
Read season	`urn:ayode:privilege-action:/season/read`
Update season	`urn:ayode:privilege-action:/season/update`
Register team	`urn:ayode:privilege-action:/season/register`

Game Endpoints

Method	Path	operationId	Description
GET	`/v1/seasons/{season-eid}/games`	`listGamesBySeasonV1`	List all games in a season
POST	`/v1/seasons/{season-eid}/games`	`createGameV1`	Create a new game
GET	`/v1/games/{game-eid}`	`getGameV1`	Get game details
PATCH	`/v1/games/{game-eid}`	`updateGameV1`	Update game settings
POST	`/v1/games/{game-eid}/start`	`startGameV1`	Start the game
POST	`/v1/games/{game-eid}/complete`	`completeGameV1`	Mark game as completed
POST	`/v1/games/{game-eid}/cancel`	`cancelGameV1`	Cancel the game

Game Participation:

Method	Path	operationId	Description
GET	`/v1/games/{game-eid}/participants`	`listGameParticipantsV1`	List teams in the game
POST	`/v1/games/{game-eid}/participants`	`addGameParticipantV1`	Add a team to the game
DELETE	`/v1/games/{game-eid}/participants/{team-eid}`	`removeGameParticipantV1`	Remove a team
PATCH	`/v1/games/{game-eid}/participants/{team-eid}`	`updateGameParticipantV1`	Update outcome/placement

Required Privileges:

Operation	Privilege URN
List games	`urn:ayode:privilege-action:/game/list`
Create game	`urn:ayode:privilege-action:/game/create`
Read game	`urn:ayode:privilege-action:/game/read`
Update game	`urn:ayode:privilege-action:/game/update`
Manage participants	`urn:ayode:privilege-action:/game/manage`

Play Endpoints

Method	Path	operationId	Description
GET	`/v1/games/{game-eid}/plays`	`listPlaysByGameV1`	List all plays in a game
POST	`/v1/games/{game-eid}/plays`	`recordPlayV1`	Record a new play (scoring event)
GET	`/v1/plays/{play-eid}`	`getPlayV1`	Get play details
POST	`/v1/plays/{play-eid}/invalidate`	`invalidatePlayV1`	Invalidate a play

Query Parameters for listPlaysByGameV1:

Parameter	Type	Description
`teamEID`	string	Filter by team
`playType`	string	Filter by play type (solve, partial, bonus, etc.)
`valid`	boolean	Filter by validity (`true` = invalidatedAt IS NULL)
`limit`	integer	Pagination limit
`offset`	integer	Pagination offset

Required Privileges:

Operation	Privilege URN
List plays	`urn:ayode:privilege-action:/play/list`
Record play	`urn:ayode:privilege-action:/play/record`
Read play	`urn:ayode:privilege-action:/play/read`
Invalidate play	`urn:ayode:privilege-action:/play/invalidate`

User-Centric Endpoints

For querying from the user’s perspective:

Method	Path	operationId	Description
GET	`/v1/users/{user-eid}/teams`	`listUserTeamsV1`	List teams the user is a member of
GET	`/v1/users/{user-eid}/team-requests`	`listUserTeamRequestsV1`	List pending invites/applications for user

Response Schemas

League Response:

{
  "envelope": { "status": "success", "requestID": "..." },
  "data": {
    "eidURN": "urn:ayode:league-eid:uuid",
    "externalID": "uuid",
    "name": "Flight Path 2026",
    "description": "...",
    "minimumMembers": 2,
    "maximumMembers": 5,
    "status": "active",
    "realmEIDURN": "urn:ayode:realm-eid:uuid",
    "teamCount": 42,
    "createdTimestamp": "2026-02-25T12:00:00.000000Z"
  }
}

Team Response:

{
  "envelope": { "status": "success", "requestID": "..." },
  "data": {
    "eidURN": "urn:ayode:team-eid:uuid",
    "externalID": "uuid",
    "name": "Code Crusaders",
    "description": "...",
    "status": "active",
    "leagueEIDURN": "urn:ayode:league-eid:uuid",
    "managerUserEIDURN": "urn:ayode:user-eid:uuid",
    "captainUserEIDURN": "urn:ayode:user-eid:uuid",
    "memberCount": 4,
    "createdTimestamp": "2026-02-25T12:00:00.000000Z"
  }
}

Play Response:

{
  "envelope": { "status": "success", "requestID": "..." },
  "data": {
    "eidURN": "urn:ayode:play-eid:uuid",
    "externalID": "uuid",
    "gameEIDURN": "urn:ayode:game-eid:uuid",
    "teamEIDURN": "urn:ayode:team-eid:uuid",
    "userEIDURN": "urn:ayode:user-eid:uuid",
    "playIndex": 6,
    "playType": "solve",
    "points": 100,
    "occurredAt": "2026-02-25T14:32:15.123456Z",
    "description": "Solved challenge: Binary Exploitation 101",
    "invalidatedAt": null
  }
}

4. Security Considerations

Authorization

All operations require appropriate privileges. Privileges are scoped to the asserted realm and may apply to descendant realms based on configuration.

Privilege Scope Reference

The scope column indicates the breadth of access granted by the privilege:

self: Action applies to the user’s own resources (e.g., their own team membership, their team’s plays)
realm: Action applies to all resources within the realm (administrative access)

Some privileges appear with multiple scopes, indicating different access levels for regular users vs. administrators.

Privilege URN	Scope	Purpose
`urn:ayode:privilege-action:/league/create`	`realm`	Create new leagues within a realm (admin)
`urn:ayode:privilege-action:/league/read`	`self`	View leagues user participates in
`urn:ayode:privilege-action:/league/read`	`realm`	View any league in the realm (admin)
`urn:ayode:privilege-action:/league/update`	`realm`	Modify league settings (admin)
`urn:ayode:privilege-action:/league/delete`	`realm`	Remove leagues (admin)
`urn:ayode:privilege-action:/league/list`	`self`	List leagues user participates in
`urn:ayode:privilege-action:/league/list`	`realm`	List all leagues in the realm (admin)
`urn:ayode:privilege-action:/team/create`	`realm`	Create new teams within a realm
`urn:ayode:privilege-action:/team/read`	`self`	View user’s own team details
`urn:ayode:privilege-action:/team/read`	`realm`	View any team in the realm (admin)
`urn:ayode:privilege-action:/team/update`	`self`	Modify user’s own team settings (captain)
`urn:ayode:privilege-action:/team/update`	`realm`	Modify any team’s settings (admin)
`urn:ayode:privilege-action:/team/delete`	`self`	Remove user’s own team (captain)
`urn:ayode:privilege-action:/team/delete`	`realm`	Remove any team (admin)
`urn:ayode:privilege-action:/team/list`	`self`	List teams user is a member of
`urn:ayode:privilege-action:/team/list`	`realm`	List all teams in the realm (admin)
`urn:ayode:privilege-action:/team/join`	`self`	Request to join a team (self only)
`urn:ayode:privilege-action:/team/leave`	`self`	Leave a team (self only)
`urn:ayode:privilege-action:/team/manage`	`self`	Manage user’s own team roster (captain)
`urn:ayode:privilege-action:/team/manage`	`realm`	Manage any team’s roster (admin)
`urn:ayode:privilege-action:/season/create`	`realm`	Create new seasons (admin)
`urn:ayode:privilege-action:/season/read`	`self`	View seasons user participates in
`urn:ayode:privilege-action:/season/read`	`realm`	View any season in the realm (admin)
`urn:ayode:privilege-action:/season/update`	`realm`	Modify season configuration (admin)
`urn:ayode:privilege-action:/season/delete`	`realm`	Remove seasons (admin)
`urn:ayode:privilege-action:/season/list`	`self`	List seasons user participates in
`urn:ayode:privilege-action:/season/list`	`realm`	List all seasons in the realm (admin)
`urn:ayode:privilege-action:/game/create`	`realm`	Schedule games (admin)
`urn:ayode:privilege-action:/game/read`	`self`	View games user’s team participates in
`urn:ayode:privilege-action:/game/read`	`realm`	View any game in the realm (admin)
`urn:ayode:privilege-action:/game/update`	`realm`	Modify game configuration (admin)
`urn:ayode:privilege-action:/game/delete`	`realm`	Remove scheduled games (admin)
`urn:ayode:privilege-action:/play/create`	`self`	Record plays for user’s own team
`urn:ayode:privilege-action:/play/create`	`realm`	Record plays for any team (admin)
`urn:ayode:privilege-action:/play/read`	`self`	View plays of user’s own team
`urn:ayode:privilege-action:/play/read`	`realm`	View plays of any team (admin)

League Privileges

Privilege URN	Description
`urn:ayode:privilege-action:/league/list`	List leagues in a realm
`urn:ayode:privilege-action:/league/create`	Create a new league
`urn:ayode:privilege-action:/league/read`	View league details
`urn:ayode:privilege-action:/league/update`	Modify league settings
`urn:ayode:privilege-action:/league/delete`	Archive/delete a league

Team Privileges

Privilege URN	Description
`urn:ayode:privilege-action:/team/list`	List teams in a league
`urn:ayode:privilege-action:/team/create`	Create a new team
`urn:ayode:privilege-action:/team/read`	View team details
`urn:ayode:privilege-action:/team/update`	Modify team settings (name, description)
`urn:ayode:privilege-action:/team/manage`	Add/remove members, set captain, ban users
`urn:ayode:privilege-action:/team/join`	Apply to join a team

Season Privileges

Privilege URN	Description
`urn:ayode:privilege-action:/season/list`	List seasons in a league
`urn:ayode:privilege-action:/season/create`	Create a new season
`urn:ayode:privilege-action:/season/read`	View season details
`urn:ayode:privilege-action:/season/update`	Modify season settings
`urn:ayode:privilege-action:/season/register`	Register a team for a season

Game Privileges

Privilege URN	Description
`urn:ayode:privilege-action:/game/list`	List games in a season
`urn:ayode:privilege-action:/game/create`	Create a new game
`urn:ayode:privilege-action:/game/read`	View game details
`urn:ayode:privilege-action:/game/update`	Modify game settings, start/complete game
`urn:ayode:privilege-action:/game/manage`	Add/remove game participants

Play Privileges

Privilege URN	Description
`urn:ayode:privilege-action:/play/list`	List plays in a game
`urn:ayode:privilege-action:/play/record`	Record a new play (scoring event)
`urn:ayode:privilege-action:/play/read`	View play details
`urn:ayode:privilege-action:/play/invalidate`	Invalidate/void a play

Authorization Realm Resolution

For endpoints where the realm is not in the path (e.g., /v1/teams/{team-eid}), the authorization realm must be resolved from the entity hierarchy. The table below specifies the target realm for privilege checks and whether descendant realms are permitted.

Entity	Target Realm Resolution	Depth
League	`League.realmID`	Self only
Team	`Team → League.realmID`	Self only
TeamMembership	`Team → League.realmID`	Self + descendants (for `/team/join`)
TeamMembershipRequest	`Team → League.realmID`	Self + descendants
TeamBan	`Team → League.realmID`	Self only
Season	`Season → League.realmID`	Self only
SeasonParticipation	`Season → League.realmID`	Self only
Game	`Game → Season → League.realmID`	Self only
GameParticipation	`Game → Season → League.realmID`	Self only
Play	`Play → Game → Season → League.realmID`	Self only

Resolution logic:

All entities ultimately resolve to the League’s owning realm (Leagues.realmID)
The asserted realm header (X-Ayode-Asserted-Realm-EID) must match the target realm or be an ancestor (if depth = “Self + descendants”)
Stored procedures join through the entity hierarchy to resolve the target realm

Example: For GET /v1/teams/{team-eid}:

targetRealmID := team.league.realmID
assertedRealmID := request.header("X-Ayode-Asserted-Realm-EID")

verify assertedRealmID matches targetRealmID (or is ancestor if descendants allowed)
check privilege: /team/read

Special cases:

/team/join allows descendants because users from child realms may join teams in parent realm leagues
/team/manage is restricted to self (league realm admins only)

Realm Boundary Enforcement

Team members MUST be members of the League’s realm or a descendant realm
This is enforced at the stored procedure level using RealmClosure
Prevents cross-realm team participation

Ban Enforcement

Before adding a user to TeamMembership, check TeamBans
Active bans: expiresAt IS NULL OR expiresAt > CURRENT_TIMESTAMP(6)

5. Backward Compatibility

This RFC introduces new tables and does not modify existing schema. All changes are additive:

✅ No changes to existing tables (Users, Realms, RealmMembership, etc.)
✅ No changes to existing stored procedures
✅ No changes to existing API endpoints
✅ New tables follow existing conventions (triple-UUID, audit columns)

6. References

Internal Documentation

ARFC-1003: Flight Path Endpoints

External Standards

RFC 4122: A Universally Unique Identifier (UUID) URN Namespace

Summary of Tables

Table	Purpose	Key Relationships
`Leagues`	Competition containers with team size rules	Belongs to Realm
`Teams`	Groups of users competing	Belongs to League
`TeamMembership`	Users in teams (with join/leave history)	Team ↔ User
`TeamMembershipRequests`	Invites and applications (with history)	Team ↔ User
`TeamBans`	Banned users (with ban/lift history)	Team ↔ User
`Seasons`	Competitive time periods	Belongs to League
`SeasonParticipation`	Teams in seasons	Season ↔ Team
`Games`	Individual matches	Belongs to Season
`GameParticipation`	Teams in games	Game ↔ Team
`Plays`	Atomic scoring events	Belongs to Game, Team, optionally User

Author

ĀYŌDÈ Development Team Codermerlin Academy Architecture

ARFC-1004: Teams, Leagues, Seasons, and Competitive Gaming Schema

Status

Abstract

Table of Contents

1. Introduction

2. Motivation

Current State

Missing Capabilities

Design Goals

3. Specification

3.1 Schema Overview

3.2 Table Definitions

Leagues

Teams

TeamMembership

TeamMembershipRequests

TeamBans

Seasons

SeasonParticipation

Games

GameParticipation

Plays

3.3 Relationship Diagrams

Entity Relationship Diagram

Scoring Flow

Team Lifecycle

Season Lifecycle

Membership Request Workflow

Realm Membership Validation

3.4 Validation Rules

Team Size Validation

Realm Membership Constraint

Captain Must Be Member

Season Registration Window

Play Team Participation (Critical Scoring Integrity)

Play User Attribution

3.5 Enforcement Policy

Why Stored Procedure Enforcement?

Constraints Enforced by Stored Procedures

Membership Request Deduplication

3.6 Aggregation Maintenance

Source of Truth

Update Strategy: Transactional Stored Procedures

Recompute Logic (Idempotent)

Why Not Triggers?

3.7 API Endpoints

Endpoint Summary

League Endpoints

Team Endpoints

Season Endpoints

Game Endpoints

Play Endpoints

User-Centric Endpoints

Response Schemas

4. Security Considerations

Authorization

Privilege Scope Reference

League Privileges

Team Privileges

Season Privileges

Game Privileges

Play Privileges

Authorization Realm Resolution

Realm Boundary Enforcement

Ban Enforcement

5. Backward Compatibility

6. References

Internal Documentation

External Standards

Summary of Tables

Author