🏠 Overview › ✅ Implementation Tasks ›✅ epic-membership-recruitment-foundation-task-001

Create Supabase migration for referral_codes table

epic-membership-recruitment-foundation-task-001 — 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.

critical priority medium complexity database pending database specialist Tier 0

Acceptance Criteria

Migration file is created at supabase/migrations/<timestamp>_create_referral_codes_table.sql following Supabase CLI naming convention
referral_codes table has columns: id UUID PRIMARY KEY DEFAULT gen_random_uuid(), mentor_id UUID NOT NULL REFERENCES auth.users(id) ON DELETE CASCADE, organisation_id UUID NOT NULL, code_string TEXT NOT NULL, is_active BOOLEAN NOT NULL DEFAULT true, created_at TIMESTAMPTZ NOT NULL DEFAULT now(), expires_at TIMESTAMPTZ
UNIQUE constraint on code_string across the entire table (global uniqueness for URL safety)
Partial unique index: CREATE UNIQUE INDEX referral_codes_one_active_per_mentor ON referral_codes (mentor_id, organisation_id) WHERE is_active = true — enforces at most one active code per mentor per org
Index on organisation_id for coordinator list queries: CREATE INDEX referral_codes_org_idx ON referral_codes (organisation_id)
Index on mentor_id for mentor self-lookup: CREATE INDEX referral_codes_mentor_idx ON referral_codes (mentor_id)
RLS enabled: ALTER TABLE referral_codes ENABLE ROW LEVEL SECURITY
RLS policy 'mentor_read_own': SELECT for authenticated users where mentor_id = auth.uid()
RLS policy 'coordinator_read_org': SELECT for users whose JWT claims include role='coordinator' and organisation_id matches the row's organisation_id
RLS policy 'mentor_insert_own': INSERT for authenticated users where mentor_id = auth.uid() — combined with the partial unique index this enforces the one-active-code rule
RLS policy 'mentor_update_own': UPDATE (is_active, expires_at columns only) for authenticated users where mentor_id = auth.uid()
No DELETE policy — codes are deactivated via is_active flag, never hard deleted (audit trail)
Migration applies cleanly with supabase db push on a fresh local Supabase instance with no errors
supabase db diff shows no pending changes after migration is applied
A corresponding down migration or comment block explains how to roll back (DROP TABLE referral_codes CASCADE)

Technical Requirements

frameworks
Flutter
apis
Supabase PostgreSQL 15
Supabase Auth
data models
assignment
contact
performance requirements
Partial unique index must make the 'one active code per mentor' constraint a zero-cost check at write time
coordinator_read_org policy must use an index scan on organisation_id — verified with EXPLAIN ANALYZE in local Supabase
expires_at column should have an optional index added in a future migration if expiry-based queries become frequent — note this in a SQL comment
security requirements
Row-Level Security enabled on table before any INSERT policy is created — never create policies on an RLS-disabled table
Service role key (used only in Edge Functions) can bypass RLS — document this in a SQL comment warning against using service role from mobile clients
mentor_id references auth.users(id) with ON DELETE CASCADE — if a user account is deleted, their referral codes are automatically removed (GDPR right to erasure support)
code_string must not encode any PII — it is a random alphanumeric token only
organisation_id should reference an organisations table if one exists; add a REFERENCES constraint and FK index — if the organisations table doesn't exist yet, add a TODO comment
RLS update policy restricts which columns can be updated (is_active, expires_at) — prevents mentors from changing mentor_id or organisation_id on existing codes

Execution Context

Execution Tier
Tier 0

Tier 0 - 440 tasks

Implementation Notes

Use the Supabase CLI workflow: supabase migration new create_referral_codes_table, then edit the generated file. Test locally with supabase db reset followed by supabase db push. For the RLS coordinator policy, the JWT claim check pattern is: (auth.jwt() -> 'app_metadata' ->> 'role' = 'coordinator') AND (auth.jwt() -> 'app_metadata' ->> 'organisation_id' = organisation_id::text). Verify that the app_metadata fields are actually set during user provisioning — if they're not, coordinate with the auth setup task to ensure claims are populated.

Use COMMENT ON TABLE referral_codes IS '...' and COMMENT ON COLUMN referral_codes.code_string IS '...' SQL comments to document the table purpose and invariants inline — this is visible in Supabase Studio and helps future developers. Consider adding a CHECK constraint: CHECK (expires_at IS NULL OR expires_at > created_at) to prevent logically invalid expiry timestamps at the DB level.

Testing Requirements

SQL-level tests using pgTAP (if available in the Supabase project) or manual verification checklist: (1) Insert a code as mentor A, attempt insert of second active code as mentor A in same org — expect unique violation. (2) Deactivate first code (is_active=false), insert new active code — expect success. (3) As mentor A, attempt SELECT on mentor B's codes — expect 0 rows returned. (4) As coordinator with matching organisation_id, SELECT on mentor A's codes — expect rows returned.

(5) As coordinator, attempt SELECT on codes from a different organisation — expect 0 rows. (6) Attempt INSERT with mentor_id != auth.uid() — expect RLS violation. Document all manual test steps in a migration verification checklist comment at the bottom of the migration file. If the project gains pgTAP support, convert checklist to pgTAP tests.

Component
Referral Code Repository
data low
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