# Arete Database Schema

The Arete domain uses PostgreSQL with [Drizzle ORM](https://orm.drizzle.team).
The schema lives in
[`libs/arete/core/src/db-schema.ts`](../../../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

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