Database Schema

To build a robust and scalable database schema for your application using Supabase, we need to focus on several key aspects: normalization, relationships, indexing, and security. Given that you’re using Supabase, which is built on top of PostgreSQL, we can leverage PostgreSQL’s powerful features like Row-Level Security (RLS), JSONB, full-text search, and triggers. Below, I’ll outline the best practices and suggestions for your database schema, along with the approach to implement it.

1. Database Schema Design

Core Tables & Relationships

Your current schema is well-structured, but let’s refine it further to ensure scalability and maintainability.

Users Table (Handled by Supabase Auth):
- id (UUID, primary key)
- email (unique, for authentication)
- created_at (timestamp, for analytics)
- RLS Policy: Only the user can access their own data (auth.uid() = id).
Books Table:
- id (UUID, primary key)
- user_id (UUID, references Users.id)
- title (text, for book title)
- author (text, for author name)
- format (enum: EPUB/PDF, for file type)
- file_path (text, Supabase Storage path for the file)
- status (enum: Wish/In-Progress/Completed, for book progress)
- priority_score (integer, for prioritization engine)
- metadata (JSONB, for custom fields like recommendation sources, genre, etc.)
- RLS Policy: Users can only access their own books (auth.uid() = user_id).

-- Add these columns to the existing `Books` table:
ALTER TABLE books
  ADD COLUMN cover_image_path TEXT,          -- Path to auto-detected/book cover in Supabase Storage
  ADD COLUMN published_date DATE,            -- Publication date of the book
  ADD COLUMN word_count INT,                 -- e.g., 58,385 words
  ADD COLUMN read_time INTERVAL,             -- e.g., 3 hours 41 mins
  ADD COLUMN linked_obsidian_notes JSONB,    -- Array of Obsidian note paths/IDs (e.g., ["obsidian://vault/note1.md"])
  ADD COLUMN status VARCHAR(20)              -- ENUM: Wish, Available, In-Progress, Lapse, Finished, Note-taking, Spaced-Repetition

Highlights Table:
- id (UUID, primary key)
- book_id (UUID, references Books.id)
- user_id (UUID, references Users.id)
- content (text, for the highlighted text)
- page (integer, for page number)
- tags (JSONB array, for tags associated with the highlight)
- created_at (timestamp, for sorting and analytics)
- RLS Policy: Users can only access their own highlights (auth.uid() = user_id).
Tags Table:
- id (UUID, primary key)
- user_id (UUID, references Users.id)
- name (text, for tag name)
- parent_tag_id (UUID, self-referencing for nested tags)
- RLS Policy: Users can only access their own tags (auth.uid() = user_id).
ObsidianSync Table (for Obsidian plugin integration):
- user_id (UUID, references Users.id)
- obsidian_api_token (text, encrypted for security)
- last_sync_time (timestamp, for tracking sync status)
- RLS Policy: Users can only access their own sync data (auth.uid() = user_id).

BookEvents

CREATE TABLE book_events (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  book_id UUID REFERENCES books(id) ON DELETE CASCADE,
  user_id UUID REFERENCES auth.users(id) ON DELETE CASCADE,
  event_type VARCHAR(50),  -- e.g., 'added', 'highlighted', 'last_visited', 'progress_updated'
  event_data JSONB,        -- e.g., { "highlight_count": 10, "progress_percent": 45 }
  created_at TIMESTAMPTZ DEFAULT NOW()
);

BookNotes

2. Indexing for Performance

Indexes are critical for query performance, especially as your dataset grows. Here’s how to optimize your schema with indexes:

Books Table:
- Composite index on (user_id, status, priority_score) for fast filtering of books by user, status, and priority.
- Full-text search index on title and author for unified search functionality.
Highlights Table:
- Composite index on (user_id, book_id) for fast retrieval of highlights by user and book.
- GIN index on tags (JSONB array) for efficient nested tag filtering.
Tags Table:
- Index on (user_id, name) for fast tag lookups by user.
ObsidianSync Table:
- Index on user_id for quick access to sync data.

3. Row-Level Security (RLS)

Supabase provides RLS to ensure that users can only access their own data. Here’s how to enforce it:

Users Table:
- Policy: auth.uid() = id (only the user can access their own data).
Books Table:
- Policy: auth.uid() = user_id (users can only access their own books).
Highlights Table:

Policy: auth.uid() = user_id (users can only access their own highlights).

Tags Table:

Policy: auth.uid() = user_id (users can only access their own tags).

ObsidianSync Table:

Policy: auth.uid() = user_id (users can only access their own sync data).

4. JSONB for Flexibility

Using JSONB for fields like metadata in the Books table and tags in the Highlights table provides flexibility for storing unstructured or semi-structured data. For example:

metadata can store recommendation sources, custom fields, or additional book details.
tags can store an array of tag IDs or nested tag structures.

5. Full-Text Search

PostgreSQL’s built-in full-text search is ideal for your unified search feature. Here’s how to implement it:

Create a full-text search index on Books.title and Books.author.
Use websearch_to_tsquery() for advanced search queries (e.g., “title:science AND author:Einstein”).

6. Triggers for Automation

Triggers can automate tasks like updating the priority_score in the Books table when a recommendation source is added or updated. For example:

Create a trigger that recalculates priority_score whenever metadata is updated.

7. Supabase Setup

To implement this schema in Supabase: 13. Create Tables:

Use the Supabase dashboard or SQL commands to create the tables with the defined schema.

Enable RLS:

Enable RLS on all tables and define the policies as outlined above.

Set Up Indexes:

Create the necessary indexes using SQL commands.

Seed Data:

Populate the tables with test data for development.

8. Migration Strategy

Supabase provides a migration tool for schema changes. Here’s how to manage migrations: 17. Local Development:

Use supabase start to run a local Supabase instance.
Create migrations in the supabase/migrations folder.

Deploy to Production:

Use supabase db push to apply migrations to your production database.

9. Backup & Monitoring

Backup:

Use pg_dump to create regular backups of your database.
Store backups in a secure location (e.g., GitHub Releases or cloud storage).

Monitoring:

Use Supabase’s built-in monitoring tools to track database performance.
Set up alerts for high CPU usage, storage limits, or failed queries.

10. Future Extensions

AI Integration:

Use OpenAI or similar services for auto-tagging and priority ranking.
Store AI-generated metadata in the metadata JSONB field.

Spaced Repetition:

Use cron jobs (via Supabase Edge Functions) to trigger review nudges based on user activity.

Local-First Sync:

Integrate CRDTs (e.g., Yjs) for offline support and conflict resolution.

Summary

Your database schema is well-designed, but by adding proper indexing, RLS policies, and leveraging PostgreSQL features like JSONB and full-text search, you can ensure scalability and performance. Supabase’s managed services make it easy to implement and maintain this schema, while its migration tools ensure smooth updates as your application evolves. Start by setting up the tables, enabling RLS, and creating indexes, then gradually add more complex features like triggers and AI integration.

Let me know if you need further assistance with specific SQL commands or Supabase configurations!

7.2

table_name	column_name	data_type
user_missions	completed_books	integer
highlights	updated_at	timestamp with time zone
challenges	id	uuid
challenges	user_id	uuid
challenges	progress	integer
challenges	total	integer
challenges	days_left	integer
challenges	start_date	timestamp with time zone
challenges	end_date	timestamp with time zone
challenges	created_at	timestamp with time zone
challenges	updated_at	timestamp with time zone
missions	id	uuid
missions	user_id	uuid
missions	progress	integer
missions	target_books	jsonb
missions	target_tags	jsonb
missions	start_date	timestamp with time zone
missions	end_date	timestamp with time zone
missions	created_at	timestamp with time zone
missions	updated_at	timestamp with time zone
users	id	uuid
users	metadata	jsonb
users	created_at	timestamp with time zone
users	updated_at	timestamp with time zone
obsidian_sync	last_sync_time	timestamp with time zone
obsidian_sync	created_at	timestamp with time zone
tags	id	uuid
tags	user_id	uuid
tags	created_at	timestamp with time zone
tags	parent_id	uuid
user_challenges	id	uuid
user_challenges	user_id	uuid
user_challenges	total_reading_time	integer
user_challenges	books_read	integer
user_challenges	current_streak	integer
user_challenges	longest_streak	integer
user_challenges	last_read_date	date
user_challenges	created_at	timestamp with time zone
user_challenges	updated_at	timestamp with time zone
user_missions	id	uuid
user_missions	user_id	uuid
obsidian_sync	user_id	uuid
user_missions	reading_books	integer
user_missions	total_highlights	integer
user_missions	created_at	timestamp with time zone
user_missions	updated_at	timestamp with time zone
books	id	uuid
books	progress	integer
books	user_id	uuid
books	metadata	jsonb
books	priority_score	integer
books	created_at	timestamp with time zone
books	updated_at	timestamp with time zone
books	last_read	timestamp with time zone
highlights	id	uuid
highlights	book_id	uuid
highlights	user_id	uuid
highlights	page	integer
highlights	created_at	timestamp with time zone
obsidian_sync	obsidian_api_token	text
missions	icon_type	text
users	name	text
books	title	text
books	author	text
tags	name	text
books	format	text
books	file_url	text
books	cover_url	text
books	status	text
users	avatar_url	text
users	provider	text
challenges	title	text
challenges	description	text
challenges	type	text
users	email	text
missions	title	text
missions	description	text
challenges	reward	text
challenges	status	text
highlights	text	text
highlights	note	text
missions	status	text

Build

Explorer