
# The Lint Setting That Ate 12 GB

## What I Was Working On

Our frontend is a pnpm monorepo: one main app plus a few shared libraries, run by Turborepo and built on AWS Amplify. Dependabot opens PRs to update dependencies, and six of the last seven had failed the cloud build.

## The Problem

It looked like dependency trouble, but the build never got that far. A lint process was killed with exit code 137, before type checking or the real build even started. Exit code 137 means the process got SIGKILL (a kill it can't catch). Here the kernel sent it because the 8 GiB build container ran out of memory.

## How I Found It

I measured the peak memory (RSS, the RAM a process actually holds) of each lint task. The six lint tasks run in parallel, and together they peaked at about 11.9 GB.

The cause was one line in the shared ESLint config: `parserOptions.project`. It turns on typed linting, so typescript-eslint builds a full TypeScript program for each package before ESLint can lint it. The typescript-eslint docs say typed linting is "generally as slow as type checking that same project."

That cost only pays off for rules that need types, like `no-floating-promises`. Our config had none. We were paying for type information nobody used.

So why only dependency PRs? A dependency update changes the lockfile. A lockfile change invalidates Turborepo's cache, so all six lint tasks rerun at once. That is exactly the case that ran out of memory.

## The Fix

I deleted one line from the shared ESLint config:

```diff
 parserOptions: {
   ecmaFeatures: {
     jsx: true,
   },
-  project: './tsconfig.json',
 },
```

Then I checked that nothing got weaker:

- The `naming-convention` rule still reports a bad interface name without type info.
- `tsc` still runs with `strict` on and finds 0 errors. Type checking stays `tsc`'s job.
- All six lint tasks pass with Turborepo's `--force` flag, so no result was replayed from cache.

That last point matters. Editing the lint config doesn't change Turborepo's cache key, so a plain `pnpm lint` can replay a result from the old config.

## Results

Peak memory per lint task, before and after:

| Lint task | Before | After |
|---|---|---|
| Main app | 3.20 GB | 0.62 GB |
| Each of four shared libraries | 1.99–2.37 GB | 0.28–0.45 GB |
| All six at once | 11.90 GB | 2.08 GB |

The main app's lint also ran about twice as fast. Same rules, same type checking.

## What I Learned

- **`parserOptions.project` isn't free.** It makes the linter build a TypeScript program per package. Turn it on only when a rule actually needs types.
- **Type checking is `tsc`'s job.** The linter doesn't need to do it again.
- **Ask why only some PRs fail.** These failed because they change the lockfile, which reruns every lint task at once. The new dependencies were never the problem.
- **Write down when to undo it.** I put the condition in the commit message: adding a type-aware rule means bringing the setting back, and a bigger build machine with it. Some options, like `types` on `naming-convention`, fail quietly without it instead of loudly.

## TL;DR

Dependency-update PRs kept failing our cloud build with out-of-memory errors, but the dependencies were fine. ESLint's `parserOptions.project` made typescript-eslint build a full TypeScript program for every package, about 11.9 GB across six parallel lint tasks in an 8 GiB container, while no rule used type information. Deleting that one line dropped peak lint memory to 2.08 GB and made the main app's lint about twice as fast. Type checking stays with `tsc`.

---

*Twelve gigabytes of type information, and not one rule asked for it.*


The Lint Setting That Ate 12 GB


# 17 Duplicate Saves Weren't a Loop

## What I Was Working On

A heavy query kept showing up in our database's slow query log (a list of queries that ran too long). For one user, it ran 17 times for the same quarterly review form. My task was to find what called it and stop the repeats.

## The Problem

Seventeen copies of one query looks like a loop: code that runs the query once per item.

But when I checked the code path, one request ran this query exactly once. If there was a loop, it wasn't in the server code.

## How I Found It

Two logs told the story:

- **The slow query log.** All 17 queries came from one app server, but over 12 different database connections, and their times overlapped. That's not one request looping. That's many requests.
- **The request log.** The same user loaded the page once, then sent 17 POSTs to the form's save endpoint in 14.4 seconds. Each took 1.06–1.55 seconds.

The form was a plain HTML form behind a **Save** button, with no guard against double submits. Each save was slow because the query scanned a whole table (a missing index, tracked as a separate fix). While the page waited, every extra click sent the form again.

It wasn't just one person: a handful of other users had repeated saves too. Luckily, saving only sets a "done" flag to true, so the repeats cost database load, not bad data.

## The Fix

Disable Save as soon as the form submits (simplified):

```js
const form = document.getElementById("save-form");
const save = form.querySelector("button[type=submit]");
form.addEventListener("submit", () => { save.disabled = true; });
window.addEventListener("pageshow", (e) => {
  if (e.persisted) save.disabled = false;
});
```

The second listener matters. After a save, the server redirects to another page. If the user presses Back, the browser may restore the old page from its **bfcache** (back/forward cache, a frozen copy kept in memory). That copy still has Save disabled. `pageshow` with `persisted` set means the page came from that cache, so Save turns back on.

## Results

A throwaway browser test stubbed the save endpoint to answer after 1.5 seconds, then clicked Save 5 times, 300 ms apart:

| | POSTs sent |
|---|---|
| Before | 5 |
| After | 1 |

## The Rails Contrast

In Rails, Turbo Drive does this for you. Its docs say it sets the submit button's `disabled` attribute when a submission begins and removes it when the submission ends. Add `data-turbo-submits-with="Saving…"` and the button text changes while it waits, too.

This page was a server-rendered .NET view with a plain form, so nothing did that for us. The page needed its own small script.

## What I Learned

- **Repeated queries aren't always a loop.** Check their timing and connections. Overlapping queries on many connections meant many requests.
- **Line up the slow query log with the request log.** The database says what ran. The request log says who asked, and how often.
- **A slow button with no feedback gets clicked again.** Disable it while the request is in flight.
- **If you disable a button, plan for Back.** The bfcache can bring the page back with the button still off.

## TL;DR

One user's form was saved 17 times in 14.4 seconds, and it looked like a loop. The slow query log showed the queries overlapping across 12 connections, and the request log showed 17 separate POSTs. Each save took over a second while Save stayed clickable. The fix disables Save on submit and turns it back on when Back restores the page from the bfcache.

---

*The loop wasn't in the code. It was the Save button, clicked again and again.*


17 Duplicate Saves Weren't a Loop


# The Preflight Check That Asks One Question

## What I Was Working On

In "The Migration That Waited 15 Minutes," I capped how long a migration waits for a table lock at 10 seconds. That stops our own migration from waiting forever. It doesn't help when someone else's schema change is already stuck, like a person running an `ALTER TABLE` by hand during a deploy. My job was a check that runs before any migration and refuses to start in that case.

## The Problem

Every statement that touches a table takes a **metadata lock** on it (a lock on the table itself, not its rows). Normal reads and writes take shared ones. They don't block each other, and they're released when the statement ends. A schema change needs an exclusive one, and while it waits, MySQL gives it priority, so later queries on that table line up behind it.

If our migration joins that line, it only makes the line longer.

## What a Queue Looks Like

MySQL's `performance_schema.metadata_locks` table lists locks that are held and locks still wanted. `GRANTED` means a session holds the lock. `PENDING` means it asked and is still waiting.

That made the rule simple. In production, `PENDING` is a fact, not a threshold to tune. Shared locks don't wait on each other, so a pending one means someone is changing a schema right now and a queue already exists.

Staging is different. NHibernate's auto schema update runs schema changes when the app starts there, so `PENDING` is routine noise. The check runs in production only.

## The Fix

A small .NET console app runs before the EF Core migration bundles and reads the lock table for a short list of busy tables:

```sql
SELECT ml.OBJECT_NAME, ml.LOCK_TYPE, ml.LOCK_STATUS, th.PROCESSLIST_ID
FROM performance_schema.metadata_locks ml
JOIN performance_schema.threads th ON th.THREAD_ID = ml.OWNER_THREAD_ID
WHERE ml.OBJECT_TYPE = 'TABLE'
  AND ml.OBJECT_SCHEMA = @schema
  AND ml.OBJECT_NAME IN (@t0, @t1, @t2);
```

Any `PENDING` row that isn't the check's own connection means stop. It exits `0` (go), `2` (a lock is queued) or `3` (can't tell), and leaves `1` for crashes. The deploy script stops before the first migration on any non-zero exit.

Exit `3` matters most. Four setups would make an empty result look like a quiet server: `performance_schema` off, the metadata-lock instrument off, a connection string with no database (so the schema filter compares to NULL and matches nothing), or an empty table list. Each one now refuses instead of passing. I checked it against a local MySQL 8.0, including turning the instrument off to see the `3`.

## What I Cut

The first design was bigger. Each cut came from one question: is there evidence we need this?

- **No modes.** No report-only or off setting. It runs on production deploys, and nowhere else.
- **No 30-second transaction check.** The 10-second lock wait already caps what a long transaction can cost a deploy.
- **No bypass switch.** A `2` clears on its own, so you wait and re-run. Skipping it takes a branch that turns it off: slower on purpose, and it leaves a commit.

It has limits too. It wouldn't have stopped the original incident by itself: when that migration started, nothing was queued yet, and the line formed behind our own `ALTER`. And its first real run is a production deploy, because nothing else runs it.

## What I Learned

- **Gate on a fact, not a threshold.** "A lock is queued" is true or false. "A transaction is 30 seconds old" is a guess about what it does next.
- **An empty result needs proof the sensor was on.** Make "can't tell" its own answer.
- **Ask "is there evidence?" before each guard.** Several in the first draft didn't survive it.

## TL;DR

Before a production migration changes any schema, a small check reads MySQL's `metadata_locks` table. If a lock on a busy table is already `PENDING`, a queue exists, and the deploy refuses (exit `2`) instead of making it longer. If the check can't trust what it sees, it refuses too (exit `3`). No modes, no bypass.

---

*The cheapest time to say no to a migration is before it has touched anything.*


The Preflight Check That Asks One Question


# Teaching AI Agents to Write Fewer, Better Comments

## Why I Dug In

AI coding agents write a lot of comments. Sometimes 20–30 lines sit on top of a single line of code. Those comments pile up, nobody updates them, and one day they no longer match the code. A wrong comment is worse than none, because readers trust it and stop checking.

So, with feedback from teammates, I wrote a comment rule for AI agents and shipped it to our frontend (TypeScript) and backend (C#) repos.

## The Rule

Only two tags:

- **`WHY:`** for a decision that isn't obvious. It must point to the ticket. One sentence by default, three at most.
- **`GOTCHA:`** (optional) for traps grep can't find: a frontend value that must match a backend default, a string-keyed lookup, "the only path that does X."

And three habits:

- **Only what a future editor would miss.** Incident dates, counts, timelines and quoted vendor docs go in the ticket or the PR. The comment is a pointer, not a warehouse.
- **Update, don't add.** Change the comment in the same edit as the code. Never stack a new comment next to a stale one, and don't comment code you aren't changing.
- **No `Last updated`, `@author` or `Generated by`.** Git blame already knows.

```ts
// Bad: narrates the next line. Delete it.
// increment the retry counter
retries += 1

// Bad: a reason with no pointer. The hook flags it.
// WHY: we need to debounce this because of performance.

// Good: one sentence, one ticket.
// WHY: debounced, cells re-render on every keystroke otherwise (#123).
```

In C#, `<summary>` says what a method does, and the why goes in `<remarks>` or a `//` comment. Claude Code loads the rule through `CLAUDE.md`, and an `AGENTS.md` summary covers tools like Cursor and Copilot.

## Enforced by Code, Not by Prompt

A rule in a prompt is only a request. So I added a Claude Code **hook**: a script that runs after every file the agent writes or edits (a `PostToolUse` hook). It is small on purpose:

- **Check-only.** It never rewrites the file. On a violation it returns a "block" with the reason, and the agent fixes the comment and saves again.
- **New text only.** It reads what the agent just wrote, never the whole file, so old comments are never flagged.
- **Fail-open.** If the hook itself breaks, it stays quiet and logs the error.

It blocks tags in the wrong case, a `WHY:` with no ticket reference, the three banned markers, and any `WHY:`/`GOTCHA:` over 6 lines. A block over 8 lines gets a warning. The hook lives in the repo's settings, so it turns on for anyone who opens the repo in Claude Code. A plugin would have been opt-in, and an opt-in rule can't be enforced.

Judgment calls, like whether a `WHY:` is real, stay in PR review.

## The Windows Catch

A teammate tested the hook on Windows, and every violation passed. The hook called `python3`, which the official Windows installer doesn't ship. The name hit a Microsoft Store stub that looks like a real program but prints nothing. The hook got empty output, exited cleanly, and Claude Code read that as approval. The test suite used `python3` too, so its "expect silence" cases passed for free. The fix tries each Python name until one really prints output, and logs a reason when none does.

## What I Learned

- **A comment is a pointer, not a warehouse.** The story lives in the ticket.
- **Put must-follow rules in code.** A hook can send the agent back to fix it. A prompt can only ask.
- **A hook checks shape, not sense.** A three-line run-on sentence passes a line cap, so when long comments kept getting through, I added "only what a future editor would miss" to the rule.
- **Fail-open needs a log and a test that can fail.** Otherwise "broken" and "all clear" look the same.

## TL;DR

AI agents wrote long comments that went stale. Our rule allows two tags, `WHY:` with a ticket and an optional `GOTCHA:`, and keeps the long story in the ticket. A check-only Claude Code hook checks what a machine can on every save; PR review handles the rest.

---

*A good comment points to the story. It doesn't tell it.*


Teaching AI Agents to Write Fewer, Better Comments


# The Index That Would Only Have Existed in Staging

## Why I Dug In

Two busy tables in our app had no index for their main lookup, so every call scanned the whole table. The fix was one index on each. The ticket asked for the index plus a matching declaration in the NHibernate mapping (the C# class that tells NHibernate about a table's columns).

The first suggestion I got was to add the index to the mapping and run the production change by hand. I pushed back, and our own docs already explained why: a mapping change alone isn't enough to ship.

## Two Ways a Schema Change Reaches the Database

Here the mapping is the source of truth for the schema, and there are two paths from it to the database:

- **Startup schema update.** When the app starts, it compares the mappings with the live database and runs the missing `ALTER TABLE` statements itself. (I wrote earlier that this update [only adds, never drops](/blog/2026-05-11-what-my-rails-instincts-got-wrong-about-nhibernate).)
- **EF Core migrations.** The same change is also written as a migration, an explicit schema change script much like a Rails migration. In production, one migration step applies these in order during the deploy.

One config setting decides whether the startup update runs. It's on in staging and dev, and off in production.

## Where a Mapping-Only Index Ends Up

Put those together. Declare an index only in the mapping, and staging and dev create it the next time the app starts. Production never runs that update, so it never gets the index. Staging would have had the index, and production would have kept scanning the whole table.

## Why Not Declare It in Both?

For an existing busy table, the answer is a deliberate no:

- **The startup index sync isn't reliable.** In our setup it re-adds foreign key constraints on every boot, which has left duplicate indexes behind.
- **Index creation becomes a startup side effect.** On a big table, `CREATE INDEX` can take minutes, and it would run just because an app instance started.
- **The mapping can't pin column order.** A multi-column index is declared by giving several columns the same index name, and the order follows the mapping. On one of my tables, the soft-delete column is mapped before the lookup column, so the mapping would have built the index in the useless order.

New, empty tables are the exception. Declaring the index in the mapping is fine there, because it gets built before the table has any rows.

## What We Do Instead

The index ships as an EF Core migration and stays out of the mapping on purpose. MySQL's `CREATE INDEX` has no `IF NOT EXISTS`, so a small helper checks `information_schema` first. Simplified, with generic names:

```csharp
public partial class AddParentIndex_Item : Migration
{
  protected override void Up(MigrationBuilder mb) =>
    mb.CreateIndexIfMissing("Item", "idx_Item_ParentId_DeletedAt", "ParentId", "DeletedAt");

  protected override void Down(MigrationBuilder mb) =>
    mb.DropIndexIfPresent("Item", "idx_Item_ParentId_DeletedAt");
}
```

The lookup column leads and the soft-delete column trails, written down instead of inferred. My two indexes went in exactly like this, as two migrations and no mapping change, merged into our staging branch.

## What I Learned

- **Check which environments run the automatic path.** If only staging runs it, a fix that shows up in staging proves nothing about production.
- **Indexes on busy tables get one explicit path.** A migration pins the column order, runs once during the deploy, and is the path production actually uses.
- **Empty tables are the exception.** An index in the mapping is fine while there's no data yet.

## TL;DR

On our .NET backend, NHibernate's startup schema update creates indexes declared in the mapping, but it's switched off in production. A mapping-only index would exist in staging and never in production. So indexes on existing busy tables ship as EF Core migrations and are left out of the mapping on purpose.

---

*If the update only runs in staging, the index only lives in staging.*


The Index That Would Only Have Existed in Staging


# When a GraphQL Client Silently Drops Your Filter

## What I Was Working On

A bug: some badges on our app's home page stopped updating. A page refresh brought them back.

Our frontend talks to the backend through an in-house GraphQL client. Each query lists the fields it wants in a small `map` function. Only `id` and `type` come for free. Any other field has to be in the map, or it is never fetched.

## The Problem

The badge query filtered meetings on one field. But its map asked for `id` alone. The filter named a field the query never fetched, and the client didn't object.

In production, filtering happens on the server, so the first load never hit the problem. But when a live update arrives over a **subscription** (the server pushing a change), the client re-applies the filter in the browser. That step needs the field. It wasn't there, so it threw an error that roughly said "you're filtering on a field this query didn't ask for."

The throw happened inside an animation-frame callback (code the browser runs before the next paint), outside React's render. No error boundary caught it. Nothing crashed. The client's queue of pending re-filters just got stuck, and the badges froze until a refresh.

## How I Found It

The client has a setting that moves filtering into the browser. With it on, the same error fires on first load and blanks a section of the page. That's loud and easy to spot, but it isn't what users get. Production uses server filtering, where the only symptom is frozen badges. A blank section seen locally with that setting on is not a production symptom.

## The Fix

One line: map the field the filter uses (simplified, names changed).

```diff
 meetings: {
   filter: { isMember: true },
-  map: ({ id }) => ({ id }),
+  map: ({ id, isMember }) => ({ id, isMember }),
 }
```

A teammate had fixed this exact line before, and a large revert later took the fix away. So I also put back their comment explaining why the field must stay mapped. I kept the change to this one query.

## Results

I checked locally: the error no longer fires. The fix is merged into our staging branch.

## What I Left for Later

Neither of these is filed yet:

- **The same trap in a sort.** Another query sorts on a created-date field it never maps. Every node has that field by default, but only `id` and `type` are fetched automatically. I had a fix with a test, but pulled it out so this change covered only the bug users were hitting.
- **A guard in the client.** This kind of bug has shown up before. The client could check filter and sort fields against the mapped fields when it builds the query, where both are known. And the re-filter step could be wrapped so one bad entry can't leave the queue stuck until a refresh.

## What I Learned

- **A client that only fetches what you list should check your filters and sorts against that list.** Without the check, the mistake surfaces far from where you made it.
- **An error thrown in a callback outside React's render skips the error boundary.** The UI doesn't crash. It just stops moving.
- **A debug setting can make a quiet bug loud.** Great for finding it, but the loud version isn't what users see.

## TL;DR

Our GraphQL client only fetches the fields a query maps. One query filtered on a field it didn't map. Production filters on the server, but live updates re-run the filter in the browser, where the missing field threw outside React. No crash, no message, just badges that stopped updating. Mapping the field fixed it. A guard in the client that checks filter and sort fields when a query is built is still on the list.

---

*A filter on a field you never fetched doesn't fail where you wrote it. It fails where nobody is looking.*


When a GraphQL Client Silently Drops Your Filter


# Indexing a GUID Stored in a TEXT Column

## What I Was Working On

One of the most expensive queries in our MySQL slow query log was a permission check. It runs every time a certain list page loads. It looks up rows in `user_org_permissions` by `user_id` and `org_id`, skipping soft-deleted ones (rows marked deleted instead of removed).

That table has over a million rows and only one index: the primary key. So every call read the whole table. The fix looked like one line: add an index on the columns the query filters by.

## The Problem

`user_id` is a `MEDIUMTEXT` column, and MySQL won't index a `TEXT` column as-is. It fails with ERROR 1170, which says a TEXT column in a key needs a key length.

For a TEXT column you must index a **prefix**: `user_id(N)` means "index only the first N characters." For text columns, N counts characters, not bytes.

## Picking the Prefix

A prefix usually trades accuracy for size, since two values that start the same look alike in the index. But these ids are GUIDs, and every stored value is exactly 36 characters (the shortest and the longest are both 36). So a prefix of 36 is the whole value. Nothing is lost.

```sql
-- ERROR 1170: a TEXT column needs a key length
CREATE INDEX idx_user_org ON user_org_permissions (user_id, org_id);

-- Works: 36 characters is the entire GUID
CREATE INDEX idx_user_org ON user_org_permissions (user_id(36), org_id);
```

## Choosing the Columns

- **`user_id` goes first.** One user matched a few rows, while their org matched hundreds. Leading with the column that narrows things down most lets the lookup jump straight to a handful of rows.
- **`delete_time` stays out**, even though the query filters on it. No code path ever soft-deletes this kind of row, and no row ever has been. Adding it would only make the index bigger. MySQL checks `delete_time IS NULL` on the few rows the index returns, which is cheap.

## Why the Migration Uses Raw SQL

Our migration helper for indexes wraps every column name in backticks. Give it `user_id(36)` and it looks for a column literally named `user_id(36)`, which doesn't exist. So I wrote the `CREATE INDEX` by hand in the EF Core migration, with the same "only if the index isn't there yet" check the helper uses. The rollback still uses the normal drop helper, since it only needs the index name.

## Checking It

Locally, `SHOW INDEX` listed `user_id` with a prefix length of 36. `EXPLAIN` showed the query using the new index, with an estimate of one row. It also showed a small leftover filter re-checking `user_id`, which is expected with a prefix index.

## What I Learned

- **MySQL can't index a TEXT column without a key length.** Use `col(N)`. For text, N is characters.
- **A prefix that covers every value loses nothing.** Check the real lengths first. If the shortest and longest are both 36, `(36)` is exact.
- **Lead with the most selective column, and skip columns that never change.** A leftover check on a few rows is cheap.
- **Know what your helpers do to your SQL.** One that quotes every name can't express a prefix length.

## TL;DR

A permission check read a whole table of over a million rows. Its user id column was MEDIUMTEXT, which MySQL won't index without a key length, so I used a 36-character prefix, which is every GUID in full. The user id leads because it narrows the most, the soft-delete column stays out because nothing ever sets it, and the migration uses raw SQL because the helper couldn't write a prefix.

---

*A prefix index sounds like a compromise. When the prefix is the whole value, it isn't.*


Indexing a GUID Stored in a TEXT Column


# Why an OR Made MySQL Skip the Index

## What I Was Working On

Our app stores a tree of nodes, a bit like an org chart. One query loads a node and everything under it. The backend is .NET with NHibernate on MySQL.

The query took the subtree's ids from a **subquery** (a query inside another query) and added the root node with an OR: `id IN (subquery) OR id = ?`.

## The Problem

Each half of that condition is a simple lookup by id. Together, MySQL dropped the index and did a **full scan**: it read every row in the table to return a handful.

Most MySQL indexes are **B-trees**, a sorted structure that is very good at "find these ids." It has no single path for "ids from this subquery, or this one other id."

## Why the OR Hurts

The MySQL manual describes two optimizations that could help. Neither covers this shape:

- **Semijoin** turns `IN (subquery)` into a kind of join. The manual says the `IN` must sit at the top level of the `WHERE`, possibly next to other terms joined by `AND`. Under an `OR`, it isn't.
- **Index merge** can run two index scans and combine them for an `OR`. The manual describes it for ORs of range conditions on keys, like `key1 = 1 OR key2 = 2`. A subquery isn't one of those.

## The Fix

I split the OR into two queries and merged the results in the app:

```sql
-- Before: one query with an OR
SELECT * FROM nodes
WHERE id IN (/* ids in the subtree */) OR id = @root;

-- After: two queries, merged in the app
SELECT * FROM nodes WHERE id IN (/* ids in the subtree */);
SELECT * FROM nodes WHERE id = @root;
```

Now the `IN` is at the top level, and the root is a plain primary-key lookup. I queued both as NHibernate **futures**, which the docs say run together in one round trip when the database supports it.

The same OR was in three queries over two tables: the nodes, and the table linking nodes to users. I split all three. Each new query kept exactly the filters and joins the old one had.

## The Merge Was the Tricky Part

An OR gives you de-duplication for free: a row that matches both sides still comes back once. Two queries don't. Here the root comes back from both, because the tree table keeps a row pairing each node with itself.

So the order matters. **Concatenate first, then de-duplicate by id.** If you de-duplicate the subquery rows first and then add the root, the root appears twice. Here, that would have shown up as a repeated user on a dashboard.

I moved the merge into a small helper and wrote five unit tests for it that need no database. To check they really guard the order, I flipped it on purpose: two of the five failed. Putting it back made them pass.

The change is merged. The before/after `EXPLAIN` check (MySQL's "show me your plan" command) against a real database is still outstanding, so I have no numbers to show here.

## What I Learned

- **An OR across a subquery and a literal can cost you the index.** Each half is easy for MySQL. The combination isn't.
- **Splitting an OR loses its free de-duplication.** Concatenate, then de-duplicate.
- **Look for the same pattern elsewhere.** Here the OR lived in three queries, not one.
- **Break your fix on purpose once.** A test that still passes with the bug isn't guarding anything.

## TL;DR

`WHERE id IN (subquery) OR id = ?` made MySQL scan the whole table. The manual's semijoin and index merge optimizations don't cover that shape. I split it into a subtree query and a root query, merged them in the app by concatenating first and de-duplicating by id second, and pinned that order with unit tests. The database-level `EXPLAIN` check is still to come.

---

*The OR was one line of SQL. Taking it out took two queries and a careful merge.*


Why an OR Made MySQL Skip the Index


# Green Pipeline, Failing Tests

## What I Was Working On

Every push to our staging branch runs a GitHub Actions pipeline: build, deploy, wait for the rollout, then run **smoke tests** (a small Playwright suite that checks the app basically works). A last job posts the result to Slack.

Failed staging deploys were not reaching Slack. I went looking for why.

## The Problem

Two quiet failures stacked up.

**The run stays green.** The smoke-test step has `continue-on-error: true`. When Playwright reports failures, the job and the whole run still finish as "success." The real result lived only in the Slack card, the test report, and the test summary in the job log. Once, several failed staging runs in a row went unnoticed for hours.

**The alert vanishes.** The Slack card had a "View Report" button whose link came from the smoke-test job. If the deploy broke earlier, that job never ran and the link was empty. Slack rejects a button with an empty link, and it throws away the *whole* message, not just the button. And `curl` was called without any `--fail` flag, so it exited 0 even when Slack answered with HTTP 400. The Slack job went green while the alert went nowhere.

So the alert only worked when the smoke tests had actually run. It was silent in exactly the cases it existed for.

## How I Found It

I compared real runs:

- **The build failed:** everything after it was skipped, the Slack job was green, and Slack rejected the message (`invalid_blocks`).
- **The rollout check failed:** same result, no message.
- **The smoke tests ran and failed:** the red card arrived.

The green-but-failed part is how GitHub Actions is designed. A step keeps two results (simplified):

```yaml
- id: smoke
  continue-on-error: true
  run: npm run smoke   # Playwright
# When the tests fail:
#   steps.smoke.outcome    -> failure  (before continue-on-error)
#   steps.smoke.conclusion -> success  (after continue-on-error)
#   the job and the run    -> success
```

Our pipeline already passed `outcome` to the Slack job. That's why the card could say "failed" while the run said "success."

## The Fix (Still an Open PR)

My PR changes only the Slack job:

- **It waits on every deploy stage** and reads each one's result (`needs.<job>.result`). The headline names the stage that broke and says the smoke tests did not run.
- **The report button is added only when its link exists.** "View Run" is always there, since GitHub builds that link itself.
- **`curl --fail-with-body`, and the reply must be `ok`.** If Slack rejects the message, the job now fails loudly.
- **Cancelled runs post nothing.** A newer push cancels the older run, and the newer run reports the real result.

I ran the script locally against a fake webhook (no real messages went out) in nine scenarios, including a failed build, a 400 from Slack, and a cancelled run. I also used a throwaway workflow to check that `needs.<job>.result` is filled in for jobs that call reusable workflows, because the docs don't cover that case.

The PR keeps `continue-on-error` on purpose. Some recent failures were flaky ("staging was slow, reran, passed") or a feature flag that was off in staging. Turning the run red needs a retry policy first.

## What I Learned

- **With `continue-on-error`, green doesn't mean passed.** Check `steps.<id>.outcome` or the test summary in the job log.
- **An alert must not depend on the thing that failed.** Here the button's link came from the job that never ran.
- **One bad block makes Slack drop the whole message, and plain `curl` won't tell you.** Use `--fail-with-body` and check the reply.

## TL;DR

The smoke-test step was `continue-on-error`, so staging runs stayed green even when tests failed, and the Slack card was the only signal. That card was silently dropped whenever the tests never ran: an empty button link made Slack reject it, and `curl` didn't fail on the 400. My open PR makes the alert name the failed stage, skip the empty button, and fail loudly if Slack says no.

---

*An alert that only fires when things work is just a success message.*


Green Pipeline, Failing Tests


# The Migration That Waited 15 Minutes

## What I Was Working On

Our production deploy has a step that runs database migrations with EF Core (.NET's ORM). One deploy ran a small `ALTER TABLE meetings ADD COLUMN ...`. The work itself takes under a second. Instead, the app stalled.

## The Problem

To change a table's structure, MySQL needs an exclusive **metadata lock** (a lock on the table's definition, not its rows). An open transaction that has used the table holds a shared one until the transaction ends, even if it only ran a `SELECT`.

Another session had `meetings` open in an unfinished transaction, so the ALTER waited. While it waited, every new statement on `meetings`, plain reads included, lined up behind it.

How long may it wait? That's `lock_wait_timeout`. The MySQL default is 31,536,000 seconds, one year, and our Aurora database was still on it.

## How I Found It

Two different timers were involved:

- **`lock_wait_timeout`** (server): how long a statement waits to *get* a lock. One year.
- **The client's command timeout**: how long the migration tool waits for an answer. 15 minutes.

The client gave up after about 15 minutes. The server didn't, and the ALTER finished anyway. We never learned which session held the lock: `performance_schema.metadata_locks` only shows the present, and nobody captured it at the time.

It reproduces on a local MySQL. One session runs `START TRANSACTION; SELECT ... FROM meetings LIMIT 1;` and stays open. Then the migration hangs.

## The Fix

I added an EF Core connection interceptor that sets a 10-second lock limit, only on connections a migration run opens (simplified):

```csharp
class LockTimeoutInterceptor : DbConnectionInterceptor {
  public override void ConnectionOpened(DbConnection conn, ConnectionEndEventData data) {
    using var cmd = conn.CreateCommand();
    cmd.CommandText = "SET SESSION lock_wait_timeout = 10, innodb_lock_wait_timeout = 10";
    cmd.ExecuteNonQuery();
  }
}
if (Environment.GetEnvironmentVariable("MIGRATION_RUN") == "1")
  options.AddInterceptors(new LockTimeoutInterceptor());
```

- `lock_wait_timeout` covers metadata locks. `innodb_lock_wait_timeout` covers row locks.
- It runs on every open, because the driver (MySqlConnector) resets session settings when it reuses a pooled connection.
- Only the migration task's start script sets the variable, so the code ships inside the app but stays off there.

Why not set it globally on the database? Then the app's own queries would throw error 1205 ("Lock wait timeout exceeded") instead of waiting, and nothing in the app catches or retries 1205.

Trade-off: a migration that bulk-updates a busy table can now hit the 10-second row-lock limit. Batch that migration rather than raising the limit.

## Results

On a local MySQL 8.0, with another session holding the table for 120 seconds:

| | Time | Outcome |
|---|---|---|
| Before | 1 m 38.67 s | waited out the holder, then succeeded |
| After | 10,056 ms | failed with 1205, exit code 1 |

A statement queued behind the migration on the same table went through as soon as the migration gave up. The change is in production now, but these numbers are from my machine.

## What I Learned

- **A tiny ALTER can stall a busy table.** The risk isn't how long the change runs. It's how long it waits for the lock, because everything after it waits too.
- **Check `lock_wait_timeout`.** MySQL's default is one year.
- **A client timeout doesn't stop the server.** The client gave up; the ALTER still finished.
- **Put the short limit only where failing is safe.** A blocked migration now exits with a clear error. App code that never expects 1205 would just start throwing.

## TL;DR

A one-column migration waited on a table's metadata lock, MySQL let it wait up to a year, and every new query on that table lined up behind it. I set both lock timeouts to 10 seconds on the migration's own connections only, so a blocked migration fails fast with error 1205 and the app is unchanged.

---

*The ALTER needed less than a second. MySQL was willing to let it wait a year.*


The Migration That Waited 15 Minutes


# When the CI Job Ended but the Migration Kept Running

## What I Was Working On

Our app runs database migrations as a one-off task on AWS ECS (Amazon's container service; a "task" is one run of a container). A GitHub Actions job starts the task, waits for it, and passes on its exit code. After a production migration got stuck waiting on a MySQL table lock, I went through that job to see what happens when it ends early.

## The Problem

The job waited with `aws ecs wait tasks-stopped`. Per the AWS CLI docs, that waiter checks every 6 seconds and gives up after 100 checks, so about 10 minutes. When it gave up, the step failed on the spot. The job ended. The task didn't.

Then a second gap showed up. The job looked finished, so it was re-run. Nothing checked whether a migration was still alive, so a second task started next to the first. It queued the same `ALTER TABLE` behind it, then failed with MySQL's "Duplicate column name" error.

## How I Found It

The old job did everything in one shell script: start the task, wait, read the exit code. The runner runs it with `bash -e`, so the first failing command ends the script. When the waiter failed, the lines that read the exit code and printed the task's ID never ran.

The bigger point: the runner only *asks* ECS to start the task, which then runs on AWS. Per GitHub's docs, a cancelled step gets SIGINT, then SIGTERM, then a kill. That ends the script on the runner. Nothing tells ECS to stop.

## The Fix

**1. Start, wait, stop.** I split the one step into three:

- **start** tags the task with `--started-by gha-<run id>-<attempt>` and saves its ID as a step output right after `run-task`.
- **wait** polls every 10 seconds instead of using the 10-minute waiter. The job also got a `timeout-minutes`, set above the longest a migration statement is allowed to run.
- **stop** runs under `if: always()`, which GitHub runs "even when canceled". A job that hits `timeout-minutes` is cancelled too, so this covers success, failure, cancel and timeout.

```yaml
- name: Stop migration task if still running
  if: always()
  run: |
    TASK_ARN="${{ steps.start.outputs.task_arn }}"
    [ -z "$TASK_ARN" ] && exit 0
    STATUS=$(aws ecs describe-tasks --cluster "$CLUSTER" --tasks "$TASK_ARN" --query 'tasks[0].lastStatus' --output text)
    [ "$STATUS" = "STOPPED" ] && exit 0
    aws ecs stop-task --cluster "$CLUSTER" --task "$TASK_ARN" --reason "job cancelled or timed out"
```

(Simplified.) `stop-task` sends the container a stop signal and kills it if it hasn't exited after 30 seconds by default.

**2. Refuse to start a second one.** A new first step lists tasks of the same task-definition family with `--desired-status RUNNING`, which also catches tasks still starting up. If one is alive, it prints who started it and when, and fails before anything is started. It waits up to 60 seconds for a task that is still stopping. It only reads from ECS, so it never stops someone else's task.

Both changes are merged and in production. I tested them against a stubbed `aws` (a fake command with canned answers). When I merged, a real cancelled run was still on the list to prove the stop path end to end.

## What I Learned

- **A CI job that starts remote work doesn't own it.** Failing, cancelling or timing out the job doesn't stop the ECS task. Clean up in an `if: always()` step.
- **Save the ID the moment you have it.** If it's written right after `run-task`, cleanup can find the task wherever the job dies.
- **"Looks finished" isn't "is finished."** Before starting a migration, ask ECS whether one is still alive, and refuse if it is.

## TL;DR

Our migration job started an ECS task and waited on it. When the job ended early, the task kept running, and a re-run could start a second migration beside it. Now the job saves the task's ID right away, stops the task in an `if: always()` step, and refuses to start while another task of the same family is alive.

---

*Ending the job only ended the job.*


When the CI Job Ended but the Migration Kept Running


# The Session Cleanup That Crashed Every 15 Minutes

## What I Was Working On

We self-host Etherpad, the open-source collaborative editor, in Docker on AWS ECS. Our dev instance had just been upgraded to a newer Etherpad. Production was still on an older version.

## The Problem

After the upgrade, dev kept dying. About 15 minutes after boot, Node.js crashed with `Reached heap limit Allocation failed`. ECS replaced the task (the running container), the new one lasted another 15 minutes, and the loop went on.

The tricky part: every quick check said it was fine. A local boot and dev checks a few minutes after startup all looked healthy. Nothing broke until the 15-minute mark.

## How I Found It

It looked like a memory limit. It wasn't:

- The exit code was 1, not 137, and no stopped ECS task said it was killed for using too much memory.
- The heap died with gigabytes of container memory still unused. We hadn't set `NODE_OPTIONS`, so Node picked its own heap limit from the container size.
- Right before the crash, garbage collection ran for almost 7 seconds and freed 4 MB. Node was spending most of its time trying to free memory and getting almost nothing back.

The logs also showed a MySQL `Query inactivity timeout` and a `Session cleanup error` just before each crash. That pointed at a new feature: Etherpad 2.7.0 added a periodic cleanup of old sessions. Its first run starts 5 seconds after boot, and it loads every session key with one unpaged lookup (`findKeys`). On our session table, that query never finished. It just filled the heap.

Someone had already reported the same thing upstream, against Etherpad 2.7.3: same error, same 15 minutes. Their workaround was turning the cleanup off. Etherpad later fixed it by reading the keys in pages of 500.

## The Fix

Etherpad already ships a setting for this, `cookie.sessionCleanup`. I set it to false in our Docker settings file. The catch is how Etherpad checks it:

```ts
// Etherpad: src/node/hooks/express.ts
if (settings.cookie.sessionCleanup !== false) {
  store.startCleanup();
}
```

The check is strict. The string `"false"` is not the boolean `false`, so the cleanup would still run while the setting looked right. Our settings fill values from environment variables, like `"${SESSION_CLEANUP:false}"`, and that is a string. So I read Etherpad's settings code to confirm it turns `"false"` into a real boolean, including the default after the colon. It does, so this works even with no variable set.

## The Trade-off

With cleanup off, expired sessions are no longer deleted, so they pile up in the database. The upstream reporter made the same trade, and it beats an instance that goes down every 15 minutes. It's a stopgap: the line comes out once we upgrade to an Etherpad with the paged cleanup.

The proof I asked for wasn't a quick boot. It was a container that stays up for at least 30 minutes, then an hour in dev with no replaced tasks.

## What I Learned

- **A check shorter than the crash interval proves nothing.** Every check under 15 minutes passed while the crash loop was running.
- **Read the exit code before adding memory.** Here, more memory would only raise Node's heap limit and push the crash later.
- **Loading every row at once is a risk in any background job.** Paging costs little.
- **See how a setting is compared.** With `!== false`, the string `"false"` turns nothing off.

## TL;DR

An Etherpad upgrade added a session cleanup that loads every session key at once, starting 5 seconds after boot. On our dev instance it filled the Node heap in about 15 minutes, crashed, restarted, and did it again. Etherpad's `cookie.sessionCleanup` setting turns it off, as long as the value ends up a real boolean `false`.

---

*The cleanup never cleaned anything. It just kept ECS busy restarting the container.*


The Session Cleanup That Crashed Every 15 Minutes


# When a Failed Lookup Looks Like 'No Access'

## What I Was Working On

When our web app boots, it makes two calls before showing anything: a login call, then a call for the user's organizations (the workspaces they belong to). If they have none, they land on a neutral screen that says, as a fact, that they don't have access to any organization.

During a backend brownout (the backend is up, but many requests fail), healthy users started seeing that screen.

## The Problem

The database had run out of pooled connections, so the backend answered with 5xx errors, not the 401 that would have sent people back to login. The boot code then made one yes/no decision (simplified):

```ts
// Before: two different facts, one boolean
const maybeNoOrg = !hasOrganization || !loginSucceeded

// After: the reason travels with the decision
type SessionOutcome = 'established' | 'no-organization' | 'unverified'
```

"The backend says this account has no organization" and "the call failed, so we know nothing" both came out `true` and went to the same screen. A failed lookup was shown to users as "your access is gone."

Then it stuck. The app used `history.replaceState` (a browser API that changes the URL without a reload) to put the no-access page in the address bar. A refresh opened that page again, however healthy the backend was by then. Only logging out or logging back in cleared it.

## How I Found It

Reports said "clearing the cache fixed it." The cache had nothing to do with it. What helped was landing back on a URL without the no-access path, which is what logging out and back in did.

There was a quieter path to the same screen. The org-list call is typed to return an array, but nothing checks that at runtime. When a response body isn't valid JSON, our fetch client returns the raw text. So a 200 that serves the old login page arrives as an empty string, and `''.length === 0` reads as "no organizations." Wrong screen, no 5xx anywhere.

## The Fix

The fix sits where the decision is made, not after it. Nothing on the server had to change.

- **Carry the reason.** Login now ends in one of three outcomes. Only `no-organization`, a fact the backend stated, gets the no-access screen, even if the org-list call fails afterwards. A login that told us nothing, a failed list call, or an unreadable list go to a new try-again screen whose button runs a fresh boot.
- **Unreadable is not empty.** `Array.isArray` runs before anything reads the length. When it fails, the app logs the payload's type and length, never the body, which can be a whole HTML page.
- **A state screen is a moment, not a place.** Every path that lets the app render now goes through one helper that clears a leftover no-access or try-again URL. A refresh recovers, and a future code path can't forget to do it.

I also added a PostHog event with the reason each time boot parks someone on one of these screens. Any reason other than `no-organization` trending up is the sign this is happening again. New tests pin each reason to its screen.

## What I Learned

- **A boolean that merges a fact with a failure will lie during an outage.** Keep the reason until the moment you pick the screen.
- **"I couldn't read it" is not "it's empty."** A type is a promise, not a check. Check the shape at runtime before you trust it.
- **A URL that describes a moment shouldn't outlive it.** If you write an error state into the address bar, clear it on the next good boot.

## TL;DR

Our boot code ORed "the backend says no organization" with "the lookup failed," so a brownout told healthy users their access was gone, and the URL kept them there. Now the decision carries one of three reasons, an unreadable list counts as unknown instead of empty, and a good boot clears any leftover error URL.

---

*"We couldn't check" and "you have nothing" are different sentences. One boolean can't say both.*


When a Failed Lookup Looks Like 'No Access'


# When an Imported Metric Has to Forget Its Source

## What I Was Working On

Our app lets people import metrics from an accounting tool and keep them in sync. Each imported metric has a menu with two unfinished items: **Delete** and **Convert to manual**. They look alike, since either way the metric stops coming from the tool. But Delete ends the metric, while Convert ends only its link to the tool.

## Delete: Reuse What Already Exists

Delete was the small one. The app already had a soft delete for metrics: archiving, which hides a record instead of erasing it. So the confirm dialog just archives the metric and quietly reloads the list. No backend change was needed.

## Convert: Keep the Row, Drop the Source

Convert needed a new API call that takes just the metric id. It keeps the id, title, frequency, goal, unit and owner. Score history lives in a separate table, so keeping the id keeps the history. What it clears is every field that says where the metric came from:

```csharp
static void ClearProvenance(Metric m) {
    m.Provider = null;    // which integration it came from
    m.AccountRef = null;  // which connected account
    m.LineRef = null;     // which external line it mirrors
    m.Detail = null;
    m.IsSynced = false;
    m.LastSyncAt = null;
    m.SyncStatus = null;
    m.SyncError = null;
}
```

## Why Every Source Field Has to Go

"Stop syncing" sounds like flipping one flag. But other parts of the system use these fields to find the metric again:

- **The provider field** decides whether a metric shows as imported or as manual.
- **The source fields as a whole** keep the provider-wide sync switch from adding it back.
- **The importer's "already imported?" check** matches on provider, account, line and frequency. While those still match, a re-import finds this old metric instead of making a new one.

## It Can't Depend on the Vendor

Convert never calls the outside tool and needs no access token for it. That's on purpose: it has to work even when the connection to the tool has expired. It's also safe to repeat. Converting a metric that's already manual returns success, not an error.

## What I Learned

- **"Remove it" and "stop syncing it" end different things.** Delete ends the record. Unlinking ends only its link to the source; the id and history stay.
- **Unlinking means clearing every field that lets the system find the record again,** not just the sync flag.
- **Test the unlink against the real check.** "It matched before, it doesn't after" proves more than checking that a few fields are empty.
- **A local unlink shouldn't need the vendor.** It has to work on an expired connection.

## TL;DR

Deleting an imported metric reused the existing archive feature. Converting one to manual keeps its id, settings and history but clears every field that records where it came from. Those fields are what the importer and the sync use to find it again, and the convert works without the vendor connection.

---

*Stopping a sync isn't a flag flip. The record has to forget where it came from.*


When an Imported Metric Has to Forget Its Source


# Keeping Form Entries Alive With sessionStorage

## What I Was Working On

Our sign-up has two steps. Step 1 is a HubSpot form, embedded in our page: name, company, phone, email and a "How did you hear about us?" dropdown. Step 2 creates the account.

## The Problem

The check for an email that's already taken only runs at the final submit on step 2. So users found out at the very end, and step 2 had no Back button. The goal: let them go back, fix the email, and keep everything else.

## The Tricky Part

Step 1's values were only handed to step 2 in memory, through the router. A plain Back button would bring back an empty form. The entries also had to survive a page refresh and be cleared after a successful sign-up. And step 1 isn't our form, so filling it back in means reaching into someone else's HTML.

## The Fix: sessionStorage as a Backup

`sessionStorage` is browser storage that belongs to one tab. It survives a reload and is cleared when the tab closes. Step 2 reads the router data first and falls back to storage, for example after a refresh. The saved entries are cleared as soon as sign-up succeeds.

## Treat Storage as Optional

Storage can fail, for example when the browser blocks sites from saving data. So every call is wrapped in try/catch and logs a warning instead of breaking the page. If storage fails, the flow works just like it did before.

```js
const KEY = "signup.step1";
export function loadProfile() {
  try {
    const raw = sessionStorage.getItem(KEY);
    return raw ? JSON.parse(raw) : null;
  } catch (e) {
    console.warn("profile load failed", e);
    return null;
  }
}
```

## Filling In a Form You Don't Own

- **Setting a field's value from code doesn't tell the form.** The form listens for input events, so we send those events ourselves.
- **The dropdown is matched by option text.** The tool can rewrite values, and positions change when someone reorders the options.
- **The fill runs once per form, right when it's ready.** With no later writes, nothing can overwrite what the user types.

## What I Learned

- **sessionStorage fits a multi-step form.** It belongs to one tab, survives a reload and is gone when the tab closes.
- **Treat storage as optional.** Wrap every call, and make the fallback the behavior you already had.
- **Setting a value from code isn't a user edit.** A form you don't own listens for events, so send them.
- **Match by text when you don't own the options.** Values and positions can change underneath you.

## TL;DR

A taken email was only rejected at the last step of a two-step sign-up. A Back button plus a small sessionStorage helper lets users fix it without retyping, even after a refresh. Every storage call is wrapped, so if storage fails, the old flow still works.

---

*The best way to never overwrite the user's input turned out to be writing it exactly once.*


Keeping Form Entries Alive With sessionStorage


# When Frequency Became Part of a Metric's Identity

## What I Was Working On

Our app imports metrics from outside tools, and a nightly job keeps the chosen ones in sync. Each imported metric is one line from the source, like revenue, tracked at one frequency, like weekly or monthly. The new feature: let users change that frequency.

## Why It Can't Be an Edit

A metric's history is a list of scores, one per period, fetched from the source at that pace. Flip a monthly metric to weekly and you get a weekly metric full of monthly numbers. So a frequency change became a fresh import at the new pace. Nothing is edited, and no scores are copied.

## Coexist or Replace

That left one question: what happens to the old metric? Users get two choices:

- **Add new:** keep the monthly metric and also track the same line weekly, side by side.
- **Replace (the default):** import the weekly one, then remove the monthly one. A warning says formulas that use the old metric may break.

## Frequency Joins the Identity

The import already had a rule for "does this metric already exist?" It matched on source, account and line, but not on frequency, so re-importing a line at a new pace just found the old metric. I added frequency to that rule, in one shared function:

```csharp
static Metric FindExisting(IEnumerable<Metric> existing, Source source,
    string accountId, string lineKey, Frequency frequency) =>
  existing.FirstOrDefault(m =>
      m.DeletedAt == null
      && m.Source == source
      && m.AccountId == accountId
      && m.LineKey == lineKey
      && m.Frequency == frequency);
```

The import and the API's up-front check both call it, so they always give the same answer.

## Everything Keyed on the Old Identity

Adding frequency was one line. The real work was finding everything that assumed "one metric per line":

- **The failure marker** would have flagged the healthy monthly metric when a weekly import failed.
- **The status list** kept only the first metric per line, so a second frequency never showed up.
- **The frontend** stored results by line id, so a second frequency would overwrite the first. The key is now line id plus frequency.

## Replace Is Create, Then Delete

Replace imports the new metric first, and only then removes the old one with a soft delete (marked as deleted, not erased). If something fails in between, the user has two metrics instead of zero. Replace also copies over the old metric's sync setting, because a fresh import starts unsynced.

## What I Learned

- **If changing a field would break the history, that field is identity.** Changing it means creating a new record.
- **Widening an identity key is a search problem.** Every lookup, map and count built on the old key has to change too.
- **Define the identity once.** When two code paths share one function, they can't disagree.
- **Swap by create-then-delete.** A failure in the middle leaves an extra record, never a missing one.

## TL;DR

An imported metric's scores are fetched per period, so changing its frequency can't be an edit. I made frequency part of the metric's identity, so a change is a fresh import, either next to the old metric or replacing it. Most of the work was finding everything still keyed on the old identity.

---

*Adding frequency to the key was one line. Finding everything that assumed the old key was the actual feature.*


When Frequency Became Part of a Metric's Identity


# Connected, Expired, Disconnected, Available: Four States, No Migration

## What I Was Working On

Our app lets a team connect an outside service over OAuth (the "allow this app to use your account" step) and import metrics from it. The integrations page got two yes/no fields per provider: `isConnected` and `isAvailable`. My story added a new state, **authorization expired**, with a Reconnect button. That makes four: connected (a live token), expired (the user must re-authorize), disconnected (the user turned it off), and available (never connected).

## The Problem

`isConnected: false` covered three of the four. The stored token couldn't tell them apart either, because both ways a connection ends look the same there:

- **The user disconnects.** The token is soft-deleted (marked deleted, not removed), and their synced metrics are marked "sync off."
- **The nightly sync fails two nights in a row** with `invalid_grant`. The token is soft-deleted, and the synced metrics are marked "reconnect needed."

The error doesn't help either: `invalid_grant` just means "re-authorize," not why.

## Where the Answer Already Lived

The difference was on the metrics. Disconnect writes "sync off," the nightly sync writes "reconnect needed," and a provider with no imported metrics was never connected. The history already told the states apart, so nothing new had to be stored.

## The Fix

I derived the state from that history, in a fixed order (simplified):

```csharp
ConnectionState Resolve(Token? token, IList<SyncStatus> metricStatuses) {
  if (token != null) {
    if (token.RefreshExpiresAt is DateTime exp && exp < DateTime.UtcNow)
      return ConnectionState.Expired;
    return ConnectionState.Connected;
  }
  if (metricStatuses.Count == 0) return ConnectionState.Available;
  if (metricStatuses.Any(s => s == SyncStatus.ReconnectNeeded)) return ConnectionState.Expired;
  return ConnectionState.Disconnected;
}
```

The order is the design: a live token wins, no history means **available**, any "reconnect needed" means **expired**, and any other history means **disconnected**. The result went out as a new `status` field, and the old yes/no fields stayed.

## A Free Expiry Signal

For one provider, we already stored when the refresh token expires. If that time has passed, the grant is dead even if nothing has tried to refresh it yet, so it reads **expired** without calling the provider. This case also returns `isConnected: false`, a deliberate change to an old field, so the page doesn't list the provider as both connected and expired. A test pins it down.

## What I Learned

- **When two causes leave the same mark on one table, look for a side effect that differs.** The token couldn't tell disconnect from expiry. The metrics could.
- **For a derived state, the order of the checks is the design.** It decides every mixed case.
- **Add the new field, keep the old ones.** If an old field's meaning must change, do it on purpose and test it.
- **`invalid_grant` says "re-authorize," not "why."** If the UI needs the why, record it yourself.

## TL;DR

The page needed to tell an expired authorization from a user disconnect and from never connecting. The token couldn't, since both endings soft-delete it, but the sync status on each imported metric could. So `status` became a field derived in a fixed order, with no schema change, and the old yes/no fields stayed.

---

*The token knew it was gone. The metrics knew why.*


Connected, Expired, Disconnected, Available: Four States, No Migration


# When Your Feature Flag Only Covers the Frontend

## Why We Changed the Plan

We were building a new integrations page: which data sources are connected, a way to start an import, and a list of past imports. Product wanted to test each piece on staging (our test environment) as it landed, not all at once at the end of a long-lived branch. So each story became a *vertical slice*: the smallest piece you can test in the UI. Each slice merged straight into staging behind a PostHog feature flag, a switch that was on in staging and off in production.

## The Flag Only Hides Half

The flag lives in the frontend, where it hides the nav link and the page's route. The backend has no flag. A new API query is live in production as soon as the backend ships, whether or not anyone can see the page that calls it. We accepted that because the new queries only read data and checked permissions, and the one destructive feature was put off.

## Keeping the Backend Safe

- **Read-only.** An early version soft-deleted known-dead tokens (marked them deleted instead of removing them). That write came out, so the slice stayed a pure read.
- **Add beside, don't change.** The existing connection check only looked at the caller's own token, and onboarding relied on it. Instead of changing it, I added an organization-level check next to it.
- **No schema changes.** On staging, a merge auto-deploys, and mapping changes alter the shared database. Reverting the code doesn't undo that.

## The Change No Flag Covers

One slice added a server-side guard: changing the frequency of an imported metric now throws an error. Showing the field as read-only in the UI wasn't enough, because an existing edit path could still change it without regrouping the metric's scores. It's the one existing behavior these slices changed, and it applies to every imported metric as soon as it ships.

## Keeping the Frontend Harmless

With the flag off, there's no nav entry and the URL redirects to a "not found" page. The catch: the flag hook returns `undefined` until flags load, so it has three states, not two.

```tsx
const enabled = useFeatureFlagEnabled('new-page-flag')

useEffect(() => {
  if (enabled === false) navigate('/404')
}, [enabled, navigate])

if (!enabled) return null // undefined: flags still loading
return <NewPage />
```

Redirecting on "not true" would send people who *do* have the flag to a 404 before the flags load. Each slice's data is also optional: if import history fails to load, the page shows an empty list instead of going blank.

## What I Learned

- **A frontend flag hides UI, not endpoints.** Treat every backend slice as shipped to production.
- **Make the unflagged half safe on its own:** read-only, permission-checked, new code beside old, no schema changes.
- **Changes to existing backend paths have no flag at all.** Know exactly which ones a slice contains.
- **Flag hooks have three states.** Redirect on `false`, not on "not true yet."

## TL;DR

We shipped a new page as small slices merged straight to staging, hidden behind a frontend feature flag. The flag hid the page, not the backend, so each new query was live in production once shipped. The backend slices stayed read-only with no schema changes, and the frontend treated each slice's data as optional.

---

*A feature flag only covers the code that reads it.*


When Your Feature Flag Only Covers the Frontend


# When Every Funnel Visit Needs Exactly One Ending

## Why I Dug In

My task was to add PostHog analytics events to a multi-step setup flow. The goal was a funnel: a view of where people drop off, and why. Each event is one line of code. The real design work was deciding **how a visit ends**.

## Not Every Exit Is an Abandon

A deliberate **Skip** gets its own event, so it never counts as a drop-off. "Abandoned" covers only real exits, and it records how the user left:

- `tab_closed`: the tab or browser closed mid-flow
- `navigated_away`: the user clicked "Leave anyway" on our leave-confirmation dialog
- `error_forced`: a dead end, like an import that failed for good

## One Ending Per Visit

The trap: `completed` fires on the result screen, and if the user then closes the tab, the tab-close handler logs `tab_closed` too. Now one visit is both finished *and* abandoned. So `completed` and `error_forced` mark the visit as "settled," and the other exits check that mark first (simplified):

```ts
const onPageHide = () => {
  const where = flowRef.current // the step the user is on, if any
  if (where == null) return
  if (analyticsState.hasSettled) return // completed / error_forced already ended it
  if (analyticsState.isRedirectingForOAuth) {
    analyticsState.clearRedirectingForOAuth()
    return
  }
  events.captureAbandoned({ step: where.step, exitType: 'tab_closed' })
}
```

The OAuth check matters because leaving for the other service's sign-in screen also unloads the page, and that isn't an abandon.

## Same Button, Two Meanings

Error screens reused the Skip handler for their "Go home" button. Without a guard, every error exit would also log a skip. Now the handler is told which button called it.

## Other Traps

- **Count once.** "Started" fires once per session. The sign-in result is read from the URL, then removed, so a refresh can't fire it again.
- **Bounded values only.** The sign-in error reason comes from the URL, and anyone can put anything in a URL. It's mapped to known codes, plus `other` and `unknown`.
- **Accept some gaps, and write them down.** Browsers don't always fire the page-hide event, especially on mobile, so some exits never arrive. A mid-flow reload also counts as a tab close. Caveats like these were written down for the people building the dashboards.

## What I Learned

- **Design the endings first.** List every way a visit can end, pick which ones are final, and make the rest check them.
- **A skip is not an abandon.** Keep deliberate exits out of drop-off, or the funnel lies.
- **A shared handler needs to know who called it.**
- **Clamp anything that comes from a URL** before it becomes an event value.

## TL;DR

Funnel events are cheap; correct endings aren't. I made `completed` and `error_forced` final with a "settled" flag, sorted the other exits by type, and kept skips out of abandonment. Every value is limited to a known set, and the gaps I accepted are written down.

---

*Firing an event is one line. Deciding what "done" means for a visit is the actual design work.*


When Every Funnel Visit Needs Exactly One Ending


# Warning Users Before They Leave a Half-Finished Flow

## What I Was Working On

Our app has a three-step flow for importing metrics from accounting data. The new ask: if someone clicks an item in the left menu mid-flow, ask before letting them go.

The modal was already designed: a "Leave import?" title, a line saying they'll lose their progress, and two buttons, **Continue import** and **Leave anyway**. Leave only on explicit confirmation, and no warning after a successful import.

## The Problem

"Show a modal when the user leaves" hides three questions:

1. **Which moves count as leaving?** Moving between the flow's own steps doesn't. The flow's **Skip** and **Go home** buttons do, but on purpose, so warning about them is noise.
2. **When is it safe to leave?** A partial or failed import still warns, since leaving gives up the retry. Only "everything imported" turns it off.
3. **How do you pause a navigation?** The modal waits for the user, so the move has to be cancelled now and replayed later.

## How I Found It

Our router (the code that maps URLs to screens) sits on the `history` library, version 4, and left-menu clicks end in `history.push`. So a menu click is a history change, not a page load, and that's where to hook in.

`history.block()` runs a function before every move. Return a string, and by default the browser shows its plain built-in confirm box. Return `false`, and the move is cancelled. We had our own modal, so the plan was: return `false`, open the modal, and replay the move if the user picks **Leave anyway**.

## The Fix

First, a plain function decides whether to block. No React, no router, no modal, so it's easy to test with a table of cases:

```ts
// simplified
function shouldBlockLeave(d): boolean {
  if (d.bypass) return false                               // the flow's own exit button
  if (d.action === 'POP') return false                     // back/forward: out of scope
  if (FLOW_PATHS.includes(d.targetPathname)) return false  // moving within the flow
  if (!d.shouldWarn) return false                          // e.g. the import fully succeeded
  return true
}
```

Then a React hook (reusable component logic) wires it up. When the function says block, the hook returns `false` and opens the modal. **Leave anyway** removes the guard first, so the replay doesn't hit it again, then goes to the full target URL.

**Skip** and **Go home** set a one-shot "bypass" flag just before navigating, and the guard clears it each time it runs. Review caught a gap: if the navigation never happens, the flag stays on and the next menu click slips through unwarned. A microtask (a callback that runs right after the current code finishes) now clears any unused flag.

Back/forward was out of scope. Reloading or closing the tab skips this guard entirely and would need a separate `beforeunload` listener, which wasn't part of this change.

## What I Learned

- **Split "should we block?" from "how do we block?"** The decision is a simple table test. The hook only wires it to the router and the modal.
- **In history v4, `false` cancels and a string prompts.** If you cancel and show your own modal, the replay is on you: unblock first, then go to the full target.
- **A flow's own exits need a way past its guard,** or you warn people about the button they just clicked.
- **A one-shot flag needs a cleanup path** for when the event that uses it never fires.

## TL;DR

To warn users before they leave a three-step import flow, a `history.block` guard cancels the move and opens a modal. A small function decides when: not on back/forward, not between the flow's steps, not after a fully successful import, and not for the flow's own exit buttons. **Leave anyway** removes the guard and replays the move.

---

*The modal was the easy part. Deciding which exits deserve it was the real work.*


Warning Users Before They Leave a Half-Finished Flow


# The Weekly Score That Landed One Column Early

## What I Was Working On

Our app's QuickBooks backend was a team effort, and the pull request I opened to take it to staging (our pre-release environment) merged today. It connects to a QuickBooks company, offers lines from its financial reports as KPIs (key numbers a team tracks), and imports at least 12 months of history into the scorecard, a table with one column per period.

The history comes from the reports themselves. QuickBooks can split a report into one column per day, week, month or quarter, and each column carries its start date. Pair each value with its column's date, and you have a history.

## The Problem

Imported **weekly** scores showed up one column too early. A balance "as of 16 May" appeared under **3–9 May** instead of **10–16 May**. Monthly, quarterly and daily imports were fine.

The value was right. It was filed under the wrong week.

## Why It Happened

Our scorecard doesn't file a weekly score under the Sunday the week starts. It files it under the Sunday **after** the week, so the 10–16 May column is stored as 17 May. Entering a score by hand and uploading a CSV file already follow that rule.

QuickBooks tags each weekly column with the week's real first day, and the importer stored that date as-is. So the 10–16 May value got 10 May, which is the key of the 3–9 May column. For the other cadences, the start date already is the right key, which is why only weekly broke.

The tests missed it because their sample columns had the same start and end date, so nothing looked like a real week.

## The Fix

A teammate landed the fix in the same pull request: a small function that shifts weekly dates as they come in.

```csharp
// simplified
static DateTime ToWeekKey(DateTime periodStart, Frequency frequency) =>
    frequency == Frequency.Weekly
        ? periodStart.StartOfWeek(DayOfWeek.Sunday).AddDays(7)
        : periodStart;
```

The key choice is *where* it lives. A shared helper already snaps any date to its period's boundary, and the shift looks tidier there. But hand-entered scores reach that helper already set to the Sunday after, so shifting inside it would push them a week late. The shift stays in the import layer, the one path that starts from a raw calendar date.

New tests check that the first day, last day and a mid-week day of 10–16 May all land on 17 May, and that other cadences pass through untouched.

## What I Learned

- **When you import into an existing app, its rules are the spec, not the calendar.** "The week of 10 May" sounds clear until the app files that week under 17 May.
- **Compare with the paths that already write the data.** Hand entry and CSV upload agreed. The new importer was the odd one out.
- **Convert at the boundary, not in the shared helper.** If other callers already pass converted values, shifting inside the helper counts twice.
- **Test data where start equals end hides period bugs.** Test a real period's first day, last day and a day in between.

## TL;DR

Weekly KPIs imported from QuickBooks landed one column early. Our app keys a weekly score by the Sunday *after* the week, while QuickBooks reports the week's first day. Adding seven days, only for weekly and only in the import layer, fixed it without double-shifting scores people enter by hand.

---

*A date can be correct and still be the wrong key.*


The Weekly Score That Landed One Column Early


# When a Bulk Import Only Half Works

## What I Was Working On

I was building the screen that appears after a user clicks **Import**. They pick some metrics from their accounting data, set up each one, and import them all at once.

The rules: never block the user from finishing, never silently skip a failed metric, and never lose their settings between retries. Show which metrics failed and why, let them retry only those (at most twice), and let them carry on with the ones that worked.

## The Problem

I first pictured a simple function: send the metrics, get back one result per metric.

The real backend started a **background job** (work the server keeps doing after it has already replied) and answered right away with a job id. Each metric's result arrived later, one at a time, over a **subscription** (a live connection where the server pushes updates). That raised the real question: what if a result never shows up?

## How I Found It

I checked the backend's contract and read the issues raised in its review. Some were exactly that case:

- after a partial failure, the job's "complete" flag never turned true
- if the job failed early, it sent **zero** progress events
- submitting twice created duplicates

None of these were frontend bugs. But they meant "one result per metric" was not guaranteed.

## The Fix

The rest of the screen still gets a plain list of results. Behind it, a small adapter keeps **one map of results, keyed by each metric's stable id**, that several sources can write into:

- **Listen first, then start.** The subscription doesn't need a job id, so I open it before starting the job and miss no early events.
- **Poll only as a backup.** Asking the server for status every few seconds starts only if the subscription breaks, and the subscription wins any conflict.
- **Never wait forever.** The flow ends when the job reports complete, when every metric has a result, or after 60 seconds.

Every exit runs the same final step, and that step fills the gaps:

```ts
function finalize(metrics: Metric[], outcomes: Map<string, Outcome>): Outcome[] {
  return metrics.map(
    (m) =>
      outcomes.get(m.key) ?? { key: m.key, status: 'failed', reason: 'No result reported' }
  )
}
```

A metric that never reported back doesn't vanish. It shows up as failed, with a reason, and can be retried. A retry re-sends only the failed metrics' saved settings and merges the new results into the old ones.

Zero successes can mean two different things, so one flag tracks whether *any* result ever arrived. If some did, the user sees the list of failures and their reasons. If none did, there are no reasons to show, so they get a generic "unable to import" card.

## What I Learned

- **A bulk action has one result per item, not one overall.** Key each result by a stable id, and partial screens and retries get simpler.
- **"No result" is a result, and it's a failure.** Send every exit through one final step that turns gaps into visible failures.
- **Subscribe before you start the job** when the stream doesn't need the job id. Otherwise a fast job can start talking before you're listening.
- **"Everything failed" and "nothing came back" need different screens.** One has reasons to show. The other has none.

## TL;DR

A bulk import that runs in the background can end four ways: all worked, some failed, all failed, or nothing came back. I kept one result per item in a map keyed by a stable id, and sent every exit through one step where a missing result becomes a failure. Partial failure and retrying only the failures followed from that.

---

*The scariest item in a bulk import isn't the one that failed. It's the one that never reported back.*


When a Bulk Import Only Half Works


# Why localhost Broke My Local Redis

## What I Was Working On

I'm a Rails developer, but these days most of my time goes into a .NET backend. Locally, the app runs right on my Mac, while MySQL and Redis (an in-memory data store, used here for things like background jobs and locks) run in Docker. Three connection strings pointed at Redis, and every one of them said `localhost`.

## The Problem

Locally, the Redis connections would sometimes fail to connect. MySQL was fine, and MySQL was on `localhost` too. Same machine, same Docker setup, same hostname: one worked, and the other sometimes didn't.

## How I Found It

The clue was in the Docker Compose file, the config that starts the containers. Redis was published as `127.0.0.1:6379:6379`, so it was only reachable on the IPv4 loopback (the address a machine uses to talk to itself), not on the IPv6 one, `::1`. And on my Mac, `localhost` comes back as `::1` first, even though `/etc/hosts` lists `127.0.0.1` first:

```
$ dscacheutil -q host -a name localhost
name: localhost
ipv6_address: ::1

name: localhost
ip_address: 127.0.0.1
```

.NET sees the same order. So the first address the app tried for `localhost` was one where Redis wasn't listening.

### Then Why Was MySQL Fine?

MySQL was published the same way. But its .NET client tries each address in turn, so when `::1` fails it moves on to `127.0.0.1`. I didn't dig into how the Redis client handles the same list. Two clients, same hostname, same kind of binding: different results.

## The Fix

I changed the three Redis connection strings from `localhost` to `127.0.0.1`, so the app asks for exactly the address Docker publishes. MySQL stayed on `localhost`, since it already worked. You could also fix this on the Docker side by publishing Redis on `[::1]` as well; I picked the connection strings so they'd match the bindings the Compose file already had. This only touches local dev: deployed environments override these strings with environment variables.

## What I Learned

- **`localhost` is a name, not an address.** It gets looked up like any other host name, and on macOS the first answer can be `::1`.
- **The order in `/etc/hosts` isn't the order you get back.** `dscacheutil -q host -a name localhost` shows the real order.
- **A host IP in a Docker port mapping is a limit.** `127.0.0.1:6379:6379` publishes on IPv4 only; publish `[::1]` too if you want IPv6.
- **Make the client match the binding.** If the server side is pinned to `127.0.0.1`, pin the client to it too.

## TL;DR

On macOS, `localhost` can resolve to the IPv6 address `::1` first. Docker published Redis only on `127.0.0.1`, so the Redis connections sometimes failed. Switching those local connection strings to `127.0.0.1` fixed it.

---

*`localhost` sounds like one place. It's really a question, and macOS has two answers.*


Why localhost Broke My Local Redis


# From Hotwire to MobX

## Why I Dug In

I came from Hotwire, the Rails way of building pages: the server renders HTML, Turbo swaps pieces of it into the page, and Stimulus adds a little JavaScript on top. This month I started a new job, and the frontend is a React single-page app that uses MobX for state. The server sends data, not HTML, and the browser decides what the page looks like. So this post maps each Hotwire habit to what took its place.

## Where the State Lives Now

In Hotwire, the state in the browser basically *is* the DOM, the page's live HTML. Here, state lives in JavaScript objects, and the DOM is just React's drawing of them. An in-house GraphQL client (the library that fetches data from the server) keeps each record as one MobX observable, an object MobX watches for changes. New data for the same id is written into that same object, so every component showing the record sees the change.

## Turbo Streams → Subscriptions

A Turbo Stream is server-rendered HTML plus a note saying which element to swap. Here, the server never sends HTML. A component asks for data with a `useSubscription` hook, which runs the first query over HTTP and then listens for live updates over a WebSocket. Each update lands in the same record object as new field values.

## Stimulus → `observer` Components

Instead of Stimulus controllers, components are wrapped in `observer`, which re-renders a component when an observable it read changes. Local state is MobX too, through small hooks used instead of `useState`. Turbo used to disable the submit button during a request; now I set and clear that flag by hand. And any state change after an `await` needs its own action (MobX's wrapper for changing state), because that code runs later, outside the first one.

## DOM Ids → Who Reads What

With Turbo, I learned to draw DOM ids around what changes on its own. With MobX, the question is who reads which observable. So views take a getter function instead of a value, and call it during their own render:

```tsx
export const UserRowView = observer(function UserRowView(props: {
  data: () => UserRowData
}) {
  return <Text type='body'>{props.data().user?.fullName ?? ''}</Text>
})
```

Spreading `{ ...user }` in a parent does the opposite: it reads every field. In a demo, that made a whole user list re-render when one user's name changed.

## What I Learned

- **The server sends data, not HTML.** React works out what the page should look like.
- **One record, one observable object.** That's why a change shows up everywhere the record is on screen.
- **Some things Turbo did for free are now my job.** Disabling buttons during a request, and a fresh action after every `await`.
- **Read late.** Pass getters down, so only the component that shows a value re-renders. Too-broad reads in MobX cost wasted renders; too-broad replaces in Turbo cost the user's cursor.

## TL;DR

Hotwire keeps UI state on the server and ships HTML. This React + MobX app keeps state in the browser and ships data, with one observable object per record kept fresh by subscriptions. Components re-render when the fields they read change, so read those fields as late as you can.

---

*In Hotwire, I asked "which DOM node gets swapped?" In MobX, the question is "who reads this?" Same instinct, one layer up.*


From Hotwire to MobX


# What My Rails Instincts Got Wrong About NHibernate

## Why I Started Keeping a List

I started a new job this month, and the backend isn't Rails. It's .NET, with NHibernate as the ORM (the library that turns database rows into objects). The C# syntax is easy to look up. The hard part is my Rails habits, and here are five that point the wrong way.

## Instinct #1: "Where's `db/migrate`?"

In Rails, the database is the source of truth, and you change it with migrations. Here it's the reverse: each class has a mapping that lists its columns, and at startup the app updates the database to match. But that update only **adds** tables, columns, keys and indexes. It never renames, changes a type, or drops anything, so renaming a property can give you a brand-new column next to the old one.

## Instinct #2: "Business Logic Goes in the Model"

In Rails, I'd look in the models or in service objects. Here, the classes are mostly plain properties, and the logic lives in "accessor" classes with static methods. Each method takes the user it acts for and checks permissions *inside* the method. That way every entry point (web controllers, the REST API, GraphQL) gets the same check without having to remember it.

## Instinct #3: "`save` Saves"

In Rails, `save` writes to the database right away. Here, objects you load through a session (NHibernate's open unit of work) are tracked. Change a property, and the change is written when the transaction commits. You don't call `Save` for that: `Commit()` is the real save button.

## Instinct #4: "Lazy Loading Just Works"

In Rails, `item.owner` loads whenever you touch it, and the price is N+1 queries (one for the list, plus one per row). NHibernate is lazy too, but a related object can only load while its session is open. Touch it after the session closes and you get an error. So load what the caller needs before you return: touch it inside the session, or fetch it with the query.

## Instinct #5: "Assigning the Association Sets the Foreign Key"

In Rails, `item.owner = user` also sets `owner_id`, the column that points at the owner's row. Here, one column can be mapped twice:

```csharp
Map(x => x.OwnerId).Column("OwnerId");
References(x => x.Owner).LazyLoad().ReadOnly().Column("OwnerId");
```

`ReadOnly()` means `Owner` is never written. Only `OwnerId` is, so assigning `item.Owner` alone doesn't change the saved value, and the update code sets both.

## What I Learned

- **The mapping is the schema, and the startup update only adds.** Renames and type changes won't happen for you.
- **Look for the permission check inside the method, not around it.** That's how every caller gets the same check.
- **`Commit()` is the real save, and lazy loading has a deadline.** Changes land at commit, and related objects must load before the session closes.
- **Read the map before trusting a property.** A read-only reference won't write its foreign key.

## TL;DR

NHibernate isn't Active Record (Rails' ORM) with different syntax. The mappings drive the schema, permission checks live inside accessor methods, and changes land at commit. Lazy loads need an open session, and a read-only reference won't move its foreign key.

---

*My Rails instincts weren't wrong about databases. They were wrong about who's in charge: in Rails the table tells the model what it is, and here the model tells the table.*


What My Rails Instincts Got Wrong About NHibernate


# When Your SDK Does Too Much (And Not Enough)

## What I Was Working On

We have a shared SDK gem that multiple apps use to talk to a third-party assessment API. The main app needed to start creating assessments directly (instead of routing through another service), so I needed to add write operations to the SDK client.

Sounds simple enough, right? Add some methods, ship it. But when I actually looked at the existing client, things got interesting.

## The Problem

The old client was doing two things at once — signing the API request (authentication) and sending it — all crammed into one method. There was no way to control what "action" (read vs. write) the request was signed with, because it was hardcoded. The HTTP layer was raw `Net::HTTP` with zero retry logic, so if the API rate-limited us (HTTP 429), we'd just... fail silently.

For read-only operations, this worked fine. Nobody noticed because reads are forgiving. But now that we needed writes, the lack of control over the signing action was a real problem — signing a write request as a read meant the API would reject it, and signing a read as a write could accidentally mutate data.

## How I Found It

I started by just trying to add the write methods. The first attempt was to bolt them onto the existing client, but I immediately hit a wall: the single `post()` method was signing everything with a hardcoded `'get'` action. There was no way to say "this is a write, sign it as `'set'`" without changing the method signature.

That led me down a rabbit hole of "wait, what else is wrong here?" and I found:
- No retry on rate limits (the API docs literally say "you will get 429'd, handle it")
- No structured error handling — errors were swallowed or raised as generic exceptions
- The HTTP transport and payload signing were tightly coupled — you couldn't test one without the other
- A scoring endpoint that explicitly requires a `'get'` action was being called with `'set'` after refactoring, which broke it with a cryptic 404

That last one was fun to debug. The API returned "Route not found" for a valid path, which made zero sense until I read the docs more carefully and found that the action parameter actually affects routing, not just auth.

## The Fix

I split the client into two clear responsibilities:

1. **`sign(data, action)`** — takes your payload and an explicit action (`'get'` or `'set'`), returns a signed request with security parameters. That's it. No HTTP, no side effects.

2. **`post(path, signed_data)`** — takes the path and the already-signed data, sends it via HTTP. Handles retry, rate limiting, error parsing.

For the HTTP layer, I swapped `Net::HTTP` for Faraday with middleware:
- Automatic retry on 429 with exponential backoff (2s, 4s, 8s, up to 3 retries)
- `raise_error` middleware so HTTP failures become proper exceptions
- JSON response parsing built in

The tricky part was backward compatibility. There were already a bunch of callers using the old pattern: `client.post(path, raw_unsigned_data)`. If I just shipped the new client, all those callers would break because they weren't signing first.

The solution: in `post()`, check if the incoming request already has a `security` key (meaning it's been signed). If not, auto-sign it with `'get'` — which matches what the old client was doing implicitly. Added a comment marking it for removal once all callers migrate.

```ruby
def post(path, request)
  request['consumer_key'] ||= @credentials[:consumer_key]
  # backward compat: auto-sign if caller hasn't signed yet
  request = sign(request, 'get') unless request.key?('security')
  conn.post(path, request)
end
```

This way, old code keeps working, and new code explicitly calls `sign()` first with the correct action.

## Results

| Aspect | Before | After |
|--------|--------|-------|
| Rate limit handling | None (silent failure) | Auto-retry 3x with backoff |
| Signing control | Hardcoded `'get'` | Explicit `'get'` or `'set'` per call |
| Read/write separation | Not possible | Caller chooses action |
| Error handling | Raw exceptions | Faraday errors + structured logging |
| Backward compatibility | N/A | Old callers work unchanged |

Tested both the new explicit-sign pattern and the old raw-data pattern against the live API via console — all methods (reads, writes, scoring, duplicating) passed.

## What I Learned

- **Separate signing from transport.** These are two different concerns. When they're coupled, you can't control either one properly. The caller should decide *what* action to sign with, and the client should handle *how* to send it.

- **Backward compatibility isn't optional for shared libraries.** If your gem is used by multiple apps, you can't just change the API and expect everyone to update simultaneously. Build a bridge, document it, plan for removal.

- **Read the API docs on actions, not just endpoints.** Some APIs use the "action" parameter for more than just auth scoping — it can affect routing, validation, and what the endpoint actually does. A 404 on a valid path might mean your *action* is wrong, not your URL.

- **Faraday middleware order matters.** `raise_error` before `json` means you get raw error bodies. `json` before `raise_error` means errors are parsed before being raised. Small difference, big impact on debugging. Match your test middleware stack to production or you'll chase phantom bugs.

- **Auto-sign fallbacks should match the old default.** When I first added the backward-compat auto-sign, I used `'set'` as the default action (because the new methods were writes). Broke every existing read caller. The old client implicitly used `'get'` — so the fallback had to match that.

## TL;DR

Refactored a shared API client to separate payload signing from HTTP transport, added Faraday with retry/rate-limiting, and built backward compatibility into the breaking change so existing callers keep working during migration.

---

*The scariest part of refactoring a shared library isn't the refactoring — it's all the callers you can't see.*


When Your SDK Does Too Much (And Not Enough)

# When Turbo Stream Replace Kills Your Search Input

## What I Was Working On

I was adding some summary statistics to a data table page — think averages displayed at the top of a list of records. The page already had a search bar and sort controls, and the table updated dynamically via Turbo Streams whenever you searched, sorted, or changed a filter.

The feature itself was straightforward: compute some averages on the backend, render them above the table. Sounds simple enough, right?

## The Problem

After shipping the first version, I noticed something maddening: every time you typed a character in the search bar, the cursor would jump back to the beginning. You'd type "Jo" looking for "John" and end up with "oJ" because the cursor reset after each keystroke.

The search was working — results were filtering correctly — but the typing experience was completely broken. Every keypress triggered a debounced form submission (Turbo Stream request), and when the response came back... the search input got nuked and replaced with a fresh one.

## How I Found It

The culprit was obvious once I looked at the Turbo Stream template:

```erb
<%= turbo_stream.replace "big_container" do %>
  
<% end %>
```

One big `turbo_stream.replace` was swapping out an entire container that held three things:
1. The summary metrics
2. The search form (with the text input)
3. The data table

When the response came back, the browser replaced the entire DOM subtree — including the `<input>` the user was actively typing in. New input, no cursor position, no selection state. Gone.

This was actually a consequence of a refactoring decision. Previously, the metrics and search controls lived in a separate header partial rendered by a different controller. To consolidate everything under one controller (so the data didn't need to be loaded in two places), I moved the search form and metrics inside the same container as the table. Good for architecture, bad for the DOM.

## The Fix

The fix was to **stop replacing everything in one big chunk** and instead target only the parts that actually change:

1. **Extract the table** into its own partial with its own DOM id
2. **Wrap the metrics** in a stable container div with its own DOM id
3. **Leave the search form in between** — not targeted by any Turbo Stream action

The Turbo Stream response went from this:

```erb
<%= turbo_stream.replace "big_container" do %>
  
<% end %>
```

To this:

```erb
<%= turbo_stream.update "metrics_container" do %>
  
<% end %>

<%= turbo_stream.replace "data_table" do %>
  
<% end %>
```

The search form sits in the DOM between `metrics_container` and `data_table`, untouched by either Turbo Stream action. Type away — the cursor stays put.

I also used `turbo_stream.update` (not `replace`) for the metrics container. The difference matters: `replace` swaps the entire element including its attributes, while `update` only changes the innerHTML. Since the metrics container had Stimulus `data-*` target attributes on it, using `replace` would have stripped those attributes and broken the JavaScript behavior. `update` preserves the outer element.

## Results

| Before | After |
|--------|-------|
| Cursor resets on every keystroke | Cursor stays in place |
| One `replace` for entire container | Two targeted stream actions |
| Search form re-rendered on every update | Search form never touched |

The initial HTML page load still renders everything together (metrics + form + table) from one controller action. Only the Turbo Stream response is granular — updating just the pieces that change.

## What I Learned

- **Turbo Stream `replace` replaces the entire element** — including any form inputs the user is interacting with. If your user is typing in something inside the replace target, their input state is gone.

- **Design your DOM ids around what changes independently.** If metrics update but the search form doesn't, they need separate containers. Don't group unrelated UI into one replaceable chunk just because they're visually adjacent.

- **`update` vs `replace` matters for Stimulus.** `replace` swaps the whole element, so any `data-*` attributes (including Stimulus targets) disappear. `update` only changes innerHTML and preserves the outer element's attributes.

- **Consolidating controllers doesn't mean consolidating DOM targets.** It's fine to have one controller serve all the data, but the Turbo Stream response should be surgical — update only what changed.

- **Extract partials along replacement boundaries.** If you're going to `turbo_stream.replace "some_id"`, there should be a partial that renders exactly that `some_id` element. This makes the initial render and the stream update use the same partial, keeping them in sync.

## TL;DR

If your search input loses cursor position during Turbo Stream updates, you're probably replacing a container that includes the input. Split your Turbo Stream targets so the search form is never inside a `replace` boundary — update the metrics and table separately, leave the form alone.

---

*The architectural instinct to consolidate is usually right. But the DOM doesn't care about your architecture — it cares about which nodes get swapped.*

When Turbo Stream Replace Kills Your Search Input


# The Unbounded Query That Took Down an Endpoint

## What I Was Working On

We have a feature where users can upload photos to their profile. There's a "See All" button that takes you to a page listing all the photos for that profile. Simple enough — click a link, see your photos.

Except the page was crashing in production with 504 gateway timeouts.

## The Problem

I pulled up the trace in our APM tool and saw the endpoint was taking over 30 seconds — way past the gateway timeout. The response never even made it back to the user.

When I looked at the controller code, I found this gem:

```ruby
def index
  @items = Item.all
end
```

That's it. No scoping. No filtering. Every single item in the entire database, loaded into memory, on every request. The table had thousands of rows across all accounts, and the view was rendering each one with its associated file attachments — triggering an avalanche of N+1 queries.

The local dev logs told the story clearly:

```
Number of queries: 228
  72 times ran query called "Attachment Load"
  71 times ran query called "ReadableItem Exists?"
  60 times ran query called "Blob Load"
Completed 200 OK in 3109ms
```

228 queries for a page that should show maybe 3-5 photos. Classic.

## How I Found It

The APM trace pointed directly at the endpoint — it showed a 504 timeout with the resource path clearly in the URL. From there, I checked the controller and immediately spotted `Item.all`. The funny thing is, the "See All" link that navigates to this page *already passes* a `profile_id` parameter in the URL. The controller just... never used it.

So the data was being sent, but nobody was listening.

## The Fix

The fix was straightforward — actually use the parameter that's already being passed:

```ruby
def index
  @items = if params[:profile_id].present?
               @profile = Profile.find(params[:profile_id])
               Item.where(folder: @profile.folders)
                   .includes(:folder, file_attachment: :blob)
                   .order(position: :asc)
             else
               Item.none
             end
end
```

Three key changes:
1. **Scope the query** — only load items belonging to the requested profile's folders
2. **Eager load associations** — `.includes(:folder, file_attachment: :blob)` eliminates the N+1 queries for attachments and blobs
3. **Safe default** — return nothing when no profile is specified, instead of everything

But fixing the controller surfaced two more issues I didn't expect:

**Orphaned file attachments:** Some items had attachment records pointing to blobs that no longer existed. The old code assumed every non-PDF file had a valid blob and tried to generate a thumbnail, which blew up with `Nil location provided. Can't build URI.` I added a guard to check both `file.attached?` AND `file.blob.present?` before rendering.

**Turbo Frame mismatch:** The "See All" link lived inside a Turbo Frame on the profile page. When clicked, Turbo intercepted the request and expected the response to contain a matching frame — but the items index page didn't have one. The fix was adding `data-turbo-frame="_top"` to tell Turbo to do a full page navigation instead of trying to swap frame content.

## Results

| Metric | Before | After |
|--------|--------|-------|
| Total queries | 228 | 30 |
| N+1 attachment loads | 72 | 2 |
| N+1 blob loads | 60 | 0 |
| ActiveRecord time | 240ms | 167ms |
| Total response time | 3,109ms | 2,296ms |
| Production result | 504 timeout | Fast load |

And remember — this was on a small local dataset. In production with thousands of items, the before case was exceeding 30 seconds.

## What I Learned

- **Always check if an "all" query is actually what you want.** If a page is designed to show a subset of data, make sure the query reflects that. `Model.all` in a controller should be a red flag.
- **Parameters being passed doesn't mean they're being used.** The scoping parameter was right there in the URL the whole time. Someone built the link correctly but never updated the controller to use it.
- **Fixing one thing often surfaces others.** The scoping fix was clean, but testing it revealed orphaned data and a Turbo Frame issue. Always manually test the full flow after a fix.
- **Eager loading and scoping are a package deal.** Scoping gets you fewer rows. Eager loading gets you fewer queries per row. You usually want both.
- **Turbo Frames are invisible until they break.** If a link inside a Turbo Frame navigates to a page that doesn't have the matching frame, Turbo silently discards the response. The `_top` directive is the escape hatch.

## TL;DR

An endpoint was loading every row in a table because the controller used `Model.all` instead of scoping by the profile parameter that was already being passed in the URL. Fixed by scoping the query, adding eager loading, and handling two secondary issues (orphaned file attachments and a Turbo Frame mismatch). Went from 228 queries and production timeouts to 30 queries and fast page loads.

---

*Sometimes the fix is just... using the parameter that's already there.*


The Unbounded Query That Took Down an Endpoint


# The N+1 That Was Hiding Next to Its Own Fix

## What I Was Working On

We had a dashboard that shows each user a summary of recent activity — completed work, overdue items, upcoming tasks, and so on. It pulls data from several categories across all of that user’s active **subscriptions** (think: one row per product or plan the user is enrolled in). Everything rolls up into a single “home” view.

## The Problem

A performance alert showed the dashboard running about **48 separate database queries** on every load: one per subscription. It showed up thousands of times across a large share of active users and had been that way for months.

The query itself was fine — a lookup for the latest metadata record per subscription. The issue was that it ran **inside a loop**. Classic N+1.

## How I Found It

The error-tracking tool pointed at the controller action and showed the repeated query pattern. Tracing the call chain:

1. Controller builds “completed items” data.
2. A method loops over all subscriptions.
3. Each iteration fetches that subscription’s completed items.
4. That path calls a metadata resolver — **database hit per subscription**.

The ironic part: **Right above**, in the same file, an almost identical method for **“overdue items”** was **already fixed** — batch preloading had been added months earlier. The completed-items path never got the same change. They were about **15 lines apart**.

The plumbing was already there too: a class method that batch-loads metadata for every subscription in **one query** (PostgreSQL `DISTINCT ON`), and a resolver that checks **“if preloaded data exists, use it; otherwise query.”** Nothing was passing preloaded data for this call site, so it always fell through to per-row queries.

## The Fix

Three pieces:

**1. Load once before the loop.** Call the batch preloader up front so you get a hash like `{ subscription_id => result, ... }`.

**2. Thread it through.** Pass that hash down so the resolver can use it and skip the DB.

**3. Fill in missing keys.** The preloader only returned rows for subscriptions that **had** metadata. If there was no row, that ID wasn’t in the hash. The resolver treated “key missing” as “go query again” — so you still N+1’d on empties. **Fix:** Initialize the hash with `nil` for every requested ID, then merge real results. “No data” becomes `nil` with a present key, not a missing key.

Without (3), you might drop from 48 queries to ~18 instead of **1**.

## Results

| Metric | Before | After |
|--------|--------|-------|
| Queries per page load | ~48 | 1
| Pattern | N+1 (one per subscription) | Single batch query |

There was also short-lived caching per subscription, which **hid** the N+1 most of the time. On cold cache—first load, expiry, or after a deploy—all 48 queries ran together. Batch preload fixes the worst case.

## What I Learned

- **When you spot a pattern, check sibling methods.** If N+1 was fixed in one branch, a twin branch may still be broken.
- **Preload hashes need explicit missing keys.** If the resolver uses `hash.key?(id)` and only “successful” IDs are in the hash, every empty case falls through to a per-item query.
- **Caching can mask N+1s.** Tools that flag repeated query patterns help catch what steady-state traffic doesn’t.
- **Reuse existing batch loaders** before building new ones. Often the fix is wiring and one edge case (missing keys).

## TL;DR

The dashboard ran ~48 queries (one per subscription) for completed-item metadata. Batch preloading already lived next door for overdue items. Reused the same pattern, fixed the preloader so empty subscriptions didn’t trigger fallback queries, and reduced the path to **1** query.

---

*Sometimes the best fix is noticing someone already solved the same problem next door.*


The N+1 That Was Hiding Next to Its Own Fix


# Why EXISTS Beats JOIN When You Just Need to Filter

## What I Was Working On

I had an API endpoint that returns membership data. Each membership belongs to a user, and each user can have multiple roles in the system (viewer, editor, admin, etc.). The API needed to:
1. Filter memberships by a user's external ID (stored in a separate roles table)
2. Include that user's external ID in the JSON response

Sounds simple enough, right?

## The Problem

Two things were making these endpoints slow:

**Problem 1: Row explosion from JOIN**

To filter memberships by a user's role ID, the old code joined the memberships table with the roles table. The issue? A single user can have 3+ roles in the same account. So joining these tables turned 1 membership row into 3 rows. Then we had to slap `.distinct` on the query to collapse them back down to 1.

Think of it like this: you're looking for people who own a dog. You join a "people" table with a "pets" table. Someone with 3 dogs now appears 3 times. So you add DISTINCT to de-duplicate. But the database already did all the work of creating those extra rows, only to throw them away. With thousands of memberships, that's a lot of wasted work.

**Problem 2: N+1 for user IDs in the response**

For each membership in the JSON response, we needed to look up the user's external ID. The old code did this in the application layer — for each membership, it would call a method on the user object that queried the roles table. 200 memberships = 200 extra database round trips. Classic N+1.

We tried to fix this with eager loading, which batches the lookups into a few queries. But that meant loading *all* the user objects, *all* their role records, and *all* the role metadata into application memory, just to pluck out one ID per membership. Like ordering the entire menu when you just want a side of fries.

## How I Found It

Honestly, it was staring me in the face once I looked at the query chain. The `.joins(...).distinct` pattern is a well-known smell. And the JSON template calling a method that hits the database per-row is textbook N+1.

## The Fix

**For the filtering: EXISTS instead of JOIN**

Instead of joining the tables and then de-duplicating, I used an EXISTS subquery. The concept is simple — instead of saying "combine these tables and give me the matches," you say "for each membership, does a matching role record *exist*? Yes or no."

The database checks the roles table for a match, finds one, immediately stops looking, and moves on. No extra rows created, no DISTINCT needed. It's like checking if someone owns a dog by peeking at the pet registry, rather than bringing all their pets to the table and then sending the duplicates home.

**For the response data: correlated subselect**

Instead of loading user objects into the app and calling methods on them, I put a small subquery right in the SELECT clause. It basically says "while you're fetching this membership, also grab the user's external ID from the roles table." The database does it all in one trip.

It's like telling the waiter "I'll have the pasta, and also grab me a side of fries" vs. ordering the pasta, waiting for it to arrive, then calling the waiter back for fries, then calling again for a drink, then calling again for dessert...

## Results

| What changed | Before | After |
|---|---|---|
| Filtering strategy | JOIN + DISTINCT (creates extra rows, then removes them) | EXISTS (just checks, no extra rows) |
| User ID lookup | 4 separate queries + app-layer filtering | 1 query, database does everything |
| Data loaded into app | Users, roles, role metadata — thousands of objects | Just the membership rows with one extra column |
| Database round trips | 4 | 1 |

## What I Learned

- **If you're JOINing just to filter (not to use the joined data in SELECT), EXISTS is almost always better.** JOIN is for when you need columns from both tables. EXISTS is for when you just need to know "does a match exist?"

- **DISTINCT is a code smell when paired with JOIN.** If you need DISTINCT after a JOIN, it usually means the JOIN created duplicates you didn't want. That's a sign you should be using EXISTS or a subquery instead.

- **The database is better at finding data than your application layer.** Loading everything into memory and filtering in your app feels natural in an ORM world, but the database has indexes, query planners, and decades of optimization for exactly this kind of work. Let it do its job.

- **Correlated subqueries aren't as scary as they sound.** "A query inside a query that runs for every row" sounds slow, but with proper indexes the database handles it efficiently. It's way faster than the alternative of loading everything into your application layer.

- **Ask yourself: am I bringing data to the code, or bringing the question to the data?** Bringing the question to the data (letting the DB handle it) is almost always faster.

## TL;DR

Was JOINing a table just to filter by it, which created duplicate rows and needed DISTINCT. Switched to EXISTS (just checks "does a match exist?" without creating extra rows). Also moved a per-row app-layer lookup into a SQL subselect so the database does it all in one trip instead of N+1 round trips.

---

*Sometimes the best optimization is just asking the database a better question.*


Why EXISTS Beats JOIN When You Just Need to Filter


# When Your Filter Logic Fights Your Archival Logic

## What I Was Working On

We have a dashboard where managers can filter by their active projects. The filter dropdown is supposed to show all the active projects they're currently assigned to. One particular type of manager — let's call them "primary managers" — was seeing 0 projects in the filter, even though they were clearly assigned to multiple active projects with active members.

## The Problem

A support ticket came in: "Manager can't filter by their projects. Shows 0 projects." We confirmed the user had active assignments, active members in those projects — everything looked fine from a data perspective. But the dashboard just showed nothing.

The weird part? They could still navigate to their projects from another tab. It was only the dashboard filter that was broken.

## How I Found It

I started by checking the obvious stuff — was the user's account in good standing? Were the assignments actually active? Was there a tenant mismatch? All clean.

Then I looked at the query building the filter list. It was doing something like:

```sql
SELECT projects.*
FROM projects
INNER JOIN groups ON groups.project_id = projects.id
WHERE projects.id IN (... active assignments ...)
  AND groups.archived = false
  AND projects.account_id = 123
```

See that `INNER JOIN` with `archived = false`? That's the culprit.

Turns out, there's a completely separate piece of logic that intentionally archives the discussion group for certain types of managers. These "primary managers" use a different kind of shared group (a "homeroom" style), so the per-project discussion groups get archived on purpose. Makes total sense for the discussion feature.

But the dashboard filter was using those same group records to determine which projects to show. Archived group = project disappears from the filter. The archival logic and the filter logic were fighting each other.

## The Fix

The fix was straightforward: for this type of manager, skip the group filter entirely. Their projects are already properly scoped by the assignment-based query upstream, which checks:

- Active manager assignment (correct role, active status, valid date range)
- At least one active member in the project
- Project status is active
- Correct tenant/account

That's more than enough filtering. The group archival status is about discussion visibility, not project validity.

```ruby
if primary_manager?
  scope
else
  scope.joins(:group)
       .where(groups: { archived: false })
end
```

For regular managers, we keep the existing behavior — archived groups should still hide those projects from the filter since they're truly done.

## Results

| Scenario | Before | After |
|----------|--------|-------|
| Primary manager dashboard | 0 projects shown | All active projects shown |
| Regular manager dashboard | Unchanged | Unchanged |
| Admin dashboard | Unchanged | Unchanged |

## What I Learned

- **Different concerns shouldn't share filter criteria.** Archival status for a discussion feature shouldn't determine visibility in a completely unrelated filter. When you add a filter condition, think about whether it could conflict with other intentional behaviors in the system.

- **`INNER JOIN` is a silent filter.** Every `INNER JOIN` is also a `WHERE EXISTS`. If the joined record doesn't exist or doesn't match your conditions, the parent row vanishes. This is especially dangerous when records can be intentionally removed or archived by other processes.

- **Start debugging from the query, not the data.** The data was perfect — all assignments were active, all members were active. The bug was in how the query assembled the results, not in the data itself.

- **User types matter in multi-tenant apps.** A feature that works perfectly for one user type can silently break for another if there's special logic that treats them differently. Always check: "does any other system modify the records this query depends on?"

## TL;DR

A dashboard filter used `INNER JOIN groups WHERE archived = false` to list projects. A separate feature intentionally archived those groups for a specific manager type. Result: those managers saw 0 projects. Fix: skip the group filter for that manager type since assignment-based scoping is already sufficient.

---

*The lesson here is really about coupling. Two features that seem unrelated — discussion group archival and dashboard filtering — were quietly coupled through a shared database record. When one feature did its job correctly, it broke the other.*


When Your Filter Logic Fights Your Archival Logic


# How PostgreSQL Actually Runs a Query

## Why I Went Down This Rabbit Hole

I was debugging a reporting query that kept timing out on a cold cache. The SQL itself was not especially exotic: scan a large event table, group rows by day and category, and count distinct users.

What slowed me down was not just the query. It was the fact that I did not have a good mental model for what PostgreSQL was actually doing under the hood.

So this post is the mental model I wish I had earlier.

## Two Kinds of Memory

The first useful thing to know is that PostgreSQL uses different types of memory for different jobs.

### `shared_buffers` -- The Shelf

`shared_buffers` is shared memory that all connections use together. Think of it like a shelf where PostgreSQL puts data it has already read from disk.

When a query needs rows from a table or an index, PostgreSQL checks whether those pages are already on the shelf. If they are, great. If not, it has to read them from disk first.

That is why the second run of the same query is often much faster than the first.

### `work_mem` -- The Workbench

`work_mem` is where PostgreSQL does active work: sorting, grouping, deduplicating, hashing, and building temporary structures while the query runs.

It is not shared. Each operation gets its own budget.

That one detail matters more than it sounds.

## The Simplified Flow

Here is a useful mental model for a large reporting query:

```text
1. Application cache check
   -> Hit? Return cached result. PostgreSQL does nothing.
   -> Miss? Continue.

2. PostgreSQL receives the query
   -> Query planner checks indexes and table statistics
   -> Chooses an execution plan

3. Read data
   -> If needed pages are in shared_buffers, read from memory
   -> Otherwise, load them from disk

4. Process data
   -> Sort, group, deduplicate, hash
   -> Each of these steps uses work_mem

5. Return result
   -> Optionally store it in application cache
```

The important part is step 4. Large queries are often slow not because the SQL is mysterious, but because one or more operations cannot stay in memory.

## Why `work_mem` Matters So Much

`work_mem` is allocated **per operation, not per query**.

So if one statement does both:

- a `GROUP BY`
- and a `COUNT(DISTINCT ...)`

that can mean multiple memory-hungry steps inside the same query. If those steps do not fit into their individual memory budgets, PostgreSQL spills to disk.

That is where things get slow.

## What Disk Spill Looks Like

When I started reading query plans more carefully, one of the most useful clues was this:

```text
Sort Method: external merge  Disk: 101984kB
```

That line means the sort could not stay in memory. PostgreSQL had to write temporary files to disk and finish the sort there instead.

Once you see that, the problem becomes much clearer:

- the query is not just "slow"
- the sort specifically is under-provisioned
- disk I/O is now part of the execution path

## A Practical `work_mem` Fix

One of the safest ways to test a higher memory budget is to use `SET LOCAL` inside the transaction that runs the heavy query:

```ruby
ActiveRecord::Base.connection.execute("SET LOCAL work_mem = '64MB'")
ActiveRecord::Base.connection.execute("SET LOCAL statement_timeout = '30s'")
```

When the transaction ends, those settings revert automatically.

That makes `SET LOCAL` very different from changing the global server setting for every connection.

## Why Not Just Keep Raising It?

Because `work_mem` is per operation and per worker.

That means one query can multiply memory usage faster than you expect:

- one query
- multiple sort/hash steps
- parallel workers

A number that looks harmless for a single operation can get expensive when all of those stack together.

That is why the usual path is:

1. measure with `EXPLAIN ANALYZE`
2. raise memory conservatively
3. verify whether the query plan improves

## Parallel Workers

PostgreSQL can split a query across multiple processes.

For example:

- `max_parallel_workers_per_gather = 2`
- one main process plus two workers
- each process gets its own memory budget

That can make a query faster, but it also means memory math matters even more.

## `hash_mem_multiplier`

Newer PostgreSQL versions also use `hash_mem_multiplier` to give hash-based operations a bigger budget than sort-based ones.

That matters because spilling a hash operation to disk is often much worse than spilling a sort.

So when memory is tight, PostgreSQL may choose a sort-based plan because hashing would be even more expensive. Once memory increases, the planner may choose a different strategy.

## `log_temp_files`

One setting I wish more people knew about is `log_temp_files`.

It tells PostgreSQL to log whenever a query creates temporary files on disk.

```text
log_temp_files = -1   -> off
log_temp_files = 0    -> log every temp file
log_temp_files = 1024 -> only log temp files larger than 1MB
```

If this is off, disk spills can happen silently. If it is on, you get direct evidence that a sort or hash operation ran out of memory.

That turns guesswork into measurement.

## A Mental Checklist

When a large query feels mysteriously slow, these are the first questions I ask now:

- Is the query slow because the cache is cold?
- Is PostgreSQL reading from disk more than I expected?
- Is a sort or aggregation spilling because `work_mem` is too small?
- Is parallelism helping, or just multiplying memory pressure?
- Would the query plan improve if I tuned one operation locally instead of changing global settings?

## What I Learned

- **`shared_buffers` and `work_mem` solve different problems.** One helps PostgreSQL reuse data it already read. The other helps it do heavy work in memory.
- **`work_mem` is per operation, not per query.** That one fact explains a lot of confusing performance behavior.
- **`SET LOCAL` is a safe tuning tool.** It lets you test changes for one job without changing the whole database.
- **Disk spills are one of the clearest performance signals.** If a sort spills, PostgreSQL is telling you exactly where the bottleneck is.
- **`log_temp_files` is practical, not obscure.** It is one of the easiest ways to find memory-bound queries.

## TL;DR

PostgreSQL is not just "running SQL." It is deciding what to read from memory, what to read from disk, how much memory each operation gets, and whether it needs temporary files to finish the job. Once you understand `shared_buffers`, `work_mem`, parallel workers, and disk spills, slow query plans start to make much more sense.

---

*The useful shift for me was going from "this query is slow" to "I can explain where PostgreSQL is spending time, and why."*


How PostgreSQL Actually Runs a Query


# Why Index Only Scan Still Hits the Heap

## The Confusing Part

One of the most misleading lines in a query plan is `Index Only Scan`.

It sounds like PostgreSQL has everything it needs in the index and will never touch the main table. That is the ideal case. But it is not guaranteed.

If the plan also shows a large number of heap fetches, PostgreSQL is still going back to the table over and over again.

So the real question becomes:

Why does an Index Only Scan still need the heap?

## The Short Answer

Because PostgreSQL does not just care whether the index has the right columns. It also cares whether it can **trust** the index entry without checking the table row behind it.

That trust comes from the **visibility map**.

## The Visibility Map

PostgreSQL uses MVCC, which means updates and deletes do not immediately remove old row versions. A table page can contain a mix of live rows and dead rows.

The visibility map keeps track of which pages are fully safe:

- `all-visible` means every row on that page is visible to every transaction
- not `all-visible` means PostgreSQL may need to check the heap to confirm what is valid

If a page is not marked all-visible, PostgreSQL has to go back to the heap even during an Index Only Scan.

That is why this kind of output matters so much:

```text
Index Only Scan using some_covering_index
Heap Fetches: 3827231
```

The plan name says "index only," but the heap fetches tell you the real cost.

## Why Pages Stop Being All-Visible

This usually happens after:

- large updates
- deletes
- heavy backfills
- any workload that leaves a lot of dead tuples behind

When that happens, PostgreSQL can no longer trust the index alone. The visibility map becomes less useful, and heap fetches go up.

## How to Actually Inspect It

If you want to see the visibility map directly, PostgreSQL has an extension for that:

```sql
CREATE EXTENSION pg_visibility;

SELECT * FROM pg_visibility('analytics_events');
```

You can also get a summary view:

```sql
SELECT
  count(*) AS total_pages,
  count(*) FILTER (WHERE all_visible) AS visible_pages,
  count(*) FILTER (WHERE NOT all_visible) AS dirty_pages,
  round(100.0 * count(*) FILTER (WHERE all_visible) / count(*), 1) AS visible_pct
FROM pg_visibility('analytics_events');
```

That gives you a quick answer to a useful question:

How much of this table is actually clean enough for true index-only reads?

## What VACUUM Has To Do With It

`VACUUM` is what cleans up dead tuples and refreshes visibility information.

That is why heap fetches are often not just an index problem. They can also be a vacuum problem.

If a table has not been vacuumed recently enough, PostgreSQL may keep using the index but still pay the cost of checking the heap for row visibility.

## VACUUM vs VACUUM FULL

These are not interchangeable tools.

| | VACUUM | VACUUM FULL |
|--|--------|-------------|
| What it does | Cleans dead rows, updates visibility map | Rewrites the whole table |
| Locking | Normal reads and writes can continue | Exclusive lock |
| Production use | Usually safe | Needs real caution |

If you are trying to improve Index Only Scan behavior, regular `VACUUM ANALYZE` is usually the right place to start.

## When VACUUM Still Does Not Solve It

There are a couple of cases where you can vacuum and still see more heap fetches than you want.

### Long-Running Transactions

If an old transaction is still open, PostgreSQL may not be allowed to mark pages all-visible yet. The old row versions might still matter to that transaction.

So a long-running transaction can quietly reduce the benefit of an otherwise good index.

### PostgreSQL's Index Cleanup Optimization

PostgreSQL can skip index cleanup when the amount of dead data is small enough that a full index pass does not look worth it.

That is usually a good optimization. But it can also explain why heap fetches stay higher than expected after vacuuming.

If you need to force full cleanup, you can do:

```sql
VACUUM (INDEX_CLEANUP ON, ANALYZE) your_table;
```

Or make that behavior the default for a specific table:

```sql
ALTER TABLE your_table SET (vacuum_index_cleanup = on);
```

## A Small Detail I Did Not Know

PostgreSQL can also mark dead index tuples as it encounters them during scans. That means indexes can improve a little just by being read repeatedly.

VACUUM is still the main cleanup mechanism, but it is not the only one.

## What I Learned

- **A covering index is not the whole story.** PostgreSQL also needs clean enough pages to trust index entries without checking the heap.
- **Heap fetches are one of the best clues in a query plan.** They tell you whether "index only" is actually cheap.
- **The visibility map is a real performance feature.** It is not just internal trivia.
- **VACUUM affects read performance, not just storage cleanup.** That was the mental shift that made this click for me.
- **`pg_visibility` is worth knowing.** It gives you a direct way to verify what the database is doing.

## TL;DR

An Index Only Scan still hits the heap when PostgreSQL cannot trust the index entry on its own. That trust comes from the visibility map, and the visibility map depends heavily on vacuum health. So if heap fetches are high, the fix may not be "add another index." It may be "clean the table well enough that PostgreSQL can finally trust the one you already have."

---

*The surprising part was learning that the index could be technically correct and still not deliver the performance I expected.*


Why Index Only Scan Still Hits the Heap


# Why Autovacuum Defaults Stop Working on Large Tables

## The Trap

PostgreSQL's autovacuum defaults sound reasonable when you first read them.

Vacuum a table after a certain percentage of it has changed. Fair enough.

The problem is that percentage-based defaults age badly as tables grow.

On a small table, 10% churn is not a big deal. On a huge table, 10% can mean waiting far too long before cleanup happens.

## Why This Breaks Down

Imagine a table with hundreds of millions of rows.

If autovacuum waits for 10-20% of that table to change before cleaning it, you are no longer talking about a normal maintenance cycle. You are talking about millions or tens of millions of row changes before cleanup starts.

That delay can affect a lot of things:

- dead tuples accumulate longer
- visibility map quality gets worse
- Index Only Scans do more heap fetches
- query performance becomes less predictable

So even if autovacuum is technically enabled, it can still be effectively too late to help.

## Why the Default Is Not "Wrong"

The default exists because PostgreSQL has to work across many workloads.

Aggressive vacuuming is not free. It uses I/O, CPU, and maintenance bandwidth. So the defaults aim for broad safety, not perfect behavior for every large or write-heavy table.

That means the right conclusion is not "autovacuum is broken."

It is:

Large tables usually need table-specific settings.

## A Better Way To Think About It

Instead of asking:

"Is autovacuum on?"

Ask:

"Will autovacuum run early enough for this specific table?"

That is a much better question.

## Table-Specific Tuning

One practical approach is to lower the scale factor and set an explicit threshold for the tables that matter most.

```sql
ALTER TABLE analytics_events SET (
  autovacuum_vacuum_scale_factor = 0.01,
  autovacuum_vacuum_threshold = 10000
);
```

The exact values depend on your write rate and table size, but the principle is simple:

- lower the percentage trigger
- add a fixed threshold
- tune the table that needs it instead of changing the whole server blindly

## One More Gotcha: Long-Running Transactions

Even a well-timed vacuum cannot fully help if an old transaction is still hanging around.

PostgreSQL may not be allowed to mark pages all-visible yet, because that transaction might still need to see old row versions.

So if vacuum seems less effective than expected, it is worth asking whether a long-running transaction is getting in the way.

## Why This Shows Up in Query Plans

Autovacuum tuning can feel like background maintenance work, but it shows up directly in query performance.

If a large table is not being vacuumed often enough:

- visibility map coverage stays worse
- heap fetches stay higher
- supposedly cheap plans stop being cheap

That is why this is not just a DBA topic. It changes how real application queries behave.

## The Long-Term Fix Is Often Not More Vacuum

Sometimes the right answer is not "vacuum harder."

If the application keeps asking for the same rollups from the same huge raw table, the long-term fix is often **pre-aggregation**.

Instead of recomputing the same summary from millions of rows on demand, you can store:

- daily totals
- per-category summaries
- cached rollups

Then the user-facing query reads a small summary table instead of the full event table.

That does not replace good vacuum settings, but it does reduce how much pressure you put on the raw table in the first place.

## What I Learned

- **Autovacuum defaults are general-purpose, not table-specific.** That is fine until one table becomes unusually large or hot.
- **Percentage thresholds get more expensive as tables grow.** The bigger the table, the more dangerous "wait for 10%" becomes.
- **Vacuum health affects read performance.** This is not just cleanup for cleanup's sake.
- **Large tables deserve explicit settings.** If a table matters to your application, tune it like it matters.
- **Pre-aggregation is often the more durable answer.** Maintenance helps, but reducing raw-query pressure helps even more.

## TL;DR

Autovacuum can be fully enabled and still be too slow for a very large table. The percentage-based defaults often wait too long before cleaning things up, which hurts visibility, heap fetches, and query performance. The practical fix is usually table-specific autovacuum settings, and the long-term fix is often to stop querying raw rows for every dashboard request.

---

*The useful shift here was realizing that "autovacuum is on" and "autovacuum is helping" are not the same thing.*


Why Autovacuum Defaults Stop Working on Large Tables


# Why My Analytics Dashboard Showed No Data

## What I Was Working On

I was working on an analytics dashboard that showed event data broken down by account and time range. One day, someone noticed it was showing "No Data" everywhere. Completely blank.

## The Problem

At first, I thought it might be a query timeout because the underlying table was fairly large. But when I checked locally with a tiny dataset, I saw the same thing. No data. That ruled out performance pretty quickly.

So I jumped into the Rails console and looked at recent event records. Every single one had `account_id = 0` and `category_name = nil`. The dashboard filters by account, so if every row says "account 0," nothing useful ever shows up.

## How I Found It

The event model had a `before_create` callback that was supposed to set `account_id` and `category_name`. That sounded reasonable. But the records were not being created one at a time through ActiveRecord. They were being buffered and then bulk-inserted using `insert_all`.

And `insert_all` **skips ActiveRecord callbacks**. No `before_create`, no `after_create`, nothing. So the callback that was supposed to populate those fields never ran.

This had probably been broken ever since the batch insert pipeline was introduced, but it went unnoticed because the dashboard was not heavily used yet.

## The Fix

**Part 1: Stop the bleeding.** I moved the `account_id` and `category_name` calculation upstream into the code path that captures the event, before it ever gets queued for batch insert. Now those values are part of the inserted hash from the beginning, and I removed the dead callback.

**Part 2: Backfill historical data.** I needed to fix about 246K records that had `account_id = 0`. So I wrote a maintenance task that finds those rows and enqueues background jobs to repair them.

This is where it got interesting.

## The Rabbit Hole: Backfilling at Scale

**Attempt 1: ID range stepping.** I grabbed the min and max IDs of the records needing repair and stepped through them in batches of 10,000. It worked perfectly locally. In production, the `MIN(id)` query timed out.

**Attempt 2: Replace MIN/MAX with ORDER BY + LIMIT.** Instead of `scope.minimum(:id)`, I used `scope.order(:id).limit(1).pick(:id)`. That used the primary key index and returned quickly. But then I realized the ID range was extremely sparse, so my range-based batching would create a huge number of mostly empty jobs.

**Attempt 3: Use `in_batches` with `pluck(:id)`.** Rails' `in_batches` pages through actual rows instead of walking empty ID space. That was much closer to the right approach. But when I tried putting it in the `collection` method so the maintenance task UI could show progress, the upfront query still timed out in production.

**Final approach: `in_batches` inside `process`.** I kept `collection` as `[1]` so nothing expensive happened up front, and moved the `in_batches` logic into `process`. Each iteration did a small indexed query, plucked the IDs, and enqueued a job. No full-table scan. No timeout.

## Results

| What | Before | After |
|------|--------|-------|
| `account_id = 0` in recent data | 246,280 | 0 |
| Analytics dashboard | "No Data" | Shows real data |
| New event records | Missing account/category | Correct from creation |

## What I Learned

- **`insert_all` skips callbacks.** If you need derived fields, they have to be present before the insert. Model callbacks will not save you.

- **What works locally can blow up in production.** Data volume changes the cost of even simple-looking queries.

- **ID ranges can be deceptive.** A reasonable record count can still map to a terrible batching strategy if the ID space is sparse.

- **Production databases have statement timeouts.** Even simple-looking queries can fail if they scan too much data.

- **Iterate toward production-safe code.** My first few attempts all had problems that only surfaced at real scale. That is normal.

## TL;DR

The dashboard showed "No Data" because bulk inserts were skipping the callback that populated key analytics fields. I fixed the write path by setting those values before `insert_all`, then rewrote the backfill a few times until it worked safely at production scale.

---

*Sometimes the real work starts after you find the bug, especially when you have to repair everything it already touched.*


Why My Analytics Dashboard Showed No Data


# The Day I Finally Used EXPLAIN ANALYZE in Production

## What I Was Working On

After fixing a data bug on an analytics dashboard, I cleared the cache so users would see fresh data. That's when things got interesting.

The largest dataset behind that dashboard has about 1.5 million pageview records in a 30-day window, and about 3.8 million over the full range. I cleared the cache, loaded the dashboard, and got a 500 error. Tried again, and it worked fine. That made it clear this was probably a cold-cache problem, not a permanently broken query.

## The Problem

The first dashboard load after clearing the cache was hitting our 20-second database timeout. The query behind the activity chart had to scan hundreds of thousands, sometimes millions, of rows, group them by date and role, and count distinct users. When nothing is cached, that means a lot of disk I/O all at once.

What confused me was that we already had a covering index for this query. On paper, it looked like the database had everything it needed.

## How I Found It

A teammate asked me to run `EXPLAIN ANALYZE` on the query in production. I had avoided it for too long because it always felt more intimidating than it should have. It turned out to be the fastest way to stop guessing.

So I ran it. Here's what I found for a 7-day window (~490K rows):

```
Index Only Scan using our_optimized_index (actual time=0.2..631 rows=489,867)
   Heap Fetches: 489,867
Sort (actual time=801..877 rows=489,867)
   Sort Method: external merge  Disk: 13184kB
Execution Time: 995ms
```

And for 30 days (~3.8M rows):

```
Index Only Scan using our_optimized_index (actual time=0.03..5190 rows=3,820,013)
   Heap Fetches: 3,827,231
Sort (actual time=7018..7471 rows=3,820,013)
   Sort Method: external merge  Disk: 101984kB
Execution Time: 8466ms
```

Two things jumped out at me immediately.

### Problem 1: Sorting Was Spilling to Disk

The first thing I noticed was `external merge Disk: 101984kB` on the 30-day query. That meant Postgres could not keep the sort in memory, so it spilled to disk. Even the 7-day query spilled about 13MB. Once I saw that, I stopped thinking "the query is kind of slow" and started thinking "the sort is definitely one of the bottlenecks."

The reason was simple: the default `work_mem` budget was too small for the amount of data being sorted. For this query, that meant Postgres had to create temporary files just to finish the operation.

The useful takeaway for me was that you can raise it just for one query:

```sql
SET LOCAL work_mem = '128MB';
```

That only applies to the current transaction, which makes it much safer than changing the global setting.

### Problem 2: The Index Was Not Saving Us as Much as I Expected

The second thing was more surprising. The plan said `Index Only Scan`, which sounded perfect. But the next line told a different story.

`Heap Fetches: 3,827,231` meant Postgres still had to go back to the main table millions of times. So yes, it was using the index, but not in the cheap way I thought it was.

That usually points to visibility map / vacuum state, not just index design. In other words, the index existed, but Postgres still could not fully trust it. That was a useful mental shift for me: having the right index is not always the same as getting the performance you expect from it.

### Bonus Discovery: Peak Hours Make Everything Worse

I ran `EXPLAIN ANALYZE` during off-peak hours and got 995ms for 7 days. During peak hours, the same query took 3,290ms. Same data, same query, same index. The only real difference was system load.

That was the moment it clicked for me: if a query takes 8.5 seconds off-peak, it does not take much for it to start timing out during busy hours.

## The Fix

The database-side fixes needed infrastructure or DBA help, so I focused on operational changes we could ship immediately:

1. **Extended cache TTL from 4 hours to 24 hours.**  
   This reduced how often users hit cold-cache paths during normal traffic.

2. **Added a cache warm-up cron at 3:00 AM Arizona time** (scheduled with EST in mind in our environment).  
   The goal was to pre-warm heavy dashboard caches during low-traffic hours, before daytime usage.

3. **Removed backoff-based retry chaining and split work into separate jobs.**  
   Instead of one chained retry flow with exponential delays, each section runs as its own job.

4. **Used `perform_in` with up to 5 retries per job.**  
   Each job now schedules its own retry attempts independently.

This combination worked better for our case: fewer cold-cache misses, warm data before peak traffic, and independent retry behavior per section rather than one shared backoff path.

## Results

| Scenario | Before | After |
|----------|--------|-------|
| First load after cache clear (large dataset) | 500 error, user sees error | Much less frequent cold-cache failures after TTL + warm-up changes |
| Peak hours, 30-day range | Sometimes times out, error displayed | Better stability due to off-peak pre-warming and isolated retries |
| Failure handling | One backoff chain for the flow | Per-section jobs with `perform_in` retries (up to 5) |

As a result, users were able to view data reliably even during peak traffic hours.

## What I Learned

- **Run `EXPLAIN ANALYZE` earlier than you think.** It turns vague slowness into something concrete you can reason about.

- **`Index Only Scan` does not automatically mean cheap.** If heap fetches are still high, the database is doing much more work than the plan name suggests.

- **Large sorts can fail for boring reasons.** Sometimes the issue is not the SQL shape, but simply that the operation does not have enough memory.

- **Database performance is not constant.** A query that looks acceptable in one moment can become a timeout during peak traffic.

- **Retry strategy matters as much as retry count.** In our case, independent per-section jobs with `perform_in` retries were more predictable than a single exponential-backoff chain.

- **The plan usually tells you more than the code does.** Once I looked at actual timings and heap fetches, the next steps became much clearer.

## TL;DR

After fixing a data bug, clearing the cache exposed a slow analytics query that sometimes timed out on first load. `EXPLAIN ANALYZE` showed sort spill and heavy heap fetches. The immediate fix was operational: increase cache TTL from 4 hours to 24 hours, warm caches at 3:00 AM Arizona time, and switch to separate per-section jobs with `perform_in` retries (up to 5) instead of one exponential-backoff chain. As a result, users were able to view data reliably even during peak traffic hours.

---

*The real win here was not just fixing one query. It was finally learning how to look at a query plan and explain what the database was actually doing.*


The Day I Finally Used EXPLAIN ANALYZE in Production


# Why Relationship Records Weren't Being Created

## What I Was Working On

I was working on a sync flow where one user type was supposed to be linked to another through a relationship record. Whenever an upstream system sent a matching record, our app was supposed to create that relationship automatically.

It was not working. The source records were showing up, but the relationship records were missing.

## The Problem

It turned out there were multiple bugs stacked on top of each other, and you could not really see the next one until you fixed the one before it.

**Bug #1: The key identifier never made it through the pipeline.**

The upstream payload had a field called `associated_user_id` that told us which secondary record should be linked. But the intermediary service that transformed the payload for our app was dropping that field. It never made it into the metadata passed downstream, so by the time the record reached our app, we had no way to know what it was supposed to be linked to.

**Bug #2: The metadata path was wrong, and the callback was too narrow.**

Even after fixing the intermediary service to include the field, our app was reading `associated_user_id` from the wrong place in the metadata structure. It was looking at the top level instead of the nested key where the value actually lived.

And even after fixing that, there was still another issue underneath it.

## The Sneaky Third Issue

Here was the tricky part: upstream, one user could generate multiple source records with different `associated_user_id` values. But in our app, those all mapped to the **same local record** because they shared the same uniqueness boundary. So each new sync overwrote the previous one.

The old callback only triggered relationship creation when the record's **status** changed. But when one synced record overwrote another with a different `associated_user_id`, the status did not change. So the callback never fired, and the new relationship record was never created.

## How I Found It

This was a real onion-peeling exercise:

1. **Started with the symptom**: a user was missing expected relationship records. I checked the synced record in our app and saw no `associated_user_id` in metadata. That pointed to the intermediary service.

2. **Fixed the intermediary service**, re-synced, and still got only partial results. The `associated_user_id` was showing up now, but our app was reading it from the wrong metadata path. Fixed that.

3. **Ran a backfill** to create the missing relationship records from existing data. That worked, but only for the local records that already existed in our app. In one test case, there were 21 upstream source records, but only 6 local records.

4. **Dug into why only 6**: it turned out the 21 upstream records were collapsing into 6 local records because of how uniqueness was defined. The last processed value won, and its `associated_user_id` was the only one stored.

5. **Found the callback gap**: even if all 21 source records flowed through, the callback would only fire when status changed, not when `associated_user_id` changed. So overwrites were being silently ignored.

## The Fix

Two changes across two services:

**In the intermediary service**: added `associated_user_id` to the metadata that gets passed along with each synced record.

**In the application**:
- Fixed the metadata read path to look in the right nested location
- Extended the callback trigger to also fire when `associated_user_id` changes, not just status. Used Rails' built-in dirty tracking (`metadata_before_last_save`) to compare the old and new values.

Now when record A linked to entity `523` gets overwritten by record B linked to entity `525`, the callback sees that `associated_user_id` changed from `"523"` to `"525"` and creates the missing relationship record.

## Results

| Metric | Before | After |
|--------|--------|-------|
| Relationship records for test case | 11 (from initial sync + manual backfill) | All 21 expected |
| Backfill for one account | 0 auto-created | 31 created automatically |
| Pipeline: `associated_user_id` passed | No | Yes |

## What I Learned

- **Data pipeline bugs compound**: when data gets dropped early in the pipeline, every downstream system that depends on it silently fails. You often do not even see the second bug until the first one is fixed.

- **Understand the data model before assuming the storage layer is wrong**: I initially thought the sync layer was broken because multiple source records were collapsing into one. It turned out that behavior was intentional. The real fix was making the callback smarter, not changing how records were stored.

- **Callbacks need to match the data flow**: if your data can change via overwrites and not just creates, your callbacks need to handle that. "Only trigger on status change" works until metadata changes become meaningful.

- **Rails dirty tracking is useful here**: `metadata_before_last_save` and `saved_change_to_metadata?` let you detect changes within JSON or hstore fields without extra infrastructure.

- **Test with real edge cases**: one user linked to 21 upstream records collapsing into 6 local ones was exactly the kind of case that exposed the bug. Simpler tests would have missed it.

## TL;DR

The record coming from upstream carried the identifier needed to create a relationship, but that value was being dropped by one service, read from the wrong path in another, and ignored by a callback that only watched status changes. Fixing all three layers made the relationship records appear correctly.

---

*Sometimes the hardest bugs are the ones where three things are broken at once, and each one hides the next one.*


Why Relationship Records Weren't Being Created


# Why One Dashboard Page Kept 502-ing

## What I Was Working On

I was working on a dashboard page that showed a handful of aggregated metrics for a large customer account. It worked fine at small scale. But once the data set got large enough, the page kept timing out with 502 errors.

## The Problem

For one large account with a dataset of around 7,000 users, the page would not load at all. Every request hit a 502 timeout from the load balancer. On top of that, the frontend was throwing JavaScript errors about missing DOM targets, which made it look like a frontend bug at first.

The 502s were happening because the backend was taking 9-24 seconds to respond, well past our ALB timeout. But a dataset with 7,000 users should not be *that* bad, right? We had pagination. So what was going on?

## How I Found It

This took a little while to untangle. Here's the path:

**First dead end: the JavaScript error.** The browser console was complaining about a missing `parent` target in a Stimulus controller. I spent some time there first, adding guard clauses so the controller would not crash when its DOM targets were not ready yet. That fixed the JS error, but the 502s kept coming.

**Second look: Datadog traces.** I checked the traces and saw the backend was spending most of its time in database queries. So this was really a backend problem, not a frontend one.

**The breakthrough: following the query chain.** I traced through the code path that runs when a user hits the dashboard page. The controller calls `build_metrics_map`, which computes metrics for the full user set, and then `process_results`, which counts and paginates the result. The important detail was that pagination only kicks in at the very end. Before that, we were still running expensive queries across the entire population.

**The real aha moment:** I looked at the policy scope that builds the metrics query and found this:

```ruby
.joins(:related_records)
.left_joins(:related_metrics)
.includes(:related_records)  # <-- THIS GUY
```

That `.includes(:related_records)` looked reasonable at first. But the metrics were already being computed in SQL using `GROUP BY` and `AVG()`. We never actually iterated over individual related record objects in Ruby. So that one `includes` was triggering a separate query like:

```sql
SELECT * FROM related_records WHERE user_id IN (1, 2, 3, ..., 7000)
```

That returned about **156,000 related records** and loaded them all into Ruby memory for data we never used. On every page load when the cache was cold.

And after that, the code ran another expensive query, `users.count`, doing a full `COUNT` across the same large multi-join relation even though we had already built a hash map of all the users and effectively knew how many there were.

## The Fix

Four things:

1. **Removed the `.includes(:related_records)`** - since the metrics are computed in SQL via aggregation, we did not need Rails to eager-load all 156K related objects into memory. The `joins` and `left_joins` were already doing the actual query work.

2. **Reused the metrics map for the count** - instead of running a separate `users.count` query, I used the size of the metrics hash map we had already built. It falls back to the database count when the map is not available.

3. **Added guard clauses to the Stimulus controller** - so it handles the case where DOM targets are not rendered yet, for example during Turbo Frame loading, without crashing.

4. **Removed unnecessary bulk loading on a related page** - there was a separate code path on another page that was also loading the full user set, so I removed that too.

## Results

| What | Before | After |
|------|--------|-------|
| Related records loaded into memory | ~156,498 | 0 |
| SQL queries for metrics | 2 (aggregation + eager load) | 1 (aggregation only) |
| Redundant COUNT query | Yes | No (uses metrics map size) |
| Page load | 502 timeout | Loads successfully |
| JS console errors | Missing target crash | None |

## What I Learned

- **`.includes` is not always the right N+1 fix.** If your query uses `GROUP BY` and aggregation, the useful work is already happening in SQL. Eager-loading the raw records on top of that just loads data nobody uses.

- **"Prevent N+1" changes need context.** A query can look more optimized while actually doing more work. The real question is whether anything in Ruby ever touches the records you are preloading.

- **Pagination doesn't help if expensive queries run before it.** Our pagination was working fine, but it only applied to the final page of results. The metrics computation, the count query, and the eager-loading all happened before pagination ever limited the result set.

- **Follow the numbers, not the row count.** A dataset with 7,000 users sounds manageable. But each user had around 22 related records on average, and the `.includes` loaded all of them. The real scale was 156K records, not 7K.

## TL;DR

A dashboard page was 502-ing because an `.includes(:related_records)` was loading 156K records into memory that nobody used even though the real work was already being done in SQL. I removed it, reused existing data for the count query, cleaned up a related Stimulus issue, and the page went from timing out to loading normally.

---

*Sometimes the best optimization is removing code that shouldn't have been there in the first place.*


Why One Dashboard Page Kept 502-ing

# The XSS Concern That Wasn't

## What I Was Working On

I was working on a feature that launched an external app from inside our Rails app using LTI. Part of that flow involved rendering a page that auto-submitted a form with signed parameters.

## The Concern

During code review, someone flagged this as a potential XSS vulnerability:

```erb
<input type="hidden" name="<%= key %>" value="<%= value %>" />
```

The concern was straightforward: what if `value` contains something like `"` or `onclick="bad stuff"`? Could someone break out of the `value` attribute and inject JavaScript?

At first glance, that sounded completely reasonable.

## Digging Into It

So I started by thinking through the failure mode:

```html

<input value="foo" onclick="alert(1)" />
```

That would absolutely be a real problem.

But then I looked closer at two things:

### 1. Does Rails auto-escape?

Yes. The `<%= %>` syntax in ERB **automatically HTML-escapes** values before rendering them into the page. So if someone tried to inject a quote character:

```ruby
# What you write
'foo" onclick="alert(1)'

# What actually renders
'foo&quot; onclick=&quot;alert(1)'
```

Those quotes get escaped to `&quot;`, which means the payload stays inside the attribute as plain text instead of turning into a new attribute.

### 2. Where does this data come from?

I also traced where `@signed_params` came from:

```ruby
{
  'user_id' => user.id.to_s,           # Our database ID
  'oauth_timestamp' => Time.now.to_i,  # Current time
  'context_id' => section.sourced_id,  # A UUID we generated
  # ... all server-generated values
}
```

In this code path, the values were generated on our side. They weren't coming from user-submitted input, which matters a lot when you're evaluating XSS risk.

## When Would This Actually Be Dangerous?

The dangerous version of this pattern is when you **bypass** Rails' escaping:

```erb
<%# DANGEROUS - these bypass escaping %>
<input value="<%== value %>" />
<input value="<%= value.html_safe %>" />
<input value="<%= raw(value) %>" />

<%# SAFE - default escaping applies %>
<input value="<%= value %>" />
```

If you use `html_safe`, `raw`, or the double-equals `<%==`, you're telling Rails, "trust me, this string is already safe." That's where this kind of code can go wrong very quickly.

## The Verdict

The code was safe in this case because:
1. ERB escapes attribute values by default
2. The data in this flow was not attacker-controlled
3. We weren't using any escape bypasses like `raw`, `html_safe`, or `<%==`

I did come away thinking the code could be a little more idiomatic with form helpers:

```erb
<%= form_tag @launch_url, id: 'lti_launch_form' do %>
  <% @signed_params.each do |key, value| %>
    <%= hidden_field_tag key, value %>
  <% end %>
<% end %>
```

Same idea, just a little more Rails-y.

## What I Learned

- **ERB escapes more than people think** - `<%= %>` gives you important protection by default
- **Watch out for escape bypasses** - `html_safe`, `raw`, and `<%==` are the danger zones
- **Trace the data source** - what matters is whether the value is attacker-controlled in this code path
- **Code review concerns are still valuable** - even when the issue turns out to be harmless, the investigation sharpens your security instincts
- **Context matters** - the same code pattern could be dangerous in a different context (e.g., if we were rendering user-submitted data)

## TL;DR

I got flagged for a possible XSS issue in an ERB template, but the code was safe because ERB was escaping the value and the data wasn't attacker-controlled in that flow. The real risk starts when you bypass escaping or render untrusted input in the wrong context.

---

*Sometimes the useful part of a security review is proving why something is safe, not just finding where it's broken.*

The XSS Concern That Wasn't


# Flash vs Flash.now: The Rails Gotcha That Finally Made Sense

## What I Was Working On

I was working on a feature with some standard error handling. If something failed, I wanted to show a helpful message and send the user to the right place.

## The Problem

My linter kept yelling at me:

```
Use flash.now before render.
```

But I wasn't using `render`. I was using `redirect_to`.

That warning made me stop and think, though. I knew the rule by habit, but I realized I couldn't explain it as clearly as I wanted to. What's the real difference between `flash` and `flash.now`?

## The Aha Moment

The key insight is understanding what a request is and that **the server has amnesia**.

Every time the browser talks to the server, that's one request. When that request ends, the server forgets everything unless you save something somewhere.

That's why redirects matter so much here:

```
You: "Hey, there was an error!"
Server: "Okay, I'll redirect you..."
[Request ends, server forgets]

Browser arrives at new page
Server: "Hello! Who are you? What error?"
```

## How Flash Solves This

Flash messages use the session so they can survive across requests when needed.

**With `redirect_to` (two requests):**
```
Request 1: Something fails
  -> You set flash[:error] = "Oops!"
  -> Rails stores it in the session
  -> You redirect the user

Request 2: User lands on new page
  -> Rails reads the flash
  -> Shows "Oops!" to the user
  -> Rails clears the flash
```

**With `render` (same request):**
```
Request 1: Something fails
  -> You set flash.now[:error] = "Oops!"
  -> Rails shows the message immediately
  -> No extra request involved
```

## Why It Matters

If you mix them up:

| You do | What happens |
|--------|--------------|
| `flash` + `redirect_to` | Works. Message shows on the next page |
| `flash.now` + `render` | Works. Message shows immediately |
| `flash` + `render` | Wrong message timing. It can show up on the next page visit |
| `flash.now` + `redirect_to` | Message disappears before the next page loads |

## The Rule I Keep In Mind

- **Redirecting?** Use `flash[:error]`
- **Rendering?** Use `flash.now[:error]`

In my case, I was redirecting, so regular `flash` was the correct choice:

```ruby
flash[:error] = "Something went wrong"
redirect_to some_path
```

## What I Learned

- **HTTP is stateless** - the server forgets everything after each request
- **Flash uses the session** to carry a message into the next request
- **`flash`** = "remember this for the next page"
- **`flash.now`** = "show this right now"
- **The request boundary is the whole mental model**

## TL;DR

`flash` survives to the next request, so use it with redirects. `flash.now` only lives for the current request, so use it with renders. Once you think in terms of request boundaries, the difference becomes much easier to remember.

---

*Once request boundaries click, a lot of Rails behavior starts to feel much less magical.*


Flash vs Flash.now: The Rails Gotcha That Finally Made Sense


# Two Practical Lessons I Picked Up Building an LTI Integration

Recently I worked on embedding an external assessment tool inside our main Rails app using LTI.

At a high level, the job sounded simple: launch the tool in an iframe, pass the right parameters, and let teachers manage assessments without leaving the main app.

The integration itself worked, but the more interesting part was everything around it. While building it, I ran into two small lessons that were more useful than the LTI setup itself.

## 1. When to Use Stimulus Instead of Turbo

My first instinct was to use Turbo Frames for the tab switching between `Gradebook` and `Alternative Assessments`.

That turned out to be the wrong tool for this case.

Here's why:

1. **LTI requires a POST** - You can't just load a URL. You need to submit a form with signed OAuth parameters.
2. **Cross-origin iframe** - Turbo can't control content from another domain.
3. **Hidden form trick** - The solution is a page that auto-submits a hidden form to the LTI endpoint.

Stimulus ended up being the better choice because we needed JavaScript to:
- Switch tabs on click
- Load the iframe lazily (only when the tab is clicked)
- Show a loading spinner while the iframe loads

Turbo is great when you're swapping server-rendered content from your own app. But when you need to submit a signed POST to another service, deal with a cross-origin iframe, and manage loading states yourself, Stimulus gives you much more control.

## 2. exists? is More Efficient Than find_by

When checking authorization, I originally wrote:

```ruby
enrollment = user.enrollments.find_by(section: section)
is_authorized = enrollment&.role == 'teacher'
```

A reviewer pointed out that `exists?` was a better fit:

```ruby
is_authorized = user.enrollments.exists?(section: section, role: 'teacher')
```

Why? `find_by` loads the full record into memory, then checks the role in Ruby. `exists?` just asks the database whether a matching row exists and returns a boolean.

It's a small optimization, but it also reads more clearly. If the question is just "does this record exist?", `exists?` usually says exactly what you mean.

## TL;DR

I built an LTI iframe integration, but the most useful takeaways were broader than LTI itself: use Stimulus when Turbo stops fitting the problem, and prefer `exists?` when you only need a yes-or-no answer.

---

*Sometimes the most useful part of a task is the stuff you learn around it.*


Two Practical Lessons I Picked Up Building an LTI Integration


# That Time I Fixed a Sneaky N+1 Query in Grade Calculations

## What I Was Working On

So today I was looking into a performance issue in our grade calculation system. We have this feature where students get weighted grades based on different assignment categories (like homework worth 20%, tests worth 50%, etc.). The code loops through each category, calculates the score, and applies the weight.

Sounds simple enough, right?

## The Problem

Turns out, this thing was making way too many database calls. Like, 40+ queries just to calculate one student's grade. Every time we calculated grades for a section with a bunch of assignments, it was painfully slow.

Looking at our monitoring tools, I could see the same queries repeating over and over:

```
"Hey database, give me assignments for category 1"
"Hey database, give me assignments for category 2"
"Hey database, give me assignments for category 3"
...
```

And it got worse. For each assignment, we were also asking:

```
"Is this assignment exempt?"
"Is this one exempt?"
"What about this one?"
...
```

Classic N+1. But actually, it was more like N+1 times three because there were multiple loops doing this.

## How I Found It

I started by checking Datadog/Sentry traces to see the query breakdown. The trace showed dozens of nearly identical queries - dead giveaway for N+1.

Then I dug into the code and found the culprit: a loop that was querying the database on every iteration. The tricky part was that there were actually THREE separate N+1 patterns hiding in there:

1. Fetching assignments per category (instead of all at once)
2. Counting exempt assignments per category with a join query
3. Checking if each individual assignment was exempt with `.exists?`

That last one was the sneakiest. It looked innocent - just a simple check - but it was running once for every single assignment.

## The Fix

The solution was basically: **load everything upfront, then work with it in memory**.

Instead of asking the database questions inside the loop, I:

1. Grabbed ALL the visible assignments in one query, then grouped them by category in Ruby
2. Got ALL the exempt assignment IDs in one query and threw them into a Set (for fast lookups)
3. Got ALL the assignment results in one query

Now the loop just works with data that's already in memory. No more "hey database" calls inside the loop.

### The Key Trick

The `.to_set` trick was clutch. Before, we were checking "is this assignment exempt?" by hitting the database every time. After, we just check if the ID is in a Set - which is basically instant.

```ruby
# Before (slow - database call every time)
next if assignment.results.exists?(status: 'exempt')

# After (fast - just checking a Set in memory)
next if exempt_ids.include?(assignment.id)
```

## Results

| Before | After |
|--------|-------|
| 40+ queries | 5 queries |
| Slow as heck | Actually reasonable |

## What I Learned

1. **N+1 can hide in plain sight.** That innocent-looking `.exists?` check was actually a database query running hundreds of times.

2. **Look for loops inside loops.** Each nested loop that touches the database multiplies your problem.

3. **Sets are your friend.** When you need to check "is X in this list?" a bunch of times, convert the list to a Set first. O(1) vs O(n) lookup.

4. **Load once, use many.** If you're going to use the same data multiple times, grab it all at the start. Don't keep asking the database the same questions.

5. **Check your traces.** Monitoring tools make N+1 issues obvious. If you see the same query pattern repeating, that's your sign.

## TL;DR

Grade calculations were slow because we were asking the database too many questions inside loops. Fixed it by asking all the questions upfront and then working with the answers in memory. Went from 40+ queries to 5.

---

*Sometimes the fix isn't complicated - it's just about being smarter about when you talk to the database.*


That Time I Fixed a Sneaky N+1 Query in Grade Calculations


# ActiveRecord has_many Joins, DISTINCT, and When ORDER BY Needs a Subquery

## What I Was Working On

I worked through material on **querying `has_many` associations** in ActiveRecord — the same ideas as `belongs_to`, but with the join direction flipped so parent rows (e.g. locations) can appear multiple times when multiple children (people) match.

The walkthrough uses a small domain: `Person` belongs to `Location` and `Role`; `Location` and `Role` each `has_many :people`. The goal is to express “billable locations” (locations that have at least one person whose role is billable) and then combine that with ordering by region and location name.

## What I Learned

### Joining the “other side” of the association

`Location.joins(:people)` inner-joins `people` to `locations`. That is the `has_many` analogue of joining from the child in a `belongs_to` lesson: you get one row per (location, person) pair, so a location with two people shows up twice. That duplication is expected; it is what lets you filter and aggregate in SQL instead of looping in Ruby.

### Nested joins read like a temporary `has_many :through`

`Location.joins(people: :role)` chains `people` → `roles`. You did not have to declare a `has_many :roles, through: :people` on `Location` for this one-off query; ActiveRecord walks the graph you name.

### Filtering on joined tables

`Location.joins(people: :role).where(roles: { billable: true })` scopes by columns on the joined `roles` table. Hash conditions on the table name keep the intent obvious and avoid string SQL in app code.

### Why `distinct` shows up

After joins, the same location can repeat. `.distinct` (Postgres: `SELECT DISTINCT "locations".*`) collapses the result to unique locations. The “distinct” requirement is not cosmetic — it matches how joins multiply rows.

### Encapsulation

Named scopes or class methods (`Location.billable`) keep join logic on the model instead of scattering it through controllers. Composing smaller scopes (e.g. `Role.billable` → `Person.billable` → `Location.billable`) stays readable even when a single method would be shorter in a tutorial.

### Ordering combined scopes: the Postgres rule

Combining something like:

- `billable` → `joins(people: :role).where(roles: { billable: true }).distinct`
- `by_region_and_location_name` → `joins(:region).merge(Region.order(:name)).order(:name)`

as `Location.billable.by_region_and_location_name` yields SQL that orders by `regions.name` and `locations.name` while the outer select is `SELECT DISTINCT "locations".*`. Postgres errors:

**`for SELECT DISTINCT, ORDER BY expressions must appear in select list`**

The mental model: `DISTINCT` can collapse rows that differed only in columns you use for `ORDER BY` but did not select. The database forces you to resolve “unique rows” first, then order in a way that does not contradict that.

### Fix: subquery with `from`, then order on the outer query

`Location.from(Location.billable, :locations)` wraps the distinct billable locations in a subquery aliased as `locations`, so the outer query sees a normal `locations` table again. Then `.by_region_and_location_name` joins `regions` and orders — without fighting `DISTINCT` in the same `SELECT`.

Final shape:

`Location.from(Location.billable, :locations).by_region_and_location_name`

The inner query deduplicates; the outer query adds the join and `ORDER BY` on unambiguous columns.

### Let the database do the work

Pushing filtering, deduplication, and ordering into SQL (via ActiveRecord) usually beats loading collections into Ruby and using `Enumerable` for the same job — fewer round trips, less memory, and work the engine is built for.

## Practical Application at Work

- **When you see duplicate parents after `joins`,** decide whether you need `distinct`, `group`, or a different shape (e.g. `EXISTS` subqueries) depending on whether you only filter or also need aggregates.
- **When you mix `distinct` with `order` on joined columns,** Postgres may reject it; a subquery or selecting the order columns explicitly are the usual escapes — the `from(subquery, :alias)` pattern keeps the “dedupe first, sort second” story clear in ActiveRecord.
- **Keep composed scopes on the model** so the awkward combinations (like subquery wrappers) live in one place and callers stay readable.

## TL;DR

`joins` on `has_many` multiplies parent rows; `where` on nested associations filters; `distinct` dedupes parent rows. Stacking `distinct` with `order` on columns from other tables can break Postgres — wrap the deduped set in `from(...)`, then join and order on the outside. Prefer pushing this into the DB via ActiveRecord over reimplementing the same logic in Ruby.

---

*Based on learning from a course on advanced ActiveRecord querying (has_many associations).*


ActiveRecord has_many Joins, DISTINCT, and When ORDER BY Needs a Subquery


# ActiveRecord DISTINCT ORDER BY Error in PostgreSQL and Why a Subquery Fixes It

## What I Was Working On

I was trying to understand a common ActiveRecord pattern for `has_many` queries:

- build a scope that joins child tables
- filter on the joined rows
- remove duplicate parent rows with `distinct`
- then order the result by columns from another joined table

The example looked simple on the Rails side, but the SQL rule behind it was easy to miss.

## What I Learned

### `joins` on a `has_many` multiplies parent rows

If a location has multiple matching people, `Location.joins(:people)` does not return one row per location. It returns one row per `(location, person)` pair. That means duplicate parent rows are expected after the join.

That is why `distinct` shows up in patterns like:

`Location.joins(people: :role).where(roles: { billable: true }).distinct`

The goal is not cosmetic deduplication. It is correcting the row multiplication introduced by the join.

### `DISTINCT` and `ORDER BY` can pull in different directions

The confusing part is when I want something like:

- unique `locations`
- ordered by `regions.name`
- then ordered by `locations.name`

At first glance, this feels harmless. But PostgreSQL sees a real ambiguity:

- `DISTINCT` says: "figure out which selected rows count as the same"
- `ORDER BY` says: "sort those rows using these expressions"

If the ordering expression is not part of the selected distinct row, the database has to answer an awkward question:

"When I collapse duplicates, which ordering value am I supposed to keep?"

That is why PostgreSQL raises:

**`for SELECT DISTINCT, ORDER BY expressions must appear in select list`**

The important mental model is:

**deduplicate first, then sort the already-defined result set**

### The subquery is really an order-of-operations fix

The subquery pattern solves that by separating the steps:

1. Inner query: find distinct billable locations
2. Outer query: treat that result like a table, join what I need, and order it

In ActiveRecord, that shape looks like:

`Location.from(Location.billable, :locations).by_region_and_location_name`

The inner `Location.billable` handles the duplicate-removal part. The outer query handles ordering.

This is not just about syntax. It is about making the SQL stages explicit so Postgres no longer has to mix deduplication and cross-table ordering in the same select step.

### Why `:locations` is there

This part finally clicked for me once I stopped thinking of `from` as "just passing another relation" and started thinking of it as:

**"Use this subquery as the table for the rest of the query."**

So:

`Location.from(Location.billable, :locations)`

means:

- take the SQL generated by `Location.billable`
- place it in the `FROM (...)` clause
- alias that subquery as `locations`

That alias matters because the rest of the relation still wants to behave like it is querying a `locations` table.

So when I later call:

- `.joins(:region)`
- `.order(:name)`
- or any scope that expects `locations.region_id` or `locations.name`

ActiveRecord now has a table-like thing named `locations` to point at.

Without the alias, the outer query would not have a stable table name for those later joins and column references.

## Practical Application at Work

- When a `has_many` join produces duplicate parents, treat `distinct` as a row-shape decision, not a cleanup trick.
- When `distinct` and ordering on joined columns collide, think in stages: first produce the unique parent set, then order it in an outer query.
- When using `from(subquery, :alias)` in ActiveRecord, remember that the alias is what lets later scopes keep talking to the subquery as if it were a normal table.

## TL;DR

`joins` on `has_many` creates duplicate parent rows, so `distinct` is often necessary. But once `DISTINCT` is involved, PostgreSQL may reject `ORDER BY` on expressions that are not in the select list. Wrapping the distinct relation in `from(subquery, :locations)` makes the sequence explicit: dedupe inside, then join and sort outside. The `:locations` part is just the alias that lets ActiveRecord keep treating the subquery result like the `locations` table.

---

*The biggest insight was not "Postgres is picky." It was realizing that SQL was asking me to choose the order of operations more explicitly.*


ActiveRecord DISTINCT ORDER BY Error in PostgreSQL and Why a Subquery Fixes It


# Concurrent Indexes, Statement Timeouts, and When Migrations Stop Being “Deterministic”

## What I Was Working On

I needed to add a nullable foreign-key-style column to a very large append-only table used for analytics-style event records, then index it so lookups stayed cheap. The Rails guides describe migrations as a clean sequence: define `up`/`down` or `change`, run `db:migrate`, everyone’s database matches. Production had other plans.

## The Problem

The table was big enough that a normal `add_index` inside a single transaction would have been risky for availability. For PostgreSQL, **`CREATE INDEX CONCURRENTLY`** avoids blocking writes on the table, but it **cannot run inside a transaction block that also runs other DDL** the usual way. In Rails, that means you reach for **`disable_ddl_transaction!`** on the migration class so the migration runner does not wrap the whole thing in one transaction.

Even with `CONCURRENTLY`, building an index on a huge table can take a long time. Our environment applied a **statement timeout** to migrations. The column `ADD COLUMN` finished quickly; the **index build hit the timeout**, rolled back from the migration’s point of view, and left us in a messy state: **the column existed in production, but the index did not**.

So the next deploy could not pretend we were on a greenfield database. Re-running a “pure” migration that always runs `add_column` would **fail** because the column was already there.

## How I Found It

Deploy logs and the database state did not match what `schema_migrations` implied we should see. The migration had partially applied: schema change (column) succeeded; long-running step (index) did not complete under the timeout.

That is the moment the textbook story (“migrations should be deterministic”) bumps into **partial failure recovery**.

## The Fix

**1. Split concerns mentally, even if one file holds both steps**

- Add column (fast) vs. create index concurrently (slow) are different risk profiles. The guides allow both in one migration, but production may force you to treat them differently over time.

**2. Use the PostgreSQL-friendly knobs**

- `disable_ddl_transaction!` so `add_index ..., algorithm: :concurrently` is valid.
- Temporarily **`SET statement_timeout = 0`** (scoped to the migration connection/session) so the index build is not killed mid-flight—*with the understanding that this is a deliberate trade-off for a controlled deploy window*.

**3. Make the migration safe to re-run after a partial apply**

- `add_column ... unless column_exists?(...)`
- `add_index ..., algorithm: :concurrently, if_not_exists: true` (or equivalent guard)
- `down` paths that use `if_exists:` so rollback is safe

This is **not** the same as “edit old migrations casually.” It is **operational idempotency** because the world outside version control already diverged.

**4. A reviewer’s point (and a valid alternative)**

A teammate suggested keeping migrations maximally deterministic—e.g. column in one migration, concurrent index in another. That is often the right default. In our case, the column had already shipped and the index had failed once; **guards were the way to finish the job without hand-editing production**.

Another pattern worth knowing: a DBA builds the index manually in a session with appropriate settings; a follow-up migration only records “create index if not already present.” That keeps Rails history aligned with reality when humans intervene. I had not used that workflow before; it is now on my list for the next huge index.

## Results

| Aspect | Before | After |
|--------|--------|-------|
| Migration run | Column added; index build timed out | Redeploy with guards completes index |
| `add_column` | Would fail on second run | `unless column_exists?` — safe to replay |
| Index creation | Killed by `statement_timeout` | `SET statement_timeout = 0` for concurrent build |
| Story | Textbook “deterministic” migration | Recoverable migration after partial apply |

## What I Learned

- **`CREATE INDEX CONCURRENTLY` + Rails migrations = remember `disable_ddl_transaction!`.** Otherwise you chase confusing errors that make sense only if you know PostgreSQL’s rules.

- **Statement timeouts apply to more than you think.** I assumed concurrent index creation might be immune; it wasn’t in our setup. Explicitly resetting timeout for the long DDL step was the fix.

- **Partial deploys change the rules.** The guides warn against editing migrations after they have run in production; they also imply a happy path. When production is half-updated, **conditional DDL** can be the responsible move.

- **“Deterministic migrations” vs. “recoverable migrations.”** In greenfield, optimize for determinism. After a failed long step in prod, optimize for **safe replay** and clear `down` behavior.

## Practical Application at Work

- For large tables: plan index creation like an ops task—timeouts, locks (or avoidance), and rollback story—not only like an ActiveRecord API exercise.
- When a migration fails mid-way: first **inspect what actually landed** (columns, indexes, constraints), then adjust the *next* migration or a guarded re-run—not always a blind rollback.
- Document for the team when you use `statement_timeout = 0` and why; future you should see the intent.

## TL;DR

I added a column and a concurrent index on a massive table. The column landed; the index timed out under migration statement limits. I used `disable_ddl_transaction!`, disabled `statement_timeout` for the index build, and made `add_column` / `add_index` idempotent so a redeploy could finish what the first run started. The Rails migration guides are still the right baseline—production just adds chapters on timeouts, partial failure, and when “conditional migrations” are the professional option.

---

*Rails guides teach the happy path. Production teaches the recovery path.*


Blog