Schema Document #

Schema v1.0 · ronilprasad8.tech · Data Architecture & Persistence

1. Database Overview #

The application uses Google Cloud Firestore as its primary database. Firestore is a NoSQL document-oriented database. There is no formal ORM (like Prisma or SQLAlchemy) used in this project; instead, the schema is implicitly defined by the JavaScript object payloads sent via the Firebase Client SDK (setDoc, addDoc) found in login.html and chatbot.js.

2. Entity-Relationship Diagram #

Although Firestore is NoSQL, conceptual relationships exist based on document IDs (the uid generated by Firebase Authentication).

erDiagram FIREBASE_AUTH { string uid PK "Generated by Firebase" string email } users { string documentId PK "Matches Auth UID" string firstName string lastName string email } knowledge { string documentId PK "Matches Auth UID (or 'ronil_global')" array facts } messages { string documentId PK "Auto-generated by Firestore" string username string text timestamp timestamp } FIREBASE_AUTH ||--o| users : "Creates" users ||--o| knowledge : "1-to-1 Mapping (Logical)"

3. Collection Definitions #

The following collections are actively written to and read from in the codebase.

Collection: `users`

Created during the signup flow in login.html (both Email/Password and Google OAuth routes).

Field	Type	Constraints / Notes	Description
`documentId`	String	Primary Key (Explicit)	Must exactly match the Firebase Authentication `user.uid`.
`firstName`	String	Required	Extracted from form input or split from Google `displayName`.
`lastName`	String	Optional	Extracted from form input or split from Google `displayName`.
`nickname`	String	Optional	Custom form input (Email registration) or fallback to `displayName` (Google).
`organization`	String	Optional	Only present for users registering via the custom Email form.
`username`	String	Optional	Derived from email prefix (e.g., `split('@')[0]`). Found only in the Google OAuth creation flow.
`email`	String	Required, Unique (by Auth)	The user's registered email address.
`createdAt`	String (ISO)	Auto-generated	Generated via JavaScript `new Date().toISOString()`.

Collection: `knowledge`

Managed by chatbot.js. Used to persist facts learned by the AI Assistant during conversations.

Field	Type	Constraints / Notes	Description
`documentId`	String	Primary Key (Explicit)	Matches the `user.uid` for normal users, or strictly `"ronil_global"` for the owner's master account.
`facts`	Array[String]	Appended via `arrayUnion`	A list of facts extracted from the LLM outputs via the `[LEARN_RONIL: ...]` or `[LEARN_USER: ...]` tags.

Collection: `messages`

Managed by chatbot.js. Triggered conditionally when the LLM outputs a [SAVE_TRIGGER: ...] tag to log specific events.

Field	Type	Constraints / Notes	Description
`documentId`	String	Auto-generated	Generated by Firestore's `addDoc()` method.
`username`	String	Required	The identifier of the user who triggered the save event.
`text`	String	Required	The payload/message content to log.
`timestamp`	Firestore Timestamp	Server-side generated	Generated via `serverTimestamp()` from the Firebase SDK.

4. Relationships #

As a NoSQL store, relationships are not enforced by foreign key constraints. Instead, they are logically maintained by the application code:

Firebase Auth → users (1-to-1): When an account is created in Firebase Auth, a corresponding document is created in the users collection where the document ID is set to the Auth UID (setDoc(doc(db, "users", user.uid), {...})).
users → knowledge (1-to-1): When a standard user talks to the AI and a fact is learned, the system stores it in a document inside the knowledge collection using their Auth UID as the document ID. This ensures the data is strictly sandboxed per user.
Exception: If the logged-in user is ronilprasad46@gmail.com, the ID used is the hardcoded string "ronil_global" instead of their UID.

5. Indexes & Constraints #

Unique Constraints: Email uniqueness is enforced globally by the Firebase Authentication service (auth/email-already-in-use), not by the users collection itself.
Array Uniqueness: The knowledge.facts array uses Firestore's arrayUnion() method, which natively prevents duplicate string entries from being added to the array.
Indexes: There are no custom composite indexes defined (e.g., no firestore.indexes.json file found in the codebase). Firestore automatically creates single-field indexes for all fields in a document by default.