Skip to content

SUPABASE_SETUP.md

Latest commit

 

History

History
281 lines (207 loc) · 6.59 KB

SUPABASE_SETUP.md

File metadata and controls

281 lines (207 loc) · 6.59 KB

🚀 Supabase Setup Guide for PTDT Settlement API

Repository note, April 21, 2026: this is the original peether-protocol workspace. Current public release work has moved to:

Use the split repositories for current setup work. The content below is retained as legacy/reference documentation unless updated by a later commit.

Recommended Database Solution for Quick Start

Why Supabase?

✅ Fastest Setup - 2 minutes to production database ✅ Free Tier Available - Start at $0, scale as you grow ✅ PostgreSQL Native - Full compatibility with our API ✅ Built-in Auth - JWT tokens included ✅ Real-time Subscriptions - For live dashboards ✅ Automatic Backups - Daily snapshots ✅ Global CDN - Low-latency everywhere

Quick Start (2 minutes)

Step 1: Create Supabase Project

  1. Go to supabase.com
  2. Click "New Project"
  3. Fill in:
    • Project Name: ptdt-settlement
    • Database Password: Generate strong password
    • Region: Pick closest to you
  4. Click "Create new project"

⏱️ Wait 2-3 minutes for project creation

Step 2: Get Connection Details

  1. Go to Settings → Database
  2. Find the Connection String section
  3. Copy the Full Connection String (PostgreSQL)
  4. Replace [YOUR-PASSWORD] with your password

Example:

postgresql://postgres:YOUR_PASSWORD@db.your-project.supabase.co:5432/postgres

Step 3: Update .env

# Copy your Supabase connection string
DATABASE_URL=postgresql://postgres:YOUR_PASSWORD@db.your-project.supabase.co:5432/postgres

Step 4: Start API

npm install
npm run dev

Done! Your API is connected to Supabase. ✅

Supabase Keys (Optional for Advanced Features)

If you want to use Supabase's real-time features or auth:

  1. Go to Settings → API
  2. Copy these values:
SUPABASE_URL=https://your-project.supabase.co
SUPABASE_ANON_KEY=your-anon-key
SUPABASE_SERVICE_ROLE_KEY=your-service-role-key

Database Schema Setup

Option A: Manual Setup (Recommended for Learning)

  1. Go to Supabase → SQL Editor
  2. Create a new query
  3. Copy and paste this schema:
-- Create quotes table
CREATE TABLE quotes (
  id TEXT PRIMARY KEY,
  ride_fare DECIMAL NOT NULL,
  currency VARCHAR(3) NOT NULL,
  ptdt_price DECIMAL NOT NULL,
  ptdt_amount DECIMAL NOT NULL,
  discount DECIMAL DEFAULT 0,
  operator_id TEXT NOT NULL,
  status VARCHAR(20) DEFAULT 'active',
  expires_at TIMESTAMP NOT NULL,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- Create payments table
CREATE TABLE payments (
  id TEXT PRIMARY KEY,
  quote_id TEXT NOT NULL,
  rider_wallet TEXT NOT NULL,
  driver_wallet TEXT NOT NULL,
  amount_ptdt DECIMAL NOT NULL,
  tx_hash TEXT,
  status VARCHAR(20) NOT NULL,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (quote_id) REFERENCES quotes(id)
);

-- Create withdrawals table
CREATE TABLE withdrawals (
  id TEXT PRIMARY KEY,
  driver_wallet TEXT NOT NULL,
  amount_ptdt DECIMAL NOT NULL,
  method VARCHAR(10) NOT NULL,
  status VARCHAR(20) NOT NULL,
  tx_hash TEXT,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- Create driver_earnings table
CREATE TABLE driver_earnings (
  driver_wallet TEXT PRIMARY KEY,
  total_earnings DECIMAL DEFAULT 0,
  ptdt_balance DECIMAL DEFAULT 0,
  rides_completed INT DEFAULT 0,
  last_updated TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- Create indexes for performance
CREATE INDEX idx_quotes_operator ON quotes(operator_id);
CREATE INDEX idx_quotes_status ON quotes(status);
CREATE INDEX idx_payments_status ON payments(status);
CREATE INDEX idx_withdrawals_driver ON withdrawals(driver_wallet);
CREATE INDEX idx_earnings_wallet ON driver_earnings(driver_wallet);
  1. Click "Run"

✅ Your database schema is now ready!

Option B: Automated with Prisma (Advanced)

If you're using Prisma ORM:

npm install @prisma/client
prisma init
prisma db push

Pricing Breakdown

Tier Cost Connections Storage Use Case
Free $0 3 500 MB Development
Pro $25/mo 100 8 GB Production (small)
Team $599/mo Unlimited 200+ GB Enterprise

Start free, upgrade only when needed.

Common Setup Issues

Issue 1: Connection Timeout

Problem: connect ECONNREFUSED

Solution:

  1. Check DATABASE_URL is correct
  2. Verify password doesn't have special chars (or URL encode them)
  3. Ensure project is not paused

Issue 2: Authentication Errors

Problem: FATAL: password authentication failed

Solution:

  1. Reset database password in Supabase settings
  2. Update DATABASE_URL with new password
  3. Test connection again

Issue 3: Permissions Denied

Problem: permission denied for schema public

Solution:

GRANT ALL ON SCHEMA public TO postgres;
GRANT ALL ON ALL TABLES IN SCHEMA public TO postgres;
GRANT ALL ON ALL SEQUENCES IN SCHEMA public TO postgres;

Best Practices

✅ Do:

  • Use strong passwords (20+ chars)
  • Enable Row Level Security (RLS) for production
  • Backup regularly (Supabase does this automatically)
  • Use connection pooling for high traffic
  • Monitor database usage in Supabase dashboard

❌ Don't:

  • Commit DATABASE_URL to git
  • Use the same password across projects
  • Give everyone full admin access
  • Run production on free tier

Monitoring & Logs

View Database Logs

  1. Go to Logs → Postgres Logs
  2. Filter by:
    • Error - Find problems
    • Slow Queries - Optimize performance
    • Connections - Monitor usage

Check Database Stats

  1. Go to Settings → Database
  2. See:
    • Storage used
    • Connections count
    • Query performance

Scaling Considerations

When you hit 1,000 daily active users:

  • Upgrade to Pro tier
  • Enable connection pooling
  • Create indexes for common queries
  • Monitor slow queries

When you hit 10,000 daily active users:

  • Use read replicas
  • Consider read-only instances
  • Implement caching layer (Redis)
  • Optimize database queries

Next Steps

  1. ✅ Create Supabase project
  2. ✅ Get connection string
  3. ✅ Update .env file
  4. ✅ Create database schema
  5. ✅ Start API with npm run dev
  6. ✅ Test endpoints

Support

You now have a production-grade PostgreSQL database in 2 minutes. 🚀