We run a US directory of special needs schools and ABA therapy providers. Providers pay to be promoted in the places where parents are searching. The first version of that feature was one integer column. It worked right up until it cost us money.

Here is the column, the failure, and the schema we replaced it with.

The naive model 

create table listings (
  id               uuid primary key,
  name             text,
  city             text,
  state            text,
  featured_priority int default 0   -- higher sorts first
);

Enter fullscreen mode Exit fullscreen mode

And the resolver behind every city page:

select *
from listings
where city = $1 and state = $2
order by featured_priority desc, name asc;

Enter fullscreen mode Exit fullscreen mode

You can ship that in an afternoon. We did. It was fine for about a year.

The bug is hiding in the word "featured"

"Featured" is not a property of a business. It is a property of the relationship between a business and a place.

A provider with clinicians covering forty towns across three states does not want to buy forty towns. They want the metro where their office is. Maybe two metros. But featured_priority lives on the listing row, so the moment we set it, that provider outranked everyone in all forty towns. The column is global. The product being sold is local.

Four things went wrong, in increasing order of how much they hurt:

  1. We gave away inventory. Selling one market promoted the provider in every other market they touched, for free. Those were markets we could have sold to them, or to a competitor.
  2. We could not price by market. A dense metro and a town of 4,000 were the same integer, so they were worth the same amount.
  3. Cancellation was all or nothing. Ending one deal demoted the provider everywhere at once.
  4. We had nowhere to put per market facts. When did this placement start? What rank was negotiated? Did this provider appear here because they paid, or just because they happen to have an office here?

That fourth one is the real tell, and it generalizes well past directories: every time you want to attach a fact to a flag, the flag should have been a row.

The fix: placement is an edge, not a node 

create table market_coverage (
  listing_id      uuid references listings(id),
  market_id       uuid references markets(id),
  plan_tier       text not null
                  check (plan_tier in ('organic','featured','premium','ultimate')),
  position_source text not null,  -- 'primary_location' | 'service_area' | 'metro'
  rank_override   int,
  placed_since    date,
  primary key (listing_id, market_id)
);

Enter fullscreen mode Exit fullscreen mode

Every question that was previously unanswerable is now just a column. A provider can be ultimate in one metro, featured in the metro next door, and organic in the eighty other towns their clinicians drive to. Cancelling a deal updates one row. Pricing reads off market_id. Reporting on active paid placements is a where plan_tier <> 'organic' instead of an archaeology project.

position_source earns its place too. It records why a listing is eligible in a market, which is a different question from what they paid. An office in the city and a service area reaching the city are not the same claim, and eventually someone will ask you to treat them differently.

Two things we still got wrong after the migration

Moving to coverage rows fixed the modeling. It did not stop us from misreading our own schema twice.

Rank override is relative, not a slot. rank_override = 2 reads like "put this listing in position 2." It does not do that. It is an input to a sort, so a single pinned provider on a page renders at #1 no matter what integer you give it. To actually get someone into second place you have to pin someone else into first. Obvious in hindsight, and it burned an afternoon before anyone said the word "comparative" out loud.

Coverage does not conjure a page. Our city pages only exist when at least one provider has a physical location anchoring that city. Service area coverage answers "who may be shown here." It does not answer "does here exist." So a provider whose service area reached a town with no anchored listing was configured, correct, invisible, and pointed at a 404. Any system with generated pages has some version of this: the eligibility rule and the existence rule are separate, and if you only enforce one, you will sell placement on a page that was never built.

Migration notes

A few things that made the cutover boring, which is the goal:

  • Backfill, dual read, then flip. We kept the old featured_priority as a fallback read for months while everything moved over. Nothing about that is clever, and that is the point.
  • Normalize tier names before you have many. We had accumulated slugs from three different product eras that all meant roughly "paid," plus a couple that encoded school versus therapy into the tier name itself. One migration collapsed them into a clean ladder and moved the provider type into its own column, where it always belonged. Doing this later would have meant touching far more call sites.
  • Put resolution in exactly one function. A single listings_for_city_page(city, state, type) decides which coverage row wins for a given city. The public site, the admin app, and background jobs all call it instead of writing their own sort. When the ranking rule changes, and it will, it changes once.

The test

If you are building anything that sells position, run your schema against one question:

Can two customers buy different things?

If your model cannot express "promoted in Denver, ordinary in Boulder," then you are not selling placement. You are selling a global flag and hoping nobody notices the difference. We noticed when we did the math on what we had been giving away.

If you would rather have the data than the schema

Modeling aside, the reason any of this exists is that public information about special needs services in the US is scattered across state agencies in formats nobody can query. We clean some of it up and publish it openly, no signup:

Both are free to cite and free to reuse. If you build something with them, I would genuinely like to see it.

I work on Special Needs Care Network, a directory helping families in the US find special needs schools and ABA therapy providers. Most of what we do is boring data modeling in service of a parent finding a provider who is actually accepting clients.