Create PostGIS bounding-box RPC function

epic-geographic-peer-mentor-map-data-infrastructure-task-004 — Write a Supabase RPC function (find_mentors_in_bounds) that accepts min_lat, min_lng, max_lat, max_lng, and organisation_id parameters, performs a PostGIS ST_MakeEnvelope bounding-box spatial query on the mentor_locations table, and returns mentor_id, latitude, longitude, display_name, and consent_level fields. Include organisation_id filter to enforce RLS at the query level as a defence-in-depth measure.

critical priority high complexity database pending database specialist Tier 3

Acceptance Criteria

Function find_mentors_in_bounds(min_lat FLOAT, min_lng FLOAT, max_lat FLOAT, max_lng FLOAT, p_organisation_id UUID) exists in the public schema and is callable via Supabase RPC

Function returns rows with fields: mentor_id (UUID), latitude (FLOAT), longitude (FLOAT), display_name (TEXT), consent_level (TEXT)

Bounding box query uses ST_MakeEnvelope(min_lng, min_lat, max_lng, max_lat, 4326) and ST_Within or ST_Intersects against the stored location geometry column

organisation_id parameter is applied as a WHERE clause filter in addition to any RLS policies (defence-in-depth)

Only mentors with active, non-expired location consent are returned — rows where consent_expires_at < NOW() are excluded

Function returns empty result set (not error) when no mentors exist in bounds

Function is callable by authenticated users with role 'coordinator' or 'peer_mentor' within the same organisation

Execution plan uses spatial index (GiST) on the location geometry column — confirmed via EXPLAIN ANALYZE

Query executes in under 200ms for up to 5,000 mentor locations in the bounding box

Function is SECURITY DEFINER with explicit search_path set to prevent schema injection

Invalid bounding box inputs (e.g. min_lat > max_lat) return a descriptive error, not a silent empty set

Migration file is idempotent (CREATE OR REPLACE FUNCTION)

Technical Requirements

frameworks

Supabase PostgreSQL 15

PostGIS Spatial Extension for PostgreSQL

apis

Supabase RPC (rpc() client method)

PostGIS ST_MakeEnvelope

PostGIS ST_Within

data models

assignment

contact

performance requirements

GiST spatial index must exist on mentor_locations.location geometry column before deploying this function

Function must complete under 200ms at p50 for datasets up to 5,000 rows in bounds

Use ST_MakeEnvelope with SRID 4326 — avoid ST_GeomFromText string parsing for performance

Avoid SELECT * — project only required columns to minimise row transfer overhead

security requirements

Function must be SECURITY DEFINER with explicit SET search_path = public, postgis

organisation_id parameter filters results even if RLS is bypassed by SECURITY DEFINER — dual-layer enforcement

Do not return personnummer, phone number, or email in function output — display_name and consent_level only

GRANT EXECUTE only to authenticated role, not anon

Location precision reduced to municipality level if organisation privacy config has location_precision = 'approximate'

Execution Context

Execution Tier

Tier 3

Tier 3 - 413 tasks

Can start after Tier 2 completes

View Full Execution Plan

Implementation Notes

Create the migration file as supabase/migrations/_create_find_mentors_in_bounds.sql. Use ST_MakeEnvelope(min_lng, min_lat, max_lng, max_lat, 4326) — note longitude is X axis (first argument), latitude is Y axis (second argument); swapping these is a common and silent bug. Store location as geography(Point, 4326) or geometry(Point, 4326) consistently across the schema — mixing types causes implicit cast overhead. The consent_expires_at filter (WHERE consent_expires_at > NOW()) must be part of the function body, not just an RLS policy, because SECURITY DEFINER bypasses caller RLS.

For approximate location support: JOIN organisation_location_config and apply ST_SnapToGrid(location, 0.1) to reduce precision to ~10km grid when precision = 'approximate'. Run EXPLAIN (ANALYZE, BUFFERS) after seeding test data to confirm the GiST index is used.

Testing Requirements

Unit test the SQL function directly via psql or supabase db test with pgTAP: (1) assert returns correct rows within bounds, (2) assert excludes rows outside bounds, (3) assert excludes expired consent rows, (4) assert organisation_id filter isolates multi-tenant data, (5) assert empty result for empty bounds, (6) assert error on inverted bounding box. Integration test via Supabase client RPC call from a Dart test: verify typed response deserialization. Performance test with seed data of 10,000 mentor rows — confirm EXPLAIN ANALYZE shows index scan, not seq scan.

Component

PostGIS Spatial Query Adapter

infrastructure medium

Dependencies (1)

Implement row-level security policies on the mentor_locations table enforcing organisation-scoped access. Coordinators may only SELECT rows where organisation_id matches their JWT claim. Peer mentors may only INSERT and UPDATE their own row. Verify policies block cross-organisation reads via Supabase policy tests. epic-geographic-peer-mentor-map-data-infrastructure-task-003

Epic Risks (3)

high impact medium prob integration

Supabase's hosted PostGIS extension behaviour may differ from the local emulator for spatial RPC functions, causing bounding-box queries to return incorrect results or fail in production while passing locally.

Mitigation & Contingency

Mitigation: Write integration tests against the Supabase emulator from the start and run the same test suite against a staging Supabase project before merging. Use ST_DWithin and ST_MakeEnvelope in plain SQL first, validate with psql, then wrap as RPC.

Contingency: If PostGIS RPC proves unreliable, fall back to client-side bounding box filtering on a full fetch of consented mentor locations (acceptable for up to ~200 mentors per chapter) until the spatial query is stabilised.

medium impact low prob dependency

OpenStreetMap tile usage may require attribution handling and rate limiting. Switching to Google Maps Flutter plugin mid-implementation would require significant rework of the map-provider-integration abstraction.

Mitigation & Contingency

Mitigation: Define the map-provider-integration abstraction interface before selecting the SDK so that the concrete implementation is swappable. Implement OSM first with correct attribution. Document Google Maps as the alternate with its API key setup steps.

Contingency: If OSM tiles are rejected by stakeholders or tile server limits are hit, activate the Google Maps Flutter plugin implementation behind the same interface without touching any UI or service code.

high impact low prob security

Incorrect RLS configuration could allow a coordinator to query mentor locations from a different organisation, constituting a GDPR data breach.

Mitigation & Contingency

Mitigation: Write dedicated RLS integration tests with two isolated test organisations and assert that cross-organisation queries return zero rows. Include these tests in CI. Have a second developer review all RLS policy SQL before migration is applied.

Contingency: If a cross-organisation data leak is discovered post-deployment, immediately disable the map feature via the organisation feature flag, revoke the affected Supabase RLS policy, and notify the data protection officer per the organisation's GDPR incident response procedure.

Quick Links

All Tasks Execution Plan