Implement ReferralCodeRepository class

epic-membership-recruitment-foundation-task-003 — Implement the ReferralCodeRepository Dart class backed by the Supabase client. Expose methods: createCode(mentorId, orgId) → ReferralCode, deactivateCode(codeId), getActiveCodeForMentor(mentorId, orgId) → ReferralCode?, lookupByCodeString(codeString) → ReferralCode?, and listCodesForOrg(orgId) → List<ReferralCode>. Enforce the one-active-code-per-mentor-per-organisation invariant at the repository level by deactivating any existing active code before inserting a new one. Handle Supabase exceptions and map them to domain exceptions.

critical priority medium complexity backend pending backend specialist Tier 1

Acceptance Criteria

ReferralCodeRepository is a Dart class in lib/features/recruitment/data/repositories/ accepting a SupabaseClient via constructor injection

createCode(mentorId, orgId) atomically deactivates any existing active code for that mentor+org pair before inserting a new one; both operations succeed or both fail (use a Postgres function / RPC to ensure atomicity)

createCode returns a fully populated ReferralCode domain model including the generated code string and expiry

deactivateCode(codeId) sets is_active = false and deactivated_at = now() on the given row; throws ReferralCodeNotFoundException if the codeId does not exist

getActiveCodeForMentor(mentorId, orgId) returns the single active ReferralCode or null; never throws if no active code exists

lookupByCodeString(codeString) returns the matching ReferralCode regardless of active status (for deep-link validation); returns null if not found

listCodesForOrg(orgId) returns all codes for the org ordered by created_at DESC

All Supabase PostgrestException instances are caught and mapped to typed domain exceptions: ReferralCodeNotFoundException, ReferralCodeConflictException, ReferralCodeRepositoryException

No Supabase-specific types leak beyond the repository boundary — callers depend only on domain models

All public methods are async and return Future<T>

Technical Requirements

frameworks

Flutter

supabase_flutter (SupabaseClient)

Riverpod (for provider registration of the repository)

apis

Supabase PostgREST API (referral_codes table)

Supabase RPC (for atomic create+deactivate operation)

data models

ReferralCode

referral_codes (Supabase table)

performance requirements

lookupByCodeString must rely on the unique index on the code_string column (task-001) and complete in < 100 ms

listCodesForOrg must use server-side ordering and limit results to 200 rows to prevent unbounded payloads

security requirements

Never expose the raw SupabaseClient outside the repository

RLS on the referral_codes table (task-001) ensures users can only read/write their own org's codes — the repository must not add client-side filtering as a substitute for server-side RLS

code_string generation must happen server-side (Postgres function using gen_random_bytes) to prevent predictable codes

Execution Context

Execution Tier

Tier 1

Tier 1 - 540 tasks

Can start after Tier 0 completes

View Full Execution Plan

Implementation Notes

Implement the atomic create+deactivate as a Postgres RPC function (e.g., `create_referral_code(mentor_id, org_id)`) rather than two sequential client calls. This prevents a race condition where two calls could each see no active code and both insert one. Use `fromJson` factory constructors on the ReferralCode model for clean deserialization. Register the repository as a Riverpod Provider (not StateNotifier) since it is a pure data-access object.

Follow the project's existing repository pattern — check other repository classes in lib/features/ for conventions on exception handling and model mapping. Avoid using `.execute()` which is deprecated in newer supabase_flutter versions; prefer the typed query builders.

Testing Requirements

Unit tests using flutter_test with a mocked SupabaseClient (use mocktail or hand-written fakes). Test each method independently: (1) createCode deactivates existing active code before insert; (2) createCode returns populated ReferralCode on success; (3) deactivateCode throws ReferralCodeNotFoundException for unknown ID; (4) getActiveCodeForMentor returns null when no active code exists; (5) lookupByCodeString returns null for unknown code string; (6) PostgrestException is correctly mapped to the appropriate domain exception for each method. Integration test (optional, against local Supabase) verifying the atomic deactivation invariant holds under concurrent calls. Target 90% line coverage on the repository class.

Component

Referral Code Repository

data low

Dependencies (1)

Write and apply a Supabase database migration that creates the referral_codes table with columns for id, mentor_id, organisation_id, code_string (unique), is_active, created_at, and expires_at. Add a partial unique index enforcing one active code per mentor per organisation. Define RLS policies so mentors can read their own codes and coordinators can read all codes within their organisation. epic-membership-recruitment-foundation-task-001

Epic Risks (3)

high impact medium prob technical

iOS Universal Links and Android App Links have distinct configuration requirements (apple-app-site-association, assetlinks.json, entitlements). A misconfiguration causes the OS to open the referral URL in a browser instead of the app, completely breaking the onboarding funnel for new members on one platform.

Mitigation & Contingency

Mitigation: Configure both Universal Links and App Links from the start of this epic using the project's existing Supabase-hosted domain. Write an E2E test on both simulators that taps a referral URL and asserts the onboarding screen is reached. Document the required server-side JSON files alongside the migration.

Contingency: If platform deep-link configuration cannot be resolved before the UI epics need the handler, implement a fallback custom-scheme URI (e.g., likeperson://referral?code=XYZ) that works unconditionally, and schedule Universal/App Link fix as a follow-up task.

high impact high prob security

Referral click events must be writable without an authenticated session (a new member who has not yet registered is tapping the link). Standard Supabase RLS cannot grant anonymous inserts without opening a security hole. If this is not solved early it blocks the entire attribution pipeline.

Mitigation & Contingency

Mitigation: Design referral_events writes to go exclusively through a Supabase Edge Function that validates the referral code exists and is active before inserting. The Edge Function uses the service-role key server-side; the client only calls the function endpoint. This is documented in the feature spec.

Contingency: If the Edge Function approach is delayed, temporarily allow anon inserts restricted by a CHECK constraint that event_type = 'click' and new_member_id IS NULL, then tighten to Edge Function writes in a follow-up migration before the feature goes to production.

medium impact low prob dependency

The qr_flutter package version pinned in pubspec may conflict with the current Flutter SDK version or with other packages in the monorepo, causing build failures that block QR code delivery.

Mitigation & Contingency

Mitigation: Verify qr_flutter compatibility against the project's Flutter SDK version as the very first task in this epic. If a conflict exists, resolve it before any other work proceeds.

Contingency: If qr_flutter cannot be made compatible, evaluate mobile_scanner (already likely in pubspec for QR scanning) which also supports generation, or implement QR generation via a lightweight Dart port as a last resort.

Quick Links

All Tasks Execution Plan