Skip to content

Database Setup Guide

This guide will help you set up a PostgreSQL database with Supabase to power the APIs.

Prerequisites

  • Deno installed
  • Supabase CLI installed
  • A Supabase account (free tier works fine)

This option runs Supabase locally using Docker, which is perfect for development.

Step 1: Install Supabase CLI

# macOS
brew install supabase/tap/supabase

# Or using npm
npm install -g supabase

Step 2: Start Supabase Local Development

# From the project root
supabase start

This will: - Start a local PostgreSQL database - Start Supabase Studio (web UI) at http://localhost:54323 - Start the Supabase API at http://127.0.0.1:54321 - Generate local API keys

Step 3: Get Your Local API Keys

After starting Supabase, you'll see output like:

API URL: http://127.0.0.1:54321
anon key: eyJhbGciOiJIUzI1NiIsInR5cCI6IkpXVCJ9...
service_role key: eyJhbGciOiJIUzI1NiIsInR5cCI6IkpXVCJ9...

Copy these values for your .env file.

Step 4: Create Environment File

Create a .env file in the project root:

# Copy the example file
cp env.example .env

Edit .env with your local Supabase values:

SUPABASE_URL=http://127.0.0.1:54321
SUPABASE_PUBLISHABLE_KEY=your-anon-key-from-step-3
SUPABASE_SECRET_KEY=your-service-role-key-from-step-3

Step 5: Run Database Migrations

Apply the database schema:

# Apply migrations to local database
supabase db reset

Or if you want to apply migrations incrementally:

supabase migration up

Step 6: Verify Database Setup

  1. Open Supabase Studio: http://localhost:54323
  2. Navigate to "Table Editor" to see your tables
  3. Check that all tables from the migration are created

Step 7: Start the API Server

deno task dev

The server should start at http://localhost:8000

Option 2: Cloud Supabase Project

If you prefer to use a cloud Supabase project instead of local development:

Step 1: Create a Supabase Project

  1. Go to supabase.com
  2. Sign up or log in
  3. Create a new project
  4. Wait for the project to be provisioned (takes a few minutes)

Step 2: Get Your Project Credentials

  1. Go to your project dashboard
  2. Navigate to Settings > API
  3. Copy:
  4. Project URL (e.g., https://xxxxx.supabase.co)
  5. anon public key
  6. service_role key (keep this secret!)

Step 3: Create Environment File

Create a .env file:

# Copy the example file
cp env.example .env

Edit .env with your cloud Supabase values:

SUPABASE_URL=https://your-project-ref.supabase.co
SUPABASE_PUBLISHABLE_KEY=your-anon-key
SUPABASE_SECRET_KEY=your-service-role-key

# Link to your cloud project
supabase link --project-ref your-project-ref-id

Step 5: Run Migrations

# Push migrations to cloud database
supabase db push

Or manually run the migration SQL in the Supabase SQL Editor: 1. Go to SQL Editor in your Supabase dashboard 2. Copy the contents of supabase/migrations/20241229000001_create_microlearning_schema.sql 3. Paste and run it

Step 6: Start the API Server

deno task dev

Database Schema Overview

The database includes the following main tables:

  • domains - Top-level learning categories
  • trails - Curated learning paths
  • concepts - Grouped knowledge units
  • sparks - Atomic micro-learning units (5-10 min)
  • beacons - Tags for content discovery
  • spark_links / concept_links - DAG relationships
  • journeys - User learning progress
  • learning_events - Activity tracking
  • milestones - Achievements
  • user_profiles - Extended user data

See the migration file for full schema details: supabase/migrations/20241229000001_create_microlearning_schema.sql

Row Level Security (RLS)

The database uses Supabase Row Level Security:

  • Public content (domains, trails, concepts, sparks) is readable by anyone when is_published = true
  • User data (journeys, events, milestones) is only accessible by the owning user
  • All user-specific tables have RLS policies enabled

Useful Commands

# Start local Supabase
supabase start

# Stop local Supabase
supabase stop

# View local Supabase status
supabase status

# Reset local database (applies all migrations)
supabase db reset

# View database logs (using Docker)
docker logs supabase_db_core

# Open Supabase Studio
open http://localhost:54323  # macOS
# Or visit http://localhost:54323 in your browser

Troubleshooting

"Missing Supabase configuration" error

Make sure your .env file exists and contains SUPABASE_URL and SUPABASE_PUBLISHABLE_KEY.

Database connection errors

  1. Verify Supabase is running: supabase status
  2. Check your .env file has the correct URL and keys
  3. For local development, ensure Docker is running (Supabase CLI uses Docker)

Migration errors

  1. Check the migration file syntax
  2. Ensure you're connected to the correct database
  3. For cloud projects, verify you have the correct permissions

Port conflicts

If ports 54321-54326 are in use, you can modify supabase/config.toml to use different ports.

Next Steps

  • Seed your database with initial data
  • Test API endpoints using the Swagger UI at http://localhost:8000/doc
  • Review the Developer Guide for API usage