What should happen when a checklist item sends a client message?
In this portal, that question starts with a milestone stored as a JSON object inside projects.milestones_json. The same milestone can also be a task linkage through tasks.milestoneKey. It can produce a client-visible update. It can fire a Notification Hub event. It can change what the client sees in the portal and what the operator sees in the CLI.
That is too much authority for one checklist item.
The early temptation was simple: keep milestones as JSONB because project setup needed to be fast. A client project does not need a full ceremony for every step. Sometimes the operator needs to add three milestones from the CLI, mark the first one done, and move on. A separate milestone table felt heavier than the problem.
I still think that part was right.
The part I got wrong was assuming the storage choice was the design decision. It wasn't. The real decision was what happens when that flexible JSON object creates side effects outside the JSON field.
The Real Problem
A milestone stored as JSONB is easy to edit and hard to govern. Postgres will store the array. Drizzle will read it back. TypeScript can normalize the shape. None of that answers the business question: when a milestone is marked done, who is allowed to know?
The portal had several competing truths:
- The admin route knows which project is being changed.
- The client record knows whether notifications are enabled.
- The project knows whether it is client-visible or internal-only.
- The milestone object knows its own
notifyClientandnotificationModevalues. - The task table knows whether linked work is complete.
- The notification layer knows whether to emit an event.
If any one of those edges is checked loosely, the result can be wrong while every individual function still returns 200.
That is the kind of failure that bothers me most. Not a crash. A clean success response attached to the wrong business truth.
The build journals had already taught that lesson elsewhere. One batch found a path where the URL project and update project could diverge inside the same tenant. Another found joined project and client rows being hydrated by ID only after the root row had been tenant-scoped. The first query was safe. The side edge was not.
I was wrong to treat tenant isolation as a problem solved at the start of a request. Every side effect has its own identity boundary.
The Constraints
I did not want a new table just to make the design feel pure. The system was still an internal operating tool. The PRD target was under 50 clients and 20 peak requests per second. The operator needed speed more than relational ceremony.
Milestones also had to stay pleasant from the CLI. The code already had a route that marks a milestone done by a 1-based index. That is not glamorous, but it matches how operators think: first milestone, second milestone, third milestone. Forcing every milestone through IDs too early would make the command surface worse.
But the shortcut had limits.
projects.milestones_json can hold flexible milestone data. It cannot decide whether an email should be sent. It cannot prove the project is tenant-scoped. It cannot decide whether a task title should be backfilled into a milestone key. It cannot stop an internal-only project from producing a client-visible message.
So the storage stayed flexible, and the side effects became strict.
The Design
The core helper is normalizeMilestones() in src/lib/milestones.ts. It does the unglamorous work first: discard empty names, preserve known keys, generate missing keys, deduplicate collisions, and coerce done into a real boolean. That gave the JSONB field a predictable shape without changing the database design.
Then syncProjectMilestoneStatus() handles the inverse direction. If tasks are linked to a milestone key, their state can mark a milestone complete only when the linked tasks justify it. That lets the task table and JSONB array communicate without pretending JSONB is relational.
The more interesting function sits in src/routes/admin/projects.ts. It is small enough to look boring, which is why I like it.
function shouldEmitMilestoneNotification(params: {
notifyClient?: boolean;
notificationMode?: 'silent' | 'material_updates' | 'all';
preferenceMode: NotificationPreferenceMode;
notificationsEnabled: boolean;
projectVisibility: typeof projects.visibility.enumValues[number];
}) {
if (!params.notificationsEnabled) return { emitted: false, reason: 'client notifications disabled' };
if (params.projectVisibility === 'internal_only') return { emitted: false, reason: 'project is internal only' };
if (params.notifyClient === false) return { emitted: false, reason: 'notifyClient false' };
if (params.notificationMode === 'silent') return { emitted: false, reason: 'notificationMode silent' };
if (params.notifyClient === true) return { emitted: true, reason: 'notifyClient true' };
if (params.notificationMode === 'material_updates' || params.notificationMode === 'all') {
return { emitted: true, reason: `notificationMode ${params.notificationMode}` };
}
if (params.preferenceMode === 'material_updates' || params.preferenceMode === 'all') {
return { emitted: true, reason: `project notification preference ${params.preferenceMode}` };
}
return { emitted: false, reason: `project notification preference ${params.preferenceMode}` };
}
This is not a clever algorithm. It is a contract.
The order matters. Client notifications being disabled beats everything. Internal-only project visibility beats an eager milestone setting. An explicit notifyClient: false beats the project preference. Silent mode beats a general setting. Only after those denials does the function permit an event.
That order reflects the business. The safest choice must win first.
The route around it does the heavier lifting. It loads the project by id and tenantId. It loads the client by id and the same tenantId. It normalizes milestones before touching the selected 1-based index. It treats an already-completed milestone as idempotent. It writes a portal update. Only then does it ask whether to emit the Notification Hub event.
The notification event itself is fire-and-forget. That is a separate design choice from the milestone boundary. The client state change should not roll back because an email layer is unavailable. The update exists. The portal can show it. The notification failure can be logged and retried outside the request path.
Tests make the boundary real. The event tests cover milestone completion, suppression when notifyClient is false, suppression when project preference is portal-only, and the case where a portal update should exist even when email does not fire. Those are not framework tests. They are business truth tests.
The backfill route is another scar. Tasks gained milestoneKey after milestone JSON already existed. The route defaults to dry-run and requires confirm=true before it mutates task titles and milestone keys. That is what a state boundary looks like when the data model changes underneath a live operator workflow: preview first, then write.
What Surprised Me
I expected the risky part to be JSONB. It was not.
The risky part was side-effect drift. A JSON object can be perfectly valid and still produce the wrong portal update. A project can be tenant-scoped and still attach a joined row carelessly later. A notification can have the right event name and the wrong visibility rule.
That changed how I read the rest of the portal code. I stopped asking only, "Is the row scoped?" I started asking, "Which other facts will this action create, and do they share the same boundary?"
That question shows up everywhere in this project: comments, reports, document caches, capacity notices, stale work, handoff summaries, and client asks. The portal is not just storing facts. It is deciding which facts are safe to expose.
The Result
The final design kept the flexibility that made milestones useful from the CLI, but moved the risk into explicit gates. JSONB still holds the editable project steps. Tasks can link to those steps. Completion can produce a portal update. Notifications can fire only when the client, project, milestone, and preference rules agree.
The latest recorded build gate reached 416 total tests, with 398 passing and 18 skipped. More important than the count, the tests caught wrong-but-running states: missing ask classification, stale internal project noise, QA due noise, generic reply drafts, and untrimmed handoff output.
That is the transferable part. Flexible storage is fine when the business fact is local. The moment it creates a side effect, treat it like a state machine.