Add organisation privacy settings to Supabase migration
epic-geographic-peer-mentor-map-data-infrastructure-task-006 — Create a Supabase migration adding location_privacy_settings JSONB column to the organisations table (or a dedicated organisation_location_config table) storing consent_expiry_days, retention_days, privacy_policy_url, and location_precision fields. Seed default values for all existing organisations and add RLS so only org admins can update their own config.
Acceptance Criteria
Technical Requirements
Execution Context
Tier 3 - 413 tasks
Can start after Tier 2 completes
Implementation Notes
Prefer adding a JSONB column to the existing organisations table over creating a separate table — the config is a 1:1 relationship and a separate table adds join complexity for minimal benefit at this scale. Use ALTER TABLE organisations ADD COLUMN IF NOT EXISTS location_privacy_settings JSONB NOT NULL DEFAULT '{...}'::jsonb. The DEFAULT clause handles future INSERT statements; the explicit UPDATE handles existing rows. For the RLS UPDATE policy, use: CREATE POLICY update_own_org_privacy ON organisations FOR UPDATE USING (id = (SELECT organisation_id FROM user_profiles WHERE id = auth.uid()) AND EXISTS (SELECT 1 FROM user_roles WHERE user_id = auth.uid() AND role = 'org_admin')).
Ensure the migration is tested in the local Supabase stack before merging.
Testing Requirements
Run supabase db reset locally and confirm the migration applies cleanly. Test RLS policies manually via psql as a peer_mentor role (should be able to SELECT, must not be able to UPDATE). Test as org_admin role (should be able to UPDATE own org, must not be able to UPDATE other org). Verify the CHECK constraint rejects invalid location_precision values ('exact', 'gps', etc.).
Verify existing rows have the default JSONB value after migration. Run supabase db diff to confirm no unexpected schema changes.
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.
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.
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.