---
title: Durable timestamped records of manager and commissioner actions
type: task
status: completed
description: >-
  Four append-only changelogs recording team settings, league settings, lineups and tag designations
  with previous value, new value and acting user, written transactionally through one shared helper,
  plus an enforced REQUEST-versus-VALUE boundary against user_activity_audit. Deployed 2026-09-10.
aliases:
  - ████████████████████████-████-████████-██████-█████████
base_uri: user:task/league/durable-manager-and-commissioner-action-records.md
created_at: '2026-09-10T17:57:07.637Z'
entity_id: 84961b62-ad2e-4130-8654-7209c92019f0
finished_at: '2026-09-10T21:13:01.573Z'
observations:
  - ████████████████████████████████████████████████
  - ████████████████████████████████████████████████
  - ████████████████████████████████████████████████
  - ████████████████████████████████████████████████
  - ████████████████████████████████████████████████
  - ████████████████████████████████████████████████
  - ████████████████████████████████████████████████
  - ████████████████████████████████████████████████
  - ████████████████████████████████████████████████
  - ████████████████████████████████████████████████
  - ████████████████████████████████████████████████
  - ████████████████████████████████████████████████
  - ████████████████████████████████████████████████
  - ████████████████████████████████████████████████
  - ████████████████████████████████████████████████
  - ████████████████████████████████████████████████
  - ████████████████████████████████████████████████
  - ████████████████████████████████████████████████
  - ████████████████████████████████████████████████
  - ████████████████████████████████████████████████
  - ████████████████████████████████████████████████
  - ████████████████████████████████████████████████
  - ████████████████████████████████████████████████
  - ████████████████████████████████████████████████
  - ████████████████████████████████████████████████
owner_identity_uri: █████████████████████████
priority: Medium
public_read: true
tags:
  - user:tag/league-xo-football.md
updated_at: '2026-11-06T00:04:51.765Z'
visibility_analyzed_at: '2026-11-06T00:17:50.861Z'
---

# Durable timestamped records of manager and commissioner actions

## Why

The 2026-09-10 Amendment XLI ruling could not establish when three teams renamed. `PUT /api/teams/:teamId` writes `teams.<field>` in place; there is no `updated_at`, no history table, and `user_activity_audit` records the request without its body and retains only from 2026-09-06. The ruling rested on a Discord archive.

Incident: [[██████████████-███████-█████████████-█████████████████████████-██-██-█████████-███-█████-█████-████-████-█████████████]]

## Decision

**Two field-level changelogs on the repo's existing convention — `teams_changelog` and `leagues_changelog` — not a general activity log, and not body capture in `user_activity_audit`.**

The two together, plus the audit log that already existed, are three records with one boundary between them:

| Table | Answers | Grain | Retention |
| --- | --- | --- | --- |
| `user_activity_audit` | WHO acted, from WHERE, against WHICH endpoint | one row per request | short by design |
| `teams_changelog` | WHAT a manager changed about his own team | one row per changed field | forever |
| `leagues_changelog` | WHAT the commissioner changed about the league | one row per changed field | forever |

**The dividing line is REQUEST versus VALUE, and it runs both ways.** A changelog must never grow a client address, user agent or device id — those are what put the audit table out of the backup set, out of the dev fixture and into `EXCLUDED_RELATIONS`. The audit table must never grow request bodies, because the authentication routes share its middleware. Both directions are asserted in `test/changelog-boundaries.spec.mjs`; the durable statement is in [[user:text/league/database-architecture.md]] under "Three activity records, three questions".

Measured basis for stopping at two: every other manager-owned mutation is already durably timestamped with its actor — `transactions.occurred_at`, `waivers.submitted`, `poaches.submitted`, `trades.offered/accepted/...`, `rosters.last_updated`, all carrying `user_id`. Team settings and league settings were the only two mutations with no durable record at all. A general log would mostly re-record what those tables already hold.

Rejected alternatives:

- **Body capture on a route whitelist in `user_activity_audit`.** The auth routes share that middleware path, so the mechanism that captures a rename body is one whitelist edit away from capturing a password. The table is also PII-bearing, excluded from backups, and short-retention by design — the opposite of what a governance record needs.
- **Per-entity `updated_at` on `teams`.** Records that something changed, not what it was. The prior value is the evidence.
- **A new general `league_activity` table.** The generalization seam already exists: `libs-server/record-changelog.mjs` is a single writer over a table registry, so another table needing the same is one registry entry plus DDL, not a new mechanism. `leagues_changelog` was added exactly that way.

## Retention

Append-only, never pruned, and **added to `db_user_tables` in `scripts/postgres-backup.sh`** — that is the retention answer. A governance record has to outlive a season and survive a restore.

## Backfill

**None, and none is possible.** The 2026 names were written in place and the prior values are gone. The record starts at the migration date; the 2026 cycle stays evidenced only by the incident entity and the Discord archive.

## Plan

1. `db/adhoc/2026-09-10-create-teams-changelog.sql` — `teams_changelog` on the changelog shape plus the acting user: `id`, `team_id`, `season_year`, `lid`, `column_name`, `previous_value`, `new_value`, `source`, `reason`, `user_id`, `changed_at timestamptz not null default now()`. Index on `(team_id, season_year, changed_at)` and on `(lid, changed_at)`. Table and column COMMENTs stating append-only and why it exists.
2. Run the conformance ratchet against a candidate schema BEFORE applying (`audit-schema-conformance.mjs --schema-file tmp/candidate.sql --table teams_changelog`).
3. `libs-server/record-changelog.mjs` — registry entry, plus an `optional_columns` concept so `user_id` passes through. `conflict: null`.
4. `api/routes/teams/index.mjs` PUT — wrap read-current, update and changelog insert in one `db.transaction`. Record only when the value actually differs. The insert is allowed to fail the request: a rename with no record is the bug being fixed, so it must not be silently droppable (unlike the audit middleware, whose contract is the opposite).
5. Manager surface: on a `name` change, post to the league's `discord_webhook_url` via `libs-server/send-discord-message.mjs` — after the response, return ignored, same shape as the transaction notifications. That is the league's actual alerts feed and it is what the ruling had to reconstruct from. No new API endpoint or UI feed; `base db query league` answers the commissioner's pre-deadline check.
6. Registries a new table needs, none of which any gate will remind you about:
   - `scripts/dev-fixture-projection.json` — a disposition per column (`keep`).
   - `db/fixtures/reset-league-tables.mjs` — required, the table is Tier 1 league-scoped via `lid`/`team_id`.
   - `db/tools/generate-reader-role-grants.mjs` — decide the direction explicitly; grant if it sits with `transactions`, exclude with a reason if the actor id argues otherwise.
   - `scripts/postgres-backup.sh` `db_user_tables`.
   - `db/tools/schema-token-vocabulary.mjs` only if the ratchet reports a token.
7. Tests: the route records a row with the prior value; a no-op write records nothing; a failed changelog insert rolls back the rename; the Discord post fires only for `name`.
8. Apply, `yarn export:schema`, `node db/tools/generate-schema-types.mjs`, one commit with the code sweep, `yarn verify`, deploy.

## Status

**Built and committed, six commits, all pushed. Not deployed — deploys are human-gated.**

Nothing is recorded in production until `yarn deploy:all` runs. All three migrations are applied to `league_production` and the API still runs the previous routes, which is harmless: every change is additive, so the old code does not know the tables exist.

### Shipped

Three append-only changelogs, one shared writer, one enforced boundary.

| Table | Records | Route |
| --- | --- | --- |
| `teams_changelog` | team settings — name, image, abbreviation, colors | `PUT /api/teams/:teamId` |
| `leagues_changelog` | league settings, across all four write branches | `PUT /api/leagues/:leagueId` |
| `rosters_players_changelog` | the lineup (`slot`); shaped for `tag` and `extensions` too | `PUT /api/teams/:teamId/lineups` |

- `libs-server/apply-tracked-field-change.mjs` owns the invariant: the update and its changelog row are one transaction, a zero-row update is a refusal, and one numeric-aware no-op comparison. Singular and batch forms. Extracted after the first two routes drifted three ways within a day.
- `libs-server/record-changelog.mjs` is the registry — a fourth table is one entry plus DDL.
- `test/changelog-boundaries.spec.mjs` asserts the request-versus-value line in both directions against the committed schema, with a positive control, plus the credential-disjointness assertion.
- A team NAME change posts to the league Discord. Settings and lineup changes do not.

### Defects found and fixed

- A league settings write matching zero rows returned 200 and, once the changelog was wired, would have recorded a change that never happened. 115 of 117 leagues have no 2026 `seasons` row, so it was the common path. Now a 400.
- A lineup submission was a bare loop, so a failure partway left the lineup half written. Now one transaction.
- The no-op comparison would have called a team renamed `'007'` to `'7'` unchanged and recorded nothing. Gated on the incoming value's type.

### Boundary

Four activity records, stated in [[user:text/league/database-architecture.md]] § "The activity records, and what each one answers" and pointed at from `docs/guides/api.md`. The dividing line is REQUEST versus VALUE: `user_activity_audit` records requests and never a body; the three changelogs record values and never a client address, user agent or device id.

Most manager actions are in NEITHER, correctly — a transaction, waiver, poach, trade or RFA bid already writes its own timestamped row. A changelog exists only where a value was overwritten in place with nothing left behind.

### Not backfillable

For any season, for all three. Every rename, settings change and lineup change before 2026-09-10 overwrote its predecessor in place. No earlier start date should be inferred.

## Second pass, 2026-09-10 — commit `6f289244f`

`yarn verify` green, 18 checks. Full suite 8440 passing; the only failure is a pre-existing one caused by a third session's uncommitted private file.

### Closed

- **`tag`** — `api/routes/teams/tag.mjs` POST and DELETE record to `rosters_players_changelog`, and the POST swap is one transaction. The route had no happy-path coverage at all; it now has five tests including a rollback control.
- **`config-sync.mjs`** — one `leagues_changelog` row per changed SETTING, `user_id: null`. Never the format id.
- **`teams.division`** — `scripts/draw-divisions.mjs` records the Article V Section 13 draw and applies it as one transaction rather than a bare per-team loop.
- **`rosters.last_updated`** — dropped, applied to `league_production`, post-condition verified.
- **`api/routes/leagues/settings.mjs`** — removed with its mount.

### The centralization answer

**Three shapes, and that is where it stops.** `tag` fit the existing batch form unchanged. The external league import did not: the two existing forms are one physical write per recorded field, and an import repoints two opaque ids in ONE statement that changes dozens of settings. `apply_tracked_replacement` is the third form. A caller holding its own transaction passes it as `database`, and knex opens a savepoint — asserted in both directions, since a rollback driven by the helper's own throw would pass either way.

### Scoped out, with reasons

- **`rosters_players.extensions`** — a derived recount of `EXTENSION` transactions. The FAAB-balance class.
- **`teams.waiver_order`** — derivable from `waivers`, which carries its own timestamps.
- **`teams.draft_order`** — a 2026 value on all 20 teams and NO writer in the tree. Finding the writer comes first.

## Remaining

**Nothing in this lane.** Deployed 2026-09-10 21:11Z at `71e0bf4e4`, both hosts.

One item was always someone else's and stays that way: **post the Article IV Section 14 deadline** when the 2027 Offseason opens — the half of the 2026 failure no code can fix. Governance lane, tracked on the open continuation `██████████████████████████████████`.