* ncmec: tighten `email` field role from STRING to EMAIL_ADDRESS scalar

Follow-up to #840 and #841. Now that `@roostorg/coop-types@2.4.0` ships
a dedicated `EMAIL_ADDRESS` scalar, switch the email field role plumbing
to require it instead of any STRING-typed field. Mirrors the two-PR
pattern PR #559 → PR #583 used for `IP_ADDRESS`.

- Bump `@roostorg/coop-types` to ^2.4.0 in server and client
- Add `EMAIL_ADDRESS` to `ScalarType`, `SignalInputType`, `FieldType`
SDL enums; regenerate codegen
- Add `[ScalarTypes.EMAIL_ADDRESS]` handler in `fieldTypeHandlers.ts`
(string coerce + trim; null on empty; format validation deferred per
Caleb's note on #840)
- `FieldRoleToScalarType.email`: STRING → EMAIL_ADDRESS
- Client `schemaFieldRolesFieldTypes.EMAIL`: String → EmailAddress
- Migration: drop the `valid_email_field_field_type` CHECK constraint
(which required STRING) and replace with one requiring EMAIL_ADDRESS
- Exhaustive-switch updates for `ManualReviewJobFieldsComponent` and
`RuleTestModal`
- `EmailAddressArbitrary` for fast-check test fixtures, with culturally
diverse example names on RFC 2606 reserved domains

Migration safety: any existing email_field mapping to a STRING field
will fail the new constraint. #840 merged hours before this; no adopter
should have configured the role yet. Operators between #840 and this
migration who did configure it will need to either change their field
type to EMAIL_ADDRESS or null out the mapping before applying.

Co-Authored-By: Claude Opus 4.7 (1M context) <noreply@anthropic.com>

* client: add EMAIL_ADDRESS cases to three exhaustive switches

CI on #842 failed `npm run lint` (= `tsc --noEmit`) because widening
`ScalarType` to include `EMAIL_ADDRESS` left three client switches
incomplete. The `JsonValue` errors in `itemTypeCodeSampleUtils.ts`
were downstream of `generateFakeScalarFieldValue` not handling the
new scalar.

- `signal.ts`: comparators for `EmailAddress` mirror `String` / `Url` / `IpAddress`
- `RuleInsightsSamplesTable.tsx`: stringify (mirrors `IpAddress`)
- `itemTypeUtils.ts:generateFakeScalarFieldValue`: returns a fake email
on RFC 2606 `example.com`

Co-Authored-By: Claude Opus 4.7 (1M context) <noreply@anthropic.com>

* ncmec: data-migrate stale email_field mappings before tightening CHECK

Addresses Tao's review on #842: the original "no adopter has had time to
configure the role" assumption was wrong. Deployments between #840 and
this migration may have mapped `email_field` to a STRING field; the new
CHECK constraint would reject those rows and block the migration entirely.

Add a DO-block data migration that runs in the same transaction before
the CHECK swap. For each item_type whose `email_field` doesn't point at
an EMAIL_ADDRESS-typed field in `fields`, NULL out the mapping and emit
a NOTICE naming the row (id, name, org_id) so operators can reconfigure
via the admin UI if needed. The underlying STRING field's data is left
untouched; only the role pointer is cleared.

Header comment updated to reflect the new data-migration step.

Co-Authored-By: Claude Opus 4.7 (1M context) <noreply@anthropic.com>

* db: forward postgres NOTICE events to console.warn during migrations

Addresses Tao's review on #842: the migration's `RAISE NOTICE` lines
were being swallowed because Sequelize's pg dialect doesn't surface
server-side messages by default. Operators running `npm run db:update`
saw the migration succeed but had no signal about which adopter
configurations had been cleared.

Wire a Sequelize `afterConnect` hook in `pg-base.ts` that attaches a
`notice` listener to every pg client the pool opens, forwarding each
message to `console.warn` prefixed with the severity. Future migrations
that use `RAISE NOTICE` (or `RAISE WARNING`) will also benefit, not just
this one.

Update the migration's header comment to direct operators to scan the
`db:update` output for `[postgres NOTICE]` lines after applying, so the
cleared mappings don't go unnoticed.

Co-Authored-By: Claude Opus 4.7 (1M context) <noreply@anthropic.com>

* ncmec: wire HMA hashes into outgoing report's fileDetails.originalFileHash (#844)

NCMEC's CyberTipline accepts an `originalFileHash[]` element with a
`hashType` attribute per entry. HMA already computes per-image hashes
at item-submission time (`HMAHashBankService.hashContentFromUrl`,
stored on the image object alongside `url`), but the NCMEC report
builder discarded them.

- Add `OriginalFileHash` type and `originalFileHash?: OriginalFileHash[]`
to `FileDetails` (positioned after `industryClassification`, before
`ipCaptureEvent`, in line with NCMEC XSD ordering for forensic fields).
- Add `hashes?: Record<string, string>` to the internal `Media` type so
the report assembly can pass them through to `#upload`.
- `buildSubmitReportParamsFromDecision` now extracts hashes from the
matching image in the item data (walks scalar, array, and map
containers). New `extractHashesForUrl` helper.
- `#upload` converts the hashes via `toOriginalFileHashes` and includes
the resulting array on `fileDetails` when non-empty.
- New `toOriginalFileHashes` exported helper handles trimming,
empty-value filtering, and the algorithm-name uppercasing for the
`hashType` attribute.

Tests:
- 4 new in `buildSubmitReportParamsFromDecision.test.ts` covering the
scalar IMAGE case, ARRAY-of-IMAGE container traversal, URL-mismatch
omission, and missing-hashes omission.
- 4 new in `ncmecReporting.test.ts` for `toOriginalFileHashes`'s
precedence rules (uppercasing, trimming, empty-value drops,
undefined-on-empty).

This is one of the P1 items from the audit in #843.

Co-authored-by: Claude Opus 4.7 (1M context) <noreply@anthropic.com>

* db: also lower client_min_messages so RAISE NOTICE actually surfaces

Initial fix only attached a notice listener, but verified locally that
NOTICE messages from RAISE NOTICE inside PL/pgSQL DO blocks were still
being filtered out. Root cause: Sequelize raises `client_min_messages`
to WARNING by default, which makes postgres filter NOTICE-severity
messages server-side before they reach the client. The listener was
correctly attached but never fired for NOTICEs.

In the same afterConnect hook, also issue `SET client_min_messages =
'notice'` so postgres sends NOTICE messages to the client. Verified
locally: per-row NOTICEs from the email-field-clearing migration now
appear in `db:update` output as expected.

Co-Authored-By: Claude Opus 4.7 (1M context) <noreply@anthropic.com>

* ncmec: wire webhook fileDetails.hash into outgoing report's originalFileHash (#850)

Closes the follow-up gap noted in PR #844. The additional-info webhook
can return a single `{ hash, hashType }` per media item; #844 wired
HMA-sourced hashes through but webhook-sourced hashes were still
dropped because:

1. `MediaAdditionalInfo` (the internal type that `#upload` consumes)
didn't declare `fileDetails`; the validation-schema-typed
`NcmecAdditionalInfoResponse` has it, but the data was erased at
the boundary between webhook response and internal use.
2. `toOriginalFileHashes` took only the HMA map as input.

Changes:

- Add `fileDetails?: { hash; hashType }` to `MediaAdditionalInfo`.
- Refactor `toOriginalFileHashes` to take both sources via an opts
object (`hmaHashes`, `webhookFileDetails`). Hashes from both are
combined and deduped on (`hashType` uppercase, trimmed hash value)
so a webhook returning the same algorithm as HMA produces one entry
in the outgoing report, not two.
- Update `#upload` call site to pass both sources.
- Remove dead `fileDetails: { ipCaptureEvent: [] }` from the
no-webhook fallback; it never matched any consumed shape and
nothing read it (the `ipCaptureEvent` lives at the top level of
`MediaAdditionalInfo`, not nested).

Tests: 6 new cases covering webhook-only, both sources, exact-match
dedup, same-algorithm-different-hash kept-both, whitespace-only
webhook hash dropped, and case-insensitive dedup on algorithm name.
The toOriginalFileHashes test block is moved to its own file
(`toOriginalFileHashes.test.ts`) to keep `ncmecReporting.test.ts`
under the 500-line max-lines limit.

Co-authored-by: Claude Opus 4.7 (1M context) <noreply@anthropic.com>

---------

Co-authored-by: Claude Opus 4.7 (1M context) <noreply@anthropic.com>

Closes #662. The full UUID with `whitespace-nowrap` overflowed the ID
column and pushed adjacent columns out of view. Truncate the displayed
value to the first 8 characters with an ellipsis; full UUID stays on
the clipboard via `CopyTextComponent`'s `displayValue` prop.

Co-authored-by: Claude Opus 4.7 (1M context) <noreply@anthropic.com>

Adds a dedicated EMAIL_ADDRESS entry to the ScalarTypes enum and maps
it to `string` in ScalarTypeRuntimeTypeMapping. Mirrors the IP_ADDRESS
addition from #559. Lets Coop tag a schema field as an email address so
integrations (NCMEC today; potentially others later) can pull it via
schema-field role metadata instead of accepting any STRING-typed field.

Format validation lives at the field-value level, not in this type.

Required before #840 can switch the `email` field role from STRING to
EMAIL_ADDRESS.

Co-authored-by: Claude Opus 4.7 (1M context) <noreply@anthropic.com>

* ncmec: add `email` schema field role for reported user

Closes #839. Adopters whose NCMEC additional-info webhook isn't
configured (or returns no email) get reports rejected as "incomplete"
because no other path populates the email. This adds an `email` field
role on user item types, parallel to the existing `ipAddress` role,
sourced from a user-schema string field of the adopter's choosing.

The submit codepath now prefers `userAdditionalInfo.email` from the
NCMEC additional-info webhook when present, falling back to the
field-role email otherwise (via new `resolveReportedPersonEmail`
helper).

Migration adds `email_field` to `public.item_types` and its temporal
mirror, with a STRING-type CHECK constraint mirroring the existing
`display_name_field` pattern. The `item_type_versions` materialized
view is dropped and recreated to include the new column, with all four
indexes and the dependent `item_type_latest_versions` view rebuilt.

Admin UI adds `Email` to the role picker for user item types.

Co-Authored-By: Claude Opus 4.7 (1M context) <noreply@anthropic.com>

* ncmec: address PR #840 review feedback

- Migration: include `created_at` in the recreated `item_type_versions`
materialized view to match the column declared in
`dbTypes.ts`. The previous `add_ip_address_field_role_to_item_types`
migration omitted it; this PR recovers parity.
- `resolveReportedPersonEmail`: trim the field-role email and treat
whitespace-only as absent, so we don't ship `{ _text: " " }` to
NCMEC (which would fail the same way the original bug did).
- Tests: two new cases on `resolveReportedPersonEmail` cover the
trim and whitespace-only paths.

Co-Authored-By: Claude Opus 4.7 (1M context) <noreply@anthropic.com>

* ncmec: revert created_at addition to MV; fix dbTypes drift instead

The previous commit added `created_at` to the `item_type_versions`
materialized view in response to a CodeRabbit comment about
`dbTypes.ts` declaring the column. CI revealed the column doesn't
exist on `public.item_types_history`, so the UNION ALL SELECT failed:

ERROR: column item_types_history.created_at does not exist

The actual drift was in `dbTypes.ts` claiming a column the MV has
never had. Remove the type declaration to match reality. The MV
itself stays unchanged from the previous (working) `ip_address`
migration's shape.

Co-Authored-By: Claude Opus 4.7 (1M context) <noreply@anthropic.com>

* ncmec: update USER item type inline snapshot for email field role

CI surfaced one inline snapshot in moderationConfigService.test.ts that
didn't include the new `email: undefined` key on `schemaFieldRoles`.
Snapshot updated to match the new shape. CONTENT and THREAD snapshots
are unaffected since email is USER-only.

Co-Authored-By: Claude Opus 4.7 (1M context) <noreply@anthropic.com>

---------

Co-authored-by: Claude Opus 4.7 (1M context) <noreply@anthropic.com>

connect-pg-simple keeps a recurring pruneSessions timer that calls
pool.query on its pool. makeApiServer created the store with the shared
KyselyPgPool but never closed it, so the timer kept running after
shutdown. In tests this leaked a setInterval per makeMockedServer() call
that fired pool.query on the harness's already-closed pinned connection
after each test, logging "Failed to prune sessions: Client was closed
and is not queryable" indefinitely and hanging the test worker — the
loop that forced the manual cancellation of CI run 27951870325.

Hold a reference to the store instance and call its close() in the
shutdown path. ownsPg is false (we pass in our own pool), so close() only
stops the prune timer and won't end the shared pool.

Co-authored-by: Claude Opus 4.8 <noreply@anthropic.com>

* graphql: add userStrikeCount field to UserItem type and resolver

Exposes the user's cumulative strike score on the UserItem GraphQL
type, calling UserStrikeService.getUserStrikeValue().

* client: show user strike count in Review Console and Investigation

Selects userStrikeCount from the UserItem type in the MRT job fragment,
the Investigation query, and the related user component's query.
Displays it as a "Strikes" field in the primary and related user panels,
mirroring where User Score appeared before it was removed in #726.

Fixes #752

* client: fix ItemTypeFieldFieldData type error in userStrikeCountField

According to Claude, spreading createFieldType widened the `type` property to the full
ScalarType union, which ItemTypeFieldFieldData (a discriminated mapped
type) can't resolve. Inline the object with a literal `type: 'NUMBER'`
instead.

* ManualReviewJobRelatedUserComponent: use @/ import path

Addresses CodeRabbit review feedback on #766.

Two textarea placeholders included the literal backtick-wrapped internal
field name `actorNote`, which is not meaningful to non-developer moderators.

Rewrites both to plain-language descriptions of the field's purpose:
- ItemAction.tsx: "...This note will be recorded in the audit log and
included in any configured webhook payload."
- BulkActioningDashboard.tsx: "Add a short note explaining this decision
— it will be recorded in the audit log and included in any configured
webhook payload."

Fixes #684

Co-authored-by: james <li@jamesdeMacBook-Pro.local>
Co-authored-by: Cassidy James <cassidyjames@roost.tools>

* test(e2e): bootstrap Playwright suite with login flow

Stand up a dedicated /e2e Playwright package (own package.json/lockfile,
matching the per-package repo layout) and the first moderator-critical flow
from #485: login + session.

Also adds a draft CI workflow that runs the suite on PRs and pushes to main,
gated by dorny/paths-filter so it only fires on app-affecting changes, and
caching the Playwright browser binaries. The full-stack bring-up mirrors the
documented local setup and still needs maintainer review + end-to-end CI
validation.

Refs #485

Co-Authored-By: Claude Opus 4.8 (1M context) <noreply@anthropic.com>

* ci(e2e): fix server/client startup hang

Redirect backgrounded server/client output to log files so they don't hold the
step's stdout pipe open, and wait on the server's /ready health endpoint (a
reliable 200) instead of the GraphQL endpoint, which returns 400 on a bare GET.
Dump the logs if readiness times out.

Co-Authored-By: Claude Opus 4.8 (1M context) <noreply@anthropic.com>

* ci(e2e): wait on server TCP port, not load-gated /ready

The /ready endpoint returns 500 when CPU usage exceeds 75% (api.ts), which is
always the case while tsc-watch and vite are compiling on a CI runner, so
wait-on never saw a 200. Wait on the server's listening TCP port instead (the
server only logs readiness after all middleware, including GraphQL, is wired).

Co-Authored-By: Claude Opus 4.8 (1M context) <noreply@anthropic.com>

* test(e2e): select login email field by input type, not role

getByRole('textbox') matched both the email and password inputs in this Ant
version (strict-mode violation in CI). The email field is the only type=text
input, so select by type to stay unambiguous.

Co-Authored-By: Claude Opus 4.8 (1M context) <noreply@anthropic.com>

* test(e2e): move Playwright into server/ and self-seed via DI factories

Relocate the Playwright suite from a standalone top-level package into
server/e2e/ so tests can import the server's DI container and the existing
test/fixtureHelpers factories. Each test now seeds the state it needs
(committed) and tears it down, instead of depending on a pre-seeded org.

- fixtures/coop.ts extends Playwright's test with a worker-scoped `deps`
fixture (getBottle(), dynamically imported so the heavy graph defers to run
time) and a test-scoped `seed` fixture exposing factory wrappers
(seed.orgWithAdmin seeds an org + password-login admin).
- login.spec.ts seeds its own admin and logs in; no env credentials.
- jest testPathIgnorePatterns excludes /e2e/ so its *.spec.ts files never run
under the unit-test runner; eslint test-rules + devDep allowlist extended to
e2e/.
- CI: drop the create-org seed step; @playwright/test is a server devDep so
only the browser binary is installed; cache keyed on server/package-lock.json;
report uploaded from server/e2e/playwright-report.

Co-Authored-By: Claude Opus 4.8 (1M context) <noreply@anthropic.com>

* test(e2e): load server runtime from transpiled/ to dodge esbuild type-import bug

Playwright's esbuild loader transpiles each file in isolation and can't elide
type-only imports written with value syntax (e.g. `import { JSON }`), so
importing the server's TS source graph threw "does not provide an export named
'JSON'" at run time. Load the compiled output (transpiled/, emitted by tsc with
those imports correctly elided) instead, via computed specifiers so tsc doesn't
resolve transpiled/ statically; types still come from the .ts source through
`typeof import(<source>)` casts. transpiled/ is present whenever the server runs
(tsc-watch / the Docker build emit it).

Relax `consistent-type-imports` (disallowTypeAnnotations: false) for test/e2e
files so the `typeof import()` casts pass lint.

Co-Authored-By: Claude Opus 4.8 (1M context) <noreply@anthropic.com>

* test(e2e): drop per-test cleanup; isolate by tenant and run fully parallel

Each test seeds a unique-id org, so the app's multi-tenancy isolates tests from
one another and no cleanup is needed (the CI database is disposable). This lets
the suite run with `fullyParallel: true` — Playwright has no random-order flag,
and concurrent execution with no fixed order is the idiomatic way to prevent
implicit ordering dependencies. Removes the Seeder's cleanup tracking entirely.

Documents the two disciplines that keep this valid (seed your own data; never
assert on cross-tenant/global state) and a "Scaling to per-worker databases"
note for when the suite grows — that future change is infra-only because the
deps fixture is already worker-scoped and tests already self-seed.

Co-Authored-By: Claude Opus 4.8 (1M context) <noreply@anthropic.com>

* clean up

* ci(e2e): cover root deps in path filter; drop runtime wait-on fetch

Add .nvmrc, root package manifests/lock, and docker-compose.yaml to the
paths-filter so the E2E job runs when files it depends on change.

Replace the dynamic `npx --yes wait-on@8` readiness check (an undeclared
runtime dependency that bypasses the lockfile) with a built-in bash wait
loop polling tcp:8080 and http:3000.

Co-Authored-By: Claude Opus 4.8 (1M context) <noreply@anthropic.com>

---------

Co-authored-by: Claude Opus 4.8 (1M context) <noreply@anthropic.com>
Co-authored-by: Cassidy James <cassidyjames@roost.tools>

Integration tests (server/test/**/*.integ.test.ts) never ran in CI. The
`test` compose service runs `test:ci`, whose jest config ignores
`*.integ.test.ts`; they only ran via `test:integ`, which nothing in CI
invoked. PRs touching server/ — including Dependabot updates — therefore
merged without integration coverage.

Add a dedicated `check_api_server_integration` job that runs the
integration tests in parallel with the existing unit/lint/build job; both
are gated on server changes and report failures independently.

Also add --runInBand to test:integ: the suites share databases and are
not parallel-safe (report-flow raced the other suites and failed when run
concurrently). This mirrors test:ci, which already runs serially.

Fixes #221

Co-authored-by: Claude Opus 4.8 (1M context) <noreply@anthropic.com>

Moves checklist items from the ethanresnick/pr-checklists action into the PR
template directly, so they work for PRs from forks. The action required
pull-requests:write which GitHub blocks on fork PRs, meaning fork contributors
never saw the checklist.

Closes #671

* [791] Extend parameterized actions to proactive rules and user strikes

* code review fixes

* Move configuredParameters onto Action type, deprecate rule-level actionParameters

- Add `configuredParameters: JSONObject` to ActionBase and all implementing
types so consumers access configured values directly from each action.
- Deprecate the top-level `actionParameters` field on Rule types.
- Update client query to read `configuredParameters` from action objects.
- Remove stale comments from validation tests.

* code review fixes

* ci: make image pulls resilient to Docker Hub flakiness

Intermittent CI failures came from transient Docker Hub registry/auth
timeouts (auth.docker.io / registry-1.docker.io) during image pulls at
`docker compose run` time. These are timeouts, not rate limits, so
authenticating wouldn't help (and can't on fork PRs anyway).

For each compose job in apply_pr_checks.yaml:

- Configure a docker.io pull-through mirror (mirror.gcr.io) via
/etc/docker/daemon.json, bypassing the flaky Docker Hub auth/token
path. No secrets, so it still works on fork PRs.
- Pre-pull/build the images each job needs in a 3-attempt retry loop
before the real steps. Images are then cached locally, so the existing
`docker compose run` steps don't hit the registry -- isolating a flaky
pull to the retried prep step without masking real lint/build/test
failures.

Co-Authored-By: Claude Opus 4.8 (1M context) <noreply@anthropic.com>

* ci: extract image-prep into a composite action

The mirror-config + retry-pull logic was duplicated across all three
compose jobs. Extract it into a local composite action
(.github/actions/prepare-docker-images) parameterized by the services to
pull/build, so each job calls it in one step.

Inputs are passed via env rather than interpolated into the run script,
matching the repo's zizmor template-injection guard.

Co-Authored-By: Claude Opus 4.8 (1M context) <noreply@anthropic.com>

* ci: run dockerised checks when CI plumbing changes

The check_api_server / check_generated_graphql / frontend jobs are gated
by paths-filter on server/** and client/**, so a PR that only touches CI
or docker plumbing (this workflow, the compose file, the Dockerfile, the
prepare-docker-images action) skipped all of them -- meaning changes to
the image-pull setup were never actually exercised in CI.

Add those plumbing paths to the server and client filters via a YAML
anchor so the relevant jobs run when they change.

Co-Authored-By: Claude Opus 4.8 (1M context) <noreply@anthropic.com>

---------

Co-authored-by: Claude Opus 4.8 (1M context) <noreply@anthropic.com>

* chore: apply prettier formatting across the codebase

Apply `prettier --write` to all `{ts,tsx,js,jsx,mjs,cjs,json,md,yaml,yml}`
files in a single pass so the codebase matches the formatting enforced by
the pre-commit hook. This is a mechanical, whitespace-only change.

Closes #799 (formatting pass portion).

* ci: enforce prettier formatting in CI

Add a check_formatting job to apply_pr_checks.yaml that runs
`prettier --check` over the same file set the pre-commit hook formats,
via actions/setup-node (no Docker, so docker-compose.yaml is untouched).

Broaden the root `npm run format` script to the same
`{ts,tsx,js,jsx,mjs,cjs,json,md,yaml,yml}` glob so `npm run format`,
lint-staged, and CI all agree on scope.

Document the new job and its local reproduction command in AGENTS.md.

Closes #799 (CI enforcement portion).

* build: add prettier and prettier:fix scripts; use them in CI

Centralize the whole-repo Prettier glob into two root package.json scripts
so CI and local dev share one source of truth:

- `npm run prettier` -> prettier --check (used by the check_formatting CI job)
- `npm run prettier:fix` -> prettier --write
- `npm run format` -> alias of prettier:fix (kept for backwards compat)

CI now runs `npm run prettier` instead of duplicating the glob inline.
The pre-commit hook (lint-staged) intentionally keeps the scoped
`prettier --write` so it only touches staged files; using the whole-repo
glob there would reformat the entire codebase on every commit.

Update AGENTS.md, docs/development/local.md, and copilot-instructions.md
to reference the canonical script names.

* [Fix] Ensure sweep also handles related items

* add tests and fix copilot review

* Add per-queue "clear other reports for a user" action sweep

* code review fixes

* code review fixes

* wording fix

* improve resiliency to help with big batches

* Make clickhouse configs for spillover on queries (#819)

* make clickhouse configs for spillover on queries

* fix bad redirect from strikes

* clickhouse optimizations + strikes fix + nullable fix

* code review fixes

* code review comments

* add tests

* fix