Testing PRs against prod-scale data¶

Plan for closing the gap where PRs pass review and CI, then fail in production because the change was only ever exercised against small dev/test datasets.

Status: proposal — not yet implemented. Written 2026-05-14.

Why this exists¶

Two recent shipments failed in production for the same underlying reason:

PR #13709 — email link-click report. The aggregation in Services/EmailItemService.cs is covered by five unit tests (Raklet.UnitTests/EmailClicksByUrlAggregationTests.cs). All five run the query against new[]{...}.AsQueryable() — in-memory LINQ-to-Objects. The code comment states the method was "extracted so it can be unit-tested against an in-memory source." In-memory LINQ supports every operation and is unrelated to what EF6 emits against SQL Server, so the tests prove the C# grouping math is correct and prove nothing about whether the query runs — or runs fast — in production. The page never worked properly on prod.
CRM Customers / live MRR dashboard. Shipped in #13663, then required ~6 follow-up performance PRs (parallelize Stripe lookups, cache + WebJob prewarm, log truncation, denylist stale refs, incremental cache refresh). Built and reviewed against dev-sized data; fell over on production volume.

The two failure modes¶

Translation failure. EF6 cannot translate the LINQ to SQL and throws NotSupportedException at runtime. PR #13709's g.Select(a => a.EmailItemId).Distinct().Count() inside a GroupBy projection is exactly this shape — EF6 either refuses it or emits a correlated per-group re-scan.
Scale failure. The query translates but is slow on prod row counts. EmailItemActions has only a single-column, non-covering index on EmailId (migration 201907251510155); Type, IsDeleted, ClickedUrl, EmailItemId are not in it, so SQL Server does one key lookup per row. A campaign sent to a large list has hundreds of thousands of action rows → timeout in prod, instant on a dev org.

Why CI did not catch either¶

.github/workflows/tests.yml runs Jest plus exactly one .NET test class (NotificationCenterCompletionLogicTests). No CI job touches a database. The [TestCategory("Integration")] tests that do hit a real database (e.g. Raklet.UnitTests/OrganisationMembershipCrudIntegrationTests.cs) are never run as a PR gate.

Core principle: a test that does not touch SQL Server cannot tell you how a query behaves in production. Today, almost none of ours do.

The plan: three layers¶

Cheapest first. Layer 1 closes the translation-failure class; Layer 2 closes the scale class; Layer 3 makes both structural instead of relying on memory.

Layer 1 — Every EF query gets one real-SQL integration test¶

Rule: if a PR adds or changes a LINQ query that EF translates, it must add a [TestCategory("Integration")] test that executes that query against SQL Server (not against .AsQueryable() over an array).

OrganisationMembershipCrudIntegrationTests is the pattern to copy — it opens a real context, asserts a known org exists, runs real service calls, cleans up in a finally. But note the codebase has four EF DbContexts, each pointing at its own database. The integration test must use the context the feature's query actually uses:

DbContext	Connection string	Holds
`RakletDb` (`Models/RakletDb.cs`)	`RakletV3`	orgs, `OrganisationMemberships`, contacts, events, payments-side refs
`RakletEmailContext` (`Raklet.Email/Database/`)	`RakletEmailContext`	`Emails`, `EmailItems`, `EmailParts`, `EmailItemActions`
`RakletMoneyContext` (`Raklet.Money/Database/`)	`RakletMoneyContext`	money-side payment data
`RakletSmsContext` (`Raklet.Sms/Database/`)	`RakletSmsContext`	SMS

Concretely: PR #13709's GetEmailClicksByUrl runs on RakletEmailContext, so its integration test must new RakletEmailContext() — not RakletDb. The existing membership test uses RakletDb only because memberships live there.

This catches translation failures (NotSupportedException) at PR time instead of in prod. It does not by itself catch scale problems — that is Layer 2.
In-memory .AsQueryable() unit tests are still fine for pure C# logic that never reaches the database. They must not be presented as evidence that a database query works.

Cost: low. Pattern and infrastructure exist; this is a discipline + CI-wiring change.

Layer 2 — A prod-scale dataset for the known-huge tables¶

Hot-tables registry¶

Create docs/agents/hot-tables.md listing tables that are large in production, which database/context they live in, and the columns queries filter/group on. Initial set, with prod sizes measured 2026-05-14 (see appendix query):

Database (DbContext)	Table	Prod rows	Prod reserved	Why large	Filter/group columns
email (`RakletEmailContext`)	`EmailItems`	17.3M	32 GB	one row per recipient per campaign	`EmailId`, `IsDeleted`
email (`RakletEmailContext`)	`EmailItemActions`	14.9M	8.3 GB	one row per recipient action per campaign	`EmailId`, `Type`, `IsDeleted`, `ClickedUrl`, `EmailItemId`
email (`RakletEmailContext`)	`EmailParts`	2.5M	15 GB	HTML bodies — very wide rows	`EmailId`
email (`RakletEmailContext`)	`Emails`	2.5M	10 GB	every campaign ever sent — wide rows	`OrganisationId`
main (`RakletDb`)	`OrganisationMemberships`	910k	6.4 GB	every contact in every org — very wide rows	`OrganisationId`, `Status`, `Type`
main (`RakletDb`)	`Organisations`	34k	3.9 GB	only 34k rows but ~120 KB/row — per-org settings/CSS/config blobs	`Id`, `Permalink`
main (`RakletDb`)	`Payments`	474k	1.1 GB	every payment ever taken	`OrganisationId`, date ranges
main (`RakletDb`)	`Debts`	1.5M	1.1 GB	per-member debts	`OrganisationId`, `OrganisationMembershipId`
main (`RakletDb`)	`OrganisationMembershipActivities`	1.8M	722 MB	activity log per member	`OrganisationMembershipId`, `OrganisationId`
main (`RakletDb`)	`ApplicationForms`	144k	688 MB	wide form payloads	`OrganisationId`

Notes: - *_2018 tables (EmailItems_2018, EmailItemActions_2018, …) are archive tables — list them so nobody queries them by accident, but exclude from seeding. - Row count is platform-wide; query performance depends on per-org / per-email fan-out. A query like GetEmailClicksByUrl(emailId) only ever touches one email's rows — what matters is the worst single email, not the 14.9M total. Before building the seeder, run the distribution query in the appendix to get the real worst-case fan-out and size the seed to it.

Scale dataset — two options, pick one¶

(a) Synthetic seeder (recommended to start). A committed script that seeds a dedicated "scale" database with realistic per-org / per-email row counts — e.g. one email with 200k–500k EmailItemActions rows across a realistic spread of Type and ClickedUrl values. Deterministic, no PII, lives in the repo, runs against any engineer's local SQL and in CI.
(b) Sanitized prod restore. Periodically restore a PII-scrubbed prod backup into a scale environment. More faithful to real distributions, but prod is tens of GB per database, needs a scrubbing pipeline, and needs infra ownership. Candidate for later.

Scale dataset sizing & disk¶

Every engineer runs MS SQL locally (no team-shared database), so the seeder must be cheap enough to run on a laptop. It is — as long as it targets the worst-realistic single org/campaign, not total prod volume.

Per-row sizes derived from the prod measurements above (reserved bytes/row, incl. indexes):

Table	≈ bytes/row	500k rows ≈
`EmailItemActions`	~560 B	~280 MB
`EmailItems`	~1.9 KB	~930 MB (wide)
`Emails` (email DB)	~4 KB	n/a — seed tens, not 500k
`EmailParts`	~6.3 KB	n/a — seed tens, not 500k
`OrganisationMemberships`	~7 KB	~3.5 GB (wide) — seed per-org max, not 500k
`Organisations`	~120 KB	n/a — seed tens of orgs, never in bulk

So a focused scenario — one Email + one EmailItem set + ~500k EmailItemActions for it — is ~300 MB to ~1 GB, fine on any laptop. Replicating all of prod (tens of GB per DB) is unnecessary and not the goal. Note the trap row-count alone hides: Organisations is only 34k rows but ~120 KB each — a query that .Include()s or wide-selects orgs across the table is a scale risk even though the row count looks small.

Guardrails for whoever builds the seeder: - Tier it. A default "small" profile for everyday local runs, a "full-scale" profile for CI / on-demand. Engineers don't pay full cost every run. - Seed into a dedicated scale database, not the engineer's working dev DB. SQL Server .mdf/.ldf files never auto-shrink — deleting seeded rows leaves the file grown. A dedicated RakletScale-style DB (per laptop, and in CI) can just be dropped and recreated. Keep the seeder idempotent (drop+recreate or truncate+reseed). - Bulk-insert, don't loop EF. 500k rows via SaveChanges is glacial — use SqlBulkCopy / batched raw inserts.

Scale test + time budget¶

For a query touching a hot table, the PR adds an integration test that runs it against the scale database and asserts a wall-clock budget (e.g. < 2s). A query that needs a covering index then fails CI instead of degrading silently in prod.

Cost: medium. The seeder is the main build item; option (a) keeps it to a repo script with no new infra.

Layer 3 — Make it structural¶

PR template. Add to .github/pull_request_template.md: "Does this touch a query on a hot table (see docs/agents/hot-tables.md)? If yes — name the index it uses, and link the scale test. If no index covers the filtered/grouped columns, add a migration."
CI. Add an integration job to tests.yml — Windows runner, against a scale database — that runs [TestCategory("Integration")]. It can start non-blocking (signal only) and become a required check once stable.
Review reflex. Any GroupBy / Distinct / .Count() on an EF query, or a .Where whose columns are not all covered by one index, gets challenged in review.

Cost: low. Template + workflow edits + a review-norm note (candidate for CLAUDE.md footgun list).

What this would have done for PR #13709¶

Layer 1 forces an integration test executing GetEmailClicksByUrl against RakletEmailContext on real SQL → the Distinct().Count()-in-GroupBy either translates or fails the test. Caught at PR time.
Layer 2's scale database (one email, ~500k action rows) + a < 2s budget → exposes the non-covering EmailId index. PR adds a covering-index migration before merge.
Layer 3's template line forces the author to name the index, making the gap visible even before tests run.

Open decisions (for team review)¶

Scale dataset: synthetic seeder (a) vs sanitized prod restore (b)? Recommendation: start with (a), revisit (b) once the habit is established.
Worst-case fan-out: run the distribution query (appendix) to set exact seed targets per hot table — biggest single email's EmailItemActions count, biggest org's OrganisationMemberships count, etc.
CI runner cost/time: the integration job needs a reachable scale database and a Windows runner. Decide blocking vs non-blocking for the first iteration.
Time budgets: per-query budget vs one global ceiling. Per-query is more honest but more bookkeeping.

Suggested rollout order¶

Confirm docs/agents/hot-tables.md contents + run the fan-out query.
Wire the integration CI job (non-blocking) + PR template line — Layer 1 & 3.
Build the synthetic seeder + scale database — Layer 2.
Fix PR #13709 as the reference example (integration test against RakletEmailContext + covering-index migration + scale test).
Make the integration job a required check.

Appendix: prod measurement queries¶

Per-table row count + size. Metadata-only (sys.dm_db_partition_stats) — instant, no table scan, safe on prod. Run once per database (main / email / money / sms — connect to each):

SELECT
    DB_NAME()                                              AS [Database],
    s.name                                                 AS [Schema],
    t.name                                                 AS [Table],
    SUM(CASE WHEN p.index_id IN (0,1) THEN p.row_count ELSE 0 END) AS [Rows],
    CAST(SUM(p.reserved_page_count) * 8.0 / 1024 AS DECIMAL(18,1)) AS [ReservedMB],
    CAST(SUM(p.used_page_count)     * 8.0 / 1024 AS DECIMAL(18,1)) AS [UsedMB]
FROM sys.dm_db_partition_stats p
JOIN sys.tables   t ON t.object_id = p.object_id
JOIN sys.schemas  s ON s.schema_id = t.schema_id
GROUP BY s.name, t.name
ORDER BY [UsedMB] DESC;

Sort by [UsedMB], not [Rows] — row count alone hides wide low-row tables like Organisations (34k rows, 3.9 GB). Re-sort by [Rows] only when you specifically need fan-out counts.

Worst-case fan-out per email. This does scan, so run off-peak. Tells you the largest single email's action count — the number that should size the seeder (repeat the pattern for OrganisationMemberships grouped by OrganisationId, etc.):

SELECT TOP 20 EmailId, COUNT(*) AS Actions
FROM dbo.EmailItemActions
WHERE EmailId IS NOT NULL
GROUP BY EmailId
ORDER BY COUNT(*) DESC;