🏠 Overview › ✅ Implementation Tasks ›✅ epic-geographic-peer-mentor-map-data-infrastructure-task-002

Create Supabase migration for mentor_locations table

epic-geographic-peer-mentor-map-data-infrastructure-task-002 — Write and apply a Supabase migration that enables the PostGIS extension, creates the mentor_locations table with all required columns including GEOGRAPHY point column and spatial GIST index, and sets up the organisation_id-scoped row-level security policies to ensure coordinators can only read mentors within their organisation.

critical priority high complexity database pending database specialist Tier 1

Acceptance Criteria

Migration file is named with a Supabase timestamp prefix (e.g., `20260329000001_create_mentor_locations.sql`) and placed in `supabase/migrations/`
`CREATE EXTENSION IF NOT EXISTS postgis;` is the first statement in the migration
`mentor_locations` table is created with all columns defined in task-001 schema design, including the `GEOGRAPHY(POINT, 4326)` column
A `GIST` spatial index named `mentor_locations_location_idx` is created on the `location` column
A composite index on `(organisation_id, consent_expires_at)` named `mentor_locations_org_consent_idx` is created
A `UNIQUE` constraint on `mentor_id` is present, ensuring one location row per mentor
RLS is enabled on the table (`ALTER TABLE mentor_locations ENABLE ROW LEVEL SECURITY`)
A coordinator SELECT policy is created that restricts reads to rows where `organisation_id = (SELECT organisation_id FROM user_profiles WHERE id = auth.uid())`
A peer mentor INSERT/UPDATE policy is created restricting writes to the mentor's own row (`mentor_id = auth.uid()`)
A `moddatetime` or equivalent trigger is set up to auto-update `updated_at` on row modification
Migration applies cleanly on a fresh local Supabase instance (`supabase db reset` completes without errors)
A rollback migration (`down` file or commented rollback SQL) is included

Technical Requirements

frameworks
Supabase
apis
Supabase CLI
PostgREST
PostGIS
data models
mentor_locations
organisations
user_profiles
users
performance requirements
GIST index must be created with `WITH (fillfactor = 90)` to reduce index bloat on frequent updates
Migration must complete in under 30 seconds on a standard Supabase project
security requirements
RLS must be enabled before any policies are created — never leave RLS enabled with zero policies (open table)
All JWT claim references must use `auth.uid()` — never trust client-supplied user IDs
The coordinator policy must join to `user_profiles` to resolve organisation membership — do not use a hard-coded organisation_id
Service-role bypass is acceptable for admin migrations but must be documented

Execution Context

Execution Tier
Tier 1

Tier 1 - 540 tasks

Can start after Tier 0 completes

Implementation Notes

Use the Supabase CLI (`supabase migration new`) to generate the migration file with correct timestamp. The `moddatetime` extension (`CREATE EXTENSION IF NOT EXISTS moddatetime;`) must be enabled before creating the trigger. Example trigger: `CREATE TRIGGER handle_updated_at BEFORE UPDATE ON mentor_locations FOR EACH ROW EXECUTE PROCEDURE moddatetime(updated_at);`. For the coordinator RLS policy, use a subquery on `user_profiles` rather than a function to keep the policy simple and inspectable.

Be aware that `auth.uid()` returns the UUID of the currently authenticated user; if Supabase is configured with email/password auth, this is the `auth.users.id`. Test both the anonymous role (should see zero rows) and the authenticated role with correct organisation membership. Consider adding a `CHECK (ST_IsValid(location::geometry))` constraint to prevent malformed geometry from being inserted.

Testing Requirements

Local integration test: run `supabase db reset` and verify migration applies without errors. Manual policy test: create two test organisations, two test mentors (one per org), and one test coordinator per org. Assert coordinator A cannot SELECT mentor B's location row. Assert mentor A cannot UPDATE mentor B's row.

Assert mentor A can INSERT/UPDATE their own row. Document all test steps and expected outcomes in the migration PR description. These tests are manual at this stage; automated Supabase policy tests are introduced in task-003.

Component
Mentor Location Repository
data high
Dependencies (1)
Design and document the PostGIS-enabled database schema for storing mentor location data, including geometry columns, spatial indexes, organisation_id foreign keys, and consent-related columns. Define the mentor_locations table with GEOGRAPHY(POINT, 4326) column, timestamps, privacy_level, and consent_expires_at fields. epic-geographic-peer-mentor-map-data-infrastructure-task-001
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