Waitlist and referral page builder

# Waitlist and referral builder

## Purpose

Owners create a waitlist campaign page at /w/:slug. Visitors sign up with an email, get a unique referral link, and move up the list when friends sign up through it. Owners see a leaderboard and export emails as CSV.

## Stack

- Remix with TypeScript
- Postgres 16 via docker compose
- Drizzle ORM with drizzle-kit migrations
- Zod for validation
- Vitest for unit and integration tests
- Playwright for one e2e smoke

## Commands

- `docker compose up -d db`: start local Postgres
- `npm run dev`: dev server on localhost:3000
- `npm run typecheck`
- `npm run lint`
- `npm run test`: Vitest, including integration tests
- `npm run build`
- `npm run test:e2e`: Playwright smoke
- `npm run db:generate`: create a migration from schema changes
- `npm run db:migrate`: apply migrations (asks first)
- `npm run db:seed`: demo campaign (asks first)

## Folder map

- app/routes: Remix routes
- app/db: schema and client
- app/lib/referrals: ranking and codes
- app/lib/auth: session
- app/lib/csv: CSV escaping
- tests/: integration tests
- e2e/: Playwright tests

## Domain rules

- Email is trimmed and lowercased into email_normalized.
- One signup per (campaign_id, email_normalized).
- referral_code is 8 random url-safe characters.
- Position is computed at read time with a SQL window function, ordered by referral_count desc, then created_at asc. Never store it.

## Referral rules

- ?ref=CODE is saved in a cookie for 30 days.
- Self-referral and unknown codes are ignored silently.
- A signup credits only its first referrer.

## Security rules

- Owner routes check the session and that the campaign belongs to the signed-in owner.
- Public leaderboard masks emails as a***@domain.com.
- CSV export prefixes cells starting with = + - @ with a single quote. All cells go through the helper in app/lib/csv.
- Never log full emails.

## Gotchas

- Remix loaders and actions run on the server only. Keep DB imports in .server.ts files.
- Run integration tests against DATABASE_URL_TEST, never the dev database.
- Migrations are generated, never hand-edited after they are applied.
- Never read or print .env. Use placeholders like YOUR_DATABASE_URL and YOUR_SESSION_SECRET in docs and examples.

## Workflow

- Explore, plan, code, then run /verify before saying anything is done.
- Use /clear between unrelated tasks.
- Ask before adding a dependency.
- The phase list is in PLAN.md.
- Skills: verify, referral-logic, db-migration.

## Imports

@SPEC.md
@REVIEW.md

---
name: test-runner
description: Use for running typecheck, lint, tests and build and reporting only the failures, so long test output stays out of the main context.
model: haiku
tools: Bash, Read
maxTurns: 12
---

You run the project's checks and report the results. You do not fix code.

Run these steps in order:
1. `docker compose ps`. Confirm the db service is up. If the database is not reachable, say so and stop.
2. `npm run typecheck`
3. `npm run lint`
4. `npm run test`
5. `npm run build`

Rules:
- Stop at the first failing command. Do not run the later steps.
- Do not edit files.
- Do not run `npm run db:migrate` or `npm run db:seed`.
- Do not read or print `.env`.

For a failure, return:
- the failing command
- the first error line with file:line
- the last 30 lines of relevant output, nothing else

Answer format:
- PASS: list each command that ran and exited 0.
- FAIL: the failing command and the error details as above.

{
  "permissions": {
    "allow": [
      "Bash(npm run typecheck)",
      "Bash(npm run lint)",
      "Bash(npm run test:*)",
      "Bash(npm run build)",
      "Bash(npm run test:e2e)",
      "Bash(npm run db:generate)",
      "Bash(npm run dev)",
      "Bash(docker compose up -d db)",
      "Bash(docker compose ps)",
      "Bash(git status)",
      "Bash(git diff:*)",
      "Bash(git log:*)",
      "Bash(curl -s localhost:*)",
      "Read(./.env.example)"
    ],
    "ask": [
      "Bash(npm install:*)",
      "Bash(npm run db:migrate)",
      "Bash(npm run db:seed)",
      "Bash(git commit:*)",
      "Bash(git push:*)",
      "Bash(docker compose down:*)"
    ],
    "deny": [
      "Read(./.env)",
      "Read(./.env.local)",
      "Read(./.env.development)",
      "Read(./.env.development.local)",
      "Read(./.env.test)",
      "Read(./.env.test.local)",
      "Read(./.env.production)",
      "Read(./.env.production.local)",
      "Bash(rm -rf:*)",
      "Bash(curl:* | sh)",
      "Bash(git push --force:*)",
      "Bash(git reset --hard:*)",
      "Bash(drizzle-kit drop:*)"
    ],
    "defaultMode": "default",
    "blockWritesOutsideWorkingDirectories": true
  },
  "subagentPromptCacheTtl": "1h",
  "env": {
    "NODE_ENV": "development"
  }
}

---
name: db-migration
description: Use for any change to app/db/schema.ts or the migrations, including new columns, constraints, seed data and running migrations against the dev or test database.
disable-model-invocation: false
---

# DB migration

Use this for every schema change. Migrations are generated by drizzle-kit. Never write migration SQL by hand.

## Steps

1. Edit `app/db/schema.ts`. Keep `unique(campaign_id, email_normalized)` on signups and the unique constraint on `referral_code`.
2. Run `npm run db:generate`. A new SQL file appears in the migrations folder.
3. Open the generated SQL and summarize it for the user: tables, columns, constraints, and anything that drops or rewrites data.
4. Ask the user before running `npm run db:migrate`. This command is on the ask list in `.claude/settings.json`, so it needs an explicit yes.
5. After a yes, run `npm run db:migrate` against the local dev database. Then apply the same migration to the test database so integration tests see the new schema. Use the method the project scripts or INSTALL.md describe. If no method is documented, stop and ask.
6. Run `/verify`.

Do not open `.env` to find connection strings. Use the variable names from `.env.example`.

## Rules

- Never edit a migration file that has been applied. Make a new schema change and generate a new migration.
- Destructive changes need an explicit yes from the user in this conversation. These include dropping a table or column, changing a column type, and dropping a unique constraint. Include a backup note in the summary, using a placeholder such as `pg_dump YOUR_DATABASE_URL > backup.sql`.
- Never build SQL by string concatenation, in migrations or in app code.
- Never run `drizzle-kit drop`. It is denied in settings.
- Do not run `npm run db:seed` as part of a migration.

## Seed script

- `npm run db:seed` must be idempotent. Look up the demo campaign slug before inserting, or use an upsert, so repeated runs create nothing new.
- It creates only one demo campaign with fake signups such as `you@example.com` and `you@example.com`.
- Never seed real emails or real owner accounts.
- `db:seed` is an ask-first command. Confirm with the user before running it.

---
name: referral-logic
description: Use when touching signup, referral codes, position ranking, leaderboard or email masking, to apply dedupe, referral crediting and ranking rules and their required tests.
paths:
  - app/lib/referrals/**
  - app/routes/w.*
---

# Referral logic

Load this before editing signup, ranking or leaderboard code. These rules match CLAUDE.md and SPEC.md. If they disagree with the code or with each other, stop and ask.

## Email normalization

- `normalizeEmail(input)` lives in `app/lib/referrals/email.ts`. It trims and lowercases the input and returns the value stored in `email_normalized`.
- `email` keeps the trimmed input for display. `email_normalized` is used only for dedupe and is never shown.
- Validate with Zod after normalizing. Maximum length is 254 characters.

## Referral codes

- Generate codes with `crypto.randomBytes(6).toString('base64url')`. This gives exactly 8 url-safe characters. Never use `Math.random`.
- On a unique violation for `referral_code`, generate a new code and retry. Make at most 3 attempts. After the third failure, fail the signup with a 500 and log the error without the email.

## Signup transaction

Run these steps in one database transaction, in this order:

1. Resolve the ref code only among signups in the same `campaign_id`. Unknown codes and codes from other campaigns are ignored silently. The signup still succeeds.
2. Insert the signup with `email_normalized`. A unique violation on `(campaign_id, email_normalized)` means the person already signed up. Show the existing signup's thanks page. Do not insert a second row and do not credit a second referrer.
3. If the referrer's `email_normalized` equals the new `email_normalized`, treat it as self-referral and ignore it.
4. Otherwise set `referred_by_signup_id` to the referrer's id.

The ref cookie (`?ref=CODE`, 30 days) is read in the signup action. Its value is used only for the code lookup in step 1.

## Ranking

Position is computed at read time and never stored. The thanks page and the leaderboard both use the same query. Do not write a second ranking query.

```sql
select
  s.id,
  s.email_normalized,
  s.created_at,
  count(r.id) as referral_count,
  row_number() over (
    order by count(r.id) desc, s.created_at asc
  ) as position
from signups s
left join signups r on r.referred_by_signup_id = s.id
where s.campaign_id = $1
group by s.id
order by position;
```

- Keep `$1` as a bound parameter. Never build this query by string concatenation.
- Postgres accepts `s.created_at` in the select list because `s.id` is the primary key.
- If two signups share the same `created_at`, add `s.id asc` as a final tie-break and mention it in the summary.
- Position is 1-based. `referral_count` is the number of signups whose `referred_by_signup_id` is this signup.

## Masking

- `maskEmail(email)` returns the first character, then `***`, then `@domain`. Example: `a***@domain.com`.
- Public pages and the public leaderboard always use the masked form.
- The owner dashboard and CSV export show full emails. Only owner-authenticated routes may return them.
- A one-character local part still returns the first character, then `***`, then `@domain`.

## Tests to always cover

Unit tests:
- `normalizeEmail`: different case and surrounding spaces produce the same value.
- `maskEmail`: normal address, one-character local part.
- Code generation: 8 characters, url-safe alphabet.

Vitest integration tests on DATABASE_URL_TEST:
- Duplicate email in a different case yields one signup and no second referral credit.
- Self-referral is ignored.
- A referral code from another campaign is ignored.
- An unknown referral code is ignored and the signup still succeeds.
- A signup credits only its first referrer.
- Ties on referral_count are broken by the earlier `created_at`.
- A referrer overtakes another signup after a new referral, and both positions change.
- The thanks page position matches the leaderboard position for the same signup.

---
name: verify
description: Use before claiming any task is done or before committing, to prove typecheck, lint, tests, build and smoke checks pass for this Remix and Postgres app.
allowed-tools: Bash, Read
---

# Verify

Run this before saying a task is done or before any commit. Never mark a task done on partial success.

## Step 0: Database and config

1. Run `docker compose up -d db`, then `docker compose ps`. The db service must show as running. If Docker is not running, stop and say so.
2. Read `.env.example`. Never read `.env`. If `.env.example` has no `DATABASE_URL_TEST` line, stop and say so. Do not guess a test database URL.

## Step 1: Typecheck

`npm run typecheck`

## Step 2: Lint

`npm run lint`

## Step 3: Tests

`npm run test`

Vitest runs unit tests and integration tests. Integration tests use DATABASE_URL_TEST only. If a test reports the test database as missing, that is a failure, not a skip.

## Step 4: Build

`npm run build`

## Step 5: Smoke

- Phase 1, before the /w/:slug signup flow exists: run `npm run dev` in the background, wait until it listens, then run `curl -s -o /dev/null -w '%{http_code}' localhost:3000/healthz`. The output must be `200`. Stop the dev server afterwards.
- Phase 2 and later: run `npm run test:e2e`.

## Failure handling

- Stop at the first failing step.
- Show the command and only the last 40 lines of its output.
- Fix the problem, then rerun from the failing step.
- After the fix, rerun all steps from Step 1 once at the end.
- If output from Steps 1 to 4 is long, suggest delegating those steps to the test-runner subagent and read its summary.

## Never

- Skip a step silently. If a step cannot run, name it and say why.
- Open, print or edit `.env`.
- Run `npm run db:migrate` or `npm run db:seed` as part of verify.

## Final report

Use this table:

| Step | Command | Result |
|---|---|---|
| 0 | docker compose ps | PASS or FAIL |
| 1 | npm run typecheck | PASS or FAIL |
| 2 | npm run lint | PASS or FAIL |
| 3 | npm run test | PASS or FAIL |
| 4 | npm run build | PASS or FAIL |
| 5 | smoke command for this phase | PASS or FAIL |

Then add one line that says what was not verified, for example manual browser checks or accessibility.

# Install and first run

This guide gets the waitlist app running with Claude Code. Read it once, then follow the daily loop.

## Prerequisites
- Node 20 or newer
- Docker Desktop, running
- Git
- Claude Code 2.1.296 or newer. Check with `claude --version`.

## What each file does
- CLAUDE.md: project memory Claude reads at the start (commands, rules, gotchas).
- SPEC.md: what to build, including open questions.
- PLAN.md: the five phases, each with a /goal prompt.
- REVIEW.md: review rules used by /code-review.
- INSTALL.md: this guide.
- .claude/settings.json: permissions (what runs freely, what asks, what is blocked).
- .claude/skills/: verify, referral-logic and db-migration, loaded when needed.
- .claude/agents/test-runner.md: a cheap helper that runs checks and returns only failures.

## Set up the project
1. Create the project folder, run `git init`, and save the generated files into it.
2. If `.env.example` does not exist yet, create it with three placeholder names: YOUR_DATABASE_URL, YOUR_DATABASE_URL_TEST, YOUR_SESSION_SECRET.
3. Copy the template: `cp .env.example .env`
4. Open `.env` in your editor and fill in local values:
   - YOUR_DATABASE_URL: the local dev database URL
   - YOUR_DATABASE_URL_TEST: a separate database URL for tests
   - YOUR_SESSION_SECRET: generate with `openssl rand -base64 32`
5. Never commit .env. Run `git status` before each commit to confirm it is not staged.

## Install the plugins
The official marketplace is added automatically. Add the skills marketplace, then install the four plugins:

```
claude plugin marketplace add anthropics/skills
claude plugin install feature-dev@claude-plugins-official
claude plugin install code-review@claude-plugins-official
claude plugin install security-guidance@claude-plugins-official
claude plugin install example-skills@anthropic-agent-skills
```

## Start Claude Code
1. Run `cd` into the project folder, then run `claude`.
2. Run `/permissions` and confirm the allow, ask and deny rules from .claude/settings.json are listed.
3. Read SPEC.md and answer its open questions, or tell Claude to pick the simplest option.
4. Paste the Phase 1 /goal prompt from PLAN.md.
5. Watch the first turns. Press Esc to interrupt if Claude heads the wrong way.

Terminal sessions start in auto mode unless `permissions.defaultMode` is set. In auto mode Claude makes edits and runs commands without asking for each one. The ask rules in settings.json still pause for npm install, migrations, seeds, commits, pushes and `docker compose down`. The deny rules still block reading .env, `rm -rf`, force pushes, hard resets and `drizzle-kit drop`.

## Daily loop
1. Read PLAN.md and find the next phase.
2. Run that phase's /goal prompt and let it finish or hit its turn bound.
3. Run `/verify`.
4. Commit the phase.
5. Run `/clear` before the next phase.

Run `/usage` if a run uses more tokens than expected. Run `/goal clear` to stop a run.

The settings have no hooks in v1. If formatting noise shows up in diffs, a Prettier hook can be added later.

## Troubleshooting
- Port 5432 already in use: another Postgres is running. Stop it, or change the host port in docker-compose.yml and both database URLs.
- Docker not running: open Docker Desktop, wait until it says it is running, then run `docker compose up -d db`.
- Migration errors: run `docker compose ps` to confirm the db service is up. Ask Claude to read the error. Do not edit an applied migration file.
- Tests fail because the test database is missing: create the database named in YOUR_DATABASE_URL_TEST, then rerun `npm run test`.
- Claude asks to read .env: answer no. The rule is in CLAUDE.md and settings.json.

# Plan: waitlist and referral page builder

Five phases. Each ends with a /goal run. After a phase passes /verify, commit, then /clear before the next phase.

Use ultrathink only where marked. Typing it needs Claude Code 2.1.311 or newer. Otherwise stay on default effort.

## Phase 1: Foundation (25 turns)

Scope: Remix scaffold, Postgres, Drizzle schema, scripts, /healthz, one DB test.

```
/goal Phase 1 of PLAN.md. Read CLAUDE.md and SPEC.md first.
End state:
1. Remix and TypeScript app in the repo root with Vitest, ESLint and Prettier.
2. docker-compose.yml with a Postgres 16 service named db.
3. .env.example with placeholders only: YOUR_DATABASE_URL, YOUR_DATABASE_URL_TEST, YOUR_SESSION_SECRET.
4. Drizzle schema in app/db/schema.ts as in SPEC.md, with unique constraints. One generated migration applied to dev and test DBs.
5. Scripts: dev, build, start, typecheck, lint, test, db:generate, db:migrate, db:seed.
6. GET /healthz returns 200 after SELECT 1.
7. One Vitest integration test: two signups insert, a duplicate normalized email is rejected.
Check: run /verify and show output proving typecheck, lint, test and build exit 0.
Also show curl -s -o /dev/null -w '%{http_code}' localhost:3000/healthz printing 200 while npm run dev runs.
Constraints: no auth, UI or referral logic. Never read or edit .env.
Approved deps: remix, drizzle-orm, drizzle-kit, a Postgres driver, zod, vitest, eslint, prettier. Ask before any other.
Database commands run against the local Docker DB only. Do not commit.
Turn bound: stop after 25 turns and report what is left and what you could not verify.
```

## Phase 2: Public signup and referrals (30 turns)

Scope: /w/:slug page, signup action, dedupe, referral cookie, codes, thanks page, ranking tests.

Start this phase with ultrathink for the ranking query design only.

```
/goal Phase 2 of PLAN.md. Read SPEC.md and the referral-logic skill first.
End state:
1. GET /w/:slug shows the campaign and a signup form.
2. Signup action normalizes and trims email, dedupes on (campaign_id, email_normalized), validates with Zod.
3. ?ref=CODE saved in a 30-day cookie. Self-referral and unknown codes ignored. First referrer only.
4. Referral codes: 8 url-safe chars, retry on unique violation, max 3 tries.
5. Thanks page shows position, referral count and share link.
6. Ranking in SQL with row_number(), tested with ties.
7. Unit tests for ranking and normalization. Integration tests for signup.
8. npm run test:e2e exists and passes. Add a minimal Playwright config and one page-load smoke. Phase 4 extends it.
Check: npm run test and npm run test:e2e both exit 0, with the summary lines shown.
Constraints: no owner area yet. Playwright (@playwright/test) is approved for this phase. Ask before any other dependency.
Never read or edit .env. Local DB only. Do not commit.
Turn bound: stop after 30 turns and report what is left.
```

## Phase 3: Owner area (30 turns)

Scope: session auth, campaign create and edit, dashboard, leaderboards, CSV export.

Start this phase with ultrathink for the auth design only.

```
/goal Phase 3 of PLAN.md. Read SPEC.md and the referral-logic skill first.
End state:
1. /signup-owner and /login with bcrypt-hashed passwords and a session cookie.
2. /dashboard/new creates a campaign. Editing a campaign works.
3. /dashboard/:id shows stats and the full leaderboard with full emails.
4. GET /w/:slug/leaderboard shows the top 10 with masked emails, like a***@domain.com.
5. /dashboard/:id/export.csv with columns from SPEC.md, escaped via app/lib/csv.
6. Ownership: anonymous users are redirected to /login or get 401. Other owners get 404.
Check: npm run test passes, including tests for ownership, masking and CSV escaping (= + - @ prefixed with a single quote).
Check: npm run test:e2e still exits 0. Show both summaries.
Constraints: bcrypt (or bcryptjs) is approved for password hashing only. Ask before any other dependency.
Never read or edit .env. Local DB only. Do not commit.
Turn bound: stop after 30 turns and report what is left.
```

## Phase 4: Hardening (25 turns)

Scope: limits, errors, empty states, accessibility, Playwright referral smoke, reviews.

```
/goal Phase 4 of PLAN.md. Read REVIEW.md first.
End state:
1. Signup rate limit: 5 per minute per IP, then 429.
2. Input limits and field errors on all forms.
3. Error pages (404, 500) and empty states for dashboard and leaderboard.
4. Accessibility pass on forms: labels, focus order, error text linked to fields.
5. Playwright smoke: visitor A signs up, visitor B signs up via A's link, A's position changes.
6. Run /code-review and security-guidance on the diff. Fix all Blocker and high-severity items.
Check: npm run test:e2e exits 0 and prints the smoke result.
Check: the /code-review output lists no Blocker. security-guidance lists no unresolved high-severity finding.
Constraints: no new dependencies. Ask before adding any. Use the frontend-design skill for the public page.
Never read or edit .env. Local DB only. Do not commit.
Turn bound: stop after 25 turns and report what is left.
```

## Phase 5: Ship prep (20 turns)

Scope: production build, start smoke, seed, README, Dockerfile, cleanup.

```
/goal Phase 5 of PLAN.md. Read INSTALL.md and CLAUDE.md first.
End state:
1. npm run build succeeds. npm run start serves the app on port 3000.
2. db:seed creates one demo campaign with fake emails like you@example.com. Running it twice creates no duplicates.
3. README with an env var table using placeholders only.
4. Dockerfile that builds and runs the app.
5. /simplify run on the diff, with changes kept only when tests still pass.
Check: npm run build exits 0.
Check: with npm run start running, curl -s -o /dev/null -w '%{http_code}' localhost:3000/healthz prints 200.
Check: npm run test and npm run test:e2e exit 0.
Constraints: no deploy. No real secrets. Ask before npm install, db:seed or db:migrate.
Never read or edit .env. Local DB only. Do not commit.
Turn bound: stop after 20 turns and report what is left.
```

## Notes

- Phase 2 adds Playwright early so the verify step for phase 2 has an e2e script to run.
- Each goal prompt needs printed command output. The evaluator judges only what appears in the conversation.

# Review rules

Apply these rules when reviewing a diff in this repo. Report each finding as Blocker, Should fix, or Nit.

## Authorization
- Every /dashboard route and /dashboard/:id/export.csv checks the session and that the campaign belongs to the signed-in owner.
- Flag any query on campaigns or signups that lacks an owner_id or campaign_id filter.
- Non-owners get a redirect or 401. Other owners' campaigns get 404.

## Privacy
- No full emails on public pages, in logs, or in error messages.
- Public leaderboards use the masking helper (a***@domain.com) everywhere.

## CSV
- Every cell goes through the escape helper in app/lib/csv.
- Quotes, commas and newlines are handled by the helper, not by string joins.
- Cells starting with = + - @ get a single quote prefix.

## Referrals
- Dedupe uses email_normalized, never the raw email.
- Self-referral is blocked. Referral codes resolve only within the same campaign.
- Ranking runs in SQL (row_number over referral_count desc, created_at asc). Tests cover ties.
- Signup and referral credit happen in one transaction.

## Database
- Schema changes come with a generated migration. Applied migrations are never edited.
- No raw SQL string concatenation. Use the Drizzle query builder or parameterized sql templates.
- Unique constraints on (campaign_id, email_normalized) and referral_code stay in place.

## Remix
- No .server.ts code or DB imports reach client bundles.
- Actions validate with Zod and return field errors with status 400.

## Tests
- New behavior has a Vitest test. Integration tests use DATABASE_URL_TEST only.
- The Playwright smoke still passes when the diff touches signup or the leaderboard.

## Secrets
- Nothing in the diff looks like a key or a real connection string. Only placeholders.
- No code reads or prints .env.

## Severity
- Blocker: security, privacy, data loss, or a broken build.
- Should fix: missing tests, missing validation, unclear ownership checks.
- Nit: naming and small clarity issues.
- Skip style issues that Prettier already handles.

# Waitlist and referral page builder: spec

## Goal

Owners launch waitlist campaign pages. Visitors sign up with an email, get a referral link, and move up the list when friends sign up through it. Owners see stats, a full leaderboard, and a CSV export.

## Non-goals (v1)

- No payments or billing
- No custom domains
- No email sending (no confirmation or welcome emails)
- No multi-user teams; each campaign has one owner
- No social login

## Users

- Owner: creates campaigns, views the dashboard and leaderboard, exports CSV.
- Visitor: signs up on a public page and shares a referral link.

## Data model

- owners(id, email, password_hash, created_at)
- campaigns(id, owner_id, slug unique, title, headline, description, created_at)
- signups(id, campaign_id, email, email_normalized, referral_code unique, referred_by_signup_id nullable, created_at)
- unique(campaign_id, email_normalized) on signups

## Routes

Public:
- GET /w/:slug: campaign page and signup form
- POST /w/:slug/signup: signup action
- GET /w/:slug/thanks: position, referral count, share link
- GET /w/:slug/leaderboard: top 10, masked emails

Auth:
- /login
- /signup-owner

Owner (session and ownership required):
- /dashboard
- /dashboard/new
- /dashboard/:id: stats and full leaderboard
- /dashboard/:id/export.csv

Ops:
- GET /healthz

## Ranking

- Order by referral_count descending, then created_at ascending.
- referral_count is the number of signups whose referred_by_signup_id is this signup.
- Position is 1-based.
- Computed at read time with SQL. Never stored.

## Referral rules

- A ?ref=CODE query value is saved in a cookie for 30 days.
- The referrer must be in the same campaign.
- Self-referral and unknown codes are ignored silently.
- A signup credits only its first referrer.

## Acceptance criteria

- Signing up as you@example.com and then you@example.com yields one signup.
- A friend signing up through a link raises the referrer's position when it changes rank.
- A signup carrying a code from another campaign has referred_by_signup_id null.
- export.csv returns 401 or a redirect to /login for anonymous users.
- export.csv returns 404 for another owner's campaign.
- The public leaderboard shows only masked emails, like a***@domain.com.
- The sixth signup from one IP within a minute returns 429.
- A slug with uppercase letters or fewer than 3 characters shows a field error.

## CSV

- Columns: email, position, referral_count, referred_by_email, signed_up_at (ISO 8601).
- Full emails, owner-only.
- Cells starting with = + - @ are escaped.

## Validation and limits

- Email validated with Zod, max 254 characters.
- Slug: 3 to 40 characters, lowercase letters, digits and hyphens.
- Signup rate limit: 5 per minute per IP.
- Invalid input returns 400 with field errors.

## Open questions

Decide these with the owner in phase 1. If the owner has no preference, use the default in brackets.

- Must an email confirm before a referral counts? [No in v1]
- Tie-break beyond created_at? [None]
- Reserved slugs such as login, dashboard, healthz, w? [Reserve them]
- Should owners.email be unique? [Yes]
- Can an owner change a slug after launch? [No in v1]