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

Design PostGIS spatial schema for mentor locations

epic-geographic-peer-mentor-map-data-infrastructure-task-001 — 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.

critical priority medium complexity database pending database specialist Tier 0

Acceptance Criteria

A written schema design document (or inline migration comments) specifies every column in `mentor_locations` with name, type, nullable/not-null, default value, and rationale
The location column is typed `GEOGRAPHY(POINT, 4326)` (WGS 84) — not `GEOMETRY` — to enable accurate great-circle distance calculations
A `GIST` spatial index is specified on the location column with the index definition written out
An `organisation_id UUID NOT NULL REFERENCES organisations(id)` foreign key column is included with cascade delete behaviour documented
A `mentor_id UUID NOT NULL REFERENCES users(id)` foreign key is included with a UNIQUE constraint ensuring one row per mentor
`privacy_level TEXT NOT NULL CHECK (privacy_level IN ('public', 'organisation_only', 'hidden'))` column is defined with default `'organisation_only'`
`consent_expires_at TIMESTAMPTZ` nullable column is defined with a comment explaining how NULL is interpreted (no expiry set)
`created_at TIMESTAMPTZ NOT NULL DEFAULT now()` and `updated_at TIMESTAMPTZ NOT NULL DEFAULT now()` columns are included
Schema document explicitly states which columns RLS policies will filter on (organisation_id, mentor_id)
Design is reviewed and signed off before migration authoring begins (task-002 dependency met)

Technical Requirements

frameworks
Supabase
apis
Supabase PostgREST
PostGIS ST_DWithin
PostGIS ST_MakeEnvelope
data models
mentor_locations
organisations
users
performance requirements
GIST index on location column must support bounding box queries via `ST_MakeEnvelope` without sequential scan on tables > 10,000 rows
Composite index on `(organisation_id, consent_expires_at)` recommended for consent-filtered queries
Schema should support queries returning results within 200 ms for up to 50,000 mentor rows
security requirements
No PII (name, phone, address) stored in mentor_locations — only spatial coordinates and consent metadata
consent_expires_at must be checked at query time; expired consent must not surface coordinates
privacy_level = 'hidden' must fully suppress the row from all non-admin queries

Execution Context

Execution Tier
Tier 0

Tier 0 - 440 tasks

Implementation Notes

Use `GEOGRAPHY(POINT, 4326)` rather than `GEOMETRY(POINT, 4326)` because Supabase's PostgREST spatial functions (`ST_DWithin`, `ST_Distance`) operate correctly on GEOGRAPHY type and return distances in metres without requiring an SRID-aware projection. The GIST index on a GEOGRAPHY column uses `geography_ops` operator class by default — confirm this is specified in the index definition. Consider a partial index `WHERE privacy_level != 'hidden'` to reduce index size for common queries. Document the `updated_at` trigger requirement (a `moddatetime` trigger or equivalent must fire on row update).

Note that Supabase Free tier includes PostGIS; confirm the specific extension version available. This design document will directly drive the migration in task-002, so completeness is critical.

Testing Requirements

Schema design review checklist: (1) verify PostGIS extension availability in the target Supabase project tier; (2) validate GEOGRAPHY vs GEOMETRY choice against Supabase PostgREST spatial query support; (3) confirm foreign key targets (`organisations`, `users`) exist in the current migration history; (4) dry-run the CREATE TABLE statement in a local Supabase instance and confirm all constraints apply without error. No automated tests for this task — deliverable is the schema document and confirmed local dry-run.

Component
Mentor Location Repository
data high
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