Skip to content

Data Model

[!abstract] Summary Core entities, their relationships, and key design patterns used in the database schema.

Entity Relationship Diagram

erDiagram
    Group ||--o{ User : contains
    Group ||--o{ Marker : owns
    Group ||--o{ Note : owns
    Group ||--o{ Transaction : owns
    Group ||--o{ Category : owns
    Group ||--o{ BudgetAccount : owns
    Group ||--o{ RecurringTransaction : owns
    Group ||--o{ GroupModule : has
    Group ||--o{ ActionHistory : tracks

    User ||--o{ RefreshToken : has
    User ||--o{ Note : creates
    User }o--o| BudgetAccount : default
    User ||--o{ ActionHistory : performs

    Marker ||--o{ StoredFile : has_images
    User ||--o| StoredFile : has_profile_pic

    Note ||--o{ NoteItem : contains

    Transaction }o--o| Category : categorized_by
    Transaction }o--|| BudgetAccount : from
    Transaction }o--o| BudgetAccount : to_transfer

    RecurringTransaction }o--o| Category : categorized_by
    RecurringTransaction }o--|| BudgetAccount : from
    RecurringTransaction }o--o| BudgetAccount : to_transfer

    BackgroundTask }o--o| User : for_user

Core Entities

User

Property Type Description
UserId Guid Primary key
UserName string Login name
Nickname string Display name
Email string Email address
PasswordHash string Bcrypt hash
RegistrationStatus enum Pending/Confirmed/Declined/Deactivated
EmailStatus enum Unconfirmed/Confirmed/ChangePending
Language enum English/Czech
Role enum User/Admin
GroupId Guid? FK to Group (null until approved)
DefaultBudgetAccountId Guid? FK to BudgetAccount

Group

Property Type Description
GroupId Guid Primary key
Name string Group name
Currency enum CZK/EUR/USD/PLN
PermissionsVersion int Cache invalidation version

Marker

Property Type Description
MarkerId Guid Primary key
Title string Marker title
Description string? Optional description
Date DateOnly Marker date
Rating short Rating value
Latitude decimal Geographic latitude
Longitude decimal Geographic longitude
GroupId Guid FK to Group

Note

Property Type Description
NoteId Guid Primary key
Title string Note title
Color string Hex color code
Icon string Icon identifier
IsPrivate bool If true, only visible to creator
IsPinned bool Pinned to top
UserId Guid FK to User (creator)
GroupId Guid FK to Group

NoteItem

Property Type Description
NoteItemId Guid Primary key
Text string Item text
Url string? Optional associated URL
IsCompleted bool Checkbox status
Order int Position in note
NoteId Guid FK to Note

Transaction

Property Type Description
TransactionId Guid Primary key
Description string? Optional description
Amount decimal Transaction amount
Date DateTime Transaction date
Type enum Income/Expense/Transfer
CategoryId Guid? FK to Category
BudgetAccountId Guid FK to source BudgetAccount
TargetBudgetAccountId Guid? FK to target (for Transfer)
GroupId Guid FK to Group

RecurringTransaction

Property Type Description
RecurringTransactionId Guid Primary key
Name string Template name
Amount decimal Amount per occurrence
Type enum Income/Expense/Transfer
Frequency enum Weekly/Monthly/Yearly
StartDate DateTime Recurrence start
EndDate DateTime? Recurrence end (null = ongoing)
DayOfWeek DayOfWeek? For weekly
DayOfMonth int? For monthly (1-31)
UseLastDayOfMonth bool Use last day of month
Month Month? For yearly
IsEnabled bool Is template active
LastProcessedDate DateTime? Last transaction generated

Category

Property Type Description
CategoryId Guid Primary key
Name string Category name
Color string Hex color code
Icon string Icon identifier
Order short Display order
GroupId Guid FK to Group

BudgetAccount

Property Type Description
BudgetAccountId Guid Primary key
Name string Account name
IsDisabled bool Is account disabled
GroupId Guid FK to Group

StoredFile

Property Type Description
FileId Guid Primary key
Category enum ProfilePicture/MarkerImage
RelatedEntityId Guid User or Marker ID
Name string Original filename
Extension string File extension
StorageKey string? R2 storage path
ProcessingStatus enum Pending/Processing/Completed/Failed
Order short Display order
GroupId Guid? FK to Group (null for profile pics)

ActionHistory

Records entity change history for audit trail purposes.

Property Type Description
ActionHistoryId Guid Primary key
GroupId Guid FK to Group
ActorUserId Guid? FK to User (null for system/background actions)
Action enum Created/Updated/Deleted/Restored
EntityType enum Marker/Transaction/Note/Category/etc.
EntityId Guid ID of the affected entity
Timestamp DateTime When the action occurred
CorrelationId Guid? Groups actions from the same HTTP request
Metadata string? JSON with additional context (changed fields)

Tracked entity types: Marker, Transaction, Note, Category, RecurringTransaction, NoteItem, BudgetAccount.

Design Patterns

Soft Delete

Entities implementing ISoftDeletable — see Soft Delete Pattern for the full list. Properties: IsDeleted, DeletedDate.

Auditing

Entities extending AuditableEntity:

  • Group, Marker, Note, NoteItem, Transaction, RecurringTransaction, Category, BudgetAccount, StoredFile

Properties: CreatedByUserId, UpdatedByUserId, CreatedDate, UpdatedDate

Note: BackgroundTask does NOT extend AuditableEntity — it has its own CreatedDate/UpdatedDate properties that are set directly in DataContext.SaveChangesAsync.

Ordering

Entities implementing IOrderable:

  • Category, StoredFile

Property: Order (short)

NoteItem has its own Order property (int) for larger capacity.

Optimistic Concurrency

Uses PostgreSQL xmin shadow property — see Concurrency Pattern for the entity list.

Action History Tracking

Automatic change tracking via ActionHistoryInterceptor — see Interceptors Pattern for configuration.

Tracked entities: Marker, Transaction, Note, Category, RecurringTransaction, NoteItem, BudgetAccount.

ID Generation

All entity IDs are Guid using UUID v7 (time-ordered) via UuidV7ValueGenerator.