Enforce one metric FactSet per (structure, entity, period_end) at the DB level

[jfrench9]

Summary

The "one standing metric FactSet per (structure_id, entity_id, period_end)"
invariant (Metrics M-1) is enforced only procedurally — a newest-wins
SELECT ... ORDER BY created_at DESC LIMIT 1 then delete-facts-and-replace.
Nothing enforces it structurally.

Two call sites now share the pattern (the second was added by the FP&A
sprint, after this issue was filed):

• operations/information_block/metrics.py:503-518 — cmd_compute_metrics,
  scenario-sliced (an actual compute never replaces a scenario month, and vice
  versa).
• operations/information_block/metrics.py:731-743 — cmd_assert_metrics,
  actuals only (pins scenario_id IS NULL).

Neither is gated by TAXONOMY_AUTHORING_ENABLED — that flag has exactly one
enforcement site repo-wide (operations/taxonomy_block/commands.py, gating
GATED_TAXONOMY_TYPES) and nothing on the metrics path touches it. Both
operations are reachable in production today.

Failure scenario

Two overlapping calls for the same key both find no existing set (or the same
one) and both create a standing metric FactSet → two standing sets for the
same period. From then on every re-run replaces only the newest; the older
duplicate's facts persist as orphans. The render path
(information_block/metric.py:87-97, which orders by period_end and keeps the
first set seen per period) hides the duplicate, so the drift is invisible.

Low likelihood today (manual, low-frequency ops), but the documented contract is
unenforced and the unit tests are mock-based, so it is never exercised against a
real DB.

Fix

⚠️ Body revised 2026-08-21. The originally proposed index was written
before fact_sets.scenario_id existed and is wrong as it stood — see
below. Earlier readers of this issue should re-read this section.

Do not implement the original form

-- WRONG — pre-dates scenario_id; collides legitimate scenario slices
UNIQUE (structure_id, entity_id, period_end) WHERE factset_type = 'metric'

fact_sets.scenario_id landed in extensions migration 0024_forecast, and both
standing-set lookups deliberately slice on it. The index above would collide a
scenario slice against its actuals row and against other slices.

Adding scenario_id to the tuple is not sufficient either: the column is
nullable (models/extensions/roboledger/fact_set.py:105-109) and Postgres
treats NULLs as distinct in a unique index by default, so the actuals case — the
one the failure scenario above describes — would still go unenforced.

The two partial indexes

-- actuals
UNIQUE (structure_id, entity_id, period_end)
  WHERE factset_type = 'metric' AND scenario_id IS NULL
-- scenario slices
UNIQUE (structure_id, entity_id, period_end, scenario_id)
  WHERE factset_type = 'metric' AND scenario_id IS NOT NULL

A single index with NULLS NOT DISTINCT is also viable — staging and prod both
run PostgreSQL 16 — but the pair states the two rules explicitly and is easier
to read against the two call sites.

New extensions migration (current head: 0034_close_receipt), created per
tenant schema plus public. 0032_one_reversal_per_entry is the direct
precedent for this whole fix shape — partial unique index via
for_each_tenant_schema, plus the pre-deploy duplicate check below — and should
be followed rather than re-derived.

Before deploying, per tenant schema:

SELECT structure_id, entity_id, period_end, scenario_id, COUNT(*)
FROM fact_sets WHERE factset_type = 'metric'
GROUP BY 1,2,3,4 HAVING COUNT(*) > 1;

A non-empty result means the drift already happened and needs resolving before
the index can be created — an index cannot be built over existing duplicates.

Code-side, at both call sites

• Delete all matching standing sets before create, not just the newest. The
  index prevents new duplicates; it cannot heal rows already duplicated in the
  wild, and today's newest-wins replace leaves the older set's facts as orphans
  forever.
• SELECT ... FOR UPDATE on the standing-set lookup, so the ordinary race
  becomes a clean serialization rather than a constraint violation surfaced as a
  500.

Any future standing-set command should follow the same pattern.

Priority

Labelled priority:low, which understates it — the repo has no
priority:medium. This is not gated by a dark flag: it sits on live
production operations, and its failure mode is silent (the render path hides the
duplicate). It should be prioritized on its own merits rather than inheriting
the dormancy of #901, which it was originally checklisted under.

Found during the v1.6.6 pre-release review; re-verified against main on
2026-08-04 and again on 2026-08-21 (c5144706) — still reproduces at both
sites, and the extensions migration history (through 0034) contains no unique
index on fact_sets.

[jfrench9]

Unlinking this from the TAXONOMY_AUTHORING_ENABLED enablement gate (#901), where it was listed as a blocker.

compute-metrics isn't gated by that flag — the flag has exactly one enforcement site repo-wide (operations/taxonomy_block/commands.py, gating GATED_TAXONOMY_TYPES), and nothing on the metrics path touches it. So the race described here is on a live operation, not one waiting behind a dark flag.

Practical effect: this should be prioritized on its own merits. Sitting on #901's checklist gave it that gate's dormancy, and the flag is false in both prod and staging with no current plan to flip it — so parking here meant parking indefinitely on something already reachable in production.

[jfrench9]

Re-verified against main (2026-08-04, v1.6.30). Still reproduces — but the proposed fix in the body is now wrong, and the race has a second site.

The proposed unique index would break scenarios

fact_sets.scenario_id landed in extensions migration 0024_forecast, after this issue was filed. Both standing-set lookups now deliberately slice on it:

• operations/information_block/metrics.py:512-520 (cmd_compute_metrics) — "Scenario slices keep their own standing sets — an actual compute never replaces a scenario month and vice versa."
• operations/information_block/metrics.py:740-746 (cmd_assert_metrics) — "Asserted series are actuals — the scenario axis never applies." (pins scenario_id IS NULL)

So the index as written —

UNIQUE (structure_id, entity_id, period_end) WHERE factset_type = 'metric'

— would collide legitimate scenario slices against their actuals row and against each other. It must include scenario_id.

Second wrinkle: scenario_id is nullable=True (models/extensions/roboledger/fact_set.py:108-112), and Postgres treats NULLs as distinct in a unique index by default — so the actuals case (the one the original scenario describes) would still not be enforced. Needs either NULLS NOT DISTINCT (PG 15+) or a pair of partial indexes:

-- actuals
UNIQUE (structure_id, entity_id, period_end)
  WHERE factset_type = 'metric' AND scenario_id IS NULL
-- scenario slices
UNIQUE (structure_id, entity_id, period_end, scenario_id)
  WHERE factset_type = 'metric' AND scenario_id IS NOT NULL

Two sites now, not one

cmd_assert_metrics (added in the FP&A sprint, after this issue) replicated the same newest-wins ORDER BY created_at DESC LIMIT 1 → delete-facts-and-replace pattern. Whichever remedy is chosen — index, delete-all-matching, or FOR UPDATE — it has to be applied at both call sites, and any new standing-set command should follow.

Priority

Reiterating the earlier comment: this is not gated by TAXONOMY_AUTHORING_ENABLED, so it sits on a live production operation. priority:low understates it — the repo has no priority:medium, so leaving the label but noting it here.