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?