The Wellwhere the information actually lives

Go back to the map you wrote in the last session and find heading two — the one that says where the information comes from. For most people in this room it says something like a folder, a spreadsheet, or, honestly, my head and a notebook in the truck.

For Dale it is a folder of text files that his daughter fills in each morning before she opens the office. That works. It has worked for months. It is worth being clear about that before we replace it with anything.

It stops working at four specific moments, and they are worth naming because you can usually feel one of them coming.

Number four is the one that quietly costs money, and it is the one this session is really about. Everything else follows from it.

A folder is a fine place to keep things. It is a poor place to keep the truth.

A well is one hole in the ground, dug once, in the right place. Around the yard there are troughs — one by the gate, one in the paddock, one in the barn. Every trough draws from the same well. Nobody carries buckets between them, and no trough ever has different water in it from another.

That is a database. One place the information lives. Everything that needs it draws from there, and because there is only one of it, nothing can ever disagree with anything else.

The word people reach for first is “a big spreadsheet”, and it is close enough to start with. The difference that matters: a well is built to be drawn from by several things at once, from several places, without anyone waiting and without two people seeing different answers. A spreadsheet is one file, and one file has one person in it at a time.

Who is going to use it?

This is the question that decides whether you need a well at all, and it is worth asking out loud before anything else. There are three honest answers and they lead three different ways.

• Just me. Then a folder is fine, and a spreadsheet is fine. Genuinely — not as a consolation. If nobody else touches it and it lives on one machine, a well is a cost with no benefit, and anybody who tells you otherwise is selling something.
• Me and the people who work for me. Now you want a well. This is Dale: two people, two places, one truth. It is the most common answer in a room like this one and it is what today is aimed at.
• My customers, over the internet, with their own logins. Then you want a well and several other things besides — a way to know who is who, a way to send them mail, a way to keep one customer from seeing another's business. That is a bigger job than today, and we will come to it.

A well is dug once, in the right place. That is most of the skill.

Here is the entire structure, and there is genuinely nothing hiding behind it. A table is a list of one kind of thing. Every one of those things gets a row. Every fact you keep about them gets a column, and every row has the same columns as every other.

Dale's well has one table in it called jobs. One row per job. Seven columns, because there are seven facts worth keeping about a job. That is the whole design, and it took about four minutes to decide.

Two rules follow from the picture, and they are the only two you need today.

• One fact, one place. Tanner's address is written once. When he moves, it is corrected once, and every trough in the yard is correct from that moment. If you find yourself changing the same fact in two places, you have two wells and one of them is lying.
• One kind of thing per table. Jobs go in the jobs table. Customers, if you ever need them separately, go in a customers table. Resist the urge to build one enormous table with everything in it — that is a spreadsheet with extra steps.

The columns you choose decide the questions you can ask

This is the part worth slowing down for, and it came out of building the demo you are about to see rather than out of a book.

Dale's table records the date a request came in. It does not record the date the estimate went out. Nobody noticed, because nobody had ever needed it. Then somebody asked what looks like the simplest question in the world — what did we bill in July? — and the well cannot answer it. It can tell you what came in during July and has since been billed, which is a different question wearing the same coat.

So: a column you did not write down is a question you cannot ask. You will not get this right first time and you are not supposed to. Adding a column later is a small job. Knowing that you needed one is the hard part, and this is how you find out.

A column you never kept is a question you cannot ask.

A question put to a well is called a query, and it is written in a language called SQL — most people say “sequel”. It has been around since the nineteen-seventies, which is why it looks the way it does, and it is not going anywhere.

You are not going to learn to write these, and you do not need to. What you need is to be able to look at one and check it asked what you meant — which is the same skill as reading the permission prompt in session five, and it matters for the same reason.

Read this one out loud in English and see how far you get:

Here is the real exchange from the demo, exactly as it came back.

Notice what happened in the third line of that query. It searched for anything like “dale”, because it did not know how Dale's name was written in that column — and it said so. In an earlier run it refused to guess at all, and told me a filter that guessed wrong would quietly hide jobs. That is the behaviour you want, and it is the reason to read the query rather than only the answer.

Read the question. The answer will always look confident.

You do not dig a well by hand any more, and you certainly do not run one yourself. You rent one. It is free at the size any of us will use today, it lives on the internet rather than on your office computer, and getting one takes about as long as making a cup of tea.

There are several companies who will do this. We use Neon, and we get it through Vercel — which is worth explaining, because it saves you an account.

Vercel is a company that runs computers so you do not have to. You will meet it properly in #010, where it is what puts your work on the internet. It also has a marketplace of the things you tend to need on the way, and a database is one of them.

So you sign up once, at Vercel, and get the well from there. One account instead of two, one bill instead of two — and both are free at this size. The account you make today is the same one you use for the next three workshops.

The key to the well

Digging the well hands you back a connection string — one line of text beginning postgresql://. Anyone who has it can read and change everything in your well, from anywhere, with no password prompt. It is worth reading that sentence twice, because it is the first real secret you have owned.

So: it never goes in a public repository, it never gets pasted into a chat window or an email, and it never appears in a screenshot. It lives in a file called .env.local in your project folder, and the first thing you do is add that filename to .gitignore — which is Lab 2, and is the reason that lab makes you check it yourself.

If one does get out, go back to the Vercel dashboard, open the well in Neon from there, and reset the password. It takes a minute. Everything using the old one stops working until you update it, which is annoying and enormously better than the alternative.

Before you close this page

The four labs are the doing. These three are the thinking, and they are what decides whether the next thing you build takes an afternoon or a fortnight.

1. Take out the brief you wrote in #008 and read heading two again — where the information comes from.
2. Answer the question from chapter two, in writing, on the map: who is going to use this? Just you, you and your staff, or your customers? One line. It changes everything that comes after it.
3. Then sketch the columns. What is the one kind of thing you are keeping — jobs, customers, deliveries? And what facts do you keep about each one? A list on paper is exactly enough.

The middle one is the one to actually do. It is a single line of writing, and everything after it — who can see what, what has to be locked, how much of #012 you need at all — follows from the answer.

The question that decides everything: who is going to use this? Just you — keep your folder. You and your staff — dig a well. Your customers, with logins — that is a bigger job, and worth planning before you start rather than after.