Database Setup

Overview

Quail BI requires two database services:

MongoDB

Stores user data, connections, dashboards, and chat history

Supabase

Handles user authentication and session management

MongoDB Setup

Option 1: MongoDB Atlas (Recommended)

Create Account

Create Cluster

Click Create to create a new cluster
Choose the Free Shared tier (M0)
Select a cloud provider and region close to your users
Name your cluster (e.g., quail-production)
Click Create Cluster

Configure Network Access

Navigate to Network Access in the sidebar
Click Add IP Address
Choose Allow Access from Anywhere (0.0.0.0/0) for development
For production, whitelist specific IPs

Create Database User

Navigate to Database Access
Click Add New Database User
Choose Password authentication
Create a username and secure password
Set Database User Privileges to Read and write to any database
Click Add User

Get Connection String

Go to Database and click Connect on your cluster
Choose Connect your application
Copy the connection string:

mongodb+srv://<username>:<password>@cluster.mongodb.net/<database>?retryWrites=true&w=majority

Replace <username>, <password>, and <database> with your values

Configure Environment

Add the connection string to .env.local:

MONGODB_URI=mongodb+srv://username:[email protected]/quail?retryWrites=true&w=majority

Never commit your connection string to version control. Keep it in .env.local only.

Option 2: Local MongoDB

For development, you can run MongoDB locally:

macOS
Linux
Windows
Docker

# Install via Homebrew
brew tap mongodb/brew
brew install [email protected]

# Start MongoDB
brew services start [email protected]

# Connection string
MONGODB_URI=mongodb://localhost:27017/quail

# Ubuntu/Debian
wget -qO - https://www.mongodb.org/static/pgp/server-7.0.asc | sudo apt-key add -
echo "deb [ arch=amd64,arm64 ] https://repo.mongodb.org/apt/ubuntu $(lsb_release -sc)/mongodb-org/7.0 multiverse" | sudo tee /etc/apt/sources.list.d/mongodb-org-7.0.list
sudo apt-get update
sudo apt-get install -y mongodb-org

# Start MongoDB
sudo systemctl start mongod

# Connection string
MONGODB_URI=mongodb://localhost:27017/quail

Download MongoDB Community Server from mongodb.com/try/download/community
Run the installer
Start MongoDB from Services or Command Prompt
Connection string:

MONGODB_URI=mongodb://localhost:27017/quail

# Run MongoDB container
docker run -d \
  --name quail-mongodb \
  -p 27017:27017 \
  -e MONGO_INITDB_ROOT_USERNAME=admin \
  -e MONGO_INITDB_ROOT_PASSWORD=password \
  -e MONGO_INITDB_DATABASE=quail \
  -v quail-mongo-data:/data/db \
  mongo:7.0

# Connection string
MONGODB_URI=mongodb://admin:password@localhost:27017/quail?authSource=admin

MongoDB Database Schema

Quail automatically creates these collections:

users

Stores user account information (synced with Supabase).

{
  "_id": "ObjectId",
  "supabaseId": "uuid",
  "email": "[email protected]",
  "createdAt": "ISODate",
  "updatedAt": "ISODate"
}

Indexes: { supabaseId: 1 } (unique), { email: 1 }

connections

Stores encrypted database connection strings.

{
  "_id": "ObjectId",
  "userId": "ObjectId",
  "name": "Production DB",
  "type": "postgresql",
  "encryptedConnectionString": "encrypted-data-here",
  "createdAt": "ISODate",
  "updatedAt": "ISODate",
  "lastConnected": "ISODate"
}

Indexes: { userId: 1 }, { userId: 1, name: 1 }

dashboards

Stores dashboard configurations.

{
  "_id": "ObjectId",
  "userId": "ObjectId",
  "name": "Sales Dashboard",
  "layout": [],
  "filters": [],
  "createdAt": "ISODate",
  "updatedAt": "ISODate"
}

Indexes: { userId: 1 }, { userId: 1, createdAt: -1 }

charts

Stores chart configurations.

{
  "_id": "ObjectId",
  "userId": "ObjectId",
  "dashboardId": "ObjectId",
  "connectionId": "ObjectId",
  "name": "Monthly Revenue",
  "type": "line",
  "config": {},
  "query": "SELECT ...",
  "createdAt": "ISODate",
  "updatedAt": "ISODate"
}

Indexes: { userId: 1 }, { dashboardId: 1 }

chats

Stores chat conversation history.

{
  "_id": "ObjectId",
  "userId": "ObjectId",
  "connectionId": "ObjectId",
  "messages": [],
  "createdAt": "ISODate",
  "updatedAt": "ISODate"
}

Indexes: { userId: 1 }, { userId: 1, createdAt: -1 }

Creating Indexes

For optimal performance, create indexes manually:

// Connect to your database
use quail;

// User indexes
db.users.createIndex({ "supabaseId": 1 }, { unique: true });
db.users.createIndex({ "email": 1 });

// Connection indexes
db.connections.createIndex({ "userId": 1 });
db.connections.createIndex({ "userId": 1, "name": 1 });

// Dashboard indexes
db.dashboards.createIndex({ "userId": 1 });
db.dashboards.createIndex({ "userId": 1, "createdAt": -1 });

// Chart indexes
db.charts.createIndex({ "userId": 1 });
db.charts.createIndex({ "dashboardId": 1 });

// Chat indexes
db.chats.createIndex({ "userId": 1 });
db.chats.createIndex({ "userId": 1, "createdAt": -1 });

Supabase Setup

Create Supabase Project

Create an account at supabase.com

Create Project

Click New Project
Enter a project name (e.g., quail-production)
Set a secure database password (save this!)
Choose a region close to your users
Click Create new project

Wait 2-3 minutes for the project to initialize.

Get API Keys

Go to Settings → API in the sidebar
Copy three values:
- Project URL (e.g., https://abc123.supabase.co)
- anon public key (under “Project API keys”)
- service_role secret key (click “Reveal” to see it)

Configure Environment

Add these to .env.local:

.env.local

NEXT_PUBLIC_SUPABASE_URL=https://your-project.supabase.co
NEXT_PUBLIC_SUPABASE_ANON_KEY=your-anon-key-here
SUPABASE_SERVICE_ROLE_KEY=your-service-role-key-here

The service_role key bypasses Row Level Security. Keep it secret and never expose it to the client.

Configure Authentication

Enable Email Authentication

Go to Authentication → Providers
Ensure Email is enabled
Configure email templates (optional)

Configure Email Templates (Optional)

Go to Authentication → Email Templates
Customize the confirmation and password reset emails
Add your branding and domain

Set Up Redirect URLs

Go to Authentication → URL Configuration
Add your site URL (e.g., https://quail.yourdomain.com)
Add redirect URLs:
- http://localhost:3000/** (for development)
- https://yourdomain.com/** (for production)

Configure Row Level Security (Optional)

For additional security, enable Row Level Security (RLS) on Supabase tables:

Quail stores most data in MongoDB, so RLS is optional. However, if you add tables to Supabase, enable RLS.

-- Enable RLS on a table
ALTER TABLE your_table ENABLE ROW LEVEL SECURITY;

-- Allow users to read their own data
CREATE POLICY "Users can view own data"
ON your_table
FOR SELECT
USING (auth.uid() = user_id);

-- Allow users to insert their own data
CREATE POLICY "Users can insert own data"
ON your_table
FOR INSERT
WITH CHECK (auth.uid() = user_id);

Testing Authentication

Verify authentication is working:

import { createClient } from '@supabase/supabase-js';

const supabase = createClient(
  process.env.NEXT_PUBLIC_SUPABASE_URL!,
  process.env.NEXT_PUBLIC_SUPABASE_ANON_KEY!
);

// Test sign up
const { data, error } = await supabase.auth.signUp({
  email: '[email protected]',
  password: 'securepassword123',
});

if (error) {
  console.error('Auth error:', error);
} else {
  console.log('Sign up successful:', data);
}

Production Considerations

MongoDB

Backups

Enable automated backups in MongoDB Atlas or set up regular mongodump backups

Monitoring

Use MongoDB Atlas monitoring or tools like Datadog, New Relic

Scaling

Upgrade to a larger cluster tier as usage grows

Security

Use IP whitelisting and strong passwords

Supabase

Custom Domain

Configure a custom domain for authentication emails

Rate Limiting

Monitor API usage and configure rate limits

Upgrade Plan

Free tier has limits; upgrade for production

Email Provider

Configure a custom SMTP provider for better deliverability

Troubleshooting

MongoDB connection fails

Check these common issues:

Connection string format: Ensure no extra spaces or line breaks
Network access: Verify IP is whitelisted in MongoDB Atlas
Credentials: Check username and password are correct
Database name: Ensure the database name exists

Test connection with MongoDB Compass:

# Download MongoDB Compass and test the connection string

Supabase authentication doesn't work

Common issues:

Wrong keys: Verify you’re using the correct anon and service_role keys
URL mismatch: Check the project URL matches your Supabase project
Email not confirmed: Check inbox for confirmation email
Redirect URL: Add your site URL to allowed redirect URLs

Test in Supabase dashboard:

Go to Authentication → Users
Try creating a test user

'NEXT_PUBLIC_SUPABASE_URL is not defined' error

Environment variables are not loaded:

Verify .env.local exists in project root
Restart the dev server after adding variables
Check variable names match exactly (case-sensitive)

Next Steps

Environment Variables

Configure all environment variables

Configuration

Advanced configuration options

Security

Secure your databases

Troubleshooting

Fix common database issues

Self-Hosting

Operations

Overview

MongoDB

Supabase

MongoDB Setup

Option 1: MongoDB Atlas (Recommended)

Option 2: Local MongoDB

MongoDB Database Schema

Creating Indexes

Supabase Setup

Create Supabase Project

Configure Authentication

Configure Row Level Security (Optional)

Testing Authentication

Production Considerations

MongoDB

Backups

Monitoring

Scaling

Security

Supabase

Custom Domain

Rate Limiting

Upgrade Plan

Email Provider

Troubleshooting

Next Steps

Environment Variables

Configuration

Security

Troubleshooting

Build docs developers (and LLMs) love

Self-Hosting

Operations

​Overview

MongoDB

Supabase

​MongoDB Setup

​Option 1: MongoDB Atlas (Recommended)

​Option 2: Local MongoDB

​MongoDB Database Schema

​Creating Indexes

​Supabase Setup

​Create Supabase Project

​Configure Authentication

​Configure Row Level Security (Optional)

​Testing Authentication

​Production Considerations

​MongoDB

Backups

Monitoring

Scaling

Security

​Supabase

Custom Domain

Rate Limiting

Upgrade Plan

Email Provider

​Troubleshooting

​Next Steps

Environment Variables

Configuration

Security

Troubleshooting

Build docs developers (and LLMs) love

Overview

MongoDB Setup

Option 1: MongoDB Atlas (Recommended)

Option 2: Local MongoDB

MongoDB Database Schema

Creating Indexes

Supabase Setup

Create Supabase Project

Configure Authentication

Configure Row Level Security (Optional)

Testing Authentication

Production Considerations

MongoDB

Supabase

Troubleshooting

Next Steps