Migration 003/004 close every DML path to the audit trail and the gradebook — and all of it was
advisory, because the application connected as a Postgres superuser and one SET switched every trigger
off at once. Migration 005 splits the credential: academy owns the schema and runs migrations,
academy_app owns nothing and cannot disable what it must obey.
Milestone 1 — a pilot vertical slice: one learner completes one Algebra I course through an auditable, AI-operated academic system. Nothing in Milestone 1 is delivered end to end yet; the layers exist and are tested in isolation.
make docs.assessment.purpose; append-only triggers + score bounds; then the TRUNCATE and REPLACE holes. All reversible on both engines.FakeProvider / UnconfiguredProvider (KD-008).248 passed, 17 skipped is the offline gate; the skips are the
Postgres branch of every cross-engine test, and they name the engine they skipped. With
ACADEMY_TEST_POSTGRES_URL set you get 265 passed — the same suite plus the 17 that needed a
live Postgres. The counts moved 244 → 248 and 246 → 265 in this pass; the last two of the Postgres figure are the
KD-017 regression tests.
Each was reproduced with a real executed command before the fix, then the same reproduction was re-run to show it now refuses. A fix you have not re-tested is a hypothesis.
Before: TRUNCATE audit_event committed and left 0 rows; TRUNCATE grade CASCADE left 0 grades.
Row-level BEFORE UPDATE/DELETE triggers do not fire on TRUNCATE. After: both REFUSED —
audit_event is append-only: TRUNCATE refused, rows intact.
BEFORE TRUNCATE trigger per table, reusing the existing
academy_reject_mutation() function (TG_OP='TRUNCATE').Before: REPLACE on an existing id committed and changed reason from orig to clobbered with the
row count unchanged. Cause: PRAGMA recursive_triggers=0, so REPLACE's implicit delete fired no trigger.
After: REFUSED, content intact, and a plain INSERT still works.
recursive_triggers=ON per connection in make_engine,
plus a pragma-independent BEFORE INSERT collision guard. Independence proven with the pragma forced OFF.Before: postgres_engine was used by 2 of 244 tests, both schema-shape only. With the URL pointed at a dead port the
whole audit/grade/mastery suite still passed. After: 13 tests run on real Postgres.
any_session parametrises over both engines, resolving postgres_engine lazily.
Disclosed: my first attempt declared it as a fixture parameter, which made both branches skip offline and silently deleted the
SQLite coverage — caught by reading the skip list, not by inspection.Before: sqlite.DATETIME, sqlite.DATE, sqlite.JSON and postgresql.TIME all
passed the guard, because their class names are allow-listed. After: keys on module/class identity — 8 of 8 injections rejected,
including the four that previously slipped through.
_portable_type_offenders(): anything defined under sqlalchemy.dialects is rejected
regardless of its name; the allow-list is a second rule.Before: it hardcoded its own seven-name list and never called the guard. Proven vacuous — after patching the real guard to accept everything, both tests still passed with a JSONB column present. After: the test calls the real function; neutering the guard now FAILS it.
Withdrew C12/C13 in PASS-REPORT-0002 (both described fixes to something already correct in a1c173a); fixed its line
counts (alembic.ini 44, docker-compose.yml 33 — never the claimed 35/42 at any commit); refreshed stale PROJECT-STATE rows.
The unreachable grade → enrollment cascade is recorded as KD-014 rather than "fixed".
git show a1c173a:alembic.ini | grep path_separator → path_separator = os at line 4.
git show a1c173a:docker-compose.yml | grep -A2 ports: → already 127.0.0.1:5442:5432.7 confirmed, 3 partial, 0 refuted. The three partials were C1 (TRUNCATE open), C2 (SQLite INSERT OR REPLACE open) and
C10 (stale report figures). C7 was partial in a different way: the guard failed closed on unknown names but not on allow-listed dialect
names. All four are now closed — see above.
Before: one statement turned every guard off. Reproduced against the real public.audit_event, then re-run as the new role:
role: academy | superuser: True role: academy_app | superuser: False
audit rows: 2 audit rows: 2
default: DELETE -> REFUSED default: DELETE -> REFUSED
REPLICA: SET session_replication_role = replica;
DELETE -> SUCCEEDED (2 -> 0) REPLICA: SET -> REFUSED (permission denied)
TRUNCATE grade CASCADE
-> SUCCEEDED REPLICA: TRUNCATE -> REFUSED
DROP TRIGGER -> SUCCEEDED DROP TRIGGER -> REFUSED (must be owner)
DISABLE TRIGGER -> SUCCEEDED DISABLE TRIG -> REFUSED (must be owner)
DROP TABLE grade -> attempted DROP TABLE -> REFUSED (must be owner)
DROP CONSTRAINT -> SUCCEEDED CREATE in public -> REFUSED (permission denied)
(score=99 then ACCEPTED)
Root cause was docker-compose.yml setting POSTGRES_USER: academy, which the
official image creates with rolsuper, rolcreatedb, rolcreaterole and
rolbypassrls all TRUE.
academy_app is NOSUPERUSER,
NOCREATEDB, NOCREATEROLE, NOREPLICATION, NOBYPASSRLS, owns nothing, has no DDL and
no TRUNCATE — so the privilege is the first line of defence and migration 004's BEFORE TRUNCATE
trigger is the second. The role is created in a migration, not an initdb script: initdb runs only on first cluster
init, so on the existing database it would never have executed and the fix would have silently not applied. Verified
reversible on both engines; the acceptance test attempts the bypass as the app role and asserts each is refused.The schema permits naming a diagnostic; the service that must refuse grading it does not exist. Until Pass 3 the rule lives only in documentation. Closes in Pass 3.
A partial unique index is not portable, so a service must enforce it. Closes in Pass 3.
The validator exists in academy.domain.validators but is not wired to the remediation branch path. It must close before
any generated item is shown to a learner. Closes in Pass 3.
Neither has this pass. Two prior rounds each found real holes in work that had passed its own tests — including one that found a hole in the tests rather than the code. Pass 2.3 is the same shape of self-asserted claim that failed verification in Pass 2.2.
You asked that the audit trail not be called "append-only" unless every path tried is refused. So here is every path that was actually run.
session.execute(update()) · session.execute(delete()) · raw SQL
UPDATE · raw SQL DELETE · upsert ON CONFLICT DO UPDATE · TRUNCATE (incl. CASCADE) ·
INSERT OR REPLACE · DELETE with no WHERE · SET session_replication_role=replica ·
DISABLE TRIGGER · DROP TRIGGER · DROP TABLE · DROP CONSTRAINT · CREATE in publicacademy) — it keeps superuser because it is the
migration credential. That is the deliberate boundary: the owner can always alter its own schema. What is fixed is that the
application no longer connects as the owner.005 (was 004). Re-checked and unchanged:
41 relations (40 app tables + alembic_version), academy_reject_mutation(),
ck_mastery_score_range, 2 audit rows, 5 triggers, no stray schemas.
Continue development of the Autonomous AI Academy at
/home/msn0624c/agent-lab/workspaces/academy (HEAD 24b86ff, migration head 005,
gate = `make check`).
CONTEXT: Pass 2.5 closed KD-017 (the app connected to Postgres as a SUPERUSER, so
one SET session_replication_role = replica switched every append-only trigger from
migrations 003/004 off at once). Migration 005 creates `academy_app`:
NOSUPERUSER / NOCREATEDB / NOCREATEROLE / NOREPLICATION / NOBYPASSRLS, owns
nothing, no DDL, and NO TRUNCATE. `academy` keeps ownership and runs Alembic.
Verified reversible on both engines; the acceptance test attempts the bypass AS
the app role. Gates: 249 passed / 18 skipped offline, 267 passed with Postgres.
See reports/PASS-REPORT-0005.md and ADR-019.
Do this first, in this order.
THEN: independent verification of Pass 2.3, Pass 2.4 AND Pass 2.5.
All three are self-reported; none has ever had a verifier. Two prior rounds each
found real holes in work that had passed its own tests — one found a hole in the
TESTS rather than the code. KD-017 is the sharpest example: it survived two
verification rounds because nobody was told to check the PRIVILEGE layer. Go
looking for the layer nobody has been asked about yet.
Method that has worked: blind, adversarial, per-claim verdicts
(CONFIRMED/PARTIAL/REFUTED), every claim backed by a real executed command,
throwaway schemas only, public schema inventoried before and after.
Specific things worth attacking in 2.5:
- Is `academy_app` really NOSUPERUSER, or does it inherit via another role?
- Does ALTER DEFAULT PRIVILEGES actually cover a table created by a LATER
migration? Create one in a throwaway schema and check.
- Can the app role reach `audit_event` at all through a different path
(a view, a SECURITY DEFINER function, a sequence)?
- Does the migration 005 downgrade leave anything behind (a grant, a default
ACL, a membership)?
NOTE: the dev-DB invariant in older prompts ("revision 003, 3 triggers") is
STALE. Current truth: revision 005, 5 triggers, 41 relations,
academy_reject_mutation(), ck_mastery_score_range, no stray schemas.
THEN: Pass 3 — domain services + first thin vertical slice.
Scope: src/academy/services/enrollment.py, services/lesson.py,
services/mastery.py (wrapping domain/mastery.py), services/apply.py,
domain/workflow.py. (DETERMINISTIC-VS-AGENT.md names core/services/*; the
implemented package is src/academy/*, settled in Pass 2.2.)
Read the Pass 3 section in docs/IMPLEMENTATION-ROADMAP.md.
Acceptance, each a named failing-first test, not an inspection:
- State machine refuses: every illegal transition raises, including a
self-transition and a terminal-state exit.
- services/apply.py refuses an artifact whose status is not
validated/approved. (Pass 2.3 built artifact_is_applicable but not the
state-changing path.)
- KD-007 closed: a generated remediation branch item whose declared answer
key fails its validator is rejected — wire academy.domain.validators to
the branch path.
- KD-005 closed: one active enrollment per learner per course, enforced by
the service, with a test. No DDL guards it.
- KD-009 closed: apply refuses to create a Grade for an assessment whose
purpose='placement'.
- I2 asserted through a SERVICE: a teacher swap leaves LessonSession /
Attempt / Grade rows and their states byte-identical — not just "the FK
still resolves".
- I11 asserted through a SERVICE: enrollment pins its CurriculumVersion;
publish v2 and the enrollment still reads v1.
Services must connect with resolve_app_url(), NOT resolve_database_url():
the app role is the one with DML-only rights (ADR-019). If a service test needs
to set up schema, that is a migration or a fixture, never the app connection.
Report both gate counts and the new test names.
THEN (after Pass 3 is green): Pass 3.1 Algebra I subject pack, Pass 4.1 faculty.
HARD RULES
- Do not modify anything outside the repo; throwaway probes go in /tmp.
- Use the project venv: cd /home/msn0624c/agent-lab/workspaces/academy &&
unset VIRTUAL_ENV && source .venv/bin/activate
- The gate is `make check` and is offline; it needs no network. Run it WITHOUT
ACADEMY_TEST_POSTGRES_URL, then again with
export ACADEMY_TEST_POSTGRES_URL='postgresql+psycopg://academy:PASSWORD@127.0.0.1:5442/academy'
- Postgres container is 'academy-db' (postgres:16, 127.0.0.1:5442).
If it is not running: `make db-up`.
- After ANY run that touches Postgres, re-inventory the public schema:
41 relations (40 app tables + alembic_version), revision 005, 5 triggers,
academy_reject_mutation(), ck_mastery_score_range, no stray schemas.
If damaged, restore with `alembic upgrade head` after dropping stray schemas
and report it. (This actually happened once, in an earlier pass: a probe
issued DROP TABLE public.activity CASCADE and it committed.)
- Do NOT claim the audit trail is "append-only" or the guards "cannot be
bypassed" unless every path you tried is refused. State which paths you tried
and what is still uncovered. See the coverage boundary in PASS-REPORT-0004.
The now-accurate form: "no DML path we could construct is accepted, on both
engines, for the non-superuser connection the application actually uses."
- Run real commands. Never infer behaviour from reading code. A migration you
have not executed is a hypothesis. (Migration 005's own downgrade was broken
until it was RUN — DependentObjectsStillExist, because database-level CONNECT
was never revoked.)
- Reproduce a failure BEFORE fixing it, then re-run the reproduction to show it
refuses. Do not report a fix you have not re-tested.
- After each pass, update the live report at https://academy.nsystems.live
(author the source at /home/msn0624c/academy-site/index.html — that is the
editable copy — then deploy by copying it to the VPS:
scp /home/msn0624c/academy-site/index.html root@100.123.166.61:/var/www/academy-report/index.html
NOTE the hosting changed on 2026-09-21: the page used to be served from
golden-eye via the cloudflared-shared-public tunnel on 127.0.0.1:8848, but that
host's Wi-Fi force-disconnects every ~15-45s (ath10k "appears to change mode
(expected VHT, found HT)"), which flapped the whole shared tunnel. It now runs
on the always-on Hostinger VPS 31.187.72.46 (nginx vhost
/etc/nginx/sites-enabled/academy-report, Let's Encrypt cert, DNS-only A record —
same pattern as stream.nsystems.live), so it no longer depends on the LAN.
The golden-eye nginx container + tunnel ingress for this hostname are now dead
weight; leave the tunnel entry alone (it still serves ~40 other hostnames):
refresh the data plate, the open/fixed lists and this prompt block.
Render-verify with Playwright at 390x844 before calling it done:
PAGE_ERRORS == [], docScrollW == winW, and inspect the screenshot.
Every verdict on this page comes from a real executed command against a real migrated schema — throwaway Postgres schemas
(av003-family, owner_probe, dep_probe) or an Alembic-migrated SQLite file. The dev
public schema was inventoried before and after every Postgres run; probes that touched real tables ran inside transactions that
were rolled back.
248 passed, 17 skipped — ruff + format + mypy strict + docs guard + pytest265 passed — same suite, cross-engine tests resolve24b86ff, on top of 0633e31 (Pass 2.5) and 6274cbereports/PASS-REPORT-0001..0005.mddocs/DECISIONS.md — ADR-019 records the least-privilege splitdocs/PROJECT-STATE.md — KD-001 … KD-018In an earlier round a probe deliberately attempted to break the fixture's isolation by issuing a fully-qualified
DROP TABLE public.activity CASCADE through the fixture's scoped engine. It succeeded — isolation is default-resolution, not
confinement. The table and the dependent FK were restored from the ORM definition and re-verified, and a later object-level diff against a fresh
migration showed zero missing and zero extra objects.
Takeaway: a fixture that keeps the dev database safe by default is still one qualified statement away from dropping it, which is why the re-inventory check belongs in the pass protocol rather than in a one-off verification.
During this pass one edit of mine also stripped a docstring opener and broke test_persistence.py's syntax. Caught by the syntax
check, reverted with git checkout, re-verified. And I introduced the fixture-parameter bug described under NF-003 — the same class of
failure as the findings themselves, which is why it is written down rather than quietly fixed.