SHUSHU Database Architecture & Core Logic

1. Database Architecture (SQLite)

The system is built on a relational database utilizing three core tables to separate the English curriculum from target languages and user progress.

TABLE core_concepts

Purpose: Holds the agnostic English curriculum.

id
item_type (vocab/sentence)
decade
english_text
target_emoji

TABLE translations

Purpose: Connects target languages to the core concepts.

id
concept_id (Foreign Key)
language_code (e.g., 'es')
translated_text

TABLE user_deck

Purpose: Tracks the live state of the user's progress.

id
concept_id (Foreign Key)
status (locked/active/mastered)
hearts (0 to 5)

2. Current MVP Game Loop (What works right now)

The basic script currently successfully handles the following logic flow:

Data Routing: Successfully runs a 3-way SQL JOIN to pull only active cards.
The Prompt: Randomly selects an active card and prompts the user with the Spanish text.
Input Validation: Accepts typed English input and checks it against the database (case-insensitive).
Progression: Awards +1 heart for a correct answer.
Unlock Logic: When a card hits 5 hearts, it updates to mastered and automatically changes the next sequential locked card to active.

3. The Missing Mechanics (Phase 3 Roadmap)

To turn this basic loop into a fully functional "ugly but testable" trainer for family and friends, we must build the following systems into the Phase 3 script:

MISSING Multi-User Support (Profiles)

Currently, the user_deck is global. If your cousin plays, they overwrite your progress. We need to add a user_id to the deck table so multiple people can log in and have their own distinct saves.

MISSING Penalties & Degradation

Right now, wrong answers do nothing. We need logic that deducts a heart (or resets to 0) if they get it wrong, forcing them to actually learn it.

MISSING Spaced Repetition (Review Mode)

Once a card is mastered, it disappears forever. We need a "Review" mechanic that periodically pulls mastered cards back into the active rotation so they don't forget them.

MISSING Directional Toggles

The user should be able to choose: Am I translating Spanish-to-English (reading comprehension), or English-to-Spanish (active recall)?

MISSING Session Limits & "New" vs "Review"

Endless loops cause burnout. The game needs to serve a specific "Session" (e.g., 10 cards: 7 reviews, 3 new ones) and give a "Session Complete" summary screen with their session stats and accuracy.