Create Supabase schema for proxy activity dual-attribution

epic-coordinator-proxy-registration-foundation-task-003 — Write and apply Supabase migration to create the proxy_activities table with dual-attribution columns: coordinator_id (the registering coordinator) and attributed_mentor_id (the peer mentor credited in Bufdir reporting). Include org_id, activity_type, date, duration, notes, is_recurring, template_id (FK), and audit fields. Add RLS policies enforcing org-scoped coordinator write permissions and read permissions.

critical priority medium complexity database pending database specialist Tier 1

Acceptance Criteria

Migration file applies cleanly via Supabase CLI and creates the proxy_activities table with all required columns: id (UUID PK), coordinator_id (UUID NOT NULL FK → auth.users), attributed_mentor_id (UUID NOT NULL FK → auth.users), org_id (UUID NOT NULL FK → organizations), activity_type (TEXT NOT NULL), date (DATE NOT NULL), duration_minutes (INTEGER NOT NULL CHECK > 0), notes (TEXT), is_recurring (BOOLEAN NOT NULL DEFAULT false), template_id (UUID NULLABLE FK → recurring_activity_templates ON DELETE SET NULL), created_at (TIMESTAMPTZ DEFAULT now()), updated_at (TIMESTAMPTZ DEFAULT now())

A CHECK constraint enforces that coordinator_id != attributed_mentor_id (a coordinator cannot attribute activity to themselves as a peer mentor)

RLS is enabled with policies: coordinators can INSERT records where coordinator_id = auth.uid() AND org_id matches their org, coordinators can SELECT all records within their org_id, coordinators can UPDATE only their own records (coordinator_id = auth.uid()), DELETE is restricted to coordinator who created the record within the same org

Indexes exist on: (org_id), (coordinator_id), (attributed_mentor_id), (date DESC) for reporting queries, and composite (org_id, attributed_mentor_id) for Bufdir report generation

A trigger updates updated_at on every UPDATE

Rollback migration provided

All FK references use ON DELETE RESTRICT for coordinator_id and attributed_mentor_id to prevent orphaned attribution records

Technical Requirements

frameworks

Supabase

apis

Supabase Migrations CLI

Supabase RLS

data models

proxy_activities

recurring_activity_templates

organizations

auth.users

performance requirements

Composite index on (org_id, attributed_mentor_id) is critical for Bufdir report queries that aggregate activities per mentor

Index on (date DESC) supports time-range filtering for monthly/annual reporting

Table must handle up to 100,000 rows per org without degradation — use BRIN index on created_at for large table range scans

security requirements

coordinator_id != attributed_mentor_id CHECK constraint is a hard data-integrity requirement from the Bufdir dual-attribution model — must not be skipped

RLS INSERT policy must validate org membership server-side — coordinator cannot set an arbitrary org_id

attributed_mentor_id FK must reference a valid user in the same org — consider a trigger or FK-based constraint to enforce same-org attribution

Audit trail: every INSERT must also write to proxy_audit_log (enforced via trigger in the audit log task, but document the dependency here)

Execution Context

Execution Tier

Tier 1

Tier 1 - 540 tasks

Can start after Tier 0 completes

View Full Execution Plan

Implementation Notes

The dual-attribution design is the most critical architectural decision in this entire epic — coordinator_id and attributed_mentor_id serve fundamentally different purposes: coordinator_id drives audit accountability and access control, while attributed_mentor_id drives Bufdir subsidy reporting. These must never be conflated or swapped. Document this directly in the migration SQL as a comment block above the table definition. The FK for attributed_mentor_id references auth.users; however, the application layer must additionally validate that the referenced user has the 'peer_mentor' role in the same org — this cannot be enforced purely at the FK level and must be reinforced in the ProxyActivityRepository.

For is_recurring, keep it a simple boolean — do not try to model recurrence logic in this table, that belongs in recurring_activity_templates.

Testing Requirements

SQL/pgTAP tests covering: (1) valid INSERT with coordinator_id ≠ attributed_mentor_id succeeds, (2) INSERT with coordinator_id = attributed_mentor_id is rejected by CHECK constraint, (3) coordinator can read own org's records, (4) coordinator cannot read another org's records, (5) coordinator can update own record but not another coordinator's record in same org, (6) template_id FK SET NULL on template deletion works correctly, (7) date index is used for range queries (use EXPLAIN ANALYZE). All tests run against local Supabase.

Component

Proxy Activity Repository

data medium

Dependencies (1)

Write and apply Supabase migration to create the recurring_activity_templates table with columns for template name, activity type, duration defaults, recurrence pattern, coordinator_id, org_id, and timestamps. Include Row Level Security policies scoped to coordinator's organization. epic-coordinator-proxy-registration-foundation-task-001

Epic Risks (3)

high impact medium prob security

Supabase RLS policies for org-scoped proxy access may be difficult to express correctly, especially for coordinators with multi-chapter access. An overly permissive policy could allow cross-org proxy registrations, corrupting Bufdir reporting; an overly restrictive policy could block legitimate coordinators from registering.

Mitigation & Contingency

Mitigation: Write integration tests covering all access boundary cases (same org, cross-org, multi-chapter coordinator) before merging any RLS migration. Use parameterised RLS test helpers already established by the auth feature.

Contingency: If RLS proves insufficient, add a server-side Edge Function validation layer that re-checks org membership before persisting any proxy record, providing defence in depth.

medium impact low prob technical

Adding new tables and foreign key constraints to an existing production Supabase database risks migration failures or locking issues if the database already contains active sessions during deployment.

Mitigation & Contingency

Mitigation: Use additive-only migrations (no DROP or ALTER on existing tables). Test full migration sequence in a staging Supabase project before production deployment. Schedule during low-traffic window.

Contingency: Maintain a rollback migration script. If the migration fails, the feature remains unreachable behind a feature flag while the schema issue is resolved.

high impact medium prob security

Audit log entries must be immutable for compliance, but Supabase RLS by default allows row owners to update their own rows. If audit records are accidentally mutable, dispute resolution and accountability guarantees are invalidated.

Mitigation & Contingency

Mitigation: Configure the proxy_audit_log table with an RLS policy that allows INSERT for coordinators but denies UPDATE and DELETE for all roles including service_role, enforced at the database level.

Contingency: If RLS cannot fully prevent updates, create a database trigger that reverts any UPDATE to the audit table and logs the attempt as a security event.

Quick Links

All Tasks Execution Plan