---
title: Retype player.date_of_birth from varchar(10) to date
type: task
status: planned
description: >-
  player.date_of_birth is varchar(10) with a 0000-00-00 sentinel; the end state is a date column
  with NULL for unknown. The danger is silent JS coercion, not SQL errors: player-era.mjs regexes
  the column as a string and a Date fails that regex, sending every player down the fallback path
  that once deleted 450 gamelogs. Off-oracle — the conformance gate does not measure it.
base_uri: user:task/league/retype-player-date-of-birth-to-date.md
created_at: '2026-08-04T21:18:06.408Z'
entity_id: dc701843-4745-41f7-bedf-ef861a1b541e
observations:
  - ████████████████████████████████████████████████
  - ████████████████████████████████████████████████
  - ████████████████████████████████████████████████
  - ████████████████████████████████████████████████
  - ████████████████████████████████████████████████
owner_identity_uri: █████████████████████████
priority: High
public_read: true
relations:
  - relates [[█████████████████████████-██████-████████-█████████]]
  - follows [[user:guideline/nfl/league/database-schema-standards.md]]
  - relates [[████████████████████████-█████████-██████-██-█████████]]
tags:
  - user:tag/league-xo-football.md
  - user:tag/software-task.md
updated_at: '2026-10-09T17:59:04.069Z'
visibility_analyzed_at: '2026-11-06T00:18:03.604Z'
---

# Retype player.date_of_birth from varchar(10) to date

## Overview

`player.date_of_birth` is `varchar(10)` holding a date, with `0000-00-00` as a MySQL-era sentinel for unknown. The end state is a real `date` column with `NULL` for unknown.

This is **item 3 of the identity-crosswalk cluster**, deferred out of the 2026-07-21 expand because a compat view preserves column *names*, not *types* — so the retype was never view-shielded and would have broken its consumers the instant it committed.

**The conformance gate does not measure this.** `audit-schema-conformance.mjs` flags seven named classes and none of them cover a varchar holding a date, so this task will never surface through the program's completion oracle. It needs an owner precisely because the gate is blind to it.

## The trap — read before writing any DDL

**The dangerous breakage is silent JS-side coercion, not the loud SQL.**

Postgres rejects `TO_DATE(date)`, a `~` regex against a `date`, and every `0000-00-00` literal immediately. Those sites are safe *because* they fail at once — the suite catches them.

Three JavaScript sites instead change meaning with **no error at all**:

1. **`libs-server/player-era.mjs:109`** (line as of 2026-10-09) — the worst. It parses the column with `/^(\d{4})-/` against `String(value)`. A `Date` stringifies to `Mon Jan 01 1990 ...` and fails that regex, so **every player** falls through to `nfl_draft_year`. That is the exact path that deleted 450 real gamelogs in league `8f4292e08`. A retype that ships without rewriting this file **in the same commit** re-arms that defect and reports success.
2. **`scripts/backfill-players-from-nflverse-weekly-rosters.mjs:247`** — compares a `Date` to a CSV string, so `dob_agrees` goes permanently false and silently stops authorizing external-ID writes.
3. **`libs-server/merge-player.mjs`** — field merge branches on `typeof string`/`number`, so a `Date` always takes the `update_player_row` path.

`player-era.mjs` did not exist at the 2026-07-21 deferral and is now the single most important reader of this column. Any plan written against the deferral's consumer list is stale.

## Assumptions

Two corrections to what the 2026-07-21 deferral assumed — both verified 2026-08-04, do not re-inherit the originals:

- **Blast radius did NOT shrink.** The frontend still reads `date_of_birth` at six sites: `player-age.js`, `selected-player.js:315`, `auction-nominated-player.js:55`, `draft.js:253`, `player-table-fields.js:144`. An earlier claim that these were gone came from a grep run in the wrong directory.
- **`nfl_coaches` is only half the prior art.** It took the `date` type but *kept a sentinel* — 7 rows at `1900-01-01`, zero NULLs — because Postgres rejects `0000-00-00`. Reusing `1900-01-01` carries the sentinel through the retype and defeats the point. The end state is `NULL`.

## Design

**Re-measured 2026-10-09, and the data is no longer in two states.** `player` has 29,144 rows in four: **26,632 well-formed**, **2,080 `0000-00-00`**, **317 NULL** and **115 empty string**. The 2026-08-04 reading (28,166 rows, 25,809 well-formed, 2,357 sentinel, zero NULLs, zero empty strings) is superseded. Writers now emit NULL and `''` alongside the sentinel, so the cast must map all three unknown forms to `NULL`, and Phase 1 should find which writer produces the empty string.

The original reading put the real minimum year at 1910, so `player-era.mjs`'s `year > 1900` guard is only ever screening the sentinel. The risk is still in the consumers, not the data.

Two dob-as-identity remnants were already removed in league `452ac38e5` (a merge tiebreaker and a stale stub-pid warning), so the sweep has two fewer consumers than the deferral recorded.

## Notes

- Renames and type changes hold the production-DDL apply slot **through consumer commit**, granted by `user:orchestrator/league-schema-redesign/league-schema-redesign.md`.
- No backwards-compatibility shims (operator ruling 2026-07-29). Do not keep a text-shaped accessor alongside the date column.
- The DDL and all consumer rewrites — especially `player-era.mjs` — must land in **one commit**. Splitting them is what re-arms the gamelog-deletion defect.

## Plan

### Phase 1 — Re-derive the consumer surface

- [ ] Re-grep every reader and writer of `date_of_birth` from the repo root, including the private submodule and the frontend. Verify: the three silent JS sites and six frontend sites above are all present; investigate any site this task does not name, since the deferral's list is known stale.
- [ ] Classify each site as loud (fails immediately under a `date`) or silent (changes meaning with no error). Verify: every silent site has a written before/after behaviour note.

### Phase 2 — Rewrite the consumers ahead of the DDL

- [ ] Rewrite `player-era.mjs:109` to read a `Date` rather than regex a string, preserving the sentinel-screening behaviour now that the sentinel is `NULL`. Verify: a targeted spec proves a player with a known date_of_birth does NOT fall through to `nfl_draft_year`; assert on the 450-gamelog path specifically.
- [ ] Rewrite the nflverse backfill comparison and the merge-player typeof branch. Verify: `dob_agrees` still authorizes external-ID writes for a matching player; merge-player still selects the correct row for a `Date` value.
- [ ] Repoint the player_age data-view SQL off `TO_DATE` and the `'0000-00-00'` guard, and the six frontend sites. Verify: data-view goldens regenerate with unchanged values; the age column renders a value, not blank, under `yarn dev:live`.

### Phase 3 — Prove green before production

- [ ] Apply the retype and the full consumer sweep against a non-production database and run the suite. Verify: suite at or above baseline; a positive control confirms the pre-change tree reproduces the old behaviour.
- [ ] Confirm the cast partitions exactly as measured. Verify: every well-formed row carries a real date, every sentinel, empty-string and NULL row carries `NULL`, and no row is lost or silently zeroed — re-measure the partition at apply time, since it moved between 2026-08-04 and 2026-10-09.

### Phase 4 — Apply

- [ ] Request the apply slot, land DDL and every consumer rewrite in one commit, release after the commit. Verify: row counts match Phase 3 exactly; `player-era.mjs` behaviour re-checked against production data before the slot is released.
- [ ] Deploy backend and frontend. Verify: both hosts report the deployed HEAD, and the age column renders in a browser.