Migrate to supabase

[mzayem]

📝 Supabase Migration Plan

📌 1. Database Migration

• Schema Conversion: Convert existing SQLite schema to PostgreSQL-compatible schema.
• Migrations Setup:
  • Install and configure Supabase CLI.
  • Create a structured migration directory.
  • Split schema.sql into:
    • Table definitions
    • Security policies
    • Seed data
• Set Up Row-Level Security (RLS) Policies to enforce data access rules.
• Data Migration: Export SQLite data and import it into Supabase PostgreSQL.
• Update Application Connection Strings to use Supabase PostgreSQL.

🚀 2. GitHub Integration & Deployment

• Set Up Supabase Project and connect it to the GitHub repository.
• Configure CI/CD Pipelines for automated deployments and migrations.
• Migrate Existing Data into the new database.

🔐 3. Authentication Migration

Current Token Flow

1. Magic provides a DID token.
2. Backend validates DID and issues a JWT.
3. JWT is stored in an HTTP-only cookie.
4. API validates JWT on each request.

Supabase Authentication Options

• Option 1: Cookie-based authentication (Similar to current implementation).
• Option 2: localStorage-based authentication (Supabase default).

Token Management Comparison

Feature	Current Implementation	Supabase Implementation
Token Type	Single JWT token	Access + Refresh token pair
Token Refresh	Manual refresh needed	Automatic refresh
Claims Handling	Custom claims management	Built-in claims support
Storage	Cookie-only	Flexible (cookie/localStorage)

• Implement Supabase Authentication with a choice of cookie-based or localStorage-based tokens.
• Ensure JWT validation on API requests.
• Session Management: Enable built-in session tracking and automatic session recovery.
• Set up Security Features: Automatic session invalidation and token revocation if needed.

---

References

• GitHub Repo may help to add auth in project

---