Create Supabase migration for referral_events table

epic-membership-recruitment-foundation-task-002 — Write and apply a Supabase database migration that creates the referral_events table with columns for id, referral_code_id (FK), event_type (enum: clicked, registered, converted), new_member_user_id (nullable), organisation_id, occurred_at, and metadata jsonb. Add RLS policies restricting write access to edge functions and read access to coordinators of the relevant organisation. Include indexes on referral_code_id and event_type for efficient attribution queries.

critical priority medium complexity database pending database specialist Tier 1

Acceptance Criteria

Migration file is created under supabase/migrations/ with a timestamped filename and applies cleanly via `supabase db push` without errors

referral_events table exists with all required columns: id (uuid, PK, default gen_random_uuid()), referral_code_id (uuid, NOT NULL, FK → referral_codes.id ON DELETE CASCADE), event_type (enum: clicked | registered | converted, NOT NULL), new_member_user_id (uuid, nullable, FK → auth.users.id), organisation_id (uuid, NOT NULL, FK → organisations.id), occurred_at (timestamptz, NOT NULL, default now()), metadata (jsonb, default '{}')

A Postgres enum type referral_event_type is created (or migration uses a CHECK constraint) and is referenced by the event_type column

Composite index exists on (referral_code_id, event_type) for attribution query performance

Index exists on (organisation_id, occurred_at DESC) for coordinator dashboard queries

RLS is enabled on the table; INSERT/UPDATE/DELETE are restricted to the service_role / edge function role only — no authenticated user may write directly

SELECT RLS policy allows coordinators to read rows where organisation_id matches their own organisation (joined via user_organisation_roles or equivalent)

Migration is idempotent: re-running it does not error (use IF NOT EXISTS guards or separate rollback migration)

A rollback migration (down) file is provided that drops the table and enum type cleanly

Migration has been reviewed for forward compatibility with the referral_codes table schema introduced in task-001

Technical Requirements

frameworks

Supabase CLI (supabase db push / supabase migration new)

apis

Supabase Management API (for applying migrations in CI)

Supabase Auth (auth.users reference)

data models

referral_events

referral_codes

organisations

auth.users

performance requirements

Attribution funnel query for a single org over 90 days must complete in < 200 ms with expected data volumes (up to 50 000 events per org)

Composite index on (referral_code_id, event_type) must be a btree index

Consider partial index on event_type = 'converted' if conversion reporting is the dominant query pattern

security requirements

RLS must be enabled; no row may be read by a user outside their organisation

Write access granted only to service_role (used by edge functions) — never to anon or authenticated roles

organisation_id must be stored redundantly on each row (denormalised from referral_codes) to allow efficient, index-supported RLS without join overhead

metadata jsonb column must not store PII beyond what is required; document allowed keys in a migration comment

Execution Context

Execution Tier

Tier 1

Tier 1 - 540 tasks

Can start after Tier 0 completes

View Full Execution Plan

Implementation Notes

Denormalise organisation_id onto referral_events (copying it from referral_codes at insert time inside the edge function) rather than joining through referral_codes in the RLS policy — this keeps the policy a simple equality check and avoids a subquery that defeats index use. Use `SECURITY DEFINER` only if absolutely needed and prefer `SECURITY INVOKER` with explicit role grants instead. The enum type should be created before the table and dropped after in the rollback. Add a migration comment block at the top of the SQL file documenting the purpose, related epic, and date.

Ensure the migration is tested against the exact Supabase Postgres version used in production (check `supabase/config.toml`).

Testing Requirements

Integration tests using the Supabase local development stack (supabase start). Test suite should cover: (1) inserting a row as service_role succeeds; (2) inserting a row as an authenticated coordinator fails (RLS enforced); (3) a coordinator can SELECT only rows belonging to their organisation; (4) a coordinator cannot SELECT rows from a different organisation; (5) the FK constraint prevents inserting an event with a non-existent referral_code_id; (6) all three event_type enum values are accepted and invalid values are rejected; (7) the migration applies cleanly from scratch and the rollback migration drops the table without errors. Use pgTAP or SQL test scripts runnable via `supabase test db`.

Component

Recruitment Attribution Repository

data medium

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