Data Model

The frontend's data model is defined entirely in TypeScript and mirrors the Supabase PostgreSQL schema. There is no ORM - queries are written as raw Supabase client calls with typed results.

Type Organization

Types are spread across three files in lib/types/:

Domain types use camelCase properties. The Database generated types use snake_case. Conversion between the two happens at the data access layer.

Core Entities

Profile

Maps to the profiles table. Core fields: id (UUID, FK to auth.users), email, display_name, avatar_url, bio, status (active | blocked), created_at.

Book

Maps to the books table. Represents a published book with its characters:

BookCharacter

Maps to the characters table. Each character belongs to one book: id, bookId, name, description, avatarUrl, color, emoji, createdAt.

ChatSession

Maps to the chat_sessions table. Links a user, a character, and a book: id, userId, characterId, bookId, createdAt, updatedAt.

ChatMessage

Maps to the messages table: id, sessionId, role ("user" | "assistant"), content, createdAt.

Category

Maps to the categories table: id (integer), name, slug, imageUrl, bookCount (computed), createdAt.

BookComment

Maps to the book_comments table: id, bookId, userId, content, createdAt, updatedAt. Includes a nested profile object with displayName and avatarUrl from a join.

Snake-Case Conversion

The conversion pattern is consistent across every DB query module:

// At the boundary - converting a DB row to a domain type
const book = toCamelCase(dbRow) as Book;

// At the boundary - converting a domain type to DB fields
const dbFields = toSnakeCase({ title, author });

The utility functions in lib/utils/transform.ts handle deeply nested conversion. They are applied by each query function before returning results to callers. This keeps the rest of the application in camelCase while the schema remains in PostgreSQL snake_case.

Generated Type Safety

The lib/types/database.ts file is generated by supabase db types and includes the full schema as a nested type. The Supabase client factories use this type parameter:

export const supabase = createBrowserClient<Database>(
  url, key
);

This gives type-safe access to .from('books').select('*') queries - the result is typed to the tables.books.Row interface.

Tables Referenced

The frontend queries approximately 15 tables. Not all have corresponding domain types - some are accessed through generic Supabase queries with inline type assertions:

• auth.users - Supabase-managed, never queried directly from the frontend
• profiles - typed via Profile domain type
• user_roles - typed inline in auth middleware
• books - typed via Book domain type
• book_characters - via BookCharacter
• book_categories - join table, typed inline
• book_comments - via BookComment
• book_ratings - via BookRating
• chat_sessions - via ChatSession
• messages - via ChatMessage
• user_preferences - via PreferenceReturn
• purchases - typed inline in payment code
• user_library - typed inline in library queries
• notifications - typed inline
• settings - typed inline in admin code