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

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

    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

    AppTask }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`	short	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)

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, AppTask

Properties: CreatedByUserId, UpdatedByUserId, CreatedDate, UpdatedDate

Ordering¶

Entities implementing IOrderable:

NoteItem, Category, StoredFile

Property: Order (short)

Optimistic Concurrency¶

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

ID Generation¶

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

Authorization — Group scoping
File Storage — StoredFile entity
Background Jobs — RecurringTransaction processing