---
title: Rewrite archive-projections.mjs for Postgres
type: task
status: completed
description: >-
  The projections retention script is inert against Postgres (MySQL-only syntax + a SELECT * column
  mismatch) and has zero callers; rewrite it to be runnable, or retire it, after confirming the
  retention policy is still wanted.
base_uri: user:task/league/rewrite-archive-projections-for-postgres.md
created_at: '2026-07-23T03:06:51.663Z'
entity_id: d563e530-0a0e-4bc9-9cac-16c61ba45e5d
finished_at: '2026-07-29T21:59:00.729Z'
observations:
  - ████████████████████████████████████████████████
  - ████████████████████████████████████████████████
  - ████████████████████████████████████████████████
owner_identity_uri: █████████████████████████
priority: Low
public_read: true
relations:
  - relates [[█████████████████████████-██████-████████-█████████]]
tags:
  - user:tag/league-xo-football.md
updated_at: '2026-07-29T21:59:00.729Z'
visibility_analyzed_at: '2026-11-05T23:50:16.882Z'
---

## Overview

`repository/active/league/scripts/archive-projections.mjs` is meant to bound the hot `projections` table (4.6M rows / 2.4GB) by moving prior-season rows into `projections_archive`, then deleting them from the live table. It is currently **inert against Postgres** and has **zero callers** (no package.json script, scheduler, or cron).

## Background

Two independent reasons it cannot run today (verified 2026-07-22, cont11):
- Both statements use **MySQL-only grammar**: `INSERT IGNORE INTO ...` and `DELETE p FROM projections p INNER JOIN projections_archive pa ...`. Postgres rejects both.
- `INSERT INTO projections_archive SELECT * FROM projections` **cannot positionally match** — the two tables have different column sets.

cont10 renamed the script's columns for vocabulary conformance (`season_year`/`generated_at`) and left the broken statements as-is deliberately (a half-fixed `DELETE` on the 4.6M-row live table is riskier than an inert script) with a documenting comment. The live `projections` table has grown to 4.6M rows **without this ever running**, so the retention it promises has never been operational on Postgres.

## Plan

- **Decide first whether the retention policy is still wanted.** If not, retire the script and remove the dead file rather than fixing it.
- If wanted:
  - Rewrite `INSERT IGNORE` as `INSERT ... ON CONFLICT DO NOTHING` with an explicit column list matching `projections_archive`'s actual columns (do NOT use `SELECT *`).
  - Rewrite the `DELETE ... FROM ... INNER JOIN` as a Postgres `DELETE FROM projections USING projections_archive WHERE ...`, keyed on the full natural key `(source_id, pid, userid, week, season_year, generated_at)`.
  - Verify the archive-vs-live column mapping and the intended retention window (`season_year != current_season.year`) before making it runnable.
  - Gate the `DELETE` behind a dry-run/count preflight given the live-table blast radius, and add a caller (scheduled-command or documented manual invocation) if it should run periodically.

## Notes

- Split off from `███████████████████████████████████████████████████` (███████████████ cluster wrap, cont11) per operator ruling 2026-07-22.