2026 07 13 Developer 0110

🎯 Session Summary

I walked the first window (5 of 8) of the Software Developer → Level 0110 "Database Mastery" quest line as a learner, in the planner's dependency-sorted order: Database Fundamentals → Data Modeling → SQL Mastery → Database Migrations → Database Security. All five ran in --mode execute against a real PostgreSQL 16 Docker instance; the sealed engine scored them 79 / 86 / 82 / 69 / 86 (avg 80.4%, 3 pass · 2 warn · 0 fail).

Headline verdict: WARN. The teaching core of every quest is genuinely strong — the relational-model, ER-modeling, JOIN/aggregation, Alembic, and security SQL all executed exactly as documented against live Postgres. But the slice has a recurring, learner-blocking pattern: listed objectives and challenge/checklist requirements the quest body never delivers. Two are hard blockers for anyone typing along verbatim — Database Fundamentals' flagship ACID example references an accounts table that is never created (fails immediately), and SQL Mastery demands a three-table join in both its Intermediate Challenge and its Completion Checklist while its seed schema only ever creates two tables. A maintainer should treat the "objectives promised but not taught" gap as the theme-wide fix.

🗺️ The Journey

#	Verdict	Quest	Score	One-line takeaway
1	⚠️	Database Fundamentals: The Relational Model and ACID	79	Core SQL is flawless, but the ACID accounts example fails as written (table never created).
2	✅	Data Modeling: Schema Design and Database Relationships	86	Solid ER→schema chapter; only a listed "Surrogate vs Natural Keys" bonus is undelivered.
3	✅	SQL Mastery: Query Language Proficiency	82	Every query runs perfectly, but RIGHT JOIN / Window Functions / three-table joins are promised and never provided.
4	⚠️	Database Migrations: Schema Evolution and Version Control	69	Alembic core is excellent; the Docker/Flyway path is broken and Liquibase is claimed but never shown.
5	✅	Database Security: Access Control and Data Encryption	86	Live injection + pgcrypto demos are faithful; GRANT-vs-DROP claim is imprecise and 3 objectives are untaught.

🔬 Evidence

All outcomes below come from the sealed walk-evidence.json (agentic execute engine, per-quest disposable sandbox, real PostgreSQL 16.14). Quoted output is trimmed from that file.

1. Database Fundamentals — 79 ⚠️ (ran 4/8 runnable snippets, 1 failed)

• passed — Chapter 1 CREATE TABLE members + INSERT + SELECT * FROM members returned both Aria and Bram exactly as shown.
• passed — Chapter 2 FK demo: the good insert for member 1 succeeded; member 999 failed with ERROR: insert or update on table "loans" violates foreign key constraint "loans_member_id_fkey" — matching the quest's shown error verbatim.
• passed — Chapter 4 3NF tables (authors, books, loans_3nf) created cleanly; \dt confirmed them, satisfying the Novice Challenge.
• passed — Intermediate Challenge (BEGIN → bad FK insert → ROLLBACK) left count(*) at 0 before and after.
• failed — Chapter 3 ACID snippet BEGIN; UPDATE accounts SET balance = ... errored immediately: ERROR: relation "accounts" does not exist. The quest never issues a CREATE TABLE accounts anywhere.
• reasoned — apt/brew/winget/Docker install lines are standard; only the Linux apt package was confirmed present (Ubuntu 24.04 candidate 16+).

2. Data Modeling — 86 ✅ (ran ⅖ runnable snippets)

• passed — Cloud/Docker path (docker run ... postgres:16, pg_isready) came up cleanly.
• passed — Chapter 2 full schema (departments/courses/students/enrolments) — all four CREATE TABLEs incl. composite PK, CHECK (credits > 0), UNIQUE, FK succeeded; the 4-table JOIN returned enrolment rows with grades (Intermediate Challenge validation).
• passed — Composite-key claim verified: re-inserting (1,1) raised duplicate key value violates unique constraint "enrolments_pkey"; the CHECK and FK constraints both rejected bad inserts.
• reasoned — macOS/Windows/Linux-native setup blocks are well-formed but not executed in the Linux sandbox (correctly OS-gated).

3. SQL Mastery — 82 ✅ (ran 8/11 runnable snippets)

• passed — Seed schema (customers/orders) + all seven query snippets ran with zero errors and produced output matching the inline comments exactly: the WHERE/ORDER BY/LIMIT (Aria, Cora), INNER JOIN (5 rows, no Dorn), LEFT JOIN (6 rows, Dorn NULL), GROUP BY/HAVING (Aria/Bram 200.00, Cora 100.00), subquery (Aria, Bram), CREATE VIEW customer_value, CREATE INDEX, and the BEGIN/COMMIT transaction using currval(...).
• reasoned (gap) — Intermediate Challenge ("Join all three relations") and Completion Checklist ("Wrote a query joining at least three tables") cannot be satisfied: \dt confirms the seed creates only customers and orders.
• reasoned (friction) — the currval() transaction only works in one continuous psql session; the engine reproduced currval of sequence ... is not yet defined in this session when reopened — never flagged in the quest.

4. Database Migrations — 69 ⚠️ (ran 8/10 runnable snippets, 1 failed)

• passed — Flyway-style V1/V2 SQL (CREATE TABLE users, ALTER TABLE users ADD COLUMN display_name) applied cleanly.
• passed — Full Alembic flow: alembic init, revision, filled upgrade()/downgrade(), alembic upgrade head (added status default 'pending'), alembic downgrade -1 (dropped it) — verified via \d orders.
• passed — expand-contract SQL and CREATE INDEX CONCURRENTLY idx_users_handle ON users(handle) both executed.
• failed — Cloud/Docker Flyway line failed: java.net.UnknownHostException: host.docker.internal on native Linux Docker, and it omits -user/-password (error for user 'null'). The postgres container also never sets POSTGRES_DB, so living_schema never exists (verified via psql -U postgres -l).
• reasoned (tested marker) — the Linux pip install alembic psycopg2-binary will hit PEP 668 (EXTERNALLY-MANAGED) on Ubuntu 24.04+; it only worked in the sandbox because its pip.conf pre-sets break-system-packages = true, which a learner's fresh machine won't have.

5. Database Security — 86 ✅ (ran 4/8 runnable snippets)

• passed — Docker path + createdb warded_vault; Chapter 1 role/grant SQL ran clean. Checkpoint verified: as app_login, INSERT/SELECT succeeded, DELETE denied (permission denied for table orders), DROP denied (must be owner of table orders).
• passed — Chapter 2 Python: vulnerable find_user with ' OR '1'='1' returned every row [(1,'alice'),(2,'bob')]; parameterized find_user_safe returned zero rows — matching the claim precisely.
• passed — Chapter 3 pgcrypto: pgp_sym_encrypt/pgp_sym_decrypt returned super-secret-value; the stored payload was confirmed binary ciphertext (\xc30d0407...).
• tested (correction) — even after GRANT ALL PRIVILEGES ON orders, DROP TABLE still failed with must be owner of table orders while DELETE succeeded, showing DROP is ownership-gated, not grant-conferred.
• reasoned — TLS sslmode=verify-full connection string is syntactically valid (tested with sslmode=prefer) but not exercised end-to-end (needs a configured CA/cert, reasonable to skip in a scratch sandbox).

🐞 Issues Found

Grouped by severity. Every item cites tested evidence or a quoted quest line; items I only reasoned about statically are labelled (reasoned).

High

• high · Database Fundamentals · Chapter 3 (ACID example, lines 308–316) · tested. The atomicity worked-example BEGIN; UPDATE accounts SET balance ... fails with ERROR: relation "accounts" does not exist — no CREATE TABLE accounts exists anywhere in the quest. This is the flagship demonstration of the primary "ACID Transactions" objective and breaks for any learner typing along. Fix: add CREATE TABLE accounts (id INTEGER PRIMARY KEY, balance NUMERIC NOT NULL); INSERT INTO accounts VALUES (1,100),(2,100); before the BEGIN block.
• high · SQL Mastery · Intermediate Challenge (line ~390) + Completion Checklist (line ~456) · tested. Both require joining three tables, but the seed schema (lines 195–214) creates only customers and orders (confirmed via \dt). The requirement is literally impossible as written. Fix: add a third seed table (e.g. products + an order_items junction, or a cities table) so a three-table join has something to join, or reword the challenge/checklist to two tables.

Medium

• medium · Database Fundamentals · Secondary Objectives (lines 108, 110) · reasoned. CHECK constraints and "Indexes (preview)" are listed objectives but no CREATE TABLE in the quest uses CHECK, and B-tree/index behaviour is never discussed. Fix: add a CHECK example and a one-paragraph note that a primary key is backed by an automatic index (a simple EXPLAIN on a PK lookup).
• medium · SQL Mastery · Primary Objective "RIGHT joins" (line 99) + Secondary "Window Functions" (line 106) · reasoned. Chapter 2 only shows INNER and LEFT JOIN; RIGHT JOIN appears only in a knowledge-check question (line 283). Window Functions are never mentioned outside the objectives list. Fix: add a short RIGHT JOIN example (or drop it from objectives) and either add a window-function snippet or move it to "further reading."
• medium · Database Migrations · Cloud/Docker path (lines 184–188) · tested. Broken as written: missing -e POSTGRES_DB=living_schema, missing Flyway -user/-password, and host.docker.internal is unresolvable on native Linux Docker. Fix: add the DB env var + credentials and either --add-host=host.docker.internal:host-gateway or a shared docker network.
• medium · Database Migrations · Linux setup (line 171–174) · tested marker. pip install alembic psycopg2-binary fails under PEP 668 on Ubuntu 24.04+/Debian 12+. Fix: recommend a venv (python3 -m venv .venv && source .venv/bin/activate) or note --break-system-packages.
• medium · Database Migrations · Intro (line 135) + Mastery Challenge (line 334) · reasoned. Liquibase is claimed "demonstrated" and offered as an equal option, but no Liquibase changelog/command ever appears (only a doc link); Flyway is only static file anatomy plus the broken info command. Fix: add a minimal worked Liquibase/Flyway run, or stop claiming they are demonstrated.
• medium · Database Security · Chapter 1 comment (line 211) + intro (line 194) · tested. "this login can read, write, AND destroy" / "An application login that can DROP TABLE is a catastrophe" overstates GRANT ALL PRIVILEGES: testing confirmed DROP is ownership-gated (must be owner of table orders) even with ALL granted. Fix: clarify that GRANT ALL enables destructive DML (DELETE/TRUNCATE), not DROP, which requires ownership.
• medium · Database Security · Auditing pillar + Advanced Challenge (line 335) · reasoned. The intro promises "runnable examples" for all four pillars, but Auditing is prose-only (no snippet), yet the Advanced Challenge requires "a log entry capturing who modified what and when." Fix: add a log_statement='mod' or pgaudit example with sample log output.
• medium · Database Security · Secondary Objectives (lines 105, 104) · reasoned. "Row-Level Security" and "Secrets Management" are listed but never taught; the only credential shown is a plaintext literal in SQL. Fix: add a short RLS CREATE POLICY example and an env-var/vault note, or move them to further study.
• medium · Data Modeling · Secondary Objective (line 105) · reasoned. "Surrogate vs Natural Keys" is listed but never discussed; the schema silently uses SERIAL surrogates with no comparison. Fix: add a short surrogate-vs- natural-key paragraph.

Low

• low · SQL Mastery · Chapter 4 transaction (lines 362–367) · tested. currval('customers_customer_id_seq') only works within one continuous session; a learner who reopens psql hits currval ... is not yet defined in this session. Fix: note the single-session assumption.
• low · Data Modeling · 3NF explanation (line 309) · reasoned. "dept_name depends on dept_id, which depends on the course" conflates a 2NF and a 3NF step; the conclusion is right but the mechanism is loosely stated.
• low · Data Modeling · Docker path (lines 184–185) · reasoned. Three of four platform paths createdb modeling_realm, but the Docker path connects to the default postgres db, so the "kingdom" naming silently doesn't carry through.
• low · Multiple · platform-setup snippets · reasoned. brew formula, winget id, and apt package/version drift over time and weren't all executable in this Linux sandbox; periodically re-verify.

No fail-verdict quest and no data-loss/safety issue was observed — all BEGIN/ROLLBACK, GRANT, and injection demos are sandbox-scoped and safe.

🔗 Chain Continuity

Ordering & prerequisites hold. The planned order matches the frontmatter dependency graph: database-fundamentals (no required quests) unlocks the rest; data-modeling and sql-mastery require fundamentals; database-migrations requires data-modeling (recommends sql-mastery); database-security requires fundamentals (recommends sql-mastery). A learner walking this window in order meets every declared prerequisite before it's needed. Nice continuity touch: Data Modeling Ch.3 explicitly says "You met [normalization] briefly in Database Fundamentals," and SQL Mastery's intro ("You have learned to store data; now you will learn to summon it") deliberately hands off from Fundamentals.

Each quest is self-contained by database, which is a strength but also masks gaps. Every quest spins up its own fresh database (datakeep, modeling_realm, query_codex, living_schema, warded_vault) and re-creates its own tables. So there's no stateful hand-off to break — good for isolation. But it means a quest can't lean on a previous quest's schema, and two of them try to anyway:

• (reasoned) Concept jump: manual IDs → SERIAL. Fundamentals assigns primary keys by hand (member_id INTEGER PRIMARY KEY, VALUES (1, ...)), but Data Modeling and SQL Mastery switch to SERIAL PRIMARY KEY with no explanation of the shift to auto-increment — and SQL Mastery's transaction then relies on currval('..._seq'), a sequence concept never introduced. A beginner meets auto-generated keys and sequences with zero scaffolding.
• (reasoned) Migrations Ch.2 assumes an orders table its own body never creates. The Alembic example runs op.add_column('orders', ...), but the quest's Chapter 1 only creates users (V1/V2). The engine's sandbox happened to have orders available (the flow passed), but a learner running the quest standalone against a fresh living_schema has no orders table for the centerpiece example. Migrations recommends SQL Mastery — which does create orders — but in a different database, so it doesn't carry over.
• CHECK constraint continuity is accidental, not designed. Fundamentals lists CHECK as an objective but never shows it; Data Modeling then uses CHECK (credits > 0) without explaining it. Neither quest teaches CHECK explicitly, so the concept falls through the crack between the two.

Where a real developer-class beginner would get stuck: (1) the ACID snippet in quest 1 fails on the first copy-paste; (2) quest 3's Intermediate Challenge asks for a three-table join the schema can't support; (3) quest 4's "no local install" Docker path is the most appealing to a beginner and is exactly the one that's broken on Linux. The teaching prose is consistently strong; the friction is concentrated in the challenge/objective/setup boilerplate over-promising relative to the body.

🧠 Reasoning & Method

• Mode: execute (sealed by the workflow). I consumed walk-plan.json and walk-evidence.json / walk-evidence.md as-is — I did not run, regenerate, or edit the engine or its evidence, and I made no changes to any quest content. My only write is this report.
• What was tested vs reasoned: every passed/failed above is from a command the engine actually ran in its disposable PostgreSQL 16 sandbox (per-quest snippet coverage: 4/8, ⅖, 8/11, 8/10, 4/8 runnable snippets). Items I derived by reading the quest source in plan order — untaught objectives, the SERIAL/ sequence concept jump, the migrations orders assumption, the 3NF wording — are explicitly labelled (reasoned) and cite the exact quoted line.
• Coverage caps & limits: this is window 1 of 2 — the planner selected 5 of the level's 8 quests (stats.windowed, window.size 5). The remaining 3 quests of Level 0110 (e.g. query-optimization, backup-recovery, connection- pooling per the unlock lists) were not walked this run and will be swept in the next window; the level is not certified complete by this session. OS- gated macOS/Windows install paths could not run in the Linux sandbox and were reasoned only. The TLS verify-full path was not exercised end-to-end (needs a CA cert). The migrations pip step only "passed" because the sandbox's pip.conf disables PEP 668 — I've flagged that as a learner-environment gap, not a green.
• Confidence: high on the executed findings (real Postgres output quoted in the sealed evidence), medium-high on the reasoned continuity findings (grounded in quoted quest lines + the frontmatter dependency graph). The engine reported 0 errored quests and avg 80.4% at ~$4.02.

Machine evidence summary (verbatim from walk-evidence.md): "5 quests evaluated · ✅ 3 pass · ⚠️ 2 warn · ❌ 0 fail · avg 80.4%".