Database Deployment

Artbase Studio uses Supabase for PostgreSQL database hosting, authentication, and storage.

Supabase Project Setup

Creating a Project

1. Go to supabase.com
2. Create new project
3. Select region closest to users
4. Note connection details

Configuration

Project Settings → API:

• Project URL: Your Supabase URL
• anon public key: Client-side key
• service_role key: Server-side key (keep secret)

---

Database Migrations

Migration Files

Migrations live in supabase/migrations/:

supabase/migrations/
├── 001_initial_schema.sql
├── 002_products_schema.sql
├── 003_orders_schema.sql
├── 013b_product_variants_schema.sql
├── 020_inventory_items_schema.sql
├── 021_customers_schema.sql
├── 022_orders_platform_fee.sql
├── 023_shipping_automation_trigger.sql
└── 024_cart_abandonment.sql

Applying Migrations

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

# Push migrations to remote
supabase db push

# Check migration status
supabase migration list

Creating New Migrations

# Generate migration file
supabase migration new add_loyalty_points

# Edit the generated file
code supabase/migrations/$(ls -t supabase/migrations | head -1)

Migration Best Practices

1. One change per migration - Easier to rollback
2. Include rollback comments - Document how to reverse
3. Test locally first - Use supabase db reset
4. Never modify existing migrations - Create new ones

-- Migration: Add loyalty_points to customers
-- Rollback: ALTER TABLE customers DROP COLUMN loyalty_points;

ALTER TABLE customers
ADD COLUMN loyalty_points INTEGER DEFAULT 0;

---

Authentication Setup

Email Templates

Configure in Dashboard → Authentication → Email Templates:

• Confirm signup: Welcome email
• Magic Link: Passwordless login
• Reset Password: Password recovery
• Invite user: Team invitations

OAuth Providers

Configure in Dashboard → Authentication → Providers:

1. Google:

   • Create OAuth credentials in Google Cloud Console
   • Add Client ID and Secret

2. Apple:

   • Configure in Apple Developer Portal
   • Add Services ID and Key

Auth Settings

Site URL: https://app.artbase.studio
Redirect URLs:
  - https://app.artbase.studio/auth/callback
  - https://*.artbase.studio/auth/callback

---

Storage Configuration

Buckets

Create buckets in Dashboard → Storage:

Bucket	Purpose	Policy
product-images	Product photos	Public read
avatars	User avatars	Authenticated
digital-products	Downloadable files	Private

Storage Policies

-- Public read for product images
CREATE POLICY "Public read product images"
ON storage.objects FOR SELECT
USING (bucket_id = 'product-images');

-- Authenticated upload to own org folder
CREATE POLICY "Members upload product images"
ON storage.objects FOR INSERT
WITH CHECK (
  bucket_id = 'product-images'
  AND (storage.foldername(name))[1] IN (
    SELECT id::text FROM organizations
    WHERE id IN (
      SELECT org_id FROM organization_members
      WHERE user_id = auth.uid()
    )
  )
);

---

Edge Functions

Deployment

# Deploy single function
supabase functions deploy function-name

# Deploy all functions
supabase functions deploy

Secrets

Store secrets in Supabase Vault:

# Set secret
supabase secrets set STRIPE_SECRET_KEY=sk_live_...

# List secrets
supabase secrets list

---

Backups

Automatic Backups

Supabase Pro plans include:

• Daily backups (7-day retention)
• Point-in-time recovery (Pro)

Manual Backup

# Export database
pg_dump -h db.xxx.supabase.co -U postgres -d postgres > backup.sql

Restore

# Restore from backup
psql -h db.xxx.supabase.co -U postgres -d postgres < backup.sql

---

Performance

Connection Pooling

Enable PgBouncer for connection pooling:

• Dashboard → Settings → Database → Connection Pooling
• Use pooler URL for high-traffic applications

Indexes

Ensure indexes exist for common queries:

-- Check missing indexes
SELECT
  schemaname,
  tablename,
  indexname,
  indexdef
FROM pg_indexes
WHERE schemaname = 'public'
ORDER BY tablename;

Query Performance

Monitor slow queries:

-- Enable query stats
CREATE EXTENSION IF NOT EXISTS pg_stat_statements;

-- Find slow queries
SELECT
  query,
  calls,
  mean_time,
  total_time
FROM pg_stat_statements
ORDER BY mean_time DESC
LIMIT 10;

---

Monitoring

Supabase Dashboard

Monitor in Dashboard → Database:

• Active connections
• Database size
• Query performance
• Replication lag

Alerts

Set up alerts for:

• High connection count
• Database size threshold
• Slow query detection

---

Troubleshooting

Connection Issues

# Test connection
psql "postgresql://postgres:password@db.xxx.supabase.co:5432/postgres"

Common issues:

• IP not allowed (check network restrictions)
• Wrong credentials
• SSL mode mismatch

Migration Failures

# Check migration status
supabase migration list

# View migration diff
supabase db diff

RLS Blocking Access

Debug RLS policies:

-- Check policies on table
SELECT * FROM pg_policies WHERE tablename = 'products';

-- Test as authenticated user
SET request.jwt.claim.sub = 'user-uuid';
SELECT * FROM products;