2 files changed, 54 insertions, 2 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")
diff --git a/packages/meshbay-hub/src/meshbay_hub/db/models.py b/packages/meshbay-hub/src/meshbay_hub/db/models.py
index 0c337c1..dbabfc2 100644
--- a/packages/meshbay-hub/src/meshbay_hub/db/models.py
+++ b/packages/meshbay-hub/src/meshbay_hub/db/models.py
@@ -15,7 +15,7 @@ from datetime import datetime, timezone
 
 from sqlalchemy import (
     Boolean, DateTime, ForeignKey, Index, Integer,
-    String, Text, UniqueConstraint,
+    String, Text, UniqueConstraint, text,
 )
 from sqlalchemy.orm import DeclarativeBase, Mapped, mapped_column, relationship
 
@@ -109,7 +109,14 @@ class Group(Base):
 
     members:     Mapped[list["GroupMember"]] = relationship(back_populates="group")
 
-    __table_args__ = (Index("ix_groups_name", "name"),)
+    __table_args__ = (
+        Index("ix_groups_name", "name"),
+        # One name per owner, case-insensitively. The group's identity stays its
+        # UUID; this is what makes `name@owner` a handle a human can rely on
+        # (two different owners may still both have a "photos"). Enforced in the
+        # DB so a race cannot slip a second one past the check in create_group.
+        Index("uq_groups_owner_name", "admin_id", text("lower(name)"), unique=True),
+    )
 
 
 class GroupMember(Base):