AnchorableEntity feature description

Board has Lanes Card is on Lane (move_to_lane, only one lane a time) Card contains one Specialism (composition) Specialism is Polymorphic (Image, Video, Task, Script etc. ) Specialism creates one or more AnchorableEntity AnchorableEntity contain Specialism specific data Card provide interface (proxy) to AnchorableEntity AnchorableEntity is the anchor for Comments

import uuid
from sqlalchemy import Column, ForeignKey, String, JSON, create_engine
from sqlalchemy.dialects.postgresql import UUID, JSONB
from sqlalchemy.orm import relationship, sessionmaker, declarative_base
from sqlalchemy.ext.declarative import declared_attr
from sqlalchemy.ext.mutable import MutableDict

Base = declarative_base()

class Board(Base):
    __tablename__ = 'boards'

    id = Column(UUID(as_uuid=True), primary_key=True, default=uuid.uuid4)
    name = Column(String, nullable=False)
    lanes = relationship('Lane', back_populates='board', cascade='all, delete-orphan')

class Lane(Base):
    __tablename__ = 'lanes'

    id = Column(UUID(as_uuid=True), primary_key=True, default=uuid.uuid4)
    name = Column(String, nullable=False)
    board_id = Column(UUID(as_uuid=True), ForeignKey('boards.id'))
    board = relationship('Board', back_populates='lanes')
    cards = relationship('Card', back_populates='lane', cascade='all, delete-orphan')

class Card(Base):
    __tablename__ = 'cards'

    id = Column(UUID(as_uuid=True), primary_key=True, default=uuid.uuid4)
    title = Column(String, nullable=False)
    lane_id = Column(UUID(as_uuid=True), ForeignKey('lanes.id'))
    lane = relationship('Lane', back_populates='cards')
    specialism = relationship('Specialism', back_populates='card', uselist=False, cascade='all, delete-orphan')

    def move_to_lane(self, new_lane):
        self.lane = new_lane

    def provide_anchorable_entity(self):
        return self.specialism.anchorable_entity if self.specialism else None

class Specialism(Base):
    __tablename__ = 'specialisms'

    id = Column(UUID(as_uuid=True), primary_key=True, default=uuid.uuid4)
    type = Column(String, nullable=False)  # Polymorphic type, e.g., Image, Video, Task, Script
    data = Column(MutableDict.as_mutable(JSONB), default=dict)  # Specialism-specific data in JSONB
    card_id = Column(UUID(as_uuid=True), ForeignKey('cards.id'))
    card = relationship('Card', back_populates='specialism')
    anchorable_entity = relationship('AnchorableEntity', uselist=False, back_populates='specialism', cascade='all, delete-orphan')

    __mapper_args__ = {
        'polymorphic_identity': 'specialism',
        'polymorphic_on': type
    }

class AnchorableEntity(Base):
    __tablename__ = 'anchorable_entities'

    id = Column(UUID(as_uuid=True), primary_key=True, default=uuid.uuid4)
    specialism_id = Column(UUID(as_uuid=True), ForeignKey('specialisms.id'))
    specialism = relationship('Specialism', back_populates='anchorable_entity')
    data = Column(MutableDict.as_mutable(JSONB), default=dict)  # Specialism-specific data
    comments = relationship('Comment', back_populates='anchorable_entity', cascade='all, delete-orphan')

class Comment(Base):
    __tablename__ = 'comments'

    id = Column(UUID(as_uuid=True), primary_key=True, default=uuid.uuid4)
    anchorable_entity_id = Column(UUID(as_uuid=True), ForeignKey('anchorable_entities.id'))
    anchorable_entity = relationship('AnchorableEntity', back_populates='comments')
    text = Column(String, nullable=False)

# Example of engine and session creation
# Replace 'DATABASE_URL' with your actual database URL
database_url = "postgresql://user:password@localhost/dbname"
engine = create_engine(database_url)
Session = sessionmaker(bind=engine)
Base.metadata.create_all(engine)

# Example usage with a session
session = Session()
board = Board(name="Project Board")
lane = Lane(name="To Do", board=board)
card = Card(title="Create domain model", lane=lane)
specialism = Specialism(type="Task", data={"description": "Design the data model."}, card=card)
anchor_entity = AnchorableEntity(specialism=specialism, data={"task_details": "Python, SQLAlchemy"})
comment = Comment(anchorable_entity=anchor_entity, text="This is a critical task.")

session.add(board)
session.commit()

Feature Description - AnchorableEntity

The AnchorableEntity feature allows users to annotate various types of content—ranging from images and videos to audio clips and Excel files. The annotations provide meaningful metadata, such as comments, spatial or temporal information, and identifiers that are unique to each annotation.

The system is designed to be flexible and extensible, allowing the creation of annotations with different types of anchors, depending on the media being annotated. Below are detailed descriptions of how the AnchorableEntity model works for different types of content, including examples of how entities are generated based on user interactions.

Types of AnchorableEntity Annotations

1. SpatialEntity: Used for annotating points or areas in images or frames of a video.
2. TemporalEntity: Used for annotating time segments in audio or video.
3. SpatioTemporalEntity: Used for annotating regions within a video that have both spatial and temporal components.
4. CellEntity: Used for annotating specific cells in an Excel spreadsheet.
5. RowEntity: Used for annotating a particular row in an Excel spreadsheet.
6. ColumnEntity: Used for annotating a particular column in an Excel spreadsheet.
7. RangeEntity: Used for annotating a range of cells in an Excel spreadsheet.

Outline Use

The AnchorableEntity feature provides a consistent and extensible model for annotating different types of media content, from images and videos to audio and Excel spreadsheets. By using well-defined subtypes such as SpatialEntity, TemporalEntity, SpatioTemporalEntity, and Excel-related entities (CellEntity, RowEntity, ColumnEntity, RangeEntity), this model enables users to annotate specific points, segments, or regions in their content easily and intuitively.

• Images can be annotated with SpatialEntity.
• Audio can be annotated with TemporalEntity.
• Videos can be annotated with SpatioTemporalEntity.
• Excel files can be annotated with CellEntity, RowEntity, ColumnEntity, or RangeEntity.

These entities are easily extendable, making it simple to add new annotation types in the future if needed. The examples provided demonstrate how user interactions through a user interface would translate into generated entities for different media types.

End to End Example

from typing import Optional, Union, Any, Dict, List
from pydantic import BaseModel, Field
from datetime import datetime
from uuid import UUID, uuid4
from sqlalchemy import String, JSON
from sqlalchemy.orm import mapped_column, Mapped

# Extending AnchorableEntity with a reference Object
class ReferenceObject(BaseModel):
    reference_id: UUID = Field(..., description="Unique identifier of the reference object")
    reference_type: str = Field(..., description="Type of the reference object, e.g., Card")
    reference_data: Dict[Any, Any] = Field(..., description="Data needed to render the anchor (e.g., version info, video ID)")

# Base SQLAlchemy model for AnchorableEntity, extended with reference object
def create_anchorable_entity_base():
    return {
        "entity_id": mapped_column(UUID, default=uuid4, primary_key=True, nullable=False, unique=True),
        "created_at": mapped_column(datetime, default=datetime.utcnow, nullable=False),
        "updated_at": mapped_column(datetime, default=datetime.utcnow, onupdate=datetime.utcnow, nullable=False),
        "comment": mapped_column(String, nullable=True),
        "reference_object": mapped_column(JSON, nullable=False),  # ReferenceObject data in JSON
    }

# Pydantic model for AnchorableEntityBase
class AnchorableEntityBase(BaseModel):
    entity_id: Optional[UUID] = Field(default_factory=uuid4, description="Unique identifier for the entity")
    created_at: Optional[datetime] = Field(default_factory=datetime.utcnow, description="Timestamp when the entity was created")
    updated_at: Optional[datetime] = Field(default_factory=datetime.utcnow, description="Timestamp when the entity was last updated")
    comment: Optional[str] = Field(None, description="Optional comment attached to this anchorable entity")
    reference_object: ReferenceObject = Field(..., description="Reference object containing id, type and reference data")

# Pydantic models for different AnchorableEntity types
class SpatialEntity(AnchorableEntityBase):
    x: float = Field(..., description="X-coordinate in the image or frame")
    y: float = Field(..., description="Y-coordinate in the image or frame")
    width: Optional[float] = Field(None, description="Width of the bounding box, if applicable")
    height: Optional[float] = Field(None, description="Height of the bounding box, if applicable")

class TemporalEntity(AnchorableEntityBase):
    start_time: float = Field(..., description="Start time of the segment in seconds")
    end_time: float = Field(..., description="End time of the segment in seconds")

class SpatioTemporalEntity(SpatialEntity, TemporalEntity):
    frame_rate: Optional[float] = Field(None, description="Frame rate of the video, if applicable")

class CellEntity(AnchorableEntityBase):
    sheet_name: str = Field(..., description="Name of the sheet where the cell is located")
    cell_reference: str = Field(..., description="Excel-style cell reference (e.g., 'A1')")

class RowEntity(AnchorableEntityBase):
    sheet_name: str = Field(..., description="Name of the sheet where the row is located")
    row_number: int = Field(..., description="Row number in the sheet (1-based index)")

class ColumnEntity(AnchorableEntityBase):
    sheet_name: str = Field(..., description="Name of the sheet where the column is located")
    column_letter: str = Field(..., description="Column letter in the sheet (e.g., 'A')")

class RangeEntity(AnchorableEntityBase):
    sheet_name: str = Field(..., description="Name of the sheet where the range is located")
    start_cell: str = Field(..., description="Starting cell of the range (e.g., 'A1')")
    end_cell: str = Field(..., description="Ending cell of the range (e.g., 'C10')")

# Union of possible Anchorable Entities
AnchorableEntity = Union[SpatialEntity, TemporalEntity, SpatioTemporalEntity, CellEntity, RowEntity, ColumnEntity, RangeEntity]

# Example usage for cards on a Kanban board
class Card(BaseModel):
    card_id: UUID = Field(default_factory=uuid4, description="Unique identifier for the card")
    asset_type: str = Field(..., description="Type of the asset e.g., 'video', 'image', 'pdf'")
    reference_objects: List[AnchorableEntity] = Field(default_factory=list, description="List of anchorable entities related to this card")

    def add_anchorable_entity(self, anchorable_entity: AnchorableEntity):
        self.reference_objects.append(anchorable_entity)

    def render_anchor_over_asset(self):
        # Example: rendering the anchors over the related asset
        for entity in self.reference_objects:
            print(f"Rendering {entity} over {self.asset_type}")

# Example end-to-end feature description with examples
# Example card containing a video asset with anchorable entities
card = Card(
    card_id=uuid4(),
    asset_type="video",
    reference_objects=[
        TemporalEntity(
            reference_object=ReferenceObject(
                reference_id=uuid4(),
                reference_type="Video",
                reference_data={"Video Id": uuid4(), "TemporalEntity": "Version 2.0"}
            ),
            start_time=5.0,
            end_time=15.0,
            comment="Interesting scene"
        )
    ]
)

# Adding an anchor to the card
new_anchor = SpatialEntity(
    reference_object=ReferenceObject(
        reference_id=uuid4(),
        reference_type="Image",
        reference_data={"Bounding Box": "Region of Interest"}
    ),
    x=50,
    y=100,
    width=200,
    height=100
)

card.add_anchorable_entity(new_anchor)

# Rendering the anchors
card.render_anchor_over_asset()

# Example: Adding and rendering an anchor for a CellEntity in an Excel sheet
cell_anchor = CellEntity(
    reference_object=ReferenceObject(
        reference_id=uuid4(),
        reference_type="Excel",
        reference_data={"Sheet Name": "SalesData", "Cell Reference": "B5"}
    ),
    sheet_name="SalesData",
    cell_reference="B5",
    comment="Review this value for accuracy"
)

card.add_anchorable_entity(cell_anchor)

# Rendering the anchors including the new cell entity
card.render_anchor_over_asset()

Base Model

from typing import Optional, Union
from pydantic import BaseModel, Field
from datetime import datetime
from uuid import UUID, uuid4

# Base Pydantic model for AnchorableEntity
class AnchorableEntityBase(BaseModel):
    entity_id: Optional[UUID] = Field(default_factory=uuid4, description="Unique identifier for the entity")
    created_at: Optional[datetime] = Field(default_factory=datetime.utcnow, description="Timestamp when the entity was created")
    updated_at: Optional[datetime] = Field(default_factory=datetime.utcnow, description="Timestamp when the entity was last updated")
    comment: Optional[str] = Field(None, description="Optional comment attached to this anchorable entity")

# Pydantic model for SpatialEntity
class SpatialEntity(AnchorableEntityBase):
    x: float = Field(..., description="X-coordinate in the image or frame")
    y: float = Field(..., description="Y-coordinate in the image or frame")
    width: Optional[float] = Field(None, description="Width of the bounding box, if applicable")
    height: Optional[float] = Field(None, description="Height of the bounding box, if applicable")

# Pydantic model for TemporalEntity
class TemporalEntity(AnchorableEntityBase):
    start_time: float = Field(..., description="Start time of the segment in seconds")
    end_time: float = Field(..., description="End time of the segment in seconds")

# Pydantic model for SpatioTemporalEntity
class SpatioTemporalEntity(SpatialEntity, TemporalEntity):
    frame_rate: Optional[float] = Field(None, description="Frame rate of the video, if applicable")

# Pydantic model for CellEntity
class CellEntity(AnchorableEntityBase):
    sheet_name: str = Field(..., description="Name of the sheet where the cell is located")
    cell_reference: str = Field(..., description="Excel-style cell reference (e.g., 'A1')")

# Pydantic model for RowEntity
class RowEntity(AnchorableEntityBase):
    sheet_name: str = Field(..., description="Name of the sheet where the row is located")
    row_number: int = Field(..., description="Row number in the sheet (1-based index)")

# Pydantic model for ColumnEntity
class ColumnEntity(AnchorableEntityBase):
    sheet_name: str = Field(..., description="Name of the sheet where the column is located")
    column_letter: str = Field(..., description="Column letter in the sheet (e.g., 'A')")

# Pydantic model for RangeEntity
class RangeEntity(AnchorableEntityBase):
    sheet_name: str = Field(..., description="Name of the sheet where the range is located")
    start_cell: str = Field(..., description="Starting cell of the range (e.g., 'A1')")
    end_cell: str = Field(..., description="Ending cell of the range (e.g., 'C10')")

# Union of possible Anchorable Entities
AnchorableEntity = Union[SpatialEntity, TemporalEntity, SpatioTemporalEntity, CellEntity, RowEntity, ColumnEntity, RangeEntity]

Example Annotations for Different Media Types

1. Image Annotation Using SpatialEntity

Scenario: A user clicks on an image to highlight a specific object (e.g., a car) by drawing a bounding box around it.

Generated Entity:

from uuid import uuid4
from datetime import datetime

spatial_entity = SpatialEntity(
    entity_id=uuid4(),
    created_at=datetime.utcnow(),
    updated_at=datetime.utcnow(),
    comment="Car detected in the parking lot",
    x=150.0,
    y=200.0,
    width=100.0,
    height=50.0
)

# This entity could be added to a list of annotations for the image
image_entities = [spatial_entity]

2. Audio Annotation Using TemporalEntity

Scenario: A user listens to an audio clip and selects a segment between 15.0 and 20.0 seconds to annotate a specific spoken phrase.

Generated Entity:

temporal_entity = TemporalEntity(
    entity_id=uuid4(),
    created_at=datetime.utcnow(),
    updated_at=datetime.utcnow(),
    comment="Interesting quote in the podcast",
    start_time=15.0,
    end_time=20.0
)

# Adding this entity to a list of annotations for the audio track
audio_entities = [temporal_entity]

3. Video Annotation Using SpatioTemporalEntity

Scenario: A user watches a video and draws a bounding box that moves with a person from the 10th to the 15th second of the video.

Generated Entity:

spatio_temporal_entity = SpatioTemporalEntity(
    entity_id=uuid4(),
    created_at=datetime.utcnow(),
    updated_at=datetime.utcnow(),
    comment="Tracking a person walking across the frame",
    x=100.0,
    y=150.0,
    width=50.0,
    height=100.0,
    start_time=10.0,
    end_time=15.0,
    frame_rate=30.0  # Video frame rate in frames per second
)

# Adding this entity to a list of annotations for the video
video_entities = [spatio_temporal_entity]

4. Excel Annotation Using CellEntity, RowEntity, ColumnEntity, and RangeEntity

Annotating a Specific Cell (CellEntity)

Scenario: A user selects a specific cell (B5) in an Excel sheet to add a note about an important value.

Generated Entity:

cell_entity = CellEntity(
    entity_id=uuid4(),
    created_at=datetime.utcnow(),
    updated_at=datetime.utcnow(),
    comment="Check value in this cell for accuracy",
    sheet_name="SalesData",
    cell_reference="B5"
)

# Adding this entity to a list of annotations for the Excel file
excel_entities = [cell_entity]

Annotating an Entire Row (RowEntity)

Scenario: A user highlights an entire row (Row 3) in an Excel sheet to flag discrepancies in the sales report.

Generated Entity:

row_entity = RowEntity(
    entity_id=uuid4(),
    created_at=datetime.utcnow(),
    updated_at=datetime.utcnow(),
    comment="Discrepancies detected in this row's data",
    sheet_name="SalesData",
    row_number=3
)

# Adding this entity to a list of annotations for the Excel file
excel_entities.append(row_entity)

Annotating a Column (ColumnEntity)

Scenario: A user wants to add a comment about an entire column (Column C) representing "Revenue" for further review.

Generated Entity:

column_entity = ColumnEntity(
    entity_id=uuid4(),
    created_at=datetime.utcnow(),
    updated_at=datetime.utcnow(),
    comment="Review revenue values for accuracy",
    sheet_name="SalesData",
    column_letter="C"
)

# Adding this entity to the list of Excel annotations
excel_entities.append(column_entity)

Annotating a Range (RangeEntity)

Scenario: A user selects a range of cells (A1:C10) to indicate that the entire section requires review.

Generated Entity:

range_entity = RangeEntity(
    entity_id=uuid4(),
    created_at=datetime.utcnow(),
    updated_at=datetime.utcnow(),
    comment="Please review all data in this range",
    sheet_name="SalesData",
    start_cell="A1",
    end_cell="C10"
)

# Adding this entity to the list of Excel annotations
excel_entities.append(range_entity)