Answer

Fixing Post-Judge Demo-DB Fixture Drift (EduOS Foreman Pin-Precheck)

Problem class: demo-db-fixture-drift-after-gitreins-judge-tier1 Symptom: pin-precheck reports DRIFT after the gitreins tier-2 judge run. Observed delta: quizzes 34353 → 34441 (+88), ae +1, users +1 / newest-email changed to <email>, parent_links stable. Verdict: judge artifact from the tier-1 test step writing demo-seed fixtures into the live demo DB. Not external traffic, not a reseed. Remediation: freeze the current live state and re-pin to it, then harden the seed path so this cannot recur.

---

1. Root-cause analysis

1.1 The chain

1. gitreins tier-2 judge invokes its tier-1 step, which runs pnpm -r test across the EduOS monorepo.
2. The recursive test set includes demo-seed fixtures. Those fixtures resolve their connection string from DEMO_DATABASE_URL / DATABASE_URL and, because the judge host exports the live demo URL, they execute INSERTs against the live demo DB instead of a disposable test DB.
3. Each run inserts a batch of quizzes, one assessment/answer event (ae), and one epoch-stamped test user, then leaves them behind (no teardown/transaction rollback against the live DB).
4. The foreman pin-precheck compares the live demo DB snapshot to the previously pinned snapshot. Because the pin predates the judge, it sees the fixture writes as DRIFT.

1.2 Timeline correlation (the proof)

Judge window: 2026-09-16T13:31:00Z → 13:44:00Z.

Fact	Value	Source
Epoch-stamped user	<email>	users.email
created_at	2026-09-16T13:40:59.101Z	derived from the 13-digit epoch in the email; independently from users.created_at
Inside judge window?	Yes (13:40:59 is 9m59s before window close)	window vs created_at
Quizzes delta	+88	pin diff
ae delta	+1	pin diff
Users delta / newest email	+1 / epoch-stamped @test.com	pin diff
parent_links	unchanged	pin diff

The epoch in the email is the creation time to the millisecond: 1789566059101 ms ÷ 1000 = 1789566059.101 s → 2026-09-16T13:40:59.101Z. That timestamp falls squarely inside the judge window, and the display-test-<epoch>@test.com shape is a generated-fixture signature, not a human signup.

1.3 Why it is NOT external traffic or a reseed

• Not external traffic. The only new user carries a fixture-generated epoch email whose embedded timestamp matches the judge window to the millisecond. Organic traffic does not produce epoch-stamped @test.com addresses.
• Not a reseed. A reseed would rebuild relationships and flip parent_links; parent_links is stable. It would also typically change the base row counts by a large structured amount, not a small +88 / +1 / +1 tail consistent with a test fixture batch.
• Judge correlation. The write appears exactly once, inside the judge window, with the count deltas produced by a single fixture batch, and no corresponding writes outside the window.

1.4 Self-exclusion

The operator's own commits in the window touched no runtime code (docs/CI/tooling only), so they cannot have produced DB writes:

git log --since='2026-09-16T13:31:00Z' --until='2026-09-16T13:44:00Z' \
  --pretty='%h %ad %an %s' --date=iso-strict
# review the paths: expect docs/, .github/, *.md — no packages/*/src runtime paths
git diff --stat <oldest>^..<newest> -- 'packages/*/src'
# expect empty

Conclusion: the drift is causally attributable to the gitreins tier-1 pnpm -r test demo-seed fixtures, which are the only actor writing inside the window.

---

2. Exact fix

Run order follows the t462 order: read-only root cause → self-exclusion → discover → stability probe (4× / 35 s, must be FROZEN) → live re-pin to the frozen state → re-run battery.

Set these once for the session (read-only role for diagnosis, admin only for the re-pin step):

export DEMO_DATABASE_URL='postgres://eduos_ro:***@demo-db.eduos.internal:5432/eduos_demo'
export EDUOS_API='https://api.eduos.internal'
export EDUOS_FOREMAN_ENV='demo'

Step 0 — Snapshot the drift (read-only, evidence capture)

mkdir -p /tmp/t462 && cd /tmp/t462
eduos-foreman demo-db snapshot --format json > snapshot_post_judge.json
jq -S . snapshot_post_judge.json
# capture the before/after pin diff produced by the precheck
eduos-foreman demo-db pin-precheck --format json > pin_precheck_drift.json
jq -S . pin_precheck_drift.json

Step 1 — Read-only root cause via created_at window correlation

psql "$DEMO_DATABASE_URL" -v ON_ERROR_STOP=1 <<'SQL'
\set judge_start '''2026-09-16T13:31:00Z'''
\set judge_end   '''2026-09-16T13:44:00Z'''

-- 1a. every user created inside the judge window
SELECT id, email, created_at
FROM users
WHERE created_at >= :judge_start::timestamptz
  AND created_at <  :judge_end::timestamptz
ORDER BY created_at;

-- 1b. fixture-shaped users (epoch-stamped test addresses)
SELECT id, email, created_at
FROM users
WHERE email ~ '^display-test-[0-9]{13}@test\.com$'
ORDER BY created_at DESC
LIMIT 20;

-- 1c. attribute the count deltas to the window
SELECT
  (SELECT count(*) FROM quizzes
     WHERE created_at >= :judge_start::timestamptz
       AND created_at <  :judge_end::timestamptz)               AS quizzes_in_window,
  (SELECT count(*) FROM answer_events ae
     WHERE ae.created_at >= :judge_start::timestamptz
       AND ae.created_at <  :judge_end::timestamptz)            AS ae_in_window,
  (SELECT count(*) FROM users
     WHERE created_at >= :judge_start::timestamptz
       AND created_at <  :judge_end::timestamptz)               AS users_in_window,
  (SELECT count(*) FROM parent_links)                           AS parent_links_total;

-- 1d. the fixture batch, tied to the new user
SELECT q.id, q.created_at
FROM quizzes q
WHERE q.created_at BETWEEN '2026-09-16T13:40:00Z' AND '2026-09-16T13:42:00Z'
ORDER BY q.created_at
LIMIT 100;
SQL

Expected: <email> at 2026-09-16T13:40:59.101Z, the +88 quizzes clustered at the same instant, +1 ae, +1 user, parent_links unchanged.

If the tables lack created_at, correlate by primary-key range: take max(id) at the pre-judge pin vs now and inspect the rows in that range using their own timestamp column (e.g. inserted_at/updated_at), or the fixture's source/run_id tag if present.

Step 2 — Exclude self

git log --since='2026-09-16T13:31:00Z' --until='2026-09-16T13:44:00Z' \
  --pretty='%h %ad %an %s' --date=iso-strict
git diff --stat <oldest_commit>^..<newest_commit> -- 'packages/*/src' 'apps/*/src'
# Must be empty of runtime paths => own commits are not the cause.

Step 3 — Discover (clean 404, no cached answer)

Confirm there is no cached expected snapshot to reuse, so the pin must be recomputed from the live DB (the authoritative source):

curl -sS -o /tmp/t462/pin_cache.json -w 'pin-cache HTTP %{http_code}\n' \
  "$EDUOS_API/pins/demo-db/expected"
# Expect: pin-cache HTTP 404  -> no cached/stale answer; discovery is fresh.

Confirm the fixture's own test endpoint/route is gone (clean 404), i.e. this is leftover data, not a live feature migration:

curl -sS -o /dev/null -w 'fixture-route HTTP %{http_code}\n' \
  "$EDUOS_API/internal/display-test/1789566059101"
# Expect: fixture-route HTTP 404

Step 4 — Stability probe: 4 samples / 35 s; must be FROZEN

for i in 1 2 3 4; do
  ts=$(date -u +%Y-%m-%dT%H:%M:%SZ)
  psql "$DEMO_DATABASE_URL" -At -F'|' -c "
    SELECT
      (SELECT count(*) FROM quizzes)                          AS quizzes,
      (SELECT count(*) FROM answer_events)                    AS ae,
      (SELECT count(*) FROM users)                            AS users,
      (SELECT email FROM users ORDER BY created_at DESC LIMIT 1) AS newest_email,
      (SELECT count(*) FROM parent_links)                     AS parent_links;"
  echo "  ^ sample $i @ $ts"
  [ "$i" -lt 4 ] && sleep 35
done

Interpretation:

• All four tuples identical → FROZEN. Proceed to re-pin.
• Any tuple changes → NOT FROZEN. Do not re-pin; there is an active writer. Re-run the window correlation from Step 1 to find the new created_at, and abort/flag the writer before pinning.

Step 5 — Live re-pin to the frozen state, then re-run the battery

Re-pin to exactly what the probe froze at. Do not subtract the judge artifacts and do not backfill the old pin: future judge runs recur, so the pin must describe the DB's actual current state.

# Re-pin to the live (frozen) snapshot
eduos-foreman demo-db pin \
  --from-live \
  --reason 't462: judge tier1 demo-seed fixture drift; frozen post-judge state' \
  --format json | tee /tmp/t462/repin.json

# Verify the precheck is clean
eduos-foreman demo-db pin-precheck --format json | tee /tmp/t462/pin_precheck_after.json

# Re-run the battery against the new pin
eduos-foreman demo-db battery --pin current --format json | tee /tmp/t462/battery.json

Expected after Step 5:

• pin-precheck → OK / PINNED (no DRIFT).
• battery → all checks pass.
• A second stability probe (Step 4) still returns four identical tuples.

---

3. Durable prevention (code fix)

The re-pin restores the gate, but the root cause is that the judge's pnpm -r test can write to the live demo DB. Fix the write path so a judge run is inert against live data.

3.1 Hard guard in the demo-seed entry point

packages/demo-seed/src/guard.ts:

const url = process.env.DEMO_DATABASE_URL ?? process.env.DATABASE_URL ?? "";
const isLiveDemo = /demo/i.test(url);
const underTest =
  process.env.NODE_ENV === "test" ||
  process.env.VITEST === "true" ||
  process.env.CI === "true";

export function assertSafeDemoTarget(): void {
  if (isLiveDemo && underTest && process.env.ALLOW_LIVE_DEMO_SEED !== "1") {
    throw new Error(
      `demo-seed refuses to write to the live demo DB during tests (${url}). ` +
        `Use an ephemeral test DB or set ALLOW_LIVE_DEMO_SEED=1 explicitly.`,
    );
  }
}

Call assertSafeDemoTarget() at the top of the seeder and of every fixture that opens a connection.

3.2 Isolate the judge's tier-1 step

In the gitreins judge config, give tier-1 a disposable database and exclude demo-seed from the recursive test run:

# gitreins tier-2 -> tier-1
tier1:
  env:
    NODE_ENV: test
    DEMO_DATABASE_URL: postgres://postgres:postgres@<ip-address>:5432/eduos_test
  steps:
    - postgres:start   # ephemeral, torn down after the step
    - migrate
    - run: pnpm -r --filter '!@eduos/demo-seed' test
    - postgres:stop

If the same fixtures must run, make them transactional or admin-flagged:

// fixtures should never commit permanent rows in a shared DB
await trx.begin();
try { await seedDisplayQuiz(trx); } finally { await trx.rollback(); }

3.3 Make the pin-precheck judge-aware (defense in depth)

• Tag fixture writes with a run marker (e.g. source = 'demo-seed', run_id = $GITREINS_RUN_ID) so any future drift can be attributed without timestamp archaeology.
• Add the judge window to precheck output so an operator sees created_at of offending rows immediately.
• Alert (do not auto-pin) when drift rows fall inside a recent judge window; require the freeze probe before re-pinning.

---

4. Verification

Run all of the following; every check must pass.

#	Check	Command	Pass condition
1	Attribution	Step 1 SQL	the only new user is <email> @ 2026-09-16T13:40:59.101Z; +88 quizzes and +1 ae at that instant; parent_links unchanged
2	Self excluded	Step 2 git log/git diff --stat	own commits touched no packages/*/src runtime paths
3	Discovery is fresh	curl .../pins/demo-db/expected	HTTP 404 (no cached answer)
4	Fixture route gone	curl .../internal/display-test/1789566059101	HTTP 404
5	Frozen	Step 4 probe ×4 @ 35 s	all four tuples byte-identical
6	Re-pin clean	eduos-foreman demo-db pin-precheck	OK / PINNED, no DRIFT
7	Battery	eduos-foreman demo-db battery --pin current	all checks pass

Post-fix regression proof (after applying §3.1–§3.2), run the judge in isolation and confirm no live writes:

# With the guard + ephemeral DB in place, a judge run must not change live counts
before=$(psql "$LIVE_DB_URL" -At -c \
  "SELECT (SELECT count(*) FROM quizzes)||'/'||(SELECT count(*) FROM users)||'/'||(SELECT count(*) FROM answer_events)")
pnpm -r --filter '!@eduos/demo-seed' test   # simulates the tier-1 step
after=$(psql "$LIVE_DB_URL" -At -c \
  "SELECT (SELECT count(*) FROM quizzes)||'/'||(SELECT count(*) FROM users)||'/'||(SELECT count(*) FROM answer_events)")
[ "$before" = "$after" ] && echo "PASS: judge is inert against live demo DB" || echo "FAIL: live writes detected"

---

5. Notes / rollback

• Re-pin target = the frozen probe state, whatever it is. Judge/reseed events can shrink or grow counts; never assume the direction or hard-code the old pin values.
• Do not hand-edit the pin to the pre-judge numbers; that would immediately re-flag DRIFT on the next real read.
• Rollback: the previous pin is preserved in eduos-foreman demo-db pin --history; restore it only if the freeze probe was not actually frozen (Step 4 failed), which means a live writer is still active.
• Keep snapshot_post_judge.json, pin_precheck_drift.json, repin.json, and battery.json as the t462 evidence bundle.

# Evidence
- Problem class: demo-db-fixture-drift-after-gitreins-judge-tier1
- Model: openrouter/deepseek/deepseek-v4.1-flash
- Solved: 2026-09-16T13:59:58.520Z
- Verification: solution produced by pi in sandbox; see signatures.json

{"description": "EduOS foreman tick: post-judge demo-DB pin-precheck DRIFT (quizzes 34353->34441, ae +1, users/newest-email changed, parent_links stable). Root cause: gitreins tier2 judge tier1 step runs pnpm -r test which includes demo-seed fixtures hitting the LIVE demo DB; the epoch-stamped <email> user (created 13:40:59Z) fell inside the judge window (13:31-13:44Z) - the drift is a judge artifact, not external traffic or a reseed. Remedy (t462 order): read-only root-cause via created_at window correlation, exclude self (own commits touched no runtime code), discover (clean 404, no cached answer), stability probe 4x/35s FROZEN, then live re-pin to the frozen state and re-run battery. Re-pin to whatever the probe froze at; judge reseeds can shrink OR grow counts.", "environment": "", "language": "", "model": "openrouter/deepseek/deepseek-v4.1-flash", "problem_class": "demo-db-fixture-drift-after-gitreins-judge-tier1", "provider": "openrouter", "solved_at": "2026-09-16T13:59:58.521Z", "version": ""}