BookBrain

BookBrain helps readers turn books into long-term vocabulary growth. It tracks words and quotes you discover, and gives you a workflow to revisit them later (so learning sticks). Users authenticate, then connect their own PostgreSQL database for content storage, so they keep ownership of their data.

Why this exists

Many reading apps can store notes, but they do not provide a clean path for structured vocabulary review. BookBrain focuses on:

• Capturing words and quotes with consistent structure
• Scheduling reviews (spaced repetition style) via nextReviewAt
• Keeping user data in the user's own database rather than a shared app database

What it solves

• Avoids “lock-in” by letting users provide their own content database connection string
• Makes onboarding predictable by provisioning the required schema automatically
• Supports updating credentials later without breaking the redirect/setup flow

Architecture (two databases)

BookBrain uses two PostgreSQL databases:

App database (your control)

Stores:

• NextAuth user accounts/sessions
• Encrypted per-user secrets (the user content DB URL and the AI API key)

Prisma schema: prisma/schema.prisma

User content database (user ownership)

Stores:

• Book, Word, Quote, and ChatMessage data
• A small metadata table (public.bookbrain_meta) used to track provisioning/schema version

Prisma schema: prisma/schema.content.prisma

The content schema is provisioned from SQL files in prisma/content-sql/*.

Setup and runtime flow

1. User signs up / logs in

Authentication is handled by NextAuth (credentials and/or Google).

2. User provides content DB + AI key

On /setup, the user submits:

• Their PostgreSQL connection string for their content DB
• Their Google Gemini API key

The app saves these values in the app database (encrypted).

3. Provision the user’s content DB if needed

After saving, the app:

• Checks /api/content-db/status to see whether the required tables/schema exist
• If not provisioned, calls /api/content-db/provision to create/upgrade tables

When provisioning succeeds, the user is redirected to the homepage.

4. Updating keys later

Users can revisit /setup at any time to update their content DB URL and/or AI key. The app keeps the setup state in sync and avoids infinite redirect loops.

Key endpoints

• /api/settings/secrets for saving/loading encrypted secrets (app DB)
• /api/content-db/status to detect whether the content schema is already provisioned (user DB)
• /api/content-db/provision to provision/upgrade the content schema into the user DB

Security and privacy notes

• User content stays in the user-provided database. The app provisions the schema but does not migrate/own the user's data.
• Secrets are encrypted using ENCRYPTION_KEY.

Tech stack

• Next.js (App Router)
• NextAuth
• Prisma (two Prisma schemas/clients)
• PostgreSQL
• pg for direct content DB provisioning checks/execution
• Gemini integration (via @ai-sdk/google)

Local development

1. Install dependencies:
   • pnpm install
2. Configure .env
3. Start:
   • pnpm dev

Common environment variables:

• APP_DATABASE_URL (required: app DB for auth + encrypted secrets)
• ENCRYPTION_KEY (required: used to encrypt the user DB URL and AI key)
• AUTH_SECRET (required: NextAuth)
• AUTH_GOOGLE_ID / AUTH_GOOGLE_SECRET (optional: Google sign-in)
• CONTENT_DATABASE_URL (required by Prisma tooling for content schema generation/migrations; runtime uses user-provided DB URL)