backend_safeguard

• 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.

• 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.

• 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).

• 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.

• [ ] 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?