731 words or 2,042? Why an aggregate's population belongs in the view, not the query string

I maintain a corpus of agent instruction files scraped from public repos — AGENTS.md, CLAUDE.md, Cursor rules and friends — and last week I added the page answering "what actually goes in a CLAUDE.md," counted rather than recommended.

The first thing that fell out was two different answers to median length:

basis	files	median words	share with runnable commands
every CLAUDE.md	775	2,042	34.2%
repo-root CLAUDE.md only	201	731	79.6%

Both are correct. The 574 non-root files — the per-package ones sitting in packages/api/CLAUDE.md — have a median of 2,276 words, which is the opposite of what I'd have guessed. Whatever the cause, they are a different population answering a different question, and someone asking "what should my CLAUDE.md contain" means the one at the root.

So the interesting part isn't the number. It's that the choice of population is a schema decision, and I'd been treating it as a query parameter.

Rule 1: the filter that defines the population goes in the view

The tempting design is /api/sections?root=true. Then the default is wrong, or the caller forgets, and a percentage computed over 775 files gets printed under a heading that says "a CLAUDE.md file." Nobody will ever notice, because the number looks fine.

create or replace view rs_section_stats as
with base as (
  select format, count(*)::int as total
  from rs_configs
  where hidden = false and is_root
  group by format
)
select c.format,
       t.tag                                                 as section_tag,
       count(*)::int                                         as config_count,
       b.total                                               as root_total,
       round(100.0 * count(*) / nullif(b.total, 0), 1)::real  as pct_of_root
from rs_configs c
cross join lateral unnest(c.section_tags) as t(tag)
join base b on b.format = c.format
where c.hidden = false and c.is_root
group by c.format, t.tag, b.total;

is_root appears twice and is optional in neither place. There is no way to call this view and get the other population. The name says pct_of_root, so a column that escapes into a page carries its own basis in the label.

Rule 2: ship the denominator on every numerator row

root_total is repeated identically on all twenty rows, which is redundant on purpose. The alternative is a page that fetches counts here and the total from somewhere else — and "somewhere else" drifts. A different hidden filter, a different date window, a count over the list endpoint that hit a row cap. A percentage with the wrong denominator is the kind of wrong that looks right.

Rule 3: two bases on one page is an invitation to divide across them

I already had a stats table publishing medians over all files. Rather than reuse it, I added a second small view, rs_root_shape, computing length and heading counts on the same root-only basis. It exists purely so a reader — or a future me — cannot pair a median from one population with a share from another in adjacent paragraphs. Duplicating an aggregate is cheaper than the errata.

Rule 4: GROUP BY cannot emit a zero

The classifier has a fixed vocabulary of 20 section tags. A group-by returns only tags that some file carries, so for windsurf-rules the view returns 13 rows and for .cursorrules it returns 19. Render that as "the tags" and you've silently claimed the missing 7 aren't categories at all — when "no file in this corpus has a security section" is one of the more interesting things you could say. The endpoint returns sections, plus vocabulary: every tag the classifier can assign. Absence is an answer, and it has to be carried out-of-band because SQL won't produce it.

Why this is a view and not application code

section_tags is a text[]. Getting a share out of it needs an unnest and a group-by — the one aggregate a list endpoint structurally can't serve. /api/configs?tag=build returns the files with a build section, never the fraction. Computing it in the page meant paging a whole format at the handler's 100-row ceiling: eight sequential reads of thirty-eight columns to produce twenty integers. One view, one read.

For the record, the finding that motivated the page: build commands come first in Anthropic's list of what to keep in a CLAUDE.md, and 39.8% of root files have a build section. Hard prohibitions are the cheapest line anyone can write, and 48.3% have those.

This is how we built RuleStack, a measured comparison hub for agent config formats: https://rulestack.kynth.studio