Decoding the Jargon — SQL, Schema, Cloudflare D1, 'Working Tree Is Dirty' & 'Source of Truth'

Spend a week building and shipping a real app and you collect a pile of words nobody ever sat you down and defined. They show up in commands you copy-paste, in the wall of text git prints back at you, in your database config, and — if you're working alongside an AI agent on a project like this one — in the docs and commit messages scrolling past. You nod along. You sort of know what they mean from context. But "sort of" is exactly the gap that makes everything feel harder than it is.

This chapter closes five of those gaps, from zero:

• SQL — the language you use to talk to a database.
• Schema — the blueprint that says what shape your data has.
• Cloudflare D1 — what tech is this? A database, specifically — here's what kind and why it exists.
• "The working tree is dirty" — what git means by it, and why it's not an insult.
• "Source of truth" — the most useful idea in the list, and the one nobody defines: which copy of something is the real one.

Three of these (SQL, schema, D1) get a full hands-on treatment elsewhere in this series — Ch 9: SQL & Databases with D1 and the Cloudflare D1 deep dive. This chapter is the plain-English on-ramp: enough to make every one of these words click, with a pointer to the deep dive when you want to go further.

"Source of Truth" — Which Copy Is the Real One?

Start here, because it's the idea the other four lean on.

The moment anything important exists in more than one place, you have a problem: when the copies disagree, which one is right? Your code lives on your laptop, on your teammate's laptop, and on GitHub — three copies. Your app's data lives in the database, but also gets cached in a dozen places for speed. A video's free/paid status lives in a config file, but the same fact is also written into the database.

The source of truth is the answer to "which copy wins." It's the one place you've designated as authoritative — the copy that, by agreement, is correct by definition. Every other copy is a derivative: a cache, a mirror, a working draft. When a derivative disagrees with the source of truth, the derivative is wrong, full stop.

Figure 1 — One copy is crowned the source of truth; everything else derives from it. The rule that makes the whole idea useful: when a derived copy and the source of truth disagree, the source of truth is right by definition.

You'll see this exact pattern all over a real project:

• For your code, the source of truth is the git repository on GitHub. Your laptop has a copy, your CI server has a copy, your teammate has a copy — but "what does the project actually contain?" is answered by the remote on GitHub, not by whatever happens to be on your machine right now. That's why git push matters: it updates the source of truth.
• For this app's data, the source of truth is the D1 database. The Mac app shows you a list of videos; the website shows the same list. Neither of those screens is the list — they're both reading from simpleappshipper-db. Change the database and both update; change a screen and you've changed nothing real.
• For the Swift course curriculum, the source of truth is the original Udemy course. This project's docs say it outright: the database's curriculum is reseeded from a script to match the Udemy lecture titles. The script is the derivative; Udemy is the truth. If they ever disagree, you fix the script, not Udemy.

How to actually use it

"Source of truth" isn't just vocabulary — it's a design rule you apply on purpose:

1. Pick one place to be canonical for each kind of information. The repo for code. The database for app data. One config file for a setting — not the same fact copy-pasted into five files.
2. Make everything else derive from it. Generate caches, mirrors, and reports from the source of truth so they can always be rebuilt. This project's node_modules (rebuilt from package.json) and its .open-next/ build output (rebuilt from source) are derivatives — that's exactly why they're safe to delete and gitignore.
3. Never hand-edit a derivative. If the curriculum's source of truth is a script, you don't fix a typo by editing the live database — you fix the script and re-run it, or the next reseed silently undoes your edit. Editing the derived copy is how "I swear I fixed this" bugs are born.

"The Working Tree Is Dirty" — You Have Unsaved Changes

This one sounds like an accusation. It isn't. It's just git reporting a fact.

Your working tree (also called the working directory) is simply the files in your project folder as they exist on disk right now — the actual .ts, .swift, and .mdx files you open in your editor. Not git's history, not some hidden snapshot — the literal files you can see in Finder.

Git constantly compares those on-disk files against the last committed snapshot (the most recent save point in history). The result of that comparison has a name:

• Clean — the files on disk are identical to the last commit. There's nothing unsaved. git status says "nothing to commit, working tree clean."
• Dirty — the files on disk differ from the last commit. You've edited, added, or deleted something that isn't committed yet. That's all "dirty" means: there are changes git hasn't recorded into history yet.

Figure 2 — "Clean" vs "dirty" is just the answer to one comparison: do the files on disk match the last commit? Dirty means "yes, there's unsaved work here" — a normal, expected state every time you're mid-edit.

Run git status on a dirty tree and it sorts your changes into three buckets. Knowing the buckets makes the output readable:

Any one of those makes the tree dirty. The cure is always the same: commit the changes (record them into history) — or, if you don't want them, discard them. Either way the tree goes clean again because disk once more matches the last commit. The mechanics of staging and committing — and the "three trees" this all flows through — are the whole subject of Git Ch 1: How Git Actually Works.

This connects straight back to source of truth. A dirty working tree means your local files are ahead of where the recorded history is — you've made changes that the source of truth (the repo, and ultimately GitHub) doesn't know about yet. git commit records them locally; git push sends them up so the real source of truth reflects them too. This is also why this very project runs an auto-sync routine that periodically commits and pushes a dirty tree on its own: it's keeping the working tree from drifting too far from the source of truth, so nothing important lives only on one machine.

SQL — The Language for Talking to a Database

A database is a program whose entire job is to store structured data and hand it back fast, safely, and to many readers at once. But you don't poke at the stored bytes directly — you ask the database for what you want using a language it understands. For the vast majority of databases, that language is SQL (Structured Query Language, usually pronounced "sequel" or just "S-Q-L").

SQL is declarative: you describe what you want and let the database figure out how to get it. You don't write loops or open files; you write a sentence-like statement and the database does the work.

-- "Give me the title and date of the 5 newest notes for user 42,
--  newest first."
SELECT title, created_at
FROM   notes
WHERE  user_id = 42
ORDER  BY created_at DESC
LIMIT  5;

Read it almost like English: select these columns from this table where this condition holds, ordered this way, limited to five. That readability is the point — and remarkably, fewer than ten statement shapes (SELECT, INSERT, UPDATE, DELETE, CREATE TABLE, and a couple more) cover almost everything a normal app ever needs.

Schema — The Blueprint of Your Data

If SQL is how you talk to the database, the schema is the shape of what's in it. Your schema is the complete definition of your tables: what tables exist, what columns each one has, what type each column holds, and how the tables relate to each other.

Think of a table as a spreadsheet with strict rules, and the schema as the rules: this sheet is called users; it has an id that's a whole number and unique; an email that's text and required; a created_at that's text. You define it with SQL:

CREATE TABLE users (
  id         INTEGER PRIMARY KEY AUTOINCREMENT,
  email      TEXT    UNIQUE NOT NULL,
  name       TEXT,
  created_at TEXT    NOT NULL
);

That CREATE TABLE statement is a piece of your schema. Run a few of them and you've described the entire structure of your app's data — which is why people say things like "what does the schema look like?" to mean "what tables and columns do we have?"

Figure 3 — A two-table schema. The schema isn't the data (the actual rows) — it's the structure the data must fit into. notes.user_id referencing users.id is a relationship, and it's part of the schema too.

Two related terms you'll hear in the same breath:

• Migration — a script that changes the schema over time (adds a column, creates a new table). Because the schema evolves as your app grows, you keep these changes as numbered files (this project keeps them under saas/migrations/) so the database can be rebuilt step by step. The migration files are the source of truth for how the schema changed over time.
• Relational — a database built around tables-with-relationships (like the arrow above) is called relational. SQL is the language of relational databases.

Cloudflare D1 — "What Tech Is This?"

Now the concrete one. When the docs say "the data lives in D1," what is D1?

Cloudflare D1 is a database — specifically, a serverless SQLite database that runs on Cloudflare's edge network and plugs directly into a Cloudflare Worker. Unpack that one phrase at a time:

So "what tech is this?" — it's SQLite (the engine) + SQL (the language) + Cloudflare's serverless edge (the hosting), bundled so a Worker can store data without you ever running a database server. This project's database, simpleappshipper-db, is exactly this: the source of truth for users, subscriptions, the video curriculum, and the community library.

Figure 4 — Where D1 sits. The Worker handles requests and reaches into D1 for structured data (rows you query with SQL) and R2 for big files (the actual video bytes). D1 holds "which videos exist and who can watch them"; R2 holds the videos themselves.

How the Five Fit Together

Each term made sense on its own; here's the satisfying part — they describe one connected system:

Your source of truth for code is the git repo; a dirty working tree just means your local files are ahead of that truth and need committing. Your source of truth for data is Cloudflare D1; its schema is the shape that data must fit; and SQL is the language your Worker speaks to read and write it.

Notice the same idea wearing two hats: source of truth governs both your code (repo) and your data (D1), and "dirty" / "uncommitted" is simply the gap between a local copy and the truth — whether that copy is your edited files or an un-pushed branch.

The Five, One Sentence Each

Pin these.

1. Source of truth — the one copy that's authoritative by definition; when copies disagree, this one is right, and everything else derives from it.
2. Working tree is dirty — the files on your disk differ from the last commit, i.e. you have uncommitted changes (a normal, expected state while you work).
3. SQL — the declarative language for asking a relational database for what you want (SELECT, INSERT, UPDATE, DELETE…).
4. Schema — the blueprint of your data: which tables and columns exist, their types, and how they relate.
5. Cloudflare D1 — a serverless SQLite database that runs on Cloudflare's edge and plugs straight into a Worker; this site's source of truth for structured data.

Try It Yourself (10 Minutes)

You only need a terminal and any git project (this one works).

1. See a clean tree. With no edits pending, run git status. Read the magic words: "nothing to commit, working tree clean." Disk matches the last commit.
2. Make it dirty. Edit any file and save. Run git status again — now it's dirty, and the file shows under "Changes not staged for commit" (modified/unstaged).
3. Create an untracked file. Make a new file, e.g. scratch.txt. Run git status — it appears under "Untracked files" because git has never seen it.
4. Stage one, watch the buckets move. Run git add scratch.txt, then git status. It jumps to "Changes to be committed" (staged) while your edited file stays unstaged. Now you can see the three buckets from the table above at once.
5. Go clean again. Either git checkout -- <file> / delete scratch.txt to discard, or git commit to record. Run git status once more — back to "working tree clean." You just watched dirty → clean happen.
6. Feel "source of truth." Run git log -1 to see the latest commit on your machine, then think: is that the same as what's on GitHub? git push is the act of making the real source of truth agree with your local copy.

For the SQL/schema/D1 half, the hands-on lab — create a table, run the six core queries, wire D1 into a Worker so your data survives a restart — is Ch 9, built to be typed along with.

Where This Lands in the Series

Ch 22 decoded the package tooling under every project. This chapter decoded the vocabulary around a project — the words in your terminal, in git's output, and in the docs — so none of them are fuzzy anymore. Two of them (working tree, source of truth) lean on the Git & GitHub series; three (SQL, schema, D1) open straight into Ch 9 and the D1 deep dive when you want the hands-on version.

One operational chapter still comes before that track opens: Ch 24 settles the cost of shipping — Cloudflare Workers Builds vs GitHub Actions, where the bill actually lives, and why a headless Mac Mini can beat both. Then Part 3 opens the modern-frontend track — why almost every team in 2026 reaches for a framework like React or Next.js instead of vanilla JavaScript, and what that solves. You now have the shared vocabulary to read everything that comes next without tripping over a word.