Skip to content

Data Models

This document describes the database schema and data models used in the Rego backend.

Overview

Rego uses SQLAlchemy ORM with async support for all database operations. The models use a declarative base with type hints for better IDE support and type safety.

All models inherit from BaseTable which provides common fields and functionality.

Base Table

Location: rego/core/models.py

class BaseTable(Base):
    __abstract__ = True

    # Primary key
    id: Mapped[UUID] = mapped_column(default_factory=uuid4, primary_key=True)

    # Audit fields
    created_by: Mapped[UUID | None] = mapped_column(default=None)
    updated_by: Mapped[UUID | None] = mapped_column(default=None)

    # Automatic timestamps
    created_at: Mapped[datetime] = mapped_column(
        DateTime(timezone=True),
        server_default=func.now(),
    )
    updated_at: Mapped[datetime] = mapped_column(
        DateTime(timezone=True),
        server_default=func.now(),
        onupdate=func.now(),
    )

    # Optimistic locking
    version: Mapped[int] = mapped_column(default=1)

Common fields on all entities:

  • id - UUID primary key
  • created_by - User who created the record
  • updated_by - User who last modified the record
  • created_at - Timestamp when record was created (UTC)
  • updated_at - Timestamp when record was last modified (UTC)
  • version - Version number for optimistic locking

User

Represents a system user with authentication credentials and profile information.

Table: user

class User(SQLAlchemyBaseUserTable[UUID], BaseTable):
    id: Mapped[UUID]

    # Authentication
    email: Mapped[str]                    # Unique, validated email
    username: Mapped[str]                 # Unique username
    hashed_password: Mapped[str]          # Argon2 hash

    # Profile
    firstname: Mapped[str | None]
    lastname: Mapped[str | None]

    # Permissions
    is_active: Mapped[bool]               # Can user log in?
    is_verified: Mapped[bool]             # Email verified?
    is_superuser: Mapped[bool]            # Admin privileges?

    # Relationships
    boards_link: Mapped[list[BoardMember]]
    boards: Mapped[list[Board]]
    assigned_cards: Mapped[list[Card]]
    assigned_checklist_items: Mapped[list[ChecklistItem]]

Relationships:

  • boards - All boards the user is a member of (many-to-many via BoardMember)
  • assigned_cards - Cards assigned to this user (many-to-many)
  • assigned_checklist_items - Checklist items assigned to this user (many-to-many)

Indexes:

  • email - Unique index
  • username - Unique index

Board

Top-level container for organizing work. Contains columns, cards, labels, and members.

Table: board

class Board(BaseTable):
    id: Mapped[UUID]
    title: Mapped[str]
    description: Mapped[str | None]

    # Relationships
    columns: Mapped[list[Column]]
    labels: Mapped[list[Label]]
    members_link: Mapped[list[BoardMember]]
    members: Mapped[list[User]]

Relationships:

  • columns - All columns in this board (one-to-many, cascade delete)
  • labels - All labels defined for this board (one-to-many, cascade delete)
  • members - All users who have access to this board (many-to-many via BoardMember)

Cascade deletes:

When a board is deleted, all related data is automatically removed: - All columns (and their cards) - All labels - All board member associations - All automation rules

BoardMember

Association table between boards and users with role-based permissions.

Table: boardmember

class BoardMember(BaseLinkTable):
    board_id: Mapped[UUID]                # Foreign key to board
    user_id: Mapped[UUID]                 # Foreign key to user
    role: Mapped[MemberRole]              # owner or member

    # Relationships
    board: Mapped[Board]
    user: Mapped[User]

Primary key: Composite of (board_id, user_id)

Roles:

class MemberRole(str, Enum):
    owner = "owner"      # Full control
    member = "member"    # Can view and edit

Indexes:

  • board_id - For finding all members of a board
  • user_id - For finding all boards of a user

Column

Vertical list within a board that contains cards. Columns are ordered using ranks.

Table: column

class Column(BaseTable):
    id: Mapped[UUID]
    board_id: Mapped[UUID]
    title: Mapped[str]
    rank: Mapped[int]                     # BigInteger for ordering
    color: Mapped[Color | None]
    is_done_column: Mapped[bool]

    # Relationships
    board: Mapped[Board]
    cards: Mapped[list[Card]]

Relationships:

  • board - The board this column belongs to (many-to-one)
  • cards - All cards in this column (one-to-many, cascade delete)

Constraints:

  • Unique constraint on (board_id, rank) - No two columns can have same rank in a board

Indexes:

  • board_id - For fetching all columns of a board
  • rank - For ordering columns

Special fields:

  • is_done_column - Marks a column as the "done" column for completed cards
  • color - Optional color for visual distinction

Card

Individual task or work item within a column. The core unit of work.

Table: card

class Card(BaseTable):
    id: Mapped[UUID]
    column_id: Mapped[UUID]
    rank: Mapped[int]                     # BigInteger for ordering
    title: Mapped[str]
    description: Mapped[str | None]

    # Dates
    start_date: Mapped[datetime | None]
    due_date: Mapped[datetime | None]

    # Status
    is_completed: Mapped[bool]
    is_archived: Mapped[bool]

    # Relationships
    column: Mapped[Column]
    labels: Mapped[list[Label]]
    assignees: Mapped[list[User]]
    checklists: Mapped[list[Checklist]]
    attachments: Mapped[list[Attachment]]

Relationships:

  • column - The column this card belongs to (many-to-one)
  • labels - Labels applied to this card (many-to-many via CardLabel)
  • assignees - Users assigned to this card (many-to-many via CardAssignee)
  • checklists - Sub-task lists within this card (one-to-many, cascade delete)
  • attachments - Files attached to this card (one-to-many, cascade delete)

Constraints:

  • Unique constraint on (column_id, rank) - No two cards can have same rank in a column

Indexes:

  • column_id - For fetching all cards in a column
  • rank - For ordering cards

Features:

  • Rich text description (markdown support)
  • Optional start and due dates for scheduling
  • Completion tracking
  • Archival without deletion

Label

Categorization tag that can be applied to cards. Scoped to a board.

Table: label

class Label(BaseTable):
    id: Mapped[UUID]
    board_id: Mapped[UUID]
    name: Mapped[str]
    color: Mapped[Color | None]

    # Relationships
    board: Mapped[Board]
    cards: Mapped[list[Card]]

Relationships:

  • board - The board this label belongs to (many-to-one)
  • cards - All cards with this label (many-to-many via CardLabel)

Indexes:

  • board_id - For fetching all labels of a board

Color format:

Colors are stored as hex strings: #RRGGBB

Example: #FF5733

CardLabel

Association table between cards and labels.

Table: cardlabel

class CardLabel(BaseLinkTable):
    card_id: Mapped[UUID]
    label_id: Mapped[UUID]

Primary key: Composite of (card_id, label_id)

This is a simple many-to-many relationship without additional attributes.

CardAssignee

Association table between cards and assigned users.

Table: cardassignee

class CardAssignee(BaseLinkTable):
    card_id: Mapped[UUID]
    user_id: Mapped[UUID]

Primary key: Composite of (card_id, user_id)

Indexes:

  • card_id - For finding all assignees of a card
  • user_id - For finding all cards assigned to a user

Checklist

Sub-task list within a card. Contains multiple checklist items.

Table: checklist

class Checklist(BaseTable):
    id: Mapped[UUID]
    card_id: Mapped[UUID]
    title: Mapped[str]
    rank: Mapped[int]                     # BigInteger for ordering

    # Relationships
    card: Mapped[Card]
    items: Mapped[list[ChecklistItem]]

Relationships:

  • card - The card this checklist belongs to (many-to-one)
  • items - All items in this checklist (one-to-many, cascade delete)

Constraints:

  • Unique constraint on (card_id, rank) - No two checklists can have same rank in a card

Indexes:

  • card_id - For fetching all checklists of a card
  • rank - For ordering checklists

ChecklistItem

Individual item within a checklist. Can be marked as completed and assigned to users.

Table: checklistitem

class ChecklistItem(BaseTable):
    id: Mapped[UUID]
    checklist_id: Mapped[UUID]
    content: Mapped[str]
    rank: Mapped[int]                     # BigInteger for ordering
    is_completed: Mapped[bool]

    # Dates
    start_date: Mapped[datetime | None]
    due_date: Mapped[datetime | None]

    # Relationships
    checklist: Mapped[Checklist]
    assignees: Mapped[list[User]]

Relationships:

  • checklist - The checklist this item belongs to (many-to-one)
  • assignees - Users assigned to this item (many-to-many via ChecklistItemAssignee)

Constraints:

  • Unique constraint on (checklist_id, rank) - No two items can have same rank in a checklist

Indexes:

  • checklist_id - For fetching all items of a checklist
  • rank - For ordering items

Features:

  • Simple text content (not markdown)
  • Completion tracking
  • Optional scheduling with dates
  • Multiple assignees supported

ChecklistItemAssignee

Association table between checklist items and assigned users.

Table: checklistitemassignee

class ChecklistItemAssignee(BaseLinkTable):
    checklist_item_id: Mapped[UUID]
    user_id: Mapped[UUID]

Primary key: Composite of (checklist_item_id, user_id)

Indexes:

  • checklist_item_id - For finding all assignees of an item
  • user_id - For finding all items assigned to a user

Attachment

File storage for cards. Files are stored as binary blobs in the database.

Table: attachment

class Attachment(BaseTable):
    id: Mapped[UUID]
    card_id: Mapped[UUID]
    name: Mapped[str]                     # Original filename
    mime_type: Mapped[str]                # e.g., "image/png"
    size: Mapped[int]                     # Size in bytes
    blob: Mapped[bytes]                   # Deferred loaded

    # Relationships
    card: Mapped[Card]

Relationships:

  • card - The card this attachment belongs to (many-to-one)

Indexes:

  • card_id - For fetching all attachments of a card

Storage:

  • Files are stored directly in PostgreSQL as LargeBinary
  • The blob field is deferred (not loaded by default)
  • Use selectinload or explicit refresh to load blob data

Size limits:

  • Maximum file size should be enforced at application level
  • Consider moving to object storage (S3) for production

AutomationRule

Defines trigger-action automation rules for boards.

Table: automation_rule

class AutomationRule(BaseTable):
    id: Mapped[UUID]
    board_id: Mapped[UUID]
    name: Mapped[str]
    is_enabled: Mapped[bool]

    # Trigger
    trigger_type: Mapped[TriggerType]
    trigger_column_id: Mapped[UUID | None]
    trigger_label_id: Mapped[UUID | None]

    # Action
    action_type: Mapped[ActionType]
    action_column_id: Mapped[UUID | None]
    action_user_id: Mapped[UUID | None]

    # Relationships
    board: Mapped[Board]
    trigger_column: Mapped[Column | None]
    trigger_label: Mapped[Label | None]
    action_column: Mapped[Column | None]
    action_user: Mapped[User | None]

Trigger types:

class TriggerType(str, Enum):
    CARD_MOVED = "card_moved"        # Card moved to specific column
    CARD_CREATED = "card_created"    # Card created in specific column
    LABEL_ADDED = "label_added"      # Specific label added to card

Action types:

class ActionType(str, Enum):
    MOVE_TO_COLUMN = "move_to_column"    # Move card to target column
    ASSIGN_MEMBER = "assign_member"      # Assign user to card

Indexes:

  • board_id - For fetching all rules of a board

Validation:

  • Trigger and action references must exist and belong to same board
  • Action user must be a board member
  • Enabled rules are evaluated on every matching event

Entity Relationships Diagram

User ─────┬─────── BoardMember ─────── Board
          │                              │
          │                              ├─── Column ─── Card ─────┬─── Checklist ─── ChecklistItem
          │                              │                         │
          │                              └─── Label ───────────────┤
          │                                                        │
          └─── CardAssignee ──────────────────────────────────────┤
          │                                                        │
          └─── ChecklistItemAssignee ─────────────────────────────┘

Card ─── Attachment

Board ─── AutomationRule

Common Patterns

Cascade Deletes

Parent-child relationships use cascade deletes:

# When board is deleted, all columns are deleted
columns: Mapped[list[Column]] = relationship(
    cascade="all, delete-orphan"
)

# When column is deleted, all cards are deleted
cards: Mapped[list[Card]] = relationship(
    cascade="all, delete-orphan"
)

Many-to-Many Relationships

Simple many-to-many (no extra data):

# Card ←→ Label
labels: Mapped[list[Label]] = relationship(
    secondary="cardlabel",
    back_populates="cards"
)

Many-to-many with extra data (use association object):

# Board ←→ User (with role)
members_link: Mapped[list[BoardMember]] = relationship(
    back_populates="board",
    cascade="all, delete-orphan"
)

Lazy Loading

Relationships are lazy-loaded by default. Use eager loading when needed:

# Eager load with selectinload
result = await session.execute(
    select(Card)
    .where(Card.id == card_id)
    .options(selectinload(Card.labels))
)

# Refresh with specific relationships
await session.refresh(card, ["labels", "assignees"])

Deferred Columns

Large binary data uses deferred loading:

blob: Mapped[bytes] = deferred(mapped_column(LargeBinary))

# Explicitly load when needed
await session.refresh(attachment, ["blob"])

Ranking System

Cards, columns, checklists, and checklist items use integer ranks for ordering.

Why integers instead of strings?

  • O(1) comparison (faster than lexicographic)
  • Fixed space (8 bytes per rank)
  • Simpler to understand and debug
  • Works with database indexes efficiently

How it works:

  1. Items are ordered by rank (ascending)
  2. New items get rank between neighbors
  3. When space runs out, all items are rebalanced

Example:

Item A: rank = 1000
Item B: rank = 2000
Item C: rank = 3000

Insert between A and B:
Item D: rank = 1500

Insert between D and B:
Item E: rank = 1750

... eventually space runs out, trigger rebalance

Database Constraints

Foreign Keys

All foreign keys have defined behavior on delete:

  • CASCADE - Delete child when parent is deleted
  • SET NULL - Set to null when parent is deleted (optional fields)
  • RESTRICT - Prevent deletion if children exist (default)

Unique Constraints

Multiple unique constraints ensure data integrity:

  • (board_id, rank) for columns - Prevents duplicate ranks
  • (column_id, rank) for cards - Prevents duplicate ranks
  • (card_id, rank) for checklists - Prevents duplicate ranks
  • (checklist_id, rank) for items - Prevents duplicate ranks
  • email for users - One email per account
  • username for users - One username per account

Check Constraints

Could be added for additional validation:

  • Rank must be non-negative
  • Start date must be before due date
  • File size must be positive

Migrations

Database schema changes are managed with Alembic.

Location: backend/rego/alembic/

Common commands:

# Generate migration from model changes
alembic revision --autogenerate -m "description"

# Apply migrations
alembic upgrade head

# Rollback one version
alembic downgrade -1

# View migration history
alembic history

Migration naming:

  • Use descriptive names: add_checklist_tables, add_board_members
  • Include date in filename automatically
  • Review autogenerated migrations before applying

Performance Considerations

Indexes

Indexes are created for:

  • Foreign keys (for joins)
  • Rank fields (for ordering)
  • Unique fields (for lookups)

Query Optimization

  • Use selectinload() for relationships to avoid N+1 queries
  • Defer loading of large blobs
  • Only load needed relationships
  • Use database connection pooling

Scaling

Current design supports:

  • Thousands of boards
  • Hundreds of columns per board
  • Thousands of cards per column
  • Limited by rank rebalancing overhead

For very large boards (10k+ cards), consider:

  • Pagination for card lists
  • Virtual scrolling in UI
  • Background rebalancing jobs
  • Archival of old cards