Database schema

This page documents the implemented PostgreSQL/SQLite schema in api/database/migrations/. It is the engineering view of tables and relationships.

For product terminology and rules (study vs session, mentorship behavior, note privacy), see Planning → Domain model and Planning → Mentorship.

Source of truth: when this doc and migrations disagree, trust the migrations.

Conventions

Convention	Detail
Primary keys	`users` and Laravel infra use `bigint`; study domain and plans use `uuid`
Ownership	Most user content rows include `user_id` on the owning account
Soft deletes	`studies`, `study_log_entries`, `notes` use `deleted_at`
Study owner	`studies.user_id` is always the mentee (learner); mentors access via `mentorship_id` in app code
Plan snapshot	`studies.plan_template_id` is audit metadata; workflow lives on `study_steps`

Migrations are applied in filename order under api/database/migrations/.

Overview

erDiagram
    users ||--o{ studies : owns
    users ||--o{ study_log_entries : logs
    users ||--o{ notes : writes
    users ||--o{ mentorships : mentor
    users ||--o{ mentorships : mentee
    users }o--o{ users : guardian_minor_links

    mentorships ||--o{ studies : mentored
    mentorships }o--o| tracks : assigned_track

    tracks ||--|{ track_modules : orders
    track_elective_groups ||--o{ track_modules : elective_slot

    study_plan_templates ||--|{ study_plan_template_steps : defines
    study_plan_templates ||--o{ study_plan_template_spiritual_phases : phases
    study_plan_templates ||--o{ studies : applied_at_create

    studies ||--|{ study_steps : has
    studies ||--o{ study_log_entries : optional
    studies ||--o{ notes : optional

    study_steps }o--o| notes : mentee_lane
    study_steps }o--o| notes : mentor_lane
    study_steps }o--o| notes : individual_lane
    study_steps }o--o| study_log_entries : completion
    study_plan_template_steps ||--o{ study_steps : snapshot

    notes }o--o| study_steps : study_step_id
    notes }o--o| study_log_entries : optional

Users, guardians, and mentorship

users holds account credentials, onboarding profile (spiritual_phase, personality_focus, learning_style, study_motivation from later migrations), and flags (is_minor, is_platform_admin).

guardian_minor_links is a pivot: many guardians per minor, many minors per guardian (household model in planning).

mentorships pairs a mentor (Meat+ in app rules) with a mentee. At most one row per (mentor_user_id, mentee_user_id); a partial unique index enforces one active mentor per mentee.

erDiagram
    users {
        bigint id PK
        string email UK
        string spiritual_phase
        date date_of_birth
        bool is_minor
        bool is_platform_admin
    }

    guardian_minor_links {
        bigint id PK
        bigint guardian_user_id FK
        bigint minor_user_id FK
    }

    mentorships {
        uuid id PK
        bigint mentor_user_id FK
        bigint mentee_user_id FK
        uuid track_id FK
        string status
        bigint invited_by_user_id FK
        datetime invited_at
        datetime accepted_at
        datetime ended_at
    }

    users ||--o{ guardian_minor_links : guardian
    users ||--o{ guardian_minor_links : minor
    users ||--o{ mentorships : mentor_user_id
    users ||--o{ mentorships : mentee_user_id
    users ||--o{ mentorships : invited_by_user_id

`mentorships.status`	Meaning
`pending`	Invitation sent; awaiting the other party
`active`	Pairing in effect
`declined`	Invite rejected
`ended`	Ended by either party
`revoked`	Reserved for future guardian/church flows

Study plans and steps

Admin-defined templates resolve at createStudy time. Steps are copied into study_steps so in-flight studies are unaffected by template edits.

Templates declare participation flags used with the user’s phase and active mentorship:

supports_mentored_participation
supports_individual_participation

erDiagram
    study_plan_templates {
        uuid id PK
        string slug UK
        string study_method
        string focus_kind
        string focus_value
        bool is_published
        bool supports_mentored_participation
        bool supports_individual_participation
    }

    study_plan_template_spiritual_phases {
        uuid id PK
        uuid template_id FK
        string spiritual_phase
    }

    study_plan_rubric_dimensions {
        uuid id PK
        string slug UK
        string title
        text description
        text ai_instructions
        int sort_order
    }

    study_plan_template_steps {
        uuid id PK
        uuid template_id FK
        string participation_flow
        int wave
        int sort_order
        string step_key
        string title
        text instructions
        string completion_mode
        string primary_actor
    }

    studies {
        uuid id PK
        bigint user_id FK
        string study_method
        string focus
        uuid plan_template_id FK
        datetime plan_applied_at
        uuid mentorship_id FK
        uuid track_id FK
        uuid satisfies_plan_template_id FK
        string participation_mode
    }

    study_steps {
        uuid id PK
        uuid study_id FK
        uuid template_step_id FK
        int sort_order
        string status
        uuid mentee_note_id FK
        uuid mentor_note_id FK
        uuid individual_note_id FK
        uuid study_log_entry_id FK
    }

    study_plan_templates ||--|{ study_plan_template_steps : template_id
    study_plan_templates ||--|{ study_plan_template_spiritual_phases : template_id
    study_plan_templates ||--o{ studies : plan_template_id
    study_plan_templates ||--o{ studies : satisfies_plan_template_id
    study_plan_template_steps ||--o{ study_steps : template_step_id
    studies ||--|{ study_steps : study_id

Spiritual phases on templates use the study_plan_template_spiritual_phases pivot (many phases per template). Step fields (participation_flow, wave, completion_mode, primary_actor) are documented in Study Plans.

`studies.participation_mode`	Step note lanes used
`mentored`	`mentee_note_id`, `mentor_note_id`
`individual`	`individual_note_id`

`study_steps.status`	Meaning
`pending`	Not yet reachable
`available`	Current actionable step
`completed`	Finished
`skipped`	Reserved
`cancelled`	Study ended; open steps cancelled

Tracks and mentorship curriculum

Admin-defined tracks order study plan templates (and elective groups) for mentorship progression. Product rules: Tracks.

track_modules is the canonical ordered sequence (module_type: plan or elective_group). The legacy pivot track_study_plan_templates is dual-written from plan modules for backward-compatible flat lists.

erDiagram
    tracks {
        uuid id PK
        string slug UK
        string title
        text description
        string spiritual_phase
        bool is_published
    }

    track_tags {
        uuid id PK
        string slug UK
        string name
    }

    track_tag {
        uuid track_id FK
        uuid track_tag_id FK
    }

    track_modules {
        uuid id PK
        uuid track_id FK
        string module_type
        int sort_order
        uuid plan_template_id FK
        uuid elective_group_id FK
        bool is_required
        text mentor_instructions
    }

    track_elective_groups {
        uuid id PK
        uuid track_id FK
        string title
        text description
        int pick_count
        string selector
    }

    track_elective_group_plans {
        uuid id PK
        uuid elective_group_id FK
        uuid plan_template_id FK
        text mentor_instructions
    }

    track_study_plan_templates {
        uuid track_id FK
        uuid plan_template_id FK
        int sort_order
        bool is_required
        text mentor_instructions
    }

    mentorships {
        uuid id PK
        uuid track_id FK
    }

    studies {
        uuid id PK
        uuid track_id FK
        uuid satisfies_plan_template_id FK
    }

    tracks ||--|{ track_modules : track_id
    tracks ||--o{ track_tag : track_id
    track_tags ||--o{ track_tag : track_tag_id
    tracks ||--|{ track_elective_groups : track_id
    track_elective_groups ||--|{ track_elective_group_plans : elective_group_id
    track_elective_groups ||--o{ track_modules : elective_group_id
    study_plan_templates ||--o{ track_modules : plan_template_id
    study_plan_templates ||--o{ track_elective_group_plans : plan_template_id
    tracks ||--o{ track_study_plan_templates : track_id
    study_plan_templates ||--o{ track_study_plan_templates : plan_template_id
    tracks ||--o{ mentorships : track_id
    tracks ||--o{ studies : track_id

`track_modules.module_type`	Row links
`plan`	`plan_template_id`; `is_required` and `mentor_instructions` on the module
`elective_group`	`elective_group_id` → pool in `track_elective_group_plans`

Skip/noop progress uses studies.satisfies_plan_template_id (see Tracks — Skippable plans).

Activity, notes, and references

Log entries record that study happened (streaks); they are not notes.

Notes store the rich text envelope and optional Automerge CRDT payloads (Note CRDT sync). A note may link to a study, a log entry, and/or a study step (study_step_id).

Bible references attach polymorphically to notes (and potentially other types later) via referenceable_type / referenceable_id.

erDiagram
    users {
        bigint id PK
    }

    studies {
        uuid id PK
        bigint user_id FK
    }

    study_log_entries {
        uuid id PK
        bigint user_id FK
        uuid study_id FK
        datetime logged_at
        text summary
    }

    notes {
        uuid id PK
        bigint user_id FK
        uuid study_id FK
        uuid study_log_entry_id FK
        uuid study_step_id FK
        text markdown_body
        text crdt_state
        string crdt_version
    }

    bible_references {
        bigint id PK
        bigint user_id FK
        string referenceable_type
        uuid referenceable_id
        string reference
    }

    study_steps {
        uuid id PK
        uuid study_id FK
    }

    users ||--o{ study_log_entries : user_id
    users ||--o{ notes : user_id
    studies ||--o{ study_log_entries : study_id
    studies ||--o{ notes : study_id
    study_log_entries ||--o{ notes : study_log_entry_id
    study_steps ||--o{ notes : study_step_id
    notes ||--o{ bible_references : morph

Step-level note foreign keys (mentee_note_id, mentor_note_id, individual_note_id on study_steps) point to the same notes table; the writer is always notes.user_id (mentee or mentor per lane rules in the API).

Feature flags (scaffold)

Platform feature toggles are stored for gradual rollout. Assignments are polymorphic (assignable_type, assignable_id) to users, groups, or churches when those entities exist.

erDiagram
    feature_flags {
        bigint id PK
        string key UK
        bool default_enabled
    }

    feature_flag_assignments {
        bigint id PK
        bigint feature_flag_id FK
        string assignable_type
        bigint assignable_id
        bool enabled
    }

    feature_flags ||--|{ feature_flag_assignments : feature_flag_id

Laravel infrastructure (not product domain)

These tables support auth and queues; they are omitted from the overview diagram:

Table	Role
`sessions`	Web session driver
`password_reset_tokens`	Password reset
`personal_access_tokens`	Sanctum API tokens
`cache`, `cache_locks`	Cache store
`jobs`, `job_batches`, `failed_jobs`	Queue workers

Document	Use when
Domain model	Product terms and entity rules
Study plans	Template resolution and step lifecycle
Tracks	Track modules, electives, mentorship assignment
Mentorship	Pairing, participation modes, note lanes
Note CRDT sync	`notes.crdt_state` wire protocol

Database schema

Database schema

Conventions

Overview

Users, guardians, and mentorship

Study plans and steps

Tracks and mentorship curriculum

Activity, notes, and references

Feature flags (scaffold)

Laravel infrastructure (not product domain)

Related docs