Database Setup - Echoes of the Past

Overview

Echoes of the Past uses Supabase as its database and authentication provider. This guide walks you through setting up your database schema, running migrations, and generating TypeScript types.

Prerequisites

Before you begin:

✅ Supabase project created (see Environment Setup)
✅ Environment variables configured
✅ Supabase CLI installed

Install Supabase CLI

Install the Supabase CLI globally:

npm install -g supabase

Verify the installation:

supabase --version

Link Your Supabase Project

Connect the Supabase CLI to your remote project:

supabase link --project-ref your-project-ref

Your project reference can be found in your Supabase dashboard URL: https://supabase.com/dashboard/project/[project-ref]

You’ll be prompted to enter your database password (set during project creation).

Database Schema Overview

The application uses the following database tables:

Tables

Table	Description
`historicalFigures`	Stores information about historical personalities
`quizzes`	Quiz sessions for users
`quizQuestions`	Questions associated with quizzes
`feedbacks`	User feedback and quiz results

Enums

Enum	Values	Usage
`categories`	`scientists`, `artists`, `philosophers`, `leaders`, `others`	Historical figure categorization
`quizType`	`ai`, `manual`	Quiz generation method
`difficulty_type`	`easy`, `medium`, `hard`	Quiz difficulty (legacy)
`mood_type`	`positive`, `neutral`, `negative`	User mood tracking (legacy)

Run Database Migrations

The project includes SQL migration files in the supabase/migrations/ directory.

Available Migrations

20240308_quiz_system_revert.sql: Removes legacy quiz system tables
20240320000000_create_feedback_table.sql: Creates the feedback system

Apply Migrations Remotely

Push migrations to your remote Supabase database:

supabase db push

This will:

Apply all pending migrations
Update your database schema
Create necessary tables, indexes, and relationships

Verify Migrations

Check the migration status:

supabase migration list

You should see:

    LOCAL      REMOTE     TIME (UTC)
  ✓  20240308  20240308   2024-03-08 10:30:00
  ✓  20240320  20240320   2024-03-20 15:45:00

Local Development Setup (Optional)

For a complete local development environment:

Start Supabase Locally

supabase start

This will:

Start a local Supabase instance using Docker
Set up PostgreSQL database
Launch Supabase Studio (local dashboard)
Configure local authentication

Requires Docker Desktop to be installed and running.

Apply Migrations Locally

supabase db reset

This will:

Reset the local database
Apply all migrations
Seed data (if configured)

Access Local Database

After starting Supabase locally, you’ll see:

Started supabase local development setup.

         API URL: http://localhost:54321
          DB URL: postgresql://postgres:postgres@localhost:54322/postgres
      Studio URL: http://localhost:54323
    Inbucket URL: http://localhost:54324
        anon key: eyJ...

Update your .env.local to use local URLs:

.env.local

NEXT_PUBLIC_SUPABASE_URL=http://localhost:54321
NEXT_PUBLIC_SUPABASE_ANON_KEY=eyJ... # use the anon key from output

Generate TypeScript Types

The project includes a script to generate TypeScript types from your Supabase schema.

Generate Types from Remote Database

Run the type generation script:

npm run gen-types

This executes:

npx supabase gen types --lang=typescript --project-id fmknwbbacrndtinyyhiw --schema public > database.types.ts

Update the --project-id flag in package.json to match your Supabase project ID.

Generated Types Structure

The database.types.ts file includes:

export type Database = {
  public: {
    Tables: {
      historicalFigures: {
        Row: { ... }      // SELECT queries
        Insert: { ... }   // INSERT operations
        Update: { ... }   // UPDATE operations
      }
      // ... other tables
    }
    Enums: {
      categories: 'scientists' | 'artists' | ...
      quizType: 'ai' | 'manual'
    }
  }
}

Using Generated Types

Import and use types in your code:

import { Database } from '@/database.types'

type HistoricalFigure = Database['public']['Tables']['historicalFigures']['Row']
type QuizInsert = Database['public']['Tables']['quizzes']['Insert']

Database Schema Details

Historical Figures Table

Stores information about historical personalities:

CREATE TABLE historicalFigures (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  name TEXT NOT NULL,
  bio TEXT NOT NULL,
  description TEXT NOT NULL,
  category categories NOT NULL,
  dateOfBirth TEXT NOT NULL,
  dateOfDeath TEXT NOT NULL,
  notableWork TEXT NOT NULL,
  imageUrl TEXT NOT NULL,
  voiceId TEXT NOT NULL,  -- ElevenLabs voice ID
  createdAt TIMESTAMP WITH TIME ZONE DEFAULT timezone('utc'::text, now())
);

Quizzes Table

Tracks quiz sessions:

CREATE TABLE quizzes (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  userId TEXT NOT NULL,
  historicalFigureId UUID NOT NULL REFERENCES historicalFigures(id),
  type quizType NOT NULL,
  created_at TIMESTAMP WITH TIME ZONE DEFAULT now(),
  updated_at TIMESTAMP WITH TIME ZONE DEFAULT now()
);

Feedbacks Table

Stores quiz results and AI-generated feedback:

CREATE TABLE feedbacks (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  userId TEXT NOT NULL,
  quizId UUID NOT NULL REFERENCES quizzes(id) ON DELETE CASCADE,
  totalScore INTEGER NOT NULL,
  categoryScores JSONB NOT NULL,
  strengths JSONB NOT NULL,
  areasForImprovement JSONB NOT NULL,
  finalAssessment TEXT NOT NULL,
  createdAt TIMESTAMP WITH TIME ZONE DEFAULT timezone('utc'::text, now())
);

Indexes for performance:

feedbacks_user_id_idx on userId
feedbacks_quiz_id_idx on quizId
GIN indexes on JSONB columns for efficient querying

Supabase Client Configuration

The application uses different Supabase clients depending on the context:

Browser Client

Location: utils/supabase/client.ts

import { createBrowserClient } from '@supabase/ssr'
import { Database } from '@/database.types'

export const createClient = () => 
  createBrowserClient<Database>(
    process.env.NEXT_PUBLIC_SUPABASE_URL!,
    process.env.NEXT_PUBLIC_SUPABASE_ANON_KEY!
  )

Use in client components and hooks.

Server Client

Location: utils/supabase/server.ts

import { createServerClient } from '@supabase/ssr'
import { cookies } from 'next/headers'

export const createClient = async () => {
  const cookieStore = await cookies()
  return createServerClient<Database>(
    process.env.NEXT_PUBLIC_SUPABASE_URL!,
    process.env.NEXT_PUBLIC_SUPABASE_ANON_KEY!,
    { cookies: { /* ... */ } }
  )
}

Use in Server Components, Server Actions, and API routes.

Middleware Client

Location: utils/supabase/middleware.ts Handles authentication in Next.js middleware for protected routes.

Row Level Security (RLS)

Ensure Row Level Security policies are configured in your Supabase dashboard to protect user data.

Recommended policies:

historicalFigures: Public read access
quizzes: Users can only access their own quizzes
feedbacks: Users can only access their own feedback

Enable RLS

In Supabase Dashboard or via SQL:

-- Enable RLS on tables
ALTER TABLE quizzes ENABLE ROW LEVEL SECURITY;
ALTER TABLE feedbacks ENABLE ROW LEVEL SECURITY;

-- Example policy: Users can only read their own quizzes
CREATE POLICY "Users can view own quizzes"
  ON quizzes
  FOR SELECT
  USING (auth.uid()::text = userId);

Backup and Restore

Create Backup

supabase db dump -f backup.sql

Restore from Backup

supabase db reset
psql $DATABASE_URL < backup.sql

Next Steps

Seed Data

Add initial historical figures data to test the application.

Test Database Connection

Run the development server and verify database queries work.

Configure Authentication

Set up Google OAuth in Supabase dashboard for user authentication.

Troubleshooting

Migration Failed

If a migration fails:

# Check migration status
supabase migration list

# Repair migrations
supabase migration repair --status applied <migration-version>

Type Generation Errors

If type generation fails:

Verify you’re logged in: supabase login
Check project ID in package.json
Ensure you have internet connection

Try generating from local database:

supabase gen types typescript --local > database.types.ts

Cannot Connect to Database

If you can’t connect:

Verify environment variables are correct
Check Supabase project status in dashboard
Ensure network firewall allows Supabase connections
Confirm database password is correct

Local Supabase Won’t Start

If local Supabase fails to start:

Ensure Docker Desktop is running
Check if ports are available (54321-54324)
Stop existing containers: supabase stop
Remove volumes: supabase db reset --force

Setup

Core Concepts

Deployment

​Overview

​Prerequisites

​Install Supabase CLI

​Link Your Supabase Project

​Database Schema Overview

​Tables

​Enums

​Run Database Migrations

​Available Migrations

​Apply Migrations Remotely

​Verify Migrations

​Local Development Setup (Optional)

​Start Supabase Locally

​Apply Migrations Locally

​Access Local Database

​Generate TypeScript Types

​Generate Types from Remote Database

​Generated Types Structure

​Using Generated Types

​Database Schema Details

​Historical Figures Table

​Quizzes Table

​Feedbacks Table

​Supabase Client Configuration

​Browser Client

​Server Client

​Middleware Client

​Row Level Security (RLS)

​Enable RLS

​Backup and Restore

​Create Backup

​Restore from Backup

​Next Steps

​Troubleshooting

​Migration Failed

​Type Generation Errors

​Cannot Connect to Database

​Local Supabase Won’t Start

Build docs developers (and LLMs) love

Overview

Prerequisites

Install Supabase CLI

Link Your Supabase Project

Database Schema Overview

Tables

Enums

Run Database Migrations

Available Migrations

Apply Migrations Remotely

Verify Migrations

Local Development Setup (Optional)

Start Supabase Locally

Apply Migrations Locally

Access Local Database

Generate TypeScript Types

Generate Types from Remote Database

Generated Types Structure

Using Generated Types

Database Schema Details

Historical Figures Table

Quizzes Table

Feedbacks Table

Supabase Client Configuration

Browser Client

Server Client

Middleware Client

Row Level Security (RLS)

Enable RLS

Backup and Restore

Create Backup

Restore from Backup

Next Steps

Troubleshooting

Migration Failed

Type Generation Errors

Cannot Connect to Database

Local Supabase Won’t Start