A temporal EAV entity store in Postgres
Part 4 — E2E testing

Part 1 admitted the storage can't say "no"; Part 2 and Part 3 rebuilt every guarantee in code. That has a consequence people underestimate: in this design, end-to-end tests aren't a quality nicety — they are the only executable form of the specification. This part is about what exactly to test, and why each class of test exists.

Goal

Turn the series' promises into assertions that run against the real thing. A conventional CRUD app can lean on the database: constraints, foreign keys and column types catch half the bugs before any test runs. Here the database enforces almost nothing by design — the semantics live in the behavior matrix, the raw CTEs, the transaction discipline and the validation ladder. A unit test with a mocked repository proves none of that: the mock will happily return what the real half-open-interval SQL never would. So the rule is: anything the series called a "rule" must exist as an E2E test against real Postgres — otherwise it's a comment, not a rule.

Two kinds of test subjects

The suite runs against two deliberately different sets of entities — one exists to prove the machinery, the other to prove the business. Mixing the two jobs into one set of fixtures is how test suites rot: machinery tests break on business churn, and business tests quietly stop covering the machinery.

A synthetic entity with every possible field

The abstract layer is tested through a test-only entity repository — a family that exists nowhere in the product, whose fieldsMeta declares every capability the virtual schema supports: one field of each scalar type, both relation directions, a tag set, a default, a DTO-name mapping, and validators. Why: no real business entity uses the full surface — testing the abstract repository only through business types leaves matrix cells permanently unvisited. The synthetic family visits all of them, and pins the machinery independently of the business: when one of these tests goes red, it is the abstract layer that broke, never a renamed product rule.

@Injectable()
export class TestEntityRepository
  extends SmentityRepository<TestEntityDTO, TestEntity, TestEntityView, TestEntityHistory>
{
  constructor(/* the six injected repositories, as in Part 2 */) {
    super(/* ... */);
    this.familyId = SM_ENTITY_TEST_FAMILY;   // reserved id, never shipped
    this.familyName = 'test_entity';
    this.fieldsMeta = [
      { field: 'aString',  type: TYPE_STR,  validators: [] },
      { field: 'anInt',    type: TYPE_INT,  validators: [] },
      { field: 'aNumber',  type: TYPE_NUM,  validators: [] },
      { field: 'aBool',    type: TYPE_BOOL, validators: [] },
      { field: 'aDate',    type: TYPE_DATE, validators: [] },
      { field: 'withDefault', type: TYPE_STR, validators: [], default: 'fallback' },
      { field: 'renamed',  type: TYPE_STR,  validators: [], dtoField: 'apiName' },
      { field: 'guarded',  type: TYPE_STR,
        validators: [{ name: 'never-empty', exec: async (_qr, value) => !!value }] },
      { field: 'parentOfRel', type: TYPE_REL_UUID_PARENT_OF, validators: [] },
      { field: 'childOfRel',  type: TYPE_REL_UUID_CHILD_OF,  validators: [] },
      { field: 'tags',        type: TYPE_TAG_SET,            validators: [] },
    ];
  }
}

Business entities for the business layer

The service and API layers are tested through the real domain types — products, categories, their repositories, services and controllers, exercised over HTTP with real permission headers. Why: these tests assert meaning, not mechanics — the duplicate-name 409, the tenancy 404, the batch that rolls back whole, the sync failure that leaves no trace. They are allowed to assume the machinery works, because the synthetic family already proved it one section up — which keeps them short, readable, and shaped like the business instead of like the storage.

The split maps cleanly onto the test classes below: the matrix, boundary, timestamp and time-travel classes (1–4) run on the synthetic family; transactionality, the validation ladder and tenancy (5–7) run on the business entities.

The three assertion altitudes

The pattern that makes these tests actually prove things: every write test asserts at three altitudes, because each altitude can lie alone.

it('creates a product', async () => {
  const dto = ProductBuilder.mock({ category: { uuid: CATEGORY_ID } });

  // 1 · HTTP: the edge accepted it
  const res = await request(app.getHttpServer())
    .post(`/v1/customers/${CUSTOMER_ID}/products`)
    .set('x-sm-user', user).set('x-sm-permissions', canEdit)
    .send(dto)
    .expect(201);
  const uuid = (res.body as ProductView).uuid;

  // 2 · View: the round-trip preserves every field
  const view = await productRepo.findByIdView(uuid);
  expect(view.name.value).toStrictEqual(dto.name);
  expect(view.price.value).toStrictEqual(dto.price);
  expect(view.parentCategory.value.uuid).toStrictEqual(CATEGORY_ID);

  // 3 · Rows: the storage is exactly what Part 1 specified
  const attrs = await dataSource.getRepository(SmentityAttr).find({
    where: { smentity: { uuid } }, relations: ['attrName'],
  });
  const name = attrs.find(a => a.attrName.name === 'name');
  expect(name?.valStr).toBe(dto.name);          // right column for the type
  expect(name?.activeTo).toBeNull();            // exactly one open version
  expect(attrs.every(a =>                       // single-timestamp rule:
    +a.activeFrom === +view.activeFrom)).toBe(true);  // no drift, ever
});

What exactly to test — and why

1. The behavior matrix, row by row

Part 1's matrix isn't documentation with tests on the side — it is the test plan. Every row of all three tables (scalar, relation, lifecycle) becomes at least one E2E case, warn rows first: same-value re-versioning, explicit-timestamp gaps and overlaps, null-vs-never-set, the relation null no-op, self-relation rejection, re-parenting with history intact. Why: these are precisely the semantics no schema enforces and no mock reproduces. When the matrix and the suite disagree, one of them is wrong — and now you find out in CI, not in support.

2. Boundary semantics at exactly T

For every close/open pair, three reads: just before T, at exactly T, just after. The old value must be visible, gone, gone; the new one absent, visible, visible — the half-open [from, to) contract. Why: every off-by-one reporting bug in this design is a boundary bug, and boundaries only fail at exactly the instant nobody manually tests.

3. The single-timestamp rule

After any create or update, assert the entity row and every touched attr/rel row carry the identical active_from (altitude 3 — the rows, not the views). Why: Part 2's first caveat. Drift is invisible in normal reads and fatal in point-in-time reads; it can only regress silently, so only a test holds the line.

4. Time travel and history

Create → update → update, capturing timestamps between steps; then read at each captured instant and assert each historical state, and fetch the history endpoint and assert the before/after diffs. Why: point-in-time reads are the product feature the whole design paid for — and the clamp for closed entities (read at active_to − ε) is subtle enough that only an explicit "close it, then GET it" test keeps it working.

5. Transactionality — assert the rollback, not just the error

The most valuable negative tests in the suite: make step N fail (a validator, a failing external sync, item 40 of a 50-item batch) and then assert zero new rows exist — raw count at altitude 3, not "the API returned 4xx". Why: Part 3's whole promise is atomic composition; a rollback that leaks even one attr-name row or half a batch quietly poisons a shared-table store. Error responses are easy to get right; clean rollbacks are not.

6. The validation ladder, one rung at a time

Why per-rung: each rung fails with a different status from a different layer — a test that only checks "some error happened" can't detect a rule silently sliding one rung down (Part 3's checklist smell).

7. Tenancy isolation

Seed two customers; create as A; read, update, list and close as B. Everything must 404 (not 403 — B shouldn't learn the entity exists), and B's list must be empty. Why: scoping here is structural (Part 3), which means one forgotten customerId in one new query is a cross-tenant leak. This is the test class with the highest consequence-per-line in the suite.

8. What deliberately stays out

Concurrency races (two same-name creates in flight, the lazy attr-name race) are real — Parts 1–3 document them as accepted — but they don't belong in this suite: a race test that passes 99% of runs is a flake generator, not a guarantee. The honest options are a dedicated stress harness or a serialization fix; we chose to document, monitor, and keep the E2E suite deterministic. Pure logic (transformers, validator functions) stays in fast unit tests; the repository CTEs get a thin integration layer; E2E carries the semantics.

The test directory — a worked example

The classes above say what kinds of tests exist; this is what the actual suite looks like, as a directory per operation. The example entity is a car: family car, fields model (string, non-empty validator), plateNumber (string, unique per fleet — a business rule), mileage (int), price (num), fleet (child-of relation to a fleet entity), tags (tag set).

Every directory carries three grades of checks. Positive: the operation does the right thing, asserted at all three altitudes. Negative: the operation is refused cleanly — right status, right message, and no trace left in storage. Impossible: states the system must never be able to produce, asserted directly on the rows — these are the tests that guard the invariants the database can't.

Caveats

• Serial and slow is a choice, not an accident. One shared database means --runInBand; the suite takes minutes, not seconds. Buying parallelism back (schema-per-worker or database-per-worker) is on the table the day the wall-clock hurts more than the setup cost.
• Truncate-based cleanup couples tests to the schema. The wipe helper must know every table; a new table that isn't added to it leaks state into the next test and fails somewhere else. Symptom to memorize: a test that fails only when run after another.
• Never sleep for time — pass it. Every timestamp-sensitive test captures or supplies explicit timestamps (the API takes timestamp everywhere precisely for this). A test that sleeps to "let time pass" is slow and flaky at boundaries — the ε clamp made that mistake once; captured timestamps fixed it.
• Boot-per-suite is heavy. A full Nest app per file adds up in memory — the suite runs with an enlarged heap and heap logging on. Watch it in CI or the OOM kill will look like a random test failure.
• Assert through public surfaces, peek through raw rows. Altitude 3 reads storage directly but never writes through it — a test that sets up state by inserting rows by hand encodes today's storage details and breaks on the next internal change. Setup goes through the API/services; only assertions go under the hood.

Before you trust it: the decision checklist

Series rules apply — decide each, don't inherit them by accident.

• Real database or approximation. In-memory fakes, SQLite, or the real engine? (my choice — real Postgres with the real migrations; the CTEs and timestamptz semantics are the system, an approximation tests a different product)
• Isolation strategy. Transaction-rollback per test, schema-per-worker, or truncate + serial? (my choice — truncate after each test plus a fresh tenant per test, serial execution; simplest thing that stays deterministic)
• Assertion altitudes. Response-only, or down to the rows? (my choice — all three on every write test; altitude 3 is non-negotiable for the single-timestamp and rollback tests)
• What counts as coverage. Line percent, or specification rows? (my choice — the behavior matrix enumerated: a matrix row without a test is an open bug, whatever the coverage number says)
• Time strategy. Sleeps, clock mocking, or explicit timestamps? (my choice — explicit timestamps captured between steps, exercising the same public parameter users get)
• Race policy. Flaky race tests, a stress harness, or documented acceptance? (my choice — documented acceptance + deterministic suite; races live in Part 1's checklist, not in CI's pass rate)
• Failure ergonomics. What a red test prints. (my choice — the full request/response dump on status mismatch; the deepest stack in the system deserves the loudest failures)

The database can't say "no", so the test suite says it instead — once per rule, at three altitudes, against the real thing.

Series

Part 1 — Storage concept · Part 2 — The abstract repository · Part 3 — Services and the API edge · Part 4 — this one: the suite that keeps the other three honest · Part 5 — Maintenance and migration.