Contracts and trade-offs

The DeskPilotRepository interface (in src/domain/repository.ts) is the contract that separates domain from storage. It declares applyIdempotentCommand as a method that must produce the same recorded result when called twice with the same commandId — that guarantee is not optional or Postgres-specific; the in-memory implementation has to satisfy it too, and the same test file (test/api/api.test.ts) runs against it.

The tenant-isolation choice has two deliberately redundant layers:

• Application layer: every PgRepository method opens a transaction, runs SELECT set_config('app.tenant_id', $1, true) (the parameterized equivalent of SET LOCAL app.tenant_id = $1, which does not accept bind parameters), and only then runs the actual query, scoped WHERE tenant_id = $1.
• Database layer: migrations/0001_init.sql enables row-level security on every tenant-scoped table and creates a tenant_isolation policy comparing tenant_id against that same current_setting('app.tenant_id', true).

alter table tickets enable row level security;
-- (users, suggestions, audit_events and applied_commands get the same alter)

drop policy if exists tenant_isolation on tickets;
create policy tenant_isolation on tickets
  using (tenant_id = current_setting('app.tenant_id', true))
  with check (tenant_id = current_setting('app.tenant_id', true));

Why two layers for the same guarantee? Because PostgreSQL's own documentation is explicit about a detail that is easy to forget: "the table's owner is typically not subject to row security policies" (PostgreSQL, Row Security Policies, accessed 2026-09-28). If a migration team tests RLS while connected as the owner — the most common role during development — the policy will never fail, even when it is broken. That is why migrations/0001_init.sql creates a separate role, deskpilot_app, with no BYPASSRLS and no ownership of any table, and only that role is what test/flow/rls.test.ts runs against. One of the five tests there exists only to prove the opposite case: with no app.tenant_id set at all, a session authenticated as deskpilot_app sees zero rows from any tenant — the same documentation explains why ("If no policy exists for the table, a default-deny policy is used"), and a missing session value produces the same practical effect.

The idempotency trade-off is narrower than it looks at first glance. Stripe's documentation on idempotent requests (accessed 2026-09-28) describes a richer contract than this example implements: Stripe's idempotency key saves the body and status code of the first request — including 500 errors — and can be pruned automatically after at least 24 hours. This chapter's applied_commands table does not expire, does not return the original body byte-for-byte, and is not scoped per API key. It is enough to prove the property this chapter asks for — a retry does not duplicate an assignment or an event — but it is not a general-purpose idempotency implementation, and a reader building this for a real external API should read that API's specific contract rather than copy this table.

The four failures this chapter has to fix, with symptom, cause and response

Failure 1 — Demo without persistence. Symptom: the flow works flawlessly live, but any restart (process, tab, laptop) erases the progress. Cause: the entire state lives in UI variables or an in-memory array in the Node process, never reaching a durable store. Response: real PostgreSQL, with a versioned migration (migrations/0001_init.sql) and a reproducible seed (seed/seed.ts); verification.md documents a specific test — triage and assignment over HTTP, then docker restart series-ai-pe04-pg, then a direct re-read through the repository — confirming the ticket and both audit events survive the container restart.

Failure 2 — Authorization only in the UI. Symptom: swapping the id in the URL, or calling the API directly and skipping the screen, lets one tenant read or change another tenant's ticket. Cause: the "is this your tenant?" check only lives in the React component deciding what to render, never in the handler that actually runs the query. Response: two independent layers, described above — a tenant_id = $1 filter on every PgRepository query, plus an RLS policy at the database level tested against a role that does not own the table. The test tenant B cannot read tenant A's ticket by id returns 404, not 403: the resource's existence is not revealed to the wrong tenant either.

Failure 3 — Sensitive seeds. Symptom: the seed script used "just for local testing" contains real names, emails or phone numbers copied from a customer spreadsheet, or a hardcoded password committed to the repository that later shows up in an environment that should never have had access to it. Cause: short-term convenience — copying a real value is faster than inventing a plausible synthetic one, and a fixed password avoids configuring an environment variable. Response: seed/seed.ts contains only synthetic identifiers (tenant-a, agent-b1) with no name, email or phone field at all; the deskpilot_app role's password is read from DESKPILOT_APP_PASSWORD, never written into the .sql file (the :'deskpilot_app_password' placeholder in migrations/0001_init.sql is substituted at runtime by migrate.ts); .env.example documents the variables with no real secret, and .env sits in this package's .gitignore.

Failure 4 — Too many components, no completed task. Symptom: the design board has twelve screens, each reviewed and approved on its own, but no end-to-end path has ever been tested all the way through. Cause: per-component review rewards local visual polish, not task completion; it is possible to "finish" every screen without ever having completed the triage task once. Response: this chapter's slice has exactly four screens (queue, detail, assignment, history), each mapped to one step of the same task, and the acceptance criterion documented in editorial-spec.md is "the complete task passes," not "the screens exist" — the full happy path: triage, assign, then read history test is the criterion, not a component checklist.

Evaluation: what the tests prove and what they do not

examples/deskpilot/ runs 25 automated tests in total: 14 in test/domain/ (13 inherited from chapter 03 unchanged, plus one new one about retry at the domain level), 6 in test/api/ (the HTTP contract against the in-memory repository, no Docker) and 5 in test/flow/ (the same contract against real PostgreSQL, with the deskpilot_app role). verification.md records the exact command and output for each block, from this session, on 2026-09-28.

What this proves: that the ticket and suggestion state machine stays correct after gaining an HTTP layer and a database; that tenant isolation is redundant across two independent layers, and the database layer fails closed when misconfigured, not open; that retries and double-clicks never duplicate an effect, both in memory and in real Postgres; that the history survives a container restart.

What this does not prove: that a real support agent can use the interface unassisted — examples/usability-protocol.md defines the think-aloud script and the timing instrument, but no session with a real participant happened for this package, and that is marked as pending, not as a result. It does not prove full WCAG 2.2 conformance — the UI applies three specific success criteria (2.1.1 Keyboard, 2.1.2 No Keyboard Trap, 4.1.3 Status Messages, the last one via the aria-live region in src/web/index.html), which is different from a formal conformance claim, which by the W3C's own definition requires a date, a level and a list of pages. And it does not prove real demand for DeskPilot exists — testing the application and validating demand are different questions, and only the first has evidence in this chapter.

Cost, security and reversibility

This slice's operating cost, today, is essentially zero outside engineering time: an ephemeral Docker container (--rm, no named volume, dynamic host port), no managed service, no paid API call — because there is no AI call anywhere in this MVP's path. That is deliberate: the deterministic baseline needs to be cheap to rerun repeatedly, because it will be recreated for every test session and compared against the AI-assisted version in chapter 05.

On the security side, this package's explicit limits are: minimized PII (no fixture contains anything resembling a real person's data), a dev login isolated behind a flag read on every request, a database secret kept out of version control, and two tenant-isolation layers tested adversarially. What is not here: real pilot consent, a retention decision beyond a retentionDays field on the Policy inherited from chapter 03 (not yet enforced by an expiry job), and any claim of regulatory compliance.

Reversibility is high by construction: the container has no named volume, so stopping it erases all state without leaving anything on disk; the migration can be reapplied from scratch against a fresh container at any time; and the single production dependency (pg) is pinned in a lockfile, so reinstalling on a clean machine reproduces the exact same dependency graph.

Exit checklist for this chapter

• npm install from the lockfile, no node_modules copied in.
• series-ai-pe04-pg container started with --rm, no named volume, dynamic port.
• npm run migrate and npm run seed run against the owner connection (DATABASE_URL), never against DATABASE_APP_URL.
• npm test (domain + API, offline) and npm run test:rls (real Postgres) both green.
• Container restart preserves the ticket and its audit history.
• Retrying the same commandId does not duplicate an assignment or an event.
• Tenant B cannot read or list tenant A's ticket, in both the application-level test and the real RLS test.
• Container stopped and removed at the end (docker ps -a --filter name=series-ai-pe04-pg empty).

Exercises

Basic. Run the full setup from examples/deskpilot/README.md on a clean machine: npm install, start the container, migrate, seed, run npm test and npm run test:rls. Acceptance criterion: all three test commands finish with fail 0, and you can explain, without looking at the code, why npm test does not need the container. Commented solution: if npm run test:rls fails with a connection error, the most common cause is .env pointing at a stale port — docker port series-ai-pe04-pg 5432/tcp changes on every docker run (and, as this chapter documented in verification.md, it can even change on a docker restart); re-read the port before rerunning the tests.

Intermediate. Add a test in test/api/api.test.ts confirming the queue screen correctly shows the empty state when a new tenant, with no seeded tickets, logs in. Acceptance criterion: the test fails before any code change (confirming there is no empty fixture tenant to test this against today) and passes once you seed — just for the test, without touching seed/seed.ts — a third tenant with no tickets. Commented solution: the simplest approach is to instantiate a MemoryRepository directly in the test and never insert a ticket for that tenant; listQueue already returns an empty array for a tenant with no rows, so the test is checking the API's contract ({tickets: []}, not an error), not an implementation gap.

Advanced. Chapter 05 is going to introduce a role that can only write Suggestion, never read or write Ticket. Modify migrations/0001_init.sql to create that role (deskpilot_suggester, for instance) with GRANT INSERT on suggestions only, and write a test in test/flow/ confirming that an attempt by that role to read tickets fails for lack of SQL privilege (not because of RLS) — the call should return a Postgres permission error, not an empty list. Acceptance criterion: the test explicitly distinguishes "no privilege on the table at all" (missing GRANT) from "privilege granted but no row visible under RLS" (the case already covered by the existing tests) — those are two different defenses, and conflating them in this preparation for chapter 05 would repeat, in miniature, this chapter's Failure #2.

Glossary

• Vertical slice: a complete end-to-end path (UI → backend → database) for a single task, as opposed to a whole layer (every screen, or the entire database) built without the others.
• Command idempotency: the property that applying the same command (identified by commandId) more than once produces the same effect as applying it once.
• RLS (Row-Level Security): a PostgreSQL mechanism restricting which rows a session can see or modify, by policy, regardless of what the standard SQL privilege check would otherwise allow.
• Non-owner role: a database connection that does not own the tables it accesses, and is therefore actually subject to RLS policies — unlike the owner, who bypasses them by default.

Bridge to chapter 05

The next chapter picks up exactly where this one stops: the suggestions table and the getSuggestion method already exist in the DeskPilotRepository contract, but nothing in this package writes a suggestion yet. Chapter 05 introduces a typed category-and-summary suggestion in shadow mode, with an offline adapter and an optional real provider, a labeled dataset with a separate holdout, and the explicit decision that final priority, assignment and external communication remain human commands validated by the same applyCommand this chapter already tested. No domain rule from this chapter changes; what changes is that, for the first time in the series, there will be a model output to compare against this deterministic baseline — and the baseline is only an honest baseline because this chapter measured what it actually does, not what it looked like doing in a four-minute video.