Database Setup

A²D uses Supabase for PostgreSQL database and authentication. This guide covers both automated and manual setup methods.

Overview

Setting up the database involves:

1. Creating a Supabase project
2. Running database migrations
3. Verifying the setup
4. Copying API credentials

Time required: ~10-15 minutes

Prerequisites

Before setting up the database, ensure you have:

• A Supabase account (sign up at supabase.com)
• Supabase CLI installed (for automated setup - optional)
• A²D repository cloned and dependencies installed

Step 1: Create Supabase Project

Creating the Project

1. Go to supabase.com/dashboard
2. Click “New Project”
3. Fill in project details:

4. Click “Create new project”

Step 2: Database Setup Methods

You have two options for setting up the database schema. Choose the method that works best for you:

Option A: Automated Setup (Recommended) ⚡

This method is faster and less error-prone.

1. Install Supabase CLI

If not already installed:

npm install -g supabase

Verify installation:

supabase --version

2. Initialize Supabase

In your A²D project directory:

supabase init

This creates a supabase/ folder with configuration files.

3. Link to Your Project

Find your project reference in the Supabase dashboard URL:

https://app.supabase.com/project/[your-project-ref]
                                      ^^^^^^^^^^^^^^^^^
                                      This is your project ref

Link your local project:

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

You’ll be prompted for:

• Database password (entered during project creation)

4. Push Migrations

Run all migrations at once:

supabase db push

What gets created:

• 11 multi-tenant tables
• Row Level Security (RLS) policies
• Database functions and triggers
• Indexes for performance
• Default design rules

Expected output:

Applying migration 20240101000000_initial_schema.sql...
Applying migration 20240101000001_rls_policies.sql...
... (8 more migrations)
Finished supabase db push.

Option B: Manual Setup 📝

If you prefer manual control or can’t use the CLI:

1. Open SQL Editor

1. Go to your Supabase project dashboard
2. Navigate to SQL Editor in the left sidebar
3. Click “New Query”

2. Run Migrations in Order

Copy the contents of each file from supabase/migrations/ and execute them one by one:

Migration Order:

1. 20240101000000_initial_schema.sql - Core tables
2. 20240101000001_rls_policies.sql - Security policies
3. 20240101000002_fix_organization_insert.sql - Organization fixes
4. 20240101000003_fix_user_insert.sql - User fixes
5. 20240101000004_add_agent_cards_and_status.sql - Agent cards
6. 20240125000001_add_user_auto_create_trigger.sql - Auto-create trigger
7. 20240126000001_fix_signup_trigger.sql - Signup trigger fix
8. 20240127000001_remove_signup_trigger.sql - Remove signup trigger
9. 20260127000001_add_mcp_auth.sql - MCP authentication
10. 20260128000001_add_design_rules.sql - Design rules

For each migration:

# Open in your code editor
code supabase/migrations/20240101000000_initial_schema.sql

-- Copy entire SQL file contents
-- Example from initial_schema.sql:
CREATE TABLE organizations (
  id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
  name TEXT NOT NULL,
  ...
);

# Paste into Supabase SQL Editor
# Click "Run"
# Wait for "Success" message

Repeat for all 10 migrations in order

Step 3: Database Schema Overview

After successful setup, your database will have these tables:

Core Tables

MCP Server Tables

Agent Card Tables

Compliance Tables

Database Diagram:

Step 4: Row Level Security (RLS)

All tables implement RLS for automatic multi-tenant data isolation.

How RLS Works

Benefits:

• Complete isolation: Organizations cannot see each other’s data
• Database-enforced: Cannot be bypassed in application code
• Automatic filtering: No manual WHERE clauses needed
• Defense in depth: Security at the database level

Policies use get_user_organization_ids() (and, where relevant, get_user_org_role()) rather than JWT claims. See Multi-Tenancy for the full RLS design and policy patterns.

Step 5: Verify Installation

After running migrations, verify the setup was successful:

1. Check Tables

1. Go to Table Editor in Supabase dashboard
2. Verify all 11 tables are listed:
   • organizations
   • users
   • mcp_servers
   • mcp_tools
   • mcp_resources
   • mcp_resource_templates
   • mcp_prompts
   • openapi_specs
   • agent_cards
   • agent_skills
   • design_rules

2. Check RLS Policies

1. Go to Authentication → Policies
2. Each table should have 4 policies:
   • select_own_org - View own organization data
   • insert_own_org - Create in own organization
   • update_own_org - Update own organization data
   • delete_own_org - Delete own organization data

3. Enable Email Authentication

1. Go to Authentication → Providers
2. Verify “Email” provider is enabled
3. (Optional) Customize email templates:
   • Settings → Auth → Email Templates

Step 6: Get API Keys

You’ll need these credentials for the next step (Configuration):

Copy API Credentials

1. Go to Settings → API
2. Copy these three values:

https://your-project-ref.supabase.co

# Used for database connections

eyJhbGciOiJIUzI1NiIsInR5cCI6IkpXVCJ9...

# Safe for client-side use
# RLS policies still apply

eyJhbGciOiJIUzI1NiIsInR5cCI6IkpXVCJ9...

# CRITICAL: Full admin access
# NEVER expose in browser or commit to git

Database Connection String

If you need to connect with external tools (psql, database GUIs):

1. Go to Settings → Database
2. Find Connection string:

postgresql://postgres:[YOUR-PASSWORD]@db.xxxxx.supabase.co:5432/postgres

Connection Pooling (for serverless):

postgresql://postgres:[YOUR-PASSWORD]@db.xxxxx.supabase.co:6543/postgres?pgbouncer=true

Troubleshooting

Migration fails with “relation already exists”

Cause: Migrations were run partially or out of order

Solution:

1. Go to Database → Reset database in Supabase dashboard
2. Confirm reset (WARNING: This deletes all data!)
3. Run all migrations again in correct order

”permission denied” errors

Cause: RLS policies not applied correctly

Solution:

1. Verify 20240101000001_rls_policies.sql ran successfully
2. Check RLS is enabled on tables:
   • Table Editor → Select table → RLS toggle should be ON
3. Re-run RLS policies migration if needed

Can’t connect from application

Cause: Incorrect API keys or project URL

Solution:

1. Double-check API keys in Supabase Settings → API
2. Ensure no extra spaces when copying keys
3. Verify project URL format: https://[ref].supabase.co (no trailing slash)
4. Test connection in browser - should show Supabase page

Tables missing after setup

Cause: Not all migrations ran successfully

Solution:

1. Check SQL Editor → History for errors
2. Identify which migrations failed
3. Fix errors and re-run failed migrations
4. Verify each migration shows “Success"

"Database password required” when linking

Cause: CLI can’t access database

Solution:

1. Ensure you’re using the database password (not account password)
2. Check project is not paused (free tier auto-pauses after 7 days inactivity)
3. Wake up project: Settings → General → Unpause project

Security Best Practices

DO ✅

• Store service_role key in .env.local (gitignored)
• Use anon key in client-side code
• Keep database password secure in password manager
• Use separate Supabase projects for dev/staging/production
• Enable database backups (Pro tier)

DON’T ❌

• Commit service_role key to git
• Use service_role key in browser/client code
• Share keys publicly or in screenshots
• Use production credentials in development
• Disable RLS policies (breaks multi-tenancy)

Next Steps

Database is ready! Proceed to:

1. Configuration - Set up environment variables with API keys
2. Quick Start - Create your first MCP server
3. Architecture: Multi-Tenancy - Deep dive into RLS

Need Help? See Database Troubleshooting or open an issue.