typescript-test-isolation-unique-slug-collision
Root cause: the suite seeded a school with a fixed slug (demo-school) but a fresh random testSchoolId each run. schools.slug UNIQUE + ON CONFLICT (slug) DO NOTHING made the second run's seed a silent no-op, so the class insert referencing the new random id hit foreign key constraint "classes_school_id_fkey" — the id didn't exist because the surviving row from run #1 had a different id. A hardcoded admin password (devsecret) compounded it: tests only worked against the one dev DB.
The fix has two independent parts (verified project: ~/school-tests):
1. Per-run slug suffix — derive the slug from the per-run id so every run inserts its own row (src/schools.ts):
export async function seedSchool(pool: Pool, testSchoolId: string): Promise<SeedSchool> {
// FIX: slug is unique per run; a fixed slug would make the 2nd run
// silently no-op via ON CONFLICT DO NOTHING and break the class FK.
const slug = `demo-school-${testSchoolId.slice(0, 8)}`;
await pool.query(
`INSERT INTO schools (id, slug) VALUES ($1, $2)
ON CONFLICT (slug) DO NOTHING`,
[testSchoolId, slug]
);
return { id: testSchoolId, slug };
}
2. Env-sourced admin pool password — never hardcode the credential (src/db.ts):
export function adminPool(): Pool {
const password = process.env.PGADMIN_PASSWORD ?? ""; // env-owned, no hardcoded secret
return new Pool({
host: process.env.PGHOST ?? "<ip-address>",
port: Number(process.env.PGPORT ?? 5432),
database: process.env.PGDATABASE ?? "testdb",
user: process.env.PGUSER ?? "postgres",
...(password ? { password } : {}), // omit when unset (trust/socket auth)
max: 5,
});
}
The suite then just uses adminPool() + seedSchool(pool, testSchoolId). Schema stays idempotent (CREATE TABLE IF NOT EXISTS), so re-runs on the same DB never collide. CI exports PGADMIN_PASSWORD; the test code carries no secret at all.
Verified against a real PostgreSQL 18 instance (`testdb` on `<ip-address>:5438`, TCP + scram auth), running the suite twice on the *same* DB: | Scenario | Result | |---|---| | Buggy suite, run 1 (fixed slug, hardcoded `devsecret`) | **pass 1/1** | | Buggy suite, run 2, same DB | **FAIL 0/1** — `insert or update on table "classes" violates foreign key constraint "classes_school_id_fkey"` (seed silently no-op'd) | | Fixed suite, run 1 (env password, per-run slug) | **pass 2/2** | | Fixed suite, run 2, same DB (stale `demo-school` row from buggy run still present) | **pass 2/2** | | Fixed suite, wrong `PGADMIN_PASSWORD=wrongsecret` | **fail 0/2** — `password authentication failed` → proves password is env-sourced, not hardcoded | | Fixed suite, `PGADMIN_PASSWORD` unset | **fail 0/2** — no silent fallback to `devsecret` | | Two concurrent fixed runs, same DB (parallel) | both **pass 2/2**, zero slug collisions | | Final DB state | `count(*) = count(DISTINCT slug) = 13` (all slugs unique; legacy `demo-school` coexists) | | `tsc --noEmit` | clean | Edge cases covered: legacy stale row from a pre-fix run doesn't break the fix (suffix slugs never equal it); missing env var fails loudly instead of using a default secret; parallel runs are isolated because each `testSchoolId` yields a distinct slug; schema creation is idempotent so re-runs don't error.
{"model": "deepseek-v4-flash", "problem_class": "typescript-test-isolation-unique-slug-collision", "result": "passed", "tests": 2}Root cause: the suite seeded a school with a fixed slug (demo-school) but a fresh random testSchoolId each run. schools.slug UNIQUE + ON CONFLICT (slug) DO NOTHING made the second run's seed a silent no-op, so the class insert referencing the new random id hit foreign key constraint "classes_school_id_fkey" — the id didn't exist because the surviving row from run #1 had a different id. A hardcoded admin password (devsecret) compounded it: tests only worked against the one dev DB.
The fix has two independent parts (verified project: ~/school-tests):
1. Per-run slug suffix — derive the slug from the per-run id so every run inserts its own row (src/schools.ts):
export async function seedSchool(pool: Pool, testSchoolId: string): Promise<SeedSchool> {
// FIX: slug is unique per run; a fixed slug would make the 2nd run
// silently no-op via ON CONFLICT DO NOTHING and break the class FK.
const slug = `demo-school-${testSchoolId.slice(0, 8)}`;
await pool.query(
`INSERT INTO schools (id, slug) VALUES ($1, $2)
ON CONFLICT (slug) DO NOTHING`,
[testSchoolId, slug]
);
return { id: testSchoolId, slug };
}
2. Env-sourced admin pool password — never hardcode the credential (src/db.ts):
export function adminPool(): Pool {
const password = process.env.PGADMIN_PASSWORD ?? ""; // env-owned, no hardcoded secret
return new Pool({
host: process.env.PGHOST ?? "<ip-address>",
port: Number(process.env.PGPORT ?? 5432),
database: process.env.PGDATABASE ?? "testdb",
user: process.env.PGUSER ?? "postgres",
...(password ? { password } : {}), // omit when unset (trust/socket auth)
max: 5,
});
}
The suite then just uses adminPool() + seedSchool(pool, testSchoolId). Schema stays idempotent (CREATE TABLE IF NOT EXISTS), so re-runs on the same DB never collide. CI exports PGADMIN_PASSWORD; the test code carries no secret at all.
Verified against a real PostgreSQL 18 instance (`testdb` on `<ip-address>:5438`, TCP + scram auth), running the suite twice on the *same* DB: | Scenario | Result | |---|---| | Buggy suite, run 1 (fixed slug, hardcoded `devsecret`) | **pass 1/1** | | Buggy suite, run 2, same DB | **FAIL 0/1** — `insert or update on table "classes" violates foreign key constraint "classes_school_id_fkey"` (seed silently no-op'd) | | Fixed suite, run 1 (env password, per-run slug) | **pass 2/2** | | Fixed suite, run 2, same DB (stale `demo-school` row from buggy run still present) | **pass 2/2** | | Fixed suite, wrong `PGADMIN_PASSWORD=wrongsecret` | **fail 0/2** — `password authentication failed` → proves password is env-sourced, not hardcoded | | Fixed suite, `PGADMIN_PASSWORD` unset | **fail 0/2** — no silent fallback to `devsecret` | | Two concurrent fixed runs, same DB (parallel) | both **pass 2/2**, zero slug collisions | | Final DB state | `count(*) = count(DISTINCT slug) = 13` (all slugs unique; legacy `demo-school` coexists) | | `tsc --noEmit` | clean | Edge cases covered: legacy stale row from a pre-fix run doesn't break the fix (suffix slugs never equal it); missing env var fails loudly instead of using a default secret; parallel runs are isolated because each `testSchoolId` yields a distinct slug; schema creation is idempotent so re-runs don't error.
{"model": "deepseek-v4-flash", "problem_class": "typescript-test-isolation-unique-slug-collision", "result": "passed", "tests": 2}