The Arete domain uses PostgreSQL with Drizzle ORM.
The schema lives in
libs/arete/core/src/db-schema.ts
and is the single source of truth for table shape, indexes, and relations.
Connection#
| Setting | Default |
|---|---|
DATABASE_URL |
postgresql://oshun:oshun_dev@localhost:5432/oshun_dev |
| Driver | node-postgres via Drizzle |
| Pool min / max | DB_POOL_MIN (2) / DB_POOL_MAX (10) |
Tables (50 total)#
Tables are grouped by phase-28 section. Every table includes
id uuid PK DEFAULT gen_random_uuid() and (where applicable) created_at /
updated_at timestamptz columns.
Verifying the count. Most tables use the multi-line
pgTable(\n 'arete_…',form, so a singlegrepwon't catch them all. Use Python'sre.findallwith a\s*betweenpgTable(and the name string:bashpython3 -c "import re,sys; print(len(set(re.findall(r\"pgTable\(\\s*'(arete_[a-z_]+)'\", open('libs/arete/core/src/db-schema.ts').read()))))"Run from the repo root — it must equal the number above. If the schema and the doc drift, update this section in the same PR as the schema change.
Identity#
arete_users— profile (email, name, avatarUrl, timezone, locale, maturityStage, preferences jsonb)arete_user_credentials— bcrypt password hash, separated for least-privilege readsarete_password_reset_tokens— short-lived reset token + hash + expiry
Habits & streaks#
arete_habits— habit definitions (name, category, frequency, cue/routine/reward, denormalizedcurrentStreak+bestStreak+totalCompletions)arete_habit_completions— per-day completion log (date,completedAt,quality,skipped,skipReason)arete_habit_stacks— chained habits ("after X, do Y")arete_streaks— explicit streak record per habit (current, best, forgiveness counters)arete_streak_freezes— protected days
Goals#
arete_goals— polymorphic goals across all time horizons + SMART + WOOP fieldsarete_goal_hierarchy— explicit parent-child edgesarete_okrs+arete_key_results— quarterly OKR pattern
Journal#
arete_journal_entries— mood + tags + content + word countarete_journal_prompts— system + custom prompts per categoryarete_thought_records— CBT thought logsarete_gratitude_entries— dedicated gratitude rows
Vision & purpose#
arete_personal_missions— Covey mission statementsarete_values— ranked valuesarete_roles— life rolesarete_vision_boards+arete_vision_board_itemsarete_renewal_activities+arete_renewal_logs— sharpen-the-saw
Time#
arete_tasks— Eisenhower-quadrant typed tasksarete_weekly_plans— big rocks, roles, review, sharpenTheSawarete_daily_plans— Big 3, time blocks, morning routine, evening reviewarete_time_blocks— calendar-style blocks (separate from daily plan blocks)arete_pomodoro_sessions— start/duration/break/distractionsarete_deep_work_sessions— Cal Newport-style deep work tracking
Balance & wellness#
arete_circle_items— Circle of Influence vs Concernarete_relationships— emotional bank account targetsarete_emotional_transactions— deposits/withdrawalsarete_wheel_of_life_assessments— periodic 8-dimension snapshotarete_wellness_logs— per-dimension daily logsarete_mood_entries— 1-5 ratings + tags + sequence number for orderingarete_sleep_entries— hours, quality, notesarete_energy_entries— morning/midday/afternoon/evening levels
Gamification#
arete_affirmations— curated + user affirmationsarete_achievements— earned badges (withprogressfor in-flight)arete_badges— badge catalogarete_user_levels— current level + XP totalsarete_points_transactions— append-only points log (amount,type,source, reason)arete_accountability_partnerships— paired users (with cached partnerName / partnerEmail / since)arete_partner_invitations— outgoing invites by emailarete_commitment_contracts— staked commitmentsarete_challenges+arete_challenge_participants— community challenges (with progress 0–100)
AI coach#
arete_coach_conversations— conversation envelope (id, userId, title, timestamps)arete_coach_messages— chronological messages (role,content,suggestions jsonb,followUp)arete_coach_feedback— user ratings on coach messages, unique per (messageId, userId)
Indexes#
Every foreign-key column carries a btree index. Composite uniques exist for:
arete_users (email)arete_user_credentials (userId)arete_achievements (userId, badgeId)arete_challenge_participants (challengeId, userId)arete_coach_feedback (messageId, userId)
Time-series tables (arete_points_transactions, arete_coach_messages,
arete_coach_conversations) carry an extra index on the timestamp column used
for ordering reads (earnedAt, createdAt, updatedAt).
ER overview#
┌─────────────┐
│ arete_users │◀─────────┐
└─────┬───────┘ │
│ owns most │ awards
▼ │
habits, goals, journal, badges + achievements
vision boards, tasks, │
weekly/daily plans, │ unlocks
coach conversations, │
moods/sleep/energy, ▼
wheel-of-life, arete_user_levels ◀── arete_points_transactions
wellness logs.
(Refer to db-schema.ts for the canonical relations definitions.)
Migrations#
Drizzle's migration generator produces SQL files committed to
libs/arete/core/migrations/ (created on demand by
pnpm drizzle-kit generate:pg). Apply with pnpm drizzle-kit migrate:pg.
The Pg-backed repositories under apps/arete/api/src/repositories/*.ts each
issue plain Drizzle calls — no raw SQL — so the schema and the runtime stay in
lock-step.
Prisma artifact#
libs/arete/database/prisma/schema.prisma exists only to satisfy the
cross-domain Prisma contract checks in libs/contracts/. Drizzle is the runtime
source of truth; new tables go into db-schema.ts, not into the Prisma file.