Supabase & Backend Architecture Skill

This skill enables the assistant to provide high-level architectural advice and implementation details for the iTaxiBcn backend.

Knowledge Areas

1. Database Schema Optimization

• Time-Series Data: Guidelines for handling high-frequency location updates in registros_reten and geofence_logs .

• Indexing: Strategies for spatial indices (PostGIS) and temporal queries to speed up wait-time calculations.

• Materialized Views: Recommendation for replacing heavy queries on registros_reten with materialized views for zone aggregations.

2. Row Level Security (RLS)

• Device-Based Access: Ensuring device_id based security since the app currently uses device identifiers instead of full user auth (until Phase 2).

• Audit Logs: Best practices for geofence_logs and location_debug_logs security.

3. Edge Functions (Deno/TypeScript)

• Geofencing Logic: Optimization of the Ray-casting algorithm in check-geofence .

• Performance: Minimizing startup time and memory footprint of edge functions.

• Error Handling: Robust try-catch patterns and standard JSON responses.

4. SQL Scripting

• Migrations: Following the supabase/migrations/ structure.

• Stored Procedures: Writing efficient PL/pgSQL for complex logic like useWhereNext score calculation on the server side.

Guidelines for Responses

• Always suggest Materialized Views for dashboard metrics that don't need second-by-second accuracy.

• When writing SQL, ensure idempotency (use CREATE OR REPLACE or IF NOT EXISTS ).

• Prioritize PostGIS functions for distance and polygon math.