Backend Safeguard Protocol (Supabase + Vercel API)

1. Database Schema & Migration Safety

• Migrations:
  • NEVER edit a previous migration. Always create a new one.
  • Migration files must be numbered/timestamped sequentially.
  • Destructive changes (DROP COLUMN) require explicit user confirmation.
• Supabase Specifics:
  • Use pg_jsonschema (if available) or CHECK constraints for complex JSON data.
  • Indexes: Ensure Foreign Keys have indices if used in JOINs frequentyl.

2. RLS (Row Level Security) "Ironclad" Rules

• Enablement: ALTER TABLE "table_name" ENABLE ROW LEVEL SECURITY; is MANDATORY.
• Policies:
  • Must have separate policies for SELECT, INSERT, UPDATE, DELETE (unless absolutely identical).
  • auth.uid() MUST be checked for user-specific data.
  • service_role usage in client is FORBIDDEN.

3. API Design & Security

• Input Validation (Zod):
  • ALL API routes must parse body/query with Zod.
  • strict() mode recommended to strip unknown fields.
• Error Handling:
  • Return standardized error structure: { error: string, code: string, details?: any }.
  • NEVER leak Stack Traces to production response.
  • Use 4xx for client errors, 5xx for server errors.
• Rate Limiting:
  • Ensure sensitive endpoints (auth, email) have rate limiting (Upstash/KV).

4. Code Structure (Vercel Functions)

• Separation of Concerns:
  • api/xxx.ts -> Controller (Parse Req, Check Auth)
  • src/services/xxx.ts -> Business Logic
  • src/data/xxx.ts -> Database Logic (Supabase calls)
• Secrets:
  • Check for process.env.XXX. NEVER hardcode strings.

5. Audit Checklist

• Is RLS enabled on all touched tables?
• Is Zod validation wrapping the request?
• Is logging present for state changes?
• Are we leaking sensitive user data in the response?