Database Entity-Relationship Diagram

Core data model with 10 entities supporting Journal + Ideas dual modes

Core Entities Overview

User

Core

Authenticated user (parent, teacher, or child)

  • PK: User ID
  • Email address
  • Display name
  • OAuth provider (Google/Apple/Microsoft)
  • OAuth provider user ID
  • Profile photo URL
  • User role
  • Timestamps
1:N → Child
1:N → JournalEntry

Child

Core

Child profile being tracked

  • PK: Child ID
  • FK: Owner User ID
  • Child name
  • Date of birth
  • Profile photo URL
  • Interests (tags)
  • Curriculum approach
  • Active status
1:N → JournalEntry
1:N → SkillProgress
1:N → SuggestedActivity
1:N → LiveCV

JournalEntry

Journal Mode

Logged moment/event with photo

  • PK: Entry ID
  • FK: Child ID
  • FK: Creator User ID
  • FK: Template ID (nullable)
  • Photo URL
  • Entry date/timestamp
  • Freeform notes
  • AI analysis data (JSON)
  • Timestamps
M:N ↔ Skill (via EntrySkill)

Skill

Core Taxonomy

Skill, behavior, or attribute

  • PK: Skill ID
  • FK: Parent Skill ID (nullable)
  • Skill name
  • Description
  • Category (life/academic/behavioral/physical)
  • Age relevance indicators

Note: Seeded from external stub list, may be hierarchical

M:N ↔ JournalEntry
M:N ↔ Template
M:N ↔ Activity

EntrySkill

Junction Table

Links entries to demonstrated skills

  • FK: Entry ID
  • FK: Skill ID
  • Confidence level (1-5 or %)
  • Source (AI vs. parent-added)
  • Notes (optional context)

Tracks: AI suggestions vs. parent modifications

Template

Journal Mode

Pre-defined activity template

  • PK: Template ID
  • Template name (e.g., "Beach Day")
  • Description
  • Common associated skills
  • Icon/representative image
  • AI matching keywords
  • Creation source (curated/community)

Purpose: Pre-selects likely skills for speed

M:N ↔ Skill

Activity

Ideas Mode

Suggested activity for skill development

  • PK: Activity ID
  • Activity name
  • Description
  • Target skills
  • Age range suitability
  • Time investment
  • Location type (indoor/outdoor)
  • Materials needed
  • Interest tags
  • Difficulty level
  • Creation source
M:N ↔ Skill
1:N → SuggestedActivity

SuggestedActivity

Ideas Mode

Personalized activity recommendation

  • PK: Suggestion ID
  • FK: Child ID
  • FK: Activity ID
  • FK: Linked Entry ID (nullable)
  • Target skill(s) - which gaps
  • Suggestion date
  • Status (suggested/bookmarked/dismissed/completed)
  • Completion date (nullable)

Tracks: User interactions for AI improvement

SkillProgress

Computed/Cached

Tracks child's progression in specific skill

  • PK: Progress ID
  • FK: Child ID
  • FK: Skill ID
  • Current level/proficiency (age-relative)
  • First demonstrated date
  • Latest demonstration date
  • Demonstration count
  • Trend (improving/stable/declining)

Calculated from: JournalEntry + EntrySkill records

LiveCV

Output/Export

Generated "Live CV" report

  • PK: CV ID
  • FK: Child ID
  • Generation date
  • Date range covered (from/to)
  • Report data (JSON)
  • Export format (PDF/JSON)
  • Shared with (array)

Used for: Accountability reporting, personal records

Relationship Types Summary

One-to-Many (1:N)

  • User → Child: One user manages multiple child profiles
  • User → JournalEntry: One user creates many entries
  • Child → JournalEntry: One child has many entries
  • Child → SkillProgress: One child has progress in many skills
  • Child → SuggestedActivity: One child receives many suggestions
  • Child → LiveCV: One child has many reports over time
  • Skill → EntrySkill: One skill appears in many entries
  • Activity → SuggestedActivity: One activity suggested to many children

Many-to-Many (M:N)

  • JournalEntry ↔ Skill: Via EntrySkill junction table
  • Template ↔ Skill: Templates suggest multiple skills
  • Activity ↔ Skill: Activities develop multiple skills

Optional Relationships

  • JournalEntry → Template: Entry may use template or be freeform
  • SuggestedActivity → JournalEntry: If suggestion leads to logged activity
  • Child → User: For ownership transfer as child matures
  • Skill → Skill: Self-referencing for hierarchical taxonomy

Data Flow Examples

1. Journal Entry Creation Flow

User creates entry
→
JournalEntry created (photo + notes)
→
AI suggests Template
→
Skills pre-selected from template
→
EntrySkill records created
→
SkillProgress updated for Child

2. Ideas Mode Flow

System analyzes SkillProgress
→
Identifies skill gaps for Child
→
Queries Activity database
→
SuggestedActivity records created
→
User completes → JournalEntry linked
→
Loop: SkillProgress updated

3. Live CV Generation Flow

User requests export for Child
→
Query JournalEntry + SkillProgress + Skill
→
LiveCV record generated with aggregated data
→
Report exported (PDF/JSON)

Entity Categories

Core Entities: User, Child, Skill
Journal Mode: JournalEntry, Template
Ideas Mode: Activity, SuggestedActivity
Junction Tables: EntrySkill
Computed/Cached: SkillProgress
Output/Export: LiveCV

Generated from /docs/phase-1/02-Database-Model.md

← Back to all workflows