Hockey Association Membership System

A comprehensive membership and registration system for adult hockey associations, built with Next.js, Supabase, and Stripe.

Git Merge Strategies

• ✅ Feature branches → development: Squash merge
• ✅ Development → main: Regular merge

Every promotion PR (development → main) will show GitHub's "This branch is out-of-date with the base branch" banner, even when it's the very first check after opening the PR and even with only one merge into development since the last promotion. This is expected, not a sign of missing work: each promotion creates a merge commit that lives only on main (it's never merged back into development, since promotion is one-directional), so main's tip is immediately a commit development doesn't have in its ancestry — regardless of how many feature merges land in development afterward. It recurs on every single promotion. Content-wise the branches stay identical (verify with git diff main development, ignoring the promotion PR's own new files, if in doubt); the banner is purely an artifact of merge-commit ancestry. Click "Merge pull request" directly — clicking "Update branch" instead would merge main into development first, which isn't part of this workflow and adds a pointless extra merge commit, since there's nothing for it to actually bring in.

Features

• User Management: Passwordless authentication with magic links and Google OAuth
• Membership System: Flexible duration-based memberships with monthly/annual pricing
• Registration System: Team and event registration with capacity management
• Advanced Discount System: Category-based discount codes with organizational grouping
  • Scholarship Fund: PRIDE codes (100%, 75%, 50%, 25% discounts)
  • Board Member, Captain, Volunteer: Role-specific discount categories
  • Usage Limits: Per-category limits per user per season (e.g., $500 scholarship cap)
  • Accounting Integration: Category-specific accounting codes for financial reporting
• Payment Processing: Secure payments via Stripe with payment intent handling
• Email Integration: Transactional emails via Loops.so for confirmations and notifications
• Admin Dashboard: Season management, membership oversight, and user administration
• Xero Integration: Automatic invoice creation and payment recording for seamless accounting
• Financial Reporting: Comprehensive financial tracking with categorized line items for accurate reporting

Financial Reporting Architecture

The system uses a sophisticated line item categorization system to track financial data for accurate reporting. All financial transactions are stored in the xero_invoice_line_items table with specific line_item_type values that determine how they appear in financial reports.

Key Database View: reports_financial_data

The reports_financial_data view is the primary source for financial reporting. It joins xero_invoice_line_items with related tables and includes only AUTHORISED invoices (not DRAFT) with completed payments.

Line Item Types and Categorization

The xero_invoice_line_items.line_item_type field categorizes financial data into four main types:

1. Memberships (line_item_type = 'membership')

• Purpose: Track membership fee payments
• Amount: Positive (charged to customer)
• Reports Section: Memberships breakdown
• Example: Annual membership fee, monthly membership fee

2. Registrations (line_item_type = 'registration')

• Purpose: Track registration fee payments
• Amount: Positive (charged to customer)
• Reports Section: Registrations breakdown
• Example: Team registration fee, event registration fee

3. Discounts (line_item_type = 'discount')

• Purpose: Track actual discount codes applied
• Amount: Negative (credit to customer)
• Reports Section: Discount Usage (grouped by category)
• Requirements: Must have discount_code_id populated
• Example: PRIDE50 scholarship code, board member discount

4. Donations (line_item_type = 'donation')

• Purpose: Track financial assistance and additional donations
• Amount:
  • Positive: Donation received (additional donation)
  • Negative: Donation given (financial assistance)
• Reports Section: Donations (received vs given)
• Example: Financial assistance for memberships, additional donations

Financial Reports Structure

Based on these line item types, the financial reports are organized as:

• Memberships: All membership line items, grouped by membership type
• Discount Usage: All discount line items with discount_code_id, grouped by discount category
• Donations: All donation line items, separated into received (positive) and given (negative)
• Registrations: All registration line items, grouped by registration type

Important Notes

• Discount codes (like PRIDE50) are tracked as discount line items with proper discount_code_id
• Financial assistance for memberships is tracked as donation line items (negative amount)
• Only AUTHORISED invoices are included in reports (DRAFT invoices are excluded)
• Customer names are resolved from the users table via the view

Getting Started

Prerequisites

• Node.js 24.x and npm. The version is pinned in .nvmrc, and package.json engines and CI use the same version. With nvm, run nvm install once, then nvm use in the repo.
• Supabase account and project
• Stripe account (test mode for development)
• Loops.so account for email integration
• Sentry account for error monitoring and alerting
• Xero account for accounting integration

1. Clone and Install

git clone <repository-url>
cd membership-system
nvm use
npm install

2. Environment Setup

Copy .env.example to .env.local and fill in real values (Google OAuth is configured in the Supabase dashboard, not via app env vars):

# Database
NEXT_PUBLIC_SUPABASE_URL=your_supabase_project_url
NEXT_PUBLIC_SUPABASE_ANON_KEY=your_supabase_anon_key
SUPABASE_SERVICE_ROLE_KEY=your_supabase_service_role_key
SUPABASE_MAX_ROWS=1000

# Payments
STRIPE_SECRET_KEY=rk_test_your_stripe_restricted_key  # restricted key, see "Stripe restricted API key"
NEXT_PUBLIC_STRIPE_PUBLISHABLE_KEY=pk_test_your_stripe_publishable_key
STRIPE_WEBHOOK_SECRET=whsec_your_webhook_secret
STRIPE_API_VERSION=2026-09-30.endive

# Email Integration
LOOPS_API_KEY=your_loops_api_key
LOOPS_EMAIL_BATCH_DELAY_MS=1000
LOOPS_WELCOME_TEMPLATE_ID=your_welcome_template_id
LOOPS_MEMBERSHIP_PURCHASE_TEMPLATE_ID=your_template_id
LOOPS_MEMBERSHIP_EXPIRING_TEMPLATE_ID=your_membership_expiring_template_id
LOOPS_PAYMENT_FAILED_TEMPLATE_ID=your_payment_failed_template_id
LOOPS_PAYMENT_METHOD_REMOVED_TEMPLATE_ID=your_payment_method_removed_template_id
LOOPS_PAYMENT_PLAN_PRE_NOTIFICATION_TEMPLATE_ID=your_payment_plan_pre_notification_template_id
LOOPS_PAYMENT_PLAN_PAYMENT_PROCESSED_TEMPLATE_ID=your_payment_plan_payment_processed_template_id
LOOPS_PAYMENT_PLAN_PAYMENT_FAILED_TEMPLATE_ID=your_payment_plan_payment_failed_template_id
LOOPS_PAYMENT_PLAN_COMPLETED_TEMPLATE_ID=your_payment_plan_completed_template_id
LOOPS_REGISTRATION_CONFIRMATION_TEMPLATE_ID=your_registration_template_id
LOOPS_WAITLIST_ADDED_TEMPLATE_ID=your_waitlist_template_id
LOOPS_WAITLIST_SELECTED_TEMPLATE_ID=your_waitlist_selected_template_id
LOOPS_ALTERNATE_SELECTION_TEMPLATE_ID=your_alternate_selection_template_id
LOOPS_ALTERNATE_REGISTRATION_CONFIRMATION_TEMPLATE_ID=your_alternate_registration_confirmation_template_id
LOOPS_EMAIL_CHANGE_CONFIRMED_TEMPLATE_ID=your_email_change_confirmed_template_id
LOOPS_REFUND_TEMPLATE_ID=your_refund_template_id
LOOPS_TEAM_MESSAGE_TEMPLATE_ID=your_team_message_template_id
LOOPS_CAPTAIN_ASSIGNMENT_NOTIFICATION_TEMPLATE_ID=your_captain_assignment_template_id
LOOPS_CAPTAIN_REMOVAL_NOTIFICATION_TEMPLATE_ID=your_captain_removal_template_id
LOOPS_CAPTAIN_ROSTER_CHANGE_TEMPLATE_ID=your_captain_roster_change_template_id
LOOPS_ADMIN_NEW_REGISTRATION_TEMPLATE_ID=your_admin_new_registration_template_id
LOOPS_ADMIN_REFUND_TEMPLATE_ID=your_admin_refund_template_id
LOOPS_ADMIN_PAYMENT_PLAN_FAILURE_TEMPLATE_ID=your_admin_payment_plan_failure_template_id

# Error Monitoring
NEXT_PUBLIC_SENTRY_DSN=your_sentry_dsn_here
SENTRY_ORG=your_sentry_org
SENTRY_PROJECT=membership-system
# Auth token for uploading source maps at build time (readable stack traces in Sentry).
# Requires "Source Map Upload" + "Release Creation" scopes. Not needed for local dev.
SENTRY_AUTH_TOKEN=your_sentry_auth_token_here

# Xero Integration Configuration
# Recommendation: Create separate Xero apps for development/preview/production environments
XERO_CLIENT_ID=your_xero_client_id_here
XERO_CLIENT_SECRET=your_xero_client_secret_here
XERO_REDIRECT_URI=http://localhost:3000/api/xero/callback
XERO_SCOPES=accounting.transactions accounting.contacts accounting.settings offline_access

# Cron
CRON_SECRET=your_random_cron_secret_for_cron_jobs

# Application Configuration
NEXT_PUBLIC_SITE_URL=http://localhost:3000
ORGANIZATION_NAME=Your Organization Name
[email protected]

# Timezone Configuration
# IANA timezone identifier for date/time display throughout the application
# Examples: America/New_York, America/Los_Angeles, Europe/London, UTC
NEXT_PUBLIC_APP_TIMEZONE=America/New_York

Automated preview testing (e.g. an AI agent driving a browser against a preview URL) needs a few additional Preview-scoped-only vars — see Preview auth bypass for automated testing.

3. Database Setup

1. Create a new Supabase project

2. Get your API keys from Supabase Dashboard:

   • Go to Settings → API
   • Click on the "API Keys" tab (not "Legacy API Keys")
   • If you don't have API keys yet, click "Create a new API key"
   • Copy the anon public key → NEXT_PUBLIC_SUPABASE_ANON_KEY
   • Copy the service_role key → SUPABASE_SERVICE_ROLE_KEY
   • Go to Settings → Data API to get the Project URL → NEXT_PUBLIC_SUPABASE_URL

   Note: Use the new API keys (not the legacy ones). The new keys use an improved JWT format and are the recommended approach.

3. Run the database schema:

   Open supabase/schema.sql, paste it into your Supabase SQL editor, and run it. That single file creates every table, constraint, index, function, trigger, view, RLS policy and comment the app expects — RLS included, so there is no separate policy step.

   schema.sql is generated from supabase/migrations/ (npm run schema:build), so it can't fall behind the migrations the way it did before. Migrations use the Supabase CLI's <timestamp>_name.sql naming, so supabase db push also works against an existing project.

4. Magic-Link Email Template

The login screen offers two ways in — a link or a 6-digit code — but src/app/auth/login/page.tsx makes a single signInWithOtp() call, so one email has to carry both. The choice on screen changes the wording, not the email. That template needs:

• {{ .Token }}, the code typed into /auth/verify-otp
• a link to /auth/magic-confirm?token_hash={{ .TokenHash }}&type=magiclink, which src/app/auth/magic-confirm/page.tsx verifies with supabase.auth.verifyOtp()

Two spellings of that link both work, and projects here use both. {{ .SiteURL }}/auth/magic-confirm?token_hash=... always lands on the site, whichever deployment asked for the email. {{ .RedirectTo }}?token_hash=... honours the emailRedirectTo the login page passes, so a magic link requested from a preview deployment comes back to that preview — but only while the preview URL is in the project's Redirect URLs allowlist; when it isn't, Supabase falls back to the Site URL and drops the /auth/magic-confirm path with it, leaving a link to the site root that verifies nothing. auth:check accepts either form.

A fresh project's default Magic Link template has neither: it links to {{ .ConfirmationURL }}, a PKCE-flow link (?code=...) that magic-confirm cannot verify, so every magic-link sign-in lands on /auth/auth-code-error. A template with only one of the two half-works, which is harder to spot — whichever method the user picks, the email may not contain what it needs.

The template lives at supabase/auth-templates/magic-link.html and is applied through Supabase's Management API:

export SUPABASE_ACCESS_TOKEN=sbp_...   # https://supabase.com/dashboard/account/tokens
npm run auth:apply -- --dry-run        # show what would change
npm run auth:apply                     # write it to the project
npm run auth:check                     # confirm the live project matches

auth:check prints the project's current template when it fails, so you can see what is actually there without opening the dashboard. The project is taken from NEXT_PUBLIC_SUPABASE_URL in your .env.local, so this targets whichever project the rest of your setup points at; pass --project-ref <ref> to override. auth:apply refuses to overwrite a template that already works — a branded one somebody wrote by hand — unless you pass --force, and it prints what it replaced either way. Only the magic-link body is touched; the subject line and every other auth template are left alone.

To do it by hand instead, go to Authentication → Email Templates → Magic Link in the dashboard and point the link at:

{{ .SiteURL }}/auth/magic-confirm?token_hash={{ .TokenHash }}&type=magiclink

This is dashboard-only state with no trace in schema.sql or the migrations, so it has to be set on every project — production, development, and any personal sandbox. npm run auth:check is how you find out whether a given project has it; run it against a project whose magic links have stopped working before assuming the bug is in the app. It applies to any custom template you write later too; see Custom SMTP Configuration.

5. Run Development Server

npm run dev

Open http://localhost:3000 to see the application.

Database Migrations

supabase/migrations/ holds the schema, as timestamped files the Supabase CLI understands (YYYYMMDDHHMMSS_name.sql, UTC). History before 2026-09-07 was collapsed into 20260907000000_baseline_schema.sql, a snapshot of the production database; the 140 dated files it replaced are in git history.

supabase/schema.sql is generated from that directory — never edit it directly:

npm run schema:build      # regenerate after adding a migration; commit the result
npm run schema:check      # fails if schema.sql is stale (blocking in CI)
npm run migrations:lint   # rejects unsafe views and public EXECUTE grants (blocking in CI)
npm run schema:verify     # applies schema.sql twice to a throwaway database

schema:verify needs a reachable PostgreSQL server via the standard PG* variables; CI runs it against a service container. It exists because parsing SQL is not enough to catch a broken migration — COMMENT ON TABLE <a view> parses cleanly and fails on execution.

Applying migrations

Migrations are applied deliberately, not as a side effect of merging. Two supported routes:

Through the workflow — the supported route. It runs supabase db push, which records what it applied in supabase_migrations.schema_migrations. Everything downstream depends on that record being true: db push skips what's already applied, and Migration applied to target database (below) compares against it.

By hand, in the SQL editor — only as a fallback, and never on its own. The editor runs the SQL but writes no tracking row, so the CLI still believes the migration is pending: the next db push tries to re-apply it and the preflight check reports the target as behind even though the change is live. If you do apply by hand, immediately record it:

supabase migration repair --status applied <version> --db-url "postgresql://..."

This is not theoretical — it's why the pre-2026-09-07 history was invisible to the CLI, and why seeding was needed before any of this could work.

The development database is the exception to "deliberately": preview deployments run against it, so a migration is normally applied there during feature work, before the branch is pushed — see AGENTS.md. Production is meant to be applied by a human before the PR that adds it merges — and Migration applied to target database (below) is a required PR check that enforces exactly that, rather than leaving it to convention.

To run it: Actions → Apply database migrations → Run workflow, pick development or production, and leave dry run checked for the first pass. The dry run prints supabase migration list, showing which files the target database has and hasn't seen. Re-run with dry run unchecked to apply.

It also runs on merge. A push to main or development that changes anything under supabase/migrations/ starts the workflow, and applies immediately and unattended on both branches — neither Environment has a required reviewer (see "Setting up the workflow" below for why, and what replaced it). Pushes that don't touch migrations trigger nothing. On development unattended is the point — previews run against that database. On main it lands in the same moment as Vercel's production deploy, but should be a no-op by the time it runs: the preflight check below already forced a manual apply before the merge was even allowed.

What a failure means. This workflow runs after the push has landed, so it cannot fail the merge or undo it. A red run means the commit is on the branch and the migration is not applied — the code is ahead of the database, and nothing was rolled back. Recovery is to fix the migration in a follow-up and let it re-run, or apply by hand and repair the history.

Two different questions get answered before merge, by two different checks. Whether a migration is safe is schema:check, migrations:lint, and the schema job, which applies schema.sql to a real PostgreSQL container — those catch a migration that shouldn't exist. Whether it's applied yet is Migration applied to target database, below — a required check that fails the pull request outright if its target database is missing anything the repo carries, catching a migration that's perfectly safe but that nobody has run. The apply workflow itself is downstream of both: by the time a merge triggers it, everything should already be in place.

What neither PR check can see is SQL applied by hand outside the repo entirely — an object created in the SQL editor writes no row to supabase_migrations.schema_migrations, so there's nothing here to compare against. That's a separate, harder problem, tracked in #312.

Enforcing it before merge

Migration applied to target database (db-migration-preflight.yml) compares supabase/migrations/ against the target database's supabase_migrations.schema_migrations, as a required PR check — on every pull request into main or development, not just ones that touch supabase/migrations/ (a required check has to report on every PR, or it blocks merges forever waiting for a run that never comes — see "Two things about the status checks specifically"). It fails the PR if the branch it's merging into is missing a migration this repo carries, with a message telling you to run Apply database migrations first.

This is what makes "apply before merge" a rule instead of a habit: nothing merges until the target database is caught up, and you control exactly when you run the apply workflow rather than waiting on anyone's approval.

It needs read access to the target database: SUPABASE_DB_URL for supabase-development (already configured, ungated), and SUPABASE_DB_URL_READONLY for the ungated supabase-production-readonly Environment described below — never the gated admin credential, or this check would inherit the same "waits for approval" problem it exists to route around.

Those checks only block a merge if you require them. Without a ruleset, a red pull request can still be merged and anyone can push straight to main, skipping the checks entirely — "we always use a PR" is a convention rather than a guarantee.

Settings → Rules → Rulesets → New branch ruleset. One ruleset covers both branches: under Target branches → Include → add main and development. Set Enforcement status: Active — a ruleset can be saved in Disabled or Evaluate mode, where it reports without blocking anything. Leave the bypass list empty; adding "Repository admin" makes the whole thing advisory for exactly the person doing the merging.

Rules to enable:

Rule	Setting
Require a pull request before merging	On, with required approvals: 0
Require status checks to pass	On — add test, schema, and Migration applied to target database, and tick Require branches to be up to date before merging
Restrict deletions	On (default)
Block force pushes	On (default)

Required approvals defaults to 1, and nobody can approve their own pull request — on a repository with effectively one maintainer that setting locks you out of merging your own work. Zero still gives you the pull request and the checks.

Rules to leave off:

• Require linear history — it rejects merge commits, and development → main is a regular merge (see Git Merge Strategies).
• Restrict updates — blocks all pushes to the branch, not just direct ones.
• Require signed commits — rejects commits from any contributor or automation not set up for signing.
• Require deployments to succeed — ties the branch to GitHub Environment deployments, which here are the migration Environments; unrelated, and confusing to tangle together.
• Restrict creations, code scanning, code quality, coverage, Copilot review — not applicable to this repository.

Two things about the status checks specifically:

• A check only appears in the picker once it has run at least once. schema is newer than test, so it may not be listed until a pull request has exercised it. The same applies to Migration applied to target database, which is newer still. Add each afterwards rather than typing the name by hand — a ruleset treats an unrecognised check as "never reported" and blocks every merge until something reports it.
• Do require Migration applied to target database, but not Apply database migrations. The former runs on pull_request and is what actually blocks a merge ahead of an unapplied migration (see "Enforcing it before merge" above). The latter never runs on pull requests at all, so requiring it would deadlock every merge waiting for a check that can't report.

One consequence to expect: with a pull request required on main, promoting development → main becomes a pull request rather than a local merge and push. Same result, one extra step, and the checks run against the exact commit that lands.

This is the route for contributors who don't hold database credentials: the connection string lives in the GitHub Environment, so anyone with write access to the repository can apply to development from the Actions tab without having it locally. Pull requests from forks can't reach repository secrets at all — ask a maintainer to run it.

Setting up the workflow

One-time, per project:

1. Seed the CLI's migration history. Already done for the existing dev and production projects on 2026-09-07 — all three of 20260907000000, 20260907000001 and 20260907000002 are recorded as applied on both. This step only applies to a new project (a personal sandbox, say).

   Migrations applied by hand are invisible to the CLI: it tracks what it has run in supabase_migrations.schema_migrations, and a manual run leaves no row there. Without seeding it, the first db push tries to re-apply everything to a database that already has it:

   supabase migration repair --status applied 20260907000000 20260907000001 20260907000002
   

   Run it against each project, naming whichever migrations that database has already received. Verify with supabase migration list.

2. Create GitHub Environments named supabase-development and supabase-production (Settings → Environments). Neither has a required reviewer.

   Deliberately not the existing Production / Preview environments: those belong to the Vercel integration, and reusing them would tangle Vercel deployments with migrations, and the names are ambiguous besides — Preview is a Vercel deployment target, not a database. Leave Vercel's alone.

   A required reviewer on supabase-production used to be the approval gate here, but it ran after the push had already landed on main — by which point Vercel had already deployed the new application code, independent of this workflow. That left a real window (observed once at over five hours) where production ran new code against old schema before anyone approved the migration. The gate moved earlier instead, to the pull request itself: see "Enforcing it before merge" above, and "CODEOWNERS" just below for a complementary, non-blocking signal.

   CODEOWNERS

   .github/CODEOWNERS names a reviewer for supabase/migrations/, so a promotion PR (development → main) carrying a migration surfaces that reviewer in its Files Changed / reviewers panel — a useful pointer to whose eyes belong on the diff, on top of the required check above, but not itself a GitHub-enforced block. Branch protection doesn't require Code Owner review here, and it couldn't easily be turned into one on this repository: GitHub never lets a PR author satisfy a required review on their own PR, including a Code Owner review, so requiring it would deadlock every promotion this single-maintainer account opens. Migration applied to target database is what actually blocks the merge; CODEOWNERS is just where you'd look to see who should be looking.

The push: trigger in .github/workflows/db-migrate.yml is already live for both development and main — nothing to uncomment. Both apply unattended; see "Enforcing it before merge" above for where the human decision actually happens for production.

The preflight check's read-only credential

Migration applied to target database (above) must not use the admin SUPABASE_DB_URL for production: that secret lives in the supabase-production Environment, which is where a required reviewer would go if you turn one on (see "Setting up the workflow" above) — and this check has to run unattended on every single PR to do its job. Point it at a gated credential and either it inherits the gate (every PR now waits on an approval for a read-only SELECT, reintroducing the exact silent-waiting problem #290 was about) or you leave supabase-production permanently ungated just to keep the check working, which throws away the point of gating the admin credential at all.

The fix is a dedicated low-privilege role for production, not a copy of the admin connection string into an ungated Environment:

4. Apply the ci_readonly_role migration to each project (it's in supabase/migrations/, like any other). It creates a ci_readonly role that can SELECT supabase_migrations.schema_migrations and nothing else — no anon/authenticated membership, no BYPASSRLS.

5. Set its password out-of-band, once per project, e.g. from the SQL editor or psql:

   ALTER ROLE ci_readonly WITH PASSWORD '<generate one — never reuse the admin password>';
   

   Never put this in a migration file — it would land in git and in schema.sql.

6. Create an ungated supabase-production-readonly GitHub Environment (Settings → Environments) — no required reviewer, since there's nothing for one to approve. Add a SUPABASE_DB_URL_READONLY secret to it, built the same way as SUPABASE_DB_URL above (Session pooler, port 5432) but with ci_readonly.<project-ref> as the username instead of postgres.<project-ref> and that role's password.

   Verify the pooler accepts that username on the development project before relying on it — Supavisor's tenant-user format is normally <role>.<project-ref>, but this repo hadn't previously used any role but postgres through it, so treat it as unconfirmed until you've connected with it once:

   psql "postgresql://ci_readonly.qojixnzpfkpteakltdoa:<password>@aws-1-us-east-2.pooler.supabase.com:5432/postgres" \
     -tAc "select version from supabase_migrations.schema_migrations order by version"
   

   supabase-development needs no equivalent role or Environment. It's not that development is inherently safe to expose — it's that its admin credential is already reachable by an unattended, automatic workflow: db-migrate.yml applies to it on every push, no gate, no approval, by design (previews need it current). The preflight check reading through that same already-unattended credential doesn't cross any new line. Production's admin credential is deliberately kept away from anything unattended — that's what the reviewer gate on db-migrate.yml is for — so handing it to a check that has to run unattended on every PR would be a new, meaningfully larger exposure, not a continuation of an existing one. ci_readonly is what lets the preflight check run unattended on production without making that trade.

Preview deployments share the development database

Preview deploys point at the shared development Supabase project, so a migration applied there is visible to every open preview at once, and to whatever is already merged. Write migrations expand/contract so that's safe:

• Add columns nullable, or with a default; never NOT NULL without a backfill in the same file.
• Don't drop or rename anything until the code that referenced it is gone from development.
• Deploy the code that reads a new column after the migration that adds it, never before.

The alternative — a real database per pull request — is Supabase's branching feature. It isn't enabled on this project; it costs per branch and needs the Vercel integration wired to point previews at the branch database. Worth revisiting if two people start colliding on the shared dev database.

Exposing Data to External Consumers

Someone will eventually want member data in a spreadsheet, a dashboard, or another tool. There is one supported way to do that, and one way that looks easier but publishes your members' data to the internet.

The rule

Never create a view or a database function to serve an external consumer. Build an authenticated API route instead.

Supabase publishes everything in the public schema through PostgREST. The moment a view or function exists there, it has a public URL — /rest/v1/<view> or /rest/v1/rpc/<function> — and the anon key that unlocks it is not a secret: it ships in the browser bundle of every page on the site. Two specific traps:

• A view without security_invoker=true runs as its owner and ignores the RLS on the tables underneath it, while Supabase's default privileges hand anon SELECT on it automatically.
• A SECURITY DEFINER function ignores RLS entirely, and (before 20260907000002_revoke_public_function_access.sql) new functions were granted to anon and authenticated by default.

This is not hypothetical. Three export helpers — get_users_data(), get_full_data() and get_current_data() — were created directly in the SQL editor to feed a Google Sheet, and explicitly granted to anon. For about eleven weeks, anyone on the internet could POST /rest/v1/rpc/get_users_data and receive every member's first name, last name, email address and member ID. They were dropped in that migration and replaced by the route described below.

The pattern

src/app/api/admin/exports/members/route.ts is the reference implementation. The shape of it:

import { NextRequest, NextResponse } from 'next/server'
import { timingSafeEqual } from 'crypto'
import { createAdminClient } from '@/lib/supabase/admin'
import { logger } from '@/lib/logging/logger'

function secretMatches(provided: string, expected: string): boolean {
  const a = Buffer.from(provided)
  const b = Buffer.from(expected)
  if (a.length !== b.length) return false   // timingSafeEqual throws on length mismatch
  return timingSafeEqual(a, b)              // constant time: no character-by-character leak
}

export async function GET(request: NextRequest) {
  const expected = process.env.EXPORT_API_SECRET

  // Fail closed. A missing secret must never mean "no authentication required".
  if (!expected) {
    return NextResponse.json({ error: 'Not configured' }, { status: 503 })
  }

  const header = request.headers.get('authorization') ?? ''
  const token = header.startsWith('Bearer ') ? header.slice(7) : ''
  if (!token || !secretMatches(token, expected)) {
    logger.logAdminAction('export-unauthorized', 'Rejected request', {}, undefined, 'warn')
    return NextResponse.json({ error: 'Unauthorized' }, { status: 401 })
  }

  // Only past the gate do we reach for the service-role client.
  const supabase = createAdminClient()
  const { data, error } = await supabase.from('users').select('first_name, last_name, email, member_id')
  if (error) {
    return NextResponse.json({ error: 'Export failed' }, { status: 500 })  // don't echo the DB error
  }

  return NextResponse.json({ rows: data }, { headers: { 'Cache-Control': 'no-store' } })
}

Why each piece matters:

Practice	Reason
Fail closed when the secret is unset	A deploy that forgets the env var must break loudly, not serve the data to everyone. The /api/cron/* routes used to get this wrong in two different ways — see src/lib/cron/auth.ts, the shared guard all six now call, for the fixed version.
timingSafeEqual, not ===	String comparison short-circuits on the first wrong byte; response timing then leaks the secret one character at a time.
Service-role client after the auth check	The export is deliberately cross-member, which every RLS policy correctly forbids. The route's own auth is what replaces RLS, so it has to come first.
Never log the token	A rejected guess in your logs is still a credential. Log that a header was present, not what it said.
Generic error bodies	relation "users" does not exist tells an attacker about your schema.
Cache-Control: no-store	Keeps member PII out of intermediary caches.
Paginate	PostgREST caps a response at 1000 rows; without .range() looping you silently export a truncated roster.

Setting up the secret

1. Generate one. Use something with real entropy, not a passphrase:

   openssl rand -base64 32
   

2. Add it to Vercel. Project → Settings → Environment Variables → add EXPORT_API_SECRET.
   • Set a different value per environment (Production, Preview, Development). A preview secret leaking must not open production.
   • Mark it Sensitive so it can't be read back out of the dashboard. You can overwrite it blind later; you'll never need to read it.
   • Redeploy — environment variable changes only take effect on a new deployment.
3. Add it locally. Put it in .env.local (never committed). vercel env pull .env.local brings the Development value down.
4. Hand it over out-of-band. Send it to the consumer through a password manager or a Signal message, not email or Slack, and never in a spreadsheet cell.
5. Rotate it when someone with access leaves, or if it's ever pasted somewhere public: set a new value in Vercel, redeploy, then update the consumer. Rotation is a two-step with a brief window where the old value still works — that's fine and better than a gap.

Never hand out the SUPABASE_SERVICE_ROLE_KEY instead. It bypasses RLS on every table in the database; the whole point of a scoped endpoint is that a leaked export credential exposes one dataset, not everything.

Consuming it

Test with curl first:

curl -H "Authorization: Bearer $EXPORT_API_SECRET" \
  "https://your-domain.com/api/admin/exports/members?dataset=members"

For a Google Sheet, use Apps Script (Extensions → Apps Script). Store the secret in Script Properties — Project Settings → Script properties — rather than pasting it into the code, so it isn't visible to anyone with view access to the script:

function refreshMembers() {
  const secret = PropertiesService.getScriptProperties().getProperty('EXPORT_API_SECRET');
  const url = 'https://your-domain.com/api/admin/exports/members?dataset=members';

  const response = UrlFetchApp.fetch(url, {
    headers: { Authorization: 'Bearer ' + secret },
    muteHttpExceptions: true,
  });

  if (response.getResponseCode() !== 200) {
    throw new Error('Export failed: ' + response.getResponseCode() + ' ' + response.getContentText());
  }

  const rows = JSON.parse(response.getContentText()).rows;
  const header = ['first_name', 'last_name', 'email', 'member_id'];
  const values = [header].concat(rows.map(function (r) {
    return header.map(function (key) { return r[key]; });
  }));

  const sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Members');
  sheet.clearContents();
  sheet.getRange(1, 1, values.length, header.length).setValues(values);
}

Then Triggers → Add Trigger → refreshMembers, time-driven, daily.

IMPORTDATA and IMPORTJSON can't be used: neither can send an Authorization header, which is exactly why the previous approach reached for a public endpoint. That constraint is the point — if a formula can read it without credentials, so can the internet.

Available datasets

GET /api/admin/exports/members

Parameter	Values	Meaning
dataset	members (default)	One row per member: name, email, member ID
	memberships	One row per member per membership type, with the latest expiry
membership_id	uuid	Restrict memberships to one membership type
paid_only	true	Count only paid memberships (default: all, including pending and refunded)
include_deleted	true	Include soft-deleted members (default: excluded)
format	json (default), csv	Response format

Before you add another endpoint

• Does it need to be cross-member? If a signed-in user should only see their own data, use the normal authenticated app routes and let RLS do the work — no secret required.
• Return the narrowest set of columns that answers the question. An export of names and emails is a smaller incident than an export of everything.
• npm run migrations:lint (blocking in CI) rejects views created without security_invoker=true and GRANT EXECUTE to public roles, so the database-side mistake fails the build.
• Supabase's Security Advisor (Dashboard → Advisors, or get_advisors over MCP) flags anon-executable SECURITY DEFINER functions and views that bypass RLS. Check it after any schema change.

Email Integration Setup (Loops.so)

The system uses Loops.so for transactional email delivery. Follow these steps to configure email templates:

Important: Every transactional email template's Subject field must start with the {testEmailPrefix} merge tag, e.g. {testEmailPrefix}Welcome to the Hockey Association!. The app sends a testEmailPrefix data variable on every send — [TEST] on preview/local deployments, an empty string in production — so preview and production emails stay visually distinguishable even though they share the same templates. Mark testEmailPrefix as optional when adding the data variable in Loops (this is a safety net only: the app always sends the variable, including an empty string in production, so the template renders correctly whether or not it's marked optional).

1. Create Loops.so Account

1. Sign up at https://loops.so
2. Get your API key from the settings
3. Add LOOPS_API_KEY to your environment variables

2. Set Up Transactional Email Templates

Membership Purchase Confirmation Template

1. In Loops Dashboard:

   • Go to "Transactional" section
   • Click "Create transactional email"
   • Name: "Membership Purchase Confirmation"

2. Add Data Variables: Use the "Insert data variable" button to add these variables:

   • userName - Customer's full name
   • membershipName - Type of membership purchased
   • amount - Price (formatted as "45.00")
   • durationMonths - Duration in months
   • validFrom - Membership start date
   • validUntil - Membership end date
   • purchaseDate - Purchase date
   • paymentIntentId - Stripe transaction ID
   • dashboardUrl - Link to user dashboard

3. Email Template Example:

   Hi [userName],
   
   Great news! Your membership purchase has been confirmed and your access is now active.
   
   MEMBERSHIP DETAILS:
   - Membership Type: [membershipName]
   - Duration: [durationMonths] months
   - Amount Paid: $[amount]
   - Valid From: [validFrom]
   - Valid Until: [validUntil]
   - Purchase Date: [purchaseDate]
   
   WHAT'S NEXT:
   • Browse available teams and events you can now join
   • Manage your account: [dashboardUrl]
   • Watch for updates about league schedules
   
   Transaction ID: [paymentIntentId]
   
   Questions? Reply to this email or contact support.
   
   Thank you for being part of our hockey community!
   The Hockey Association Team
   

4. Get Template ID:

   • Copy the template ID from Loops
   • Add it to your .env.local as LOOPS_MEMBERSHIP_PURCHASE_TEMPLATE_ID

3. Additional Required Email Templates

The system includes several other email types that need templates configured:

Welcome Email Template (LOOPS_WELCOME_TEMPLATE_ID)

Data Variables:

• userName - New user's full name
• dashboardUrl - Link to user dashboard
• membershipUrl - Link to membership purchase page

Template Example:

Hi [userName],

Welcome to the Hockey Association! 🏒

Your account has been successfully created. Here's what you can do next:

• Explore membership options: [membershipUrl]
• Access your dashboard: [dashboardUrl]
• Browse upcoming seasons and events

We're excited to have you as part of our hockey community!

Questions? Reply to this email anytime.

The Hockey Association Team

Membership Expiration Warning (LOOPS_MEMBERSHIP_EXPIRING_TEMPLATE_ID)

Sent by the /api/cron/membership-reminders job (see § Cron Jobs) when a membership's valid_until is exactly 30, 14, 7, or 1 days away.

Data Variables:

• user_name - Member's full name
• membership_name - Type of membership expiring
• expiration_date - When membership expires
• days_until_expiration - Number of days remaining (30, 14, 7, or 1)
• renew_url - Link to renewal page

Template Example:

Hi [user_name],

Your [membership_name] will expire in [days_until_expiration] days on [expiration_date].

To avoid any interruption to your membership benefits, please renew before the expiration date.

Renew now: [renew_url]

Questions about renewal? Reply to this email.

The Hockey Association Team

Payment Failed Notification (LOOPS_PAYMENT_FAILED_TEMPLATE_ID)

Data Variables:

• userName - Customer's full name
• membershipName - Type of membership attempted
• amount - Payment amount that failed
• failureReason - Reason for payment failure
• retryUrl - Link to retry payment

Template Example:

Hi [userName],

We were unable to process your payment for [membershipName] in the amount of $[amount].

Reason: [failureReason]

Please try again: [retryUrl]

If you continue to have issues, please contact our support team.

The Hockey Association Team

Registration Confirmation Template (LOOPS_REGISTRATION_CONFIRMATION_TEMPLATE_ID)

Data Variables:

• userName - Customer's full name
• registrationName - Name of event/team registered for
• categoryName - Registration category (Player, Goalie, etc.)
• seasonName - Season name and dates
• amount - Registration fee paid (formatted as "75.00")
• registrationDate - Date of registration
• paymentIntentId - Stripe transaction ID
• dashboardUrl - Link to user dashboard

Template Example:

Hi [userName],

Congratulations! Your registration has been confirmed and payment processed successfully.

REGISTRATION DETAILS:
- Event/Team: [registrationName]
- Category: [categoryName]
- Season: [seasonName]
- Amount Paid: $[amount]
- Registration Date: [registrationDate]

WHAT'S NEXT:
• Watch for team communications and schedule updates
• Manage your registrations: [dashboardUrl]
• Contact team organizers if you have questions

Transaction ID: [paymentIntentId]

Questions about your registration? Reply to this email.

Welcome to the team!
The Hockey Association Team

Waitlist Added Notification Template (LOOPS_WAITLIST_ADDED_TEMPLATE_ID)

Data Variables:

• userName - Customer's full name
• registrationName - Name of event/team they were waitlisted for
• categoryName - Registration category (Player, Goalie, etc.)
• seasonName - Season name and dates
• position - Position on the waitlist
• waitlistDate - Date added to waitlist
• dashboardUrl - Link to user dashboard

Template Example:

Hi [userName],

You've been added to the waitlist for [registrationName].

WAITLIST DETAILS:
- Event/Team: [registrationName]
- Category: [categoryName]
- Season: [seasonName]
- Your Position: #[position] on the waitlist
- Added to Waitlist: [waitlistDate]

WHAT'S NEXT:
• You'll be notified if a spot becomes available
• Check your status anytime: [dashboardUrl]
• We'll contact you with further instructions if selected

Thank you for your interest in joining the team!
The Hockey Association Team

Waitlist Selected Confirmation Template (LOOPS_WAITLIST_SELECTED_TEMPLATE_ID)

Data Variables:

• userName - Customer's full name
• registrationName - Name of event/team
• categoryName - Registration category
• seasonName - Season name and dates
• amount - Amount charged (formatted as number, e.g., "50.00")
• purchaseDate - Date of charge (formatted as localized date string)
• paymentIntentId - Stripe payment intent ID (or "N/A" for free charges)
• discountApplied - Optional discount details (e.g., "CODE25: -$25.00")
• dashboardUrl - Link to user dashboard

Template Example:

Hi [userName],

Great news! You've been selected from the waitlist for [registrationName].

REGISTRATION CONFIRMATION:
- Event/Team: [registrationName]
- Category: [categoryName]
- Season: [seasonName]
- Amount Charged: $[amount]
- Payment Date: [purchaseDate]
- Payment Intent ID: [paymentIntentId]
[if discountApplied]
- Discount Applied: [discountApplied]
[endif]

WHAT'S NEXT:
• Your payment method has been charged
• You're now fully registered
• View your registration: [dashboardUrl]

Questions? Reply to this email anytime.

Best,
The Hockey Association Team

Alternate Selection Confirmation Template (LOOPS_ALTERNATE_SELECTION_TEMPLATE_ID)

Data Variables:

• userName - Customer's full name
• registrationName - Name of registration (e.g., "Rec League - Fall/Winter 2025-26")
• seasonName - Season name
• gameDescription - Description of the specific game (e.g., "Game 1")
• gameDate - Formatted game date (e.g., "Monday, September 11, 2025")
• gameTime - Formatted game time (e.g., "7:00 PM")
• amount - Amount charged as formatted number (e.g., 22.50)
• purchaseDate - Formatted purchase date (e.g., "September 11, 2025")
• dashboardUrl - Link to user dashboard

Template Example:

Hi [userName],

You've been confirmed as an alternate for [gameDescription]!

GAME DETAILS:
- Registration: [registrationName] ([seasonName])
- Game: [gameDescription]
- Date & Time: [gameDate] at [gameTime]
- Amount Paid: $[amount]
- Confirmation Date: [purchaseDate]

WHAT'S NEXT:
• You're now on the alternate list for this game
• You'll be contacted if a spot becomes available
• Check your status anytime: [dashboardUrl]

Thanks for being part of the team!
The Hockey Association Team

Alternate Registration Confirmation Template (LOOPS_ALTERNATE_REGISTRATION_CONFIRMATION_TEMPLATE_ID)

Sent to the member when they sign up as an alternate for a registration (event type alternate.registered). Always sent (not gated on email preferences). If the template ID is not configured, the send is skipped with a warning and the sign-up still succeeds.

Data Variables:

• userName - Member's full name
• registrationName - Name of registration (e.g., "NYCPHA Recreational League")
• seasonName - Season name (e.g., "Fall/Winter 2026")
• categoryName - Always "Alternate"
• registrationDate - Sign-up date in Eastern Time, same format as the registration confirmation (e.g., "9/19/2026")
• alternatePrice - Per-game alternate price before any discounts, as a formatted number (e.g., "25.00"); charged only if selected for a game
• dashboardUrl - Link to user dashboard (/user)

Template Example:

Hi [userName],

You're signed up as an alternate for [registrationName] ([seasonName]).

ALTERNATE DETAILS:
- Registration: [registrationName]
- Season: [seasonName]
- Role: [categoryName]
- Signed Up: [registrationDate]
- Price per Game (before any discounts, charged only if selected): $[alternatePrice]

WHAT BEING AN ALTERNATE MEANS:
• Captains or admins may select you to fill in for specific games
• You're only charged (using your saved payment method) when you're selected for a game
• You'll get a separate confirmation each time you're selected

CHANGE OR WITHDRAW:
• To withdraw or change your alternate sign-up, reply to this email or contact the league admins
• View your registrations anytime: [dashboardUrl]

Thanks for being part of the team!
The Hockey Association Team

Payment Method Removed Notification Template (LOOPS_PAYMENT_METHOD_REMOVED_TEMPLATE_ID)

Data Variables:

• userName - Customer's full name
• paymentMethod - Last 4 digits of removed payment method (e.g., "****1234")

Template Example:

Hi [userName],

Your payment method [paymentMethod] has been successfully removed from your account.

WHAT THIS MEANS:
• You've been removed from all alternate registrations
• You'll need to add a new payment method to register as an alternate again
• Your existing team registrations are not affected

If you didn't request this change or have questions, please contact our support team immediately.

The Hockey Association Team

Payment Plan Pre-Notification Template (LOOPS_PAYMENT_PLAN_PRE_NOTIFICATION_TEMPLATE_ID)

Data Variables:

• userName - Customer's full name
• registrationName - Name of registration
• seasonName - Season name and dates
• categoryName - Registration category
• nextPaymentDate - Date of upcoming payment (formatted as localized date string)
• nextPaymentAmount - Amount to be charged (formatted as "25.00")
• installmentNumber - Which installment (2, 3, or 4)
• remainingBalance - Total remaining balance (formatted as "75.00")
• dashboardUrl - Link to user dashboard

Template Example:

Hi [userName],

This is a friendly reminder that your next payment plan installment is coming up soon.

UPCOMING PAYMENT:
- Registration: [registrationName] ([seasonName])
- Category: [categoryName]
- Payment [installmentNumber] of 4
- Amount: $[nextPaymentAmount]
- Scheduled Date: [nextPaymentDate]

PAYMENT PLAN STATUS:
- Remaining Balance: $[remainingBalance]
- Payment Method: Your saved payment method will be charged automatically

WHAT YOU CAN DO:
• View payment plan details: [dashboardUrl]
• Pay remaining balance early (no penalty)
• Update your payment method if needed

Questions? Reply to this email anytime.

The Hockey Association Team

Payment Plan Payment Processed Template (LOOPS_PAYMENT_PLAN_PAYMENT_PROCESSED_TEMPLATE_ID)

Data Variables:

• userName - Customer's full name
• registrationName - Name of registration
• seasonName - Season name and dates
• categoryName - Registration category
• paymentDate - Date payment was processed (formatted as localized date string)
• paymentAmount - Amount charged (formatted as "25.00")
• installmentNumber - Which installment was paid (2, 3, or 4)
• totalPaid - Total amount paid so far (formatted as "50.00")
• remainingBalance - Remaining balance (formatted as "50.00")
• nextPaymentDate - Next payment date OR "No more payments due"
• dashboardUrl - Link to user dashboard

Template Example:

Hi [userName],

Your payment plan installment has been successfully processed!

PAYMENT DETAILS:
- Registration: [registrationName] ([seasonName])
- Category: [categoryName]
- Payment [installmentNumber] of 4
- Amount Charged: $[paymentAmount]
- Payment Date: [paymentDate]

PAYMENT PLAN STATUS:
- Total Paid: $[totalPaid]
- Remaining Balance: $[remainingBalance]
- Next Payment: [nextPaymentDate]

WHAT YOU CAN DO:
• View payment plan details: [dashboardUrl]
• Pay remaining balance early (no penalty)
• View your registration status

Thank you for your payment!

The Hockey Association Team

Payment Plan Payment Failed Template (LOOPS_PAYMENT_PLAN_PAYMENT_FAILED_TEMPLATE_ID)

Data Variables:

• userName - Customer's full name
• registrationName - Name of registration
• seasonName - Season name and dates
• categoryName - Registration category
• failedAmount - Amount that failed to charge (formatted as "25.00")
• installmentNumber - Which installment failed (2, 3, or 4)
• attemptCount - Current attempt number (e.g., 1, 2, 3)
• maxAttempts - Maximum retry attempts (always 3)
• nextRetryDate - When we'll retry (formatted as localized date string, or "No more retries")
• failureReason - Reason for failure (e.g., "Insufficient funds", "Card declined")
• dashboardUrl - Link to user dashboard

Template Example:

Hi [userName],

We were unable to process your payment plan installment.

PAYMENT ATTEMPT DETAILS:
- Registration: [registrationName] ([seasonName])
- Category: [categoryName]
- Payment [installmentNumber] of 4
- Amount: $[failedAmount]
- Attempt: [attemptCount] of [maxAttempts]
- Reason: [failureReason]

WHAT HAPPENS NEXT:
• We'll automatically retry: [nextRetryDate]
• Your registration remains active regardless of payment status
• You can update your payment method or pay manually

WHAT YOU CAN DO:
• Update payment method: [dashboardUrl]
• Pay remaining balance now to avoid retry attempts
• Contact us if you need payment arrangement assistance

Questions? Reply to this email anytime.

The Hockey Association Team

Admin Payment Plan Failure Template (LOOPS_ADMIN_PAYMENT_PLAN_FAILURE_TEMPLATE_ID)

Sent to every admin (unless they opted out via preferences.emailNotifications.paymentPlanFailures = false) each time a payment plan installment fails to charge. Subject: {testEmailPrefix}Payment Plan Failure: {userName} – {registrationName} (mark the prefix optional).

Data Variables:

• adminName - Admin's full name
• userName - Member whose installment failed
• registrationName - Name of registration
• seasonName - Season name ("N/A" if the registration has no season)
• installmentNumber - Which installment failed (2, 3, or 4)
• totalInstallments - Installments in the plan (4)
• installmentAmount - Amount that failed, e.g. "$25.00"
• failureReason - Stripe's decline reason
• attemptSummary - e.g. "Attempt 2 of 3 failed. 1 automatic retry remaining." or, on the last attempt, a note that retries are exhausted and Run Payments must be used after the member updates their card
• userUrl - Link to the member's admin page
• scheduledDate - Original scheduled date of this installment (DATE column, formatted without timezone shift, e.g. "10/16/2026")
• amountPaid - Total paid so far, e.g. "$162.50"
• remainingBalance - Balance still owed, e.g. "$487.50"
• dashboardUrl - Link to the dashboard footer button (/user)
• testEmailPrefix - Added automatically on every send ([TEST] outside production); use it in the Subject

Template Example:

Hi [adminName],

A payment plan installment failed for [userName].

- Registration: [registrationName] [seasonName]
- Installment: [installmentNumber] of [totalInstallments]
- Amount: [installmentAmount]
- Reason: [failureReason]
- Scheduled date: [scheduledDate]
- Paid so far: [amountPaid]
- Remaining balance: [remainingBalance]

[attemptSummary]

View member: [userUrl]

Payment Plan Completed Template (LOOPS_PAYMENT_PLAN_COMPLETED_TEMPLATE_ID)

Data Variables:

• userName - Customer's full name
• registrationName - Name of registration
• seasonName - Season name and dates
• categoryName - Registration category
• totalAmount - Total amount paid (formatted as "100.00")
• completionDate - Date plan was completed (formatted as localized date string)
• dashboardUrl - Link to user dashboard

Template Example:

Hi [userName],

Congratulations! Your payment plan has been completed successfully.

PAYMENT PLAN SUMMARY:
- Registration: [registrationName] ([seasonName])
- Category: [categoryName]
- Total Amount Paid: $[totalAmount]
- Completion Date: [completionDate]

YOUR REGISTRATION:
• Fully paid and confirmed
• No further payments due
• View registration: [dashboardUrl]

Thank you for completing your payment plan!

The Hockey Association Team

Email Change Confirmed Template (LOOPS_EMAIL_CHANGE_CONFIRMED_TEMPLATE_ID)

Data Variables:

• firstName - User's first name (formatted as "FirstName ([email protected])" for admin notifications)
• oldEmail - Previous email address
• newEmail - New email address
• googleAuthWarning - Warning message if Google OAuth email differs from new email (empty string if no mismatch)
• supportEmail - Support contact email
• organizationName - Organization name

Recipients:

• User's old email address (confirmation of change)
• User's new email address (confirmation of change)
• SUPPORT_EMAIL (admin notification)

Template Example:

Hi [firstName],

Your email address has been successfully updated.

EMAIL CHANGE SUMMARY:
- Previous Email: [oldEmail]
- New Email: [newEmail]

[googleAuthWarning]

If you did not make this change, please contact us immediately at [supportEmail].

Thank you,
[organizationName]

Note on googleAuthWarning variable: This variable will be empty if the user doesn't have Google OAuth linked, or if their Google email matches the new email. When present, it contains:

IMPORTANT: Your Google account ([email protected]) is still linked to this account but uses a different email address. If you no longer want to sign in with Google, you can unlink it in your Account Settings under "Account Security".

4. Template Management Guidelines

🚨 IMPORTANT: When adding new email functionality to the application:

1. Add the email event type to EMAIL_EVENTS in src/lib/email/service.ts
2. Create the email function (e.g., sendRegistrationConfirmation())
3. Add environment variable for template ID (e.g., LOOPS_REGISTRATION_TEMPLATE_ID)
4. Update this README.md with template setup instructions and data variables
5. Test the email with sample data to ensure all variables work correctly

This ensures all email templates are properly documented and maintainable.

5. Custom SMTP Configuration (Optional)

By default, Supabase handles authentication emails (magic links, password resets) using their email service. For a more professional experience with custom branding and domain, you can configure custom SMTP using Google Workspace.

Setting Up Google Workspace SMTP

Prerequisites:

• Google Workspace account with admin access
• Custom domain (e.g., nycpha.org)
• Email address for sending auth emails (e.g., [email protected])

Step 1: Create App Password

1. Go to Google Account Security
2. Enable 2-Step Verification (required for app passwords)
3. Go to App passwords section
4. Generate an app password for "Mail"
5. Copy the 16-character password (without spaces: abcd1234efgh5678)

Step 2: Configure Supabase SMTP

1. Go to Supabase Dashboard → Authentication → Settings

2. Scroll down to SMTP Settings

3. Enable Custom SMTP toggle

4. Configure these settings:

   SMTP Host: smtp.gmail.com
   SMTP Port: 587
   SMTP User: [email protected]
   SMTP Pass: abcd1234efgh5678 (app password - no spaces)
   Sender Email: [email protected]
   Sender Name: NYC PHA
   

5. Click Save

Step 3: Add Email Logo (Optional)

1. Host your logo at https://yourdomain.com/images/logo-email.png

2. Go to Authentication → Email Templates

3. Customize templates with your logo:

   <div style="margin-bottom: 30px;">
     <img src="https://yourdomain.com/images/logo-email.png" 
          alt="Your Organization Logo" 
          style="max-width: 240px; height: auto; display: block;">
   </div>
   
   <h2>Welcome to your account!</h2>
   <p>Please click <a href="{{ .SiteURL }}/auth/magic-confirm?token_hash={{ .TokenHash }}&type=magiclink">this link</a> to log in.</p>
   
   <br><br>
   
   <div>
     <img src="https://yourdomain.com/images/logo-email.png" 
          alt="Your Organization Logo" 
          style="max-width: 240px; height: auto; display: block;">
   </div>
   

Keep the token_hash/type link shape from Magic-Link Email Template when you customize — a branded template that falls back to {{ .ConfirmationURL }} breaks magic-link sign-in. Put the branded version in supabase/auth-templates/magic-link.html and re-run npm run auth:apply, so the next person's auth:apply doesn't reset your work.

Step 4: Test Configuration

1. Try logging in with a magic link
2. Check that emails are sent from your custom domain
3. Verify logo appears correctly in email templates
4. Test with different email clients (Gmail, Outlook, etc.)

Benefits of Custom SMTP:

• ✅ Professional branding - Emails sent from your domain
• ✅ Custom logo - Add your organization's visual identity
• ✅ Better deliverability - Google Workspace reputation
• ✅ Consistent experience - Matches your organization's communications

Troubleshooting SMTP:

• Authentication failed: Ensure app password has no spaces
• Port issues: Use port 587 with STARTTLS (not 465 with SSL)
• Logo not showing: Check image URL is publicly accessible
• Emails not sending: Verify sender email exists in Google Workspace

6. Testing Email Integration

1. Complete the Loops setup above
2. If using custom SMTP, test the magic link authentication
3. Make a test membership purchase in your application
4. Check the email_logs table in Supabase to verify delivery status
5. Confirm the email was received with proper variable substitution
6. Test both authentication emails (via SMTP) and transactional emails (via Loops)

7. Never Fire-and-Forget an Email (lint rule)

On Vercel the function is frozen as soon as a route returns, so any email promise left floating after the response is silently dropped some of the time (#395, fixed in #398). The local ESLint rule local/no-floating-email-send (scripts/eslint-rules/no-floating-email-send.js) fails npm run lint (and CI) when one of these is neither awaited, returned, nor handed to something that owns it:

• email notifier calls: stage*Notification(s) / stage*Email(s), send*Notification(s) / send*Confirmation / send*Email(s), sendEmailImmediately, stageEmail, logEmailToDatabase, and any emailService.send* / emailService.stage* / emailStagingManager.* / emailProcessor.* send or stage call
• email_logs writes: .from('email_logs').insert(...) / .upsert(...)

It applies to src/app/api/**/route.ts, src/lib/email/**, src/lib/services/** and src/lib/**/*processor*.ts. It's a syntactic check, so it stays quiet about everything else.

How to comply:

// Needed before responding: await it
await emailService.sendWaitlistAddedNotification({ ... })

// Post-response work: schedule it so Vercel keeps the invocation alive
runAfterResponse('my-notifications', async () => {
  await Promise.all([
    stageCaptainRosterChangeNotification(...).catch((err) => logger.logSystem(..., 'warn')),
    stageAdminNewRegistrationNotification(...),
  ])
})

A .catch() on its own doesn't count: the promise still floats. Assigning the promise to a variable is treated as handled, so make sure you await it later.

Escape hatch (rare; always say why):

// eslint-disable-next-line local/no-floating-email-send -- <reason this is safe>

Error Monitoring Setup (Sentry)

The application uses Sentry for error monitoring and alerting, particularly for critical payment issues where payments succeed but database operations fail.

1. Create Sentry Project

1. Sign up at https://sentry.io
2. Create Organization (if needed)
3. Create one Next.js project:
   • membership-system - Single project using environments

2. Configure Environment Variables

Both Development & Production:

NEXT_PUBLIC_SENTRY_DSN=your_sentry_dsn_here
SENTRY_ORG=your_sentry_org
SENTRY_PROJECT=membership-system

Production/CI only (used at build time to upload source maps for readable stack traces; not needed for local dev):

SENTRY_AUTH_TOKEN=your_sentry_auth_token_here

Generate this token from Sentry's Settings → Auth Tokens with the Source Map Upload and Release Creation scopes. Without it, builds still succeed, but Sentry stack traces show minified code instead of your original source.

The system automatically uses NODE_ENV to separate development and production events into different environments within the same project.

3. How to Report Errors (and Why It's Reliable on Vercel)

There are two ways to send an error to Sentry, and a rule about a third:

• logger.error(...) / the category helpers (logger.logPaymentProcessing(..., 'error'), logXeroSync, logSystem, etc. — see src/lib/logging/logger.ts) — the default choice for normal error logging. 'error'-level calls are automatically reported to Sentry; info/warn/debug are not, unless the call site explicitly uses logger.reportWarningToSentry(...). When you're in a catch block and have the real caught value, pass it as the last argument so Sentry gets the real stack/type instead of a synthetic one built from the message string:

  } catch (error) {
    logger.logPaymentProcessing(
      'operation-name',
      'Human-readable message',
      { paymentIntentId, error: error instanceof Error ? error.message : String(error) }, // for console/file/admin-log display
      'error',
      error // the real caught value, for Sentry - can be omitted if there isn't one (e.g. a business-rule check with no exception)
    )
  }
  

  metadata.error (the string) and the trailing error argument (the real value) serve different consumers — don't conflate them. Setting metadata.error to the raw error object instead of .message will silently blank it out in console/file/admin-log output, since JSON.stringify(new Error(...)) serializes to {}.

• src/lib/sentry-helpers.ts (captureSentryError, captureSentryMessage, and the payment/account-deletion wrappers built on them) — use this instead of the logger when you need automatic user/session context (it looks up the current Supabase user and profile) or request context (browser/OS/device/IP) attached to the event. This is what the two "payment succeeded but database write failed" call sites use, since that's the highest-stakes error path in the app.

• Never call Sentry.captureException/captureMessage directly. Always go through one of the two paths above. If you have a specific reason you must call the Sentry SDK directly, call scheduleSentryFlush() from src/lib/sentry-flush.ts immediately afterward.

Why the last rule matters: Sentry.captureException/captureMessage only enqueue an event on an internal transport; the actual network send happens asynchronously and isn't awaited by those calls. On Vercel, the serverless function is frozen as soon as the response is sent - if the send hasn't completed by then, it's silently cut off and the event never arrives, no error thrown. This isn't hypothetical: it's what caused #368, where logger.error calls in the Stripe webhook routinely never reached Sentry. scheduleSentryFlush() calls Sentry.flush() (which waits for delivery) inside Next.js's after() (which keeps the invocation alive long enough for that wait, without delaying the response itself). Both logger.ts and sentry-helpers.ts already do this for you - it's only direct Sentry.* calls that need to opt in manually.

4. Critical Error Scenarios Monitored

The system automatically captures and alerts on:

• Payment succeeded but membership creation failed - Critical business issue
• Database connection failures during payment processing
• Email delivery failures after successful purchases
• Stripe API errors and timeout issues

5. Alert Configuration

Development Environment: Minimal alerts for testing Production Environment:

• Email/Slack notifications for critical errors
• Real-time monitoring of payment inconsistencies
• Performance tracking for payment operations

6. Testing Error Monitoring

1. Test with payment failure cards to verify error capture
2. Check Sentry dashboard for error events
3. Verify alert notifications are working properly

Testing the Payment System

The application uses Stripe in test mode for development. Use these test card numbers to simulate different scenarios:

Test Credit Card Numbers

Successful Payments:

• Visa: 4242 4242 4242 4242
• Visa (debit): 4000 0566 5566 5556
• Mastercard: 5555 5555 5555 4444
• American Express: 3782 822463 10005

Payment Failures:

• Generic decline: 4000 0000 0000 0002
• Insufficient funds: 4000 0000 0000 9995
• Lost card: 4000 0000 0000 9987
• Stolen card: 4000 0000 0000 9979
• Expired card: 4000 0000 0000 0069
• Incorrect CVC: 4000 0000 0000 0127
• Processing error: 4000 0000 0000 0119

For All Test Cards:

• Expiry Date: Use any future date (e.g., 12/34)
• CVC: Use any 3-digit number (e.g., 123)
• ZIP Code: Use any 5-digit number (e.g., 12345)

Testing Different Scenarios

1. Successful Purchase: Use 4242 4242 4242 4242 to test the complete flow and email delivery
2. Payment Failure: Use 4000 0000 0000 0002 to test error handling and user notifications
3. Email Delivery: Check your email_logs table in Supabase after each test
4. Toast Notifications: Verify success/error messages appear correctly

Stripe Link Testing

The application supports Stripe Link for one-click payments:

• Use test card 4242 4242 4242 4242 with email [email protected]
• Complete the first purchase to set up Link
• Subsequent purchases will offer one-click Link payments

Project Structure

src/
├── app/                 # Next.js app directory
├── components/          # React components
├── lib/                # Utilities and services
│   ├── email/
│   │   ├── processor.ts # Email processing for payment completion
│   │   ├── service.ts # Email integration service
│   │   └── staging.ts # Email staging and batch processing
│   ├── supabase.ts     # Database client
│   └── stripe.ts       # Payment processing
├── proxy.ts            # Authentication proxy (middleware)
└── types/              # TypeScript type definitions

Testing

The application uses Jest with TypeScript support for comprehensive unit and integration testing.

Test Framework Setup

Configuration:

• Test Runner: Jest with ts-jest preset
• Environment: Node.js environment for API testing
• Module Mapping: @/ aliases resolve to src/ directory
• Coverage: Comprehensive coverage reporting with HTML output

Key Files:

• jest.config.js - Jest configuration with TypeScript support
• jest.setup.js - Global test setup and mocks
• src/__tests__/ - Test files organized by feature

Setting Up Jest (First Time Setup)

If you're setting up Jest for the first time or encountering issues, follow these steps:

1. Install Jest Dependencies

# Install Jest and TypeScript support
npm install --save-dev jest ts-jest @types/jest

# Install additional testing utilities (optional)
npm install --save-dev @testing-library/jest-dom

2. Create Jest Configuration

Create jest.config.js in your project root:

/** @type {import('jest').Config} */
module.exports = {
  preset: 'ts-jest',
  testEnvironment: 'node',
  roots: ['<rootDir>/src'],
  testMatch: [
    '<rootDir>/src/**/__tests__/**/*.{js,jsx,ts,tsx}',
    '<rootDir>/src/**/*.{test,spec}.{js,jsx,ts,tsx}'
  ],
  moduleNameMapper: {
    '^@/(.*)$': '<rootDir>/src/$1',
  },
  setupFilesAfterEnv: ['<rootDir>/jest.setup.js'],
  collectCoverageFrom: [
    'src/**/*.{js,jsx,ts,tsx}',
    '!src/**/*.d.ts',
    '!src/**/__tests__/**',
    '!src/**/node_modules/**',
  ],
  coverageDirectory: 'coverage',
  coverageReporters: ['text', 'lcov', 'html'],
  transform: {
    '^.+\\.(ts|tsx)$': 'ts-jest',
  },
  moduleFileExtensions: ['ts', 'tsx', 'js', 'jsx', 'json'],
  testTimeout: 10000,
  verbose: true,
}

3. Create Jest Setup File

Create jest.setup.js in your project root:

// Mock environment variables
process.env.NEXT_PUBLIC_SUPABASE_URL = 'http://localhost:54321'
process.env.NEXT_PUBLIC_SUPABASE_ANON_KEY = 'test-anon-key'
process.env.SUPABASE_SERVICE_ROLE_KEY = 'test-service-role-key'

// Mock Next.js modules that aren't available in test environment
jest.mock('next/headers', () => ({
  cookies: jest.fn(() => ({
    get: jest.fn(),
    set: jest.fn(),
    delete: jest.fn(),
  })),
}))

jest.mock('next/navigation', () => ({
  useRouter: jest.fn(() => ({
    push: jest.fn(),
    replace: jest.fn(),
    back: jest.fn(),
  })),
  useSearchParams: jest.fn(() => ({
    get: jest.fn(),
  })),
}))

4. Update Package.json Scripts

Add test scripts to your package.json:

{
  "scripts": {
    "test": "npx jest",
    "test:watch": "npx jest --watch",
    "test:coverage": "npx jest --coverage"
  }
}

5. Common Setup Issues & Solutions

Issue: "moduleNameMapping" validation warning

• Solution: Use moduleNameMapper (not moduleNameMapping) in jest.config.js

Issue: Cannot find module '@/lib/...' errors

• Solution: Ensure moduleNameMapper is correctly configured with '^@/(.*)$': '<rootDir>/src/$1'

Issue: WSL/Windows path compatibility problems

• Solution: Run tests in WSL terminal instead of Windows PowerShell
• Use npx jest in package.json scripts instead of direct jest command

Issue: Permission errors during npm install

• Solution: Use npx jest instead of global jest installation
• Ensure you're running in the correct directory with proper permissions

Issue: TypeScript compilation errors in tests

• Solution: Ensure ts-jest is installed and configured in jest.config.js
• Check that your tsconfig.json includes the test directories

Issue: Cannot find module '@jest/globals' errors

• Solution: Remove @jest/globals imports from test files
• Jest globals (describe, it, expect, jest, beforeEach) are available automatically
• Change import { describe, it, expect, jest } from '@jest/globals' to just import your actual modules

6. Verify Setup

Test your Jest configuration:

# Run Jest to verify setup
npm test

# Should show: "No tests found" or run existing tests
# If you see configuration errors, review the steps above

Running Tests

# Run all tests
npm test

# Run tests in watch mode
npm run test:watch

# Run tests with coverage report
npm run test:coverage

# Run specific test file
npm test -- user-alternate-registrations.test.ts

# Run tests matching pattern
npm test -- --testNamePattern="should require authentication"

Test Structure

Tests are organized by feature and API endpoint:

src/__tests__/
├── api/
│   ├── admin/
│   │   └── registrations/
│   │       └── alternates.test.ts     # Admin alternate management
│   └── user-alternate-registrations.test.ts  # User alternate registration
└── components/                        # Component tests (future)

Current Test Coverage

API Endpoints Tested:

• Admin Alternate Management (/api/admin/registrations/[id]/alternates)
  • Authentication and authorization
  • Input validation and error handling
  • Enable/disable alternate configurations
  • Registration cleanup on disable
• User Alternate Registration (/api/user-alternate-registrations)
  • User authentication requirements
  • Duplicate registration prevention
  • Payment method validation
  • Registration retrieval

Test Categories:

• ✅ Authentication: Ensures proper user authentication
• ✅ Authorization: Verifies admin privilege requirements
• ✅ Validation: Tests input validation and error responses
• ✅ Business Logic: Validates core functionality
• ✅ Error Handling: Tests edge cases and failure scenarios

Mock Configuration

The test suite includes comprehensive mocking for external dependencies:

Supabase Mocking:

// Mocked database operations
const mockSupabase = {
  from: jest.fn(() => ({
    select: jest.fn().mockReturnThis(),
    insert: jest.fn().mockReturnThis(),
    update: jest.fn().mockReturnThis(),
    delete: jest.fn().mockReturnThis(),
    eq: jest.fn().mockReturnThis(),
    single: jest.fn()
  }))
}

Service Mocking:

• SetupIntentService: Payment method validation
• Logger: Application logging
• Authentication: User session management

Writing New Tests

When adding new API endpoints or features:

1. Create test file in appropriate __tests__ directory

2. Follow naming convention: feature-name.test.ts

3. Include test categories:

   describe('/api/your-endpoint', () => {
     describe('POST - Create resource', () => {
       it('should require authentication', async () => {
         // Test authentication requirement
       })
       
       it('should validate required fields', async () => {
         // Test input validation
       })
       
       it('should successfully create resource', async () => {
         // Test successful operation
       })
     })
   })
   

4. Mock external dependencies using Jest mocks

5. Test both success and failure scenarios

6. Verify proper error responses and status codes

Test Environment Variables

Tests use mocked environment variables configured in jest.setup.js:

process.env.NEXT_PUBLIC_SUPABASE_URL = 'http://localhost:54321'
process.env.NEXT_PUBLIC_SUPABASE_ANON_KEY = 'test-anon-key'
process.env.SUPABASE_SERVICE_ROLE_KEY = 'test-service-role-key'

Best Practices

• Isolation: Each test should be independent and not rely on other tests
• Mocking: Mock external services to ensure tests run reliably
• Descriptive Names: Use clear, descriptive test names that explain the expected behavior
• Edge Cases: Test both happy path and error scenarios
• Authentication: Always test authentication and authorization requirements
• Cleanup: Ensure tests clean up any side effects

Future Testing Enhancements

Planned additions to the test suite:

• Component Testing: React component unit tests
• Integration Testing: End-to-end API workflow tests
• Performance Testing: Load testing for critical endpoints
• Database Testing: Direct database operation testing
• Email Testing: Email delivery and template testing

Key Services

• Email Service (src/lib/email/service.ts): Handles all email communications
• Supabase Client (src/lib/supabase.ts): Database operations and auth
• Stripe Integration (src/lib/stripe.ts): Payment processing
• Sentry Integration (src/lib/sentry-helpers.ts): Error monitoring and alerting

Development Workflow

1. Make Changes: Edit code and test locally
2. Test Email: Use test purchases to verify email delivery
3. Check Logs: Monitor email_logs table for delivery status
4. Commit Changes: Use descriptive commit messages

Issues, Bugs, and Feature Requests

All issue tracking goes through GitHub Issues.

Open an issue to report a bug, propose a feature, or track work in progress. Design discussion belongs in the issue thread and its linked pull requests, so that tracking, review, and history stay in one place. Don't add planning or feature-spec markdown files to the repo for this purpose — the docs/ directory is for durable reference documentation (architecture, setup guides, standards, troubleshooting), not for tracking work.

Deployment

Vercel Deployment (Recommended)

The application is designed to deploy on Vercel with optimal Next.js integration and built-in cron job support for Xero token management.

Prerequisites

• Vercel account (free tier available)
• GitHub/GitLab/Bitbucket repository
• All services configured (Supabase, Stripe, Loops.so, etc.)

Step 1: Install Vercel CLI

npm install -g vercel

Step 2: Login to Vercel

vercel login

Step 3: Deploy from Repository

# From your project directory
vercel --prod

Follow the prompts:

• Project name: Choose a name for your project
• Directory: Use current directory (default)
• Settings: Accept defaults for Next.js project

Step 4: Configure Environment Variables

In your Vercel dashboard, add all environment variables from your .env.local. Replace your-domain with:

• Staging/Testing: membership-system-nycpha-preview
• Production: membership-system-nycpha

Application Configuration:

NEXT_PUBLIC_SITE_URL=https://your-domain.vercel.app
CRON_SECRET=your_random_cron_secret_for_cron_jobs

Database & Authentication:

NEXT_PUBLIC_SUPABASE_URL=your_supabase_project_url
NEXT_PUBLIC_SUPABASE_ANON_KEY=your_supabase_anon_key
SUPABASE_SERVICE_ROLE_KEY=your_supabase_service_role_key

Google OAuth is configured in the Supabase Dashboard (Authentication → Providers), not via app environment variables.

Note: For Supabase API keys, use the new API keys (not legacy) from your production Supabase project (not development). Go to Settings → API → "API Keys" tab and create new keys if needed.

Payment Processing:

STRIPE_SECRET_KEY=rk_live_your_stripe_restricted_key
NEXT_PUBLIC_STRIPE_PUBLISHABLE_KEY=pk_live_your_stripe_live_publishable_key
STRIPE_WEBHOOK_SECRET=whsec_your_production_webhook_secret
STRIPE_API_VERSION=2026-09-30.endive

Stripe restricted API key (STRIPE_SECRET_KEY)

STRIPE_SECRET_KEY is a restricted key (rk_test_... / rk_live_...), not a standard sk_... secret key. Create it in Stripe Dashboard → Developers → API keys → Create restricted key, start from the One-time payments template, and make sure it has at least the permissions below. Use one key per Stripe mode and per Vercel environment. The app reads it only server-side, in src/lib/stripe/server-client.ts. NEXT_PUBLIC_STRIPE_PUBLISHABLE_KEY (pk_...) stays a normal publishable key. Elements uses it in the browser through src/lib/stripe-client.ts.

Resource	Permission	Why the app needs it
PaymentIntents	Write	Create membership and registration payments, plus off-session charges for payment plans, waitlist and alternates. Retrieve intents in confirm routes, Xero invoice creation and the webhook.
SetupIntents	Write	Save a card for later (/api/create-setup-intent, /api/confirm-setup-intent)
Customers	Write	Create the Stripe customer when a card is saved. Delete it on account deletion.
PaymentMethods	Write	Retrieve card details. Attach saved cards. Detach on card removal or account deletion. List cards on account deletion.
Refunds	Write	Admin refunds (/api/admin/refunds, /api/admin/refunds/process)
Charges	Read	The webhook retrieves charges, including expanded latest_charge and refunds.
Balance transactions	Read	The webhook reads Stripe fees for the Xero fee sync (balanceTransactions.retrieve, expand: balance_transaction).

Not needed: Subscriptions, Invoices, Prices, Products, Checkout Sessions, Events, Webhook Endpoints, Payouts, Disputes and Connect. Leave them as None unless a template sets them. Webhook signature verification (webhooks.constructEvent) uses STRIPE_WEBHOOK_SECRET, not an API permission. Xero sync and scripts/ make no Stripe API calls of their own. stripe listen (local testing) uses your Stripe CLI login, not this key.

Rotating or replacing the key:

1. In Stripe, roll the old standard key with a short expiry (for example, 1 hour) so it stays valid until the deploy finishes.
2. Create the restricted key (see the table above) in test mode for Development/Preview and in live mode for Production.
3. In Vercel → Project → Settings → Environment Variables, set STRIPE_SECRET_KEY separately for each environment. Never commit or paste key values.
4. Redeploy each environment. Env changes only apply to new deployments.
5. On Preview, test checkout (new card), saving a card, a payment-plan charge (off-session), an admin refund, and a webhook delivery that records the Stripe fee and syncs to Xero.
6. Watch Vercel logs and Sentry for Stripe permission errors, for example The provided key 'rk_...' does not have the required permissions for this endpoint. If one appears, add the named permission to the key (Dashboard → key → Edit). There's no need to create a new key.
7. Let the old key expire, or revoke it once everything is confirmed.

Webhook signing secret (STRIPE_WEBHOOK_SECRET): each webhook endpoint has its own signing secret (whsec_...), so each environment and endpoint needs its own value. To roll it: open Stripe Dashboard → Webhooks → endpoint → Roll secret, choose an expiry for the old secret, set the new value in that Vercel environment, redeploy, and confirm a test event returns 200.

Email & Monitoring:

LOOPS_API_KEY=your_loops_api_key
LOOPS_EMAIL_BATCH_DELAY_MS=1000
LOOPS_WELCOME_TEMPLATE_ID=your_welcome_template_id
LOOPS_MEMBERSHIP_PURCHASE_TEMPLATE_ID=your_template_id
LOOPS_MEMBERSHIP_EXPIRING_TEMPLATE_ID=your_membership_expiring_template_id
LOOPS_PAYMENT_FAILED_TEMPLATE_ID=your_payment_failed_template_id
LOOPS_PAYMENT_METHOD_REMOVED_TEMPLATE_ID=your_payment_method_removed_template_id
LOOPS_PAYMENT_PLAN_PRE_NOTIFICATION_TEMPLATE_ID=your_payment_plan_pre_notification_template_id
LOOPS_PAYMENT_PLAN_PAYMENT_PROCESSED_TEMPLATE_ID=your_payment_plan_payment_processed_template_id
LOOPS_PAYMENT_PLAN_PAYMENT_FAILED_TEMPLATE_ID=your_payment_plan_payment_failed_template_id
LOOPS_PAYMENT_PLAN_COMPLETED_TEMPLATE_ID=your_payment_plan_completed_template_id
LOOPS_REGISTRATION_CONFIRMATION_TEMPLATE_ID=your_registration_template_id
LOOPS_WAITLIST_ADDED_TEMPLATE_ID=your_waitlist_template_id
LOOPS_WAITLIST_SELECTED_TEMPLATE_ID=your_waitlist_selected_template_id
LOOPS_ALTERNATE_SELECTION_TEMPLATE_ID=your_alternate_selection_template_id
LOOPS_ALTERNATE_REGISTRATION_CONFIRMATION_TEMPLATE_ID=your_alternate_registration_confirmation_template_id
LOOPS_EMAIL_CHANGE_CONFIRMED_TEMPLATE_ID=your_email_change_confirmed_template_id
LOOPS_REFUND_TEMPLATE_ID=your_refund_template_id
LOOPS_TEAM_MESSAGE_TEMPLATE_ID=your_team_message_template_id
LOOPS_CAPTAIN_ASSIGNMENT_NOTIFICATION_TEMPLATE_ID=your_captain_assignment_template_id
LOOPS_CAPTAIN_REMOVAL_NOTIFICATION_TEMPLATE_ID=your_captain_removal_template_id
LOOPS_CAPTAIN_ROSTER_CHANGE_TEMPLATE_ID=your_captain_roster_change_template_id
LOOPS_ADMIN_NEW_REGISTRATION_TEMPLATE_ID=your_admin_new_registration_template_id
LOOPS_ADMIN_REFUND_TEMPLATE_ID=your_admin_refund_template_id
LOOPS_ADMIN_PAYMENT_PLAN_FAILURE_TEMPLATE_ID=your_admin_payment_plan_failure_template_id
NEXT_PUBLIC_SENTRY_DSN=your_sentry_dsn_here
SENTRY_ORG=your_sentry_org
SENTRY_PROJECT=membership-system
SENTRY_AUTH_TOKEN=your_sentry_auth_token_here

Xero Integration:

Important: Create separate Xero apps for production and preview environments to avoid tenant conflicts.

1. Create Production Xero App:

   • Go to Xero Developer Console
   • Create new app (name it "Your App - Production")
   • Set redirect URI: https://your-production-domain.com/api/xero/callback

2. Create Preview Xero App:

   • Create another app (name it "Your App - Preview")
   • Set redirect URI: https://your-preview-domain.vercel.app/api/xero/callback

# Use production app credentials for production environment
XERO_CLIENT_ID=your_production_xero_client_id_here
XERO_CLIENT_SECRET=your_production_xero_client_secret_here
XERO_REDIRECT_URI=https://your-domain.com/api/xero/callback
XERO_SCOPES=accounting.transactions accounting.contacts accounting.settings offline_access

Preview auth bypass for automated testing

The app's login screen (magic link/OTP, Google OAuth, or passkey) can't be driven by automated testing tools — none of those methods are automatable, and passkeys are broken on preview URLs entirely (RP ID mismatch). GET /api/preview-auth lets an automated tester (e.g. an AI agent driving a browser) obtain a real, valid session on preview deployments only, by signing in as a dedicated test user with signInWithPassword. It 404s immediately (never 401/403/redirects, so it can't be fingerprinted) unless VERCEL_ENV === 'preview' and a matching secret is supplied — it can never activate in production.

Setup (one-time, manual):

1. Generate a secret: openssl rand -hex 32 — this becomes PREVIEW_AUTH_SECRET.
2. In the Vercel dashboard: Project Settings → Environment Variables → add PREVIEW_AUTH_SECRET, PREVIEW_TEST_USER_EMAIL, PREVIEW_TEST_USER_PASSWORD (and optionally PREVIEW_TEST_ADMIN_EMAIL/PREVIEW_TEST_ADMIN_PASSWORD for admin-page testing), checking only the "Preview" environment box. Leave Production unchecked — double-check by filtering the env var list to Production and confirming these keys are absent.
3. Create the matching test user(s) in the preview Supabase project's Auth (confirm you're targeting the preview project, not production — they're separate projects): Dashboard → Authentication → Users → Add user, with "Auto Confirm User" on and a password matching PREVIEW_TEST_USER_PASSWORD.
4. Create the matching row in the app users table (preview project), with onboarding_completed_at set and is_admin set appropriately. Simplest way: log in once as the test user through the real magic-link UI and complete onboarding normally.
5. Rotate PREVIEW_AUTH_SECRET (regenerate, update the Preview-scoped env var, redeploy) any time it might have leaked.
6. Redeploy the preview after adding/changing these — Vercel doesn't retroactively apply new env vars to a deployment that's already running.

Never commit the real secret or test-user password anywhere — .env.example only lists the variable names with blank/placeholder values. The real values live in Vercel's Preview-scoped env vars (encrypted at rest). To let someone else (a teammate, or the automation/LLM they're running) use the bypass, they should pull it themselves rather than have it pasted into chat/Slack/etc.:

1. Get added to the nycpha team on Vercel (Team Settings → Members → Invite) with at least the Developer role — Developers can manage environment variables for Preview/Development, they're just locked out of Production env vars, which is exactly the right level of access for this secret. (Member also works and is broader, but Developer is the minimum needed.)
2. From their own machine: vercel login, then vercel link inside the repo to connect it to nycpha/membership-system, then vercel env pull --environment=preview to write the current values (including PREVIEW_AUTH_SECRET) to a local .env file — never manually retype or forward the value.

"Their own machine" is load-bearing. This needs a vercel CLI that can either reuse a cached login or complete the interactive OAuth flow — neither is available in a fresh Claude Code on the web / cloud session, so an AI agent running there can't pull PREVIEW_AUTH_SECRET itself this way, and therefore can't call /api/preview-auth on its own. Enabling the Vercel MCP connector in that session doesn't help either — it covers project/deployment management, not reading environment variable values. The secret still has to be pulled on a machine with real CLI auth and handed to the agent's session (e.g. as an env var on the environment, or a .env.local it's given), never pasted as plaintext into chat.

Sensitive flag — these vars split into two different tiers:

• PREVIEW_AUTH_SECRET must stay non-Sensitive. Devs/LLMs need to know its value to call the endpoint at all, so it has to remain pull-able via vercel env pull / visible in the dashboard.
• PREVIEW_TEST_USER_PASSWORD and PREVIEW_TEST_ADMIN_PASSWORD should be marked Sensitive. Nobody needs to read these back out — the route reads them server-side and calls signInWithPassword internally, the caller never sees or needs the password itself. They're also real Supabase credentials that could authenticate directly against the Supabase API, not just through this route, so there's no reason to leave them human-readable. Marking a var Sensitive doesn't stop the deployed app from using it, it only stops anyone (including you) from reading the value back out afterward — set it once, and if you ever need to change it, overwrite it blind rather than editing it.
• PREVIEW_TEST_USER_EMAIL / PREVIEW_TEST_ADMIN_EMAIL aren't secrets (they're fake addresses that never receive mail — see setup step 3) and don't need the Sensitive flag either way.

Usage: navigate to https://<preview-url>/api/preview-auth?secret=<PREVIEW_AUTH_SECRET>&role=member (or role=admin) — it sets session cookies and redirects to /dashboard (or /admin). No changes are needed to the normal login flow, and src/proxy.ts is untouched.

Step 5: Update Service Configurations

Supabase Authentication:

1. Go to your Supabase Dashboard → Authentication → URL Configuration
2. Update Site URL to: https://your-domain.vercel.app
3. Add to Redirect URLs:
   • https://your-domain.vercel.app/auth/callback
   • https://your-domain.vercel.app/** (wildcard for all auth flows)

Stripe Webhooks:

1. Go to your Stripe Dashboard → Webhooks
2. Update endpoint URL to: https://your-domain.vercel.app/api/stripe-webhook
3. Copy the new webhook secret to STRIPE_WEBHOOK_SECRET

Google OAuth:

1. Go to Google Cloud Console → Credentials
2. Update authorized redirect URIs to include:
   • https://your-domain.vercel.app/api/auth/callback/google

Xero OAuth:

1. Go to Xero Developer Portal → Your App
2. Update OAuth 2.0 redirect URI to:
   • https://your-domain.vercel.app/api/xero/callback

Step 6: Verify Deployment

Automatic Features (Vercel handles these):

• ✅ Next.js Build: Automatic build optimization
• ✅ Serverless Functions: API routes automatically deployed
• ✅ Cron Jobs: Background processing (Xero sync, email sync, daily housekeeping, payment plans)
• ✅ SSL Certificate: Automatic HTTPS with custom domain support
• ✅ CDN: Global edge network for fast loading

Test Your Deployment:

1. Visit your production URL
2. Test user registration and login
3. Make a test purchase with Stripe test cards
4. Verify email delivery through Loops.so
5. Check Xero integration (if configured)
6. Monitor Sentry for any deployment errors

Step 7: Set Up Custom Domain (Optional)

1. Purchase Domain: Buy domain from your preferred registrar
2. Add to Vercel: In project settings → Domains → Add domain
3. Configure DNS: Update your domain's nameservers to Vercel's
4. Update Environment Variables: Change NEXTAUTH_URL to your custom domain
5. Update Service Callbacks: Update Stripe, Google, and Xero redirect URLs

Deployment Checklist

• All environment variables configured in Vercel dashboard
• Stripe webhooks updated to production URL
• Google OAuth redirect URIs updated
• Xero OAuth redirect URI updated
• Supabase RLS policies tested with production data
• Email templates tested with production environment
• Sentry error monitoring configured for production
• Custom domain configured (if desired)
• SSL certificate active and verified
• Vercel Pro plan activated (required for cron jobs)
• CRON_SECRET environment variable configured
• Cron jobs verified in Vercel dashboard (6 active jobs, matching vercel.json)

Setting up a personal Vercel project for preview deploys

Contributors normally deploy previews through the shared nycpha/membership-system project. If you instead connect a personal/sandbox Vercel project to test a preview deploy, two things will bite you:

Empty placeholder vars silently survive a bulk paste-import. When you first connect a GitHub repo, Vercel auto-creates empty placeholder environment variables for every key it detects in .env.example. A bulk "Import .env" paste into the dashboard silently skips any key that already exists — even when the existing value is empty. The result is that vercel env ls shows every expected name present while several are still blank, and the failure only surfaces much later as a build crash from a module-level guard (Error: Missing Xero environment variables, Error: supabaseUrl is required.), one variable at a time.

Editing the value in place through the dashboard did not reliably persist either. Delete and recreate the specific variable instead, piping the value on stdin so the secret doesn't land in your shell history:

npx vercel env rm KEY_NAME preview -y
printf %s "$KEY_NAME" | npx vercel env add KEY_NAME preview

vercel.json's cron schedules block Hobby-tier deploys. Two of the crons in vercel.json run more often than daily (/api/cron/xero-sync every 5 minutes, /api/cron/email-sync every minute), which Hobby-tier Vercel accounts don't allow. A personal non-Pro project fails at the "Deploying outputs" step — after a full successful build, so every retry burns the build time — with:

Error: Hobby accounts are limited to daily cron jobs. This cron expression (*/5 * * * *)
would run more than once per day. Upgrade to the Pro plan to unlock all Cron Jobs features on Vercel.

vercel deploy --local-config <crons-free-vercel.json> does not work around this; the remote build re-reads the real vercel.json from the uploaded source tree regardless.

Don't edit vercel.json to get around it. Dirtying a tracked file to make a deploy succeed is easy to commit by accident, and you don't need the crons anyway: every scheduled job except cleanup is also triggerable from the admin UI on a running preview deployment, which is the better way to exercise them.

Cron path	Schedule	Manual trigger
/api/cron/xero-sync	*/5 * * * *	Admin dashboard (/admin) → Sync Invoices and Payments
/api/cron/email-sync	* * * * *	Admin dashboard (/admin) → Sync Emails
/api/cron/sync-xero-accounts	3 2 * * *	Admin dashboard (/admin) → Sync Accounting Codes, or /admin/xero-integration → Accounts
/api/cron/payment-plans	6 2 * * *	Payment Plans report (/admin/reports/payment-plans) → Run Payments
/api/cron/membership-reminders	9 2 * * *	Membership Reports (/admin/reports/memberships) → Send Reminder Emails Now
/api/cron/daily-housekeeping	0 2 * * *	No UI trigger — call the endpoint directly with Authorization: Bearer $CRON_SECRET

So: deploy to a Hobby project only if you actually need the crons registered (you almost certainly don't — upgrade the project to Pro in that case), and otherwise validate the preview against the shared team project, or exercise the jobs through the admin UI above.

Membership expiration reminders. /api/cron/membership-reminders emails a member when their membership's valid_until is exactly 30, 14, 7, or 1 days away (MEMBERSHIP_REMINDER_THRESHOLDS_DAYS in src/lib/services/membership-reminder-processor.ts). These are hardcoded, not admin-configurable, and matched by strict equality rather than <= — the same trick the payment-plan pre-notification job uses instead of a dedup table. That means a member gets at most one reminder per threshold, but it also means a day the cron doesn't run is a reminder that's simply never sent (no catch-up on the next run). Keep that in mind before changing the schedule or the threshold list.

Setting Up Vercel Cron Jobs (Pro Plan Required)

The application uses Vercel Cron jobs for background processing. Vercel Pro plan is required for the advanced cron job scheduling used in this system.

1. Upgrade to Vercel Pro:

1. Go to your Vercel account settings
2. Upgrade to Pro plan ($20/month)
3. Wait 5-10 minutes for the upgrade to propagate

2. Configure CRON_SECRET Environment Variable:

1. Go to your Vercel project dashboard

2. Navigate to Settings → Environment Variables

3. Add CRON_SECRET with a random string value

4. Generate a secure random string:

   openssl rand -base64 32
   

5. Set the value for Production environment

3. Verify Cron Jobs in Vercel Dashboard:

1. Go to Settings → Cron Jobs
2. You should see 6 active cron jobs, matching vercel.json:
   • xero-sync - Every 5 minutes (Xero invoice/payment sync)
   • email-sync - Every minute (staged email processing, limit 100 per batch; transient failures stay pending and retry on the next run)
   • daily-housekeeping - Daily at 2 AM (expire abandoned registrations, abandon stale Xero carts, prune email logs past 90-day retention)
   • sync-xero-accounts - Daily at 2:03 AM (refresh Xero chart of accounts)
   • payment-plans - Daily at 2:06 AM (process scheduled payment plan charges)
   • membership-reminders - Daily at 2:09 AM (email members whose membership expires in exactly 30, 14, 7, or 1 days)

Note: Cron jobs will not appear until the Pro plan is fully active and CRON_SECRET is configured.

2. Verify CRON_SECRET Environment Variable:

• Ensure CRON_SECRET is set in your Vercel environment variables
• This secret is used to authenticate cron job requests
• Generate a random string if not already set

3. Test Cron Jobs (Optional): You can manually test cron jobs using curl:

# Test email sync operations (replace with your domain and secret)
curl -X GET https://your-domain.vercel.app/api/cron/email-sync \
  -H "Authorization: Bearer your-cron-secret"

# Test Xero sync operations
curl -X GET https://your-domain.vercel.app/api/cron/xero-sync \
  -H "Authorization: Bearer your-cron-secret"

# Test daily housekeeping operations
curl -X GET https://your-domain.vercel.app/api/cron/daily-housekeeping \
  -H "Authorization: Bearer your-cron-secret"

4. Monitor Cron Job Execution:

• Check Vercel dashboard → Functions → Cron Jobs for execution logs
• Review application logs for cron job activity
• Monitor admin interface for sync status

Note: Vercel Hobby accounts are limited to daily cron schedules. Because vercel.json declares two sub-daily crons, a Hobby-tier project cannot deploy this repo at all — the deploy fails after the build with Hobby accounts are limited to daily cron jobs. See Setting up a personal Vercel project for preview deploys for how to work with a Hobby project without editing vercel.json.

Monitoring & Maintenance

Vercel Dashboard:

• Monitor deployment logs and function execution
• View usage analytics and performance metrics
• Manage environment variables and domains

Application Health:

• Stripe: Monitor payment processing in Stripe dashboard
• Supabase: Check database performance and connection pools
• Loops.so: Monitor email delivery rates and engagement
• Sentry: Track errors and performance issues
• Xero: Verify automatic invoicing and contact sync

Troubleshooting Common Issues

Build Failures:

• Check environment variables are properly set
• Verify all dependencies are installed
• Review build logs in Vercel dashboard

Runtime Errors:

• Check Sentry for detailed error reports
• Verify database connections and API keys
• Test payment flows with Stripe test cards

Email Issues:

• Verify Loops.so templates and API key
• Check email logs in Supabase email_logs table
• Test with different email providers

Xero Sync Issues:

• Verify OAuth redirect URI matches exactly
• Check Xero token expiration (automatic refresh should work)
• Review sync logs in admin interface

Debugging

The system includes several debugging tools to help troubleshoot issues with user payments, Xero sync, and system operations.

Debug Scripts

Located in scripts/debug/, these scripts help investigate specific issues:

User Invoice and Payment Debugging

debug-user-invoices.js - Investigate a specific user's invoices and payments:

# Usage: node debug-user-invoices.js <user_id>
node scripts/debug/debug-user-invoices.js 79e9a75e-2580-4d56-8d10-d1a6f8542118

What it shows:

• User details (name, email, member ID)
• All payments for the user
• All Xero invoices linked to the user
• All Xero payments linked to the user
• Summary with potential issues (failed syncs, duplicates)

Xero Sync Log Debugging

debug-user-sync-logs.js - Investigate Xero sync operations for a specific user:

# Usage: node debug-user-sync-logs.js <user_id>
node scripts/debug/debug-user-sync-logs.js 79e9a75e-2580-4d56-8d10-d1a6f8542118

What it shows:

• All Xero sync operations for the user's payments
• Failed sync operations and error messages
• Potential duplicate operations (race conditions)
• Timeline of sync events

Environment Setup for Debug Scripts

Before running debug scripts, ensure your environment is configured:

# Set up environment variables
export NEXT_PUBLIC_SUPABASE_URL="your_supabase_url"
export SUPABASE_SERVICE_ROLE_KEY="your_service_role_key"

# Or use .env.local file
source .env.local

Common Debugging Scenarios

Duplicate Xero Invoices:

# Check if a user has duplicate invoice numbers
node scripts/debug/debug-user-invoices.js <user_id>

Failed Xero Sync:

# Check sync logs for failed operations
node scripts/debug/debug-user-sync-logs.js <user_id>

Payment Processing Issues:

# Check all payment and invoice data for a user
node scripts/debug/debug-user-invoices.js <user_id>

Admin Interface Debugging

The admin interface also provides debugging tools:

• /admin/logs - View system logs and error messages
• /admin/xero-integration - Monitor Xero sync status and retry failed operations
• /admin/reports/financial - Review payment and invoice data

Database Debugging

For direct database access, use the Supabase dashboard:

• Table Editor - Browse and query data directly
• SQL Editor - Run custom queries for investigation
• Logs - View real-time database logs

Support

For questions about the codebase or setup process, refer to:

• Planning Document: docs/PLANNING.md for detailed architecture
• Database Schema: supabase/schema.sql for data models
• Email Logs: Check Supabase email_logs table for debugging
• Issues: GitHub Issues for bugs, feature requests, and work in progress

---

Xero Accounting Integration

The system includes automatic Xero integration for seamless accounting and bookkeeping.

📚 Important Resources:

• Xero API Rate Limits - Current rate limits and best practices

Setting Up Xero Integration

1. Create Xero Developer App

1. Go to Xero Developer Portal
2. Sign in with your Xero account
3. Create a new app with these settings:
   • App Type: Web app
   • App Name: "Hockey Association Membership System"
   • Company/Application URL: Your domain (e.g., https://yourdomain.com)
   • OAuth 2.0 redirect URI:
     • Development: http://localhost:3000/api/xero/callback
     • Production: https://yourdomain.com/api/xero/callback
   • Scopes:
     • accounting.transactions - Create and manage invoices
     • accounting.contacts - Create and manage contacts
     • accounting.settings - Read chart of accounts
     • offline_access - Refresh tokens

2. Configure Environment Variables

Add these to your .env.local:

XERO_CLIENT_ID=your_xero_client_id_here
XERO_CLIENT_SECRET=your_xero_client_secret_here
XERO_REDIRECT_URI=http://localhost:3000/api/xero/callback
XERO_SCOPES=accounting.transactions accounting.contacts accounting.settings offline_access

3. Connect to Xero

1. Start your development server: npm run dev
2. Log in as an admin user
3. Navigate to /admin/xero-integration
4. Click "Connect to Xero" and authorize the integration
5. Your Xero organization will now be connected

4. Xero Setup Recommendations

Chart of Accounts Setup:

• MEMBERSHIP - Membership revenue account
• REGISTRATION - Registration revenue account
• DONATION - Donation revenue account
• STRIPE - Stripe bank account for deposit tracking
• STRIPE_FEES - Expense account for processing fees
• DISCOUNT-SCHOLAR - Scholarship discount tracking
• DISCOUNT-BOARD - Board member discount tracking

Bank Account Configuration:

• Set up your Stripe account in Xero's bank accounts
• Use account code STRIPE for automatic payment recording

5. URI Configuration for All Services

Important: These configurations should already be complete for your account, but here's the complete setup for reference.

Google OAuth Configuration

Google Cloud Console → APIs & Services → Credentials → OAuth 2.0 Client ID

Authorized JavaScript origins:

http://localhost:3000
https://membership-system-nycpha-preview.vercel.app
https://my.nycpha.org

Authorized redirect URIs:

http://localhost:3000/api/auth/callback/google
https://membership-system-nycpha-preview.vercel.app/api/auth/callback/google
https://my.nycpha.org/api/auth/callback/google

Supabase Authentication Configuration

Supabase Dashboard → Authentication → URL Configuration

Site URL:

https://my.nycpha.org

Redirect URLs:

https://my.nycpha.org/auth/callback
https://my.nycpha.org/**

For Development Environment:

• Site URL: https://membership-system-nycpha-preview.vercel.app

• Redirect URLs:

  https://membership-system-git-*-nycpha.vercel.app/**
  https://membership-system-nycpha-preview.vercel.app/**
  http://localhost:3000/**
  

Note: The wildcard pattern /** includes /auth/callback, so specific callback URLs are not needed when using wildcards.

Supabase Dashboard → Authentication → Sign In / Providers → Google

1. Click on "Google" in the providers list
2. Enable "Sign in with Google"
3. Add your Google OAuth credentials:

Client ID: Your Google OAuth client ID Client Secret: Your Google OAuth client secret

Where to get Google OAuth credentials:

1. Go to Google Cloud Console
2. Navigate to APIs & Services → Credentials
3. Find your OAuth 2.0 Client ID (or create a new one)
4. Copy the Client ID
5. For Client Secret:
   • If you have an existing secret: You cannot view it again (security feature)
   • Create a new Client Secret: Click "Reset Secret" or "Create New Secret"
   • Copy the new secret immediately (it's only shown once)

Note: You'll need to configure these settings for each Supabase project (development and production databases).

Security Settings:

• DO NOT enable "Skip nonce checks" - This is a security feature that should remain enabled
• Keep all default security settings unless you have a specific reason to change them

Stripe Webhook Configuration

Stripe Dashboard → Webhooks

You need separate webhook endpoints for each environment:

Production Webhook:

https://my.nycpha.org/api/stripe-webhook

Development/Preview Webhook:

https://membership-system-nycpha-preview.vercel.app/api/stripe-webhook

Feature Branch Testing (optional): For testing specific feature branches, you can create additional webhooks:

https://membership-system-git-[branch-name]-nycpha.vercel.app/api/stripe-webhook

Events to Send: Subscribe each endpoint to these ten events. All are handled in src/app/api/stripe-webhook/route.ts except where noted.

Event	What it drives in the code
charge.refunded	Re-fetches the charge with its refunds. For each refund: if it's failed/canceled, releases it (see refund.failed). If our refunds row is already failed/cancelled, it is left alone and no email is sent (out-of-order delivery). Otherwise it marks the row completed (conditional update), moves its Xero credit note and payment from staged to pending for batch sync, and stages the refund notification email. A refund made directly in Stripe gets a new refunds row and an error log asking for a manual Xero credit note.
charge.updated	Once the balance transaction exists, records the Stripe fee on payments.stripe_fee_amount and the matching full-payment xero_payments staging row.
payment_intent.canceled	No handler. Not in the event switch, so it's acknowledged with 200 and ignored. Keep it subscribed only if you want it in the Stripe event log.
payment_intent.payment_failed	Marks the payment failed, for alternate selections and for general payments (user registrations/memberships payment_status: 'failed'), and runs the payment completion processor with trigger_source: 'stripe_webhook_payment_failed' (failure email, cleanup).
payment_intent.succeeded	Marks the payment completed with its Stripe fee and charge ID, then branches on metadata.purpose: waitlist_selection, alternate_selection, payment_plan_installment, payment_plan_early_payoff, or a normal membership/registration purchase (marks it paid and runs the payment completion processor for Xero staging and emails).
payment_method.detached	Clears the saved card from the user (clearSavedPaymentMethod), removes their alternate selections, and stages the "payment method removed" email. A no-op if /api/remove-payment-method already cleared it.
setup_intent.setup_failed	Sets users.setup_intent_status = 'failed'.
setup_intent.succeeded	Saves the card on the user (stripe_payment_method_id, stripe_setup_intent_id, , and when present).

Also handled, but not in the list above: charge.refund.updated uses the same handler as refund.updated. You don't need to subscribe to it: refund.updated already covers the same changes, and subscribing to both just delivers each change twice (harmless, since the handler is idempotent).

Setup: sandbox (test mode) first

1. Stripe Dashboard, in test mode → Developers → Webhooks → Add endpoint.
2. Endpoint URL: https://membership-system-nycpha-preview.vercel.app/api/stripe-webhook (or a feature-branch preview URL, see above).
3. Select the ten events in the table above.
4. After saving, reveal the endpoint's Signing secret and set it as STRIPE_WEBHOOK_SECRET in Vercel → Project → Settings → Environment Variables for Preview/Development. Redeploy so it takes effect. Never commit or paste the value.
5. Verify:
   • Use Send test event on the endpoint (for example refund.updated). The delivery should show HTTP 200. A 400 means the signing secret doesn't match.
   • Or run stripe listen --forward-to localhost:3000/api/stripe-webhook locally and stripe trigger charge.refunded. Use the whsec_... secret that stripe listen prints as your local STRIPE_WEBHOOK_SECRET.
   • End to end: make a test payment, refund it from the admin UI, and check that the refund becomes completed and its credit note moves to pending.

Setup: production (live mode)

1. Switch the Dashboard to live mode → Developers → Webhooks → Add endpoint (or edit the existing one).
2. Endpoint URL: https://my.nycpha.org/api/stripe-webhook.
3. Select the same ten events.
4. Set that endpoint's signing secret as STRIPE_WEBHOOK_SECRET for the Production environment in Vercel, then redeploy.
5. Verify: the endpoint page in Stripe should show recent deliveries returning 200 with no failures. Check the Vercel function logs for /api/stripe-webhook after the first real payment or refund.

Important:

• Each webhook endpoint gets its own signing secret (STRIPE_WEBHOOK_SECRET)
• Use production webhook only with live Stripe keys
• Use development webhook with test Stripe keys

Xero OAuth Configuration

Xero Developer Portal → Your App → OAuth 2.0

Redirect URI:

https://my.nycpha.org/api/xero/callback

Payment Processing & Xero Integration Flow

The system implements a robust, multi-stage payment processing pipeline that ensures data integrity, handles failures gracefully, and provides complete transparency into operations.

Architecture Overview

┌─────────────────┐    ┌─────────────────────┐    ┌─────────────────┐
│   Stripe        │    │  Payment Completion │    │  Xero Batch     │
│   Webhook       │───▶│  Processor          │───▶│  Sync Manager   │
└─────────────────┘    └─────────────────────┘    └─────────────────┘
        │                        │                        │
        ▼                        ▼                        ▼
┌─────────────────┐    ┌─────────────────────┐    ┌─────────────────┐
│  Core Business  │    │  Email Staging &    │    │  Rate Limited   │
│  Records        │    │  Processing         │    │  API Calls      │
└─────────────────┘    └─────────────────────┘    └─────────────────┘

Email Processing Architecture

The system implements a fire-and-forget email processing architecture optimized for serverless environments to ensure fast payment completion responses while maintaining reliable email delivery.

Email Processing Flow

Payment Completion (Fast Response):
┌─────────────────────────────────────┐
│ PaymentCompletionProcessor          │
│ ├─ Phase 1: Xero staging (awaited)  │
│ ├─ Phase 2: Email staging (awaited) │
│ ├─ Phase 3: Email processing (fire-and-forget) │
│ ├─ Phase 4: Xero sync (fire-and-forget) │
│ └─ Phase 5: Discount usage (awaited) │
└─────────────────────────────────────┘

Background Email Processing:
┌─────────────────────────────────────┐
│ EmailProcessingManager              │
│ └─ emailStagingManager.processStagedEmails() │
│    └─ Send emails one by one with delays │
└─────────────────────────────────────┘

Scheduled Tasks (Cron):
┌─────────────────────────────────────┐
│ Cron Endpoints                      │
│ ├─ /api/cron/email-sync            │
│ └─ /api/cron/daily-housekeeping    │
└─────────────────────────────────────┘

Key Design Principles

1. Single Source of Truth

• Only PaymentCompletionProcessor handles staged email processing
• No duplicate processing from background services
• Eliminates race conditions and duplicate emails

2. Fire-and-Forget Processing

• Payment completion doesn't wait for email processing
• Immediate response to users for better UX
• Background failures don't affect payment completion

3. Serverless-Optimized

• No long-running processes or setInterval calls
• All scheduled tasks handled by Vercel cron endpoints
• Removed unused scheduled processing methods

4. Simple Email Processing

• No complex batch job system for emails
• Direct processing of staged emails with delays
• Clear separation between staging and sending

Email Processing Components

Email Staging Manager (src/lib/email/staging.ts)

• Stages emails in database for batch processing
• Handles email delays and rate limiting
• Processes emails one by one with configurable delays

Email Processing Manager (src/lib/email/batch-sync-email.ts)

• Simplified email processing utility
• No complex batch job system
• Direct processing of staged emails

Payment Completion Processor (src/lib/payment-completion-processor.ts)

• Stages confirmation emails during payment completion
• Triggers fire-and-forget email processing
• Ensures emails are queued for immediate delivery

Benefits

1. Fast Payment Response - Payment completion returns immediately
2. No Duplicate Emails - Single source of truth for email processing
3. Better UX - Users get immediate confirmation
4. Error Isolation - Background failures don't affect payment completion
5. Serverless Compatible - No long-running processes
6. Simple Architecture - Easy to understand and maintain

Detailed Flow

1. Payment Initiation

• User completes payment via Stripe
• Payment intent created with metadata (user_id, membership_id, etc.)
• User redirected to success/failure page

2. Stripe Webhook Processing

Event: payment_intent.succeeded or payment_intent.payment_failed

Webhook Handler (src/app/api/stripe-webhook/route.ts):

• ✅ Validates webhook signature for security
• ✅ Updates core business records:
  • user_memberships or user_registrations → payment_status: 'paid'
  • payments → status: 'completed'
• ✅ Triggers Payment Completion Processor for orchestration
• ✅ Handles payment failures with cleanup and notifications

Key Design Principle: Webhook focuses solely on core business record updates and delegates all complex processing to the Payment Completion Processor.

3. Payment Completion Processor

Service: src/lib/payment-completion-processor.ts

Responsibilities:

• ✅ Xero Staging Record Management:
  • Creates/updates xero_invoices and xero_payments staging records
  • Transitions records from 'staged' → 'pending' → 'synced'
  • Handles both new staging and invoice-first flow scenarios
• ✅ Email Orchestration:
  • Stages confirmation emails for batch processing
  • Triggers immediate email sending for critical notifications
• ✅ Batch Sync Triggering:
  • Initiates Xero batch sync operations
  • Ensures proper sequencing of operations

Staging Record Search Logic:

// 1. Search by payment_id (for completed payments)
// 2. Search by user_id in staging_metadata (for staged records)
// 3. Search by xero_invoice_id (for invoice-first flow)
// 4. Handle null payment_id scenarios gracefully

4. Xero Batch Sync Manager

Service: src/lib/xero/batch-sync.ts

Singleton Pattern with Concurrency Protection:

• ✅ Prevents concurrent execution - Only one sync operation at a time
• ✅ Rate limit protection - 2-second minimum delay between syncs
• ✅ Comprehensive logging - Unique call IDs for transparency
• ✅ State management - Tracks running status and timing

Sync Process:

1. Phase 1: Query pending records from database
2. Phase 2: Connect to Xero (with token refresh if needed)
3. Phase 3: Sync invoices (create contacts, generate invoices)
4. Phase 4: Sync payments (record payments against invoices)

Rate Limiting Strategy:

// Minimum 2-second delay between sync operations
private readonly MIN_DELAY_BETWEEN_SYNCS = 2000

// Automatic delay enforcement
if (remainingDelay > 0) {
  console.log(`⏳ Rate limit protection: waiting ${remainingDelay}ms`)
  await new Promise(resolve => setTimeout(resolve, remainingDelay))
}

5. Xero API Integration

Contact Management:

• ✅ Smart contact creation with member ID integration
• ✅ Archived contact handling with intelligent resolution
• ✅ Naming convention: "First Last - MemberID" format
• ✅ Duplicate prevention with name-first search strategy

Invoice Generation:

• ✅ Detailed line items for membership/registration fees
• ✅ Discount code integration as negative line items
• ✅ Professional formatting using Xero templates
• ✅ Status tracking (DRAFT → AUTHORISED)

Payment Recording:

• ✅ Net amount recording (gross minus Stripe fees)
• ✅ Bank account integration with Stripe account codes
• ✅ Reference tracking with payment intent IDs
• ✅ Automatic reconciliation with invoices

Data Flow States

Staging Record Lifecycle

┌─────────┐    ┌──────────┐    ┌─────────┐    ┌─────────┐
│ staged  │───▶│ pending  │───▶│ synced  │───▶│ failed  │
└─────────┘    └──────────┘    └─────────┘    └─────────┘
     │              │              │              │
  Initial        Ready for      Successfully   Error state
  creation       Xero sync      synced to      (retryable)
                 (webhook       Xero
                 triggered)                     

Payment Status Flow

┌─────────────┐    ┌─────────────┐    ┌─────────────┐
│ processing  │───▶│ completed   │───▶│ synced      │
└─────────────┘    └─────────────┘    └─────────────┘
     │                   │                   │
  Payment in          Payment              Xero sync
  progress            successful           complete

Error Handling & Resilience

Graceful Degradation

• ✅ Payment processing continues even if Xero sync fails
• ✅ Email delivery independent of Xero operations
• ✅ Staging records preserved for retry mechanisms
• ✅ Webhook idempotency prevents duplicate processing

Retry Mechanisms

• ✅ Automatic retry via cron jobs every 2 minutes
• ✅ Manual sync options in admin interface
• ✅ Failed record tracking with detailed error logging
• ✅ Token refresh handles Xero authentication expiry

Monitoring & Alerting

• ✅ Sentry integration for critical error tracking
• ✅ Comprehensive logging with unique call IDs
• ✅ Admin dashboard for sync status monitoring
• ✅ Email notifications for failed operations

Performance Optimizations

Batch Processing

• ✅ Intelligent batching based on record count
• ✅ Concurrency control to prevent API rate limits
• ✅ Optimized queries with proper indexing
• ✅ Memory management for large datasets

Rate Limit Management

• ✅ 2-second minimum delays between sync operations
• ✅ API call spacing within batch operations
• ✅ Token refresh optimization to minimize API calls
• ✅ Concurrent execution prevention via singleton pattern

Security Considerations

Data Protection

• ✅ Webhook signature validation prevents unauthorized calls
• ✅ Environment-specific Xero apps prevent tenant conflicts
• ✅ Token encryption in database storage
• ✅ RLS policies protect sensitive data

Access Control

• ✅ Admin-only sync operations require proper authentication
• ✅ Audit logging for all sync operations
• ✅ Error masking prevents sensitive data exposure
• ✅ Rate limiting prevents abuse

Testing & Validation

Test Scenarios

1. Successful Payment Flow: Complete end-to-end processing
2. Payment Failure: Proper cleanup and notifications
3. Xero Sync Failure: Graceful degradation and retry
4. Concurrent Operations: Singleton pattern validation
5. Rate Limiting: Delay enforcement verification
6. Token Expiry: Automatic refresh handling

Monitoring Points

• ✅ Webhook delivery via Stripe dashboard
• ✅ Database record states in Supabase
• ✅ Xero sync status in admin interface
• ✅ Email delivery via Loops.so dashboard
• ✅ Error tracking via Sentry

How Xero Integration Works

Automatic Sync Process

1. Payment Completed → Stripe webhook triggers staging record creation
2. Staging Records → Invoice and payment records created with 'pending' status
3. Cron Job Processing → Every 2 minutes, cron jobs sync eligible records:
   • Invoices: All pending/staged invoices
   • Payments: Only completed, non-free payments
4. Contact Creation → User automatically added as Xero contact
5. Invoice Generation → Detailed invoice created with line items:
   • Membership purchases
   • Registration fees
   • Donations (if applicable)
   • Discount codes (as negative line items)
6. Payment Recording → Net payment recorded (gross amount minus Stripe fees)
7. Fee Tracking → Stripe processing fees optionally recorded as expenses

Sync Criteria

Invoices: All records with sync_status IN ('pending', 'staged')

Payments: Filtered by:

• sync_status IN ('pending', 'staged')
• payments.status = 'completed'
• payments.payment_method != 'free'

This ensures only completed, paid transactions are synced to Xero.

Manual Sync Operations

The admin interface provides manual sync options:

• Sync Contacts - Create Xero contacts for all users who have made payments
• Sync Invoices - Create invoices for all completed payments
• Record Payments - Record Stripe payments in Xero for existing invoices

Financial Benefits

✅ Automated Bookkeeping - Eliminates manual invoice entry ✅ Accurate Fee Tracking - Stripe processing costs automatically recorded ✅ Professional Invoicing - Uses Xero's templates and delivery system ✅ Real-time Sync - Financial data synchronized every 2 minutes ✅ Discount Transparency - Clear breakdown of promotional pricing ✅ Audit Trail - Complete transaction history and error logging ✅ Staging-First Approach - Zero data loss with robust staging system ✅ Intelligent Filtering - Only syncs eligible payments (completed, non-free) ✅ Pro Plan Optimization - Leverages Vercel Pro for high-frequency processing

Contact Management & Conflict Resolution

Xero Contact Constraints:

• ✅ Unique Names Required: Xero enforces unique contact names
• ⚠️ Duplicate Emails Allowed: Xero permits multiple contacts with same email address

Our Contact Strategy:

1. Member ID Integration: All users are assigned unique member IDs (e.g., 1001, 1002)
2. Naming Convention: Contacts created as "First Last - MemberID" (e.g., "David Wender - 1001")
3. Intelligent Archived Contact Handling: Smart detection and resolution of archived contact conflicts
4. Name Standardization: Ensures all contacts follow consistent naming conventions

Improved Contact Search Strategy

Our system uses a name-first search strategy to prevent picking up the wrong contact when multiple contacts exist with the same email:

1. ✅ Search by exact contact name first → Look for "First Last - MemberID" (e.g., "David Wender - 1002")
2. ✅ Fall back to email search → Only if exact name not found, search by email
3. ✅ Prioritize exact matches → Use exact name matches over partial matches
4. ✅ Handle archived contacts → Skip archived contacts, find active alternatives
5. ✅ Create new only if necessary → Create new contact only if no suitable alternatives exist

This prevents the system from picking up the wrong contact when you have multiple contacts with the same email address in Xero.

When Archived Contact is Detected

Our system follows an intelligent 5-step process to handle archived contacts while minimizing duplication:

1. ✅ Search by exact contact name first → Look for "First Last - MemberID"
2. ✅ Fall back to email search → Get all contacts with user's email address
3. ✅ Look for non-archived alternatives → Find contacts that aren't archived
4. ✅ Check naming convention → Does contact name start with "First Last - MemberID"?
5. ✅ Use standardized contact → Apply updates and use for operations
6. ✅ Create new only if necessary → Create new contact only if no alternatives exist

Archived Contact Resolution Scenarios

Scenario 1: Perfect Match Found

• Situation: Find archived "David Wender - 1002", find active "David Wender - 1002"
• Action: ✅ Use existing active contact as-is
• Result: No new contact created, maintains data integrity

Scenario 2: Legacy Contact Found

• Situation: Find archived "David Wender - 1002", find active "David Wender" (old format)
• Action: ✅ Update to "David Wender - 1002" (standardized naming)
• Result: Legacy contact updated to follow naming convention

Scenario 3: Different Member Found

• Situation: Find archived "David Wender - 1002", find active "David Wender - 1001"
• Action: ✅ Use existing "David Wender - 1001" as-is (already correct format)
• Result: Uses different member's contact (same person, different membership)

Scenario 4: Archived Contact with Exact Name

• Situation: Find archived "David Wender - 1002" (exact name match)
• Action: ✅ Rename to "David Wender - 1002 - Archived" then create new "David Wender - 1002"
• Result: Archived contact preserved, new active contact created

Scenario 5: No Alternatives - Create New

• Situation: Find archived "David Wender - 1002", no other active contacts found
• Action: ✅ Create new "David Wender - 1002"
• Result: New contact created with clean naming

Benefits

• ✅ Prevents Contact Duplication: Reduces unnecessary contact creation by 80%+
• ✅ Naming Consistency: All contacts follow "First Last - MemberID" format
• ✅ Legacy Cleanup: Gradually updates old contact names to new standard
• ✅ Archive Respect: Doesn't override business decisions to archive contacts
• ✅ Smart Archived Contact Handling: Renames archived contacts to avoid conflicts
• ✅ Member ID Visibility: Always shows member number in Xero contact name
• ✅ Audit Trail: Clear tracking when multiple contacts needed for same person
• ✅ Clean Contact Creation: New contacts created with proper naming, no timestamps needed

Troubleshooting

Common Issues:

• Token Expired: Tokens refresh automatically, but you can reconnect manually (refresh tokens expire after 60 days)
• Sync Failures: Check the sync logs in the admin interface for detailed error messages
• Missing Invoices: Use the bulk sync feature to catch up on historical data
• Account Codes: Discount codes and memberships will use default codes if not configured
• Archived Contacts: System automatically creates new contacts when encountering archived ones

Error Monitoring:

• All sync operations are logged to Sentry for monitoring
• Detailed sync logs available in admin interface
• Failed syncs don't affect payment processing

Testing Xero Integration

1. Use Xero Demo Company for testing (recommended)
2. Complete a test purchase to verify the full sync workflow
3. Check Xero for created contact, invoice, and payment
4. Verify sync status in admin interface
5. Test bulk sync features with historical data

---

Recent Optimizations & Cleanup

Email Processing Architecture Overhaul (Latest)

Problem Solved: Duplicate email processing and slow payment completion responses due to complex batch job systems.

Changes Made:

• ✅ Removed duplicate email processing - Only PaymentCompletionProcessor handles staged emails
• ✅ Simplified batch processing - Removed complex batch job system for emails
• ✅ Fire-and-forget processing - Payment completion doesn't wait for emails/Xero sync
• ✅ Serverless optimization - Removed unused scheduled processing methods
• ✅ Cleaner codebase - Removed 738+ lines of unused code

Performance Improvements:

• Faster payment completion - Immediate response to users
• No duplicate emails - Single source of truth for email processing
• Better error isolation - Background failures don't affect payment completion
• Simplified architecture - Easier to understand and maintain

Architecture Cleanup

Removed Components:

• ❌ ScheduledBatchProcessor - Unused scheduled processing logic
• ❌ Complex batch job system for emails - Unnecessary complexity
• ❌ Unused Database imports - Cleaned up imports
• ❌ Duplicate email processing paths - Single source of truth

Optimized Components:

• ✅ BatchProcessor - Kept for Xero sync operations (actively used)
• ✅ EmailProcessingManager - Simplified email processing utility
• ✅ PaymentCompletionProcessor - Fire-and-forget architecture
• ✅ Cron endpoints - Handle all scheduled tasks in serverless environment

Current Architecture:

Payment Completion (Fast):
┌─────────────────────────────────────┐
│ PaymentCompletionProcessor          │
│ ├─ Phase 1: Xero staging (awaited)  │
│ ├─ Phase 2: Email staging (awaited) │
│ ├─ Phase 3: Email processing (fire-and-forget) │
│ ├─ Phase 4: Xero sync (fire-and-forget) │
│ └─ Phase 5: Discount usage (awaited) │
└─────────────────────────────────────┘

Scheduled Tasks (Cron):
┌─────────────────────────────────────┐
│ Cron Endpoints                      │
│ ├─ /api/cron/xero-sync             │
│ ├─ /api/cron/email-sync            │
│ └─ /api/cron/daily-housekeeping    │
└─────────────────────────────────────┘

Batch Processing (Xero Only):
┌─────────────────────────────────────┐
│ BatchProcessor                      │
│ └─ Used by Xero sync operations    │
└─────────────────────────────────────┘

---

Tech Stack: Next.js 14, TypeScript, Tailwind CSS, Supabase, Stripe, Loops.so, Xero API