1 files changed, 45 insertions, 0 deletions
diff --git a/packages/meshbay-hub/src/meshbay_hub/db/migrations/versions/c3d4e5f6a7b8_group_name_unique_per_owner.py b/packages/meshbay-hub/src/meshbay_hub/db/migrations/versions/c3d4e5f6a7b8_group_name_unique_per_owner.py
new file mode 100644
index 0000000..6fc5b73
--- /dev/null
+++ b/packages/meshbay-hub/src/meshbay_hub/db/migrations/versions/c3d4e5f6a7b8_group_name_unique_per_owner.py
@@ -0,0 +1,45 @@
+"""group_name_unique_per_owner
+
+Revision ID: c3d4e5f6a7b8
+Revises: b1c2d3e4f5a6
+Create Date: 2026-08-28 14:00:00.000000
+
+One group name per owner, case-insensitively. The group's identity is still
+its UUID — this only makes `name@owner` a dependable handle.
+
+Pre-flight: abort if the data already violates it, with the offending
+(admin_id, name) pairs listed, rather than silently renaming anyone's group.
+"""
+from typing import Sequence, Union
+
+from alembic import op
+import sqlalchemy as sa
+
+
+revision: str = 'c3d4e5f6a7b8'
+down_revision: Union[str, Sequence[str], None] = 'b1c2d3e4f5a6'
+branch_labels: Union[str, Sequence[str], None] = None
+depends_on: Union[str, Sequence[str], None] = None
+
+
+def upgrade() -> None:
+    bind = op.get_bind()
+    dupes = bind.execute(sa.text(
+        "SELECT admin_id, lower(name) AS n, count(*) AS c "
+        "FROM groups GROUP BY admin_id, lower(name) HAVING count(*) > 1"
+    )).fetchall()
+    if dupes:
+        listed = ", ".join(f"{r.admin_id[:8]}/{r.n!r}×{r.c}" for r in dupes)
+        raise RuntimeError(
+            "Cannot add uq_groups_owner_name: an owner already has two groups "
+            f"with the same name (case-insensitively): {listed}. "
+            "Rename or delete one of each pair, then re-run the migration."
+        )
+    op.create_index(
+        "uq_groups_owner_name", "groups",
+        ["admin_id", sa.text("lower(name)")], unique=True,
+    )
+
+
+def downgrade() -> None:
+    op.drop_index("uq_groups_owner_name", table_name="groups")