---
title: Add an nfl_franchise_seasons table for historical team display fidelity
type: task
status: planned
description: >-
  Production team codes are retroactively normalized to current franchises, so historical rows
  render under the wrong name; add a season-keyed franchise display lookup layered on top of the
  normalized join key.
base_uri: user:task/league/data-views/build-nfl-franchise-seasons-table.md
created_at: '2026-08-05T18:50:58.270Z'
entity_id: fb586e2b-dc4e-4d9d-bc3c-2a8adecdf987
observations:
  - ████████████████████████████████████████████████
owner_identity_uri: █████████████████████████
public_read: true
tags:
  - user:tag/league-xo-football.md
  - user:tag/software-task.md
updated_at: '2026-10-14T17:52:01.314Z'
visibility_analyzed_at: '2026-11-05T23:48:44.499Z'
---

# nfl_franchise_seasons table

## Problem

The production database is retroactively normalized to current team codes: `nfl_plays` has no `OAK`, and every Raiders play back to 1970 is `LV`. That keeps joins simple but makes historical display wrong. A 1995 Raiders row shows as Las Vegas, and there is no way to render the name, city, or logo a franchise actually carried in a given season.

There is also no `nfl_teams` master table at all. Team data is keyed by `nfl_team` on `nfl_team_seasonlogs` / `nfl_team_gamelogs`, and the data-views team row grain builds its FROM source from an inline `VALUES` CTE generated from `libs-shared/constants/nfl-teams-constants.mjs`.

## Scope

Add an `nfl_franchise_seasons` table giving each (franchise, season) its display identity, and use it for display only — the normalized team code stays the join key everywhere.

## Plan

- [ ] **Design the schema**
  - Columns: franchise key, `year`, `team_code` (the normalized current code), historical abbreviation, city, nickname, full name, conference, division, logo/color references.
  - Purpose: One row per franchise per season. The normalized `team_code` remains the join key; the historical abbreviation is display-only.

- [ ] **Source and seed the data**
  - Purpose: Populate every season the database covers (1970 onward). Identify a source and record it in the adhoc migration.

- [ ] **Add the migration and export the schema**
  - Files: `db/adhoc/YYYY-MM-DD-add-nfl-franchise-seasons.sql`, `db/schema.postgres.sql`
  - Purpose: Follow the league schema-change workflow — `yarn db:exec`, then `yarn export:schema`, commit the schema diff with the adhoc file.

- [ ] **Wire display resolution**
  - Purpose: Resolve a (team_code, year) pair to its season-accurate display identity in the data-views team row grain and anywhere else team names render against a historical year.

## Notes

- Deferred from [[█████████████████████-█████████████-██-████████-████████]] as a display-fidelity concern explicitly out of that plan's scope.
- Do not denormalize historical codes back into `nfl_plays`. The normalization is load-bearing for every join in the query builder; this table is a display lookup layered on top.
- A franchise-history tab in [[█████████████████████-███████████-████████-████-████████]] depends on this table.