---
title: Persist the external league chain root as an absolute mirror key
type: task
status: completed
description: >-
  Close the forward-direction chain gap that still mints a second mirror when an older season is
  imported after a newer one across an unimported gap; needs DDL shipped with the schema export in
  one commit.
base_uri: user:task/league/persist-external-league-chain-root.md
created_at: '2026-09-21T02:12:17.063Z'
entity_id: 998058ed-cdfe-48dd-8663-ae27deda717a
finished_at: '2026-09-21T15:46:19.267Z'
owner_identity_uri: █████████████████████████
priority: Low
public_read: true
relations:
  - succeeds [[██████████████████████-████████-██████-██████-█████████████]]
tags:
  - user:tag/league-xo-football.md
  - user:tag/software-task.md
updated_at: '2026-09-21T15:46:19.267Z'
visibility_analyzed_at: '2026-11-05T23:49:48.075Z'
---

## Problem

Importing an OLDER season after a newer one, across an unimported gap, still mints a second mirror of the same league — two `lid`s, two format rows, two commissioner seats for one real league.

`08ecd2c83` closed the backward direction with a bounded vendor chain-walk. The forward direction is still one hop, so the case survives when all three of these hold: the newer season is already imported, there is an unimported gap below it, and the older season is pasted afterwards. Strictly smaller than what was closed, and deliberately left.

The module doc in `libs-server/external-fantasy-leagues/find-or-create-league-mirror.mjs` states this residue rather than implying the walk is complete. Keep that honest — if this task changes the behavior, the doc changes with it; if it is abandoned, the doc stays.

## Why the current design cannot close it

Mirror identity is resolved by walking the season chain from the id being imported. A connection row records the id it SUCCEEDED, so the chain is reconstructable only through rows that exist. Across an unimported gap there is no row to bridge — the skipped season's row is exactly what was never written.

The backward direction escapes this by asking the vendor for ancestors. The forward direction cannot: you would have to ask the vendor for a league's DESCENDANTS, which the platforms do not expose.

## The fix, and why it is DDL

Persist the chain ROOT as an absolute key on the mirror — the earliest external league id in the chain, resolved once at import and carried forward. Two leagues share a mirror if and only if they resolve to the same root, which is decidable without any walk and without a gap-bridging row.

This is a schema change, so read `docs/guides/schema.md` before touching it. Note the repo rule that bites here: **DDL, the schema export, and the code that depends on them ship in ONE commit.** Between the apply and the commit, any other session's `yarn export:schema` carries the change without the accompanying sweep.

## Scope

- Add the chain-root column, with the DDL, the `yarn export:schema` regeneration and the resolution code in a single commit.
- Backfill existing mirrors. Leagues 121 and 122 exist today; the backfill must resolve each one's root through the same vendor walk the import uses, and must be re-runnable.
- Decide what happens when a backfill CANNOT resolve a root because the vendor no longer serves an ancestor season. Failing closed leaves a null root that the lookup must not treat as "matches everything" — that would merge unrelated leagues, which is worse than the duplicate this task exists to fix.
- Keep the advisory-lock discipline from `08ecd2c83`: the exact-key check is repeated inside the transaction-scoped lock, and the root check has to live inside it too.

## Done when

- Importing a newer season, then an older one across a gap, resolves to ONE mirror.
- A case proves it, shown to go red against a tree without the root key.
- The module doc no longer claims a residue that is closed.

## Design, settled 2026-09-21

### Where the forward gap actually is

Resolution in `libs-server/external-fantasy-leagues/find-or-create-league-mirror.mjs` runs in this order:

- Exact-key hit outside any lock (`:132`), returning `existing` without writing.
- Everything else inside one transaction holding `pg_advisory_xact_lock` on the mirror key (`:176-179`), with the exact-key check repeated under the lock (`:181`).
- The **backward** walk (`:200-232`), seeded from the pasted payload's own `previous_league_id`, bounded by `MAX_CHAIN_WALK = 25` (`:44`), checking stored connections before spending a vendor read.
- The **forward** hop (`:243-251`) — a single `.where({ previous_external_league_id: key })` lookup, one hop, no walk and no vendor assist.

The asymmetry is not an oversight, and the module doc says so at `:79-86`: Sleeper exposes ancestors through `previous_league_id` and never exposes descendants. Bridging a forward gap by walking would mean walking backward from *every* stored chain on the platform to see whether one passes through the pasted id. That is why an absolute key is the answer rather than a longer walk.

### The column

```
chain_root_external_league_id character varying(255) NULL
```

with a **non-unique** btree index on `(platform, chain_root_external_league_id)`.

- **Type matches `external_league_id` (255), not `previous_external_league_id` (64).** The root is compared against primary external ids across platforms, so it must hold the same range as the column it is compared to. The 64 on the existing column is a Sleeper-era narrowing and is not the precedent to copy.
- **Non-unique on purpose.** Two connections sharing a root is the entire point of the column; a unique index would refuse exactly the rows it exists to group. A partial unique index here would also re-run this lane's own 42P10 trap on a NULL-heavy column.
- **Nullable, and NULL must never act as a wildcard.** A backfill that cannot reach the vendor for an old ancestor records NULL rather than a fabricated root. Every read of the column needs an explicit not-null guard, or a mirror with an unknown root silently matches another with an unknown root.

### Where it gets written, and what it costs

Two insert sites, both in `libs-server/create-league-from-external.mjs` — `create_league_from_external` (`:409-420`) and `attach_external_season_to_mirror` (`:583-590`). Both already take `previous_external_league_id`, so the new value threads the same way.

**Zero extra vendor calls in the ordinary case.** The backward walk already terminates at either a known connection (copy its root) or the earliest ancestor the vendor still serves (that id becomes the root), so the value is a byproduct of resolution that already runs. Only the backfill pays, once per existing mirror, and it cannot shortcut — the raw chain is precisely what is undiscoverable without asking the vendor.

The root check MUST run inside the advisory lock, beside the exact-key re-check at `:181`. Resolving outside it re-opens the race that `08ecd2c83` closed.

### The one-commit requirement — smaller than the lane assumed

Per `docs/guides/schema.md`: author `private/db/adhoc/YYYY-MM-DD-<slug>.sql` with a `-- STATUS: PENDING` banner, `yarn db:exec` it, `yarn export:schema`, regenerate `db/tools/generate-schema-types.mjs` output, and commit the adhoc (banner flipped to APPLIED), the schema diff and both generated type files together.

**No `dev-fixture-projection.json` entry is needed, and this is verified rather than assumed.** `external_league_connections` is absent from `db_user_tables` in `scripts/postgres-backup.sh:53-90`, and the completeness gate only checks tables enumerated there. Controlled both ways: `external_platform_credentials` and `user_external_accounts` ARE in that list and DO carry projection entries; `external_league_connections` is in neither. So the trap that once blocked league pushes fleet-wide does not apply to this column.

Two gotchas that do apply. `yarn export:schema` dumps the whole database and will sweep concurrent lanes' DDL into the diff — read it by category before committing, per this lane's own hard-won rule. And the token-vocabulary ratchet may not recognize `chain` or `root`; read the bracketed hint before assuming a rename is wanted.

### One correction to the investigation

The unique constraint on `(platform, external_league_id)` **does** exist — `external_league_connections_platform_external_league_id_key`, confirmed against `pg_index` on production. It is invisible in the schema dump's `CREATE TABLE` block because it is a table constraint Postgres materializes as a separate index. Reading the dump alone suggested it was absent; that is the same over-anchored-read trap this lane already records. Do not "add" it.