Skip to content

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

Description

@jfrench9

Summary

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

Failure scenario

Two overlapping compute-metrics 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, dedupe by period_end keeping newest) hides
the duplicate, so the drift is invisible.

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

Fix

Any/all of:

  • Partial unique index (extensions migration):
    UNIQUE (structure_id, entity_id, period_end) WHERE factset_type = 'metric'
  • Delete all matching standing sets (not just the newest) before create
  • SELECT ... FOR UPDATE on the standing set lookup

Priority

Gate this before enabling metrics broadly in prod. Not a v1.6.6 blocker (the
surface ships dark: TAXONOMY_AUTHORING_ENABLED off in prod).

Found during the v1.6.6 pre-release review.

Metadata

Metadata

Assignees

No one assigned

    Labels

    area:schemasGraph schema definitions (core, roboledger, extensions)priority:lowBacklogsize:small< 1 day

    Type

    No type

    Projects

    No projects

    Milestone

    No milestone

    Relationships

    None yet

    Development

    No branches or pull requests

    Issue actions