Design a relational schema

A schema is more than column storage. It is the layer that protects your data when application code has a bug — and application code always eventually has a bug.

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 a notes schema that uses 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 here is one your route handlers would otherwise have to enforce in three different places. D1 enforces foreign key constraints, so an insert with a user_id that matches no user fails loudly:

D1_ERROR: FOREIGN KEY constraint failed

That error during development is the schema doing its job. The alternative is an orphaned note discovered months later.

Index what you actually query

Add indexes for real filter and ordering patterns, not every column:

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

This one serves “this user’s notes, newest first,” which is the query the application runs on every page load. An index speeds selected reads but costs storage and write work — each insert updates every index on the table. Indexing every column makes writes slow while helping no actual query.

Decide the boring things once

Keep timestamps in a documented format. I use integer Unix milliseconds and note that in the schema file; mixing ISO strings in some rows and integers in others makes sorting quietly wrong. SQLite’s type system is permissive, so discipline has to come from you.

For multi-tenant data, define ownership from the start: which column marks the owning user or tenant, and every query filters by it. Retrofitting tenancy onto a shared table is far harder than including user_id not null on day one.

Now write the notes schema, then 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 build any routes.