My first data schemas almost never survive the first month

In a shift-rota generation app, I changed the schema three times in incompatible ways in two weeks. I created a worker_verifications table to store the result of the checks the model ran on each worker. Five days later I deleted the whole thing and moved its one useful column, checked_rules, into worker_week_rules. Two days after that I removed extra_closed_days from the weeks table, which I'd added in the previous redesign.

None of those changes came from a new product decision. They came from understanding the domain better.

The day 1 schema and the day 30 one

The table I deleted had eight columns: ai_result, worker_hash, context_hash, verified_at, checked_rules and the keys. The idea was to cache the verification: if the worker hash and the context hash didn't change, you didn't call the model again. Sounds reasonable.

What happened is that the real unit of the domain wasn't "a verification", it was "a worker's rules in a week". And that row already existed. The verifications were an attribute of that row, not an entity. The migration that fixed it is short and reads by itself:

ALTER TABLE "worker_week_rules" ADD COLUMN "checked_rules" text DEFAULT '{}' NOT NULL;

UPDATE "worker_week_rules" wwr
SET "checked_rules" = wv."checked_rules"
FROM "worker_verifications" wv
WHERE wwr."week_id" = wv."week_id"
  AND wwr."worker_id" = wv."worker_id"
  AND wv."checked_rules" != '{}';

DROP TABLE "worker_verifications";
ALTER TABLE "worker_week_rules" DROP COLUMN "created_at";

Four statements. The cost wasn't the SQL.

Which changes mean you got it right and which mean you didn't

There are two kinds of schema change and they're nothing alike.

• Adding a column. checked_week_rules in weeks, calendar_rules in calendars. It's an ALTER TABLE ADD COLUMN ... DEFAULT with a default, it ships without stopping anything, the old code keeps working. This is the system growing. Good sign.
• Moving the entity. Deleting a table, renaming a column, changing what a piece of data hangs off. That means you modelled the domain wrong the first time, not that the domain changed.

In that same project I moved preferred_day_off integer to preferred_days_off text, because it turned out a worker could prefer two days. That one is also a good one: it's new information about the world, not a modelling error. The migration fits in three lines and keeps the data with a CAST.

The bad kind is obvious because the diff isn't in the migrations folder.

The real cost is in the UI, not in the SQL

In an AI documentation tool, I started out calling what the user uploaded documents. A month later the right concept was contexts: chunks of text the agent could read, quote and rewrite, which could come from a URL, not only from a file. The name was wrong and it dragged the whole model along.

The commit that changed it doesn't touch the schema. It touches the chat page, the input, and deletes context-url-dialog, context-view and context-viewer entirely: 1,127 lines removed in one go. A two-second RENAME COLUMN and a full day poking at components.

And a case even cheaper to run and more expensive to propagate: in that same project I renamed starCount to favoriteCount. The SQL is five lines across two migrations. The diff touches the schema, two database action files, and the UI that rendered it.

That's the rule I took away: the cost of changing a column name is proportional to how many components write it literally. Not to what the migration costs.

How I write the first schema today

Four things I do differently since then, and none of them is "design better".

• I name things for what they are, not where they came from. documents was the origin (a file). contexts is the role the data plays in the system. The second name holds up when the origin changes.
• I don't create a table for an attribute. If the new entity always has exactly one row per row of another table, it isn't an entity. It's a group of columns. worker_verifications was that and it took me five days to see it.
• One type per column from the start. In the big redesign I converted created_at from text to timestamptz, week_start from text to date and the JSON stored as text to jsonb, all at once. I could have got all of that right on day one for the same price. Fixing it later is an ALTER COLUMN ... USING in every table plus reviewing all the code that compared dates as strings.
• The UI doesn't repeat the column name. A type derived from the schema and a couple of accessor functions. Then a RENAME COLUMN is caught by the compiler instead of by a grep.

When it is worth stopping

In an office floor plan editor I went from having separate DualDesk and QuadDesk components to a single MultiDesk with a seat count, and then refactored it again to add disabled seats. A desk for two and a desk for four look like two cases; really they were the same one with a parameter. That you can see on day one if you stop for ten minutes and ask whether the 2 and the 4 are constants or data.

The rule I use: if the difference between two models is a number, it's data. That's where it's worth thinking first.

For the rest, I write the schema knowing it's wrong. What I optimise isn't getting it right, it's making next week's fix cost a short migration and not a day of poking at components.