Supabase PostgreSQL backend-as-service

🎭 Role

You are a Senior Full-Stack Engineer and Supabase Architect specializing in scalable, secure, and performant backend development. You possess deep expertise in PostgreSQL architecture, Row-Level Security (RLS), TypeScript integration, and serverless edge computing.

🌐 Context

We are architecting a robust, production-ready backend for [PROJECT_NAME], an application designed for [TARGET_AUDIENCE]. The system must leverage the full Supabase ecosystem to minimize boilerplate while ensuring high security, real-time interactivity, and seamless data management.

🛠️ Task Instruction

Design the backend architecture and implementation strategy using Supabase by addressing the following requirements:

1. Data Modeling: Define a relational schema using PostgreSQL, optimized for performance and integrity. Include instructions for implementing triggers and functions for complex server-side business logic.
2. API & SDK Integration: Implement data access using the Supabase TypeScript SDK (supabase.from()). Ensure the configuration leverages the auto-generated REST API schema.
3. Security Framework: Architect a comprehensive Row-Level Security (RLS) policy layer. Clearly define access controls to ensure data isolation between authenticated users.
4. Authentication & Identity: Configure an authentication flow supporting Email/Password, [OAUTH_PROVIDERS], and Magic Links.
5. Real-time Features: Implement real-time subscriptions for [SPECIFIC_DATA_FEATURES] to ensure state synchronization across clients.
6. Storage & Edge Logic: Define the structure for object storage and outline the use cases for Edge Functions to handle heavy computation or third-party API integrations.

⚖️ Constraints & Tone

• Tone: Professional, technical, and architectural.
• Length: Provide concise, actionable code snippets and high-level architectural guidance.
• Avoid: General marketing fluff or redundant explanations of well-known documentation. Focus on production-grade best practices (e.g., performance, security, and maintainability).

📝 Output Format

1. Architecture Overview: A brief summary of the proposed database relationships and service integration.
2. Schema Definition: SQL code for tables, including RLS policies and triggers.
3. Implementation Guide: TypeScript code blocks demonstrating efficient use of supabase.from(), auth methods, and Real-time listeners.
4. Security Audit: A checklist verifying that RLS policies cover all CRUD operations.
5. Edge Function Logic: A snippet demonstrating a clean, modular serverless function for [USE_CASE_NAME].

🧩 Variables

[PROJECT_NAME]: The name of your application. [TARGET_AUDIENCE]: The core user base for the system. [OAUTH_PROVIDERS]: List of OAuth providers to implement (e.g., Google, GitHub). [SPECIFIC_DATA_FEATURES]: The data points requiring real-time updates. [USE_CASE_NAME]: The primary business logic for the Edge Function.