Database Schema & Diagram

Navigation: README | Diagram Architecture | How It Works | Database Schema | Challenge Requirements | Deployment

The KidStory (ai.kidstory.app) application uses Google Cloud Firestore (NoSQL) for storing user data, story metadata, and quiz results. Firestore is also the RAG data source for three Google ADK v1.0 agents — LearningAdvisor, ParentInsights, and StoryAdaptation — which read quiz history at query time to ground their responses.

We use a hierarchical structure where stories are stored as a sub-collection under each user. This ensures data isolation and efficient querying for individual users.

1. Entity Relationship Diagram (ERD)

---

2. Collection Reference

users (Collection)

Root collection for user profiles.

• Document ID: uid (from Firebase Authentication)

Field	Type	Description
uid	String	The unique user ID from Firebase Auth.
email	String	User's email address.
displayName	String	User's full name.
photoURL	String	URL to user's Google profile picture.
createdAt	Timestamp	When the account was first created.
totalStories	Number	Counter for total stories generated.
currentStreak	Number	Consecutive days with at least one story or quiz activity.
lastActivityDate	String	UTC date (YYYY-MM-DD) of the most recent activity. Used to compute streaks.
updatedAt	Timestamp	Set by updateStreak on every streak write.
referralCode	String	Unique 6-char uppercase code (e.g. K7MX2Q). Auto-generated on first sign-in.
referralsMade	Number	How many new users have redeemed this user's code.
referredBy	String	UID of the user who referred this person. Set once on first sign-in with a code. Prevents double-redeem.

---

users/{uid}/stories (Sub-Collection)

Stores all stories generated by a specific user.

• Document ID: Auto-generated UUID

Field	Type	Description
id	String	Unique identifier for the story (UUID).
title	String	The AI-generated title of the story.
authorName	String	Denormalized display name of the story owner. Stored at save time so public pages can show the author without a cross-collection read.
prompt	String	The original prompt provided by the child.
language	String	Language code for the story (en, es, fr, pt, pt_pt, de, it, ja, ko, zh, ar, hi, id, ru, vi, ms, fil, tet, my, lo, km). Defaults to "en".
coverImageUrl	String	GCS URL to the first page's illustration.
status	String	Generation status (generating, complete, error).
isPublic	Boolean	Whether the story is publicly accessible via /s/[id]. Defaults to false.
createdAt	Timestamp	When the story was generated.
pages	Array<Object>	An ordered array of page content (see below).
lastQuizScore	Number	Score of the most recent quiz (e.g., 4).
lastQuizTotal	Number	Total questions in the quiz (e.g., 5).
lastQuizDate	Timestamp	When the last quiz was completed.

pages Array Object Structure

Each object in the pages array represents a single page of the book:

{
  "pageNumber": 1,
  "text": "Once upon a time, in a land of sparkling rivers...",
  "imageUrl": "https://storage.googleapis.com/bucket/images/storyId/page-1.png",
  "audioUrl": "https://storage.googleapis.com/bucket/audio/storyId/page-1.mp3",
  "imagePrompt": "Watercolor illustration of a magical forest with sparkling rivers..."
}

Field	Type	Description
pageNumber	Number	Page order (1-indexed).
text	String	The story text for this page (written in the story's language).
imageUrl	String	GCS signed URL to the watercolor illustration.
audioUrl	String	GCS signed URL to the narration audio (MP3).
imagePrompt	String	The prompt used to generate the illustration (always in English for image model compatibility).

---

3. Supported Languages

The language field uses the following codes. Story text, quiz questions, TTS narration, and feedback are all generated in the selected language.

Code	Language	Voice Input Support
en	English	Yes
es	Spanish	Yes
fr	French	Yes
pt	Portuguese	Yes
de	German	Yes
it	Italian	Yes
ja	Japanese	Yes
ko	Korean	Yes
zh	Chinese (Simplified)	Yes
ar	Arabic	Yes
hi	Hindi	Yes
id	Indonesian	Yes
ru	Russian	Yes
vi	Vietnamese	Yes
ms	Malay	Yes
fil	Filipino	Yes
pt_pt	Portuguese (Portugal)	Yes
tet	Tetum	No (tap only)
my	Burmese (Myanmar)	No (tap only)
lo	Lao	No (tap only)
km	Khmer (Cambodia)	No (tap only)

Note: Stories created before language support was added default to "en" when the field is absent. The language field is used by the quiz system to generate questions and TTS audio in the correct language.

---

users/{uid}/settings/credits (Document)

Tracks the user's credit balance and monthly usage.

Field	Type	Description
credits	Number	Current credit balance.
lastResetMonth	String	"YYYY-MM" format. Used to detect monthly reset.
totalCreditsUsed	Number	Lifetime credits consumed.
storiesThisMonth	Number	Stories created in the current month.
quizzesThisMonth	Number	Quizzes taken in the current month.
monthlyLimit	Number	Plan credit limit — 30 (Free) or 150 (Pro). Source of truth for Pro status: isPro = monthlyLimit > 30.
proSince	String	ISO date when Pro was activated. null for Free users.
proExpiresAt	String	ISO date of Pro expiry (end of current month). null for Free users. Displayed on the profile page.

Credits reset to monthlyLimit on the 1st of each month. Story generation costs 5 credits, quiz costs 1 credit.

---

users/{uid}/settings/parentInsights (Document)

Stores the parent's email report preferences and last delivery date.

Field	Type	Description
email	String	Email address to send progress reports to.
frequency	String	"weekly" or "monthly".
enabled	Boolean	Whether email reports are active.
lastSentAt	Timestamp	When the most recent report was successfully delivered.

Written by POST /api/parent-insights (action: send). Read by useParentInsights hook.

---

users/{uid}/adaptations (Sub-Collection)

Cached output from StoryAdaptationAgent. One document per run, keyed by auto-generated ID.

Field	Type	Description
adaptedPrompt	String	Ready-to-use story prompt calibrated to the child's level.
vocabularyLevel	String	"simple" / "moderate" / "rich".
pageCount	Number	Recommended pages: 4, 5, or 6.
focusTheme	String	Central story theme (e.g. "ocean adventure").
adaptationReason	String	Why this story was chosen (shown to parent).
encouragement	String	Warm sentence shown to the child before reading.
durationMs	Number	Agent call duration in ms (used by Observability Dashboard).
createdAt	Timestamp	When this adaptation was generated.

Read by GET /api/agent-stats and GET /api/adapt-story. The most recent document is used as cache — the agent is skipped if no new quiz has been completed since the last adaptation.

---

users/{uid}/settings/achievements (Document)

Tracks earned badges and PDF download count.

Field	Type	Description
earnedIds	String[]	Array of earned badge IDs.
pdfDownloads	Number	Count of PDF downloads (for badge).
updatedAt	String	ISO date of last update.

---

4. Storage Structure (Google Cloud Storage)

Media assets are stored in a GCS bucket (default: storybook-for-kids-media) with 7-day signed URLs.

gs://storybook-for-kids-media/
  images/
    {storyId}/
      page-1.png
      page-2.png
      ...
  audio/
    {storyId}/
      page-1.mp3
      page-2.mp3
      ...
  recommendation/
    {userId}/
      insight.wav

Path Pattern	Format	Description
images/{storyId}/page-{N}.png	PNG	Watercolor illustration for page N
audio/{storyId}/page-{N}.mp3	MP3/WAV	Narration audio for page N
recommendation/{userId}/insight.wav	WAV	Gemini TTS audio for the AI learning recommendation on the profile page. Overwritten each time a new recommendation is generated.

---

feedback (Collection)

User feedback submitted via the in-app feedback button.

Field	Type	Description
userId	String	Firebase Auth UID (or "anonymous").
userEmail	String	User's email (nullable).
userName	String	User's display name (nullable).
rating	Number	1-5 star rating.
message	String	Optional text feedback (nullable).
language	String	"en" or "tet" — language used in the form.
createdAt	String	ISO date string.

Security: any authenticated user can create. No client reads (admin-only via Firebase Console).

---

Related Documentation

• How This App Works — How the workflows populate and use this data
• Complete Diagram Architecture — Visual diagrams showing data flow between components
• Challenge Requirements — How the data architecture meets Challenge criteria
• Deployment Guide — Setting up Firestore and Cloud Storage for deployment