# mcla-open.sqlite: data dictionary

A single SQLite 3 file. Every table is plain, with no views, triggers or FTS. Identifiers are
already **canonical**: every `player_id` is a canonical player id and every `team_slug` /
`*_slug` is a canonical team slug, so a naive `JOIN` works without any alias lookup.

Row counts are approximate and come from the 2026 build. Check `dataset_meta.generated_utc` for the build date.

## Conventions

| Thing | Meaning |
|---|---|
| `year` | Season year (spring season). The MCLA season runs Feb–May, so `year` = calendar year of play. |
| team slug | Stable text id for a program, e.g. `brigham-young`, `colorado-state`. Treat it as opaque. Some canonical slugs use underscores (`utah_valley`) and others use hyphens; both forms are valid ids. Joins to `teams.slug`. |
| player id | Stable text id. There are three forms: `<name>-<6 hex>` (an mcla.us player page id, e.g. `mitchell-adkins-858c67`), `arc-<n>` (an archive.mcla.us-only player with no mcla.us page), and `aa-<name>-<team>` (a synthetic record for a pre-2006 honoree who has awards but no stats). Joins to `players.id`. |
| game id | Text. mcla.us games use a UUID (`0193a2e0-…`). Legacy USLIA games use `uslia-<year>-<mmdd>-<away>-vs-<home>`, and bracket-only backfills use `tg_<…>`. |
| `NULL` | Not recorded upstream. Do not read it as zero. |
| percentages | `faceoff_pct`, `save_pct` are 0–100 (e.g. `38.5`), not 0–1. |

### Code legends

**Division** (`team_seasons.division`, `polls.division`, `tournament_games.division`)

| code | meaning |
|---|---|
| `d1` | MCLA Division I |
| `d2` | MCLA Division II |
| `d3` | MCLA Division III (a few seasons only) |
| empty / NULL | unlabeled. Common before ~2005 and for non-MCLA opponents. |

`player_awards.division` uses the honors-page labels `MCLA-1`, `MCLA-2`, `MCLA-3` (= d1/d2/d3).
It is empty on a few rows. `teams.division_label` is a display string such as "Division I".

**Game type** (`games.game_type` / `games.game_type_label`)

| game_type | game_type_label | meaning / era |
|---|---|---|
| `R` | MCLA Game | regular-season MCLA game |
| `D` | Divisional | regular-season game vs a divisional opponent |
| `CT` | Conf. Tourney | conference tournament game |
| `P` | Playoff | post-season playoff (conference) |
| `NT` | National Tournament | MCLA national championship tournament |
| `S` | Scrimmage | scrimmage (counts toward no record) |
| `N/A` | N/A | type not given by the source (2006–2015) |
| `T` | (raw note) | USLIA era (2000–2005), where the source notes flagged a tournament game |
| NULL / empty | NULL or a raw USLIA note (`N`, `*`) | USLIA era (1997–2005), untyped |

**Game status** (`games.status`): `FINAL`, `F/OT` or `FINAL/OT` (overtime; the latter on USLIA-era games), `Cancelled`,
`Postponed`, `No Contest`. Unplayed games carry a time label (`7pm EDT`). `played` = 1 only
for games with a final score.

**Player position** (`players.position`): `Attack`, `Midfield`, `Defense`, `Goalie`,
`LSM` (long-stick midfielder), `SSDM` (short-stick defensive midfielder), `FOS`
(face-off specialist), `unknown`, or NULL. NULL is common for pre-2015 archive players.

**Position group** (`game_player_stats.position_group`): the same vocabulary (`Attack`,
`Midfield`, `SSDM`, `LSM`, `Defense`, `FOS`, `Goalie`). It is only populated 2015+, and NULL for
archive-sourced lines (2006–2014).

**Award position** (`player_awards.position`): `A` attack, `M` midfield, `D` defense,
`G` goalie, `LSM` long-stick midfielder, `SSDM` short-stick defensive midfielder,
`FO` face-off. It is NULL when the source gave no position.

**Award team level** (`player_awards.team_level`)

| value | meaning |
|---|---|
| 1 | First team |
| 2 | Second team |
| 3 | Third team |
| 4 | Honorable mention (HM) |

**Award** (`player_awards.award`): `all-american`, `all-conference`.

**Team award kind** (`team_awards.kind`): `national_championship`, `national_runner_up`,
`conference_championship`.

**Tournament round** (`tournament_games.round` / `round_order`): `First Round` = 1,
`Quarterfinals` = 2, `Semifinals` = 3, `Finals` = 4.

**Poll week** (`polls.week_key`): `preseason`, `week-1` … `week-10`, `final`. Some seasons
(2000–2008, 2020–2024) use bare numbers `1` … `10` for the weekly polls. Treat `N` and
`week-N` as the same week. **`polls.change_dir`**: `up`, `down`, `none`, `new`, or NULL.

**Eligibility** (`game_player_stats.eligibility`): class year as printed by mcla.us
(`1st`, `2nd`, `3rd`, `4th`, `5th`, `6th`). `0th` appears on a few 2015 rows and is an
upstream artifact. Populated 2015+ only.

---

## Tables

### `dataset_meta`
Key/value metadata about the build.

| column | type | description |
|---|---|---|
| key | TEXT PK | `dataset`, `generated_utc`, `homepage`, `download`, `license` (= `CC BY 4.0`), `license_url`, `license_legalcode`, `attribution`, `attribution_notice`, `rights_note`, `source`, `ids`, `player_team_slugs`, `coverage`, `caveats`, `docs` |
| value | TEXT | value |

### `conferences` (~9 rows)
| column | type | description |
|---|---|---|
| slug | TEXT | conference id (`alc`, `clc`, `lsa`, `pncll`, `rmlc`, `selc`, `slc`, `umlc`, `wcll`) |
| name | TEXT | full name, e.g. "Western Collegiate Lacrosse League" |

### `seasons` (~28 rows)
| column | type | description |
|---|---|---|
| year | INTEGER | a season present in the source game logs |

### `teams` (~680 rows)
One row per canonical program. This includes non-MCLA opponents and pre-MCLA (USLIA) programs.

| column | type | description |
|---|---|---|
| slug | TEXT | canonical team slug (primary key) |
| name | TEXT | latest display name |
| mascot | TEXT | mascot / nickname |
| location | TEXT | "City, ST" |
| logo_url | TEXT | path to the crest on virtuallyvarsity.com (`/assets/logos/…`). The logos are trademarks of their owners and are **not** covered by the dataset licence. |
| established_year | INTEGER | founding year, where known |
| division_label | TEXT | current division display label |
| conference_label | TEXT | current conference display label |
| latitude, longitude | REAL | campus coordinates (OpenStreetMap Nominatim geocode; see PROVENANCE) |

### `team_seasons` (~5.9k rows)
One row per team per season. Conference, division and record all change over time, so they live here.

| column | type | description |
|---|---|---|
| year | INTEGER | season |
| team_slug | TEXT | → `teams.slug` |
| conference_slug | TEXT | → `conferences.slug`. NULL/empty before ~2005 and for independents. |
| division | TEXT | `d1` / `d2` / `d3` / empty |
| sub_division | TEXT | free-text sub-grouping, e.g. "SELC DIV II Independent" |
| head_coach | TEXT | head coach that season, where known |
| wins, losses, ties | INTEGER | season record (repaired against the game log where standings were wrong) |
| players | INTEGER | roster size, where known |

### `games` (~29.5k rows, 1997–2026)
| column | type | description |
|---|---|---|
| id | TEXT | game id (see Conventions) |
| year | INTEGER | season |
| date | TEXT | ISO `YYYY-MM-DD` |
| time_label | TEXT | scheduled start label, for unplayed games only |
| status | TEXT | see legend |
| played | INTEGER | 1 = has a final score, 0 = not played / upcoming |
| home_slug, away_slug | TEXT | canonical team slugs |
| home_score, away_score | INTEGER | final score (NULL if unplayed) |
| home_rank, away_rank | INTEGER | poll rank shown at game time, if any |
| venue_slug, venue_name | TEXT | venue |
| game_type, game_type_label | TEXT | see legend |
| url | TEXT | source page. A relative path is on `https://mcla.us`, and `https://www.uslia.net/...` marks a legacy game. |

### `game_box_score` (~38.5k rows, 2006+)
Scoring by period, one row per team per game. It is stored only when the source has a real
per-period breakdown.

| column | type | description |
|---|---|---|
| game_id | TEXT | → `games.id` |
| team_slug | TEXT | canonical team slug |
| is_home | INTEGER | 1 home / 0 away |
| q1, q2, q3, q4 | INTEGER | goals per quarter |
| ot | INTEGER | all overtime goals combined (MCLA reports one OT column) |
| total | INTEGER | final goals. This is **authoritative**: about 2.3k old rows have quarters that don't sum to it. |

### `game_team_stats` (~17.3k rows, 2015+)
| column | type | description |
|---|---|---|
| game_id | TEXT | → `games.id` |
| team_slug | TEXT | canonical team slug |
| is_home | INTEGER | 1 home / 0 away |
| goals | INTEGER | goals scored |
| goals_against | INTEGER | goals allowed |
| ground_balls | INTEGER | ground balls |
| faceoff_pct | REAL | face-off win % (0–100) |
| saves | INTEGER | saves |
| save_pct | REAL | save % (0–100) |

### `game_player_stats` (~832k rows, 2006+)
One stat line per player per game. A player can have two lines in a game under different
`position_group`s (e.g. Midfield + FOS). Pure-bench all-zero lines are omitted.

| column | type | description |
|---|---|---|
| game_id | TEXT | → `games.id` |
| team_slug | TEXT | the player's team for this game: the game's canonical `home_slug` or `away_slug`, chosen by `is_home`. It is NULL when the side could not be determined unambiguously (a few dozen lines in 4 games). |
| is_home | INTEGER | 1 = player was on the home team, 0 = away, NULL = unresolved |
| source_team_slug | TEXT | the raw team slug from the mcla.us roster tab, kept for traceability. It is often **not** a `teams.slug` (e.g. `brigham_young`, `grand-valley-state-university-d58bbc`, `lsu`), so join on `team_slug`, not on this column. |
| player_id | TEXT | → `players.id` (canonical) |
| player_name | TEXT | name as printed on the box score |
| jersey | INTEGER | jersey number |
| eligibility | TEXT | class year (see legend). 2015+ only. |
| position_group | TEXT | see legend. 2015+ only. |
| goals, assists, points | INTEGER | scoring |
| shots | INTEGER | shots |
| ground_balls | INTEGER | ground balls |
| penalties | INTEGER | penalties |
| caused_turnovers | INTEGER | caused turnovers |
| fo_won, fo_lost | INTEGER | face-offs won / lost |
| goals_against | INTEGER | goals allowed (goalies) |
| saves | INTEGER | saves (goalies) |
| save_pct | REAL | save % (0–100, goalies) |

### `players` (~67k rows)
Canonical identities only. Retired duplicate ids appear in `player_id_crosswalk`, not here.

| column | type | description |
|---|---|---|
| id | TEXT | canonical player id |
| name | TEXT | display name |
| position | TEXT | see legend |

### `player_seasons` (~118k rows, 2006+)
Season totals, one row per player per season per team (a mid-season transfer has two rows).

| column | type | description |
|---|---|---|
| player_id | TEXT | → `players.id` |
| year | INTEGER | season |
| team_slug | TEXT | canonical team slug. A small residue (~200 rows) still carries a truncated upstream slug (`st`, `southern`, `illinois`…) where it couldn't be resolved unambiguously (see PROVENANCE). |
| jersey | INTEGER | jersey number |
| gp | INTEGER | games played. **Pre-2015 values are a floor**, not exact. |
| gb | INTEGER | ground balls |
| shots | INTEGER | shots |
| goals, assists | INTEGER | scoring (points = goals + assists) |
| saves | INTEGER | saves |
| turnovers | INTEGER | turnovers (as reported upstream) |
| fo_won, fo_lost | INTEGER | face-offs won / lost. Face-off losses are largely untracked before ~2013. |

### `player_awards` (~15k rows, 2000–present)
All-American and All-Conference honors.

| column | type | description |
|---|---|---|
| player_id | TEXT | → `players.id`, or NULL if the honoree could not be linked to a player |
| player_name | TEXT | "First Last" |
| team_slug | TEXT | canonical team slug, or NULL if the school could not be resolved |
| school_name | TEXT | school label as printed by the source |
| year | INTEGER | season |
| division | TEXT | `MCLA-1` / `MCLA-2` / `MCLA-3` / empty |
| award | TEXT | `all-american` / `all-conference` |
| team_level | INTEGER | 1/2/3 = first/second/third team, 4 = honorable mention |
| position | TEXT | see legend |
| source | TEXT | provenance: `archive-mcla`, `allamerica-csv`, `laxpower-wayback`, `mcla-us` (see PROVENANCE) |

### `team_awards` (~600 rows)
| column | type | description |
|---|---|---|
| team_slug | TEXT | canonical team slug |
| kind | TEXT | see legend |
| year | INTEGER | season |
| label | TEXT | optional custom label |
| detail | TEXT | division or conference + division, e.g. `D1`, `WCLL D2` |
| source | TEXT | `auto` (derived from national-tournament finals) or `wikipedia` (conference titles) |

### `tournament_games` (~680 rows, 1997–present)
National tournament brackets with seeds.

| column | type | description |
|---|---|---|
| id | TEXT | bracket game id, e.g. `2026-d1-firstround-1` |
| year | INTEGER | season |
| division | TEXT | `d1` / `d2` |
| round | TEXT | see legend |
| round_order | INTEGER | 1–4 (see legend) |
| game_in_round | INTEGER | bracket position within the round |
| winner_slug, loser_slug | TEXT | canonical team slugs |
| winner_seed, loser_seed | INTEGER | tournament seed |
| winner_score, loser_score | INTEGER | final score |
| ot | INTEGER | 1 = decided in overtime |
| winner_conf, loser_conf | TEXT | conference abbreviation at the time (e.g. `RMLC`, `WCLL`) |
| game_id | TEXT | → `games.id` for the matching game-log row |

### `polls` (~8.6k rows; D1 2000+, D2 2004+)
| column | type | description |
|---|---|---|
| year | INTEGER | season |
| division | TEXT | `d1` / `d2` |
| week_key | TEXT | see legend |
| rank | INTEGER | poll rank |
| team_slug | TEXT | canonical team slug |
| team_name | TEXT | team name as printed in the poll |
| record | TEXT | record at poll time, e.g. "8-1" (not present in USLIA-era polls) |
| points | INTEGER | poll points |
| first_place_votes | INTEGER | first-place votes |
| prev_rank | INTEGER | previous week's rank |
| change_dir | TEXT | see legend |

### `player_id_crosswalk` (~47k rows)
Optional. Maps retired mcla.us player ids to their canonical id. mcla.us re-issues the same
real player under many hash ids. You only need this table if you hold mcla.us ids from
elsewhere.

| column | type | description |
|---|---|---|
| old_id | TEXT | retired id |
| canonical_id | TEXT | → `players.id` |

### `team_slug_crosswalk` (~55 rows)
Optional. Maps retired team slugs (renames, the 2025 mcla.us slug migration, merged
duplicate programs) to the canonical slug.

| column | type | description |
|---|---|---|
| old_slug | TEXT | retired slug |
| canonical_slug | TEXT | → `teams.slug` |

## Indexes

`player_seasons(player_id)`, `player_seasons(team_slug, year)`, `game_player_stats(game_id)`,
`game_player_stats(player_id)`, `game_player_stats(team_slug)`, `games(id)` (unique),
`games(year)`, `games(home_slug)`, `games(away_slug)`, `team_seasons(team_slug)`,
`player_awards(player_id)`.