Allow a camera to belong to multiple organizations

[MateoLostanlen]

Hi everyone,

We have a modeling question about cameras and organizations in the API.

Right now, a camera belongs to a single organization through a foreign key:

class Camera(SQLModel, table=True):
    __tablename__ = "cameras"
    id: int = Field(None, primary_key=True)
    organization_id: int = Field(..., foreign_key="organizations.id", nullable=False)
    name: str = Field(..., min_length=5, max_length=100, nullable=False, unique=True)
    angle_of_view: float = Field(..., gt=0, le=360, nullable=False)
    elevation: float = Field(..., gt=0, lt=10000, nullable=False)
    lat: float = Field(..., gt=-90, lt=90)
    lon: float = Field(..., gt=-180, lt=180)
    is_trustable: bool = True
    last_active_at: Union[datetime, None] = None
    last_image: Union[str, None] = None
    created_at: datetime = Field(default_factory=datetime.utcnow, nullable=False)

Our organizations represent geographic regions or departments:

class Organization(SQLModel, table=True):
    __tablename__ = "organizations"
    id: int = Field(None, primary_key=True)
    name: str = Field(..., min_length=5, max_length=100, nullable=False, unique=True)
    telegram_id: Union[str, None] = Field(None, nullable=True)
    slack_hook: Union[str, None] = Field(None, nullable=True)

We also have users tied to a single organization:

class User(SQLModel, table=True):
    __tablename__ = "users"
    id: int = Field(None, primary_key=True)
    organization_id: int = Field(..., foreign_key="organizations.id", nullable=False)
    role: UserRole = Field(UserRole.USER, nullable=False)
    login: str = Field(..., index=True, unique=True, min_length=2, max_length=50, nullable=False)
    hashed_password: str = Field(..., min_length=5, max_length=70, nullable=False)
    created_at: datetime = Field(default_factory=datetime.utcnow, nullable=False)

Problem

In some real deployments, a camera is located on the border between two regions or departments. Firefighters from both organizations would like to see and use the same camera in the interface.

With the current schema, a camera can only be attached to one organization. The only workaround would be to duplicate the camera entry, which would create several issues:

• inconsistent metadata and configuration between copies
• confusion when looking at detections and sequences
• more complex maintenance when updating or disabling a physical camera

We would prefer to keep a single camera record and allow several organizations to have access to it.

Proposal

Introduce an association table to represent a many to many relation between cameras and organizations.

Example:

class CameraOrganization(SQLModel, table=True):
    __tablename__ = "camera_organizations"
    camera_id: int = Field(..., foreign_key="cameras.id", primary_key=True)
    organization_id: int = Field(..., foreign_key="organizations.id", primary_key=True)
    is_primary: bool = Field(default=False, nullable=False)

Then we could:

• keep Camera as the technical and physical entity
• link it to one or more organizations through CameraOrganization
• optionally keep Camera.organization_id as the primary organization for backward compatibility and default behavior, at least during a transition phase

API behavior for permissions could then be:

• a user can access all cameras that are linked to their organization through camera_organizations
• for existing instances, we would create one CameraOrganization row for each camera using the current organization_id

Migration plan

High level idea:

1. Add camera_organizations table

2. Backfill it from existing data:

   • for each camera, create one CameraOrganization with organization_id = camera.organization_id and is_primary = True

3. Update queries and permission checks to use the association table

4. Later, consider deprecating Camera.organization_id once the new relation is fully used