🏠 Overview › ✅ Implementation Tasks ›✅ epic-coordinator-proxy-registration-foundation-task-001

Create Supabase schema for recurring activity templates

epic-coordinator-proxy-registration-foundation-task-001 — 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.

critical priority low complexity database pending database specialist Tier 0

Acceptance Criteria

Migration file is created with a timestamped name following project conventions (e.g., 20260329_create_recurring_activity_templates.sql) and successfully applies via Supabase CLI without errors
Table includes all required columns: id (UUID, primary key, default gen_random_uuid()), name (TEXT NOT NULL), activity_type (TEXT NOT NULL), duration_minutes (INTEGER NOT NULL), recurrence_pattern (TEXT NOT NULL — values: daily, weekly, monthly, custom), coordinator_id (UUID NOT NULL, FK → auth.users), org_id (UUID NOT NULL, FK → organizations), created_at (TIMESTAMPTZ NOT NULL DEFAULT now()), updated_at (TIMESTAMPTZ NOT NULL DEFAULT now())
RLS is enabled on the table and at least three policies exist: SELECT for coordinators within the same org_id, INSERT for authenticated coordinators scoped to their own org_id, UPDATE/DELETE restricted to the coordinator who created the record
A trigger updates updated_at on every UPDATE operation
Appropriate indexes exist: on (org_id), on (coordinator_id), and a composite index on (org_id, coordinator_id)
Migration is idempotent — re-running it does not produce errors (use IF NOT EXISTS guards)
Rollback migration (down migration) is provided alongside the up migration

Technical Requirements

frameworks
Supabase
apis
Supabase Migrations CLI
Supabase RLS
data models
recurring_activity_templates
organizations
auth.users
performance requirements
Index on org_id ensures coordinator list queries complete in under 100ms for up to 10,000 templates per org
UUID primary keys preferred over serial integers for distributed insert safety
security requirements
RLS must be enabled before any data is inserted — never disable RLS on this table
INSERT policy must enforce that coordinator_id matches auth.uid() to prevent impersonation
org_id on INSERT must be validated against the coordinator's own organization — do not allow cross-org template creation
No policy grants SELECT to service_role or anon except via server-side functions with explicit justification

Execution Context

Execution Tier
Tier 0

Tier 0 - 440 tasks

Implementation Notes

Use Supabase CLI migrations workflow (supabase migration new). Define recurrence_pattern as TEXT with a CHECK constraint rather than a custom ENUM to avoid painful ALTER TYPE migrations later if new patterns are added. The duration_minutes column should default to 30 to align with the workshop requirement that 30 minutes is the standard default for HLF (380 registrations/year use case). Store recurrence_pattern as a free-form JSON string in a notes column if structured recurrence (e.g., 'every Monday at 10:00') is needed in future — keep the initial column simple.

Ensure the FK to organizations uses ON DELETE CASCADE so template orphans are avoided if an org is removed.

Testing Requirements

Write SQL-level tests (using pgTAP or Supabase's built-in test runner) verifying: (1) a coordinator can insert a template for their own org, (2) a coordinator cannot insert a template for a different org, (3) a coordinator can read their org's templates but not another org's, (4) a coordinator can delete their own template but not another coordinator's template within the same org, (5) updated_at is automatically set on update. Run migration against a local Supabase instance and confirm all tests pass before merging.

Component
Recurring Activity Template Repository
data low
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