Database Model

Overview

This document defines the database objects (entities) needed for LBH Educate and their relationships.

Core Entities

User

Represents an authenticated user of the system (parent, teacher, or child).

Key Fields:

• User ID (primary key)
• Email address
• Display name
• OAuth provider (Google, Apple, Microsoft)
• OAuth provider user ID
• Profile photo URL
• Account creation timestamp
• Last login timestamp
• User role (parent, child, teacher) - may evolve over time

Child

Represents a child being tracked in the system.

Key Fields:

• Child ID (primary key)
• Owner user ID (foreign key to User - the parent/guardian who created the profile)
• Child name
• Date of birth
• Profile photo URL
• Interests (text or array of tags)
• Curriculum approach (unschooling, worldschool, online, tutoring, traditional, etc.)
• Creation timestamp
• Active status (boolean - allows archiving without deletion)

Notes:

• Ownership can be transferred to the child's own user account as they mature
• Multiple users can have access to one child (collaborative)

JournalEntry

Represents a logged moment/event with photo and educational value.

Key Fields:

• Entry ID (primary key)
• Child ID (foreign key to Child)
• Creator user ID (foreign key to User - who created the entry)
• Photo URL (cloud storage reference)
• Entry date/timestamp
• Freeform notes (text - parent's observations)
• AI analysis data (JSON - what the AI detected from the photo)
• Template used (foreign key to Template, nullable)
• Creation timestamp
• Last modified timestamp

Notes:

• The heart of the system - where everyday moments are captured

Skill

Represents a skill from the master skills taxonomy.

Key Fields:

• Skill ID (primary key)
• Skill name (e.g., "Problem Solving", "Reading fluency", "Fine Motor Skills")
• Category (Literacy, Communication, Numeracy, Motor skills, Analytical, Cognitive)
• Sub-category (e.g., "Decoding" within Literacy, "Memory" within Cognitive)
• Description/Explanation
• Alternative names (previous or alternative terminology for the skill)
• Age relevance indicators (age ranges where this skill is typically developing)

Notes:

• Hierarchical structure: Category → Sub-category → Skill
• Seeded from supplied CSV data (Skills-Skills.csv)
• 64 core skills across 6 major categories:
  • Literacy (14 skills): Letter recognition → Creative writing
  • Communication (10 skills): Verbal/written/non-verbal, public speaking
  • Numeracy (13 skills): Number recognition → Algebra, geometry, statistics
  • Motor skills (4 skills): Dexterity, coordination, locomotion, balance
  • Analytical (13 skills): Decision making, problem solving, critical thinking
  • Cognitive (10 skills): Memory, focus, organization, meditation

Behaviour

Represents a character trait or behavioral attribute from the behaviours taxonomy.

Key Fields:

• Behaviour ID (primary key)
• Behaviour name (e.g., "Resilient", "Empathic", "Curious", "Confident")
• Description/Explanation
• Alternative names (previous or alternative terminology)

Notes:

• Flat list structure (non-hierarchical)
• Seeded from supplied CSV data (Behaviours-Table 1.csv)
• 22 core character traits including: Independent, Motivated, Adaptable, Emotional intelligence, Creative, Resourceful, Compassionate, Resilient, Kind, Curious, Empathic, Dependable, Trust-worthy, Self-aware, Conscientious, Adventurous, Collaborative, Leader, Courageous
• Distinct from Skills - represents character/personality traits vs. learned abilities

EntrySkill (Junction Table)

Links journal entries to demonstrated skills.

Key Fields:

• Entry ID (foreign key to JournalEntry)
• Skill ID (foreign key to Skill)
• Confidence level (how strongly this skill was demonstrated, 1-5 scale or percentage)
• Source (AI-suggested vs. parent-added)
• Notes (optional context about this specific demonstration)

Notes:

• Many-to-many relationship between entries and skills
• Tracks both AI suggestions and parent modifications

EntryBehaviour (Junction Table)

Links journal entries to demonstrated character traits/behaviours.

Key Fields:

• Entry ID (foreign key to JournalEntry)
• Behaviour ID (foreign key to Behaviour)
• Confidence level (how strongly this behaviour was demonstrated, 1-5 scale or percentage)
• Source (AI-suggested vs. parent-added)
• Notes (optional context about this specific demonstration)

Notes:

• Many-to-many relationship between entries and behaviours
• Separate from EntrySkill to maintain clear distinction between skills and character traits

Template

Represents a pre-defined activity or event template with common skill associations.

Key Fields:

• Template ID (primary key)
• Template name (e.g., "Beach Day", "Cooking Together", "Caring for Animals")
• Description
• Common associated skills (array of Skill IDs or junction table)
• Icon or representative image
• AI matching keywords (helps AI suggest appropriate templates)
• Creation source (developer-curated vs. community-generated)

Notes:

• Makes tagging faster by pre-selecting likely skills
• Can be refined over time based on usage patterns

KnowledgeArea

Represents a domain or area of knowledge that can be explored through activities.

Key Fields:

• Knowledge Area ID (primary key)
• Category (Household, Communication, Reading, Society, Well-being, Travel, Tech, etc.)
• Sub-category (e.g., "Cooking" within Household, "Nutrition" within Well-being)
• Knowledge description (what this knowledge area encompasses)
• Related skills (array of Skill IDs - which skills this knowledge area develops)

Notes:

• Seeded from supplied CSV data (multiple "Table 1" files organized by category)
• Major knowledge categories include:
  • Household: Cooking, Shopping, Budgeting, Car Maintenance, Garden Maintenance, House Maintenance
  • Communication: Various communication contexts
  • Reading: Book-related literacy activities
  • Society: Politics, Volunteering, Social skills
  • Well-being: Nutrition, Physical health, Mental health, Spiritual health
  • Travel: Language learning, Transport
  • Tech: Technology skills (minimal data currently)
• Provides context for activity suggestions

Activity (Ideas Mode)

Represents suggested activities for skill development, linked to knowledge areas.

Key Fields:

• Activity ID (primary key)
• Knowledge Area ID (foreign key to KnowledgeArea)
• Activity name/title
• Description (what the activity involves)
• Target skills (array of Skill IDs or junction table - which skills this activity develops)
• Target behaviours (array of Behaviour IDs - which character traits this might develop)
• Progression level (1-3 scale indicating complexity/difficulty)
• Age range suitability
• Time investment (quick/medium/long or minutes)
• Location type (indoor/outdoor/either)
• Materials needed (text or structured list)
• Interest tags (relates to child interests)
• Creation source (developer-curated vs. AI-generated vs. community)

Notes:

• Seeded from supplied CSV data (e.g., Household-Cooking.csv contains 40+ progressive activities)
• Structured as: Knowledge Area → Activity Ideas → Skills Used → Level
• Used in Ideas Mode to suggest activities to parents
• Can be filtered and personalized based on child profile
• Example progression: Cooking activities range from "Identify kitchen equipment" (Level 1) to "Cook a meal from a recipe book" (Level 3)

SuggestedActivity (Personalized Recommendations)

Represents an activity suggested to a specific child based on gap analysis.

Key Fields:

• Suggestion ID (primary key)
• Child ID (foreign key to Child)
• Activity ID (foreign key to Activity)
• Target skill(s) (which gaps this addresses)
• Suggestion date
• Status (suggested/bookmarked/dismissed/completed)
• Completion date (if completed)
• Linked journal entry ID (if completed and logged)

Notes:

• Tracks which suggestions were shown, accepted, or ignored
• Improves recommendation algorithm over time

SkillProgress

Tracks a child's progression in a specific skill over time.

Key Fields:

• Progress ID (primary key)
• Child ID (foreign key to Child)
• Skill ID (foreign key to Skill)
• Current level/proficiency (age-relative measure)
• First demonstrated date
• Latest demonstration date
• Demonstration count (how many journal entries show this skill)
• Trend indicator (improving/stable/declining)

Notes:

• Calculated/derived entity based on journal entries
• Tracks progression against the child themselves, not external benchmarks

LiveCV (Exportable Report)

Represents a generated "Live CV" report for a child.

Key Fields:

• CV ID (primary key)
• Child ID (foreign key to Child)
• Generation date
• Date range covered (from/to dates)
• Report data (JSON - structured summary of skills, entries, progress)
• Export format (PDF, JSON, etc.)
• Shared with (array of user IDs or external identifiers)

Notes:

• May be regenerated periodically or on-demand
• Used for accountability reporting or personal records

Relationships

One-to-Many

• User → Children (one user can create/manage multiple child profiles)
• User → Journal Entries (one user creates many entries)
• Child → Journal Entries (one child has many entries)
• Child → Skill Progress records (one child has progress in many skills)
• Child → Suggested Activities (one child receives many suggestions)
• Skill → Entry Skills (one skill appears in many entries)
• Behaviour → Entry Behaviours (one behaviour appears in many entries)
• Knowledge Area → Activities (one knowledge area contains many activity ideas)
• Activity → Suggested Activities (one activity template can be suggested to many children)

Many-to-Many

• Journal Entries ↔ Skills (via EntrySkill junction table)
• Journal Entries ↔ Behaviours (via EntryBehaviour junction table)
• Templates ↔ Skills (one template suggests multiple skills, one skill appears in multiple templates)
• Templates ↔ Behaviours (one template may suggest behavioural traits)
• Activities ↔ Skills (one activity develops multiple skills, one skill can be developed by multiple activities)
• Activities ↔ Behaviours (one activity may develop multiple character traits)
• Knowledge Areas ↔ Skills (knowledge areas relate to multiple skills)

Optional Relationships

• Journal Entry → Template (entry may use a template, or be freeform)
• Suggested Activity → Journal Entry (if suggestion leads to logged activity)
• Child → User (child profile can eventually be linked to child's own user account for ownership transfer)

Data Flow

Journal Entry Creation Flow

1. User creates entry (photo + notes) → JournalEntry created
2. AI analyzes photo/notes → suggests Template
3. Template's associated Skills and Behaviours are pre-selected
4. User confirms/modifies → EntrySkill and EntryBehaviour records created
5. SkillProgress records updated for Child

Ideas Mode Flow

1. System analyzes Child's SkillProgress → identifies gaps
2. System queries KnowledgeArea and Activity databases for gap-filling activities
3. Activities filtered by progression level, age-appropriateness, and child interests
4. Personalized SuggestedActivity records created
5. User bookmarks/dismisses → SuggestedActivity status updated
6. User completes activity → creates JournalEntry linked to suggestion

Live CV Generation Flow

1. User requests export for Child
2. System queries JournalEntry, SkillProgress, Skills, Behaviours
3. LiveCV record generated with aggregated data (skills progress + character development)
4. Report exported and saved

Notes

• This is a living document that will be updated after each brainstorming prompt
• Focus on the data structure, not implementation details
• Consider relationships, constraints, and key fields
• Some entities (like SkillProgress) may be computed/cached rather than directly input