DATA ARCHITECTURE

Database Schema

Explore the PostgreSQL database structure used by HabitFlow, including its Prisma models, fields, relationships, indexes and enums.

Database Overview

Database

PostgreSQL

ORM

Prisma

Models

Enums

Primary IDs

UUID / CUID

Relations

One-to-many

Entity Models

Nine models defined in the current Prisma schema.

1. User

users

Stores user profile information and account preferences.

Field	Type	Description
id	String	Primary key (UUID)
name	String?	User display name
email	String	Unique email address
firstName	String?	First name
lastName	String?	Last name
passwordHash	String?	Hashed password
image	String?	Profile image
timezone	String	Defaults to UTC
createdAt	DateTime	Creation timestamp
updatedAt	DateTime	Last update timestamp

Relationships: Has many habits, categories, completions, daily statistics, reminders, accounts and sessions.

2. Habit

habits

Stores the habits created and managed by users.

Field	Type	Description
id	String	Primary key (UUID)
userId	String	Owner user ID
title	String	Habit name
description	String?	Habit description
color	String	Display color
icon	String	Habit icon
goalType	HabitFrequency	Daily, weekly or monthly
targetPerDay	Int	Daily target
streakGoal	Int?	Optional streak goal
categoryId	String	Category reference
status	HabitStatus	Habit status
currentStreak	Int	Current streak
longestStreak	Int	Longest streak
totalCompletions	Int	Total completions
startDate	DateTime	Start date
endDate	DateTime?	Optional end date

Relationships: Belongs to a User and HabitCategory. Has many completions, daily statistics and reminders.

3. HabitCategory

HabitCategory

Organizes habits into user-specific categories.

Field	Type	Description
id	String	Primary key (UUID)
title	String	Category name
userId	String	Owner user ID

Relationships: Belongs to a User and has many Habits.

4. HabitCompletion

habit_completions

Records individual habit completion events.

Field	Type	Description
id	String	Primary key (UUID)
habitId	String	Related habit
userId	String	Related user
date	DateTime	Completion date
completedAt	DateTime	Completion timestamp

Relationships: Belongs to a Habit and User.

5. HabitDailyStat

habit_daily_stats

Stores aggregated daily completion progress.

Field	Type	Description
id	String	Primary key (UUID)
habitId	String	Related habit
userId	String	Related user
date	DateTime	Statistics date
completionCount	Int	Completed count
targetCount	Int	Expected target

Relationships: Belongs to a Habit and User. Has a unique constraint on habitId and date.

6. HabitReminder

habit_reminders

Stores scheduled reminder preferences for habits.

Field	Type	Description
id	String	Primary key (UUID)
userId	String	Related user
habitId	String	Related habit
time	DateTime	Reminder time
daysOfWeek	Int[]	Selected weekdays
enabled	Boolean	Reminder enabled state
createdAt	DateTime	Creation timestamp
updatedAt	DateTime	Last update timestamp

Relationships: Belongs to a User and Habit.

7. Account

Account

Stores OAuth provider account information for authentication.

Field	Type	Description
id	String	Primary key (CUID)
userId	String	Related user
provider	String	Authentication provider
providerAccountId	String	Provider account ID
type	String	Account type
access_token	String?	Optional access token
refresh_token	String?	Optional refresh token

Relationships: Belongs to a User. Provider and providerAccountId are unique together.

8. Session

Session

Stores authentication sessions.

Field	Type	Description
id	String	Primary key (CUID)
sessionToken	String	Unique session token
userId	String	Related user
expires	DateTime	Session expiration

Relationships: Belongs to a User.

9. VerificationToken

VerificationToken

Stores tokens used for account verification workflows.

Field	Type	Description
identifier	String	Verification identifier
token	String	Unique verification token
expires	DateTime	Token expiration

Relationships: Has a compound unique constraint on identifier and token. No User relation is defined in this schema.

Enums

Enumerated values used to control habit frequency and status.

HabitFrequency

Defines how frequently a habit should be performed.

DAILYWEEKLYMONTHLY

HabitStatus

Defines the current lifecycle status of a habit.

ACTIVEINACTIVECOMPLETEDARCHIVED

Data Integrity

The schema uses primary keys, unique constraints, foreign-key relationships and indexes to organize records and support data integrity. User and habit relationships use cascading deletion where explicitly configured in the Prisma schema.

Field

Type

Description

String

Primary key (UUID)

name

String?

User display name

String

Unique email address

firstName

String?

First name

lastName

String?

Last name

passwordHash

String?

Hashed password

image

String?

Profile image

timezone

String

Defaults to UTC

createdAt

DateTime

Creation timestamp

updatedAt

DateTime

Last update timestamp

Field

Type

Description

String

Primary key (UUID)

userId

String

Owner user ID

title

String

Habit name

description

String?

Habit description

color

String

Display color

icon

String

Habit icon

goalType

HabitFrequency

Daily, weekly or monthly

targetPerDay

Int

Daily target

streakGoal

Int?

Optional streak goal

categoryId

String

Category reference

status

HabitStatus

Habit status

currentStreak

Int

Current streak

longestStreak

Int

Longest streak

totalCompletions

Int

Total completions

startDate

DateTime

Start date

endDate

DateTime?

Optional end date

Field

Type

Description

String

Primary key (UUID)

title

String

Category name

userId

String

Owner user ID

Field

Type

Description

String

Primary key (UUID)

habitId

String

Related habit

userId

String

Related user

date

DateTime

Completion date

completedAt

DateTime

Completion timestamp

Field

Type

Description

String

Primary key (UUID)

habitId

String

Related habit

userId

String

Related user

date

DateTime

Statistics date

completionCount

Int

Completed count

targetCount

Int

Expected target

Field

Type

Description

String

Primary key (UUID)

userId

String

Related user

habitId

String

Related habit

time

DateTime

Reminder time

daysOfWeek

Int[]

Selected weekdays

enabled

Boolean

Reminder enabled state

createdAt

DateTime

Creation timestamp

updatedAt

DateTime

Last update timestamp

Field

Type

Description

String

Primary key (CUID)

userId

String

Related user

provider

String

Authentication provider

providerAccountId

String

Provider account ID

type

String

Account type

access_token

String?

Optional access token

refresh_token

String?

Optional refresh token

Field

Type

Description

String

Primary key (CUID)

sessionToken

String

Unique session token

userId

String

Related user

expires

DateTime

Session expiration

Field

Type

Description

identifier

String

Verification identifier

token

String

Unique verification token

expires

DateTime

Token expiration