Database Schema & Diagram
Navigation: README | Architecture & Diagrams | How It Works | Database Schema | Hackathon Requirements | Deployment
The KidStory (ai.kidstory.app) application uses Google Cloud Firestore (NoSQL) for storing user data, story metadata, and quiz results.
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. |
users/{uid}/stories (Sub-Collection)
Stores all stories generated by a specific user.
- Document ID: Auto-generated or UUID
| Field | Type | Description |
|---|---|---|
id | String | Unique identifier for the story. |
title | String | The AI-generated title of the story. |
prompt | String | The original prompt provided by the child. |
coverImageUrl | String | GCS URL to the first page's illustration. |
status | String | Generation status (generating, complete, error). |
voice | String | Selected narrator voice (Aoede or Callirrhoe). |
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. These are populated either by the interleaved multimodal stream or by parallel generation fallback:
{
"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 (3-4 sentences). |
imageUrl | String | GCS signed URL to the watercolor illustration. |
audioUrl | String | GCS signed URL to the narration audio (WAV). |
imagePrompt | String | The prompt used to generate the illustration. |
3. 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
...| 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 |
Related Documentation
- How This App Works — How the workflows populate and use this data
- System Architecture & Diagrams — Visual diagrams showing data flow between components
- Hackathon Requirements — How the data architecture meets hackathon criteria
- Deployment Guide — Setting up Firestore and Cloud Storage for deployment