Database Package (Supabase)¶

Overview¶

The packages/database/supabase/ directory contains the Supabase configuration and database setup for the Delta platform. This includes database migrations, edge functions, and configuration files for the Supabase backend-as-a-service.

Purpose¶

Database schema management through migrations
Edge functions for server-side logic
Authentication and authorization setup
Real-time subscriptions and triggers
Database seeding and initial data

Technology Stack¶

Database: PostgreSQL (via Supabase)
Authentication: Supabase Auth
Edge Functions: Deno runtime
Migrations: SQL-based schema management
Real-time: Supabase Realtime

Directory Structure¶

packages/database/supabase/
├── config.toml              # Supabase project configuration
├── import_map.json          # Deno import map for edge functions
├── seed.sql                 # Database seeding script
├── .env.local.example       # Environment variables template
├── docs/                    # Database-specific documentation
├── functions/               # Supabase Edge Functions
└── migrations/              # Database migration files

Key Components¶

Configuration¶

File: config.toml
Purpose: Supabase project configuration
Contains:
Database settings
Authentication configuration
API settings
Edge function configuration

Database Migrations¶

Location: migrations/
Purpose: Version-controlled database schema changes
Format: SQL files with timestamp prefixes
Example: 20250520062148_create_items_to_update_table.sql

Edge Functions¶

Location: functions/
Runtime: Deno
Purpose: Server-side logic and API endpoints
Features:
TypeScript support
Direct database access
Authentication integration

Database Seeding¶

File: seed.sql
Purpose: Initial data population
Usage: Development and testing environments

Development¶

Local Development Setup¶

# Navigate to the database directory
cd packages/database/supabase

# Install Supabase CLI (if not already installed)
yarn install -g supabase

# Start local Supabase instance
supabase start

# Apply migrations
supabase db reset

# Seed the database
supabase db seed

Environment Setup¶

Copy .env.local.example to .env.local and configure:

Supabase project URL
Service role key
Anonymous key
Database connection strings

Database Management¶

Creating Migrations¶

# Create a new migration
supabase migration new migration_name

# Apply migrations to local database
supabase db reset

# Apply migrations to remote database
supabase db push

Migration Best Practices¶

Descriptive Names: Use clear, descriptive migration names
Incremental Changes: Make small, focused changes
Rollback Safety: Ensure migrations can be safely rolled back
Data Preservation: Handle existing data carefully
Testing: Test migrations on development data first

Example Migration Structure¶

-- Create table
CREATE TABLE items_to_update (
    id UUID DEFAULT gen_random_uuid() PRIMARY KEY,
    item_data JSONB NOT NULL,
    status TEXT DEFAULT 'pending',
    created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
    updated_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
);

-- Add indexes
CREATE INDEX idx_items_to_update_status ON items_to_update(status);
CREATE INDEX idx_items_to_update_created_at ON items_to_update(created_at);

-- Add RLS policies
ALTER TABLE items_to_update ENABLE ROW LEVEL SECURITY;

-- Create policies for authenticated users
-- Note: Always use (SELECT auth.uid()) to prevent per-row re-evaluation
CREATE POLICY "Users can view their own items" ON items_to_update
    FOR SELECT
    TO authenticated
    USING ((SELECT auth.uid()) IS NOT NULL);

Edge Functions¶

Development¶

# Create a new edge function
supabase functions new function_name

# Serve functions locally
supabase functions serve

# Deploy functions
supabase functions deploy function_name

Function Structure¶

import { serve } from 'https://deno.land/std@0.168.0/http/server.ts';
import { createClient } from 'https://esm.sh/@supabase/supabase-js@2';

serve(async (req) => {
  try {
    // Initialize Supabase client
    const database = createClient(
      Deno.env.get('SUPABASE_URL') ?? '',
      Deno.env.get('SUPABASE_PUBLISHABLE_KEY') ?? ''
    );

    // Function logic here
    const { data, error } = await supabase.from('table_name').select('*');

    if (error) throw error;

    return new Response(JSON.stringify({ data }), {
      headers: { 'Content-Type': 'application/json' },
    });
  } catch (error) {
    return new Response(JSON.stringify({ error: error.message }), {
      status: 400,
      headers: { 'Content-Type': 'application/json' },
    });
  }
});

Authentication & Authorization¶

Row Level Security (RLS)¶

Supabase uses PostgreSQL's RLS for fine-grained access control:

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

-- Create policies
CREATE POLICY "policy_name" ON table_name
    FOR operation USING (condition);

Authentication Integration¶

JWT-based authentication
Social login providers
Email/password authentication
Magic link authentication
Phone authentication

Real-time Features¶

Subscriptions¶

// Subscribe to table changes
const subscription = supabase
  .channel('table_changes')
  .on(
    'postgres_changes',
    { event: '*', schema: 'public', table: 'table_name' },
    (payload) => {
      console.log('Change received!', payload);
    }
  )
  .subscribe();

Broadcast¶

// Send real-time messages
const channel = supabase.channel('room1');
channel.send({
  type: 'broadcast',
  event: 'message',
  payload: { message: 'Hello World!' },
});

Deployment¶

Production Deployment¶

Create Supabase Project: Set up project on Supabase dashboard
Configure Environment: Set production environment variables
Run Migrations: Apply database schema to production
Deploy Functions: Deploy edge functions to production
Configure Policies: Set up RLS policies and permissions

Environment Variables¶

SUPABASE_URL: Project URL
SUPABASE_PUBLISHABLE_KEY: Publishable/public key
SUPABASE_SECRET_KEY: Secret key (server-side only)
DATABASE_URL: Direct database connection string

Monitoring and Maintenance¶

Database Monitoring¶

Query performance analysis
Connection pool monitoring
Storage usage tracking
Real-time connection monitoring

Backup and Recovery¶

Automatic daily backups
Point-in-time recovery
Manual backup creation
Disaster recovery procedures

Security Best Practices¶

RLS Policies: Always enable and configure RLS
API Keys: Secure service role keys
CORS: Configure appropriate CORS settings
Rate Limiting: Implement rate limiting for APIs
Input Validation: Validate all inputs in edge functions

Integration Points¶

App: Direct client connection via Supabase client
Edge Functions: Server-side logic and API endpoints
Authentication: User management and session handling
Real-time: Live updates and notifications
Storage: File uploads and management

Documentation¶

Additional database-specific documentation is available in:

docs/creating_supabase_migrations.md - Migration creation guide
Supabase official documentation
PostgreSQL documentation for advanced database features