Design a relational schema

A schema is not just a place to store columns. It’s the layer that protects your data when the application code has a bug. And application code always has a bug, sooner or later.

Primary keys identify rows. Foreign keys express relationships. NOT NULL, UNIQUE and CHECK constraints reject invalid state even when a code path forgets to validate. Here is the notes schema, using all of them:

create table users (
  id integer primary key autoincrement,
  email text not null unique,
  created_at integer not null
);

create table notes (
  id integer primary key autoincrement,
  user_id integer not null references users(id),
  title text not null check (length(title) <= 200),
  body text,
  created_at integer not null
);

Every rule in there is one your route handlers would otherwise enforce in three different places. D1 enforces foreign keys, so a note pointing at a user that does not exist fails loudly:

D1_ERROR: FOREIGN KEY constraint failed

When you see that error in development, the schema is doing its job. The alternative is an orphaned note you discover months later.

Index what you actually query

Add indexes for the filters and orderings you really run, not for every column:

create index idx_notes_user_created on notes (user_id, created_at desc);

This one serves “this user’s notes, newest first”, the query the app runs on every page load.

An index speeds up the reads it matches, but it costs storage and write work. Every insert updates every index on the table. Index every column and writes get slow while no real query gets faster.

Decide the boring things once

Pick one timestamp format and write it down. I use integer Unix milliseconds and note that in the schema file. Mix ISO strings in some rows and integers in others, and sorting goes quietly wrong. SQLite’s type system is permissive, so the discipline has to come from you.

For multi-tenant data, define ownership on day one. Decide which column marks the owning user, and filter by it in every query. user_id integer not null costs nothing now. Adding it to a shared table later, with data already in it, is a painful migration.

Now write the notes schema and try to break it. Insert a duplicate email, a note with a missing owner, and a title over 200 characters. Each attempt should fail with a constraint error, before you write a single route.