Database Schema

All tables are defined in packages/db/src/schema.ts using Drizzle ORM. The database is PostgreSQL.

---

Auth tables

These four tables are owned and managed by Better Auth. Never write to them directly — go through the auth instance exported from packages/auth.

user

Column	Type	Notes
id	text PK	Generated by Better Auth
name	text	Display name
email	text unique
email_verified	boolean	Defaults to false
image	text	Optional avatar URL
created_at	timestamp
updated_at	timestamp	Auto-updated

session

Column	Type	Notes
id	text PK
token	text unique	Bearer token sent by the mobile client
expires_at	timestamp
ip_address	text	Optional
user_agent	text	Optional
user_id	text FK → user	Cascade delete

Index on user_id.

account

Stores credential data per auth provider. For email/password auth, password holds the bcrypt hash.

Column	Type	Notes
id	text PK
account_id	text	Provider-side account ID
provider_id	text	"credential" for email/password
user_id	text FK → user	Cascade delete
password	text	Hashed. Email/password auth only
access_token	text	OAuth only
refresh_token	text	OAuth only

Index on user_id.

verification

Short-lived tokens for email verification and password reset.

Column	Type	Notes
id	text PK
identifier	text	Typically the user’s email address
value	text	The token
expires_at	timestamp

Index on identifier.

---

App tables

calendars

Column	Type	Notes
id	uuid PK
name	text	Display name
color	text	Hex colour for the UI
creator_id	text FK → user	Cascade delete
created_at	timestamp
updated_at	timestamp	Auto-updated

events

Column	Type	Notes
id	uuid PK
title	text	Event title
color	text	Hex colour for the UI
start	timestamp	Event start time
end	timestamp	Event end time
is_all_day	boolean
is_canceled	boolean
organizer	text FK → user	Currently the same as creator_id
creator_id	text FK → user	Cascade delete
description	text	Optional notes
location	text	Optional location string
recurrence	text	RFC 5545 RRULE string, e.g. FREQ=WEEKLY;BYDAY=MO,WE,FR
url	text	Optional link
created_at	timestamp
updated_at	timestamp	Auto-updated

Note

Events can belong to multiple calendars via the calendar_events join table. The recurrence field stores an RRULE string; the client expands it into individual occurrences for the visible date range at render time — no pre-expanded occurrences are stored in the database.

calendar_invites

An invite record is created when a calendar owner generates a shareable link. The id doubles as the invite token — it’s the value in the deep link URL.

Column	Type	Notes
id	uuid PK	Also the invite token in the URL
calendar_id	uuid FK → calendars	Cascade delete
expires_at	timestamp	When the link stops working
max_uses	integer	null = unlimited
created_at	timestamp
updated_at	timestamp	Auto-updated

user_settings

One row per user. Created with defaults on first request if it doesn’t exist.

Column	Type	Default	Notes
id	text PK/FK → user	—	Same as user ID
show_kanji	boolean	true	Show Japanese characters in calendar header
default_calendar_view	text	"week"	"day", "week", or "month"
week_starts_on	text	"monday"	"monday" or "sunday"
created_at	timestamp
updated_at	timestamp		Auto-updated

---

Join tables

calendar_members

Records which users are members of which calendars.

Column	Type	Notes
id	uuid PK
user_id	text FK → user	Cascade delete
calendar_id	uuid FK → calendars	Cascade delete
created_at	timestamp
updated_at	timestamp	Auto-updated

calendar_events

Links events to calendars. An event can appear in more than one calendar.

Column	Type	Notes
id	uuid PK
event_id	uuid FK → events	Cascade delete
calendar_id	uuid FK → calendars	Cascade delete
created_at	timestamp
updated_at	timestamp	Auto-updated

event_users

Links events to specific users (attendees or participants beyond calendar membership).

Column	Type	Notes
id	uuid PK
event_id	uuid FK → events	Cascade delete
user_id	text FK → user	Cascade delete
created_at	timestamp
updated_at	timestamp	Auto-updated

---

Entity relationships

user ──< session
user ──< account
user ──< verification
user ──1 user_settings

user ──< calendars              (creator)
user ──< calendar_members >──── calendars
user ──< events                 (creator)
user ──< event_users >────────── events

calendars ──< calendar_invites
calendars ──< calendar_events >── events

Note

All foreign keys use onDelete: cascade. Deleting a user removes their calendars, events, sessions, and settings. Deleting a calendar removes its invites, member links, and event links — but events shared with other calendars are not deleted.

---

Migrations

# After editing packages/db/src/schema.ts, generate a new migration:
pnpm --filter @musubi/db generate

# Apply pending migrations:
pnpm --filter @musubi/db migrate
# or:
pnpm db:migrate

Migration SQL files live in packages/db/drizzle/.