likithabommasani21 · Nov 13, 2026 · Nov 13, 2026
diff --git a/alembic/versions/73d27e78571a_add_release_level_to_releasemixin_.py b/alembic/versions/73d27e78571a_add_release_level_to_releasemixin_.py
@@ -0,0 +1,185 @@
+"""add release_level to ReleaseMixin tables
+
+Revision ID: 73d27e78571a
+Revises: 7480e4ee69dc
+Create Date: 2026-11-13
+
+Splits release_status's visibility half into release_level, per
+docs/release-level-and-data-maturity-vocabulary.md. release_status stays
+for now -- dropped in a later migration once every consumer has moved over.
+
+Maps public->public, private->private, draft/provisional->pending by name
+only (provisional only matters on the two transducer tables). We expect
+published/final/archived to be empty everywhere (open question 2), so
+rather than quietly defaulting them to pending too, the migration raises
+if it finds one -- better to find out now.
+
+Seven tables are __versioned__ (sqlalchemy_continuum) and need the same
+column on their _version shadow table too, or writes to them fail -- same
+pattern as e123456789ab_add_observation_data_quality.py.
+"""
+
+from typing import Sequence, Union
+
+import sqlalchemy as sa
+from alembic import op
+
+revision: str = "73d27e78571a"
+down_revision: Union[str, Sequence[str], None] = "7480e4ee69dc"
+branch_labels: Union[str, Sequence[str], None] = None
+depends_on: Union[str, Sequence[str], None] = None
+
+CATEGORY = "release_level"
+CATEGORY_DESCRIPTION = (
+    "Who may see a record: pending (no release decision made), private "
+    "(explicitly withheld), or public (released)."
+)
+PENDING_DEFINITION = (
+    "Not yet released to any audience, because no release decision has "
+    "been made yet."
+)
+CATEGORY_TERMS = ("pending", "private", "public")
+
+# Every table carrying ReleaseMixin's release_status column.
+TABLES = (
+    "address",
+    "analysis_method",
+    "aquifer_system",
+    "aquifer_type",
+    "asset",
+    "contact",
+    "data_provenance",
+    "deployment",
+    "email",
+    "field_activity",
+    "field_event",
+    "field_event_participant",
+    "geologic_formation",
+    "group",
+    "location",
+    "measuring_point_history",
+    "monitoring_frequency_history",
+    "notes",
+    "observation",
+    "parameter",
+    "permission_history",
+    "phone",
+    "regulatory_limit",
+    "sample",
+    "sensor",
+    "status_history",
+    "stratigraphic_interval",
+    "thing",
+    "thing_aquifer_association",
+    "thing_id_link",
+    "transducer_observation",
+    "transducer_observation_block",
+    "well_casing_material",
+    "well_purpose",
+    "well_screen",
+)
+
+# __versioned__ (sqlalchemy_continuum) tables among the above -- their
+# "_version" shadow table needs the same column too.
+VERSIONED_TABLES = (
+    "aquifer_system",
+    "geologic_formation",
+    "location",
+    "observation",
+    "parameter",
+    "regulatory_limit",
+    "thing",
+)
+
+
+def upgrade() -> None:
+    connection = op.get_bind()
+
+    connection.execute(
+        sa.text(
+            "INSERT INTO lexicon_category (name, description) "
+            "VALUES (:name, :description) ON CONFLICT (name) DO NOTHING"
+        ),
+        {"name": CATEGORY, "description": CATEGORY_DESCRIPTION},
+    )
+    connection.execute(
+        sa.text(
+            "INSERT INTO lexicon_term (term, definition) VALUES (:term, :definition) "
+            "ON CONFLICT (term) DO NOTHING"
+        ),
+        {"term": "pending", "definition": PENDING_DEFINITION},
+    )
+    connection.execute(
+        sa.text("""
+            INSERT INTO lexicon_term_category_association (term_id, category_id)
+            SELECT t.id, c.id
+            FROM lexicon_term t, lexicon_category c
+            WHERE t.term = ANY(:terms) AND c.name = :category
+            ON CONFLICT DO NOTHING
+            """),
+        {"terms": list(CATEGORY_TERMS), "category": CATEGORY},
+    )
+
+    for table in TABLES:
+        op.add_column(
+            table,
+            sa.Column("release_level", sa.String(length=100), nullable=True),
+        )
+        op.create_foreign_key(
+            f"fk_{table}_release_level",
+            table,
+            "lexicon_term",
+            ["release_level"],
+            ["term"],
+            onupdate="CASCADE",
+        )
+
+        for release_status, release_level in (
+            ("public", "public"),
+            ("private", "private"),
+            ("draft", "pending"),
+            ("provisional", "pending"),
+        ):
+            connection.execute(
+                sa.text(
+                    f'UPDATE "{table}" SET release_level = :release_level '
+                    f"WHERE release_status = :release_status"
+                ),
+                {"release_status": release_status, "release_level": release_level},
+            )
+
+        unaccounted = connection.execute(
+            sa.text(
+                f'SELECT release_status, COUNT(*) FROM "{table}" '
+                f"WHERE release_level IS NULL GROUP BY release_status"
+            )
+        ).all()
+        if unaccounted:
+            raise RuntimeError(
+                f"{table}: release_status value(s) not handled by this "
+                f"migration, refusing to guess a release_level for them: "
+                f"{dict(unaccounted)}"
+            )
+
+    for table in VERSIONED_TABLES:
+        op.add_column(
+            f"{table}_version",
+            sa.Column(
+                "release_level",
+                sa.String(length=100),
+                sa.ForeignKey("lexicon_term.term", onupdate="CASCADE"),
+                nullable=True,
+            ),
+        )
+
+
+def downgrade() -> None:
+    for table in VERSIONED_TABLES:
+        op.drop_column(f"{table}_version", "release_level")
+
+    for table in TABLES:
+        op.drop_constraint(f"fk_{table}_release_level", table, type_="foreignkey")
+        op.drop_column(table, "release_level")
+
+    # Lexicon terms/categories left in place -- same convention as
+    # b2c3d4e5f6a7 and c89d6680bff6.
diff --git a/core/enums.py b/core/enums.py
@@ -60,6 +60,7 @@
 QualityFlag: type[Enum] = build_enum_from_lexicon_category("quality_flag")
 Relation: type[Enum] = build_enum_from_lexicon_category("relation")
 ReleaseStatus: type[Enum] = build_enum_from_lexicon_category("release_status")
+ReleaseLevel: type[Enum] = build_enum_from_lexicon_category("release_level")
 ReviewStatus: type[Enum] = build_enum_from_lexicon_category("review_status")
 Role: type[Enum] = build_enum_from_lexicon_category("role")
 SampleMatrix: type[Enum] = build_enum_from_lexicon_category("sample_matrix")

diff --git a/core/lexicon.json b/core/lexicon.json
@@ -263,6 +263,10 @@
     {
       "name": "data_type",
       "description": "Which kind of data a permission_grant applies to. Reuses the activity_type terms (well inventory, groundwater level, water chemistry) rather than inventing a parallel list."
+    },
+    {
+      "name": "release_level",
+      "description": "Who may see a record: pending (no release decision made), private (explicitly withheld), or public (released)."
     }
   ],
   "terms": [
@@ -1813,14 +1817,16 @@
     },
     {
       "categories": [
-        "release_status"
+        "release_status",
+        "release_level"
       ],
       "term": "public",
       "definition": "public version"
     },
     {
       "categories": [
-        "release_status"
+        "release_status",
+        "release_level"
       ],
       "term": "private",
       "definition": "private version"
@@ -8668,6 +8674,13 @@
       ],
       "term": "program",
       "definition": "Scoped to one program, e.g. AMMP or Geothermal."
+    },
+    {
+      "categories": [
+        "release_level"
+      ],
+      "term": "pending",
+      "definition": "Not yet released to any audience, because no release decision has been made yet."
     }
   ]
-}
+}
diff --git a/db/base.py b/db/base.py
@@ -100,6 +100,10 @@ class ReleaseMixin:
     def release_status(self):
         return lexicon_term(default="draft")
 
+    @declared_attr
+    def release_level(self):
+        return lexicon_term(default="pending")
+
 
 class AuditMixin:
     """Mixin to add standard audit columns to a model."""

diff --git a/docs/release-level-and-data-maturity-vocabulary.md b/docs/release-level-and-data-maturity-vocabulary.md
@@ -1,6 +1,6 @@
 # Release Level and Review State Vocabulary
 
-**Status:** First draft
+**Status:** Updated
 
 **References:** ADR5, "One Enforcement Engine, Two Grant Tables" ("Two axes in
 one column" section). Blocks BDMS-1382.
@@ -120,15 +120,17 @@ rather than part of `ReleaseMixin`.
 becomes `pending` (open question 1) — same underlying behavior, renamed
 value, which is still a real data migration for every existing `draft` row,
 not just a documentation change. `provisional` maps cleanly to
-`review_state`, same word either side; `published` becomes `unknown`
+`review_state`, same word either side, on the three tables that get a
+`review_state` column — and per open question 2's audit, no other table has
+any rows using `provisional`/`published`/`final` at all, so there is nothing
+left to decide for "any other table." `published` becomes `unknown`
 (deliberately not the same word, since the legacy `published` and the old
 `review_state=published` proposal were two unrelated things that happened to
-share a name — `unknown` removes that collision). Both only apply on tables
-that actually have the `review_state` column; on any other table, what these
-legacy values even meant, and what `release_level` those rows should get
-either way, is all one open question (see open question 2). `final` is a
-reasonable guess on both axes, not a confirmed one; `archived` is genuinely
-unresolved.
+share a name — `unknown` removes that collision). `final` is a reasonable
+guess on both axes, not a confirmed one, but moot in practice since no rows
+use it; `archived` is genuinely unresolved; `TransducerObservation`/
+`TransducerObservationBlock` rows where `release_status` itself is literally
+`provisional` are their own separate case, see open question 5.
 
 ## Open questions
 
@@ -143,37 +145,31 @@ unresolved.
 
 **Future** — do not block this draft:
 
-2. **Row-level audit of `provisional`, `published`, and `final`, including
-   what they mean on tables with no `review_state` column** (e.g. `Contact`,
-   `Phone`). How many existing rows use each value, on which tables, and
-   what should each become? For tables with `review_state`
-   (`Observation`/`TransducerObservation`/`TransducerObservationBlock`, open
-   question 4), the value side is already settled
-   (`provisional`/`published`→`unknown`); what's left is each row's
-   `release_level`. For every other table, "provisional" likely never meant
-   a review state in the first place — there's no review process for a
-   phone number — so it's unclear whether it meant "not yet public"
-   (→ `pending`) or something else, and needs a real decision, not a guess
-   embedded in the mapping matrix. ADR5 itself notes this kind of data
-   migration has no CD path in this repo and is run by hand.
+2. **Resolved, via a row-level audit of live data:** `published`/`final`
+   have zero rows anywhere, and `provisional` as a `release_status` value
+   exists only on `TransducerObservation`/`TransducerObservationBlock` —
+   every other table has none. Nothing to decide except for those two
+   tables, promoted to their own question — see open question 5.
 3. **`archived` doesn't get a slot in either axis for now.** Nothing today
    depends on it behaving differently from `pending`/`private`. If a real need
    for a distinct archived/retired state emerges later, it gets its own
    field then — not a value crammed into `release_level`, since an archived
    record can plausibly still be public (e.g. a retired monitoring well's
    historical readings), and squeezing it into a single-valued
    `release_level` would make "public" and "archived" mutually exclusive.
-4. **Resolved: `review_state` gets its own mixin, not folded into
-   `ReleaseMixin`.** Visibility applies to every record; review state
-   doesn't — "approved" means nothing for a phone number or an aquifer
-   type. Starts on `Observation`, `TransducerObservation`, and
-   `TransducerObservationBlock`, where review already has a defined
-   meaning; other tables get it added only once their own meaning is
-   defined, not universally up front. Cheap to revisit: a mixin composes
-   onto more models later with no refactor needed, so starting narrow
-   forecloses nothing. See Section 3's "Existing precedent" for how
-   `TransducerObservation` sets its value today, and why `Observation` would
-   need a different mechanism.
+4. **Resolved:** `review_state` gets its own mixin, not folded into
+   `ReleaseMixin` — not every record has a review state. Starts on
+   `Observation`, `TransducerObservation`, and `TransducerObservationBlock`
+   only; see Section 3 for how each sets its value.
+5. **Assuming, not yet confirmed: the `TransducerObservation` (76,725
+   rows) and `TransducerObservationBlock` (108 rows) rows with
+   `release_status='provisional'` become `release_level='pending'`,**
+   migrated in the same pass as every other table rather than held back
+   separately. Based on the evidence so far (every one of these rows also
+   has `data_maturity='provisional'`, never `approved`; `docs/
+   hydrograph-correction-publish.md` describes "publishing as provisional"
+   as a deliberate operator choice distinct from `public`) — needs real
+   sign-off from the team.
 
 ## References