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.