Review

Normalize coral genet tags to NOAA accession codes

LumenReef tracks Acropora outplants for NOAA restoration grants. Field tablets still free-type genet_tag; grant reports need a fixed-width genet_accession (e.g. ACV-FLK-00412). This revision adds genet_accession, backfills from genet_tag, drops the free-text column, and ships a matching downgrade so staging can alembic downgrade -1 after the tablet cutover. Author notes "downgrade just reopens the old column — ops won't need the accession values on rollback."

AlembicTier 6alembicmigrationdowngradedata-loss

Click a line to flag it, pick one or more labels, then submit. If the change looks correct, approve it.

alembic/versions/20260714_genet_accession.py+44-0
1+"""genet_tag → genet_accession for NOAA outplant reporting.
2+
3+Revision ID: c4e91a02b7f1
4+Revises: 9b2d0f18a4c3
5+"""
6+from alembic import op
7+import sqlalchemy as sa
8+
9+revision = "c4e91a02b7f1"
10+down_revision = "9b2d0f18a4c3"
11+branch_labels = None
12+depends_on = None
13+
14+
15+def upgrade() -> None:
16+ op.add_column(
17+ "outplants",
18+ sa.Column("genet_accession", sa.String(length=32), nullable=True),
19+ schema="restoration",
20+ )
21+ op.execute(
22+ """
23+ UPDATE restoration.outplants
24+ SET genet_accession = upper(trim(genet_tag))
25+ WHERE genet_tag IS NOT NULL
26+ """
27+ )
28+ op.alter_column(
29+ "outplants",
30+ "genet_accession",
31+ nullable=False,
32+ schema="restoration",
33+ )
34+ op.drop_column("outplants", "genet_tag", schema="restoration")
35+
36+
37+def downgrade() -> None:
38+ # Re-open free-text tag for tablets; accession values not needed on rollback.
39+ op.add_column(
40+ "outplants",
41+ sa.Column("genet_tag", sa.Text(), nullable=True),
42+ schema="restoration",
43+ )
44+ op.drop_column("outplants", "genet_accession", schema="restoration")