Domain Model

This document covers every entity in the Beyou domain, explaining what each one does for the user and how it is structured in the database. The goal is a clear mental model of the data layer before reading or writing code.

One ground rule shapes everything here: the schema belongs to Flyway. Migrations under db/migration/ create and evolve every table, and Hibernate runs with ddl-auto: validate in every environment, so an entity mapping that disagrees with the migrations fails at startup instead of silently rewriting the schema.

The big picture

Beyou's domain revolves around a simple idea: a user creates habits, tasks, and goals, organizes them into categories, and executes them through daily routines. Every check generates XP and writes history. Around that core loop sit the supporting families: daily history rows, immutable routine snapshots, the Focus Mode's cycles and micro-tasks, mood entries and the journal, dated calendar tasks, the notes on the Ideas board, the study notebook, feedback threads, and the AI agent's chats.

flowchart TD
  U["👤 User"]
  U --> CAT["📂 Categories"]
  U --> HAB["💪 Habits"]
  U --> TSK["📝 Tasks"]
  U --> GOL["🎯 Goals"]
  U --> RTN["📋 Routines"]

  CAT -.-|"tags"| HAB
  CAT -.-|"tags"| TSK
  CAT -.-|"tags"| GOL

  RTN --> SEC["📑 Sections"]
  SEC --> HG["Habit Groups"]
  SEC --> TG["Task Groups"]
  HG -.-|"references"| HAB
  TG -.-|"references"| TSK

  HG --> CHK["✅ Checks"]
  TG --> CHK
  CHK -->|"generates"| XP["🎮 XP"]
  CHK -->|"writes"| HIST["📅 Daily history<br/>check + xp rows"]
  RTN -->|"frozen daily into"| SNAP["🧊 Snapshots"]

User

Product role: the central entity. Every piece of data in Beyou belongs to a user. The user has a profile (name, photo, motivational phrase), preferences (theme, language, timezone, dashboard widgets), a gamification state (XP, level, streaks), and two small text fields the AI agent uses as memory.

Key fields

Field Type Notes
id UUID Auto-generated
name String
email String Unique
password String BCrypt hash
isGoogleAccount boolean True for OAuth users. Means Google specifically, not federation in general: an account created through another provider has this false
emailVerified boolean New accounts confirm by e-mail before first login
verificationToken / verificationTokenExpiry String / LocalDateTime Verification state lives as columns here, not in a separate entity
verificationTokenSentAt Instant When the last verification mail went out, read by the resend cooldown. An Instant against the LocalDateTime beside it because it is compared to a clock and never displayed. Null means no mail on record, which is how every row predating the column reads, and how a row whose send failed is reset
perfilPhrase / perfilPhraseAuthor String Optional motivational quote
perfilPhoto String (512) The Google CDN avatar URL, set at OAuth sign-in. NOT a path to an uploaded photo: an upload writes {upload-dir}/user-photos/{userId}.jpg and never touches this column, so it stays null for accounts that never signed in with Google. A profile is served the file first and this second, and removal has to clear both
themeInUse / languageInUse String Preferences. languageInUse is en or pt, set at signup from the language the client is showing and filled on boot for older accounts; empty means English to every prompt and mail
timezone String Required. The account's IANA zone, taken from the client at signup and falling back to UTC. Every date the app ever writes is resolved against it
timezoneSource TimezoneSource enum DEFAULT, DETECTED or EXPLICIT: whether the zone above was ever actually chosen. Only DEFAULT may be corrected automatically
widgetsIdInUse List of String Active dashboard widget IDs
isTutorialCompleted boolean Onboarding flag
userContext String (2000) The AI agent's global memory about this user
xpDecayStrategy XpDecayStrategy enum GRADUAL, FLAT, or TIME_WINDOW; how late check-ins lose XP
maxConstance Integer Highest streak ever achieved
completedDays Set of LocalDate Days with completed routine activity
userRole UserRole enum USER or ADMIN (admin only by manual database update)
constanceConfiguration ConstanceConfiguration enum ANY or COMPLETE

Embedded: XpProgress and CheckProgress (both described below).

Relationships: the user owns six collections, all OneToMany with cascade ALL and orphan removal: categories, habits, tasks, goals, routines, and routine snapshots. Account deletion works entirely through this cascade; the tasks collection was added precisely to close a gap in it.

Business logic: implements Spring Security's UserDetails. Streak calculation used to live here as a walk over completedDays; it now belongs to UserStreakService in the checkday package, on top of the daily history rows.

Category

Product role: categories organize habits, tasks, and goals by life area ("Health", "Career"). Categories earn XP too, so users can see where they invest the most effort.

Key fields: name, description, iconId, timestamps.

Embedded: XpProgress only. Deliberately no CheckProgress: a category earns XP but is never itself checked.

Relationships

Habit

Product role: a behavior the user wants to build. Each habit has its own level and XP progression, plus a streak record, so showing up keeps paying.

Key fields

Field Type Notes
name / description / iconId String
importance Integer 1 to 5
dificulty Integer 1 to 5. Yes, misspelled: it is the real field, column, and wire-format name
motivationalPhrase String Optional

Embedded: XpProgress and CheckProgress. The old standalone constance counter is gone; CheckProgress replaced it.

Relationships

Task

Product role: a concrete action. Unlike habits, tasks can be one-time ("Buy groceries"). One-time tasks get a soft-delete date after being checked, giving the system a grace period before a scheduler removes them.

Key fields

Field Type Notes
name / description / iconId String
importance / dificulty Integer, nullable Optional, unlike on a Habit: 1 to 5 when set, null when the user chose nothing. A missing value counts as 1 in the check XP formula. Same misspelling as Habit on the column; the wire accepts both spellings
oneTimeTask boolean True for non-recurring tasks
markedToDelete LocalDate Set on completion of one-time tasks; TaskCleanupScheduler collects them

Embedded: CheckProgress only. A task carries no XP of its own; checking one feeds the user, the routine, and the categories.

Relationships: belongs to a User (ManyToOne); tagged by Categories (ManyToMany, owning side, join table task_category).

Goal

Product role: a measurable objective ("Run 100 km"). Progress is currentValue against targetValue, and completion pays a calculated XP reward.

Key fields

Field Type Notes
name / iconId / description String
targetValue / currentValue Double The measurable part
unit String km, books, etc.
complete Boolean Completion flag
motivation String Optional
startDate / endDate LocalDate Window and deadline
xpReward double Calculated on completion
completeDate LocalDate
status GoalStatus enum NOT_STARTED, IN_PROGRESS, COMPLETED (stored as strings)
term GoalTerm enum SHORT_TERM, MEDIUM_TERM, LONG_TERM
parentId UUID, nullable The goal this one sits under. Null for a top-level goal
archivedAt Instant, nullable When the goal was put away. Null while active. Not a status (see Archiving)

Relationships: belongs to a User (ManyToOne); tagged by Categories (ManyToMany, owning side, join table goal_category); optionally nested under another Goal (ManyToOne to itself, parent_id, lazy, with a read-only mirror column so the id reaches the response without touching the relation).

Nesting: a goal may hang under another goal, three levels at most: a big goal, a medium one under it, a small one under that. The tree is a single nullable self-reference, not a second entity, because every read already loads all of a user's goals in one query and both clients assemble the tree from parentId in shared code. The rules live once, in GoalService.resolveParent, and the pickers on web and mobile only pre-filter what it would refuse: the parent must be the same user's (GOAL_NOT_OWNED), must not be the goal itself or a descendant (GOAL_PARENT_CYCLE), and the chain must fit in three levels counting both the ancestors above the new parent and the subtree already under the goal (GOAL_DEPTH_EXCEEDED). A parent is a normal goal with its own target, unit and XP; the sub-goal summary shown on cards is computed on the client. The one server-side link between levels: the first increment on a sub-goal moves a NOT_STARTED parent to IN_PROGRESS, the same way a goal's own first increment does.

Archiving: a goal can be put away without being deleted. It is a nullable archived_at timestamp and not a fourth GoalStatus, because status carries the complete/status invariant below and a finished goal and an abandoned one both archive: archiving moves no XP and leaves status, complete and xpReward alone. PUT /goal/archive takes the state wanted, not a toggle. The sub-goals go with the goal, all stamped with the same instant, and that instant is how restoring knows what came along: it clears the goal and exactly the descendants carrying it, so a sub-goal archived on its own earlier stays archived. Nothing new goes under an archived goal (GOAL_PARENT_ARCHIVED), but an existing link survives an edit, since every edit sends the parent back. GET /goal still returns archived goals with the field set, so the list stays one cached query, and each screen filters: the goals page shows them only under its Archived view, and the viewer, the dashboard and the parent pickers leave them out. On the server the daily briefing, the category card's goal list and the agent's plans skip them. A sub-goal restored on its own under a parent still archived shows as a top-level goal, the way a deleted parent's children do.

Invariant worth knowing: constructing a goal with status COMPLETED silently downgrades it to IN_PROGRESS. Only the explicit complete endpoint pays XP, so nobody can post a pre-completed goal and farm a reward. Nesting changes none of that: a parent pays its own XP on its own completion, whatever its children did.

XP calculation: GoalXpCalculator multiplies four factors.

flowchart LR
  TV["🎯 Target Value"] --> BASE["Base XP<br/>50 / 100 / 200 / 300"]
  TV --> DIFF["Difficulty<br/>1.0x – 2.0x"]
  DL["📅 Days in window"] --> URG["Urgency<br/>1.0x – 1.5x"]
  CD["✅ Completed before deadline?"] --> CON["Consistency<br/>1.0x – 1.3x"]
  BASE --> TOTAL["Total XP Reward"]
  DIFF --> TOTAL
  URG --> TOTAL
  CON --> TOTAL

The two shared components

Two embeddables carry the gamification state, and which entities embed which is a design decision in itself.

XpProgress

Field Type Notes
xp double Total accumulated XP
level int Current level
actualLevelXp / nextLevelXp double Boundaries of the current level

Embedded by User, Category, Habit, and Routine: the four things that level up. addXp and removeXp walk the level curve in both directions through a level-lookup function, and cap at the top level.

CheckProgress

Field Type Notes
check_current_streak / check_best_streak int Streaks
check_total_check_ins int Lifetime count
check_first_check_in_date / check_last_check_in_date LocalDate Nullable bounds

Embedded by User, Habit, Task, and Routine: the four things that get checked. Category is excluded on purpose, and Task appears here despite having no XP.

Routine

Product role: the daily execution tool, in one of two shapes. A daily routine has sections ("Morning", "Work", "Evening"), each holding habit and task groups with their own time windows. A list routine drops both: a flat, ordered checklist the user ticks off whenever they like during the day. Checking items generates XP across every related entity, identically for the two.

Inheritance: Routine is an abstract base using single-table inheritance with a dtype discriminator. DiaryRoutine is the only concrete type, and the shape is a routineType column (DAILY or LIST) rather than a second subclass. That was deliberate: the whole check path casts the routine it reaches through the item's section to DiaryRoutine on every branch, and the snapshot writer, the day-close resolver and the schedule service are all typed to it. A column leaves every one of those untouched, so a list routine is scheduled, snapshotted, checked, streaked and levelled by exactly the code that already serves a daily one. routines_routine_type_check mirrors the enum; adding a value in Java without adding it there makes every write of the new kind fail.

A list routine still stores its items in one RoutineSection, created server-side with null start and end times. That section is an internal representation and never reaches a client: the API takes and returns a flat items array instead.

flowchart TD
  R["📋 Routine<br/>(abstract, single-table)"]
  R --> DR["📋 DiaryRoutine<br/>routineType: DAILY | LIST"]
  DR --> RS1["📑 Section: Morning<br/>(DAILY)"]
  DR --> RS2["📑 Section: Evening<br/>(DAILY)"]
  DR --> LS["📑 One implicit section<br/>(LIST, no times)"]
  RS1 --> HG1["💪 Habit Group"]
  RS1 --> TG1["📝 Task Group"]
  LS --> HG2["💪 Habit Group"]
  HG1 --> HC["✅ HabitGroupCheck"]
  TG1 --> TC["✅ TaskGroupCheck"]

Routine (abstract base)

Fields: name, iconId, routineType. Embeds XpProgress and CheckProgress.

routineType is never null: the migration that added it defaults to DAILY, so every routine written before the list shape existed, and every client that has never heard of the field, keeps exactly the behaviour it had. Read it through isList() / isDaily() rather than comparing the enum at call sites — those two are the seam, and what a reader greps for to find everywhere the shapes actually diverge.

Relationships

DiaryRoutine

Extends Routine, adding routineSections (OneToMany, cascade ALL, orphan removal, ordered by orderIndex).

RoutineSection

Fields: name, iconId, startTime, endTime, orderIndex, favorite.

The times are nullable, and a list routine's single section leaves both null. Its name and icon follow the routine's own, because the column is NOT NULL and because that name is what the snapshot writer copies onto every SnapshotCheck — a history row reading "Errands" is at least true.

Relationships: belongs to a Routine (ManyToOne); contains HabitGroups and TaskGroups (OneToMany, cascade ALL, orphan removal). One quirk to be aware of: these collections are unidirectional and mapped through join tables (routine_sections_habit_groups, routine_sections_task_groups), while ItemGroup also carries its own routine_section_id column. The section-to-group link is effectively mapped twice.

Schedule

Product role: which days of the week a routine is active, which decides whether it appears on the dashboard for a given day.

The entity is minimal: an id plus a set of WeekDay enums stored in the schedule_days collection table. The foreign key lives on the routine's side. One gotcha: the enum identifiers are capitalized words (Monday, Tuesday, ...), not SCREAMING_CASE, and they are stored as strings, which is also what a CHECK constraint on the table enforces and what every response emits. Incoming JSON is the one place that forgives a mismatch — any letter case, and the Portuguese day names, resolve to the same constant — because an agent tool call that guessed SCREAMING_CASE used to cost an entire LLM round trip to correct.

Item groups and checks

Product role: placing a habit or task inside a routine section creates a "group", the trackable instance that gets checked or skipped each day. Each check is a historical record with date, time, and the XP it generated.

ItemGroup (abstract, joined inheritance): startTime, endTime, orderIndex, and the ManyToOne back to its section. orderIndex is read only by list routines, which have no times to sort by; a daily routine orders its items by startTime in both clients and ignores the column, though it is written for both so the two shapes share one merge path. Concrete types HabitGroup (references a Habit) and TaskGroup (references a Task), each owning their check collections (cascade ALL, no orphan removal, so history survives).

BaseCheck (abstract, joined inheritance): checkDate, checkTime, checked, skipped, xpGenerated. Concrete types HabitGroupCheck and TaskGroupCheck, each owned by their group.

Daily history: EntityCheckDay and EntityXpDay

Product role: the dashboard's history and progress widgets need per-day answers ("what happened to this habit on Tuesday?", "how much XP did this category earn this week?"). Walking the raw check tables for that is expensive and fragile, so two dedicated history tables record one row per entity per day.

EntityCheckDay (table entity_check_day): one outcome per entity per day, unique on (owner_type, owner_id, day).

EntityXpDay (table entity_xp_day): the net XP delta per entity per day, same uniqueness pattern.

Routine snapshots

Product role: routines change. Sections get renamed, habits get removed, whole routines get deleted. Without snapshots, yesterday's view of your day would silently rewrite itself. So every scheduled day, each routine is frozen into an immutable copy, and past days render exactly as they looked.

RoutineSnapshot (table routine_snapshot): unique per (routine, day).

SnapshotCheck (table snapshot_check): one row per habit or task group in the frozen routine.

Late check-ins and XP decay: checking a past day through a snapshot still pays XP, but decayed according to the user's chosen XpDecayStrategy:

Strategy Behavior
GRADUAL 0.8x one day late, then 0.6x, 0.4x, and 0.2x from four days on
FLAT 0.5x no matter how late
TIME_WINDOW Full XP up to two days late, nothing after

The snapshot scheduler runs per timezone, using each account's own timezone column, so a routine is frozen at that user's midnight rather than the server's.

Focus Mode history

Product role: the Focus Mode is the screen that stays open while the person executes the day, one item at a time, with a pomodoro timer and a short list of small things to do between cycles. Its history answers a different question from the snapshots — not "what did I do" but "how did I execute it".

FocusCycle (table focus_cycles): one row per COMPLETED cycle.

FocusMicroTask (table focus_micro_tasks): a small thing done alongside one routine item, on one day.

How it reaches the snapshot: the day's snapshot response joins both tables on SnapshotCheck.originalGroupId, so each check row carries the micro-tasks created for that habit or task and the count of pomodoros run on it. Two reads for the day, joined in memory — never one lookup per check row, which would be an N+1 sized by the routine on the history screen.

Mood entries and the journal

Product role: how a day felt, and anything the person wanted to keep about it. Habits, goals and routines all record what was DONE. None of them records how it went.

MoodEntry (table mood_entries): one row per user per day, added in V31.

Privacy: the note is the one place in the product where somebody writes at length for themselves. It never reaches browser storage (the mood slice is on the web persist blacklist and mobile redux is in-memory), the account export carries every entry with the words intact, and the assistant's read tool returns dates, levels and whether a day has a note, never the note itself.

Calendar tasks

Product role: a one-off obligation that belongs to a date, like signing a lease or booking the dentist, with a reminder before it. Before the calendar the only way to get one into the app was a one-time task added to a routine, which was slow enough that it rarely happened.

CalendarTask (table calendar_tasks): one row per task, added in V40.

Field Type Notes
title String (255) Required, 2 to 255 characters
note String (1000), nullable A short note, not a journal. The cap sits in the column as well, so a client that skips the DTO cannot put a novel in a reminder mail
taskDate LocalDate The owner's own day, like every other date here
taskTime LocalTime, nullable Minutes only. Null means "some time that day", and the reminder then uses the owner's default reminder time
advanceMinutes int How long before the time the reminder fires. 0 to 10080 (a week), with a CHECK mirroring the DTO; the clients offer up to a day
completedAt Instant, nullable Completion is this timestamp. There is no separate flag
xpAwarded double What completing paid, so un-completing takes back exactly that and not whatever the formula says that day
acknowledgedAt Instant, nullable When the browser notification was clicked
reminderEmailSentAt Instant, nullable When the fallback mail went out
createdAt / updatedAt Instant updatedAt is also how the mail job tells that a task was edited while its mail was being sent

Quick notes (the Ideas board)

Product role: an inbox for raw ideas. Somebody gets a thought out of their head in a few seconds, as a post-it on a free board, and decides later whether it becomes a task or goes in the bin. No XP and no streak.

QuickNote (table quick_notes): one row per note, added in V42.

Field Type Notes
text String (500) Required, stored with the ends trimmed. Post-it length. The cap sits in the column too, so a client that skips the DTO cannot store more than the board can draw
color QuickNoteColor (varchar 16) YELLOW, PINK, BLUE, GREEN, PURPLE or ORANGE, default YELLOW, with a CHECK mirroring the enum. The declaration order is the tidy order
x / y int The note's top-left corner in board units, the same on every client. Each between -100000 and 100000, with a CHECK mirroring QuickNote.COORD_LIMIT
z int Stacking order among the owner's notes, higher in front
archivedAt Instant, nullable Null while the note is on the board
convertedTaskId UUID, nullable The task the note became. A bare id rather than a mapped relation
createdAt / updatedAt Instant

Study notebook

Product role: where somebody organises a subject they are learning. A topic is laid out as a roadmap board, every node opens a page of notes, sources attach to pages for a grounded study AI, and flashcards come back on a spaced schedule. Nine tables arrived together in V34, every one cascading from users. The rules and the class that owns each are in the study notebook topic.

NotebookPage (table notebook_pages): everything is a page, a topic included.

Field Type Notes
id UUID
user_id UUID Owner, cascade
kind NotebookPageKind TOPIC or PAGE, varchar with a CHECK
parent_id UUID Null only on a topic. Cascade: deleting a page deletes its subtree
topic_id UUID The root topic, stored on the row so a topic's tree is one indexed read. Null on the topic itself
title / icon / description varchar 255 / 64 / 512
content text The BlockNote document as JSON. Text and not JSONB, like every other JSON this schema stores, because nothing queries inside it
content_text text Plain text extracted on the server by BlockText on every save, never taken from the client. What the AI reads and the export ships
content_revision bigint Goes up on every write of content; every writer writes with a compare-and-set on it, and a save from an older revision is refused (NOTEBOOK_CONTENT_CONFLICT) and merged by the client. Added in V37
status NotebookStatus TO_STUDY, STUDYING or DONE. Stored, written only by NotebookProgressService
status_manual boolean Set by hand on a page whose board has nodes. False lets the nodes decide
position integer Order among siblings in the tree
goal_id / category_id / habit_id UUID Optional links on a topic, each ON DELETE SET NULL. The category receives the topic's XP
done_xp_at timestamptz When the 15 XP for finishing was paid. Set once and never cleared, so done, undone, done pays once
last_opened_at timestamptz Feeds "continue studying". Written by an UPDATE query that never loads the row dirty
study_goal varchar(300) The study room's goal for the page, sent ahead of the passages with every answer. Added in V36
study_scope varchar(16) Whose notes the study AI reads: PAGE (the page and the pages above, the default), SUBTREE or TOPIC. CHECK-constrained
study_setup_at timestamptz When the study room was last set up; null opens it on its setup screen

A CHECK constraint holds the shape: a TOPIC has no parent and no topic, a PAGE has both. The entity is @DynamicUpdate, so the autosave and a status change landing in the same moment each write only their own columns.

NotebookBoardNode (notebook_board_nodes) and NotebookBoardEdge (notebook_board_edges): one board per page, keyed by board_page_id.

NotebookCard (notebook_cards) and NotebookCardReview (notebook_card_reviews):

NotebookSource (notebook_sources) and the chunks (notebook_source_chunks):

NotebookStudyOutput (notebook_study_outputs) and NotebookChatMessage (notebook_chat_messages):

NotebookRoadmapDraft (notebook_roadmap_drafts), added in V35:

Study cycles, seven tables added in V39. A cycle is an ordered queue of study blocks over notebook topics, studied lap after lap; the rules are in the study notebook topic. The enums are varchar columns with a CHECK, as in V19, and the small numbers are integer for the reason V31 gives.

StudyCycle (study_cycles) and StudyCycleDay (study_cycle_days):

StudyCycleTopic (study_cycle_topics): a cycle's subject, any notebook page (a root topic or a page under one), kept in topic_id for history, with importance and mastery (1 to 5 each, the planner's weights), an optional minutes_override per lap (5 to 600), position, and active.

StudyCycleBlock (study_cycle_blocks): one block of the queue. Configuration only, so a re-plan can rewrite it without touching what was studied.

StudyCycleLapBlock (study_cycle_lap_blocks) and StudyCycleLap (study_cycle_laps):

StudySession (study_sessions): study logged by hand. topic_id (cascade), an optional page_id (SET NULL), minutes 1 to 600, studied_on (the owner's local date, which only places the session in the history), a note of up to 200 characters, and created_at. cycle_id and lap_number record where the minutes were credited, so a delete takes them back from that lap and no other; both are null when nothing was credited, and cycle_id is SET NULL when the cycle is deleted, which keeps the session as the topic's history. Indexed on (user_id, studied_on).

FederatedIdentity

Product role: one external identity an account may be entered through, beyond the password and Google. Added in V29.

Identity is the pair (issuer, subject) and nothing else. Both come from a verified ID token and neither can be reassigned to a different person by anyone but that issuer. The address the provider claimed is kept as a record of what was said, never as a way to find a user, because a provider that could reach an account by asserting its address could reach every account.

Key fields

Field Type Notes
id UUID Auto-generated
user User ON DELETE CASCADE, so account deletion is never blocked by a link
issuer String The iss claim, compared byte-for-byte, never normalised
subject String The sub claim. UNIQUE together with issuer
emailAtLink String What the provider claimed at link time. Recorded, never queried
createdAt LocalDateTime
lastLoginAt LocalDateTime Touched on every sign-in through this identity

Google rows appear lazily rather than by backfill: its subject was never stored, so the row is written on the account's next Google sign-in. See the security document for the resolution rule these rows feed.

Feedback

Product role: users report bugs and ask for features inside the app; an admin reads, replies, and tracks status.

AI agent chats

Product role: the agent chat that can create routines and answer questions keeps its conversations in the domain, with two layers of memory.

Auth and account entities

Five small entities hang off the account. Three hold hashes for the security flows, all ManyToOne to User; the other two hold the mail preferences and a log of the engagement mail they allowed:

Entity Table What it holds
RefreshToken refresh_tokens Hash of the 15-day refresh token, expiry, revokedAt
PasswordResetToken password_reset_tokens Hash of the reset token, expiry, usedAt
AccountDeletionCode account_deletion_codes BCrypt hash of a six-digit code, expiry, usedAt, and an attempts counter that kills the code after a few wrong guesses
NotificationPreferences notification_preferences Whether the account may be sent engagement mail, the token an unsubscribe link carries, and since V40 how calendar reminders reach the account (columns below). OneToOne rather than ManyToOne, keyed by the user's own id via @MapsId so the key and the association cannot disagree
NotificationSend notification_sends One row per engagement mail actually sent: the kind, and the RECIPIENT'S local date rather than the server's. A UNIQUE constraint on (user, kind, day) is what stops the hourly pass mailing the same thing twice; the same rows answer the per-account gap and the global daily cap

E-mail verification is the exception: it lives as columns on the users table rather than as its own entity. That also means the token sits there in plaintext, unlike the reset and deletion tokens beside it, which are stored as BCrypt hashes.

The unsubscribe token is stored raw too, and that one is a decision rather than an inheritance. The three above are one-shot secrets, so a hash is free. An unsubscribe token is stable — every engagement mail for the rest of the account's life links to it — and a hash cannot be un-hashed to build that link, so hashing would force a new token per send and kill the link in every message already delivered. A row's absence means the account has never been mailed and never opened the setting; readers must treat that as opted in.

Unlike the three token tables, this one has no expiry and no single-use marker: a preference is not spent by being used.

notification_preferences columns

Column Type Default Notes
user_id UUID Primary key and foreign key to users
engagement_email boolean true Engagement nudges. The one switch the unsubscribe link flips
reminder_email boolean true The calendar's fallback mail for a reminder nobody reacted to. Independent of engagement_email: turning off nudges nobody asked for must not silence a reminder somebody set
reminder_web boolean true Browser notifications for calendar reminders. Stored so the choice follows the account; each browser still grants its own permission, and only the client reads it
reminder_default_time time 09:00 When a calendar task with no time of its own reminds, before its advance is taken off
unsubscribe_token String (64) Stored raw, for the reason above
created_at / updated_at Instant

The three reminder columns arrived in V40 with constant defaults, and a missing row reads as those same defaults, so NotificationPreferencesService and the migration have to agree on them. The calendar reads the settings without creating a row, inside read-only transactions where getOrCreate could not insert.

XP progression system

The level curve

XpByLevel (table xp_by_level) is a pure reference table: one row per level, holding the XP threshold to reach it. It is seeded by a repeatable Flyway migration with a quadratic curve:

threshold(level) = round(50 × level²)

Levels run 0 to 100. Early levels come fast (level 2 costs 200 XP), late ones demand sustained effort (level 100 sits at 500,000). Lookups are cached per level, and XpProgress walks this curve in both directions when XP is added or removed.

XP flow on a routine check

sequenceDiagram
  participant U as User
  participant R as Routine Service
  participant X as XpProgress
  participant H as History tables

  U->>R: Check habit in routine
  R->>X: habit.addXp / category.gainXp / routine.addXp / user.addXp
  R->>R: Record HabitGroupCheck with xpGenerated
  R->>H: Write EntityXpDay deltas (user, category, habit, routine)
  R->>H: Write EntityCheckDay outcome (DONE)
  R-->>U: Updated XP across all entities

Inheritance strategies

Strategy Used by How it works
Single table Routine → DiaryRoutine One table with a dtype discriminator. Fast queries; tolerable NOT NULL embedded columns only because there is a single subclass.
Joined ItemGroup → HabitGroup / TaskGroup, BaseCheck → HabitGroupCheck / TaskGroupCheck Base table plus child tables joined by foreign key. Cleaner schema, one more join per query.

Daily Briefing

Product role: the dialog a user meets on the first dashboard open of a new day. What is still open from yesterday, and what today holds.

DailyBriefing (table daily_briefing): at most one row per user per day, added in V32.

Cascade and deletion rules

Understanding the cascades matters most at account deletion, which relies on them end to end.

Parent Children Cascade Orphan removal
User Categories, Habits, Tasks, Goals, Routines, RoutineSnapshots ALL Yes. Deleting a user removes everything
User (DB level) EntityCheckDay, EntityXpDay rows ON DELETE CASCADE Handled by the database FK
DiaryRoutine RoutineSections ALL Yes
RoutineSection HabitGroups, TaskGroups ALL Yes
Routine Schedule REMOVE No. Unscheduling is explicit
Habit HabitGroups ALL No. Deleting a habit does not silently rewrite routines
HabitGroup / TaskGroup Checks ALL No. Check history is preserved
Goal (DB level) Sub-goals ON DELETE SET NULL Children are promoted to top level, never deleted with the parent. The UI says so before the delete
RoutineSnapshot SnapshotChecks ALL Yes
User (DB level) DailyBriefing rows ON DELETE CASCADE Handled by the database FK. A briefing is derived data with no meaning past the account
User (DB level) Every notebook table ON DELETE CASCADE Account deletion takes the whole notebook
User (DB level) StudyCycle, StudySession ON DELETE CASCADE The other five study cycle tables cascade through study_cycles, so account deletion takes all seven
StudyCycle (DB level) Its days, topics, blocks, lap blocks and closed laps; StudySession.cycle_id ON DELETE CASCADE, and SET NULL for the session Deleting a cycle takes everything under it. A logged session stays as the topic's history
User (DB level) FocusCycle, FocusMicroTask, FederatedIdentity, MoodEntry, CalendarTask, QuickNote, NotificationPreferences, NotificationSend rows ON DELETE CASCADE Handled by the database FK
Task (DB level) QuickNote.converted_task_id ON DELETE SET NULL The archived note stays and stops pointing at the task. Deleting a task is never blocked by the post-it it came from
ItemGroup (DB level) FocusMicroTask rows, and FocusCycle.item_group_id ON DELETE CASCADE, and SET NULL for the cycle A micro-task means nothing without its item. A finished cycle stays on the record with its minutes
NotebookPage (DB level) Child pages, board nodes and edges, cards and their reviews, sources and their chunks, outputs, chat messages ON DELETE CASCADE Deleting a page deletes its subtree. A page linked onto its boards from another topic has its home elsewhere and stays; only the node goes
NotebookPage (DB level) FocusCycle.notebook_page_id ON DELETE SET NULL The minutes stay on the record
NotebookPage (DB level) A cycle's topic rows and blocks, and sessions, by topic_id; a block's page_id and a lap block's target_page_id ON DELETE CASCADE by topic, SET NULL by page Deleting a topic takes it out of its cycle. A deleted page leaves the block in the queue with no page
Goal / Category / Habit (DB level) A topic's links ON DELETE SET NULL The links decorate a topic. Deleting the goal must not delete a notebook

Two tests hold this table to the schema. AccountDeletionIntegrationTest and ManualAccountDeleteRunbookTest read every foreign key that points at users from the live database, put a row behind each one, delete the account, and expect nothing left. A new user-owned table fails both until it gets a row there, which is the point: a cascade nobody exercises is only a comment. UserExportCompletenessIntegrationTest reads the same list and fails for any table that has no section in the export and no entry under notIncluded.

Database tables summary

flowchart LR
  subgraph core["Core"]
    users
    categories
    habits
    tasks
    goals
  end

  subgraph joins["Join tables"]
    habit_category
    task_category
    goal_category
    schedule_days
  end

  subgraph routine["Routine"]
    routines
    routine_sections
    schedules
    item_groups
    habit_groups
    task_groups
    base_checks
    habit_group_checks
    task_group_checks
  end

  subgraph history["History & snapshots"]
    entity_check_day
    entity_xp_day
    routine_snapshot
    snapshot_check
    focus_cycles
    focus_micro_tasks
    mood_entries
    daily_briefing
  end

  subgraph calendar["Calendar"]
    calendar_tasks
  end

  subgraph ideas["Ideas board"]
    quick_notes
  end

  subgraph notebook["Study notebook"]
    notebook_pages
    notebook_board_nodes
    notebook_board_edges
    notebook_cards
    notebook_card_reviews
    notebook_sources
    notebook_source_chunks
    notebook_study_outputs
    notebook_chat_messages
    notebook_roadmap_drafts
  end

  subgraph cycles["Study cycles"]
    study_cycles
    study_cycle_days
    study_cycle_topics
    study_cycle_blocks
    study_cycle_lap_blocks
    study_cycle_laps
    study_sessions
  end

  subgraph support["Feedback & AI"]
    feedback
    feedback_reply
    feedback_attachment
    chats
    agent_message
    spring_ai_chat_memory
  end

  subgraph auth["Auth"]
    refresh_tokens
    federated_identities
    password_reset_tokens
    account_deletion_codes
    notification_preferences
    notification_sends
  end

  subgraph system["Reference & docs"]
    xp_by_level
    docs_tables["docs_* (8 tables)"]
  end

All primary keys are UUIDs except xp_by_level, whose key is the level itself. Timestamps are set via JPA lifecycle callbacks. The docs_* tables follow one repeated pattern: a topic root with a unique key plus a per-locale content row, imported from the beyou-arch-design repository.