Eve library

Arete Database Schema

Guide

Revision 1

On this page

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 single grep won't catch them all. Use Python's re.findall with a \s* between pgTable( and the name string:

bash
python3 -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 reads
  • arete_password_reset_tokens — short-lived reset token + hash + expiry

Habits & streaks#

  • arete_habits — habit definitions (name, category, frequency, cue/routine/reward, denormalized currentStreak + 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 fields
  • arete_goal_hierarchy — explicit parent-child edges
  • arete_okrs + arete_key_results — quarterly OKR pattern

Journal#

  • arete_journal_entries — mood + tags + content + word count
  • arete_journal_prompts — system + custom prompts per category
  • arete_thought_records — CBT thought logs
  • arete_gratitude_entries — dedicated gratitude rows

Vision & purpose#

  • arete_personal_missions — Covey mission statements
  • arete_values — ranked values
  • arete_roles — life roles
  • arete_vision_boards + arete_vision_board_items
  • arete_renewal_activities + arete_renewal_logs — sharpen-the-saw

Time#

  • arete_tasks — Eisenhower-quadrant typed tasks
  • arete_weekly_plans — big rocks, roles, review, sharpenTheSaw
  • arete_daily_plans — Big 3, time blocks, morning routine, evening review
  • arete_time_blocks — calendar-style blocks (separate from daily plan blocks)
  • arete_pomodoro_sessions — start/duration/break/distractions
  • arete_deep_work_sessions — Cal Newport-style deep work tracking

Balance & wellness#

  • arete_circle_items — Circle of Influence vs Concern
  • arete_relationships — emotional bank account targets
  • arete_emotional_transactions — deposits/withdrawals
  • arete_wheel_of_life_assessments — periodic 8-dimension snapshot
  • arete_wellness_logs — per-dimension daily logs
  • arete_mood_entries — 1-5 ratings + tags + sequence number for ordering
  • arete_sleep_entries — hours, quality, notes
  • arete_energy_entries — morning/midday/afternoon/evening levels

Gamification#

  • arete_affirmations — curated + user affirmations
  • arete_achievements — earned badges (with progress for in-flight)
  • arete_badges — badge catalog
  • arete_user_levels — current level + XP totals
  • arete_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 email
  • arete_commitment_contracts — staked commitments
  • arete_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#

text
       ┌─────────────┐
       │ 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.